How to read Excel to Datatable using NPOI C#

Question

I'm Trying to read Excel to DataTable using NPOI.Every thing working fine but only issue is If we have any Column cell is empty in that row it is not reading .In Excel i have 4 row's with (each row have some empty values for cells).

Excel File Image : enter image description here

After Reading That Excel To data table :enter image description here

I want like this in data table

        private DataTable GetDataTableFromExcel(String Path)
        {
        XSSFWorkbook wb;
        XSSFSheet sh;
        String Sheet_name;
        using (var fs = new FileStream(Path, FileMode.Open, FileAccess.Read))
        {
        wb = new XSSFWorkbook(fs);
        Sheet_name = wb.GetSheetAt(0).SheetName;  //get first sheet name
        }
        DataTable DT = new DataTable();
        DT.Rows.Clear();
        DT.Columns.Clear();
        // get sheet
        sh = (XSSFSheet)wb.GetSheet(Sheet_name);
        int i = 0;
        while (sh.GetRow(i) != null)
        {
         // add neccessary columns
         if (DT.Columns.Count < sh.GetRow(i).Cells.Count)
        {
        for (int j = 0; j < sh.GetRow(i).Cells.Count; j++)
        {
            DT.Columns.Add("", typeof(string));
        }
        }
        // add row
        DT.Rows.Add();

        // write row value
        for (int j = 0; j < sh.GetRow(i).Cells.Count; j++)
        {
            var cell = sh.GetRow(i).GetCell(j);
            DT.Rows[i][j] = sh.GetRow(i).GetCell(j);

        }
        i++;
        }
        return DT;
        }

Plese hlp me.

it's skiping cells ..not reading cell values for example please the attached images — Anil kumar, Jun 09 '18 at 07:31
For example In excel i have total 4 rows .. In first row each and every cells have values.In 2nd row have only value for 2nd cells , 3rd and 4th also same .. but finally it skipping and returning 4 rows . — Anil kumar, Jun 09 '18 at 07:39
Please help i'm trying but I'm not getting what happening here. — Anil kumar, Jun 09 '18 at 07:40

score 0 · Answer 1 · answered Jun 11 '18 at 13:50

you may have to try something along this line. its workable code to read the excel using NPOI.

// read the current row data
XSSFRow headerRow = (XSSFRow)sheet.GetRow(0);
// LastCellNum is the number of cells of current rows
int cellCount = headerRow.LastCellNum;
 // LastRowNum is the number of rows of current table
int rowCount = sheet.LastRowNum + 1; 
 bool isBlanKRow = false;
//Start reading data after first row(header row) of excel sheet.

for (int i = (sheet.FirstRowNum + 1); i < rowCount; i++)
{
    XSSFRow row = (XSSFRow)sheet.GetRow(i);
    DataRow dataRow = dt.NewRow();
    isBlanKRow = true;
    try
    {
        for (int j = row.FirstCellNum; j < cellCount; j++)
        {
            if (null != row.GetCell(j) && !string.IsNullOrEmpty(row.GetCell(j).ToString()) && !string.IsNullOrWhiteSpace(row.GetCell(j).ToString()))
            {
                dataRow[j] = row.GetCell(j).ToString();
                isBlanKRow = false;
            }
        }
    }
    catch (Exception Ex)
     { 

     }
     if (!isBlanKRow)
     {
         dt.Rows.Add(dataRow);
     }

}

How to read Excel to Datatable using NPOI C#

1 Answers1