Read Excel file without headers ASP.Net

Question

I am trying to follow this code to read Excel files. Unfortunately, I cannot make it work, because I need to read Excel files without headers. How can I read an Excel files without headers?

public static DataTable ToDataTable(this ExcelPackage package)
    {
        ExcelWorksheet workSheet = package.Workbook.Worksheets.First();
        DataTable table = new DataTable();


        foreach (var firstRowCell in workSheet.Cells[1, 1, 1, workSheet.Dimension.End.Column])
        {
            table.Columns.Add(firstRowCell.Text);
        }

        for (var rowNumber = 2; rowNumber <= workSheet.Dimension.End.Row; rowNumber++)
        {
            var row = workSheet.Cells[rowNumber, 1, rowNumber, workSheet.Dimension.End.Column];
            var newRow = table.NewRow();
            foreach (var cell in row)
            {
                    newRow[cell.Start.Column-1] = cell.Text;
            }
            table.Rows.Add(newRow);  
        }
        return table;
    }

maybe try something like this one http://stackoverflow.com/questions/13396604/excel-to-datatable-using-epplus-excel-locked-for-editing with `bool hasHeader = false;` — Slai, Jan 30 '17 at 02:20

Ghasan غسان · Answer 1 · 2017-01-30T02:59:08.707

1

You can see in the for statement that it loops starting from the second row (skipping the header), what you need is to loop from the first row (as there is no header row) like this:

var rowNumber = 1

If your data is empty at the first row, then please add how many columns you are expecting to be there in the file:

// Notice I replaced workSheet.Dimension.End.Column with 6, because I am expecting 6 columns.
foreach (var firstRowCell in workSheet.Cells[1, 1, 1, 6])
{
    table.Columns.Add(firstRowCell.Text);
}

edited Jan 30 '17 at 02:59

answered Jan 30 '17 at 01:57

Ghasan غسان

5,577
4
33
44

Changed it to 1 but it gives me an error says the cannot find column 0. – Jujumancer Jan 30 '17 at 02:04
Line 30: newRow[cell.Start.Column - 1] = cell.Text; – Jujumancer Jan 30 '17 at 02:35
Basically the excel file's structure is the first row is empty. – Jujumancer Jan 30 '17 at 02:36

score 0 · Answer 2 · answered Jan 30 '17 at 08:30

0

I was able to make a work around with the problem. I automatically added columns. Changing to foreach to a for loop.

for (int x = 0; x <= workSheet.Dimension.End.Column; x++)
        {
            table.Columns.Add(x.ToString());
        }

answered Jan 30 '17 at 08:30

Jujumancer

125
1
12

Read Excel file without headers ASP.Net

2 Answers2