Click here to Skip to main content
15,902,299 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
C#
public DataTable getDataTableFromExcel(string path)
{   
    using (var pck = new OfficeOpenXml.ExcelPackage())
    {
        using (var stream = File.OpenRead(path))
        {
            pck.Load(stream);
        }
        var ws = pck.Workbook.Worksheets.First();
        DataTable tbl = new DataTable();
        bool hasHeader = true; 
        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;
            }
            tbl.Rows.Add(row);
        }
       
        return tbl;
    }
}
Posted
Updated 21-May-15 1:31am
v2
Comments
CHill60 21-May-15 7:31am    
What error?
What is your question?
Member 11458121 21-May-15 7:38am    
Can not open the package. Package is an OLE compound document. If this is an encrypted package, please supply the password
Member 11458121 21-May-15 7:42am    
The following code is working fine for .xlsx, but it's not working for .xls.
Mehdi Gholam 21-May-15 7:50am    
EPPlus does not support the old .xls files.
frikrishna 17-Oct-16 13:37pm    
I am facing same problem, is there a way to deal with this, can we change xls to xlsx within the code, how did you deal with this, using NPOI as solution suggested seems to be long process

1 solution

As mentioned in the comments, EPPlus does not support .xls files. It only supports the newer .xlsx files.

To open an old .xls file, you'll need to use a different library - for example, NPOI[^].
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900