none
how we can ignore empty cell while reading excel sheet in c#?? RRS feed

  • Question

  • here is my code...

            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++)
                        {
                                tempRow[i] = GetCellValue(sDoc, row.Descendants<Cell>().ElementAt(i));
                           
                        }
                        dt.Rows.Add(tempRow);
                    }
                }
                dt.Rows.RemoveAt(0);
                return dt;

            }

    Tuesday, May 29, 2018 9:17 AM

All replies

  • You're calling a GetCellValue method that seems to be reading the cell value. You didn't post that code so we cannot answer how it returns an empty value. In theory though it should check the cell value and look for either a null or empty string value. If it finds either then the cell is empty. Note however that this may not work correctly if you're dealing with formulas.

    Please post code in a code block (top of editor window) so it is easier to read.


    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, May 29, 2018 2:01 PM
    Moderator