Reading Excel data into DataTable

Question

Hi all I have my excel with the following Rows and columns

SKU  Quantity  Line Price  Unit Price  Line Discount
XYZ     2          4            2           1

case 2

SKU  Quantity  Line Price  Unit Price  Line Discount
XYZ     2                                 1

In case2 after excecuting Line Discount value is showing in Line Price

public static DataSet LoadExcelData(string pUserID, string pFilePath)
    {
            DataSet lDSExcel = new DataSet();
            DataTable lDTExcel = new DataTable();

            using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(pFilePath, false))
            {

                WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
                IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
                string relationshipId = sheets.First().Id.Value;
                WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
                Worksheet workSheet = worksheetPart.Worksheet;
                SheetData sheetData = workSheet.GetFirstChild<SheetData>();
                IEnumerable<Row> rows = sheetData.Descendants<Row>();

                foreach (Cell cell in rows.ElementAt(0))
                {
                    lDTExcel.Columns.Add(GetCellValue(spreadSheetDocument, cell));
                }

                foreach (Row row in rows)
                {
                    DataRow tempRow = lDTExcel.NewRow();

                    for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
                    {
                        tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
                    }

                    lDTExcel.Rows.Add(tempRow);
                }
                lDSExcel.Tables.Add(lDTExcel);
            }
            lDTExcel.Rows.RemoveAt(0);
            return lDSExcel;
      }

public static string GetCellValue(SpreadsheetDocument document, Cell cell)
    {
            string value = string.Empty;
            SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
            //if (cell.CellValue != null)
            //    value = cell.CellValue.InnerXml;

            if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
            {
                return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
            }
            else
            {
                return value;
            }
        }
    }

But this is giving an exception can some one help me

What kind of exception? Could you please show actual message? — Yuriy Galanter, Jun 14 '13 at 14:39
Yes name space was included, exception is `string is not in correct format` i.e due to `return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;` when an empty string is there — Developer, Jun 14 '13 at 15:27

score 0 · Answer 1 · answered Jun 17 '13 at 03:42

0

In the function GetCellValue(), change the shared string return portion to:

return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(cell.CellValue.InnerText)].InnerText;

You were parsing an empty string (the variable "value", which was initialised with an empty string). I'll leave you with error checking on cell.CellValue in case that's not initialised...

answered Jun 17 '13 at 03:42

Vincent Tan

3,058
22
21

You need to keep track of the 'missing' cells in the xlsx, and then in your dataset/datatable, insert a blank or DBNull value in it's place. see http://stackoverflow.com/questions/3837981/reading-excel-open-xml-is-ignoring-blank-cells?lq=1 – Brian Wells Apr 04 '14 at 14:14

Reading Excel data into DataTable

1 Answers1