C# WorkbookPart.GetPartsOfType方法代码示例

        public static string GetCellValue(WorkbookPart wbPart, Cell theCell)
        {
            string value = theCell.InnerText;
            if (theCell.DataType != null)
            {
                switch (theCell.DataType.Value)
                {
                    case CellValues.SharedString:
                        var stringTable = wbPart.
                          GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                        if (stringTable != null)
                        {
                            value = stringTable.SharedStringTable.
                              ElementAt(int.Parse(value)).InnerText;
                        }
                        break;

                    case CellValues.Boolean:
                        switch (value)
                        {
                            case "0":
                                value = "FALSE";
                                break;
                            default:
                                value = "TRUE";
                                break;
                        }
                        break;
                }
            }
            return value;
        }

        protected string GetCellValue(WorkbookPart wbPart, Cell theCell)
        {
            if (theCell == null)
            {
                return null;
            }

            var value = theCell.InnerText;
            if (theCell.DataType == null)
            {
                return value;
            }

            switch (theCell.DataType.Value)
            {
                case (CellValues.SharedString):
                {
                    var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                    if (stringTable != null)
                    {
                        value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
                    }
                    break;
                }
                case (CellValues.Boolean):
                {
                    switch (value)
                    {
                        case "0":
                        {
                            value = "FALSE";
                            break;
                        }
                        default:
                        {
                            value = "TRUE";
                            break;
                        }
                    }
                    break;
                }
                case (CellValues.Date):
                {
                    if (!string.IsNullOrEmpty(value))
                    {
                        value = DateTime.FromOADate(Convert.ToDouble(value)).ToString(CultureInfo.CurrentCulture);
                    }
                    break;
                }
            }

            return value;
        }

        public static string GetCellValue(WorkbookPart wbPart, Cell theCell)
        {
            string value = theCell.InnerText;
            if (theCell.DataType != null)
            {
                switch (theCell.DataType.Value)
                {
                    case CellValues.SharedString:
                        var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                        if (stringTable != null)
                        {
                            value = stringTable.SharedStringTable.
                              ElementAt(int.Parse(value)).InnerText;
                        }
                        break;

                    case CellValues.Boolean:
                        switch (value)
                        {
                            case "0":
                                value = "FALSE";
                                break;
                            default:
                                value = "TRUE";
                                break;
                        }
                        break;
                    case CellValues.Date:
                        var date = new DateTime();
                        var success = DateTime.TryParse(value, out date);
                        if (success)
                        {
                            value = date.ToString("MM-yyyy");
                        }
                        break;
                }
            }
            return value;
        }

        // Adds child parts and generates content of the specified part.
        public static void CreateTable(WorkbookPart workbookPart, WorksheetPart worksheetPart, string[] columns, int topLeftColumn, int topLeftRow, int width, int height)
        {
            List<WorksheetPart> worksheets = workbookPart.GetPartsOfType<WorksheetPart>().ToList();
            uint maxTableId = worksheets.Select(ws => ws.TableDefinitionParts.ToList()).SelectMany(tableDefinitions => tableDefinitions).Aggregate<TableDefinitionPart, uint>(0, (current, tableDef) => Math.Max(tableDef.Table.Id, current));

            uint tableId = maxTableId + 1;

            var tables = new TableParts { Count = 1U };
            worksheetPart.Worksheet.Append((IEnumerable<OpenXmlElement>)tables);

            var newTableDefnPart = worksheetPart.AddNewPart<TableDefinitionPart>();
            string relationshipId = worksheetPart.GetIdOfPart(newTableDefnPart);

            string cellReference = string.Format("{0}{1}:{2}{3}", GetColumnIdentifier(topLeftColumn), topLeftRow, GetColumnIdentifier(topLeftColumn + width - 1), topLeftRow + height);
            var table1 = new Table { Id = tableId, Name = "Table" + relationshipId, DisplayName = "Table" + relationshipId, Reference = cellReference, TotalsRowShown = false };
            var autoFilter1 = new AutoFilter { Reference = cellReference };

            var tableColumns1 = new TableColumns { Count = (uint)columns.Length };
            for (int iColumn = 0; iColumn < columns.Length; iColumn++)
            {
                var tableColumn = new TableColumn { Id = (UInt32Value)(uint)iColumn + 1, Name = columns[iColumn] };
                tableColumns1.Append((IEnumerable<OpenXmlElement>)tableColumn);
            }
            var tableStyleInfo1 = new TableStyleInfo { Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };

            table1.Append((IEnumerable<OpenXmlElement>)autoFilter1);
            table1.Append((IEnumerable<OpenXmlElement>)tableColumns1);
            table1.Append((IEnumerable<OpenXmlElement>)tableStyleInfo1);

            newTableDefnPart.Table = table1;

            var table = new TablePart { Id = relationshipId };
            tables.Append((IEnumerable<OpenXmlElement>)table);

            //TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9" };
            //worksheetPart.Worksheet.Append(tableStyles1);
        }

        // Given the main workbook part, and a text value, insert the text into 
        // the shared string table. Create the table if necessary. If the value 
        // already exists, return its index. If it doesn't exist, insert it and 
        // return its new index.
        internal static int InsertSharedStringItem(WorkbookPart wbPart, string value)
        {
            int index = 0;
            bool found = false;
            var stringTablePart = wbPart
                .GetPartsOfType<SharedStringTablePart>().FirstOrDefault();

            // If the shared string table is missing, something's wrong.
            // Just return the index that you found in the cell.
            // Otherwise, look up the correct text in the table.
            if (stringTablePart == null)
            {
                // Create it.
                stringTablePart = wbPart.AddNewPart<SharedStringTablePart>();
            }

            var stringTable = stringTablePart.SharedStringTable;
            if (stringTable == null)
            {
                stringTable = new SharedStringTable();
                stringTablePart.SharedStringTable = stringTable;
            }

            // Iterate through all the items in the SharedStringTable. 
            // If the text already exists, return its index.
            foreach (SharedStringItem item in stringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == value)
                {
                    found = true;
                    break;
                }
                index += 1;
            }

            if (!found)
            {
                stringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(value)));
                stringTable.Save();
            }

            return index;
        }

        //
        //  Insert the string into the shared string table of the WorkBookPart
        //
        private int insertSharedStringItem(WorkbookPart wbPart, string Value)
        {
            //
            //  Look if the string already exists into the SharedStringTable, if it does return it
            //  else return a new one
            //
            int index = 0;
            bool found = false;
            //
            // If the shared string table is missing, something's wrong.
            // Just return the index that you found in the cell.
            // Otherwise, look up the correct text in the table.
            //
            var stringTablePart = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
            if (stringTablePart == null)
            {
                //  Create a new SharedString
                stringTablePart = wbPart.AddNewPart<SharedStringTablePart>();
            }

            var stringTable = stringTablePart.SharedStringTable;
            if (stringTable == null)
            {
                stringTable = new SharedStringTable();
            }
            //
            // Iterate through all the items in the SharedStringTable.
            // If the text already exists, return its index.
            //
            foreach (SharedStringItem item in stringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == Value)
                {
                    found = true;
                    break;
                }
                index++;
            }
            //
            //  if not found append a new SharedString to the table
            //
            if (!found)
            {
                stringTable.AppendChild(new SharedStringItem(new Text(Value)));
                try
                {
                    stringTable.Save();
                }
                catch (Exception) { }
            }

            return index;
        }

        private string GetCellValue(Worksheet worksheet, WorkbookPart wbPart, string targetCell)
        {
            uint targetRow = GetRowIndex(targetCell);

            string result = null;
            SharedStringTablePart sstPart = wbPart.GetPartsOfType<SharedStringTablePart>().First();
            SharedStringTable ssTable = sstPart.SharedStringTable;

            try
            {

                Row row = worksheet.Descendants<Row>().SingleOrDefault(r => r.RowIndex == targetRow);

                //non ho la riga
                if (row == null)
                    return result;

                Cell cell = row.Descendants<Cell>().SingleOrDefault(c => targetCell.Equals(c.CellReference));
                if (cell == null)
                    return result;

                if (cell.DataType != null && cell.DataType == CellValues.SharedString)
                {
                    result = ssTable.ChildElements[Convert.ToInt32(cell.CellValue.Text)].InnerText;
                }
                else
                {
                    if (cell.CellValue != null)
                    {
                        result = cell.CellValue.Text;
                    }
                }
            }
            catch (Exception)
            {

            }

            return result;
        }

        /// <summary>
        /// restituisce il contenuto di una cella Excel
        /// </summary>
        /// <param name="wsPart">parametro del foglio excel</param>
        /// <param name="wbPart">parametro del foglio excel</param>
        /// <param name="i">riga</param>
        /// <param name="j">colonna</param>
        /// <returns></returns>
        public string GetCellValueNew(WorksheetPart wsPart, WorkbookPart wbPart, int i, int j)
        {
            string value = null;
            String addressName = Convert.ToString((char)('A' + j));
            addressName = (addressName + (i + 1));
            Cell theCell = wsPart.Worksheet.Descendants<Cell>().Where(c => c.CellReference == addressName).FirstOrDefault();
            if (theCell != null)
            {
                string test = null;
                value = theCell.InnerText;
                if (theCell.DataType != null)
                {
                    test = theCell.DataType.Value.ToString();
                    switch (theCell.DataType.Value)
                    {
                        case CellValues.String:
                            value = theCell.GetFirstChild<CellValue>()?.Text;
                            break;
                        case CellValues.SharedString:
                            var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                            if (stringTable != null)
                            {
                                value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
                            }
                            break;
                    }
                }
                else {
                    value = theCell.GetFirstChild<CellValue>()?.Text;
                }
            }

            return value;
        }

        public ExcelHelper Open(string ExcelFile,bool IsEditable=false)
        {
            _expectSheets = new List<string>();
            OpenSettings os = new OpenSettings();
            this._excelFilePath = ExcelFile;
            _doc = SpreadsheetDocument.Open(_excelFilePath, IsEditable);
            _wbPart = _doc.WorkbookPart;
            _shareStringPart = _wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();

            shareStringDic = new Dictionary<int, string>();

            int j = 0;

            foreach (var item in _shareStringPart.SharedStringTable.Elements<SharedStringItem>()) {
                shareStringDic.Add(j, item.InnerText);
                j++;
            }

            return _instance;
        }

        /// <summary>
        /// Заполнение ячейки данными
        /// </summary>
        /// <param name="wbPart"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        private int InsertSharedStringItem(WorkbookPart wbPart, string value)
        {
            var index = 0;
            var found = false;
            var stringTablePart = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault() ??
                                  wbPart.AddNewPart<SharedStringTablePart>();

            var stringTable = stringTablePart.SharedStringTable ?? new SharedStringTable();

            foreach (var item in stringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == value)
                {
                    found = true;
                    break;
                }
                index += 1;
            }

            if (found) return index;
            stringTable.AppendChild(new SharedStringItem(new Text(value)));
            stringTable.Save();

            return index;
        }

 /// <summary>
 /// Function is used to read row values for the provided excel sheet
 /// </summary>
 /// <param name="row">Row from the excel sheet</param>
 /// <param name="workbookPart">WorkbookPart object</param>
 /// <returns>List of values in row</returns>
 private static Collection<string> ReadRow(Row row, WorkbookPart workbookPart)
 {
     string value = string.Empty;
     int rowSize = row.Elements().Count(), emptyValue = 0;
     Collection<string> rowValue = new Collection<string>();
     try
     {
         foreach (Cell cell in row.Elements<Cell>())
         {
             if (null != cell)
             {
                 CellValue cellValue = cell.Descendants<CellValue>().FirstOrDefault();
                 if (null != cellValue)
                 {
                     value = cellValue.Text;
                     if (null != cell.DataType)
                     {
                         switch (cell.DataType.Value)
                         {
                             case CellValues.SharedString:
                                 var stringTable = workbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                                 if (null != stringTable)
                                 {
                                     value = stringTable.SharedStringTable.
                                       ElementAt(int.Parse(value, CultureInfo.InvariantCulture)).InnerText;
                                 }
                                 break;
                             case CellValues.Boolean:
                                 switch (value)
                                 {
                                     case "0":
                                         value = "FALSE";
                                         break;
                                     default:
                                         value = "TRUE";
                                         break;
                                 }
                                 break;
                         }
                     }
                 }
                 else
                 {
                     value = string.Empty;
                 }
                 value = value.Trim();
                 if (string.IsNullOrWhiteSpace(value))
                 {
                     emptyValue++;
                 }
                 if (string.Equals("NA", value, StringComparison.OrdinalIgnoreCase))
                 {
                     value = string.Empty;
                 }
                 rowValue.Add(value);
             }
         }
     }
     catch (Exception exception)
     {
         ErrorLogger.LogErrorToTextFile(errorFilePath, "Exception occurred while reading row value: " + exception.Message);
     }
     if (emptyValue == rowSize)
     {
         return null;
     }
     return rowValue;
 }

        private string FindCellInnerValue(WorkbookPart wbPart, Cell theCell,bool reference=true)
        {
            if (theCell != null)
            {
                var value = theCell.InnerText;

                if (theCell.DataType != null)
                {
                    switch (theCell.DataType.Value)
                    {
                        case CellValues.SharedString:
                            var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                            if (stringTable != null)
                            {
                                value = stringTable.SharedStringTable.
                                  ElementAt(int.Parse(value)).InnerText;
                                if (reference)
                                    return theCell.CellReference;
                                else
                                    return value;
                            }
                            break;
                    }
                }
                if(theCell.CellValue!=null)
                    return theCell.CellValue.InnerText;
            }
            return null;
        }

        static string GetCellValue(WorkbookPart wbPart, Cell theCell)
        {
            string value = theCell.InnerText;
            if (theCell.DataType != null)
            {
                switch (theCell.DataType.Value)
                {
                    case CellValues.SharedString:
                        var stringTable = wbPart.
                          GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                        if (stringTable != null)
                        {
                            //var allNoAttributesValue = stringTable.SharedStringTable.
                            //    ElementAt(int.Parse(value)).Where(m => m.HasAttributes == false).Select(m => m.InnerText).Aggregate((m1, m2) => m1 + m2);
                            //value = allNoAttributesValue;

                            //var a = stringTable.SharedStringTable.
                            //        ElementAt(int.Parse(value));
                            //var node = stringTable.SharedStringTable.
                            //    ElementAt(int.Parse(value)).First();

                            //var nodeA = stringTable.SharedStringTable.
                            //    ElementAt(int.Parse(value));

                            var allAllLocalNameNode = stringTable.SharedStringTable.
                                ElementAt(int.Parse(value)).Where(m => allowLocalName.Contains(m.LocalName)).ToList();
                            if (allAllLocalNameNode.Any())
                            {
                                value = allAllLocalNameNode.Select(m => m.InnerText).Aggregate((m1, m2) => m1 + m2);
                            }
                            else
                            {
                                value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
                            }

                            //var a = System.Text.RegularExpressions.Regex.Replace(fieldFirstChild.OuterXml, "<[^>]*>", "");

                            //!!Old first field
                            //var fieldFirstChild = stringTable.SharedStringTable.
                            //    ElementAt(int.Parse(value)).FirstChild;
                            //if (fieldFirstChild != null)
                            //{
                            //    value = fieldFirstChild.InnerText;
                            //}
                            //else
                            //{
                            //    value = stringTable.SharedStringTable.
                            //      ElementAt(int.Parse(value)).InnerText;
                            //}
                        }
                        break;

                    case CellValues.Boolean:
                        switch (value)
                        {
                            case "0":
                                value = "FALSE";
                                break;
                            default:
                                value = "TRUE";
                                break;
                        }
                        break;
                    case CellValues.String:
                        if (theCell.CellValue != null)
                            value = theCell.CellValue.InnerText;
                        break;
                }
            }
            return value;
        }

 private string GetCellValue(WorkbookPart wbPart, Cell cell)
 {
     if (cell.DataType != null && cell.DataType == CellValues.SharedString && !string.IsNullOrWhiteSpace(cell.CellValue.InnerText))
     {
         var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
         if (stringTable != null)
         {
             return stringTable.SharedStringTable.ElementAt(int.Parse(cell.CellValue.InnerText)).InnerText;
         }
         else
         {
             return cell.CellValue.InnerText;
         }
     }
     else
     {
         return cell?.CellValue?.InnerText;
     }
 }

        //Temporal hide
        /*
        public List<SCNNiveldeGestion> GetValuesIndexNiveldeGestion(Stream ExcelStream, string sheetName, string columnName, List<string> GestionListValues, List<string> EstatalValues)
        {
            List<SCNNiveldeGestion> ListNiveldeGestion = new List<SCNNiveldeGestion>();
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(ExcelStream, false))
            {
                WorkbookPart wbPart = document.WorkbookPart;
                Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().
                  Where(s => s.Name == sheetName).FirstOrDefault();

                if (theSheet == null)
                {
                    throw new ArgumentException("sheetName");
                }

                WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
                IEnumerable<Cell> cells = wsPart.Worksheet.Descendants<Cell>().Where(c => string.Compare(GetColumnName(c.CellReference.Value),
                    columnName, true) == 0).OrderBy(R => R.CellReference.Value);
                foreach (Cell currentcell in cells)
                {
                    string cValue = GetCleanValueCell(currentcell, ref wbPart);
                    if (!string.IsNullOrEmpty(cValue))
                    {
                        if (GestionListValues.IndexOf(cValue) != -1)
                        {
                            ListNiveldeGestion.Add(new SCNNiveldeGestion
                            {
                                NiveldeGestionIndex = cValue,
                                Position = GetRowIndex(currentcell.CellReference.Value)
                            });
                        }
                        else
                        {
                            if (EstatalValues.IndexOf(cValue) != -1)
                            {
                                ListNiveldeGestion.Add(new SCNNiveldeGestion
                                {
                                    NiveldeGestionIndex = "Estatal",
                                    Position = GetRowIndex(currentcell.CellReference.Value)
                                });
                            }
                            else
                            {
                                if (!string.Equals(cValue, "Programa de Cumplimiento Grupo Modelo") && !string.Equals(cValue, "Indicar el órgano que emite el ordenamiento (sin abreviaciones)"))
                                {
                                    ListNiveldeGestion.Add(new SCNNiveldeGestion
                                    {
                                        NiveldeGestionIndex = cValue,
                                        Position = "-1"
                                    });
                                }
                            }
                        }
                        //ValuesColumn.Append(" Value: " + GetValueCell(ref wbPart, ref wsPart, currentcell.CellReference.Value));
                    }
                }
            }

            return ListNiveldeGestion;
        }

        */
        //Final Temporal Hide
        private string GetCleanValueCell(Cell theCell, ref WorkbookPart wbPart)
        {
            string value = null;
            if (theCell != null)
            {
                value = theCell.InnerText;
                if (theCell.DataType != null)
                {
                    switch (theCell.DataType.Value)
                    {
                        case CellValues.SharedString:
                            // For shared strings, look up the value in the shared strings table.
                            var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                            // If the shared string table is missing, something's wrong.
                            // Just return the index that you found in the cell.
                            // Otherwise, look up the correct text in the table.
                            if (stringTable != null)
                            {
                                value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
                            }
                            break;
                        case CellValues.Boolean:
                            switch (value)
                            {
                                case "0":
                                    value = "FALSE";
                                    break;
                                default:
                                    value = "TRUE";
                                    break;
                            }
                            break;
                    }
                }
            }
            return value;
//.........这里部分代码省略.........