using OfficeOpenXml.Style;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;

namespace 練習
{
    public partial class _Default : Page
    {

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MSSQL_DBconnect"].ConnectionString);
        protected void Page_Load(object sender, EventArgs e)
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                string unit_type = "u.[name]";
                unit_type = "u.[nickname]";
                cmd.CommandText = @"DECLARE @StartDay varchar(10)
                SET @StartDay = @start_date
                DECLARE @EndDay varchar(10)
                SET @EndDay = @end_date
                DECLARE @accuse_no varchar(50)
                SET @accuse_no = @accuse
                DECLARE @unit_type varchar(50)
                SET @unit_type = @type

                declare @sql varchar(8000)
                set @sql =
                'SELECT ' + @unit_type + ' as ''單位'',case when accuse_tp = ''2'' or accuse_tp=''4'' then ''逕行舉發'' else ''現場攔停'' end as 類型,'+
                'SUM(case when (ruleid like ''35101%'' or ruleid like ''35102%'' or  ruleid like ''352%'' or  ruleid like ''353%'' or  ruleid like ''354%'' or  ruleid like ''355%'') and len(ruleid)=8 then 1 else 0 end) as ''酒後駕車35'','+
                'SUM(case when ruleid like ''531%'' then 1 else 0 end) as ''闖紅燈53-1'','+
                'SUM(case when ((ruleid like ''33%'' OR ruleid like ''40%'' OR ruleid like ''43%'') AND (CAST(sp2 AS INT)-CAST(sp1 AS INT)>=40)) then 1 else 0 end) as ''嚴重超速40公里以上 33 40 43'','+
                'SUM(case when ruleid like ''45101%'' OR ruleid like ''45103%'' OR ruleid = ''3310707'' OR ruleid = ''3310708'' then 1 else 0 end) as ''逆向行駛 45 33'','+
                'SUM(case when ((ruleid like ''48102%'' or ruleid like ''48104%'') and kind <>''3'' and kind <> ''4'') then 1 else 0 end) as ''轉彎未依規定 48.1'','+
                'SUM(case when ruleid like ''43101%'' OR ruleid like ''43103%'' OR ruleid like ''43104%'' then 1 else 0 end) as ''蛇行惡意逼車(快速道路、一般道路)33 43'','+
                'SUM(case when ruleid like ''33109%'' then 1 else 0 end) as ''行駛路肩(快速道路)33'','+
                'SUM(case when ruleid like ''33%'' AND truth like ''%大型車%外車道%'' then 1 else 0 end) as ''大型車慢速車未依規定行駛外車道(快速道路)33'','+
                'SUM(case when ruleid like ''4511301%'' then 1 else 0 end) as ''機車行駛禁行機車道45-13'','+
                'SUM(case when ruleid like ''48102%'' and (kind = ''3'' or kind = ''4'') then 1 else 0 end) as ''機車不依兩段式左轉48.1.2'','+
                'SUM(case when ((ruleid like ''35101%'' or ruleid like ''35102%'' or  ruleid like ''352%'' or  ruleid like ''353%'' or  ruleid like ''354%'' or  ruleid like ''355%'') and len(ruleid)=8)  '+
                'or (ruleid like ''531%'')  '+
                'or ((ruleid like ''33%'' OR ruleid like ''40%'' OR ruleid like ''43%'') AND (CAST(sp2 AS INT)-CAST(sp1 AS INT)>=40)) '+
                'or (ruleid like ''45101%'' OR ruleid like ''45103%'' OR ruleid = ''3310707'' OR ruleid = ''3310708'')  '+
                'or ((ruleid like ''48102%'' or ruleid like ''48104%'') and kind <>''3'' and kind <> ''4'') '+
                'or (ruleid like ''43101%'' OR ruleid like ''43103%'' OR ruleid like ''43104%'') '+
                'or (ruleid like ''33109%'') '+
                'or (ruleid like ''33%'' AND truth like ''%大型車%外車道%'') '+
                'or (ruleid like ''4511301%'') '+
                'or (ruleid like ''48102%'' and (kind = ''3'' or kind = ''4'')) then 1 else 0 end) as ''合計'' '+
                'FROM '+
                '( '+
                ' select tkt_no, rule1 as ''ruleid'', truth_1 as ''truth'', accuse_tp, accuse_no,kind,rule1_sp1 as ''sp1'',rule1_sp2 as ''sp2'' '+
                ' from police where ((TRIM(in_date) >= ''' + @StartDay + ''' AND TRIM(in_date) <= ''' + @EndDay + ''') or (in_date = ''0000000'' and TRIM(get_date) >= ''' + @StartDay + ''' AND TRIM(get_date) <= ''' + @EndDay + ''')) AND TRIM(rule1) <> ''''  AND accuse_no like ''' + @accuse_no + ''' '+
                ' UNION ALL '+
                ' select tkt_no, rule2 as ''ruleid'', truth_2 as ''truth'', accuse_tp, accuse_no,kind,rule2_sp1 as ''sp1'',rule2_sp2 as ''sp2'' '+
                ' from police where ((TRIM(in_date) >= ''' + @StartDay + ''' AND TRIM(in_date) <= ''' + @EndDay + ''') or (in_date = ''0000000'' and TRIM(get_date) >= ''' + @StartDay + ''' AND TRIM(get_date) <= ''' + @EndDay + ''')) AND TRIM(rule2) <> ''''  AND accuse_no like ''' + @accuse_no + ''' '+
                ') A '+
                'left join unit u on A.accuse_no=u.code '+
                'GROUP BY ' + @unit_type + ',case when accuse_tp = ''2'' or accuse_tp=''4'' then ''逕行舉發'' else ''現場攔停'' end '+
                'UNION ALL '+
                'SELECT ''總計'' as ''單位'',case when accuse_tp = ''2'' or accuse_tp=''4'' then ''逕行舉發'' else ''現場攔停'' end as 類型,'+
                'SUM(case when (ruleid like ''35101%'' or ruleid like ''35102%'' or  ruleid like ''352%'' or  ruleid like ''353%'' or  ruleid like ''354%'' or  ruleid like ''355%'') and len(ruleid)=8 then 1 else 0 end) as ''酒後駕車35'','+
                'SUM(case when ruleid like ''531%'' then 1 else 0 end) as ''闖紅燈53-1'','+
                'SUM(case when ((ruleid like ''33%'' OR ruleid like ''40%'' OR ruleid like ''43%'') AND (CAST(sp2 AS INT)-CAST(sp1 AS INT)>=40)) then 1 else 0 end) as ''嚴重超速40公里以上 33 40 43'','+
                'SUM(case when ruleid like ''45101%'' OR ruleid like ''45103%'' OR ruleid = ''3310707'' OR ruleid = ''3310708'' then 1 else 0 end) as ''逆向行駛 45 33'','+
                'SUM(case when ((ruleid like ''48102%'' or ruleid like ''48104%'') and kind <>''3'' and kind <> ''4'') then 1 else 0 end) as ''轉彎未依規定 48.1'','+
                'SUM(case when ruleid like ''43101%'' OR ruleid like ''43103%'' OR ruleid like ''43104%'' then 1 else 0 end) as ''蛇行惡意逼車(快速道路、一般道路)33 43'','+
                'SUM(case when ruleid like ''33109%'' then 1 else 0 end) as ''行駛路肩(快速道路)33'','+
                'SUM(case when ruleid like ''33%'' AND truth like ''%大型車%外車道%'' then 1 else 0 end) as ''大型車慢速車未依規定行駛外車道(快速道路)33'','+
                'SUM(case when ruleid like ''4511301%'' then 1 else 0 end) as ''機車行駛禁行機車道45-13'','+
                'SUM(case when ruleid like ''48102%'' and (kind = ''3'' or kind = ''4'') then 1 else 0 end) as ''機車不依兩段式左轉48.1.2'','+
                'SUM(case when ((ruleid like ''35101%'' or ruleid like ''35102%'' or  ruleid like ''352%'' or  ruleid like ''353%'' or  ruleid like ''354%'' or  ruleid like ''355%'') and len(ruleid)=8)  '+
                'or (ruleid like ''531%'')  '+
                'or ((ruleid like ''33%'' OR ruleid like ''40%'' OR ruleid like ''43%'') AND (CAST(sp2 AS INT)-CAST(sp1 AS INT)>=40)) '+
                'or (ruleid like ''45101%'' OR ruleid like ''45103%'' OR ruleid = ''3310707'' OR ruleid = ''3310708'')  '+
                'or ((ruleid like ''48102%'' or ruleid like ''48104%'') and kind <>''3'' and kind <> ''4'') '+
                'or (ruleid like ''43101%'' OR ruleid like ''43103%'' OR ruleid like ''43104%'') '+
                'or (ruleid like ''33109%'') '+
                'or (ruleid like ''33%'' AND truth like ''%大型車%外車道%'') '+
                'or (ruleid like ''4511301%'') '+
                'or (ruleid like ''48102%'' and (kind = ''3'' or kind = ''4'')) then 1 else 0 end) as ''合計'' '+
                'FROM '+
                '('+
                'select tkt_no, rule1 as ''ruleid'', truth_1 as ''truth'', accuse_tp, accuse_no,kind,rule1_sp1 as ''sp1'',rule1_sp2 as ''sp2'' '+
                'from police where ((TRIM(in_date) >= ''' + @StartDay + ''' AND TRIM(in_date) <= ''' + @EndDay + ''') or (in_date = ''0000000'' and TRIM(get_date) >= ''' + @StartDay + ''' AND TRIM(get_date) <= ''' + @EndDay + ''')) AND TRIM(rule1) <> '''' AND accuse_no like ''' + @accuse_no + ''' '+
                'UNION ALL '+
                'select tkt_no, rule2 as ''ruleid'', truth_2 as ''truth'', accuse_tp, accuse_no,kind,rule2_sp1 as ''sp1'',rule2_sp2 as ''sp2'' '+
                'from police where ((TRIM(in_date) >= ''' + @StartDay + ''' AND TRIM(in_date) <= ''' + @EndDay + ''') or (in_date = ''0000000'' and TRIM(get_date) >= ''' + @StartDay + ''' AND TRIM(get_date) <= ''' + @EndDay + ''')) AND TRIM(rule2) <> '''' AND accuse_no like ''' + @accuse_no + ''' '+
                ') A '+
                'left join unit u on A.accuse_no=u.code '+
                'GROUP BY case when accuse_tp = ''2'' or accuse_tp=''4'' then ''逕行舉發'' else ''現場攔停'' end '

                exec (@sql)";
                cmd.Parameters.AddWithValue("@start_date", "1120101");
                cmd.Parameters.AddWithValue("@end_date", "1120501");
                cmd.Parameters.AddWithValue("@accuse", "%");
                cmd.Parameters.AddWithValue("@type", unit_type);
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds, "data1");
                //777
                if (ds.Tables["data1"].Rows.Count > 0)
                {
                    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                    ExcelPackage ep = new ExcelPackage();
                    ExcelWorksheet sheet = ep.Workbook.Worksheets.Add("加強取締重大交通違規");
                    sheet.Cells[1, 1].Value = "列印時間：" + (DateTime.Now.Year - 1911).ToString() + DateTime.Now.ToString("MMdd");
                    sheet.Cells[2, 1].Value = "列印單位：";
                    sheet.Cells[3, 1].Value = "列印人員：" + Session["UserName"];
                    sheet.Cells[1, 2, 1, 13].Value = "苗栗縣警察局";
                    sheet.Cells[1, 2, 1, 13].Merge = true;
                    sheet.Cells[2, 2, 2, 13].Value = "加強取締重大交通違規";
                    sheet.Cells[2, 2, 2, 13].Merge = true;
                    sheet.Cells[3, 2, 3, 13].Value = "統計期間：" + "1120501" + "至" + "1120531";
                    sheet.Cells[3, 2, 3, 13].Merge = true;
                    sheet.Cells[1, 2, 2, 13].Style.Font.Bold = true;
                    sheet.Cells[1, 2, 3, 13].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    sheet.Cells[1, 2, 3, 13].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    //標題列
                    sheet.Cells[4, 1, 4, 2].Value = "單位\\項目";
                    sheet.Cells[4, 1, 4, 2].Merge = true;
                    sheet.Cells[4, 3].Value = "酒後駕車35";
                    sheet.Cells[4, 4].Value = "闖紅燈53-1";
                    sheet.Cells[4, 5].Value = "嚴重超速40公里以上 33 40 43";
                    sheet.Cells[4, 6].Value = "逆向行駛 45 33";
                    sheet.Cells[4, 7].Value = "轉彎未依規定 48.1";
                    sheet.Cells[4, 8].Value = "蛇行惡意逼車(快速道路、一般道路)33 43";
                    sheet.Cells[4, 9].Value = "行駛路肩(快速道路)33";
                    sheet.Cells[4, 10].Value = "大型車慢速車未依規定行駛外車道(快速道路)33";
                    sheet.Cells[4, 11].Value = "機車行駛禁行機車道45-13";
                    sheet.Cells[4, 12].Value = "機車不依兩段式左轉48.1.2";
                    sheet.Cells[4, 13].Value = "合計";
                    Dictionary<string, int> unit_title = new Dictionary<string, int>
                    {
                        {"苗栗分局", 5},
                        {"竹南分局", 7},
                        {"頭份分局", 9},
                        {"通霄分局", 11},
                        {"大湖分局", 13},
                        {"保安隊", 15},
                        {"交通隊", 17},
                        {"苗栗拖吊場", 19},
                        {"竹南拖吊場", 21},
                        {"總計", 23},
                    };
                    Dictionary<string, int> unit_title2 = new Dictionary<string, int>();

                    foreach (KeyValuePair<string, int> pair in unit_title)
                    {
                        string title = pair.Key;
                        int row = pair.Value;
                        sheet.Cells[row, 1, row + 1, 1].Value = title;
                        sheet.Cells[row, 1, row + 1, 1].Merge = true;
                        sheet.Cells[row, 1, row + 1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        sheet.Cells[row, 1, row + 1, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                        sheet.Cells[row, 2].Value = "現場攔停";
                        sheet.Cells[row + 1, 2].Value = "逕行舉發";
                        sheet.Cells[row, 3, row, 13].Value = 0;
                        sheet.Cells[row + 1, 3, row + 1, 13].Value = 0;
                    }


                    foreach (DataRow data_row in ds.Tables["data1"].Rows)
                    {

                        if (unit_title.ContainsKey(data_row["單位"].ToString()))
                        {
                            int row = unit_title[data_row["單位"].ToString()];
                            if (data_row["類型"].ToString() == "逕行舉發")
                                row++;
                            //sheet.Cells[row, 1].Value = data_row["單位"].ToString();
                            //sheet.Cells[row, 2].Value = data_row["類型"].ToString();
                            sheet.Cells[row, 3].Value = int.Parse(data_row["酒後駕車35"].ToString());
                            sheet.Cells[row, 4].Value = int.Parse(data_row["闖紅燈53-1"].ToString());
                            sheet.Cells[row, 5].Value = int.Parse(data_row["嚴重超速40公里以上 33 40 43"].ToString());
                            sheet.Cells[row, 6].Value = int.Parse(data_row["逆向行駛 45 33"].ToString());
                            sheet.Cells[row, 7].Value = int.Parse(data_row["轉彎未依規定 48.1"].ToString());
                            sheet.Cells[row, 8].Value = int.Parse(data_row["蛇行惡意逼車(快速道路、一般道路)33 43"].ToString());
                            sheet.Cells[row, 9].Value = int.Parse(data_row["行駛路肩(快速道路)33"].ToString());
                            sheet.Cells[row, 10].Value = int.Parse(data_row["大型車慢速車未依規定行駛外車道(快速道路)33"].ToString());
                            sheet.Cells[row, 11].Value = int.Parse(data_row["機車行駛禁行機車道45-13"].ToString());
                            sheet.Cells[row, 12].Value = int.Parse(data_row["機車不依兩段式左轉48.1.2"].ToString());
                            sheet.Cells[row, 13].Value = int.Parse(data_row["合計"].ToString());

                        }
                        else
                        {
                            if (unit_title2.ContainsKey(data_row["單位"].ToString()))
                            {
                                int row = unit_title2[data_row["單位"].ToString()];
                                if (data_row["類型"].ToString() == "逕行舉發")
                                {
                                    row++;
                                    sheet.Cells[row, 3].Value = int.Parse(data_row["酒後駕車35"].ToString());
                                    sheet.Cells[row, 4].Value = int.Parse(data_row["闖紅燈53-1"].ToString());
                                    sheet.Cells[row, 5].Value = int.Parse(data_row["嚴重超速40公里以上 33 40 43"].ToString());
                                    sheet.Cells[row, 6].Value = int.Parse(data_row["逆向行駛 45 33"].ToString());
                                    sheet.Cells[row, 7].Value = int.Parse(data_row["轉彎未依規定 48.1"].ToString());
                                    sheet.Cells[row, 8].Value = int.Parse(data_row["蛇行惡意逼車(快速道路、一般道路)33 43"].ToString());
                                    sheet.Cells[row, 9].Value = int.Parse(data_row["行駛路肩(快速道路)33"].ToString());
                                    sheet.Cells[row, 10].Value = int.Parse(data_row["大型車慢速車未依規定行駛外車道(快速道路)33"].ToString());
                                    sheet.Cells[row, 11].Value = int.Parse(data_row["機車行駛禁行機車道45-13"].ToString());
                                    sheet.Cells[row, 12].Value = int.Parse(data_row["機車不依兩段式左轉48.1.2"].ToString());
                                    sheet.Cells[row, 13].Value = int.Parse(data_row["合計"].ToString());
                                }
                                else
                                {
                                    sheet.Cells[row, 3].Value = int.Parse(data_row["酒後駕車35"].ToString());
                                    sheet.Cells[row, 4].Value = int.Parse(data_row["闖紅燈53-1"].ToString());
                                    sheet.Cells[row, 5].Value = int.Parse(data_row["嚴重超速40公里以上 33 40 43"].ToString());
                                    sheet.Cells[row, 6].Value = int.Parse(data_row["逆向行駛 45 33"].ToString());
                                    sheet.Cells[row, 7].Value = int.Parse(data_row["轉彎未依規定 48.1"].ToString());
                                    sheet.Cells[row, 8].Value = int.Parse(data_row["蛇行惡意逼車(快速道路、一般道路)33 43"].ToString());
                                    sheet.Cells[row, 9].Value = int.Parse(data_row["行駛路肩(快速道路)33"].ToString());
                                    sheet.Cells[row, 10].Value = int.Parse(data_row["大型車慢速車未依規定行駛外車道(快速道路)33"].ToString());
                                    sheet.Cells[row, 11].Value = int.Parse(data_row["機車行駛禁行機車道45-13"].ToString());
                                    sheet.Cells[row, 12].Value = int.Parse(data_row["機車不依兩段式左轉48.1.2"].ToString());
                                    sheet.Cells[row, 13].Value = int.Parse(data_row["合計"].ToString());
                                }
                            }
                            else
                            {
                                unit_title2[data_row["單位"].ToString()] = (unit_title2.Count() + 1) * 2 + 3;
                                int row = unit_title2[data_row["單位"].ToString()];
                                sheet.Cells[row, 1, row + 1, 1].Value = data_row["單位"].ToString();
                                sheet.Cells[row, 1, row + 1, 1].Merge = true;
                                sheet.Cells[row, 1, row + 1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                sheet.Cells[row, 1, row + 1, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                sheet.Cells[row, 2].Value = "現場攔停";
                                sheet.Cells[row + 1, 2].Value = "逕行舉發";
                                sheet.Cells[row, 3, row, 13].Value = 0;
                                sheet.Cells[row + 1, 3, row + 1, 13].Value = 0;
                                if (data_row["類型"].ToString() == "逕行舉發")
                                {
                                    row++;
                                    sheet.Cells[row, 3].Value = int.Parse(data_row["酒後駕車35"].ToString());
                                    sheet.Cells[row, 4].Value = int.Parse(data_row["闖紅燈53-1"].ToString());
                                    sheet.Cells[row, 5].Value = int.Parse(data_row["嚴重超速40公里以上 33 40 43"].ToString());
                                    sheet.Cells[row, 6].Value = int.Parse(data_row["逆向行駛 45 33"].ToString());
                                    sheet.Cells[row, 7].Value = int.Parse(data_row["轉彎未依規定 48.1"].ToString());
                                    sheet.Cells[row, 8].Value = int.Parse(data_row["蛇行惡意逼車(快速道路、一般道路)33 43"].ToString());
                                    sheet.Cells[row, 9].Value = int.Parse(data_row["行駛路肩(快速道路)33"].ToString());
                                    sheet.Cells[row, 10].Value = int.Parse(data_row["大型車慢速車未依規定行駛外車道(快速道路)33"].ToString());
                                    sheet.Cells[row, 11].Value = int.Parse(data_row["機車行駛禁行機車道45-13"].ToString());
                                    sheet.Cells[row, 12].Value = int.Parse(data_row["機車不依兩段式左轉48.1.2"].ToString());
                                    sheet.Cells[row, 13].Value = int.Parse(data_row["合計"].ToString());
                                }
                                else
                                {
                                    sheet.Cells[row, 3].Value = int.Parse(data_row["酒後駕車35"].ToString());
                                    sheet.Cells[row, 4].Value = int.Parse(data_row["闖紅燈53-1"].ToString());
                                    sheet.Cells[row, 5].Value = int.Parse(data_row["嚴重超速40公里以上 33 40 43"].ToString());
                                    sheet.Cells[row, 6].Value = int.Parse(data_row["逆向行駛 45 33"].ToString());
                                    sheet.Cells[row, 7].Value = int.Parse(data_row["轉彎未依規定 48.1"].ToString());
                                    sheet.Cells[row, 8].Value = int.Parse(data_row["蛇行惡意逼車(快速道路、一般道路)33 43"].ToString());
                                    sheet.Cells[row, 9].Value = int.Parse(data_row["行駛路肩(快速道路)33"].ToString());
                                    sheet.Cells[row, 10].Value = int.Parse(data_row["大型車慢速車未依規定行駛外車道(快速道路)33"].ToString());
                                    sheet.Cells[row, 11].Value = int.Parse(data_row["機車行駛禁行機車道45-13"].ToString());
                                    sheet.Cells[row, 12].Value = int.Parse(data_row["機車不依兩段式左轉48.1.2"].ToString());
                                    sheet.Cells[row, 13].Value = int.Parse(data_row["合計"].ToString());
                                }

                            }
                        }

                    }

                    sheet.Cells[4, 1, 24, 13].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                    sheet.Cells[4, 1, 24, 13].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                    sheet.Cells[4, 1, 24, 13].Style.Border.Left.Style = ExcelBorderStyle.Thin;
                    sheet.Cells[4, 1, 24, 13].Style.Border.Right.Style = ExcelBorderStyle.Thin;

                    sheet.Cells[4, 1, unit_title2.Count() * 2 + 4, 13].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                    sheet.Cells[4, 1, unit_title2.Count() * 2 + 4, 13].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                    sheet.Cells[4, 1, unit_title2.Count() * 2 + 4, 13].Style.Border.Left.Style = ExcelBorderStyle.Thin;
                    sheet.Cells[4, 1, unit_title2.Count() * 2 + 4, 13].Style.Border.Right.Style = ExcelBorderStyle.Thin;


                    sheet.Column(1).Width = 25;
                    byte[] b = ep.GetAsByteArray();
                    string filename = "加強取締重大交通違規.xlsx";
                    Response.Clear();
                    Response.Buffer = true;
                    Response.AddHeader("content-disposition", "attachment;filename=" + filename);
                    Response.Charset = "";
                    Response.ContentType = "application/excel";
                    Response.BinaryWrite(b);
                    Response.Flush();
                    Response.End();
                }
            }
        }
    }
}