protected override void OnData() { base.OnData(); if (Db.FromId((ulong)HHId) is HH hh) { Hdr = $"{hh.PP.CC.Ad}►{HH.FullParentAd(hh)}"; AAs.Data = HH.CumBky(hh); } }
public static Response HHsCumBkyXlsx(long HHId) { using (ExcelPackage pck = new ExcelPackage()) { ExcelWorksheet ws = pck.Workbook.Worksheets.Add("HHsCumBky"); // Header (first row) ws.Cells[1, 1].Value = "Yıl"; ws.Cells[1, 2].Value = "Ay"; ws.Cells[1, 3].Value = "Gider"; ws.Cells[1, 4].Value = "Gelir"; ws.Cells[1, 5].Value = "CumKln"; ws.Row(1).Style.Font.Bold = true; ws.Column(3).Style.Numberformat.Format = "#,###"; ws.Column(4).Style.Numberformat.Format = "#,###"; ws.Column(5).Style.Numberformat.Format = "#,###"; string OutputFileName = "HesapToplam.xlsx"; if (Db.FromId((ulong)HHId) is HH hh) { OutputFileName = $"HesapToplam-{HH.FullParentAd(hh)}-{DateTime.Today.ToString("yyMMdd")}.xlsx"; pck.Workbook.Properties.Title = "Hesap Toplamları"; pck.Workbook.Properties.Author = "Şener DEMİRAL"; pck.Workbook.Properties.Subject = $"{hh.PP.CC.Ad}►{HH.FullParentAd(hh)}"; int cr = 2; foreach (var ff in HH.CumBky(hh)) { ws.Cells[cr, 1].Value = ff.Yil; ws.Cells[cr, 2].Value = ff.Ay; ws.Cells[cr, 3].Value = ff.Gdr; ws.Cells[cr, 4].Value = ff.Glr; ws.Cells[cr, 5].Value = ff.CumBky; ws.Cells[cr, 6].Value = ff.Adt; cr++; } using (var range = ws.Cells["A1:F1"]) { range.AutoFilter = true; range.Style.Fill.PatternType = ExcelFillStyle.Solid; range.Style.Fill.BackgroundColor.SetColor(Color.LightGray); } ws.Row(1).Height = 20; ws.Row(1).Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; ws.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; ws.Column(1).Width = 10; ws.Column(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; ws.Column(2).Width = 10; ws.Column(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; ws.Column(3).Width = 12; ws.Column(4).Width = 12; ws.Column(5).Width = 12; ws.Column(6).Width = 10; ws.Column(6).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; } Response r = new Response(); //r.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; r.ContentType = "application/octet-stream"; //r.Headers["Content-Disposition"] = $"attachment; filename=\"HHsCumBky{DateTime.Today.ToString("yyMMdd")}.xlsx\""; r.Headers["Content-Disposition"] = $"attachment; filename=\"{OutputFileName}\""; var oms = new MemoryStream(); pck.SaveAs(oms); oms.Seek(0, SeekOrigin.Begin); r.StreamedBody = oms; return(r); } }
public static Response FFsXlsx(long PPId, long HHId, long TTId, string BasTrhX, string BitTrhX) { using (ExcelPackage pck = new ExcelPackage()) { ExcelWorksheet ws = pck.Workbook.Worksheets.Add("FFsRpr"); // Header (first row) ws.Cells[1, 1].Value = "Tarih"; ws.Cells["A1:A2"].Merge = true; ws.Cells[1, 2].Value = "Hesap"; ws.Cells["B1:B2"].Merge = true; ws.Cells[1, 3].Value = "Etiket"; ws.Cells["C1:C2"].Merge = true; ws.Cells[1, 8].Value = "Not"; ws.Cells["H1:H2"].Merge = true; ws.Cells[1, 4].Value = "Gercek"; ws.Cells["D1:E1"].Merge = true; ws.Cells[1, 6].Value = "Beklenen"; ws.Cells["F1:G1"].Merge = true; ws.Cells[2, 4].Value = "Gelir"; ws.Cells[2, 5].Value = "Gider"; ws.Cells[2, 6].Value = "Gelir"; ws.Cells[2, 7].Value = "Gider"; ws.Row(1).Style.Font.Bold = true; ws.Column(1).Style.Numberformat.Format = "dd.mm.yy"; ws.Column(4).Style.Numberformat.Format = "#,###"; ws.Column(5).Style.Numberformat.Format = "#,###"; ws.Column(6).Style.Numberformat.Format = "#,###"; ws.Column(7).Style.Numberformat.Format = "#,###"; if (Db.FromId((ulong)PPId) is PP pp) { string Hdr = $"{pp.CC.Ad}►{pp.Ad}"; if (!string.IsNullOrEmpty(BasTrhX)) { DateTime basTrh = Convert.ToDateTime(BasTrhX); DateTime bitTrh = Convert.ToDateTime(BitTrhX); if (basTrh == bitTrh) { Hdr = $"{pp.CC.Ad}►{pp.Ad}►{basTrh:dd.MM.yy}"; } else { Hdr = $"{pp.CC.Ad}►{pp.Ad}►{basTrh:dd.MM.yy} >=< {bitTrh:dd.MM.yy}"; } } if (Db.FromId((ulong)HHId) is HH hh) { Hdr = $"{Hdr}►{HH.FullParentAd(hh)}"; } if (Db.FromId((ulong)TTId) is TT tt) { Hdr = $"{Hdr}►{tt.Ad}"; } ws.HeaderFooter.OddHeader.CenteredText = Hdr; int cr = 3; IEnumerable <FF> ffs = FF.View(PPId, HHId, TTId, BasTrhX, BitTrhX); foreach (var ff in ffs.OrderByDescending((x) => x.Trh)) { ws.Cells[cr, 1].Value = ff.Trh; ws.Cells[cr, 2].Value = ff.HHAdFull; ws.Cells[cr, 3].Value = ff.TTAd; ws.Cells[cr, 4].Value = ff.Glr; ws.Cells[cr, 5].Value = ff.Gdr; ws.Cells[cr, 6].Value = ff.BklGlr; ws.Cells[cr, 7].Value = ff.BklGdr; ws.Cells[cr, 8].Value = ff.Ad; cr++; } using (var range = ws.Cells["A2:H2"]) { range.AutoFilter = true; //range.Style.Fill.PatternType = ExcelFillStyle.Solid; //range.Style.Fill.BackgroundColor.SetColor(Color.LightGray); } ws.Row(1).Height = 20; ws.Row(1).Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; ws.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; ws.Column(1).Width = 12; ws.Column(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; ws.Column(2).AutoFit(); ws.Column(3).AutoFit(); ws.Column(4).Width = 12; ws.Column(5).Width = 12; ws.Column(6).Width = 12; ws.Column(7).Width = 12; ws.Column(8).AutoFit(); ws.Cells[cr, 4].Formula = $"SUM(D2:D{cr-1})"; ws.Cells[cr, 5].Formula = $"SUM(E2:E{cr-1})"; ws.Cells[cr, 6].Formula = $"SUM(F2:F{cr-1})"; ws.Cells[cr, 7].Formula = $"SUM(G2:G{cr-1})"; using (var range = ws.Cells[$"D{cr}:G{cr}"]) { range.Style.Font.Bold = true; range.Style.Fill.PatternType = ExcelFillStyle.Solid; range.Style.Fill.BackgroundColor.SetColor(Color.LightGray); } } Response r = new Response(); //r.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; r.ContentType = "application/octet-stream"; r.Headers["Content-Disposition"] = "attachment; filename=\"FFsRpr.xlsx\""; var oms = new MemoryStream(); pck.SaveAs(oms); oms.Seek(0, SeekOrigin.Begin); r.StreamedBody = oms; return(r); } }