protected void Page_Load(object sender, EventArgs e) { CtlLib.SetUser(Session["APP_DBUSER"].ToString()); //string l_pk = Request["master_pk"]; string TemplateFile = "rpt_bisc00020_O_4.xls"; string TempFile = "../../../../system/temp/rpt_bisc00020_O_4_" + Session["User_ID"].ToString() + ".xls"; TemplateFile = Server.MapPath(TemplateFile); TempFile = Server.MapPath(TempFile); //Create a new workbook IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile); //Add worksheet IWorksheet exSheet = exBook.Worksheets[1]; IRange range; //bind data to excel file //string para = "'" + l_pk + "'"; string p_from_date = Request["p_from_date"]; string p_to_date = Request["p_to_date"]; string p_wh_type = Request["p_wh_type"]; string p_tin_warehouse_pk = Request["p_wh"]; string p_use_yn = Request["p_use_yn"]; string p_item_group_pk = Request["p_item_group_pk"]; string p_item = Request["p_item"]; string p_in_out_yn = Request["p_in_out_yn"]; string p_wh_name = Request["p_wh_name"]; string para = "'" + p_from_date + "','" + p_to_date + "','" + p_wh_type + "','" + p_tin_warehouse_pk + "','" + p_use_yn + "','" + p_item_group_pk + "','" + p_item + "','" + p_in_out_yn + "'"; DataTable dt = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00020_O_4", "'" + Session["User_ID"].ToString() + "'"); DataTable dt1 = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00020_O_1", para); //--set-----DataBind--in---Master--- if (!string.IsNullOrEmpty(dt.Rows[0]["pic"].ToString())) { exSheet.Cells[1, 1].Select(); byte[] MyData = new byte[0]; MyData = (byte[])dt.Rows[0]["pic"]; MemoryStream stream = new MemoryStream(MyData); Bitmap b = new Bitmap(stream); exSheet.Shapes.AddPicture(ResizeImage(b, 90, 59)); } exSheet.Cells[6, 1].Value = "W/H Name : " + p_wh_name; exSheet.Cells[2, 3].Value = dt.Rows[0]["partner_name"].ToString(); exSheet.Cells[3, 3].Value = dt.Rows[0]["addr1"].ToString(); exSheet.Cells[4, 3].Value = "Tax code:" + dt.Rows[0]["tax_code"].ToString(); exSheet.Cells[2, 6].Value = "Tell:" + dt.Rows[0]["phone_no"].ToString(); exSheet.Cells[3, 6].Value = "Fax:" + dt.Rows[0]["fax_no"].ToString(); exSheet.Cells[4, 6].Value = "Print date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm"); exSheet.Cells[6, 6].Value = System.DateTime.ParseExact(p_from_date, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy") + "-" + System.DateTime.ParseExact(p_to_date, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy"); for (int l_addrow = 1; l_addrow < dt1.Rows.Count; l_addrow++) { exSheet.Range["A9"].Rows.EntireRow.Insert();//insert row new of sheet } double total = 0; for (int l_row = 0; l_row <= dt1.Rows.Count - 1; l_row++) { total = total + CommondLib.ConvertToDbl(dt1.Rows[l_row]["begin_qty"].ToString()); exSheet.Cells[8 + l_row, 1].Value = l_row + 1; exSheet.Cells[8 + l_row, 2].Value = dt1.Rows[l_row]["item_code"]; exSheet.Cells[8 + l_row, 3].Value = dt1.Rows[l_row]["item_name"]; exSheet.Cells[8 + l_row, 4].Value = dt1.Rows[l_row]["uom"]; if (dt1.Rows[l_row]["begin_qty"].ToString() != "") { exSheet.Cells[8 + l_row, 5].Value = CommondLib.ConvertToDbl(dt1.Rows[l_row]["begin_qty"].ToString()); } } exSheet.Cells[8 + dt1.Rows.Count, 5].Value = total; if (File.Exists(TempFile)) { File.Delete(TempFile); } // hide row firts range = exSheet.Range["A1"]; // hide row A5 range.Rows.Hidden = true; // font bold header exBook.SaveAs(TempFile); System.IO.FileInfo file = new System.IO.FileInfo(TempFile); Response.Clear(); Response.Charset = "UTF-8"; Response.ContentEncoding = System.Text.Encoding.UTF8; //Add header, give a default file name for "File Download/Store as" Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name)); //Add header, set file size to enable browser display download progress Response.AddHeader("Content-Length", file.Length.ToString()); //Set the return string is unavailable reading for client, and must be downloaded Response.ContentType = "application/ms-exSheet"; //Response.ContentType = "application/pdf"; //Send file string to client Response.WriteFile(TempFile); //Response.WriteFile(pdfFilePath); //Stop execute Response.End(); }
protected void Page_Load(object sender, EventArgs e) { CtlLib.SetUser(Session["APP_DBUSER"].ToString()); string l_repayment_pk = Request["p_tht_repayment_pk"]; string p_employee_pk = Request["p_employee_pk"]; string TemplateFile = "rpt_60250170_Guest_Invoice.xls"; string TempFile = "../../../../system/temp/rpt_60250170_Guest_Invoice_" + Session["User_ID"].ToString() + ".xls"; TemplateFile = Server.MapPath(TemplateFile); TempFile = Server.MapPath(TempFile); //Create a new workbook IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile); //Add worksheet IWorksheet exSheet = exBook.Worksheets[1]; IRange range; //bind data to excel file string para = ""; DataTable dt; para = "'" + l_repayment_pk + "'"; dt = CtlLib.TableReadOpenCursor("ht_rpt_60250170_repayment", para); string SQL = ""; SQL = "select max(full_name) from thr_employee where pk='" + p_employee_pk + "'"; DataTable dtEmp = new DataTable(); dtEmp = CtlLib.TableReadOpen(SQL); exSheet.Cells[33, 5].Value = dtEmp.Rows[0][0].ToString(); //full_name login sofware //-----header--- //---end--header--- //--set-----DataBind--in---Master--- if (dt.Rows.Count == 0) { exSheet.Cells[8, 4].Value = "There is not data!"; } if (dt.Rows.Count > 0) { string l_status_title = "GUEST INVOICE"; exSheet.Cells[7, 3].Value = l_status_title; //exSheet.Cells[ 8, 1].Value = dt.Rows[0]["confirm_msg"].ToString(); exSheet.Cells[9, 3].Value = dt.Rows[0]["guest_name"].ToString(); exSheet.Cells[9, 6].Value = dt.Rows[0]["print_time"].ToString(); exSheet.Cells[10, 3].Value = dt.Rows[0]["partner_name"].ToString(); exSheet.Cells[10, 6].Value = dt.Rows[0]["room_no"].ToString(); //exSheet.Cells[10, 7].Value = l_folio_type; exSheet.Cells[11, 3].Value = dt.Rows[0]["customer_address"].ToString(); exSheet.Cells[11, 6].Value = dt.Rows[0]["invoice_no"].ToString(); exSheet.Cells[12, 3].Value = dt.Rows[0]["tax_code"].ToString(); exSheet.Cells[12, 6].Value = dt.Rows[0]["arrival_dt"].ToString(); //exSheet.Cells[13, 3].Value = dt.Rows[0]["guest_name"].ToString(); //total guest exSheet.Cells[13, 6].Value = dt.Rows[0]["checkout_dt"].ToString(); exSheet.Cells[14, 4].Value = dt.Rows[0]["paymethod"].ToString(); exSheet.Cells[17, 5].Value = double.Parse(dt.Rows[0]["total_due_vnd_pp"].ToString()); // exSheet.Cells[17, 7].Value = float.Parse(dt.Rows[0]["total_due_usd_pp"].ToString()); //exSheet.Cells[18, 5].Value = dt.Rows[0]["paymethod"].ToString();TTDB VND //exSheet.Cells[18, 7].Value = dt.Rows[0]["paymethod"].ToString();TTDB USD exSheet.Cells[19, 5].Value = int.Parse(dt.Rows[0]["svc_amt"].ToString()); exSheet.Cells[19, 7].Value = float.Parse(dt.Rows[0]["svc_amt_usd"].ToString()); exSheet.Cells[20, 5].Value = int.Parse(dt.Rows[0]["total_vnd"].ToString()); exSheet.Cells[20, 7].Value = float.Parse(dt.Rows[0]["total_usd"].ToString()); exSheet.Cells[21, 5].Value = int.Parse(dt.Rows[0]["vat_amt"].ToString()); exSheet.Cells[21, 7].Value = float.Parse(dt.Rows[0]["vat_amt_usd"].ToString()); //exSheet.Cells[22, 5].Value = int.Parse(dt.Rows[0]["total_discount_amt"].ToString()); //exSheet.Cells[22, 7].Value = float.Parse(dt.Rows[0]["total_discount_amt_usd"].ToString()); //exSheet.Cells[22, 5].Value = int.Parse(dt.Rows[0]["advance_amt"].ToString()); //exSheet.Cells[22, 7].Value = float.Parse(dt.Rows[0]["advance_amt_usd"].ToString()); exSheet.Cells[23, 5].Value = int.Parse(dt.Rows[0]["total_due_end_vnd"].ToString()); exSheet.Cells[23, 7].Value = float.Parse(dt.Rows[0]["total_due_usd"].ToString()); exSheet.Cells[24, 3].Value = int.Parse(dt.Rows[0]["cash_vnd"].ToString()); exSheet.Cells[24, 5].Value = float.Parse(dt.Rows[0]["cash_usd"].ToString()); exSheet.Cells[25, 3].Value = int.Parse(dt.Rows[0]["credit_vnd"].ToString()); exSheet.Cells[25, 5].Value = float.Parse(dt.Rows[0]["credit_usd"].ToString()); if (int.Parse(dt.Rows[0]["cash_vnd"].ToString()) == 0 && float.Parse(dt.Rows[0]["cash_usd"].ToString()) == 0) { range = exSheet.Range["A24"]; range.Rows.Hidden = true; } if (int.Parse(dt.Rows[0]["credit_vnd"].ToString()) == 0 && float.Parse(dt.Rows[0]["credit_usd"].ToString()) == 0) { range = exSheet.Range["A25"]; range.Rows.Hidden = true; } string inword_amt, inword_amt_usd; inword_amt = CommondLib.Num2VNText(dt.Rows[0]["total_due_end_vnd"].ToString(), "VND"); inword_amt_usd = CommondLib.Num2EngText(dt.Rows[0]["total_due_usd"].ToString(), "USD"); exSheet.Cells[26, 3].Value = inword_amt.ToString(); exSheet.Cells[27, 3].Value = inword_amt_usd.ToString(); } /*if (l_svc < 1)//not svc then hide row svc * { * range = exSheet.Range["A15"]; * // hide row A15 * range.Rows.Hidden = true; * }*/ //---------------------- ////end-set-----DataBind--in---Master--- for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++) { exSheet.Range["A16"].Rows.EntireRow.Insert();//insert row new of sheet } //set date title of year //exSheet.Cells[2, 1].Value = "Income Statement Year" + " " + l_year; //set data to detail. // loop detail not percent int l_cnt = dt.Rows.Count; if (dt.Rows.Count > 0) { for (int l_row = 1; l_row <= dt.Rows.Count; l_row++) { for (int col = 0; col <= 6; col++) { if (col == 1 || col == 2 || col == 3) { exSheet.Cells[15 + l_row, col + 1].Value = dt.Rows[l_row - 1][col].ToString(); exSheet.Cells[l_row + 15, col + 1].HorizontalAlignment = XlHAlign.xlHAlignLeft; exSheet.Range[16, col + 1, l_row + 15, 7].Font.Bold = false; } else { if (col == 6) { if (dt.Rows[l_row - 1][col].ToString() != "") { exSheet.Cells[15 + l_row, col + 1].Value = float.Parse(dt.Rows[l_row - 1][col].ToString()); exSheet.Cells[15, col + 1, l_row + 15, col + 1].NumberFormat = "#,##0.00"; exSheet.Cells[l_row + 15, col + 1].HorizontalAlignment = XlHAlign.xlHAlignRight; } } else { if (dt.Rows[l_row - 1][col].ToString() != "") { exSheet.Cells[15 + l_row, col + 1].Value = long.Parse(dt.Rows[l_row - 1][col].ToString()); exSheet.Cells[15, col + 1, l_row + 15, col + 1].NumberFormat = "#,##0"; exSheet.Cells[l_row + 15, col + 1].HorizontalAlignment = XlHAlign.xlHAlignRight; } } exSheet.Range[16, col + 1, l_row + 15, 7].Font.Bold = false; } //exSheet.Cells[l_row + 12, 1].HorizontalAlignment = XlHAlign.xlHAlignCenter; } } } // end loop detail percent if (File.Exists(TempFile)) { File.Delete(TempFile); } // hide row firts range = exSheet.Range["A1"]; // hide row A5 range.Rows.Hidden = true; // font bold header exBook.SaveAs(TempFile); //CtlLib.ExcelToPdf(TempFile); //string pdfFilePath = TempFile.Replace(".xls", ".pdf"); //write out to client broswer System.IO.FileInfo file = new System.IO.FileInfo(TempFile); //System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath); Response.Clear(); Response.Charset = "UTF-8"; Response.ContentEncoding = System.Text.Encoding.UTF8; //Add header, give a default file name for "File Download/Store as" Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name)); //Add header, set file size to enable browser display download progress Response.AddHeader("Content-Length", file.Length.ToString()); //Set the return string is unavailable reading for client, and must be downloaded Response.ContentType = "application/ms-exSheet"; //Response.ContentType = "application/pdf"; //Send file string to client Response.WriteFile(TempFile); //Response.WriteFile(pdfFilePath); //Stop execute Response.End(); }
protected void Page_Load(object sender, EventArgs e) { CtlLib.SetUser(Session["APP_DBUSER"].ToString()); string TemplateFile = "rpt_60250300_vat_invoice.xls"; string TempFile = "../../../../system/temp/rpt_60250300_vat_invoice_" + Session["User_ID"].ToString() + ".xls"; TemplateFile = Server.MapPath(TemplateFile); TempFile = Server.MapPath(TempFile); //Create a new workbook IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile); //Add worksheet IWorksheet exSheet = exBook.Worksheets[1]; IRange range; //bind data to excel file string p_tac_crca_pk = Request.QueryString["p_tac_crca_pk"]; string para = "'" + p_tac_crca_pk + "'"; DataTable dtMaster = CtlLib.TableReadOpenCursor("ac_rpt_60250300_vatinvoice_mst", para); DataTable dtDetail = CtlLib.TableReadOpenCursor("ac_rpt_60250300_vatinvoice_dtl", para); //set master data //day exSheet.Cells["D13"].Value = dtMaster.Rows[0]["dd"].ToString(); //month exSheet.Cells["E13"].Value = dtMaster.Rows[0]["mm"].ToString(); //year exSheet.Cells["G13"].Value = dtMaster.Rows[0]["yyyy"].ToString(); //guest name exSheet.Cells["D14"].Value = dtMaster.Rows[0]["recv_person"].ToString(); //customer name exSheet.Cells["D15"].Value = dtMaster.Rows[0]["customer_name"].ToString(); //tax code exSheet.Cells["D16"].Value = dtMaster.Rows[0]["taxcode"].ToString(); //customer address exSheet.Cells["C17"].Value = dtMaster.Rows[0]["addr1"].ToString(); //pay_method exSheet.Cells["D18"].Value = dtMaster.Rows[0]["pay_method"].ToString(); //sub net total exSheet.Cells["H33"].Value = double.Parse(dtMaster.Rows[0]["net_amt"].ToString()); //service charge amt exSheet.Cells["H34"].Value = double.Parse(dtMaster.Rows[0]["svc_amt"].ToString()); //vat amt exSheet.Cells["H35"].Value = double.Parse(dtMaster.Rows[0]["vat_amt"].ToString()); //total amt exSheet.Cells["H36"].Value = double.Parse(dtMaster.Rows[0]["total_amt"].ToString()); //VAT RATE exSheet.Cells["HC5"].Value = 10; //number to VN words exSheet.Cells["A39"].Value = CommondLib.Num2VNText(dtMaster.Rows[0]["total_amt"].ToString(), "VND"); //set detail data int l_pos = 22; int i = 0; for (i = 0; i < dtDetail.Rows.Count; i++) { //exSheet.Cells[l_pos+i,1].Value =i+1; exSheet.Cells[l_pos + i, 2].Value = dtDetail.Rows[i]["item_name"].ToString(); //item name //exSheet.Cells[l_pos+i,4].Value = dtDetail.Rows[i]["item_uom"].ToString(); //unit //exSheet.Cells[l_pos+i,5].Value = dtDetail.Rows[i]["qty"].ToString(); //qty //exSheet.Cells[l_pos+i,6].Value = dtDetail.Rows[i]["u_price"].ToString(); //price exSheet.Cells[l_pos + i, 8].Value = double.Parse(dtDetail.Rows[i]["amt"].ToString()); //net amt } if (File.Exists(TempFile)) { File.Delete(TempFile); } exBook.SaveAs(TempFile); //write out to client broswer System.IO.FileInfo file = new System.IO.FileInfo(TempFile); //System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath); Response.Clear(); Response.Charset = "UTF-8"; Response.ContentEncoding = System.Text.Encoding.UTF8; //Add header, give a default file name for "File Download/Store as" Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name)); //Add header, set file size to enable browser display download progress Response.AddHeader("Content-Length", file.Length.ToString()); //Set the return string is unavailable reading for client, and must be downloaded Response.ContentType = "application/ms-exSheet"; //Response.ContentType = "application/pdf"; //Send file string to client //Response.WriteFile(pdfFilePath); Response.WriteFile(TempFile); //Stop execute Response.End(); }
protected void Page_Load(object sender, EventArgs e) { CtlLib.SetUser(Session["APP_DBUSER"].ToString()); //CtlLib.SetUser("hr"); string p_mon = Request["p_mon"].ToString(); string p_reg_type = Request.QueryString["p_reg_type"].ToString(); string p_org_pk = Request.QueryString["p_org"].ToString(); string TemplateFile = "rpt_hris00600_0.xls"; string TempFile = "../../../system/temp/rpt_hris00600_0_" + Session["User_ID"].ToString() + ".xls"; TemplateFile = Server.MapPath(TemplateFile); TempFile = Server.MapPath(TempFile); //Create a new workbook IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile); //Add worksheet IWorksheet exSheet = exBook.Worksheets[1]; string SQL = "select code,v.CODE_NM " + "from vhr_hr_code v " + "where v.ID='HR0049' and nvl(v.tco_company_pk,'" + p_org_pk + "')=" + "(select a.tco_company_pk from tco_org a where a.del_if=0 and a.pk='" + p_org_pk + "')"; //Response.Write(SQL); //Response.End(); DataTable dt_com = CtlLib.TableReadOpen(SQL); SQL = " SELECT UPPER((select F.CODE_FNM from vhr_hr_code f where F.ID = 'HR0054' and F.CODE = V.CHAR_5)) title ,v.CODE_NM, " + " ti.FULL_NAME, " + " case when E.SEX = 'M' then substr(E.BIRTH_DT,1,4) else '' end BIRTH_DT_M, " + " case when E.SEX = 'F' then substr(E.BIRTH_DT,1,4) else '' end BIRTH_DT_F, " + " ti.SOCIAL_NO, " + " (case when TI.INS_REG_TYPE='02' and TI.DETAIL_INS_REG_TYPE='07' then (CASE WHEN ti.ST_SOCIAL_DT IS NOT NULL THEN (case when months_between(TO_DATE (ti.INS_MONTH, 'yyyymm'),TO_DATE (ti.ST_SOCIAL_DT, 'yyyymm')) >12 then '0-12' " + " else 0 || '-'||months_between(TO_DATE (ti.INS_MONTH, 'yyyymm'),TO_DATE (ti.ST_SOCIAL_DT, 'yyyymm')) end) ELSE NULL END) " + " else (CASE WHEN ti.ST_SOCIAL_DT IS NOT NULL THEN TRUNC(months_between(TO_DATE (ti.INS_MONTH, 'yyyymm'),TO_DATE (ti.ST_SOCIAL_DT, 'yyyymm')) / 12) ||'-' || MOD(months_between(TO_DATE (ti.INS_MONTH, 'yyyymm'),TO_DATE (ti.ST_SOCIAL_DT, 'yyyymm')),12) ELSE NULL END) " + " end) tg_thamgia, " + " ROUND (NVL (ti.AVERAGE_SALARY, 0), 0), " + " ti.CONDITION, " + " ti.THOI_DIEM, " + " TO_CHAR (TO_DATE (ti.FROM_DT, 'yyyymmdd'), 'dd/mm/yyyy') from_dt, " + " TO_CHAR (TO_DATE (ti.TO_DT, 'yyyymmdd'), 'dd/mm/yyyy') to_dt, " + " ROUND (NVL (ti.DAYS, 0), 2), " + " ROUND (NVL (ti.PROGRESSIVE, 0), 0), " + " ROUND (ti.INS_AMT, -2),'', " + " e.emp_id " + " FROM THR_INS_REGULATION ti, vhr_hr_code v, thr_employee e " + " WHERE ti.DEL_IF = 0 " + " AND e.del_if = 0 " + " AND e.pk = ti.thr_emp_pk " + " AND ti.DEL_IF = 0 " + " AND ti.INS_MONTH = '" + p_mon + "' " + " and decode('" + p_reg_type + "','ALL','ALL',ti.INS_REG_TYPE)='" + p_reg_type + "' " + " AND v.ID = 'HR0055' " + " AND v.CODE = ti.DETAIL_INS_REG_TYPE " + "ORDER BY ( (SELECT F.CODE " + " FROM vhr_hr_code f " + " WHERE F.ID = 'HR0054' AND F.CODE = V.CHAR_5)), NVL (v.num_1, 0), " + " v.code_nm, " + " e.emp_id,ti.FULL_NAME, " + " ti.from_dt "; DataTable dt_emp = CtlLib.TableReadOpen(SQL); int irow = dt_emp.Rows.Count; int icol = dt_emp.Columns.Count; //Response.Write(SQL); //Response.End(); if (dt_emp.Rows.Count == 0) { Response.Write("There is no data to show"); Response.End(); } //Khai bao bien int i, j = 0, k = 0, pos = 20; int count = 0; string[] atype = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K" }; string[] btype = new string[] { "I", "II", "III", "IV", "V", "VI", "VII", "VIII", "IX", "X", "XI" }; Double total_day = 0, total_prog = 0, total_amt = 0, grand_total = 0; int a = 0, b = 0; string quy = ""; int month; //Insert new row for (i = 0; i < irow - 1; i++) { if (dt_emp.Rows[i][0].ToString() != dt_emp.Rows[i + 1][0].ToString()) { a++; } if (dt_emp.Rows[i][1].ToString() != dt_emp.Rows[i + 1][1].ToString()) { b++; } } for (i = 0; i < irow + a + b; i++) { exSheet.Range[pos + 1, 1].Rows.EntireRow.Insert(); } month = int.Parse(p_mon.Substring(4, 2).ToString()); if (month >= 1 && month <= 3) { quy = "01"; } else if (month >= 4 && month <= 6) { quy = "02"; } else if (month >= 7 && month <= 9) { quy = "03"; } else { quy = "04"; } exSheet.Cells["A1"].Value = "Tên cơ quan (đơn vị): " + dt_com.Rows[0][1].ToString(); exSheet.Cells["A2"].Value = "Mã đơn vị: " + dt_com.Rows[1][1].ToString(); exSheet.Cells["A3"].Value = "Điện thoại: " + dt_com.Rows[12][1].ToString(); exSheet.Cells["A6"].Value = "Tháng " + p_mon.Substring(4, 2).ToString() + " quý " + quy + " năm " + p_mon.Substring(0, 4).ToString(); exSheet.Cells["A7"].Value = "Số hiệu tài khoản: " + dt_com.Rows[8][1].ToString() + " mở tại: " + dt_com.Rows[9][1].ToString(); //Show data for (i = 0; i < irow; i++) { //total_day = total_day + Double.Parse(dt_emp.Rows[i][12].ToString()); //total_prog = total_prog + Double.Parse(dt_emp.Rows[i][13].ToString()); total_amt = total_amt + Double.Parse(dt_emp.Rows[i][14].ToString()); grand_total = grand_total + Double.Parse(dt_emp.Rows[i][14].ToString()); //Dong dau tien if (i == 0) { exSheet.Cells[pos + i - 2, 1].Value = atype[j].ToString(); exSheet.Cells[pos + i - 1, 1].Value = btype[k].ToString(); exSheet.Cells[pos + i - 2, 2].Value = dt_emp.Rows[i][0].ToString(); exSheet.Cells[pos + i - 1, 2].Value = dt_emp.Rows[i][1].ToString(); k++; } count++; exSheet.Cells[pos + i, 1].Value = count; for (int col = 2; col < icol; col++) { if (col == 7 || (col >= 12 && col <= 14)) { exSheet.Cells[pos + i, col].Value = Double.Parse(dt_emp.Rows[i][col].ToString()); } else { exSheet.Cells[pos + i, col].Value = dt_emp.Rows[i][col].ToString(); } } if (i == irow - 1) //Dong cuoi cung { exSheet.Cells[pos + i + 1, 2].Value = "Tổng cộng "; //exSheet.Cells[pos + i + 1, 13].Value = total_day; //exSheet.Cells[pos + i + 1, 14].Value = total_prog; exSheet.Cells[pos + i + 1, 14].Value = total_amt; exSheet.Cells[pos + i + 1, 1, pos + i + 1, 15].Rows.Font.Bold = true; pos = pos + 1; total_day = 0; total_prog = 0; total_amt = 0; } else { if (dt_emp.Rows[i][0].ToString() != dt_emp.Rows[i + 1][0].ToString()) { exSheet.Cells[pos + i + 1, 2].Value = "Tổng cộng "; //exSheet.Cells[pos + i + 1, 13].Value = total_day; //exSheet.Cells[pos + i + 1, 14].Value = total_prog; exSheet.Cells[pos + i + 1, 14].Value = total_amt; exSheet.Cells[pos + i + 1, 1, pos + i + 1, 15].Rows.Font.Bold = true; j++; exSheet.Cells[pos + i + 2, 1].Value = atype[j].ToString(); exSheet.Cells[pos + i + 2, 2].Value = dt_emp.Rows[i + 1][0].ToString(); exSheet.Cells[pos + i + 2, 1, pos + i + 1, 11].Rows.Font.Bold = true; pos = pos + 2; k = 0; count = 0; total_day = 0; total_prog = 0; total_amt = 0; } if (dt_emp.Rows[i][1].ToString() != dt_emp.Rows[i + 1][1].ToString()) { exSheet.Cells[pos + i + 1, 1].Value = btype[k].ToString(); exSheet.Cells[pos + i + 1, 2].Value = dt_emp.Rows[i + 1][1].ToString(); exSheet.Cells[pos + i + 1, 1, pos + i + 1, 11].Rows.Font.Bold = true; pos = pos + 1; k++; count = 0; } } } pos = 20 + irow + a + b + 20; //cộng thêm phần điều chỉnh exSheet.Cells["F" + (pos + 3)].Value = grand_total; exSheet.Cells["C" + (pos + 6)].Value = "(" + string.Format("Viết bằng chữ: {0}", CommondLib.Num2VNText(grand_total.ToString(), "VND")) + ")"; exSheet.Cells["K" + (pos + 8)].Value = string.Format("Ngày {0} tháng {1} năm {2}", DateTime.Now.Day, DateTime.Now.Month, DateTime.Now.Year); exSheet.Cells["a" + (pos + 15)].Value = dt_com.Rows[5][1].ToString(); exSheet.Cells["g" + (pos + 15)].Value = dt_com.Rows[6][1].ToString(); exSheet.Cells["k" + (pos + 15)].Value = dt_com.Rows[7][1].ToString(); // end loop detail percent if (File.Exists(TempFile)) { File.Delete(TempFile); } // hide row firts //range = exSheet.Range["A1"]; // hide row A5 //range.Rows.Hidden = true; exBook.SaveAs(TempFile); //CtlLib.ExcelToPdf(TempFile); //string pdfFilePath = TempFile.Replace(".xls", ".pdf"); //write out to client broswer System.IO.FileInfo file = new System.IO.FileInfo(TempFile); //System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath); Response.Clear(); Response.Charset = "UTF-8"; Response.ContentEncoding = System.Text.Encoding.UTF8; //Add header, give a default file name for "File Download/Store as" Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name)); //Add header, set file size to enable browser display download progress Response.AddHeader("Content-Length", file.Length.ToString()); //Set the return string is unavailable reading for client, and must be downloaded Response.ContentType = "application/ms-exSheet"; //Response.ContentType = "application/pdf"; //Send file string to client Response.WriteFile(TempFile); //Response.WriteFile(pdfFilePath); //Stop execute Response.End(); }