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