在服务器上处理 Excel 文档

引言

通常，Excel 电子表格被用于在应用程序之间传输数据。 CSV 文件也被用于相同的目的。

有很多方法可以处理 Excel 电子表格和 CSV 文件。

使用 OLEDB 自动化读取和写入 Excel 文档

您需要添加对位于 C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel 或类似文件夹中的 Microsoft.Office.Interop.Excel 库的引用。

还需要使用 Excel = Microsoft.Office.Interop.Excel; 语句。

以下步骤可用于从 dataset 创建 Excel 电子表格

1. 创建 Excel 对象

   Application excel = new Excel.Application();
   Workbook workBook = excel.Workbooks.Add();
   Worksheet sheet = workBook.ActiveSheet;

2. 创建标题行

   foreach (DataColumn dc in ds.Tables[0].Columns)
   {
       sheet.Cells[1, c + 1] = dc.Caption;
       c++;
   }

3. 从 dataset 加载数据

   foreach (DataRow dr in ds.Tables[0].Rows)
   {
       foreach (DataColumn dc in ds.Tables[0].Columns)
       {
           sheet.Cells[r + 1, c + 1] = dr[dc].ToString();
           c++;
       }
       r++;
       c=0;
   }

4. 以所需的格式保存结果

   workBook.SaveAs(OutputFileName, XlFileFormat.xlExcel8); 
   workbook.Close();

应用程序可以读取和写入最新格式的 Excel 文档，或者使用 2003 年或更早版本的电子表格格式，可以处理 CSV 或制表符分隔的文档。

但是，不建议使用此方法。它速度慢、占用资源多且不可靠。 必须在服务器上安装 Microsoft Excel。 开发人员应该预料到很多意外情况。

Jet OLEDB 提供程序

操作电子表格和文本文件的另一种方法是使用 JET OLEDB 提供程序。

创建 Excel 文件的步骤是

1. 创建连接 string

   excelConStr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;
   Data Source={0};Extended Properties=""Excel 8.0;""", OutputFileName);

2. 创建和填充电子表格

   using (OleDbConnection conn = new OleDbConnection(excelConStr))
   {
        conn.Open();
        oledbcmd = new OleDbCommand();
        oledbcmd.Connection = conn;
   
        string ssql = "CREATE TABLE [table1] (";
   
        foreach (DataColumn dc in ds.Tables[0].Columns)
           ssql += "" + dc.Caption + " VARCHAR, ";
       
        ssql += ")";
        ssql = ssql.Replace(", )", ")");
   
        oledbcmd.CommandText = ssql;
        oledbcmd.ExecuteNonQuery();
   
        foreach (DataRow dr in ds.Tables[0].Rows)
        {
            ssql = "INSERT INTO [table1] VALUES(";
            foreach(DataColumn dc in ds.Tables[0].Columns)
                ssql += "'" + dr[dc].ToString() + "',";
   
            ssql += ")";
            ssql = ssql.Replace(",)", ")");
   
            oledbcmd.CommandText = ssql;
            oledbcmd.ExecuteNonQuery()
        }
        conn.Close()
   }

此代码说明了如何从数据集创建 Excel 电子表格。 您将获得 Excel 2003 格式。 可以通过更新连接字符串的扩展属性来创建不同的格式。

Microsoft Access 数据库引擎可再发行组件

这不是服务器端应用程序中 Jet OLEDB 提供程序的替代品，但它有很多不错的功能。 例如，您可以使用以下步骤使用 SQL 语句读取 CSV 文件

1. 创建连接字符串

   string CSVConStr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;
   Data Source={0};Extended Properties=""Text;HDR=YES;IMEX=2;FMT=Delimited""", 
   System.IO.Path.GetDirectoryName(FileName));

   CSV 文件的第一行必须有一个标题。

2. 从 CSV 文件创建 dataset

   using (excelCon = new OleDbConnection(CSVConStr))
   {
       excelCon.Open();   
       SQL = "SELECT * FROM [" + System.IO.Path.GetFileName(FileName) + "]";
       cmd = new OleDbCommand(SQL, excelCon);
       ds = new DataSet();
       da = new OleDbDataAdapter(cmd);
       da.Fill(ds, "MyData");
   }

Microsoft Access Database Engine 2010 Redistributable (https://www.microsoft.com/en-us/download/details.aspx?id=13255) 应安装在主机上。 服务器上不需要安装 Excel。

ClosedXML 库

下面的示例显示了如何从 dataset 创建 Excel 2007/2010 电子表格

1. 添加对 ClosedXML 库的引用
2. 添加 using ClosedXML.Excel
3. 添加实际代码

   var wb = new XLWorkbook();
   wb.Worksheets.Add(ds);
   wb.SaveAs(OutputFileName);

SDK 和文档可在此处获得：here。

此 SDK 运行速度非常快，并提供了一种很好的面向对象的方式来操作文件，并且服务器上不需要安装 Excel。

您可以考虑的另一个强大的工具是 EPPlus (https://epplus.codeplex.com/)。