protected void btn_XuatExcel_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
try
{
LoadDanhSach(0, 1);
var dt = new DataTable();
dt = ViewState["Table_data"] as DataTable;
if (dt.Rows.Count > 0)
{
var ExistFile = Server.MapPath(vPathCommonBieuMau_FN + "BAO_CAO_THANG.xlsx");
var File = new System.IO.FileInfo(ExistFile);
using (ExcelPackage pck = new ExcelPackage(File))
{
ExcelWorksheet ws = pck.Workbook.Worksheets.First();
int vIndexRow = 11;
//Gán đơn vị
if (!_currentUser.IsInRole("Administrators"))
{
var objND = vDC.QLSC_NGUOIDUNGs.Where(x => x.UserID == _currentUser.UserID).SingleOrDefault();
if (objND != null)
{
var tenDV = vDC.QLSC_DONVIs.Where(x => x.DONVI_ID == objND.DONVI_ID).SingleOrDefault().DONVI_TEN;
ws.Cells[2, 1].Value = "ĐIỆN LỰC " + tenDV.ToUpper();
ws.Cells[4, 13].Value = tenDV + ", ngày " + DateTime.Now.Day + " tháng " + DateTime.Now.Month + " năm " + DateTime.Now.Year;
}
}
else
{
ws.Cells[2, 1].Value = "";
ws.Cells[4, 13].Value = "Sóc Trăng" + ", ngày " + DateTime.Now.Day + " tháng " + DateTime.Now.Month + " năm " + DateTime.Now.Year;
}
if (drpDonVi.SelectedValue != "0")
{
ws.Cells[2, 1].Value = "ĐIỆN LỰC " + drpDonVi.Text.ToUpper();
ws.Cells[4, 13].Value = drpDonVi.Text + ", ngày " + DateTime.Now.Day + " tháng " + DateTime.Now.Month + " năm " + DateTime.Now.Year;
}
//Gán thời gian cho báo cáo
ws.Cells[6, 1].Value = "Báo cáo tình hình sự cố từ ngày " + ClassCommon.HienThiNgayThangNam(txtTuNgay.SelectedDate ?? DateTime.Now.AddDays(-7)) + " đến ngày " + ClassCommon.HienThiNgayThangNam(txtDenNgay.SelectedDate ?? DateTime.Now);
for (int i = 0; i < dt.Rows.Count; i++)
{
ws.Cells[vIndexRow, 1].Value = (i + 1);
ws.Cells[vIndexRow, 2].Value = dt.Rows[i]["SC_NGAYXAYRA"];
ws.Cells[vIndexRow, 3].Value = dt.Rows[i]["SC_GIOXAYRA"];
ws.Cells[vIndexRow, 4].Value = dt.Rows[i]["SC_NGAYTAILAP"];
ws.Cells[vIndexRow, 5].Value = dt.Rows[i]["SC_NOIDUNG"];
ws.Cells[vIndexRow, 6].Value = dt.Rows[i]["SC_NGUYENNHAN"];
ws.Cells[vIndexRow, 7].Value = dt.Rows[i]["SC_DONVI_TENRUTGON"];
ws.Cells[vIndexRow, 8].Value = dt.Rows[i]["SC_VTTB_TENCHUNGLOAI"];
ws.Cells[vIndexRow, 9].Value = dt.Rows[i]["SC_VTTB_SOLUONG"];
ws.Cells[vIndexRow, 10].Value = dt.Rows[i]["SC_VTTB_NHASANXUAT"];
ws.Cells[vIndexRow, 11].Value = dt.Rows[i]["SC_VTTB_TENCHUNGLOAI2"];
ws.Cells[vIndexRow, 12].Value = dt.Rows[i]["SC_VTTB_SOLUONG2"];
ws.Cells[vIndexRow, 13].Value = dt.Rows[i]["SC_VTTB_NHASANXUAT2"];
ws.Cells[vIndexRow, 14].Value = dt.Rows[i]["SC_VTTB_TENCHUNGLOAI3"];
ws.Cells[vIndexRow, 15].Value = dt.Rows[i]["SC_VTTB_SOLUONG3"];
ws.Cells[vIndexRow, 16].Value = dt.Rows[i]["SC_VTTB_NHASANXUAT3"];
ws.Cells[vIndexRow, 17].Value = dt.Rows[i]["SC_VTTB_TENCHUNGLOAI4"];
ws.Cells[vIndexRow, 18].Value = dt.Rows[i]["SC_VTTB_SOLUONG4"];
ws.Cells[vIndexRow, 19].Value = dt.Rows[i]["SC_VTTB_NHASANXUAT4"];
ws.Cells[vIndexRow, 20].Value = dt.Rows[i]["SC_VTTB_TENCHUNGLOAI5"];
ws.Cells[vIndexRow, 21].Value = dt.Rows[i]["SC_VTTB_SOLUONG5"];
ws.Cells[vIndexRow, 22].Value = dt.Rows[i]["SC_VTTB_NHASANXUAT5"];
ws.Cells[vIndexRow, 23].Value = dt.Rows[i]["SC_DIENAP"];
ws.Cells[vIndexRow, 24].Value = dt.Rows[i]["SC_CQ"];
ws.Cells[vIndexRow, 25].Value = dt.Rows[i]["SC_KQ"];
ws.Cells[vIndexRow, 26].Value = dt.Rows[i]["SC_PHANLOAI_TQ_DUONGTRUC"];
ws.Cells[vIndexRow, 27].Value = dt.Rows[i]["SC_PHANLOAI_TQ_NGARE"];
ws.Cells[vIndexRow, 28].Value = dt.Rows[i]["SC_PHANLOAI_VC_DUONGTRUC"];
ws.Cells[vIndexRow, 29].Value = dt.Rows[i]["SC_PHANLOAI_VC_NGARE"];
ws.Cells[vIndexRow, 30].Value = dt.Rows[i]["SC_PHANLOAI_TBA"];
ws.Cells[vIndexRow, 31].Value = dt.Rows[i]["SC_PHANLOAI_HA"];
ws.Cells[vIndexRow, 32].Value = dt.Rows[i]["SC_THIETBIDONGCAT_MSRCS"];
ws.Cells[vIndexRow, 33].Value = dt.Rows[i]["SC_TONGSOKHACHHANG"];
ws.Cells[vIndexRow, 34].Value = dt.Rows[i]["SC_TAISAN_TBA"];
ws.Cells[vIndexRow, 35].Value = dt.Rows[i]["SC_TAISAN_HA"];
ws.Cells[vIndexRow, 36].Value = dt.Rows[i]["SC_GHICHU"];
vIndexRow++;
ws.InsertRow(vIndexRow, 1, vIndexRow - 1);
}
ws.DeleteRow(vIndexRow);
Byte[] fileBytes = pck.GetAsByteArray();
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=" + "Bao_Cao_Su_Co_Dien_Luc_" + DateTime.Now.Day + "_" + DateTime.Now.Month + "_"
+ DateTime.Now.Year + ".xlsx");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
Response.BinaryWrite(fileBytes);
HttpContext.Current.Response.Flush(); // Sends all currently buffered output to the client.
HttpContext.Current.Response.SuppressContent = true; // Gets or sets a value indicating whether to send HTTP content to the client.
HttpContext.Current.ApplicationInstance.CompleteRequest(); // Causes ASP.NET to bypass all events and filtering in the HTTP pipeline chain of execution and directly execute the EndRequest event.
}
}
}
catch (Exception Ex)
{ }
}
}
public void LoadDanhSach(int pCurentPage, int vXuatExcel)
{
if (vXuatExcel == 1)
{
vPageSize = 99999;
}
try
{
UserInfo _currentUser = UserController.Instance.GetCurrentUserInfo();
var pUserID = _currentUser.UserID;
string pKEYWORD = "";
if (Session[PortalSettings.ActiveTab.TabID + pUserID + "tukhoa_search"] != null)
{
pKEYWORD = Session[PortalSettings.ActiveTab.TabID + pUserID + "tukhoa_search"].ToString();
}
txt_TK_NoiDung.Text = pKEYWORD;
int DONVI_ID = 0;
if (Session[PortalSettings.ActiveTab.TabID + pUserID + "tukhoa_donvi"] != null)
{
drpDonVi.SelectedValue = Session[PortalSettings.ActiveTab.TabID + pUserID + "tukhoa_donvi"].ToString();
DONVI_ID = int.Parse(Session[PortalSettings.ActiveTab.TabID + pUserID + "tukhoa_donvi"].ToString());
}
DateTime tungay = DateTime.Parse(txtTuNgay.SelectedDate.ToString()).Date;
DateTime denngay = DateTime.Parse(txtDenNgay.SelectedDate.ToString()).Date;
if (Session[PortalSettings.ActiveTab.TabID + pUserID + "tukhoa_tungay"] != null)
{
txtTuNgay.SelectedDate = DateTime.Parse(Session[PortalSettings.ActiveTab.TabID + pUserID + "tukhoa_tungay"].ToString());
tungay = DateTime.Parse(Session[PortalSettings.ActiveTab.TabID + pUserID + "tukhoa_tungay"].ToString()).Date;
}
if (Session[PortalSettings.ActiveTab.TabID + pUserID + "tukhoa_denngay"] != null)
{
txtDenNgay.SelectedDate = DateTime.Parse(Session[PortalSettings.ActiveTab.TabID + pUserID + "tukhoa_denngay"].ToString());
denngay = DateTime.Parse(Session[PortalSettings.ActiveTab.TabID + pUserID + "tukhoa_denngay"].ToString()).Date.AddHours(23).AddMinutes(59).AddSeconds(59);
}
//var lstSuCo = vDC.QLSC_SUCOs.ToList();
dtTable = new DataTable();
dtTable.Columns.Add("SC_ID");
dtTable.Columns.Add("SC_NGAYXAYRA");
dtTable.Columns.Add("SC_GIOXAYRA");
dtTable.Columns.Add("SC_NGAYTAILAP");
dtTable.Columns.Add("SC_NOIDUNG");
dtTable.Columns.Add("SC_NGUYENNHAN");
dtTable.Columns.Add("SC_DONVI");
dtTable.Columns.Add("SC_DONVI_TENRUTGON");
dtTable.Columns.Add("SC_VTTB_TENCHUNGLOAI");
dtTable.Columns.Add("SC_VTTB_SOLUONG");
dtTable.Columns.Add("SC_VTTB_NHASANXUAT");
dtTable.Columns.Add("SC_VTTB_NAMSANXUAT");
dtTable.Columns.Add("SC_VTTB_TENCHUNGLOAI2");
dtTable.Columns.Add("SC_VTTB_SOLUONG2");
dtTable.Columns.Add("SC_VTTB_NHASANXUAT2");
dtTable.Columns.Add("SC_VTTB_NAMSANXUAT2");
dtTable.Columns.Add("SC_VTTB_TENCHUNGLOAI5");
dtTable.Columns.Add("SC_VTTB_SOLUONG5");
dtTable.Columns.Add("SC_VTTB_NHASANXUAT5");
dtTable.Columns.Add("SC_VTTB_NAMSANXUAT5");
dtTable.Columns.Add("SC_VTTB_TENCHUNGLOAI3");
dtTable.Columns.Add("SC_VTTB_SOLUONG3");
dtTable.Columns.Add("SC_VTTB_NHASANXUAT3");
dtTable.Columns.Add("SC_VTTB_NAMSANXUAT3");
dtTable.Columns.Add("SC_VTTB_TENCHUNGLOAI4");
dtTable.Columns.Add("SC_VTTB_SOLUONG4");
dtTable.Columns.Add("SC_VTTB_NHASANXUAT4");
dtTable.Columns.Add("SC_VTTB_NAMSANXUAT4");
dtTable.Columns.Add("SC_DIENAP");
//dtTable.Columns.Add("");
dtTable.Columns.Add("SC_CQ");
dtTable.Columns.Add("SC_KQ");
dtTable.Columns.Add("SC_PHANLOAI_TQ_DUONGTRUC");
dtTable.Columns.Add("SC_PHANLOAI_TQ_NGARE");
dtTable.Columns.Add("SC_PHANLOAI_VC_DUONGTRUC");
dtTable.Columns.Add("SC_PHANLOAI_VC_NGARE");
dtTable.Columns.Add("SC_PHANLOAI_TBA");
dtTable.Columns.Add("SC_PHANLOAI_HA");
dtTable.Columns.Add("SC_TONGSOKHACHHANG");
dtTable.Columns.Add("SC_TAISAN_TBA");
dtTable.Columns.Add("SC_TAISAN_HA");
dtTable.Columns.Add("FILE");
dtTable.Columns.Add("SC_GHICHU");
dtTable.Columns.Add("SC_THIETBIDONGCAT_MSRCS");
dtTable.Columns.Add("UserID");
var lstSuCo1 = (from sc in vDC.QLSC_SUCOs
join dv in vDC.QLSC_DONVIs on sc.DONVI_ID equals dv.DONVI_ID
join loai in vDC.QLSC_LOAISUCOs on sc.LOAISC_ID equals loai.LOAISC_ID
where (SqlMethods.Like(sc.SC_NOIDUNG, "%" + pKEYWORD + "%") || SqlMethods.Like(sc.SC_NGUYENNHAN, "%" + pKEYWORD + "%")) &&
sc.SC_NGAYXAYRA >= tungay &&
sc.SC_NGAYXAYRA <= denngay &&
(dv.DONVI_ID == DONVI_ID || DONVI_ID == 0)
orderby sc.SC_NGAYXAYRA descending
select new
{
sc.DONVI_ID,
sc.SC_ID,
sc.SC_NGAYXAYRA,
sc.SC_NGAYTAILAP,
sc.SC_NOIDUNG,
sc.SC_NGUYENNHAN,
dv.DONVI_TEN,
dv.DONVI_TENRUTGON,
sc.SC_VTTB_TENCHUNGLOAI,
sc.SC_VTTB_SOLUONG,
sc.SC_VTTB_NHASANXUAT,
sc.SC_VTTB_TENCHUNGLOAI2,
sc.SC_VTTB_SOLUONG2,
sc.SC_VTTB_NHASANXUAT2,
sc.SC_VTTB_TENCHUNGLOAI3,
sc.SC_VTTB_SOLUONG3,
sc.SC_VTTB_NHASANXUAT3,
sc.SC_VTTB_TENCHUNGLOAI4,
sc.SC_VTTB_SOLUONG4,
sc.SC_VTTB_NHASANXUAT4,
sc.SC_VTTB_TENCHUNGLOAI5,
sc.SC_VTTB_SOLUONG5,
sc.SC_VTTB_NHASANXUAT5,
sc.SC_DIENAP,
sc.SC_CQ,
sc.SC_KQ,
sc.SC_LOAI,
sc.SC_TONGSOKH,
sc.SC_TAISAN,
sc.SC_GHICHU,
sc.UserID,
sc.SC_THIETBIDONGCAT_MSRCS
});
count = lstSuCo1.Count();
if (!_currentUser.IsInRole("Administrators"))
{
objNGUOIDUNG = vDC.QLSC_NGUOIDUNGs.Where(x => x.UserID == _currentUser.UserID).SingleOrDefault();
if (objNGUOIDUNG != null)
{
lstSuCo1 = lstSuCo1.Where(x => x.DONVI_ID == objNGUOIDUNG.DONVI_ID);
count = lstSuCo1.Count();
}
}
//lstSuCo1 = lstSuCo1.Skip((pCurentPage) * vPageSize).Take(vPageSize).ToList();
foreach (var it in lstSuCo1)
{
DataRow row = dtTable.NewRow();
row["SC_ID"] = it.SC_ID;
row["SC_NGAYXAYRA"] = ClassCommon.HienThiNgayThangNam(Convert.ToDateTime(it.SC_NGAYXAYRA));
row["SC_GIOXAYRA"] = String.Format("{0:HH:mm}", it.SC_NGAYTAILAP);
row["SC_NGAYTAILAP"] = String.Format("{0:HH:mm}", it.SC_NGAYTAILAP);
row["SC_NOIDUNG"] = it.SC_NOIDUNG;
row["SC_NGUYENNHAN"] = it.SC_NGUYENNHAN;
row["SC_DONVI"] = it.DONVI_TEN;
row["SC_DONVI_TENRUTGON"] = it.DONVI_TENRUTGON;
row["SC_VTTB_TENCHUNGLOAI"] = it.SC_VTTB_TENCHUNGLOAI;
row["SC_VTTB_SOLUONG"] = it.SC_VTTB_SOLUONG;
row["SC_VTTB_NHASANXUAT"] = it.SC_VTTB_NHASANXUAT;
row["SC_VTTB_TENCHUNGLOAI2"] = it.SC_VTTB_TENCHUNGLOAI2;
row["SC_VTTB_SOLUONG2"] = it.SC_VTTB_SOLUONG2;
row["SC_VTTB_NHASANXUAT2"] = it.SC_VTTB_NHASANXUAT2;
row["SC_VTTB_TENCHUNGLOAI3"] = it.SC_VTTB_TENCHUNGLOAI3;
row["SC_VTTB_SOLUONG3"] = it.SC_VTTB_SOLUONG3;
row["SC_VTTB_NHASANXUAT3"] = it.SC_VTTB_NHASANXUAT3;
row["SC_VTTB_TENCHUNGLOAI4"] = it.SC_VTTB_TENCHUNGLOAI4;
row["SC_VTTB_SOLUONG4"] = it.SC_VTTB_SOLUONG4;
row["SC_VTTB_NHASANXUAT4"] = it.SC_VTTB_NHASANXUAT4;
row["SC_VTTB_TENCHUNGLOAI5"] = it.SC_VTTB_TENCHUNGLOAI5;
row["SC_VTTB_SOLUONG5"] = it.SC_VTTB_SOLUONG5;
row["SC_VTTB_NHASANXUAT5"] = it.SC_VTTB_NHASANXUAT5;
row["SC_DIENAP"] = it.SC_DIENAP == 1 ? "HT" : "";
row["SC_CQ"] = it.SC_CQ == 1 ? "x" : "";
row["SC_KQ"] = it.SC_KQ == 1 ? "x" : "";
row["SC_DIENAP"] = it.SC_DIENAP == 2 ? "HT" : "TT";
row["SC_PHANLOAI_TQ_DUONGTRUC"] = it.SC_LOAI == 1 ? "x" : "";
row["SC_PHANLOAI_TQ_NGARE"] = it.SC_LOAI == 2 ? "x" : "";
row["SC_PHANLOAI_VC_DUONGTRUC"] = it.SC_LOAI == 3 ? "x" : "";
row["SC_PHANLOAI_VC_NGARE"] = it.SC_LOAI == 4 ? "x" : "";
row["SC_PHANLOAI_TBA"] = it.SC_LOAI == 5 ? "x" : "";
row["SC_PHANLOAI_HA"] = it.SC_LOAI == 6 ? "x" : "";
row["SC_TONGSOKHACHHANG"] = it.SC_TONGSOKH;
row["SC_TAISAN_TBA"] = it.SC_TAISAN == 1 ? "x" : "";
row["SC_TAISAN_HA"] = it.SC_TAISAN == 2 ? "x" : "";
row["SC_THIETBIDONGCAT_MSRCS"] = it.SC_THIETBIDONGCAT_MSRCS;
var temp = objTAPTINController.Get_TapTin_By_ObjectID_LoaiID(it.SC_ID, (int)CommonEnum.TapTinObjectLoai.File);
string strFile = "";
if (temp.Count > 0)
{
foreach (var file in temp)
{
strFile += "<a title='" + file.FILE_NAME + "' href='" + vPathCommonData + "/" + file.FILE_NAME + "' target='_blank' style='padding-left:10px;'> ";
strFile += "<span class='glyphicon glyphicon-download-alt' style='color:blue;font-size:13px'></span> ";
//strFile += file.FILE_MOTA;
strFile += "</a>";
//strFile += temp.Count > 1? ",":"";
}
row["FILE"] = strFile;
}
row["SC_GHICHU"] = it.SC_GHICHU;
row["UserID"] = it.UserID;
dtTable.Rows.Add(row);
}
dgDanhSach.DataSource = dtTable;
dgDanhSach.PageSize = vPageSize;
dgDanhSach.VirtualItemCount = count;
dgDanhSach.CurrentPageIndex = pCurentPage;
dgDanhSach.DataBind();
ViewState["Table_data"] = dtTable;
}
catch (Exception ex)
{
}
}