ASP.NET中匯入和匯出標準Excel檔案

本文所示範的匯入和匯出方法並未使用Excel COM組件，而是使用原始的ASP.NET OleDb驅動程式，這使得ASP.NET應用程式即使部署在未安裝Excel的伺服器上也同樣能支援匯入和匯出校準的Excel格式檔案。

本文所示範的匯入和匯出方法並未使用Excel COM組件，而是使用原始的ASP.NET OleDb驅動程式，這使得ASP.NET應用程式即使部署在未安裝Excel的伺服器上也同樣能支援匯入和匯出校準的Excel格式檔案。

一、匯出

網上有不少關於ASP.NET應用程式匯出Excel檔案的方法，大多都是設定Response對象的Content-Type為“application/ms-excel”後，以字串形式輸出資料供使用者下載（可參考：http://blog.csdn.net/jilm168/archive/2007/11/06/1869118.aspx）。

雖說這種方式確實能實現匯出Excel檔案的目的，但其匯出的Excel檔案是不標準的。如果你用記事本開啟使用此方式匯出的檔案會發現該檔案是一個純文字檔案，這個倒是次要的，因為強大的Excel絕對有能力識別並顯示這種“偽”Excel檔案，但如果我們的應用程式要支援匯入的話，那麼這一檔案就無法被程式正確識別了。

為了匯出標準的Excel檔案，我們可以首先準備好一個標準Excel檔案做為匯出模板，匯出資料時先建立此模板的一個副本，使用OleDb驅動程式向副本中寫入資料後，將此副本的二進位位元組流輸出至用戶端供使用者下載，最後再刪除此副本。

如何製做一個Excel檔案模板呢？你可以在安裝有Excel的機器上建立一個“Microsoft Excel 工作表”，在工作表的第一行設定好需要的欄位，然後選中整個表，點右鍵選擇“設定儲存格格式”，在“數字”標籤的“分類”列表選擇“文本”，點擊“確定” 並儲存檔案。將儲存格格式設定為“文本”主要是為了防止日後匯入資料時某些資料格式無法被識別。

有了這個模板，我們就可以開始寫代碼匯出資料了。本樣本所使用模板檔案位於網站根目錄，名為“demo.xls”，共包含三個列：“ID”、“姓名”和“生日”。

excel_template

// 根據模板檔案棄置站台
string filePath = Server.MapPath("~/" + Guid.NewGuid().ToString() + ".xls");
File.Copy(Server.MapPath("~/demo.xls"), filePath);
// 使用OleDb驅動程式串連到副本
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;");
using (conn)
{
conn.Open();
// 增加記錄
OleDbCommand cmd = new OleDbCommand("INSERT INTO [Sheet1$]([ID], [姓名], [生日]) VALUES(@Id, @Name, @Birthday)", conn);
cmd.Parameters.AddWithValue("@Id", "1");
cmd.Parameters.AddWithValue("@Name", "Hsu Yencheng");
cmd.Parameters.AddWithValue("@Birthday", "1981-10-13");
cmd.ExecuteNonQuery();
}
// 輸出副本的二進位位元組流
Response.ContentType = "application/ms-excel";
Response.AppendHeader("Content-Disposition", "attachment;filename=info.xls");
Response.BinaryWrite(File.ReadAllBytes(filePath));
// 刪除副本
File.Delete(filePath);

 

二、匯入

相對於匯出，匯入部分的實現代碼要簡單的多：
string filePath = Server.MapPath("~/info.xls");
OleDbDataAdapter da = new OleDbDataAdapter(
"SELECT * FROM [Sheet1$]",
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0");
DataTable dt = new DataTable();
da.Fill(dt);
// 接下來你就可以對 dt 為所欲為了……
上面的代碼中，我們匯入了位於網站根目錄的“info.xls”，匯入時我們依舊使用OleDb驅動程式來訪問Excel檔案。再次提醒讀者，此種方式只支援匯入標準的Excel檔案，這也是上面匯出部分使用預先準備好的Excel模板檔案的原因。

另外，以上示範僅針對Excel 2000-2003格式的檔案，如果您要匯入或匯出的是Excel 2007檔案（*.xlsx），那麼您需要將連接字串改為（可參考：http://www.connectionstrings.com/）：
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=info.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=info.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

-----經過實踐,上面的方法是可行,但有個問題:執行效率很低,超過255行的資料匯出時,特別的慢(在網上查得是MS的限制).解決方案是:將INSERT部分不使用參數模式,而直接使用串拼接成INSERT的SQL語句,執行起來速度很快,匯出時也很快,超過255時也很快,我僅測試過用SQL串語句產生並匯出2000條,檔案大小有3M,速度是不慢的,完成可以接受.而使用原方法,在256條時就感覺到慢了.

另外:OLEDB模式下可以先用CREATE TABLE產生SHEET1$這種EXCEL工作表,然後再INSERT資料,證實是可行的.這樣模板檔案就只要一個空白的EXCEL文檔了.靈活性高很多.