浮雲雅築: [研究][C#][ASP.NET] GridView 匯出成 Excel / .ODF (.ods) (以Html方式輸出)

[研究][C#][ASP.NET] GridView 匯出成 Excel / .ODF (.ods) (以Html方式輸出)

2020-04-09
2020-04-15 更新

**********

因為本篇在 Excel 2007 或更新版開啟會有警告，不是很好做法。

建議改用下面這篇

[研究][C#]匯出、匯入 ODF/.ods 檔案 (使用 Ionic.Zip、DotNetZip)

http://shaurong.blogspot.com/2020/09/caspnetwebform-ioniczip-odfods.html

**********

【開放文件格式 (Open Document Format, ODF)】：
LibreOffice 或 Apache OpenOffice 中相對 Microsoft Excel 試算表的軟體稱為 Calc，檔案為 .ods (OpenDocument Spreadsheet)。

Libre Office 6.3.5安定版下載網址：
https://zh-tw.libreoffice.org/download/libreoffice-still/

Apache OpenOffice 4.1.7下載網址：
https://openoffice.apache.org/downloads.html

開發工具：Visual Studio Enterprise 2019 v16.5.3

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ODSExportHTML.Default"

 EnableEventValidation = "false" %>

<!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>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TestDBConnectionString %>" 
                SelectCommand="SELECT * FROM [TestTable]" 
                ></asp:SqlDataSource>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="True" DataSourceID="SqlDataSource1">
            </asp:GridView>
            <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" />
        </div>
    </form>
</body>
</html>

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;

namespace ODSExportHTML
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {

            Label_MSG1.Text = "";
            if (GridView1.Rows.Count == 0)
            {
                Label_MSG1.Text = "無可匯出資料。";
                Label_MSG1.ForeColor = System.Drawing.Color.Red;
                return;
            }
            ExportGridViewToODS(GridView1);
        }
        private void ExportGridViewToODS(GridView gridViewExport)
        {
            //string fileName = "匯出檔名" + DateTime.Now + ".xls";
            string fileName = "匯出檔名";
            HtmlForm form = new HtmlForm();
            Response.Clear();
            Response.Buffer = true;
            //Response.Charset = "";
            Response.Charset = "UTF-8";
            Response.AddHeader("Content-Disposition", "attachment;filename=\"" +

                HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) +

                DateTime.Now.ToString("yyyyMMddHHmmss") + ".ods\"");
            // Office 2007 File Format MIME Types for HTTP Content Streaming
            // http://blogs.msdn.com/b/vsofficedeveloper/archive/2008/05/08/office-2007-open-xml-mime-types.aspx
            //Response.ContentType = "application/vnd.ms-excel";
            Response.ContentType = "application/vnd.oasis.opendocument.spreadsheet";
            Response.Write("<meta http-equiv=Content-Type content=text/html;charset=utf-8>");
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            form.Attributes["runat"] = "server";
            //form.Controls.Add(gridViewExport);
            Boolean oldAllowPaging = gridViewExport.AllowPaging;
            Boolean oldAllowSorting = gridViewExport.AllowSorting;
            gridViewExport.AllowPaging = false;  // 不可有分頁，否則只會列出目前分頁的內容
            gridViewExport.AllowSorting = false;  // 有排序標題列不好看
            gridViewExport.DataBind();   // 不重新 DataBiind，標題列依然有排序底線
            form.Controls.Add(gridViewExport);
            this.Controls.Add(form);

            // form.RenderControl(hw); 若出現錯誤訊息：RegisterForEventValidation 只能在 Render(); 期間呼叫
            // 解決：把　gridViewExport 放在 <form runat=server></form>  之間
            // 或將 EnableEventValidation = "false" AutoEventWireup="true" 這兩個屬性加到 <%page %> 標籤裡。

            // 您正在嘗試開啟 'XXXX.xls'，其檔案格式與副檔名所指定的格式不同，開啟檔案前，請確認檔案未損毀，且來自信任的來源
            // Office Excel 2007 開啟時才會出現，Office Excel 2003以下的版本卻是沒有這個對話框！
            // 因為新版本 對檔案格式檢查較嚴格
            form.RenderControl(hw);

            string style = @"<style> .textmode { mso-number-format:\@;}</style>";
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
            gridViewExport.AllowPaging = oldAllowPaging;
            gridViewExport.AllowSorting = oldAllowSorting;
        }
    }
}