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