Hi to all.

I have a problem with reading .xlsx files in asp.net mvc2.0 application, using c#. Problem occurs when reading empty cell from .xlsx file. My code simply skips this cell and reads the next one.

For example, if the contents of .xlsx file are:

FirstName   LastName   Age
John                   36

They will be read as:

FirstName   LastName   Age
John        36

Here's the code that does the reading.

        private string GetValue(Cell cell, SharedStringTablePart stringTablePart)
    {
        if (cell.ChildElements.Count == 0)
            return string.Empty;

        //get cell value
        string value = cell.ElementAt(0).InnerText;//CellValue.InnerText;

        //Look up real value from shared string table
        if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
            value = stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;

        return value;
    }



        private DataTable ExtractExcelSheetValuesToDataTable(string xlsxFilePath, string sheetName)
    {
        DataTable dt = new DataTable();

        using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(xlsxFilePath, true))
        {
            //Access the main Workbook part, which contains data
            WorkbookPart workbookPart = myWorkbook.WorkbookPart;
            WorksheetPart worksheetPart = null;

            if (!string.IsNullOrEmpty(sheetName))
            {
                Sheet ss = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).SingleOrDefault<Sheet>();
                worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);
            }
            else
            {
                worksheetPart = workbookPart.WorksheetParts.FirstOrDefault();
            }

            SharedStringTablePart stringTablePart = workbookPart.SharedStringTablePart;
            if (worksheetPart != null)
            {
                Row lastRow = worksheetPart.Worksheet.Descendants<Row>().LastOrDefault();
                Row firstRow = worksheetPart.Worksheet.Descendants<Row>().FirstOrDefault();

                if (firstRow != null)
                {
                    foreach (Cell c in firstRow.ChildElements)
                    {
                        string value = GetValue(c, stringTablePart);
                        dt.Columns.Add(value);
                    }
                }

                if (lastRow != null)
                {
                    for (int i = 2; i <= lastRow.RowIndex; i++)
                    {
                        DataRow dr = dt.NewRow();
                        bool empty = true;
                        Row row = worksheetPart.Worksheet.Descendants<Row>().Where(r => i == r.RowIndex).FirstOrDefault();

                        int j = 0;
                        if (row != null)
                        {
                            foreach (Cell c in row.ChildElements)
                            {
                                //Get cell value
                                string value = GetValue(c, stringTablePart);
                                if (!string.IsNullOrEmpty(value) && value != "")
                                    empty = false;

                                dr[j] = value;
                                j++;
                                if (j == dt.Columns.Count)
                                    break;
                            }

                            if (empty)
                                break;

                            dt.Rows.Add(dr);
                        }
                    }
                }
            }
        }

        return dt;

    }