C# Row.Elements方法代码示例

        // Add a cell with the specified address to a row.
        private static DocumentFormat.OpenXml.Spreadsheet.Cell CreateCell(Row row, String address)
        {
            DocumentFormat.OpenXml.Spreadsheet.Cell cellResult;
            DocumentFormat.OpenXml.Spreadsheet.Cell refCell = null;

            // Cells must be in sequential order according to CellReference. 
            // Determine where to insert the new cell.
            foreach (DocumentFormat.OpenXml.Spreadsheet.Cell cell in row.Elements<DocumentFormat.OpenXml.Spreadsheet.Cell>())
            {
                // if (string.Compare(cell.CellReference.Value, address, true) > 0)
                if (CompareCellName(cell.CellReference.Value, address) > 0)
                {
                    refCell = cell;
                    break;
                }
            }

            cellResult = new DocumentFormat.OpenXml.Spreadsheet.Cell();
            cellResult.CellReference = address;

            row.InsertBefore(cellResult, refCell);
            return cellResult;
        }

        private ExpandoObject ParseFromRow(Row row, ISheetDefinition sheetDefinition)
        {
            IDictionary<string, object> expando = new ExpandoObject();
            var cells = row.Elements<Cell>().ToList();
            var colDefs = sheetDefinition.ColumnDefinitions.ToList();
            var count = Math.Max(cells.Count, colDefs.Count);
            for (var index = 0; index < count; index++)
            {
                var colDef = index < colDefs.Count ? colDefs[index] : null;
                var cell = index < cells.Count ? cells[index] : null;
                string propName;
                object propValue;
                if (cell != null)
                {
                    propName = cell.GetMdsolAttribute("propertyName");
                    propValue = _converterManager.GetCSharpValue(cell);
                }
                else if (colDef != null)
                {
                    propName = colDef.PropertyName;
                    propValue = null;
                }
                else
                {
                    throw new NotSupportedException("Cell and CellDefinition are both null");
                }

                expando.Add(propName, propValue);
            }
            return (ExpandoObject) expando;
        }

        // Add a cell with the specified address to a row.
        protected Cell CreateCell(Row row, String address)
        {
            Cell cellResult;
            Cell refCell = null;

            // Cells must be in sequential order according to CellReference.
            // Determine where to insert the new cell.
            foreach (Cell cell in row.Elements<Cell>())
            {
                if (string.Compare(cell.CellReference.Value, address, true) > 0)
                {
                    refCell = cell;
                    break;
                }
            }

            cellResult = new Cell();
            cellResult.CellReference = address;

            row.InsertBefore(cellResult, refCell);
            return cellResult;
        }

 /// <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 bool IsEmpty(Row row)
        {
            Cell c;

            for (int i = 0; i < row.ChildElements.Count(); i++)
            {
                // get current cell at i
                c = row.Elements<Cell>().ElementAt(i);
                if (c.CellValue != null)
                {
                    return false;
                }

            }

            return true;
        }

 private static Cell GetCell(int cellIndex, Row row)
 {
     try
     {
         return row.Elements<Cell>().ElementAt(cellIndex);
     }
     catch
     {
         return null;
     }
 }

 private static Cell GetCell(Worksheet worksheet, Row row, string columnName)
 {
     try
     {
         return row.Elements<Cell>().Where(c => string.Compare
                (c.CellReference.Value, columnName +
                row.RowIndex, true) == 0).First();
     }
     catch (Exception)
     {
         return null;
     }
 }

        private static void SetRowTypeSubType(ref RowTypeEnum rowType, ref RowSubTypeEnum rowSubType, Row row, int rowCount, SharedStringTable sst)
        {
            Debug.WriteLine("SetRowTypeSubType");

            rowType = RowTypeEnum.UNKNOWN_ROW_TYPE;
            rowSubType = RowSubTypeEnum.UNKNOWN_ROW_SUB_TYPE;

            if (row.RowIndex == 1)
            {
                rowType = RowTypeEnum.HeaderRow;
                rowSubType = RowSubTypeEnum.HeaderRow1;
            }
            else if (row.RowIndex == 2)
            {
                rowType = RowTypeEnum.HeaderRow;
                rowSubType = RowSubTypeEnum.HeaderRow2;
            }
            else if (row.RowIndex == 3)
            {
                rowType = RowTypeEnum.HeaderRow;
                rowSubType = RowSubTypeEnum.HeaderRow3;
            }
            else if (row.RowIndex == 4)
            {
                rowType = RowTypeEnum.HeaderRow;
                rowSubType = RowSubTypeEnum.HeaderRow4;
            }
            else if (row.RowIndex == 5)
            {
                rowType = RowTypeEnum.HeaderRow;
                rowSubType = RowSubTypeEnum.HeaderRow5;
            }
            else if (row.RowIndex == 6)
            {
                rowType = RowTypeEnum.HeaderRow;
                rowSubType = RowSubTypeEnum.HeaderRow6;
            }
            else if (row.RowIndex == 7)
            {
                rowType = RowTypeEnum.HeaderRow;
                rowSubType = RowSubTypeEnum.HeaderRow7;
            }
            else if (row.RowIndex == 8)
            {
                rowType = RowTypeEnum.HeaderRow;
                rowSubType = RowSubTypeEnum.HeaderRow8;
            }
            else if (row.RowIndex == rowCount)
            {
                rowType = RowTypeEnum.FooterRow;
                rowSubType = RowSubTypeEnum.FooterRow3;
            }
            else
            {
                Cell cell = row.Elements<Cell>().ElementAt(0);
                string cellColumn = clsOpenXmlBC.GetExcelColumnName(cell.CellReference.ToString());
                string cellValue = clsOpenXmlBC.GetCellValue(cell, sst);
                Debug.WriteLine("\t" + "cell.CellReference" + cell.CellReference.ToString() + ", cellColumn: " + cellColumn + ", cellValue: " + cellValue);
                if ((cellColumn == "A") && (clsValidation.IsInt32(cellValue)))
                {
                    rowType = RowTypeEnum.DetailRow;
                    rowSubType = RowSubTypeEnum.DetailRow1;
                }
                else if ((cellColumn == "B") && (cellValue == "Total Meter Readings Entered :"))
                {
                    rowType = RowTypeEnum.FooterRow;
                    rowSubType = RowSubTypeEnum.FooterRow1;
                }
                else if (((cellColumn == "B") && (cellValue == "Total Meter Readings Not Entered"))
                    || ((cellColumn == "A") && (cellValue == "Total Meter Readings Not Entered")))
                {
                    rowType = RowTypeEnum.FooterRow;
                    rowSubType = RowSubTypeEnum.FooterRow2;
                }
                else
                {
                    rowType = RowTypeEnum.DetailRow;
                    rowSubType = RowSubTypeEnum.DetailRow2;
                }
            }
        }

        /// <summary>
        /// Creates a <see cref="OilWellModel"/> from the provided row.
        /// </summary>
        /// <param name="row">The spreadsheet row to import.</param>
        /// <param name="spreadsheet">The source spreadsheet. This is used to resolve shared string values.</param>
        /// <returns>Populated <see cref="OilWellModel"/> containing data from provided spreadsheet row.</returns>
        /// <exception cref="ApplicationException"> if source spreadsheet contains invalid data.</exception>
        private OilWellModel CreateOilWellModel(Row row, SpreadsheetDocument spreadsheet)
        {
            var model = new OilWellModel();
            var cells = row.Elements<Cell>();
            foreach (var cell in cells)
            {
                string cellValue = cell.CellValue.Text;
                if (cell.DataType != null && cell.DataType == CellValues.SharedString)
                {
                    var sharedStringId = Int32.Parse(cellValue);
                    cellValue = spreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(sharedStringId).Text.Text;
                }

                var columnNumber = ExcelColumnNameToNumber(cell.CellReference);
                var columnType = (SpreadsheetColumn) columnNumber;

                switch (columnType)
                {
                    case SpreadsheetColumn.ApiNumber:
                        model.API = cellValue;
                        break;
                    case SpreadsheetColumn.BarrelsPerInch:
                        model.BarrelsPerInch = float.Parse(cellValue);
                        break;
                    case SpreadsheetColumn.County:
                        model.County = cellValue;
                        break;
                    case SpreadsheetColumn.Latitude:
                        model.Latitude = float.Parse(cellValue);
                        break;
                    case SpreadsheetColumn.Longitude:
                        model.Longitude = float.Parse(cellValue);
                        break;
                    case SpreadsheetColumn.Owner:
                        model.Owner = cellValue;
                        break;
                    case SpreadsheetColumn.PropertyNumber:
                        model.PropertyNumber = cellValue;
                        break;
                    case SpreadsheetColumn.RNG:
                        model.RNG = cellValue;
                        break;
                    case SpreadsheetColumn.SEC:
                        model.SEC = cellValue;
                        break;
                    case SpreadsheetColumn.TWP:
                        model.TWP = cellValue;
                        break;
                    case SpreadsheetColumn.TankMID:
                        model.TankMID = cellValue;
                        break;
                    case SpreadsheetColumn.TankName:
                        model.TankName = cellValue;
                        break;
                    case SpreadsheetColumn.TankNumber:
                        model.TankNumber = int.Parse(cellValue);
                        break;
                    case SpreadsheetColumn.TankSize:
                        model.TankSize = float.Parse(cellValue);
                        break;
                    case SpreadsheetColumn.WellName:
                        model.WellName = cellValue;
                        break;
                    default:
                        throw new ApplicationException("Invalid column id in cell name " + cell.CellReference);
                }
                //Console.WriteLine("{0} ({1}): {3} = {2}", cell.CellReference, columnNumber, cellValue, columnType);
            }
            return model;
        }

        private static void PopulateAQ1DetailRow(SharedStringTable sst, clsAQ1ReportDetailRow objAQ1ReportRow, RowSubTypeEnum rowSubType, Row row)
        {
            Debug.WriteLine("PopulateAQ1DetailRow");
            string cellValue;
            string column;

            foreach (Cell c in row.Elements<Cell>())
            {
                cellValue = clsOpenXmlBC.GetCellValue(c, sst);

                column = clsOpenXmlBC.GetExcelColumnName(c.CellReference.ToString());
                Debug.WriteLine("\t" + "CellReference: " + c.CellReference.ToString() + ", column: " + column);

                switch (column)
                {
                    case "A": objAQ1ReportRow.A_row1_Folio = cellValue; break;
                    case "B": objAQ1ReportRow.B_row1_CCN = cellValue; break;
                    case "C": objAQ1ReportRow.C_row1_CCNStat = cellValue; break;
                    case "D": objAQ1ReportRow.D_row1_CCNLink = cellValue; break;
                    case "E": objAQ1ReportRow.E_row1_CCN10 = cellValue; break;
                    case "F": objAQ1ReportRow.F_row1_MtrStat = cellValue; break;
                    case "G": objAQ1ReportRow.G_row1_GAP = cellValue; break;
                    case "H":
                        if (rowSubType == RowSubTypeEnum.DetailRow1)
                            objAQ1ReportRow.H_row1_CurrentDate = cellValue;
                        else if (rowSubType == RowSubTypeEnum.DetailRow2)
                            objAQ1ReportRow.H_row2_CurrentRdg = cellValue;

                        break;
                    case "I":
                        if (rowSubType == RowSubTypeEnum.DetailRow1)
                            objAQ1ReportRow.I_row1_PreviousDate = cellValue;
                        else if (rowSubType == RowSubTypeEnum.DetailRow2)
                            objAQ1ReportRow.I_row2_PreviousRdg = cellValue;

                        break;
                    case "J": objAQ1ReportRow.J_row1_CutRemvDt = cellValue; break;
                    case "K": objAQ1ReportRow.K_row1_RstrRplcDt = cellValue; break;
                    case "L": objAQ1ReportRow.L_row1_CsmpByMeter = cellValue; break;
                    case "M": objAQ1ReportRow.M_row1_Days = cellValue; break;
                    case "N": objAQ1ReportRow.N_row1_CsmpBilled = cellValue; break;
                    case "O": objAQ1ReportRow.O_row1_Water = cellValue; break;
                    case "P": objAQ1ReportRow.P_row1_Sewarage = cellValue; break;
                    case "Q": objAQ1ReportRow.Q_row1_Rent = cellValue; break;
                    case "R": objAQ1ReportRow.R_row1_BillAmt = cellValue; break;
                    case "S": objAQ1ReportRow.S_row1_Additional = cellValue; break;
                    case "T": objAQ1ReportRow.T_row1_Flat = cellValue; break;
                    case "U": objAQ1ReportRow.U_row1_Flag = cellValue; break;
                    case "V": objAQ1ReportRow.V_row1_GroupCode = cellValue; break;
                    case "W": objAQ1ReportRow.W_row1_RateCharge = cellValue; break;
                    case "X":
                        if (rowSubType == RowSubTypeEnum.DetailRow1)
                            objAQ1ReportRow.X_row1_CutDate = cellValue;
                        else if (rowSubType == RowSubTypeEnum.DetailRow2)
                            objAQ1ReportRow.X_row2_Reason_Part2 = cellValue;

                        break;
                    case "Y": objAQ1ReportRow.Y_row1_Reason = cellValue; break;
                }
            }
        }

        private static void PopulateAQ1HeaderRow(SharedStringTable sst, clsAQ1ReportHeaderRow objAQ1HeaderRow, RowSubTypeEnum rowSubType, Row row)
        {
            Debug.WriteLine("PopulateAQ1HeaderRow");

            if (rowSubType == RowSubTypeEnum.HeaderRow1)
            {
                objAQ1HeaderRow.Date = clsOpenXmlBC.GetCellValue(row.Elements<Cell>().ElementAt(2), sst).TrimStart(' ', ':');
            }
            else if (rowSubType == RowSubTypeEnum.HeaderRow2)
            {
                objAQ1HeaderRow.PageNo = clsOpenXmlBC.GetCellValue(row.Elements<Cell>().ElementAt(2), sst);
            }
            else if (rowSubType == RowSubTypeEnum.HeaderRow3)
            {
                //Empty Row. No action;
            }
            else if (rowSubType == RowSubTypeEnum.HeaderRow4)
            {
                objAQ1HeaderRow.GeneratedBy = clsOpenXmlBC.GetCellValue(row.Elements<Cell>().ElementAt(4), sst);
            }
            else if (rowSubType == RowSubTypeEnum.HeaderRow5)
            {
                objAQ1HeaderRow.WardName = clsOpenXmlBC.GetCellValue(row.Elements<Cell>().ElementAt(1), sst).TrimStart(' ', ':');
            }
            else if (rowSubType == RowSubTypeEnum.HeaderRow6)
            {
                objAQ1HeaderRow.MeterBinderNo = clsOpenXmlBC.GetCellValue(row.Elements<Cell>().ElementAt(1), sst).TrimStart(' ', ':');
                objAQ1HeaderRow.ReadingCycle = clsOpenXmlBC.GetCellValue(row.Elements<Cell>().ElementAt(4), sst);
                objAQ1HeaderRow.Year = clsOpenXmlBC.GetCellValue(row.Elements<Cell>().ElementAt(7), sst);
            }
        }

        private static Cell GetCell(Row row, string columnName)
        {
            string cellReference = columnName + row.RowIndex;
            int columnIndex = ExcelUtilities.ColumnNameToOrdinal(columnName);

            var cell = row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).FirstOrDefault();
            if (cell == null)
            {
                Cell refCell = null;
                foreach (Cell otherCell in row.Elements<Cell>())
                {
                    int otherColumnIndex = ExcelUtilities.ColumnNameToOrdinal(ExcelUtilities.GetColumnName(otherCell.CellReference.Value));
                    if (otherColumnIndex > columnIndex)
                    {
                        refCell = otherCell;
                        break;
                    }
                }
                cell = new Cell() { CellReference = cellReference };
                row.InsertBefore(cell, refCell);
                if (refCell == null)
                {
                    if (row.Spans == null)
                        row.Spans = new ListValue<StringValue>();
                    row.Spans.InnerText = string.Format("1:{0}", columnIndex);
                }
                //connection.DeferredSubmit();
            }
            return cell;
        }

 private static Cell GetCell(Row row, int columnIndex)
 {
     return row.Elements<Cell>().FirstOrDefault(c => string.Compare(c.CellReference.Value, GetColumnName(columnIndex) + row.RowIndex, true) == 0);
 }

        private static void IncrementIndexes(Row row, int increment)
        {
            uint newRowIndex;
            newRowIndex = System.Convert.ToUInt32(row.RowIndex.Value + increment);

            foreach (Cell cell in row.Elements<Cell>())
            {
                string cellReference = cell.CellReference.Value;
                cell.CellReference = new StringValue(cellReference.Replace(row.RowIndex.Value.ToString(), newRowIndex.ToString()));
            }

            row.RowIndex = new UInt32Value(newRowIndex);
        }

        /// <summary>
        /// Вставка ячейки по адресу
        /// </summary>
        /// <param name="row"></param>
        /// <param name="address"></param>
        /// <returns></returns>
        private Cell CreateCell(Row row, String address)
        {
            var refCell = row.Elements<Cell>().FirstOrDefault(cell => String.Compare(cell.CellReference.Value, address, StringComparison.OrdinalIgnoreCase) > 0);

            Cell cellResult = new Cell { CellReference = address };

            row.InsertBefore(cellResult, refCell);
            return cellResult;
        }