How to read excel using EPPlus

Question

How to read excel using EPPlus

using .net 4.5

I'm trying to read .xls/.xlsx file using EPPlus (v4.0.4), but get an error. SO has questions on the same error but none relate or solve my problem.

protected void Page_Load(object sender, EventArgs e)
    {
        GetDataTableFromExcel(@"D:\test.xlsx");
    }

private 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[1];
     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;
  }
}

The error occurs at pck.Load(stream);

A disk error occurred during a write operation. (Exception from HRESULT: 0x8003001D (STG_E_WRITEFAULT)

c#

epplus

asked on Stack Overflow Oct 27, 2015 by

Qwerty

2 Answers

A simple example how you can use EPPlus to read excel file:

Reff : http://sforsuresh.in/reading-excel-file-using-epplus-package/

public void readXLS(string FilePath)
{
    FileInfo existingFile = new FileInfo(FilePath);
    using (ExcelPackage package = new ExcelPackage(existingFile))
    {
        //get the first worksheet in the workbook
        ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
        int colCount = worksheet.Dimension.End.Column;  //get Column Count
        int rowCount = worksheet.Dimension.End.Row;     //get row count
        for (int row = 1; row <= rowCount; row++)
        {
            for (int col = 1; col <= colCount; col++)
            {
                Console.WriteLine(" Row:" + row + " column:" + col + " Value:" + worksheet.Cells[row, col].Value.ToString().Trim());
            }
        }
    }
}

answered on Stack Overflow Aug 22, 2018 by

Suresh Kamrushi • edited Aug 23, 2018 by

Suresh Kamrushi

We can first convert the xls file to xlsx format using Microsoft.Office.Introp.excel, AFTER the conversion use new formatted file to read with EPPPLUS.

 public static DataTable ReadExcelFileToDataTable(string filePath, bool isFirstRowHeader = true)
        {
            #region  Convert xls file to xlsx file
           // Convert xls file to xlsx file --to use below code Microsoft.Excel must installed on the system on which cod eis running

                var app = new Microsoft.Office.Interop.Excel.Application();
                var web = app.Workbooks.Open("");
                web.SaveAs(filePath + ".x", FileFormat: Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook);
                web.Close();
                app.Quit();

            #endregion
            var newFileName = filePath + ".x";
            DataTable tbl = new DataTable(); ;

            Excel.ExcelPackage xlsPackage = new Excel.ExcelPackage(new FileInfo(newFileName));  //using Excel = OfficeOpenXml;    <--EPPLUS
            Excel.ExcelWorkbook workBook = xlsPackage.Workbook;

            try
            {
                Excel.ExcelWorksheet wsworkSheet = workBook.Worksheets[0];

                    foreach (var firstRowCell in wsworkSheet.Cells[1, 1, 1, wsworkSheet.Dimension.End.Column]) 
                    {
                        var colName = "";
                        colName = firstRowCell.Text;
                        tbl.Columns.Add(isFirstRowHeader ? colName : string.Format("Column {0}", firstRowCell.Start.Column)); 
                    }
                    var startRow = isFirstRowHeader ? 2 : 1;
                    for (int rowNum = startRow; rowNum <= wsworkSheet.Dimension.End.Row; rowNum++)
                    {
                        var wsRow = wsworkSheet.Cells[rowNum, 1, rowNum, wsworkSheet.Dimension.End.Column];
                        DataRow row = tbl.Rows.Add();
                        foreach (var cell in wsRow)
                        {
                            row[cell.Start.Column - 1] = cell.Text;
                        }
                    }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            return tbl;
        }

answered on Stack Overflow Nov 26, 2019 by

Sukhraj

User contributions licensed under CC BY-SA 3.0