利用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的效率呢?
-----------------------------------------------------------------------------
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的效率呢?
我直接写System.Web.UI好象不行啊
这样速度会快很多
楼主试试吧
但是有,可以利用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;
{
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;
}
我直接写System.Web.UI是报错的
string[, ] StringArray = ConvertDataTableToStringArray(dt);
好象说ConvertDataTableToStringArray出了问题;14楼的代码我试了,效率很高,我决定就采用这种方法了,有个小问题就是在最后一句
excelSheet.get_Range("A1", excelSheet.Cells[rowCount, colCount]).Value2 = dataArray;excelSheet.Cells[rowCount,colCount]这里应该是[rowCount+1,colCount],否则的话会少一行数据。
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();
上面大侠的方法都试过了,都不太满意.
最后只好导入到csv文件中,挺快的,打开格式也和Excel一样.呵呵
请问这个速度大概得多少,就是纯粹的5000行.
谢谢了,应为我项目中也有这个Excel效率,现在就是没办法提速,只好用CSV代替了.