What's the easiest way to create an Excel tabl

问题:

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

Software available:

• .NET 4 (C#)
• Excel 2010 (using the Excel API is OK)
• 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)
• 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.

What is the very simplest way to do this?

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

回答1:

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

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

2. Top of module add:

   using Microsoft.Office.Interop.Excel;
   

3. 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.

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:

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.

回答4:

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 will be able to convert it, but it will display a warning saying that the content does not match the extension.

回答5:

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

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.

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

回答6:

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

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
• give the data area (including the headers) a name (ie "MyData")

Implementing export

• copy template file to destination folder
• open an oledb-database connection to the destination file
• use sql to insert data

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"
            );
    }