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_KhuVuc_ChiTiet(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_KhuVuc.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 = 8;
#region Ghi dữ liệu
_exSheet.Replace("NGAYTHANG", "Ngày " + DateTime.Now.Day + " tháng " + DateTime.Now.Month + " năm " + DateTime.Now.Year);
DM_DVQLYService dm_dviSer = new DM_DVQLYService();
var donvi = dm_dviSer.SelectDM_DVQLY(ma_dviqly);
string tongdong = Session["tongdong"] + "";
if (donvi.Type < 3)
{
_exSheet.Replace("ntTieuDe", "BÁO CÁO SẢN LƯỢNG ĐIỆN NĂNG GIAO NHẬN VỚI CÁC CÔNG TY ĐIỆN LỰC");
}
else
{
_exSheet.Replace("ntTieuDe", " BÁO CÁO ĐIỆN NĂNG GIAO NHẬN VỚI CÔNG TY " + donvi.TEN_DVIQLY);
}
if (lst.Count > 1)
{
_exSheet.Cells.InsertRows(donghientai + 1, (lst.Count() + int.Parse(tongdong)) * 5 - 3);
}
string macto = "", tenkhuvuc = "";
int vitri = 0;
Style celicaStil = exBook.Styles[exBook.Styles.Add()];
celicaStil.VerticalAlignment = TextAlignmentType.Center;
celicaStil.HorizontalAlignment = TextAlignmentType.Center;
celicaStil.Pattern = BackgroundType.Solid;
celicaStil.Font.IsBold = true;
celicaStil.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
StyleFlag flg = new StyleFlag();
flg.Font = true; int tongtram = 0;
flg.CellShading = true;
foreach (var ct in lst)
{
tongtram = tongtram + 1;
celicaStil.VerticalAlignment = TextAlignmentType.Center;
if (tenkhuvuc != ct.TEN_DVIQLY)
{
var countKV = lst.Where(x => x.TEN_DVIQLY == ct.TEN_DVIQLY).Count();
_range[donghientai + vitri, 0].PutValue(ct.TEN_DVIQLY);
var categoryCounts =
from p in lst
group p by p.TenTram into g
select new { TEN_DVIQLY = ct.TEN_DVIQLY, ProductCount = g.Count() };
_range.Merge(donghientai + vitri, 0, (categoryCounts.Count() + int.Parse(tongdong)) * 5, 1);
_range[donghientai + vitri, 0].SetStyle(celicaStil);
tenkhuvuc = ct.TEN_DVIQLY;
}
if (macto != ct.MaCongTo)
{
_range[donghientai + vitri, 1].PutValue(ct.TenTram);
_range.Merge(donghientai + vitri, 1, 5, 1);
_range[donghientai + vitri, 1].SetStyle(celicaStil);
_range[donghientai + vitri, 2].PutValue(ct.TenDiemDo);
_range.Merge(donghientai + vitri, 2, 5, 1);
_range[donghientai + vitri, 2].SetStyle(celicaStil);
_range[donghientai + vitri, 3].PutValue(ct.TU_TI);
_range.Merge(donghientai + vitri, 3, 5, 1);
_range[donghientai + vitri, 3].SetStyle(celicaStil);
_range[donghientai + vitri, 4].PutValue(ct.TenCongTo);
_range.Merge(donghientai + vitri, 4, 5, 1);
_range[donghientai + vitri, 4].SetStyle(celicaStil);
_range[donghientai + vitri, 5].PutValue(ct.HeSoNhan);
_range.Merge(donghientai + vitri, 5, 5, 1);
_range[donghientai + vitri, 5].SetStyle(celicaStil);
macto = ct.MaCongTo;
}
for (int i = 0; i < 5; i++)
{
if (i == 0)
{
_range[donghientai + vitri + i, 6].PutValue("Tổng P");
_range[donghientai + vitri + i, 7].PutValue(ct.Giao_P_Dau);
_range[donghientai + vitri + i, 8].PutValue(ct.Giao_P_Cuoi);
_range[donghientai + vitri + i, 9].PutValue(ct.Giao_P_SanLuong);
_range[donghientai + vitri + i, 10].PutValue(ct.CosGiao);
_range[donghientai + vitri + i, 11].PutValue(ct.Nhan_P_Dau);
_range[donghientai + vitri + i, 12].PutValue(ct.Nhan_P_Cuoi);
_range[donghientai + vitri + i, 13].PutValue(ct.Nhan_P_SanLuong);
_range[donghientai + vitri + i, 14].PutValue(ct.CosNhan);
}
else if (i == 1)
{
_range[donghientai + vitri + i, 6].PutValue("Tổng Q");
_range[donghientai + vitri + i, 7].PutValue(ct.Giao_Q_Dau);
_range[donghientai + vitri + i, 8].PutValue(ct.Giao_Q_Cuoi);
_range[donghientai + vitri + i, 9].PutValue(ct.Giao_Q_SanLuong);
_range[donghientai + vitri + i, 10].PutValue("");
_range[donghientai + vitri + i, 11].PutValue(ct.Nhan_Q_Dau);
_range[donghientai + vitri + i, 12].PutValue(ct.Nhan_Q_Cuoi);
_range[donghientai + vitri + i, 13].PutValue(ct.Nhan_Q_SanLuong);
_range[donghientai + vitri + i, 14].PutValue("");
}
else if (i == 2)
{
_range[donghientai + vitri + i, 6].PutValue("Biểu 1");
_range[donghientai + vitri + i, 7].PutValue(ct.Giao_Bieu1_Dau);
_range[donghientai + vitri + i, 8].PutValue(ct.Giao_Bieu1_Cuoi);
_range[donghientai + vitri + i, 9].PutValue(ct.Giao_Bieu1_SanLuong);
_range[donghientai + vitri + i, 10].PutValue("");
_range[donghientai + vitri + i, 11].PutValue(ct.Nhan_Bieu1_Dau);
_range[donghientai + vitri + i, 12].PutValue(ct.Nhan_Bieu1_Cuoi);
_range[donghientai + vitri + i, 13].PutValue(ct.Nhan_Bieu1_SanLuong);
_range[donghientai + vitri + i, 14].PutValue("");
}
else if (i == 3)
{
_range[donghientai + vitri + i, 6].PutValue("Biểu 2");
_range[donghientai + vitri + i, 7].PutValue(ct.Giao_Bieu2_Dau);
_range[donghientai + vitri + i, 8].PutValue(ct.Giao_Bieu2_Cuoi);
_range[donghientai + vitri + i, 9].PutValue(ct.Giao_Bieu1_SanLuong);
_range[donghientai + vitri + i, 10].PutValue("");
_range[donghientai + vitri + i, 11].PutValue(ct.Nhan_Bieu2_Dau);
_range[donghientai + vitri + i, 12].PutValue(ct.Nhan_Bieu2_Cuoi);
_range[donghientai + vitri + i, 13].PutValue(ct.Nhan_Bieu2_SanLuong);
_range[donghientai + vitri + i, 14].PutValue("");
}
else if (i == 4)
{
_range[donghientai + vitri + i, 6].PutValue("Biểu 3");
_range[donghientai + vitri + i, 7].PutValue(ct.Giao_Bieu3_Dau);
_range[donghientai + vitri + i, 8].PutValue(ct.Giao_Bieu3_Cuoi);
_range[donghientai + vitri + i, 9].PutValue(ct.Giao_Bieu3_SanLuong);
_range[donghientai + vitri + i, 10].PutValue("");
_range[donghientai + vitri + i, 11].PutValue(ct.Nhan_Bieu3_Dau);
_range[donghientai + vitri + i, 12].PutValue(ct.Nhan_Bieu3_Cuoi);
_range[donghientai + vitri + i, 13].PutValue(ct.Nhan_Bieu3_SanLuong);
_range[donghientai + vitri + i, 14].PutValue("");
}
}
//in tổng
_range[donghientai + vitri + 5, 1].PutValue("");
_range.Merge(donghientai + vitri + 5, 1, 5, 1);
_range[donghientai + vitri + 5, 1].SetStyle(celicaStil);
var lstTong = db.BC_GiaoNhan2Chieu_TongTram(ma_dviqly, int.Parse("0" + cmbThang.Value), int.Parse("0" + cmbNam.Value)).ToList();
var ctTong = lstTong.SingleOrDefault(x => x.TenTram == ct.TenTram);
if (ctTong != null)
{
if (tongtram == ctTong.TongTram)
{
tongtram = 0;
for (int i = 0; i < 5; i++)
{
celicaStil.VerticalAlignment = TextAlignmentType.Left;
celicaStil.HorizontalAlignment = TextAlignmentType.Left;
if (i == 0)
{
_range[donghientai + vitri + i + 5, 2].PutValue("Tổng P");
_range[donghientai + vitri + i + 5, 2].SetStyle(celicaStil);
_range.Merge(donghientai + vitri + i + 5, 2, 1, 7);
_range[donghientai + vitri + i + 5, 9].PutValue(ctTong.slPGiao);
_range[donghientai + vitri + i + 5, 13].PutValue(ctTong.slPNhan);
_range[donghientai + vitri + i + 5, 13].SetStyle(celicaStil);
_range[donghientai + vitri + i + 5, 9].SetStyle(celicaStil);
}
else if (i == 1)
{
_range[donghientai + vitri + i + 5, 2].PutValue("Tổng Q");
_range[donghientai + vitri + i + 5, 2].SetStyle(celicaStil);
_range.Merge(donghientai + vitri + i + 5, 2, 1, 7);
_range[donghientai + vitri + i + 5, 9].PutValue(ctTong.slQGiao);
_range[donghientai + vitri + i + 5, 13].PutValue(ctTong.slQNhan);
_range[donghientai + vitri + i + 5, 13].SetStyle(celicaStil);
_range[donghientai + vitri + i + 5, 9].SetStyle(celicaStil);
}
else if (i == 2)
{
_range[donghientai + vitri + i + 5, 2].PutValue("Tổng Biểu 1");
_range[donghientai + vitri + i + 5, 2].SetStyle(celicaStil);
_range.Merge(donghientai + vitri + i + 5, 2, 1, 7);
_range[donghientai + vitri + i + 5, 9].PutValue(ctTong.slb1Giao);
_range[donghientai + vitri + i + 5, 13].PutValue(ctTong.slb1Nhan);
_range[donghientai + vitri + i + 5, 13].SetStyle(celicaStil);
_range[donghientai + vitri + i + 5, 9].SetStyle(celicaStil);
}
else if (i == 3)
{
_range[donghientai + vitri + i + 5, 2].PutValue("Tổng Biểu 2");
_range[donghientai + vitri + i + 5, 2].SetStyle(celicaStil);
_range.Merge(donghientai + vitri + i + 5, 2, 1, 7);
_range[donghientai + vitri + i + 5, 9].PutValue(ctTong.slb2Giao);
_range[donghientai + vitri + i + 5, 13].PutValue(ctTong.slb2Nhan);
_range[donghientai + vitri + i + 5, 13].SetStyle(celicaStil);
_range[donghientai + vitri + i + 5, 9].SetStyle(celicaStil);
}
else if (i == 4)
{
_range[donghientai + vitri + i + 5, 2].PutValue("Tổng Biểu 3");
_range[donghientai + vitri + i + 5, 2].SetStyle(celicaStil);
_range.Merge(donghientai + vitri + i + 5, 2, 1, 7);
_range[donghientai + vitri + i + 5, 9].PutValue(ctTong.slb3Giao);
_range[donghientai + vitri + i + 5, 13].PutValue(ctTong.slb3Nhan);
_range[donghientai + vitri + i + 5, 13].SetStyle(celicaStil);
_range[donghientai + vitri + i + 5, 9].SetStyle(celicaStil);
}
else if (i == 5)
{
_range[donghientai + vitri + i + 5, 2].PutValue("Tổng 3 Biểu");
_range[donghientai + vitri + i + 5, 2].SetStyle(celicaStil);
_range.Merge(donghientai + vitri + i + 5, 2, 1, 7);
_range[donghientai + vitri + i + 5, 9].PutValue(ctTong.tong3B_Giao);
_range[donghientai + vitri + i + 5, 13].PutValue(ctTong.tong3B_Nhan);
_range[donghientai + vitri + i + 5, 13].SetStyle(celicaStil);
_range[donghientai + vitri + i + 5, 9].SetStyle(celicaStil);
}
}
vitri = vitri + 5;
}
}
vitri = vitri + 5;
}
#endregion
exBook.Save("BC_KhuVuc.xls", SaveType.OpenInExcel, FileFormatType.Default, this.Response);
//Response.Redirect("../BaoCao/Report.aspx?Loai=5&strSQL=" + strTruyVan);
}
