示例#1
0
        public void ExecuteSQLExtension(HttpContext ctx)
        {
            string connString = ConfigurationManager.AppSettings["strConn"];

            connString += "User Id=" + _dbuser + ";Password="******"|!" };
            string           procName;
            XmlTextWriter    xw = new XmlTextWriter(ctx.Response.OutputStream, new System.Text.UTF8Encoding(true));
            XmlNodeList      xinputs;
            OracleConnection connection = new OracleConnection(connString);

            connection.Open();
            OracleCommand     command = connection.CreateCommand();
            OracleTransaction transaction;

            transaction         = connection.BeginTransaction();
            command.Transaction = transaction;
            try
            {
                if (_dsoType == "process")
                {
                    //get input parameter listt
                    xinputs = _clientXMLDoc.SelectNodes("//input");

                    procName      = "Call " + _procedure + "( ";
                    _exeStatement = procName;
                    _parameter    = xinputs[0].InnerText;
                    parameter     = _parameter.Split(CDelimeter, System.StringSplitOptions.None);

                    command.CommandText = "select data from esys.tes_dxf_file where pk = '" + parameter[0] + "'";
                    //SaveLogData();
                    //string s = (string) command.ExecuteOracleScalar();
                    OracleDataReader reader = command.ExecuteReader();
                    reader.Read();
                    OracleLob    blob           = reader.GetOracleLob(0);
                    StreamReader myStreamReader = new StreamReader(blob, System.Text.Encoding.Default);
                    string       s         = myStreamReader.ReadToEnd();
                    string       file_name = "temp/tmp" + DateTime.Now.ToString("yymmdd-hh24miss") + ".dxf";
                    CtlLib.EmptyFolder("temp");
                    CtlLib.CreateFile(file_name, s);
                    _p_rtn_value = file_name;
                }
                transaction.Commit();
                ExecuteSQLSelect(ctx);
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                _errorno  = "1";
                _errormsg = ex.Message;
                SaveLogData();
                BuildErrorXML(xw);
            }
            finally
            {
                command.Dispose();
                connection.Close();
            }
        }
示例#2
0
 protected void Page_Load(object sender, EventArgs e)
 {
     try
     {
         string connString = ConfigurationManager.AppSettings["strConn"];
         connString += "User Id=Ctl;Password=Ctl2";
         OracleConnection connection = new OracleConnection(connString);
         connection.Open();
         OracleCommand command    = connection.CreateCommand();
         string        login_user = Session["User_ID"].ToString();
         command.CommandText = "call Ctl.sp_upd_user_logout('" + login_user + "')";
         command.ExecuteNonQuery();
         command.Dispose();
         connection.Close();
     }
     catch (Exception ex)
     {
         CtlLib.WriteLogError(ex.Message);
     }
     Session.Abandon();
     Response.Redirect("../Login.aspx");
 }
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser("sale");

        string p_from_dt   = Request["p_from_dt"];
        string p_to_dt     = Request["p_to_dt"];
        string p_item      = Request["p_item"];
        string p_item_type = Request["p_item_type"];

        string TemplateFile = "rpt_dsbs00230_2.xls";
        string TempFile     = "../../../../system/temp/rpt_dsbs00230_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_from_dt + "','" + p_to_dt + "','" + p_item + "','" + p_item_type + "'";
        DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_RPT_DSBS00230_3", para);

        if (dtDetail.Rows.Count == 0)
        {
            Response.Write("No Data"); Response.End();
        }

        for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++)
        {
            exSheet.Range["A5"].Rows.EntireRow.Insert();//insert row new of sheet
        }

        int pos = 4;

        for (int l_row = 0; l_row < dtDetail.Rows.Count; l_row++)
        {
            exSheet.Cells[pos + l_row, 1].Value = dtDetail.Rows[l_row]["item_code"].ToString();
            exSheet.Cells[pos + l_row, 2].Value = dtDetail.Rows[l_row]["item_name"].ToString();
            exSheet.Cells[pos + l_row, 3].Value = dtDetail.Rows[l_row]["out_uom"].ToString();
            exSheet.Cells[pos + l_row, 4].Value = dtDetail.Rows[l_row]["out_qty"];
            exSheet.Cells[pos + l_row, 5].Value = dtDetail.Rows[l_row]["item_amount"];
            exSheet.Cells[pos + l_row, 6].Value = dtDetail.Rows[l_row]["tax_amount"];
            exSheet.Cells[pos + l_row, 7].Value = dtDetail.Rows[l_row]["total_amount"];
            exSheet.Cells[pos + l_row, 8].Value = dtDetail.Rows[l_row]["ccy_unit"].ToString();
        }
        exSheet.Cells[dtDetail.Rows.Count + 4, 5].Value = "=SUM(E4:E" + (dtDetail.Rows.Count + 3) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 4, 6].Value = "=SUM(F4:F" + (dtDetail.Rows.Count + 3) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 4, 7].Value = "=SUM(G4:G" + (dtDetail.Rows.Count + 3) + ")";

        // 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();
    }
示例#4
0
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser(Session["APP_DBUSER"].ToString());
        //string l_pk = Request["master_pk"];

        string TemplateFile = "rpt_bisc00020_O_4.xls";
        string TempFile     = "../../../../system/temp/rpt_bisc00020_O_4_" + Session["User_ID"].ToString() + ".xls";

        TemplateFile = Server.MapPath(TemplateFile);
        TempFile     = Server.MapPath(TempFile);

        //Create a new workbook
        IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);

        //Add worksheet
        IWorksheet exSheet = exBook.Worksheets[1];

        IRange range;

        //bind data to excel file

        //string para = "'" + l_pk + "'";
        string p_from_date        = Request["p_from_date"];
        string p_to_date          = Request["p_to_date"];
        string p_wh_type          = Request["p_wh_type"];
        string p_tin_warehouse_pk = Request["p_wh"];
        string p_use_yn           = Request["p_use_yn"];
        string p_item_group_pk    = Request["p_item_group_pk"];
        string p_item             = Request["p_item"];
        string p_in_out_yn        = Request["p_in_out_yn"];
        string p_wh_name          = Request["p_wh_name"];

        string    para = "'" + p_from_date + "','" + p_to_date + "','" + p_wh_type + "','" + p_tin_warehouse_pk + "','" + p_use_yn + "','" + p_item_group_pk + "','" + p_item + "','" + p_in_out_yn + "'";
        DataTable dt   = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00020_O_4", "'" + Session["User_ID"].ToString() + "'");
        DataTable dt1  = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00020_O_1", para);

        //--set-----DataBind--in---Master---
        if (!string.IsNullOrEmpty(dt.Rows[0]["pic"].ToString()))
        {
            exSheet.Cells[1, 1].Select();
            byte[] MyData = new byte[0];
            MyData = (byte[])dt.Rows[0]["pic"];
            MemoryStream stream = new MemoryStream(MyData);

            Bitmap b = new Bitmap(stream);
            exSheet.Shapes.AddPicture(ResizeImage(b, 90, 59));
        }

        exSheet.Cells[6, 1].Value = "W/H Name : " + p_wh_name;

        exSheet.Cells[2, 3].Value = dt.Rows[0]["partner_name"].ToString();

        exSheet.Cells[3, 3].Value = dt.Rows[0]["addr1"].ToString();

        exSheet.Cells[4, 3].Value = "Tax code:" + dt.Rows[0]["tax_code"].ToString();

        exSheet.Cells[2, 6].Value = "Tell:" + dt.Rows[0]["phone_no"].ToString();

        exSheet.Cells[3, 6].Value = "Fax:" + dt.Rows[0]["fax_no"].ToString();

        exSheet.Cells[4, 6].Value = "Print date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm");

        exSheet.Cells[6, 6].Value = System.DateTime.ParseExact(p_from_date, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy") + "-" + System.DateTime.ParseExact(p_to_date, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy");

        for (int l_addrow = 1; l_addrow < dt1.Rows.Count; l_addrow++)
        {
            exSheet.Range["A9"].Rows.EntireRow.Insert();//insert row new of sheet
        }
        double total = 0;

        for (int l_row = 0; l_row <= dt1.Rows.Count - 1; l_row++)
        {
            total = total + CommondLib.ConvertToDbl(dt1.Rows[l_row]["begin_qty"].ToString());
            exSheet.Cells[8 + l_row, 1].Value = l_row + 1;
            exSheet.Cells[8 + l_row, 2].Value = dt1.Rows[l_row]["item_code"];
            exSheet.Cells[8 + l_row, 3].Value = dt1.Rows[l_row]["item_name"];
            exSheet.Cells[8 + l_row, 4].Value = dt1.Rows[l_row]["uom"];
            if (dt1.Rows[l_row]["begin_qty"].ToString() != "")
            {
                exSheet.Cells[8 + l_row, 5].Value = CommondLib.ConvertToDbl(dt1.Rows[l_row]["begin_qty"].ToString());
            }
        }

        exSheet.Cells[8 + dt1.Rows.Count, 5].Value = total;

        if (File.Exists(TempFile))
        {
            File.Delete(TempFile);
        }


        // hide row firts

        range = exSheet.Range["A1"];
        // hide row A5
        range.Rows.Hidden = true;

        // font bold header

        exBook.SaveAs(TempFile);
        System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
        Response.Clear();
        Response.Charset         = "UTF-8";
        Response.ContentEncoding = System.Text.Encoding.UTF8;
        //Add header, give a default file name for "File Download/Store as"
        Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
        //Add header, set file size to enable browser display download progress
        Response.AddHeader("Content-Length", file.Length.ToString());
        //Set the return string is unavailable reading for client, and must be downloaded
        Response.ContentType = "application/ms-exSheet";
        //Response.ContentType = "application/pdf";
        //Send file string to client
        Response.WriteFile(TempFile);
        //Response.WriteFile(pdfFilePath);
        //Stop execute
        Response.End();
    }
示例#5
0
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            string sqlText = "";
            try
            {
                string img_pk     = Request.QueryString["img_pk"];
                string table_name = Request.QueryString["table_name"];

                if (img_pk == null || img_pk == "" || img_pk == "0")
                {
                    img_pk = "-1";//show default image
                }

                if (table_name.IndexOf(".") > 1)
                {
                    dbuser = table_name.Substring(0, table_name.IndexOf("."));

                    //get database pass by database user name
                    conString  = ConfigurationManager.AppSettings["strConn"];
                    conString += "User Id=GASP;password=gasp2";
                    connection = new OracleConnection(conString);
                    command    = new OracleCommand();

                    command.CommandText = "Call GASP.sp_sel_db_pass2('" + dbuser + "', :p_rtn_value)";
                    command.Parameters.Add(":p_rtn_value", OracleType.Cursor).Direction = ParameterDirection.Output;
                    connection.Open();
                    command.Connection = connection;
                    OracleDataReader reader = command.ExecuteReader();
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            dbpass = reader["dbuser_pwd"].ToString();
                        }
                        conString  = ConfigurationManager.AppSettings["strConn"];
                        conString += "User Id=" + dbuser + ";password="******"select * from " + table_name + " where pk =" + img_pk;
                    }
                }
                else
                {
                    dbuser     = Session["APP_DBUSER"].ToString();
                    dbpass     = Session["APP_DBPASS"].ToString();
                    conString  = ConfigurationManager.AppSettings["strConn"];
                    conString += "User Id=" + dbuser + ";password="******"select * from " + dbuser + "." + table_name + " where pk =" + img_pk;
                }



                connection = new OracleConnection(conString);
                connection.Open();
                command = connection.CreateCommand();

                command.CommandText = sqlText;
                OracleDataReader dr          = command.ExecuteReader();
                byte[]           fileData    = null;
                string           contenttype = "";
                string           filename    = "";
                if (dr.Read())
                {
                    filename    = dr["filename"].ToString();
                    contenttype = dr["CONTENTTYPE"].ToString();
                    fileData    = (byte[])dr["DATA"];
                }
                connection.Close();

                Response.ClearContent();
                Response.AddHeader("Content-Disposition", "attachment; filename=" + filename);
                BinaryWriter bw = new BinaryWriter(Response.OutputStream);
                bw.Write(fileData);
                bw.Close();
                Response.ContentType = contenttype;
                Response.End();
            }
            catch (Exception ex)
            {
                CtlLib.WriteLogError(ex.Message + ". " + sqlText);
                throw (ex);
            }
        }
    }
示例#6
0
    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_tamtru"];
        string TemplateFile          = "rpt_60300020_PA18.xml";
        string TempFile = "../../../../system/temp/rpt_60300020_PA18_" + Session["User_ID"].ToString() + ".xml";

        //string TempFile = "../../../../system/temp/rpt_60300020_PA18" + ".xml";
        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_60250030_PA18", 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, 6].Value = dt.Rows[0]["print_time"].ToString();
         * }*/

        //----------------------
        ////end-set-----DataBind--in---Master---

        int    l_row_first = 5, l_cnt = dt.Rows.Count;
        string v_all_data_to_xml = "";

        /*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++)
        {
            if (dt.Rows[l_row]["col1"].ToString() != "")
            {
                v_all_data_to_xml = v_all_data_to_xml + dt.Rows[l_row]["col1"].ToString();
            }
        }

        //==============================
        StreamWriter log;

        if (!File.Exists(TempFile))
        {
            File.WriteAllText(TempFile, String.Empty);
            //log = new StreamWriter(TempFile);
        }

        /*else
         * {
         * //File.AppendAllText("c:\\file.txt","Your Text");
         * log = File.AppendText(TempFile);
         * }*/
        //TempFile = "../../../../system/temp/rpt_60300020_PA18.xml";
        // Write to the file:
        //log.WriteLine(DateTime.Now);
        log = new StreamWriter(TempFile);
        log.WriteLine(v_all_data_to_xml);
        log.WriteLine();

        // Close the stream:
        log.Close();
        //==============================
        // 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
        //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/xml";
        //Response.ContentType = "application/pdf";
        //Send file string to client
        Response.WriteFile(TempFile);
        //Response.WriteFile(pdfFilePath);
        //Stop execute
        Response.End();
    }
示例#7
0
    protected void Page_Load(object sender, EventArgs e)
    {
        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_1.xls";
        string TempFile     = "../../../../system/temp/rpt_hrpm00700_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;

        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,   " +
              "    A.FULL_NAME,   " +
              "    to_char(to_date(A.JOIN_DT,'yyyymmdd'),'dd/mm/yyyy') join_dt,   " +
              "    to_char(to_date(A.left_DT,'yyyymmdd'),'dd/mm/yyyy') left_dt,   " +
              "    A.POS_NM,   " +
              "    ROUND(NVL(A.SALARY_LEVEL1,0),10),   " +
              "    ROUND(NVL(A.SALARY_LEVEL2,0),10),   " +
              "    ROUND(NVL(A.SALARY_LEVEL1,0)*NVL(A.EXCHANGE_RATE,1),0),   " +
              "    ROUND(NVL(A.SALARY_LEVEL2,0)*NVL(A.EXCHANGE_RATE,1),0),   " +
              "    ROUND(NVL(A.ALLOW_AMT1,0)*NVL(A.EXCHANGE_RATE,1),0),   " +
              "    ROUND(NVL(A.ALLOW_AMT2,0)*NVL(A.EXCHANGE_RATE,1),0),   " +
              "    ROUND(NVL(A.ALLOW_AMT3,0)*NVL(A.EXCHANGE_RATE,1),0),   " +
              "    ROUND (NVL (A.ALLOW_AMT1, 0) + NVL (A.ALLOW_AMT2, 0) + NVL (A.ALLOW_AMT3, 0), 0) total_allow, " +
              "    ROUND ((NVL (A.ALLOW_AMT1, 0) + NVL (A.ALLOW_AMT2, 0) + NVL (A.ALLOW_AMT3, 0))*NVL(A.EXCHANGE_RATE,1), 0) total_allow_vnd, " +
              "    ROUND(NVL(A.WT_L1,0)/8 + NVL(a.HOL_DAY_L1,0),10),  " +
              "    ROUND(NVL(A.WT_L2,0)/8 + NVL(a.HOL_DAY_L2,0),10),  " +
              "    ROUND(NVL(A.ABS_ALE_L1,0)/8,10),  " +
              "    ROUND(NVL(A.ABS_ALE_L2,0)/8,10),  " +
              "    ROUND(NVL(A.ABS_OTHER_PAY_L1,0)/8,10),  " +
              "    ROUND(NVL(A.ABS_OTHER_PAY_L2,0)/8,10),  " +
              "    ROUND((NVL(A.WT_L1_AMT,0) + nvl(A.WT_L2_AMT,0) + nvl(A.TOTAL_ABS_PAY_L1_AMT,0) + nvl(A.TOTAL_ABS_PAY_L2_AMT,0)+ NVL(a.HOL_DAY_L1_AMT,0)+ NVL(a.HOL_DAY_L2_AMT,0))*NVL(A.EXCHANGE_RATE,1),0) total_wd_amt,   " +
              "    ROUND(NVL(A.OT_L1,0),10),  " +
              "    ROUND(NVL(A.OT_L2,0),10),  " +
              "    ROUND(NVL(A.ST_L1,0) + nvl(A.HT_L1,0)+ nvl(A.OHT_L1,0),10),  " +
              "    ROUND(NVL(A.ST_L2,0) + nvl(A.HT_L2,0)+ nvl(A.OHT_L2,0),10),  " +
              "    ROUND(NVL(A.NT_30_L1,0),10),  " +
              "    ROUND(NVL(A.NT_30_L2,0),10),  " +
              "    ROUND(NVL(A.NT_45_L1,0),10),  " +
              "    ROUND(NVL(A.NT_45_L2,0),10),  " +
              "    ROUND(NVL(A.NT_60_L1,0),10),  " +
              "    ROUND(NVL(A.NT_60_L2,0),10),  " +
              "    ROUND(NVL(A.NT_90_L1,0),10),  " +
              "    ROUND(NVL(A.NT_90_L2,0),10),  " +
              "    ROUND((NVL(A.TOTAL_OT_L1_AMT,0) + nvl(A.TOTAL_OT_L2_AMT,0))*NVL(A.EXCHANGE_RATE,1),0) total_ot_amt,  " +
              "    ROUND((NVL(A.OT_TAX_L1_AMT,0) + nvl(A.OT_TAX_L2_AMT,0))*NVL(A.EXCHANGE_RATE,1),0) total_ot_tax,  " +
              "    ROUND((NVL(A.RETURN_AMT,0) + nvl(A.INC_AMT,0))*NVL(A.EXCHANGE_RATE,1),0),  " +
              "    ROUND(NVL(A.ALE_STOP,0),10),   " +
              "    ROUND((NVL(A.ALE_STOP_AMT,0))*NVL(A.EXCHANGE_RATE,1),0),   " +
              "    ROUND(NVL(A.SEVERANCE_MONTH,0),10),   " +
              "    ROUND(NVL(A.SEVERANCE_AMT,0)*NVL(A.EXCHANGE_RATE,1),0),   " +
              "    ROUND(NVL(A.GROSS_AMT,0)*NVL(A.EXCHANGE_RATE,1),0),   " +
              "    ROUND(NVL(A.NET_AMT,0)*NVL(A.EXCHANGE_RATE,1),0), " +
              "    ROUND(NVL(A.ADVANCE_AMT,0)*NVL(A.EXCHANGE_RATE,1),0),  " +
              "    ROUND(NVL(A.DEDUCT_PIT,0)*NVL(A.EXCHANGE_RATE,1),0),  " +
              "    ROUND(NVL(A.HEALTH_COM_NET,0)*NVL(A.EXCHANGE_RATE,1) ,0),   " +
              "    ROUND(NVL(A.INCOME_AMT_AFTER,0)*NVL(A.EXCHANGE_RATE,1) ,0),   " +
              "    ROUND(NVL(A.ALLOW_AMT4,0)*NVL(A.EXCHANGE_RATE,1),0) house_company, " +
              "    ROUND(NVL(A.HOUSING_FEE,0)*NVL(A.EXCHANGE_RATE,1),0),  " +
              "    ROUND((NVL(A.NET_AMT,0) + NVL(A.HOUSING_FEE,0))*NVL(A.EXCHANGE_RATE,1),0),  " +
              "    ROUND(NVL(A.INCOME_BEFORE_TAX,0)*NVL(A.EXCHANGE_RATE,1),0),  " +
              "    ROUND(NVL(A.INCOME_TAX_NET,0)*NVL(A.EXCHANGE_RATE,1),0),   " +
              "    ROUND(NVL(A.INS_SALARY,0)*NVL(A.EXCHANGE_RATE,1),0), " +
              "    ROUND(NVL(A.HEALTH_45_AMT,0)*NVL(A.EXCHANGE_RATE,1),0),   " +
              "    ROUND((NVL(A.NET_AMT,0) + NVL(A.HOUSING_FEE,0) + NVL(A.INCOME_TAX_NET,0) + NVL(A.HEALTH_45_AMT,0))*NVL(A.EXCHANGE_RATE,1),0) total_cost  " +
              "    FROM THR_MONTH_SALARY A  " +
              "    WHERE A.DEL_IF=0 AND A.WORK_MON='" + p_work_mon + "'  " +
              "    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_m_type + "','ALL','ALL',a.MONEY_KIND)='" + p_m_type + "'" +
              "   and decode('" + p_emp_type + "','ALL','ALL',A.EMPLOYEE_TYPE)='" + p_emp_type + "'" +
              "     AND NVL(A.NET_AMT,0) " + p_type +
              " ORDER BY A.DEPT_NM,A.THR_GROUP_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);


        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 = 6;
        int allow = 11, allow_k = 23, equip = 112;     //vi tri phu cap
        int pos_nt = 23;
        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();



        //---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; i++)
        {
            exSheet.Range[pos + i + 1, 1].Rows.EntireRow.Insert();
        }

        //Show data
        for (i = 0; i < irow_emp; i++)
        {
            for (k = 8; k < icol_emp; k++)
            {
                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 < 8)
                {
                    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)
            {
                exSheet.Cells[pos + i + 1, 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 = 8; k < icol_emp; k++)
                {
                    exSheet.Cells[pos + i + 1, k].Value = grant_total[k];
                }
            }
        }


        // 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("sale");


        string p_from_date   = Request["p_from_date"];
        string p_to_date     = Request["p_to_date"];
        string p_order_type  = Request["p_order_type"];
        string p_po_vendor   = Request["p_po_vendor"];
        string p_item_group  = Request["p_item_group"];
        string p_item        = Request["p_item"];
        string p_search_type = Request["p_searchtype"];
        string p_curr        = Request["p_curr"];
        string p_io_type     = Request["p_io_type"];
        string p_sale_yn     = Request["p_sale_yn"];
        string p_wh_type     = Request["p_wh_type"];
        string p_wh_pk       = Request["p_wh_pk"];
        string p_gd_type     = Request["p_gd_type"];

        string TemplateFile = "";
        string TempFile     = "";


        if (p_search_type == "REQ")
        {
            TemplateFile = "rpt_dscd00030.xls";
            TempFile     = "../../../../system/temp/rpt_dscd00030_" + Session["User_ID"].ToString() + ".xls";
            TemplateFile = Server.MapPath(TemplateFile);
            TempFile     = Server.MapPath(TempFile);
        }
        else
        {
            TemplateFile = "rpt_dscd00030_1.xls";
            TempFile     = "../../../../system/temp/rpt_dscd00030_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;

        //bind data to excel file

        string    para     = "'" + p_from_date + "','" + p_to_date + "','" + p_order_type + "','" + p_po_vendor + "','" + p_item_group + "','" + p_item + "','" + p_search_type + "','" + p_curr + "','" + p_io_type + "','" + p_sale_yn + "','" + p_wh_type + "','" + p_wh_pk + "','" + p_gd_type + "'";
        DataTable dtInfo   = CtlLib.TableReadOpenCursor("st_lg_rpt_dscd00030_INFO");
        DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_dscd00030", 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, 95, 55));
        }

        exSheet.Cells["P2"].Value = "Print date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm");

        string s_date = System.DateTime.ParseExact(p_from_date, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy");

        s_date += " - " + System.DateTime.ParseExact(p_to_date, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy");
        exSheet.Cells["B2"].Value = "Date : " + s_date;


        for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++)
        {
            exSheet.Range["A5"].Rows.EntireRow.Insert();//insert row new of sheet
        }

        for (int i = 0; i < dtDetail.Rows.Count; i++)
        {
            for (int j = 0; j < dtDetail.Columns.Count; j++)
            {
                exSheet.Cells[4 + i, j + 1].Value = dtDetail.Rows[i][j];
            }
        }
        exSheet.Cells[dtDetail.Rows.Count + 4, 12].Value = "=SUM(L4:L" + (dtDetail.Rows.Count + 3) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 4, 14].Value = "=SUM(N4:N" + (dtDetail.Rows.Count + 3) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 4, 16].Value = "=SUM(P4:P" + (dtDetail.Rows.Count + 3) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 4, 17].Value = "=SUM(Q4:Q" + (dtDetail.Rows.Count + 3) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 4, 19].Value = "=SUM(S4:S" + (dtDetail.Rows.Count + 3) + ")";
        //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) + ")";
        // 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();
    }
示例#9
0
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser(Session["APP_DBUSER"].ToString());

        string l_pk = Request["p_master_pk"];

        string TemplateFile = "rpt_60240010_Group_Record.xls";
        string TempFile     = "../../../../system/temp/rpt_60240010_Group_Record_" + 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_60240010_Group_Record", para);
        //-----header---

        //---end--header---
        //--set-----DataBind--in---Master---
        if (dt.Rows.Count == 0)
        {
            exSheet.Cells[6, 8].Value = "There is not data!";
        }
        if (dt.Rows.Count > 0)
        {
            //No.of person: Adult:       Child:
            exSheet.Cells[4, 3].Value = "No. of Room :" + dt.Rows[0]["total_room"].ToString();
            exSheet.Cells[4, 1].Value = "No.of person: Adult: " + dt.Rows[0]["adult"].ToString() + " Child: " + dt.Rows[0]["childrens"].ToString();
            exSheet.Cells[3, 2].Value = dt.Rows[0]["from_date"].ToString();
            exSheet.Cells[3, 4].Value = dt.Rows[0]["arrival_time"].ToString();
            exSheet.Cells[3, 6].Value = dt.Rows[0]["date_to"].ToString();
            exSheet.Cells[3, 8].Value = dt.Rows[0]["departure_time"].ToString();
            exSheet.Cells[2, 6].Value = dt.Rows[0]["company_name"].ToString();
            exSheet.Cells[2, 2].Value = dt.Rows[0]["group_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_total_room = 0, l_total_guest = 0;

        if (dt.Rows.Count > 0)
        {
            int l_cnt = dt.Rows.Count;
            for (int l_row = 1; l_row <= dt.Rows.Count; l_row++)
            {
                exSheet.Cells[l_row + 8, 4, l_row + 8, 8].Merge();
                for (int col = 0; col <= 6; col++)
                {
                    if (col == 0)
                    {
                        exSheet.Cells[8 + l_row, 1].Value = dt.Rows[l_row - 1]["room_no"].ToString();
                    }
                    if (col == 1)
                    {
                        exSheet.Cells[8 + l_row, 2].Value = dt.Rows[l_row - 1]["guest_name"].ToString();
                    }
                    if (col == 3)
                    {
                        exSheet.Cells[8 + l_row, 4].Value = dt.Rows[l_row - 1]["description"].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

        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_room_no_guest_group = Request["p_room"];
        string l_report_type         = Request["p_report_type"];
        string l_dt_from             = Request["p_from_date"];
        string l_dt_to      = Request["p_to_date"];
        string TemplateFile = "rpt_60250020_inhouse_list_date.xls";
        string TempFile     = "../../../../system/temp/rpt_60250020_inhouse_list_date_" + 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 + "','" + l_dt_from + "','" + l_dt_to + "'";
        dt   = CtlLib.TableReadOpenCursor("ht_rpt_60250090_in_ListDate", 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]["para_date"].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_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();
    }
示例#11
0
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser(Session["APP_DBUSER"].ToString());


        string TemplateFile = "rpt_60300010_Reservation_daily_report.xls";
        string TempFile     = "../../../../system/temp/rpt_60300010_Reservation_daily_report_" + 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_dtfrm = Request["p_dtfrm"];


        string    para = "'" + p_dtfrm + "'";
        DataTable dt   = CtlLib.TableReadOpenCursor("ht_rpt_60300010_rm_rsv_state", para);

        // MASTER
        exSheet.Cells["c2"].Value  = dt.Rows[0]["p_date"].ToString();
        exSheet.Cells[2, 43].Value = dt.Rows[0]["print_time"].ToString();
        //exSheet.Cells[6, 1].Value ="Print time: "+ dt.Rows[0]["print_time"].ToString();
        exSheet.Cells[4, 13].Value = dt.Rows[0]["month_curr"].ToString();
        exSheet.Cells[4, 16].Value = dt.Rows[0]["month02"].ToString();
        exSheet.Cells[4, 19].Value = dt.Rows[0]["month03"].ToString();
        exSheet.Cells[4, 22].Value = dt.Rows[0]["month04"].ToString();
        exSheet.Cells[4, 25].Value = dt.Rows[0]["month05"].ToString();
        exSheet.Cells[4, 28].Value = dt.Rows[0]["month06"].ToString();
        exSheet.Cells[4, 31].Value = dt.Rows[0]["month07"].ToString();
        exSheet.Cells[4, 34].Value = dt.Rows[0]["month08"].ToString();
        exSheet.Cells[4, 37].Value = dt.Rows[0]["month09"].ToString();
        exSheet.Cells[4, 40].Value = dt.Rows[0]["month10"].ToString();
        exSheet.Cells[4, 43].Value = dt.Rows[0]["month11"].ToString();
        exSheet.Cells[4, 46].Value = dt.Rows[0]["month12"].ToString();

        // INSERT ROWS


        for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++)
        {
            exSheet.Range["A6"].Rows.EntireRow.Insert();//insert row new of sheet
        }
        int pos = 4, row_num = 0;

        for (int i = 1; i <= dt.Rows.Count; i++)
        {
            if ((dt.Rows[i - 1]["mkt_segment"].ToString() != "") && (dt.Rows[i - 1]["company_name"].ToString() != ""))
            {
                row_num = row_num + 1;
                exSheet.Cells[pos + i, 1].Value = row_num;
            }

            exSheet.Cells[pos + i, 2].Value = dt.Rows[i - 1]["mkt_segment"];
            exSheet.Cells[pos + i, 3].Value = dt.Rows[i - 1]["company_name"];
            //exSheet.Cells[pos + i, 4 ].Value = dt.Rows[i-1]["Allotment_day"];
            exSheet.Cells[pos + i, 4].Value  = dt.Rows[i - 1]["total_night"];
            exSheet.Cells[pos + i, 5].Value  = dt.Rows[i - 1]["weekday"];
            exSheet.Cells[pos + i, 6].Value  = dt.Rows[i - 1]["weekday_rate"];
            exSheet.Cells[pos + i, 7].Value  = dt.Rows[i - 1]["weekend"];
            exSheet.Cells[pos + i, 8].Value  = dt.Rows[i - 1]["weekend_rate"];
            exSheet.Cells[pos + i, 9].Value  = dt.Rows[i - 1]["total_rm_amt"];
            exSheet.Cells[pos + i, 10].Value = dt.Rows[i - 1]["total_rate"];

            exSheet.Cells[pos + i, 11].Value = dt.Rows[i - 1]["n1"];
            exSheet.Cells[pos + i, 12].Value = dt.Rows[i - 1]["rate1"];
            exSheet.Cells[pos + i, 13].Value = dt.Rows[i - 1]["d1"];

            exSheet.Cells[pos + i, 14].Value = dt.Rows[i - 1]["n2"];
            exSheet.Cells[pos + i, 15].Value = dt.Rows[i - 1]["rate2"];
            exSheet.Cells[pos + i, 16].Value = dt.Rows[i - 1]["d2"];

            exSheet.Cells[pos + i, 17].Value = dt.Rows[i - 1]["n3"];
            exSheet.Cells[pos + i, 18].Value = dt.Rows[i - 1]["rate3"];
            exSheet.Cells[pos + i, 19].Value = dt.Rows[i - 1]["d3"];

            exSheet.Cells[pos + i, 20].Value = dt.Rows[i - 1]["n4"];
            exSheet.Cells[pos + i, 21].Value = dt.Rows[i - 1]["rate4"];
            exSheet.Cells[pos + i, 22].Value = dt.Rows[i - 1]["d4"];

            exSheet.Cells[pos + i, 23].Value = dt.Rows[i - 1]["n5"];
            exSheet.Cells[pos + i, 24].Value = dt.Rows[i - 1]["rate5"];
            exSheet.Cells[pos + i, 25].Value = dt.Rows[i - 1]["d5"];

            exSheet.Cells[pos + i, 26].Value = dt.Rows[i - 1]["n6"];
            exSheet.Cells[pos + i, 27].Value = dt.Rows[i - 1]["rate6"];
            exSheet.Cells[pos + i, 28].Value = dt.Rows[i - 1]["d6"];

            exSheet.Cells[pos + i, 29].Value = dt.Rows[i - 1]["n7"];
            exSheet.Cells[pos + i, 30].Value = dt.Rows[i - 1]["rate7"];
            exSheet.Cells[pos + i, 31].Value = dt.Rows[i - 1]["d7"];

            exSheet.Cells[pos + i, 32].Value = dt.Rows[i - 1]["n8"];
            exSheet.Cells[pos + i, 33].Value = dt.Rows[i - 1]["rate8"];
            exSheet.Cells[pos + i, 34].Value = dt.Rows[i - 1]["d8"];

            exSheet.Cells[pos + i, 35].Value = dt.Rows[i - 1]["n9"];
            exSheet.Cells[pos + i, 36].Value = dt.Rows[i - 1]["rate9"];
            exSheet.Cells[pos + i, 37].Value = dt.Rows[i - 1]["d9"];

            exSheet.Cells[pos + i, 38].Value = dt.Rows[i - 1]["n10"];
            exSheet.Cells[pos + i, 39].Value = dt.Rows[i - 1]["rate10"];
            exSheet.Cells[pos + i, 40].Value = dt.Rows[i - 1]["d10"];

            exSheet.Cells[pos + i, 41].Value = dt.Rows[i - 1]["n11"];
            exSheet.Cells[pos + i, 42].Value = dt.Rows[i - 1]["rate11"];
            exSheet.Cells[pos + i, 43].Value = dt.Rows[i - 1]["d11"];

            exSheet.Cells[pos + i, 44].Value = dt.Rows[i - 1]["n12"];
            exSheet.Cells[pos + i, 45].Value = dt.Rows[i - 1]["rate12"];
            exSheet.Cells[pos + i, 46].Value = dt.Rows[i - 1]["d12"];

            exSheet.Cells[pos + i, 47].Value = dt.Rows[i - 1]["night_month_over"];
            exSheet.Cells[pos + i, 48].Value = dt.Rows[i - 1]["rate_over"];
            exSheet.Cells[pos + i, 49].Value = dt.Rows[i - 1]["amt_month_over"];
            if ((dt.Rows[i - 1]["mkt_segment"].ToString() != "") && (dt.Rows[i - 1]["company_name"].ToString() == ""))
            {
                string l_Color          = "0XD8D8D8";
                Color  colorValueFrmHex = ColorTranslator.FromHtml(l_Color);               //var color
                exSheet.Cells[pos + i, 3, pos + i, 49].Interior.Color = colorValueFrmHex;  //set color
                exSheet.Cells[pos + i, 3].Value = "Sub Total:";
            }

            if ((dt.Rows[i - 1]["mkt_segment"].ToString() == "") && (dt.Rows[i - 1]["company_name"].ToString() == ""))
            {
                string l_Color          = "0X18D8D8";
                Color  colorValueFrmHex = ColorTranslator.FromHtml(l_Color);               //var color
                exSheet.Cells[pos + i, 3, pos + i, 49].Interior.Color = colorValueFrmHex;  //set color
                exSheet.Cells[pos + i, 3].Value = "Total:";
            }

            if (i < dt.Rows.Count)
            {
                if (dt.Rows[i - 1]["mkt_segment"].ToString() == dt.Rows[i]["mkt_segment"].ToString())
                {
                    exSheet.Range[i + 4, 2, i + 5, 2].Merge();
                }
            }
        }
        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("sale");
        string l_pk = Request["master_pk"];

        string TemplateFile = "rpt_dscd00076.xls";
        string TempFile     = "../../../../system/temp/rpt_dscd00076_" + 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() + "'");

        dtInfo.Rows.Add();
        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, 143, 84));
        }

        exSheet.Cells["P3"].Value = "Print date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm");

        exSheet.Cells["E5"].Value = dtInfo.Rows[0]["partner_name"].ToString();
        exSheet.Cells["M5"].Value = dtMaster.Rows[0]["slip_no"].ToString();

        exSheet.Cells["E6"].Value = dtInfo.Rows[0]["addr1"].ToString();
        exSheet.Cells["M6"].Value = dtMaster.Rows[0]["out_date"].ToString();

        exSheet.Cells["E7"].Value = dtInfo.Rows[0]["tax_code"].ToString();
        exSheet.Cells["M7"].Value = dtMaster.Rows[0]["description"].ToString();

        exSheet.Cells["E8"].Value = dtInfo.Rows[0]["phone_no"].ToString();
        //---------
        exSheet.Cells["E10"].Value = dtMaster.Rows[0]["bill_partner_name"].ToString();
        exSheet.Cells["M10"].Value = dtMaster.Rows[0]["deli_partner_name"].ToString();

        exSheet.Cells["E11"].Value = dtMaster.Rows[0]["short_nm"].ToString();
        exSheet.Cells["M11"].Value = dtMaster.Rows[0]["deli_addr1"].ToString();

        exSheet.Cells["E12"].Value = dtMaster.Rows[0]["bill_addr1"].ToString();
        exSheet.Cells["E13"].Value = dtMaster.Rows[0]["tax_code"].ToString();
        exSheet.Cells["G13"].Value = dtMaster.Rows[0]["phone_no"].ToString();

        exSheet.Cells["E14"].Value = dtMaster.Rows[0]["po_date"].ToString();

        exSheet.Cells["E15"].Value = dtMaster.Rows[0]["po_no"].ToString();
        //----------------------
        ////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
        int pos = 21;

        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_bc"];
            exSheet.Cells[pos + l_row, 3].Value = dtDetail.Rows[l_row]["item_code"];
            exSheet.Cells[pos + l_row, 4].Value = dtDetail.Rows[l_row]["item_name"];
            exSheet.Cells[pos + l_row, 4, pos + l_row, 6].Merge();
            exSheet.Cells[pos + l_row, 7].Value  = dtDetail.Rows[l_row]["out_uom"];
            exSheet.Cells[pos + l_row, 8].Value  = dtDetail.Rows[l_row]["out_qty"];
            exSheet.Cells[pos + l_row, 9].Value  = dtDetail.Rows[l_row]["unit_price"];
            exSheet.Cells[pos + l_row, 10].Value = dtDetail.Rows[l_row]["discount_rate"];
            exSheet.Cells[pos + l_row, 11].Value = dtDetail.Rows[l_row]["discount_amount"];
            exSheet.Cells[pos + l_row, 11, pos + l_row, 13].Merge();
            exSheet.Cells[pos + l_row, 14].Value = dtDetail.Rows[l_row]["total_amount"];
            exSheet.Cells[pos + l_row, 15].Value = dtDetail.Rows[l_row]["ref_no"];
            exSheet.Cells[pos + l_row, 16].Value = dtDetail.Rows[l_row]["description"];
        }
        exSheet.Cells[dtDetail.Rows.Count + 21, 8].Value  = "=SUM(H21:H" + (dtDetail.Rows.Count + 20) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 21, 11].Value = "=SUM(K21:K" + (dtDetail.Rows.Count + 20) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 21, 14].Value = "=SUM(N21:N" + (dtDetail.Rows.Count + 20) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 22, 14].Value = "=SUM(N21:N" + (dtDetail.Rows.Count + 20) + ")*10%";
        exSheet.Cells[dtDetail.Rows.Count + 23, 14].Value = "=SUM(N21:N" + (dtDetail.Rows.Count + 20) + ")+ (SUM(N21:N" + (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);

        //write out to client broswer
        System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
        //System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
        Response.Clear();
        Response.Charset         = "UTF-8";
        Response.ContentEncoding = System.Text.Encoding.UTF8;
        //Add header, give a default file name for "File Download/Store as"
        Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
        //Add header, set file size to enable browser display download progress
        Response.AddHeader("Content-Length", file.Length.ToString());
        //Set the return string is unavailable reading for client, and must be downloaded
        Response.ContentType = "application/ms-exSheet";
        //Response.ContentType = "application/pdf";
        //Send file string to client
        //Response.WriteFile(pdfFilePath);
        Response.WriteFile(TempFile);
        //Stop execute
        Response.End();
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser(Session["APP_DBUSER"].ToString());
        //CtlLib.SetUser("SALE");

        string TemplateFile = "rpt_dsbs00200_ST01.xls";
        string TempFile     = "../../../../system/temp/rpt_dsbs00200_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_company_pk = Request.QueryString["p_company_pk"];
        string p_date_type  = Request.QueryString["p_date_type"];
        string p_from_dt    = Request.QueryString["p_from_dt"];
        string p_to_dt      = Request.QueryString["p_to_dt"];
        string p_po_item    = Request.QueryString["p_po_item"];
        string p_order_type = Request.QueryString["p_order_type"];
        string p_bill_to    = Request.QueryString["p_bill_to"];

        string    para     = "'" + p_from_dt + "','" + p_to_dt + "'";
        DataTable dtHeader = CtlLib.TableReadOpenCursor("st_lg_rpt_dsbs00063_1", para);

        para = "'" + p_company_pk + "','" + p_date_type + "','" + p_from_dt + "','" + p_to_dt + "','" + p_po_item + "','" + p_order_type + "','" + p_bill_to + "'";
        DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_dsbs00063", para);

        // MASTER

        //--set-----DataBind--in---Master---
        if (!string.IsNullOrEmpty(dtHeader.Rows[0]["pic"].ToString()))
        {
            exSheet.Cells[1, 1].Select();
            byte[] MyData = new byte[0];
            MyData = (byte[])dtHeader.Rows[0]["pic"];
            MemoryStream stream = new MemoryStream(MyData);

            Bitmap b = new Bitmap(stream);
            exSheet.Shapes.AddPicture(ResizeImage(b, 90, 59));
        }

        exSheet.Cells["C1"].Value = dtHeader.Rows[0]["partner_name"];
        exSheet.Cells["C2"].Value = dtHeader.Rows[0]["addr1"];
        exSheet.Cells["C3"].Value = "Tax Code: " + dtHeader.Rows[0]["tax_code"];

        exSheet.Cells["Q1"].Value = "Tel: " + dtHeader.Rows[0]["phone_no"];
        exSheet.Cells["Q2"].Value = "Fax No: " + dtHeader.Rows[0]["fax_no"];
        exSheet.Cells["Q3"].Value = "Print Date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm");

        exSheet.Cells["Q7"].Value = dtHeader.Rows[0]["t_date"];
        // END MASTER

        // INSERT ROWS

        for (int l_addrow = 0; l_addrow < dtDetail.Rows.Count - 1; l_addrow++)
        {
            exSheet.Range["A11"].Rows.EntireRow.Insert();//insert row new of sheet
        }

        // SET DATA
        int pos = 10;
        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  = i + 1;
            exSheet.Cells[pos + i, 2].Value  = dtDetail.Rows[i]["bill_to_name"];
            exSheet.Cells[pos + i, 3].Value  = dtDetail.Rows[i]["grp_nm"];
            exSheet.Cells[pos + i, 4].Value  = dtDetail.Rows[i]["company_name"];
            exSheet.Cells[pos + i, 5].Value  = dtDetail.Rows[i]["order_year"];
            exSheet.Cells[pos + i, 6].Value  = dtDetail.Rows[i]["po_no"];
            exSheet.Cells[pos + i, 7].Value  = dtDetail.Rows[i]["ITEM_ETD"];
            exSheet.Cells[pos + i, 8].Value  = dtDetail.Rows[i]["item_code"];
            exSheet.Cells[pos + i, 9].Value  = dtDetail.Rows[i]["item_name"];
            exSheet.Cells[pos + i, 10].Value = dtDetail.Rows[i]["ord_qty"];
            exSheet.Cells[pos + i, 11].Value = dtDetail.Rows[i]["unit_price"];
            exSheet.Cells[pos + i, 12].Value = dtDetail.Rows[i]["item_amount"];
            exSheet.Cells[pos + i, 13].Value = dtDetail.Rows[i]["act_etd"];
            exSheet.Cells[pos + i, 14].Value = dtDetail.Rows[i]["out_qty"];
            exSheet.Cells[pos + i, 15].Value = dtDetail.Rows[i]["bal_qty"];
            exSheet.Cells[pos + i, 16].Value = dtDetail.Rows[i]["out_amount"];
            exSheet.Cells[pos + i, 17].Value = dtDetail.Rows[i]["description"];
            exSheet.Cells[pos + i, 18].Value = dtDetail.Rows[i]["dest_port"];
            exSheet.Cells[pos + i, 19].Value = dtDetail.Rows[i]["att01"];
            exSheet.Cells[pos + i, 20].Value = dtDetail.Rows[i]["ex_nation"];
            //-------------
        }

        int t = pos + i - 1;

        exSheet.Cells[pos + i, 10].Value = "=sum(J10:J" + t + ")";
        exSheet.Cells[pos + i, 12].Value = "=sum(L10:L" + t + ")";
        exSheet.Cells[pos + i, 14].Value = "=sum(N10:N" + t + ")";
        exSheet.Cells[pos + i, 15].Value = "=sum(O10:O" + t + ")";
        exSheet.Cells[pos + i, 16].Value = "=sum(P10:P" + 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 TemplateFile = "rpt_60250300_vat_invoice.xls";
        string TempFile     = "../../../../system/temp/rpt_60250300_vat_invoice_" + Session["User_ID"].ToString() + ".xls";

        TemplateFile = Server.MapPath(TemplateFile);
        TempFile     = Server.MapPath(TempFile);

        //Create a new workbook
        IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);

        //Add worksheet
        IWorksheet exSheet = exBook.Worksheets[1];

        IRange range;

        //bind data to excel file
        string p_tac_crca_pk = Request.QueryString["p_tac_crca_pk"];
        string para          = "'" + p_tac_crca_pk + "'";



        DataTable dtMaster = CtlLib.TableReadOpenCursor("ac_rpt_60250300_vatinvoice_mst", para);
        DataTable dtDetail = CtlLib.TableReadOpenCursor("ac_rpt_60250300_vatinvoice_dtl", para);


        //set master data
        //day
        exSheet.Cells["D13"].Value = dtMaster.Rows[0]["dd"].ToString();
        //month
        exSheet.Cells["E13"].Value = dtMaster.Rows[0]["mm"].ToString();
        //year
        exSheet.Cells["G13"].Value = dtMaster.Rows[0]["yyyy"].ToString();
        //guest name
        exSheet.Cells["D14"].Value = dtMaster.Rows[0]["recv_person"].ToString();
        //customer name
        exSheet.Cells["D15"].Value = dtMaster.Rows[0]["customer_name"].ToString();
        //tax code
        exSheet.Cells["D16"].Value = dtMaster.Rows[0]["taxcode"].ToString();
        //customer address
        exSheet.Cells["C17"].Value = dtMaster.Rows[0]["addr1"].ToString();
        //pay_method
        exSheet.Cells["D18"].Value = dtMaster.Rows[0]["pay_method"].ToString();

        //sub net total
        exSheet.Cells["H33"].Value = double.Parse(dtMaster.Rows[0]["net_amt"].ToString());
        //service charge amt
        exSheet.Cells["H34"].Value = double.Parse(dtMaster.Rows[0]["svc_amt"].ToString());
        //vat amt
        exSheet.Cells["H35"].Value = double.Parse(dtMaster.Rows[0]["vat_amt"].ToString());
        //total amt
        exSheet.Cells["H36"].Value = double.Parse(dtMaster.Rows[0]["total_amt"].ToString());

        //VAT RATE
        exSheet.Cells["HC5"].Value = 10;
        //number to VN words
        exSheet.Cells["A39"].Value = CommondLib.Num2VNText(dtMaster.Rows[0]["total_amt"].ToString(), "VND");

        //set detail data
        int l_pos = 22;
        int i     = 0;

        for (i = 0; i < dtDetail.Rows.Count; i++)
        {
            //exSheet.Cells[l_pos+i,1].Value =i+1;
            exSheet.Cells[l_pos + i, 2].Value = dtDetail.Rows[i]["item_name"].ToString();          //item name

            //exSheet.Cells[l_pos+i,4].Value = dtDetail.Rows[i]["item_uom"].ToString(); //unit
            //exSheet.Cells[l_pos+i,5].Value = dtDetail.Rows[i]["qty"].ToString(); //qty
            //exSheet.Cells[l_pos+i,6].Value = dtDetail.Rows[i]["u_price"].ToString(); //price

            exSheet.Cells[l_pos + i, 8].Value = double.Parse(dtDetail.Rows[i]["amt"].ToString());          //net amt
        }

        if (File.Exists(TempFile))
        {
            File.Delete(TempFile);
        }


        exBook.SaveAs(TempFile);
        //write out to client broswer
        System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
        //System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
        Response.Clear();
        Response.Charset         = "UTF-8";
        Response.ContentEncoding = System.Text.Encoding.UTF8;
        //Add header, give a default file name for "File Download/Store as"
        Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
        //Add header, set file size to enable browser display download progress
        Response.AddHeader("Content-Length", file.Length.ToString());
        //Set the return string is unavailable reading for client, and must be downloaded
        Response.ContentType = "application/ms-exSheet";
        //Response.ContentType = "application/pdf";
        //Send file string to client
        //Response.WriteFile(pdfFilePath);
        Response.WriteFile(TempFile);
        //Stop execute
        Response.End();
    }
示例#15
0
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser("SALE");
        //string l_pk = Request["master_pk"];

        string TemplateFile = "rpt_bisc00070_ST01.xls";
        string TempFile     = "../../../../system/temp/rpt_bisc00070_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_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_tin_warehouse_pk = Request.QueryString["p_tin_warehouse_pk"];
        string p_use_yn           = Request.QueryString["p_use_yn"];
        string p_item_group_pk    = Request.QueryString["p_item_group_pk"];
        string p_item             = Request.QueryString["p_item"];
        string p_in_out_yn        = Request.QueryString["p_in_out_yn"];
        string p_lang             = Request.QueryString["p_lang"];
        string p_wh_name          = Request.QueryString["p_wh_name"];

        string para = "'" + p_from_date + "','" + p_to_date + "','" + p_wh_type + "','" + p_tin_warehouse_pk + "','" + p_use_yn + "','" + p_item_group_pk + "','" + p_item + "','" + p_in_out_yn + "','" + p_lang + "'";

        DataTable dtInfo   = CtlLib.TableReadOpenCursor("st_lg_rpt_BISC00070_ST01_2", "'" + Session["User_ID"].ToString() + "'");
        DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_BISC00070_ST01_1", para);

        if (dtDetail.Rows.Count == 0)
        {
            Response.Write("There is no data");
            Response.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();

        exSheet.Cells["K1"].Value = "Tell: " + dtInfo.Rows[0]["phone_no"].ToString();

        exSheet.Cells["K2"].Value = "Fax: " + dtInfo.Rows[0]["fax_no"].ToString();

        exSheet.Cells["K3"].Value = "Print date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm");

        exSheet.Cells["I5"].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["C5"].Value = p_wh_name;
        //---------

        ////end-set-----DataBind--in---Master---

        for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++)
        {
            exSheet.Range["A9"].Rows.EntireRow.Insert();//insert row new of sheet
        }
        //set data to detail.
        // loop detail not percent
        int l_pos = 8;

        for (int i = 0; i < dtDetail.Rows.Count; i++)
        {
            exSheet.Cells[l_pos + i, 1].Value = i + 1;
            exSheet.Cells[l_pos + i, 2].Value = dtDetail.Rows[i]["wh_name"];
            exSheet.Cells[l_pos + i, 3].Value = dtDetail.Rows[i]["item_code"];
            exSheet.Cells[l_pos + i, 4].Value = dtDetail.Rows[i]["item_name"];
            exSheet.Cells[l_pos + i, 5].Value = dtDetail.Rows[i]["uom"];
            exSheet.Cells[l_pos + i, 6].Value = dtDetail.Rows[i]["lot_no"];

            exSheet.Cells[l_pos + i, 7].Value = dtDetail.Rows[i]["begin_qty"];

            exSheet.Cells[l_pos + i, 8].Value  = dtDetail.Rows[i]["open_qty"];
            exSheet.Cells[l_pos + i, 9].Value  = dtDetail.Rows[i]["income_qty"];
            exSheet.Cells[l_pos + i, 10].Value = dtDetail.Rows[i]["prod_in_qty"];
            exSheet.Cells[l_pos + i, 11].Value = dtDetail.Rows[i]["ass_in_qty"];
            exSheet.Cells[l_pos + i, 12].Value = dtDetail.Rows[i]["trans_in_qty"];
            exSheet.Cells[l_pos + i, 13].Value = dtDetail.Rows[i]["ex_in_qty"];
            exSheet.Cells[l_pos + i, 14].Value = dtDetail.Rows[i]["in_return_qty"];
            exSheet.Cells[l_pos + i, 15].Value = dtDetail.Rows[i]["others_in_qty"];

            exSheet.Cells[l_pos + i, 16].Value = dtDetail.Rows[i]["total_in"];

            exSheet.Cells[l_pos + i, 17].Value = dtDetail.Rows[i]["outgo_qty"];
            exSheet.Cells[l_pos + i, 18].Value = dtDetail.Rows[i]["deli_qty"];
            exSheet.Cells[l_pos + i, 19].Value = dtDetail.Rows[i]["prod_out_qty"];
            exSheet.Cells[l_pos + i, 20].Value = dtDetail.Rows[i]["ass_out_qty"];
            exSheet.Cells[l_pos + i, 21].Value = dtDetail.Rows[i]["trans_out_qty"];
            exSheet.Cells[l_pos + i, 22].Value = dtDetail.Rows[i]["ex_out_qty"];
            exSheet.Cells[l_pos + i, 23].Value = dtDetail.Rows[i]["out_return_qty"];
            exSheet.Cells[l_pos + i, 24].Value = dtDetail.Rows[i]["discard_qty"];
            exSheet.Cells[l_pos + i, 25].Value = dtDetail.Rows[i]["others_out_qty"];
            exSheet.Cells[l_pos + i, 26].Value = dtDetail.Rows[i]["adj_qty"];

            exSheet.Cells[l_pos + i, 27].Value = dtDetail.Rows[i]["total_out"];

            exSheet.Cells[l_pos + i, 28].Value = dtDetail.Rows[i]["end_qty"];
        }

        exSheet.Cells[dtDetail.Rows.Count + l_pos, 7].Value  = "=SUM(G8:G" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
        exSheet.Cells[dtDetail.Rows.Count + l_pos, 8].Value  = "=SUM(H8:H" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
        exSheet.Cells[dtDetail.Rows.Count + l_pos, 9].Value  = "=SUM(I8:I" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
        exSheet.Cells[dtDetail.Rows.Count + l_pos, 10].Value = "=SUM(J8:J" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
        exSheet.Cells[dtDetail.Rows.Count + l_pos, 11].Value = "=SUM(K8:K" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
        exSheet.Cells[dtDetail.Rows.Count + l_pos, 12].Value = "=SUM(L8:L" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
        exSheet.Cells[dtDetail.Rows.Count + l_pos, 13].Value = "=SUM(M8:M" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
        exSheet.Cells[dtDetail.Rows.Count + l_pos, 14].Value = "=SUM(N8:N" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
        exSheet.Cells[dtDetail.Rows.Count + l_pos, 15].Value = "=SUM(O8:O" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
        exSheet.Cells[dtDetail.Rows.Count + l_pos, 16].Value = "=SUM(P8:P" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
        exSheet.Cells[dtDetail.Rows.Count + l_pos, 17].Value = "=SUM(Q8:Q" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
        exSheet.Cells[dtDetail.Rows.Count + l_pos, 18].Value = "=SUM(R8:R" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
        exSheet.Cells[dtDetail.Rows.Count + l_pos, 19].Value = "=SUM(S8:S" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
        exSheet.Cells[dtDetail.Rows.Count + l_pos, 20].Value = "=SUM(T8:T" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
        exSheet.Cells[dtDetail.Rows.Count + l_pos, 21].Value = "=SUM(U8:U" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
        exSheet.Cells[dtDetail.Rows.Count + l_pos, 22].Value = "=SUM(V8:V" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
        exSheet.Cells[dtDetail.Rows.Count + l_pos, 23].Value = "=SUM(W8:W" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
        exSheet.Cells[dtDetail.Rows.Count + l_pos, 24].Value = "=SUM(X8:X" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
        exSheet.Cells[dtDetail.Rows.Count + l_pos, 25].Value = "=SUM(Y8:Y" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
        exSheet.Cells[dtDetail.Rows.Count + l_pos, 26].Value = "=SUM(Z8:Z" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
        exSheet.Cells[dtDetail.Rows.Count + l_pos, 27].Value = "=SUM(AA8:AA" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
        exSheet.Cells[dtDetail.Rows.Count + l_pos, 28].Value = "=SUM(AB8:AB" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
        // 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_inio00100_0.xls";
        string TempFile     = "../../../../system/temp/rpt_inio00100_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;

        //bind data to excel file

        string p_master_pk;

        p_master_pk = Request.QueryString["master_pk"];

        string para = "'" + p_master_pk + "'";

        DataTable dtInfo   = CtlLib.TableReadOpenCursor("ST_LG_RPT_INIO00100_0_0", "'" + Session["User_ID"].ToString() + "'");
        DataTable dtMaster = CtlLib.TableReadOpenCursor("ST_LG_RPT_INIO00100_0_1", para);
        DataTable dtDetail = CtlLib.TableReadOpenCursor("ST_LG_RPT_INIO00100_0_2", para);

        // MASTER

        exSheet.Cells[1, 3].Value = dtInfo.Rows[0]["partner_name"].ToString();

        exSheet.Cells[1, 8].Value = "Tel: " + dtInfo.Rows[0]["phone_no"].ToString();

        exSheet.Cells[2, 3].Value = "Addr: " + dtInfo.Rows[0]["addr1"].ToString();

        exSheet.Cells[2, 8].Value = "Fax: " + dtInfo.Rows[0]["fax_no"].ToString();

        exSheet.Cells[3, 3].Value = "MST: " + dtInfo.Rows[0]["tax_code"].ToString();

        exSheet.Cells[3, 8].Value = "Print Date: " + DateTime.Now.ToString("dd/MM/yyyy");  //dtInfo.Rows[0]["issue_dt"].ToString();

        //--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));
        }

        if (dtMaster.Rows.Count > 0)
        {
            exSheet.Cells["H4"].Value = dtMaster.Rows[0]["slip_no"];
            exSheet.Cells["H5"].Value = dtMaster.Rows[0]["req_date"];

            exSheet.Cells["C6"].Value = dtMaster.Rows[0]["wh_name"];
            exSheet.Cells["C7"].Value = dtMaster.Rows[0]["requester_name"];


            exSheet.Cells["G7"].Value = dtMaster.Rows[0]["ref_no"];

            exSheet.Cells["C8"].Value = dtMaster.Rows[0]["description"];
        }

        // END MASTER

        // INSERT ROWS

        for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++)
        {
            exSheet.Range["A13"].Rows.EntireRow.Insert();//insert row new of sheet
        }

        // SET DATA
        int    pos       = 12;
        int    i         = 0;
        double total_sub = 0;

        for (i = 0; i < dtDetail.Rows.Count; i++)
        {
            exSheet.Cells[pos + i, 1].Value = dtDetail.Rows[i]["seq"];

            exSheet.Cells[pos + i, 2].Value = dtDetail.Rows[i]["item_code"];
            exSheet.Cells[pos + i, 3].Value = dtDetail.Rows[i]["item_name"];
            exSheet.Cells[pos + i, 6].Value = dtDetail.Rows[i]["req_uom"];

            exSheet.Cells[pos + i, 7].Value = dtDetail.Rows[i]["req_qty"];

            exSheet.Cells[pos + i, 9].Value = dtDetail.Rows[i]["description"];

            total_sub += Convert.ToDouble(dtDetail.Rows[i]["req_qty"]);
        }
        // total
        exSheet.Cells["G" + (pos + i + 0)].Value = total_sub;

        //---------
        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());
        //CtlLib.SetUser("SALE");

        string TemplateFile = "rpt_bisc00171_ST02.xls";
        string TempFile     = "../../../../system/temp/rpt_bisc00171_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 p_lang          = Request.QueryString["p_lang"];
        string p_wh_name       = Request.QueryString["p_wh_name"];
        string p_wh_pk         = Request.QueryString["p_wh_pk"];
        string p_item_group_pk = Request.QueryString["p_item_group_pk"];
        string p_item          = Request.QueryString["p_item"];
        string p_date          = Request.QueryString["p_date"];

        DataTable dtInfo = CtlLib.TableReadOpenCursor("st_lg_rpt_BISC00171_ST02", "'" + Session["User_ID"].ToString() + "'");

        string    para     = "'" + p_date + "'";
        DataTable dtMaster = CtlLib.TableReadOpenCursor("st_lg_rpt_BISC00171_ST02_1", para);

        para = "'" + p_lang + "','" + p_wh_pk + "','" + p_item_group_pk + "','" + p_item + "','" + p_date + "'";
        DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_BISC00171_ST02_2", 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, 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["G5"].Value = System.DateTime.ParseExact(p_date, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy");

        exSheet.Cells["C5"].Value = p_wh_name;
        //---------

        ////end-set-----DataBind--in---Master---

        for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count * 3 - 2; l_addrow++)
        {
            exSheet.Range["A11"].Rows.EntireRow.Insert();//insert row new of sheet
        }

        if (dtMaster.Rows.Count > 0)
        {
            exSheet.Cells["H6"].Value  = dtMaster.Rows[0]["date01"];
            exSheet.Cells["I6"].Value  = dtMaster.Rows[0]["date02"];
            exSheet.Cells["J6"].Value  = dtMaster.Rows[0]["date03"];
            exSheet.Cells["K6"].Value  = dtMaster.Rows[0]["date04"];
            exSheet.Cells["L6"].Value  = dtMaster.Rows[0]["date05"];
            exSheet.Cells["M6"].Value  = dtMaster.Rows[0]["date06"];
            exSheet.Cells["N6"].Value  = dtMaster.Rows[0]["date07"];
            exSheet.Cells["O6"].Value  = dtMaster.Rows[0]["date08"];
            exSheet.Cells["P6"].Value  = dtMaster.Rows[0]["date09"];
            exSheet.Cells["Q6"].Value  = dtMaster.Rows[0]["date10"];
            exSheet.Cells["R6"].Value  = dtMaster.Rows[0]["date11"];
            exSheet.Cells["S6"].Value  = dtMaster.Rows[0]["date12"];
            exSheet.Cells["T6"].Value  = dtMaster.Rows[0]["date13"];
            exSheet.Cells["U6"].Value  = dtMaster.Rows[0]["date14"];
            exSheet.Cells["V6"].Value  = dtMaster.Rows[0]["date15"];
            exSheet.Cells["W6"].Value  = dtMaster.Rows[0]["date16"];
            exSheet.Cells["X6"].Value  = dtMaster.Rows[0]["date17"];
            exSheet.Cells["Y6"].Value  = dtMaster.Rows[0]["date18"];
            exSheet.Cells["Z6"].Value  = dtMaster.Rows[0]["date19"];
            exSheet.Cells["AA6"].Value = dtMaster.Rows[0]["date20"];
            exSheet.Cells["AB6"].Value = dtMaster.Rows[0]["date21"];
            exSheet.Cells["AC6"].Value = dtMaster.Rows[0]["date22"];
            exSheet.Cells["AD6"].Value = dtMaster.Rows[0]["date23"];
            exSheet.Cells["AE6"].Value = dtMaster.Rows[0]["date24"];
            exSheet.Cells["AF6"].Value = dtMaster.Rows[0]["date25"];
            exSheet.Cells["AG6"].Value = dtMaster.Rows[0]["date26"];
            exSheet.Cells["AH6"].Value = dtMaster.Rows[0]["date27"];
            exSheet.Cells["AI6"].Value = dtMaster.Rows[0]["date28"];
            exSheet.Cells["AJ6"].Value = dtMaster.Rows[0]["date29"];
            exSheet.Cells["AK6"].Value = dtMaster.Rows[0]["date30"];
            exSheet.Cells["AL6"].Value = dtMaster.Rows[0]["date31"];

            exSheet.Cells["H7"].Value  = dtMaster.Rows[1]["date01"];
            exSheet.Cells["I7"].Value  = dtMaster.Rows[1]["date02"];
            exSheet.Cells["J7"].Value  = dtMaster.Rows[1]["date03"];
            exSheet.Cells["K7"].Value  = dtMaster.Rows[1]["date04"];
            exSheet.Cells["L7"].Value  = dtMaster.Rows[1]["date05"];
            exSheet.Cells["M7"].Value  = dtMaster.Rows[1]["date06"];
            exSheet.Cells["N7"].Value  = dtMaster.Rows[1]["date07"];
            exSheet.Cells["O7"].Value  = dtMaster.Rows[1]["date08"];
            exSheet.Cells["P7"].Value  = dtMaster.Rows[1]["date09"];
            exSheet.Cells["Q7"].Value  = dtMaster.Rows[1]["date10"];
            exSheet.Cells["R7"].Value  = dtMaster.Rows[1]["date11"];
            exSheet.Cells["S7"].Value  = dtMaster.Rows[1]["date12"];
            exSheet.Cells["T7"].Value  = dtMaster.Rows[1]["date13"];
            exSheet.Cells["U7"].Value  = dtMaster.Rows[1]["date14"];
            exSheet.Cells["V7"].Value  = dtMaster.Rows[1]["date15"];
            exSheet.Cells["W7"].Value  = dtMaster.Rows[1]["date16"];
            exSheet.Cells["X7"].Value  = dtMaster.Rows[1]["date17"];
            exSheet.Cells["Y7"].Value  = dtMaster.Rows[1]["date18"];
            exSheet.Cells["Z7"].Value  = dtMaster.Rows[1]["date19"];
            exSheet.Cells["AA7"].Value = dtMaster.Rows[1]["date20"];
            exSheet.Cells["AB7"].Value = dtMaster.Rows[1]["date21"];
            exSheet.Cells["AC7"].Value = dtMaster.Rows[1]["date22"];
            exSheet.Cells["AD7"].Value = dtMaster.Rows[1]["date23"];
            exSheet.Cells["AE7"].Value = dtMaster.Rows[1]["date24"];
            exSheet.Cells["AF7"].Value = dtMaster.Rows[1]["date25"];
            exSheet.Cells["AG7"].Value = dtMaster.Rows[1]["date26"];
            exSheet.Cells["AH7"].Value = dtMaster.Rows[1]["date27"];
            exSheet.Cells["AI7"].Value = dtMaster.Rows[1]["date28"];
            exSheet.Cells["AJ7"].Value = dtMaster.Rows[1]["date29"];
            exSheet.Cells["AK7"].Value = dtMaster.Rows[1]["date30"];
            exSheet.Cells["AL7"].Value = dtMaster.Rows[1]["date31"];
        }

        //--------------------------
        double p_begin = 0, p_total = 0, p_01 = 0, p_02 = 0, p_03 = 0, p_04 = 0, p_05 = 0, p_06 = 0, p_07 = 0, p_08 = 0, p_09 = 0, p_10 = 0,
               p_11 = 0, p_12 = 0, p_13 = 0, p_14 = 0, p_15 = 0, p_16 = 0, p_17 = 0, p_18 = 0, p_19 = 0, p_20 = 0,
               p_21 = 0, p_22 = 0, p_23 = 0, p_24 = 0, p_25 = 0, p_26 = 0, p_27 = 0, p_28 = 0, p_29 = 0, p_30 = 0, p_31 = 0;

        int l_pos = 8;

        for (int i = 0; i < dtDetail.Rows.Count; i++)
        {
            if (i > 0)
            {
                exSheet.Range[8, 1, 10, 39].Copy(exSheet.Range[l_pos, 1, l_pos + 2, 39], XlPasteType.xlPasteAll);
            }

            exSheet.Cells[l_pos, 1].Value = i + 1;
            exSheet.Cells[l_pos, 2].Value = dtDetail.Rows[i]["item_code"];
            exSheet.Cells[l_pos, 3].Value = dtDetail.Rows[i]["item_name"];

            exSheet.Cells[l_pos, 4].Value = dtDetail.Rows[i]["uom"];

            exSheet.Cells[l_pos, 6].Value = dtDetail.Rows[i]["begin_qty"];

            exSheet.Cells[l_pos + 0, 7].Value = dtDetail.Rows[i]["total_in"];
            exSheet.Cells[l_pos + 1, 7].Value = dtDetail.Rows[i]["total_out"];
            exSheet.Cells[l_pos + 2, 7].Value = dtDetail.Rows[i]["end_qty"];

            exSheet.Range[l_pos, 1, l_pos + 2, 1].Merge();
            exSheet.Range[l_pos, 2, l_pos + 2, 2].Merge();
            exSheet.Range[l_pos, 3, l_pos + 2, 3].Merge();
            exSheet.Range[l_pos, 4, l_pos + 2, 4].Merge();
            exSheet.Range[l_pos, 6, l_pos + 2, 6].Merge();


            exSheet.Cells[l_pos, 8].Value     = dtDetail.Rows[i]["in_qty_01"];
            exSheet.Cells[l_pos + 1, 8].Value = dtDetail.Rows[i]["out_qty_01"];
            exSheet.Cells[l_pos + 2, 8].Value = "=F" + (l_pos) + "+H" + (l_pos) + "-H" + (l_pos + 1);

            exSheet.Cells[l_pos, 9].Value     = dtDetail.Rows[i]["in_qty_02"];
            exSheet.Cells[l_pos + 1, 9].Value = dtDetail.Rows[i]["out_qty_02"];
            exSheet.Cells[l_pos + 2, 9].Value = "=H" + (l_pos + 2) + "+I" + (l_pos) + "-I" + (l_pos + 1);

            exSheet.Cells[l_pos, 10].Value     = dtDetail.Rows[i]["in_qty_03"];
            exSheet.Cells[l_pos + 1, 10].Value = dtDetail.Rows[i]["out_qty_03"];
            exSheet.Cells[l_pos + 2, 10].Value = "=I" + (l_pos + 2) + "+J" + (l_pos) + "-J" + (l_pos + 1);

            exSheet.Cells[l_pos, 11].Value     = dtDetail.Rows[i]["in_qty_04"];
            exSheet.Cells[l_pos + 1, 11].Value = dtDetail.Rows[i]["out_qty_04"];
            exSheet.Cells[l_pos + 2, 11].Value = "=J" + (l_pos + 2) + "+K" + (l_pos) + "-K" + (l_pos + 1);

            exSheet.Cells[l_pos, 12].Value     = dtDetail.Rows[i]["in_qty_05"];
            exSheet.Cells[l_pos + 1, 12].Value = dtDetail.Rows[i]["out_qty_05"];
            exSheet.Cells[l_pos + 2, 12].Value = "=K" + (l_pos + 2) + "+L" + (l_pos) + "-L" + (l_pos + 1);

            exSheet.Cells[l_pos, 13].Value     = dtDetail.Rows[i]["in_qty_06"];
            exSheet.Cells[l_pos + 1, 13].Value = dtDetail.Rows[i]["out_qty_06"];
            exSheet.Cells[l_pos + 2, 13].Value = "=L" + (l_pos + 2) + "+M" + (l_pos) + "-M" + (l_pos + 1);

            exSheet.Cells[l_pos, 14].Value     = dtDetail.Rows[i]["in_qty_07"];
            exSheet.Cells[l_pos + 1, 14].Value = dtDetail.Rows[i]["out_qty_07"];
            exSheet.Cells[l_pos + 2, 14].Value = "=M" + (l_pos + 2) + "+N" + (l_pos) + "-N" + (l_pos + 1);

            exSheet.Cells[l_pos, 15].Value     = dtDetail.Rows[i]["in_qty_08"];
            exSheet.Cells[l_pos + 1, 15].Value = dtDetail.Rows[i]["out_qty_08"];
            exSheet.Cells[l_pos + 2, 15].Value = "=N" + (l_pos + 2) + "+O" + (l_pos) + "-O" + (l_pos + 1);

            exSheet.Cells[l_pos, 16].Value     = dtDetail.Rows[i]["in_qty_09"];
            exSheet.Cells[l_pos + 1, 16].Value = dtDetail.Rows[i]["out_qty_09"];
            exSheet.Cells[l_pos + 2, 16].Value = "=O" + (l_pos + 2) + "+P" + (l_pos) + "-P" + (l_pos + 1);

            exSheet.Cells[l_pos, 17].Value     = dtDetail.Rows[i]["in_qty_10"];
            exSheet.Cells[l_pos + 1, 17].Value = dtDetail.Rows[i]["out_qty_10"];
            exSheet.Cells[l_pos + 2, 17].Value = "=P" + (l_pos + 2) + "+Q" + (l_pos) + "-Q" + (l_pos + 1);

            exSheet.Cells[l_pos, 18].Value     = dtDetail.Rows[i]["in_qty_11"];
            exSheet.Cells[l_pos + 1, 18].Value = dtDetail.Rows[i]["out_qty_11"];
            exSheet.Cells[l_pos + 2, 18].Value = "=Q" + (l_pos + 2) + "+R" + (l_pos) + "-R" + (l_pos + 1);

            exSheet.Cells[l_pos, 19].Value     = dtDetail.Rows[i]["in_qty_12"];
            exSheet.Cells[l_pos + 1, 19].Value = dtDetail.Rows[i]["out_qty_12"];
            exSheet.Cells[l_pos + 2, 19].Value = "=R" + (l_pos + 2) + "+S" + (l_pos) + "-S" + (l_pos + 1);

            exSheet.Cells[l_pos, 20].Value     = dtDetail.Rows[i]["in_qty_13"];
            exSheet.Cells[l_pos + 1, 20].Value = dtDetail.Rows[i]["out_qty_13"];
            exSheet.Cells[l_pos + 2, 20].Value = "=S" + (l_pos + 2) + "+T" + (l_pos) + "-T" + (l_pos + 1);

            exSheet.Cells[l_pos, 21].Value     = dtDetail.Rows[i]["in_qty_14"];
            exSheet.Cells[l_pos + 1, 21].Value = dtDetail.Rows[i]["out_qty_14"];
            exSheet.Cells[l_pos + 2, 21].Value = "=T" + (l_pos + 2) + "+U" + (l_pos) + "-U" + (l_pos + 1);

            exSheet.Cells[l_pos, 22].Value     = dtDetail.Rows[i]["in_qty_15"];
            exSheet.Cells[l_pos + 1, 22].Value = dtDetail.Rows[i]["out_qty_15"];
            exSheet.Cells[l_pos + 2, 22].Value = "=U" + (l_pos + 2) + "+V" + (l_pos) + "-V" + (l_pos + 1);

            exSheet.Cells[l_pos, 23].Value     = dtDetail.Rows[i]["in_qty_16"];
            exSheet.Cells[l_pos + 1, 23].Value = dtDetail.Rows[i]["out_qty_16"];
            exSheet.Cells[l_pos + 2, 23].Value = "=V" + (l_pos + 2) + "+W" + (l_pos) + "-W" + (l_pos + 1);

            exSheet.Cells[l_pos, 24].Value     = dtDetail.Rows[i]["in_qty_17"];
            exSheet.Cells[l_pos + 1, 24].Value = dtDetail.Rows[i]["out_qty_17"];
            exSheet.Cells[l_pos + 2, 24].Value = "=W" + (l_pos + 2) + "+X" + (l_pos) + "-X" + (l_pos + 1);

            exSheet.Cells[l_pos, 25].Value     = dtDetail.Rows[i]["in_qty_18"];
            exSheet.Cells[l_pos + 1, 25].Value = dtDetail.Rows[i]["out_qty_18"];
            exSheet.Cells[l_pos + 2, 25].Value = "=X" + (l_pos + 2) + "+Y" + (l_pos) + "-Y" + (l_pos + 1);

            exSheet.Cells[l_pos, 26].Value     = dtDetail.Rows[i]["in_qty_19"];
            exSheet.Cells[l_pos + 1, 26].Value = dtDetail.Rows[i]["out_qty_19"];
            exSheet.Cells[l_pos + 2, 26].Value = "=Y" + (l_pos + 2) + "+Z" + (l_pos) + "-Z" + (l_pos + 1);

            exSheet.Cells[l_pos, 27].Value     = dtDetail.Rows[i]["in_qty_20"];
            exSheet.Cells[l_pos + 1, 27].Value = dtDetail.Rows[i]["out_qty_20"];
            exSheet.Cells[l_pos + 2, 27].Value = "=Z" + (l_pos + 2) + "+AA" + (l_pos) + "-AA" + (l_pos + 1);

            exSheet.Cells[l_pos, 28].Value     = dtDetail.Rows[i]["in_qty_21"];
            exSheet.Cells[l_pos + 1, 28].Value = dtDetail.Rows[i]["out_qty_21"];
            exSheet.Cells[l_pos + 2, 28].Value = "=AA" + (l_pos + 2) + "+AB" + (l_pos) + "-AB" + (l_pos + 1);

            exSheet.Cells[l_pos, 29].Value     = dtDetail.Rows[i]["in_qty_22"];
            exSheet.Cells[l_pos + 1, 29].Value = dtDetail.Rows[i]["out_qty_22"];
            exSheet.Cells[l_pos + 2, 29].Value = "=AB" + (l_pos + 2) + "+AC" + (l_pos) + "-AC" + (l_pos + 1);

            exSheet.Cells[l_pos, 30].Value     = dtDetail.Rows[i]["in_qty_23"];
            exSheet.Cells[l_pos + 1, 30].Value = dtDetail.Rows[i]["out_qty_23"];
            exSheet.Cells[l_pos + 2, 30].Value = "=AC" + (l_pos + 2) + "+AD" + (l_pos) + "-AD" + (l_pos + 1);

            exSheet.Cells[l_pos, 31].Value     = dtDetail.Rows[i]["in_qty_24"];
            exSheet.Cells[l_pos + 1, 31].Value = dtDetail.Rows[i]["out_qty_24"];
            exSheet.Cells[l_pos + 2, 31].Value = "=AD" + (l_pos + 2) + "+AE" + (l_pos) + "-AE" + (l_pos + 1);

            exSheet.Cells[l_pos, 32].Value     = dtDetail.Rows[i]["in_qty_25"];
            exSheet.Cells[l_pos + 1, 32].Value = dtDetail.Rows[i]["out_qty_25"];
            exSheet.Cells[l_pos + 2, 32].Value = "=AE" + (l_pos + 2) + "+AF" + (l_pos) + "-AF" + (l_pos + 1);

            exSheet.Cells[l_pos, 33].Value     = dtDetail.Rows[i]["in_qty_26"];
            exSheet.Cells[l_pos + 1, 33].Value = dtDetail.Rows[i]["out_qty_26"];
            exSheet.Cells[l_pos + 2, 33].Value = "=AF" + (l_pos + 2) + "+AG" + (l_pos) + "-AG" + (l_pos + 1);

            exSheet.Cells[l_pos, 34].Value     = dtDetail.Rows[i]["in_qty_27"];
            exSheet.Cells[l_pos + 1, 34].Value = dtDetail.Rows[i]["out_qty_27"];
            exSheet.Cells[l_pos + 2, 34].Value = "=AG" + (l_pos + 2) + "+AH" + (l_pos) + "-AH" + (l_pos + 1);

            exSheet.Cells[l_pos, 35].Value     = dtDetail.Rows[i]["in_qty_28"];
            exSheet.Cells[l_pos + 1, 35].Value = dtDetail.Rows[i]["out_qty_28"];
            exSheet.Cells[l_pos + 2, 35].Value = "=AH" + (l_pos + 2) + "+AI" + (l_pos) + "-AI" + (l_pos + 1);

            exSheet.Cells[l_pos, 36].Value     = dtDetail.Rows[i]["in_qty_29"];
            exSheet.Cells[l_pos + 1, 36].Value = dtDetail.Rows[i]["out_qty_29"];
            exSheet.Cells[l_pos + 2, 36].Value = "=AI" + (l_pos + 2) + "+AJ" + (l_pos) + "-AJ" + (l_pos + 1);

            exSheet.Cells[l_pos, 37].Value     = dtDetail.Rows[i]["in_qty_30"];
            exSheet.Cells[l_pos + 1, 37].Value = dtDetail.Rows[i]["out_qty_30"];
            exSheet.Cells[l_pos + 2, 37].Value = "=AJ" + (l_pos + 2) + "+AK" + (l_pos) + "-AK" + (l_pos + 1);

            exSheet.Cells[l_pos, 38].Value     = dtDetail.Rows[i]["in_qty_31"];
            exSheet.Cells[l_pos + 1, 38].Value = dtDetail.Rows[i]["out_qty_31"];
            exSheet.Cells[l_pos + 2, 38].Value = "=AK" + (l_pos + 2) + "+AL" + (l_pos) + "-AL" + (l_pos + 1);

            exSheet.Cells[l_pos, 39].Value = dtDetail.Rows[i]["end_qty"];
            exSheet.Range[l_pos, 39, l_pos + 2, 39].Merge();

            p_total += Convert.ToDouble(dtDetail.Rows[i]["end_qty"]);
            p_begin += Convert.ToDouble(dtDetail.Rows[i]["begin_qty"]);

            p_01 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 8].Value);
            p_02 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 9].Value);
            p_03 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 10].Value);
            p_04 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 11].Value);
            p_05 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 12].Value);
            p_06 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 13].Value);
            p_07 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 14].Value);
            p_08 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 15].Value);
            p_09 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 16].Value);
            p_10 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 17].Value);
            p_11 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 18].Value);
            p_12 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 19].Value);
            p_13 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 20].Value);
            p_14 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 21].Value);
            p_15 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 22].Value);
            p_16 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 23].Value);
            p_17 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 24].Value);
            p_18 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 25].Value);
            p_19 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 26].Value);
            p_20 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 27].Value);
            p_21 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 28].Value);
            p_22 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 29].Value);
            p_23 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 30].Value);
            p_24 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 31].Value);
            p_25 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 32].Value);
            p_26 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 33].Value);
            p_27 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 34].Value);
            p_28 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 35].Value);
            p_29 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 36].Value);
            p_30 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 37].Value);
            p_31 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 38].Value);

            l_pos = l_pos + 3;
        }


        exSheet.Cells[l_pos, 6].Value = p_begin;

        exSheet.Cells[l_pos, 8].Value  = p_01;
        exSheet.Cells[l_pos, 9].Value  = p_02;
        exSheet.Cells[l_pos, 10].Value = p_03;

        exSheet.Cells[l_pos, 11].Value = p_04;
        exSheet.Cells[l_pos, 12].Value = p_05;
        exSheet.Cells[l_pos, 13].Value = p_06;

        exSheet.Cells[l_pos, 14].Value = p_07;
        exSheet.Cells[l_pos, 15].Value = p_08;
        exSheet.Cells[l_pos, 16].Value = p_09;

        exSheet.Cells[l_pos, 17].Value = p_10;
        exSheet.Cells[l_pos, 18].Value = p_11;
        exSheet.Cells[l_pos, 19].Value = p_12;

        exSheet.Cells[l_pos, 20].Value = p_13;
        exSheet.Cells[l_pos, 21].Value = p_14;
        exSheet.Cells[l_pos, 22].Value = p_15;

        exSheet.Cells[l_pos, 23].Value = p_16;
        exSheet.Cells[l_pos, 24].Value = p_17;
        exSheet.Cells[l_pos, 25].Value = p_18;

        exSheet.Cells[l_pos, 26].Value = p_19;
        exSheet.Cells[l_pos, 27].Value = p_20;
        exSheet.Cells[l_pos, 28].Value = p_21;

        exSheet.Cells[l_pos, 29].Value = p_22;
        exSheet.Cells[l_pos, 30].Value = p_23;
        exSheet.Cells[l_pos, 31].Value = p_24;

        exSheet.Cells[l_pos, 32].Value = p_25;
        exSheet.Cells[l_pos, 33].Value = p_26;
        exSheet.Cells[l_pos, 34].Value = p_27;

        exSheet.Cells[l_pos, 35].Value = p_28;
        exSheet.Cells[l_pos, 36].Value = p_29;
        exSheet.Cells[l_pos, 37].Value = p_30;

        exSheet.Cells[l_pos, 38].Value = p_31;

        exSheet.Cells[l_pos, 39].Value = p_total;

        // 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();
    }
示例#18
0
    protected void Page_Load(object sender, EventArgs e)
    {
        //CtlLib.SetUser(Session["crm"].ToString());
        CtlLib.SetUser("crm");

        string TemplateFile = "rpt_ht_60270070_POS_Adjust.xls";
        string TempFile     = "../../../../system/temp/rpt_ht_60270070_POS_Adjust_" + 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_master_pk = Request["p_master_pk"];

        string    para = "'" + p_master_pk + "'";
        DataTable dt   = CtlLib.TableReadOpenCursor("sp_rpt_htrt00120", 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_date"];
            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["B2"].Value = dt.Rows[0]["print_time"];

        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();
    }
示例#19
0
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser(Session["APP_DBUSER"].ToString());

        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_wh_pk     = Request["p_wh_pk"];
        string p_cust      = Request["p_cust"];


        string TemplateFile = "rpt_dsbs00300.xls";
        string TempFile     = "../../../../system/temp/rpt_dsbs00300_" + 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 = "'" + p_from_date + "','" + p_to_date + "','" + p_wh_type + "','" + p_wh_pk + "','" + p_cust + "'";

        DataTable dtInfo   = CtlLib.TableReadOpenCursor("st_lg_RPT_dsbs00300_1", "'" + Session["User_ID"].ToString() + "','" + p_from_date + "','" + p_to_date + "','" + p_wh_pk + "'");
        DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_RPT_dsbs00300", para);

        if (dtInfo.Rows.Count == 0)
        {
            goto xxx;
        }
        //--set-----DataBind--in---Master---
        if (!string.IsNullOrEmpty(dtInfo.Rows[0]["pic"].ToString()))
        {
            exSheet.Cells["A1"].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["J1"].Value = "Tel: " + dtInfo.Rows[0]["phone_no"].ToString();
        exSheet.Cells["J2"].Value = "Fax: " + dtInfo.Rows[0]["fax_no"].ToString();
        exSheet.Cells["J3"].Value = "Print date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm");

        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["J7"].Value = dtInfo.Rows[0]["t_date"].ToString();
        exSheet.Cells["C7"].Value = dtInfo.Rows[0]["warehouse"].ToString();
        ////end-set-----DataBind--in---Master---
xxx:
        if (dtDetail.Rows.Count == 0)
        {
            return;
        }

        for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++)
        {
            exSheet.Range["A10"].Rows.EntireRow.Insert();//insert row new of sheet
        }

        for (int l_row = 1; l_row <= dtDetail.Rows.Count; l_row++)
        {
            exSheet.Cells[8 + l_row, 1].Value  = dtDetail.Rows[l_row - 1]["wh_id"].ToString();
            exSheet.Cells[8 + l_row, 2].Value  = dtDetail.Rows[l_row - 1]["wh_name"].ToString();
            exSheet.Cells[8 + l_row, 3].Value  = dtDetail.Rows[l_row - 1]["OUT_DATE"].ToString();
            exSheet.Cells[8 + l_row, 4].Value  = dtDetail.Rows[l_row - 1]["partner_id"].ToString();
            exSheet.Cells[8 + l_row, 5].Value  = dtDetail.Rows[l_row - 1]["partner_name"].ToString();
            exSheet.Cells[8 + l_row, 6].Value  = dtDetail.Rows[l_row - 1]["slip_no"].ToString();
            exSheet.Cells[8 + l_row, 7].Value  = dtDetail.Rows[l_row - 1]["ccy_unit"].ToString();
            exSheet.Cells[8 + l_row, 8].Value  = dtDetail.Rows[l_row - 1]["item_amount"];
            exSheet.Cells[8 + l_row, 9].Value  = dtDetail.Rows[l_row - 1]["tax_amount"];
            exSheet.Cells[8 + l_row, 10].Value = dtDetail.Rows[l_row - 1]["total_amount"];
            exSheet.Cells[8 + l_row, 11].Value = dtDetail.Rows[l_row - 1]["INVOICE_NO"].ToString();
            exSheet.Cells[8 + l_row, 12].Value = dtDetail.Rows[l_row - 1]["invoice_date"].ToString();
            exSheet.Cells[8 + l_row, 13].Value = dtDetail.Rows[l_row - 1]["invoice_net_Tr_amt"];
            exSheet.Cells[8 + l_row, 14].Value = dtDetail.Rows[l_row - 1]["invoice_vat_tax_amt"];
            exSheet.Cells[8 + l_row, 15].Value = dtDetail.Rows[l_row - 1]["invoice_total_amount"];
        }

        exSheet.Cells[9 + dtDetail.Rows.Count, 8].Value  = "=SUM(H9:H" + (dtDetail.Rows.Count + 8) + ")";
        exSheet.Cells[9 + dtDetail.Rows.Count, 9].Value  = "=SUM(I9:I" + (dtDetail.Rows.Count + 8) + ")";
        exSheet.Cells[9 + dtDetail.Rows.Count, 10].Value = "=SUM(J9:J" + (dtDetail.Rows.Count + 8) + ")";

        exSheet.Cells[9 + dtDetail.Rows.Count, 13].Value = "=SUM(M9:M" + (dtDetail.Rows.Count + 8) + ")";
        exSheet.Cells[9 + dtDetail.Rows.Count, 14].Value = "=SUM(N9:N" + (dtDetail.Rows.Count + 8) + ")";
        exSheet.Cells[9 + dtDetail.Rows.Count, 15].Value = "=SUM(O9:O" + (dtDetail.Rows.Count + 8) + ")";
        // 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_fr  = Request["p_fr"];
        string l_to  = Request["p_to"];
        string l_loc = Request["p_loc"];

        string TemplateFile = "rpt_60270030_Invoice.xls";
        string TempFile     = "../../../../system/temp/rpt_60270030_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 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_invoice2", para);
        if (dt.Rows.Count > 0)
        {
            sheet.Cells[4, 5].Value  = dt.Rows[0][14];
            sheet.Cells[4, 11].Value = dt.Rows[0][15];
            sheet.Cells[5, 5].Value  = dt.Rows[0][16];

            for (int y = 0; y < dt.Rows.Count; y++)
            {
                if (dt.Rows[y][0].ToString() != "" && dt.Rows[y][3].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 Invoice ID: " + dt.Rows[y][0].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 5:
                        case 6:
                        case 7:
                        case 8:
                        case 9:
                            sheet.Cells[row, int.Parse(title[0][x])].Value               = double.Parse(dt.Rows[y][x + 3].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][0].ToString() != "" && dt.Rows[y][3].ToString() != "")
                {
                    if (y == 0 || (y > 0 && dt.Rows[y - 1][0].ToString() != "" && dt.Rows[y - 1][3].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               = "Invoice ID: " + dt.Rows[y][0].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;

                            case 2:
                                sheet.Cells[row, int.Parse(title[0][x])].Value               = "Guest Name: " + dt.Rows[y][2].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;
                                break;

                            case 9:
                                sheet.Cells[row, int.Parse(title[0][x])].Value               = "Cover: " + dt.Rows[y][1].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;
                                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 + 3].ToString();
                            break;

                        case 1:
                            sheet.Cells[row, int.Parse(title[0][x])].Value = dt.Rows[y][x + 3].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 + 3].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 + 3].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 + 3].ToString());
                            sheet.Cells[row, int.Parse(title[0][x])].NumberFormat        = "###,###,##0";
                            sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignRight;
                            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();
    }
示例#21
0
    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)
    {
        int C_GRP_NM    = 1;
        int C_ITEM_CODE = 2;
        int C_ITEM_NAME = 3;
        int C_UOM       = 4;
        int C_STOCK_QTY = 5;
        int C_WH_1      = 6;
        int C_WH_2      = 7;
        int C_WH_3      = 8;
        int C_WH_4      = 9;
        int C_WH_5      = 10;
        int C_WH_6      = 11;
        int C_WH_7      = 12;
        int C_WH_8      = 13;
        int C_WH_9      = 14;
        int C_WH_10     = 15;

        CtlLib.SetUser(Session["APP_DBUSER"].ToString());

        string TemplateFile = "rpt_bisc00140_grand.xls";
        string TempFile     = "../../../../system/temp/rpt_bisc00140_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_grp = Request.QueryString["p_item_grp"];
        string p_item     = Request.QueryString["p_item"];
        string p_rad_type = Request.QueryString["p_rad_type"];
        string p_dt_from  = Request.QueryString["p_dt_from"];

        string p_wh_pk_1  = Request.QueryString["p_wh_pk_1"];
        string p_wh_pk_2  = Request.QueryString["p_wh_pk_2"];
        string p_wh_pk_3  = Request.QueryString["p_wh_pk_3"];
        string p_wh_pk_4  = Request.QueryString["p_wh_pk_4"];
        string p_wh_pk_5  = Request.QueryString["p_wh_pk_5"];
        string p_wh_pk_6  = Request.QueryString["p_wh_pk_6"];
        string p_wh_pk_7  = Request.QueryString["p_wh_pk_7"];
        string p_wh_pk_8  = Request.QueryString["p_wh_pk_8"];
        string p_wh_pk_9  = Request.QueryString["p_wh_pk_9"];
        string p_wh_pk_10 = Request.QueryString["p_wh_pk_10"];

        string p_storage = Request.QueryString["p_storage"];
        string p_wh_type = Request.QueryString["p_wh_type"];

        string paraHeader = "'" + p_storage +
                            "','" + p_wh_type + "'";

        string para = "'" + p_lang +
                      "','" + p_item_grp +
                      "','" + p_item +
                      "','" + p_rad_type +
                      "','" + p_dt_from +
                      "','" + p_wh_pk_1 +
                      "','" + p_wh_pk_2 +
                      "','" + p_wh_pk_3 +
                      "','" + p_wh_pk_4 +
                      "','" + p_wh_pk_5 +
                      "','" + p_wh_pk_6 +
                      "','" + p_wh_pk_7 +
                      "','" + p_wh_pk_8 +
                      "','" + p_wh_pk_9 +
                      "','" + p_wh_pk_10 + "'";

        DataTable dtInfo   = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00140_2", "'" + Session["User_ID"].ToString() + "'");
        DataTable dtHeader = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00140", paraHeader);
        DataTable dtDetail = CtlLib.TableReadOpenCursor("lg_sel_bisc00140_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_dt_from, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy");

        exSheet.Cells["E6"].Value = String.Format("Date: {0}", strFromDt);

        //Set data for header
        if (dtHeader.Rows != null && dtHeader.Rows.Count > 0)
        {
            exSheet.Cells[7, C_WH_1].Value  = dtHeader.Rows[0]["wh_01_nm"];
            exSheet.Cells[7, C_WH_2].Value  = dtHeader.Rows[0]["wh_02_nm"];
            exSheet.Cells[7, C_WH_3].Value  = dtHeader.Rows[0]["wh_03_nm"];
            exSheet.Cells[7, C_WH_4].Value  = dtHeader.Rows[0]["wh_04_nm"];
            exSheet.Cells[7, C_WH_5].Value  = dtHeader.Rows[0]["wh_05_nm"];
            exSheet.Cells[7, C_WH_6].Value  = dtHeader.Rows[0]["wh_06_nm"];
            exSheet.Cells[7, C_WH_7].Value  = dtHeader.Rows[0]["wh_07_nm"];
            exSheet.Cells[7, C_WH_8].Value  = dtHeader.Rows[0]["wh_08_nm"];
            exSheet.Cells[7, C_WH_9].Value  = dtHeader.Rows[0]["wh_09_nm"];
            exSheet.Cells[7, C_WH_10].Value = dtHeader.Rows[0]["wh_10_nm"];
        }

        //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_qty1 = 0, sum_qty2 = 0, sum_qty3 = 0, sum_qty4 = 0, sum_qty5 = 0;
        double sum_qty6 = 0, sum_qty7 = 0, sum_qty8 = 0, sum_qty9 = 0, sum_qty10 = 0;

        double tmp_qty1 = 0, tmp_qty2 = 0, tmp_qty3 = 0, tmp_qty4 = 0, tmp_qty5 = 0;
        double tmp_qty6 = 0, tmp_qty7 = 0, tmp_qty8 = 0, tmp_qty9 = 0, tmp_qty10 = 0;

        double sum_qty1_sub = 0, sum_qty2_sub = 0, sum_qty3_sub = 0, sum_qty4_sub = 0, sum_qty5_sub = 0;
        double sum_qty6_sub = 0, sum_qty7_sub = 0, sum_qty8_sub = 0, sum_qty9_sub = 0, sum_qty10_sub = 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, C_WH_1].Value  = String.Format("{0:0,0}", sum_qty1_sub);
                exSheet.Cells[l_pos, C_WH_2].Value  = String.Format("{0:0,0}", sum_qty2_sub);
                exSheet.Cells[l_pos, C_WH_3].Value  = String.Format("{0:0,0}", sum_qty3_sub);
                exSheet.Cells[l_pos, C_WH_4].Value  = String.Format("{0:0,0}", sum_qty4_sub);
                exSheet.Cells[l_pos, C_WH_5].Value  = String.Format("{0:0,0}", sum_qty5_sub);
                exSheet.Cells[l_pos, C_WH_6].Value  = String.Format("{0:0,0}", sum_qty6_sub);
                exSheet.Cells[l_pos, C_WH_7].Value  = String.Format("{0:0,0}", sum_qty7_sub);
                exSheet.Cells[l_pos, C_WH_8].Value  = String.Format("{0:0,0}", sum_qty8_sub);
                exSheet.Cells[l_pos, C_WH_9].Value  = String.Format("{0:0,0}", sum_qty9_sub);
                exSheet.Cells[l_pos, C_WH_10].Value = String.Format("{0:0,0}", sum_qty10_sub);

                FormatTotalRange(exSheet, l_pos, C_GRP_NM, l_pos, C_WH_10);
                exSheet.Cells[l_pos, C_GRP_NM, l_pos, C_STOCK_QTY].Merge();

                l_pos += 1;

                sum_qty1_sub = 0; sum_qty2_sub = 0; sum_qty3_sub = 0; sum_qty4_sub = 0; sum_qty5_sub = 0;
                sum_qty6_sub = 0; sum_qty7_sub = 0; sum_qty8_sub = 0; sum_qty9_sub = 0; sum_qty10_sub = 0;
            }

            exSheet.Cells[l_pos, C_GRP_NM].Value    = dtDetail.Rows[i]["grp_nm"];
            exSheet.Cells[l_pos, C_ITEM_CODE].Value = dtDetail.Rows[i]["item_code"];
            exSheet.Cells[l_pos, C_ITEM_NAME].Value = dtDetail.Rows[i]["item_name"];
            exSheet.Cells[l_pos, C_UOM].Value       = dtDetail.Rows[i]["uom"];
            exSheet.Cells[l_pos, C_STOCK_QTY].Value = dtDetail.Rows[i]["stock_qty"];

            exSheet.Cells[l_pos, C_WH_1].Value  = dtDetail.Rows[i]["qty1"];
            exSheet.Cells[l_pos, C_WH_2].Value  = dtDetail.Rows[i]["qty2"];
            exSheet.Cells[l_pos, C_WH_3].Value  = dtDetail.Rows[i]["qty3"];
            exSheet.Cells[l_pos, C_WH_4].Value  = dtDetail.Rows[i]["qty4"];
            exSheet.Cells[l_pos, C_WH_5].Value  = dtDetail.Rows[i]["qty5"];
            exSheet.Cells[l_pos, C_WH_6].Value  = dtDetail.Rows[i]["qty6"];
            exSheet.Cells[l_pos, C_WH_7].Value  = dtDetail.Rows[i]["qty7"];
            exSheet.Cells[l_pos, C_WH_8].Value  = dtDetail.Rows[i]["qty8"];
            exSheet.Cells[l_pos, C_WH_9].Value  = dtDetail.Rows[i]["qty9"];
            exSheet.Cells[l_pos, C_WH_10].Value = dtDetail.Rows[i]["qty10"];

            FormatNormalRange(exSheet, l_pos, C_GRP_NM, l_pos, C_WH_10);

            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;
            }

            if (double.TryParse(dtDetail.Rows[i]["qty6"].ToString(), out tmp_qty6))
            {
                sum_qty6_sub += tmp_qty6;
                sum_qty6     += tmp_qty6;
            }

            if (double.TryParse(dtDetail.Rows[i]["qty7"].ToString(), out tmp_qty7))
            {
                sum_qty7_sub += tmp_qty7;
                sum_qty7     += tmp_qty7;
            }

            if (double.TryParse(dtDetail.Rows[i]["qty8"].ToString(), out tmp_qty8))
            {
                sum_qty8_sub += tmp_qty8;
                sum_qty8     += tmp_qty8;
            }

            if (double.TryParse(dtDetail.Rows[i]["qty9"].ToString(), out tmp_qty9))
            {
                sum_qty9_sub += tmp_qty9;
                sum_qty9     += tmp_qty9;
            }

            if (double.TryParse(dtDetail.Rows[i]["qty10"].ToString(), out tmp_qty10))
            {
                sum_qty10_sub += tmp_qty10;
                sum_qty10     += tmp_qty10;
            }
        }

        l_pos += 1;
        exSheet.Cells[l_pos, C_WH_1].Value  = String.Format("{0:0,0}", sum_qty1_sub);
        exSheet.Cells[l_pos, C_WH_2].Value  = String.Format("{0:0,0}", sum_qty2_sub);
        exSheet.Cells[l_pos, C_WH_3].Value  = String.Format("{0:0,0}", sum_qty3_sub);
        exSheet.Cells[l_pos, C_WH_4].Value  = String.Format("{0:0,0}", sum_qty4_sub);
        exSheet.Cells[l_pos, C_WH_5].Value  = String.Format("{0:0,0}", sum_qty5_sub);
        exSheet.Cells[l_pos, C_WH_6].Value  = String.Format("{0:0,0}", sum_qty6_sub);
        exSheet.Cells[l_pos, C_WH_7].Value  = String.Format("{0:0,0}", sum_qty7_sub);
        exSheet.Cells[l_pos, C_WH_8].Value  = String.Format("{0:0,0}", sum_qty8_sub);
        exSheet.Cells[l_pos, C_WH_9].Value  = String.Format("{0:0,0}", sum_qty9_sub);
        exSheet.Cells[l_pos, C_WH_10].Value = String.Format("{0:0,0}", sum_qty10_sub);

        FormatTotalRange(exSheet, l_pos, C_GRP_NM, l_pos, C_WH_10);
        exSheet.Cells[l_pos, C_GRP_NM, l_pos, C_STOCK_QTY].Merge();

        l_pos += 1;
        exSheet.Cells[l_pos, C_WH_1].Value  = String.Format("{0:0,0}", sum_qty1);
        exSheet.Cells[l_pos, C_WH_2].Value  = String.Format("{0:0,0}", sum_qty2);
        exSheet.Cells[l_pos, C_WH_3].Value  = String.Format("{0:0,0}", sum_qty3);
        exSheet.Cells[l_pos, C_WH_4].Value  = String.Format("{0:0,0}", sum_qty4);
        exSheet.Cells[l_pos, C_WH_5].Value  = String.Format("{0:0,0}", sum_qty5);
        exSheet.Cells[l_pos, C_WH_6].Value  = String.Format("{0:0,0}", sum_qty6);
        exSheet.Cells[l_pos, C_WH_7].Value  = String.Format("{0:0,0}", sum_qty7);
        exSheet.Cells[l_pos, C_WH_8].Value  = String.Format("{0:0,0}", sum_qty8);
        exSheet.Cells[l_pos, C_WH_9].Value  = String.Format("{0:0,0}", sum_qty9);
        exSheet.Cells[l_pos, C_WH_10].Value = String.Format("{0:0,0}", sum_qty10);

        FormatTotalRange(exSheet, l_pos, C_GRP_NM, l_pos, C_WH_10);

        exSheet.Cells[l_pos, C_GRP_NM, l_pos, C_STOCK_QTY].Merge();
        exSheet.Cells[l_pos, C_GRP_NM, l_pos, C_STOCK_QTY].HorizontalAlignment = XlHAlign.xlHAlignCenter;
        exSheet.Cells[l_pos, C_GRP_NM, l_pos, C_STOCK_QTY].Value     = "GRAND TOTAL";
        exSheet.Cells[l_pos, C_GRP_NM, l_pos, C_STOCK_QTY].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_dt_frm = "", l_dt_departure = "", l_parameter = "", l_guest_name = "", l_total_room = "", l_report_type = "";

        l_dt_frm       = Request["p_dt_frm"];
        l_dt_departure = Request["p_dt_departure"];
        l_guest_name   = Request["p_guest_name"];
        l_total_room   = Request["p_count_RM"];
        l_report_type  = Request["p_report_type"];

        l_parameter = "'" + l_dt_frm + "','" + l_dt_departure + "','" + l_guest_name + "','" + l_report_type + "'";

        string TemplateFile = "rpt_60300010.xls";
        string TempFile     = "../../../../system/temp/rpt_60300010_" + 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_arrival", 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)
        {
            if (l_report_type == "1")
            {
                exSheet.Cells[1, 5].Value = "Arrival list ";
            }
            if (l_report_type == "2")
            {
                exSheet.Cells[1, 5].Value = "Arrival list (Normal)";
            }
            if (l_report_type == "3")
            {
                exSheet.Cells[1, 5].Value = "Arrival list (VIP)";
            }
            exSheet.Cells[2, 1].Value  = dt.Rows[0]["date_para"].ToString();
            exSheet.Cells[2, 14].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 = 4;

        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]["company_name"].ToString();
            exSheet.Cells[l_row_first + l_row, 2].Value = dt.Rows[l_row]["last_name"].ToString();
            exSheet.Cells[l_row_first + l_row, 3].Value = dt.Rows[l_row]["from_date"].ToString();
            exSheet.Cells[l_row_first + l_row, 4].Value = dt.Rows[l_row]["date_to"].ToString();
            if (dt.Rows[l_row]["total_day"].ToString() != "")
            {
                exSheet.Cells[l_row_first + l_row, 5].Value = double.Parse(dt.Rows[l_row]["total_day"].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());
            }
            exSheet.Cells[l_row_first + l_row, 7].Value = dt.Rows[l_row]["room_type"].ToString();
            if (dt.Rows[l_row]["room_no"].ToString() != "")
            {
                exSheet.Cells[l_row_first + l_row, 8].Value = double.Parse(dt.Rows[l_row]["room_no"].ToString());
            }
            //exSheet.Cells[l_row_first + l_row, 9].Value = dt.Rows[l_row]["contact_person"].ToString();
            if (dt.Rows[l_row]["total_adult"].ToString() != "")
            {
                exSheet.Cells[l_row_first + l_row, 9].Value = double.Parse(dt.Rows[l_row]["total_adult"].ToString());
            }
            if (dt.Rows[l_row]["total_child"].ToString() != "")
            {
                exSheet.Cells[l_row_first + l_row, 10].Value = double.Parse(dt.Rows[l_row]["total_child"].ToString());
            }
            if (dt.Rows[l_row]["slip_no"].ToString() != "")
            {
                exSheet.Cells[l_row_first + l_row, 11].Value = dt.Rows[l_row]["slip_no"].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]["vip_type"].ToString();
            exSheet.Cells[l_row_first + l_row, 14].Value = dt.Rows[l_row]["description"].ToString();
            //exSheet.Cells[l_row_first + l_row, 15].Value = dt.Rows[l_row]["description"].ToString();
            exSheet.Cells[l_row_first + l_row, 16].Value = dt.Rows[l_row]["nation"].ToString();
            exSheet.Cells[l_row_first + l_row, 17].Value = dt.Rows[l_row]["group_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();
    }
示例#24
0
    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();
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser("sale");

        string TemplateFile = "rpt_dscd00030_ST02.xls";
        string TempFile     = "../../../../system/temp/rpt_dscd00030_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 p_from_date     = Request["p_from_date"];
        string p_to_date       = Request["p_to_date"];
        string p_order_type    = Request["p_order_type"];
        string p_po_vendor     = Request["p_po_vendor"];
        string p_item_group_pk = Request["p_item_group_pk"];
        string p_item          = Request["p_item"];
        string p_type          = Request["p_type"];
        string p_inout_type    = Request["p_inout_type"];
        string p_wh_type       = Request["p_wh_type"];
        string p_wh_pk         = Request["p_wh_pk"];

        string    para     = "'" + p_from_date + "','" + p_to_date + "','" + Session["User_ID"].ToString() + "'";
        DataTable dtMaster = CtlLib.TableReadOpenCursor("st_lg_rpt_dscd00030_ST02_1", para);

        if (dtMaster.Rows.Count > 0)
        {
            exSheet.Cells["B2"].Value = dtMaster.Rows[0]["partner_name"];
            exSheet.Cells["H2"].Value = "Tel: " + dtMaster.Rows[0]["phone_no"];

            exSheet.Cells["B3"].Value = dtMaster.Rows[0]["addr1"];
            exSheet.Cells["H3"].Value = "Fax: " + dtMaster.Rows[0]["fax_no"];

            exSheet.Cells["B4"].Value = "Tax No: " + dtMaster.Rows[0]["tax_code"];
            exSheet.Cells["H4"].Value = "Print Date: " + DateTime.Now.ToString("dd/MM/yyyy HH:mm");
        }

        exSheet.Cells["A7"].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");

        para = "'" + p_from_date + "','" + p_to_date + "','" + p_order_type + "','" + p_po_vendor + "','" + p_item_group_pk + "','" + p_item + "','" + p_type + "','" + p_inout_type + "','" + p_wh_type + "','" + p_wh_pk + "'";
        DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_RPT_DSCD00030_ST02_2", para);

        for (int r = 0; r < dtDetail.Rows.Count; r++)
        {
            if (r < dtDetail.Rows.Count - 2)
            {
                exSheet.Range["A" + (10 + r)].Rows.EntireRow.Insert();
            }
            for (int c = 0; c < dtDetail.Columns.Count; c++)
            {
                exSheet.Cells[9 + r, c + 1].Value = dtDetail.Rows[r][c];
            }
        }
        // end loop detail not percent
        if (dtDetail.Rows.Count > 0)
        {
            exSheet.Cells["F" + (dtDetail.Rows.Count + 9)].Value = "=SUM(F9:F" + (dtDetail.Rows.Count + 8) + ")";
            exSheet.Cells["G" + (dtDetail.Rows.Count + 9)].Value = "=SUM(G9:G" + (dtDetail.Rows.Count + 8) + ")";
            exSheet.Cells["H" + (dtDetail.Rows.Count + 9)].Value = "=SUM(H9:H" + (dtDetail.Rows.Count + 8) + ")";
        }
        // 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();
    }
示例#26
0
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser(Session["APP_DBUSER"].ToString());
        string l_dtfrm      = Request["p_dtfrom"];
        string l_dtto       = Request["p_dtto"];
        string l_guest_name = Request["p_guest_name"];

        string l_parameter = "'" + l_dtfrm + "','" + l_dtto + "','" + l_guest_name + "'";

        string TemplateFile = "rpt_60280090_inhouse_log.xls";
        string TempFile     = "../../../../system/temp/rpt_60280090_inhouse_log_" + 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("rpt_60280090", l_parameter);

        //-----header---

        //---end--header---
        //--set-----DataBind--in---Master---

        exSheet.Cells[3, 1].Value  = dtDetail.Rows[0]["date_para"].ToString();
        exSheet.Cells[3, 9].Value  = dtDetail.Rows.Count;
        exSheet.Cells[3, 10].Value = " Room(s)";
        exSheet.Cells[3, 22].Value = dtDetail.Rows[0]["print_time"].ToString();
        //----------------------
        ////end-set-----DataBind--in---Master---

        //sumtotal
        // not tax
        //exSheet.Cells[13, 7].Value = long.Parse(dtTotal.Rows[0][0].ToString());//total
        // exSheet.Cells[16, 7].Value = long.Parse(dtTotal.Rows[0][0].ToString());//grand total

        //end sum total
        for (int l_addrow = 1; l_addrow < dtDetail.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_firstt = 6;

        for (int l_row = 0; l_row < dtDetail.Rows.Count; l_row++)
        {
            for (int col = 0; col <= 22; col++)
            {
                //exSheet.Cells[11 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1][col].ToString();
                //exSheet.Cells[5, col + 1].Value = dt.Rows[0][col].ToString();
                if (col == 0 || col == 1 || col == 2 || col == 3 || col == 4 || col == 5 || col == 6 || col == 7 || col == 9 || col == 10 || col == 11 || col == 12 || col == 13 || col == 18 || col == 19 || col == 20 || col == 22)
                {
                    exSheet.Cells[l_firstt + l_row, col + 1].Value = dtDetail.Rows[l_row][col].ToString();
                }
                else
                {
                    if (dtDetail.Rows[l_row][col].ToString() != "")
                    {
                        exSheet.Cells[l_firstt + l_row, col + 1].Value = double.Parse(dtDetail.Rows[l_row][col].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

        /*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_user, p_workgroup, p_search, p_org, p_from_dt, p_to_dt, p_nation, p_lang;

        p_user      = Request["p_user"].ToString();
        p_workgroup = Request["p_wg"].ToString();
        p_search    = Request["p_emp_search"].ToString();
        p_org       = Request["p_tco_org_pk"].ToString();
        p_from_dt   = Request["p_from_dt"].ToString();
        p_to_dt     = Request["p_to_dt"].ToString();
        p_nation    = Request["p_nation"].ToString();
        p_lang      = Request["p_lang"].ToString();

        string TemplateFile;

        if (p_lang == "ENG")
        {
            TemplateFile = "rpt_hrti00400_1_eng.xls";
        }
        else
        {
            TemplateFile = "rpt_hrti00400_1_vie.xls";
        }

        string TempFile = "../../../../system/temp/rpt_hrti00400_1_" + Session["User_ID"].ToString() + ".xls";

        TemplateFile = Server.MapPath(TemplateFile);
        TempFile     = Server.MapPath(TempFile);

        //Create a new workbook
        IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);

        IWorksheet exSheet = exBook.Worksheets[1];

        IRange range;

        string para = "'" + p_org + "','" + p_workgroup + "','" + p_search + "','" + p_from_dt +
                      "','" + p_to_dt + "','" + p_nation + "','" + p_lang + "'";

        DataTable dt_emp = CtlLib.TableReadOpenCursor("ST_HR_RPT_HRTI00400_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 != "")
        {
            if (p_lang == "ENG")
            {
                exSheet.Cells["A2"].Value = "From Date " + dt_emp.Rows[0][0].ToString() + " -----" + " To Date " + dt_emp.Rows[0][1].ToString();
            }
            else
            {
                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 != 3)
                    {
                        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(Session["APP_DBUSER"].ToString());

        string p_year = Request["p_year"];

        string TemplateFile = "rpt_60300010_nationality_customer_analysis.xls";
        string TempFile     = "../../../../system/temp/rpt_60300010_nationality_customer_analysis_" + 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_bk_source_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]["booking_source"];

            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]["booking_source"].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]["booking_source"].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]["booking_source"].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(Session["APP_DBUSER"].ToString());

        string l_room_no_guest_group = Request["p_room"];

        string TemplateFile = "rpt_60250030_inhouse_list2.xls";
        string TempFile     = "../../../../system/temp/rpt_60250030_inhouse_list2_" + 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 + "'";
        dt   = CtlLib.TableReadOpenCursor("ht_rpt_60250030_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, 8].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
        int l_total_room = 0, l_total_guest = 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 <= 7; col++)
                {
                    if (col == 3)
                    {
                        if (int.Parse(dt.Rows[l_row - 1][col].ToString()) > 999)
                        {
                            l_total_room = l_total_room + 1;
                        }
                    }
                    if (col == 4)
                    {
                        l_total_guest = l_total_guest + int.Parse(dt.Rows[l_row - 1][col].ToString());
                    }
                    if (col == 0 || col == 1 || col == 2 || col == 3 || col == 5 || col == 6 || col == 7)
                    {
                        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;
                        //exSheet.Range[5, col + 1, l_row + 5, 7].Font.Bold = false;
                    }
                    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;
                    }
                }
            }
        }
        exSheet.Cells[3, 1].Value = "Total : " + l_total_room + " Room(s.)";
        //exSheet.Cells[3, 6].Value = "Total : "+l_total_guest+" Guest(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());
        Response.ContentType = "application/vnd.ms-excel";
        Response.Buffer      = false;

        string p_tco_org_pk          = Request["p_tco_org_pk"].ToString();
        string p_thr_wg_pk           = Request["p_thr_wg_pk"].ToString();
        string p_temp                = Request["p_temp"].ToString();
        string p_contract_type       = Request["p_contract_type"].ToString();
        string p_from_begin_contract = Request["p_from_begin_contract"].ToString();
        string p_to_begin_contract   = Request["p_to_begin_contract"].ToString();
        string p_from_end_contract   = Request["p_from_begin_contract"].ToString();
        string p_to_end_contract     = Request["p_to_begin_contract"].ToString();
        string p_from_begin_prob     = Request["p_from_begin_probation"].ToString();
        string p_to_begin_prob       = Request["p_to_begin_probation"].ToString();
        string p_from_end_prob       = Request["p_from_end_probation"].ToString();
        string p_to_end_prob         = Request["p_to_end_probation"].ToString();
        string p_status              = Request["p_status"].ToString();

        string TemplateFile = "rpt_hrem00500_6.xls";
        string TempFile     = "../../../../system/temp/rpt_hrem00500_6_" + Session["APP_DBUSER"].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_thr_wg_pk + "', '" + p_temp + "','" + p_contract_type + "','" + p_from_begin_contract + "','" + p_to_begin_contract;

        para += "','" + p_from_end_contract + "','" + p_to_end_contract + "','" + p_from_begin_prob + "','" + p_to_begin_prob + "','" + p_from_end_prob + "','" + p_to_end_prob + "','" + p_status + "'";

        DataTable dt_emp = CtlLib.TableReadOpenCursor("ST_HR_RPT_HREM00500_6", para);
        int       irow, icol_emp;

        irow     = dt_emp.Rows.Count;
        icol_emp = dt_emp.Columns.Count;

        if (irow == 0)
        {
            Response.Write("There is no data");
            Response.End();
        }
        int i, j, k;
        int pos = 5, total_col = icol_emp;

        //bind data to excel file

        //string retstring = p_curentmonth.Substring(4,2) +"-"+p_curentmonth.Substring(0,4);
        //exSheet.Cells["C1"].Value=exSheet.Cells["C1"].Value+retstring;
        exSheet.Cells["B1"].Value = "Report by: " + Session["APP_DBUSER"].ToString();
        exSheet.Cells["B2"].Value = "Report date: " + DateTime.Now.ToString("dd/MM/yyyy");

        //---end--header---
        //Insert new row

        for (i = 0; i < irow - 1; i++)
        {
            exSheet.Range[pos + 1, 1].Rows.EntireRow.Insert();
        }
        //Show data
        for (i = 0; i < irow; i++)
        {
            //----------------------------------------

            exSheet.Cells[pos + i, 1].Value = i + 1;
            for (j = 2; j < icol_emp; j++)
            {
                if (j >= 12)
                {
                    exSheet.Cells[pos + i, j].Value = Double.Parse(dt_emp.Rows[i][j].ToString());
                }
                else
                {
                    exSheet.Cells[pos + i, j].Value = dt_emp.Rows[i][j].ToString();
                }
            }
        }
//--------------------------------------



//----------------------------------------
        // hide row firts
        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();
    }