using System; using System.Collections.Generic; using System.IO; using System.Linq; using ClosedXML.Excel; using Microsoft.AspNetCore.Mvc; using rmutr_report.Models; using Swashbuckle.AspNetCore.Annotations; namespace rmutr_report.Controllers { [SwaggerTag("สำหรับรายงานค่าสอนพิเศษ")] public class TeachingFeeExtra : Controller { readonly Setting _setting; public TeachingFeeExtra(Setting setting) { this._setting = setting; } [HttpPost, Route("reports/teaching_fee_extra/{type}")] [ApiExplorerSettings(GroupName = "reports")] public IActionResult GetTeachingFeeExtraReport([FromRoute] string type, [FromBody] teaching_fee_extra _teaching_fee_extra) { //var t = _teaching_fee_extra.ToArray(); { var workbook = new XLWorkbook(); var ws = workbook.Worksheets.Add("ค่าสอนพิเศษ"); ws.Range("B1:K1").Merge().Value = "แบบฟอร์มการคำนวนค่าสอนอาจารย์พิเศษ"; ws.Range("B1:K1").Style.Alignment.WrapText = true; ws.Range("B1:K1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Range("B1:K1").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("B1").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("B1").Style.Font.FontSize = 16; ws.Range("B1:K1").Style.Font.Bold = true; ws.Column(1).Width = 10; ws.Column(2).Width = 40; 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 = 25; ws.Column(11).Width = 30; int row = 8; int col = 11; int x = 1; int y = 2; int z = 3; //ws.Cell(row, 3).SetDataType(XLDataType.Number); if (_teaching_fee_extra != null) { // foreach (var tt in _teaching_fee_extra) // { ws.Cell("B2").Value = "หน่วยงาน " + _teaching_fee_extra.agency_name_th; ws.Cell("B2").Style.Alignment.WrapText = true; ws.Cell("B2").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("B2").Style.Font.FontSize = 16; ws.Cell("B2").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; ws.Cell("B2").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("C2").Value = "พื้นที่ " + _teaching_fee_extra.area; ws.Cell("C2").Style.Alignment.WrapText = true; ws.Cell("C2").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("C2").Style.Font.FontSize = 16; ws.Cell("C2").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; ws.Cell("C2").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("B3").Value = "ผลผลิต " + _teaching_fee_extra.product; ws.Cell("B3").Style.Alignment.WrapText = true; ws.Cell("B3").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("B3").Style.Font.FontSize = 16; ws.Cell("B3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; ws.Cell("B3").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range("B4:B7").Merge().Value = "รายวิชาที่สอน"; ws.Range("B4:B7").Merge().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Range("B4:B7").Merge().Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("B4").Style.Font.Bold = true; ws.Cell("B4").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("B4").Style.Font.FontSize = 16; ws.Cell("B4").Style.Fill.BackgroundColor = XLColor.Bisque; ws.Range("B4:B7").Merge().Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range("C4:C7").Merge().Value = "อัตราค่าสอน"; ws.Range("C4:C7").Merge().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Range("C4:C7").Merge().Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("C4").Style.Font.Bold = true; ws.Cell("C4").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("C4").Style.Font.FontSize = 16; ws.Cell("C4").Style.Fill.BackgroundColor = XLColor.Bisque; ws.Range("C4:C7").Merge().Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range("D4:I4").Merge().Value = "รวมค่าสอนพิเศษ/ค่าการศึกษา"; ws.Range("D4:I4").Merge().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Range("D4:I4").Merge().Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("D4").Style.Font.Bold = true; ws.Cell("D4").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("D4").Style.Font.FontSize = 16; ws.Cell("D4").Style.Fill.BackgroundColor = XLColor.Bisque; ws.Range("D4:I4").Merge().Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range("D5:I5").Merge().Value = "(รวมจำนวนชั่วโมง x จำนวนสัปดาห์ x อัตราค่าสอน)"; ws.Range("D5:I5").Merge().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Range("D5:I5").Merge().Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("D5").Style.Font.Bold = true; ws.Cell("D5").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("D5").Style.Font.FontSize = 16; ws.Cell("D5").Style.Fill.BackgroundColor = XLColor.Bisque; ws.Range("D5:I5").Merge().Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range("D6:F6").Merge().Value = "'2/" + _teaching_fee_extra.term_2_year; ws.Range("D6:F6").Merge().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Range("D6:F6").Merge().Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("D6").Style.Font.Bold = true; ws.Cell("D6").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("D6").Style.Font.FontSize = 16; ws.Cell("D6").Style.Fill.BackgroundColor = XLColor.Bisque; ws.Cell("D6").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range("G6:I6").Merge().Value = "'1/" + _teaching_fee_extra.term_1_year; ws.Range("G6:I6").Merge().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Range("G6:I6").Merge().Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("G6").Style.Font.Bold = true; ws.Cell("G6").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("G6").Style.Font.FontSize = 16; ws.Cell("G6").Style.Fill.BackgroundColor = XLColor.Bisque; ws.Range("G6:I6").Merge().Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Cell("D7").Value = "จำนวนชั่วโมง"; ws.Cell("D7").Style.Font.Bold = true; ws.Cell("D7").Style.Alignment.WrapText = true; ws.Cell("D7").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("D7").Style.Font.FontSize = 16; ws.Cell("D7").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("D7").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("D7").Style.Fill.BackgroundColor = XLColor.Bisque; ws.Cell("D7").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Cell("E7").Value = "จำนวนสัปดาห์"; ws.Cell("E7").Style.Font.Bold = true; ws.Cell("E7").Style.Alignment.WrapText = true; ws.Cell("E7").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("E7").Style.Font.FontSize = 16; ws.Cell("E7").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("E7").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("E7").Style.Fill.BackgroundColor = XLColor.Bisque; ws.Cell("E7").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Cell("F7").Value = "รวมเงิน"; ws.Cell("F7").Style.Font.Bold = true; ws.Cell("F7").Style.Alignment.WrapText = true; ws.Cell("F7").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("F7").Style.Font.FontSize = 16; ws.Cell("F7").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("F7").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("F7").Style.Fill.BackgroundColor = XLColor.Bisque; ws.Cell("F7").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Cell("G7").Value = "จำนวนชั่วโมง"; ws.Cell("G7").Style.Font.Bold = true; ws.Cell("G7").Style.Alignment.WrapText = true; ws.Cell("G7").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("G7").Style.Font.FontSize = 16; ws.Cell("G7").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("G7").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("G7").Style.Fill.BackgroundColor = XLColor.Bisque; ws.Cell("G7").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Cell("H7").Value = "จำนวนสัปดาห์"; ws.Cell("H7").Style.Font.Bold = true; ws.Cell("H7").Style.Alignment.WrapText = true; ws.Cell("H7").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("H7").Style.Font.FontSize = 16; ws.Cell("H7").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("H7").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("H7").Style.Fill.BackgroundColor = XLColor.Bisque; ws.Cell("H7").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Cell("I7").Value = "รวมเงิน"; ws.Cell("I7").Style.Font.Bold = true; ws.Cell("I7").Style.Alignment.WrapText = true; ws.Cell("I7").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("I7").Style.Font.FontSize = 16; ws.Cell("I7").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("I7").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("I7").Style.Fill.BackgroundColor = XLColor.Bisque; ws.Cell("I7").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range("J4:J7").Merge().Value = "รวมเงินค่าสอนพิเศษทั้งสิ้น"; ws.Range("J4:J7").Merge().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Range("J4:J7").Merge().Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("J4").Style.Font.Bold = true; ws.Cell("J4").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("J4").Style.Font.FontSize = 16; ws.Cell("J4").Style.Fill.BackgroundColor = XLColor.Bisque; ws.Range("J4:J7").Merge().Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range("K4:K7").Merge().Value = "สาขาวิชา"; ws.Range("K4:K7").Merge().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Range("K4:K7").Merge().Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("K4").Style.Font.Bold = true; ws.Cell("K4").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("K4").Style.Font.FontSize = 16; ws.Cell("K4").Style.Fill.BackgroundColor = XLColor.Bisque; ws.Range("K4:K7").Merge().Style.Border.OutsideBorder = XLBorderStyleValues.Thin; foreach (var teaching in _teaching_fee_extra.data) { var countrow = teaching.datadetail.Count; if (countrow == 1) { ws.Range(ws.Cell(row, col), ws.Cell(row, col)).Merge().Value = teaching.major; } if (countrow == 2) { ws.Range(ws.Cell(row, col), ws.Cell(row + 1, col)).Merge().Value = teaching.major; } if (countrow == 3) { ws.Range(ws.Cell(row, col), ws.Cell(row + 2, col)).Merge().Value = teaching.major; } if (countrow == 4) { ws.Range(ws.Cell(row, col), ws.Cell(row + 3, col)).Merge().Value = teaching.major; } if (countrow == 5) { ws.Range(ws.Cell(row, col), ws.Cell(row + 4, col)).Merge().Value = teaching.major; } if (countrow == 6) { ws.Range(ws.Cell(row, col), ws.Cell(row + 5, col)).Merge().Value = teaching.major; } if (countrow == 7) { ws.Range(ws.Cell(row, col), ws.Cell(row + 6, col)).Merge().Value = teaching.major; } if (countrow == 8) { ws.Range(ws.Cell(row, col), ws.Cell(row + 7, col)).Merge().Value = teaching.major; } if (countrow == 9) { ws.Range(ws.Cell(row, col), ws.Cell(row + 8, col)).Merge().Value = teaching.major; } if (countrow == 10) { ws.Range(ws.Cell(row, col), ws.Cell(row + 9, col)).Merge().Value = teaching.major; } foreach (var datadetail in teaching.datadetail) { if (datadetail.course != "รวมทั้งสิ้น") { ws.Cell(row, 2).Value = datadetail.course; ws.Cell(row, 3).Value = null; ws.Cell(row, 4).Value = datadetail.number_of_hours1; ws.Cell(row, 5).Value = datadetail.number_of_weeks1; ws.Cell(row, 6).Value = datadetail.amount1; ws.Cell(row, 7).Value = datadetail.number_of_hours2; ws.Cell(row, 8).Value = datadetail.number_of_weeks2; ws.Cell(row, 9).Value = datadetail.amount2; ws.Cell(row, 10).Value = datadetail.total_amount; //ws.Range(ws.Cell(row, col), ws.Cell(row+x+1, col)).Merge().Value = teaching.major; ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Font.FontName = "TH SarabunPSK"; ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Font.FontSize = 16; ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Alignment.WrapText = true; ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; //ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Font.Bold = true; if (datadetail.is_color == true) { ws.Cell(row, 2).Style.Fill.BackgroundColor = XLColor.Gainsboro; ws.Cell(row, 3).Style.Fill.BackgroundColor = XLColor.Gainsboro; ws.Cell(row, 4).Style.Fill.BackgroundColor = XLColor.Gainsboro; ws.Cell(row, 5).Style.Fill.BackgroundColor = XLColor.Gainsboro; ws.Cell(row, 6).Style.Fill.BackgroundColor = XLColor.Gainsboro; ws.Cell(row, 7).Style.Fill.BackgroundColor = XLColor.Gainsboro; ws.Cell(row, 8).Style.Fill.BackgroundColor = XLColor.Gainsboro; ws.Cell(row, 9).Style.Fill.BackgroundColor = XLColor.Gainsboro; ws.Cell(row, 10).Style.Fill.BackgroundColor = XLColor.Gainsboro; ws.Cell(row, 11).Style.Fill.BackgroundColor = XLColor.White; ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Font.Bold = true; } if (datadetail.is_color == false) { ws.Cell(row, 2).Style.Fill.BackgroundColor = XLColor.White; ws.Cell(row, 3).Style.Fill.BackgroundColor = XLColor.White; ws.Cell(row, 4).Style.Fill.BackgroundColor = XLColor.White; ws.Cell(row, 5).Style.Fill.BackgroundColor = XLColor.White; ws.Cell(row, 6).Style.Fill.BackgroundColor = XLColor.White; ws.Cell(row, 7).Style.Fill.BackgroundColor = XLColor.White; ws.Cell(row, 8).Style.Fill.BackgroundColor = XLColor.White; ws.Cell(row, 9).Style.Fill.BackgroundColor = XLColor.White; ws.Cell(row, 10).Style.Fill.BackgroundColor = XLColor.White; ws.Cell(row, 11).Style.Fill.BackgroundColor = XLColor.White; ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Font.Bold = false; } ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 2), ws.Cell(row, 10)).Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Cell(row, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; ws.Cell(row, 3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; ws.Cell(row, 4).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell(row, 5).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell(row, 6).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell(row, 7).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell(row, 8).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell(row, 9).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell(row, 10).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell(row, 11).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Range(ws.Cell(row, 4), ws.Cell(row, 10)).Style.NumberFormat.SetFormat("#,#"); row++; } // else if (datadetail.is_color == false && datadetail.course != "รวมทั้งสิ้น") // { // ws.Cell(row, 2).Value = datadetail.course; // ws.Cell(row, 3).Value = null; // ws.Cell(row, 4).Value = datadetail.number_of_hours1; // ws.Cell(row, 5).Value = datadetail.number_of_weeks1; // ws.Cell(row, 6).Value = datadetail.amount1; // ws.Cell(row, 7).Value = datadetail.number_of_hours2; // ws.Cell(row, 8).Value = datadetail.number_of_weeks2; // ws.Cell(row, 9).Value = datadetail.amount2; // ws.Cell(row, 10).Value = datadetail.total_amount; // //ws.Range(ws.Cell(row, col), ws.Cell(row+x+1, col)).Merge().Value = teaching.major; // ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Font.FontName = // "TH SarabunPSK"; // ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Font.FontSize = 16; // ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Alignment.WrapText = true; // ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Alignment.Vertical = // XLAlignmentVerticalValues.Center; // //ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Font.Bold = true; // ws.Cell(row, 2).Style.Fill.BackgroundColor = XLColor.White; // ws.Cell(row, 3).Style.Fill.BackgroundColor = XLColor.White; // ws.Cell(row, 4).Style.Fill.BackgroundColor = XLColor.White; // ws.Cell(row, 5).Style.Fill.BackgroundColor = XLColor.White; // ws.Cell(row, 6).Style.Fill.BackgroundColor = XLColor.White; // ws.Cell(row, 7).Style.Fill.BackgroundColor = XLColor.White; // ws.Cell(row, 8).Style.Fill.BackgroundColor = XLColor.White; // ws.Cell(row, 9).Style.Fill.BackgroundColor = XLColor.White; // ws.Cell(row, 10).Style.Fill.BackgroundColor = XLColor.White; // ws.Cell(row, 11).Style.Fill.BackgroundColor = XLColor.White; // ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Border.OutsideBorder = // XLBorderStyleValues.Thin; // ws.Range(ws.Cell(row, 2), ws.Cell(row, 10)).Style.Border.RightBorder = // XLBorderStyleValues.Thin; // ws.Cell(row, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; // ws.Cell(row, 3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; // ws.Cell(row, 4).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row, 5).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row, 6).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row, 7).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row, 8).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row, 9).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row, 10).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row, 11).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Range(ws.Cell(row, 4), ws.Cell(row, 10)).Style.NumberFormat.SetFormat("#,#"); // row++; // } else if (datadetail.course == "รวมทั้งสิ้น") { //ws.Cell(row2, 2).Value = datadetail.teacher; ws.Range(ws.Cell(row, 2), ws.Cell(row, 3)).Merge().Value = datadetail.course; ws.Range(ws.Cell(row, 2), ws.Cell(row, 3)).Merge(); ws.Cell(row, 4).Value = datadetail.number_of_hours1; ws.Cell(row, 5).Value = datadetail.number_of_weeks1; ws.Cell(row, 6).Value = datadetail.amount1; ws.Cell(row, 7).Value = datadetail.number_of_hours2; ws.Cell(row, 8).Value = datadetail.number_of_weeks2; ws.Cell(row, 9).Value = datadetail.amount2; ws.Cell(row, 10).Value = datadetail.total_amount; ws.Cell(row, 11).Value = null; ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Font.FontName = "TH SarabunPSK"; ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Font.FontSize = 16; ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Alignment.WrapText = true; ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 2), ws.Cell(row, 10)).Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Cell(row, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //ws.Cell(row2, 3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell(row, 4).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell(row, 5).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell(row, 6).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell(row, 7).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell(row, 8).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell(row, 9).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell(row, 10).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell(row, 11).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Fill.BackgroundColor = XLColor.Bisque; ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Font.Bold = true; ws.Range(ws.Cell(row, 4), ws.Cell(row, 10)).Style.NumberFormat.SetFormat("#,#"); row++; } // ws.Cell(row, 2).Value = datadetail.course; // ws.Cell(row, 3).Value = datadetail.teaching_rate; // ws.Cell(row, 4).Value = datadetail.number_of_hours1; // ws.Cell(row, 5).Value = datadetail.number_of_weeks1; // ws.Cell(row, 6).Value = datadetail.amount1; // ws.Cell(row, 7).Value = datadetail.number_of_hours2; // ws.Cell(row, 8).Value = datadetail.number_of_weeks2; // ws.Cell(row, 9).Value = datadetail.amount2; // ws.Cell(row, 10).Value = datadetail.total_amount; // ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Font.FontName = // "TH SarabunPSK"; // ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Font.FontSize = 16; // ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Alignment.WrapText = true; // ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Alignment.Vertical = // XLAlignmentVerticalValues.Center; // ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Border.OutsideBorder = // XLBorderStyleValues.Thin; // ws.Range(ws.Cell(row, 2), ws.Cell(row, 10)).Style.Border.RightBorder = // XLBorderStyleValues.Thin; // ws.Cell(row, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; // ws.Cell(row, 3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row, 4).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row, 5).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row, 6).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row, 7).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row, 8).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row, 9).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row, 10).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row, 11).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Range(ws.Cell(row, 4), ws.Cell(row, 10)).Style.NumberFormat.SetFormat("#,#"); // row++; } } // foreach (var teaching in _teaching_fee_extra.data) // { // ws.Cell(row, 2).Value = teaching.course; // ws.Cell(row, 3).Value = null; // ws.Cell(row, 4).Value = teaching.number_of_hours1; // ws.Cell(row, 5).Value = teaching.number_of_weeks1; // ws.Cell(row, 6).Value = teaching.amount1; // ws.Cell(row, 7).Value = teaching.number_of_hours2; // ws.Cell(row, 8).Value = teaching.number_of_weeks2; // ws.Cell(row, 9).Value = teaching.amount2; // ws.Cell(row, 10).Value = teaching.total_amount; // //ws.Cell(row, 11).Value = teaching.major; // ws.Range(ws.Cell(row, 11), ws.Cell(row2, 11)).Merge().Value = teaching.major; // ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Font.FontName = // "TH SarabunPSK"; // ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Font.FontSize = 16; // ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Alignment.WrapText = true; // ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Alignment.Vertical = // XLAlignmentVerticalValues.Center; // ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Font.Bold = true; // ws.Cell(row, 2).Style.Fill.BackgroundColor = XLColor.Gainsboro; // ws.Cell(row, 3).Style.Fill.BackgroundColor = XLColor.Gainsboro; // ws.Cell(row, 4).Style.Fill.BackgroundColor = XLColor.Gainsboro; // ws.Cell(row, 5).Style.Fill.BackgroundColor = XLColor.Gainsboro; // ws.Cell(row, 6).Style.Fill.BackgroundColor = XLColor.Gainsboro; // ws.Cell(row, 7).Style.Fill.BackgroundColor = XLColor.Gainsboro; // ws.Cell(row, 8).Style.Fill.BackgroundColor = XLColor.Gainsboro; // ws.Cell(row, 9).Style.Fill.BackgroundColor = XLColor.Gainsboro; // ws.Cell(row, 10).Style.Fill.BackgroundColor = XLColor.Gainsboro; // ws.Cell(row, 11).Style.Fill.BackgroundColor = XLColor.White; // ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Border.OutsideBorder = // XLBorderStyleValues.Thin; // ws.Range(ws.Cell(row, 2), ws.Cell(row, 11)).Style.Border.RightBorder = // XLBorderStyleValues.Thin; // ws.Cell(row, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; // ws.Cell(row, 3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; // ws.Cell(row, 4).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row, 5).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row, 6).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row, 7).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row, 8).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row, 9).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row, 10).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row, 11).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Range(ws.Cell(row, 4), ws.Cell(row, 10)).Style.NumberFormat.SetFormat("#,#"); // //row++; // foreach (var datadetail in teaching.datadetail) // { // if (datadetail.teacher != "รวมทั้งสิ้น") // { // ws.Cell(row2, 2).Value = datadetail.teacher; // ws.Cell(row2, 3).Value = datadetail.teaching_rate; // ws.Cell(row2, 4).Value = datadetail.number_of_hours1; // ws.Cell(row2, 5).Value = datadetail.number_of_weeks1; // ws.Cell(row2, 6).Value = datadetail.amount1; // ws.Cell(row2, 7).Value = datadetail.number_of_hours2; // ws.Cell(row2, 8).Value = datadetail.number_of_weeks2; // ws.Cell(row2, 9).Value = datadetail.amount2; // ws.Cell(row2, 10).Value = datadetail.total_amount; // ws.Cell(row2, 11).Value = null; // ws.Range(ws.Cell(row2, 2), ws.Cell(row2, 11)).Style.Font.FontName = // "TH SarabunPSK"; // ws.Range(ws.Cell(row2, 2), ws.Cell(row2, 11)).Style.Font.FontSize = 16; // ws.Range(ws.Cell(row2, 2), ws.Cell(row2, 11)).Style.Alignment.WrapText = true; // ws.Range(ws.Cell(row2, 2), ws.Cell(row2, 11)).Style.Alignment.Vertical = // XLAlignmentVerticalValues.Center; // ws.Range(ws.Cell(row2, 2), ws.Cell(row2, 11)).Style.Border.OutsideBorder = // XLBorderStyleValues.Thin; // ws.Range(ws.Cell(row2, 2), ws.Cell(row2, 11)).Style.Border.RightBorder = // XLBorderStyleValues.Thin; // ws.Cell(row2, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; // ws.Cell(row2, 3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row2, 4).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row2, 5).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row2, 6).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row2, 7).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row2, 8).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row2, 9).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row2, 10).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row2, 11).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Range(ws.Cell(row2, 4), ws.Cell(row2, 10)).Style.NumberFormat.SetFormat("#,#"); // //row2++; // } // //row++; // if (datadetail.teacher == "รวมทั้งสิ้น") // { // //ws.Cell(row2, 2).Value = datadetail.teacher; // ws.Range(ws.Cell(row2, 2), ws.Cell(row2, 3)).Merge().Value = datadetail.teacher; // ws.Range(ws.Cell(row2, 2), ws.Cell(row2, 3)).Merge(); // //ws.Cell(row2, 3).Value = datadetail.teaching_rate; // ws.Cell(row2, 4).Value = datadetail.number_of_hours1; // ws.Cell(row2, 5).Value = datadetail.number_of_weeks1; // ws.Cell(row2, 6).Value = datadetail.amount1; // ws.Cell(row2, 7).Value = datadetail.number_of_hours2; // ws.Cell(row2, 8).Value = datadetail.number_of_weeks2; // ws.Cell(row2, 9).Value = datadetail.amount2; // ws.Cell(row2, 10).Value = datadetail.total_amount; // ws.Cell(row2, 11).Value = null; // ws.Range(ws.Cell(row2, 2), ws.Cell(row2, 11)).Style.Font.FontName = // "TH SarabunPSK"; // ws.Range(ws.Cell(row2, 2), ws.Cell(row2, 11)).Style.Font.FontSize = 16; // ws.Range(ws.Cell(row2, 2), ws.Cell(row2, 11)).Style.Alignment.WrapText = true; // ws.Range(ws.Cell(row2, 2), ws.Cell(row2, 11)).Style.Alignment.Vertical = // XLAlignmentVerticalValues.Center; // ws.Range(ws.Cell(row2, 2), ws.Cell(row2, 11)).Style.Border.OutsideBorder = // XLBorderStyleValues.Thin; // ws.Range(ws.Cell(row2, 2), ws.Cell(row2, 11)).Style.Border.RightBorder = // XLBorderStyleValues.Thin; // ws.Cell(row2, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // //ws.Cell(row2, 3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row2, 4).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row2, 5).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row2, 6).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row2, 7).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row2, 8).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row2, 9).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row2, 10).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Cell(row2, 11).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // ws.Range(ws.Cell(row2, 2), ws.Cell(row2, 11)).Style.Fill.BackgroundColor = XLColor.Bisque; // ws.Range(ws.Cell(row2, 2), ws.Cell(row2, 11)).Style.Font.Bold = true; // ws.Range(ws.Cell(row2, 4), ws.Cell(row2, 10)).Style.NumberFormat.SetFormat("#,#"); // //row2++; // } // //row++; // row2++; // } // row++; // //row2++; // } } 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", "teaching_fee_extra_" + date + ".xlsx"); } } } } }