頁面到處EXCEL

 頁面到處EXCEL

導出EXCEL#region 導出EXCEL
        /**//// <summary>
        /// 導出EXCEL
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnExcel_Click(object sender, Infragistics.WebUI.WebDataInput.ButtonEventArgs e)
        ...{
            string strExpNo;
            strExpNo = txtExp_No.Text.Trim();
            string strFilePath = Server.MapPath(@"..FileTemplate");

            CFPIA5008 objCFPIA5008 = new CFPIA5008(m_objConnection);
            OWC11.SpreadsheetClass xlsheet = new OWC11.SpreadsheetClass();

            xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, 9]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[1, 1] = "跟款催繳查詢";

            xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, 14]).Font.set_Bold(true);
            xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, 14]).set_HorizontalAlignment(OWC11.XlHAlign.xlHAlignCenter);
            xlsheet.get_Range(xlsheet.Cells[2, 1], xlsheet.Cells[2, 14]).Font.set_Bold(true);
            xlsheet.get_Range(xlsheet.Cells[2, 1], xlsheet.Cells[2, 14]).set_HorizontalAlignment(OWC11.XlHAlign.xlHAlignCenter);

            xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, 14]).Font.set_Size(14);
            xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, 9]).set_ColumnWidth(15);
            xlsheet.ActiveSheet.Cells[2, 1] = "經辦別";
            xlsheet.ActiveSheet.Cells[2, 2] = "貨款到期日";
            xlsheet.ActiveSheet.Cells[2, 3] = "出口案號";
            xlsheet.ActiveSheet.Cells[2, 4] = "客戶代號";
            xlsheet.ActiveSheet.Cells[2, 5] = "款項別";
            xlsheet.ActiveSheet.Cells[2, 6] = "金額";
            xlsheet.ActiveSheet.Cells[2, 7] = "收款日";
            xlsheet.ActiveSheet.Cells[2, 8] = "繳款日";
            xlsheet.ActiveSheet.Cells[2, 9] = "逾期日數";


            int Rows = UltraWebGrid1.Rows.Count + 3;
            xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[Rows, 9]).Borders.set_LineStyle(XlLineStyle.xlContinuous);
            for (int i = 0; i < UltraWebGrid1.Rows.Count; i++)
            ...{
                xlsheet.ActiveSheet.Cells[i + 3, 1] = UltraWebGrid1.Rows[i].Cells.FromKey("UserId").Text;
                xlsheet.ActiveSheet.Cells[i + 3, 2] = UltraWebGrid1.Rows[i].Cells.FromKey("BlDate").Text;
                xlsheet.ActiveSheet.Cells[i + 3, 3] = UltraWebGrid1.Rows[i].Cells.FromKey("ExpNo").Text;
                xlsheet.ActiveSheet.Cells[i + 3, 4] = UltraWebGrid1.Rows[i].Cells.FromKey("CustId").Text;
                xlsheet.ActiveSheet.Cells[i + 3, 5] = UltraWebGrid1.Rows[i].Cells.FromKey("CAID").Text;
                xlsheet.ActiveSheet.Cells[i + 3, 6] = UltraWebGrid1.Rows[i].Cells.FromKey("Sum").Text;
                xlsheet.ActiveSheet.Cells[i + 3, 7] = UltraWebGrid1.Rows[i].Cells.FromKey("AccDate").Text;
                xlsheet.ActiveSheet.Cells[i + 3, 8] = UltraWebGrid1.Rows[i].Cells.FromKey("PayDate").Text;
                xlsheet.ActiveSheet.Cells[i + 3, 9] = UltraWebGrid1.Rows[i].Cells.FromKey("OverDate").Text;
            }

            try
            ...{
                string desFileName = "跟款催繳查詢" + "_" + "All" + "_" + System.DateTime.Now.ToString("yyMMddHHmmssff") + ".xls";
                string strPathFileName = strFilePath + desFileName;
                string path = strPathFileName;
                FileInfo file = new FileInfo(path);
                if (Directory.Exists(strFilePath) == false)
                ...{
                    Directory.CreateDirectory(strFilePath);
                }
                xlsheet.Export(strPathFileName,     OWC11.SheetExportActionEnum.ssExportActionNone,OWC11.SheetExportFormat.ssExportXMLSpreadsheet);
                Response.Clear();
                Response.ContentEncoding = System.Text.Encoding.UTF8;
                Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
                Response.AddHeader("Content-Length", file.Length.ToString());
                Response.ContentType = "application/ms-excel";
                Response.WriteFile(file.FullName);
                file = null;
                m_objUCommon.showMessage("導出成功", this.Page);
            }
            catch (Exception ex)
            ...{
                m_objUCommon.showMessage(ex.Message, this.Page);
                return;
            }
        }
        #endregion