用c#创建Excel表最简单的方法是什么?

I have some tabular data that I'd like to turn into an Excel table.

我有一些表格数据，我想把它们变成Excel表格。

Software available:

软件可用:

.NET 4 (C#)
net 4(c#)
Excel 2010 (using the Excel API is OK)
Excel 2010(使用Excel API就可以了)
I prefer not to use any 3rd party libraries
我不喜欢使用任何第三方库。

Information about the data:

信息数据:

A couple million rows
几百万行
5 columns, all strings (very simple and regular table structure)
5列，所有字符串(非常简单和常规的表结构)
In my script I'm currently using a nested List data structure but I can change that
在我的脚本中，我正在使用一个嵌套的列表数据结构，但是我可以修改它
Performance of the script is not critical
脚本的性能并不重要。

Searching online gives many results, and I'm confused whether I should use OleDb, ADO RecordSets, or something else. Some of these technologies seem like overkill for my scenario, and some seem like they might be obsolete.

在网上搜索会得到很多结果，我不知道应该使用OleDb、ADO记录集还是其他东西。这些技术中的一些似乎对我的场景来说是多余的，有些看起来可能已经过时了。

What is the very simplest way to do this?

最简单的方法是什么?

Edit: this is a one-time script I intend to run from my attended desktop.

编辑:这是我打算从我的桌面运行的一次性脚本。

6 个解决方案

#1

Honoring your request to avoid 3rd party tools and using COM objects, here's how I'd do it.

尊重您的要求，避免第三方工具和使用COM对象，以下是我的做法。

Add reference to project: Com object Microsoft Excel 11.0.
添加对项目的引用:Com对象Microsoft Excel 11.0。
Top of module add:

模块的添加:
```
using Microsoft.Office.Interop.Excel;
```

Add event logic like this:

添加事件逻辑如下:

private void DoThatExcelThing()
{

    ApplicationClass myExcel;
    try
    {
        myExcel = GetObject(,"Excel.Application")
    }
    catch (Exception ex)
    {
        myExcel = New ApplicationClass()
    }

    myExcel.Visible = true;
    Workbook wb1 = myExcel.Workbooks.Add("");
    Worksheet ws1 = (Worksheet)wb1.Worksheets[1];

    //Read the connection string from App.Config
    string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["NewConnString"].ConnectionString;

    //Open a connection to the database
    SqlConnection myConn = new SqlConnection();
    myConn.ConnectionString = strConn;
    myConn.Open();

    //Establish the query
    SqlCommand myCmd = new SqlCommand("select * from employees", myConn);
    SqlDataReader myRdr = myCmd.ExecuteReader();

    //Read the data and put into the spreadsheet.
    int j = 3;
    while (myRdr.Read())
    {
        for (int i=0 ; i < myRdr.FieldCount; i++)
        {
            ws1.Cells[j, i+1] = myRdr[i].ToString();
        }
        j++;
    }

    //Populate the column names
    for (int i = 0; i < myRdr.FieldCount ; i++)
    {
        ws1.Cells[2, i+1] = myRdr.GetName(i);
    }
    myRdr.Close();
    myConn.Close();

    //Add some formatting
    Range rng1 = ws1.get_Range("A1", "H1");
    rng1.Font.Bold = true;
    rng1.Font.ColorIndex = 3;
    rng1.HorizontalAlignment = XlHAlign.xlHAlignCenter;

    Range rng2 = ws1.get_Range("A2", "H50");
    rng2.WrapText = false;
    rng2.EntireColumn.AutoFit();

    //Add a header row
    ws1.get_Range("A1", "H1").EntireRow.Insert(XlInsertShiftDirection.xlShiftDown, Missing.Value);
    ws1.Cells[1, 1] = "Employee Contact List";
    Range rng3 = ws1.get_Range("A1", "H1");
    rng3.Merge(Missing.Value);
    rng3.Font.Size = 16;
    rng3.Font.ColorIndex = 3;
    rng3.Font.Underline = true;
    rng3.Font.Bold = true;
    rng3.VerticalAlignment = XlVAlign.xlVAlignCenter;

    //Save and close
    string strFileName = String.Format("Employees{0}.xlsx", DateTime.Now.ToString("HHmmss"));
    System.IO.File.Delete(strFileName);
    wb1.SaveAs(strFileName, XlFileFormat.xlWorkbookDefault, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
        XlSaveAsAccessMode.xlExclusive, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value);
    myExcel.Quit();

}

#2

Avoid using COM interop at all costs. Use a third-party API. Really. In fact, if you're doing this server-side, you virtually have to. There are plenty of free options. I highly recommend using EPPlus, but there are also enterprise-level solutions available. I've used EPPlus a fair amount, and it works great. Unlike interop, it allows you to generate Excel files without requiring Excel to be installed on the machine, which means you also don't have to worry about COM objects sticking around as background processes. Even with proper object disposal, the Excel processes don't always end.

尽量避免使用COM互操作。使用第三方API。真的。事实上，如果您在服务器端执行此操作，您实际上必须这样做。有很多免费的选择。我强烈推荐使用EPPlus，但也有企业级的解决方案。我已经使用了相当数量的EPPlus，它工作得很好。与interop不同，它允许您生成Excel文件，而不需要在机器上安装Excel，这意味着您也不必担心COM对象作为后台进程存在。即使有了适当的对象处理，Excel过程也不会总是结束。

http://epplus.codeplex.com/releases/view/42439

I know you said you want to avoid third-party libraries, but they really are the way to go. Microsoft does not recommend automating Office. It's really not meant to be automated anyway.

我知道你说过要避免使用第三方库，但它们确实是可行的。微软不推荐自动办公软件。它真的不应该是自动化的。

http://support.microsoft.com/kb/257757

However, you may want to reconsider inserting "a couple million rows" into a single spreadsheet.

但是，您可能需要重新考虑将“几百万行”插入到一个电子表格中。

#3

I once read that the easiest way to create an Excel table was to actualy write a HTML table, including its structure and data, and simply name the file .xls.

我曾经读到过，创建Excel表的最简单方法是编写一个HTML表，包括它的结构和数据，并简单地命名文件.xls。

Excel will be able to convert it, but it will display a warning saying that the content does not match the extension.

Excel将能够转换它，但是它会显示一个警告，说内容与扩展不匹配。

#4

Some things for your consideration...

有些事情需要你考虑……

If this is a client side solution, there is nothing wrong with using Interops. If this is a server side solution, Don't use Interops. Good alternative is OpenXML SDK from Microsoft if you don't want 3rd party solution. It's free. I believe the latest one has similar object model that Excel has. It's a lot faster, A LOT, in generating the workbook vs going the interops way which can bog down your server.

如果这是客户端解决方案，那么使用interop没有什么错。如果这是一个服务器端解决方案，不要使用Interops。如果不想要第三方解决方案，最好的选择是微软的OpenXML SDK。这是免费的。我相信最新的一个有类似于Excel的对象模型。它的速度要快得多，在生成工作簿vs进行互操作时，会使服务器崩溃。

#5

I agree that a 3rd party dll would be cleaner than the com, but if you go the interop route...

我同意一个第三方dll比com更干净，但是如果你走的是interop路线…

Hands down the best way to populate an excel sheet is to first put the data in a 2 dimensional string array, then get an excel range object with the same dimensions and set it (range.set_value2(oarray) I think). Using any other method is hideously slow.

填充excel表的最佳方法是首先将数据放在一个二维字符串数组中，然后获得一个具有相同维度的excel range对象并设置它(range.set_value2(oarray))。使用任何其他方法都是极其缓慢的。

Also be sure you use the appropriate cleanup code in your finally block.

还要确保在finally块中使用了适当的清理代码。

#6

i implemented "export to Excel" with the ms-access-ole-db-driver that can also read and write excel files the follwoing way:

我使用ms-access-ole-db驱动程序实现了“导出到Excel”，该驱动程序也可以通过以下方式读写Excel文件:

preparation (done once)

准备(完成一次)

create an excel file that contains all (header, Formatting, formulas, diagrams) with an empty data area as a template to be filled
创建一个excel文件，其中包含所有(标题、格式、公式、图表)，其中一个空数据区域作为要填充的模板
give the data area (including the headers) a name (ie "MyData")
给数据区域(包括头)一个名称(即“MyData”)

Implementing export

实现出口

copy template file to destination folder
将模板文件复制到目标文件夹
open an oledb-database connection to the destination file
打开到目标文件的oledb-database连接
use sql to insert data
使用sql插入数据

Example

例子

Excel table with Named area "MyData"
Name, FamilyName, Birthday

open System.Data.OleDb.OleDbConnection
execute sql "Insert into MyData(Name, FamilyName, Birthday) values(...)"

I used this connection string

我使用了这个连接字符串

private const string FORMAT_EXCEL_CONNECT =
        // @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR={1}""";
        @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR={1}""";


    private static string GetExcelConnectionString(string excelFilePath, bool header)
    {
        return string.Format(FORMAT_EXCEL_CONNECT,
            excelFilePath,
            (header) ? "Yes" : "No"
            );
    }

#1