public SYS_PARTY_TYPE GetPartyType(int partyId) { try { using (var context = (ConnectionEFDataFirst)Activator.CreateInstance(typeof(ConnectionEFDataFirst), _connectionStr)) { SYS_PARTY_TYPE getData = new SYS_PARTY_TYPE(); getData = (from p in context.TBL_PARTY join pt in context.SYS_PARTY_TYPE on p.PartyTypeID equals pt.PartyTypeID where p.PartyID == partyId select pt).FirstOrDefault(); return(getData); } } catch (Exception ex) { return(null); } }
public bool exportExcel(int partyID, ref string refer, SYS_PARTY_TYPE objSYS_PARTY_TYPE, TBL_PARTY objTBL_PARTY) { var a = objSYS_PARTY_TYPE == null ? "" : objSYS_PARTY_TYPE.PartyTypeName; List <dynamic> lstDA_PARTY_PRODUCT_MATERIAL = DA_PARTY_PRODUCT_MATERIAL.Instance.GetViewReportForDatatablePagging(0, int.MaxValue, "MaterialName", "", Convert.ToInt32(partyID)); using (ExcelPackage pck = new ExcelPackage()) { try { //tạo dữ liệu khi các biến toàn cục không có dữ liệu var wsList = pck.Workbook.Worksheets.Add("Báo cáo"); ////int nCot = dtToExcel.Columns.Count; ////int nDong = dtToExcel.Rows.Count; //////định dạng chung cho cả sheet //wsList.Cells[1, 1, nDong + 3, 4].Style.Font.Name = "Tahoma"; //wsList.Cells[1, 1, nDong + 3, 4].Style.Font.Size = 11; wsList.Cells[4, 1, 4, 7].Style.Border.Left.Style = ExcelBorderStyle.Thin; wsList.Cells[4, 1, 4, 7].Style.Border.Top.Style = ExcelBorderStyle.Thin; wsList.Cells[4, 1, 4, 7].Style.Border.Right.Style = ExcelBorderStyle.Thin; wsList.Cells[4, 1, 4, 7].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; #region phần cứng //đỗ dữ liệu wsList.Cells["A1"].LoadFromText("ĐỊNH LƯỢNG THEO TIỆC NGÀY " + objTBL_PARTY.PartyDate.ToString("dd/MM/yyyy"));//cong ngay wsList.Cells[2, 1].LoadFromText("Ông/ bà: "); wsList.Cells[2, 2].LoadFromText(objTBL_PARTY.CustomerName.ToString()); wsList.Cells[2, 3].LoadFromText("Tiệc: " + a); wsList.Cells[2, 4].LoadFromText("Giờ: " + objTBL_PARTY.PartyDate.ToString("hh'h'mm")); wsList.Cells[3, 1].LoadFromText("Địa chỉ: "); wsList.Cells[3, 2].LoadFromText(objTBL_PARTY.PartyAddress); //wsList.Cells[2, 2].LoadFromText("gan value"); //wsList.Cells[2, 2].LoadFromText("gan value"); //wsList.Cells["B2"].LoadFromText(date); // wsList.Cells[nDong + 3, 1].LoadFromText("Tổng cộng"); //wsList.Cells[nDong + 3, 4].LoadFromText(SUM.ToString()); //style wsList.Cells[1, 1, 1, 8].Merge = true; // wsList.Cells[3, 1, 3, 7].Merge = true; wsList.Cells[1, 1, 1, 7].Style.Font.Bold = true; wsList.Cells[4, 1, 4, 7].Style.Font.Bold = true; wsList.Cells[2, 1, 3, 1].Style.Font.Bold = true; //wsList.Cells[1, 1, 1, nCot - 1].Style.Font.Size = 14; wsList.Cells[1, 1, 1, 8].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; wsList.Cells[4, 1, 4, 8].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // wsList.Cells[2, 2, 2, 4].Merge = true; // wsList.Cells[2, 2, 2, nCot - 1].Style.Font.Bold = true; //// wsList.Cells["A2"].Style.Font.Size = 14; // wsList.Cells[2, 2, 2, nCot - 1].Style.Font.Size = 12; // wsList.Cells[2, 1, 2, nCot - 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //wsList.Cells[nDong + 3, 1, nDong + 3, 4].Style.Font.Bold = true; //wsList.Cells[nDong + 3, 1, nDong + 3, 3].Merge = true; //wsList.Cells[nDong + 3, 1, nDong + 3, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; #endregion #region dữ liệu //tiêu đề wsList.Cells[4, 1].LoadFromText("STT"); wsList.Cells[4, 2].LoadFromText("NGUYÊN LIỆU"); wsList.Cells[4, 3].LoadFromText("SỐ LƯỢNG/TRỌNG LƯỢNG"); wsList.Cells[4, 4].LoadFromText("ĐVT"); wsList.Cells[4, 5].LoadFromText("ĐƠN GIÁ"); wsList.Cells[4, 6].LoadFromText("NHÀ CUNG CẤP"); wsList.Cells[4, 7].LoadFromText("GIAO HÀNG TRƯỚC"); //Hien thi du lieu try { ////for (int i = 0; i < nCot; i++) // { for (int j = 0; j < lstDA_PARTY_PRODUCT_MATERIAL.Count(); j++) { wsList.Cells[j + 5, 1].LoadFromText((j + 1).ToString()); wsList.Cells[j + 5, 2].LoadFromText(lstDA_PARTY_PRODUCT_MATERIAL[j].MaterialName == null ? "" : lstDA_PARTY_PRODUCT_MATERIAL[j].MaterialName.ToString()); decimal quantity = lstDA_PARTY_PRODUCT_MATERIAL[j].Quantity; // wsList.Cells[j + 5, 3].LoadFromText(string.Format("{#,##0.####}", quantity)); wsList.Cells[j + 5, 3].Value = quantity; wsList.Cells[j + 5, 3].Style.Numberformat.Format = "#,##0.0000"; decimal unitPrice = lstDA_PARTY_PRODUCT_MATERIAL[j].UnitPrice ?? ""; wsList.Cells[j + 5, 5].Value = unitPrice; wsList.Cells[j + 5, 5].Style.Numberformat.Format = "#,##0"; wsList.Cells[j + 5, 4].LoadFromText(lstDA_PARTY_PRODUCT_MATERIAL[j].UOMName == null ? "" : lstDA_PARTY_PRODUCT_MATERIAL[j].UOMName.ToString()); wsList.Cells[j + 5, 6].LoadFromText(lstDA_PARTY_PRODUCT_MATERIAL[j].VendorName == null ? "" : lstDA_PARTY_PRODUCT_MATERIAL[j].VendorName.ToString()); // bool isDelivery = lstDA_PARTY_PRODUCT_MATERIAL[j].UnitPrice; wsList.Cells[j + 5, 7].LoadFromText(lstDA_PARTY_PRODUCT_MATERIAL[j].IsDelivery == true ? "Có" : "Không"); wsList.Cells[1, 1, j + 5, 7].Style.Font.Name = "Times New Roman"; wsList.Cells[1, 1, j + 3, 7].Style.Font.Size = 11; wsList.Cells[j + 5, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; wsList.Cells[j + 5, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; wsList.Cells[j + 5, 5].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; wsList.Cells[j + 5, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; wsList.Cells[j + 5, 6].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; wsList.Cells[j + 5, 7].Merge = true; //Khung Viền wsList.Cells[j + 5, 1, j + 5, 7].Style.Border.Left.Style = ExcelBorderStyle.Thin; wsList.Cells[j + 5, 1, j + 5, 7].Style.Border.Top.Style = ExcelBorderStyle.Thin; wsList.Cells[j + 5, 1, j + 5, 7].Style.Border.Right.Style = ExcelBorderStyle.Thin; wsList.Cells[j + 5, 1, j + 5, 7].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; //wsList.Cells[3, 1, nDong + 3, 4] //wsList.Cells[3, 1, nDong + 3, 4].Style.Border.Top.Style = ExcelBorderStyle.Thin; //wsList.Cells[3, 1, nDong + 3, 4].Style.Border.Right.Style = ExcelBorderStyle.Thin; //wsList.Cells[3, 1, nDong + 3, 4].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; } //} } catch (Exception ex) { string e = ex.Message; } #endregion //định dạng wrap text //định dạng width cho sheet wsList.Column(1).Width = 10; wsList.Column(2).Width = 20; wsList.Column(3).Width = 30; wsList.Column(4).Width = 10; wsList.Column(5).Width = 15; wsList.Column(6).Width = 20; wsList.Column(7).Width = 25; string fileName = (Path.GetDirectoryName(Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)) + "/Dinh luong tiec.xlsx").Remove(0, 6); Stream stream = System.IO.File.Create(fileName); pck.SaveAs(stream); stream.Close(); pck.Dispose(); return(true); } catch (Exception ex) { refer = ex.Message; return(false); } } }