using System; using System.Collections.Generic; using System.IO; using ClosedXML.Excel; using FastReport; using FastReport.Export.Pdf; using Microsoft.AspNetCore.Mvc; using rmutr_report.Models; using Swashbuckle.AspNetCore.Annotations; namespace rmutr_report.Controllers { [SwaggerTag("สำหรับรายงานสรุปจำนวนนักศึกษาทั้งหมด")] public class SummaryStudentTotalCampus : Controller { readonly Setting _setting; public SummaryStudentTotalCampus(Setting setting) { _setting = setting; } [HttpPost, Route("reports/summary_student_total_campus/{type}")] [ApiExplorerSettings(GroupName = "reports")] public IActionResult GetDetailStd2Report([FromRoute] string type, [FromBody] student_totalcampus _std) { var hr1 = new List() {_std}; foreach (var header in _std.header_data) { header.total1 = header.male1 + header.female1; header.total2 = header.male2 + header.female2; header.total3 = header.male3 + header.female3; header.total4 = header.male4 + header.female4; header.total5 = header.male5 + header.female5; header.total6 = header.male6 + header.female6; header.total7 = header.male1 + header.male2 + header.male3 + header.male4 + header.male5 + header.male6 + header.female1 + header.female2 + header.female3 + header.female4 + header.female5 + header.female6; header.room7 = header.room1 + header.room2 + header.room3 + header.room4 + header.room5 + header.room6; header.male7 = header.male1 + header.male2 + header.male3 + header.male4 + header.male5 + header.male6; header.female7 = header.female1 + header.female2 + header.female3 + header.female4 + header.female5 + header.female6; foreach (var data in header.data) { data.total1 = data.male1 + data.female1; data.total2 = data.male2 + data.female2; data.total3 = data.male3 + data.female3; data.total4 = data.male4 + data.female4; data.total5 = data.male5 + data.female5; data.total6 = data.male6 + data.female6; data.total7 = data.male1 + data.male2 + data.male3 + data.male4 + data.male5 + data.male6 + data.female1 + data.female2 + data.female3 + data.female4 + data.female5 + data.female6; data.room7 = data.room1 + data.room2 + data.room3 + data.room4 + data.room5 + data.room6; data.male7 = data.male1 + data.male2 + data.male3 + data.male4 + data.male5 + data.male6; data.female7 = data.female1 + data.female2 + data.female3 + data.female4 + data.female5 + data.female6; if (header.filter == "0") { data.name = null; data.male1 = null; data.male2 = null; data.male3 = null; data.male4 = null; data.male5 = null; data.male6 = null; data.male7 = null; data.female1 = null; data.female2 = null; data.female3 = null; data.female4 = null; data.female5 = null; data.female6 = null; data.female7 = null; data.total1 = null; data.total2 = null; data.total3 = null; data.total4 = null; data.total5 = null; data.total6 = null; data.total7 = null; data.room1 = null; data.room2 = null; data.room3 = null; data.room4 = null; data.room5 = null; data.room6 = null; data.room7 = null; } } if (header.filter == "0") { header.header_name = null; header.male1 = null; header.male2 = null; header.male3 = null; header.male4 = null; header.male5 = null; header.male6 = null; header.male7 = null; header.female1 = null; header.female2 = null; header.female3 = null; header.female4 = null; header.female5 = null; header.female6 = null; header.female7 = null; header.total1 = null; header.total2 = null; header.total3 = null; header.total4 = null; header.total5 = null; header.total6 = null; header.total7 = null; header.room1 = null; header.room2 = null; header.room3 = null; header.room4 = null; header.room5 = null; header.room6 = null; header.room7 = null; } } Report report = new Report(); report.Load(_setting.report_path + "summary_student_total_campus.frx"); report.RegisterData(hr1, "student_totalcampus"); report.Prepare(); MemoryStream stream1 = new MemoryStream(); switch (type) { case "pdf": PDFExport pdf = new PDFExport(); report.Export(pdf, stream1); stream1.Seek(0, SeekOrigin.Begin); return File(stream1, "application/pdf"); } var workbook = new XLWorkbook(); var ws = workbook.Worksheets.Add("student_total_campus"); ws.Range("A1:AD1").Merge().Value = "จำนวนนักศึกษาทั้งหมด ปีการศึกษา " + _std.academic_year_name_th + " ภาคการศึกษาที่ " + _std.academic_semester_name_th + " มหาวิทยาลัยเทคโนโลยีราชมงคลรัตนโกสินทร์"; ws.Cell("A1").Style.Font.Bold = true; ws.Cell("A1").Style.Alignment.WrapText = true; ws.Cell("A1").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("A1").Style.Font.FontSize = 16; ws.Range("A1:AD1").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range("A1:AD1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Range("A2:B3").Merge().Value = "คณะ/วิทยาลัย - สาขาวิชา"; ws.Range("A2:B3").Style.Font.Bold = true; ws.Range("A2:B3").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range("A2:B3").Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Range("C2:F2").Merge().Value = "ชั้นปีที่ 1 " + "(" + _std.admit_year1 + ")"; ws.Range("C2:F2").Style.Font.Bold = true; ws.Range("C2:F2").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range("C2:F2").Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Cell("C3").Value = "ชาย"; ws.Cell("D3").Value = "หญิง"; ws.Cell("E3").Value = "รวม"; ws.Cell("F3").Value = "จำนวนห้อง"; ws.Range("G2:J2").Merge().Value = "ชั้นปีที่ 2 " + "(" + _std.admit_year2 + ")"; ws.Range("G2:J2").Style.Font.Bold = true; ws.Range("G2:J2").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range("G2:J2").Style.Border.RightBorder = XLBorderStyleValues.Thin; //ws.Range("G2:J2").Style.Fill.BackgroundColor = XLColor.LavenderBlush; ws.Cell("G3").Value = "ชาย"; ws.Cell("H3").Value = "หญิง"; ws.Cell("I3").Value = "รวม"; ws.Cell("J3").Value = "จำนวนห้อง"; ws.Range("K2:N2").Merge().Value = "ชั้นปีที่ 3 " + "(" + _std.admit_year3 + ")"; ws.Range("K2:N2").Style.Font.Bold = true; ws.Range("K2:N2").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range("K2:N2").Style.Border.RightBorder = XLBorderStyleValues.Thin; //ws.Range("K2:N2").Style.Fill.BackgroundColor = XLColor.LavenderBlush; ws.Cell("K3").Value = "ชาย"; ws.Cell("L3").Value = "หญิง"; ws.Cell("M3").Value = "รวม"; ws.Cell("N3").Value = "จำนวนห้อง"; ws.Range("O2:R2").Merge().Value = "ชั้นปีที่ 4 " + "(" + _std.admit_year4 + ")"; ws.Range("O2:R2").Style.Font.Bold = true; ws.Range("O2:R2").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range("O2:R2").Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Cell("O3").Value = "ชาย"; ws.Cell("P3").Value = "หญิง"; ws.Cell("Q3").Value = "รวม"; ws.Cell("R3").Value = "จำนวนห้อง"; ws.Range("S2:V2").Merge().Value = "ชั้นปีที่ 5 " + "(" + _std.admit_year5 + ")"; ws.Range("S2:V2").Style.Font.Bold = true; ws.Range("S2:V2").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range("S2:V2").Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Cell("S3").Value = "ชาย"; ws.Cell("T3").Value = "หญิง"; ws.Cell("U3").Value = "รวม"; ws.Cell("V3").Value = "จำนวนห้อง"; ws.Range("W2:Z2").Merge().Value = "นักศึกษาตกรุ่น/>ชั้นปีที่ 4/5"; ws.Range("W2:Z2").Style.Font.Bold = true; ws.Range("W2:Z2").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range("W2:Z2").Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Cell("W3").Value = "ชาย"; ws.Cell("X3").Value = "หญิง"; ws.Cell("Y3").Value = "รวม"; ws.Cell("Z3").Value = "จำนวนห้อง"; ws.Range("AA2:AD2").Merge().Value = "รวม"; ws.Range("AA2:AD2").Style.Font.Bold = true; ws.Range("AA2:AD2").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range("AA2:AD2").Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Cell("AA3").Value = "ชาย"; ws.Cell("AB3").Value = "หญิง"; ws.Cell("AC3").Value = "รวม"; ws.Cell("AD3").Value = "จำนวนห้อง"; ws.Range("A2:AD3").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range("A2:AD3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Range("C3:AD3").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range("C3:F3").Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Range("G3:J3").Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Range("K3:N3").Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Range("O3:R3").Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Range("S3:V3").Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Range("W3:Z3").Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Range("AA3:AD3").Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Range("AD3").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Row(1).AdjustToContents(); ws.Column(1).Width = 5; ws.Column(2).Width = 30; ws.Column(3).Width = 10; ws.Column(4).Width = 10; ws.Column(5).Width = 10; ws.Column(6).Width = 10; ws.Column(7).Width = 10; ws.Column(8).Width = 10; ws.Column(9).Width = 10; ws.Column(10).Width = 10; ws.Column(11).Width = 10; ws.Column(12).Width = 10; ws.Column(13).Width = 10; ws.Column(15).Width = 10; ws.Column(16).Width = 10; ws.Column(17).Width = 10; ws.Column(18).Width = 10; ws.Column(19).Width = 10; ws.Column(20).Width = 10; ws.Column(21).Width = 10; ws.Column(22).Width = 10; ws.Column(23).Width = 10; ws.Column(24).Width = 10; ws.Column(25).Width = 10; ws.Column(26).Width = 10; ws.Column(27).Width = 10; ws.Range("A2:AD3").Style.Font.FontName = "TH SarabunPSK"; ws.Range("A2:AD3").Style.Font.FontSize = 16; ws.Range("A2:AD3").Style.Fill.BackgroundColor = XLColor.Bisque; int row = 4; int no = 1; //int rowno = 1; if (_std != null) { foreach (var header in _std.header_data) { decimal? sum1 = header.male1 + header.female1; decimal? sum2 = header.male2 + header.female2; decimal? sum3 = header.male3 + header.female3; decimal? sum4 = header.male4 + header.female4; decimal? sum5 = header.male5 + header.female5; decimal? sum6 = header.male6 + header.female6; decimal? sumroom = header.room1 + header.room2 + header.room3 + header.room4 + header.room5 + header.room6; if (header.filter == "1") { ws.Cell(row, 1).Value = header.header_name; ws.Cell(row, 2).Value = null; ws.Cell(row, 3).Value = header.male1; ws.Cell(row, 4).Value = header.female1; ws.Cell(row, 5).Value = sum1; ws.Cell(row, 6).Value = header.room1; ws.Cell(row, 7).Value = header.male2; ws.Cell(row, 8).Value = header.female2; ws.Cell(row, 9).Value = sum2; ws.Cell(row, 10).Value = header.room2; ws.Cell(row, 11).Value = header.male3; ws.Cell(row, 12).Value = header.female3; ws.Cell(row, 13).Value = sum3; ws.Cell(row, 14).Value = header.room3; ws.Cell(row, 15).Value = header.male4; ws.Cell(row, 16).Value = header.female4; ws.Cell(row, 17).Value = sum4; ws.Cell(row, 18).Value = header.room4; ws.Cell(row, 19).Value = header.male5; ws.Cell(row, 20).Value = header.female5; ws.Cell(row, 21).Value = sum5; ws.Cell(row, 22).Value = header.room5; ws.Cell(row, 23).Value = header.male6; ws.Cell(row, 24).Value = header.female6; ws.Cell(row, 25).Value = sum6; ws.Cell(row, 26).Value = header.room6; ws.Cell(row, 27).Value = header.male1 + header.male2 + header.male3 + header.male4 + header.male5 + header.male6; ws.Cell(row, 28).Value = header.female1 + header.female2 + header.female3 + header.female4 + header.female5 + header.female6; ws.Cell(row, 29).Value = sum1 + sum2 + sum3 + sum4 + sum5 + sum6; ws.Cell(row, 30).Value = sumroom; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontName = "TH SarabunPSK"; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontSize = 14; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.Bold = true; ws.Range(ws.Cell(row, 1), ws.Cell(row, 2)).Merge().Style.Fill.BackgroundColor = XLColor.ColumbiaBlue; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Fill.BackgroundColor = XLColor.ColumbiaBlue; ws.Cell(row, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; ws.Cell(row, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.NumberFormat.SetFormat("#,#"); row++; } // if (header.filter == (double?) 1.1) // { // foreach (var detail in header.data) // { // ws.Cell(row, 1).Value = null; // ws.Cell(row, 2).Value = detail.name; // ws.Cell(row, 3).Value = detail.male1; // ws.Cell(row, 4).Value = detail.female1; // ws.Cell(row, 5).Value = detail.total1; // ws.Cell(row, 6).Value = detail.room1; // ws.Cell(row, 7).Value = detail.male2; // ws.Cell(row, 8).Value = detail.female2; // ws.Cell(row, 9).Value = detail.total2; // ws.Cell(row, 10).Value = detail.room2; // ws.Cell(row, 11).Value = detail.male3; // ws.Cell(row, 12).Value = detail.female3; // ws.Cell(row, 13).Value = detail.total3; // ws.Cell(row, 14).Value = detail.room3; // ws.Cell(row, 15).Value = detail.male4; // ws.Cell(row, 16).Value = detail.female4; // ws.Cell(row, 17).Value = detail.total4; // ws.Cell(row, 18).Value = detail.room4; // ws.Cell(row, 19).Value = detail.male5; // ws.Cell(row, 20).Value = detail.female5; // ws.Cell(row, 21).Value = detail.total5; // ws.Cell(row, 22).Value = detail.room5; // ws.Cell(row, 23).Value = detail.male6; // ws.Cell(row, 24).Value = detail.female6; // ws.Cell(row, 25).Value = detail.total6; // ws.Cell(row, 26).Value = detail.room6; // ws.Cell(row, 27).Value = detail.male7; // ws.Cell(row, 28).Value = detail.female7; // ws.Cell(row, 29).Value = detail.total7; // ws.Cell(row, 30).Value = detail.room7; // ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontName = "TH SarabunPSK"; // ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontSize = 14; // ws.Cell(row, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; // ws.Cell(row, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; // ws.Cell(row, 2).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; // // ws.Range(ws.Cell(row, 1), ws.Cell(row, 2)).Merge().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; // // ws.Range(ws.Cell(row, 1), ws.Cell(row, 2)).Merge().Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; // ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Horizontal = // XLAlignmentHorizontalValues.Right; // ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Vertical = // XLAlignmentVerticalValues.Center; // ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; // ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.RightBorder = XLBorderStyleValues.Thin; // row++; // } // } if (header.filter == "2") { ws.Cell(row, 1).Value = header.header_name; ws.Cell(row, 2).Value = null; ws.Cell(row, 3).Value = header.male1; ws.Cell(row, 4).Value = header.female1; ws.Cell(row, 5).Value = sum1; ws.Cell(row, 6).Value = header.room1; ws.Cell(row, 7).Value = header.male2; ws.Cell(row, 8).Value = header.female2; ws.Cell(row, 9).Value = sum2; ws.Cell(row, 10).Value = header.room2; ws.Cell(row, 11).Value = header.male3; ws.Cell(row, 12).Value = header.female3; ws.Cell(row, 13).Value = sum3; ws.Cell(row, 14).Value = header.room3; ws.Cell(row, 15).Value = header.male4; ws.Cell(row, 16).Value = header.female4; ws.Cell(row, 17).Value = sum4; ws.Cell(row, 18).Value = header.room4; ws.Cell(row, 19).Value = header.male5; ws.Cell(row, 20).Value = header.female5; ws.Cell(row, 21).Value = sum5; ws.Cell(row, 22).Value = header.room5; ws.Cell(row, 23).Value = header.male6; ws.Cell(row, 24).Value = header.female6; ws.Cell(row, 25).Value = sum6; ws.Cell(row, 26).Value = header.room6; ws.Cell(row, 27).Value = header.male1 + header.male2 + header.male3 + header.male4 + header.male5 + header.male6; ws.Cell(row, 28).Value = header.female1 + header.female2 + header.female3 + header.female4 + header.female5 + header.female6; ws.Cell(row, 29).Value = sum1 + sum2 + sum3 + sum4 + sum5 + sum6; ws.Cell(row, 30).Value = sumroom; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontName = "TH SarabunPSK"; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontSize = 14; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.Bold = true; ws.Range(ws.Cell(row, 1), ws.Cell(row, 2)).Merge().Style.Fill.BackgroundColor = XLColor.CadmiumYellow; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Fill.BackgroundColor = XLColor.CadmiumYellow; ws.Cell(row, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; ws.Cell(row, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.NumberFormat.SetFormat("#,#"); row++; } if (header.filter == "3") { ws.Cell(row, 1).Value = header.header_name; ws.Cell(row, 2).Value = null; ws.Cell(row, 3).Value = header.male1; ws.Cell(row, 4).Value = header.female1; ws.Cell(row, 5).Value = sum1; ws.Cell(row, 6).Value = header.room1; ws.Cell(row, 7).Value = header.male2; ws.Cell(row, 8).Value = header.female2; ws.Cell(row, 9).Value = sum2; ws.Cell(row, 10).Value = header.room2; ws.Cell(row, 11).Value = header.male3; ws.Cell(row, 12).Value = header.female3; ws.Cell(row, 13).Value = sum3; ws.Cell(row, 14).Value = header.room3; ws.Cell(row, 15).Value = header.male4; ws.Cell(row, 16).Value = header.female4; ws.Cell(row, 17).Value = sum4; ws.Cell(row, 18).Value = header.room4; ws.Cell(row, 19).Value = header.male5; ws.Cell(row, 20).Value = header.female5; ws.Cell(row, 21).Value = sum5; ws.Cell(row, 22).Value = header.room5; ws.Cell(row, 23).Value = header.male6; ws.Cell(row, 24).Value = header.female6; ws.Cell(row, 25).Value = sum6; ws.Cell(row, 26).Value = header.room6; ws.Cell(row, 27).Value = header.male1 + header.male2 + header.male3 + header.male4 + header.male5 + header.male6; ws.Cell(row, 28).Value = header.female1 + header.female2 + header.female3 + header.female4 + header.female5 + header.female6; ws.Cell(row, 29).Value = sum1 + sum2 + sum3 + sum4 + sum5 + sum6; ws.Cell(row, 30).Value = sumroom; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontName = "TH SarabunPSK"; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontSize = 14; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.Bold = true; ws.Range(ws.Cell(row, 1), ws.Cell(row, 2)).Merge().Style.Fill.BackgroundColor = XLColor.White; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Fill.BackgroundColor = XLColor.White; ws.Cell(row, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; ws.Cell(row, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.Underline = XLFontUnderlineValues.Single; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.NumberFormat.SetFormat("#,#"); row++; } if (header.filter == "4") { ws.Cell(row, 1).Value = header.header_name; ws.Cell(row, 2).Value = null; ws.Cell(row, 3).Value = header.male1; ws.Cell(row, 4).Value = header.female1; ws.Cell(row, 5).Value = sum1; ws.Cell(row, 6).Value = header.room1; ws.Cell(row, 7).Value = header.male2; ws.Cell(row, 8).Value = header.female2; ws.Cell(row, 9).Value = sum2; ws.Cell(row, 10).Value = header.room2; ws.Cell(row, 11).Value = header.male3; ws.Cell(row, 12).Value = header.female3; ws.Cell(row, 13).Value = sum3; ws.Cell(row, 14).Value = header.room3; ws.Cell(row, 15).Value = header.male4; ws.Cell(row, 16).Value = header.female4; ws.Cell(row, 17).Value = sum4; ws.Cell(row, 18).Value = header.room4; ws.Cell(row, 19).Value = header.male5; ws.Cell(row, 20).Value = header.female5; ws.Cell(row, 21).Value = sum5; ws.Cell(row, 22).Value = header.room5; ws.Cell(row, 23).Value = header.male6; ws.Cell(row, 24).Value = header.female6; ws.Cell(row, 25).Value = sum6; ws.Cell(row, 26).Value = header.room6; ws.Cell(row, 27).Value = header.male1 + header.male2 + header.male3 + header.male4 + header.male5 + header.male6; ws.Cell(row, 28).Value = header.female1 + header.female2 + header.female3 + header.female4 + header.female5 + header.female6; ws.Cell(row, 29).Value = sum1 + sum2 + sum3 + sum4 + sum5 + sum6; ws.Cell(row, 30).Value = sumroom; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontName = "TH SarabunPSK"; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontSize = 14; ws.Range(ws.Cell(row, 1), ws.Cell(row, 2)).Merge().Style.Fill.BackgroundColor = XLColor.White; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Fill.BackgroundColor = XLColor.White; ws.Cell(row, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; ws.Cell(row, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.NumberFormat.SetFormat("#,#"); row++; } // // if (header.filter == (double?) 4.2) // { // foreach (var detail in header.data) // { // ws.Cell(row, 1).Value = detail.name; // ws.Cell(row, 2).Value = null; // ws.Cell(row, 3).Value = detail.male1; // ws.Cell(row, 4).Value = detail.female1; // ws.Cell(row, 5).Value = detail.total1; // ws.Cell(row, 6).Value = detail.room1; // ws.Cell(row, 7).Value = detail.male2; // ws.Cell(row, 8).Value = detail.female2; // ws.Cell(row, 9).Value = detail.total2; // ws.Cell(row, 10).Value = detail.room2; // ws.Cell(row, 11).Value = detail.male3; // ws.Cell(row, 12).Value = detail.female3; // ws.Cell(row, 13).Value = detail.total3; // ws.Cell(row, 14).Value = detail.room3; // ws.Cell(row, 15).Value = detail.male4; // ws.Cell(row, 16).Value = detail.female4; // ws.Cell(row, 17).Value = detail.total4; // ws.Cell(row, 18).Value = detail.room4; // ws.Cell(row, 19).Value = detail.male5; // ws.Cell(row, 20).Value = detail.female5; // ws.Cell(row, 21).Value = detail.total5; // ws.Cell(row, 22).Value = detail.room5; // ws.Cell(row, 23).Value = detail.male6; // ws.Cell(row, 24).Value = detail.female6; // ws.Cell(row, 25).Value = detail.total6; // ws.Cell(row, 26).Value = detail.room6; // ws.Cell(row, 27).Value = detail.male7; // ws.Cell(row, 28).Value = detail.female7; // ws.Cell(row, 29).Value = detail.total7; // ws.Cell(row, 30).Value = detail.room7; // ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontName = "TH SarabunPSK"; // ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontSize = 14; // // ws.Cell(row, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; // // ws.Cell(row, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; // ws.Range(ws.Cell(row, 1), ws.Cell(row, 2)).Merge().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; // ws.Range(ws.Cell(row, 1), ws.Cell(row, 2)).Merge().Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; // ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Horizontal = // XLAlignmentHorizontalValues.Right; // ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Vertical = // XLAlignmentVerticalValues.Center; // ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; // ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.RightBorder = XLBorderStyleValues.Thin; // row++; // } // } if (header.filter == "5") { ws.Cell(row, 1).Value = header.header_name; ws.Cell(row, 2).Value = null; ws.Cell(row, 3).Value = header.male1; ws.Cell(row, 4).Value = header.female1; ws.Cell(row, 5).Value = sum1; ws.Cell(row, 6).Value = header.room1; ws.Cell(row, 7).Value = header.male2; ws.Cell(row, 8).Value = header.female2; ws.Cell(row, 9).Value = sum2; ws.Cell(row, 10).Value = header.room2; ws.Cell(row, 11).Value = header.male3; ws.Cell(row, 12).Value = header.female3; ws.Cell(row, 13).Value = sum3; ws.Cell(row, 14).Value = header.room3; ws.Cell(row, 15).Value = header.male4; ws.Cell(row, 16).Value = header.female4; ws.Cell(row, 17).Value = sum4; ws.Cell(row, 18).Value = header.room4; ws.Cell(row, 19).Value = header.male5; ws.Cell(row, 20).Value = header.female5; ws.Cell(row, 21).Value = sum5; ws.Cell(row, 22).Value = header.room5; ws.Cell(row, 23).Value = header.male6; ws.Cell(row, 24).Value = header.female6; ws.Cell(row, 25).Value = sum6; ws.Cell(row, 26).Value = header.room6; ws.Cell(row, 27).Value = header.male1 + header.male2 + header.male3 + header.male4 + header.male5 + header.male6; ws.Cell(row, 28).Value = header.female1 + header.female2 + header.female3 + header.female4 + header.female5 + header.female6; ws.Cell(row, 29).Value = sum1 + sum2 + sum3 + sum4 + sum5 + sum6; ws.Cell(row, 30).Value = sumroom; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontName = "TH SarabunPSK"; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontSize = 14; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.Bold = true; ws.Range(ws.Cell(row, 1), ws.Cell(row, 2)).Merge().Style.Fill.BackgroundColor = XLColor.White; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Fill.BackgroundColor = XLColor.White; ws.Cell(row, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; ws.Cell(row, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.NumberFormat.SetFormat("#,#"); row++; } if (header.filter == "6") { ws.Cell(row, 1).Value = header.header_name; ws.Cell(row, 2).Value = null; ws.Cell(row, 3).Value = header.male1; ws.Cell(row, 4).Value = header.female1; ws.Cell(row, 5).Value = sum1; ws.Cell(row, 6).Value = header.room1; ws.Cell(row, 7).Value = header.male2; ws.Cell(row, 8).Value = header.female2; ws.Cell(row, 9).Value = sum2; ws.Cell(row, 10).Value = header.room2; ws.Cell(row, 11).Value = header.male3; ws.Cell(row, 12).Value = header.female3; ws.Cell(row, 13).Value = sum3; ws.Cell(row, 14).Value = header.room3; ws.Cell(row, 15).Value = header.male4; ws.Cell(row, 16).Value = header.female4; ws.Cell(row, 17).Value = sum4; ws.Cell(row, 18).Value = header.room4; ws.Cell(row, 19).Value = header.male5; ws.Cell(row, 20).Value = header.female5; ws.Cell(row, 21).Value = sum5; ws.Cell(row, 22).Value = header.room5; ws.Cell(row, 23).Value = header.male6; ws.Cell(row, 24).Value = header.female6; ws.Cell(row, 25).Value = sum5; ws.Cell(row, 26).Value = header.room6; ws.Cell(row, 27).Value = header.male1 + header.male2 + header.male3 + header.male4 + header.male5 + header.male6; ws.Cell(row, 28).Value = header.female1 + header.female2 + header.female3 + header.female4 + header.female5 + header.female6; ws.Cell(row, 29).Value = sum1 + sum2 + sum3 + sum4 + sum5 + sum6; ws.Cell(row, 30).Value = sumroom; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontName = "TH SarabunPSK"; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontSize = 14; ws.Range(ws.Cell(row, 1), ws.Cell(row, 2)).Merge().Style.Fill.BackgroundColor = XLColor.MintGreen; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Fill.BackgroundColor = XLColor.MintGreen; ws.Range(ws.Cell(row, 1), ws.Cell(row, 2)).Style.Font.Bold = true; ws.Cell(row, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; ws.Cell(row, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.NumberFormat.SetFormat("#,#"); row++; } if (header.filter == "6.1") { foreach (var detail in header.data) { decimal? sums1 = detail.male1 + detail.female1; decimal? sums2 = detail.male2 + detail.female2; decimal? sums3 = detail.male3 + detail.female3; decimal? sums4 = detail.male4 + detail.female4; decimal? sums5 = detail.male5 + detail.female5; decimal? sums6 = detail.male6 + detail.female6; decimal? sumsroom = detail.room1 + detail.room2 + detail.room3 + detail.room4 + detail.room5 + detail.room6; ws.Cell(row, 1).Value = detail.name; ws.Cell(row, 2).Value = null; ws.Cell(row, 3).Value = detail.male1; ws.Cell(row, 4).Value = detail.female1; ws.Cell(row, 5).Value = sums1; ws.Cell(row, 6).Value = detail.room1; ws.Cell(row, 7).Value = detail.male2; ws.Cell(row, 8).Value = detail.female2; ws.Cell(row, 9).Value = sums2; ws.Cell(row, 10).Value = detail.room2; ws.Cell(row, 11).Value = detail.male3; ws.Cell(row, 12).Value = detail.female3; ws.Cell(row, 13).Value = sums3; ws.Cell(row, 14).Value = detail.room3; ws.Cell(row, 15).Value = detail.male4; ws.Cell(row, 16).Value = detail.female4; ws.Cell(row, 17).Value = sums4; ws.Cell(row, 18).Value = detail.room4; ws.Cell(row, 19).Value = detail.male5; ws.Cell(row, 20).Value = detail.female5; ws.Cell(row, 21).Value = sums5; ws.Cell(row, 22).Value = detail.room5; ws.Cell(row, 23).Value = detail.male6; ws.Cell(row, 24).Value = detail.female6; ws.Cell(row, 25).Value = sums6; ws.Cell(row, 26).Value = detail.room6; ws.Cell(row, 27).Value = detail.male1 + detail.male2 + detail.male3 + detail.male4 + detail.male5 + detail.male6; ws.Cell(row, 28).Value = detail.female1 + detail.female2 + detail.female3 + detail.female4 + detail.female5 + detail.female6; ws.Cell(row, 29).Value = sums1 + sums2 + sums3 + sums4 + sums5 + sums6; ws.Cell(row, 30).Value = sumsroom; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontName = "TH SarabunPSK"; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontSize = 14; ws.Range(ws.Cell(row, 1), ws.Cell(row, 2)).Merge().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; ws.Range(ws.Cell(row, 1), ws.Cell(row, 2)).Merge().Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.NumberFormat.SetFormat("#,#"); //rowno++; row++; } } if (header.filter == "7") { ws.Cell(row, 1).Value = header.header_name; ws.Cell(row, 2).Value = null; ws.Cell(row, 3).Value = header.male1; ws.Cell(row, 4).Value = header.female1; ws.Cell(row, 5).Value = sum1; ws.Cell(row, 6).Value = header.room1; ws.Cell(row, 7).Value = header.male2; ws.Cell(row, 8).Value = header.female2; ws.Cell(row, 9).Value = sum2; ws.Cell(row, 10).Value = header.room2; ws.Cell(row, 11).Value = header.male3; ws.Cell(row, 12).Value = header.female3; ws.Cell(row, 13).Value = sum3; ws.Cell(row, 14).Value = header.room3; ws.Cell(row, 15).Value = header.male4; ws.Cell(row, 16).Value = header.female4; ws.Cell(row, 17).Value = sum4; ws.Cell(row, 18).Value = header.room4; ws.Cell(row, 19).Value = header.male5; ws.Cell(row, 20).Value = header.female5; ws.Cell(row, 21).Value = sum5; ws.Cell(row, 22).Value = header.room5; ws.Cell(row, 23).Value = header.male6; ws.Cell(row, 24).Value = header.female6; ws.Cell(row, 25).Value = sum6; ws.Cell(row, 26).Value = header.room6; ws.Cell(row, 27).Value = header.male1 + header.male2 + header.male3 + header.male4 + header.male5 + header.male6; ws.Cell(row, 28).Value = header.female1 + header.female2 + header.female3 + header.female4 + header.female5 + header.female6; ws.Cell(row, 29).Value = sum1 + sum2 + sum3 + sum4 + sum5 + sum6; ws.Cell(row, 30).Value = sumroom; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontName = "TH SarabunPSK"; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontSize = 14; ws.Range(ws.Cell(row, 1), ws.Cell(row, 2)).Merge().Style.Fill.BackgroundColor = XLColor.BabyBlue; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Fill.BackgroundColor = XLColor.BabyBlue; ws.Range(ws.Cell(row, 1), ws.Cell(row, 2)).Style.Font.Bold = true; ws.Cell(row, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; ws.Cell(row, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.NumberFormat.SetFormat("#,#"); row++; } if (header.filter == "7.1") { foreach (var detail in header.data) { decimal? sums1 = detail.male1 + detail.female1; decimal? sums2 = detail.male2 + detail.female2; decimal? sums3 = detail.male3 + detail.female3; decimal? sums4 = detail.male4 + detail.female4; decimal? sums5 = detail.male5 + detail.female5; decimal? sums6 = detail.male6 + detail.female6; decimal? sumsroom = detail.room1 + detail.room2 + detail.room3 + detail.room4 + detail.room5 + detail.room6; ws.Cell(row, 1).Value = detail.name; ws.Cell(row, 2).Value = null; ws.Cell(row, 3).Value = detail.male1; ws.Cell(row, 4).Value = detail.female1; ws.Cell(row, 5).Value = sums1; ws.Cell(row, 6).Value = detail.room1; ws.Cell(row, 7).Value = detail.male2; ws.Cell(row, 8).Value = detail.female2; ws.Cell(row, 9).Value = sums2; ws.Cell(row, 10).Value = detail.room2; ws.Cell(row, 11).Value = detail.male3; ws.Cell(row, 12).Value = detail.female3; ws.Cell(row, 13).Value = sums3; ws.Cell(row, 14).Value = detail.room3; ws.Cell(row, 15).Value = detail.male4; ws.Cell(row, 16).Value = detail.female4; ws.Cell(row, 17).Value = sums4; ws.Cell(row, 18).Value = detail.room4; ws.Cell(row, 19).Value = detail.male5; ws.Cell(row, 20).Value = detail.female5; ws.Cell(row, 21).Value = sums5; ws.Cell(row, 22).Value = detail.room5; ws.Cell(row, 23).Value = detail.male6; ws.Cell(row, 24).Value = detail.female6; ws.Cell(row, 25).Value = sums6; ws.Cell(row, 26).Value = detail.room6; ws.Cell(row, 27).Value = detail.male1 + detail.male2 + detail.male3 + detail.male4 + detail.male5 + detail.male6; ws.Cell(row, 28).Value = detail.female1 + detail.female2 + detail.female3 + detail.female4 + detail.female5 + detail.female6; ws.Cell(row, 29).Value = sums1 + sums2 + sums3 + sums4 + sums5 + sums6; ws.Cell(row, 30).Value = sumsroom; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontName = "TH SarabunPSK"; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontSize = 14; ws.Range(ws.Cell(row, 1), ws.Cell(row, 2)).Merge().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; ws.Range(ws.Cell(row, 1), ws.Cell(row, 2)).Merge().Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.NumberFormat.SetFormat("#,#"); //rowno++; row++; } } if (header.filter == "8") { ws.Cell(row, 1).Value = header.header_name; ws.Cell(row, 2).Value = null; ws.Cell(row, 3).Value = header.male1; ws.Cell(row, 4).Value = header.female1; ws.Cell(row, 5).Value = sum1; ws.Cell(row, 6).Value = header.room1; ws.Cell(row, 7).Value = header.male2; ws.Cell(row, 8).Value = header.female2; ws.Cell(row, 9).Value = sum2; ws.Cell(row, 10).Value = header.room2; ws.Cell(row, 11).Value = header.male3; ws.Cell(row, 12).Value = header.female3; ws.Cell(row, 13).Value = sum3; ws.Cell(row, 14).Value = header.room3; ws.Cell(row, 15).Value = header.male4; ws.Cell(row, 16).Value = header.female4; ws.Cell(row, 17).Value = sum4; ws.Cell(row, 18).Value = header.room4; ws.Cell(row, 19).Value = header.male5; ws.Cell(row, 20).Value = header.female5; ws.Cell(row, 21).Value = sum5; ws.Cell(row, 22).Value = header.room5; ws.Cell(row, 23).Value = header.male6; ws.Cell(row, 24).Value = header.female6; ws.Cell(row, 25).Value = sum6; ws.Cell(row, 26).Value = header.room6; ws.Cell(row, 27).Value = header.male1 + header.male2 + header.male3 + header.male4 + header.male5 + header.male6; ws.Cell(row, 28).Value = header.female1 + header.female2 + header.female3 + header.female4 + header.female5 + header.female6; ws.Cell(row, 29).Value = sum1 + sum2 + sum3 + sum4 + sum5 + sum6; ws.Cell(row, 30).Value = sumroom; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontName = "TH SarabunPSK"; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontSize = 14; ws.Range(ws.Cell(row, 1), ws.Cell(row, 2)).Merge().Style.Fill.BackgroundColor = XLColor.Gainsboro; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Fill.BackgroundColor = XLColor.Gainsboro; ws.Cell(row, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; ws.Cell(row, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.NumberFormat.SetFormat("#,#"); row++; } if (header.filter == "8.1") { foreach (var detail in header.data) { decimal? sums1 = detail.male1 + detail.female1; decimal? sums2 = detail.male2 + detail.female2; decimal? sums3 = detail.male3 + detail.female3; decimal? sums4 = detail.male4 + detail.female4; decimal? sums5 = detail.male5 + detail.female5; decimal? sums6 = detail.male6 + detail.female6; decimal? sumsroom = detail.room1 + detail.room2 + detail.room3 + detail.room4 + detail.room5 + detail.room6; ws.Cell(row, 1).Value = detail.name; ws.Cell(row, 2).Value = null; ws.Cell(row, 3).Value = detail.male1; ws.Cell(row, 4).Value = detail.female1; ws.Cell(row, 5).Value = sums1; ws.Cell(row, 6).Value = detail.room1; ws.Cell(row, 7).Value = detail.male2; ws.Cell(row, 8).Value = detail.female2; ws.Cell(row, 9).Value = sums2; ws.Cell(row, 10).Value = detail.room2; ws.Cell(row, 11).Value = detail.male3; ws.Cell(row, 12).Value = detail.female3; ws.Cell(row, 13).Value = sums3; ws.Cell(row, 14).Value = detail.room3; ws.Cell(row, 15).Value = detail.male4; ws.Cell(row, 16).Value = detail.female4; ws.Cell(row, 17).Value = sums4; ws.Cell(row, 18).Value = detail.room4; ws.Cell(row, 19).Value = detail.male5; ws.Cell(row, 20).Value = detail.female5; ws.Cell(row, 21).Value = sums5; ws.Cell(row, 22).Value = detail.room5; ws.Cell(row, 23).Value = detail.male6; ws.Cell(row, 24).Value = detail.female6; ws.Cell(row, 25).Value = sums6; ws.Cell(row, 26).Value = detail.room6; ws.Cell(row, 27).Value = detail.male1 + detail.male2 + detail.male3 + detail.male4 + detail.male5 + detail.male6; ws.Cell(row, 28).Value = detail.female1 + detail.female2 + detail.female3 + detail.female4 + detail.female5 + detail.female6; ws.Cell(row, 29).Value = sums1 + sums2 + sums3 + sums4 + sums5 + sums6; ws.Cell(row, 30).Value = sumsroom; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontName = "TH SarabunPSK"; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontSize = 14; ws.Range(ws.Cell(row, 1), ws.Cell(row, 2)).Merge().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; ws.Range(ws.Cell(row, 1), ws.Cell(row, 2)).Merge().Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; // ws.Cell(row, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; // ws.Cell(row, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; // ws.Cell(row, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; // ws.Cell(row, 2).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.NumberFormat.SetFormat("#,#"); row++; } } if (header.filter == "0") { ws.Cell(row, 1).Value = null; ws.Cell(row, 2).Value = null; ws.Cell(row, 3).Value = null; ws.Cell(row, 4).Value = null; ws.Cell(row, 5).Value = null; ws.Cell(row, 6).Value = null; ws.Cell(row, 7).Value = null; ws.Cell(row, 8).Value = null; ws.Cell(row, 9).Value = null; ws.Cell(row, 10).Value = null; ws.Cell(row, 11).Value = null; ws.Cell(row, 12).Value = null; ws.Cell(row, 13).Value = null; ws.Cell(row, 14).Value = null; ws.Cell(row, 15).Value = null; ws.Cell(row, 16).Value = null; ws.Cell(row, 17).Value = null; ws.Cell(row, 18).Value = null; ws.Cell(row, 19).Value = null; ws.Cell(row, 20).Value = null; ws.Cell(row, 21).Value = null; ws.Cell(row, 22).Value = null; ws.Cell(row, 23).Value = null; ws.Cell(row, 24).Value = null; ws.Cell(row, 25).Value = null; ws.Cell(row, 26).Value = null; ws.Cell(row, 27).Value = null; ws.Cell(row, 28).Value = null; ws.Cell(row, 29).Value = null; ws.Cell(row, 30).Value = null; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontName = "TH SarabunPSK"; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Font.FontSize = 14; ws.Range(ws.Cell(row, 1), ws.Cell(row, 2)).Merge().Style.Fill.BackgroundColor = XLColor.White; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Fill.BackgroundColor = XLColor.White; ws.Cell(row, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; ws.Cell(row, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; ws.Range(ws.Cell(row, 3), ws.Cell(row, 30)).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 1), ws.Cell(row, 30)).Style.Border.RightBorder = XLBorderStyleValues.Thin; row++; } } } if (type == "xlsx") { using (var stream = new MemoryStream()) { workbook.SaveAs(stream); var content = stream.ToArray(); string date = DateTime.Now.ToString("yyyyMMddHHmmss"); return File( content, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "sumstd" + date + ".xlsx"); } } return Ok(); } } }