protected void Page_Load(object sender, EventArgs e) { CtlLib.SetUser(Session["APP_DBUSER"].ToString()); string l_date = Request["p_Month"]; string l_week = Request["p_week"]; string TemplateFile = "rpt_60300010_WeeklyRevenue.xls"; string TempFile = "../../../../system/temp/rpt_60300010_WeeklyRevenue_" + 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_date + "','" + l_week + "'"; dt = CtlLib.TableReadOpenCursor("rpt_60300010_rev_weekly_usd", para); //-----header--- exSheet.Cells[2, 3].Value = dt.Rows[0]["months"].ToString(); exSheet.Cells[2, 6].Value = l_week; exSheet.Cells[4, 4].Value = dt.Rows[0]["mon_d"].ToString(); exSheet.Cells[4, 6].Value = dt.Rows[0]["tue_d"].ToString(); exSheet.Cells[4, 8].Value = dt.Rows[0]["wed_d"].ToString(); exSheet.Cells[4, 10].Value = dt.Rows[0]["thu_d"].ToString(); exSheet.Cells[4, 12].Value = dt.Rows[0]["fri_d"].ToString(); exSheet.Cells[4, 14].Value = dt.Rows[0]["sat_d"].ToString(); exSheet.Cells[4, 16].Value = dt.Rows[0]["sun_d"].ToString(); //---end--header--- //--set-----DataBind--in---Master--- exSheet.Cells[7, 4].Value = float.Parse(dt.Rows[0]["d8"].ToString()); exSheet.Cells[7, 5].Value = float.Parse(dt.Rows[0]["d1"].ToString()); exSheet.Cells[7, 6].Value = float.Parse(dt.Rows[0]["d9"].ToString()); exSheet.Cells[7, 7].Value = float.Parse(dt.Rows[0]["d2"].ToString()); exSheet.Cells[7, 8].Value = float.Parse(dt.Rows[0]["d10"].ToString()); exSheet.Cells[7, 9].Value = float.Parse(dt.Rows[0]["d3"].ToString()); exSheet.Cells[7, 10].Value = float.Parse(dt.Rows[0]["d11"].ToString()); exSheet.Cells[7, 11].Value = float.Parse(dt.Rows[0]["d4"].ToString()); exSheet.Cells[7, 12].Value = float.Parse(dt.Rows[0]["d12"].ToString()); exSheet.Cells[7, 13].Value = float.Parse(dt.Rows[0]["d5"].ToString()); exSheet.Cells[7, 14].Value = float.Parse(dt.Rows[0]["d13"].ToString()); exSheet.Cells[7, 15].Value = float.Parse(dt.Rows[0]["d6"].ToString()); exSheet.Cells[7, 16].Value = float.Parse(dt.Rows[0]["d14"].ToString()); exSheet.Cells[7, 17].Value = float.Parse(dt.Rows[0]["d7"].ToString()); exSheet.Cells[7, 18].Value = float.Parse(dt.Rows[0]["sum_guest_qty"].ToString()); exSheet.Cells[7, 19].Value = float.Parse(dt.Rows[0]["mm_sum"].ToString()); exSheet.Cells[7, 20].Value = float.Parse(dt.Rows[0]["avg_guest_qty"].ToString()); exSheet.Cells[7, 21].Value = float.Parse(dt.Rows[0]["avg_amount_qty"].ToString()); exSheet.Cells[8, 4].Value = float.Parse(dt.Rows[1]["d8"].ToString()); exSheet.Cells[8, 5].Value = float.Parse(dt.Rows[1]["d1"].ToString()); exSheet.Cells[8, 6].Value = float.Parse(dt.Rows[1]["d9"].ToString()); exSheet.Cells[8, 7].Value = float.Parse(dt.Rows[1]["d2"].ToString()); exSheet.Cells[8, 8].Value = float.Parse(dt.Rows[1]["d10"].ToString()); exSheet.Cells[8, 9].Value = float.Parse(dt.Rows[1]["d3"].ToString()); exSheet.Cells[8, 10].Value = float.Parse(dt.Rows[1]["d11"].ToString()); exSheet.Cells[8, 11].Value = float.Parse(dt.Rows[1]["d4"].ToString()); exSheet.Cells[8, 12].Value = float.Parse(dt.Rows[1]["d12"].ToString()); exSheet.Cells[8, 13].Value = float.Parse(dt.Rows[1]["d5"].ToString()); exSheet.Cells[8, 14].Value = float.Parse(dt.Rows[1]["d13"].ToString()); exSheet.Cells[8, 15].Value = float.Parse(dt.Rows[1]["d6"].ToString()); exSheet.Cells[8, 16].Value = float.Parse(dt.Rows[1]["d14"].ToString()); exSheet.Cells[8, 17].Value = float.Parse(dt.Rows[1]["d7"].ToString()); exSheet.Cells[8, 18].Value = float.Parse(dt.Rows[1]["sum_guest_qty"].ToString()); exSheet.Cells[8, 19].Value = float.Parse(dt.Rows[1]["mm_sum"].ToString()); exSheet.Cells[8, 20].Value = float.Parse(dt.Rows[1]["avg_guest_qty"].ToString()); exSheet.Cells[8, 21].Value = float.Parse(dt.Rows[1]["avg_amount_qty"].ToString()); exSheet.Cells[9, 4].Value = float.Parse(dt.Rows[2]["d8"].ToString()); exSheet.Cells[9, 5].Value = float.Parse(dt.Rows[2]["d1"].ToString()); exSheet.Cells[9, 6].Value = float.Parse(dt.Rows[2]["d9"].ToString()); exSheet.Cells[9, 7].Value = float.Parse(dt.Rows[2]["d2"].ToString()); exSheet.Cells[9, 8].Value = float.Parse(dt.Rows[2]["d10"].ToString()); exSheet.Cells[9, 9].Value = float.Parse(dt.Rows[2]["d3"].ToString()); exSheet.Cells[9, 10].Value = float.Parse(dt.Rows[2]["d11"].ToString()); exSheet.Cells[9, 11].Value = float.Parse(dt.Rows[2]["d4"].ToString()); exSheet.Cells[9, 12].Value = float.Parse(dt.Rows[2]["d12"].ToString()); exSheet.Cells[9, 13].Value = float.Parse(dt.Rows[2]["d5"].ToString()); exSheet.Cells[9, 14].Value = float.Parse(dt.Rows[2]["d13"].ToString()); exSheet.Cells[9, 15].Value = float.Parse(dt.Rows[2]["d6"].ToString()); exSheet.Cells[9, 16].Value = float.Parse(dt.Rows[2]["d14"].ToString()); exSheet.Cells[9, 17].Value = float.Parse(dt.Rows[2]["d7"].ToString()); exSheet.Cells[9, 18].Value = float.Parse(dt.Rows[2]["sum_guest_qty"].ToString()); exSheet.Cells[9, 19].Value = float.Parse(dt.Rows[2]["mm_sum"].ToString()); exSheet.Cells[9, 20].Value = float.Parse(dt.Rows[2]["avg_guest_qty"].ToString()); exSheet.Cells[9, 21].Value = float.Parse(dt.Rows[2]["avg_amount_qty"].ToString()); exSheet.Cells[10, 4].Value = float.Parse(dt.Rows[3]["d8"].ToString()); exSheet.Cells[10, 5].Value = float.Parse(dt.Rows[3]["d1"].ToString()); exSheet.Cells[10, 6].Value = float.Parse(dt.Rows[3]["d9"].ToString()); exSheet.Cells[10, 7].Value = float.Parse(dt.Rows[3]["d2"].ToString()); exSheet.Cells[10, 8].Value = float.Parse(dt.Rows[3]["d10"].ToString()); exSheet.Cells[10, 9].Value = float.Parse(dt.Rows[3]["d3"].ToString()); exSheet.Cells[10, 10].Value = float.Parse(dt.Rows[3]["d11"].ToString()); exSheet.Cells[10, 11].Value = float.Parse(dt.Rows[3]["d4"].ToString()); exSheet.Cells[10, 12].Value = float.Parse(dt.Rows[3]["d12"].ToString()); exSheet.Cells[10, 13].Value = float.Parse(dt.Rows[3]["d5"].ToString()); exSheet.Cells[10, 14].Value = float.Parse(dt.Rows[3]["d13"].ToString()); exSheet.Cells[10, 15].Value = float.Parse(dt.Rows[3]["d6"].ToString()); exSheet.Cells[10, 16].Value = float.Parse(dt.Rows[3]["d14"].ToString()); exSheet.Cells[10, 17].Value = float.Parse(dt.Rows[3]["d7"].ToString()); exSheet.Cells[10, 18].Value = float.Parse(dt.Rows[3]["sum_guest_qty"].ToString()); exSheet.Cells[10, 19].Value = float.Parse(dt.Rows[3]["mm_sum"].ToString()); exSheet.Cells[10, 20].Value = float.Parse(dt.Rows[3]["avg_guest_qty"].ToString()); exSheet.Cells[10, 21].Value = float.Parse(dt.Rows[3]["avg_amount_qty"].ToString()); exSheet.Cells[11, 4].Value = float.Parse(dt.Rows[4]["d8"].ToString()); exSheet.Cells[11, 5].Value = float.Parse(dt.Rows[4]["d1"].ToString()); exSheet.Cells[11, 6].Value = float.Parse(dt.Rows[4]["d9"].ToString()); exSheet.Cells[11, 7].Value = float.Parse(dt.Rows[4]["d2"].ToString()); exSheet.Cells[11, 8].Value = float.Parse(dt.Rows[4]["d10"].ToString()); exSheet.Cells[11, 9].Value = float.Parse(dt.Rows[4]["d3"].ToString()); exSheet.Cells[11, 10].Value = float.Parse(dt.Rows[4]["d11"].ToString()); exSheet.Cells[11, 11].Value = float.Parse(dt.Rows[4]["d4"].ToString()); exSheet.Cells[11, 12].Value = float.Parse(dt.Rows[4]["d12"].ToString()); exSheet.Cells[11, 13].Value = float.Parse(dt.Rows[4]["d5"].ToString()); exSheet.Cells[11, 14].Value = float.Parse(dt.Rows[4]["d13"].ToString()); exSheet.Cells[11, 15].Value = float.Parse(dt.Rows[4]["d6"].ToString()); exSheet.Cells[11, 16].Value = float.Parse(dt.Rows[4]["d14"].ToString()); exSheet.Cells[11, 17].Value = float.Parse(dt.Rows[4]["d7"].ToString()); exSheet.Cells[11, 18].Value = float.Parse(dt.Rows[4]["sum_guest_qty"].ToString()); exSheet.Cells[11, 19].Value = float.Parse(dt.Rows[4]["mm_sum"].ToString()); exSheet.Cells[11, 20].Value = float.Parse(dt.Rows[4]["avg_guest_qty"].ToString()); exSheet.Cells[11, 21].Value = float.Parse(dt.Rows[4]["avg_amount_qty"].ToString()); exSheet.Cells[12, 4].Value = float.Parse(dt.Rows[5]["d8"].ToString()); exSheet.Cells[12, 5].Value = float.Parse(dt.Rows[5]["d1"].ToString()); exSheet.Cells[12, 6].Value = float.Parse(dt.Rows[5]["d9"].ToString()); exSheet.Cells[12, 7].Value = float.Parse(dt.Rows[5]["d2"].ToString()); exSheet.Cells[12, 8].Value = float.Parse(dt.Rows[5]["d10"].ToString()); exSheet.Cells[12, 9].Value = float.Parse(dt.Rows[5]["d3"].ToString()); exSheet.Cells[12, 10].Value = float.Parse(dt.Rows[5]["d11"].ToString()); exSheet.Cells[12, 11].Value = float.Parse(dt.Rows[5]["d4"].ToString()); exSheet.Cells[12, 12].Value = float.Parse(dt.Rows[5]["d12"].ToString()); exSheet.Cells[12, 13].Value = float.Parse(dt.Rows[5]["d5"].ToString()); exSheet.Cells[12, 14].Value = float.Parse(dt.Rows[5]["d13"].ToString()); exSheet.Cells[12, 15].Value = float.Parse(dt.Rows[5]["d6"].ToString()); exSheet.Cells[12, 16].Value = float.Parse(dt.Rows[5]["d14"].ToString()); exSheet.Cells[12, 17].Value = float.Parse(dt.Rows[5]["d7"].ToString()); exSheet.Cells[12, 18].Value = float.Parse(dt.Rows[5]["sum_guest_qty"].ToString()); exSheet.Cells[12, 19].Value = float.Parse(dt.Rows[5]["mm_sum"].ToString()); exSheet.Cells[12, 20].Value = float.Parse(dt.Rows[5]["avg_guest_qty"].ToString()); exSheet.Cells[12, 21].Value = float.Parse(dt.Rows[5]["avg_amount_qty"].ToString()); exSheet.Cells[13, 4].Value = float.Parse(dt.Rows[6]["d8"].ToString()); exSheet.Cells[13, 5].Value = float.Parse(dt.Rows[6]["d1"].ToString()); exSheet.Cells[13, 6].Value = float.Parse(dt.Rows[6]["d9"].ToString()); exSheet.Cells[13, 7].Value = float.Parse(dt.Rows[6]["d2"].ToString()); exSheet.Cells[13, 8].Value = float.Parse(dt.Rows[6]["d10"].ToString()); exSheet.Cells[13, 9].Value = float.Parse(dt.Rows[6]["d3"].ToString()); exSheet.Cells[13, 10].Value = float.Parse(dt.Rows[6]["d11"].ToString()); exSheet.Cells[13, 11].Value = float.Parse(dt.Rows[6]["d4"].ToString()); exSheet.Cells[13, 12].Value = float.Parse(dt.Rows[6]["d12"].ToString()); exSheet.Cells[13, 13].Value = float.Parse(dt.Rows[6]["d5"].ToString()); exSheet.Cells[13, 14].Value = float.Parse(dt.Rows[6]["d13"].ToString()); exSheet.Cells[13, 15].Value = float.Parse(dt.Rows[6]["d6"].ToString()); exSheet.Cells[13, 16].Value = float.Parse(dt.Rows[6]["d14"].ToString()); exSheet.Cells[13, 17].Value = float.Parse(dt.Rows[6]["d7"].ToString()); exSheet.Cells[13, 18].Value = float.Parse(dt.Rows[6]["sum_guest_qty"].ToString()); exSheet.Cells[13, 19].Value = float.Parse(dt.Rows[6]["mm_sum"].ToString()); exSheet.Cells[13, 20].Value = float.Parse(dt.Rows[6]["avg_guest_qty"].ToString()); exSheet.Cells[13, 21].Value = float.Parse(dt.Rows[6]["avg_amount_qty"].ToString()); exSheet.Cells[14, 4].Value = float.Parse(dt.Rows[7]["d8"].ToString()); exSheet.Cells[14, 5].Value = float.Parse(dt.Rows[7]["d1"].ToString()); exSheet.Cells[14, 6].Value = float.Parse(dt.Rows[7]["d9"].ToString()); exSheet.Cells[14, 7].Value = float.Parse(dt.Rows[7]["d2"].ToString()); exSheet.Cells[14, 8].Value = float.Parse(dt.Rows[7]["d10"].ToString()); exSheet.Cells[14, 9].Value = float.Parse(dt.Rows[7]["d3"].ToString()); exSheet.Cells[14, 10].Value = float.Parse(dt.Rows[7]["d11"].ToString()); exSheet.Cells[14, 11].Value = float.Parse(dt.Rows[7]["d4"].ToString()); exSheet.Cells[14, 12].Value = float.Parse(dt.Rows[7]["d12"].ToString()); exSheet.Cells[14, 13].Value = float.Parse(dt.Rows[7]["d5"].ToString()); exSheet.Cells[14, 14].Value = float.Parse(dt.Rows[7]["d13"].ToString()); exSheet.Cells[14, 15].Value = float.Parse(dt.Rows[7]["d6"].ToString()); exSheet.Cells[14, 16].Value = float.Parse(dt.Rows[7]["d14"].ToString()); exSheet.Cells[14, 17].Value = float.Parse(dt.Rows[7]["d7"].ToString()); exSheet.Cells[14, 18].Value = float.Parse(dt.Rows[7]["sum_guest_qty"].ToString()); exSheet.Cells[14, 19].Value = float.Parse(dt.Rows[7]["mm_sum"].ToString()); exSheet.Cells[14, 20].Value = float.Parse(dt.Rows[7]["avg_guest_qty"].ToString()); exSheet.Cells[14, 21].Value = float.Parse(dt.Rows[7]["avg_amount_qty"].ToString()); exSheet.Cells[15, 4].Value = float.Parse(dt.Rows[8]["d8"].ToString()); exSheet.Cells[15, 5].Value = float.Parse(dt.Rows[8]["d1"].ToString()); exSheet.Cells[15, 6].Value = float.Parse(dt.Rows[8]["d9"].ToString()); exSheet.Cells[15, 7].Value = float.Parse(dt.Rows[8]["d2"].ToString()); exSheet.Cells[15, 8].Value = float.Parse(dt.Rows[8]["d10"].ToString()); exSheet.Cells[15, 9].Value = float.Parse(dt.Rows[8]["d3"].ToString()); exSheet.Cells[15, 10].Value = float.Parse(dt.Rows[8]["d11"].ToString()); exSheet.Cells[15, 11].Value = float.Parse(dt.Rows[8]["d4"].ToString()); exSheet.Cells[15, 12].Value = float.Parse(dt.Rows[8]["d12"].ToString()); exSheet.Cells[15, 13].Value = float.Parse(dt.Rows[8]["d5"].ToString()); exSheet.Cells[15, 14].Value = float.Parse(dt.Rows[8]["d13"].ToString()); exSheet.Cells[15, 15].Value = float.Parse(dt.Rows[8]["d6"].ToString()); exSheet.Cells[15, 16].Value = float.Parse(dt.Rows[8]["d14"].ToString()); exSheet.Cells[15, 17].Value = float.Parse(dt.Rows[8]["d7"].ToString()); exSheet.Cells[15, 18].Value = float.Parse(dt.Rows[8]["sum_guest_qty"].ToString()); exSheet.Cells[15, 19].Value = float.Parse(dt.Rows[8]["mm_sum"].ToString()); exSheet.Cells[15, 20].Value = float.Parse(dt.Rows[8]["avg_guest_qty"].ToString()); exSheet.Cells[15, 21].Value = float.Parse(dt.Rows[8]["avg_amount_qty"].ToString()); exSheet.Cells[16, 4].Value = float.Parse(dt.Rows[9]["d8"].ToString()); exSheet.Cells[16, 5].Value = float.Parse(dt.Rows[9]["d1"].ToString()); exSheet.Cells[16, 6].Value = float.Parse(dt.Rows[9]["d9"].ToString()); exSheet.Cells[16, 7].Value = float.Parse(dt.Rows[9]["d2"].ToString()); exSheet.Cells[16, 8].Value = float.Parse(dt.Rows[9]["d10"].ToString()); exSheet.Cells[16, 9].Value = float.Parse(dt.Rows[9]["d3"].ToString()); exSheet.Cells[16, 10].Value = float.Parse(dt.Rows[9]["d11"].ToString()); exSheet.Cells[16, 11].Value = float.Parse(dt.Rows[9]["d4"].ToString()); exSheet.Cells[16, 12].Value = float.Parse(dt.Rows[9]["d12"].ToString()); exSheet.Cells[16, 13].Value = float.Parse(dt.Rows[9]["d5"].ToString()); exSheet.Cells[16, 14].Value = float.Parse(dt.Rows[9]["d13"].ToString()); exSheet.Cells[16, 15].Value = float.Parse(dt.Rows[9]["d6"].ToString()); exSheet.Cells[16, 16].Value = float.Parse(dt.Rows[9]["d14"].ToString()); exSheet.Cells[16, 17].Value = float.Parse(dt.Rows[9]["d7"].ToString()); exSheet.Cells[16, 18].Value = float.Parse(dt.Rows[9]["sum_guest_qty"].ToString()); exSheet.Cells[16, 19].Value = float.Parse(dt.Rows[9]["mm_sum"].ToString()); exSheet.Cells[16, 20].Value = float.Parse(dt.Rows[9]["avg_guest_qty"].ToString()); exSheet.Cells[16, 21].Value = float.Parse(dt.Rows[9]["avg_amount_qty"].ToString()); exSheet.Cells[17, 4].Value = float.Parse(dt.Rows[10]["d8"].ToString()); exSheet.Cells[17, 5].Value = float.Parse(dt.Rows[10]["d1"].ToString()); exSheet.Cells[17, 6].Value = float.Parse(dt.Rows[10]["d9"].ToString()); exSheet.Cells[17, 7].Value = float.Parse(dt.Rows[10]["d2"].ToString()); exSheet.Cells[17, 8].Value = float.Parse(dt.Rows[10]["d10"].ToString()); exSheet.Cells[17, 9].Value = float.Parse(dt.Rows[10]["d3"].ToString()); exSheet.Cells[17, 10].Value = float.Parse(dt.Rows[10]["d11"].ToString()); exSheet.Cells[17, 11].Value = float.Parse(dt.Rows[10]["d4"].ToString()); exSheet.Cells[17, 12].Value = float.Parse(dt.Rows[10]["d12"].ToString()); exSheet.Cells[17, 13].Value = float.Parse(dt.Rows[10]["d5"].ToString()); exSheet.Cells[17, 14].Value = float.Parse(dt.Rows[10]["d13"].ToString()); exSheet.Cells[17, 15].Value = float.Parse(dt.Rows[10]["d6"].ToString()); exSheet.Cells[17, 16].Value = float.Parse(dt.Rows[10]["d14"].ToString()); exSheet.Cells[17, 17].Value = float.Parse(dt.Rows[10]["d7"].ToString()); exSheet.Cells[17, 18].Value = float.Parse(dt.Rows[10]["sum_guest_qty"].ToString()); exSheet.Cells[17, 19].Value = float.Parse(dt.Rows[10]["mm_sum"].ToString()); exSheet.Cells[17, 20].Value = float.Parse(dt.Rows[10]["avg_guest_qty"].ToString()); exSheet.Cells[17, 21].Value = float.Parse(dt.Rows[10]["avg_amount_qty"].ToString()); exSheet.Cells[18, 4].Value = float.Parse(dt.Rows[11]["d8"].ToString()); exSheet.Cells[18, 5].Value = float.Parse(dt.Rows[11]["d1"].ToString()); exSheet.Cells[18, 6].Value = float.Parse(dt.Rows[11]["d9"].ToString()); exSheet.Cells[18, 7].Value = float.Parse(dt.Rows[11]["d2"].ToString()); exSheet.Cells[18, 8].Value = float.Parse(dt.Rows[11]["d10"].ToString()); exSheet.Cells[18, 9].Value = float.Parse(dt.Rows[11]["d3"].ToString()); exSheet.Cells[18, 10].Value = float.Parse(dt.Rows[11]["d11"].ToString()); exSheet.Cells[18, 11].Value = float.Parse(dt.Rows[11]["d4"].ToString()); exSheet.Cells[18, 12].Value = float.Parse(dt.Rows[11]["d12"].ToString()); exSheet.Cells[18, 13].Value = float.Parse(dt.Rows[11]["d5"].ToString()); exSheet.Cells[18, 14].Value = float.Parse(dt.Rows[11]["d13"].ToString()); exSheet.Cells[18, 15].Value = float.Parse(dt.Rows[11]["d6"].ToString()); exSheet.Cells[18, 16].Value = float.Parse(dt.Rows[11]["d14"].ToString()); exSheet.Cells[18, 17].Value = float.Parse(dt.Rows[11]["d7"].ToString()); exSheet.Cells[18, 18].Value = float.Parse(dt.Rows[11]["sum_guest_qty"].ToString()); exSheet.Cells[18, 19].Value = float.Parse(dt.Rows[11]["mm_sum"].ToString()); exSheet.Cells[18, 20].Value = float.Parse(dt.Rows[11]["avg_guest_qty"].ToString()); exSheet.Cells[18, 21].Value = float.Parse(dt.Rows[11]["avg_amount_qty"].ToString()); exSheet.Cells[19, 4].Value = float.Parse(dt.Rows[12]["d8"].ToString()); exSheet.Cells[19, 5].Value = float.Parse(dt.Rows[12]["d1"].ToString()); exSheet.Cells[19, 6].Value = float.Parse(dt.Rows[12]["d9"].ToString()); exSheet.Cells[19, 7].Value = float.Parse(dt.Rows[12]["d2"].ToString()); exSheet.Cells[19, 8].Value = float.Parse(dt.Rows[12]["d10"].ToString()); exSheet.Cells[19, 9].Value = float.Parse(dt.Rows[12]["d3"].ToString()); exSheet.Cells[19, 10].Value = float.Parse(dt.Rows[12]["d11"].ToString()); exSheet.Cells[19, 11].Value = float.Parse(dt.Rows[12]["d4"].ToString()); exSheet.Cells[19, 12].Value = float.Parse(dt.Rows[12]["d12"].ToString()); exSheet.Cells[19, 13].Value = float.Parse(dt.Rows[12]["d5"].ToString()); exSheet.Cells[19, 14].Value = float.Parse(dt.Rows[12]["d13"].ToString()); exSheet.Cells[19, 15].Value = float.Parse(dt.Rows[12]["d6"].ToString()); exSheet.Cells[19, 16].Value = float.Parse(dt.Rows[12]["d14"].ToString()); exSheet.Cells[19, 17].Value = float.Parse(dt.Rows[12]["d7"].ToString()); exSheet.Cells[19, 18].Value = float.Parse(dt.Rows[12]["sum_guest_qty"].ToString()); exSheet.Cells[19, 19].Value = float.Parse(dt.Rows[12]["mm_sum"].ToString()); exSheet.Cells[19, 20].Value = float.Parse(dt.Rows[12]["avg_guest_qty"].ToString()); exSheet.Cells[19, 21].Value = float.Parse(dt.Rows[12]["avg_amount_qty"].ToString()); exSheet.Cells[20, 4].Value = float.Parse(dt.Rows[13]["d8"].ToString()); exSheet.Cells[20, 5].Value = float.Parse(dt.Rows[13]["d1"].ToString()); exSheet.Cells[20, 6].Value = float.Parse(dt.Rows[13]["d9"].ToString()); exSheet.Cells[20, 7].Value = float.Parse(dt.Rows[13]["d2"].ToString()); exSheet.Cells[20, 8].Value = float.Parse(dt.Rows[13]["d10"].ToString()); exSheet.Cells[20, 9].Value = float.Parse(dt.Rows[13]["d3"].ToString()); exSheet.Cells[20, 10].Value = float.Parse(dt.Rows[13]["d11"].ToString()); exSheet.Cells[20, 11].Value = float.Parse(dt.Rows[13]["d4"].ToString()); exSheet.Cells[20, 12].Value = float.Parse(dt.Rows[13]["d12"].ToString()); exSheet.Cells[20, 13].Value = float.Parse(dt.Rows[13]["d5"].ToString()); exSheet.Cells[20, 14].Value = float.Parse(dt.Rows[13]["d13"].ToString()); exSheet.Cells[20, 15].Value = float.Parse(dt.Rows[13]["d6"].ToString()); exSheet.Cells[20, 16].Value = float.Parse(dt.Rows[13]["d14"].ToString()); exSheet.Cells[20, 17].Value = float.Parse(dt.Rows[13]["d7"].ToString()); exSheet.Cells[20, 18].Value = float.Parse(dt.Rows[13]["sum_guest_qty"].ToString()); exSheet.Cells[20, 19].Value = float.Parse(dt.Rows[13]["mm_sum"].ToString()); exSheet.Cells[20, 20].Value = float.Parse(dt.Rows[13]["avg_guest_qty"].ToString()); exSheet.Cells[20, 21].Value = float.Parse(dt.Rows[13]["avg_amount_qty"].ToString()); exSheet.Cells[21, 4].Value = float.Parse(dt.Rows[14]["d8"].ToString()); exSheet.Cells[21, 5].Value = float.Parse(dt.Rows[14]["d1"].ToString()); exSheet.Cells[21, 6].Value = float.Parse(dt.Rows[14]["d9"].ToString()); exSheet.Cells[21, 7].Value = float.Parse(dt.Rows[14]["d2"].ToString()); exSheet.Cells[21, 8].Value = float.Parse(dt.Rows[14]["d10"].ToString()); exSheet.Cells[21, 9].Value = float.Parse(dt.Rows[14]["d3"].ToString()); exSheet.Cells[21, 10].Value = float.Parse(dt.Rows[14]["d11"].ToString()); exSheet.Cells[21, 11].Value = float.Parse(dt.Rows[14]["d4"].ToString()); exSheet.Cells[21, 12].Value = float.Parse(dt.Rows[14]["d12"].ToString()); exSheet.Cells[21, 13].Value = float.Parse(dt.Rows[14]["d5"].ToString()); exSheet.Cells[21, 14].Value = float.Parse(dt.Rows[14]["d13"].ToString()); exSheet.Cells[21, 15].Value = float.Parse(dt.Rows[14]["d6"].ToString()); exSheet.Cells[21, 16].Value = float.Parse(dt.Rows[14]["d14"].ToString()); exSheet.Cells[21, 17].Value = float.Parse(dt.Rows[14]["d7"].ToString()); exSheet.Cells[21, 18].Value = float.Parse(dt.Rows[14]["sum_guest_qty"].ToString()); exSheet.Cells[21, 19].Value = float.Parse(dt.Rows[14]["mm_sum"].ToString()); exSheet.Cells[21, 20].Value = float.Parse(dt.Rows[14]["avg_guest_qty"].ToString()); exSheet.Cells[21, 21].Value = float.Parse(dt.Rows[14]["avg_amount_qty"].ToString()); exSheet.Cells[22, 4].Value = float.Parse(dt.Rows[15]["d8"].ToString()); exSheet.Cells[22, 5].Value = float.Parse(dt.Rows[15]["d1"].ToString()); exSheet.Cells[22, 6].Value = float.Parse(dt.Rows[15]["d9"].ToString()); exSheet.Cells[22, 7].Value = float.Parse(dt.Rows[15]["d2"].ToString()); exSheet.Cells[22, 8].Value = float.Parse(dt.Rows[15]["d10"].ToString()); exSheet.Cells[22, 9].Value = float.Parse(dt.Rows[15]["d3"].ToString()); exSheet.Cells[22, 10].Value = float.Parse(dt.Rows[15]["d11"].ToString()); exSheet.Cells[22, 11].Value = float.Parse(dt.Rows[15]["d4"].ToString()); exSheet.Cells[22, 12].Value = float.Parse(dt.Rows[15]["d12"].ToString()); exSheet.Cells[22, 13].Value = float.Parse(dt.Rows[15]["d5"].ToString()); exSheet.Cells[22, 14].Value = float.Parse(dt.Rows[15]["d13"].ToString()); exSheet.Cells[22, 15].Value = float.Parse(dt.Rows[15]["d6"].ToString()); exSheet.Cells[22, 16].Value = float.Parse(dt.Rows[15]["d14"].ToString()); exSheet.Cells[22, 17].Value = float.Parse(dt.Rows[15]["d7"].ToString()); exSheet.Cells[22, 18].Value = float.Parse(dt.Rows[15]["sum_guest_qty"].ToString()); exSheet.Cells[22, 19].Value = float.Parse(dt.Rows[15]["mm_sum"].ToString()); exSheet.Cells[22, 20].Value = float.Parse(dt.Rows[15]["avg_guest_qty"].ToString()); exSheet.Cells[22, 21].Value = float.Parse(dt.Rows[15]["avg_amount_qty"].ToString()); exSheet.Cells[23, 4].Value = float.Parse(dt.Rows[16]["d8"].ToString()); exSheet.Cells[23, 5].Value = float.Parse(dt.Rows[16]["d1"].ToString()); exSheet.Cells[23, 6].Value = float.Parse(dt.Rows[16]["d9"].ToString()); exSheet.Cells[23, 7].Value = float.Parse(dt.Rows[16]["d2"].ToString()); exSheet.Cells[23, 8].Value = float.Parse(dt.Rows[16]["d10"].ToString()); exSheet.Cells[23, 9].Value = float.Parse(dt.Rows[16]["d3"].ToString()); exSheet.Cells[23, 10].Value = float.Parse(dt.Rows[16]["d11"].ToString()); exSheet.Cells[23, 11].Value = float.Parse(dt.Rows[16]["d4"].ToString()); exSheet.Cells[23, 12].Value = float.Parse(dt.Rows[16]["d12"].ToString()); exSheet.Cells[23, 13].Value = float.Parse(dt.Rows[16]["d5"].ToString()); exSheet.Cells[23, 14].Value = float.Parse(dt.Rows[16]["d13"].ToString()); exSheet.Cells[23, 15].Value = float.Parse(dt.Rows[16]["d6"].ToString()); exSheet.Cells[23, 16].Value = float.Parse(dt.Rows[16]["d14"].ToString()); exSheet.Cells[23, 17].Value = float.Parse(dt.Rows[16]["d7"].ToString()); exSheet.Cells[23, 18].Value = float.Parse(dt.Rows[16]["sum_guest_qty"].ToString()); exSheet.Cells[23, 19].Value = float.Parse(dt.Rows[16]["mm_sum"].ToString()); exSheet.Cells[23, 20].Value = float.Parse(dt.Rows[16]["avg_guest_qty"].ToString()); exSheet.Cells[23, 21].Value = float.Parse(dt.Rows[16]["avg_amount_qty"].ToString()); exSheet.Cells[24, 4].Value = float.Parse(dt.Rows[17]["d8"].ToString()); exSheet.Cells[24, 5].Value = float.Parse(dt.Rows[17]["d1"].ToString()); exSheet.Cells[24, 6].Value = float.Parse(dt.Rows[17]["d9"].ToString()); exSheet.Cells[24, 7].Value = float.Parse(dt.Rows[17]["d2"].ToString()); exSheet.Cells[24, 8].Value = float.Parse(dt.Rows[17]["d10"].ToString()); exSheet.Cells[24, 9].Value = float.Parse(dt.Rows[17]["d3"].ToString()); exSheet.Cells[24, 10].Value = float.Parse(dt.Rows[17]["d11"].ToString()); exSheet.Cells[24, 11].Value = float.Parse(dt.Rows[17]["d4"].ToString()); exSheet.Cells[24, 12].Value = float.Parse(dt.Rows[17]["d12"].ToString()); exSheet.Cells[24, 13].Value = float.Parse(dt.Rows[17]["d5"].ToString()); exSheet.Cells[24, 14].Value = float.Parse(dt.Rows[17]["d13"].ToString()); exSheet.Cells[24, 15].Value = float.Parse(dt.Rows[17]["d6"].ToString()); exSheet.Cells[24, 16].Value = float.Parse(dt.Rows[17]["d14"].ToString()); exSheet.Cells[24, 17].Value = float.Parse(dt.Rows[17]["d7"].ToString()); exSheet.Cells[24, 18].Value = float.Parse(dt.Rows[17]["sum_guest_qty"].ToString()); exSheet.Cells[24, 19].Value = float.Parse(dt.Rows[17]["mm_sum"].ToString()); exSheet.Cells[24, 20].Value = float.Parse(dt.Rows[17]["avg_guest_qty"].ToString()); exSheet.Cells[24, 21].Value = float.Parse(dt.Rows[17]["avg_amount_qty"].ToString()); exSheet.Cells[25, 4].Value = float.Parse(dt.Rows[18]["d8"].ToString()); exSheet.Cells[25, 5].Value = float.Parse(dt.Rows[18]["d1"].ToString()); exSheet.Cells[25, 6].Value = float.Parse(dt.Rows[18]["d9"].ToString()); exSheet.Cells[25, 7].Value = float.Parse(dt.Rows[18]["d2"].ToString()); exSheet.Cells[25, 8].Value = float.Parse(dt.Rows[18]["d10"].ToString()); exSheet.Cells[25, 9].Value = float.Parse(dt.Rows[18]["d3"].ToString()); exSheet.Cells[25, 10].Value = float.Parse(dt.Rows[18]["d11"].ToString()); exSheet.Cells[25, 11].Value = float.Parse(dt.Rows[18]["d4"].ToString()); exSheet.Cells[25, 12].Value = float.Parse(dt.Rows[18]["d12"].ToString()); exSheet.Cells[25, 13].Value = float.Parse(dt.Rows[18]["d5"].ToString()); exSheet.Cells[25, 14].Value = float.Parse(dt.Rows[18]["d13"].ToString()); exSheet.Cells[25, 15].Value = float.Parse(dt.Rows[18]["d6"].ToString()); exSheet.Cells[25, 16].Value = float.Parse(dt.Rows[18]["d14"].ToString()); exSheet.Cells[25, 17].Value = float.Parse(dt.Rows[18]["d7"].ToString()); exSheet.Cells[25, 18].Value = float.Parse(dt.Rows[18]["sum_guest_qty"].ToString()); exSheet.Cells[25, 19].Value = float.Parse(dt.Rows[18]["mm_sum"].ToString()); exSheet.Cells[25, 20].Value = float.Parse(dt.Rows[18]["avg_guest_qty"].ToString()); exSheet.Cells[25, 21].Value = float.Parse(dt.Rows[18]["avg_amount_qty"].ToString()); //---------------------- ////end-set-----DataBind--in---Master--- //set date title of year //exSheet.Cells[2, 1].Value = "Income Statement Year" + " " + l_year; //set data to detail. // loop detail not percent // 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 l_no_parameter = Request["p_no_parameter"]; string l_parameter = "'" + l_no_parameter + "'"; string TemplateFile = "rpt_60300010_ooo.xls"; string TempFile = "../../../../system/temp/rpt_60300010_ooo_" + 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 DataTable dt; dt = CtlLib.TableReadOpenCursor("ht_rpt_60300010_OOO", l_parameter); //-----header--- //---end--header--- //--set-----DataBind--in---Master--- if (dt.Rows.Count == 0) { exSheet.Cells[6, 1].Value = "There is not data!"; } if (dt.Rows.Count > 0) { //exSheet.Cells[2, 1].Value = dt.Rows[0]["date_para"].ToString(); // exSheet.Cells[2, 15].Value = dt.Rows[0]["print_time"].ToString(); // exSheet.Cells[4, 12].Value = dt.Rows[0]["date_header"].ToString(); } //---------------------- ////end-set-----DataBind--in---Master--- int l_row_first = 6; for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++) { exSheet.Range["A5"].Rows.EntireRow.Insert();//insert row new of sheet } for (int l_row = 0; l_row < dt.Rows.Count; l_row++) { exSheet.Cells[l_row_first + l_row, 1].Value = dt.Rows[l_row]["room_type"].ToString(); exSheet.Cells[l_row_first + l_row, 2].Value = dt.Rows[l_row]["room_no"].ToString(); exSheet.Cells[l_row_first + l_row, 3].Value = dt.Rows[l_row]["from_dt"].ToString(); exSheet.Cells[l_row_first + l_row, 4].Value = dt.Rows[l_row]["to_dt"].ToString(); exSheet.Cells[l_row_first + l_row, 5].Value = dt.Rows[l_row]["description"].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; // 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 l_pk = Request["p_detail_pk"]; //string l_tco_company_pk=Request["p_company_pk"]; string TemplateFile = "rpt_60240020_R_Card2.xls"; string TempFile = "../../../../system/temp/rpt_60240020_R_Card2_" + 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, dtSum; para = "'" + l_pk + "'"; dt = CtlLib.TableReadOpenCursor("ht_rpt_60240020_rcard", para); //-----header--- //---end--header--- //--set-----DataBind--in---Master--- /*if (dt.Rows.Count == 0) * { * exSheet.Cells[6, 8].Value = "There is not data!"; * * } * //insert row de can bang page * if (dt.Rows.Count == 1) * { * for (int l_addrow = 1; l_addrow < 7; l_addrow++) * { * exSheet.Range["A32"].Rows.EntireRow.Insert();//insert row new of sheet * * } * } * if (dt.Rows.Count == 2) * { * for (int l_addrow = 1; l_addrow < 6; l_addrow++) * { * exSheet.Range["A32"].Rows.EntireRow.Insert();//insert row new of sheet * * } * } * if (dt.Rows.Count == 3) * { * for (int l_addrow = 1; l_addrow < 5; l_addrow++) * { * exSheet.Range["A32"].Rows.EntireRow.Insert();//insert row new of sheet * * } * } * if (dt.Rows.Count == 4) * { * for (int l_addrow = 1; l_addrow < 4; l_addrow++) * { * exSheet.Range["A32"].Rows.EntireRow.Insert();//insert row new of sheet * * } * } * if (dt.Rows.Count == 5) * { * for (int l_addrow = 1; l_addrow < 3; l_addrow++) * { * exSheet.Range["A32"].Rows.EntireRow.Insert();//insert row new of sheet * * } * } * if (dt.Rows.Count == 6) * { * for (int l_addrow = 1; l_addrow < 2; l_addrow++) * { * exSheet.Range["A32"].Rows.EntireRow.Insert();//insert row new of sheet * * } * } * if (dt.Rows.Count == 7) * { * for (int l_addrow = 1; l_addrow < 11; l_addrow++) * { * exSheet.Range["A32"].Rows.EntireRow.Insert();//insert row new of sheet * * } * } * if (dt.Rows.Count == 8) * { * for (int l_addrow = 1; l_addrow < 10; l_addrow++) * { * exSheet.Range["A32"].Rows.EntireRow.Insert();//insert row new of sheet * * } * } * if (dt.Rows.Count == 9) * { * for (int l_addrow = 1; l_addrow < 9; l_addrow++) * { * exSheet.Range["A32"].Rows.EntireRow.Insert();//insert row new of sheet * * } * } * if (dt.Rows.Count == 10) * { * for (int l_addrow = 1; l_addrow < 8; l_addrow++) * { * exSheet.Range["A32"].Rows.EntireRow.Insert();//insert row new of sheet * * } * } * if (dt.Rows.Count == 11) * { * for (int l_addrow = 1; l_addrow < 7; l_addrow++) * { * exSheet.Range["A32"].Rows.EntireRow.Insert();//insert row new of sheet * * } * } * if (dt.Rows.Count == 12) * { * for (int l_addrow = 1; l_addrow < 6; l_addrow++) * { * exSheet.Range["A32"].Rows.EntireRow.Insert();//insert row new of sheet * * } * } * if (dt.Rows.Count == 13) * { * for (int l_addrow = 1; l_addrow < 5; l_addrow++) * { * exSheet.Range["A32"].Rows.EntireRow.Insert();//insert row new of sheet * * } * } * if (dt.Rows.Count == 14) * { * for (int l_addrow = 1; l_addrow < 4; l_addrow++) * { * exSheet.Range["A32"].Rows.EntireRow.Insert();//insert row new of sheet * * } * } * if (dt.Rows.Count == 15) * { * for (int l_addrow = 1; l_addrow < 3; l_addrow++) * { * exSheet.Range["A32"].Rows.EntireRow.Insert();//insert row new of sheet * * } * }*/ //end insert row de can bang page if (dt.Rows.Count > 0) { //exSheet.Cells[3, 1].Value = "Month :"+l_month.Substring(4,2)+"/"+l_month.Substring(0,4); exSheet.Cells[2, 6].Value = "Print time :" + dt.Rows[0]["print_time"].ToString(); exSheet.Cells[6, 2].Value = dt.Rows[0]["slip_no"].ToString(); exSheet.Cells[6, 3].Value = dt.Rows[0]["from_date"].ToString(); exSheet.Cells[6, 5].Value = dt.Rows[0]["date_to"].ToString(); exSheet.Cells[9, 3].Value = dt.Rows[0]["guest_name"].ToString(); exSheet.Cells[10, 3].Value = dt.Rows[0]["nation"].ToString(); exSheet.Cells[12, 3].Value = dt.Rows[0]["addr1"].ToString(); //partner exSheet.Cells[13, 3].Value = dt.Rows[0]["email_address"].ToString(); //partner exSheet.Cells[13, 7].Value = dt.Rows[0]["phone_no"].ToString(); //partner /*exSheet.Cells[6, 7].Value = dt.Rows[0]["contact_person"].ToString(); * * exSheet.Cells[7, 7].Value = dt.Rows[0]["company_name"].ToString(); * exSheet.Cells[11, 3].Value = dt.Rows[0]["crt_dt"].ToString(); * exSheet.Cells[9, 7].Value = dt.Rows[0]["phone"].ToString(); * exSheet.Cells[10, 7].Value = dt.Rows[0]["fax"].ToString(); * exSheet.Cells[11, 7].Value = dt.Rows[0]["email"].ToString(); * exSheet.Cells[12, 6].Value = "Reservation# :"+dt.Rows[0]["slip_no"].ToString(); * exSheet.Cells[14, 3].Value = dt.Rows[0]["contact_person"].ToString();//Dear: * if(dt.Rows[0]["total_deposit_amt_bk"].ToString() !="") * { * exSheet.Cells[24, 4].Value = double.Parse(dt.Rows[0]["total_deposit_amt_bk"].ToString()); * } * exSheet.Cells[25, 3].Value = dt.Rows[0]["credit_card_owner"].ToString(); * exSheet.Cells[26, 3].Value = dt.Rows[0]["credit_type"].ToString(); * exSheet.Cells[27, 3].Value = dt.Rows[0]["credit_no"].ToString(); * exSheet.Cells[28, 3].Value = dt.Rows[0]["expire_date"].ToString(); * exSheet.Cells[21, 3].Value = dt.Rows[0]["request_desc"].ToString();*/ } //---------------------- ////end-set-----DataBind--in---Master--- for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++) { exSheet.Range["A9"].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_total_room = 0, l_total_guest = 0; int l_begin_row = 8; int l_cnt = dt.Rows.Count; for (int l_row = 0; l_row < dt.Rows.Count; l_row++) { exSheet.Range[l_begin_row + l_row, 6, l_begin_row + l_row, 7].Merge(); exSheet.Cells[l_begin_row + l_row, 2].Value = dt.Rows[l_row]["total_amt"]; exSheet.Cells[l_begin_row + l_row, 3].Value = dt.Rows[l_row]["total_guest"]; exSheet.Cells[l_begin_row + l_row, 4].Value = dt.Rows[l_row]["total_deposit_amt_bk"]; exSheet.Cells[l_begin_row + l_row, 5].Value = dt.Rows[l_row]["room_type"]; exSheet.Cells[l_begin_row + l_row, 6].Value = dt.Rows[l_row]["room_no"]; } 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_60280060_Invoice_List.xls"; string TempFile = "../../../../system/temp/rpt_60280060_Invoice_List_" + 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_month = Request["p_month"]; string p_crt_by = Request["p_crt_by"]; string para = "'" + p_month + "','" + p_crt_by + "'"; DataTable dt = CtlLib.TableReadOpenCursor("ht_sel_60280060_month", para); // MASTER // INSERT ROWS for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++) { exSheet.Range["A9"].Rows.EntireRow.Insert();//insert row new of sheet } // SET DATA int pos = 8; int i = 0; for (i = 0; i < dt.Rows.Count; i++) { // exSheet.Cells[pos + i, 1 ].Value = i+1; exSheet.Cells[pos + i, 1].Value = dt.Rows[i]["invoice_no"]; // exSheet.Cells[pos + i, 3 ].Value = dt.Rows[i]["invoice_no"]; // exSheet.Cells[pos + i, 4 ].Value = dt.Rows[i]["pay_date"]; // exSheet.Cells[pos + i, 5 ].Value = dt.Rows[i]["guest_name"]; // exSheet.Cells[pos + i, 6 ].Value = dt.Rows[i]["checkin_date"]; // exSheet.Cells[pos + i, 7 ].Value = dt.Rows[i]["checkout_date"]; // exSheet.Cells[pos + i, 8 ].Value = dt.Rows[i]["partner_name"]; // exSheet.Cells[pos + i, 9 ].Value = dt.Rows[i]["description"]; } //--------- //exSheet.Cells[pos + i, 6 ].Value = "=sum(F10:F"+(pos+i-1)+")"; //exSheet.Cells[pos + i, 7].Value = "=sum(G10:G"+(pos+i-1)+")"; //--------- if (File.Exists(TempFile)) { File.Delete(TempFile); } // hide row firts range = exSheet.Range["A1"]; // hide row A5 // range.Rows.Hidden = true; // font bold header /*range = exSheet.Range["A1:AC1"]; * range.Rows[4].Font.Bold = true;*/ 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()); string l_from_date = Request["p_from_date"]; string l_to_date = Request["p_to_date"]; string TemplateFile = "rpt_bisc00030_DR07.xls"; string TempFile = "../../../../system/temp/rpt_bisc00030_DR07_" + 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 l_parameter = "'" + l_from_date + "','" + l_to_date + "'"; DataTable dtMaster = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00030_DR07_1", l_parameter); DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00030_DR07_2", l_parameter); for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++) { exSheet.Range["A12"].Rows.EntireRow.Insert();//insert row new of sheet } if (dtMaster.Rows.Count > 0) { exSheet.Cells["C8"].Value = dtMaster.Rows[0]["doc_date"].ToString(); } //----------------------- exSheet.Cells["L8"].Value = DateTime.Now.ToString("HH:mm"); int l_row = 0; // loop detail not percent for (l_row = 0; l_row < dtDetail.Rows.Count; l_row++) { exSheet.Cells[11 + l_row, 1].Value = dtDetail.Rows[l_row]["item_seq"]; exSheet.Cells[11 + l_row, 2].Value = dtDetail.Rows[l_row]["item_code"]; exSheet.Cells[11 + l_row, 3].Value = dtDetail.Rows[l_row]["virtual_code"]; exSheet.Cells[11 + l_row, 4].Value = dtDetail.Rows[l_row]["item_name"]; exSheet.Cells[11 + l_row, 5].Value = dtDetail.Rows[l_row]["virtual_name"]; exSheet.Cells[11 + l_row, 6].Value = dtDetail.Rows[l_row]["uom"]; exSheet.Cells[11 + l_row, 7].Value = dtDetail.Rows[l_row]["begin_act"]; exSheet.Cells[11 + l_row, 8].Value = dtDetail.Rows[l_row]["begin_doc"]; exSheet.Cells[11 + l_row, 9].Value = dtDetail.Rows[l_row]["income_qty"]; exSheet.Cells[11 + l_row, 10].Value = "=(H" + (11 + l_row) + "+I" + (11 + l_row) + ")"; exSheet.Cells[11 + l_row, 11].Value = "=(G" + (11 + l_row) + "+I" + (11 + l_row) + ")"; exSheet.Cells[11 + l_row, 12].Value = dtDetail.Rows[l_row]["outgo_qty"]; exSheet.Cells[11 + l_row, 13].Value = dtDetail.Rows[l_row]["return_qty"]; exSheet.Cells[11 + l_row, 14].Value = "=J" + (11 + l_row) + "-L" + (11 + l_row) + "+M" + (11 + l_row); exSheet.Cells[11 + l_row, 15].Value = dtDetail.Rows[l_row]["plan_qty"]; exSheet.Cells[11 + l_row, 16].Value = "=O" + (11 + l_row) + "/25"; exSheet.Cells[11 + l_row, 17].Value = dtDetail.Rows[l_row]["val01"]; exSheet.Cells[11 + l_row, 18].Value = "=K" + (11 + l_row) + "-L" + (11 + l_row) + "+M" + (11 + l_row); exSheet.Cells[11 + l_row, 19].Value = dtDetail.Rows[l_row]["unit_price"]; exSheet.Cells[11 + l_row, 20].Value = "=S" + (11 + l_row) + "*R" + (11 + l_row); exSheet.Cells[11 + l_row, 23].Value = "=R" + (11 + l_row) + "/(O" + (11 + l_row) + "/30)"; exSheet.Cells[11 + l_row, 24].Value = dtDetail.Rows[l_row]["po_qty"]; } exSheet.Cells[11 + l_row, 7].Value = "=SUM(G11:G" + (11 + l_row - 1) + ")"; exSheet.Cells[11 + l_row, 8].Value = "=SUM(H11:H" + (11 + l_row - 1) + ")"; exSheet.Cells[11 + l_row, 9].Value = "=SUM(I11:I" + (11 + l_row - 1) + ")"; exSheet.Cells[11 + l_row, 10].Value = "=SUM(J11:J" + (11 + l_row - 1) + ")"; exSheet.Cells[11 + l_row, 11].Value = "=SUM(K11:K" + (11 + l_row - 1) + ")"; exSheet.Cells[11 + l_row, 12].Value = "=SUM(L11:L" + (11 + l_row - 1) + ")"; exSheet.Cells[11 + l_row, 13].Value = "=SUM(M11:M" + (11 + l_row - 1) + ")"; exSheet.Cells[11 + l_row, 14].Value = "=SUM(N11:N" + (11 + l_row - 1) + ")"; exSheet.Cells[11 + l_row, 15].Value = "=SUM(O11:O" + (11 + l_row - 1) + ")"; exSheet.Cells[11 + l_row, 16].Value = "=SUM(P11:P" + (11 + l_row - 1) + ")"; exSheet.Cells[11 + l_row, 17].Value = "=SUM(Q11:Q" + (11 + l_row - 1) + ")"; exSheet.Cells[11 + l_row, 18].Value = "=SUM(R11:R" + (11 + l_row - 1) + ")"; exSheet.Cells[11 + l_row, 20].Value = "=SUM(T11:T" + (11 + l_row - 1) + ")"; exSheet.Cells[11 + l_row, 24].Value = "=SUM(X11:X" + (11 + l_row - 1) + ")"; // end loop detail percent if (File.Exists(TempFile)) { File.Delete(TempFile); } // font bold header /*range = exSheet.Range["A1:AC1"]; * range.Rows[4].Font.Bold = true;*/ exBook.SaveAs(TempFile); //write out to client broswer 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"; //Send file string to client Response.WriteFile(TempFile); //Stop execute Response.End(); }
protected void Page_Load(object sender, EventArgs e) { CtlLib.SetUser(Session["APP_DBUSER"].ToString()); string l_room_no_guest_group = Request["p_room"]; string l_report_type = Request["p_report_type"]; string TemplateFile = "rpt_60250260_inhouse_list_detail.xls"; string TempFile = "../../../../system/temp/rpt_60250260_inhouse_list_detail_" + 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, dtSum; para = "'" + l_room_no_guest_group + "','" + l_report_type + "'"; dt = CtlLib.TableReadOpenCursor("ht_rpt_60250260_new", para); //-----header--- //---end--header--- //--set-----DataBind--in---Master--- if (dt.Rows.Count == 0) { exSheet.Cells[3, 3].Value = "There is not data!"; } if (dt.Rows.Count > 0) { // exSheet.Cells[3, 1].Value = "Total : "+dt.Rows.Count+" Rooms."; exSheet.Cells[3, 13].Value = dt.Rows[0]["print_time"].ToString(); //exSheet.Cells[3, 1].Value = dt.Rows[0]["date_para"].ToString(); if (l_report_type == "1") { exSheet.Cells[2, 2].Value = "Inhouse list "; } if (l_report_type == "2") { exSheet.Cells[2, 2].Value = "Inhouse list (Normal)"; } if (l_report_type == "3") { exSheet.Cells[2, 2].Value = "Inhouse list (VIP)"; } } //---------------------- ////end-set-----DataBind--in---Master--- int l_row_first = 5, l_cnt = dt.Rows.Count; double tt_adult_total_guest = 0, tt_child_total_guest = 0, l_total_room = 0; for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++) { exSheet.Range["A6"].Rows.EntireRow.Insert();//insert row new of sheet } for (int l_row = 0; l_row < dt.Rows.Count; l_row++) { exSheet.Cells[l_row_first + l_row, 1].Value = dt.Rows[l_row]["guest_name"].ToString(); exSheet.Cells[l_row_first + l_row, 2].Value = dt.Rows[l_row]["checkin_date"].ToString(); exSheet.Cells[l_row_first + l_row, 3].Value = dt.Rows[l_row]["departure_date"].ToString(); if (dt.Rows[l_row]["room_no"].ToString() != "") { exSheet.Cells[l_row_first + l_row, 4].Value = double.Parse(dt.Rows[l_row]["room_no"].ToString()); } exSheet.Cells[l_row_first + l_row, 5].Value = dt.Rows[l_row]["room_code"].ToString(); if (dt.Rows[l_row]["room_rate"].ToString() != "") { exSheet.Cells[l_row_first + l_row, 6].Value = double.Parse(dt.Rows[l_row]["room_rate"].ToString()); } if (dt.Rows[l_row]["child_total_guest"].ToString() != "") { exSheet.Cells[l_row_first + l_row, 7].Value = double.Parse(dt.Rows[l_row]["child_total_guest"].ToString()); tt_child_total_guest = tt_child_total_guest + double.Parse(dt.Rows[l_row]["child_total_guest"].ToString()); } if (dt.Rows[l_row]["adult_total_guest"].ToString() != "") { exSheet.Cells[l_row_first + l_row, 8].Value = double.Parse(dt.Rows[l_row]["adult_total_guest"].ToString()); tt_adult_total_guest = tt_adult_total_guest + double.Parse(dt.Rows[l_row]["adult_total_guest"].ToString()); } exSheet.Cells[l_row_first + l_row, 9].Value = dt.Rows[l_row]["company_name"].ToString(); exSheet.Cells[l_row_first + l_row, 10].Value = dt.Rows[l_row]["nation"].ToString(); exSheet.Cells[l_row_first + l_row, 11].Value = dt.Rows[l_row]["request_master"].ToString(); exSheet.Cells[l_row_first + l_row, 12].Value = dt.Rows[l_row]["detail_request"].ToString(); //exSheet.Cells[l_row_first + l_row, 13].Value = dt.Rows[l_row]["adult_total_guest"].ToString(); special service exSheet.Cells[l_row_first + l_row, 14].Value = dt.Rows[l_row]["VIP"].ToString(); exSheet.Cells[l_row_first + l_row, 15].Value = dt.Rows[l_row]["birthday"].ToString(); } exSheet.Cells[l_row_first + l_cnt, 7].Value = tt_child_total_guest; exSheet.Cells[l_row_first + l_cnt, 8].Value = tt_adult_total_guest; //exSheet.Cells[3, 1].Value = "Total : "+l_total_room+" Room(s.)"; // 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()); //CtlLib.SetUser("SALE"); string TemplateFile = "rpt_dsbs00200_YJ03.xls"; string TempFile = "../../../../system/temp/rpt_dsbs00200_YJ03_" + 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_parent_pk = Request.QueryString["p_parent_pk"]; string para = "'" + p_parent_pk + "'"; DataTable dt = CtlLib.TableReadOpenCursor("st_lg_rpt_dsbs00200_YJ03_2", para); DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_dsbs00200_YJ03_3", para); if (dt.Rows.Count > 0) { exSheet.Cells["C6"].Value = dt.Rows[0]["slip_no"]; exSheet.Cells["F6"].Value = dt.Rows[0]["order_date"]; exSheet.Cells["B7"].Value = dt.Rows[0]["partner_name"]; exSheet.Cells["A12"].Value = dt.Rows[0]["PACKING_MARKS"]; } // INSERT ROWS for (int l_addrow = 0; l_addrow < dtDetail.Rows.Count - 1; l_addrow++) { exSheet.Range["A10"].Rows.EntireRow.Insert();//insert row new of sheet } // SET DATA int pos = 9; int i = 0; //Color color1 = ColorTranslator.FromHtml("0xCCFFFF"); //Color color2 = ColorTranslator.FromHtml("0xFFFFCC"); for (i = 0; i < dtDetail.Rows.Count; i++) { exSheet.Cells[pos + i, 1].Value = dtDetail.Rows[i]["ref_no"]; exSheet.Cells[pos + i, 2].Value = dtDetail.Rows[i]["description"]; exSheet.Cells[pos + i, 3].Value = dtDetail.Rows[i]["color"]; exSheet.Cells[pos + i, 4].Value = dtDetail.Rows[i]["ord_qty"]; exSheet.Cells[pos + i, 5].Value = dtDetail.Rows[i]["ord_uom"]; exSheet.Cells[pos + i, 6].Value = dtDetail.Rows[i]["unit_price"]; exSheet.Cells[pos + i, 7].Value = dtDetail.Rows[i]["item_amount"]; exSheet.Cells[pos + i, 8].Value = dtDetail.Rows[i]["month_plan"]; exSheet.Cells[pos + i, 9].Value = dtDetail.Rows[i]["item_etd"]; //------------- } int t = pos + i - 1; exSheet.Cells[pos + i, 4].Value = "=sum(D9:D" + t + ")"; exSheet.Cells[pos + i, 7].Value = "=sum(G4:G" + t + ")"; //--------- if (File.Exists(TempFile)) { File.Delete(TempFile); } // hide row firts range = exSheet.Range["A1"]; // hide row A5 // range.Rows.Hidden = true; // font bold header /*range = exSheet.Range["A1:AC1"]; * range.Rows[4].Font.Bold = true;*/ 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()); string l_dt_from = Request["p_dtfrm"]; string l_dt_to = Request["p_dtto"]; string TemplateFile = "rpt_60300010_complimentary.xls"; string TempFile = "../../../../system/temp/rpt_60300010_complimentary_" + 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, dtSum; para = "'" + l_dt_from + "','" + l_dt_to + "'"; dt = CtlLib.TableReadOpenCursor("rpt_60300010_com", para); //-----header--- //---end--header--- //--set-----DataBind--in---Master--- if (dt.Rows.Count == 0) { exSheet.Cells[6, 1].Value = "There is not data!"; } if (dt.Rows.Count > 0) { //exSheet.Cells[3, 1].Value = "Date :"+l_dt_from.Substring(6, 2)+"/"+l_dt_from.Substring(4, 2)+"/"+l_dt_from.Substring(0,4); exSheet.Cells[3, 6].Value = dt.Rows[0]["print_time"].ToString(); exSheet.Cells[3, 1].Value = dt.Rows[0]["date_para"].ToString(); } //---------------------- ////end-set-----DataBind--in---Master--- for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++) { exSheet.Range["A7"].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; int l_first = 5; if (dt.Rows.Count > 0) { for (int l_row = 0; l_row < dt.Rows.Count; l_row++) { exSheet.Cells[l_first + l_row, 1].Value = dt.Rows[l_row]["last_name"].ToString(); exSheet.Cells[l_first + l_row, 2].Value = dt.Rows[l_row]["total_guest"].ToString(); exSheet.Cells[l_first + l_row, 3].Value = dt.Rows[l_row]["ROOM_TYPE"].ToString(); if (dt.Rows[l_row]["room_no"].ToString() != "") { exSheet.Cells[l_first + l_row, 4].Value = double.Parse(dt.Rows[l_row]["room_no"].ToString()); } exSheet.Cells[l_first + l_row, 5].Value = dt.Rows[l_row]["checkin_date"].ToString(); exSheet.Cells[l_first + l_row, 6].Value = dt.Rows[l_row]["departure_date"].ToString(); exSheet.Cells[l_first + l_row, 7].Value = dt.Rows[l_row]["company_name"].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; // 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 l_fr = Request["p_fr"]; string l_to = Request["p_to"]; string l_loc = Request["p_loc"]; string TemplateFile = "rpt_60270030_Item_Type.xls"; string TempFile = "../../../../system/temp/rpt_60270030_Item_Type_" + 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 sheet = exBook.Worksheets[1]; IRange range; //bind data to excel file string para = ""; string[][] title = new string[2][] { new string[] { "1", "2", "6", "7", "8", "9", "10", "11", "12", "13" }, new string[] { "Item Code", "Item Name", "Unit", "Q.Ty", "Price", "Sub", "Service", "Tax", "Discount", "Total" } }; int row = 6, tt_dt = 0; DataTable dt; para = "'" + l_fr + "','" + l_to + "','" + l_loc + "'"; dt = CtlLib.TableReadOpenCursor("rpt_60270030_item_type", para); if (dt.Rows.Count > 0) { sheet.Cells[4, 5].Value = dt.Rows[0][12]; sheet.Cells[4, 11].Value = dt.Rows[0][13]; sheet.Cells[5, 5].Value = dt.Rows[0][14]; for (int y = 0; y < dt.Rows.Count; y++) { if (dt.Rows[y][10].ToString() != "" && dt.Rows[y][1].ToString() == "") { row++; for (int x = 0; x < title[0].Length; x++) { sheet.Cells[row, int.Parse(title[0][x])].Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlDot; sheet.Cells[row, int.Parse(title[0][x])].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous; if (title[0][x] == "2") { sheet.Cells[row, int.Parse(title[0][x]), row, int.Parse(title[0][x + 1]) - 1].Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlDot; sheet.Cells[row, int.Parse(title[0][x]), row, int.Parse(title[0][x + 1]) - 1].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous; } switch (x) { case 0: sheet.Cells[row, int.Parse(title[0][x])].Value = "Total " + dt.Rows[y][10].ToString() + " " + dt.Rows[y][11].ToString(); sheet.Cells[row, int.Parse(title[0][x])].Font.Bold = true; sheet.Cells[row, int.Parse(title[0][x])].Font.Size = 8; sheet.Cells[row, int.Parse(title[0][x])].Font.Color = Color.Black; sheet.Cells[row, int.Parse(title[0][x])].WrapText = false; sheet.Cells[row, int.Parse(title[0][x])].VerticalAlignment = XlVAlign.xlVAlignCenter; sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignLeft; sheet.Cells[row, int.Parse(title[0][x])].RowHeight = 20; break; case 3: sheet.Cells[row, int.Parse(title[0][x])].Value = double.Parse(dt.Rows[y][x].ToString()); sheet.Cells[row, int.Parse(title[0][x])].NumberFormat = "###,###,##0"; sheet.Cells[row, int.Parse(title[0][x])].Font.Bold = true; sheet.Cells[row, int.Parse(title[0][x])].Font.Size = 8; sheet.Cells[row, int.Parse(title[0][x])].Font.Color = Color.Black; sheet.Cells[row, int.Parse(title[0][x])].WrapText = false; sheet.Cells[row, int.Parse(title[0][x])].VerticalAlignment = XlVAlign.xlVAlignCenter; sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignCenter; sheet.Cells[row, int.Parse(title[0][x])].RowHeight = 20; break; case 5: case 6: case 7: case 8: case 9: sheet.Cells[row, int.Parse(title[0][x])].Value = double.Parse(dt.Rows[y][x].ToString()); sheet.Cells[row, int.Parse(title[0][x])].NumberFormat = "###,###,##0"; sheet.Cells[row, int.Parse(title[0][x])].Font.Bold = true; sheet.Cells[row, int.Parse(title[0][x])].Font.Size = 8; sheet.Cells[row, int.Parse(title[0][x])].Font.Color = Color.Black; sheet.Cells[row, int.Parse(title[0][x])].WrapText = false; sheet.Cells[row, int.Parse(title[0][x])].VerticalAlignment = XlVAlign.xlVAlignCenter; sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignRight; sheet.Cells[row, int.Parse(title[0][x])].RowHeight = 20; break; } } row++; sheet.Cells[row, 1].RowHeight = 20; } else if (dt.Rows[y][10].ToString() != "" && dt.Rows[y][1].ToString() != "") { if (y == 0 || (y > 0 && dt.Rows[y - 1][10].ToString() != "" && dt.Rows[y - 1][1].ToString() == "")) { if (y == 0) { for (int x = 0; x < title[0].Length; x++) { sheet.Cells[row, int.Parse(title[0][x])].Value = "'" + title[1][x]; sheet.Cells[row, int.Parse(title[0][x])].Font.Bold = true; sheet.Cells[row, int.Parse(title[0][x])].Font.Color = Color.Black; sheet.Cells[row, int.Parse(title[0][x])].VerticalAlignment = XlVAlign.xlVAlignCenter; sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignCenter; sheet.Cells[row, int.Parse(title[0][x])].RowHeight = 20; sheet.Cells[row, int.Parse(title[0][x])].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous; if (title[0][x] == "2") { sheet.Cells[row, int.Parse(title[0][x]), row, int.Parse(title[0][x + 1]) - 1].Merge(); sheet.Cells[row, int.Parse(title[0][x]), row, int.Parse(title[0][x + 1]) - 1].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous; } } } row++; for (int x = 0; x < title[0].Length; x++) { switch (x) { case 0: sheet.Cells[row, int.Parse(title[0][x])].Value = dt.Rows[y][10].ToString() + " " + dt.Rows[y][11].ToString(); sheet.Cells[row, int.Parse(title[0][x])].Font.Bold = true; sheet.Cells[row, int.Parse(title[0][x])].Font.Size = 8; sheet.Cells[row, int.Parse(title[0][x])].Font.Color = Color.Black; sheet.Cells[row, int.Parse(title[0][x])].WrapText = false; sheet.Cells[row, int.Parse(title[0][x])].VerticalAlignment = XlVAlign.xlVAlignBottom; sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignLeft; sheet.Cells[row, int.Parse(title[0][x])].RowHeight = 20; sheet.Cells[row, int.Parse(title[0][x])].Font.Underline = XlUnderlineStyle.xlUnderlineStyleSingle; break; } } } row++; for (int x = 0; x < title[0].Length; x++) { //sheet.Cells[row, int.Parse(title[0][x])].Font.Bold = true; sheet.Cells[row, int.Parse(title[0][x])].Font.Color = Color.Black; sheet.Cells[row, int.Parse(title[0][x])].WrapText = false; sheet.Cells[row, int.Parse(title[0][x])].Font.Size = 8; sheet.Cells[row, int.Parse(title[0][x])].VerticalAlignment = XlVAlign.xlVAlignCenter; sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignLeft; sheet.Cells[row, int.Parse(title[0][x])].RowHeight = 15; switch (x) { case 0: sheet.Cells[row, int.Parse(title[0][x])].Value = dt.Rows[y][x].ToString(); break; case 1: sheet.Cells[row, int.Parse(title[0][x])].Value = dt.Rows[y][x].ToString(); sheet.Cells[row, int.Parse(title[0][x]), row, int.Parse(title[0][x + 1]) - 1].Merge(); break; case 2: sheet.Cells[row, int.Parse(title[0][x])].Value = dt.Rows[y][x].ToString(); sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignCenter; break; case 3: sheet.Cells[row, int.Parse(title[0][x])].Value = double.Parse(dt.Rows[y][x].ToString()); sheet.Cells[row, int.Parse(title[0][x])].NumberFormat = "###,###,##0"; sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignCenter; break; default: sheet.Cells[row, int.Parse(title[0][x])].Value = double.Parse(dt.Rows[y][x].ToString()); sheet.Cells[row, int.Parse(title[0][x])].NumberFormat = "###,###,##0"; sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignRight; break; } } } else if (dt.Rows[y][10].ToString() == "" && dt.Rows[y][1].ToString() == "") { row++; for (int x = 0; x < title[0].Length; x++) { switch (x) { case 3: sheet.Cells[row, int.Parse(title[0][x])].Value = double.Parse(dt.Rows[y][x].ToString()); sheet.Cells[row, int.Parse(title[0][x])].NumberFormat = "###,###,##0"; sheet.Cells[row, int.Parse(title[0][x])].Font.Bold = true; sheet.Cells[row, int.Parse(title[0][x])].Font.Size = 8; sheet.Cells[row, int.Parse(title[0][x])].Font.Color = Color.Black; sheet.Cells[row, int.Parse(title[0][x])].WrapText = false; sheet.Cells[row, int.Parse(title[0][x])].VerticalAlignment = XlVAlign.xlVAlignCenter; sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignCenter; sheet.Cells[row, int.Parse(title[0][x])].RowHeight = 20; break; case 5: case 6: case 7: case 8: case 9: sheet.Cells[row, int.Parse(title[0][x])].Value = double.Parse(dt.Rows[y][x].ToString()); sheet.Cells[row, int.Parse(title[0][x])].NumberFormat = "###,###,##0"; sheet.Cells[row, int.Parse(title[0][x])].Font.Bold = true; sheet.Cells[row, int.Parse(title[0][x])].Font.Size = 8; sheet.Cells[row, int.Parse(title[0][x])].Font.Color = Color.Black; sheet.Cells[row, int.Parse(title[0][x])].WrapText = false; sheet.Cells[row, int.Parse(title[0][x])].VerticalAlignment = XlVAlign.xlVAlignCenter; sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignRight; sheet.Cells[row, int.Parse(title[0][x])].RowHeight = 20; break; } } } } } // end loop detail percent if (File.Exists(TempFile)) { File.Delete(TempFile); } // hide row firts range = sheet.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 l_dtfrm = Request["p_from"]; string l_dtto = Request["p_to"]; string l_guest_name = Request["p_guest_name"]; string l_parameter = "'" + l_dtfrm + "','" + l_dtto + "'"; string TemplateFile = "rpt_6028010_revenue_item.xls"; string TempFile = "../../../../system/temp/rpt_6028010_revenue_item_" + 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 //DataTable dtMst = CtlLib.TableReadOpenCursor("acnt.sp_rpt_gfrp00010_mst", l_parameter); DataTable dtDetail = CtlLib.TableReadOpenCursor("ht_rpt_60280100_1", l_parameter); //-----header--- //---end--header--- //--set-----DataBind--in---Master--- if (dtDetail.Rows.Count > 0) { exSheet.Cells[3, 1].Value = dtDetail.Rows[0]["date_para"].ToString(); exSheet.Cells[3, 7].Value = dtDetail.Rows[0]["print_time"].ToString(); } //---------------------- int l_first_row1 = 6; for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++) { exSheet.Range[l_first_row1 + l_addrow, 1].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_first_row = 5; int l_cnt = dtDetail.Rows.Count; double l_total_qty = 0, l_total_amt = 0; for (int l_row = 1; l_row <= dtDetail.Rows.Count; l_row++) { exSheet.Cells[l_first_row + l_row, 1].Value = dtDetail.Rows[l_row - 1]["invoice_date"]; exSheet.Cells[l_first_row + l_row, 2].Value = dtDetail.Rows[l_row - 1]["invoice_id"]; exSheet.Cells[l_first_row + l_row, 3].Value = dtDetail.Rows[l_row - 1]["item_name"]; exSheet.Cells[l_first_row + l_row, 4].Value = dtDetail.Rows[l_row - 1]["item_qty"]; if (dtDetail.Rows[l_row - 1]["item_qty"].ToString() != "") { l_total_qty = l_total_qty + double.Parse(dtDetail.Rows[l_row - 1]["item_qty"].ToString()); } exSheet.Cells[l_first_row + l_row, 5].Value = dtDetail.Rows[l_row - 1]["price"]; exSheet.Cells[l_first_row + l_row, 6].Value = dtDetail.Rows[l_row - 1]["amount"]; exSheet.Cells[l_first_row + l_row, 7].Value = dtDetail.Rows[l_row - 1]["discount_amt"]; exSheet.Cells[l_first_row + l_row, 8].Value = dtDetail.Rows[l_row - 1]["total_due"]; if (dtDetail.Rows[l_row - 1]["total_due"].ToString() != "") { l_total_amt = l_total_amt + double.Parse(dtDetail.Rows[l_row - 1]["total_due"].ToString()); } if (l_row < dtDetail.Rows.Count) { if (dtDetail.Rows[l_row - 1]["invoice_id"].ToString() == dtDetail.Rows[l_row]["invoice_id"].ToString()) { exSheet.Range[l_first_row + l_row, 1, l_first_row + l_row + 1, 1].Merge(); exSheet.Range[l_first_row + l_row, 2, l_first_row + l_row + 1, 2].Merge(); exSheet.Range[l_first_row + l_row, 8, l_first_row + l_row + 1, 8].Merge(); //l_long_term=l_long_term+1; } } } //exSheet.Cells[6 + l_cnt, 3].Value=l_total_qty; //exSheet.Cells[6 + l_cnt, 5].Value=l_total_amt; // 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 /*range = exSheet.Range["A1:AC1"]; * range.Rows[4].Font.Bold = true;*/ exBook.SaveAs(TempFile); //write out to client broswer 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"; //Send file string to client Response.WriteFile(TempFile); //Stop execute Response.End(); }
protected void Page_Load(object sender, EventArgs e) { CtlLib.SetUser(Session["APP_DBUSER"].ToString()); string p_tco_org_pk, p_wg, p_from, p_to, p_txtID, p_work_mon, p_lang; p_tco_org_pk = Request["p_tco_org_pk"].ToString(); p_wg = Request["p_wg"].ToString(); p_from = Request["p_from_dt"].ToString(); p_to = Request["p_to_dt"].ToString(); p_txtID = Request["p_emp_search"].ToString(); p_work_mon = Request["p_month"].ToString(); p_lang = Request["p_lang"].ToString(); string TemplateFile; if (p_lang == "ENG") { TemplateFile = "rpt_hrti00400_2_eng.xls"; } else { TemplateFile = "rpt_hrti00400_2_vie.xls"; } string TempFile = "../../../../system/temp/rpt_hrti00400_2_" + 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; string para = "'" + p_tco_org_pk + "','" + p_wg + "','" + p_work_mon + "','" + p_from + "','" + p_to + "','" + p_txtID + "'"; DataTable dt_detail = CtlLib.TableReadOpenCursor("ST_HR_RPT_HRTI00400_2", para); string sql_day = "select substr(c.car_date,-2), c.hol_type from thr_calendar c " + "where c.del_if=0 " + "and c.car_date between '" + p_from + "' and '" + p_to + "' " + "and c.tco_company_pk = (select g.tco_company_pk " + " from tco_org g " + " where g.del_if = 0 and g.pk = '" + p_tco_org_pk + "') " + " order by c.car_date "; DataTable dt_day = CtlLib.TableReadOpen(sql_day); string sql_date = "select " + " to_char(to_date('" + p_work_mon + "', 'yyyymm'), 'mm yyyy') " + " from dual "; DataTable dt_date = CtlLib.TableReadOpen(sql_date); if (dt_detail.Rows.Count == 0) { Response.Write("Không có dữ liệu!"); Response.End(); } int row_loop = 5; int l_col = dt_detail.Columns.Count - 31; int row_emp = dt_detail.Rows.Count; int row_day = dt_day.Rows.Count; int count = 0; int s_row = 4; int pos = 4; int s_col = 8; int temp = 0; int l_col_pass = 6; for (int d = 0; d < dt_day.Rows.Count; d++) { exSheet.Cells[4, s_col + d].Value = dt_day.Rows[d][0].ToString(); } for (int l_addrow = 1; l_addrow < dt_detail.Rows.Count; l_addrow++) { exSheet.Range[row_loop + l_addrow, 1].Rows.EntireRow.Insert(); //insert row new of sheet } if (p_from != "" && p_to != "") { if (p_lang == "ENG") { exSheet.Cells["A2"].Value = "From Date " + dt_detail.Rows[0][0].ToString() + " -----" + " To Date " + dt_detail.Rows[0][1].ToString(); } else { exSheet.Cells["A2"].Value = "Từ ngày " + dt_detail.Rows[0][0].ToString() + " -----" + " Đến ngày " + dt_detail.Rows[0][1].ToString(); } } // xu ly du lieu do vao excel if (dt_detail.Rows.Count > 0) { for (int l_row = 0; l_row < dt_detail.Rows.Count; l_row++) { count++; exSheet.Cells[row_loop + l_row, 1].Value = count; // do du lieu vao cac dong for (int col = 2; col < l_col; col++) { if (IsNumeric(dt_detail.Rows[l_row][col].ToString()) && col != 2) { exSheet.Cells[row_loop + l_row, col].Value = double.Parse(dt_detail.Rows[l_row][col].ToString()); } else { exSheet.Cells[row_loop + l_row, col].Value = dt_detail.Rows[l_row][col].ToString(); } } pos++; for (int col = s_col; col < s_col + 31; col++) { if (dt_detail.Rows[l_row][col + 31].ToString() == "SUN") { exSheet.Range[row_loop + l_row, col].Interior.Color = Color.SkyBlue; } if (dt_detail.Rows[l_row][col + 31].ToString() == "HOL") { exSheet.Range[row_loop + l_row, col].Interior.Color = Color.Pink; if (dt_detail.Rows[l_row][col].ToString() == "") { exSheet.Cells[row_loop + l_row, col].Value = "PH"; } } } } } for (int row = 0; row < row_day; row++) { temp = 31 - row_day; switch (temp) { case 3: exSheet.Range["AJ1"].Columns.Hidden = true; exSheet.Range["AK1"].Columns.Hidden = true; exSheet.Range["AL1"].Columns.Hidden = true; break; case 2: exSheet.Range["AK1"].Columns.Hidden = true; exSheet.Range["AL1"].Columns.Hidden = true; break; case 1: exSheet.Range["AL1"].Columns.Hidden = true; break; } switch (dt_day.Rows[row][1].ToString()) { case "SUN": exSheet.Range[s_row, row + s_col].Interior.Color = Color.Green; break; case "HOL": exSheet.Range[s_row, row + s_col, s_row, row + s_col].Interior.Color = Color.Pink; break; } } if (File.Exists(TempFile)) { File.Delete(TempFile); } // hide row firts range = exSheet.Range["A1"]; // hide row A5 // range.Rows.Hidden = true; // font bold header /*range = exSheet.Range["A1:AC1"]; * range.Rows[4].Font.Bold = true;*/ 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()); string TemplateFile = "rpt_bisc00150_grand.xls"; string TempFile = "../../../../system/temp/rpt_bisc00150_grand_" + 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_lang = Request.QueryString["p_lang"]; string p_item_group = Request.QueryString["p_item_grp"]; string p_item = Request.QueryString["p_item"]; string p_wh_type = Request.QueryString["p_wh_type"]; string p_wh = Request.QueryString["p_wh"]; string p_wh_name = Request.QueryString["p_wh_name"]; string p_use_yn = Request.QueryString["p_use_yn"]; string p_rad_type = Request.QueryString["p_rad_type"]; string p_dt_from = Request.QueryString["p_dt_from"]; string para = "'" + p_lang + "','" + p_item_group + "','" + p_item + "','" + p_wh_type + "','" + p_wh + "','" + p_use_yn + "','" + p_rad_type + "','" + p_dt_from + "'"; DataTable dtInfo = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00150_2", "'" + Session["User_ID"].ToString() + "'"); DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00150_1", para); //Set Data In Master if (!string.IsNullOrEmpty(dtInfo.Rows[0]["pic"].ToString())) { exSheet.Cells[1, 1].Select(); byte[] MyData = new byte[0]; MyData = (byte[])dtInfo.Rows[0]["pic"]; MemoryStream stream = new MemoryStream(MyData); Bitmap b = new Bitmap(stream); exSheet.Shapes.AddPicture(ResizeImage(b, 100, 55)); } exSheet.Cells["C1"].Value = dtInfo.Rows[0]["partner_name"].ToString(); exSheet.Cells["C2"].Value = dtInfo.Rows[0]["addr1"].ToString(); exSheet.Cells["C3"].Value = "Tax code: " + dtInfo.Rows[0]["tax_code"].ToString(); exSheet.Cells["I1"].Value = "Tell: " + dtInfo.Rows[0]["phone_no"].ToString(); exSheet.Cells["I1"].Value = "Fax: " + dtInfo.Rows[0]["fax_no"].ToString(); exSheet.Cells["I1"].Value = "Print date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm"); exSheet.Cells["F5"].Value = System.DateTime.ParseExact(p_dt_from, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy"); exSheet.Cells["A6"].Value = String.Format("W/H name: {0}", p_wh_name); exSheet.Cells["G6"].Value = String.Format("Date: {0}", System.DateTime.ParseExact(p_dt_from, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy").ToString()); //insert row new of sheet for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++) { exSheet.Range["A9"].Rows.EntireRow.Insert(); } //sum variable double sum_stock_qty = 0; double sum_qty1 = 0; double sum_qty2 = 0; double sum_qty3 = 0; double sum_qty4 = 0; double sum_qty5 = 0; double sum_stock_qty_sub = 0; double sum_qty1_sub = 0; double sum_qty2_sub = 0; double sum_qty3_sub = 0; double sum_qty4_sub = 0; double sum_qty5_sub = 0; double tmp_stock_qty = 0; double tmp_qty1 = 0; double tmp_qty2 = 0; double tmp_qty3 = 0; double tmp_qty4 = 0; double tmp_qty5 = 0; //Set data int l_pos = 7; for (int i = 0; i < dtDetail.Rows.Count; i++) { l_pos += 1; if (i > 0 && dtDetail.Rows[i]["tlg_it_itemgrp_pk"].ToString() != dtDetail.Rows[i - 1]["tlg_it_itemgrp_pk"].ToString()) { exSheet.Cells[l_pos, 4].Value = String.Format("{0:0,0}", sum_stock_qty_sub); exSheet.Cells[l_pos, 7].Value = String.Format("{0:0,0}", sum_qty1_sub); exSheet.Cells[l_pos, 10].Value = String.Format("{0:0,0}", sum_qty2_sub); exSheet.Cells[l_pos, 13].Value = String.Format("{0:0,0}", sum_qty3_sub); exSheet.Cells[l_pos, 16].Value = String.Format("{0:0,0}", sum_qty4_sub); exSheet.Cells[l_pos, 19].Value = String.Format("{0:0,0}", sum_qty5_sub); FormatTotalRange(exSheet, l_pos, 1, l_pos, 20); exSheet.Cells[l_pos, 1, l_pos, 3].Merge(); l_pos += 1; sum_stock_qty_sub = 0; sum_qty1_sub = 0; sum_qty2_sub = 0; sum_qty3_sub = 0; sum_qty4_sub = 0; sum_qty5_sub = 0; } exSheet.Cells[l_pos, 1].Value = dtDetail.Rows[i]["grp_nm"]; exSheet.Cells[l_pos, 2].Value = dtDetail.Rows[i]["item_code"]; exSheet.Cells[l_pos, 2].Font.Bold = true; exSheet.Cells[l_pos, 3].Value = dtDetail.Rows[i]["item_name"]; exSheet.Cells[l_pos, 4].Value = dtDetail.Rows[i]["stock_qty"]; exSheet.Cells[l_pos, 4].Font.Bold = true; exSheet.Cells[l_pos, 5].Value = dtDetail.Rows[i]["uom"]; exSheet.Cells[l_pos, 6].Value = dtDetail.Rows[i]["trans_rate_1"]; exSheet.Cells[l_pos, 7].Value = dtDetail.Rows[i]["qty1"]; exSheet.Cells[l_pos, 7].Font.Bold = true; exSheet.Cells[l_pos, 8].Value = dtDetail.Rows[i]["trans_uom_1"]; exSheet.Cells[l_pos, 9].Value = dtDetail.Rows[i]["trans_rate_2"]; exSheet.Cells[l_pos, 10].Value = dtDetail.Rows[i]["qty2"]; exSheet.Cells[l_pos, 10].Font.Bold = true; exSheet.Cells[l_pos, 11].Value = dtDetail.Rows[i]["trans_uom_2"]; exSheet.Cells[l_pos, 12].Value = dtDetail.Rows[i]["trans_rate_3"]; exSheet.Cells[l_pos, 13].Value = dtDetail.Rows[i]["qty3"]; exSheet.Cells[l_pos, 13].Font.Bold = true; exSheet.Cells[l_pos, 14].Value = dtDetail.Rows[i]["trans_uom_3"]; exSheet.Cells[l_pos, 15].Value = dtDetail.Rows[i]["trans_rate_4"]; exSheet.Cells[l_pos, 16].Value = dtDetail.Rows[i]["qty4"]; exSheet.Cells[l_pos, 16].Font.Bold = true; exSheet.Cells[l_pos, 17].Value = dtDetail.Rows[i]["trans_uom_4"]; exSheet.Cells[l_pos, 18].Value = dtDetail.Rows[i]["trans_rate_5"]; exSheet.Cells[l_pos, 19].Value = dtDetail.Rows[i]["qty5"]; exSheet.Cells[l_pos, 19].Font.Bold = true; exSheet.Cells[l_pos, 20].Value = dtDetail.Rows[i]["trans_uom_5"]; FormatNormalRange(exSheet, l_pos, 1, l_pos, 20); if (double.TryParse(dtDetail.Rows[i]["stock_qty"].ToString(), out tmp_stock_qty)) { sum_stock_qty_sub += tmp_stock_qty; sum_stock_qty += tmp_stock_qty; } if (double.TryParse(dtDetail.Rows[i]["qty1"].ToString(), out tmp_qty1)) { sum_qty1_sub += tmp_qty1; sum_qty1 += tmp_qty1; } if (double.TryParse(dtDetail.Rows[i]["qty2"].ToString(), out tmp_qty2)) { sum_qty2_sub += tmp_qty2; sum_qty2 += tmp_qty2; } if (double.TryParse(dtDetail.Rows[i]["qty3"].ToString(), out tmp_qty3)) { sum_qty3_sub += tmp_qty3; sum_qty3 += tmp_qty3; } if (double.TryParse(dtDetail.Rows[i]["qty4"].ToString(), out tmp_qty4)) { sum_qty4_sub += tmp_qty4; sum_qty4 += tmp_qty4; } if (double.TryParse(dtDetail.Rows[i]["qty5"].ToString(), out tmp_qty5)) { sum_qty5_sub += tmp_qty5; sum_qty5 += tmp_qty5; } } //The final sub total row l_pos += 1; exSheet.Cells[l_pos, 4].Value = String.Format("{0:0,0}", sum_stock_qty_sub); exSheet.Cells[l_pos, 7].Value = String.Format("{0:0,0}", sum_qty1_sub); exSheet.Cells[l_pos, 10].Value = String.Format("{0:0,0}", sum_qty2_sub); exSheet.Cells[l_pos, 13].Value = String.Format("{0:0,0}", sum_qty3_sub); exSheet.Cells[l_pos, 16].Value = String.Format("{0:0,0}", sum_qty4_sub); exSheet.Cells[l_pos, 19].Value = String.Format("{0:0,0}", sum_qty5_sub); FormatTotalRange(exSheet, l_pos, 1, l_pos, 20); exSheet.Cells[l_pos, 1, l_pos, 3].Merge(); //The total row l_pos += 1; exSheet.Cells[l_pos, 4].Value = String.Format("{0:0,0}", sum_stock_qty); exSheet.Cells[l_pos, 7].Value = String.Format("{0:0,0}", sum_qty1); exSheet.Cells[l_pos, 10].Value = String.Format("{0:0,0}", sum_qty2); exSheet.Cells[l_pos, 13].Value = String.Format("{0:0,0}", sum_qty3); exSheet.Cells[l_pos, 16].Value = String.Format("{0:0,0}", sum_qty4); exSheet.Cells[l_pos, 19].Value = String.Format("{0:0,0}", sum_qty5); FormatTotalRange(exSheet, l_pos, 1, l_pos, 20); exSheet.Cells[l_pos, 1, l_pos, 3].Merge(); exSheet.Cells[l_pos, 1, l_pos, 3].HorizontalAlignment = XlHAlign.xlHAlignCenter; exSheet.Cells[l_pos, 1, l_pos, 3].Value = "GRAND TOTAL"; exSheet.Cells[l_pos, 1, l_pos, 3].Font.Bold = false; // end loop detail percent 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()); 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(); if (dt.Rows[0]["total_due_vnd_pp"].ToString() != "") { exSheet.Cells[17, 5].Value = double.Parse(dt.Rows[0]["total_due_vnd_pp"].ToString()); // } if (dt.Rows[0]["total_due_usd_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 if (dt.Rows[0]["svc_amt"].ToString() != "") { exSheet.Cells[19, 5].Value = double.Parse(dt.Rows[0]["svc_amt"].ToString()); } if (dt.Rows[0]["svc_amt_usd"].ToString() != "") { exSheet.Cells[19, 7].Value = float.Parse(dt.Rows[0]["svc_amt_usd"].ToString()); } if (dt.Rows[0]["total_vnd"].ToString() != "") { exSheet.Cells[20, 5].Value = double.Parse(dt.Rows[0]["total_vnd"].ToString()); } if (dt.Rows[0]["total_usd"].ToString() != "") { exSheet.Cells[20, 7].Value = float.Parse(dt.Rows[0]["total_usd"].ToString()); } if (dt.Rows[0]["vat_amt"].ToString() != "") { exSheet.Cells[21, 5].Value = double.Parse(dt.Rows[0]["vat_amt"].ToString()); } if (dt.Rows[0]["vat_amt_usd"].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()); if (dt.Rows[0]["total_due_end_vnd"].ToString() != "") { exSheet.Cells[23, 5].Value = double.Parse(dt.Rows[0]["total_due_end_vnd"].ToString()); } if (dt.Rows[0]["total_due_usd"].ToString() != "") { exSheet.Cells[23, 7].Value = float.Parse(dt.Rows[0]["total_due_usd"].ToString()); } if (dt.Rows[0]["cash_vnd"].ToString() != "") { exSheet.Cells[24, 3].Value = double.Parse(dt.Rows[0]["cash_vnd"].ToString()); } if (dt.Rows[0]["cash_usd"].ToString() != "") { exSheet.Cells[24, 5].Value = float.Parse(dt.Rows[0]["cash_usd"].ToString()); } if (dt.Rows[0]["credit_vnd"].ToString() != "") { exSheet.Cells[25, 3].Value = double.Parse(dt.Rows[0]["credit_vnd"].ToString()); } if (dt.Rows[0]["credit_usd"].ToString() != "") { exSheet.Cells[25, 5].Value = float.Parse(dt.Rows[0]["credit_usd"].ToString()); } if (double.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 (double.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("sale"); string TemplateFile = "rpt_dscd00070_KB_4.xls"; string TempFile = "../../../../system/temp/rpt_dscd00070_KB_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 master_pk = "'" + Request.QueryString["master_pk"] + "'"; DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_RPT_DSCD00070_KB_1", master_pk); master_pk = "'" + Request.QueryString["master_pk"] + "','" + Session["User_ID"].ToString() + "'"; DataTable dtMaster = CtlLib.TableReadOpenCursor("st_lg_rpt_dscd00070_KB", master_pk); // MASTER if (dtMaster.Rows.Count > 0) { StringBuilder sb = new StringBuilder(); sb.Append("Số: "); sb.Append(dtMaster.Rows[0]["slip_no"].ToString() + " / "); sb.Append("Ngày " + dtMaster.Rows[0]["out_dd"].ToString() + " "); sb.Append("Tháng " + dtMaster.Rows[0]["out_mm"].ToString() + " "); sb.Append("Năm " + dtMaster.Rows[0]["out_yy"].ToString()); exSheet.Cells["C2"].Value = sb.ToString(); exSheet.Cells["C4"].Value = dtMaster.Rows[0]["partner_name"].ToString(); exSheet.Cells["C5"].Value = dtMaster.Rows[0]["addr1"].ToString(); exSheet.Cells["C6"].Value = dtMaster.Rows[0]["tax_code"].ToString(); exSheet.Cells["C7"].Value = dtMaster.Rows[0]["phone_no"].ToString(); exSheet.Cells["F7"].Value = dtMaster.Rows[0]["fax_no"].ToString(); exSheet.Cells["C8"].Value = dtMaster.Rows[0]["loc_addr"].ToString(); } // END MASTER for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++) { exSheet.Range["A13"].Rows.EntireRow.Insert();//insert row new of sheet } // DETAIL int pos = 12; for (int l_row = 0; l_row < dtDetail.Rows.Count; l_row++) { exSheet.Cells[pos + l_row, 1].Value = l_row + 1; exSheet.Cells[pos + l_row, 2].Value = dtDetail.Rows[l_row]["item"]; exSheet.Cells[pos + l_row, 2, pos + l_row, 4].Merge(); exSheet.Cells[pos + l_row, 5].Value = dtDetail.Rows[l_row]["out_qty"]; exSheet.Cells[pos + l_row, 6].Value = dtDetail.Rows[l_row]["unit_price"]; exSheet.Cells[pos + l_row, 7].Value = "=E" + (pos + l_row) + "*F" + (pos + l_row); exSheet.Cells[pos + l_row, 8].Value = dtDetail.Rows[l_row]["description"]; } // END DETAIL exSheet.Cells["F" + (dtDetail.Rows.Count + 12)].Value = "=SUM(G12:G" + (dtDetail.Rows.Count + 11) + ")"; exSheet.Cells["F" + (dtDetail.Rows.Count + 13)].Value = "=F" + (dtDetail.Rows.Count + 12) + "*" + dtDetail.Rows[0]["tax_rate"] + "%"; exSheet.Cells["F" + (dtDetail.Rows.Count + 14)].Value = "=F" + (dtDetail.Rows.Count + 12) + "+" + "F" + (dtDetail.Rows.Count + 13); exSheet.Cells["H" + (dtDetail.Rows.Count + 14)].Value = "(" + dtMaster.Rows[0]["tr_ccy"].ToString() + ")"; if (File.Exists(TempFile)) { File.Delete(TempFile); } // hide row firts range = exSheet.Range["A1"]; // hide row A5 // range.Rows.Hidden = true; // font bold header /*range = exSheet.Range["A1:AC1"]; * range.Rows[4].Font.Bold = true;*/ 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()); string p_year = Request["p_year"]; string TemplateFile = "rpt_60300010_nationality_mkt_segment_yearly.xls"; string TempFile = "../../../../system/temp/rpt_60300010_nationality_mkt_segment_yearly_" + 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, dtSum; para = "'" + p_year + "'"; dt = CtlLib.TableReadOpenCursor("rpt_60300010_mkt_segment_year", para); //-----header--- //---end--header--- //--set-----DataBind--in---Master--- if (dt.Rows.Count == 0) { exSheet.Cells[6, 1].Value = "There is not data!"; } /*if (dt.Rows.Count > 0) * { * exSheet.Cells[3, 1].Value = dt.Rows[0]["date_para"].ToString(); * exSheet.Cells[3, 9].Value = dt.Rows[0]["print_time"].ToString(); * exSheet.Cells[4, 11].Value = dt.Rows[0]["date_header"].ToString(); * }*/ //---------------------- ////end-set-----DataBind--in---Master--- /*for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++) * { * exSheet.Range["A7"].Rows.EntireRow.Insert();//insert row new of sheet * * }*/ for (int l_addrow_1 = 0; l_addrow_1 < dt.Rows.Count - 1; l_addrow_1++) //add row show with data of table { exSheet.Range[5 + l_addrow_1 + 1, 1].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; for (int l_row = 0; l_row < dt.Rows.Count; l_row++) { exSheet.Cells[3, 28].Value = "Total " + p_year; exSheet.Cells[5 + l_row, 2].Value = dt.Rows[l_row]["nation"]; exSheet.Cells[5 + l_row, 3].Value = dt.Rows[l_row]["customer_type"]; exSheet.Cells[5 + l_row, 4].Value = dt.Rows[l_row]["mm1"]; exSheet.Cells[5 + l_row, 5].Value = dt.Rows[l_row]["rate_mm1"]; exSheet.Cells[5 + l_row, 6].Value = dt.Rows[l_row]["mm2"]; exSheet.Cells[5 + l_row, 7].Value = dt.Rows[l_row]["rate_mm2"]; exSheet.Cells[5 + l_row, 8].Value = dt.Rows[l_row]["mm3"]; exSheet.Cells[5 + l_row, 9].Value = dt.Rows[l_row]["rate_mm3"]; exSheet.Cells[5 + l_row, 10].Value = dt.Rows[l_row]["mm4"]; exSheet.Cells[5 + l_row, 11].Value = dt.Rows[l_row]["rate_mm4"]; exSheet.Cells[5 + l_row, 12].Value = dt.Rows[l_row]["mm5"]; exSheet.Cells[5 + l_row, 13].Value = dt.Rows[l_row]["rate_mm5"]; exSheet.Cells[5 + l_row, 14].Value = dt.Rows[l_row]["mm6"]; exSheet.Cells[5 + l_row, 15].Value = dt.Rows[l_row]["rate_mm6"]; exSheet.Cells[5 + l_row, 16].Value = dt.Rows[l_row]["mm7"]; exSheet.Cells[5 + l_row, 17].Value = dt.Rows[l_row]["rate_mm7"]; exSheet.Cells[5 + l_row, 18].Value = dt.Rows[l_row]["mm8"]; exSheet.Cells[5 + l_row, 19].Value = dt.Rows[l_row]["rate_mm8"]; exSheet.Cells[5 + l_row, 20].Value = dt.Rows[l_row]["mm9"]; exSheet.Cells[5 + l_row, 21].Value = dt.Rows[l_row]["rate_mm9"]; exSheet.Cells[5 + l_row, 22].Value = dt.Rows[l_row]["mm10"]; exSheet.Cells[5 + l_row, 23].Value = dt.Rows[l_row]["rate_mm10"]; exSheet.Cells[5 + l_row, 24].Value = dt.Rows[l_row]["mm11"]; exSheet.Cells[5 + l_row, 25].Value = dt.Rows[l_row]["rate_mm11"]; exSheet.Cells[5 + l_row, 26].Value = dt.Rows[l_row]["mm12"]; exSheet.Cells[5 + l_row, 27].Value = dt.Rows[l_row]["rate_mm12"]; exSheet.Cells[5 + l_row, 28].Value = dt.Rows[l_row]["total_year"]; exSheet.Cells[5 + l_row, 29].Value = dt.Rows[l_row]["rate_year"]; Color color2 = ColorTranslator.FromHtml("0xFFFFCC"); if (dt.Rows[l_row]["customer_type"].ToString() == "" && dt.Rows[l_row]["mm1"].ToString() != "") { exSheet.Cells[5 + l_row, 3, 5 + l_row, 29].Interior.Color = color2; //Color.Green; } if (dt.Rows[l_row]["nation"].ToString() != "" && dt.Rows[l_row]["customer_type"].ToString() == "" && dt.Rows[l_row]["mm1"].ToString() != "") { exSheet.Cells[5 + l_row, 3].Value = "Sub Total"; //SUBTOTAL; exSheet.Cells[5 + l_row, 29].Value = "100%"; } if (dt.Rows[l_row]["nation"].ToString() == "" && dt.Rows[l_row]["customer_type"].ToString() == "" && dt.Rows[l_row]["mm1"].ToString() != "") { exSheet.Cells[5 + l_row, 3].Value = "Total"; //TOTAL; exSheet.Cells[5 + l_row, 29].Value = "100%"; exSheet.Cells[5 + l_row, 5].Value = "100%"; exSheet.Cells[5 + l_row, 7].Value = "100%"; exSheet.Cells[5 + l_row, 9].Value = "100%"; exSheet.Cells[5 + l_row, 11].Value = "100%"; exSheet.Cells[5 + l_row, 13].Value = "100%"; exSheet.Cells[5 + l_row, 15].Value = "100%"; exSheet.Cells[5 + l_row, 17].Value = "100%"; exSheet.Cells[5 + l_row, 19].Value = "100%"; exSheet.Cells[5 + l_row, 21].Value = "100%"; exSheet.Cells[5 + l_row, 23].Value = "100%"; exSheet.Cells[5 + l_row, 25].Value = "100%"; exSheet.Cells[5 + l_row, 27].Value = "100%"; } if (l_row > 1 && l_row < dt.Rows.Count) { if (dt.Rows[l_row - 1]["nation"].ToString() == dt.Rows[l_row]["nation"].ToString()) { exSheet.Range[l_row + 4, 2, l_row + 5, 2].Merge(); //l_long_term=l_long_term+1; } } if (l_row > 0 && l_row == 1) { if (dt.Rows[l_row - 1]["nation"].ToString() == dt.Rows[l_row]["nation"].ToString()) { exSheet.Range[l_row + 4, 2, l_row + 5, 2].Merge(); //l_long_term=l_long_term+1; } } } // 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("sale"); string l_date = Request["p_date"]; string l_wh = Request["p_wh"]; string l_item = Request["p_item"]; string l_parameter = "'" + l_date + "','" + l_wh + "','" + l_item + "'"; string TemplateFile = "rpt_bisc00090.xls"; string TempFile = "../../../../system/temp/rpt_bisc00090_" + 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 //-----header--- DataTable dtHeader = CtlLib.TableReadOpenCursor("LG_SEL_BISC00090_HEADER", l_parameter); int count = 5; for (int i = 0; i < dtHeader.Rows.Count; i++) { exSheet.Cells[4, count].Value = dtHeader.Rows[i][0].ToString(); count++; } //---end--header--- DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_BISC00090", l_parameter); for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++) { exSheet.Range["A6"].Rows.EntireRow.Insert();//insert row new of sheet } // loop detail not percent for (int l_row = 1; l_row <= dtDetail.Rows.Count; l_row++) { for (int col = 0; col <= 15; col++) { if (col == 0 || col == 1 || col == 2 || col == 3) { exSheet.Cells[4 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1][col].ToString(); } else { if (col >= 4 && col <= 15) { if (!string.IsNullOrEmpty(dtDetail.Rows[l_row - 1][col].ToString())) { exSheet.Cells[4 + l_row, col + 1].Value = float.Parse(dtDetail.Rows[l_row - 1][col].ToString()); } else { exSheet.Cells[4 + l_row, col + 1].Value = string.Empty; } } } } } // 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 /*range = exSheet.Range["A1:AC1"]; * range.Rows[4].Font.Bold = true;*/ exBook.SaveAs(TempFile); //write out to client broswer 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"; //Send file string to client Response.WriteFile(TempFile); //Stop execute Response.End(); }
protected void Page_Load(object sender, EventArgs e) { CtlLib.SetUser(Session["APP_DBUSER"].ToString()); string l_dt_from = Request["p_dtfrm"]; string l_dt_to = Request["p_dtto"]; string TemplateFile = "rpt_60300010_complimentary_BK.xls"; string TempFile = "../../../../system/temp/rpt_60300010_complimentary_BK_" + 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, dtSum; para = "'" + l_dt_from + "','" + l_dt_to + "'"; dt = CtlLib.TableReadOpenCursor("rpt_60300010_company_stt_bk", para); //-----header--- //---end--header--- //--set-----DataBind--in---Master--- if (dt.Rows.Count == 0) { exSheet.Cells[6, 1].Value = "There is not data!"; } if (dt.Rows.Count > 0) { exSheet.Cells[3, 1].Value = dt.Rows[0]["date_para"].ToString(); exSheet.Cells[3, 10].Value = dt.Rows[0]["print_time"].ToString(); exSheet.Cells[4, 12].Value = dt.Rows[0]["date_header"].ToString(); } //---------------------- ////end-set-----DataBind--in---Master--- for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++) { exSheet.Range["A7"].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 <= 14; col++) { if (col == 0 || col == 1 || col == 14) { exSheet.Cells[5 + l_row, col + 1].Value = dt.Rows[l_row - 1][col].ToString(); exSheet.Cells[l_row + 5, col + 1].HorizontalAlignment = XlHAlign.xlHAlignLeft; } else { if (col == 4 || col == 7 || col == 10 || col == 13) { exSheet.Cells[5 + l_row, col + 1].Value = float.Parse(dt.Rows[l_row - 1][col].ToString()); exSheet.Cells[5, col + 1, l_row + 5, col + 1].NumberFormat = "#,##0.00"; exSheet.Cells[l_row + 5, col + 1].HorizontalAlignment = XlHAlign.xlHAlignRight; } else { exSheet.Cells[5 + l_row, col + 1].Value = double.Parse(dt.Rows[l_row - 1][col].ToString()); exSheet.Cells[5, col + 1, l_row + 5, col + 1].NumberFormat = "#,##0"; exSheet.Cells[l_row + 5, col + 1].HorizontalAlignment = XlHAlign.xlHAlignRight; } } } } } // end loop detail percent if (File.Exists(TempFile)) { File.Delete(TempFile); } 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("hr"); CtlLib.SetUser(Session["APP_DBUSER"].ToString()); string p_dept, p_group, p_status, p_type, p_pay_type, p_emp_id, p_work_mon, p_m_type, p_sal_period, p_emp_type; p_dept = Request["l_dept"].ToString(); p_group = Request["l_group"].ToString(); p_status = Request["l_status"].ToString(); p_type = Request["l_type"].ToString(); p_pay_type = Request["l_pay_type"].ToString(); p_emp_id = Request["l_emp_id"].ToString(); p_work_mon = Request["l_work_mon"].ToString(); p_sal_period = Request["l_sal_period"].ToString(); p_emp_type = Request["l_Emp_Type"].ToString(); p_m_type = Request.QueryString["l_m_type"].ToString(); //p_from_leftdate = Request["l_from_leftdate"].ToString(); //p_to_leftdate = Request["l_to_leftdate"].ToString(); string TemplateFile = "rpt_hrpm00700_0.xls"; string TempFile = "../../../../system/temp/rpt_hrpm00700_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]; IRange range; if (p_type == "ALL") { p_type = "= NVL (a.net_amt, 0)"; } string SQL = "SELECT A.TCO_DEPT_PK dept_pk,'', " + " a.DEPT_NM dept_nm, " + " A.EMP_ID emp_id_2, " + " A.FULL_NAME full_nm_3, " + " TO_CHAR (TO_DATE (A.JOIN_DT, 'yyyymmdd'), 'dd/mm/yyyy') join_dt_4, " + " TO_CHAR (TO_DATE (A.left_DT, 'yyyymmdd'), 'dd/mm/yyyy') left_dt_5, " + " A.POS_NM pos_nm_6, " + " (SELECT code_nm " + " FROM vhr_hr_code " + " WHERE id = 'HR0117' AND code = a.times) " + " times_74, " + " (CASE " + " WHEN NVL (a.CONFIRM_DT, a.from_dt) > a.from_dt " + " THEN " + " ROUND (NVL (A.SALARY_LEVEL1, 0), 10) " + " ELSE " + " 0 " + " END) " + " sal_level1_7, " + " (CASE " + " WHEN NVL (a.CONFIRM_DT, a.to_dt) > a.to_DT THEN 0 " + " ELSE ROUND (NVL (A.SALARY_LEVEL2, 0), 10) " + " END) " + " sal_level2_8, " + " ROUND (NVL (A.ALLOW_AMT1, 0), 10) allow_amt1_9, " + " ROUND (NVL (A.ALLOW_AMT2, 0), 10) allow_amt2_10, " + " ROUND (NVL (A.ALLOW_AMT3, 0), 10) allow_amt3_11, " + " ROUND (NVL (A.ALLOW_AMT4, 0), 10) allow_amt4_12, " + " ROUND (NVL (A.ALLOW_AMT5, 0), 10) allow_amt5_13, " + " ROUND (NVL (A.ALLOW_AMT6, 0), 10) allow_amt6_14, " + " ROUND (NVL (A.ALLOW_AMT7, 0), 10) allow_amt7_15, " + " ROUND (NVL (A.ALLOW_AMT8, 0), 10) allow_amt8_16, " + " ROUND (NVL (A.ALLOW_AMT9, 0), 10) allow_amt1_9, " + " ROUND (NVL (A.ALLOW_AMT10, 0), 10) allow_amt1_9, " + " ROUND (NVL (A.ALLOW_AMT11, 0), 10) allow_amt1_9, " + " ROUND (NVL (A.ALLOW_AMT12, 0), 10) allow_amt1_9, " + " ROUND (NVL (A.ALLOW_AMT1, 0) + NVL (A.ALLOW_AMT2, 0) + NVL (A.ALLOW_AMT3, 0) + NVL (A.ALLOW_AMT4, 0)" + " + NVL (A.ALLOW_AMT5, 0) + NVL (A.ALLOW_AMT6, 0) + NVL (A.ALLOW_AMT7, 0) + NVL (A.ALLOW_AMT8, 0)" + " + NVL (A.ALLOW_AMT9, 0) + NVL (A.ALLOW_AMT10, 0) + NVL (A.ALLOW_AMT11, 0) + NVL (A.ALLOW_AMT12, 0), 0) total_allow, " + " ROUND (NVL (A.ALLOW_K1_AMT, 0), 10) allow_k1_amt_38, " + " ROUND (NVL (A.ALLOW_K2_AMT, 0), 10) allow_k2_amt_39, " + " ROUND (NVL (A.ALLOW_K3_AMT, 0), 10) allow_k3_amt_40, " + " ROUND (NVL (A.ALLOW_K4_AMT, 0), 10) allow_k4_amt_41, " + " ROUND (NVL (A.ALLOW_K5_AMT, 0), 10) allow_k5_amt_42, " + " ROUND (NVL (A.ALLOW_K6_AMT, 0), 10) allow_k6_amt_43, " + " ROUND (NVL (A.ALLOW_K7_AMT, 0), 10) allow_k7_amt_44, " + " ROUND (NVL (A.ALLOW_K8_AMT, 0), 10) allow_k8_amt_45, " + " ROUND (NVL (A.WT_L1, 0) / 8 + NVL (a.HOL_DAY_L1, 0), 10) wd1_17, " + " ROUND (NVL (A.WT_L2, 0) / 8 + NVL (a.HOL_DAY_L2, 0), 10) wd2_18, " + " ROUND (NVL (A.TOTAL_ABS_NPAY_L1, 0) / 8, 10) abs_npay_19, " + " ROUND (NVL (A.TOTAL_ABS_NPAY_L2, 0) / 8, 10) abs_npay_20, " + " ROUND (NVL (A.WT_L1_AMT, 0), 10) wd1_17, " + " ROUND (NVL (A.WT_L2_AMT, 0), 10) wd2_18, " + " ROUND (NVL (A.ABS_ALE_L1, 0) / 8, 10) abs_ale1_19, " + " ROUND (NVL (A.ABS_ALE_L2, 0) / 8, 10) abs_ale2_20, " + " ROUND (NVL (A.ABS_ALE_L1_AMT, 0), 10) abs_ale1_19, " + " ROUND (NVL (A.ABS_ALE_L2_AMT, 0), 10) abs_ale2_20, " + " ROUND (NVL (A.ABS_OTHER_PAY_L1, 0) / 8, 10) abs_other1_21, " + " ROUND (NVL (A.ABS_OTHER_PAY_L2, 0) / 8, 10) abs_other2_22, " + " ROUND (NVL (A.ABS_OTHER_PAY_L1_AMT, 0), 10) abs_other1_21, " + " ROUND (NVL (A.ABS_OTHER_PAY_L2_AMT, 0), 10) abs_other2_22, " + " 0 abs_12_1_19, "+ " 0 abs_12_2_20, "+ " 0 abs_12_1_19, "+ " 0 abs_12_2_20, "+ //" ROUND (NVL (A.ABS_12_L1, 0) / 8, 10) abs_12_1_19, " + //" ROUND (NVL (A.ABS_12_L2, 0) / 8, 10) abs_12_2_20, " + //" ROUND (NVL (A.ABS_12_L1_AMT, 0), 10) abs_12_1_19, " + //" ROUND (NVL (A.ABS_12_L2_AMT, 0), 10) abs_12_2_20, " + " ROUND (NVL (A.WT_L1_AMT, 0) + NVL (A.WT_L2_AMT, 0) + NVL (A.HOL_DAY_L1_AMT, 0) + NVL (A.HOL_DAY_L2_AMT, 0), 10) sal_wd_23, " + " ROUND (NVL (A.WT_L1, 0) + NVL (a.HOL_DAY_L1, 0), 10) wd1_17, " + " ROUND (NVL (A.WT_L2, 0) + NVL (a.HOL_DAY_L2, 0), 10) wd2_18, " + " ROUND (NVL (A.OT_L1, 0), 10) ot_l1_24, " + " ROUND (NVL (A.OT_L2, 0), 10) ot_l2_25, " + " ROUND (NVL (A.OT_L1_amt, 0), 10) ot_l1_amt_24, " + " ROUND (NVL (A.OT_L2_amt, 0), 10) ot_l2_amt_25, " + " ROUND (NVL (A.ST_L1, 0), 10) st_l1_24, " + " ROUND (NVL (A.ST_L2, 0), 10) st_l2_25, " + " ROUND (NVL (A.ST_L1_amt, 0), 10) st_l1_amt_24, " + " ROUND (NVL (A.ST_L2_amt, 0), 10) st_l2_amt_25, " + " ROUND (NVL (A.OST_L1, 0), 10) ost_l1_24, " + " ROUND (NVL (A.OST_L2, 0), 10) ost_l2_25, " + " ROUND (NVL (A.OST_L1_amt, 0), 10) ost_l1_amt_24, " + " ROUND (NVL (A.OST_L2_amt, 0), 10) ost_l2_amt_25, " + " ROUND (NVL (A.HT_L1, 0), 10) ht_l1_24, " + " ROUND (NVL (A.HT_L2, 0), 10) ht_l2_25, " + " ROUND (NVL (A.HT_L1_amt, 0), 10) ht_l1_amt_24, " + " ROUND (NVL (A.HT_L2_amt, 0), 10) ht_l2_amt_25, " + " ROUND (NVL (A.OHT_L1, 0), 10) oht_l1_24, " + " ROUND (NVL (A.OHT_L2, 0), 10) oht_l2_25, " + " ROUND (NVL (A.OHT_L1_amt, 0), 10) oht_l1_amt_24, " + " ROUND (NVL (A.OHT_L2_amt, 0), 10) oht_l2_amt_25, " + " ROUND (NVL (A.NT_30_L1, 0), 10) nt_30_l1_28, " + " ROUND (NVL (A.NT_30_L2, 0), 10) nt_30_l2_29, " + " ROUND (NVL (A.NT_30_L1_amt, 0), 10) nt_30_l1_amt_28, " + " ROUND (NVL (A.NT_30_L2_amt, 0), 10) nt_30_l2_amt_29, " + " ROUND (NVL (A.NT_45_L1, 0), 10) nt_45_l1_30, " + " ROUND (NVL (A.NT_45_L2, 0), 10) nt_45_l2_31, " + " ROUND (NVL (A.NT_45_L1_amt, 0), 10) nt_45_l1_amt_30, " + " ROUND (NVL (A.NT_45_L2_amt, 0), 10) nt_45_l2_amt_31, " + " ROUND (NVL (A.NT_45_2_L1, 0), 10) nt_45_l1_30, " + " ROUND (NVL (A.NT_45_2_L2, 0), 10) nt_45_l2_31, " + " ROUND (NVL (A.NT_45_2_L1_amt, 0), 10) nt_45_l1_amt_30, " + " ROUND (NVL (A.NT_45_2_L2_amt, 0), 10) nt_45_l2_amt_31, " + " ROUND (NVL (A.NT_60_L1, 0), 10) nt_60_l1_32, " + " ROUND (NVL (A.NT_60_L2, 0), 10) nt_60_l2_33, " + " ROUND (NVL (A.NT_60_L1_amt, 0), 10) nt_60_l1_amt_32, " + " ROUND (NVL (A.NT_60_L2_amt, 0), 10) nt_60_l2_amt_33, " + " ROUND (NVL (A.NOST_L1, 0), 10) nt_60_l1_32, " + " ROUND (NVL (A.NOST_L2, 0), 10) nt_60_l2_33, " + " ROUND (NVL (A.NOST_L1_amt, 0), 10) nt_60_l1_amt_32, " + " ROUND (NVL (A.NOST_L2_amt, 0), 10) nt_60_l2_amt_33, " + " ROUND (NVL (A.NT_90_L1, 0), 10) nt_90_l1_34, " + " ROUND (NVL (A.NT_90_L2, 0), 10) nt_90_l2_35, " + " ROUND (NVL (A.NT_90_L1_amt, 0), 10) nt_90_l1_amt_34, " + " ROUND (NVL (A.NT_90_L2_amt, 0), 10) nt_90_l2_amt_35, " + " ROUND (NVL (A.NOHT_L1, 0), 10) nt_90_l1_34, " + " ROUND (NVL (A.NOHT_L2, 0), 10) nt_90_l2_35, " + " ROUND (NVL (A.NOHT_L1_amt, 0), 10) nt_90_l1_amt_34, " + " ROUND (NVL (A.NOHT_L2_amt, 0), 10) nt_90_l2_amt_35, " + " ROUND (NVL (A.TOTAL_OT_L1_AMT, 0) + NVL (A.TOTAL_OT_L2_AMT, 0), 10) " + " total_ot_amt_36, " + " ROUND (NVL (A.OT_TAX_L1_AMT, 0) + NVL (A.OT_TAX_L2_AMT, 0), 10) " + " ot_tax_amt_37, " + " ROUND (NVL (A.RETURN_AMT, 0), 10) return_amt_46, " + " ROUND (NVL (A.INC_AMT, 0) + NVL (A.BONUS_YEARLY_AMT, 0), 10) " + " incentive_amt_47, " + " ROUND (NVL (A.ALE_STOP, 0), 10) ale_stop_48, " + " ROUND (NVL (A.ALE_STOP_AMT, 0), 10) ale_stop_amt_49, " + " ROUND (NVL (A.SEVERANCE_MONTH, 0), 10) serverance_mon_50, " + " ROUND (NVL (A.SEVERANCE_AMT, 0), 10) serverance_amt_51, " + " ROUND (NVL (A.GROSS_AMT, 0), 10) gross_amt_52, " + " ROUND (NVL (A.BREAK_CONTRACT_AMT, 0), 10) break_con_amt_53, " + " ROUND (NVL (A.ADVANCE_AMT, 0), 10) advance_amt_54, " + " ROUND (NVL (A.ADD_FOR_TAX, 0), 10) add_for_tax, " + " ROUND (NVL (A.GROSS_AMT_2, 0), 10) gross_amt_2_52, " + " ROUND (NVL (A.DEDUCT_PIT, 0), 10) deduct_pit_55, " + " ROUND (NVL (A.INCOME_AMT_2, 0), 10) income_amt_56, " + " ROUND (NVL (A.INCOME_BEFORE_TAX, NVL (A.INCOME_AMT, 0)), 10) " + " income_bef_amt_57, " + " ROUND (NVL (A.INCOME_TAX, 0), 10) income_tax_58, " + " ROUND (NVL (A.UNION_AMT, 0), 10) union_amt_59, " + " ROUND (NVL (A.SOCIAL_AMT, 0), 10) social_amt_60, " + " ROUND (NVL (A.HEALTH_AMT, 0), 10) health_amt_61, " + " ROUND (NVL (A.UNEMP_INS, 0), 10) unemp_ins_amt_62, " + " ROUND (NVL (A.RETURN_NO_PIT, 0) + NVL (A.ADJ_FOR_TAX, 0), 10) " + " return_no_pit_63, " + " ROUND (NVL (A.ADVANCE_NO_PIT, 0), 10) advance_no_pit_64, " + " ROUND (NVL (A.health_card_amt, 0), 10) break_con_amt_53, " + " ROUND (NVL (A.EQUIP1_AMT, 0), 10) equip1_amt_66, " + " ROUND (NVL (A.EQUIP2_AMT, 0), 10) equip2_amt_67, " + " ROUND (NVL (A.EQUIP3_AMT, 0), 10) equip3_amt_68, " + " ROUND (NVL (A.EQUIP4_AMT, 0), 10) equip4_amt_69, " + " ROUND (NVL (A.EQUIP5_AMT, 0), 10) equip5_amt_70, " + " ROUND (NVL (A.EQUIP6_AMT, 0), 10) equip6_amt_71, " + " ROUND (NVL (A.EQUIP7_AMT, 0), 10) equip7_amt_72, " + " ROUND (NVL (A.EQUIP8_AMT, 0), 10) equip8_amt_73, " + " ROUND (NVL (A.health_card_amt, 0) + NVL (A.EQUIP1_AMT, 0) - NVL (A.EQUIP2_AMT, 0) - NVL (A.EQUIP3_AMT, 0) - NVL (A.EQUIP4_AMT, 0) - NVL (A.EQUIP5_AMT, 0) - NVL (A.EQUIP6_AMT, 0) - NVL (A.EQUIP7_AMT, 0) - NVL (A.EQUIP8_AMT, 0), 10) total_deduct, " + " ROUND (NVL (A.actual_deduct, 0), 10) actual_deduct, " + " ROUND (NVL (A.ADVANCE_AMT2, 0), 10) advance_amt_2, " + " ROUND (NVL (A.UNIFORM_AMT, 0), 10) uniform_amt, " + " DECODE ('" + p_m_type + "', " + " 'ALL', ROUND (NVL (A.NET_AMT, 0), 0), " + " '01', ROUND (NVL (A.NET_AMT, 0), 0), " + " ROUND (NVL (A.NET_AMT, 0), 10)) " + " net_amt_65,'', " + " ROUND (NVL (A.SOCIAL_COMPANY, 0), 10) social_com, " + " ROUND (NVL (A.HEALTH_COMPANY, 0), 10) health_com, " + " ROUND (NVL (A.UNEMP_COM_INS, 0), 10) unemp_com, " + " ROUND (NVL (A.UNION_COMPANY, 0), 10) union_com, " + " (SELECT TO_CHAR (TO_DATE (m.from_dt, 'yyyymmdd'), 'dd-mm-yy') " + " || ' to ' " + " || TO_CHAR (TO_DATE (m.to_dt, 'yyyymmdd'), 'dd-mm-yy') " + " FROM thr_close m " + " WHERE m.del_if = 0 " + " AND m.ID = '" + p_sal_period + "' " + " AND m.month_close = '" + p_work_mon + "' " + " AND m.times = NVL (a.times, '0')) " + " period_resign_75 " + " FROM thr_month_salary a, tco_org b, thr_work_group c " + " WHERE a.del_if = 0 " + " AND a.work_mon = '" + p_work_mon + "' " + " AND b.del_if = 0 " + " AND c.del_if = 0 " + " AND a.tco_dept_pk = b.pk " + " AND a.thr_group_pk = c.pk " + " AND (a.tco_dept_pk IN " + " ( SELECT g.pk " + " FROM tco_org g " + " WHERE g.del_if = 0 " + " START WITH g.pk = " + " DECODE ('" + p_dept + "', " + " 'ALL', 0, " + " '" + p_dept + "') " + " CONNECT BY PRIOR g.pk = g.p_pk) " + " OR '" + p_dept + "' = 'ALL') " + " AND DECODE ('" + p_group + "', 'ALL', 'ALL', a.thr_group_pk) = '" + p_group + "' " + " AND a.emp_id LIKE '%" + p_emp_id + "%' " + " AND DECODE ('" + p_status + "', 'ALL', 'ALL', a.status) = '" + p_status + "' " + " AND DECODE ('" + p_pay_type + "', 'ALL', 'ALL', a.pay_type) = '" + p_pay_type + "' " + " and decode('" + p_emp_type + "' ,'ALL','ALL',A.EMPLOYEE_TYPE)='" + p_emp_type + "' " + " AND DECODE ('" + p_m_type + "', 'ALL', 'ALL', a.MONEY_KIND) = '" + p_m_type + "' " + " AND DECODE ('" + p_sal_period + "', 'ALL', 'ALL', A.SAL_PERIOD) = '" + p_sal_period + "' " + " AND NVL (a.net_amt, 0) " + p_type + "ORDER BY A.DEPT_NM, a.emp_id "; //Response.Write(SQL); //Response.End(); DataTable dt_emp = CtlLib.TableReadOpen(SQL); int irow_emp, icol_emp; irow_emp = dt_emp.Rows.Count; icol_emp = dt_emp.Columns.Count; if (irow_emp == 0) { Response.Write("There is no data of salary"); Response.End(); } string para_com = "'" + p_dept + "','" + p_work_mon + "'"; DataTable dt_com = CtlLib.TableReadOpenCursor("ST_HR_RPT_CO_COMPANY", para_com); DataTable dt_allow = CtlLib.TableReadOpenCursor("ST_HR_RPT_CO_ALLOW_SALARY"); DataTable dt_allow_k = CtlLib.TableReadOpenCursor("ST_HR_RPT_CO_ALLOW_K_SALARY"); DataTable dt_equip = CtlLib.TableReadOpenCursor("ST_HR_RPT_CO_EQUIP"); SQL = "SELECT NT_RATE nt30, " + " NOT_RATE nt45, " + " NOT_2_RATE nt45_2, " + " OST_RATE ost, " + " NOST_RATE nost, " + " OHT_RATE oht, " + " NOHT_RATE noht, " + " NST_RATE nt60, " + " NHT_RATE nt90 " + " FROM THR_MONTH_OT_RATE " + " WHERE del_if = 0 AND work_mon = '" + p_work_mon + "' "; DataTable dt_rate = CtlLib.TableReadOpen(SQL); int i, j, k, i_allow; int pos = 7; int allow = 11, allow_k = 24, equip = 125; //vi tri phu cap int pos_nt = 73; int emp_dept = 0, n_dept = 0; Double[] sub_total = new Double[icol_emp]; Double[] grant_total = new Double[icol_emp]; //bind data to excel file //-----header--- exSheet.Cells[1, 1].Select(); exSheet.Cells["C1"].Value = dt_com.Rows[0][0].ToString(); exSheet.Cells["C2"].Value = dt_com.Rows[0][1].ToString(); exSheet.Cells["C3"].Value = "Phone: " + dt_com.Rows[0][2].ToString(); exSheet.Cells[1, pos_nt].Value = "EMPLOYEE'S PAYROLL IN " + dt_com.Rows[0][4].ToString(); exSheet.Cells[2, pos_nt].Value = "BẢNG LƯƠNG NHÂN VIÊN THÁNG " + dt_com.Rows[0][5].ToString(); exSheet.Cells[4, pos_nt].Value = string.Format("NT {0}%", dt_rate.Rows[0]["NT30"].ToString()); exSheet.Cells[5, pos_nt].Value = string.Format("Trợ cấp đêm", dt_rate.Rows[0]["NT30"].ToString()); pos_nt += 2; exSheet.Cells[4, pos_nt].Value = string.Format("NT {0}% Amount", dt_rate.Rows[0]["NT30"].ToString()); exSheet.Cells[5, pos_nt].Value = string.Format("Tiền trợ cấp đêm {0}%", dt_rate.Rows[0]["NT30"].ToString()); pos_nt += 2; exSheet.Cells[4, pos_nt].Value = string.Format("NOT {0}%", dt_rate.Rows[0]["NT45"].ToString()); exSheet.Cells[5, pos_nt].Value = string.Format("Trợ cấp T.C đêm", dt_rate.Rows[0]["NT45"].ToString()); pos_nt += 2; exSheet.Cells[4, pos_nt].Value = string.Format("NOT {0}% Amount", dt_rate.Rows[0]["NT45"].ToString()); exSheet.Cells[5, pos_nt].Value = string.Format("Tiền trợ T.C cấp đêm {0}%", dt_rate.Rows[0]["NT45"].ToString()); pos_nt += 2; exSheet.Cells[4, pos_nt].Value = string.Format("NOT {0}%", dt_rate.Rows[0]["NT45_2"].ToString()); exSheet.Cells[5, pos_nt].Value = string.Format("Trợ cấp T.C đêm", dt_rate.Rows[0]["NT45_2"].ToString()); pos_nt += 2; exSheet.Cells[4, pos_nt].Value = string.Format("NOT {0}% Amount", dt_rate.Rows[0]["NT45_2"].ToString()); exSheet.Cells[5, pos_nt].Value = string.Format("Tiền trợ cấp T.C đêm {0}%", dt_rate.Rows[0]["NT45_2"].ToString()); pos_nt += 2; exSheet.Cells[4, pos_nt].Value = string.Format("NST {0}%", dt_rate.Rows[0]["NT60"].ToString()); exSheet.Cells[5, pos_nt].Value = string.Format("Trợ cấp đêm chủ nhật", dt_rate.Rows[0]["NT60"].ToString()); pos_nt += 2; exSheet.Cells[4, pos_nt].Value = string.Format("NST {0}% Amount", dt_rate.Rows[0]["NT60"].ToString()); exSheet.Cells[5, pos_nt].Value = string.Format("Tiền trợ cấp đêm chủ nhật {0}%", dt_rate.Rows[0]["NT60"].ToString()); pos_nt += 2; exSheet.Cells[4, pos_nt].Value = string.Format("NOST {0}%", dt_rate.Rows[0]["NOST"].ToString()); exSheet.Cells[5, pos_nt].Value = string.Format("Trợ cấp T.C đêm chủ nhật", dt_rate.Rows[0]["NOST"].ToString()); pos_nt += 2; exSheet.Cells[4, pos_nt].Value = string.Format("NOST {0}% Amount", dt_rate.Rows[0]["NOST"].ToString()); exSheet.Cells[5, pos_nt].Value = string.Format("Tiền trợ cấp T.C đêm chủ nhật {0}%", dt_rate.Rows[0]["NOST"].ToString()); pos_nt += 2; exSheet.Cells[4, pos_nt].Value = string.Format("NHT {0}%", dt_rate.Rows[0]["NT90"].ToString()); exSheet.Cells[5, pos_nt].Value = string.Format("Trợ cấp đêm lễ", dt_rate.Rows[0]["NT90"].ToString()); pos_nt += 2; exSheet.Cells[4, pos_nt].Value = string.Format("NHT {0}% Amount", dt_rate.Rows[0]["NT90"].ToString()); exSheet.Cells[5, pos_nt].Value = string.Format("Tiền trợ cấp đêm lễ {0}%", dt_rate.Rows[0]["NT90"].ToString()); pos_nt += 2; exSheet.Cells[4, pos_nt].Value = string.Format("NOHT {0}%", dt_rate.Rows[0]["NOHT"].ToString()); exSheet.Cells[5, pos_nt].Value = string.Format("Trợ cấp T.C đêm lễ", dt_rate.Rows[0]["NOHT"].ToString()); pos_nt += 2; exSheet.Cells[4, pos_nt].Value = string.Format("NOHT {0}% Amount", dt_rate.Rows[0]["NOHT"].ToString()); exSheet.Cells[5, pos_nt].Value = string.Format("Tiền trợ cấp T.C đêm lễ {0}%", dt_rate.Rows[0]["NOHT"].ToString()); for (i_allow = 0; i_allow < 12; i_allow++) { if (dt_allow.Rows[0][i_allow + 12].ToString() == "1") { exSheet.Cells[4, allow + i_allow].Value = dt_allow.Rows[0][i_allow].ToString(); exSheet.Cells[5, allow + i_allow].Value = dt_allow.Rows[0][24 + i_allow].ToString(); } } for (i_allow = 0; i_allow < 8; i_allow++) { if (dt_allow_k.Rows[0][i_allow].ToString() != "") { exSheet.Cells[4, allow_k + i_allow].Value = dt_allow_k.Rows[0][i_allow].ToString(); exSheet.Cells[5, allow_k + i_allow].Value = dt_allow_k.Rows[0][i_allow + 8].ToString(); } if (dt_equip.Rows[0][i_allow].ToString() != "") { exSheet.Cells[4, equip + i_allow].Value = dt_equip.Rows[0][i_allow].ToString(); exSheet.Cells[6, equip + i_allow].Value = dt_equip.Rows[0][i_allow + 8].ToString(); } } //insert picture if (dt_com.Rows[0][3].ToString() != "") { byte[] MyData = new byte[0]; MyData = (byte[])dt_com.Rows[0][3]; MemoryStream stream = new MemoryStream(MyData); Bitmap b = new Bitmap(stream); exSheet.Shapes.AddPicture(ResizeImage(b, 115, 85)); } //---end--header--- for (k = 0; k < icol_emp; k++) { sub_total[k] = 0; grant_total[k] = 0; } for (i = 0; i < irow_emp - 1; i++) { j = i + 1; if (dt_emp.Rows[i]["dept_pk"].ToString() != dt_emp.Rows[j]["dept_pk"].ToString()) { n_dept++; } } //Insert new row for (i = 0; i < irow_emp - 1 + n_dept; i++) { exSheet.Range[pos + 1, 1].Rows.EntireRow.Insert(); } //Show data for (i = 0; i < irow_emp; i++) { for (k = 9; k < icol_emp - 1; k++) { if (k == icol_emp - 6) { continue; } else { sub_total[k] = sub_total[k] + double.Parse(dt_emp.Rows[i][k].ToString()); grant_total[k] = grant_total[k] + double.Parse(dt_emp.Rows[i][k].ToString()); } } emp_dept++; exSheet.Cells[pos + i, 1].Value = emp_dept; for (j = 2; j < icol_emp; j++) { if (j < 9 || j == icol_emp - 6 || j == icol_emp - 1) { exSheet.Cells[pos + i, j].Value = dt_emp.Rows[i][j].ToString(); } else { exSheet.Cells[pos + i, j].Value = double.Parse(dt_emp.Rows[i][j].ToString()); } } if (i != irow_emp - 1 && (dt_emp.Rows[i]["dept_pk"].ToString() != dt_emp.Rows[i + 1]["dept_pk"].ToString())) { exSheet.Cells[pos + i + 1, 1, pos + i + 1, 4].Merge(); exSheet.Cells[pos + i + 1, 1].Value = "Total " + dt_emp.Rows[i]["dept_nm"].ToString() + " (" + emp_dept + " employees)"; exSheet.Cells[pos + i + 1, 1, pos + i + 1, icol_emp - 1].Rows.Font.Bold = true; for (k = 9; k < icol_emp - 1; k++) { exSheet.Cells[pos + i + 1, k].Value = sub_total[k]; sub_total[k] = 0; } //Insert page break IPageBreaks pbs = exBook.Worksheets[1].HPageBreaks; IRange usedrange = exBook.Worksheets[1].UsedRange; //pbs.Reset(); pbs.Add(usedrange.Rows[pos + i + 2]); //end insert page beak pos = pos + 1; emp_dept = 0; } else if (i == irow_emp - 1) { exSheet.Cells[pos + i + 1, 1, pos + i + 1, 4].Merge(); exSheet.Cells[pos + i + 1, 1].Value = "Total " + dt_emp.Rows[i]["dept_nm"].ToString() + ": " + emp_dept + " employee(s)"; exSheet.Cells[pos + i + 1, 1, pos + i + 1, icol_emp - 1].Rows.Font.Bold = true; exSheet.Cells[pos + i + 2, 1, pos + i + 2, 4].Merge(); exSheet.Cells[pos + i + 2, 1].Value = "Grant Total: " + dt_emp.Rows.Count + " employee(s)"; exSheet.Cells[pos + i + 2, 1, pos + i + 2, icol_emp - 1].Rows.Font.Bold = true; for (k = 9; k < icol_emp - 1; k++) { exSheet.Cells[pos + i + 1, k].Value = sub_total[k]; exSheet.Cells[pos + i + 2, k].Value = grant_total[k]; } } } //Hide allowance column for (i_allow = 0; i_allow < 12; i_allow++) { if (dt_allow.Rows[0][i_allow + 12].ToString() != "1") { exSheet.Range[1, allow + i_allow].Columns.Hidden = true; } } for (i_allow = 0; i_allow < 8; i_allow++) { if (dt_allow_k.Rows[0][i_allow].ToString() == "") { exSheet.Range[1, allow_k + i_allow].Columns.Hidden = true; } if (dt_equip.Rows[0][i_allow].ToString() == "") { exSheet.Range[1, equip + i_allow].Columns.Hidden = true; } } // end loop detail percent if (File.Exists(TempFile)) { File.Delete(TempFile); } 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()); CtlLib.SetUser("SALE"); string TemplateFile = "rpt_dscd00070_kb01.xls"; string TempFile = "../../../../system/temp/rpt_dscd00070_kb01_" + 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 master_pk = Request["master_pk"]; string para = "'" + master_pk + "'"; DataTable dt = CtlLib.TableReadOpenCursor("st_lg_rpt_dscd00070_kb01", para); DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_dscd00070_kb01_1", para); // DataTable dtInfo = CtlLib.TableReadOpenCursor("st_lg_rpt_epbp00010_st01_2", "'" + Session["User_ID"].ToString() + "'"); // MASTER if (dt.Rows.Count > 0) { exSheet.Cells["h6"].Value = dt.Rows[0]["slip_no"]; exSheet.Cells["h7"].Value = "No/dr "; //+dt.Rows[0]["req_date"]; exSheet.Cells["h8"].Value = "Co/cr"; //;+dt.Rows[0]["dept_name"]; exSheet.Cells["d8"].Value = dt.Rows[0]["dd"]; exSheet.Cells["e8"].Value = dt.Rows[0]["mm"]; exSheet.Cells["f8"].Value = dt.Rows[0]["yy"]; exSheet.Cells["d9"].Value = dt.Rows[0]["partner_name"]; exSheet.Cells["d10"].Value = dt.Rows[0]["addr"]; exSheet.Cells["d11"].Value = dt.Rows[0]["TAX_CODE"]; exSheet.Cells["d12"].Value = dt.Rows[0]["PHONE_NO"]; exSheet.Cells["f12"].Value = dt.Rows[0]["FAX_NO"]; exSheet.Cells["d16"].Value = dt.Rows[0]["description"]; exSheet.Cells["e13"].Value = dt.Rows[0]["deli_loc_pk"]; exSheet.Cells["e14"].Value = dt.Rows[0]["full_name"]; exSheet.Cells["h14"].Value = dt.Rows[0]["tel"]; } // INSERT ROWS //for (int l_addrow = 0; l_addrow < dtDetail.Rows.Count-1; l_addrow++) //{ // exSheet.Range["A17"].Rows.EntireRow.Insert();//insert row new of sheet //} // SET DATA int pos = 19; int i = 0; for (i = 0; i < dtDetail.Rows.Count; i++) { exSheet.Range[pos + i, 4, pos + i, 5].Merge(); exSheet.Cells[pos + i, 2].Value = i + 1; exSheet.Cells[pos + i, 3].Value = dtDetail.Rows[i]["out_item_code"]; exSheet.Cells[pos + i, 4].Value = dtDetail.Rows[i]["out_item_name"]; exSheet.Cells[pos + i, 6].Value = dtDetail.Rows[i]["lot_no"]; exSheet.Cells[pos + i, 7].Value = dtDetail.Rows[i]["out_qty"]; // exSheet.Cells[pos + i, 8 ].Value = dtDetail.Rows[i]["in_uom"]; exSheet.Cells[pos + i, 9].Value = dtDetail.Rows[i]["description"]; } // exSheet.Cells["i30"].Value =dtDetail.Rows[0]["item_amount"]; // exSheet.Cells["i31"].Value =dtDetail.Rows[0]["vat_amount"]; // exSheet.Cells["i32"].Value =dtDetail.Rows[0]["total_amount"]; //--------- //exSheet.Cells[pos + i, 6 ].Value = "=sum(F10:F"+(pos+i-1)+")"; //exSheet.Cells[pos + i, 7].Value = "=sum(G10:G"+(pos+i-1)+")"; //--------- if (File.Exists(TempFile)) { File.Delete(TempFile); } // hide row firts range = exSheet.Range["A1"]; // hide row A5 // range.Rows.Hidden = true; // font bold header /*range = exSheet.Range["A1:AC1"]; * range.Rows[4].Font.Bold = true;*/ 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()); string l_floor_worksheet = Request["p_floor_worksheet"]; string l_date = Request["p_date"]; string TemplateFile = "rpt_60260030_worksheet.xls"; string TempFile = "../../../../system/temp/rpt_60260030_worksheet_" + 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, dtSum; para = "'" + l_floor_worksheet + "','" + l_date + "'"; dt = CtlLib.TableReadOpenCursor("ht_rpt_60260030_worksheet", para); //-----header--- //---end--header--- //--set-----DataBind--in---Master--- if (dt.Rows.Count == 0) { exSheet.Cells[3, 3].Value = "There is not data!"; } if (dt.Rows.Count > 0) { exSheet.Cells[4, 12].Value = dt.Rows[0]["date_para"].ToString(); exSheet.Cells[4, 28].Value = dt.Rows[0]["print_time"].ToString(); exSheet.Cells[5, 1].Value = dt.Rows[0]["floor_name"].ToString(); } //---------------------- ////end-set-----DataBind--in---Master--- for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++) { exSheet.Range["A10"].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; for (int l_row = 1; l_row <= dt.Rows.Count; l_row++) { for (int col = 0; col <= 30; col++) { if (col == 3 || col == 4) { exSheet.Cells[8 + l_row, col + 1].Font.Name = "Wingdings 2"; exSheet.Cells[8 + l_row, col + 1].Font.Bold = true; } exSheet.Cells[8 + l_row, col + 1].Value = dt.Rows[l_row - 1][col].ToString(); exSheet.Cells[l_row + 8, col + 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()); CtlLib.SetUser("crm"); string TemplateFile = "rpt_ht_60270090_POS_Adjust_Inquiry.xls"; string TempFile = "../../../../system/temp/rpt_ht_60270090_POS_Adjust_Inquiry_" + 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_invoice_or_room_no = Request["p_invoice_or_room_no"]; string p_dt_frm = Request["p_dt_frm"]; string p_dt_to = Request["p_dt_to"]; string para = "'" + p_invoice_or_room_no + "','" + p_dt_frm + "','" + p_dt_to + "'"; DataTable dt = CtlLib.TableReadOpenCursor("sp_rpt_htrt00160", para); // MASTER // INSERT ROWS for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++) { exSheet.Range["A5"].Rows.EntireRow.Insert();//insert row new of sheet } // SET DATA int pos = 4; int i = 0; for (i = 0; i < dt.Rows.Count; i++) { exSheet.Cells[pos + i, 1].Value = dt.Rows[i]["invoice_id"]; exSheet.Cells[pos + i, 2].Value = dt.Rows[i]["room_no"]; exSheet.Cells[pos + i, 3].Value = dt.Rows[i]["guest_name"]; exSheet.Cells[pos + i, 4].Value = dt.Rows[i]["invoice_amtvat"]; exSheet.Cells[pos + i, 5].Value = dt.Rows[i]["adj_date1"]; exSheet.Cells[pos + i, 6].Value = dt.Rows[i]["adj_amt_vnd"]; exSheet.Cells[pos + i, 7].Value = dt.Rows[i]["adj_by_pk"]; exSheet.Cells[pos + i, 8].Value = dt.Rows[i]["adj_reason"]; exSheet.Cells[pos + i, 9].Value = dt.Rows[i]["paymethod"]; exSheet.Cells[pos + i, 10].Value = dt.Rows[i]["crt_dt"]; } exSheet.Cells["B2"].Value = dt.Rows[0]["para_date"]; exSheet.Cells["I2"].Value = dt.Rows[0]["print_time"]; //exSheet.Cells[pos + i, 6 ].Value = "=sum(F10:F"+(pos+i-1)+")"; exSheet.Cells[pos + i, 4].Value = "=sum(D4:D" + (pos + i - 1) + ")"; exSheet.Cells[pos + i, 6].Value = "=sum(F4:F" + (pos + i - 1) + ")"; if (File.Exists(TempFile)) { File.Delete(TempFile); } // hide row firts range = exSheet.Range["A1"]; // hide row A5 // range.Rows.Hidden = true; // font bold header /*range = exSheet.Range["A1:AC1"]; * range.Rows[4].Font.Bold = true;*/ 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()); string TemplateFile = "rpt_60250010_birthday.xls"; string TempFile = "../../../../system/temp/rpt_60250010_birthday_" + 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_pk = Request.QueryString["p_pk"]; string p_dtfrom = Request.QueryString["p_dtfrom"]; string p_dtto = Request.QueryString["p_dtto"]; string para = "'" + p_pk + "','" + p_dtfrom + "','" + p_dtto + "'"; DataTable dtInfo = CtlLib.TableReadOpenCursor("ht_rpt_60250010_info", "'" + Session["User_ID"].ToString() + "'"); DataTable dtDetail = CtlLib.TableReadOpenCursor("hr_sel_10010023_announce_data", para); ////end-set-----DataBind--in---Master--- if (!string.IsNullOrEmpty(dtInfo.Rows[0]["pic"].ToString())) { exSheet.Cells[1, 1].Select(); byte[] MyData = new byte[0]; MyData = (byte[])dtInfo.Rows[0]["pic"]; MemoryStream stream = new MemoryStream(MyData); Bitmap b = new Bitmap(stream); exSheet.Shapes.AddPicture(ResizeImage(b, 100, 55)); } exSheet.Cells["C1"].Value = dtInfo.Rows[0]["partner_name"].ToString(); exSheet.Cells["C2"].Value = dtInfo.Rows[0]["addr1"].ToString(); exSheet.Cells["C3"].Value = "Tax code: " + dtInfo.Rows[0]["tax_code"].ToString(); for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++) { exSheet.Range["A7"].Rows.EntireRow.Insert();//insert row new of sheet } //set data to detail. // loop detail not percent int l_pos = 6; int i = 0; for (i = 0; i < dtDetail.Rows.Count; i++) { exSheet.Cells[4, 1].Value = "Birthday :" + dtDetail.Rows[0]["dtfrom"].ToString() + " ~ " + dtDetail.Rows[0]["dtto"].ToString(); exSheet.Cells[4, 7].Value = "Print time :" + dtDetail.Rows[0]["print_time"].ToString(); exSheet.Cells[l_pos + i, 1].Value = i + 1; exSheet.Cells[l_pos + i, 2].Value = dtDetail.Rows[i]["room_no"].ToString(); exSheet.Cells[l_pos + i, 3].Value = dtDetail.Rows[i]["full_name"].ToString(); exSheet.Cells[l_pos + i, 4].Value = dtDetail.Rows[i]["gender"].ToString(); exSheet.Cells[l_pos + i, 5].Value = dtDetail.Rows[i]["nation"]; exSheet.Cells[l_pos + i, 6].Value = dtDetail.Rows[i]["checkin_date"].ToString(); exSheet.Cells[l_pos + i, 7].Value = dtDetail.Rows[i]["departure_date"].ToString(); exSheet.Cells[l_pos + i, 8].Value = dtDetail.Rows[i]["birthday"].ToString(); } //exSheet.Cells[dtDetail.Rows.Count + 6, 36].Value = "=SUM(AJ5:AJ" + (dtDetail.Rows.Count + 5) + ")"; // end loop detail not percent // 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 /*range = exSheet.Range["A1:AC1"]; * range.Rows[4].Font.Bold = true;*/ 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()); string TemplateFile = "rpt_bisc00160_grand.xls"; string TempFile = "../../../../system/temp/rpt_bisc00160_grand_" + 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_from_date = Request.QueryString["p_from_date"]; string p_to_date = Request.QueryString["p_to_date"]; string p_wh_type = Request.QueryString["p_wh_type"]; string p_wh = Request.QueryString["p_wh"]; string p_wh_name = Request.QueryString["p_wh_name"]; string p_use_yn = Request.QueryString["p_use_yn"]; string p_item_grp = Request.QueryString["p_item_grp"]; string p_item = Request.QueryString["p_item"]; string p_lang = Request.QueryString["p_lang"]; string para = "'" + p_from_date + "','" + p_to_date + "','" + p_wh_type + "','" + p_wh + "','" + p_use_yn + "','" + p_item_grp + "','" + p_item + "','" + p_lang + "'"; DataTable dtInfo = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00160_2", "'" + Session["User_ID"].ToString() + "'"); DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00160_1", para); //Set Data In Master if (!string.IsNullOrEmpty(dtInfo.Rows[0]["pic"].ToString())) { exSheet.Cells[1, 1].Select(); byte[] MyData = new byte[0]; MyData = (byte[])dtInfo.Rows[0]["pic"]; MemoryStream stream = new MemoryStream(MyData); Bitmap b = new Bitmap(stream); exSheet.Shapes.AddPicture(ResizeImage(b, 100, 55)); } exSheet.Cells["C1"].Value = dtInfo.Rows[0]["partner_name"].ToString(); exSheet.Cells["C2"].Value = dtInfo.Rows[0]["addr1"].ToString(); exSheet.Cells["C3"].Value = "Tax code: " + dtInfo.Rows[0]["tax_code"].ToString(); exSheet.Cells["I1"].Value = "Tell: " + dtInfo.Rows[0]["phone_no"].ToString(); exSheet.Cells["I1"].Value = "Fax: " + dtInfo.Rows[0]["fax_no"].ToString(); exSheet.Cells["I1"].Value = "Print date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm"); string strFromDt = System.DateTime.ParseExact(p_from_date, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy"); string strToDt = System.DateTime.ParseExact(p_to_date, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy"); exSheet.Cells["A6"].Value = String.Format("W/H name: {0}", p_wh_name); exSheet.Cells["E6"].Value = String.Format("Date: {0} - {1}", strFromDt, strToDt); //insert row new of sheet for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++) { exSheet.Range["A9"].Rows.EntireRow.Insert(); } //sum variable double sum_begin_qty = 0; double sum_begin_ref_qty = 0; double sum_in_qty = 0; double sum_in_ref_qty = 0; double sum_out_qty = 0; double sum_out_ref_qty = 0; double sum_end_qty = 0; double sum_end_ref_qty = 0; double tmp_begin_qty = 0; double tmp_begin_ref_qty = 0; double tmp_in_qty = 0; double tmp_in_ref_qty = 0; double tmp_out_qty = 0; double tmp_out_ref_qty = 0; double tmp_end_qty = 0; double tmp_end_ref_qty = 0; //Set data int l_pos = 8; for (int i = 0; i < dtDetail.Rows.Count; i++) { exSheet.Cells[l_pos + i, 1].Value = dtDetail.Rows[i]["grp_nm"]; exSheet.Cells[l_pos + i, 2].Value = dtDetail.Rows[i]["item_code"]; exSheet.Cells[l_pos + i, 3].Value = dtDetail.Rows[i]["item_name"]; exSheet.Cells[l_pos + i, 4].Value = dtDetail.Rows[i]["uom"]; exSheet.Cells[l_pos + i, 5].Value = dtDetail.Rows[i]["lot_no"]; exSheet.Cells[l_pos + i, 6].Value = dtDetail.Rows[i]["begin_qty"]; exSheet.Cells[l_pos + i, 7].Value = dtDetail.Rows[i]["begin_ref_qty"]; exSheet.Cells[l_pos + i, 8].Value = dtDetail.Rows[i]["in_qty"]; exSheet.Cells[l_pos + i, 9].Value = dtDetail.Rows[i]["in_ref_qty"]; exSheet.Cells[l_pos + i, 10].Value = dtDetail.Rows[i]["out_qty"]; exSheet.Cells[l_pos + i, 11].Value = dtDetail.Rows[i]["out_ref_qty"]; exSheet.Cells[l_pos + i, 12].Value = dtDetail.Rows[i]["end_qty"]; exSheet.Cells[l_pos + i, 13].Value = dtDetail.Rows[i]["end_ref_qty"]; if (double.TryParse(dtDetail.Rows[i]["begin_qty"].ToString(), out tmp_begin_qty)) { sum_begin_qty += tmp_begin_qty; } if (double.TryParse(dtDetail.Rows[i]["begin_ref_qty"].ToString(), out tmp_begin_ref_qty)) { sum_begin_ref_qty += tmp_begin_ref_qty; } if (double.TryParse(dtDetail.Rows[i]["in_qty"].ToString(), out tmp_in_qty)) { sum_in_qty += tmp_in_qty; } if (double.TryParse(dtDetail.Rows[i]["in_ref_qty"].ToString(), out tmp_in_ref_qty)) { sum_in_ref_qty += tmp_in_ref_qty; } if (double.TryParse(dtDetail.Rows[i]["out_qty"].ToString(), out tmp_out_qty)) { sum_out_qty += tmp_out_qty; } if (double.TryParse(dtDetail.Rows[i]["out_ref_qty"].ToString(), out tmp_out_ref_qty)) { sum_out_ref_qty += tmp_out_ref_qty; } if (double.TryParse(dtDetail.Rows[i]["end_qty"].ToString(), out tmp_end_qty)) { sum_end_qty += tmp_end_qty; } if (double.TryParse(dtDetail.Rows[i]["end_ref_qty"].ToString(), out tmp_end_ref_qty)) { sum_end_ref_qty += tmp_end_ref_qty; } } exSheet.Cells[dtDetail.Rows.Count + l_pos, 6].Value = String.Format("{0:0,0}", sum_begin_qty); exSheet.Cells[dtDetail.Rows.Count + l_pos, 7].Value = String.Format("{0:0,0}", sum_begin_ref_qty); exSheet.Cells[dtDetail.Rows.Count + l_pos, 8].Value = String.Format("{0:0,0}", sum_in_qty); exSheet.Cells[dtDetail.Rows.Count + l_pos, 9].Value = String.Format("{0:0,0}", sum_in_ref_qty); exSheet.Cells[dtDetail.Rows.Count + l_pos, 10].Value = String.Format("{0:0,0}", sum_out_qty); exSheet.Cells[dtDetail.Rows.Count + l_pos, 11].Value = String.Format("{0:0,0}", sum_out_ref_qty); exSheet.Cells[dtDetail.Rows.Count + l_pos, 12].Value = String.Format("{0:0,0}", sum_end_qty); exSheet.Cells[dtDetail.Rows.Count + l_pos, 13].Value = String.Format("{0:0,0}", sum_end_ref_qty); exSheet.Cells[dtDetail.Rows.Count + l_pos, 1, dtDetail.Rows.Count + l_pos, 13].Interior.Color = Color.FromArgb(255, 204, 153); exSheet.Cells[dtDetail.Rows.Count + l_pos, 1, dtDetail.Rows.Count + l_pos, 13].RowHeight = 30; exSheet.Cells[dtDetail.Rows.Count + l_pos, 1, dtDetail.Rows.Count + l_pos, 13].VerticalAlignment = XlVAlign.xlVAlignCenter; exSheet.Cells[dtDetail.Rows.Count + l_pos, 1, dtDetail.Rows.Count + l_pos, 13].HorizontalAlignment = XlHAlign.xlHAlignRight; exSheet.Cells[dtDetail.Rows.Count + l_pos, 1, dtDetail.Rows.Count + l_pos, 13].Font.Bold = true; exSheet.Cells[dtDetail.Rows.Count + l_pos, 1, dtDetail.Rows.Count + l_pos, 13].Borders[XlBordersIndex.xlInsideAll].LineStyle = XlLineStyle.xlContinuous; exSheet.Cells[dtDetail.Rows.Count + l_pos, 1, dtDetail.Rows.Count + l_pos, 13].Borders[XlBordersIndex.xlAround].LineStyle = XlLineStyle.xlContinuous; exSheet.Cells[dtDetail.Rows.Count + l_pos, 1, dtDetail.Rows.Count + l_pos, 5].Merge(); exSheet.Cells[dtDetail.Rows.Count + l_pos, 1, dtDetail.Rows.Count + l_pos, 5].HorizontalAlignment = XlHAlign.xlHAlignCenter; exSheet.Cells[dtDetail.Rows.Count + l_pos, 1, dtDetail.Rows.Count + l_pos, 5].Value = "GRAND TOTAL"; exSheet.Cells[dtDetail.Rows.Count + l_pos, 1, dtDetail.Rows.Count + l_pos, 5].Font.Bold = false; // end loop detail percent 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()); //string l_pk = Request["master_pk"]; string TemplateFile = "rpt_bisc00020_ST02.xls"; string TempFile = "../../../../system/temp/rpt_bisc00020_ST02_" + 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_tin_warehouse_pk"]; 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_item_group_pk + "','" + p_item + "','" + p_in_out_yn + "'"; DataTable dt = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00020_ST02_1", "'" + Session["User_ID"].ToString() + "'"); DataTable dt1 = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00020_ST02_2", 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]["end_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]["end_qty"].ToString() != "") { exSheet.Cells[8 + l_row, 5].Value = CommondLib.ConvertToDbl(dt1.Rows[l_row]["end_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 p_org, p_position, p_search, p_requestno, p_pass, p_select, p_from_dt, p_to_dt, p_lang; p_org = Request["p_org"].ToString(); p_position = Request["p_position"].ToString(); p_search = Request["p_search"].ToString(); p_requestno = Request["p_request_no"].ToString(); p_pass = Request["p_pass"].ToString(); p_select = Request["p_select"].ToString(); p_from_dt = Request["p_from_dt"].ToString(); p_to_dt = Request["p_to_dt"].ToString(); p_lang = Request["p_lang"].ToString(); string TemplateFile; if (p_lang == "ENG") { TemplateFile = "rpt_hrrc00400_1_eng.xls"; } else { TemplateFile = "rpt_hrrc00400_1_vie.xls"; } string TempFile = "../../../../system/temp/rpt_hrrc00400_1" + 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; string para = "'" + p_org + "','" + p_position + "','" + p_search + "','" + p_requestno + "','" + p_pass + "','" + p_select + "','" + p_from_dt + "','" + p_to_dt + "','" + p_lang + "'"; DataTable dt_emp = CtlLib.TableReadOpenCursor("ST_HR_RPT_HRRC00400_1", para); if (dt_emp.Rows.Count == 0) { Response.Write("Không có dữ liệu!"); Response.End(); } int row_loop = 4; int l_col = dt_emp.Columns.Count; int count = 0; for (int l_addrow = 1; l_addrow < dt_emp.Rows.Count; l_addrow++) { exSheet.Range[row_loop + l_addrow, 1].Rows.EntireRow.Insert(); //insert row new of sheet } if (p_from_dt != "" && p_to_dt != "") { exSheet.Cells["A2"].Value = "Từ ngày " + dt_emp.Rows[0][0].ToString() + " Đến ngày " + dt_emp.Rows[0][1].ToString(); } // xu ly du lieu do vao excel if (dt_emp.Rows.Count > 0) { for (int l_row = 0; l_row < dt_emp.Rows.Count; l_row++) { count++; exSheet.Cells[row_loop + l_row, 1].Value = count; // do du lieu vao cac dong for (int col = 2; col < l_col; col++) { if (IsNumeric(dt_emp.Rows[l_row][col].ToString()) && col != 2) { exSheet.Cells[row_loop + l_row, col].Value = double.Parse(dt_emp.Rows[l_row][col].ToString()); } else { exSheet.Cells[row_loop + l_row, col].Value = dt_emp.Rows[l_row][col].ToString(); } } } } if (File.Exists(TempFile)) { File.Delete(TempFile); } // hide row firts range = exSheet.Range["A1"]; // hide row A5 // range.Rows.Hidden = true; // font bold header /*range = exSheet.Range["A1:AC1"]; * range.Rows[4].Font.Bold = true;*/ 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("crm"); string l_room_no = Request["p_room_no"]; string l_checkout_date_frm = Request["p_checkout_date_frm"]; string l_checkout_date_to = Request["p_checkout_date_to"]; string TemplateFile = "rpt_60280040_inquiry_checkout.xls"; string TempFile = "../../../../system/temp/rpt_60280040_inquiry_checkout_" + 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_room_no + "','" + l_checkout_date_frm + "','" + l_checkout_date_to + "'"; dt = CtlLib.TableReadOpenCursor("rpt_60280040_inquiry", para); //-----header--- //---end--header--- //--set-----DataBind--in---Master--- if (dt.Rows.Count == 0) { exSheet.Cells[8, 2].Value = "There is not data!"; } if (dt.Rows.Count > 0) { exSheet.Cells[3, 1].Value = dt.Rows[0]["date_para"].ToString(); exSheet.Cells[3, 25].Value = dt.Rows[0]["print_time"].ToString(); } //---------------------- ////end-set-----DataBind--in---Master--- for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++) { exSheet.Range["A6"].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 double l_Room_Charge = 0, l_Total_Due = 0, l_Payed = 0, l_Deposit = 0, l_F_B_Hotel = 0, l_MiniBar = 0, l_Laundry = 0; double l_Telephone = 0, l_Others = 0, l_Spa_fee = 0, l_Lotus_shop = 0, l_Adjust = 0; int l_cnt = dt.Rows.Count; for (int l_row = 1; l_row <= dt.Rows.Count; l_row++) { for (int col = 0; col <= 25; col++) { if (col == 1 || col == 2 || col == 3 || col == 4 || col == 6 || col == 20 || col == 21 || col == 22 || col == 23 || col == 24 || col == 25) { exSheet.Cells[4 + l_row, col + 1].Value = dt.Rows[l_row - 1][col].ToString(); exSheet.Cells[l_row + 4, col + 1].HorizontalAlignment = XlHAlign.xlHAlignLeft; } else { if (dt.Rows[l_row - 1][col].ToString() != "") { exSheet.Cells[4 + l_row, col + 1].Value = double.Parse(dt.Rows[l_row - 1][col].ToString()); } if (col == 8) { l_Room_Charge = l_Room_Charge + double.Parse(dt.Rows[l_row - 1][col].ToString()); } if (col == 9) { l_Total_Due = l_Total_Due + double.Parse(dt.Rows[l_row - 1][col].ToString()); } if (col == 10) { if (dt.Rows[l_row - 1][col].ToString() != "") { l_Payed = l_Payed + double.Parse(dt.Rows[l_row - 1][col].ToString()); } } if (col == 11) { l_Deposit = l_Deposit + double.Parse(dt.Rows[l_row - 1][col].ToString()); } if (col == 12) { l_F_B_Hotel = l_F_B_Hotel + double.Parse(dt.Rows[l_row - 1][col].ToString()); } if (col == 13) { l_MiniBar = l_MiniBar + double.Parse(dt.Rows[l_row - 1][col].ToString()); } if (col == 14) { l_Laundry = l_Laundry; } if (col == 15) { l_Telephone = l_Telephone + double.Parse(dt.Rows[l_row - 1][col].ToString()); } if (col == 16) { l_Others = l_Others + double.Parse(dt.Rows[l_row - 1][col].ToString()); } if (col == 17) { l_Spa_fee = l_Spa_fee + double.Parse(dt.Rows[l_row - 1][col].ToString()); } if (col == 18) { l_Lotus_shop = l_Lotus_shop + double.Parse(dt.Rows[l_row - 1][col].ToString()); } if (col == 19) { l_Adjust = l_Adjust + double.Parse(dt.Rows[l_row - 1][col].ToString());; } exSheet.Cells[4, col + 1, l_row + 4, col + 1].NumberFormat = "#,##0"; exSheet.Cells[l_row + 4, col + 1].HorizontalAlignment = XlHAlign.xlHAlignRight; if (dt.Rows[l_row - 1][col].ToString() != "") { if (double.Parse(dt.Rows[l_row - 1][col].ToString()) == 0) { exSheet.Cells[4 + l_row, col + 1].Value = ""; } } } } exSheet.Cells[5 + dt.Rows.Count, 9].Value = l_Room_Charge; exSheet.Cells[5 + dt.Rows.Count, 10].Value = l_Total_Due; exSheet.Cells[5 + dt.Rows.Count, 11].Value = l_Payed; exSheet.Cells[5 + dt.Rows.Count, 12].Value = l_Deposit; exSheet.Cells[5 + dt.Rows.Count, 13].Value = l_F_B_Hotel; exSheet.Cells[5 + dt.Rows.Count, 14].Value = l_MiniBar; exSheet.Cells[5 + dt.Rows.Count, 15].Value = l_Laundry; exSheet.Cells[5 + dt.Rows.Count, 16].Value = l_Telephone; exSheet.Cells[5 + dt.Rows.Count, 17].Value = l_Others; exSheet.Cells[5 + dt.Rows.Count, 18].Value = l_Spa_fee; exSheet.Cells[5 + dt.Rows.Count, 19].Value = l_Lotus_shop; exSheet.Cells[5 + dt.Rows.Count, 20].Value = l_Adjust; } // 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("crm"); string l_room_no = Request["p_room_no"]; string l_checkout_date_frm = Request["p_checkout_date_frm"]; string l_checkout_date_to = Request["p_checkout_date_to"]; string TemplateFile = "rpt_60280040_inquiry_checkout.xls"; string TempFile = "../../../../system/temp/rpt_60280040_inquiry_checkout_" + 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_room_no + "','" + l_checkout_date_frm + "','" + l_checkout_date_to + "'"; dt = CtlLib.TableReadOpenCursor("rpt_60280040_inquiry", para); //-----header--- //---end--header--- //--set-----DataBind--in---Master--- if (dt.Rows.Count == 0) { exSheet.Cells[8, 2].Value = "There is not data!"; } if (dt.Rows.Count > 0) { exSheet.Cells[3, 1].Value = dt.Rows[0]["date_para"].ToString(); exSheet.Cells[3, 25].Value = dt.Rows[0]["print_time"].ToString(); } //---------------------- ////end-set-----DataBind--in---Master--- int l_first_row = 5; for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++) { exSheet.Range[l_first_row + l_addrow, 1].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_Room_Charge = 0, l_Total_Due = 0, l_Payed = 0, l_Deposit = 0, l_F_B_Hotel = 0, l_MiniBar = 0, l_Laundry = 0; int l_Telephone = 0, l_Others = 0, l_Spa_fee = 0, l_Lotus_shop = 0, l_Adjust = 0; int l_cnt = dt.Rows.Count; for (int l_row = 0; l_row < dt.Rows.Count; l_row++) { exSheet.Cells[l_first_row + l_row, 1].Value = dt.Rows[l_row]["folio"]; exSheet.Cells[l_first_row + l_row, 2].Value = dt.Rows[l_row]["room_no"]; exSheet.Cells[l_first_row + l_row, 3].Value = dt.Rows[l_row]["last_name"]; exSheet.Cells[l_first_row + l_row, 4].Value = dt.Rows[l_row]["nation"]; exSheet.Cells[l_first_row + l_row, 5].Value = dt.Rows[l_row]["checkin_date"]; exSheet.Cells[l_first_row + l_row, 6].Value = dt.Rows[l_row]["checkout_date"]; exSheet.Cells[l_first_row + l_row, 7].Value = dt.Rows[l_row]["days"]; exSheet.Cells[l_first_row + l_row, 8].Value = dt.Rows[l_row]["room_type"]; exSheet.Cells[l_first_row + l_row, 9].Value = dt.Rows[l_row]["avg_rate_amt"]; exSheet.Cells[l_first_row + l_row, 10].Value = dt.Rows[l_row]["room_charge"]; exSheet.Cells[l_first_row + l_row, 11].Value = dt.Rows[l_row]["total_due"]; exSheet.Cells[l_first_row + l_row, 12].Value = dt.Rows[l_row]["actual_pay"]; exSheet.Cells[l_first_row + l_row, 13].Value = dt.Rows[l_row]["advance_amt"]; exSheet.Cells[l_first_row + l_row, 14].Value = dt.Rows[l_row]["fnb_hotel_amt"]; exSheet.Cells[l_first_row + l_row, 15].Value = dt.Rows[l_row]["minibar_amt"]; exSheet.Cells[l_first_row + l_row, 16].Value = dt.Rows[l_row]["laundry_amt"]; exSheet.Cells[l_first_row + l_row, 17].Value = dt.Rows[l_row]["telephone"]; exSheet.Cells[l_first_row + l_row, 18].Value = dt.Rows[l_row]["others_amt"]; exSheet.Cells[l_first_row + l_row, 19].Value = dt.Rows[l_row]["spa_amt"]; exSheet.Cells[l_first_row + l_row, 20].Value = dt.Rows[l_row]["shop_amt"]; exSheet.Cells[l_first_row + l_row, 21].Value = dt.Rows[l_row]["adjust_amt"]; exSheet.Cells[l_first_row + l_row, 22].Value = dt.Rows[l_row]["checkin_by"]; exSheet.Cells[l_first_row + l_row, 23].Value = dt.Rows[l_row]["checkin_time"]; exSheet.Cells[l_first_row + l_row, 24].Value = dt.Rows[l_row]["checkout_by"]; exSheet.Cells[l_first_row + l_row, 25].Value = dt.Rows[l_row]["checkout_time"]; exSheet.Cells[l_first_row + l_row, 26].Value = dt.Rows[l_row]["rate_name"]; exSheet.Cells[l_first_row + l_row, 27].Value = dt.Rows[l_row]["pay_method"]; if (dt.Rows[l_row]["room_charge"].ToString() != "") { l_Room_Charge = l_Room_Charge + int.Parse(dt.Rows[l_row]["room_charge"].ToString()); //8--19 } if (dt.Rows[l_row]["total_due"].ToString() != "") { l_Total_Due = l_Total_Due + int.Parse(dt.Rows[l_row]["total_due"].ToString()); //8--19 } if (dt.Rows[l_row]["actual_pay"].ToString() != "") { l_Payed = l_Payed + int.Parse(dt.Rows[l_row]["actual_pay"].ToString()); //8--19 } if (dt.Rows[l_row]["advance_amt"].ToString() != "") { l_Deposit = l_Deposit + int.Parse(dt.Rows[l_row]["advance_amt"].ToString()); //8--19 } if (dt.Rows[l_row]["fnb_hotel_amt"].ToString() != "") { l_F_B_Hotel = l_F_B_Hotel + int.Parse(dt.Rows[l_row]["fnb_hotel_amt"].ToString()); //8--19 } if (dt.Rows[l_row]["minibar_amt"].ToString() != "") { l_MiniBar = l_MiniBar + int.Parse(dt.Rows[l_row]["minibar_amt"].ToString()); //8--19 } if (dt.Rows[l_row]["laundry_amt"].ToString() != "") { l_Laundry = l_Laundry + int.Parse(dt.Rows[l_row]["laundry_amt"].ToString()); //8--19 } if (dt.Rows[l_row]["telephone"].ToString() != "") { l_Telephone = l_Telephone + int.Parse(dt.Rows[l_row]["telephone"].ToString()); //8--19 } if (dt.Rows[l_row]["others_amt"].ToString() != "") { l_Others = l_Others + int.Parse(dt.Rows[l_row]["others_amt"].ToString()); //8--19 } if (dt.Rows[l_row]["spa_amt"].ToString() != "") { l_Spa_fee = l_Spa_fee + int.Parse(dt.Rows[l_row]["spa_amt"].ToString()); //8--19 } if (dt.Rows[l_row]["shop_amt"].ToString() != "") { l_Lotus_shop = l_Lotus_shop + int.Parse(dt.Rows[l_row]["shop_amt"].ToString()); //8--19 } if (dt.Rows[l_row]["adjust_amt"].ToString() != "") { l_Adjust = l_Adjust + int.Parse(dt.Rows[l_row]["adjust_amt"].ToString()); //8--19 } } exSheet.Cells[5 + dt.Rows.Count, 10].Value = l_Room_Charge; exSheet.Cells[5 + dt.Rows.Count, 11].Value = l_Total_Due; exSheet.Cells[5 + dt.Rows.Count, 12].Value = l_Payed; exSheet.Cells[5 + dt.Rows.Count, 13].Value = l_Deposit; exSheet.Cells[5 + dt.Rows.Count, 14].Value = l_F_B_Hotel; exSheet.Cells[5 + dt.Rows.Count, 15].Value = l_MiniBar; exSheet.Cells[5 + dt.Rows.Count, 16].Value = l_Laundry; exSheet.Cells[5 + dt.Rows.Count, 17].Value = l_Telephone; exSheet.Cells[5 + dt.Rows.Count, 18].Value = l_Others; exSheet.Cells[5 + dt.Rows.Count, 19].Value = l_Spa_fee; exSheet.Cells[5 + dt.Rows.Count, 20].Value = l_Lotus_shop; exSheet.Cells[5 + dt.Rows.Count, 21].Value = l_Adjust; // 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 l_from = Request["p_dtfrom"]; string l_to = Request["p_dtto"]; string TemplateFile = "rpt_60280170_rvn_cabana_detail.xls"; string TempFile = "../../../../system/temp/rpt_60280170_rvn_cabana_detail_" + 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, dtSum; para = "'" + l_from + "','" + l_to + "'"; dt = CtlLib.TableReadOpenCursor("rpt_ht_sel_60280170", para); //-----header--- //---end--header--- //--set-----DataBind--in---Master--- if (dt.Rows.Count == 0) { exSheet.Cells[3, 3].Value = "There is not data!"; } if (dt.Rows.Count > 0) { exSheet.Cells[3, 1].Value = dt.Rows[0]["date_para"].ToString(); // exSheet.Cells[3, 4].Value = dt.Rows[0]["roomno"].ToString(); // exSheet.Cells[3, 5].Value = dt.Rows[0]["paymnent"].ToString(); exSheet.Cells[3, 7].Value = dt.Rows[0]["print_time"].ToString(); // exSheet.Cells[2, 3].Value = dt.Rows[0]["report_name"].ToString(); } //---------------------- ////end-set-----DataBind--in---Master--- for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++) { exSheet.Range["A7"].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; double l_total_qty = 0, l_total_amount = 0; if (dt.Rows.Count > 0) { //int l_cnt = dt.Rows.Count; for (int l_row = 1; l_row <= dt.Rows.Count; l_row++) { for (int col = 0; col <= 8; col++) { if (col == 0) { exSheet.Cells[5 + l_row, 1].Value = dt.Rows[l_row - 1][0].ToString(); } if (col == 1) { exSheet.Cells[5 + l_row, 2].Value = dt.Rows[l_row - 1][1].ToString(); } if (col == 2) { if (dt.Rows[l_row - 1][2].ToString() != "") { exSheet.Cells[5 + l_row, 3].Value = dt.Rows[l_row - 1][2].ToString(); } } if (col == 3) { exSheet.Cells[5 + l_row, 4].Value = dt.Rows[l_row - 1][3].ToString(); } if (col == 4) { if (dt.Rows[l_row - 1][4].ToString() != "") { exSheet.Cells[5 + l_row, 5].Value = dt.Rows[l_row - 1][4].ToString(); } } if (col == 5) { if (dt.Rows[l_row - 1][5].ToString() != "") { exSheet.Cells[5 + l_row, 6].Value = dt.Rows[l_row - 1][5].ToString(); } } if (col == 6) { if (dt.Rows[l_row - 1][6].ToString() != "") { exSheet.Cells[5 + l_row, 7].Value = double.Parse(dt.Rows[l_row - 1][6].ToString()); } } if (col == 7) { if (dt.Rows[l_row - 1][7].ToString() != "") { exSheet.Cells[5 + l_row, 8].Value = double.Parse(dt.Rows[l_row - 1][7].ToString()); l_total_qty = l_total_qty + double.Parse(dt.Rows[l_row - 1][7].ToString()); } } if (col == 8) { if (dt.Rows[l_row - 1][8].ToString() != "") { exSheet.Cells[5 + l_row, 9].Value = double.Parse(dt.Rows[l_row - 1][8].ToString()); l_total_amount = l_total_amount + double.Parse(dt.Rows[l_row - 1][8].ToString()); } } } } } exSheet.Cells[6 + l_cnt, 8].Value = l_total_qty; exSheet.Cells[6 + l_cnt, 9].Value = l_total_amount; // end loop detail percent if (File.Exists(TempFile)) { File.Delete(TempFile); } range = exSheet.Range["A1"]; 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 l_dtfrm = Request["p_dtfrm"]; string l_dtto = Request["p_dtto"]; string TemplateFile = "rpt_60240050_special_service_booking.xls"; string TempFile = "../../../../system/temp/rpt_60240050_special_service_booking_" + 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_dtfrm + "','" + l_dtto + "'"; dt = CtlLib.TableReadOpenCursor("ht_rpt_60240050", para); //-----header--- //---end--header--- //--set-----DataBind--in---Master--- if (dt.Rows.Count == 0) { exSheet.Cells[3, 3].Value = "There is not data!"; } if (dt.Rows.Count > 0) { exSheet.Cells[3, 1].Value = dt.Rows[0]["print_time"].ToString(); exSheet.Cells[3, 5].Value = dt.Rows[0]["date_para"].ToString(); /*exSheet.Cells[2, 1].Select(); * * byte[] MyData = new byte[0]; * MyData = (byte[])dt.Rows[0]["logo_lotus"]; * MemoryStream stream = new MemoryStream(MyData); * exSheet.Shapes.AddPicture(stream);*/ } //---------------------- ////end-set-----DataBind--in---Master--- for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++) { exSheet.Range["A6"].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 double l_total_vnd = 0; float l_total_usd = 0; int l_cnt = dt.Rows.Count; for (int l_row = 1; l_row <= dt.Rows.Count; l_row++) { for (int col = 0; col <= 7; col++) { if (col == 0 || col == 1 || col == 2 || col == 3 || col == 5 || col == 6) { exSheet.Cells[4 + l_row, col + 1].Value = dt.Rows[l_row - 1][col].ToString(); exSheet.Cells[l_row + 4, col + 1].HorizontalAlignment = XlHAlign.xlHAlignLeft; } else { if (dt.Rows[l_row - 1][col].ToString() == "") { exSheet.Cells[4 + l_row, col + 1].Value = dt.Rows[l_row - 1][col].ToString(); } else { exSheet.Cells[4 + l_row, col + 1].Value = double.Parse(dt.Rows[l_row - 1][col].ToString()); exSheet.Cells[4, col + 1, l_row + 4, col + 1].NumberFormat = "#,##0"; exSheet.Cells[l_row + 4, col + 1].HorizontalAlignment = XlHAlign.xlHAlignRight; } } if (dt.Rows[l_row - 1][0].ToString() != "" && dt.Rows[l_row - 1][3].ToString() == "") { exSheet.Cells[l_row + 4, 1, l_row + 4, 8].Interior.Color = Color.Silver; } if (dt.Rows[l_row - 1][0].ToString() == "" && dt.Rows[l_row - 1][3].ToString() == "") { exSheet.Cells[l_row + 4, 1, l_row + 4, 8].Interior.Color = Color.Orange; } } } // 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()); //CtlLib.SetUser("SALE"); string TemplateFile = "rpt_dsos00010_ST01.xls"; string TempFile = "../../../../system/temp/rpt_dsos00010_ST01_" + 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_tsa_saleorder_m_pk = Request.QueryString["p_tsa_saleorder_m_pk"]; string para = "'" + p_tsa_saleorder_m_pk + "'"; DataTable dtSaleOrd = CtlLib.TableReadOpenCursor("st_lg_rpt_dsos00010_st01_1", para); DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_dsos00010_st01_2", para); DataTable dtInfo = CtlLib.TableReadOpenCursor("st_lg_rpt_dsos00010_st01_3", "'" + Session["User_ID"].ToString() + "'"); // MASTER //--set-----DataBind--in---Master--- if (!string.IsNullOrEmpty(dtInfo.Rows[0]["pic"].ToString())) { exSheet.Cells[1, 1].Select(); byte[] MyData = new byte[0]; MyData = (byte[])dtInfo.Rows[0]["pic"]; MemoryStream stream = new MemoryStream(MyData); Bitmap b = new Bitmap(stream); exSheet.Shapes.AddPicture(ResizeImage(b, 90, 59)); } //exSheet.Cells["C1"].Value = dtInfo.Rows[0]["partner_name"]; //exSheet.Cells["C2"].Value = dtInfo.Rows[0]["addr1"]; //exSheet.Cells["C3"].Value = "Tax Code: " + dtInfo.Rows[0]["tax_code"]; //exSheet.Cells["G1"].Value = "Tel: " + dtInfo.Rows[0]["phone_no"]; //exSheet.Cells["G2"].Value = "Fax No: " + dtInfo.Rows[0]["fax_no"]; exSheet.Cells["I4"].Value = "Print Date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm"); //SET DATA MASTER SALE ORDER if (dtSaleOrd.Rows.Count > 0) { exSheet.Cells["C6"].Value = dtSaleOrd.Rows[0]["item_name"]; exSheet.Cells["D9"].Value = dtSaleOrd.Rows[0]["unit_price"]; exSheet.Cells["C10"].Value = dtSaleOrd.Rows[0]["ord_qty"]; } // INSERT ROWS for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++) { exSheet.Range["A15"].Rows.EntireRow.Insert();//insert row new of sheet } // SET DATA DETAIL int pos = 14, l_cnt = 0; int i = 0, l_total_amount = 0; l_cnt = dtDetail.Rows.Count; for (i = 0; i < dtDetail.Rows.Count; i++) { exSheet.Cells[pos + i, 1].Value = i + 1; exSheet.Cells[pos + i, 3].Value = dtDetail.Rows[i]["item_code"]; exSheet.Cells[pos + i, 4].Value = dtDetail.Rows[i]["item_name"]; exSheet.Cells[pos + i, 5].Value = dtDetail.Rows[i]["cons_qty"]; exSheet.Cells[pos + i, 6].Value = dtDetail.Rows[i]["mat_uom"]; exSheet.Cells[pos + i, 7].Value = dtDetail.Rows[i]["pur_price"]; exSheet.Cells[pos + i, 8].Value = dtDetail.Rows[i]["loss_rate"]; exSheet.Cells[pos + i, 9].Value = dtDetail.Rows[i]["amount"]; if (dtDetail.Rows[i]["amount"].ToString() != "") { l_total_amount = l_total_amount + int.Parse(dtDetail.Rows[i]["amount"].ToString()); } } exSheet.Cells[pos + l_cnt, 9].Value = l_total_amount; exSheet.Cells["I9"].Value = l_total_amount; //--------- //--------- if (File.Exists(TempFile)) { File.Delete(TempFile); } // hide row firts range = exSheet.Range["A1"]; // hide row A5 // range.Rows.Hidden = true; // font bold header /*range = exSheet.Range["A1:AC1"]; * range.Rows[4].Font.Bold = true;*/ 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(); }