如何使用EPPlus读取Excel

Question

使用 .net 4.5

我正在尝试使用 EPPlus (v4.0.4) 读取 .xls/.xlsx 文件，但是遇到了错误。虽然SO上有关于这个错误的问题，但是没有一个与我的问题相关或者解决我的问题。

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;
  }
}

错误发生在pck.Load(stream);。

写操作期间发生磁盘错误。(HRESULT: 0x8003001D (STG_E_WRITEFAULT) 异常)

- Qwerty

---

我认为使用EPPlus读取xlsx文件有更简单的方法。看这里 - Rob

你试图读取xlsx文件，我说得对吗？ - Rob

是的，你说得对。我也尝试使用 .xls 格式，但出现了相同的错误。 - sukesh

test.xlsx文件长什么样？ - Rob

你的代码看起来没问题。确保它是真正的 xlsx 格式，而不是一个重命名为 'xls' 或 'csv' 的文件。如果你无法弄清楚，请将文件上传到某个地方。 - Ernie S

Answer 1

一个使用EPPlus读取Excel文件的简单示例：

参考：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());
            }
        }
    }
}

- Suresh Kamrushi

---

使用上述代码无法读取xls文件，访问worksheets[1]时会出现索引超出范围的异常。 - Atif Aziz

@AtifAziz /ALL - 我将 package.Workbook.worksheet[1]; 改为 package.Workbook.worksheet[0];，这样就可以正常工作了。（由于编辑队列已满，我无法进行编辑。） - Danimal111

Answer 2

我们可以使用Microsoft.Office.Introp.excel将xls文件转换为xlsx格式，转换完成后使用新格式文件与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;
        }

- Sukhraj

Answer 3

使用此代码，您不会因为空单元格而出现错误。它还将根据类中的属性转换数据类型！

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Reflection;
using OfficeOpenXml;

public static class ReadExcel
    {
        public static List<T> ReadExcelToList<T>(this ExcelWorksheet worksheet) where T : new()
        {
            List<T> collection = new List<T>();
            try
            {
                DataTable dt = new DataTable();
                foreach (var firstRowCell in new T().GetType().GetProperties().ToList())
                {
                    //Add table colums with properties of T
                    dt.Columns.Add(firstRowCell.Name);
                }
                for (int rowNum = 2; rowNum <= worksheet.Dimension.End.Row; rowNum++)
                {
                    var wsRow = worksheet.Cells[rowNum, 1, rowNum, worksheet.Dimension.End.Column];
                    DataRow row = dt.Rows.Add();
                    foreach (var cell in wsRow)
                    {
                        row[cell.Start.Column - 1] = cell.Text;
                    }
                }
                
                //Get the colums of table
                var columnNames = dt.Columns.Cast<DataColumn>().Select(c => c.ColumnName).ToList();
                
                //Get the properties of T
                List<PropertyInfo> properties = new T().GetType().GetProperties().ToList();

                collection = dt.AsEnumerable().Select(row =>
                {
                    T item = Activator.CreateInstance<T>();
                    foreach (var pro in properties)
                    {
                        if (columnNames.Contains(pro.Name) || columnNames.Contains(pro.Name.ToUpper()))
                        {
                            PropertyInfo pI = item.GetType().GetProperty(pro.Name);
                            pro.SetValue(item, (row[pro.Name] == DBNull.Value) ? null : Convert.ChangeType(row[pro.Name], (Nullable.GetUnderlyingType(pI.PropertyType) == null) ? pI.PropertyType : Type.GetType(pI.PropertyType.GenericTypeArguments[0].FullName)));
                        }
                    }
                    return item;
                }).ToList();

            }
            catch (Exception ex)
            {
                //Save error log
            }

            return collection;
        }
    }

如何调用此函数？请查看下面的代码。

public List<Users> GetStudentsFromExcel(HttpPostedFileBase file)
{
    List<Users> list = new List<Users>();
    if (file != null)
    {
        try
        {
            using (ExcelPackage package = new ExcelPackage(file.InputStream))
            {
                ExcelWorkbook workbook = package.Workbook;
                if (workbook != null)
                {
                    ExcelWorksheet worksheet = workbook.Worksheets.FirstOrDefault();
                    if (worksheet != null)
                    {
                        list = worksheet.ReadExcelToList<Users>();
                        //Your code
                    }
                }
            }
        }
        catch (Exception ex)
        {
            //Save error log
        }
    }
    return list;
}

public class Users
{
    public string Code { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    public string Address { get; set; }
    public DateTime CreatedAt { get; set; }
}

希望能帮助到某些人！

- Cong Bang