Take the 2-minute tour ×

I use Epplus to reading xlsx files from stream.

It has a bug , it cant read some columns in my workbook.How can read xlsx files from stream to datatable without epplus ?

my older code:

 public static DataSet ReadExcelFile(Stream stream)
            //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
            IExcelDataReader excelReader =    
            DataSet result = excelReader.AsDataSet();

            return result;

        catch (Exception x)
            throw x;

I didnt report it, but i tried so much combinations.If there are empty columns in worksheet ,epplus reader cant read correctly column values.

share|improve this question
add comment

1 Answer

up vote 9 down vote accepted

"It has a bug , it cant read some columns in my workbook"

Can you describe the bug, have you reported it or is it already known, what version are you using?

Here's a simple approach to load an excel file into a DataTable with EPPlus.

public static DataTable getDataTableFromExcel(string path)
    using (var pck = new OfficeOpenXml.ExcelPackage())
        using (var stream = File.OpenRead(path))
        var ws = pck.Workbook.Worksheets.First();  
        DataTable tbl = new DataTable();
        bool hasHeader = true; // adjust it accordingly( i've mentioned that this is a simple approach)
        foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
            tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
        var startRow = hasHeader ? 2 : 1;
        for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
            var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
            var row = tbl.NewRow();
            foreach (var cell in wsRow)
                row[cell.Start.Column - 1] = cell.Text;
        return tbl;
share|improve this answer
i got null object referance on var ws = pck.Workbook.Worksheets["Worksheet1"]; any idea ? –  Mennan Jun 28 '12 at 7:38
@Mennan: Try var ws = pck.Workbook.Worksheets.First(); instead (edited my answer accordingly). –  Tim Schmelter Jun 28 '12 at 7:48
I use Epplus version , and pck.Workbook.Worksheets has no First() method sorry , also i tried Worksheets[0] , still is null.I dont understand –  Mennan Jun 28 '12 at 7:55
@Mennan: Then you're not using .NET framework >= 3.5. Try var ws = pck.Workbook.Worksheets[1]; since index of ExcelWorksheets starts with 1. Can you open that excel file manually? –  Tim Schmelter Jun 28 '12 at 8:01
Thanks for this, helped a lot. I had some issues regarding empty columns though, had to remove them from my excel for this to work. –  Contra Sep 12 '12 at 11:29
show 3 more comments

Your Answer


