C#写入excel数据的效率问题
利用C#(windows)实现将datatable中数据写入excel文件，我看网上有许多方法，参考了其中一部分，我采用了将datatable中的数据依次写入到xls的cell中的方法，具体代码如下：
-----------------------------------------
private void DTtoExcel(DataTable dt,String fname)
{
Excel.Application excel = new Excel.Application();
int rowIndex = 1;
int colIndex = 0;
Excel.Workbook xbook = excel.Workbooks.Add(true);
foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
xbook.SaveAs(fname,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
xbook = null;
excel.Quit();
excel = null;
}
------------------------------------------------------
这段代码可以实现将datatable数据写入xls，但是我觉得效率好低，对于一个2500行，20列的xls，写入要接近5分钟，我觉得有点接受不了。
我之前在C#(Web)下写过同样的一个功能，是利用HtmlTextWriter和StringWriter来实现的，对于同样一个xls，只要5秒钟左右，这二者效率不可比，我想问问在windows下能不能也通过数据流来实现写入xls？如果不能够，那有什么方法可以提高写入xls的效率呢？
[解决办法]
Winform 程序也可以用 Web 功能的，相应的命名空间，也可以用 HtmlTextWriter 。
[解决办法]
DataTable dt = GetData(); //此处会返回50000条记录的DataTable对象
string[, ] StringArray = ConvertDataTableToStringArray(dt); // 此处将DataTable中的数据导入到string二维数组中
Excel.Range StringRange = (Excel.Range)workSheet.Cells[2, 1];
StringRange = StringRange.get_Resize(dt.Rows.Count, dt.Columns.Count);
StringRange.Value2 = StringArray ;
Save(); // 保存Excel file
这样速度会快很多
楼主试试吧
[解决办法]
影响Excel的速度很多，常见的就是每写一个单元格，就会刷屏，所以要把它的选项关掉
但是有，可以利用Excel内置的特性，让Excel自己去处理速度的问题，所以，你先获得你的数据，
然后根据你的行列数，实例一个Range，然后，把数据一次性赋值给Range
楼上就是利用这一特性，奖数据转换为二维数组，赋给Range的Value2 属性
[解决办法]
楼主可以用批量写入的方法,这种方式非常快:
先把数据填充到一个二维数组,再将二维数组填充到RANGE.
示例代码:
Excel.Application xApp = new Excel.ApplicationClass(); string[,] strs = new string[9, 9]; for(int i = 0; i < 9; i++) for(int j = 0; j < 9; j++) strs[i, j] = Convert.ToString((i + 1) * (j + 1)); Excel.Workbook xBook = xApp.Workbooks.Add(Missing.Value); Excel.Worksheet xSheet = (Excel.Worksheet)xBook.Worksheets[1]; xSheet.get_Range(xSheet.Cells[1, 1], xSheet.Cells[9, 9]).Value = strs;
[解决办法]
private void button1_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true; //提示是否创建(*****).xls文件
saveFileDialog.Title = "导出Excel文件到";
//进度条
progressBar1.Refresh();
progressBar1.Visible = true;
progressBar1.Minimum = 1;
progressBar1.Maximum = dataGridView1.Rows.Count;
progressBar1.Step = 1;
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
Stream myStream;
myStream = saveFileDialog.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));
string str = "";
try
{
//写标题
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
if (i > 0)
{
str += "\t";
}
str += dataGridView1.Columns[i].HeaderText;
}
sw.WriteLine(str);
//写内容
for (int j = 0; j < dataGridView1.Rows.Count - 1; j++)
{
progressBar1.PerformStep();
string tempStr = "";
for (int k = 0; k < dataGridView1.Columns.Count; k++)
{
if (k > 0)
{
tempStr += "\t";
}
tempStr += dataGridView1.Rows[j].Cells[k].Value.ToString();
}
sw.WriteLine(tempStr);
}
progressBar1.Visible = false;
MessageBox.Show("导出数据成功");
sw.Close();
myStream.Close();
}
catch (Exception ee)
{
MessageBox.Show(ee.Message);
return;
}
finally
{
sw.Close();
myStream.Close();
}
}
}
[解决办法]
Excel.Application excel=new Microsoft.Office.Interop.Excel.Application(); Excel.Workbook excelBook = excel.Workbooks.Add(Type.Missing); Excel.Worksheet excelSheet = (Excel.Worksheet)excelBook.ActiveSheet; excel.Visible = true; try { DataTable dt = GetTable(); DataTableToExcel(dt, excelSheet); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; GC.Collect(); }}public void DataTableToExcel(DataTable dt, Excel.Worksheet excelSheet) { int rowCount = dt.Rows.Count; int colCount = dt.Columns.Count; object[,] dataArray = new object[rowCount+1, colCount]; for (int k = 0; k < colCount; k++) { dataArray[0, k] = dt.Columns[k].ColumnName; } for (int i = 0; i < rowCount; i++) { for (int j = 0; j < colCount; j++) { dataArray[i+1, j] = dt.Rows[i][j]; } } excelSheet.get_Range("A1", excelSheet.Cells[rowCount, colCount]).Value2 = dataArray; }
[解决办法]
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + strXlsPathName + ";Extended Properties='Excel 8.0;HDR=YES;ReadOnly=false;'";
OleDbConnection objConn = new OleDbConnection(strConn);
objConn.Open();
OleDbCommand objCmd = new OleDbCommand() ;
objCmd.Connection = objConn;
//objCmd.CommandText = "CREATE TABLE SheetYY(Column1 varchar(255), Column2 varchar(255))";
//objCmd.ExecuteNonQuery();
objCmd.CommandText = " insert into [Sheet1$] (Column1,Column2,Column5)" + " values(1,'2',3)";
objCmd.ExecuteNonQuery();
objCmd.CommandText = " insert into [Sheet1$] (Column1,Column2,Column3,Column4,Column5)" + " values(2,'2',3,'a','b')";
objCmd.ExecuteNonQuery();
objCmd.CommandText = " insert into [Sheet2$] (Column1,Column2,Column3,Column4,Column5)" + " values(2,'2',3,'a','b')";
objCmd.ExecuteNonQuery();
objConn.Close();