C#写Excel，大量数据

Question

一个单机的软件，对大量数据进行处理，数据量一般是几万。保存在datatable中，在datagridview中显示。现在需要把这些数据导出，目前是一格一格的输出，但是速度太慢了。请问那个大侠有个快一点的方法？我的开发环境是vs2008，office是2007，动态链接库引入的是版本12，不是11，也不是9
说明：数据量是不定的，行数固定（900多行），但是列数不固定，可能是两列，也可能是200列，但是不会超过Excel要求的256列。

Answer 1

datagridview有用流模式导出为excel的方法，可以查一下资料

Answer 2

回3#，之前用流导出的是快，但是不是标准的excel文件。
回5#，跟数据库有啥关系？我想的是一次写一个格会很慢，但是一次写一行我就不会了。网上查了很多资料，据说可以用块写入，但是要定义块的位置。因为我的行数不固定，我不知道那个块的位置该怎么定义。
我现在用的是多线程，总不能一保存就等他半分钟什么都不干吧。这个谁也受不了啊~

Answer 3

aspose.cells组件，不需要excel，直接写文件，速度很快

Answer 4

我突然想起了。在后台拼个Table HTML，然后保存为xls文件。

Answer 5

//从楼主的描述来看，数据的来源应该是数据库吧？不可能手录吧？
//如果是，请下看
//不用以个个加，也不用做XML（因为是2007，格式更加复杂了），ms-sql 2000为例。
cn.ConnectionString   =   "Provider=SQLOLEDB.1;Persist   Security   Info=False;User   ID=jtaf;Password=登陆密码;Initial Catalog=sql里的数据库;Data   Source=sql服务器别名或IP"   
cn.CursorLocation = adUseClient；  
cn.Open；    
cn.Execute("select * into OpenRowSet('microsoft.jet.oledb.4.0','Excel   8.0;hdr=yes;database=c:\Test.xls;','select * from [Sheet1$]') from table1")；
//excel其实也是个完美的数据库。。

Answer 6

不是数据库啊~就是Excel表中的数据。而且我现在是要导出，导入数据已经实现了，而且也已经装到Datatable中了，而且在datagridview中可以显示了。现在关键是怎么把它快点写回去Excel。

Answer 7

OLEDB不行么，应该是除了IO流之外 最快的吧
        /// <summary>
/// 输出Excel文件
/// </summary>
/// <param name="location">输出文件的位置</param>
/// <param name="fileName">输出文件的表的名称</param>
/// <param name="exportTable"></param>
private void ExportExcelFile(string location,string tableName,DataTable exportTable)
{
string OLEDBConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ location+";";   
OLEDBConnStr +=   " Extended Properties=Excel 8.0;";
string strCreateTableSQL = @" CREATE TABLE ";
strCreateTableSQL += tableName;//@" 统计表 ";
strCreateTableSQL += @" ( ";
for(int i= 0;i<exportTable.Columns.Count-1;i++)
{
strCreateTableSQL +=" "+ exportTable.Columns[i].Caption+@" VARCHAR,";
}
strCreateTableSQL +=" " + exportTable.Columns[exportTable.Columns.Count-1].Caption+@" VARCHAR";
/*
strCreateTableSQL += @" 单号 VARCHAR, ";
strCreateTableSQL += @" 备注 VARCHAR  ";*/
strCreateTableSQL += @" ) "; OleDbConnection oConn = new OleDbConnection(); 
   
oConn.ConnectionString = OLEDBConnStr; 
OleDbCommand oCreateComm = new OleDbCommand();
oCreateComm.Connection = oConn;
oCreateComm.CommandText = strCreateTableSQL; oConn.Open(); 
oCreateComm.ExecuteNonQuery();
for(int i=0;i<exportTable.Rows.Count;i++)
{
oCreateComm.CommandText = "INSERT INTO ["+tableName+"$] (";//统计表$] (";

for(int j=0;j<exportTable.Columns.Count-1;j++)
{
oCreateComm.CommandText += exportTable.Columns[j].Caption+ ",";
}
oCreateComm.CommandText += exportTable.Columns[exportTable.Columns.Count-1].Caption +@") VALUES ('";
for(int j=0;j<exportTable.Columns.Count-1;j++)
{
oCreateComm.CommandText += exportTable.Rows[i][j].ToString()+"','";
}
oCreateComm.CommandText += exportTable.Rows[i][exportTable.Columns.Count-1].ToString()+ "')";

oCreateComm.ExecuteNonQuery();
}
oConn.Close(); }

Answer 8

location是应该连文件名都有了的吧？然后那个tablename应该是表名吧？还是Excel文件的名字啊？
然后在“oCreateComm.ExecuteNonQuery();”出错了，字段定义语法错误。不明白为什么

Answer 9

location 存储的文件名tablename 工作表名Sheet1什么的可能有些类型的字段，这个输出函数不支持吧。

Answer 10

这个不就更简单么？在datagridview，单元格获得焦点时候保存值，失去焦点的时候就将改变的值和保存的比较下，如果不同就，在特定的数组中加上，这样执行更改直接改那个索引的单元格即可。
不要从头刷啊

Answer 11

你可以关注一下 openXML 技术
我做过 word 相关的东西，也是因为生成速度巨慢所以使用 openXML 
Excel 没有生成过，应该都差不多吧
---
只有 office 2007 支持这个技术

Answer 12

告诉你个方法吧，一般人我不说哦。
写成 .csv 格式，好处不用我给你说哦。
即使必须 xls 格式，调用excel打开后，另存为 xls 就 ok

Answer 13

期待中我也想要Excel导入到数据库中的方法。。

Answer 14

可能是我没有描述清楚吧。不能改写原始的数据文件的。要新建文件保存的...所以不能用DataGridView直接保存。

Answer 15

呵呵，导出EXCEL，使用xml方式，最快，我试过。
完整代码如下：
 // /////////////////////////////////////////////////////////////
        // File: CExcelManage.cs Class: ExportToExcel.CExcelManage
        // Date: 2/25/2010 Author: Shelwin Nee
        // Language: C# Framework: .NET2.0
        //
        // Corporation:MESNAC
        // /////////////////////////////////////////////////////////////        /// <summary>
        /// 导出datatable到excel中
        /// </summary>
        /// <param name="dt">需要导出的表</param>
        /// <returns></returns>
 public static bool DataTableToExcel(DataTable dt)
        {
            bool res = true;
            if (dt == null)
            {
                return false;
            }
            SaveFileDialog dialog1 = new SaveFileDialog();
            dialog1.AddExtension = true;
            dialog1.CheckPathExists = true;
            dialog1.Filter = "Excel Workbooks (*.xls) | *.xls";
            dialog1.OverwritePrompt = true;
            dialog1.Title = "保存Excel格式文件";
            string filePath = "";
            int numTemp = 0;
            int numRow = 0;
            int numCol = 0;
             if (dialog1.ShowDialog() != DialogResult.OK)
            {
                return false;
            }
            numTemp = 0;
            numRow = dt.Rows.Count + 1;
            numCol = dt.Columns.Count;
            numTemp = 0;
            filePath = dialog1.FileName;
            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }
            StreamWriter writer1 = new StreamWriter(filePath, false);
            StreamWriter writer2 = writer1;
            try
            {
                writer2.WriteLine("<?xml version=\"1.0\"?>");
                writer2.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
                writer2.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
                writer2.WriteLine(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
                writer2.WriteLine(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
                writer2.WriteLine(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
                writer2.WriteLine(" xmlns:html=\"http://www.w3.org/TR/REC-html40\">");
                writer2.WriteLine(" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
                writer2.WriteLine("  <Author>SHELWIN NEE</Author>");
                writer2.WriteLine(string.Format("  <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));
                writer2.WriteLine("  <Company>MESNAC</Company>");
                writer2.WriteLine("  <Version>11.6408</Version>");
                writer2.WriteLine(" </DocumentProperties>");
                writer2.WriteLine(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
                writer2.WriteLine("  <WindowHeight>8955</WindowHeight>");
                writer2.WriteLine("  <WindowWidth>11355</WindowWidth>");
                writer2.WriteLine("  <WindowTopX>480</WindowTopX>");
                writer2.WriteLine("  <WindowTopY>15</WindowTopY>");
                writer2.WriteLine("  <ProtectStructure>False</ProtectStructure>");
                writer2.WriteLine("  <ProtectWindows>False</ProtectWindows>");
                writer2.WriteLine(" </ExcelWorkbook>");
                writer2.WriteLine(" <Styles>");
                writer2.WriteLine("  <Style ss:ID=\"Default\" ss:Name=\"Normal\">");
                writer2.WriteLine("   <Alignment ss:Vertical=\"Bottom\"/>");
                writer2.WriteLine("   <Borders/>");
                writer2.WriteLine("   <Font/>");
                writer2.WriteLine("   <Interior/>");
                writer2.WriteLine("   <Protection/>");
                writer2.WriteLine("  </Style>");
                writer2.WriteLine("  <Style ss:ID=\"s21\">");
                writer2.WriteLine("   <Alignment ss:Vertical=\"Bottom\" ss:WrapText=\"1\"/>");
                writer2.WriteLine("  </Style>");
                writer2.WriteLine(" </Styles>");
                writer2.WriteLine(" <Worksheet ss:Name=\"XRay Data Report\">");
                writer2.WriteLine(string.Format("  <Table ss:ExpandedColumnCount=\"{0}\" ss:ExpandedRowCount=\"{1}\" x:FullColumns=\"1\"", numCol.ToString(), numRow.ToString()));
                writer2.WriteLine("   x:FullRows=\"1\">");                //确定列的格式
                for (numTemp = 0; numTemp != numCol - 1; numTemp++)
                {
                    writer2.WriteLine("<Column ss:AutoFitWidth=\"1\"/>");
                }
                //导出标题
                writer2.WriteLine("<Row>");
                for (numTemp = 0; numTemp != numCol; numTemp++)
                {
                    writer2.Write("<Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">");
                    writer2.Write(dt.Columns[numTemp].Caption);
                    writer2.WriteLine("</Data></Cell>");
                }
                writer2.WriteLine("</Row>");
                //导出数据
                foreach (DataRow row1 in dt.Rows)
                {
                    writer2.WriteLine("<Row>");
                    for (numTemp = 0; numTemp != numCol; numTemp++)
                    {
                        writer2.Write("<Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">");
                        writer2.Write(row1[numTemp].ToString());
                        writer2.WriteLine("</Data></Cell>");
                    }
                    writer2.WriteLine("</Row>");
                }
                writer2.WriteLine("  </Table>");
                writer2.WriteLine("  <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
                writer2.WriteLine("   <Selected/>");
                writer2.WriteLine("   <Panes>");
                writer2.WriteLine("    <Pane>");
                writer2.WriteLine("     <Number>3</Number>");
                writer2.WriteLine("     <ActiveRow>1</ActiveRow>");
                writer2.WriteLine("    </Pane>");
                writer2.WriteLine("   </Panes>");
                writer2.WriteLine("   <ProtectObjects>False</ProtectObjects>");
                writer2.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");
                writer2.WriteLine("  </WorksheetOptions>");
                writer2.WriteLine(" </Worksheet>");
                writer2.WriteLine(" <Worksheet ss:Name=\"Sheet2\">");
                writer2.WriteLine("  <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
                writer2.WriteLine("   <ProtectObjects>False</ProtectObjects>");
                writer2.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");
                writer2.WriteLine("  </WorksheetOptions>");
                writer2.WriteLine(" </Worksheet>");
                writer2.WriteLine(" <Worksheet ss:Name=\"Sheet3\">");
                writer2.WriteLine("  <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
                writer2.WriteLine("   <ProtectObjects>False</ProtectObjects>");
                writer2.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");
                writer2.WriteLine("  </WorksheetOptions>");
                writer2.WriteLine(" </Worksheet>");
                writer2.WriteLine("</Workbook>");
                writer2 = null;
                writer1.Close();
            }
            catch
            {
                res = false;
            }
            finally
            {
                if (writer2 != null)
                {
                    writer2 = null;
                }
                if (writer1 != null)
                {
                    writer1.Close();
                    writer1 = null;
                }
            }
            return res;
        }

Answer 16

http://download.csdn.net/source/2241999里面方法比较多,建议去看下

Answer 17

用OLE比较好，就是把Excel文件当作数据库来操作，lz可以搜下。

Answer 18

不用一个格一个格的写，得到一片区域的range对象，然后把datetable数据转化为Object[,]，让后让range.value=那个二维数组就可以一次填充一片数据了！比如
Worksheet.get_Range(Worksheet.Cells[1,1],Worksheet.Cells[3,3]).Value2=new object[3,3]{};就可以了，具体的Excel版本和类库可能略有不同，稍微改改就可以了

Answer 19

同理，读取大量的单元格也用着个套路，只不过从Excel读出的object二维数组的索引都是从1开始的

Answer 20

可以描述的详细点么？如何把datatable的数据转化为object[,]啊？
如果能给出具体的示例代码就更感激了~

Answer 21

object[,] datas=new object[datatable.Rows.Count,datatable.Columns.Count];
for(int r=0;r<datatable.Rows.Count;r++)
{
for(int c=0;c<datatable.Columns.Count;c++)
{
datas[r,c]=datatable.Rows[r][c];
}
}

Answer 22

用数组来做
一个一个赋值要慢死人的
测试 10*10000 10万条1.一个一个赋值 
For i = 1 To 10000
    xlRange = xlSheet.Range(xlSheet.Cells(i, 1), xlSheet.Cells(i, 10))
    xlRange(1, 1).value = i
    xlRange(1, 2).value = "1.1"
    xlRange(1, 3).value = "1.12"
    xlRange(1, 4).value = "1.123"
    xlRange(1, 5).value = "1.1234"
    xlRange(1, 6).value = "1.12345"
    xlRange(1, 7).value = "1.123456"
    xlRange(1, 8).value = "1.1234567"
    xlRange(1, 9).value = "1.12345678"
    xlRange(1, 10).value = "1.123456789"
Next
xlBook.Save()历时7分35秒
2.利用2维数组一次性保存
'(A)'
Dim xlArray(9999, 9) As Object
For i = 0 To 9999
    xlArray(i, 0) = New Object : xlArray(i, 0) = i + 1
    xlArray(i, 1) = New Object : xlArray(i, 1) = "1.1"
    xlArray(i, 2) = New Object : xlArray(i, 2) = "1.12"
    xlArray(i, 3) = New Object : xlArray(i, 3) = "1.123"
    xlArray(i, 4) = New Object : xlArray(i, 4) = "1.1234"
    xlArray(i, 5) = New Object : xlArray(i, 5) = "1.12345"
    xlArray(i, 6) = New Object : xlArray(i, 6) = "1.123456"
    xlArray(i, 7) = New Object : xlArray(i, 7) = "1.1234567"
    xlArray(i, 8) = New Object : xlArray(i, 8) = "1.12345678"
    xlArray(i, 9) = New Object : xlArray(i, 9) = "1.123456789"
Next
xlRange = xlSheet.Range("A1").Resize(10000, 10)
xlRange.Value = xlArray
xlBook.Save()历时3秒Excel的导入数据最优方法就是用数组  速度很快
但是缺点也是有的，就是无法保存原先Excel里面的cell的格式，我一般只能在代码里去设置了

Answer 23

openxml参考如下工程，开源的
http://exceldatareader.codeplex.com/
oledb完全可以快速读写excel