// load a book
        private void _btnLoad_Click(object sender, System.EventArgs e)
        {
            // choose file
            OpenFileDialog dlg = new OpenFileDialog();

            dlg.DefaultExt = "xls";
            dlg.FileName   = "*.xls";
            if (dlg.ShowDialog() != DialogResult.OK)
            {
                return;
            }

            // clear everything
            _book.Clear();
            _tab.TabPages.Clear();

            // load book
            _book.Load(dlg.FileName);

            // create one grid per sheet and add them to listbox
            foreach (XLSheet sheet in _book.Sheets)
            {
                // create a new grid for this sheet
                C1FlexGrid flex = new C1FlexGrid();
                flex.BorderStyle  = _flex.BorderStyle;
                flex.AllowMerging = _flex.AllowMerging;
                flex.Dock         = _flex.Dock;

                // load sheet into new grid
                LoadSheet(flex, sheet, true);

                // add new grid to the list
                TabPage pg = new TabPage();
                pg.Text   = sheet.Name;
                flex.Name = sheet.Name;
                pg.Controls.Add(flex);
                _tab.TabPages.Add(pg);
            }

            // select current sheet
            _tab.SelectedIndex = _book.Sheets.SelectedIndex;
        }
        private void _btCreate_Click(object sender, RoutedEventArgs e)
        {
            // create new workbook
            if (_book == null)
            {
                _book = new C1XLBook();
            }

            // clear the book
            _book.Clear();
            _book.Sheets.Clear();

            // load from resources
            Assembly a = Assembly.GetExecutingAssembly();

            foreach (string res in a.GetManifestResourceNames())
            {
                if (!res.ToLower().EndsWith(".xlsx"))
                {
                    continue;
                }

                using (var stream = a.GetManifestResourceStream(res))
                {
                    // load Excel file
                    var book = new C1XLBook();
                    book.Load(stream, FileFormat.OpenXml);

                    // clone and rename first sheet (sheet names must be unique)
                    var ss = res.Split('.');
                    Debug.Assert(ss.Length >= 3);
                    XLSheet clone = book.Sheets[0].Clone();
                    clone.Name = ss[ss.Length - 2];

                    // add cloned sheet to main book
                    _book.Sheets.Add(clone);
                }
            }

            // allow save the file
            _lblStatus.Text    = "You can save workbook";
            _btnSave.IsEnabled = true;
        }
예제 #3
0
        private void _btCreate_Click(object sender, RoutedEventArgs e)
        {
            // create new workbook
            if (_book == null)
            {
                _book = new C1XLBook();
            }

            // clear the book
            _book.Clear();

            // add some styles
            XLStyle s1 = new XLStyle(_book);
            XLStyle s2 = new XLStyle(_book);
            XLStyle s3 = new XLStyle(_book);

            s1.Format = "#,##0.00000";
            s2.Format = "#,##0.00000";
            s2.Font   = new XLFont("Courier New", 14);
            s3.Format = "dd-MMM-yy";

            // populate sheet with some random values
            XLSheet sheet = _book.Sheets[0];
            Random  r     = new Random();

            for (int i = 0; i < 100; i++)
            {
                sheet[i, 0].Value = r.NextDouble() * 100000;
                sheet[i, 0].Style = (i % 13 == 0) ? s2 : s1;
            }
            for (int i = 0; i < 100; i++)
            {
                sheet[i, 1].Value = new DateTime(2005, r.Next(1, 12), r.Next(1, 28));
                sheet[i, 1].Style = s3;
            }

            // automatic sizing
            AutoSizeColumns(sheet);

            // allow save the file
            _lblStatus.Text    = "You can save workbook";
            _btnSave.IsEnabled = true;
        }
예제 #4
0
        private string CreateExcelFile()
        {
            //clear Excel book, remove the single blank sheet
            _c1xl.Clear();
            _c1xl.Sheets.Clear();
            _c1xl.DefaultFont = new Font("Tahoma", 8);

            //create Excel styles
            _styTitle  = new XLStyle(_c1xl);
            _styHeader = new XLStyle(_c1xl);
            _styMoney  = new XLStyle(_c1xl);
            _styOrder  = new XLStyle(_c1xl);

            //set up styles
            _styTitle.Font       = new Font(_c1xl.DefaultFont.Name, 15, FontStyle.Bold);
            _styTitle.ForeColor  = Color.Blue;
            _styHeader.Font      = new Font(_c1xl.DefaultFont, FontStyle.Bold);
            _styHeader.ForeColor = Color.White;
            _styHeader.BackColor = Color.DarkGray;
            _styMoney.Format     = XLStyle.FormatDotNetToXL("c");
            _styOrder.Font       = _styHeader.Font;
            _styOrder.ForeColor  = Color.Red;

            //create report with one sheet per category
            DataTable dt = GetCategories();

            foreach (DataRow dr in dt.Rows)
            {
                CreateSheet(dr);
            }

            //save xls file
            string uid      = System.Guid.NewGuid().ToString();
            string filename = Server.MapPath("~") + "\\Temp\\testexcel" + uid + ".xls";

            _c1xl.Save(filename);

            return(filename);
        }
예제 #5
0
        private void _btCreate_Click(object sender, RoutedEventArgs e)
        {
            // create new workbook
            if (_book == null)
            {
                _book = new C1XLBook();
            }

            // clear the book
            _book.Clear();

            // first sheet
            XLSheet sheet = _book.Sheets[0];

            // column width in twips
            sheet.Columns[0].Width = 2000;
            sheet.Columns[1].Width = 2200;

            // string formulas
            string s = "String:";

            sheet[0, 0].Value = s;
            sheet[1, 0].Value = s;
            sheet[2, 0].Value = s;

            sheet[0, 1].Value = "apples";
            sheet[1, 1].Value = "and";
            sheet[2, 1].Value = "oranges";

            s = "String formula:";
            sheet[4, 0].Value = s;
            sheet[5, 0].Value = s;

            sheet[4, 1].Value   = "apples and oranges";
            sheet[5, 1].Value   = "apples an";
            sheet[4, 1].Formula = "CONCATENATE(B1,\" \",B2, \" \",B3)";
            sheet[5, 1].Formula = "LEFT(B5,9)";

            // simple formulas
            sheet[7, 0].Value   = "Formula: 5!";
            sheet[7, 1].Value   = 120;
            sheet[7, 1].Formula = "1*2*3*4*5";

            sheet[8, 0].Value   = "Formula: 12/0";
            sheet[8, 1].Value   = 0;
            sheet[8, 1].Formula = "12/0";

            sheet[9, 0].Value   = "Formula: 1 = 1";
            sheet[9, 1].Value   = true;
            sheet[9, 1].Formula = "1=1";

            sheet[10, 0].Value   = "Formula: 1 = 2";
            sheet[10, 1].Value   = false;
            sheet[10, 1].Formula = "1 = 2";

            // now function
            sheet[12, 0].Value   = "Formula: Now()";
            sheet[12, 1].Value   = DateTime.Now;
            sheet[12, 1].Formula = "Now()";

            XLStyle            style = new XLStyle(_book);
            DateTimeFormatInfo dtfi  = CultureInfo.CurrentCulture.DateTimeFormat;

            style.Format       = XLStyle.FormatDotNetToXL(dtfi.ShortDatePattern + " " + dtfi.ShortTimePattern);
            sheet[12, 1].Style = style;

            // allow save the file
            _lblStatus.Text    = "You can save workbook";
            _btnSave.IsEnabled = true;
        }
예제 #6
0
        public void DoExport()
        {
            int rBillNo = 0;
            int cBillNo = 1;

            int rBillDate = 0;
            int cBillDate = 10;

            int rBillMonth = 2;
            int cBillMonth = 0;

            int rContact = 5;
            int cContact = 3;

            int rCustomer = 5;
            int cCustomer = 7;

            int rContract = 7;
            int cContract = 1;

            int rRate = 11;
            int cRate = 9;

            int rRateDate = 11;
            int cRateDate = 12;

            int rRent = 15;

            int rManager = 23;

            int rParking = 31;

            int rExtra = 39;

            int rElec = 47;

            int rWater = 55;

            int rService = 63;

            int rPaid = 70;

            int rDept = 77;

            int rOffice = 88;
            int cOffice = 3;

            int rPhone = 89;
            int cPhone = 3;

            int rBank = 88;
            int cBank = 7;

            int rAccountName = 89;
            int cAccountName = 7;

            int rAccount = 90;
            int cAccount = 7;

            int rSum = 81;
            int cSum = 12;

            int rSumVND = 80;
            int cSumVND = 12;

            int rSumRead = 82;
            int cSumRead = 13;

            ////
            using (SqlDatabase db = new SqlDatabase())
            {

                DataSet ds = new DataSet();
                DataSet dsCus = new DataSet();

                string sql = string.Empty;

                string Bank = "";
                string Account = "";
                string AccountName = "";
                string Office = "";
                string OfficeAddress = "";
                string OfficePhone = "";

                ds = new DataSet();
                sql = " SELECT Bank,Account,AccountName,Office,OfficeAddress,OfficePhone";
                sql += " FROM Mst_Building";
                sql += " WHERE BuildingId = '" + sBuildingId + "' ";
                using (SqlCommand cm = db.CreateCommand(sql))
                {
                    SqlDataAdapter da = new SqlDataAdapter(cm);
                    da.Fill(ds);

                    if (ds != null)
                    {
                        DataTable dt = ds.Tables[0];
                        foreach (DataRow rowType in dt.Rows)
                        {
                            Bank = rowType["Bank"].ToString();
                            Account = rowType["Account"].ToString();
                            AccountName = rowType["AccountName"].ToString();
                            Office = rowType["Office"].ToString();
                            OfficeAddress = rowType["OfficeAddress"].ToString();
                            OfficePhone = rowType["OfficePhone"].ToString();
                        }
                    }
                }

                //Danh sách Bill
                sql = "  Select BillDate,UsdExchangeDate,UsdExchange,BillNo,B.Name,B.ContactName,A.CustomerId, A.YearMonths, A.Id ";
                sql += " From	PaymentBillInfo A, Customer B";
                sql += " Where	A.BuildingId = B.BuildingId and A.CustomerId = B.CustomerId and B.DelFlag = 0 and A.BuildingId = '" + sBuildingId + "' and YearMonth = '" + sYearMonth + "'";

                string BillDate = "";
                string UsdExchangeDate = "";
                string UsdExchange = "";
                string BillNo = "";
                string Name = "";
                string ContactName = "";
                string CustomerId = "";
                string lsYearmonth = "";
                string id = "";

                string maxYearMonth = "";

                using (SqlCommand cmCus = db.CreateCommand(sql))
                {
                    SqlDataAdapter daCus = new SqlDataAdapter(cmCus);
                    daCus.Fill(dsCus);

                    if (dsCus != null)
                    {
                        string strDT = DateTime.Now.ToString("yyyyMMddHHmmss");

                        DataTable dtCus = dsCus.Tables[0];
                        foreach (DataRow rowCus in dtCus.Rows)
                        {
                            BillDate = rowCus[0].ToString();
                            UsdExchangeDate = rowCus[1].ToString();
                            UsdExchange = rowCus[2].ToString();
                            BillNo = rowCus[3].ToString();
                            Name = rowCus[4].ToString();
                            ContactName = rowCus[5].ToString();
                            CustomerId = rowCus[6].ToString();
                            lsYearmonth = rowCus[7].ToString();
                            id = rowCus[8].ToString();

                            string[] strTmpYearMonth = lsYearmonth.Split(',');

                            for (int l = 0; l < strTmpYearMonth.Length; l++)
                            {
                                string tmp = strTmpYearMonth[l];
                                if (maxYearMonth == "")
                                    maxYearMonth = tmp;
                                if (maxYearMonth.CompareTo(tmp) < 0)
                                    maxYearMonth = tmp;
                            }

                            C1XLBook xlbBook = new C1XLBook();
                            //ShowData(drpYear.SelectedValue + drpMonth.SelectedValue);
                            XLStyle xlstStyle = new XLStyle(xlbBook);
                            xlstStyle.AlignHorz = XLAlignHorzEnum.Center;
                            xlstStyle.AlignVert = XLAlignVertEnum.Center;
                            xlstStyle.WordWrap = true;
                            xlstStyle.Font = new Font("", 8, FontStyle.Regular);
                            xlstStyle.SetBorderColor(Color.Black);
                            xlstStyle.BorderBottom = XLLineStyleEnum.Thin;
                            xlstStyle.BorderTop = XLLineStyleEnum.Thin;
                            xlstStyle.BorderLeft = XLLineStyleEnum.Thin;
                            xlstStyle.BorderRight = XLLineStyleEnum.Thin;
                            xlstStyle.Format = "#,##0.00_);(#,##0.00)";

                            XLStyle xlstStyleH = new XLStyle(xlbBook);
                            xlstStyleH.AlignHorz = XLAlignHorzEnum.Center;
                            xlstStyleH.AlignVert = XLAlignVertEnum.Center;
                            xlstStyleH.Font = new Font("", 8, FontStyle.Bold);
                            xlstStyleH.SetBorderColor(Color.Black);
                            xlstStyleH.BorderBottom = XLLineStyleEnum.Thin;
                            xlstStyleH.BorderTop = XLLineStyleEnum.Thin;
                            xlstStyleH.BorderLeft = XLLineStyleEnum.Thin;
                            xlstStyleH.BorderRight = XLLineStyleEnum.Thin;
                            xlstStyleH.WordWrap = true;

                            XLStyle xlstStyleSum = new XLStyle(xlbBook);
                            xlstStyleSum.AlignHorz = XLAlignHorzEnum.Right;
                            xlstStyleSum.AlignVert = XLAlignVertEnum.Center;
                            xlstStyleSum.Font = new Font("", 8, FontStyle.Bold);
                            xlstStyleSum.SetBorderColor(Color.Black);
                            xlstStyleSum.BorderBottom = XLLineStyleEnum.Thin;
                            xlstStyleSum.BorderTop = XLLineStyleEnum.Thin;
                            xlstStyleSum.BorderLeft = XLLineStyleEnum.Thin;
                            xlstStyleSum.BorderRight = XLLineStyleEnum.Thin;
                            xlstStyleSum.WordWrap = true;

                            string fileName = sFilePath + @"\Template\BillTongQuat.xlsx";
                            if (!Directory.Exists(sFilePath + @"\Building\" + sBuildingId + @"\Bill"))
                            {
                                Directory.CreateDirectory(sFilePath + @"\Report\Building\" + sBuildingId + @"\Bill");
                            }

                            string strFilePath = sFilePath + @"\Building\" + sBuildingId + @"\Bill\Bill" + "_" + CustomerId + "_" + BillNo + id + "_" + strDT + ".xlsx";
                            string strFilePathExport = sFilePath.Replace(@"\", "/") + @"/Building/" + sBuildingId + @"/Bill/Bill" + "_" + CustomerId + "_" + BillNo + id + "_" + strDT + ".xlsx";

                            string fileNameDes = strFilePath;

                            //string fileNameDes = HttpContext.Current.Server.MapPath(@"~\Report\Building\" + sBuildingId + @"\TongHopDienTich" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
                            File.Copy(fileName, fileNameDes);

                            xlbBook.Load(fileNameDes);
                            XLSheet xlsSheet = xlbBook.Sheets["TongHop"];
                            XLSheet xlsSheetEn = xlbBook.Sheets["TongHop_En"];

                            //Thông tin về Ngân hàng của Tòa Nhà
                            xlsSheet[rOffice, cOffice].Value = xlsSheet[rOffice, cOffice].Value.ToString().Replace("{%VAN_PHONG%}", Office);
                            xlsSheet[rPhone, cPhone].Value = xlsSheet[rPhone, cPhone].Value.ToString().Replace("{%DIEN_THOAI%}", OfficePhone);

                            xlsSheet[rBank, cBank].Value = xlsSheet[rBank, cBank].Value.ToString().Replace("{%NGAN_HANG%}", Bank);
                            xlsSheet[rAccountName, cAccountName].Value = xlsSheet[rAccountName, cAccountName].Value.ToString().Replace("{%TEN_TAI_KHOAN%}", AccountName);
                            xlsSheet[rAccount, cAccount].Value = xlsSheet[rAccount, cAccount].Value.ToString().Replace("{%SO_TAI_KHOAN%}", Account);
                            //Thông tin về Ngân hàng của Tòa Nhà

                            //Customer
                            xlsSheet[rCustomer, cCustomer].Value = xlsSheet[rCustomer, cCustomer].Value.ToString().Replace("{%TEN_CONG_TY%}", Name);
                            //Contact
                            xlsSheet[rContact, cContact].Value = xlsSheet[rContact, cContact].Value.ToString().Replace("{%NGUOI_DAI_DIEN%}", ContactName);

                            //Bill No
                            xlsSheet[rBillNo, cBillNo].Value = xlsSheet[rBillNo, cBillNo].Value.ToString().Replace("{%BILL_NO%}", BillNo);

                            ///////////////////////////////////////////
                            //Thông tin về Ngân hàng của Tòa Nhà
                            xlsSheetEn[rOffice, cOffice].Value = xlsSheetEn[rOffice, cOffice].Value.ToString().Replace("{%VAN_PHONG%}", Office);
                            xlsSheetEn[rPhone, cPhone].Value = xlsSheetEn[rPhone, cPhone].Value.ToString().Replace("{%DIEN_THOAI%}", OfficePhone);

                            xlsSheetEn[rBank, cBank].Value = xlsSheetEn[rBank, cBank].Value.ToString().Replace("{%NGAN_HANG%}", Bank);
                            xlsSheetEn[rAccountName, cAccountName].Value = xlsSheetEn[rAccountName, cAccountName].Value.ToString().Replace("{%TEN_TAI_KHOAN%}", AccountName);
                            xlsSheetEn[rAccount, cAccount].Value = xlsSheetEn[rAccount, cAccount].Value.ToString().Replace("{%SO_TAI_KHOAN%}", Account);
                            //Thông tin về Ngân hàng của Tòa Nhà

                            //Customer
                            xlsSheetEn[rCustomer, cCustomer].Value = xlsSheetEn[rCustomer, cCustomer].Value.ToString().Replace("{%TEN_CONG_TY%}", Name);
                            //Contact
                            xlsSheetEn[rContact, cContact].Value = xlsSheetEn[rContact, cContact].Value.ToString().Replace("{%NGUOI_DAI_DIEN%}", ContactName);

                            //Bill No
                            xlsSheetEn[rBillNo, cBillNo].Value = xlsSheetEn[rBillNo, cBillNo].Value.ToString().Replace("{%BILL_NO%}", BillNo);
                            ///////////////////////////////////////////
                            //Ngay Thang Nam
                            DateTime dtime = DateTime.Today;

                            string strTmp = xlsSheet[rBillDate, cBillDate].Value.ToString().Replace("{%NGAY%}", dtime.ToString("dd"));
                            strTmp = strTmp.Replace("{%THANG%}", dtime.ToString("MM"));
                            xlsSheet[rBillDate, cBillDate].Value = strTmp.Replace("{%NAM%}", dtime.ToString("yyyy"));

                            strTmp = xlsSheetEn[rBillDate, cBillDate].Value.ToString().Replace("{%NGAY%}", dtime.ToString("dd"));
                            strTmp = strTmp.Replace("{%THANG%}", dtime.ToString("MM"));
                            xlsSheetEn[rBillDate, cBillDate].Value = strTmp.Replace("{%NAM%}", dtime.ToString("yyyy"));

                            //Nam
                            xlsSheet[rBillMonth, cBillMonth].Value = xlsSheet[rBillMonth, cBillMonth].Value.ToString().Replace("{%NAM_THANG%}", sMonth + "/" + sYear);
                            xlsSheetEn[rBillMonth, cBillMonth].Value = xlsSheetEn[rBillMonth, cBillMonth].Value.ToString().Replace("{%NAM_THANG%}", sMonth + "/" + sYear);

                            //Thông tin Tỉ giá
                            xlsSheet[rRate, cRate].Value = xlsSheet[rRate, cRate].Value.ToString().Replace("{%TI_GIA%}", UsdExchange);
                            xlsSheet[rRateDate, cRateDate].Value = xlsSheet[rRateDate, cRateDate].Value.ToString().Replace("{%NGAY_AP_DUNG%}", UsdExchangeDate);
                            //Thông tin Tỉ giá
                            xlsSheetEn[rRate, cRate].Value = xlsSheetEn[rRate, cRate].Value.ToString().Replace("{%TI_GIA%}", UsdExchange);
                            xlsSheetEn[rRateDate, cRateDate].Value = xlsSheetEn[rRateDate, cRateDate].Value.ToString().Replace("{%NGAY_AP_DUNG%}", UsdExchangeDate);

                            Hashtable contractIdLst = new Hashtable();
                            string contract = "";
                            ////

                            //Thue phong
                            ds = new DataSet();
                            sql = " Select A.*, B.ContractDate";
                            sql += " FROM PaymentRoom A, RentContract B";
                            sql += " WHERE A.ContractId = B.ContractId and A.BuildingId = B.BuildingId and A.BuildingId = '" + sBuildingId + "' and A.CustomerId = '" + CustomerId + "' and A.YearMonth in (" + lsYearmonth + ")";

                            int sumRow = 0;
                            int j = 0;
                            decimal[] LastSumPriceVND = new decimal[7] { 0, 0, 0, 0, 0, 0, 0 };
                            decimal[] LastSumPriceUSD = new decimal[7] { 0, 0, 0, 0, 0, 0, 0 };

                            decimal PaidPriceVND = 0;
                            decimal PaidPriceUSD = 0;

                            int line = 0;
                            using (SqlCommand cm = db.CreateCommand(sql))
                            {
                                SqlDataAdapter da = new SqlDataAdapter(cm);
                                da.Fill(ds);

                                line = rRent - 3 + j;

                                XLCellRange mCell = new XLCellRange(line, line + 2, 1, 3);
                                xlsSheet.MergedCells.Add(mCell);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 4, 5);
                                xlsSheet.MergedCells.Add(mCell);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 4, 5);
                                xlsSheet.MergedCells.Add(mCell);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 2, line + 2, 4, 5);
                                xlsSheet.MergedCells.Add(mCell);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 6, 7);
                                xlsSheet.MergedCells.Add(mCell);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 6, 7);
                                xlsSheet.MergedCells.Add(mCell);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 8, 9);
                                xlsSheet.MergedCells.Add(mCell);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 8, 9);
                                xlsSheet.MergedCells.Add(mCell);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 10, 11);
                                xlsSheet.MergedCells.Add(mCell);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 10, 11);
                                xlsSheet.MergedCells.Add(mCell);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 12, 13);
                                xlsSheet.MergedCells.Add(mCell);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 12, 13);
                                xlsSheet.MergedCells.Add(mCell);
                                xlsSheetEn.MergedCells.Add(mCell);

                                if (ds != null)
                                {
                                    int count = 0;
                                    DataTable dt = ds.Tables[0];
                                    int k = 0;
                                    foreach (DataRow rowType in dt.Rows)
                                    {
                                        decimal tmp01 = Convert.ToDecimal(rowType["LastRentSumUSD"]);
                                        decimal tmp02 = Convert.ToDecimal(rowType["LastRentSumVND"]);

                                        string ContractId = Func.ParseString(rowType["ContractId"]);
                                        string ContractNo = Func.ParseString(rowType["ContractNo"]);
                                        string YearMonth = Func.ParseString(rowType["YearMonth"]);
                                        string Area = Func.ParseString(rowType["Area"]);
                                        string Regional = Func.ParseString(rowType["Regional"]);
                                        string Floor = Func.ParseString(rowType["Floor"]);
                                        string BeginContract = Func.ParseString(rowType["ContractDate"]);

                                        if (!contractIdLst.ContainsKey(ContractId + "(" + Func.FormatDMY(BeginContract) + ")"))
                                        {
                                            contractIdLst.Add(ContractId + "(" + Func.FormatDMY(BeginContract) + ")", ContractNo + "(" + Func.FormatDMY(BeginContract) + ")");
                                            contract += ";" + ContractNo + "(" + Func.FormatDMY(BeginContract) + ")";
                                        }
                                        if (tmp01 > 0 || tmp02 > 0)
                                        {

                                            if (count >= 1)
                                            {
                                                xlsSheet.Rows.Insert(rRent + 1 + j);
                                                xlsSheetEn.Rows.Insert(rRent + 1 + j);
                                                j++;
                                            }
                                            count++;
                                            int tmp = rRent + j;
                                            xlsSheet[tmp, 1].Value = Name;
                                            xlsSheet[tmp, 4].Value = rowType["Area"];
                                            xlsSheet[tmp, 6].Value = rowType["MonthRentPriceUSD"];
                                            xlsSheet[tmp, 7].Value = rowType["MonthRentPriceVND"];

                                            xlsSheet[tmp, 8].Value = rowType["MonthRentSumUSD"];
                                            xlsSheet[tmp, 9].Value = rowType["MonthRentSumVND"];

                                            xlsSheet[tmp, 10].Value = rowType["VatRentPriceUSD"];
                                            xlsSheet[tmp, 11].Value = rowType["VatRentPriceVND"];

                                            xlsSheet[tmp, 12].Value = rowType["LastRentSumUSD"];
                                            xlsSheet[tmp, 13].Value = rowType["LastRentSumVND"];

                                            XLCellRange mrCell = new XLCellRange(tmp, tmp, 1, 3);
                                            xlsSheet.MergedCells.Add(mrCell);

                                            mrCell = new XLCellRange(tmp, tmp, 4, 5);
                                            xlsSheet.MergedCells.Add(mrCell);

                                            ////EN
                                            xlsSheetEn[tmp, 1].Value = Name;
                                            xlsSheetEn[tmp, 4].Value = rowType["Area"];
                                            xlsSheetEn[tmp, 6].Value = rowType["MonthRentPriceUSD"];
                                            xlsSheetEn[tmp, 7].Value = rowType["MonthRentPriceVND"];

                                            xlsSheetEn[tmp, 8].Value = rowType["MonthRentSumUSD"];
                                            xlsSheetEn[tmp, 9].Value = rowType["MonthRentSumVND"];

                                            xlsSheetEn[tmp, 10].Value = rowType["VatRentPriceUSD"];
                                            xlsSheetEn[tmp, 11].Value = rowType["VatRentPriceVND"];

                                            xlsSheetEn[tmp, 12].Value = rowType["LastRentSumUSD"];
                                            xlsSheetEn[tmp, 13].Value = rowType["LastRentSumVND"];

                                            mrCell = new XLCellRange(tmp, tmp, 1, 3);
                                            xlsSheetEn.MergedCells.Add(mrCell);

                                            mrCell = new XLCellRange(tmp, tmp, 4, 5);
                                            xlsSheetEn.MergedCells.Add(mrCell);
                                            ////EN

                                            LastSumPriceVND[0] += Convert.ToDecimal(rowType["LastRentSumVND"]);
                                            LastSumPriceUSD[0] += Convert.ToDecimal(rowType["LastRentSumUSD"]);
                                        }
                                        else
                                        {
                                            k++;
                                        }
                                    }
                                    mCell = new XLCellRange(rRent + 1 + j, rRent + 1 + j, 1, 11);
                                    xlsSheet.MergedCells.Add(mCell);
                                    xlsSheetEn.MergedCells.Add(mCell);

                                    xlsSheet[rRent + 1 + j, 12].Value = LastSumPriceUSD[0];
                                    xlsSheet[rRent + 1 + j, 13].Value = LastSumPriceVND[0];

                                    xlsSheetEn[rRent + 1 + j, 12].Value = LastSumPriceUSD[0];
                                    xlsSheetEn[rRent + 1 + j, 13].Value = LastSumPriceVND[0];

                                    for (int row = rRent + sumRow; row <= rRent + dt.Rows.Count - k; row++)
                                    {
                                        for (int col = 1; col <= 13; col++)
                                        {
                                            xlsSheet[row, col].Style = xlstStyle;
                                            xlsSheetEn[row, col].Style = xlstStyle;
                                        }
                                    }
                                    sumRow += dt.Rows.Count - 1 - k;

                                    ////////////////////////
                                    for (int col = 1; col <= 13; col++)
                                    {
                                        xlsSheet[rRent + 1 + j, col].Style = xlstStyleSum;
                                        xlsSheetEn[rRent + 1 + j, col].Style = xlstStyleSum;
                                    }
                                    line = rManager - 3 + j;
                                    mCell = new XLCellRange(line, line + 2, 1, 3);
                                    xlsSheet.MergedCells.Add(mCell);

                                    mCell = new XLCellRange(line, line, 4, 5);
                                    xlsSheet.MergedCells.Add(mCell);

                                    mCell = new XLCellRange(line + 1, line + 1, 4, 5);
                                    xlsSheet.MergedCells.Add(mCell);

                                    mCell = new XLCellRange(line + 2, line + 2, 4, 5);
                                    xlsSheet.MergedCells.Add(mCell);

                                    mCell = new XLCellRange(line, line, 6, 7);
                                    xlsSheet.MergedCells.Add(mCell);

                                    mCell = new XLCellRange(line + 1, line + 1, 6, 7);
                                    xlsSheet.MergedCells.Add(mCell);

                                    mCell = new XLCellRange(line, line, 8, 9);
                                    xlsSheet.MergedCells.Add(mCell);

                                    mCell = new XLCellRange(line + 1, line + 1, 8, 9);
                                    xlsSheet.MergedCells.Add(mCell);

                                    mCell = new XLCellRange(line, line, 10, 11);
                                    xlsSheet.MergedCells.Add(mCell);

                                    mCell = new XLCellRange(line + 1, line + 1, 10, 11);
                                    xlsSheet.MergedCells.Add(mCell);

                                    mCell = new XLCellRange(line, line, 12, 13);
                                    xlsSheet.MergedCells.Add(mCell);

                                    mCell = new XLCellRange(line + 1, line + 1, 12, 13);
                                    xlsSheet.MergedCells.Add(mCell);

                                    ////En
                                    mCell = new XLCellRange(line, line + 2, 1, 3);
                                    xlsSheetEn.MergedCells.Add(mCell);

                                    mCell = new XLCellRange(line, line, 4, 5);
                                    xlsSheetEn.MergedCells.Add(mCell);

                                    mCell = new XLCellRange(line + 1, line + 1, 4, 5);
                                    xlsSheetEn.MergedCells.Add(mCell);

                                    mCell = new XLCellRange(line + 2, line + 2, 4, 5);
                                    xlsSheetEn.MergedCells.Add(mCell);

                                    mCell = new XLCellRange(line, line, 6, 7);
                                    xlsSheetEn.MergedCells.Add(mCell);

                                    mCell = new XLCellRange(line + 1, line + 1, 6, 7);
                                    xlsSheetEn.MergedCells.Add(mCell);

                                    mCell = new XLCellRange(line, line, 8, 9);
                                    xlsSheetEn.MergedCells.Add(mCell);

                                    mCell = new XLCellRange(line + 1, line + 1, 8, 9);
                                    xlsSheetEn.MergedCells.Add(mCell);

                                    mCell = new XLCellRange(line, line, 10, 11);
                                    xlsSheetEn.MergedCells.Add(mCell);

                                    mCell = new XLCellRange(line + 1, line + 1, 10, 11);
                                    xlsSheetEn.MergedCells.Add(mCell);

                                    mCell = new XLCellRange(line, line, 12, 13);
                                    xlsSheetEn.MergedCells.Add(mCell);

                                    mCell = new XLCellRange(line + 1, line + 1, 12, 13);
                                    xlsSheetEn.MergedCells.Add(mCell);
                                    ////En
                                    k = 0;
                                    count = 0;
                                    foreach (DataRow row in dt.Rows)
                                    {
                                        decimal tmp01 = Convert.ToDecimal(row["LastManagerSumUSD"]);
                                        decimal tmp02 = Convert.ToDecimal(row["LastManagerSumVND"]);

                                        if (tmp01 > 0 || tmp02 > 0)
                                        {

                                            if (count >= 1)
                                            {
                                                xlsSheet.Rows.Insert(rManager + 1 + j);
                                                xlsSheetEn.Rows.Insert(rManager + 1 + j);
                                                j++;
                                            }
                                            count++;
                                            int tmp = rManager + j;

                                            string YearMonth = Func.ParseString(row["YearMonth"]);
                                            string Area = Func.ParseString(row["Area"]);

                                            xlsSheet[tmp, 1].Value = Name;
                                            xlsSheet[tmp, 4].Value = row["Area"];
                                            xlsSheet[tmp, 6].Value = row["MonthManagerPriceUSD"];
                                            xlsSheet[tmp, 7].Value = row["MonthManagerPriceVND"];

                                            xlsSheet[tmp, 8].Value = row["MonthManagerSumUSD"];
                                            xlsSheet[tmp, 9].Value = row["MonthManagerSumVND"];

                                            xlsSheet[tmp, 10].Value = row["VatManagerPriceUSD"];
                                            xlsSheet[tmp, 11].Value = row["VatManagerPriceVND"];

                                            xlsSheet[tmp, 12].Value = row["LastManagerSumUSD"];
                                            xlsSheet[tmp, 13].Value = row["LastManagerSumVND"];

                                            XLCellRange mrCell = new XLCellRange(tmp, tmp, 1, 3);
                                            xlsSheet.MergedCells.Add(mrCell);

                                            mrCell = new XLCellRange(tmp, tmp, 4, 5);
                                            xlsSheet.MergedCells.Add(mrCell);

                                            ////En
                                            xlsSheetEn[tmp, 1].Value = Name;
                                            xlsSheetEn[tmp, 4].Value = row["Area"];
                                            xlsSheetEn[tmp, 6].Value = row["MonthManagerPriceUSD"];
                                            xlsSheetEn[tmp, 7].Value = row["MonthManagerPriceVND"];

                                            xlsSheetEn[tmp, 8].Value = row["MonthManagerSumUSD"];
                                            xlsSheetEn[tmp, 9].Value = row["MonthManagerSumVND"];

                                            xlsSheetEn[tmp, 10].Value = row["VatManagerPriceUSD"];
                                            xlsSheetEn[tmp, 11].Value = row["VatManagerPriceVND"];

                                            xlsSheetEn[tmp, 12].Value = row["LastManagerSumUSD"];
                                            xlsSheetEn[tmp, 13].Value = row["LastManagerSumVND"];

                                            mrCell = new XLCellRange(tmp, tmp, 1, 3);
                                            xlsSheetEn.MergedCells.Add(mrCell);

                                            mrCell = new XLCellRange(tmp, tmp, 4, 5);
                                            xlsSheetEn.MergedCells.Add(mrCell);
                                            ////En
                                            LastSumPriceVND[1] += Convert.ToDecimal(row["LastManagerSumVND"]);
                                            LastSumPriceUSD[1] += Convert.ToDecimal(row["LastManagerSumUSD"]);
                                        }
                                        else { k++; }
                                    }
                                    mCell = new XLCellRange(rManager + 1 + j, rManager + 1 + j, 1, 11);
                                    xlsSheet.MergedCells.Add(mCell);
                                    xlsSheetEn.MergedCells.Add(mCell);

                                    xlsSheet[rManager + 1 + j, 12].Value = LastSumPriceUSD[1];
                                    xlsSheet[rManager + 1 + j, 13].Value = LastSumPriceVND[1];

                                    xlsSheetEn[rManager + 1 + j, 12].Value = LastSumPriceUSD[1];
                                    xlsSheetEn[rManager + 1 + j, 13].Value = LastSumPriceVND[1];

                                    for (int row = rManager + sumRow; row <= rManager + sumRow + dt.Rows.Count - k; row++)
                                    {
                                        for (int col = 1; col <= 13; col++)
                                        {
                                            xlsSheet[row, col].Style = xlstStyle;
                                            xlsSheetEn[row, col].Style = xlstStyle;
                                        }
                                    }

                                    for (int col = 1; col <= 13; col++)
                                    {
                                        xlsSheet[rManager + 1 + j, col].Style = xlstStyleSum;
                                        xlsSheetEn[rManager + 1 + j, col].Style = xlstStyleSum;
                                    }
                                    sumRow += dt.Rows.Count - 1 - k;
                                }
                            }

                            ds = new DataSet();
                            //Xuất ra toàn bộ nội dung theo Trang
                            sql = " SELECT COUNT(*) AS Num, YearMonth, TariffsParkingName, PriceVND, PriceUSD, SUM(VatVND) AS VatVND,SUM(VatUSD) AS VatUSD, SUM(SumVND) AS SumVND, SUM(SumUSD) AS SumUSD, SUM(LastPriceVND) AS LastPriceVND";
                            sql += "        , SUM(LastPriceUSD) AS LastPriceUSD";
                            sql += " FROM         dbo.PaymentParking";
                            sql += " WHERE BuildingId = '" + sBuildingId + "' and CustomerId = '" + CustomerId + "' and YearMonth in (" + lsYearmonth + ")";

                            sql += " GROUP BY YearMonth, TariffsParkingName, PriceVND, PriceUSD, Vat, daysParking";
                            sql += " HAVING (SUM(LastPriceVND) >0 or SUM(LastPriceUSD) >0)";
                            using (SqlCommand cm = db.CreateCommand(sql))
                            {
                                SqlDataAdapter da = new SqlDataAdapter(cm);
                                da.Fill(ds);

                                line = rParking - 3 + j;
                                XLCellRange mCell = new XLCellRange(line, line + 2, 1, 3);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 4, 5);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 4, 5);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 2, line + 2, 4, 5);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 6, 7);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 6, 7);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 8, 9);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 8, 9);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 10, 11);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 10, 11);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 12, 13);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 12, 13);
                                xlsSheet.MergedCells.Add(mCell);

                                ////En
                                mCell = new XLCellRange(line, line + 2, 1, 3);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 4, 5);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 4, 5);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 2, line + 2, 4, 5);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 6, 7);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 6, 7);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 8, 9);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 8, 9);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 10, 11);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 10, 11);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 12, 13);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 12, 13);
                                xlsSheetEn.MergedCells.Add(mCell);
                                ////En
                                if (ds != null)
                                {
                                    int count = 0;
                                    DataTable dt = ds.Tables[0];

                                    foreach (DataRow row in dt.Rows)
                                    {
                                        if (count >= 1)
                                        {
                                            xlsSheet.Rows.Insert(rParking + 1 + j);
                                            xlsSheetEn.Rows.Insert(rParking + 1 + j);
                                            j++;
                                        }
                                        count++;
                                        int tmp = rParking + j;

                                        string Num = Func.ParseString(row["Num"]);
                                        string TariffsParkingName = Func.ParseString(row["TariffsParkingName"]);

                                        xlsSheet[tmp, 1].Value = TariffsParkingName;
                                        xlsSheet[tmp, 4].Value = Num;
                                        xlsSheet[tmp, 6].Value = row["PriceUSD"];
                                        xlsSheet[tmp, 7].Value = row["PriceVND"];

                                        xlsSheet[tmp, 8].Value = row["SumUSD"];
                                        xlsSheet[tmp, 9].Value = row["SumVND"];

                                        xlsSheet[tmp, 10].Value = row["VatUSD"];
                                        xlsSheet[tmp, 11].Value = row["VatVND"];

                                        xlsSheet[tmp, 12].Value = row["LastPriceUSD"];
                                        xlsSheet[tmp, 13].Value = row["LastPriceVND"];

                                        XLCellRange mrCell = new XLCellRange(tmp, tmp, 1, 3);
                                        xlsSheet.MergedCells.Add(mrCell);

                                        mrCell = new XLCellRange(tmp, tmp, 4, 5);
                                        xlsSheet.MergedCells.Add(mrCell);

                                        /////En
                                        xlsSheetEn[tmp, 1].Value = TariffsParkingName;
                                        xlsSheetEn[tmp, 4].Value = Num;
                                        xlsSheetEn[tmp, 6].Value = row["PriceUSD"];
                                        xlsSheetEn[tmp, 7].Value = row["PriceVND"];

                                        xlsSheetEn[tmp, 8].Value = row["SumUSD"];
                                        xlsSheetEn[tmp, 9].Value = row["SumVND"];

                                        xlsSheetEn[tmp, 10].Value = row["VatUSD"];
                                        xlsSheetEn[tmp, 11].Value = row["VatVND"];

                                        xlsSheetEn[tmp, 12].Value = row["LastPriceUSD"];
                                        xlsSheetEn[tmp, 13].Value = row["LastPriceVND"];

                                        mrCell = new XLCellRange(tmp, tmp, 1, 3);
                                        xlsSheetEn.MergedCells.Add(mrCell);

                                        mrCell = new XLCellRange(tmp, tmp, 4, 5);
                                        xlsSheetEn.MergedCells.Add(mrCell);
                                        /////En
                                        LastSumPriceVND[2] += Convert.ToDecimal(row["LastPriceVND"]);
                                        LastSumPriceUSD[2] += Convert.ToDecimal(row["LastPriceUSD"]);
                                    }
                                    xlsSheet[rParking + 1 + j, 12].Value = LastSumPriceUSD[2];
                                    xlsSheet[rParking + 1 + j, 13].Value = LastSumPriceVND[2];

                                    mCell = new XLCellRange(rParking + 1 + j, rParking + 1 + j, 1, 11);
                                    xlsSheet.MergedCells.Add(mCell);

                                    /////En
                                    xlsSheetEn[rParking + 1 + j, 12].Value = LastSumPriceUSD[2];
                                    xlsSheetEn[rParking + 1 + j, 13].Value = LastSumPriceVND[2];

                                    mCell = new XLCellRange(rParking + 1 + j, rParking + 1 + j, 1, 11);
                                    xlsSheetEn.MergedCells.Add(mCell);
                                    /////En

                                    for (int row = rParking + sumRow; row <= rParking + sumRow + dt.Rows.Count; row++)
                                    {
                                        for (int col = 1; col <= 13; col++)
                                        {
                                            xlsSheet[row, col].Style = xlstStyle;
                                            xlsSheetEn[row, col].Style = xlstStyle;
                                        }
                                    }

                                    for (int col = 1; col <= 13; col++)
                                    {
                                        xlsSheet[rParking + 1 + j, col].Style = xlstStyleSum;
                                        xlsSheetEn[rParking + 1 + j, col].Style = xlstStyleSum;
                                    }
                                    sumRow += dt.Rows.Count - 1;
                                }
                            }

                            ds = new DataSet();
                            sql = "SELECT id";
                            sql += " ,YearMonth,BuildingId,CustomerId,RoomId,ExtraHour,VAT,OtherFee01,OtherFee02";
                            sql += " ,PriceUSD,PriceVND,VatUSD,VatVND,SumUSD,SumVND,IsNull(LastPriceUSD,0) LastPriceUSD      ,IsNull(LastPriceVND,0) LastPriceVND ";
                            sql += " ,RentArea,dbo.fnDateTime(FromWD) BeginDate,dbo.fnDateTime(EndWD) EndDate,ExtratimeType";
                            sql += " FROM PaymentExtraTimeMonth";
                            sql += " WHERE BuildingId = '" + sBuildingId + "' and CustomerId = '" + CustomerId + "' and YearMonth in (" + lsYearmonth + ")";

                            using (SqlCommand cm = db.CreateCommand(sql))
                            {
                                SqlDataAdapter da = new SqlDataAdapter(cm);
                                da.Fill(ds);
                                line = rExtra - 3 + j;
                                //Phi dien
                                XLCellRange mCell = new XLCellRange(line, line + 2, 1, 3);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line + 2, 4, 4);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 6, 7);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 6, 7);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 8, 9);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 8, 9);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 10, 11);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 10, 11);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 12, 13);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 12, 13);
                                xlsSheet.MergedCells.Add(mCell);

                                /////En
                                mCell = new XLCellRange(line, line + 2, 1, 3);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line + 2, 4, 4);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 6, 7);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 6, 7);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 8, 9);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 8, 9);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 10, 11);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 10, 11);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 12, 13);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 12, 13);
                                xlsSheetEn.MergedCells.Add(mCell);
                                /////En
                                if (ds != null)
                                {
                                    int count = 0;
                                    DataTable dt = ds.Tables[0];

                                    foreach (DataRow row in dt.Rows)
                                    {
                                        if (count >= 1)
                                        {
                                            xlsSheet.Rows.Insert(rExtra + 1 + j);
                                            xlsSheetEn.Rows.Insert(rExtra + 1 + j);
                                            j++;
                                        }
                                        count++;
                                        int tmp = rExtra + j;

                                        string ExtraHour = Func.ParseString(row["ExtraHour"]);
                                        string BeginDate = Func.ParseString(row["BeginDate"]);
                                        string EndDate = Func.ParseString(row["EndDate"]);

                                        string ExtratimeType = Func.ParseString(row["ExtratimeType"]);

                                        xlsSheet[tmp, 1].Value = BeginDate + "~" + EndDate;
                                        xlsSheet[tmp, 5].Value = ExtraHour;

                                        xlsSheet[tmp, 4].Value = "Diện tích";
                                        if ("0".Equals(ExtratimeType))
                                        {
                                            xlsSheet[tmp, 4].Value = "m2*h";
                                        }
                                        xlsSheet[tmp, 6].Value = row["PriceUSD"];
                                        xlsSheet[tmp, 7].Value = row["PriceVND"];

                                        xlsSheet[tmp, 8].Value = row["SumUSD"];
                                        xlsSheet[tmp, 9].Value = row["SumVND"];

                                        xlsSheet[tmp, 10].Value = row["VatUSD"];
                                        xlsSheet[tmp, 11].Value = row["VatVND"];

                                        xlsSheet[tmp, 12].Value = row["LastPriceUSD"];
                                        xlsSheet[tmp, 13].Value = row["LastPriceVND"];

                                        LastSumPriceVND[3] += Convert.ToDecimal(row["LastPriceVND"]);
                                        LastSumPriceUSD[3] += Convert.ToDecimal(row["LastPriceUSD"]);

                                        XLCellRange mrCell = new XLCellRange(tmp, tmp, 1, 3);
                                        xlsSheet.MergedCells.Add(mrCell);
                                        //////En
                                        xlsSheetEn[tmp, 1].Value = BeginDate + "~" + EndDate;
                                        xlsSheetEn[tmp, 5].Value = ExtraHour;

                                        xlsSheetEn[tmp, 4].Value = "Di?n tích";
                                        if ("0".Equals(ExtratimeType))
                                        {
                                            xlsSheetEn[tmp, 4].Value = "m2*h";
                                        }
                                        xlsSheetEn[tmp, 6].Value = row["PriceUSD"];
                                        xlsSheetEn[tmp, 7].Value = row["PriceVND"];

                                        xlsSheetEn[tmp, 8].Value = row["SumUSD"];
                                        xlsSheetEn[tmp, 9].Value = row["SumVND"];

                                        xlsSheetEn[tmp, 10].Value = row["VatUSD"];
                                        xlsSheetEn[tmp, 11].Value = row["VatVND"];

                                        xlsSheetEn[tmp, 12].Value = row["LastPriceUSD"];
                                        xlsSheetEn[tmp, 13].Value = row["LastPriceVND"];

                                        //LastSumPriceVND[3] += Convert.ToDecimal(row["LastPriceVND"]);
                                        //LastSumPriceUSD[3] += Convert.ToDecimal(row["LastPriceUSD"]);

                                        mrCell = new XLCellRange(tmp, tmp, 1, 3);
                                        xlsSheetEn.MergedCells.Add(mrCell);
                                        //////En

                                        for (int col = 1; col <= 13; col++)
                                        {
                                            xlsSheet[tmp, col].Style = xlstStyle;
                                            xlsSheetEn[tmp, col].Style = xlstStyle;
                                        }

                                    }
                                    mCell = new XLCellRange(rExtra + 1 + j, rExtra + 1 + j, 1, 11);
                                    xlsSheet.MergedCells.Add(mCell);
                                    xlsSheetEn.MergedCells.Add(mCell);

                                    xlsSheet[rExtra + 1 + j, 12].Value = LastSumPriceUSD[3];
                                    xlsSheet[rExtra + 1 + j, 13].Value = LastSumPriceVND[3];

                                    xlsSheetEn[rExtra + 1 + j, 12].Value = LastSumPriceUSD[3];
                                    xlsSheetEn[rExtra + 1 + j, 13].Value = LastSumPriceVND[3];

                                    for (int row = rExtra + sumRow; row <= rExtra + sumRow + dt.Rows.Count; row++)
                                    {
                                        for (int col = 1; col <= 13; col++)
                                        {
                                            xlsSheet[row, col].Style = xlstStyle;
                                            xlsSheetEn[row, col].Style = xlstStyle;
                                        }
                                    }

                                    for (int col = 1; col <= 13; col++)
                                    {
                                        xlsSheet[rExtra + 1 + j, col].Style = xlstStyleSum;
                                        xlsSheetEn[rExtra + 1 + j, col].Style = xlstStyleSum;
                                    }
                                    sumRow += dt.Rows.Count - 1;
                                }
                            }

                            ds = new DataSet();
                            //Dien
                            //Xuất ra toàn bộ nội dung theo Trang
                            sql = " SELECT dbo.fnDateTime(A.DateFrom) DateFrom, dbo.fnDateTime(A.DateTo) DateTo, A.Vat, B.id, B.UsedElecWaterId, B.FromIndex, B.ToIndex, B.OtherFee01, B.OtherFee02, B.Mount, B.PriceVND, B.PriceUSD, B.SumVND, B.SumUSD, ";
                            sql += "        B.VatVND, B.VatUSD ,IsNull(B.LastPriceUSD,0) LastPriceUSD      ,IsNull(B.LastPriceVND,0) LastPriceVND , B.Name, B.WaterPricePercent,B.ElecPricePercent ";
                            sql += " FROM   PaymentElecWater AS A INNER JOIN ";
                            sql += "        PaymentElecWaterDetail AS B ON A.UsedElecWaterId = B.UsedElecWaterId";
                            sql += " WHERE A.BuildingId = '" + sBuildingId + "' and A.CustomerId = '" + CustomerId + "' and TarrifsOfWaterId = 0  and A.YearMonth in (" + lsYearmonth + ")";
                            sql += " Order by A.DateFrom, B.FromIndex";

                            using (SqlCommand cm = db.CreateCommand(sql))
                            {
                                SqlDataAdapter da = new SqlDataAdapter(cm);
                                da.Fill(ds);

                                line = rElec - 3 + j;
                                //Phi dien
                                XLCellRange mCell = new XLCellRange(line, line + 2, 1, 1);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line + 2, 2, 2);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 3, 3);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 4, 4);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 5, 5);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 7, 7);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 8, 8);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 9, 9);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 10, 10);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 11, 11);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 12, 13);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 12, 13);
                                xlsSheet.MergedCells.Add(mCell);
                                /////En
                                mCell = new XLCellRange(line, line + 2, 1, 1);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line + 2, 2, 2);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 3, 3);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 4, 4);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 5, 5);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 7, 7);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 8, 8);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 9, 9);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 10, 10);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 11, 11);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 12, 13);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 12, 13);
                                xlsSheetEn.MergedCells.Add(mCell);
                                /////En

                                for (int col = 1; col < 13; col++)
                                {
                                    xlsSheet[line, col].Style = xlstStyleH;
                                    xlsSheet[line + 1, col].Style = xlstStyleH;
                                    xlsSheet[line + 2, col].Style = xlstStyleH;

                                    xlsSheetEn[line, col].Style = xlstStyleH;
                                    xlsSheetEn[line + 1, col].Style = xlstStyleH;
                                    xlsSheetEn[line + 2, col].Style = xlstStyleH;
                                }

                                if (ds != null)
                                {
                                    int count = 0;
                                    DataTable dt = ds.Tables[0];
                                    if (dt.Rows.Count > 0)
                                    {
                                        foreach (DataRow row in dt.Rows)
                                        {
                                            if (count >= 1)
                                            {
                                                xlsSheet.Rows.Insert(rElec + 1 + j);
                                                xlsSheetEn.Rows.Insert(rElec + 1 + j);
                                                j++;

                                            }
                                            count++;
                                            int tmp = rElec + j;

                                            string DateFrom = Func.ParseString(row["DateFrom"]);
                                            string DateTo = Func.ParseString(row["DateTo"]);

                                            string FromIndex = Func.ParseString(row["FromIndex"]);
                                            string ToIndex = Func.ParseString(row["ToIndex"]);
                                            string OtherFee01 = Func.ParseString(row["OtherFee01"]);
                                            string OtherFee02 = Func.ParseString(row["OtherFee02"]);
                                            string Mount = Func.ParseString(row["Mount"]);
                                            string ElecPricePercent = Func.ParseString(row["ElecPricePercent"]);

                                            xlsSheet[tmp, 1].Value = DateFrom;
                                            xlsSheet[tmp, 2].Value = DateTo;
                                            xlsSheet[tmp, 3].Value = FromIndex;
                                            xlsSheet[tmp, 4].Value = ToIndex;
                                            xlsSheet[tmp, 5].Value = OtherFee01;
                                            xlsSheet[tmp, 6].Value = Mount;
                                            xlsSheet[tmp, 7].Value = row["PriceVND"];
                                            xlsSheet[tmp, 8].Value = row["VatVND"];

                                            xlsSheet[tmp, 9].Value = row["SumVND"];
                                            xlsSheet[tmp, 10].Value = row["OtherFee02"];
                                            xlsSheet[tmp, 11].Value = row["ElecPricePercent"];
                                            xlsSheet[tmp, 12].Value = row["LastPriceVND"];

                                            mCell = new XLCellRange(tmp, tmp, 12, 13);
                                            xlsSheet.MergedCells.Add(mCell);

                                            /////En
                                            xlsSheetEn[tmp, 1].Value = DateFrom;
                                            xlsSheetEn[tmp, 2].Value = DateTo;
                                            xlsSheetEn[tmp, 3].Value = FromIndex;
                                            xlsSheetEn[tmp, 4].Value = ToIndex;
                                            xlsSheetEn[tmp, 5].Value = OtherFee01;
                                            xlsSheetEn[tmp, 6].Value = Mount;
                                            xlsSheetEn[tmp, 7].Value = row["PriceVND"];
                                            xlsSheetEn[tmp, 8].Value = row["VatVND"];

                                            xlsSheetEn[tmp, 9].Value = row["SumVND"];
                                            xlsSheetEn[tmp, 10].Value = row["OtherFee02"];
                                            xlsSheetEn[tmp, 11].Value = row["ElecPricePercent"];
                                            xlsSheetEn[tmp, 12].Value = row["LastPriceVND"];

                                            mCell = new XLCellRange(tmp, tmp, 12, 13);
                                            xlsSheetEn.MergedCells.Add(mCell);
                                            /////En
                                            for (int col = 1; col <= 12; col++)
                                            {
                                                xlsSheet[tmp, col].Style = xlstStyle;
                                                xlsSheetEn[tmp, col].Style = xlstStyle;
                                            }
                                            LastSumPriceVND[4] += Convert.ToDecimal(row["LastPriceVND"]);
                                            LastSumPriceUSD[4] += Convert.ToDecimal(row["LastPriceUSD"]);
                                        }
                                        xlsSheet[rElec + 1 + j, 12].Value = LastSumPriceVND[4];
                                        mCell = new XLCellRange(rElec + 1 + j, rElec + 1 + j, 1, 11);
                                        xlsSheet.MergedCells.Add(mCell);

                                        mCell = new XLCellRange(rElec + 1 + j, rElec + 1 + j, 12, 13);
                                        xlsSheet.MergedCells.Add(mCell);

                                        xlsSheetEn[rElec + 1 + j, 12].Value = LastSumPriceVND[4];
                                        mCell = new XLCellRange(rElec + 1 + j, rElec + 1 + j, 1, 11);
                                        xlsSheetEn.MergedCells.Add(mCell);

                                        mCell = new XLCellRange(rElec + 1 + j, rElec + 1 + j, 12, 13);
                                        xlsSheetEn.MergedCells.Add(mCell);

                                        for (int col = 1; col <= 13; col++)
                                        {
                                            xlsSheet[rElec + 1 + j, col].Style = xlstStyleSum;
                                            xlsSheetEn[rElec + 1 + j, col].Style = xlstStyleSum;
                                        }
                                        sumRow += dt.Rows.Count - 1;
                                    }
                                }
                            }

                            ds = new DataSet();
                            //Nuoc
                            //Xuất ra toàn bộ nội dung theo Trang
                            sql = " SELECT dbo.fnDateTime(A.DateFrom) DateFrom, dbo.fnDateTime(A.DateTo) DateTo, A.Vat, B.id, B.UsedElecWaterId, B.FromIndex, B.ToIndex, B.OtherFee01, B.OtherFee02, B.Mount, B.PriceVND, B.PriceUSD, B.SumVND, B.SumUSD, ";
                            sql += "        B.VatVND, B.VatUSD,IsNull(B.LastPriceUSD,0) LastPriceUSD,IsNull(B.LastPriceVND,0) LastPriceVND, B.Name, B.WaterPricePercent,B.ElecPricePercent  ";
                            sql += " FROM   PaymentElecWater AS A INNER JOIN ";
                            sql += "        PaymentElecWaterDetail AS B ON A.UsedElecWaterId = B.UsedElecWaterId";
                            sql += " WHERE A.BuildingId = '" + sBuildingId + "' and A.CustomerId = '" + CustomerId + "' and TarrifsOfElecId = 0 and A.YearMonth in (" + lsYearmonth + ")";
                            sql += " Order by A.DateFrom, B.FromIndex";

                            using (SqlCommand cm = db.CreateCommand(sql))
                            {
                                SqlDataAdapter da = new SqlDataAdapter(cm);
                                da.Fill(ds);
                                line = rWater - 3 + j;
                                //Phi dien
                                XLCellRange mCell = new XLCellRange(line, line + 2, 1, 1);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line + 2, 2, 2);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 3, 3);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 4, 4);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 6, 6);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 7, 7);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 8, 8);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 9, 9);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 10, 10);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 11, 11);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 12, 13);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 12, 13);
                                xlsSheet.MergedCells.Add(mCell);

                                /////En
                                mCell = new XLCellRange(line, line + 2, 1, 1);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line + 2, 2, 2);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 3, 3);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 4, 4);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 6, 6);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 7, 7);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 8, 8);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 9, 9);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 10, 10);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 11, 11);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 12, 13);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 12, 13);
                                xlsSheetEn.MergedCells.Add(mCell);
                                /////En

                                for (int col = 1; col < 13; col++)
                                {
                                    xlsSheet[line, col].Style = xlstStyleH;
                                    xlsSheet[line + 1, col].Style = xlstStyleH;
                                    xlsSheet[line + 2, col].Style = xlstStyleH;

                                    xlsSheetEn[line, col].Style = xlstStyleH;
                                    xlsSheetEn[line + 1, col].Style = xlstStyleH;
                                    xlsSheetEn[line + 2, col].Style = xlstStyleH;
                                }

                                if (ds != null)
                                {
                                    int count = 0;
                                    DataTable dt = ds.Tables[0];
                                    if (dt.Rows.Count > 0)
                                    {
                                        foreach (DataRow row in dt.Rows)
                                        {
                                            if (count >= 1)
                                            {
                                                xlsSheet.Rows.Insert(rWater + 1 + j);
                                                xlsSheetEn.Rows.Insert(rWater + 1 + j);
                                                j++;
                                            }
                                            count++;
                                            int tmp = rWater + j;

                                            string DateFrom = Func.ParseString(row["DateFrom"]);
                                            string DateTo = Func.ParseString(row["DateTo"]);

                                            string FromIndex = Func.ParseString(row["FromIndex"]);
                                            string ToIndex = Func.ParseString(row["ToIndex"]);
                                            string OtherFee01 = Func.ParseString(row["OtherFee01"]);
                                            string OtherFee02 = Func.ParseString(row["OtherFee02"]);
                                            string Mount = Func.ParseString(row["Mount"]);

                                            xlsSheet[tmp, 1].Value = DateFrom;
                                            xlsSheet[tmp, 2].Value = DateTo;
                                            xlsSheet[tmp, 3].Value = FromIndex;
                                            xlsSheet[tmp, 4].Value = ToIndex;
                                            xlsSheet[tmp, 5].Value = Mount;
                                            xlsSheet[tmp, 6].Value = row["PriceVND"];
                                            xlsSheet[tmp, 7].Value = row["OtherFee01"];
                                            xlsSheet[tmp, 8].Value = row["VatVND"];

                                            xlsSheet[tmp, 9].Value = row["SumVND"];
                                            xlsSheet[tmp, 10].Value = row["OtherFee02"];
                                            xlsSheet[tmp, 11].Value = row["WaterPricePercent"];
                                            xlsSheet[tmp, 12].Value = row["LastPriceVND"];

                                            /////En
                                            xlsSheetEn[tmp, 1].Value = DateFrom;
                                            xlsSheetEn[tmp, 2].Value = DateTo;
                                            xlsSheetEn[tmp, 3].Value = FromIndex;
                                            xlsSheetEn[tmp, 4].Value = ToIndex;
                                            xlsSheetEn[tmp, 5].Value = Mount;
                                            xlsSheetEn[tmp, 6].Value = row["PriceVND"];
                                            xlsSheetEn[tmp, 7].Value = row["OtherFee01"];
                                            xlsSheetEn[tmp, 8].Value = row["VatVND"];

                                            xlsSheetEn[tmp, 9].Value = row["SumVND"];
                                            xlsSheetEn[tmp, 10].Value = row["OtherFee02"];
                                            xlsSheetEn[tmp, 11].Value = row["WaterPricePercent"];
                                            xlsSheetEn[tmp, 12].Value = row["LastPriceVND"];

                                            /////En
                                            for (int col = 1; col <= 12; col++)
                                            {
                                                xlsSheet[tmp, col].Style = xlstStyle;
                                                xlsSheetEn[tmp, col].Style = xlstStyle;
                                            }
                                            LastSumPriceVND[5] += Convert.ToDecimal(row["LastPriceVND"]);
                                            LastSumPriceUSD[5] += Convert.ToDecimal(row["LastPriceUSD"]);

                                            mCell = new XLCellRange(tmp, tmp, 12, 13);
                                            xlsSheet.MergedCells.Add(mCell);
                                            xlsSheetEn.MergedCells.Add(mCell);
                                        }
                                        xlsSheet[rWater + 1 + j, 12].Value = LastSumPriceVND[5];
                                        xlsSheetEn[rWater + 1 + j, 12].Value = LastSumPriceVND[5];
                                        mCell = new XLCellRange(rWater + 1 + j, rWater + 1 + j, 1, 11);
                                        xlsSheet.MergedCells.Add(mCell);
                                        xlsSheetEn.MergedCells.Add(mCell);

                                        mCell = new XLCellRange(rWater + 1 + j, rWater + 1 + j, 12, 13);
                                        xlsSheet.MergedCells.Add(mCell);
                                        xlsSheetEn.MergedCells.Add(mCell);

                                        for (int col = 1; col <= 13; col++)
                                        {
                                            xlsSheet[rWater + 1 + j, col].Style = xlstStyleSum;
                                            xlsSheetEn[rWater + 1 + j, col].Style = xlstStyleSum;
                                        }
                                        sumRow += dt.Rows.Count - 1;
                                    }
                                }
                            }

                            //Service
                            ds = new DataSet();

                            sql = string.Empty;
                            sql = " SELECT Service,dbo.fnDateTime(ServiceDateFrom) ServiceDateFrom,dbo.fnDateTime(ServiceDateTo) ServiceDateTo,PriceVND,PriceUSD,VatUSD,VatVND,Mount,Unit,SumVND,SumUSD,isnull(LastPriceVND,0) LastPriceVND,isnull(LastPriceUSD,0) LastPriceUSD";
                            sql += " FROM   PaymentService";
                            sql += " WHERE BuildingId = '" + sBuildingId + "' and CustomerId = '" + CustomerId + "' and YearMonth in (" + lsYearmonth + ")";
                            sql += " Order By ServiceDate ";

                            using (SqlCommand cm = db.CreateCommand(sql))
                            {
                                SqlDataAdapter da = new SqlDataAdapter(cm);
                                da.Fill(ds);
                                line = rService - 3 + j;
                                //Phi khác
                                XLCellRange mCell = new XLCellRange(line, line + 2, 1, 1);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line + 2, 2, 2);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line + 2, 3, 3);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line + 2, 4, 4);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 5, 5);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 6, 7);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 6, 7);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 8, 9);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 8, 9);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 10, 11);
                                xlsSheet.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 10, 11);
                                xlsSheet.MergedCells.Add(mCell);

                                /////En
                                mCell = new XLCellRange(line, line + 2, 1, 1);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line + 2, 2, 2);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line + 2, 3, 3);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line + 2, 4, 4);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 2, 5, 5);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 6, 7);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 6, 7);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 8, 9);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 8, 9);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line, line, 10, 11);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 10, 11);
                                xlsSheetEn.MergedCells.Add(mCell);
                                /////En
                                for (int col = 1; col < 13; col++)
                                {
                                    xlsSheet[line, col].Style = xlstStyleH;
                                    xlsSheet[line + 1, col].Style = xlstStyleH;
                                    xlsSheet[line + 2, col].Style = xlstStyleH;

                                    xlsSheetEn[line, col].Style = xlstStyleH;
                                    xlsSheetEn[line + 1, col].Style = xlstStyleH;
                                    xlsSheetEn[line + 2, col].Style = xlstStyleH;
                                }
                                mCell = new XLCellRange(line, line, 12, 13);
                                xlsSheet.MergedCells.Add(mCell);
                                xlsSheetEn.MergedCells.Add(mCell);

                                mCell = new XLCellRange(line + 1, line + 1, 12, 13);
                                xlsSheet.MergedCells.Add(mCell);
                                xlsSheetEn.MergedCells.Add(mCell);

                                if (ds != null)
                                {
                                    int count = 0;
                                    DataTable dt = ds.Tables[0];

                                    if (dt.Rows.Count > 0)
                                    {

                                        foreach (DataRow row in dt.Rows)
                                        {
                                            if (count >= 1)
                                            {
                                                xlsSheet.Rows.Insert(rService + 1 + j);
                                                xlsSheetEn.Rows.Insert(rService + 1 + j);
                                                j++;
                                            }
                                            count++;
                                            int tmp = rService + j;

                                            string Service = Func.ParseString(row["Service"]);
                                            string ServiceDateFrom = Func.ParseString(row["ServiceDateFrom"]);
                                            string ServiceDateTo = Func.ParseString(row["ServiceDateTo"]);

                                            string Mount = Func.ParseString(row["Mount"]);

                                            xlsSheet[tmp, 1].Value = Service;
                                            xlsSheet[tmp, 2].Value = Func.ParseString(row["Unit"]);
                                            xlsSheet[tmp, 3].Value = ServiceDateFrom;
                                            xlsSheet[tmp, 4].Value = ServiceDateTo;
                                            xlsSheet[tmp, 5].Value = Mount;

                                            xlsSheet[tmp, 6].Value = row["PriceUSD"];
                                            xlsSheet[tmp, 7].Value = row["PriceVND"];

                                            xlsSheet[tmp, 8].Value = row["SumUSD"];
                                            xlsSheet[tmp, 9].Value = row["SumVND"];

                                            xlsSheet[tmp, 10].Value = row["VatUSD"];
                                            xlsSheet[tmp, 11].Value = row["VatVND"];

                                            xlsSheet[tmp, 12].Value = row["LastPriceUSD"];
                                            xlsSheet[tmp, 13].Value = row["LastPriceVND"];

                                            /////En
                                            xlsSheetEn[tmp, 1].Value = Service;
                                            xlsSheetEn[tmp, 2].Value = Func.ParseString(row["Unit"]);
                                            xlsSheetEn[tmp, 3].Value = ServiceDateFrom;
                                            xlsSheetEn[tmp, 4].Value = ServiceDateTo;
                                            xlsSheetEn[tmp, 5].Value = Mount;

                                            xlsSheetEn[tmp, 6].Value = row["PriceUSD"];
                                            xlsSheetEn[tmp, 7].Value = row["PriceVND"];

                                            xlsSheetEn[tmp, 8].Value = row["SumUSD"];
                                            xlsSheetEn[tmp, 9].Value = row["SumVND"];

                                            xlsSheetEn[tmp, 10].Value = row["VatUSD"];
                                            xlsSheetEn[tmp, 11].Value = row["VatVND"];

                                            xlsSheetEn[tmp, 12].Value = row["LastPriceUSD"];
                                            xlsSheetEn[tmp, 13].Value = row["LastPriceVND"];
                                            /////En

                                            for (int col = 1; col <= 13; col++)
                                            {
                                                xlsSheet[tmp, col].Style = xlstStyle;
                                                xlsSheetEn[tmp, col].Style = xlstStyle;
                                            }
                                            LastSumPriceVND[6] += Convert.ToDecimal(row["LastPriceVND"]);
                                            LastSumPriceUSD[6] += Convert.ToDecimal(row["LastPriceUSD"]);
                                        }
                                        xlsSheet[rService + 1 + j, 12].Value = LastSumPriceUSD[6];
                                        xlsSheet[rService + 1 + j, 13].Value = LastSumPriceVND[6];

                                        xlsSheetEn[rService + 1 + j, 12].Value = LastSumPriceUSD[6];
                                        xlsSheetEn[rService + 1 + j, 13].Value = LastSumPriceVND[6];

                                        mCell = new XLCellRange(rService + 1 + j, rService + 1 + j, 1, 11);
                                        xlsSheet.MergedCells.Add(mCell);
                                        xlsSheetEn.MergedCells.Add(mCell);

                                        for (int col = 1; col <= 13; col++)
                                        {
                                            xlsSheet[rService + 1 + j, col].Style = xlstStyleSum;
                                            xlsSheetEn[rService + 1 + j, col].Style = xlstStyleSum;
                                        }
                                        sumRow += dt.Rows.Count - 1;
                                    }
                                }
                            }

                            //Paid
                            sql = "Select  *";
                            sql += " From    PaymentBillDetail";
                            sql += " Where   BuildingId = '" + sBuildingId + "' and CustomerId = '" + CustomerId + "' and YearMonth in (" + lsYearmonth + ") and YearMonth < " + maxYearMonth + "";
                            string strYearMonth = "";
                            int lineTmp = rPaid - 2 + j;

                            //Paid
                            XLCellRange mCellTmp = new XLCellRange(lineTmp, lineTmp + 1, 1, 1);
                            xlsSheet.MergedCells.Add(mCellTmp);

                            mCellTmp = new XLCellRange(lineTmp, lineTmp, 2, 3);
                            xlsSheet.MergedCells.Add(mCellTmp);

                            mCellTmp = new XLCellRange(lineTmp, lineTmp, 4, 5);
                            xlsSheet.MergedCells.Add(mCellTmp);

                            mCellTmp = new XLCellRange(lineTmp, lineTmp, 6, 7);
                            xlsSheet.MergedCells.Add(mCellTmp);

                            mCellTmp = new XLCellRange(lineTmp, lineTmp, 8, 9);
                            xlsSheet.MergedCells.Add(mCellTmp);

                            mCellTmp = new XLCellRange(lineTmp, lineTmp, 12, 13);
                            xlsSheet.MergedCells.Add(mCellTmp);

                            /////En
                            mCellTmp = new XLCellRange(lineTmp, lineTmp + 1, 1, 1);
                            xlsSheetEn.MergedCells.Add(mCellTmp);

                            mCellTmp = new XLCellRange(lineTmp, lineTmp, 2, 3);
                            xlsSheetEn.MergedCells.Add(mCellTmp);

                            mCellTmp = new XLCellRange(lineTmp, lineTmp, 4, 5);
                            xlsSheetEn.MergedCells.Add(mCellTmp);

                            mCellTmp = new XLCellRange(lineTmp, lineTmp, 6, 7);
                            xlsSheetEn.MergedCells.Add(mCellTmp);

                            mCellTmp = new XLCellRange(lineTmp, lineTmp, 8, 9);
                            xlsSheetEn.MergedCells.Add(mCellTmp);

                            mCellTmp = new XLCellRange(lineTmp, lineTmp, 12, 13);
                            xlsSheetEn.MergedCells.Add(mCellTmp);
                            /////En
                            Hashtable rowNo = new Hashtable();
                            decimal[] PaidSumVND = new decimal[7] { 0, 0, 0, 0, 0, 0, 0 };
                            decimal[] PaidSumUSD = new decimal[7] { 0, 0, 0, 0, 0, 0, 0 };

                            DataTable dtPaid = DbHelper.GetDataTable(sql);
                            for (int i = 0; i < dtPaid.Rows.Count; i++)
                            {
                                string PaymentType = Func.ParseString(dtPaid.Rows[i]["PaymentType"]);
                                string MoneyUSD = Func.ParseString(dtPaid.Rows[i]["MoneyUSD"]);
                                string MoneyVND = Func.ParseString(dtPaid.Rows[i]["MoneyVND"]);
                                string PaidUSD = Func.ParseString(dtPaid.Rows[i]["PaidUSD"]);
                                string PaidVND = Func.ParseString(dtPaid.Rows[i]["PaidVND"]);
                                string ExchangeType = Func.ParseString(dtPaid.Rows[i]["ExchangeType"]);
                                string YearMonth = Func.ParseString(dtPaid.Rows[i]["YearMonth"]);

                                if (!rowNo.Contains(YearMonth))
                                {
                                    if (rowNo.Count != 0)
                                    {
                                        xlsSheet.Rows.Insert(rPaid + j + 1);
                                        xlsSheetEn.Rows.Insert(rPaid + j + 1);
                                        j++;
                                    }
                                    rowNo.Add(YearMonth, j);
                                }
                                int m = Func.ParseInt(rowNo[YearMonth]);
                                strYearMonth = YearMonth;
                                decimal tmpUSD = Convert.ToDecimal(MoneyUSD) - Convert.ToDecimal(PaidUSD);
                                decimal tmpVND = Convert.ToDecimal(MoneyVND) - Convert.ToDecimal(PaidVND);

                                PaidPriceUSD += Convert.ToDecimal(dtPaid.Rows[i]["PaidUSD"]);
                                PaidPriceVND += Convert.ToDecimal(dtPaid.Rows[i]["PaidVND"]);

                                xlsSheet[rPaid + m, 1].Value = YearMonth.Substring(4, 2) + "/" + YearMonth.Substring(0, 4);
                                xlsSheetEn[rPaid + m, 1].Value = YearMonth.Substring(4, 2) + "/" + YearMonth.Substring(0, 4);
                                switch (PaymentType)
                                {
                                    case "1":
                                        //Rent
                                        xlsSheet[rPaid + m, 2].Value = dtPaid.Rows[i]["PaidUSD"];
                                        xlsSheet[rPaid + m, 3].Value = dtPaid.Rows[i]["PaidVND"];

                                        xlsSheetEn[rPaid + m, 2].Value = dtPaid.Rows[i]["PaidUSD"];
                                        xlsSheetEn[rPaid + m, 3].Value = dtPaid.Rows[i]["PaidVND"];

                                        PaidSumUSD[0] += Convert.ToDecimal(dtPaid.Rows[i]["PaidUSD"]);
                                        PaidSumVND[0] += Convert.ToDecimal(dtPaid.Rows[i]["PaidVND"]);

                                        break;
                                    case "2":
                                        //Manager
                                        xlsSheet[rPaid + m, 4].Value = dtPaid.Rows[i]["PaidUSD"];
                                        xlsSheet[rPaid + m, 5].Value = dtPaid.Rows[i]["PaidVND"];

                                        xlsSheetEn[rPaid + m, 4].Value = dtPaid.Rows[i]["PaidUSD"];
                                        xlsSheetEn[rPaid + m, 5].Value = dtPaid.Rows[i]["PaidVND"];

                                        PaidSumUSD[1] += Convert.ToDecimal(dtPaid.Rows[i]["PaidUSD"]);
                                        PaidSumVND[1] += Convert.ToDecimal(dtPaid.Rows[i]["PaidVND"]);

                                        break;
                                    case "3":
                                        //Parking
                                        xlsSheet[rPaid + m, 6].Value = dtPaid.Rows[i]["PaidUSD"];
                                        xlsSheet[rPaid + m, 7].Value = dtPaid.Rows[i]["PaidVND"];

                                        xlsSheetEn[rPaid + m, 6].Value = dtPaid.Rows[i]["PaidUSD"];
                                        xlsSheetEn[rPaid + m, 7].Value = dtPaid.Rows[i]["PaidVND"];

                                        PaidSumUSD[2] += Convert.ToDecimal(dtPaid.Rows[i]["PaidUSD"]);
                                        PaidSumVND[2] += Convert.ToDecimal(dtPaid.Rows[i]["PaidVND"]);
                                        break;
                                    case "4":
                                        //Extra
                                        xlsSheet[rPaid + m, 8].Value = dtPaid.Rows[i]["PaidUSD"];
                                        xlsSheet[rPaid + m, 9].Value = dtPaid.Rows[i]["PaidVND"];

                                        xlsSheetEn[rPaid + m, 8].Value = dtPaid.Rows[i]["PaidUSD"];
                                        xlsSheetEn[rPaid + m, 9].Value = dtPaid.Rows[i]["PaidVND"];

                                        PaidSumUSD[3] += Convert.ToDecimal(dtPaid.Rows[i]["PaidUSD"]);
                                        PaidSumVND[3] += Convert.ToDecimal(dtPaid.Rows[i]["PaidVND"]);
                                        break;
                                    case "5":
                                        xlsSheet[rPaid + m, 10].Value = dtPaid.Rows[i]["PaidVND"];
                                        xlsSheetEn[rPaid + m, 10].Value = dtPaid.Rows[i]["PaidVND"];

                                        PaidSumUSD[4] += Convert.ToDecimal(dtPaid.Rows[i]["PaidUSD"]);
                                        PaidSumVND[4] += Convert.ToDecimal(dtPaid.Rows[i]["PaidVND"]);
                                        break;
                                    case "6":
                                        xlsSheet[rPaid + m, 11].Value = dtPaid.Rows[i]["PaidVND"];
                                        xlsSheetEn[rPaid + m, 11].Value = dtPaid.Rows[i]["PaidVND"];

                                        PaidSumUSD[5] += Convert.ToDecimal(dtPaid.Rows[i]["PaidUSD"]);
                                        PaidSumVND[5] += Convert.ToDecimal(dtPaid.Rows[i]["PaidVND"]);
                                        break;
                                    case "7":
                                        xlsSheet[rPaid + m, 12].Value = dtPaid.Rows[i]["PaidUSD"];
                                        xlsSheet[rPaid + m, 13].Value = dtPaid.Rows[i]["PaidVND"];

                                        xlsSheetEn[rPaid + m, 12].Value = dtPaid.Rows[i]["PaidUSD"];
                                        xlsSheetEn[rPaid + m, 13].Value = dtPaid.Rows[i]["PaidVND"];
                                        break;
                                    default:
                                        break;
                                }
                                for (int row = rPaid + m; row <= rPaid + 1 + j; row++)
                                {
                                    for (int col = 1; col <= 13; col++)
                                    {
                                        xlsSheet[row, col].Style = xlstStyle;
                                    }
                                }
                            }
                            lineTmp = rPaid - 2 + j;

                            xlsSheet[lineTmp + 3, 2].Value = PaidSumUSD[0];
                            xlsSheet[lineTmp + 3, 3].Value = PaidSumVND[0];
                            xlsSheet[lineTmp + 3, 4].Value = PaidSumUSD[1];
                            xlsSheet[lineTmp + 3, 5].Value = PaidSumVND[1];
                            xlsSheet[lineTmp + 3, 6].Value = PaidSumUSD[2];
                            xlsSheet[lineTmp + 3, 7].Value = PaidSumVND[2];
                            xlsSheet[lineTmp + 3, 8].Value = PaidSumUSD[3];
                            xlsSheet[lineTmp + 3, 9].Value = PaidSumVND[3];
                            xlsSheet[lineTmp + 3, 10].Value = PaidSumVND[4];
                            xlsSheet[lineTmp + 3, 11].Value = PaidSumVND[5];
                            xlsSheet[lineTmp + 3, 12].Value = PaidSumUSD[6];
                            xlsSheet[lineTmp + 3, 13].Value = PaidSumVND[6];

                            /////En
                            xlsSheetEn[lineTmp + 3, 2].Value = PaidSumUSD[0];
                            xlsSheetEn[lineTmp + 3, 3].Value = PaidSumVND[0];
                            xlsSheetEn[lineTmp + 3, 4].Value = PaidSumUSD[1];
                            xlsSheetEn[lineTmp + 3, 5].Value = PaidSumVND[1];
                            xlsSheetEn[lineTmp + 3, 6].Value = PaidSumUSD[2];
                            xlsSheetEn[lineTmp + 3, 7].Value = PaidSumVND[2];
                            xlsSheetEn[lineTmp + 3, 8].Value = PaidSumUSD[3];
                            xlsSheetEn[lineTmp + 3, 9].Value = PaidSumVND[3];
                            xlsSheetEn[lineTmp + 3, 10].Value = PaidSumVND[4];
                            xlsSheetEn[lineTmp + 3, 11].Value = PaidSumVND[5];
                            xlsSheetEn[lineTmp + 3, 12].Value = PaidSumUSD[6];
                            xlsSheetEn[lineTmp + 3, 13].Value = PaidSumVND[6];
                            /////En

                            for (int col = 1; col <= 13; col++)
                            {
                                xlsSheet[lineTmp + 3, col].Style = xlstStyleSum;
                                xlsSheetEn[lineTmp + 3, col].Style = xlstStyleSum;
                            }

                            ///////////////DEPT
                            sql = "  Select *";
                            sql += " From   v_DeptBill";
                            sql += " Where  BuildingId = '" + sBuildingId + "' and CustomerId = '" + CustomerId + "' and YearMonth not in (" + lsYearmonth + ") and YearMonth < " + maxYearMonth + "";
                            sql += " And    (DeptUsd <> 0 or DeptVnd <> 0)";
                            strYearMonth = "";
                            lineTmp = rDept - 2 + j;

                            //Paid
                            mCellTmp = new XLCellRange(lineTmp, lineTmp + 1, 1, 1);
                            xlsSheet.MergedCells.Add(mCellTmp);

                            mCellTmp = new XLCellRange(lineTmp, lineTmp, 2, 3);
                            xlsSheet.MergedCells.Add(mCellTmp);

                            mCellTmp = new XLCellRange(lineTmp, lineTmp, 4, 5);
                            xlsSheet.MergedCells.Add(mCellTmp);

                            mCellTmp = new XLCellRange(lineTmp, lineTmp, 6, 7);
                            xlsSheet.MergedCells.Add(mCellTmp);

                            mCellTmp = new XLCellRange(lineTmp, lineTmp, 8, 9);
                            xlsSheet.MergedCells.Add(mCellTmp);

                            mCellTmp = new XLCellRange(lineTmp, lineTmp, 12, 13);
                            xlsSheet.MergedCells.Add(mCellTmp);

                            //////En
                            mCellTmp = new XLCellRange(lineTmp, lineTmp + 1, 1, 1);
                            xlsSheetEn.MergedCells.Add(mCellTmp);

                            mCellTmp = new XLCellRange(lineTmp, lineTmp, 2, 3);
                            xlsSheetEn.MergedCells.Add(mCellTmp);

                            mCellTmp = new XLCellRange(lineTmp, lineTmp, 4, 5);
                            xlsSheetEn.MergedCells.Add(mCellTmp);

                            mCellTmp = new XLCellRange(lineTmp, lineTmp, 6, 7);
                            xlsSheetEn.MergedCells.Add(mCellTmp);

                            mCellTmp = new XLCellRange(lineTmp, lineTmp, 8, 9);
                            xlsSheetEn.MergedCells.Add(mCellTmp);

                            mCellTmp = new XLCellRange(lineTmp, lineTmp, 12, 13);
                            xlsSheetEn.MergedCells.Add(mCellTmp);
                            //////En
                            rowNo = new Hashtable();
                            decimal DeptPriceVND = 0;
                            decimal DeptPriceUSD = 0;

                            decimal[] DeptSumVND = new decimal[7] { 0, 0, 0, 0, 0, 0, 0 };
                            decimal[] DeptSumUSD = new decimal[7] { 0, 0, 0, 0, 0, 0, 0 };

                            DataTable dtDept = DbHelper.GetDataTable(sql);
                            for (int i = 0; i < dtDept.Rows.Count; i++)
                            {
                                string PaymentType = Func.ParseString(dtDept.Rows[i]["PaymentType"]);
                                string DeptUSD = Func.ParseString(dtDept.Rows[i]["DeptUSD"]);
                                string DeptVND = Func.ParseString(dtDept.Rows[i]["DeptVND"]);
                                string YearMonth = Func.ParseString(dtDept.Rows[i]["YearMonth"]);

                                if (!rowNo.Contains(YearMonth))
                                {
                                    if (rowNo.Count != 0)
                                    {
                                        xlsSheet.Rows.Insert(rDept + j + 1);
                                        xlsSheetEn.Rows.Insert(rDept + j + 1);
                                        j++;
                                    }
                                    rowNo.Add(YearMonth, j);
                                }
                                int m = Func.ParseInt(rowNo[YearMonth]);
                                strYearMonth = YearMonth;

                                DeptPriceUSD += Convert.ToDecimal(dtDept.Rows[i]["DeptUSD"]);
                                DeptPriceVND += Convert.ToDecimal(dtDept.Rows[i]["DeptVND"]);

                                xlsSheet[rDept + m, 1].Value = YearMonth.Substring(4, 2) + "/" + YearMonth.Substring(0, 4);
                                xlsSheetEn[rDept + m, 1].Value = YearMonth.Substring(4, 2) + "/" + YearMonth.Substring(0, 4);

                                switch (PaymentType)
                                {
                                    case "1":
                                        //Rent
                                        xlsSheet[rDept + m, 2].Value = dtDept.Rows[i]["DeptUSD"];
                                        xlsSheet[rDept + m, 3].Value = dtDept.Rows[i]["DeptVND"];

                                        xlsSheetEn[rDept + m, 2].Value = dtDept.Rows[i]["DeptUSD"];
                                        xlsSheetEn[rDept + m, 3].Value = dtDept.Rows[i]["DeptVND"];

                                        DeptSumUSD[0] += Convert.ToDecimal(dtDept.Rows[i]["DeptUSD"]);
                                        DeptSumVND[0] += Convert.ToDecimal(dtDept.Rows[i]["DeptVND"]);

                                        break;
                                    case "2":
                                        //Manager
                                        xlsSheet[rDept + m, 4].Value = dtDept.Rows[i]["DeptUSD"];
                                        xlsSheet[rDept + m, 5].Value = dtDept.Rows[i]["DeptVND"];

                                        xlsSheetEn[rDept + m, 4].Value = dtDept.Rows[i]["DeptUSD"];
                                        xlsSheetEn[rDept + m, 5].Value = dtDept.Rows[i]["DeptVND"];

                                        DeptSumUSD[1] += Convert.ToDecimal(dtDept.Rows[i]["DeptUSD"]);
                                        DeptSumVND[1] += Convert.ToDecimal(dtDept.Rows[i]["DeptVND"]);

                                        break;
                                    case "3":
                                        //Parking
                                        xlsSheet[rDept + m, 6].Value = dtDept.Rows[i]["DeptUSD"];
                                        xlsSheet[rDept + m, 7].Value = dtDept.Rows[i]["DeptVND"];

                                        xlsSheetEn[rDept + m, 6].Value = dtDept.Rows[i]["DeptUSD"];
                                        xlsSheetEn[rDept + m, 7].Value = dtDept.Rows[i]["DeptVND"];

                                        DeptSumUSD[2] += Convert.ToDecimal(dtDept.Rows[i]["DeptUSD"]);
                                        DeptSumVND[2] += Convert.ToDecimal(dtDept.Rows[i]["DeptVND"]);
                                        break;
                                    case "4":
                                        //Extra
                                        xlsSheet[rDept + m, 8].Value = dtDept.Rows[i]["DeptUSD"];
                                        xlsSheet[rDept + m, 9].Value = dtDept.Rows[i]["DeptVND"];

                                        xlsSheetEn[rDept + m, 8].Value = dtDept.Rows[i]["DeptUSD"];
                                        xlsSheetEn[rDept + m, 9].Value = dtDept.Rows[i]["DeptVND"];

                                        DeptSumUSD[3] += Convert.ToDecimal(dtDept.Rows[i]["DeptUSD"]);
                                        DeptSumVND[3] += Convert.ToDecimal(dtDept.Rows[i]["DeptVND"]);
                                        break;
                                    case "5":
                                        xlsSheet[rDept + m, 10].Value = dtDept.Rows[i]["DeptVND"];
                                        xlsSheetEn[rDept + m, 10].Value = dtDept.Rows[i]["DeptVND"];

                                        DeptSumUSD[4] += Convert.ToDecimal(dtDept.Rows[i]["DeptUSD"]);
                                        DeptSumVND[4] += Convert.ToDecimal(dtDept.Rows[i]["DeptVND"]);
                                        break;
                                    case "6":
                                        xlsSheet[rDept + m, 11].Value = dtDept.Rows[i]["DeptVND"];
                                        xlsSheetEn[rDept + m, 11].Value = dtDept.Rows[i]["DeptVND"];

                                        DeptSumUSD[5] += Convert.ToDecimal(dtDept.Rows[i]["DeptUSD"]);
                                        DeptSumVND[5] += Convert.ToDecimal(dtDept.Rows[i]["DeptVND"]);
                                        break;
                                    case "7":
                                        xlsSheet[rDept + m, 12].Value = dtDept.Rows[i]["DeptUSD"];
                                        xlsSheet[rDept + m, 13].Value = dtDept.Rows[i]["DeptVND"];

                                        xlsSheetEn[rDept + m, 12].Value = dtDept.Rows[i]["DeptUSD"];
                                        xlsSheetEn[rDept + m, 13].Value = dtDept.Rows[i]["DeptVND"];

                                        DeptSumUSD[6] += Convert.ToDecimal(dtDept.Rows[i]["DeptUSD"]);
                                        DeptSumVND[6] += Convert.ToDecimal(dtDept.Rows[i]["DeptVND"]);
                                        break;
                                    default:
                                        break;
                                }
                                for (int row = rDept + m; row <= rDept + 1 + j; row++)
                                {
                                    for (int col = 1; col <= 13; col++)
                                    {
                                        xlsSheet[row, col].Style = xlstStyle;
                                        xlsSheetEn[row, col].Style = xlstStyle;
                                    }
                                }
                            }
                            lineTmp = rDept - 2 + j;

                            xlsSheet[lineTmp + 3, 2].Value = DeptSumUSD[0];
                            xlsSheet[lineTmp + 3, 3].Value = DeptSumVND[0];
                            xlsSheet[lineTmp + 3, 4].Value = DeptSumUSD[1];
                            xlsSheet[lineTmp + 3, 5].Value = DeptSumVND[1];
                            xlsSheet[lineTmp + 3, 6].Value = DeptSumUSD[2];
                            xlsSheet[lineTmp + 3, 7].Value = DeptSumVND[2];
                            xlsSheet[lineTmp + 3, 8].Value = DeptSumUSD[3];
                            xlsSheet[lineTmp + 3, 9].Value = DeptSumVND[3];
                            xlsSheet[lineTmp + 3, 10].Value = DeptSumVND[4];
                            xlsSheet[lineTmp + 3, 11].Value = DeptSumVND[5];
                            xlsSheet[lineTmp + 3, 12].Value = DeptSumUSD[6];
                            xlsSheet[lineTmp + 3, 13].Value = DeptSumVND[6];

                            //////En
                            xlsSheetEn[lineTmp + 3, 2].Value = DeptSumUSD[0];
                            xlsSheetEn[lineTmp + 3, 3].Value = DeptSumVND[0];
                            xlsSheetEn[lineTmp + 3, 4].Value = DeptSumUSD[1];
                            xlsSheetEn[lineTmp + 3, 5].Value = DeptSumVND[1];
                            xlsSheetEn[lineTmp + 3, 6].Value = DeptSumUSD[2];
                            xlsSheetEn[lineTmp + 3, 7].Value = DeptSumVND[2];
                            xlsSheetEn[lineTmp + 3, 8].Value = DeptSumUSD[3];
                            xlsSheetEn[lineTmp + 3, 9].Value = DeptSumVND[3];
                            xlsSheetEn[lineTmp + 3, 10].Value = DeptSumVND[4];
                            xlsSheetEn[lineTmp + 3, 11].Value = DeptSumVND[5];
                            xlsSheetEn[lineTmp + 3, 12].Value = DeptSumUSD[6];
                            xlsSheetEn[lineTmp + 3, 13].Value = DeptSumVND[6];
                            //////En
                            for (int col = 1; col <= 13; col++)
                            {
                                xlsSheet[lineTmp + 3, col].Style = xlstStyleSum;
                                xlsSheetEn[lineTmp + 3, col].Style = xlstStyleSum;
                            }

                            xlsSheet[lineTmp + 3, 1].Style = xlstStyleSum;
                            xlsSheetEn[lineTmp + 3, 1].Style = xlstStyleSum;

                            decimal AllSumVND = 0;
                            decimal AllSumUSD = 0;
                            for (int i = 0; i < 7; i++)
                            {
                                AllSumVND += LastSumPriceVND[i];
                                AllSumUSD += LastSumPriceUSD[i];
                            }

                            AllSumVND -= PaidPriceVND;
                            AllSumUSD -= PaidPriceUSD;

                            AllSumVND += DeptPriceVND;
                            AllSumUSD += DeptPriceUSD;

                            xlsSheet[rSumVND + j, cSumVND].Value = Func.FormatNumber_New(AllSumUSD);
                            xlsSheet[rSumVND + j, cSumVND].Value += "(USD)";
                            xlsSheet[rSumVND + j, cSumVND + 1].Value = Func.FormatNumber_New(AllSumVND);
                            xlsSheet[rSumVND + j, cSumVND + 1].Value += "(VND)";

                            xlsSheetEn[rSumVND + j, cSumVND].Value = Func.FormatNumber_New(AllSumUSD);
                            xlsSheetEn[rSumVND + j, cSumVND].Value += "(USD)";
                            xlsSheetEn[rSumVND + j, cSumVND + 1].Value = Func.FormatNumber_New(AllSumVND);
                            xlsSheetEn[rSumVND + j, cSumVND + 1].Value += "(VND)";

                            AllSumVND += Convert.ToDecimal(AllSumUSD * Convert.ToDecimal(UsdExchange));

                            string strMoney = Func.docso(Convert.ToInt32(AllSumVND));
                            string strMoneyEn = Func.DocSo_En(Convert.ToInt32(AllSumVND));

                            xlsSheet[rContract, cContract].Value = xlsSheet[rContract, cContract].Value.ToString().Replace("{%HOP_DONG%}", String.IsNullOrEmpty(contract) ? "" : contract.Substring(1));
                            xlsSheet[rSum + j, cSum].Value = Convert.ToInt32(AllSumVND);

                            mCellTmp = new XLCellRange(rSum + j, rSum + j, cSum, cSum + 1);
                            xlsSheet.MergedCells.Add(mCellTmp);
                            xlsSheet[rSum + j, cSum].Style = xlstStyleSum;
                            xlsSheet[rSum + j, cSum + 1].Style = xlstStyleSum;
                            xlsSheet[rSumRead + j, cSumRead].Value = xlsSheet[rSumRead + j, cSumRead].Value.ToString().Replace("{%TONG_CHU%}", strMoney.ToUpper());

                            xlsSheetEn[rContract, cContract].Value = xlsSheetEn[rContract, cContract].Value.ToString().Replace("{%HOP_DONG%}", String.IsNullOrEmpty(contract) ? "" : contract.Substring(1));
                            xlsSheetEn[rSum + j, cSum].Value = Convert.ToInt32(AllSumVND);

                            mCellTmp = new XLCellRange(rSum + j, rSum + j, cSum, cSum + 1);
                            xlsSheetEn.MergedCells.Add(mCellTmp);
                            xlsSheetEn[rSum + j, cSum].Style = xlstStyleSum;
                            xlsSheetEn[rSum + j, cSum + 1].Style = xlstStyleSum;
                            xlsSheetEn[rSumRead + j, cSumRead].Value = xlsSheetEn[rSumRead + j, cSumRead].Value.ToString().Replace("{%TONG_CHU%}", strMoneyEn.ToUpper());

                            xlbBook.Save(fileNameDes);
                            xlbBook.Clear();
                            //ScriptManager.RegisterStartupScript(Page, this.GetType(), "", "PopUp('" + strFilePathExport + "'," + PopupWidth + "," + PopupHeight + ",'EditReport', true);", true);
                        }
                    }
                }
            }
        }