2016年3月2日 星期三

[研究][C#][ASP.NET] GridView 匯出成 .xls (使用 Excel )

[研究][C#][ASP.NET] GridView 匯出成 .xls (使用 Excel )

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
Default.aspx 內容為

<%@ 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) { }
    }
}



(完)

沒有留言:

張貼留言