應用ADO.NET OleDB 方式將 DataSet 匯出轉成 Excel
讓匯出的Excel在上傳回Server的時候是可以讀取的
剛看到 F6 Team 的 使用ASP.NET的GridView滙出/儲存資料至Excel,Word,Txt檔 ，
這篇有人回應，利用 GridView or DataGrid 轉出的 Excel 會不是 Excel 的格式，
所以在將匯出的 Excel 傳回去給Server處理的時候，無法讀取這個Excel。
就Dotjum知道應用 .NET 將資料集DataSet 匯出成 Excel 的方式有三種方式：
1.透過 DataGird、GridView 方式來轉出 Excel ，可以參考在 點部落 其他朋友的文章有介紹，
擴展 GridView 控制項 - 支援 Excel 及 Word 匯出
輸出成Excel，並且有多個工作表（Work Sheet）
將ASP.NET的成果，匯出 Excel檔案
[ASP.NET] GridView 轉 Excel
使用ASP.NET的GridView滙出/儲存資料至Excel,Word,Txt檔
2. 應用 Microsoft.Office.Interop.Excel ，把TSQL下給Excel，Excel去叫資料回來，這個方法我用在WinForm.
3.應用ADO.NET OleDB 方式將 DataSet 匯出轉成 Excel
之前使用第一種方法GridView匯出的資料Excel修改後，要傳回Server在判斷，Server端會無法讀取Excel，
GridView匯出Excel方式已經含一些Html格式在裡面，所以讀取方面會出現錯誤，
之前有看過在 GridView 轉出Excel，加上一些程式碼去過濾資料，能夠轉出的Excel是可以回傳上Server端的.NET解析，
但好像沒有記下來，後來我採用第三種方式來處理.
詳細的處理流程如下
1.先連線到資料庫取出資料到 DataTable
2.產生一個臨時的 .xsl
3.透過 OleDB 的方式將 DataTable資料每一筆 Insert 進去
4.Response.WriteFile 臨時的 .xsl
5.刪除臨時的 .xsl
(要注意的是寫入的資料夾記得要開給ASP.NET權限）
實做的程式碼
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.SqlClient;
using System.Text;
using System.Data.OleDb;
public partial class DataSetToExcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnDSToExcel_Click1(object sender, EventArgs e)
{
//這邊是取SqlServer最新資料
DataSet dsData = new DataSet();
SqlConnection sqlConn = new SqlConnection();
sqlConn = new SqlConnection(@"連線字串");
SqlDataAdapter sqlAdapter = new SqlDataAdapter("SELECT TOP 10 * FROM dbo.Products", sqlConn);
sqlAdapter.Fill(dsData, "Sheet1");
DataTable dtTable = dsData.Tables["Sheet1"];
//取目前目錄
string physicPath = HttpContext.Current.Server.MapPath(this.Context.Request.Path);
//產生一個臨時檔案
string fileName = Guid.NewGuid() + ".Xls";
//把臨時檔案的實體Full路徑
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + physicPath + fileName + ";Extended Properties=Excel 8.0;";
//傳給產生Excel的程式處理 該副程式會把資料填進去
DataTableToExcel(dtTable, connString);
Response.Clear();
//要給的實體路徑
Response.WriteFile(physicPath + fileName);
string httpHeader = "attachment;filename=backup.Xls";
Response.ContentType = "application/vnd.ms-excel";
Response.AppendHeader("Content-Disposition", httpHeader);
Response.Flush();
//刪除臨時文件
System.IO.File.Delete(physicPath + fileName);
Response.End();
}
public void DataTableToExcel(DataTable dt, string connString)
{
//先算出欄位及列數
int rows = dt.Rows.Count;
int cols = dt.Columns.Count;
//用來建立命令
StringBuilder sb = new StringBuilder();
sb.Append("CREATE TABLE ");
sb.Append(dt.TableName + " ( ");
//用來做開TABLE的欄名資訊
for (int i = 0; i < cols; i++)
{
if (i < cols - 1)
sb.Append(string.Format("{0} varchar,", dt.Columns[i].ColumnName));
else
sb.Append(string.Format("{0} varchar)", dt.Columns[i].ColumnName));
}
//把要開啟的臨時Excel建立起來
using (OleDbConnection objConn = new OleDbConnection(connString))
{
OleDbCommand objCmd = new OleDbCommand();
objCmd.Connection = objConn;
objCmd.CommandText = sb.ToString();
objConn.Open();
//先執行CreateTable的任務
objCmd.ExecuteNonQuery();
//開始處理資料內容的新增
#region 開始處理資料內容的新增
//把之前 CreateTable 清空
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO ");
sb.Append(dt.TableName + " ( ");
//這邊開始組該Excel欄位順序
for (int i = 0; i < cols; i++)
{
if (i < cols - 1)
sb.Append(dt.Columns[i].ColumnName + ",");
else
sb.Append(dt.Columns[i].ColumnName + ") values (");
}
//這邊組 DataTable裡面的值要給到Excel欄位的
for (int i = 0; i < cols; i++)
{
if (i < cols - 1)
sb.Append("@" + dt.Columns[i].ColumnName + ",");
else
sb.Append("@" + dt.Columns[i].ColumnName + ")");
}
#endregion
//建立插入動作的Command
objCmd.CommandText = sb.ToString();
OleDbParameterCollection param = objCmd.Parameters;
for (int i = 0; i < cols; i++)
{
param.Add(new OleDbParameter("@" + dt.Columns[i].ColumnName, OleDbType.VarChar));
}
//使用參數化的方式來給予值
foreach (DataRow row in dt.Rows)
{
for (int i = 0; i < param.Count; i++)
{
param[i].Value = row[i];
}
//執行這一筆的給值
objCmd.ExecuteNonQuery();
}
}//end using
}
}
透過將DataTable的資料使用 ADO.NET OleDB 方式新增資料進去方式，最後匯出的Excel就會是一般的Excel格式。
Dotjum的方法可能程式碼多了些，因該還有更好的方法，還請知道的朋友，可以分享更好的方法。
