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

问题描述

我想将Excel工作表的内容读入C#DataTable.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.

我在本文中看到的所有建议都假定存在 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#数据表的示例.

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屋！