using Furion.DatabaseAccessor.Extensions;
using Furion.JsonSerialization;
using Microsoft.Extensions.Options;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using YBEE.EQM.Core;

namespace YBEE.EQM.Application;

/// <summary>
/// 校考成绩上报管理服务
/// </summary>
public class ExamOrgScoreReportService : IExamOrgScoreReportService, ITransient
{
    private readonly IRepository<ExamOrgScoreReport> _rep;
    private readonly EqmSiteOptions _eqmSiteOptions;
    private readonly IExportExcelService _exportExcelService;

    public ExamOrgScoreReportService(IRepository<ExamOrgScoreReport> rep, IOptions<EqmSiteOptions> options, IExportExcelService exportExcelService)
    {
        _rep = rep;
        _eqmSiteOptions = options.Value;
        _exportExcelService = exportExcelService;
    }

    /// <summary>
    /// 上传校考成绩
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task<UploadExamOrgScoreReportOutput> Upload(UploadExamOrgScoreReportInput input)
    {
        UploadExamOrgScoreReportOutput ret = new() { IsSuccess = true };

        string fileExt = Path.GetExtension(input.File.FileName).ToLower();
        if (fileExt != ".xls" && fileExt != ".xlsx")
        {
            throw Oops.Oh(ErrorCode.E1010);
        }

        string fileRoot = Path.Combine(_eqmSiteOptions.ResourceFileRoot, DataReportType.SCHOOL_EXAM_SCORE.ToString(), input.ExamPlanId.ToString());
        if (!Directory.Exists(fileRoot))
        {
            Directory.CreateDirectory(fileRoot);
        }
        string fileName = $"{Guid.NewGuid()}{fileExt}";
        string filePath = Path.Combine(fileRoot, fileName);
        string saveFilePath = Path.Combine(DataReportType.SCHOOL_EXAM_SCORE.ToString(), input.ExamPlanId.ToString(), fileName);
        using FileStream fs = File.Create(filePath);
        await input.File.CopyToAsync(fs);
        await fs.FlushAsync();
        fs.Close();

        try
        {
            var examCourse = await _rep.Change<ExamCourse>().DetachedEntities.FirstOrDefaultAsync(t => t.ExamPlanId == input.ExamPlanId && t.ExamGradeId == input.ExamGradeId && t.CourseId == input.CourseId) ?? throw Oops.Oh(ErrorCode.E2001);
            ret.ScoreReportConfig = JSON.Deserialize<ExamCourseScoreReportConfig>(examCourse.ScoreReportConfig);

            using FileStream fsv = new(filePath, FileMode.Open, FileAccess.Read);
            IWorkbook workbook = ExcelUtil.GetWorkbook(filePath, fsv);
            var sheet = workbook.GetSheetAt(0);
            var rows = sheet.GetRowEnumerator();
            var evaluator = ExcelUtil.GetEvaluator(workbook);

            for (int i = 0; i < ret.ScoreReportConfig.HeaderRowIndex; i++)
            {
                rows.MoveNext();
            }

            rows.MoveNext();
            var headerRow = (IRow)rows.Current;
            List<string> columns = new();
            ret.ColumnsCount = headerRow.LastCellNum;
            if (ret.ColumnsCount != ret.ScoreReportConfig.HeaderColumnNames.Count)
            {
                ret.IsSuccess = false;
            }
            for (int i = 0; i < ret.ColumnsCount; i++)
            {
                var ct = headerRow.GetCell(i)?.ToString() ?? "";
                ct = ct.Trim().Replace("（", "(").Replace("）", ")").Replace(" ", "").ToUpper();
                columns.Add(ct);
            }

            var tplColumns = ret.ScoreReportConfig.HeaderColumnNames.Select(x => x.Trim().Replace("（", "(").Replace("）", ")").Replace(" ", "").ToUpper()).ToList();
            for (int i = 0; i < tplColumns.Count; i++)
            {
                if (i < ret.ColumnsCount)
                {
                    var col = columns[i];
                    UploadExamOrgScoreReportOutput.ExamOrgScoreReportColumnMatchValue mv = new()
                    {
                        Name = col,
                        IsMatched = tplColumns[i] == col,
                    };
                    ret.Columns.Add(i, mv);
                    if (!mv.IsMatched)
                    {
                        ret.IsSuccess = false;
                        ret.ErrorColumnCount++;
                    }
                }
                else
                {
                    ret.Columns.Add(i, new() { Name = "", IsMatched = false });
                    ret.IsSuccess = false;
                    ret.ErrorColumnCount++;
                }
            }

            ret.RowCount = sheet.LastRowNum - ret.ScoreReportConfig.HeaderRowIndex - 1;
            int sampleRowCount = Math.Max(ret.RowCount, 10);
            int rc = 0;
            while (rows.MoveNext())
            {
                var row = (IRow)rows.Current;

                if (++rc > sampleRowCount)
                {
                    break;
                }

                Dictionary<int, object> drow = new();
                for (int j = 0; j < tplColumns.Count; j++)
                {
                    var cell = row.GetCell(j);
                    if (cell != null)
                    {
                        if (cell.CellType == CellType.Numeric)
                        {
                            drow.Add(j, cell.NumericCellValue);
                        }
                        else if (cell.CellType == CellType.Formula)
                        {
                            var cv = evaluator.Evaluate(cell);
                            if (cv.CellType == CellType.Numeric)
                            {
                                drow.Add(j, cv.NumberValue);
                            }
                            else
                            {
                                drow.Add(j, cv.ToString());
                            }
                        }
                        else
                        {
                            drow.Add(j, cell.ToString());
                        }
                    }
                    else
                    {
                        drow.Add(j, "");
                    }
                }
                ret.Rows.Add(drow);
            }

            workbook.Close();
            fsv.Close();

            // 验证成功写入
            if (ret.IsSuccess)
            {
                var item = await _rep.FirstOrDefaultAsync(t => t.ExamPlanId == input.ExamPlanId && t.ExamGradeId == input.ExamGradeId && t.CourseId == input.CourseId && t.SysOrgId == CurrentSysUserInfo.SysOrgId);
                if (item != null)
                {
                    var delPath = Path.Combine(_eqmSiteOptions.ResourceFileRoot, item.FilePath);
                    if (File.Exists(delPath))
                    {
                        File.Delete(delPath);
                    }
                    await item.DeleteAsync();
                }
                var examOrg = await _rep.Change<ExamOrg>().FirstOrDefaultAsync(t => t.ExamPlanId == input.ExamPlanId && t.SysOrgId == CurrentSysUserInfo.SysOrgId) ?? throw Oops.Oh(ErrorCode.E2009);
                item = input.Adapt<ExamOrgScoreReport>();
                item.ExamOrgId = examOrg.Id;
                item.SysOrgId = CurrentSysUserInfo.SysOrgId;
                item.FilePath = saveFilePath;
                item.FileName = input.File.FileName;
                item.FileSize = input.File.Length;
                item.FileExtName = fileExt;
                item.DataRows = JSON.Serialize(ret.Rows);
                await item.InsertAsync();
            }
            else
            {
                File.Delete(filePath);
            }

            return ret;
        }
        catch (Exception ex)
        {
            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }
            throw new Exception("上传错误", ex);
        }
    }
    /// <summary>
    /// 查询某科目校考成绩上报数据
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task<ExamOrgScoreReportOutput> QueryOne(QueryExamOrgScoreReportInput input)
    {
        var item = await _rep.DetachedEntities.FirstOrDefaultAsync(t => t.ExamPlanId == input.ExamPlanId && t.ExamGradeId == input.ExamGradeId && t.CourseId == input.CourseId && t.SysOrgId == CurrentSysUserInfo.SysOrgId);
        return item.Adapt<ExamOrgScoreReportOutput>();
    }

    /// <summary>
    /// 获取机构校考上报明细列表
    /// </summary>
    /// <param name="examPlanId"></param>
    /// <returns></returns>
    public async Task<List<ExamOrgScoreReportItem>> GetListByExamPlanId(int examPlanId)
    {
        var items = await _rep.SqlQueriesAsync<ExamOrgScoreReportItem>($@"
SELECT 
    T1.id AS exam_course_id, 
    T1.exam_plan_id, 
    T1.exam_grade_id,
    T3.grade_begin_year, 
    T1.grade_id,
    T5.`name` AS grade_name,
    T1.course_id, 
    T4.`name` AS course_name,
    T1.score_report_config,
    T2.id,
    T2.exam_org_id, 
    T2.sys_org_id, 
    T2.file_name, 
    T2.file_path, 
    T2.file_ext_name, 
    T2.file_size, 
    T2.create_time, 
    T2.update_time, 
    T2.create_sys_user_id,
    T6.`name` AS create_sys_user_name,
    T2.update_sys_user_id
FROM exam_course AS T1
LEFT JOIN (SELECT * FROM exam_org_score_report WHERE sys_org_id = {CurrentSysUserInfo.SysOrgId}) AS T2 ON T1.exam_plan_id = T2.exam_plan_id AND T1.exam_grade_id =  T2.exam_grade_id AND T1.course_id = T2.course_id
LEFT JOIN exam_grade AS T3 ON T1.exam_grade_id = T3.id
LEFT JOIN base_course AS T4 ON T1.course_id = T4.id
LEFT JOIN base_grade AS T5 ON T1.grade_id = T5.id
LEFT JOIN sys_user AS T6 ON T2.create_sys_user_id = T6.id
JOIN (SELECT * FROM exam_org WHERE sys_org_id = {CurrentSysUserInfo.SysOrgId} AND is_required_exam = 1)AS T7 ON T1.exam_plan_id = T7.exam_plan_id
WHERE T1.exam_plan_id = {examPlanId}
ORDER BY T1.grade_id, T1.course_id
");
        return items;
    }

    /// <summary>
    /// 合并学校上报小题分
    /// </summary>
    /// <param name="examPlanId"></param>
    /// <returns></returns>
    public async Task<(string, byte[])> MergeMinorScore(int examPlanId)
    {
        var examPlan = await _rep.Change<ExamPlan>().FirstOrDefaultAsync(t => t.Id == examPlanId) ?? throw Oops.Oh(ErrorCode.E2001, "监测计划");
        var orgs = await _rep.Change<SysOrg>().DetachedEntities.Where(t => t.EducationStage == examPlan.EducationStage).ToListAsync();
        var orgDict = orgs.ToDictionary(t => t.Code);

        // 临时存放目录
        string fileRoot = Path.Combine(FileUtil.GetTempFileRoot(), $"{Guid.NewGuid()}");
        Directory.CreateDirectory(fileRoot);
        string filePath = Path.Combine(fileRoot, $"{examPlan.Name}-校考-小题成绩");
        Directory.CreateDirectory(filePath);
        try
        {
            var examCourses = await _rep.Change<ExamCourse>()
                                    .DetachedEntities
                                    .Where(t => t.ExamPlanId == examPlanId)
                                    .ProjectToType<ExamCourseOutput>()
                                    .ToListAsync();

            examCourses = examCourses.Where(t => t.ScoreReportConfig.HeaderColumnNames.Count > 0).ToList();
            var allCourses = examCourses.Select(t => new { Id = t.CourseId, t.Course.Name }).Distinct().OrderBy(t => t.Id).ToList();

            IWorkbook twb = new HSSFWorkbook();
            ISheet tsheet = twb.CreateSheet();
            var tCellStyles = _exportExcelService.GetCellStyle(twb);

            int tRowNum = 0;
            IRow tHeaderRow = tsheet.CreateRow(tRowNum++);
            int tci = 0;
            _exportExcelService.AddCell("学校", tHeaderRow, tci++, tCellStyles.ColumnFillHeaderStyle, tsheet, 20);
            _exportExcelService.AddCell("学校ID", tHeaderRow, tci++, tCellStyles.ColumnFillHeaderStyle, tsheet, 8);
            _exportExcelService.AddCell("考号", tHeaderRow, tci++, tCellStyles.ColumnFillHeaderStyle, tsheet, 12);
            _exportExcelService.AddCell("年级", tHeaderRow, tci++, tCellStyles.ColumnFillHeaderStyle, tsheet, 6);
            _exportExcelService.AddCell("班级", tHeaderRow, tci++, tCellStyles.ColumnFillHeaderStyle, tsheet, 6);
            _exportExcelService.AddCell("身份证号码", tHeaderRow, tci++, tCellStyles.ColumnFillHeaderStyle, tsheet, 20);
            _exportExcelService.AddCell("姓名", tHeaderRow, tci++, tCellStyles.ColumnFillHeaderStyle, tsheet, 12);
            Dictionary<short, int> courseIndexes = [];
            foreach (var ac in allCourses)
            {
                courseIndexes.Add(ac.Id, tci);
                _exportExcelService.AddCell(ac.Name, tHeaderRow, tci++, tCellStyles.ColumnFillHeaderStyle, tsheet, 8);
            }

            var ecgs = examCourses.GroupBy(t => t.GradeId);
            foreach (var ecg in ecgs)
            {
                var fecg = ecg.First();

                // 获取抽样学生信息
                var students = await _rep.SqlQueriesAsync<ExamSampleStudentExportDto>($@"
SET @esid = (SELECT id FROM exam_sample WHERE exam_plan_id = @examPlanId AND is_selected = 1);
SELECT 
	T2.sys_org_id,
    T3.tqes_id AS sys_org_tqes_id,
	T3.`name` AS sys_org_name,
	T2.sys_org_branch_id,
	T4.`name` AS sys_org_branch_name,
	T2.grade_id,
    G.grade_number,
	T2.school_class_id,
	T2.class_number,
	T2.certificate_type,
	T2.id_number,
	T1.exam_student_id,
	REPLACE(REPLACE(T2.`name`, '.', ''), '·', '') AS exam_student_name,
	T1.exam_number,
	T1.exam_sample_type
FROM exam_sample_student AS T1
JOIN exam_student AS T2 ON T1.exam_student_id = T2.id
JOIN sys_org AS T3 ON T2.sys_org_id = T3.id
LEFT JOIN sys_org AS T4 ON T2.sys_org_branch_id = T4.id
JOIN base_grade AS G ON T2.grade_id = G.id
WHERE T1.exam_sample_id = @esid AND T2.exam_plan_id = @examPlanId AND T2.grade_id = @gradeId;
", new { ExamPlanId = examPlanId, fecg.GradeId });
                // 总分列表
                List<ExamOrgScoreReportCourseTotalDto> totalItems = [];

                // 按科目合并和提取
                var ecs = ecg.OrderBy(t => t.CourseId).ToList();
                foreach (var examCourse in ecs)
                {
                    var mergeItems = await _rep.SqlQueriesAsync<ExamOrgScoreReportMergeDto>(@$"
SELECT T1.id, t1.sys_org_id, T2.`name` AS sys_org_name, grade_id, course_id, file_path
FROM exam_org_score_report AS T1
JOIN sys_org AS T2 ON T1.sys_org_id = T2.id
WHERE exam_plan_id = @examPlanId AND T1.grade_id = @gradeId AND T1.course_id = @courseId AND T1.sys_org_id IN (
	SELECT sys_org_id 
	FROM exam_org_data_report
	WHERE exam_plan_id = @examPlanId AND type = 6 and `status` = 3
)
", new { ExamPlanId = examPlanId, examCourse.GradeId, examCourse.CourseId });

                    string sheetName = $"{examCourse.Course.Name}小题成绩";
                    IWorkbook wb = new XSSFWorkbook();
                    ISheet sheet = wb.CreateSheet(sheetName);

                    int rowNum = 0;
                    IRow headerRow = sheet.CreateRow(rowNum++);

                    int hccount = examCourse.ScoreReportConfig.HeaderColumnNames.Count;
                    for (int ci = 0; ci < hccount; ci++)
                    {
                        headerRow.CreateCell(ci).SetCellValue(examCourse.ScoreReportConfig.HeaderColumnNames[ci]);
                    }

                    foreach (var mi in mergeItems)
                    {
                        var fp = Path.Combine(_eqmSiteOptions.ResourceFileRoot, mi.FilePath);
                        using FileStream fsv = new(fp, FileMode.Open, FileAccess.Read);
                        IWorkbook swb = ExcelUtil.GetWorkbook(mi.FilePath, fsv);
                        var ssheet = swb.GetSheetAt(0);
                        var rows = ssheet.GetRowEnumerator();
                        var evaluator = ExcelUtil.GetEvaluator(swb);

                        int ri = examCourse.ScoreReportConfig.HeaderRowIndex + 1;
                        // 跳过标题
                        for (int i = 0; i < ri; i++)
                        {
                            rows.MoveNext();
                        }
                        // 读取行
                        while (rows.MoveNext())
                        {
                            var row = (IRow)rows.Current;
                            if ((row.GetCell(0)?.ToString()?.Trim() ?? "") == "")
                            {
                                break;
                            }

                            ExamOrgScoreReportCourseTotalDto totalItem = new()
                            {
                                CourseId = examCourse.CourseId,
                                GradeId = examCourse.GradeId,
                            };
                            var nrow = sheet.CreateRow(rowNum++);
                            for (int ci = 0; ci < hccount; ci++)
                            {
                                var cell = row.GetCell(ci);
                                if (cell == null)
                                {
                                    continue;
                                }

                                #region 提取单元格数据
                                var ncell = nrow.CreateCell(ci);
                                if (ci == 1)
                                {
                                    string en = cell?.ToString()?.Trim() ?? "";
                                    if (en != "" && en.Length < 11)
                                    {
                                        en = en.PadLeft(11, '0');
                                    }
                                    ncell.SetCellValue(en);

                                    // 提取考号
                                    totalItem.ExamNumber = en;
                                    if (totalItem.ExamNumber.Length != 11)
                                    {
                                        //throw new Exception();
                                        throw Oops.Oh(ErrorCode.E2013, $"{fp}，考号长度错误，【{en}】");
                                    }
                                    //var orgCode = totalItem.ExamNumber[..3];
                                    //if(orgDict.TryGetValue(orgCode, out SysOrg value))
                                    //{
                                    //    totalItem.SysOrgId = value.Id;
                                    //}
                                    //if(short.TryParse(totalItem.ExamNumber.AsSpan(3, 2), out short gn))
                                    //{
                                    //    totalItem.GradeNumber = gn;
                                    //}
                                    //if (short.TryParse(totalItem.ExamNumber.AsSpan(5, 2), out short cn))
                                    //{
                                    //    totalItem.ClassNumber = cn;
                                    //}
                                }
                                else // 提取姓名
                                if (ci == 0)
                                {
                                    string n = cell?.ToString()?.Trim() ?? "";
                                    ncell.SetCellValue(n);
                                    totalItem.StudentName = n.Replace(".", "").Replace("·", "");
                                }
                                else if (cell.CellType == CellType.Numeric)
                                {
                                    ncell.SetCellValue(cell.NumericCellValue);
                                }
                                else if (cell.CellType == CellType.Formula)
                                {
                                    var cv = evaluator.Evaluate(cell);
                                    if (cv.CellType == CellType.Numeric)
                                    {
                                        ncell.SetCellValue(cv.NumberValue);
                                    }
                                    else if (cv.CellType == CellType.Boolean)
                                    {
                                        ncell.SetCellValue(cv.BooleanValue);
                                    }
                                    else
                                    {
                                        ncell.SetCellValue(cell?.ToString()?.Trim() ?? "");
                                    }
                                }
                                else if (cell.CellType == CellType.Boolean)
                                {
                                    ncell.SetCellValue(cell.BooleanCellValue);
                                }
                                else
                                {
                                    ncell.SetCellValue(cell?.ToString()?.Trim() ?? "");
                                }
                                #endregion

                                // 提取总分
                                if (ci == 4 && decimal.TryParse(ncell.ToString(), out decimal v))
                                {
                                    totalItem.Score = v;
                                }
                            }
                            totalItems.Add(totalItem);
                        }
                    }

                    sheet.CreateFreezePane(0, 1);
                    MemoryStream ms = new();
                    wb.Write(ms, false);
                    ms.Flush();
                    await File.WriteAllBytesAsync(Path.Combine(filePath, $"{examCourse.ExamGrade.Grade.Name}-{examCourse.Course.Name}-小题成绩.xlsx"), ms.ToArray());
                }

                #region 生成年级文件
                IWorkbook gwb = new HSSFWorkbook();
                ISheet gsheet = gwb.CreateSheet();
                var gcellStyles = _exportExcelService.GetCellStyle(gwb);

                int grownum = 0;
                IRow gHeaderRow = gsheet.CreateRow(grownum++);
                int gci = 0;
                _exportExcelService.AddCell("学校", gHeaderRow, gci++, gcellStyles.ColumnFillHeaderStyle, gsheet, 20);
                _exportExcelService.AddCell("学校ID", gHeaderRow, gci++, gcellStyles.ColumnFillHeaderStyle, gsheet, 8);
                _exportExcelService.AddCell("考号", gHeaderRow, gci++, gcellStyles.ColumnFillHeaderStyle, gsheet, 12);
                _exportExcelService.AddCell("年级", gHeaderRow, gci++, gcellStyles.ColumnFillHeaderStyle, gsheet, 6);
                _exportExcelService.AddCell("班级", gHeaderRow, gci++, gcellStyles.ColumnFillHeaderStyle, gsheet, 6);
                _exportExcelService.AddCell("身份证号码", gHeaderRow, gci++, gcellStyles.ColumnFillHeaderStyle, gsheet, 20);
                _exportExcelService.AddCell("姓名", gHeaderRow, gci++, gcellStyles.ColumnFillHeaderStyle, gsheet, 12);
                foreach (var ec in ecs)
                {
                    _exportExcelService.AddCell(ec.Course.Name, gHeaderRow, gci++, gcellStyles.ColumnFillHeaderStyle, gsheet, 8);
                }

                var items = (from t in totalItems
                             join s in students on t.ExamNumber equals s.ExamNumber
                             select new
                             {
                                 s.SysOrgName,
                                 s.SysOrgTqesId,
                                 t.ExamNumber,
                                 s.GradeNumber,
                                 s.ClassNumber,
                                 s.IdNumber,
                                 t.StudentName,
                                 s.ExamStudentName,
                                 t.CourseId,
                                 t.Score
                             }).GroupBy(t => t.ExamNumber).OrderBy(t => t.Key);

                foreach (var item in items)
                {
                    IRow row = gsheet.CreateRow(grownum++);
                    IRow trow = tsheet.CreateRow(tRowNum++);

                    var stu = item.First();
                    if (stu.StudentName != stu.ExamStudentName)
                    {
                        throw Oops.Oh(ErrorCode.E2013, $"{stu.ToJson()}");
                    }

                    gci = 0;
                    _exportExcelService.AddCell(stu.SysOrgName, row, gci, gcellStyles.LeftCellStyle, gsheet, 20);
                    _exportExcelService.AddCell(stu.SysOrgName, trow, gci++, tCellStyles.LeftCellStyle, tsheet, 20);

                    _exportExcelService.AddCell(stu.SysOrgTqesId, row, gci, gcellStyles.CenterCellStyle, gsheet, 8);
                    _exportExcelService.AddCell(stu.SysOrgTqesId, trow, gci++, tCellStyles.CenterCellStyle, tsheet, 8);

                    _exportExcelService.AddCell(stu.ExamNumber, row, gci, gcellStyles.CenterCellStyle, gsheet, 12);
                    _exportExcelService.AddCell(stu.ExamNumber, trow, gci++, tCellStyles.CenterCellStyle, tsheet, 12);

                    _exportExcelService.AddCell(stu.GradeNumber, row, gci, gcellStyles.CenterCellStyle, gsheet, 6);
                    _exportExcelService.AddCell(stu.GradeNumber, trow, gci++, tCellStyles.CenterCellStyle, tsheet, 6);

                    _exportExcelService.AddCell(stu.ClassNumber, row, gci, gcellStyles.CenterCellStyle, gsheet, 6);
                    _exportExcelService.AddCell(stu.ClassNumber, trow, gci++, tCellStyles.CenterCellStyle, tsheet, 6);

                    _exportExcelService.AddCell(stu.IdNumber ?? "", row, gci, gcellStyles.CenterCellStyle, gsheet, 20);
                    _exportExcelService.AddCell(stu.IdNumber ?? "", trow, gci++, tCellStyles.CenterCellStyle, tsheet, 20);

                    _exportExcelService.AddCell(stu.StudentName, row, gci, gcellStyles.CenterCellStyle, gsheet, 12);
                    _exportExcelService.AddCell(stu.StudentName, trow, gci++, tCellStyles.CenterCellStyle, tsheet, 12);

                    foreach (var ec in ecs)
                    {
                        var s = item.FirstOrDefault(t => t.CourseId == ec.CourseId);
                        if (s == null)
                        {
                            gci++;
                            continue;
                        }

                        _exportExcelService.AddCell(s.Score, trow, courseIndexes[s.CourseId], tCellStyles.CenterCellStyle, tsheet, 8);
                        _exportExcelService.AddCell(s.Score, row, gci++, gcellStyles.CenterCellStyle, gsheet, 8);
                    }
                }

                gsheet.CreateFreezePane(0, 1);
                MemoryStream gms = new();
                gwb.Write(gms, false);
                gms.Flush();
                await File.WriteAllBytesAsync(Path.Combine(filePath, $"{examPlan.EducationStage.GetDescription()}-{fecg.ExamGrade.Grade.Name}-期末总分成绩（TQES）.xlsx"), gms.ToArray());
                #endregion
            }

            tsheet.CreateFreezePane(0, 1);
            MemoryStream tms = new();
            twb.Write(tms, false);
            tms.Flush();
            await File.WriteAllBytesAsync(Path.Combine(filePath, $"{examPlan.EducationStage.GetDescription()}-期末总分成绩.xlsx"), tms.ToArray());

            string outFileName = $"{examPlan.Name}-校考-小题成绩.zip";
            string outFilePath = Path.Combine(fileRoot, outFileName);
            ICSharpCode.SharpZipLib.Zip.FastZip zip = new();
            zip.CreateZip(outFilePath, filePath, true, string.Empty);

            var retBytes = await File.ReadAllBytesAsync(outFilePath);
            return (outFileName, retBytes);
        }
        catch (Exception ex)
        {
            throw new Exception("导出错误", ex);
        }
        finally
        {
            Directory.Delete(fileRoot, true);
        }
    }
}
