完全无组件,不用调用Excel.exe 导出数据到Excel代码,Asp.Net代码库,德仔网

代码语言
代码分类
【Asp.Net】完全无组件,不用调用Excel.exe 导出数据到Excel
作者:CSDN / 发布于2012/1/11/ 873
<div><span style="color: rgb(0,0,255)">using</span><span style="color: rgb(0,0,0)"> System;
</span><span style="color: rgb(0,0,255)">using</span><span style="color: rgb(0,0,0)"> System.Text;
</span><span style="color: rgb(0,0,255)">using</span><span style="color: rgb(0,0,0)"> System.Data;
</span><span style="color: rgb(0,0,255)">using</span><span style="color: rgb(0,0,0)"> System.Data.OleDb;

</span><span style="color: rgb(0,0,255)">namespace</span><span style="color: rgb(0,0,0)"> PowerAgent.DBUtility
{
 </span><span style="color: rgb(0,128,0)">/**/</span><span style="color: rgb(128,128,128)">///</span><span style="color: rgb(0,128,0)"> </span><span style="color: rgb(128,128,128)"><summary></span><span style="color: rgb(0,128,0)">
 </span><span style="color: rgb(128,128,128)">///</span><span style="color: rgb(0,128,0)"> DataToExcel 的摘要说明。

 </span><span style="color: rgb(128,128,128)">///</span><span style="color: rgb(0,128,0)"> DataToExcel 的摘要说明。
 </span><span style="color: rgb(128,128,128)">///</span><span style="color: rgb(0,128,0)"> </span><span style="color: rgb(128,128,128)"></summary></span><span style="color: rgb(128,128,128)">
</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">public</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">class</span><span style="color: rgb(0,0,0)"> DataToExcel
 {
 </span><span style="color: rgb(0,0,255)">const</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">string</span><span style="color: rgb(0,0,0)"> ConnectionString </span><span style="color: rgb(0,0,0)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(128,0,0)">Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;</span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(0,0,0)">;

 </span><span style="color: rgb(0,0,255)">public</span><span style="color: rgb(0,0,0)"> DataToExcel()
 {
 }

 </span><span style="color: rgb(0,0,255)">public</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">string</span><span style="color: rgb(0,0,0)"> DataTableToExcel(DataTable dt, </span><span style="color: rgb(0,0,255)">string</span><span style="color: rgb(0,0,0)"> excelPath)
 {
 </span><span style="color: rgb(0,0,255)">if</span><span style="color: rgb(0,0,0)"> (dt </span><span style="color: rgb(0,0,0)">==</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">null</span><span style="color: rgb(0,0,0)">)
 {
 </span><span style="color: rgb(0,0,255)">return</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(128,0,0)">DataTable不能为空</span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(0,0,0)">;
 }

 </span><span style="color: rgb(0,0,255)">int</span><span style="color: rgb(0,0,0)"> rows </span><span style="color: rgb(0,0,0)">=</span><span style="color: rgb(0,0,0)"> dt.Rows.Count;
 </span><span style="color: rgb(0,0,255)">int</span><span style="color: rgb(0,0,0)"> cols </span><span style="color: rgb(0,0,0)">=</span><span style="color: rgb(0,0,0)"> dt.Columns.Count;
 StringBuilder sb;
 </span><span style="color: rgb(0,0,255)">string</span><span style="color: rgb(0,0,0)"> connString;

 </span><span style="color: rgb(0,0,255)">if</span><span style="color: rgb(0,0,0)"> (rows </span><span style="color: rgb(0,0,0)">==</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,128)">0</span><span style="color: rgb(0,0,0)">)
 {
 </span><span style="color: rgb(0,0,255)">return</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(128,0,0)">没有数据</span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(0,0,0)">;
 }

 sb </span><span style="color: rgb(0,0,0)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">new</span><span style="color: rgb(0,0,0)"> StringBuilder();
 connString </span><span style="color: rgb(0,0,0)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">string</span><span style="color: rgb(0,0,0)">.Format(ConnectionString, excelPath);

 </span><span style="color: rgb(0,128,0)">//</span><span style="color: rgb(0,128,0)">生成创建表的脚本</span><span style="color: rgb(0,128,0)">
</span><span style="color: rgb(0,0,0)"> sb.Append(</span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(128,0,0)">CREATE TABLE </span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(0,0,0)">);
 sb.Append(dt.TableName </span><span style="color: rgb(0,0,0)">+</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(128,0,0)"> ( </span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(0,0,0)">);

 </span><span style="color: rgb(0,0,255)">for</span><span style="color: rgb(0,0,0)"> (</span><span style="color: rgb(0,0,255)">int</span><span style="color: rgb(0,0,0)"> i </span><span style="color: rgb(0,0,0)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,128)">0</span><span style="color: rgb(0,0,0)">; i </span><span style="color: rgb(0,0,0)"><</span><span style="color: rgb(0,0,0)"> cols; i</span><span style="color: rgb(0,0,0)">++</span><span style="color: rgb(0,0,0)">)
 {
 </span><span style="color: rgb(0,0,255)">if</span><span style="color: rgb(0,0,0)"> (i </span><span style="color: rgb(0,0,0)"><</span><span style="color: rgb(0,0,0)"> cols </span><span style="color: rgb(0,0,0)">-</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,128)">1</span><span style="color: rgb(0,0,0)">)
 sb.Append(</span><span style="color: rgb(0,0,255)">string</span><span style="color: rgb(0,0,0)">.Format(</span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(128,0,0)">{0} varchar,</span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(0,0,0)">, dt.Columns.ColumnName));
 </span><span style="color: rgb(0,0,255)">else</span><span style="color: rgb(0,0,0)">
 sb.Append(</span><span style="color: rgb(0,0,255)">string</span><span style="color: rgb(0,0,0)">.Format(</span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(128,0,0)">{0} varchar)</span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(0,0,0)">, dt.Columns.ColumnName));
 }

 </span><span style="color: rgb(0,0,255)">using</span><span style="color: rgb(0,0,0)"> (OleDbConnection objConn </span><span style="color: rgb(0,0,0)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">new</span><span style="color: rgb(0,0,0)"> OleDbConnection(connString))
{
OleDbCommand objCmd </span><span style="color: rgb(0,0,0)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">new</span><span style="color: rgb(0,0,0)"> OleDbCommand();
 objCmd.Connection </span><span style="color: rgb(0,0,0)">=</span><span style="color: rgb(0,0,0)"> objConn;

 objCmd.CommandText </span><span style="color: rgb(0,0,0)">=</span><span style="color: rgb(0,0,0)"> sb.ToString();

 </span><span style="color: rgb(0,0,255)">try</span><span style="color: rgb(0,0,0)">
 {
 objConn.Open();
 objCmd.ExecuteNonQuery();
 }
 </span><span style="color: rgb(0,0,255)">catch</span><span style="color: rgb(0,0,0)"> (Exception e)
 {
 </span><span style="color: rgb(0,0,255)">return</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(128,0,0)">在Excel中创建表失败，错误信息：</span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,0)">+</span><span style="color: rgb(0,0,0)"> e.Message;
 }

 生成插入数据脚本</span><span style="color: rgb(0,0,255)">#region</span><span style="color: rgb(0,0,0)"> 生成插入数据脚本</span><span style="color: rgb(0,0,0)">
 sb.Remove(</span><span style="color: rgb(128,0,128)">0</span><span style="color: rgb(0,0,0)">, sb.Length);
 sb.Append(</span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(128,0,0)">INSERT INTO </span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(0,0,0)">);
 sb.Append(dt.TableName </span><span style="color: rgb(0,0,0)">+</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(128,0,0)"> ( </span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(0,0,0)">);

 </span><span style="color: rgb(0,0,255)">for</span><span style="color: rgb(0,0,0)"> (</span><span style="color: rgb(0,0,255)">int</span><span style="color: rgb(0,0,0)"> i </span><span style="color: rgb(0,0,0)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,128)">0</span><span style="color: rgb(0,0,0)">; i </span><span style="color: rgb(0,0,0)"><</span><span style="color: rgb(0,0,0)"> cols; i</span><span style="color: rgb(0,0,0)">++</span><span style="color: rgb(0,0,0)">)
 {
 </span><span style="color: rgb(0,0,255)">if</span><span style="color: rgb(0,0,0)"> (i </span><span style="color: rgb(0,0,0)"><</span><span style="color: rgb(0,0,0)"> cols </span><span style="color: rgb(0,0,0)">-</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,128)">1</span><span style="color: rgb(0,0,0)">)
 sb.Append(dt.Columns.ColumnName </span><span style="color: rgb(0,0,0)">+</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(128,0,0)">,</span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(0,0,0)">);
 </span><span style="color: rgb(0,0,255)">else</span><span style="color: rgb(0,0,0)">
 sb.Append(dt.Columns.ColumnName </span><span style="color: rgb(0,0,0)">+</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(128,0,0)">) values (</span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(0,0,0)">);
 }

 </span><span style="color: rgb(0,0,255)">for</span><span style="color: rgb(0,0,0)"> (</span><span style="color: rgb(0,0,255)">int</span><span style="color: rgb(0,0,0)"> i </span><span style="color: rgb(0,0,0)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,128)">0</span><span style="color: rgb(0,0,0)">; i </span><span style="color: rgb(0,0,0)"><</span><span style="color: rgb(0,0,0)"> cols; i</span><span style="color: rgb(0,0,0)">++</span><span style="color: rgb(0,0,0)">)
 {
 </span><span style="color: rgb(0,0,255)">if</span><span style="color: rgb(0,0,0)"> (i </span><span style="color: rgb(0,0,0)"><</span><span style="color: rgb(0,0,0)"> cols </span><span style="color: rgb(0,0,0)">-</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,128)">1</span><span style="color: rgb(0,0,0)">)
 sb.Append(</span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(128,0,0)">@</span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,0)">+</span><span style="color: rgb(0,0,0)"> dt.Columns.ColumnName </span><span style="color: rgb(0,0,0)">+</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(128,0,0)">,</span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(0,0,0)">);
 </span><span style="color: rgb(0,0,255)">else</span><span style="color: rgb(0,0,0)">
 sb.Append(</span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(128,0,0)">@</span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,0)">+</span><span style="color: rgb(0,0,0)"> dt.Columns.ColumnName </span><span style="color: rgb(0,0,0)">+</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(128,0,0)">)</span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(0,0,0)">);
 }
 </span><span style="color: rgb(0,0,255)">#endregion</span><span style="color: rgb(0,0,0)">


 </span><span style="color: rgb(0,128,0)">//</span><span style="color: rgb(0,128,0)">建立插入动作的Command</span><span style="color: rgb(0,128,0)">
</span><span style="color: rgb(0,0,0)"> objCmd.CommandText </span><span style="color: rgb(0,0,0)">=</span><span style="color: rgb(0,0,0)"> sb.ToString();
 OleDbParameterCollection param </span><span style="color: rgb(0,0,0)">=</span><span style="color: rgb(0,0,0)"> objCmd.Parameters;

 </span><span style="color: rgb(0,0,255)">for</span><span style="color: rgb(0,0,0)"> (</span><span style="color: rgb(0,0,255)">int</span><span style="color: rgb(0,0,0)"> i </span><span style="color: rgb(0,0,0)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,128)">0</span><span style="color: rgb(0,0,0)">; i </span><span style="color: rgb(0,0,0)"><</span><span style="color: rgb(0,0,0)"> cols; i</span><span style="color: rgb(0,0,0)">++</span><span style="color: rgb(0,0,0)">)
 {
 param.Add(</span><span style="color: rgb(0,0,255)">new</span><span style="color: rgb(0,0,0)"> OleDbParameter(</span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(128,0,0)">@</span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,0)">+</span><span style="color: rgb(0,0,0)"> dt.Columns.ColumnName, OleDbType.VarChar));
 }

 </span><span style="color: rgb(0,128,0)">//</span><span style="color: rgb(0,128,0)">遍历DataTable将数据插入新建的Excel文件中</span><span style="color: rgb(0,128,0)">
</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">foreach</span><span style="color: rgb(0,0,0)"> (DataRow row </span><span style="color: rgb(0,0,255)">in</span><span style="color: rgb(0,0,0)"> dt.Rows)
 {
 </span><span style="color: rgb(0,0,255)">for</span><span style="color: rgb(0,0,0)"> (</span><span style="color: rgb(0,0,255)">int</span><span style="color: rgb(0,0,0)"> i </span><span style="color: rgb(0,0,0)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,128)">0</span><span style="color: rgb(0,0,0)">; i </span><span style="color: rgb(0,0,0)"><</span><span style="color: rgb(0,0,0)"> param.Count; i</span><span style="color: rgb(0,0,0)">++</span><span style="color: rgb(0,0,0)">)
 {
 param.Value </span><span style="color: rgb(0,0,0)">=</span><span style="color: rgb(0,0,0)"> row;
 }

 objCmd.ExecuteNonQuery();
 }

 </span><span style="color: rgb(0,0,255)">return</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(128,0,0)">数据已成功导入Excel</span><span style="color: rgb(128,0,0)">"</span><span style="color: rgb(0,0,0)">;
 }</span><span style="color: rgb(0,128,0)">//</span><span style="color: rgb(0,128,0)">end using</span><span style="color: rgb(0,128,0)">
</span><span style="color: rgb(0,0,0)"> }
 }</span><span style="color: rgb(0,128,0)">//</span><span style="color: rgb(0,128,0)">end class</span><span style="color: rgb(0,128,0)">
</span><span style="color: rgb(0,0,0)">}
</span></div>
评论列表
本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。
试试其它关键字
同语言下
可能有用的
CSDN贡献的其它代码(27)