CodeKicks.com
Focus on Microsoft Technologies - Tutorials, Articles, Code Samples.

Wednesday, May 08, 2013

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;
        }

Post a Comment