Problem with skipping empty cells while importing data from .xlsx file in asp.net c# application

Question

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;

    }

Sasa Ninkovic · Accepted Answer · 2010-12-28 12:54:26Z

1

i had same problem. This is my workout:

 int offset = GetColDiff(lastCol, cell.CellReference);

     //filling empty columns
     while (offset-- > 1)
        dt.Rows[rowCounter][cnt++] = DBNull.Value;
     //filling regular column
     dt.Rows[rowCounter][cnt++] = value;

   lastCol = cell.CellReference;

******************
//calculating column distance
    int GetColDiff(string prev, string curr)
    {
        int i=0;
        int index1 = 0;
        int index2 = 0;

        while (prev!="0" && prev.Length>i && Char.IsLetter(prev[i]))//prev=="0"-startingcondition
        {
            index1 += ('Z' - 'A' + 1) * index1 + (prev[i] - 'A');
            i++;
        }
        i = 0;
        while (curr.Length>i && char.IsLetter(curr[i]))
        {
            index2 += ('Z' - 'A'+ 1) * index2 + (curr[i] - 'A');
            i++;
        }
        return index2 - index1;
    }

edited Dec 28, 2010 at 12:54

answered Dec 28, 2010 at 12:48

Sasa Ninkovic

3763 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sab Than Over a year ago

This triggered enough sparks to help me solve the problem I was facing with blank cells when using POI XSSF in Java. Thanks!

Grant Mikesh · Accepted Answer · 2010-11-12 19:12:56Z

My solution to this problem isn't quite as elegant as some might use.
First, I map the columns to a char (A, B, C, D, etc), so I can know that FirstName = A, LastName = B, and Age = C.

Next, I look through the dataCells to see if there is a cell that has the Age reference. If there is a Age cell referenced, I will check the cell's DataType.

ex: dataCells.Where(x => x.CellReference.Value.Contains(cellIndex)).First().DataType == CellValues.SharedString)

In this case, cellIndex would = 'C'.

If the previous linq query is true, then you'll go to the sharedString table and find the value for the age by CellReference.

var age = sharedStrings.ChildElements[int.Parse(dataCells.Where(x => x.CellReference.Value.Contains(cellIndex)).FirstOrDefault().InnerText)].InnerText;

Your problem with accidentally setting the LastName (Column B) to whatever the Age (Column C) should be avoided if you work off of cell reference for each DataRow.

Side note: One thing I just ran into is that blank cells in Excel are stored two different ways. Sometimes there's a reference to a SharedStringTable index (cell.DataType = "s" and cell.InnerText = "37"), and sometimes the cell is just empty (cell.DataType = null and cell.InnerText = "").

Collectives™ on Stack Overflow

Problem with skipping empty cells while importing data from .xlsx file in asp.net c# application

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related