使用 ClosedXML 将 Excel 工作表读入 DataTable [英] Read Excel worksheet into DataTable using ClosedXML

问题描述

我想将 Excel 工作表的内容读入 C# 数据表.Excel 工作表可以具有可变数量的列和行.Excel 工作表中的第一行将始终包含列名称，但其他行可能为空.

I would like to read the contents of an Excel worksheet into a C# DataTable. The Excel worksheet could have a variable numbers of columns and rows. The first row in the Excel worksheet will always contain the column names but other rows may be blank.

我在 SO 中看到的所有建议都假定存在 Microsoft.ACE.OLEDB.我的系统上没有安装这个库，因为当我尝试其中一些解决方案时，我收到了这个错误.

All of the suggestions I have seen here in SO all assume the presence of Microsoft.ACE.OLEDB. I do not have this library installed on my system as when I try some of these solutions I get this error.

Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

奇怪的是我安装了 Office 2016.

Strange considering I have Office 2016 installed.

出于这个原因，我希望通过 Nuget 使用 ClosedXML 库，但我在他们的 wiki 中没有看到任何将 Excel 工作表读取到 C# 中的 DataTable 的示例.

For this reason I was hoping to use the ClosedXML library via Nuget but I do not see any examples in their wiki of reading an Excel worksheet to a DataTable in C#.

推荐答案

这不是我的例子.我不记得我从哪里得到它，因为它在我的档案中.但是，这对我有用.我遇到的唯一问题是空白单元格.根据 ClosedXML GitHUb wiki 页面上的讨论，它与 Excel 不跟踪有关不受数据限制的空单元格.我发现，如果我将数据添加到单元格，然后删除相同的数据，该过程会起作用.

This is example is not mine. I cannot remember where I got it from as it was in my archives. However, this works for me. The only issue I ran into was with blank cells. According to a dicussion on the ClosedXML GitHUb wiki page it has something to do with Excel not tracking empty cells that are not bounded by data. I found that if I added data to the cells and then removed the same data the process worked.

public static DataTable ImportExceltoDatatable(string filePath, string sheetName)
{
  // Open the Excel file using ClosedXML.
  // Keep in mind the Excel file cannot be open when trying to read it
  using (XLWorkbook workBook = new XLWorkbook(filePath))
  {
    //Read the first Sheet from Excel file.
    IXLWorksheet workSheet = workBook.Worksheet(1);

    //Create a new DataTable.
    DataTable dt = new DataTable();

    //Loop through the Worksheet rows.
    bool firstRow = true;
    foreach (IXLRow row in workSheet.Rows())
    {
      //Use the first row to add columns to DataTable.
      if (firstRow)
      {
        foreach (IXLCell cell in row.Cells())
        {
          dt.Columns.Add(cell.Value.ToString());
        }
        firstRow = false;
      }
      else
      {
        //Add rows to DataTable.
        dt.Rows.Add();
        int i = 0;

        foreach (IXLCell cell in row.Cells(row.FirstCellUsed().Address.ColumnNumber, row.LastCellUsed().Address.ColumnNumber))
        {
          dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
          i++;
        }
      }
    }

    return dt;
  }
}

需要添加

using System.Data;
using ClosedXML.Excel;

以及 ClosedXML nuget 包

As well as the ClosedXML nuget package

对于其他日期时间数据类型...这可能会有所帮助... 参考

For other datetime data type... this could be helpful... reference

if (cell.Address.ColumnLetter=="J") // Column with date datatype
 {
    DateTime dtime = DateTime.FromOADate(double.Parse(cell.Value.ToString()));
                     dt.Rows[dt.Rows.Count - 1][i] = dtime;
 }
 else
 {
      dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
 }

这篇关于使用 ClosedXML 将 Excel 工作表读入 DataTable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！