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

Wednesday, May 08, 2013

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

Post a Comment