Reading Excel using ClosedXML
I have used closedXML api to read the excel. Here is how you do it. Statistically, this performs better than OpenXML. public DataTable ReadDataFromExcelUsingClosedXML() { string filePath ="@c:/temp/example.xlsx"; var LobjWorkbook = new XLWorkbook(filePath); var LobjWorksheet = LobjWorkbook.Worksheets.First(); var LobjFullRange = LobjWorksheet.RangeUsed(); var LobjUsedRange = LobjWorksheet.Range(MobjImportMapper.HeaderRowIndex + 1, 1, LobjFullRange.RangeAddress.LastAddress.RowNumber, LobjFullRange.RangeAddress.LastAddress.ColumnNumber); var LiNumberOfcolumnsInTheExcel = LobjUsedRange.ColumnCount(); // for progress bar int LiAggregateRowCounter = MobjImportMapper.HeaderRowIndex; int LiTotalNumberOfRows = LobjWorksheet.RowCount() - LiAggregateRowCounter; int LiPercentage = 0; foreach (var LobjRow in LobjUsedRange.RowsUsed()) { int LiTemp = 0; object[] LobjrowData = new object[LiNumberOfcolumnsInTheExcel + 1]; LobjrowData[LiTemp] = LobjRow.RangeAddress.FirstAddress.RowNumber; LiTemp++; LobjRow.Cells().ForEach(PobjCell => LobjrowData[LiTemp++] = PobjCell.Value); LdtExcelData.Rows.Add(LobjrowData); // for progress bar LiPercentage = ((100 * LiAggregateRowCounter / LiTotalNumberOfRows) / 4) * 3; if (LiPercentage > 5) PobjBackgoundWorker.ReportProgress(LiPercentage); LiAggregateRowCounter++; // ===================== } return LdtExcelData; }
span.fullpost {display:none;}