Click here to Skip to main content
15,890,438 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
while reading excel file, empty cell value generate the error...I want to ignore the empty cell value..

What I have tried:

C#
public static DataTable GetDataTableFromSpreadsheet(Stream MyExcelStream, bool ReadOnly)
{
    DataTable dt = new DataTable();
    using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(MyExcelStream, ReadOnly))
    {
        WorkbookPart workbookPart = sDoc.WorkbookPart;
        IEnumerable<Sheet> sheets = sDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
        string relationshipId = sheets.First().Id.Value;
        WorksheetPart worksheetPart = (WorksheetPart)sDoc.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))
        {
            dt.Columns.Add(GetCellValue(sDoc, cell));
        }

        foreach (Row row in rows) //this will also include your header row...
        {
            DataRow tempRow = dt.NewRow();

            for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
            {
                if (tempRow[i] == DBNull.Value || String.IsNullOrWhiteSpace(tempRow[i].ToString()))
                {
                    continue;
                }
                else
                {
                    tempRow[i] = GetCellValue(sDoc, row.Descendants<Cell>().ElementAt(i));

                }

            }

            dt.Rows.Add(tempRow);
        }
    }
    dt.Rows.RemoveAt(0);
    return dt;


}
Posted
Updated 29-May-18 2:28am
Comments
CHill60 29-May-18 8:39am    
It's a good idea to give details of any errors that are raised and on what line. That way people can help you quicker

1 solution

Try changing
if (tempRow[i] == DBNull.Value || String.IsNullOrWhiteSpace(tempRow[i].ToString()))
to
if (DBNull.Value.Equals(tempRow[i]) || String.IsNullOrWhiteSpace(tempRow[i].ToString()))
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900