什么是创建C＃Excel表格的最简单的方法？ [英] What's the easiest way to create an Excel table with C#?

问题描述

我有一些，我想变成一个Excel表格表格数据

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

软件可供选择：

• .NET 4（C＃）


• Excel 2010中（使用Excel API是OK）


• 我喜欢不使用任何第三方库

有关数据的信息：

• 几百万行


• 5列，所有的字符串（非常简单和普通表结构）


• 在我的剧本我M目前使用嵌套表的数据结构，但我可以改变


• 脚本的性能不是关键

• A couple million rows
• 5 columns, all strings (very simple and regular table structure)
• In my script I'm currently using a nested List data structure but I can change that
• Performance of the script is not critical

在线搜索提供了许多成果，我很困惑我是否应该使用OLEDB中，ADO记录集，或别的东西。其中一些技术似乎有点小题大做了我的情况，有的看起来像他们可能已经过时了。

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.

什么是做到这一点的最简单的方法是什么？

What is the very simplest way to do this?

修改：这是一个一次性的脚本，我打算从我参加的桌面运行

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

推荐答案

尊敬你的要求，以避免第三方的工具，并使用COM对象，这里就是我会做的。

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

1. 添加引用项目：COM对象
   的Microsoft Excel 11.0



2. 顶模块添加的：

1. Add reference to project: Com object Microsoft Excel 11.0.

2. 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();

}

这篇关于什么是创建C＃Excel表格的最简单的方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！