Reading Excel using OpenXML
public DataTable ReadDataFromExcel() { string filePath = @"c:/temp/temp.xlsx"; using (SpreadsheetDocument LobjDocument = SpreadsheetDocument.Open(filePath, false)) { WorkbookPart LobjWorkbookPart = LobjDocument.WorkbookPart; Sheet LobjSheetToImport = LobjWorkbookPart.Workbook.Descendants<Sheet>().First<Sheet>(); WorksheetPart LobjWorksheetPart = (WorksheetPart)(LobjWorkbookPart.GetPartById(LobjSheetToImport.Id)); SheetData LobjSheetData = LobjWorksheetPart.Worksheet.Elements<SheetData>().First(); //Read only the data rows and skip all the header rows. int LiRowIterator = 1; // for progress bar int LiTotal = LobjSheetData.Elements<Row>().Count() - MobjImportMapper.HeaderRowIndex; // ================= foreach (Row LobjRowItem in LobjSheetData.Elements<Row>().Skip(6)) { DataRow LdrDataRow = LdtExcelData.NewRow(); int LiColumnIndex = 0; int LiHasData = 0; LdrDataRow[LiColumnIndex] = LobjRowItem.RowIndex; //LiRowIterator; LiColumnIndex++; //TODO: handle restriction of column range. foreach (Cell LobjCellItem in LobjRowItem.Elements<Cell>().Where(PobjCell => ImportHelper.GetColumnIndexFromExcelColumnName(ImportHelper.GetColumnName(PobjCell.CellReference)) <= MobjImportMapper.LastColumnIndex)) { // Gets the column index of the cell with data int LiCellColumnIndex = 10; if (LiColumnIndex < LiCellColumnIndex) { do { LdrDataRow[LiColumnIndex] = string.Empty; LiColumnIndex++; } while (LiColumnIndex < LiCellColumnIndex); } string LstrCellValue = LobjCellItem.InnerText; if (LobjCellItem.DataType != null) { switch (LobjCellItem.DataType.Value) { case CellValues.SharedString: var LobjStringTable = LobjWorkbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault(); DocumentFormat.OpenXml.OpenXmlElement LXMLElment = null; string LstrXMLString = String.Empty; if (LobjStringTable != null) { LstrXMLString = LobjStringTable.SharedStringTable.ElementAt(int.Parse(LstrCellValue, CultureInfo.InvariantCulture)).InnerXml; if (LstrXMLString.IndexOf("<x:rPh", StringComparison.CurrentCulture) != -1) { LXMLElment = LobjStringTable.SharedStringTable.ElementAt(int.Parse(LstrCellValue, CultureInfo.InvariantCulture)).FirstChild; LstrCellValue = LXMLElment.InnerText; } else { LstrCellValue = LobjStringTable.SharedStringTable.ElementAt(int.Parse(LstrCellValue, CultureInfo.InvariantCulture)).InnerText; } } break; default: break; } } LdrDataRow[LiColumnIndex] = LstrCellValue.Trim(); if (!string.IsNullOrEmpty(LstrCellValue)) LiHasData++; LiColumnIndex++; } if (LiHasData > 0) { LiRowIterator++; LdtExcelData.Rows.Add(LdrDataRow); } } } return LdtExcelData; }
span.fullpost {display:none;}
