110,534
社区成员
发帖
与我相关
我的任务
分享浅析C#中DataTable数据写入Excel的3种方式
工作2个月，对C#从毫无头绪，到小有心得。很开心，今天事不多，把最近在用的技术写下来，给亲爱的朋友们和以后的自己。O(∩_∩)O（小弟才疏学浅。初来乍到。自己心得。不对之处请大家多多指出！谢谢了）
当从DataTable里面读取出数据并且写入Excel时，有很多种方法。前段时间做了一个小工具，就是读取XML然后写入Excel。
我的实现思路是：从XML读出来，存入DataTable，然后再从DataTable写入Excel。
当然，会有比这个更好的思路，但是现阶段的我只是个小菜鸟，想不到这些实现的最优化方法，只能先这么写。
Xml读出存入DataTable的方法，不想深究了，因为读XML的过程只需要按节点来遍历就OK，然后将得到的数据放入DataRow再Add进入DataTable，如果DataTable大于1个，还可以考虑使用DataSet，当然，这些都是基本的遍历写入、写出，模式相对比较固定了。
我要说的是DataTable写入Excel的过程。实现这个写入的过程我所知道的有2种。一种是ExcelApplication，另一种是OLEDB。
其中ExcelApplication中有2种方法：CellBy Cell 和RowBy Row。OLEDB其实就是一种将Excel当做数据库，利用SQL语句操作的方式。
这些方法，就让我慢慢讲来。
首先，遍历DataTable
For(introw=0;row <DataTable.Rows.Count;row++)
{
For(int col =0;col<DataTable.Columns.Count;col++)
{
Console.Write (“Cell[” + row +”][” + col +”] =” + DataTable.Rows[row][col] + “\t”);
}
Console.Write(“\n”);
}
接下来要做的就是把我们得到的数据写入到Excel里面
第一种：Excel.Application的Cell by Cell
//建立Excel连接
Excel.Application excel = newExcel.Application();
Excel.Workbook book =excel.Workbooks.Add(Missing.Value);
Excel.Worksheet sheet =(Excel.Worksheet)book.ActiveSheet;
或者
Excel.Application excel = newExcel.Application();
Excel.Workbook book =excel.Workbooks. Open(FilePath,0, false, 5, "","", true,Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,"\t", false,false, 0, true,1, 0);
Excel.Worksheet sheet = book.ActiveSheet asExcel.Worksheet;
然后将我们得到的DataTable中的数据写入到Excel中：
//写入第一行即列名
sheet.Cells[1, col + 1] = DataTable.Columns[col].ColumnName;
//从第二行开始写入数据
sheet.Cells[row + 2, col + 1] = DataTable.Rows[row][col].ToString();
最后保存写入的Excel文件：
Book.Save();
Book.SaveAs(FilePath,Excel.XlFileFormat.xlWorkbookNormal,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Excel.XlSaveAsAccessMode.xlExclusive,Missing.Value,Missing.Value,Missing.Value, Missing.Value, Missing.Value);
Book.Close(true,FilePath,Missing.Value);
//退出Excel.Application
Excel.Quit();
Summry：
此方法简单，易学，适合使用在数据量较小，不追求高速率的程序项目中。Cellby Cell的写入速度实在不敢恭维， 10M的XML文件数据，要写1分多钟。
第二种：OleDB
OleDB的操作方式和SQL语句基本一样。所以，我们可以把它当成数据库一样来操作。
首先创建一个连接对象
Private static OleDbConnection m_conn = newOleDbConnection();
Private static OleDbCommand m_cmd = new OleDbCommand();
为了使连接更灵活，我们将另外创建一个单独的方法来获取连接和关闭连接：
private voidopenConnection(string excelFileName, bool addIMEX)
{
if (m_conn.State == ConnectionState.Closed)
{
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;"+
"Extended Properties=Excel 8.0;"+
"data source=" +excelFileName;
if (excelFileName.ToLower().IndexOf(".xlsx") > -1)
{
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;DataSource="+excelFileName;
if (addIMEX)
{
strCon += ";Extended Properties=\"Excel12.0;HDR=YES;IMEX=1\"";
}
else
{
strCon += ";Extended Properties=\"Excel12.0;HDR=YES\"";
}
}
m_conn.ConnectionString = strCon;
m_cmd.Connection = m_conn;
try
{
m_conn.Open();
}
catch (Exceptione)
{ throw newException(e.Message); }
}
}
private voidcloseConnection()
{
if (m_conn.State == ConnectionState.Open)
{
m_conn.Close();
m_conn.Dispose();
m_cmd.Dispose();
}
}
对这个连接方法的理解只需要知道就可以了，如果想深入学习，可以深究，否则，使用的时候会拿来用就可以了。
然后我们要做的就是打开连接，将我们需要的SQL语句放入连接：
openConnection(FilePath,false);
string sqlStr = @“INSERT INTO [Sheet1$](colname1,colname2,colname3) VALUES (value1,value2,value3)”;//需要注意的是SQL中，colname是不能存在空格的。
m_cmd.CommandType = CommandType.Text;
m_cmd.CommandText = sqlStr;
m_cmd.ExecuteNonQuery();
closeConnection();
这时候只需将要插入的数据，以遍历的方式赋值给sqlStr，然后依次Query就可以达到将数据写入Excel的目的。
Summry：
这种方法大大提高了写入Excel的速度，100M左右的文件能控制在2-3分钟之内写完，对于一个熟悉数据库操作连接的人来说OleDb是不难的，但是如果一个完全没有接触过DataBase的人想在短时间内搞懂这个方法，也并非易事。并且，这种方法存在三个方面要特别注意：第一，OleDb在操作的过程中已经涉及流的概念，即我们要自己手动开关Connection，这时候很容易犯的错就是，遍历过程中不停地Open和Close，反而加大了程序的运行时间，起到相反的作用。第二点就是，使用流对Excel文件操作的时候，微软规定每个Cell所容纳的字符不得超过255个。也就是说，使用这种写入是有限制，有风险的。那有的人可能会说，我可以把异常Rows取出来，在最后以CellBy Cell的方式再写入。对，这种方式可以实现，但是我们在完成了功能的时候，所追求的就是最大化的效率。如果这样做，无疑，效率会很低。第三点就是OleDb只能对存在的Excel文件进行操作，它是不能自己创建新的文件的。
第三种：Excel.Appliaction的Row By Row
//获取Application连接对象
Excel.Application excel = new Excel.Application();
Excel.Workbook book =excel.Workbooks.Add(Missing.Value);
Excel.Worksheet sheet =(Excel.Worksheet)book.ActiveSheet;
//假定Excel写入起始单元格时A1，结束单元格时F10；
String startCell = “A1”;
String EndCell = “F10”;//结束单元格通常为不确定，需要自己写算法来计算
//建立一个对象矩阵，行数和列数已知。
Object[,] satData = new object[DataTable.Rows.Count+1,DataTable.Columns.Count];
Int row = 1;
Foreach（DataRows dr in DataTable.Rows）
{
Int col=0;
Foreach(var item in dr.ItemArray)
{
satData[row,col] =item;
col++;
}
Row++;
}
Sheet.get_Range(startCell,endCell).Value2= satData;
Book.Close(true,FilePath,Missing.Value);
Excel.Quit();
Summry:
这个方法，将写入Excel的速度提到了最大化，150M的XML文件，读出加写入时间总和不会超过1分钟。但是，这种方法的弊端是，不太利于理解，并且只能整行写入，当中间Cell出现问题时，是无法更改的。并且，必须要提前知道需要写入的行数和列数（当然，这个是可以用方法动态给出的）。
相比较3种方法，对于数据量相对较小的文件，还是一，二种方法比较适合，学习简单，使用简单，并且小文件的读写几乎可以将时间方面忽略不计。但是在大文件的读写方面，最好还是使用第三种方法，这种方法虽然不太容易理解，但是如果学会了，使用起来是极其方便的。并且没有255Char溢出异常。
最后还要补充的是当使用Excel.Application方法的时候，系统会自动创建一个Excel进程，即使当你使用Excel.Quit()之后，也是不会关闭的。这样就会导致一个结果，当你运行程序次数越多，机器会越卡，假如这个用户是一个不太懂电脑的人，不知道去看一下进程是否有异常，最终的结果势必就是电脑卡死。
为了解决这个问题，我们可以通过进程ID来唯一标识我们自己创建的进程，然后在关闭Excel.Application的时候一同将进程Kill掉。
//Process to Kill Excel
[DllImport("User32.dll",CharSet = CharSet.Auto)]
public static extern intGetWindowThreadProcessId(IntPtr hwnd, out int ID);
public voidKill(Excel.Application excel)
{
IntPtr t = new IntPtr(excel.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p =System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}
Ok.这样我们就可以保证自己的程序正常而高效率地运行啦！O(∩_∩)O！
Thanks for your reading！Any question you can contact me by ： Luoyan35714@126.com.
版权所有，转载记得要注明出处哦！亲！
@Admin Zhihua.
当从DataTable里面读取出数据并且写入Excel时，有很多种方法。前段时间做了一个小工具，就是读取XML然后写入Excel。
我的实现思路是：从XML读出来，存入DataTable，然后再从DataTable写入Excel。
当然，会有比这个更好的思路，但是现阶段的我只是个小菜鸟，想不到这些实现的最优化方法，只能先这么写。
Xml读出存入DataTable的方法，不想深究了，因为读XML的过程只需要按节点来遍历就OK，然后将得到的数据放入DataRow再Add进入DataTable，如果DataTable大于1个，还可以考虑使用DataSet，当然，这些都是基本的遍历写入、写出，模式相对比较固定了。
我要说的是DataTable写入Excel的过程。实现这个写入的过程我所知道的有2种。一种是ExcelApplication，另一种是OLEDB。
其中ExcelApplication中有2种方法：CellBy Cell 和RowBy Row。OLEDB其实就是一种将Excel当做数据库，利用SQL语句操作的方式。
这些方法，就让我慢慢讲来。
首先，遍历DataTable
For(introw=0;row <DataTable.Rows.Count;row++)
{
For(int col =0;col<DataTable.Columns.Count;col++)
{
Console.Write (“Cell[” + row +”][” + col +”] =” + DataTable.Rows[row][col] + “\t”);
}
Console.Write(“\n”);
}
接下来要做的就是把我们得到的数据写入到Excel里面
第一种：Excel.Application的Cell by Cell
//建立Excel连接
Excel.Application excel = newExcel.Application();
Excel.Workbook book =excel.Workbooks.Add(Missing.Value);
Excel.Worksheet sheet =(Excel.Worksheet)book.ActiveSheet;
或者
Excel.Application excel = newExcel.Application();
Excel.Workbook book =excel.Workbooks. Open(FilePath,0, false, 5, "","", true,Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,"\t", false,false, 0, true,1, 0);
Excel.Worksheet sheet = book.ActiveSheet asExcel.Worksheet;
然后将我们得到的DataTable中的数据写入到Excel中：
//写入第一行即列名
sheet.Cells[1, col + 1] = DataTable.Columns[col].ColumnName;
//从第二行开始写入数据
sheet.Cells[row + 2, col + 1] = DataTable.Rows[row][col].ToString();
最后保存写入的Excel文件：
Book.Save();
Book.SaveAs(FilePath,Excel.XlFileFormat.xlWorkbookNormal,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Excel.XlSaveAsAccessMode.xlExclusive,Missing.Value,Missing.Value,Missing.Value, Missing.Value, Missing.Value);
Book.Close(true,FilePath,Missing.Value);
//退出Excel.Application
Excel.Quit();
Summry：
此方法简单，易学，适合使用在数据量较小，不追求高速率的程序项目中。Cellby Cell的写入速度实在不敢恭维， 10M的XML文件数据，要写1分多钟。
第二种：OleDB
OleDB的操作方式和SQL语句基本一样。所以，我们可以把它当成数据库一样来操作。
首先创建一个连接对象
Private static OleDbConnection m_conn = newOleDbConnection();
Private static OleDbCommand m_cmd = new OleDbCommand();
为了使连接更灵活，我们将另外创建一个单独的方法来获取连接和关闭连接：
private voidopenConnection(string excelFileName, bool addIMEX)
{
if (m_conn.State == ConnectionState.Closed)
{
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;"+
"Extended Properties=Excel 8.0;"+
"data source=" +excelFileName;
if (excelFileName.ToLower().IndexOf(".xlsx") > -1)
{
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;DataSource="+excelFileName;
if (addIMEX)
{
strCon += ";Extended Properties=\"Excel12.0;HDR=YES;IMEX=1\"";
}
else
{
strCon += ";Extended Properties=\"Excel12.0;HDR=YES\"";
}
}
m_conn.ConnectionString = strCon;
m_cmd.Connection = m_conn;
try
{
m_conn.Open();
}
catch (Exceptione)
{ throw newException(e.Message); }
}
}
private voidcloseConnection()
{
if (m_conn.State == ConnectionState.Open)
{
m_conn.Close();
m_conn.Dispose();
m_cmd.Dispose();
}
}
对这个连接方法的理解只需要知道就可以了，如果想深入学习，可以深究，否则，使用的时候会拿来用就可以了。
然后我们要做的就是打开连接，将我们需要的SQL语句放入连接：
openConnection(FilePath,false);
string sqlStr = @“INSERT INTO [Sheet1$](colname1,colname2,colname3) VALUES (value1,value2,value3)”;//需要注意的是SQL中，colname是不能存在空格的。
m_cmd.CommandType = CommandType.Text;
m_cmd.CommandText = sqlStr;
m_cmd.ExecuteNonQuery();
closeConnection();
这时候只需将要插入的数据，以遍历的方式赋值给sqlStr，然后依次Query就可以达到将数据写入Excel的目的。
Summry：
这种方法大大提高了写入Excel的速度，100M左右的文件能控制在2-3分钟之内写完，对于一个熟悉数据库操作连接的人来说OleDb是不难的，但是如果一个完全没有接触过DataBase的人想在短时间内搞懂这个方法，也并非易事。并且，这种方法存在三个方面要特别注意：第一，OleDb在操作的过程中已经涉及流的概念，即我们要自己手动开关Connection，这时候很容易犯的错就是，遍历过程中不停地Open和Close，反而加大了程序的运行时间，起到相反的作用。第二点就是，使用流对Excel文件操作的时候，微软规定每个Cell所容纳的字符不得超过255个。也就是说，使用这种写入是有限制，有风险的。那有的人可能会说，我可以把异常Rows取出来，在最后以CellBy Cell的方式再写入。对，这种方式可以实现，但是我们在完成了功能的时候，所追求的就是最大化的效率。如果这样做，无疑，效率会很低。第三点就是OleDb只能对存在的Excel文件进行操作，它是不能自己创建新的文件的。
第三种：Excel.Appliaction的Row By Row
//获取Application连接对象
Excel.Application excel = new Excel.Application();
Excel.Workbook book =excel.Workbooks.Add(Missing.Value);
Excel.Worksheet sheet =(Excel.Worksheet)book.ActiveSheet;
//假定Excel写入起始单元格时A1，结束单元格时F10；
String startCell = “A1”;
String EndCell = “F10”;//结束单元格通常为不确定，需要自己写算法来计算
//建立一个对象矩阵，行数和列数已知。
Object[,] satData = new object[DataTable.Rows.Count+1,DataTable.Columns.Count];
Int row = 1;
Foreach（DataRows dr in DataTable.Rows）
{
Int col=0;
Foreach(var item in dr.ItemArray)
{
satData[row,col] =item;
col++;
}
Row++;
}
Sheet.get_Range(startCell,endCell).Value2= satData;
Book.Close(true,FilePath,Missing.Value);
Excel.Quit();
Summry:
这个方法，将写入Excel的速度提到了最大化，150M的XML文件，读出加写入时间总和不会超过1分钟。但是，这种方法的弊端是，不太利于理解，并且只能整行写入，当中间Cell出现问题时，是无法更改的。并且，必须要提前知道需要写入的行数和列数（当然，这个是可以用方法动态给出的）。
相比较3种方法，对于数据量相对较小的文件，还是一，二种方法比较适合，学习简单，使用简单，并且小文件的读写几乎可以将时间方面忽略不计。但是在大文件的读写方面，最好还是使用第三种方法，这种方法虽然不太容易理解，但是如果学会了，使用起来是极其方便的。并且没有255Char溢出异常。
最后还要补充的是当使用Excel.Application方法的时候，系统会自动创建一个Excel进程，即使当你使用Excel.Quit()之后，也是不会关闭的。这样就会导致一个结果，当你运行程序次数越多，机器会越卡，假如这个用户是一个不太懂电脑的人，不知道去看一下进程是否有异常，最终的结果势必就是电脑卡死。
为了解决这个问题，我们可以通过进程ID来唯一标识我们自己创建的进程，然后在关闭Excel.Application的时候一同将进程Kill掉。
//Process to Kill Excel
[DllImport("User32.dll",CharSet = CharSet.Auto)]
public static extern intGetWindowThreadProcessId(IntPtr hwnd, out int ID);
public voidKill(Excel.Application excel)
{
IntPtr t = new IntPtr(excel.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p =System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}
Ok.这样我们就可以保证自己的程序正常而高效率地运行啦！O(∩_∩)O！
Thanks for your reading！Any question you can contact me by ： Luoyan35714@126.com.
版权所有，转载记得要注明出处哦！亲！
@Admin Zhihua.
...全文
请发表友善的回复…
发表回复
泡泡龙 2014-12-28
- 打赏
- 举报
写入excel 单元格数据的最快方法是二维数组
操作excel的时候，要及时释放非托管资源，这样excel在执行app.quit之后，就能正常退出了
luoyan35714 2014-12-26
- 打赏
- 举报
12年写的东西，那时候还啥都不知道呢。~哈哈~没想到14年了还有人在回复~非常感谢~
luoyan35714 2014-12-26
- 打赏
- 举报
还有我的个人博客网站哦~欢迎参观
http://www.hifreud.com/
luoyan35714 2014-12-26
- 打赏
- 举报
已经不做C#多年了，现在转战Java
对于你的观点我不是很同意。不过大家可以求同存异嘛。
1. 你说前人栽树后人乘凉，那如果我们不栽树，我们的后人怎么乘凉？、
2. 技术遇到难题，如果只解决不总结，下次遇到还是不会，如果只总结，不分享，怎么知道自己是对的，或许还有比你更好的解决方案呢。
3. Java领域我所知道的一些大牛都是喜欢写博客分享知识的。比如Martin Fowler， Rod Johnson, 毕玄,林昊（http://www.blogjava.net/BlueDavy）
4. 你说有时间多去学点别人的，这个我赞同，但是学习的同时也要思考，每种技术都是在解决特定场景下的业务问题或技术问题。如果有一天现有的技术解决不了你的问题怎么办。办法只有一个，自己创造技术。
不知你是怎么理解的。
by_封爱 版主 2014-11-06
- 打赏
- 举报
LZ爱学习的精神自然可嘉..
不过现成的东西我们没必要在玩的那么深..
正所谓前人栽树后人乘凉,说的就是这个道理.
所以有的时候 遇到问题 先google(实在上不去就baidu)下.
你在2014能遇到的问题 早在很久甚至10年前已经有大批量的人都遇到了..而且都有不错的解决方案.
所以除了针对业务上面的东西.技术方便一般来说都可以搜索得到....
不如把你写这个的时间,去研究下别人的东西如何使用......
smthgdin_020 2014-11-05
- 打赏
- 举报
内容有点多。不过分享的精神要赞一下。
於黾 2014-11-05
- 打赏
- 举报
这格式...看得眼睛都变长了
楼主用过NPOI吗?
X龙 2014-11-05
- 打赏
- 举报
很有帮助
wq519686385 2012-09-05
- 打赏
- 举报
不错,谢谢分享
智者知已应修善业 2012-09-05
- 打赏
- 举报
不错,谢谢分享
Tod707070 2012-09-04
- 打赏
- 举报
那种开启excel以后一格一格写的方法是最慢的，要是想导入个几万行数据要等几个小时。
Tod707070 2012-09-04
- 打赏
- 举报
不知道楼主用过QueryTable方法没有，就是先写成txt文件，再用Excel的QueryTable.refresh方法进行载入文本。这个速度是最快的。
