protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser("sale");
string l_pk = Request["master_pk"];
string TemplateFile = "rpt_dscd00075.xls";
string TempFile = "../../../../system/temp/rpt_dscd00075_" + 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 + "'";
DataTable dtInfo = CtlLib.TableReadOpenCursor("st_lg_RPT_DSCD00075_2", "'" + Session["User_ID"].ToString() + "'");
DataTable dtMaster = CtlLib.TableReadOpenCursor("st_lg_RPT_DSCD00075", para);
DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_RPT_DSCD00075_1", para);
//--set-----DataBind--in---Master---
if (!string.IsNullOrEmpty(dtInfo.Rows[0]["pic"].ToString()))
{
exSheet.Cells[2, 2].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, 131, 71));
}
exSheet.Cells[3, 15].Value = "Print date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm");
exSheet.Cells[5, 4].Value = dtInfo.Rows[0]["partner_name"].ToString();
exSheet.Cells[5, 12].Value = dtMaster.Rows[0]["slip_no"].ToString();
exSheet.Cells[6, 4].Value = dtInfo.Rows[0]["addr1"].ToString();
exSheet.Cells[6, 12].Value = dtMaster.Rows[0]["out_date"].ToString();
exSheet.Cells[7, 4].Value = dtInfo.Rows[0]["tax_code"].ToString();
exSheet.Cells[7, 12].Value = dtMaster.Rows[0]["description"].ToString();
exSheet.Cells[8, 4].Value = dtInfo.Rows[0]["phone_no"].ToString();
//---------
exSheet.Cells[10, 4].Value = dtMaster.Rows[0]["bill_partner_name"].ToString();
exSheet.Cells[10, 12].Value = dtMaster.Rows[0]["deli_partner_name"].ToString();
exSheet.Cells[11, 4].Value = dtMaster.Rows[0]["short_nm"].ToString();
exSheet.Cells[11, 12].Value = dtMaster.Rows[0]["deli_addr1"].ToString();
exSheet.Cells[12, 4].Value = dtMaster.Rows[0]["bill_addr1"].ToString();
exSheet.Cells[13, 4].Value = dtMaster.Rows[0]["tax_code"].ToString();
exSheet.Cells[13, 6].Value = dtMaster.Rows[0]["phone_no"].ToString();
exSheet.Cells[14, 4].Value = dtMaster.Rows[0]["po_date"].ToString();
exSheet.Cells[15, 4].Value = dtMaster.Rows[0]["po_no"].ToString();
/*exSheet.Cells[11, 2].Value = dtMst.Rows[0]["charger"].ToString();
* exSheet.Cells[12, 2].Value = dtMst.Rows[0]["department_name"].ToString();
* exSheet.Cells[20, 2].Value = dtMst.Rows[0]["partner_name"].ToString();
* exSheet.Cells[20, 3].Value = dtMst.Rows[0]["ACCOUNT_NO"].ToString();
* exSheet.Cells[22, 2].Value = dtMst.Rows[0]["BEDEFI_BANK_NM"].ToString();
* exSheet.Cells[17, 6].Value = dtMst.Rows[0]["PAY_METHOD"].ToString();
*
* exSheet.Cells[13, 4].Value = "Amount (" + dtMst.Rows[0]["REQ_CCY"].ToString() + " )";
*
*
* exSheet.Cells[15, 4].Value = long.Parse(dtTotal.Rows[0][0].ToString());//total
* exSheet.Cells[15, 4, 15, 4].NumberFormat = "#,##0";
*
* exSheet.Cells[17, 4].Value = long.Parse(dtTotal.Rows[0][0].ToString());//total rerurn company
* exSheet.Cells[17, 4, 17, 4].NumberFormat = "#,##0";*/
//----------------------
////end-set-----DataBind--in---Master---
for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++)
{
exSheet.Range["A22"].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
for (int l_row = 1; l_row <= dtDetail.Rows.Count; l_row++)
{
for (int col = 0; col <= 14; col++)
{
if (col == 0) // seq
{
exSheet.Cells[20 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1]["rownum"].ToString();
}
else if (col == 1) // item code
{
exSheet.Cells[20 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1]["item_code"].ToString();
}
else if (col == 2) // item name
{
exSheet.Cells[20 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1]["item_name"].ToString();
exSheet.Cells[20 + l_row, 3, 20 + l_row, 5].Merge();
}
else if (col == 5) // uom
{
exSheet.Cells[20 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1]["out_uom"].ToString();
}
else if (col == 6) // qty
{
exSheet.Cells[20 + l_row, col + 1].Value = Convert.ToDouble(dtDetail.Rows[l_row - 1]["out_qty"]);
}
else if (col == 7) // unit_price
{
exSheet.Cells[20 + l_row, col + 1].Value = Convert.ToDouble(dtDetail.Rows[l_row - 1]["unit_price"]);
}
else if (col == 8) // discount_rate
{
exSheet.Cells[20 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["discount_rate"]);
}
else if (col == 9) // discount_amount
{
exSheet.Cells[20 + l_row, col + 1].Value = Convert.ToDouble(dtDetail.Rows[l_row - 1]["discount_amount"]);
exSheet.Cells[20 + l_row, 10, 20 + l_row, 12].Merge();
}
else if (col == 12) // total_amount
{
exSheet.Cells[20 + l_row, col + 1].Value = Convert.ToDouble(dtDetail.Rows[l_row - 1]["total_amount"]);
}
else if (col == 13) // ref_no
{
exSheet.Cells[20 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1]["ref_no"].ToString();
}
else if (col == 14) // description
{
exSheet.Cells[20 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1]["description"].ToString();
}
/* if (col == 0)
* {
* exSheet.Cells[20 + l_row, col].Value = l_row; // no
* }
* else
* {
* exSheet.Cells[20 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1]["item_code"].ToString();
* }
*
* else
* {
* exSheet.Cells[12 + l_row, col + 1].Value = long.Parse(dtDetail.Rows[l_row - 1][col].ToString());
* exSheet.Cells[13, col + 1, l_row + 13, col + 1].NumberFormat = "#,##0";
* exSheet.Cells[l_row + 12, col + 1].HorizontalAlignment = XlHAlign.xlHAlignRight;
* exSheet.Range[13, col + 1, l_row + 13, 6].Font.Bold = false;
* }
*
* exSheet.Cells[l_row + 12, 1].HorizontalAlignment = XlHAlign.xlHAlignCenter;
* exSheet.Cells[l_row + 13, 1].HorizontalAlignment = XlHAlign.xlHAlignCenter;
* exSheet.Cells[l_row + 13, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
* exSheet.Cells[l_row + 13, 3].HorizontalAlignment = XlHAlign.xlHAlignLeft;
* exSheet.Cells[l_row + 13, 5].HorizontalAlignment = XlHAlign.xlHAlignLeft;
* exSheet.Cells[l_row + 13, 6].HorizontalAlignment = XlHAlign.xlHAlignLeft;*/
}
}
exSheet.Cells[dtDetail.Rows.Count + 21, 7].Value = "=SUM(G21:G" + (dtDetail.Rows.Count + 20) + ")";
exSheet.Cells[dtDetail.Rows.Count + 21, 10].Value = "=SUM(J21:J" + (dtDetail.Rows.Count + 20) + ")";
exSheet.Cells[dtDetail.Rows.Count + 21, 13].Value = "=SUM(M21:M" + (dtDetail.Rows.Count + 20) + ")";
exSheet.Cells[dtDetail.Rows.Count + 22, 13].Value = "=SUM(M21:M" + (dtDetail.Rows.Count + 20) + ")*10%";
exSheet.Cells[dtDetail.Rows.Count + 23, 13].Value = "=SUM(M21:M" + (dtDetail.Rows.Count + 20) + ")+ (SUM(M21:M" + (dtDetail.Rows.Count + 20) + ")*10%)";
// 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);
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(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_bisc00070_happycook.xls";
string TempFile = "../../../../system/temp/rpt_bisc00070_happycook_" + 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 dtInfo = CtlLib.TableReadOpenCursor("st_lg_rpt_BISC00070", "'" + Session["User_ID"].ToString() + "'");
DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_BISC00070_HAPPY_1", para);
//--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, 131, 71));
}
exSheet.Cells[1, 3].Value = dtInfo.Rows[0]["partner_name"].ToString();
exSheet.Cells[2, 3].Value = dtInfo.Rows[0]["addr1"].ToString();
exSheet.Cells[3, 3].Value = "Tax code:" + dtInfo.Rows[0]["tax_code"].ToString();
exSheet.Cells[1, 13].Value = "Tell:" + dtInfo.Rows[0]["phone_no"].ToString();
exSheet.Cells[2, 13].Value = "Fax:" + dtInfo.Rows[0]["fax_no"].ToString();
exSheet.Cells[3, 13].Value = "Print date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm");
exSheet.Cells[5, 13].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");
//---------
/*exSheet.Cells[10, 4].Value = dtMaster.Rows[0]["bill_partner_name"].ToString();
* exSheet.Cells[10, 12].Value = dtMaster.Rows[0]["deli_partner_name"].ToString();
*
* exSheet.Cells[11, 4].Value = dtMaster.Rows[0]["short_nm"].ToString();
* exSheet.Cells[11, 12].Value = dtMaster.Rows[0]["deli_addr1"].ToString();
*
* exSheet.Cells[12, 4].Value = dtMaster.Rows[0]["bill_addr1"].ToString();
* exSheet.Cells[13, 4].Value = dtMaster.Rows[0]["tax_code"].ToString();
* exSheet.Cells[13, 6].Value = dtMaster.Rows[0]["phone_no"].ToString();
*
* exSheet.Cells[14, 4].Value = dtMaster.Rows[0]["po_date"].ToString();
*
* exSheet.Cells[15, 4].Value = dtMaster.Rows[0]["po_no"].ToString();
* exSheet.Cells[11, 2].Value = dtMst.Rows[0]["charger"].ToString();
* exSheet.Cells[12, 2].Value = dtMst.Rows[0]["department_name"].ToString();
* exSheet.Cells[8, 2].Value = dtMst.Rows[0]["partner_name"].ToString();
* exSheet.Cells[8, 3].Value = dtMst.Rows[0]["ACCOUNT_NO"].ToString();
* exSheet.Cells[22, 2].Value = dtMst.Rows[0]["BEDEFI_BANK_NM"].ToString();
* exSheet.Cells[17, 6].Value = dtMst.Rows[0]["PAY_METHOD"].ToString();
*
* exSheet.Cells[13, 4].Value = "Amount (" + dtMst.Rows[0]["REQ_CCY"].ToString() + " )";
*
*
* exSheet.Cells[15, 4].Value = long.Parse(dtTotal.Rows[0][0].ToString());//total
* exSheet.Cells[15, 4, 15, 4].NumberFormat = "#,##0";
*
* exSheet.Cells[17, 4].Value = long.Parse(dtTotal.Rows[0][0].ToString());//total rerurn company
* exSheet.Cells[17, 4, 17, 4].NumberFormat = "#,##0";*/
//----------------------
////end-set-----DataBind--in---Master---
for (int l_addrow = 1; l_addrow < dtDetail.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
for (int l_row = 1; l_row <= dtDetail.Rows.Count; l_row++)
{
for (int col = 0; col <= 14; col++)
{
if (col == 0) // seq
{
exSheet.Cells[8 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1]["rownum"].ToString();
}
else if (col == 1) // item code
{
exSheet.Cells[8 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1]["item_code"].ToString();
}
else if (col == 2) // uom
{
exSheet.Cells[8 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1]["uom"].ToString();
//exSheet.Cells[8 + l_row, 3, 8 + l_row, 5].Merge();
}
else if (col == 3) // begin qty
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["begin_qty"]);
}
else if (col == 4) // Production
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["prod"]);
}
else if (col == 5) // other in
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["other_in"]);
}
else if (col == 6) //buy
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToDouble(dtDetail.Rows[l_row - 1]["buy"]);
}
else if (col == 7) // transfer_in
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToDouble(dtDetail.Rows[l_row - 1]["transfer_in"]);
}
else if (col == 8) // assembly_in
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["assembly_in"]);
}
else if (col == 9) // return
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToDouble(dtDetail.Rows[l_row - 1]["return"]);
}
else if (col == 10) // total_in
{
exSheet.Cells[8 + l_row, col + 1].Value = "=SUM(E" + (8 + l_row) + ":J" + (8 + l_row) + ")";
}
else if (col == 11) // sale
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["sale"]);
}
else if (col == 12) // pro_order
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["pro_order"]);
}
else if (col == 13) // warranty
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["warranty"]);
}
else if (col == 14) // promote
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["promote"]);
}
else if (col == 15) // manager
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["manager"]);
}
else if (col == 16) // sample
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["sample"]);
}
else if (col == 17) // packing
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["packing"]);
}
else if (col == 18) // assembly_out
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["assembly_out"]);
}
else if (col == 19) // transfer_out
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["transfer_out"]);
}
else if (col == 8) // gift
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["gift"]);
}
else if (col == 21) // export
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["export"]);
}
else if (col == 22) // total_out
{
exSheet.Cells[8 + l_row, col + 1].Value = "=SUM(L" + (8 + l_row) + ":V" + (8 + l_row) + ")";
}
else if (col == 23) // end_qty
{
exSheet.Cells[8 + l_row, col + 1].Value = "=D" + (8 + l_row) + "+ K" + (8 + l_row) + "- W" + (8 + l_row);
}
else if (col == 24) // description
{
exSheet.Cells[8 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1]["description"].ToString();
}
/* if (col == 0)
* {
* exSheet.Cells[8 + l_row, col].Value = l_row; // no
* }
* else
* {
* exSheet.Cells[8 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1]["item_code"].ToString();
* }
*
* else
* {
* exSheet.Cells[12 + l_row, col + 1].Value = long.Parse(dtDetail.Rows[l_row - 1][col].ToString());
* exSheet.Cells[13, col + 1, l_row + 13, col + 1].NumberFormat = "#,##0";
* exSheet.Cells[l_row + 12, col + 1].HorizontalAlignment = XlHAlign.xlHAlignRight;
* exSheet.Range[13, col + 1, l_row + 13, 6].Font.Bold = false;
* }
*
* exSheet.Cells[l_row + 12, 1].HorizontalAlignment = XlHAlign.xlHAlignCenter;
* exSheet.Cells[l_row + 13, 1].HorizontalAlignment = XlHAlign.xlHAlignCenter;
* exSheet.Cells[l_row + 13, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
* exSheet.Cells[l_row + 13, 3].HorizontalAlignment = XlHAlign.xlHAlignLeft;
* exSheet.Cells[l_row + 13, 5].HorizontalAlignment = XlHAlign.xlHAlignLeft;
* exSheet.Cells[l_row + 13, 6].HorizontalAlignment = XlHAlign.xlHAlignLeft;*/
}
}
exSheet.Cells[dtDetail.Rows.Count + 9, 4].Value = "=SUM(D9:D" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 5].Value = "=SUM(E9:E" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 6].Value = "=SUM(F9:F" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 7].Value = "=SUM(G9:G" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 8].Value = "=SUM(H9:H" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 9].Value = "=SUM(I9:I" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 10].Value = "=SUM(J9:J" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 11].Value = "=SUM(K9:K" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 12].Value = "=SUM(L9:L" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 13].Value = "=SUM(M9:M" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 14].Value = "=SUM(N9:N" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 15].Value = "=SUM(O9:O" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 16].Value = "=SUM(P9:P" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 17].Value = "=SUM(Q9:Q" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 18].Value = "=SUM(R9:R" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 19].Value = "=SUM(S9:S" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 20].Value = "=SUM(T9:T" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 21].Value = "=SUM(U9:U" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 22].Value = "=SUM(V9:V" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 23].Value = "=SUM(W9:W" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 24].Value = "=SUM(X9:X" + (dtDetail.Rows.Count + 8) + ")";
//exSheet.Cells[dtDetail.Rows.Count + 22, 13].Value = "=SUM(M21:M" + (dtDetail.Rows.Count + 8) + ")*10%";
//exSheet.Cells[dtDetail.Rows.Count + 23, 13].Value = "=SUM(M21:M" + (dtDetail.Rows.Count + 8) + ")+ (SUM(M21:M" + (dtDetail.Rows.Count + 8) + ")*10%)";
// 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);
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(pdfFilePath);
Response.WriteFile(TempFile);
//Stop execute
Response.End();
}