Excel to DataTable using EPPlus - excel locked for editing

Question

I'm using the following code to convert an Excel to a datatable using EPPlus:

public DataTable ExcelToDataTable(string path) {     var pck = new OfficeOpenXml.ExcelPackage();     pck.Load(File.OpenRead(path));     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);     }     pck.Dispose();     return tbl; } 

It creates the Excel, however, when I try to open it, it gives me the message that it is locked for editing by another user and that I can only open it in Read-Only mode.

I thought using:

pck.Dispose(); 

would solve the issue, however I'm still getting the same error.

Also, when I try to delete the file, I get the message: The action can't be completed because the file is open in WebDev.WebServer40.EXE.

Any ideas how to resolve this? Thanks in advance. :)

Answer 1

I see, that's what i've posted recently here(now corrected). It can be improved since the ExcelPackage and the FileStream(from File.OpenRead) are not disposed after using.

public static DataTable GetDataTableFromExcel(string path, bool hasHeader = true) {     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();         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 (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)         {             var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];             DataRow row = tbl.Rows.Add();             foreach (var cell in wsRow)             {                 row[cell.Start.Column - 1] = cell.Text;             }         }         return tbl;     } } 

Answer 2

A extension version of Tim Schmelter's answer.

public static DataTable ToDataTable(this ExcelWorksheet ws, bool hasHeaderRow = true) {     var tbl = new DataTable();     foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])         tbl.Columns.Add(hasHeaderRow ?             firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));     var startRow = hasHeaderRow ? 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; }