iT邦幫忙

0

c# GridView 轉出 excel 標頭變亂碼

button1 查詢sql資訊,丟到 GridView顯示,顯示上沒有亂碼

button2 是匯出,將GridView匯出到excel 裡,但表頭卻變成亂碼?

以下是我的圖片及語法,不知是有什麼問題呢?

謝謝!!

https://ithelp.ithome.com.tw/upload/images/20200103/200719314wfOWSjG11.png

 protected void Button1_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection("Data Source=****;Initial Catalog=**** ;Persist Security Info=True;User ID=***;Password=*** ; Connection Timeout=900;");
            conn.Open();

            string selectCmd =
            "SELECT 工號,SUM(事假)事假,SUM(病假)病假,SUM(特休)特休,SUM(公傷)公傷,SUM(公假)公假,SUM(產假)產假,SUM(陪產假)陪產假,SUM(育嬰假)育嬰假,SUM(婚假)婚假,SUM(喪假)喪假,SUM(生理假)生理假,SUM(其它)其它 " +
"FROM( " +
"SELECT 工號,開始日期,結束日期,請假小時數 事假,0 病假,0 特休,0 公傷,0 公假,0 產假,0 陪產假,0 育嬰假,0 婚假,0 喪假,0 生理假,0 其它 " +
"FROM YS_SPE " +
"WHERE 假別='事假' " +
"UNION ALL " +
"SELECT 工號,開始日期,結束日期,0 事假,請假小時數  病假,0 特休,0 公傷,0 公假,0 產假,0 陪產假,0 育嬰假,0 婚假,0 喪假,0 生理假,0 其它 " +
"FROM YS_SPE " +
"WHERE 假別='病假' " +
"UNION ALL " +
"SELECT 工號,開始日期,結束日期,0 事假,0  病假,請假小時數 特休,0 公傷,0 公假,0 產假,0 陪產假,0 育嬰假,0 婚假,0 喪假,0 生理假,0 其它 " +
"FROM YS_SPE " +
"WHERE 假別='特休' " +
"UNION ALL " +
"SELECT 工號,開始日期,結束日期,0 事假,0  病假,0 特休,請假小時數 公傷,0 公假,0 產假,0 陪產假,0 育嬰假,0 婚假,0 喪假,0 生理假,0 其它 " +
"FROM YS_SPE " +
"WHERE 假別='公傷' " +
"UNION ALL " +
"SELECT 工號,開始日期,結束日期,0 事假,0  病假,0 特休,0 公傷,請假小時數 公假,0 產假,0 陪產假,0 育嬰假,0 婚假,0 喪假,0 生理假,0 其它 " +
"FROM YS_SPE " +
"WHERE 假別='公假' " +
"UNION ALL " +
"SELECT 工號,開始日期,結束日期,0 事假,0  病假,0 特休,0 公傷,0 公假,請假小時數 產假,0 陪產假,0 育嬰假,0 婚假,0 喪假,0 生理假,0 其它 " +
"FROM YS_SPE " +
"WHERE 假別='產假' " +
"UNION ALL " +
"SELECT 工號,開始日期,結束日期,0 事假,0  病假,0 特休,0 公傷,0 公假,0 產假,請假小時數 陪產假,0 育嬰假,0 婚假,0 喪假,0 生理假,0 其它 " +
"FROM YS_SPE " +
"WHERE 假別='陪產假' " +
"UNION ALL " +
"SELECT 工號,開始日期,結束日期,0 事假,0  病假,0 特休,0 公傷,0 公假,0 產假,0 陪產假,請假小時數 育嬰假,0 婚假,0 喪假,0 生理假,0 其它 " +
"FROM YS_SPE " +
"WHERE 假別='育嬰假' " +
"UNION ALL " +
"SELECT 工號,開始日期,結束日期,0 事假,0  病假,0 特休,0 公傷,0 公假,0 產假,0 陪產假,0 育嬰假,請假小時數 婚假,0 喪假,0 生理假,0 其它 " +
"FROM YS_SPE " +
"WHERE 假別='婚假' " +
"UNION ALL " +
"SELECT 工號,開始日期,結束日期,0 事假,0  病假,0 特休,0 公傷,0 公假,0 產假,0 陪產假,0 育嬰假,0 婚假,請假小時數 喪假,0 生理假,0 其它 " +
"FROM YS_SPE " +
"WHERE 假別='喪假' " +
"UNION ALL " +
"SELECT 工號,開始日期,結束日期,0 事假,0  病假,0 特休,0 公傷,0 公假,0 產假,0 陪產假,0 育嬰假,0 婚假,0 喪假,請假小時數 生理假,0 其它 " +
"FROM YS_SPE " +
"WHERE 假別='生理假' " +
"UNION ALL " +
"SELECT 工號,開始日期,結束日期,0 事假,0  病假,0 特休,0 公傷,0 公假,0 產假,0 陪產假,0 育嬰假,0 婚假,0 喪假,0 生理假,請假小時數 其它 " +
"FROM YS_SPE " +
"WHERE 假別 NOT IN ('事假','病假','特休','公傷','公假','產假','陪產假','育嬰假','婚假','喪假','生理假') " +
")A " +
"WHERE 開始日期>='20200101'  AND 結束日期<='20200120' " +
"GROUP BY 工號 ";



            SqlDataAdapter da = new SqlDataAdapter(selectCmd, conn);
            DataSet ds = new DataSet();

            da.Fill(ds);
            this.GridView1.DataSource = ds.Tables[0];
            GridView1.DataBind();
            da.Dispose();
            conn.Close();
        }
        
    protected void Button2_Click(object sender, EventArgs e)
        {
            
            Response.ClearContent();
            string excelFileName = "報表" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
            Response.AddHeader("content-disposition", "attachment;filename=" + Server.UrlEncode(excelFileName));

           
            Response.ContentType = "application/excel";
            System.IO.StringWriter stringWrite = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
            GridView1.RenderControl(htmlWrite);
            Response.Write(stringWrite.ToString());
            Response.End();




     
        }
        public override void VerifyRenderingInServerForm(Control control)
        {
            // '處理'GridView' 的控制項 'GridView' 必須置於有 runat=server 的表單標記之中   
        }    
pp73793 iT邦新手 4 級 ‧ 2020-01-03 17:27:42 檢舉
亂碼不知跟定序是否有關係呢?
0
player
iT邦大師 1 級 ‧ 2020-01-03 15:17:40

Response.ClearContent();
改成
Response.Clear();
不然ASP.NET原本的HttpHeader會直接出去吧?

其他的你自己試

ckp6250 iT邦研究生 4 級 ‧ 2020-01-03 15:51:28 檢舉

SQL 可以簡化一下,

SELECT
工號,
開始日期,
結束日期,
sum(if(假別='事假',請假小時數,0)) AS 事假,
sum(if(假別='病假',請假小時數,0)) AS 病假,
.
.
.
.
FROM YS_SPE
WHERE 開始日期>='20200101'  AND 結束日期<='20200120'
GROUP BY 工號
pp73793 iT邦新手 4 級 ‧ 2020-01-03 16:34:07 檢舉

感謝!!指導

0
海綿寶寶
iT邦大神 1 級 ‧ 2020-01-03 16:47:12

我猜
把所有的 column name 全部改成英文
就一切 OK 了

看更多先前的回應...收起先前的回應...
魯大 iT邦高手 1 級 ‧ 2020-01-03 16:56:26 檢舉

如果純粹是編碼的問題,全部改成英文應該就一切OK了
海綿大果然老練

魯大 N 久不見了
2020新年快樂
/images/emoticon/emoticon57.gif

player iT邦大師 1 級 ‧ 2020-01-03 18:41:56 檢舉

asp.net web form 如果沒改輸出語系的話, 預設應該是UTF8吧?
理論上中文欄位應該是OK的

魯大 iT邦高手 1 級 ‧ 2020-01-07 13:10:39 檢舉

/images/emoticon/emoticon57.gif

0
Anthony_Yang
iT邦新手 3 級 ‧ 2020-01-06 10:25:10

路過支援
順便貼上之前讀檔案遇到中文變亂馬的狀況解法

//讀檔案
// 不指定 Encoding,其 Encodeing 為 Unicode
System.IO.StreamReader ReadFile = new System.IO.StreamReader(Convert.ToString(this.utxtFilePath.Text));

// 指定 Encoding 為 System.Text.Encoding.Default (作業系統目前 ANSI 字碼頁的編碼方式)
System.IO.StreamReader ReadFile = new System.IO.StreamReader(Convert.ToString(this.utxtFilePath.Text),System.Text.Encoding.Default);

我要發表回答

立即登入回答