我有一些表格数据,我想变成一个Excel表.
可用软件：
> .NET 4(C#)
> Excel 2010(使用Excel API可以)
>我不喜欢使用任何第三方图书馆
有关数据的信息：
>几百万行
> 5列,所有字符串(非常简单和规则的表结构)
>在我的脚本中,我正在使用一个嵌套的List数据结构,但是我可以改变它
>脚本的性能并不重要
在线搜索会给出很多结果,我是否会使用OleDb,ADO Recordset或其他内容感到困惑.这些技术中的一些似乎对我的场景似乎是过度的,有些似乎可能已经过时了.
最简单的方法是什么？
编辑：这是一个一次性脚本,我打算从我的桌面上运行.
解决方法
尊重您的要求,以避免第三方工具和使用COM对象,我将如何做.
>添加对项目的引用：Com对象
Microsoft Excel 11.0.
>模块顶部添加：
using Microsoft.Office.Interop.Excel;
>添加如下事件逻辑：
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,XlSaveAsAccessMode.xlExclusive,false,Missing.Value);
myExcel.Quit();
}
