[Solved] Empty cells skipped (ignored) while reading data from Excel to DataTable in ClosedXML

Last Reply 8 months ago By shanssv

Posted 8 months ago

Hi,

I want to import a excel sheet into datatable in vb.net. I am using this example from here

Read and Import Excel data to DataTable using ClosedXml in ASP.Net with C# and VB.Net

The problem is when one of the column is empty (it has header but no values in rows) it shifts the other column to fill the empty column so the right most column(s) becomes empty.

Any help?

Thanks

Posted 8 months ago Modified on 8 months ago

adding (1, dt.Columns.Count) did the trick.

protected void ImportExcel(object sender, EventArgs e)
{
    //Save the uploaded Excel file.
    string filePath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
    FileUpload1.SaveAs(filePath);

    //Open the Excel file using ClosedXML.
    using (XLWorkbook workBook = new XLWorkbook(filePath))
    {
        //Read the first Sheet from Excel file.
        IXLWorksheet workSheet = workBook.Worksheet(1);

        //Create a new DataTable.
        DataTable dt = new DataTable();

        //Loop through the Worksheet rows.
        bool firstRow = true;
        foreach (IXLRow row in workSheet.Rows())
        {
            //Use the first row to add columns to DataTable.
            if (firstRow)
            {
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Columns.Add(cell.Value.ToString());
                }
                firstRow = false;
            }
            else
            {
                //Add rows to DataTable.
                dt.Rows.Add();
                int i = 0;
                foreach (IXLCell cell in row.Cells(1, dt.Columns.Count))
                {
                    dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                    i++;
                }
            }

            GridView1.DataSource = dt;
            GridView1.DataBind();
        }
    }
}

 

Protected Sub ImportExcel(sender As Object, e As EventArgs)
    'Save the uploaded Excel file.
    Dim filePath As String = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
    FileUpload1.SaveAs(filePath)

    'Open the Excel file using ClosedXML.
    Using workBook As New XLWorkbook(filePath)
        'Read the first Sheet from Excel file.
        Dim workSheet As IXLWorksheet = workBook.Worksheet(1)

        'Create a new DataTable.
        Dim dt As New DataTable()

        'Loop through the Worksheet rows.
        Dim firstRow As Boolean = True
        For Each row As IXLRow In workSheet.Rows()
            'Use the first row to add columns to DataTable.
            If firstRow Then
                For Each cell As IXLCell In row.Cells()
                    dt.Columns.Add(cell.Value.ToString())
                Next
                firstRow = False
            Else
                'Add rows to DataTable.
                dt.Rows.Add()
                Dim i As Integer = 0
                For Each cell As IXLCell In row.Cells(1, dt.Columns.Count)
                    dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString()
                    i += 1
                Next
            End If

            GridView1.DataSource = dt
            GridView1.DataBind()
        Next
    End Using
End Sub