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("สำหรับรายงาน ง.7")]
    public class studymaterial : Controller
    {
        readonly Setting _setting;

        public studymaterial(Setting setting)
        {
            this._setting = setting;
        }

        [HttpPost, Route("reports/study_material/{type}")]
        [ApiExplorerSettings(GroupName = "reports")]
        public IActionResult GetMaterialReport([FromRoute] string type,
            [FromBody] study_material _study_material)
        {
            {
                var workbook = new XLWorkbook();
                var ws = workbook.Worksheets.Add("ง.7");
                ws.Range("A1:J1").Merge().Value = "แบบฟอร์มแผนการเรียน คณะ" + _study_material.faculty + "ปีงบประมาณ " +
                                                  _study_material.budget_year;
                ws.Range("A1:J1").Style.Alignment.WrapText = true;
                ws.Range("A1:J1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                ws.Range("A1:J1").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                ws.Cell("A1").Style.Font.FontName = "TH SarabunPSK";
                ws.Cell("A1").Style.Font.FontSize = 16;
                ws.Range("A1:J1").Style.Font.Bold = true;
                ws.Range("A2:J2").Merge().Value =
                    "ผลผลิต " + _study_material.product +" " + _study_material.faculty;
                ws.Range("A2:J2").Style.Alignment.WrapText = true;
                ws.Range("A2:J2").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                ws.Range("A2:J2").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                ws.Cell("A2").Style.Font.FontName = "TH SarabunPSK";
                ws.Cell("A2").Style.Font.FontSize = 16;
                ws.Range("A2:J2").Style.Font.Bold = true;
                ws.Range("A3:A5").Merge().Value = "ภาคการศึกษา";
                ws.Range("A3:A5").Style.Font.FontName = "TH SarabunPSK";
                ws.Range("A3:A5").Style.Font.FontSize = 16;
                ws.Range("A3:A5").Style.Font.Bold = true;
                ws.Range("A3:A5").Style.Alignment.WrapText = true;
                ws.Range("B3:B5").Merge().Value = "นักศึกษา";
                ws.Range("B3:B5").Style.Font.FontName = "TH SarabunPSK";
                ws.Range("B3:B5").Style.Font.FontSize = 16;
                ws.Range("B3:B5").Style.Font.Bold = true;
                ws.Range("B3:B5").Style.Alignment.WrapText = true;
                ws.Range("C3:F4").Merge().Value = "พื้นที่เขตการศึกษา";
                ws.Range("C3:F4").Style.Font.FontName = "TH SarabunPSK";
                ws.Range("C3:F4").Style.Font.FontSize = 16;
                ws.Range("C3:F4").Style.Font.Bold = true;
                ws.Range("C3:F4").Style.Alignment.WrapText = true;
                ws.Range("C3:F4").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                ws.Range("C3:F4").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                ws.Cell("C5").Value = "เพาะช่าง";
                ws.Cell("C5").Style.Font.FontName = "TH SarabunPSK";
                ws.Cell("C5").Style.Font.FontSize = 16;
                ws.Cell("C5").Style.Font.Bold = true;
                ws.Cell("C5").Style.Alignment.WrapText = true;
                ws.Cell("D5").Value = "บพิตรพิมุข จักรวรรดิ";
                ws.Cell("D5").Style.Font.FontName = "TH SarabunPSK";
                ws.Cell("D5").Style.Font.FontSize = 16;
                ws.Cell("D5").Style.Font.Bold = true;
                ws.Cell("D5").Style.Alignment.WrapText = true;
                ws.Cell("E5").Value = "ศาลายา";
                ws.Cell("E5").Style.Font.FontName = "TH SarabunPSK";
                ws.Cell("E5").Style.Font.FontSize = 16;
                ws.Cell("E5").Style.Font.Bold = true;
                ws.Cell("E5").Style.Alignment.WrapText = true;
                ws.Cell("F5").Value = "วังไกลกังวล";
                ws.Cell("F5").Style.Font.FontName = "TH SarabunPSK";
                ws.Cell("F5").Style.Font.FontSize = 16;
                ws.Cell("F5").Style.Font.Bold = true;
                ws.Cell("F5").Style.Alignment.WrapText = true;
                ws.Range("G3:G4").Merge().Value = "รวม สายวิทย์";
                ws.Range("G3:G4").Style.Font.FontName = "TH SarabunPSK";
                ws.Range("G3:G4").Style.Font.FontSize = 16;
                ws.Range("G3:G4").Style.Font.Bold = true;
                ws.Cell("G3").Style.Alignment.WrapText = true;
                ws.Cell("G5").Value = "(1)";
                ws.Cell("G5").Style.Font.FontName = "TH SarabunPSK";
                ws.Cell("G5").Style.Font.FontSize = 16;
                ws.Cell("G5").Style.Font.Bold = true;
                ws.Cell("G5").Style.Alignment.WrapText = true;
                ws.Range("H3:H4").Merge().Value = "รวม สายสังคม";
                ws.Range("H3:H4").Style.Font.FontName = "TH SarabunPSK";
                ws.Range("H3:H4").Style.Font.FontSize = 16;
                ws.Range("H3:H4").Style.Font.Bold = true;
                ws.Cell("H3").Style.Alignment.WrapText = true;
                ws.Cell("H5").Value = "(2)";
                ws.Cell("H5").Style.Font.FontName = "TH SarabunPSK";
                ws.Cell("H5").Style.Font.FontSize = 16;
                ws.Cell("H5").Style.Font.Bold = true;
                ws.Cell("H5").Style.Alignment.WrapText = true;
                ws.Range("I3:I4").Merge().Value = "อัตราค่าสอน สายวิทย์";
                ws.Range("I3:I4").Style.Font.FontName = "TH SarabunPSK";
                ws.Range("I3:I4").Style.Font.FontSize = 16;
                ws.Range("I3:I4").Style.Font.Bold = true;
                ws.Cell("I3").Style.Alignment.WrapText = true;
                ws.Cell("I5").Value = "(1) x 175 = (3)";
                ws.Cell("I5").Style.Font.FontName = "TH SarabunPSK";
                ws.Cell("I5").Style.Font.FontSize = 16;
                ws.Cell("I5").Style.Font.Bold = true;
                ws.Cell("I5").Style.Alignment.WrapText = true;
                ws.Range("J3:J4").Merge().Value = "อัตราค่าสอน สายสังคม";
                ws.Range("J3:J4").Style.Font.FontName = "TH SarabunPSK";
                ws.Range("J3:J4").Style.Font.FontSize = 16;
                ws.Range("J3:J4").Style.Font.Bold = true;
                ws.Cell("J3").Style.Alignment.WrapText = true;
                ws.Cell("J5").Value = "(2) x 125 = (4)";
                ws.Cell("J5").Style.Font.FontName = "TH SarabunPSK";
                ws.Cell("J5").Style.Font.FontSize = 16;
                ws.Cell("J5").Style.Font.Bold = true;
                ws.Cell("J5").Style.Alignment.WrapText = true;
                ws.Range("A3:J5").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                ws.Range("A3:J5").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                ws.Range("A3:J5").Style.Fill.BackgroundColor = XLColor.Gainsboro;
                ws.Range("C3:F4").Style.Border.BottomBorder =
                    XLBorderStyleValues.Thin;
                ws.Range("A3:J5").Style.Border.OutsideBorder =
                    XLBorderStyleValues.Thin;
                ws.Range("A3:J5").Style.Border.LeftBorder =
                    XLBorderStyleValues.Thin;
                ws.Range("A3:J5").Style.Border.RightBorder =
                    XLBorderStyleValues.Thin;
                ws.Column(1).Width = 20;
                ws.Column(2).Width = 40;
                ws.Column(3).Width = 15;
                ws.Column(4).Width = 15;
                ws.Column(5).Width = 15;
                ws.Column(6).Width = 15;
                ws.Column(7).Width = 15;
                ws.Column(8).Width = 15;
                ws.Column(9).Width = 15;
                ws.Column(10).Width = 15;
                var row1 = ws.Row(3);
                row1.Height = 20;
                int row = 6;
             

                if (_study_material != null)
                {
                    foreach (var data in _study_material.data1)
                    {
                        
                        //     ws.Cell(row, 1).Value = "ภาคการศึกษา " + data.academic_semester + "/" + data.academic_year;
                        //     ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Font.FontName =
                        //         "TH SarabunPSK";
                        //     ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Font.FontSize = 16;
                        //     ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Alignment.WrapText = true;
                        //     ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Alignment.Vertical =
                        //         XLAlignmentVerticalValues.Center;
                        //     ws.Cell(row, 1).Style.Font.Bold = true;
                        //     ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Border.OutsideBorder =
                        //         XLBorderStyleValues.Thin;
                        //     ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Border.RightBorder =
                        //         XLBorderStyleValues.Thin;
                        //     ws.Cell(row, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
                        //     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.Range(ws.Cell(row, 3), ws.Cell(row, 10)).SetDataType(XLDataType.Number);
                        //     ws.Range(ws.Cell(row, 3), ws.Cell(row, 10)).Style.NumberFormat.SetFormat("#,#");
                        //     row++;
                        ws.Cell(row, 1).Value = "ภาคการศึกษา " + data.academic_semester + "/" + data.academic_year;
                        foreach (var data2 in data.data2)
                        {
                            // ws.Cell(row, 1).Value = "ภาคการศึกษา " + data.academic_semester + "/" + data.academic_year;
                            ws.Cell(row, 2).Value =
                                "ชั้นปีที่ " + data2.year_class + " (รหัส " + data.academic_year + ")";
                            ws.Cell(row, 9).Value = data2.total_cost_science;
                            ws.Cell(row, 10).Value = data2.total_cost_social;
                            ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Font.FontName =
                                "TH SarabunPSK";
                            ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Font.FontSize = 16;
                            ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Alignment.WrapText = true;
                            ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Alignment.Vertical =
                                XLAlignmentVerticalValues.Center;
                            ws.Cell(row, 1).Style.Font.Bold = true;
                            ws.Cell(row, 2).Style.Font.Bold = true;
                            ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Border.OutsideBorder =
                                XLBorderStyleValues.Thin;
                            ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Border.RightBorder =
                                XLBorderStyleValues.Thin;
                            ws.Cell(row, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                            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.Range(ws.Cell(row, 3), ws.Cell(row, 10)).SetDataType(XLDataType.Number);
                            ws.Range(ws.Cell(row, 3), ws.Cell(row, 10)).Style.NumberFormat.SetFormat("#,#");
                            row++;
                            foreach (var data3 in data2.data3)
                            {
                                
                                if (data3.academic_semester == "รวม" ||
                                    data3.academic_semester == "รวม 1 ปีภาคการศึกษา")
                                {
                                    ws.Cell(row, 1).Value = data3.academic_semester;
                                    ws.Cell(row, 2).Value = null ;
                                    ws.Cell(row, 3).Value = data3.pohchang;
                                    ws.Cell(row, 4).Value = data3.bophitphimuk;
                                    ws.Cell(row, 5).Value = data3.salaya;
                                    ws.Cell(row, 6).Value = data3.klai_kangwon;
                                    ws.Cell(row, 7).Value = data3.sum_science;
                                    ws.Cell(row, 8).Value = data3.sum_social;
                                    ws.Cell(row, 9).Value = data3.cost_science;
                                    ws.Cell(row, 10).Value = data3.cost_social;

                                    ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Font.FontName =
                                        "TH SarabunPSK";
                                    ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Font.FontSize = 16;
                                    ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Alignment.WrapText = true;
                                    ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Alignment.Vertical =
                                        XLAlignmentVerticalValues.Center;
                                    ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Font.Bold = true;
                                    ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Border.OutsideBorder =
                                        XLBorderStyleValues.Thin;
                                    ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Border.RightBorder =
                                        XLBorderStyleValues.Thin;
                                    ws.Cell(row, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                                    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.Range(ws.Cell(row, 3), ws.Cell(row, 10)).SetDataType(XLDataType.Number);
                                    ws.Range(ws.Cell(row, 3), ws.Cell(row, 10)).Style.NumberFormat.SetFormat("#,#");
                                    //row++;
                                }

                                if (data3.academic_semester != "รวม" &&
                                    data3.academic_semester != "รวม 1 ปีภาคการศึกษา")
                                {
                                    string major = "     -  สาขา ";
                                    ws.Cell(row, 1).Value = data3.academic_semester;
                                    ws.Cell(row, 2).Value =  data3.student;
                                    ws.Cell(row, 3).Value = data3.pohchang;
                                    ws.Cell(row, 4).Value = data3.bophitphimuk;
                                    ws.Cell(row, 5).Value = data3.salaya;
                                    ws.Cell(row, 6).Value = data3.klai_kangwon;
                                    ws.Cell(row, 7).Value = data3.sum_science;
                                    ws.Cell(row, 8).Value = data3.sum_social;
                                    ws.Cell(row, 9).Value = data3.cost_science;
                                    ws.Cell(row, 10).Value = data3.cost_social;

                                    ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Font.FontName =
                                        "TH SarabunPSK";
                                    ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Font.FontSize = 16;
                                    ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Alignment.WrapText = true;
                                    ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Alignment.Vertical =
                                        XLAlignmentVerticalValues.Center;
                                    ws.Cell(row, 1).Style.Font.Bold = true;
                                    ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Border.OutsideBorder =
                                        XLBorderStyleValues.Thin;
                                    ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Border.RightBorder =
                                        XLBorderStyleValues.Thin;
                                    ws.Cell(row, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                                    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.Range(ws.Cell(row, 3), ws.Cell(row, 10)).SetDataType(XLDataType.Number);
                                    ws.Range(ws.Cell(row, 3), ws.Cell(row, 10)).Style.NumberFormat.SetFormat("#,#");
                                    //row++;
                                }
                                row++;
                                
                            }
                        }

                       
                        // if (data.academic_semester != "รวม")
                        // {
                        //     ws.Cell(row, 1).Value = data.academic_semester;
                        //     ws.Cell(row, 2).Value = data.student;
                        //     ws.Cell(row, 3).Value = data.pohchang;
                        //     ws.Cell(row, 4).Value = data.bophitphimuk;
                        //     ws.Cell(row, 5).Value = data.salaya;
                        //     ws.Cell(row, 6).Value = data.klai_kangwon;
                        //     ws.Cell(row, 7).Value = data.sum_science;
                        //     ws.Cell(row, 8).Value = data.sum_social;
                        //     ws.Cell(row, 9).Value = data.cost_science;
                        //     ws.Cell(row, 10).Value = data.cost_social;
                        //
                        //     ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Font.FontName =
                        //         "TH SarabunPSK";
                        //     ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Font.FontSize = 16;
                        //     ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Alignment.WrapText = true;
                        //     ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Alignment.Vertical =
                        //         XLAlignmentVerticalValues.Center;
                        //     ws.Cell(row, 1).Style.Font.Bold = true;
                        //     ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Border.OutsideBorder =
                        //         XLBorderStyleValues.Thin;
                        //     ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Border.RightBorder =
                        //         XLBorderStyleValues.Thin;
                        //     ws.Cell(row, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
                        //     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.Range(ws.Cell(row, 3), ws.Cell(row, 10)).SetDataType(XLDataType.Number);
                        //     ws.Range(ws.Cell(row, 3), ws.Cell(row, 10)).Style.NumberFormat.SetFormat("#,#");
                        //     row++;
                        //
                        // }
                        // if (data.academic_semester == "รวม" || data.academic_semester == "รวม 1 ปีภาคการศึกษา")
                        //    {
                        //  
                        //     ws.Cell(row, 1).Value = data.academic_semester;
                        //     ws.Cell(row, 2).Value = data.student;
                        //     ws.Cell(row, 3).Value = data.pohchang;
                        //     ws.Cell(row, 4).Value = data.bophitphimuk;
                        //     ws.Cell(row, 5).Value = data.salaya;
                        //     ws.Cell(row, 6).Value = data.klai_kangwon;
                        //     ws.Cell(row, 7).Value = data.sum_science;
                        //     ws.Cell(row, 8).Value = data.sum_social;
                        //     ws.Cell(row, 9).Value = data.cost_science;
                        //     ws.Cell(row, 10).Value = data.cost_social;
                        //     ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Font.Bold = true;
                        //     ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Font.FontName =
                        //         "TH SarabunPSK";
                        //     ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Font.FontSize = 16;
                        //     ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Alignment.WrapText = true;
                        //     ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Alignment.Vertical =
                        //         XLAlignmentVerticalValues.Center;
                        //     ws.Cell(row, 1).Style.Font.Bold = true;
                        //     ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Border.OutsideBorder =
                        //         XLBorderStyleValues.Thin;
                        //     ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Border.RightBorder =
                        //         XLBorderStyleValues.Thin;
                        //     ws.Cell(row, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                        //     ws.Cell(row, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                        //     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.Range(ws.Cell(row, 3), ws.Cell(row, 10)).SetDataType(XLDataType.Number);
                        //     ws.Range(ws.Cell(row, 3), ws.Cell(row, 10)).Style.NumberFormat.SetFormat("#,#");
                        //     row++;
                        //    }
                    }
                    if (_study_material.total_cost_science !=null &&_study_material.total_cost_social !=null)
                    {
                        ws.Range(ws.Cell(row, 1), ws.Cell(row, 8)).Merge().Value = "รวมทั้งหมด";
                        ws.Cell(row, 9).Value = _study_material.total_cost_science;
                        ws.Cell(row, 10).Value = _study_material.total_cost_social;
                        ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Font.FontName =
                            "TH SarabunPSK";
                        ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Font.FontSize = 16;
                        ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Alignment.WrapText = true;
                        ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Alignment.Vertical =
                            XLAlignmentVerticalValues.Center;
                        ws.Range(ws.Cell(row, 1), 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.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Font.Bold = true;
                        ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Border.OutsideBorder =
                            XLBorderStyleValues.Thin;
                        ws.Range(ws.Cell(row, 1), ws.Cell(row, 10)).Style.Border.RightBorder =
                            XLBorderStyleValues.Thin;
                        ws.Range(ws.Cell(row, 9), ws.Cell(row, 10)).SetDataType(XLDataType.Number);
                        ws.Range(ws.Cell(row, 9), ws.Cell(row, 10)).Style.NumberFormat.SetFormat("#,#");
                    }
                }

                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",
                        "study_material_" + date + ".xlsx");
                }
            }
        }
    }
}