2016-03-02
參考這篇修改使用的
http://www.encodedna.com/2013/01/asp.net-export-to-excel.htm
幾點注意事項:
- 電腦上必須安裝 Excel 商業版本 ( Excel Viewer 實際測試無效)
- MS-SQL Server 上先建立一個資料庫 TestDB,裡面有個資料表 TestTable,有兩個欄位 no 和 myname,隨邊輸入幾筆資料。
- 測試環境是 Visual Studio 2015 + Excel 2010 + SQL Server 2014
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> <title></title> </head> <body> <form id="form1" runat="server"> <div> <!-- BUTTON CONTROL TO EXPORT DATA TO EXCEL. --> <p><input type="button" onserverclick="ExportToExcel" value="Export data to Excel" runat="server" /></p> <!-- SHOW MESSAGE. --> <p><asp:Label ID="lblConfirm" Text="" runat="server"></asp:Label></p> <div> <!-- VIEW BUTTON WILL OPEN THE EXCEL FILE FOR VIEWING. --> <div style="float:left;padding-right:10px;"> <input type="button" onserverclick="ViewData" id="btView" value="View Data" runat="server" style="display:none;" /> </div> <!--DOWNLOAD EXCEL FILE. --> <div style="float:left;"> <asp:Button ID="btDownLoadFile" Text="Download" OnClick="DownLoadFile" runat="server" style="display:none;" /> </div> </div> </div> </form> </body> </html> |
Default.aspx.cs 內容
using System; using System.Data; using System.Data.SqlClient; using System.IO; using System.Text; using System.Web; // Visual Studio 2015 要加入 "參考",參考名稱的 "Microsoft Excel 14.0 Object Library" // 電腦可能要安裝 Microsoft Office 2010 using Excel = Microsoft.Office.Interop.Excel; using ExcelAutoFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void ExportToExcel(object sender, EventArgs e) { string sCon = "Data Source=.;Persist Security Info=False;Integrated Security=SSPI;" + "Initial Catalog=TestDB;User Id=sa;Password=P@ssw0rd;Connect Timeout=30;"; using (SqlConnection con = new SqlConnection(sCon)) { using (SqlCommand cmd = new SqlCommand("SELECT * FROM TestTable")) { SqlDataAdapter sda = new SqlDataAdapter(); try { cmd.Connection = con; con.Open(); sda.SelectCommand = cmd; DataTable dt = new DataTable(); sda.Fill(dt); if (dt.Rows.Count > 0) { string path = Server.MapPath("exportedfiles\\"); if (!Directory.Exists(path)) // CHECK IF THE FOLDER EXISTS. IF NOT, CREATE A NEW FOLDER. { Directory.CreateDirectory(path); } File.Delete(path + "EmployeeDetails.xlsx"); // DELETE THE FILE BEFORE CREATING A NEW ONE. // ADD A WORKBOOK USING THE EXCEL APPLICATION. Excel.Application xlAppToExport = new Excel.Application(); xlAppToExport.Workbooks.Add(""); // ADD A WORKSHEET. Excel.Worksheet xlWorkSheetToExport = default(Excel.Worksheet); //xlWorkSheetToExport = (Excel.Worksheet)xlAppToExport.Sheets["Sheet1"]; xlWorkSheetToExport = (Excel.Worksheet)xlAppToExport.Sheets["工作表1"]; // 繁體中文版Excel要改用 "工作表1" // ROW ID FROM WHERE THE DATA STARTS SHOWING. //int iRowCnt = 4; int iRowCnt = 3; // SHOW THE HEADER. xlWorkSheetToExport.Cells[1, 1] = "Employee Details"; Excel.Range range = xlWorkSheetToExport.Cells[1, 1] as Excel.Range; range.EntireRow.Font.Name = "Calibri"; range.EntireRow.Font.Bold = true; range.EntireRow.Font.Size = 20; xlWorkSheetToExport.Range["A1:D1"].MergeCells = true; // MERGE CELLS OF THE HEADER. // SHOW COLUMNS ON THE TOP. //xlWorkSheetToExport.Cells[iRowCnt - 1, 1] = "Employee Name"; //xlWorkSheetToExport.Cells[iRowCnt - 1, 2] = "Mobile No."; //xlWorkSheetToExport.Cells[iRowCnt - 1, 3] = "PresentAddress"; //xlWorkSheetToExport.Cells[iRowCnt - 1, 4] = "Email Address"; xlWorkSheetToExport.Cells[iRowCnt - 1, 1] = "編號"; xlWorkSheetToExport.Cells[iRowCnt - 1, 2] = "姓名"; xlWorkSheetToExport.Cells[iRowCnt - 1, 3] = "住址"; int i; for (i = 0; i <= dt.Rows.Count - 1; i++) { //xlWorkSheetToExport.Cells[iRowCnt, 1] = dt.Rows[i].Field("EmpName"); //xlWorkSheetToExport.Cells[iRowCnt, 2] = dt.Rows[i].Field("Mobile"); //xlWorkSheetToExport.Cells[iRowCnt, 3] = dt.Rows[i].Field("PresentAddress"); //xlWorkSheetToExport.Cells[iRowCnt, 4] = dt.Rows[i].Field("Email"); //xlWorkSheetToExport.Cells[iRowCnt, 1] = (int)dt.Rows[i].Field("no"); //xlWorkSheetToExport.Cells[iRowCnt, 2] = dt.Rows[i].Field("myname"); xlWorkSheetToExport.Cells[iRowCnt, 1] = dt.Rows[i]["no"]; xlWorkSheetToExport.Cells[iRowCnt, 2] = dt.Rows[i]["myname"]; xlWorkSheetToExport.Cells[iRowCnt, 3] = dt.Rows[i]["addr"]; iRowCnt = iRowCnt + 1; } // FINALLY, FORMAT THE EXCEL SHEET USING EXCEL'S AUTOFORMAT FUNCTION. //Excel.Range range1 = xlAppToExport.ActiveCell.Worksheet.Cells[4, 1] as Excel.Range; Excel.Range range1 = xlAppToExport.ActiveCell.Worksheet.Cells[3, 1] as Excel.Range; range1.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatList3); // SAVE THE FILE IN A FOLDER. xlWorkSheetToExport.SaveAs(path + "EmployeeDetails.xlsx"); // CLEAR. xlAppToExport.Workbooks.Close(); xlAppToExport.Quit(); xlAppToExport = null; xlWorkSheetToExport = null; lblConfirm.Text = "Data Exported Successfully"; lblConfirm.Attributes.Add("style", "color:green; font: normal 14px Verdana;"); btView.Attributes.Add("style", "display:block"); btDownLoadFile.Attributes.Add("style", "display:block"); } } catch (Exception ex) { lblConfirm.Text = ex.Message.ToString(); lblConfirm.Attributes.Add("style", "color:red; font: bold 14px/16px Sans-Serif,Arial"); } finally { sda.Dispose(); sda = null; } } } } // VIEW THE EXPORTED EXCEL DATA. protected void ViewData(object sender, System.EventArgs e) { // 如果直接開啟檔案內容,開啟第二次會出現錯誤 // 由於另一個處理序正在使用檔案 'C:\CodeTemp\WebSite2\exportedfiles\EmployeeDetails.xlsx',所以無法存取該檔案。 string path = Server.MapPath("exportedfiles\\"); try { // CHECK IF THE FOLDER EXISTS. if (Directory.Exists(path)) { // CHECK IF THE FILE EXISTS. if (File.Exists(path + "EmployeeDetails.xlsx")) { // SHOW (NOT DOWNLOAD) THE EXCEL FILE. Excel.Application xlAppToView = new Excel.Application(); xlAppToView.Workbooks.Open(path + "EmployeeDetails.xlsx"); xlAppToView.Visible = true; } } } catch (Exception ex) { // } } // DOWNLOAD THE FILE. protected void DownLoadFile(object sender, EventArgs e) { try { string sPath = Server.MapPath("exportedfiles\\"); string fileName = "匯出檔案"; Response.Clear(); Response.AppendHeader("Content-Disposition", "attachment; filename=EmployeeDetails.xlsx"); //Response.ContentEncoding = Encoding.GetEncoding(950);//950就是所謂的BIG5 //Response.AddHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls\""); Response.TransmitFile(sPath + "EmployeeDetails.xlsx"); //Response.TransmitFile(sPath + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls\""); Response.End(); } catch (Exception ex) { } } } |
(完)
沒有留言:
張貼留言