110,533
社区成员
发帖
与我相关
我的任务
分享请问如何高效的把DataTable中的内容写入Excel中?
我用的C#代码是这样的,极慢不知何解?
/// <summary>
/// 把DataTable导入到Excel中
/// </summary>
/// <param name="tableName">表的名字</param>
/// <param name="result">要导入的DataTable</param>
public String OuterPutExcel(String tableName, DataTable result, int num)
{
oExcel = new Excel.ApplicationClass();
DataTable dt = new DataTable();//按客户出货日月统计表
StringBuilder build = new StringBuilder();
build.Append(Server.MapPath(Request.ApplicationPath)).Append("\\App_Data\\").Append(DateTime.Now.Year.ToString()).Append("年-").Append(DateTime.Now.Month).Append("月-").Append(DateTime.Now.Day.ToString()).Append("日").Append("成品报表.xls");
sFile = build.ToString();
if (num == 1)//第一次调用,判断是否有今天的成品报表,有此删除,添加新Excel文件.
{
if (File.Exists(sFile))
File.Delete(sFile); // '删除服务端临时文件
oExcel.Workbooks.Add(Type.Missing);//添加一个新的工作薄;
Excel.Workbook activeBook = (Excel.Workbook)oExcel.ActiveWorkbook;
activeBook.SaveAs(sFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing);//新建一个Excel文件;
}
oExcel.Visible = true;
oExcel.DisplayAlerts = false;
//'定义一个新的工作簿
oBooks = oExcel.Workbooks;//
oBooks.Open(sFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oBook = oBooks[1];
//oBook.Protect(Type.Missing, true, true);//设置工作簿保护
oSheets = oBook.Worksheets;
try
{
//把导入的数据表插入到适当的位置
for (int i = 0; i < oSheets.Count; i++)
{
oSheet = (Excel.Worksheet)oSheets[i + 1];
//oSheet.Protect(Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, false);//工作表保护;
oCells = oSheet.Cells;
if (oSheet.Name.ToLower().Trim() == tableName.ToLower().Trim() || tableName == "发货--按工厂分" || tableName == "发货--按客户分")
{
if (tableName == "发货--按客户分")
oSheet.Name = "发货--按客户分";
else if (tableName == "发货--按工厂分")
{
oSheet = (Excel.Worksheet)oSheets[2];//第二个工作表
oSheet.Name = "发货--按工厂分";
oCells = oSheet.Cells;
}
oCells.Clear();
DumpData(result, oCells);//在Excel中写数据.
oSheet.SaveAs(sFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//oExcel.ActiveWorkbook.Close(true, sTemplate, null); // 关闭 Excel 文件且保存Excel文件
break;//跳出循环
}
if (i == oSheets.Count - 1)//在原来的表中找不到此工作表.
{
if (tableName == "发货--按工厂分")//插入到第一个工作表中
{
oSheets.Add((Excel.Worksheet)oSheets[1], Type.Missing, Type.Missing, Type.Missing);//插入的工作表
oSheet = oBook.ActiveSheet as Excel.Worksheet;
oSheet.Name = "发货--按工厂分";
oCells = oSheet.Cells;
oCells.Clear();
DumpData(result, oCells);//在Excel中写数据.
oSheet.SaveAs(sFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
break;
}
else if (tableName == "发货--按客户分")//插入到第二个工作表中
{
oSheets.Add((Excel.Worksheet)oSheets[2], Type.Missing, Type.Missing, Type.Missing);//插入的工作表
oSheet = oBook.ActiveSheet as Excel.Worksheet;
oSheet.Name = "发货--按客户分";
oCells = oSheet.Cells;
oCells.Clear();
DumpData(result, oCells);//在Excel中写数据.
oSheet.SaveAs(sFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//oBook.SaveAs(sFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
break;
}
else//插入到第三个工作表之后.
{
if (oSheet.Name.ToLower() == "sheet3")
{
oSheet.Name = tableName;
oCells = oSheet.Cells;
oCells.Clear();
DumpData(result, oCells);//在Excel中写数据.
oSheet.SaveAs(sFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//oBook.SaveAs(sFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
break;
}
oSheets.Add(Type.Missing, (Excel.Worksheet)oSheets[oSheets.Count], Type.Missing, Type.Missing);//插入的工作表
oSheet = oBook.ActiveSheet as Excel.Worksheet;
oSheet.Name = tableName;
oCells = oSheet.Cells;
oCells.Clear();
DumpData(result, oCells);//在Excel中写数据.
oSheet.SaveAs(sFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//oBook.SaveAs(sFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
break;
}
}
}
}
catch (System.Exception ex)
{
System.Diagnostics.Process myproc = new System.Diagnostics.Process();//
System.Diagnostics.Process[] proc = System.Diagnostics.Process.GetProcessesByName("excel");
foreach (System.Diagnostics.Process process in proc)
{
if (!process.CloseMainWindow())
{
process.Kill();
}
}
}
finally
{
oBook.Close(Type.Missing, Type.Missing, Type.Missing);
//'退出Excel，并且释放调用的COM资源
oExcel.Quit();
Marshal.ReleaseComObject(oCells); Marshal.ReleaseComObject(oSheet);
Marshal.ReleaseComObject(oSheets); Marshal.ReleaseComObject(oBook);
Marshal.ReleaseComObject(oBooks); Marshal.ReleaseComObject(oExcel);
oExcel = null; oBooks = null; oBook = null;
oSheets = null; oSheet = null; oCells = null;
System.GC.Collect();
}
/*这里用到个goto语句,是因为: 线程是异步执行的,下面的代码要访问download.xls文件,但有
少数情况下上面的线程'未能及时释放download.xls文件的指针,那么下面代码执行语句时会抛出异常, 当发生异常时需要等待资源释放后,'再重新访问该文件, 保证下载文件能够正确下载*/
return sFile;
...全文
请发表友善的回复…
发表回复
noky 2008-06-10
- 打赏
- 举报
操作Excel有两种方式，
1.把Excel当作对象进行操作，这属于VBA的范畴，你的方法就是这种，这种方法直观，但是效率底下，数据量一大就超级的慢
2.把Excel当作数据库对待，以下是部分实例，希望对你有用
try
{
...
...
mCnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";
mOleCnn = new OleDbConnection(mCnnStr );
mOleCom = new OleDbCommand();
mOleCom.Connection = mOleCnn ;
mOleCom.Connection.Open();
string columnStr = "CREATE TABLE Sheet1("; // 创建表，表名字为sheet1，你可以更改，就是在xls中看到的名字。
foreach (..........)
{
// 此处的columnStr是拼接出标准的Create Table语句，其中的name是列的名字，varchar是列的类型
columnStr += name + " varchar,";
}
...
...
mOleCom.CommandText = columnStr ;//让其执行Create语句，创建出表来
mOleCom.ExecuteNonQuery();
mOleCom.Parameters.Clear();
/*此处需要你需要创建一个OleDbParameterCollection对象，此对象就是你table中的数据，只需要在此处将table中的每一个row中对应的数据填入相应的位置就可以了。比如
paramColl[name].Value = value，此处value就是datable中指定行，指定列的数据值
*/
//拼接完成OleDbParameterCollection对象后，执行mOleCom，这样就可以经DataTable中的一行数据写入xls中。
mOleCom.ExecuteNonQuery();
.................
.................
catch(Exception ex)
{
。。。。。。。。。。。。
}
}
1.把Excel当作对象进行操作，这属于VBA的范畴，你的方法就是这种，这种方法直观，但是效率底下，数据量一大就超级的慢
2.把Excel当作数据库对待，以下是部分实例，希望对你有用
try
{
...
...
mCnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";
mOleCnn = new OleDbConnection(mCnnStr );
mOleCom = new OleDbCommand();
mOleCom.Connection = mOleCnn ;
mOleCom.Connection.Open();
string columnStr = "CREATE TABLE Sheet1("; // 创建表，表名字为sheet1，你可以更改，就是在xls中看到的名字。
foreach (..........)
{
// 此处的columnStr是拼接出标准的Create Table语句，其中的name是列的名字，varchar是列的类型
columnStr += name + " varchar,";
}
...
...
mOleCom.CommandText = columnStr ;//让其执行Create语句，创建出表来
mOleCom.ExecuteNonQuery();
mOleCom.Parameters.Clear();
/*此处需要你需要创建一个OleDbParameterCollection对象，此对象就是你table中的数据，只需要在此处将table中的每一个row中对应的数据填入相应的位置就可以了。比如
paramColl[name].Value = value，此处value就是datable中指定行，指定列的数据值
*/
//拼接完成OleDbParameterCollection对象后，执行mOleCom，这样就可以经DataTable中的一行数据写入xls中。
mOleCom.ExecuteNonQuery();
.................
.................
catch(Exception ex)
{
。。。。。。。。。。。。
}
}
机器人 2008-06-10
- 打赏
- 举报
http://blog.csdn.net/fangxinggood/archive/2006/04/08/655313.aspx
zhiguo2008 2008-06-10
- 打赏
- 举报
2楼知道有相关的参考代码吗? 麻烦贴一贴.
烈火焚身 2008-06-10
- 打赏
- 举报
up
suosuoyyy 2008-06-10
- 打赏
- 举报
建议你通过Oledb方式来进行Excel文件的读写，把Excel文件的Sheet页作为数据表来操作，那样效率会高很多。
可以查找相关资料：
SELECT *FROM OPENROWSET( 'MICROSOFT.JET.OLEDB.4.0','Excel 8.0;IMEX=1;HDR=YES;DATABASE=你的excel.xls',[sheet1$])
可以查找相关资料：
SELECT *FROM OPENROWSET( 'MICROSOFT.JET.OLEDB.4.0','Excel 8.0;IMEX=1;HDR=YES;DATABASE=你的excel.xls',[sheet1$])
kbryant 2008-06-10
- 打赏
- 举报
关注
s32702 2008-06-10
- 打赏
- 举报
学习中
zhiguo2008 2008-06-10
- 打赏
- 举报
这段代码报错 ,执行到 mOleCom.ExecuteNonQuery(); 插入数据失败.
{System.Data.OleDb.OleDbException: 语法错误 (操作符丢失) 在查询表达式 '@安科讯-1177主板' 中。
{System.Data.OleDb.OleDbException: 语法错误 (操作符丢失) 在查询表达式 '@安科讯-1177主板' 中。
private void generateExcelTable(DataTable tb,String path)
{
try
{
String mCnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";
OleDbConnection mOleCnn = new OleDbConnection(mCnnStr );
OleDbCommand mOleCom = new OleDbCommand();
mOleCom.Connection = mOleCnn ;
mOleCom.Connection.Open();
OleDbParameterCollection paramColl = mOleCom.Parameters;
//OleDbParameter[] paramColl = new OleDbParameter[tb.Columns.Count];
String isExistsTable = "drop table " + tb.TableName;
mOleCom.CommandText = isExistsTable;
mOleCom.ExecuteNonQuery();
string columnStr = "CREATE TABLE "+tb.TableName+"("; // 创建表，表名字为sheet1，你可以更改，就是在xls中看到的名字。
foreach (DataColumn name in tb.Columns)
{
// 此处的columnStr是拼接出标准的Create Table语句，其中的name是列的名字，varchar是列的类型
if (tb.Columns.IndexOf(name) == tb.Columns.Count - 1)//如果为最后一列,则不要添加","号;
{
columnStr += "["+name.ColumnName + "] varchar";
continue;
}
columnStr += "["+name.ColumnName + "] varchar,";
//添加参数
//paramColl[tb.Columns.IndexOf(name)] = new OleDbParameter(name.ColumnName, OleDbType.VarChar, 100);
}
columnStr += ")";
mOleCom.CommandText = columnStr ;//让其执行Create语句，创建出表来
mOleCom.ExecuteNonQuery();
mOleCom.Parameters.Clear();
/*此处需要你需要创建一个OleDbParameterCollection对象，此对象就是你table中的数据，只需要在此处将table中的每一个row中对应的数据填入相应的位置就可以了。比如
paramColl[name].Value = value，此处value就是datable中指定行，指定列的数据值
*/
String insertStr = "insert into " + tb.TableName + " values(";//构造插入数据的Sql语句;
foreach(DataColumn name in tb.Columns)
{
if (tb.Columns.IndexOf(name) == tb.Columns.Count - 1)//如果为最后一列,则不要添加","号;
{
insertStr += "@" + name.ColumnName;
continue;
}
insertStr += "@" + name.ColumnName+",";
}
insertStr += ")";
mOleCom.CommandText = insertStr;//插入语句的Sql
foreach (DataColumn name in tb.Columns)//添加参数定义;
{
paramColl.Add(new OleDbParameter(name.ColumnName, OleDbType.VarChar, 100));
}
foreach(DataRow row in tb.Rows)
{
foreach (DataColumn name in tb.Columns)
{
paramColl[name.ColumnName].Value = row[name];
}
}
//拼接完成OleDbParameterCollection对象后，执行mOleCom，这样就可以经DataTable中的一行数据写入xls中。
mOleCom.ExecuteNonQuery();
}
catch(Exception ex)
{
}
}zhiguo2008 2008-06-10
- 打赏
- 举报
上面的问题解决了,在测试下面的代码..
zhiguo2008 2008-06-10
- 打赏
- 举报
我测试七楼兄弟的代码时,发现代码执行到第一个 mOleCom.ExecuteNonQuery(); 创建表时报错:
System.Data.OleDb.OleDbException: Microsoft Jet 数据库引擎找不到对象'CustomerGoodsTable'。请确定对象是否存在，并正确地写出它的名称和路径。
"ustomerGoodsTable" 为我要创建的工作表. "path"是我的Excel文件路径. 测试代码中是"D:\\项目\\ProjectERP\\App_Data\\2008年-6月-10日成品报表.xls"
能否看一看这代码?
System.Data.OleDb.OleDbException: Microsoft Jet 数据库引擎找不到对象'CustomerGoodsTable'。请确定对象是否存在，并正确地写出它的名称和路径。
"ustomerGoodsTable" 为我要创建的工作表. "path"是我的Excel文件路径. 测试代码中是"D:\\项目\\ProjectERP\\App_Data\\2008年-6月-10日成品报表.xls"
能否看一看这代码?
private void generateExcelTable(DataTable tb,String path)
{
try
{
String mCnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";
OleDbConnection mOleCnn = new OleDbConnection(mCnnStr );
OleDbCommand mOleCom = new OleDbCommand();
mOleCom.Connection = mOleCnn ;
mOleCom.Connection.Open();
string columnStr = "CREATE TABLE "+tb.TableName+"("; // 创建表，表名字为sheet1，你可以更改，就是在xls中看到的名字。
foreach (DataColumn name in tb.Columns)
{
// 此处的columnStr是拼接出标准的Create Table语句，其中的name是列的名字，varchar是列的类型
if (tb.Columns.IndexOf(name) == tb.Columns.Count - 1)//如果为最后一列,则不要添加","号;
{
columnStr += "["+name.ColumnName + "] varchar";
continue;
}
columnStr += "["+name.ColumnName + "] varchar,";
}
columnStr += ")";
mOleCom.CommandText = columnStr ;//让其执行Create语句，创建出表来
mOleCom.ExecuteNonQuery();
mOleCom.Parameters.Clear();
/*此处需要你需要创建一个OleDbParameterCollection对象，此对象就是你table中的数据，只需要在此处将table中的每一个row中对应的数据填入相应的位置就可以了。比如
paramColl[name].Value = value，此处value就是datable中指定行，指定列的数据值
*/
OleDbParameterCollection paramColl = mOleCom.Parameters;
foreach(DataRow row in tb.Rows)
{
foreach (DataColumn name in tb.Columns)
{
paramColl[name.ColumnName].Value = row[name];
}
}
//拼接完成OleDbParameterCollection对象后，执行mOleCom，这样就可以经DataTable中的一行数据写入xls中。
mOleCom.ExecuteNonQuery();
}
catch(Exception ex)
{
}
}
九章落地 2008-06-10
- 打赏
- 举报
若把Excel当数据源,对Excel格式的控制,显得很不灵活.
楼主可以用批量写入的方法,这种方式非常快:
先把数据填充到一个二维数组,再将二维数组填充到RANGE.
示例代码:
楼主可以用批量写入的方法,这种方式非常快:
先把数据填充到一个二维数组,再将二维数组填充到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;
zhiguo2008 2008-06-10
- 打赏
- 举报
7楼的兄弟,你的方法2真的是我没有尝试的方法.试试看...
