protected void btnXuat_Click(object sender, EventArgs e) { MTCSYT.SYS_Session session = (MTCSYT.SYS_Session)Session["SYS_Session"]; int ma_dviqly = int.Parse(session.User.ma_dviqly + ""); var lst = db.BC_CHOT_ChiSoThang(ma_dviqly, int.Parse("0" + cmbThang.Value), int.Parse("0" + cmbNam.Value)).ToList(); var lsttONG = db.BC_CHOT_GiaoNhan2Chieu_TongTram(ma_dviqly, int.Parse("0" + cmbThang.Value), int.Parse("0" + cmbNam.Value)).ToList(); #region Chuẩn bị tệp excel mẫu để ghi dữ liệu string destFile = Server.MapPath("~/Tem/BC_BCDienLuc.xls"); string sTemplate = (destFile); Workbook exBook = new Workbook(); exBook.Open(sTemplate, FileFormatType.Excel2003); _exSheet = exBook.Worksheets[0]; _range = _exSheet.Cells; #endregion // Xử lý replace các thông tin báo cáo tĩnh theo công ty //Report.ReplaceSpecificationField(_exSheet); int donghientai = 7; #region Ghi dữ liệu _exSheet.Replace("NGAYTHANG", "Ngày " + DateTime.Now.Day + " tháng " + DateTime.Now.Month + " năm " + DateTime.Now.Year); Style celicaStil = exBook.Styles[exBook.Styles.Add()]; celicaStil.Font.IsBold = true; DM_DVQLYService dm_dviSer = new DM_DVQLYService(); var donvi = dm_dviSer.SelectDM_DVQLY(ma_dviqly); _exSheet.Replace("ntDienLuc", donvi.NAME_DVIQLY.Split('-')[1].ToUpper()); _exSheet.Replace("ntTieuDe", "BÁO CÁO SẢN LƯỢNG ĐIỆN GIAO CỦA " + donvi.NAME_DVIQLY.Split('-')[1].ToString().ToUpper() + " TRONG THÁNG " + cmbThang.Value + " NĂM " + cmbNam.Value); if (lst.Count > 0) { _exSheet.Cells.InsertRows(donghientai + 1, (lst.Count + lsttONG.Count * 2) - 3); } string idTram = "0"; int vitri = 0; int sttTong = 0, sttChiTiet = 0; foreach (var tongtram in lsttONG) { if (idTram != tongtram.IDTram + "") { var tram = db.DM_Trams.SingleOrDefault(x => x.IDTram == tongtram.IDTram); var phuongthuc = db.DM_ChiNhanhs.SingleOrDefault(x => x.ID == int.Parse(tram.IDChiNhanh)); _range[donghientai + vitri, 0].PutValue(phuongthuc.MoTa); _range.Merge(donghientai + vitri, 0, 1, 21); _range[donghientai + vitri, 0].SetStyle(celicaStil); vitri = vitri + 1; sttTong = sttTong + 1; _range[donghientai + vitri, 0].PutValue(sttTong); _range[donghientai + vitri, 0].SetStyle(celicaStil); _range[donghientai + vitri, 1].PutValue(tongtram.TenTram); _range[donghientai + vitri, 1].SetStyle(celicaStil); _range[donghientai + vitri, 7].PutValue(tongtram.slPGiao); _range[donghientai + vitri, 7].SetStyle(celicaStil); _range[donghientai + vitri, 11].PutValue(tongtram.slb1Giao); _range[donghientai + vitri, 11].SetStyle(celicaStil); _range[donghientai + vitri, 15].PutValue(tongtram.slb2Giao); _range[donghientai + vitri, 15].SetStyle(celicaStil); _range[donghientai + vitri, 19].PutValue(tongtram.slb3Giao); _range[donghientai + vitri, 19].SetStyle(celicaStil); vitri = vitri + 1; var lstByTram = lst.Where(x => x.IDTram == tongtram.IDTram); sttChiTiet = sttChiTiet + 1; foreach (var chitiet in lstByTram) { _range[donghientai + vitri, 0].PutValue(sttTong + "." + sttChiTiet); _range[donghientai + vitri, 1].PutValue(chitiet.TenDiemDo); _range[donghientai + vitri, 2].PutValue(chitiet.CapDienAp); _range[donghientai + vitri, 3].PutValue(chitiet.HeSoNhan); _range[donghientai + vitri, 4].PutValue(chitiet.Giao_P_Dau); _range[donghientai + vitri, 5].PutValue(chitiet.Giao_P_Cuoi); _range[donghientai + vitri, 6].PutValue(chitiet.Giao_P_Cuoi - chitiet.Giao_P_Dau); _range[donghientai + vitri, 7].PutValue(chitiet.Giao_P_SanLuong); _range[donghientai + vitri, 8].PutValue(chitiet.Giao_Bieu1_Dau); _range[donghientai + vitri, 9].PutValue(chitiet.Giao_Bieu1_Cuoi); _range[donghientai + vitri, 10].PutValue(chitiet.Giao_Bieu1_Cuoi - chitiet.Giao_Bieu1_Dau); _range[donghientai + vitri, 11].PutValue(chitiet.Giao_Bieu1_SanLuong); _range[donghientai + vitri, 12].PutValue(chitiet.Giao_Bieu2_Dau); _range[donghientai + vitri, 13].PutValue(chitiet.Giao_Bieu2_Cuoi); _range[donghientai + vitri, 14].PutValue(chitiet.Giao_Bieu2_Cuoi - chitiet.Giao_Bieu2_Dau); _range[donghientai + vitri, 15].PutValue(chitiet.Giao_Bieu2_SanLuong); _range[donghientai + vitri, 16].PutValue(chitiet.Giao_Bieu3_Dau); _range[donghientai + vitri, 17].PutValue(chitiet.Giao_Bieu3_Cuoi); _range[donghientai + vitri, 18].PutValue(chitiet.Giao_Bieu3_Cuoi - chitiet.Giao_Bieu3_Dau); _range[donghientai + vitri, 19].PutValue(chitiet.Giao_Bieu3_SanLuong); vitri = vitri + 1; } idTram = tongtram.IDTram + ""; } } #endregion exBook.Save("bc_dienLuc.xls", SaveType.OpenInExcel, FileFormatType.Default, this.Response); //Response.Redirect("../BaoCao/Report.aspx?Loai=5&strSQL=" + strTruyVan); }