YBEE.EQM/YBEE.EQM.Application/Ncee/NceeExport/Services/NceeExportService.cs

using NPOI.HSSF.UserModel;
using NPOI.OpenXmlFormats.Dml.Chart;
using NPOI.SS.UserModel;
using NPOI.SS.UserModel.Charts;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System.Data;
using YBEE.EQM.Core;

namespace YBEE.EQM.Application;

/// <summary>
/// 高中模拟分析导出服务
/// </summary>
public class NceeExportService : INceeExportService, ITransient
{
    private readonly IRepository<NceeLineTotal> _rep;
    private readonly IExportExcelService _exportExcelService;

    public NceeExportService(IRepository<NceeLineTotal> rep, IExportExcelService exportExcelService)
    {
        _rep = rep;
        _exportExcelService = exportExcelService;
    }

    #region 导出报表
    /// <summary>
    /// 导出联盟区县模拟划线报表
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <returns></returns>
    /// <exception cref="Exception"></exception>
    public async Task<(string, byte[])> ExportAllianceDistrict(int nceePlanId)
    {
        var nceePlan = await _rep.Change<NceePlan>().DetachedEntities.ProjectToType<NceePlanOutput>().FirstOrDefaultAsync(t => t.Id == nceePlanId) ?? throw Oops.Oh(ErrorCode.E2001);
        var orgList = await GetSysOrgList(nceePlanId);
        var courses = await _rep.Change<Course>().Where(t => t.Id < 10 || t.Id == 100 || t.Id == 101).ProjectToType<NceeCourseDto>().OrderBy(t => t.Sequence).ToListAsync();
        var baseLineTable = await GetBaseLineTable(nceePlanId);
        var lineLevelTable = baseLineTable.DefaultView.ToTable(true, "ncee_line_level", "ncee_line_level_name");

        // 临时存放目录
        string fileRoot = Path.Combine(FileUtil.GetTempFileRoot(), $"{Guid.NewGuid()}");
        Directory.CreateDirectory(fileRoot);
        string filePath = Path.Combine(fileRoot, $"{nceePlan.Name}-统计结果");
        Directory.CreateDirectory(filePath);
        try
        {
            // 导出物理类分段统计
            string rangePath4 = Path.Combine(filePath, $"{nceePlan.Name}-物理类分段统计表.xlsx");
            var bytes4 = await ExportScoreRange(nceePlanId, 4, orgList, courses);
            await File.WriteAllBytesAsync(rangePath4, bytes4);

            // 导出历史类分段统计
            string rangePath8 = Path.Combine(filePath, $"{nceePlan.Name}-历史类分段统计表.xlsx");
            var bytes8 = await ExportScoreRange(nceePlanId, 8, orgList, courses);
            await File.WriteAllBytesAsync(rangePath8, bytes8);

            // 导出有效分统计表
            string lbPath = Path.Combine(filePath, $"{nceePlan.Name}-有效分及上线统计表.xlsx");
            var lbs = await ExportLine(nceePlanId, courses, lineLevelTable, "区县");
            await File.WriteAllBytesAsync(lbPath, lbs);

            if (nceePlan.Config.ExportConvertScoreEnabled)
            {
                // 导出机构转换成绩
                var orgConvertScores = await ExportOrgConvertScore(nceePlanId, orgList, "区县", isExportConvertRange: nceePlan.Config.ConvertEnabled, isExportOrder: nceePlan.Config.ExportOrderEnabled);
                foreach (var convertScore in orgConvertScores)
                {
                    string orgPath = Path.Combine(filePath, convertScore.Key);
                    Directory.CreateDirectory(orgPath);
                    await File.WriteAllBytesAsync(Path.Combine(orgPath, $"{nceePlan.Name}-转换成绩-{convertScore.Key.Split("-")[1]}.xlsx"), convertScore.Value);
                    File.Copy(rangePath4, Path.Combine(orgPath, Path.GetFileName(rangePath4)));
                    File.Copy(rangePath8, Path.Combine(orgPath, Path.GetFileName(rangePath8)));
                    File.Copy(lbPath, Path.Combine(orgPath, Path.GetFileName(lbPath)));
                }

                // 导出全部转换成绩
                var convertScoreBytes = await ExportConvertScore(nceePlanId, "区县", isExportConvertRange: nceePlan.Config.ConvertEnabled, isExportOrder: nceePlan.Config.ExportOrderEnabled);
                await File.WriteAllBytesAsync(Path.Combine(filePath, $"{nceePlan.Name}-转换成绩-总表.xlsx"), convertScoreBytes);
            }

            string outFileName = $"{nceePlan.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);
        }
    }
    /// <summary>
    /// 导出已选科的模拟划线报表
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <returns></returns>
    /// <exception cref="Exception"></exception>
    public async Task<(string, byte[])> ExportDirectionSeleted(int nceePlanId)
    {
        var nceePlan = await _rep.Change<NceePlan>().DetachedEntities.ProjectToType<NceePlanOutput>().FirstOrDefaultAsync(t => t.Id == nceePlanId) ?? throw Oops.Oh(ErrorCode.E2001);
        var orgList = await GetSysOrgList(nceePlanId);
        var courses = await _rep.Change<Course>().Where(t => t.Id < 10 || t.Id == 100 || t.Id == 101).ProjectToType<NceeCourseDto>().OrderBy(t => t.Sequence).ToListAsync();
        var baseLineTable = await GetBaseLineTable(nceePlanId);
        var lineLevelTable = baseLineTable.DefaultView.ToTable(true, "ncee_line_level", "ncee_line_level_name");

        // 临时存放目录
        string fileRoot = Path.Combine(FileUtil.GetTempFileRoot(), $"{Guid.NewGuid()}");
        Directory.CreateDirectory(fileRoot);
        string filePath = Path.Combine(fileRoot, $"{nceePlan.Name}-统计结果");
        Directory.CreateDirectory(filePath);
        try
        {
            // 导出物理类分段统计
            string rangePath4 = Path.Combine(filePath, $"{nceePlan.Name}-物理类分段统计表.xlsx");
            var bytes4 = await ExportScoreRange(nceePlanId, 4, orgList, courses);
            await File.WriteAllBytesAsync(rangePath4, bytes4);

            // 导出历史类分段统计
            string rangePath8 = Path.Combine(filePath, $"{nceePlan.Name}-历史类分段统计表.xlsx");
            var bytes8 = await ExportScoreRange(nceePlanId, 8, orgList, courses);
            await File.WriteAllBytesAsync(rangePath8, bytes8);

            // 导出有效分统计表
            string lbPath = Path.Combine(filePath, $"{nceePlan.Name}-有效分及上线统计表.xlsx");
            var lbs = await ExportLine(nceePlanId, courses, lineLevelTable, "学校", false);
            await File.WriteAllBytesAsync(lbPath, lbs);

            // 导出各班级上线情况
            string classPath = Path.Combine(filePath, $"{nceePlan.Name}-各班上线情况统计表.xlsx");
            var classBs = await ExportClassLine(nceePlanId, baseLineTable);
            await File.WriteAllBytesAsync(classPath, classBs);

            // 导出各机构上线统计数据
            var orgClassLines = await ExportOrgClassLine(nceePlanId, orgList, baseLineTable, courses);
            foreach (var orgClassLine in orgClassLines)
            {
                string orgPath = Path.Combine(filePath, orgClassLine.Key);
                if (!Directory.Exists(orgPath)) { Directory.CreateDirectory(orgPath); }
                await File.WriteAllBytesAsync(Path.Combine(orgPath, $"{nceePlan.Name}-{orgClassLine.Key}-上线统计.xlsx"), orgClassLine.Value);
            }

            // 导出各机构分段数据（物理）
            var orgRanges4 = await ExportOrgScoreRange(nceePlanId, 4, orgList, courses);
            foreach (var range in orgRanges4)
            {
                string orgPath = Path.Combine(filePath, range.Key);
                if (!Directory.Exists(orgPath)) { Directory.CreateDirectory(orgPath); }
                await File.WriteAllBytesAsync(Path.Combine(orgPath, $"{nceePlan.Name}-{range.Key}-分段统计-物理类.xlsx"), range.Value);
            }
            // 导出各机构分段数据（历史）
            var orgRanges8 = await ExportOrgScoreRange(nceePlanId, 8, orgList, courses);
            foreach (var range in orgRanges8)
            {
                string orgPath = Path.Combine(filePath, range.Key);
                if (!Directory.Exists(orgPath)) { Directory.CreateDirectory(orgPath); }
                await File.WriteAllBytesAsync(Path.Combine(orgPath, $"{nceePlan.Name}-{range.Key}-分段统计-历史类.xlsx"), range.Value);
            }

            if (nceePlan.Config.ExportConvertScoreEnabled)
            {
                // 导出各机构转换成绩
                var orgConvertScores = await ExportOrgConvertScore(nceePlanId, orgList, "学校", isExportConvertRange: nceePlan.Config.ConvertEnabled);
                foreach (var convertScore in orgConvertScores)
                {
                    string orgPath = Path.Combine(filePath, convertScore.Key);
                    Directory.CreateDirectory(orgPath);
                    await File.WriteAllBytesAsync(Path.Combine(orgPath, $"{nceePlan.Name}-转换成绩-{convertScore.Key}.xlsx"), convertScore.Value);
                }

                // 导出全部转换成绩
                var convertScoreBytes = await ExportConvertScore(nceePlanId, "学校", isExportConvertRange: nceePlan.Config.ConvertEnabled);
                await File.WriteAllBytesAsync(Path.Combine(filePath, $"{nceePlan.Name}-转换成绩-总表.xlsx"), convertScoreBytes);
            }

            string outFileName = $"{nceePlan.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);
        }
    }
    /// <summary>
    /// 导出未选科的模拟划线报表
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <returns></returns>
    /// <exception cref="Exception"></exception>
    public async Task<(string, byte[])> ExportDirectionUnseleted(int nceePlanId)
    {
        var nceePlan = await _rep.Change<NceePlan>().DetachedEntities.ProjectToType<NceePlanOutput>().FirstOrDefaultAsync(t => t.Id == nceePlanId) ?? throw Oops.Oh(ErrorCode.E2001);
        var orgList = await GetSysOrgList(nceePlanId);
        var courses = await _rep.Change<Course>().Where(t => t.Id < 10 || t.Id == 100).ProjectToType<NceeCourseDto>().OrderBy(t => t.Sequence).ToListAsync();
        var baseLineTable = await GetBaseLineTable(nceePlanId);
        var lineLevelTable = baseLineTable.DefaultView.ToTable(true, "ncee_line_level", "ncee_line_level_name");

        // 临时存放目录
        string fileRoot = Path.Combine(FileUtil.GetTempFileRoot(), $"{Guid.NewGuid()}");
        Directory.CreateDirectory(fileRoot);
        string filePath = Path.Combine(fileRoot, $"{nceePlan.Name}-统计结果");
        Directory.CreateDirectory(filePath);
        try
        {
            // 导出分段统计
            string rangePath4 = Path.Combine(filePath, $"{nceePlan.Name}-分段统计表.xlsx");
            var bytes4 = await ExportScoreRangeUnselected(nceePlanId, orgList, courses);
            await File.WriteAllBytesAsync(rangePath4, bytes4);

            // 导出有效分统计表
            string lbPath = Path.Combine(filePath, $"{nceePlan.Name}-有效分及上线统计表.xlsx");
            var lbs = await ExportLineUnselected(nceePlanId, courses, lineLevelTable);
            await File.WriteAllBytesAsync(lbPath, lbs);

            // 导出各班级上线情况
            string classPath = Path.Combine(filePath, $"{nceePlan.Name}-各班上线情况统计表.xlsx");
            var classBs = await ExportClassLineUnselected(nceePlanId, baseLineTable);
            await File.WriteAllBytesAsync(classPath, classBs);

            // 导出各机构上线统计数据
            var orgClassLines = await ExportOrgClassLineUnselected(nceePlanId, orgList, baseLineTable, courses);
            foreach (var orgClassLine in orgClassLines)
            {
                string orgPath = Path.Combine(filePath, orgClassLine.Key);
                if (!Directory.Exists(orgPath)) { Directory.CreateDirectory(orgPath); }
                await File.WriteAllBytesAsync(Path.Combine(orgPath, $"{nceePlan.Name}-{orgClassLine.Key}-上线统计.xlsx"), orgClassLine.Value);
            }

            // 导出各机构分段数据
            var orgRanges4 = await ExportOrgScoreRangeUnselected(nceePlanId, orgList, courses);
            foreach (var range in orgRanges4)
            {
                string orgPath = Path.Combine(filePath, range.Key);
                if (!Directory.Exists(orgPath)) { Directory.CreateDirectory(orgPath); }
                await File.WriteAllBytesAsync(Path.Combine(orgPath, $"{nceePlan.Name}-{range.Key}-分段统计.xlsx"), range.Value);
            }

            string outFileName = $"{nceePlan.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);
        }
    }
    #endregion

    #region 导出方法
    /// <summary>
    /// 导出分数段统计表
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <param name="directionCourseId"></param>
    /// <param name="orgList"></param>
    /// <param name="courses"></param>
    /// <returns></returns>
    private async Task<byte[]> ExportScoreRange(int nceePlanId, short directionCourseId, List<SysOrgOutput> orgList, List<NceeCourseDto> courses)
    {
        var tcourses = courses.Where(t => t.Id < 10).ToList();
        tcourses.Insert(0, courses.FirstOrDefault(t => t.Id == 100));

        IWorkbook wb = new XSSFWorkbook();
        foreach (var course in tcourses)
        {
            if (course.IsSkip(directionCourseId)) { continue; }

            DataTable table;
            if (course.Id == 100)
            {
                table = await GetTotalScoreRangeTable(nceePlanId, directionCourseId, orgList);
            }
            else
            {
                table = await GetCourseScoreRangeTable(nceePlanId, directionCourseId, course.Id, orgList);
            }

            string sheetName = course.Name;
            if (course.Id == 100 || course.Id > 4 && course.Id != 8)
            {
                sheetName = $"{sheetName}X";
            }

            ISheet sheet = wb.CreateSheet(sheetName);
            sheet.DisplayGridlines = false;

            var cellStyle = _exportExcelService.GetCellStyle(wb);

            // 行索引号
            int rowNum = 0;

            #region 列头
            IRow headerRow = sheet.CreateRow(rowNum++);
            int ci = 0;
            _exportExcelService.AddCell("分数段", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 9);
            foreach (var org in orgList)
            {
                _exportExcelService.AddCell(org.ShortName2, headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 7);
            }
            _exportExcelService.AddCell("该段人数", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 9);
            _exportExcelService.AddCell("人数累计", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 9);

            sheet.CreateFreezePane(0, rowNum);
            #endregion

            #region 数据
            foreach (DataRow dr in table.Rows)
            {
                IRow row = sheet.CreateRow(rowNum++);
                int rci = 0;
                _exportExcelService.AddCell(dr["exam_score_range_name"], row, rci++, cellStyle.CenterCellStyle);
                foreach (var org in orgList)
                {
                    _exportExcelService.AddCell(dr[$"total_count_{org.Id}"], row, rci++, cellStyle.CenterCellStyle);
                }
                _exportExcelService.AddCell(dr["total_count_range_current"], row, rci++, cellStyle.CenterCellStyle);
                _exportExcelService.AddCell(dr["total_count_range_sum"], row, rci++, cellStyle.CenterCellStyle);
            }
            #endregion

            #region 图表
            int oi = 0;
            const int CHART_ROW_COUNT = 13;
            foreach (var org in orgList)
            {
                XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
                int startRow = oi * CHART_ROW_COUNT + oi + 1;
                int endRow = startRow + CHART_ROW_COUNT;
                XSSFClientAnchor anchor = (XSSFClientAnchor)drawing.CreateAnchor(0, 0, 0, 0, ci + 1, startRow, ci + 10, endRow);

                CreateBarChart(sheet, drawing, anchor, 1, table.Rows.Count + 1, oi + 1, org.ShortName2, org.ShortName2, "人数", "分数段");
                oi++;
            }
            #endregion
        }

        MemoryStream ms = new();
        wb.Write(ms, false);
        ms.Flush();

        return ms.ToArray();
    }
    /// <summary>
    /// 导出分数段统计表（未选科）
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <param name="orgList"></param>
    /// <param name="courses"></param>
    /// <returns></returns>
    private async Task<byte[]> ExportScoreRangeUnselected(int nceePlanId, List<SysOrgOutput> orgList, List<NceeCourseDto> courses)
    {
        var tcourses = courses.Where(t => t.Id < 10).ToList();
        tcourses.Insert(0, courses.FirstOrDefault(t => t.Id == 100));

        IWorkbook wb = new XSSFWorkbook();
        foreach (var course in tcourses)
        {
            DataTable table;
            if (course.Id == 100)
            {
                table = await GetTotalScoreRangeTableUnselected(nceePlanId, orgList);
            }
            else
            {
                table = await GetCourseScoreRangeTableUnselected(nceePlanId, course.Id, orgList);
            }

            string sheetName = course.Name;

            ISheet sheet = wb.CreateSheet(sheetName);
            sheet.DisplayGridlines = false;

            var cellStyle = _exportExcelService.GetCellStyle(wb);

            // 行索引号
            int rowNum = 0;

            #region 列头
            IRow headerRow = sheet.CreateRow(rowNum++);
            int ci = 0;
            _exportExcelService.AddCell("分数段", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 9);
            foreach (var org in orgList)
            {
                _exportExcelService.AddCell(org.ShortName2, headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 7);
            }
            _exportExcelService.AddCell("该段人数", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 9);
            _exportExcelService.AddCell("人数累计", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 9);

            sheet.CreateFreezePane(0, rowNum);
            #endregion

            #region 数据
            foreach (DataRow dr in table.Rows)
            {
                IRow row = sheet.CreateRow(rowNum++);
                int rci = 0;
                _exportExcelService.AddCell(dr["exam_score_range_name"], row, rci++, cellStyle.CenterCellStyle);
                foreach (var org in orgList)
                {
                    _exportExcelService.AddCell(dr[$"total_count_{org.Id}"], row, rci++, cellStyle.CenterCellStyle);
                }
                _exportExcelService.AddCell(dr["total_count_range_current"], row, rci++, cellStyle.CenterCellStyle);
                _exportExcelService.AddCell(dr["total_count_range_sum"], row, rci++, cellStyle.CenterCellStyle);
            }
            #endregion

            #region 图表
            int oi = 0;
            const int CHART_ROW_COUNT = 13;
            foreach (var org in orgList)
            {
                XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
                int startRow = oi * CHART_ROW_COUNT + oi + 1;
                int endRow = startRow + CHART_ROW_COUNT;
                XSSFClientAnchor anchor = (XSSFClientAnchor)drawing.CreateAnchor(0, 0, 0, 0, ci + 1, startRow, ci + 10, endRow);

                CreateBarChart(sheet, drawing, anchor, 1, table.Rows.Count + 1, oi + 1, org.ShortName2, org.ShortName2, "人数", "分数段");
                oi++;
            }
            #endregion
        }

        MemoryStream ms = new();
        wb.Write(ms, false);
        ms.Flush();

        return ms.ToArray();
    }
    /// <summary>
    /// 导出机构分数段统计表
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <param name="directionCourseId"></param>
    /// <param name="orgList"></param>
    /// <param name="courses"></param>
    /// <returns></returns>
    private async Task<Dictionary<string, byte[]>> ExportOrgScoreRange(int nceePlanId, short directionCourseId, List<SysOrgOutput> orgList, List<NceeCourseDto> courses)
    {
        Dictionary<string, byte[]> ret = new();

        var tcourses = courses.Where(t => t.Id < 10).ToList();
        tcourses.Insert(0, courses.FirstOrDefault(t => t.Id == 100));

        foreach (var org in orgList)
        {
            IWorkbook wb = new XSSFWorkbook();
            foreach (var course in tcourses)
            {
                if (course.IsSkip(directionCourseId)) { continue; }

                DataTable table;
                if (course.Id == 100)
                {
                    table = await GetTotalScoreRangeTable(nceePlanId, directionCourseId, orgList);
                }
                else
                {
                    table = await GetCourseScoreRangeTable(nceePlanId, directionCourseId, course.Id, orgList);
                }

                string sheetName = course.Name;
                if (course.Id == 100 || course.Id > 4 && course.Id != 8)
                {
                    sheetName = $"{sheetName}X";
                }

                ISheet sheet = wb.CreateSheet(sheetName);
                sheet.DisplayGridlines = false;

                var cellStyle = _exportExcelService.GetCellStyle(wb);

                // 行索引号
                int rowNum = 0;

                #region 列头
                IRow headerRow = sheet.CreateRow(rowNum++);
                int ci = 0;
                _exportExcelService.AddCell("分数段", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 9);
                _exportExcelService.AddCell("人数", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 7);
                _exportExcelService.AddCell("累计", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 7);

                sheet.CreateFreezePane(0, rowNum);
                #endregion

                #region 数据
                decimal scount = 0;
                foreach (DataRow dr in table.Rows)
                {
                    IRow row = sheet.CreateRow(rowNum++);
                    int rci = 0;
                    _exportExcelService.AddCell(dr["exam_score_range_name"], row, rci++, cellStyle.CenterCellStyle);
                    decimal c = (decimal)dr[$"total_count_{org.Id}"];
                    scount += c;
                    _exportExcelService.AddCell(c, row, rci++, cellStyle.CenterCellStyle);
                    _exportExcelService.AddCell(scount, row, rci++, cellStyle.CenterCellStyle);
                }
                #endregion

                #region 图表
                const int CHART_ROW_COUNT = 13;
                XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
                int startRow = 1;
                int endRow = startRow + CHART_ROW_COUNT;
                XSSFClientAnchor anchor = (XSSFClientAnchor)drawing.CreateAnchor(0, 0, 0, 0, ci + 1, startRow, ci + 10, endRow);
                CreateBarChart(sheet, drawing, anchor, 1, table.Rows.Count + 1, 1, org.ShortName, org.ShortName, "人数", "分数段");
                #endregion
            }

            MemoryStream ms = new();
            wb.Write(ms, false);
            ms.Flush();

            ret.Add($"{org.Code}-{org.ShortName}", ms.ToArray());
        }

        return ret;
    }
    /// <summary>
    /// 导出机构分数段统计表（未选科）
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <param name="orgList"></param>
    /// <param name="courses"></param>
    /// <returns></returns>
    private async Task<Dictionary<string, byte[]>> ExportOrgScoreRangeUnselected(int nceePlanId, List<SysOrgOutput> orgList, List<NceeCourseDto> courses)
    {
        Dictionary<string, byte[]> ret = new();

        var tcourses = courses.Where(t => t.Id < 10).ToList();
        tcourses.Insert(0, courses.FirstOrDefault(t => t.Id == 100));

        foreach (var org in orgList)
        {
            IWorkbook wb = new XSSFWorkbook();
            foreach (var course in tcourses)
            {
                DataTable table;
                if (course.Id == 100)
                {
                    table = await GetTotalScoreRangeTableUnselected(nceePlanId, orgList);
                }
                else
                {
                    table = await GetCourseScoreRangeTableUnselected(nceePlanId, course.Id, orgList);
                }

                string sheetName = course.Name;

                ISheet sheet = wb.CreateSheet(sheetName);
                sheet.DisplayGridlines = false;

                var cellStyle = _exportExcelService.GetCellStyle(wb);

                // 行索引号
                int rowNum = 0;

                #region 列头
                IRow headerRow = sheet.CreateRow(rowNum++);
                int ci = 0;
                _exportExcelService.AddCell("分数段", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 9);
                _exportExcelService.AddCell("人数", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 7);
                _exportExcelService.AddCell("累计", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 7);

                sheet.CreateFreezePane(0, rowNum);
                #endregion

                #region 数据
                decimal scount = 0;
                foreach (DataRow dr in table.Rows)
                {
                    IRow row = sheet.CreateRow(rowNum++);
                    int rci = 0;
                    _exportExcelService.AddCell(dr["exam_score_range_name"], row, rci++, cellStyle.CenterCellStyle);
                    decimal c = (decimal)dr[$"total_count_{org.Id}"];
                    scount += c;
                    _exportExcelService.AddCell(c, row, rci++, cellStyle.CenterCellStyle);
                    _exportExcelService.AddCell(scount, row, rci++, cellStyle.CenterCellStyle);
                }
                #endregion

                #region 图表
                const int CHART_ROW_COUNT = 13;
                XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
                int startRow = 1;
                int endRow = startRow + CHART_ROW_COUNT;
                XSSFClientAnchor anchor = (XSSFClientAnchor)drawing.CreateAnchor(0, 0, 0, 0, ci + 1, startRow, ci + 10, endRow);
                CreateBarChart(sheet, drawing, anchor, 1, table.Rows.Count + 1, 1, org.ShortName, org.ShortName, "人数", "分数段");
                #endregion
            }

            MemoryStream ms = new();
            wb.Write(ms, false);
            ms.Flush();

            ret.Add($"{org.Code}-{org.ShortName}", ms.ToArray());
        }

        return ret;
    }
    /// <summary>
    /// 导出有效分
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <param name="wb"></param>
    /// <param name="cellStyle"></param>
    /// <param name="lineScoreTable"></param>
    /// <param name="isExportRate"></param>
    /// <returns></returns>
    private async Task ExportLineScore(int nceePlanId, IWorkbook wb, ExportExcelCellStyle cellStyle, DataTable lineScoreTable = null, bool isExportRate = false)
    {
        var cellStylePercent = wb.CreateCellStyle();
        cellStylePercent.CloneStyleFrom(cellStyle.CenterCellStyle);
        cellStylePercent.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");

        lineScoreTable ??= await GetLineScoreTable(nceePlanId);
        int mcount = isExportRate ? 8 : 7;

        ISheet sheet = wb.CreateSheet("有效分");
        sheet.DisplayGridlines = false;
        // 行索引号
        int rowNum = 0;

        foreach (var directionCourse in NceeUtil.DirectionCourses)
        {
            #region 表头
            IRow headerRow1 = sheet.CreateRow(rowNum++);
            headerRow1.HeightInPoints = 30;
            int ci = 0;
            int scoreWidth = 8;
            _exportExcelService.AddCell($"{directionCourse.Name}类有效分", headerRow1, ci++, cellStyle.TitleStyle, sheet, scoreWidth);
            for (; ci < mcount; ci++)
            {
                _exportExcelService.AddCell("", headerRow1, ci, cellStyle.TitleStyle, sheet, scoreWidth);
            }
            sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));

            IRow headerRow2 = sheet.CreateRow(rowNum++);
            headerRow2.HeightInPoints = 15;
            ci = 0;
            _exportExcelService.AddCell("类型", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            _exportExcelService.AddCell("语文", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            _exportExcelService.AddCell("数学", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            _exportExcelService.AddCell("英语", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            _exportExcelService.AddCell(directionCourse.Name, headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            _exportExcelService.AddCell("综合X", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            _exportExcelService.AddCell("总分X", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            if (isExportRate)
            {
                _exportExcelService.AddCell("划线率", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            }
            #endregion

            #region 数据
            foreach (DataRow dr in lineScoreTable.Select($"direction_course_id = {directionCourse.Id}"))
            {
                IRow row = sheet.CreateRow(rowNum++);
                row.HeightInPoints = 15;
                int rci = 0;
                _exportExcelService.AddCell(dr["ncee_line_level_name"], row, rci++, cellStyle.CenterCellStyle);
                _exportExcelService.AddCell(dr["line_score_x_1"], row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
                _exportExcelService.AddCell(dr["line_score_x_2"], row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
                _exportExcelService.AddCell(dr["line_score_x_3"], row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
                _exportExcelService.AddCell(dr[$"line_score_x_{directionCourse.Id}"], row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
                _exportExcelService.AddCell(dr["line_score_x_101"], row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
                _exportExcelService.AddCell(dr["line_score_x_100"], row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
                if (isExportRate)
                {
                    _exportExcelService.AddCell(dr["line_rate"], row, rci++, cellStylePercent, zeroToBlank: true);
                }
            }
            #endregion

            rowNum += 2;
        }
    }
    /// <summary>
    /// 导出有效分（未选科）
    /// </summary>
    /// <param name="wb"></param>
    /// <param name="cellStyle"></param>
    /// <param name="lineScoreTable"></param>
    /// <param name="courses"></param>
    /// <returns></returns>
    private void ExportLineScore(IWorkbook wb, ExportExcelCellStyle cellStyle, DataTable lineScoreTable, List<NceeCourseDto> courses)
    {
        var cellStylePercent = wb.CreateCellStyle();
        cellStylePercent.CloneStyleFrom(cellStyle.CenterCellStyle);
        cellStylePercent.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");

        ISheet sheet = wb.CreateSheet("有效分");
        sheet.DisplayGridlines = false;
        // 行索引号
        int rowNum = 0;

        #region 表头
        IRow headerRow1 = sheet.CreateRow(rowNum++);
        headerRow1.HeightInPoints = 30;
        int ci = 0;
        int scoreWidth = 8;
        _exportExcelService.AddCell($"有效分", headerRow1, ci++, cellStyle.TitleStyle, sheet, scoreWidth);
        for (; ci < courses.Count + 1; ci++)
        {
            _exportExcelService.AddCell("", headerRow1, ci, cellStyle.TitleStyle, sheet, scoreWidth);
        }
        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));

        IRow headerRow2 = sheet.CreateRow(rowNum++);
        headerRow2.HeightInPoints = 15;
        ci = 0;
        _exportExcelService.AddCell("类型", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        foreach (var course in courses)
        {
            _exportExcelService.AddCell(course.Name, headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        }
        #endregion

        #region 数据
        foreach (DataRow dr in lineScoreTable.Select($"direction_course_id = 99"))
        {
            IRow row = sheet.CreateRow(rowNum++);
            row.HeightInPoints = 15;
            int rci = 0;
            _exportExcelService.AddCell(dr["ncee_line_level_name"], row, rci++, cellStyle.CenterCellStyle);
            foreach (var course in courses)
            {
                _exportExcelService.AddCell(dr[$"line_score_x_{course.Id}"], row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
            }
        }
        #endregion
    }
    /// <summary>
    /// 导出转换区间
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <param name="wb"></param>
    /// <param name="cellStyle"></param>
    /// <returns></returns>
    private async Task ExportConvertRange(int nceePlanId, IWorkbook wb, ExportExcelCellStyle cellStyle)
    {
        var convertRangeTable = await GetConvertRangeTable(nceePlanId);
        var convertGrades = await _rep.Change<NceeConvertGrade>().Entities.ToListAsync();

        ISheet convertRangeSheet = wb.CreateSheet("原始分转换区间");
        convertRangeSheet.DisplayGridlines = false;
        // 行索引号
        int rowNum = 0;

        // 标题1
        IRow titleRow1 = convertRangeSheet.CreateRow(rowNum++);
        titleRow1.HeightInPoints = 30;
        int ci = 0;
        int colWidth = 13;
        _exportExcelService.AddCell("等级比例赋分区间", titleRow1, ci++, cellStyle.TitleStyle, convertRangeSheet, colWidth);
        foreach (var rg in convertGrades)
        {
            _exportExcelService.AddCell("", titleRow1, ci++, cellStyle.TitleStyle, convertRangeSheet, colWidth);
        }
        convertRangeSheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, convertGrades.Count));

        // 表头1
        IRow headerRow1 = convertRangeSheet.CreateRow(rowNum++);
        headerRow1.HeightInPoints = 15;
        ci = 0;
        _exportExcelService.AddCell("等级", headerRow1, ci++, cellStyle.ColumnFillHeaderStyle, convertRangeSheet, colWidth);
        foreach (var rg in convertGrades)
        {
            _exportExcelService.AddCell(rg.Name, headerRow1, ci++, cellStyle.ColumnFillHeaderStyle, convertRangeSheet, colWidth);
        }

        // 表头：比例
        IRow headerRow2 = convertRangeSheet.CreateRow(rowNum++);
        headerRow2.HeightInPoints = 15;
        ci = 0;
        _exportExcelService.AddCell("比例", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, convertRangeSheet, colWidth);
        foreach (var rg in convertGrades)
        {
            _exportExcelService.AddCell($"约{Math.Round(rg.Rate * 100, 0)}%", headerRow2, ci++, cellStyle.CenterCellStyle, convertRangeSheet, colWidth);
        }

        // 表头：赋分区间
        IRow headerRow3 = convertRangeSheet.CreateRow(rowNum++);
        headerRow3.HeightInPoints = 15;
        ci = 0;
        _exportExcelService.AddCell("赋分区间", headerRow3, ci++, cellStyle.ColumnFillHeaderStyle, convertRangeSheet, colWidth);
        foreach (var rg in convertGrades)
        {
            _exportExcelService.AddCell($"{Math.Round(rg.MinScore)}～{Math.Round(rg.MaxScore)}", headerRow3, ci++, cellStyle.CenterCellStyle, convertRangeSheet, colWidth);
        }

        rowNum += 2;

        // 标题2
        IRow titleRow2 = convertRangeSheet.CreateRow(rowNum++);
        titleRow2.HeightInPoints = 30;
        ci = 0;
        _exportExcelService.AddCell("原始分转换区间", titleRow2, ci++, cellStyle.TitleStyle, convertRangeSheet, colWidth);
        foreach (var rg in convertGrades)
        {
            _exportExcelService.AddCell("", titleRow2, ci++, cellStyle.TitleStyle, convertRangeSheet, colWidth);
        }
        convertRangeSheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, convertGrades.Count));

        // 表头4
        IRow headerRow4 = convertRangeSheet.CreateRow(rowNum++);
        headerRow4.HeightInPoints = 15;
        ci = 0;
        _exportExcelService.AddCell("学科", headerRow4, ci++, cellStyle.ColumnFillHeaderStyle, convertRangeSheet, colWidth);
        foreach (var rg in convertGrades)
        {
            _exportExcelService.AddCell(rg.Name, headerRow4, ci++, cellStyle.ColumnFillHeaderStyle, convertRangeSheet, colWidth);
        }

        // 数据
        foreach (DataRow dr in convertRangeTable.Rows)
        {
            IRow row = convertRangeSheet.CreateRow(rowNum++);
            row.HeightInPoints = 15;
            int rci = 0;
            _exportExcelService.AddCell(dr["course_name"], row, rci++, cellStyle.CenterCellStyle);
            foreach (var rg in convertGrades)
            {
                var a1 = ((decimal)dr[$"min_score_{rg.Name.ToLower()}"]).ToString("0.#");
                var a2 = ((decimal)dr[$"max_score_{rg.Name.ToLower()}"]).ToString("0.#");
                _exportExcelService.AddCell($"[{a1}, {a2}]", row, rci++, cellStyle.LeftCellStyle);
            }
        }
    }
    /// <summary>
    /// 导出上线合并
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <param name="wb"></param>
    /// <param name="cellStyle"></param>
    /// <param name="orgTitle"></param>
    /// <returns></returns>
    private async Task ExportLineTotal(int nceePlanId, IWorkbook wb, ExportExcelCellStyle cellStyle, string orgTitle)
    {
        var table = await GetLineCountTable(nceePlanId);

        ISheet sheet = wb.CreateSheet("上线合并");
        sheet.DisplayGridlines = false;

        // 行索引号
        int rowNum = 0;

        #region 表头
        IRow titleRow = sheet.CreateRow(rowNum++);
        titleRow.HeightInPoints = 30;
        int ci = 0;
        int scoreWidth = 10;
        _exportExcelService.AddCell("上线合并汇总", titleRow, ci++, cellStyle.TitleStyle, sheet, scoreWidth);
        for (; ci < 16; ci++)
        {
            _exportExcelService.AddCell("", titleRow, ci, cellStyle.TitleStyle, sheet, scoreWidth);
        }
        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));

        IRow headerRow0 = sheet.CreateRow(rowNum++);
        headerRow0.HeightInPoints = 15;
        ci = 0;
        _exportExcelService.AddCell(orgTitle, headerRow0, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("两类合并", headerRow0, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("", headerRow0, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("", headerRow0, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("", headerRow0, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("", headerRow0, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("物理类", headerRow0, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("", headerRow0, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("", headerRow0, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("", headerRow0, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("", headerRow0, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("历史类", headerRow0, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("", headerRow0, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("", headerRow0, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("", headerRow0, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("", headerRow0, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);

        IRow headerRow1 = sheet.CreateRow(rowNum++);
        headerRow1.HeightInPoints = 15;
        ci = 0;
        _exportExcelService.AddCell("", headerRow1, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("参考人数", headerRow1, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("特控", headerRow1, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("", headerRow1, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("本科", headerRow1, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("", headerRow1, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("参考人数", headerRow1, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("特控", headerRow1, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("", headerRow1, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("本科", headerRow1, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("", headerRow1, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("参考人数", headerRow1, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("特控", headerRow1, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("", headerRow1, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("本科", headerRow1, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("", headerRow1, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);

        IRow headerRow2 = sheet.CreateRow(rowNum++);
        headerRow1.HeightInPoints = 15;
        ci = 0;
        _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("人数", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("比例(%)", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("人数", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("比例(%)", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("人数", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("比例(%)", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("人数", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("比例(%)", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("人数", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("比例(%)", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("人数", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
        _exportExcelService.AddCell("比例(%)", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);

        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 3, rowNum - 1, 0, 0));
        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 3, rowNum - 3, 1, 5));
        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 3, rowNum - 3, 6, 10));
        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 3, rowNum - 3, 11, 15));

        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 1, 1, 1));
        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 2, 2, 3));
        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 2, 4, 5));

        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 1, 6, 6));
        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 2, 7, 8));
        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 2, 9, 10));

        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 1, 11, 11));
        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 2, 12, 13));
        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 2, 14, 15));
        #endregion

        #region 数据
        foreach (DataRow dr in table.Rows)
        {
            IRow row = sheet.CreateRow(rowNum++);
            row.HeightInPoints = 15;
            int rci = 0;
            _exportExcelService.AddCell(dr["sys_org_name"], row, rci++, cellStyle.CenterCellStyle);
            _exportExcelService.AddCell(dr["total_count"], row, rci++, cellStyle.CenterCellStyle);
            _exportExcelService.AddCell(dr["line_count_1"], row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
            _exportExcelService.AddCell(dr["line_rate_1"], row, rci++, cellStyle.PercentCellStyleP2, zeroToBlank: true);
            _exportExcelService.AddCell(dr["line_count_2"], row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
            _exportExcelService.AddCell(dr["line_rate_2"], row, rci++, cellStyle.PercentCellStyleP2, zeroToBlank: true);
            _exportExcelService.AddCell(dr["d_4_total_count"], row, rci++, cellStyle.CenterCellStyle);
            _exportExcelService.AddCell(dr["d_4_line_count_1"], row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
            _exportExcelService.AddCell(dr["d_4_line_rate_1"], row, rci++, cellStyle.PercentCellStyleP2, zeroToBlank: true);
            _exportExcelService.AddCell(dr["d_4_line_count_2"], row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
            _exportExcelService.AddCell(dr["d_4_line_rate_2"], row, rci++, cellStyle.PercentCellStyleP2, zeroToBlank: true);
            _exportExcelService.AddCell(dr["d_8_total_count"], row, rci++, cellStyle.CenterCellStyle);
            _exportExcelService.AddCell(dr["d_8_line_count_1"], row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
            _exportExcelService.AddCell(dr["d_8_line_rate_1"], row, rci++, cellStyle.PercentCellStyleP2, zeroToBlank: true);
            _exportExcelService.AddCell(dr["d_8_line_count_2"], row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
            _exportExcelService.AddCell(dr["d_8_line_rate_2"], row, rci++, cellStyle.PercentCellStyleP2, zeroToBlank: true);
        }
        #endregion
    }

    /// <summary>
    /// 导出上线统计
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <param name="courses"></param>
    /// <param name="lineLevelTable"></param>
    /// <param name="orgTitle">区县或学校标题名称</param>
    /// <param name="isExportConvertRange">是否导出转换区间</param>
    /// <returns></returns>
    private async Task<byte[]> ExportLine(int nceePlanId, List<NceeCourseDto> courses, DataTable lineLevelTable, string orgTitle, bool isExportConvertRange = true)
    {
        IWorkbook wb = new XSSFWorkbook();
        var cellStyle = _exportExcelService.GetCellStyle(wb, titleFontSize: 14);

        #region 上线合并
        await ExportLineTotal(nceePlanId, wb, cellStyle, orgTitle);
        #endregion

        #region 平均分
        {
            var tcourses = courses.Where(t => t.Id != 101).ToList();

            ISheet sheet = wb.CreateSheet("平均分");
            sheet.DisplayGridlines = false;

            // 行索引号
            int rowNum = 0;

            foreach (var directionCourse in NceeUtil.DirectionCourses)
            {
                var table = await GetAvgScoreTable(nceePlanId, directionCourse.Id);

                #region 表头
                IRow headerRow1 = sheet.CreateRow(rowNum++);
                headerRow1.HeightInPoints = 30;
                int ci = 0;
                int scoreWidth = 8;
                _exportExcelService.AddCell($"{directionCourse.Name}类平均分", headerRow1, ci++, cellStyle.TitleStyle, sheet, 10);
                for (; ci < 19; ci++)
                {
                    _exportExcelService.AddCell("", headerRow1, ci, cellStyle.TitleStyle, sheet, scoreWidth);
                }
                sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));

                IRow headerRow2 = sheet.CreateRow(rowNum++);
                headerRow2.HeightInPoints = 15;
                ci = 0;
                _exportExcelService.AddCell("学科", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
                foreach (var course in tcourses)
                {
                    if (course.IsSkip(directionCourse.Id)) { continue; }
                    string cn = course.Name;
                    if (course.Id == 5 || course.Id == 6 || course.Id == 7 || course.Id == 9 || course.Id == 100)
                    {
                        cn = $"{cn}X";
                    }
                    _exportExcelService.AddCell(cn, headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    if (ci % 2 == 1)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 2, ci - 1));
                    }
                }


                IRow headerRow3 = sheet.CreateRow(rowNum++);
                headerRow3.HeightInPoints = 15;
                ci = 0;
                _exportExcelService.AddCell(orgTitle, headerRow3, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
                foreach (var course in tcourses)
                {
                    if (course.IsSkip(directionCourse.Id)) { continue; }
                    _exportExcelService.AddCell("人数", headerRow3, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    _exportExcelService.AddCell("平均", headerRow3, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                }
                #endregion

                #region 数据
                foreach (DataRow dr in table.Rows)
                {
                    IRow row = sheet.CreateRow(rowNum++);
                    row.HeightInPoints = 15;
                    int rci = 0;
                    _exportExcelService.AddCell(dr["sys_org_short_name"], row, rci++, cellStyle.CenterCellStyle);
                    foreach (var course in tcourses)
                    {
                        if (course.IsSkip(directionCourse.Id)) { continue; }
                        _exportExcelService.AddCell(dr[$"total_count_{course.Id}"], row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
                        _exportExcelService.AddCell(dr[$"avg_score_x_{course.Id}"], row, rci++, cellStyle.NumberCellStyleP2, zeroToBlank: true);
                    }
                }
                #endregion

                rowNum += 2;
            }
        }
        #endregion

        #region 有效分
        var lineScoreTable = await GetLineScoreTable(nceePlanId);
        await ExportLineScore(nceePlanId, wb, cellStyle, lineScoreTable);
        #endregion

        #region 单上线
        {
            var table = await GetLineTotalTable(nceePlanId);

            ISheet sheet = wb.CreateSheet("有效分总分单上线");
            sheet.DisplayGridlines = false;
            // 行索引号
            int rowNum = 0;

            foreach (DataRow lineLevel in lineLevelTable.Rows)
            {
                #region 表头
                // 分类标题
                IRow headerRow1 = sheet.CreateRow(rowNum++);
                headerRow1.HeightInPoints = 30;
                int ci = 0;
                int countWidth = 6;
                int scoreWidth = 8;
                int orgWidth = 10;
                _exportExcelService.AddCell($"{lineLevel["ncee_line_level_name"]}有效分总分单上线", headerRow1, ci++, cellStyle.TitleStyle, sheet, orgWidth);
                for (; ci < 30; ci++)
                {
                    _exportExcelService.AddCell("", headerRow1, ci, cellStyle.TitleStyle, sheet, scoreWidth);
                }
                sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));

                // 方向标题
                IRow headerRow2 = sheet.CreateRow(rowNum++);
                headerRow2.HeightInPoints = 15;
                ci = 0;
                _exportExcelService.AddCell(orgTitle, headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                foreach (var directionCourse in NceeUtil.DirectionCourses)
                {
                    _exportExcelService.AddCell($"{directionCourse.Name}类", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    for (int i = 0; i < 12; i++)
                    {
                        _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    }
                    sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 13, ci - 1));
                }
                _exportExcelService.AddCell("合计", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);

                // 学科上有效分标题
                IRow headerRow3 = sheet.CreateRow(rowNum++);
                headerRow3.HeightInPoints = 15;
                ci = 0;
                _exportExcelService.AddCell("", headerRow3, ci++, cellStyle.ColumnFillHeaderStyle, sheet, orgWidth);
                foreach (var directionCourse in NceeUtil.DirectionCourses)
                {
                    _exportExcelService.AddCell("学科上有效分人数及比例", headerRow3, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    for (int i = 0; i < 9; i++)
                    {
                        _exportExcelService.AddCell("", headerRow3, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    }
                    sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 10, ci - 1));

                    _exportExcelService.AddCell("总分上\n线情况", headerRow3, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    _exportExcelService.AddCell("", headerRow3, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    _exportExcelService.AddCell("上线率", headerRow3, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                }
                _exportExcelService.AddCell("", headerRow3, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                _exportExcelService.AddCell("", headerRow3, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                _exportExcelService.AddCell("", headerRow3, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);

                // 科目标题
                IRow headerRow4 = sheet.CreateRow(rowNum++);
                headerRow4.HeightInPoints = 15;
                ci = 0;
                _exportExcelService.AddCell("", headerRow4, ci++, cellStyle.ColumnFillHeaderStyle, sheet, orgWidth);
                foreach (var directionCourse in NceeUtil.DirectionCourses)
                {
                    foreach (var cu in courses.Where(t => t.Id < 4 || t.Id == directionCourse.Id || t.Id == 101))
                    {
                        _exportExcelService.AddCell(cu.Name, headerRow4, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                        _exportExcelService.AddCell("", headerRow4, ci++, cellStyle.TitleStyle, sheet, scoreWidth);
                        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 2, ci - 1));
                    }
                    _exportExcelService.AddCell("", headerRow4, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    _exportExcelService.AddCell("", headerRow4, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 1, ci - 2, ci - 1));

                    _exportExcelService.AddCell("", headerRow4, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                }
                _exportExcelService.AddCell("", headerRow4, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                _exportExcelService.AddCell("", headerRow4, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                _exportExcelService.AddCell("", headerRow4, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                sheet.AddMergedRegion(new CellRangeAddress(rowNum - 3, rowNum - 1, 0, 0));

                // 有效分标题
                IRow headerRow5 = sheet.CreateRow(rowNum++);
                headerRow5.HeightInPoints = 15;
                ci = 0;
                _exportExcelService.AddCell("有效分", headerRow5, ci++, cellStyle.ColumnFillHeaderStyle, sheet, orgWidth);
                foreach (var directionCourse in NceeUtil.DirectionCourses)
                {
                    var lineScore = lineScoreTable.Select($"direction_course_id = {directionCourse.Id} AND ncee_line_level = {lineLevel["ncee_line_level"]}").First();
                    foreach (var cu in courses.Where(t => t.Id < 4 || t.Id == directionCourse.Id || t.Id == 100 || t.Id == 101))
                    {
                        _exportExcelService.AddCell(lineScore[$"line_score_x_{cu.Id}"], headerRow5, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                        _exportExcelService.AddCell("", headerRow5, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 2, ci - 1));
                    }
                    _exportExcelService.AddCell("", headerRow5, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                }
                _exportExcelService.AddCell("", headerRow5, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                _exportExcelService.AddCell("", headerRow5, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                _exportExcelService.AddCell("", headerRow5, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                sheet.AddMergedRegion(new CellRangeAddress(rowNum - 4, rowNum - 1, ci - 3, ci - 1));

                // 人数比例标题
                IRow headerRow6 = sheet.CreateRow(rowNum++);
                headerRow6.HeightInPoints = 15;
                ci = 0;
                _exportExcelService.AddCell("", headerRow6, ci++, cellStyle.ColumnFillHeaderStyle, sheet, orgWidth);
                foreach (var directionCourse in NceeUtil.DirectionCourses)
                {
                    foreach (var cu in courses.Where(t => t.Id < 4 || t.Id == directionCourse.Id || t.Id == 101))
                    {
                        _exportExcelService.AddCell("人数", headerRow6, ci++, cellStyle.ColumnFillHeaderStyle, sheet, countWidth);
                        _exportExcelService.AddCell("比例", headerRow6, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    }
                    _exportExcelService.AddCell("参考", headerRow6, ci++, cellStyle.ColumnFillHeaderStyle, sheet, countWidth);
                    _exportExcelService.AddCell("上线", headerRow6, ci++, cellStyle.ColumnFillHeaderStyle, sheet, countWidth);
                    _exportExcelService.AddCell("", headerRow6, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    sheet.AddMergedRegion(new CellRangeAddress(rowNum - 4, rowNum - 1, ci - 1, ci - 1));
                }
                _exportExcelService.AddCell("参考", headerRow6, ci++, cellStyle.ColumnFillHeaderStyle, sheet, countWidth);
                _exportExcelService.AddCell("上线", headerRow6, ci++, cellStyle.ColumnFillHeaderStyle, sheet, countWidth);
                _exportExcelService.AddCell("比例", headerRow6, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);

                #endregion

                #region 数据
                foreach (DataRow dr in table.Select($"ncee_line_level = {lineLevel["ncee_line_level"]} AND direction_course_id = 4"))
                {
                    IRow row = sheet.CreateRow(rowNum++);
                    row.HeightInPoints = 15;
                    int rci = 0;
                    _exportExcelService.AddCell(dr["sys_org_short_name"], row, rci++, cellStyle.CenterCellStyle);
                    foreach (var cu in courses.Where(t => t.Id < 5 || t.Id == 101))
                    {
                        _exportExcelService.AddCell(dr[$"line_count_{cu.Id}"], row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
                        _exportExcelService.AddCell(dr[$"line_rate_{cu.Id}"], row, rci++, cellStyle.NumberCellStyleP4, zeroToBlank: true);
                    }
                    var tc_4_100 = (int)dr["total_count_100"];
                    var lc_4_100 = (int)dr["line_count_100"];
                    _exportExcelService.AddCell(tc_4_100, row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
                    _exportExcelService.AddCell(lc_4_100, row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
                    _exportExcelService.AddCell(dr["line_rate_100"], row, rci++, cellStyle.NumberCellStyleP4, zeroToBlank: true);
                    var dr8 = table.Select($"ncee_line_level = {lineLevel["ncee_line_level"]} AND direction_course_id = 8 AND sys_org_id = {dr["sys_org_id"]}").FirstOrDefault();

                    int tc_8_100 = 0;
                    int lc_8_100 = 0;
                    if (dr8 != null)
                    {
                        foreach (var cu in courses.Where(t => t.Id < 4 || t.Id == 8 || t.Id == 101))
                        {
                            _exportExcelService.AddCell(dr8[$"line_count_{cu.Id}"], row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
                            _exportExcelService.AddCell(dr8[$"line_rate_{cu.Id}"], row, rci++, cellStyle.NumberCellStyleP4, zeroToBlank: true);
                        }
                        tc_8_100 = (int)dr8["total_count_100"];
                        lc_8_100 = (int)dr8["line_count_100"];
                        _exportExcelService.AddCell(tc_8_100, row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
                        _exportExcelService.AddCell(lc_8_100, row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
                        _exportExcelService.AddCell(dr8["line_rate_100"], row, rci++, cellStyle.NumberCellStyleP4, zeroToBlank: true);
                    }
                    _exportExcelService.AddCell(tc_4_100 + tc_8_100, row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
                    _exportExcelService.AddCell(lc_4_100 + lc_8_100, row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
                    _exportExcelService.AddCell((lc_4_100 + lc_8_100) * 1.0 / (tc_4_100 + tc_8_100), row, rci++, cellStyle.NumberCellStyleP4, zeroToBlank: true);
                }
                #endregion

                rowNum += 2;
            }

            sheet.CreateFreezePane(1, 0);
        }
        #endregion

        #region 双上线
        {
            var table = await GetLineCourseTable(nceePlanId);

            ISheet sheet = wb.CreateSheet("有效分总分双上线");
            sheet.DisplayGridlines = false;
            // 行索引号
            int rowNum = 0;

            foreach (DataRow lineLevel in lineLevelTable.Rows)
            {
                #region 表头
                // 分类标题
                IRow headerRow1 = sheet.CreateRow(rowNum++);
                headerRow1.HeightInPoints = 30;
                int ci = 0;
                int countWidth = 6;
                int scoreWidth = 8;
                int orgWidth = 10;
                _exportExcelService.AddCell($"{lineLevel["ncee_line_level_name"]}有效分总分双上线", headerRow1, ci++, cellStyle.TitleStyle, sheet, orgWidth);
                for (; ci < 21; ci++)
                {
                    _exportExcelService.AddCell("", headerRow1, ci, cellStyle.TitleStyle, sheet, scoreWidth);
                }
                sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));

                // 方向标题
                IRow headerRow2 = sheet.CreateRow(rowNum++);
                headerRow2.HeightInPoints = 15;
                ci = 0;
                _exportExcelService.AddCell(orgTitle, headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                foreach (var directionCourse in NceeUtil.DirectionCourses)
                {
                    _exportExcelService.AddCell($"{directionCourse.Name}类", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    for (int i = 0; i < 9; i++)
                    {
                        _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    }
                    sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 10, ci - 1));
                }

                // 科目标题
                IRow headerRow4 = sheet.CreateRow(rowNum++);
                headerRow4.HeightInPoints = 15;
                ci = 0;
                _exportExcelService.AddCell("", headerRow4, ci++, cellStyle.ColumnFillHeaderStyle, sheet, orgWidth);
                foreach (var directionCourse in NceeUtil.DirectionCourses)
                {
                    foreach (var cu in courses.Where(t => t.Id < 4 || t.Id == directionCourse.Id || t.Id == 101))
                    {
                        var cuName = cu.Name;
                        if (cu.Id == 101)
                        {
                            cuName = $"{cuName}X";
                        }
                        _exportExcelService.AddCell(cuName, headerRow4, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                        _exportExcelService.AddCell("", headerRow4, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 2, ci - 1));
                    }
                }
                sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 1, 0, 0));

                // 有效分标题
                IRow headerRow5 = sheet.CreateRow(rowNum++);
                headerRow5.HeightInPoints = 15;
                ci = 0;
                _exportExcelService.AddCell("有效分", headerRow5, ci++, cellStyle.ColumnFillHeaderStyle, sheet, orgWidth);
                foreach (var directionCourse in NceeUtil.DirectionCourses)
                {
                    var lineScore = lineScoreTable.Select($"direction_course_id = {directionCourse.Id} AND ncee_line_level = {lineLevel["ncee_line_level"]}").First();
                    foreach (var cu in courses.Where(t => t.Id < 4 || t.Id == directionCourse.Id || t.Id == 101))
                    {
                        _exportExcelService.AddCell(lineScore[$"line_score_x_{cu.Id}"], headerRow5, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                        _exportExcelService.AddCell("", headerRow5, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 2, ci - 1));
                    }
                }

                // 人数比例标题
                IRow headerRow6 = sheet.CreateRow(rowNum++);
                headerRow6.HeightInPoints = 15;
                ci = 0;
                _exportExcelService.AddCell("", headerRow6, ci++, cellStyle.ColumnFillHeaderStyle, sheet, orgWidth);
                foreach (var directionCourse in NceeUtil.DirectionCourses)
                {
                    foreach (var cu in courses.Where(t => t.Id < 4 || t.Id == directionCourse.Id || t.Id == 101))
                    {
                        _exportExcelService.AddCell("人数", headerRow6, ci++, cellStyle.ColumnFillHeaderStyle, sheet, countWidth);
                        _exportExcelService.AddCell("比例", headerRow6, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    }
                }

                #endregion

                #region 数据
                foreach (DataRow dr in table.Select($"ncee_line_level = {lineLevel["ncee_line_level"]} AND direction_course_id = 4"))
                {
                    IRow row = sheet.CreateRow(rowNum++);
                    row.HeightInPoints = 15;
                    int rci = 0;
                    _exportExcelService.AddCell(dr["sys_org_short_name"], row, rci++, cellStyle.CenterCellStyle);
                    foreach (var cu in courses.Where(t => t.Id < 5 || t.Id == 101))
                    {
                        _exportExcelService.AddCell(dr[$"line_count_{cu.Id}"], row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
                        _exportExcelService.AddCell(dr[$"line_rate_{cu.Id}"], row, rci++, cellStyle.NumberCellStyleP4, zeroToBlank: true);
                    }

                    var dr8 = table.Select($"ncee_line_level = {lineLevel["ncee_line_level"]} AND direction_course_id = 8 AND sys_org_id = {dr["sys_org_id"]}").FirstOrDefault();
                    if (dr8 != null)
                    {
                        foreach (var cu in courses.Where(t => t.Id < 4 || t.Id == 8 || t.Id == 101))
                        {
                            _exportExcelService.AddCell(dr8[$"line_count_{cu.Id}"], row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
                            _exportExcelService.AddCell(dr8[$"line_rate_{cu.Id}"], row, rci++, cellStyle.NumberCellStyleP4, zeroToBlank: true);
                        }
                    }
                }
                #endregion

                rowNum += 2;
            }

            sheet.CreateFreezePane(1, 0);
        }
        #endregion

        #region 转换区间
        if (isExportConvertRange)
        {
            await ExportConvertRange(nceePlanId, wb, cellStyle);
        }
        #endregion

        MemoryStream ms = new();
        wb.Write(ms, false);
        ms.Flush();

        return ms.ToArray();
    }
    /// <summary>
    /// 导出上线统计（未选科）
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <param name="courses"></param>
    /// <param name="lineLevelTable"></param>
    /// <returns></returns>
    private async Task<byte[]> ExportLineUnselected(int nceePlanId, List<NceeCourseDto> courses, DataTable lineLevelTable)
    {
        IWorkbook wb = new XSSFWorkbook();
        var cellStyle = _exportExcelService.GetCellStyle(wb, titleFontSize: 14);

        #region 上线合并
        await ExportLineTotal(nceePlanId, wb, cellStyle, "学校");
        #endregion

        #region 平均分
        {
            ISheet sheet = wb.CreateSheet("平均分");
            sheet.DisplayGridlines = false;

            // 行索引号
            int rowNum = 0;

            var table = await GetAvgScoreTable(nceePlanId, 99);

            #region 表头
            IRow headerRow1 = sheet.CreateRow(rowNum++);
            headerRow1.HeightInPoints = 30;
            int ci = 0;
            int scoreWidth = 8;
            _exportExcelService.AddCell($"平均分", headerRow1, ci++, cellStyle.TitleStyle, sheet, 10);
            for (; ci < courses.Count * 2 + 1; ci++)
            {
                _exportExcelService.AddCell("", headerRow1, ci, cellStyle.TitleStyle, sheet, scoreWidth);
            }
            sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));

            IRow headerRow2 = sheet.CreateRow(rowNum++);
            headerRow2.HeightInPoints = 15;
            ci = 0;
            _exportExcelService.AddCell("学科", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
            foreach (var course in courses)
            {
                string cn = course.Name;
                _exportExcelService.AddCell(cn, headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                if (ci % 2 == 1)
                {
                    sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 2, ci - 1));
                }
            }


            IRow headerRow3 = sheet.CreateRow(rowNum++);
            headerRow3.HeightInPoints = 15;
            ci = 0;
            _exportExcelService.AddCell("学校", headerRow3, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
            foreach (var course in courses)
            {
                _exportExcelService.AddCell("人数", headerRow3, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                _exportExcelService.AddCell("平均", headerRow3, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            }
            #endregion

            #region 数据
            foreach (DataRow dr in table.Rows)
            {
                IRow row = sheet.CreateRow(rowNum++);
                row.HeightInPoints = 15;
                int rci = 0;
                _exportExcelService.AddCell(dr["sys_org_short_name"], row, rci++, cellStyle.CenterCellStyle);
                foreach (var course in courses)
                {
                    _exportExcelService.AddCell(dr[$"total_count_{course.Id}"], row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
                    _exportExcelService.AddCell(dr[$"avg_score_x_{course.Id}"], row, rci++, cellStyle.NumberCellStyleP2, zeroToBlank: true);
                }
            }
            #endregion
        }
        #endregion

        #region 有效分
        var lineScoreTable = await GetLineScoreTable(nceePlanId);
        ExportLineScore(wb, cellStyle, lineScoreTable, courses);
        #endregion

        #region 单上线
        {
            var table = await GetLineTotalTable(nceePlanId);

            ISheet sheet = wb.CreateSheet("有效分总分单上线");
            sheet.DisplayGridlines = false;
            // 行索引号
            int rowNum = 0;

            foreach (DataRow lineLevel in lineLevelTable.Rows)
            {
                #region 表头
                // 分类标题
                IRow headerRow1 = sheet.CreateRow(rowNum++);
                headerRow1.HeightInPoints = 30;
                int ci = 0;
                int countWidth = 6;
                int scoreWidth = 8;
                int orgWidth = 10;
                _exportExcelService.AddCell($"{lineLevel["ncee_line_level_name"]}有效分总分单上线", headerRow1, ci++, cellStyle.TitleStyle, sheet, orgWidth);
                for (; ci < courses.Count * 2 + 1; ci++)
                {
                    _exportExcelService.AddCell("", headerRow1, ci, cellStyle.TitleStyle, sheet, scoreWidth);
                }
                sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));

                // 科目标题
                IRow headerRow4 = sheet.CreateRow(rowNum++);
                headerRow4.HeightInPoints = 15;
                ci = 0;
                _exportExcelService.AddCell("科目", headerRow4, ci++, cellStyle.ColumnFillHeaderStyle, sheet, orgWidth);
                foreach (var cu in courses)
                {
                    _exportExcelService.AddCell(cu.Name, headerRow4, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    _exportExcelService.AddCell("", headerRow4, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 2, ci - 1));
                }

                // 有效分标题
                IRow headerRow5 = sheet.CreateRow(rowNum++);
                headerRow5.HeightInPoints = 15;
                ci = 0;
                _exportExcelService.AddCell("有效分", headerRow5, ci++, cellStyle.ColumnFillHeaderStyle, sheet, orgWidth);
                var lineScore = lineScoreTable.Select($"direction_course_id = 99 AND ncee_line_level = {lineLevel["ncee_line_level"]}").First();
                foreach (var cu in courses)
                {
                    _exportExcelService.AddCell(lineScore[$"line_score_x_{cu.Id}"], headerRow5, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    _exportExcelService.AddCell("", headerRow5, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 2, ci - 1));
                }

                // 人数比例标题
                IRow headerRow6 = sheet.CreateRow(rowNum++);
                headerRow6.HeightInPoints = 15;
                ci = 0;
                _exportExcelService.AddCell("学校", headerRow6, ci++, cellStyle.ColumnFillHeaderStyle, sheet, orgWidth);
                foreach (var cu in courses)
                {
                    _exportExcelService.AddCell("人数", headerRow6, ci++, cellStyle.ColumnFillHeaderStyle, sheet, countWidth);
                    _exportExcelService.AddCell("比例", headerRow6, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                }

                #endregion

                #region 数据
                foreach (DataRow dr in table.Select($"ncee_line_level = {lineLevel["ncee_line_level"]} AND direction_course_id = 99"))
                {
                    IRow row = sheet.CreateRow(rowNum++);
                    row.HeightInPoints = 15;
                    int rci = 0;
                    _exportExcelService.AddCell(dr["sys_org_short_name"], row, rci++, cellStyle.CenterCellStyle);
                    foreach (var cu in courses)
                    {
                        _exportExcelService.AddCell(dr[$"line_count_{cu.Id}"], row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
                        _exportExcelService.AddCell(dr[$"line_rate_{cu.Id}"], row, rci++, cellStyle.NumberCellStyleP4, zeroToBlank: true);
                    }
                }
                #endregion
            }

            sheet.CreateFreezePane(1, 0);
        }
        #endregion

        #region 双上线
        {
            var tcourse = courses.Where(t => t.Id < 100).ToList();

            var table = await GetLineCourseTable(nceePlanId);

            ISheet sheet = wb.CreateSheet("有效分总分双上线");
            sheet.DisplayGridlines = false;
            // 行索引号
            int rowNum = 0;

            foreach (DataRow lineLevel in lineLevelTable.Rows)
            {
                #region 表头
                // 分类标题
                IRow headerRow1 = sheet.CreateRow(rowNum++);
                headerRow1.HeightInPoints = 30;
                int ci = 0;
                int countWidth = 6;
                int scoreWidth = 8;
                int orgWidth = 10;
                _exportExcelService.AddCell($"{lineLevel["ncee_line_level_name"]}有效分总分双上线", headerRow1, ci++, cellStyle.TitleStyle, sheet, orgWidth);
                for (; ci < tcourse.Count * 2 + 1; ci++)
                {
                    _exportExcelService.AddCell("", headerRow1, ci, cellStyle.TitleStyle, sheet, scoreWidth);
                }
                sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));

                // 科目标题
                IRow headerRow4 = sheet.CreateRow(rowNum++);
                headerRow4.HeightInPoints = 15;
                ci = 0;
                _exportExcelService.AddCell("科目", headerRow4, ci++, cellStyle.ColumnFillHeaderStyle, sheet, orgWidth);
                foreach (var cu in tcourse)
                {
                    _exportExcelService.AddCell(cu.Name, headerRow4, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    _exportExcelService.AddCell("", headerRow4, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 2, ci - 1));
                }

                // 有效分标题
                IRow headerRow5 = sheet.CreateRow(rowNum++);
                headerRow5.HeightInPoints = 15;
                ci = 0;
                _exportExcelService.AddCell("有效分", headerRow5, ci++, cellStyle.ColumnFillHeaderStyle, sheet, orgWidth);
                var lineScore = lineScoreTable.Select($"direction_course_id = 99 AND ncee_line_level = {lineLevel["ncee_line_level"]}").First();
                foreach (var cu in tcourse)
                {
                    _exportExcelService.AddCell(lineScore[$"line_score_x_{cu.Id}"], headerRow5, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    _exportExcelService.AddCell("", headerRow5, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 2, ci - 1));
                }

                // 人数比例标题
                IRow headerRow6 = sheet.CreateRow(rowNum++);
                headerRow6.HeightInPoints = 15;
                ci = 0;
                _exportExcelService.AddCell("学校", headerRow6, ci++, cellStyle.ColumnFillHeaderStyle, sheet, orgWidth);
                foreach (var cu in tcourse)
                {
                    _exportExcelService.AddCell("人数", headerRow6, ci++, cellStyle.ColumnFillHeaderStyle, sheet, countWidth);
                    _exportExcelService.AddCell("比例", headerRow6, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                }

                #endregion

                #region 数据
                foreach (DataRow dr in table.Select($"ncee_line_level = {lineLevel["ncee_line_level"]} AND direction_course_id = 99"))
                {
                    IRow row = sheet.CreateRow(rowNum++);
                    row.HeightInPoints = 15;
                    int rci = 0;
                    _exportExcelService.AddCell(dr["sys_org_short_name"], row, rci++, cellStyle.CenterCellStyle);
                    foreach (var cu in tcourse)
                    {
                        _exportExcelService.AddCell(dr[$"line_count_{cu.Id}"], row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
                        _exportExcelService.AddCell(dr[$"line_rate_{cu.Id}"], row, rci++, cellStyle.NumberCellStyleP4, zeroToBlank: true);
                    }
                }
                #endregion
            }

            sheet.CreateFreezePane(1, 0);
        }
        #endregion

        MemoryStream ms = new();
        wb.Write(ms, false);
        ms.Flush();

        return ms.ToArray();
    }
    /// <summary>
    /// 导出转换成绩
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <param name="orgTitle"></param>
    /// <param name="org"></param>
    /// <param name="isExportConvertRange">导出转换区间</param>
    /// <param name="isExportOrder">导出排名</param>
    /// <returns></returns>
    private async Task<byte[]> ExportConvertScore(int nceePlanId, string orgTitle, SysOrgOutput org = null, bool isExportConvertRange = false, bool isExportOrder = false)
    {
        IWorkbook wb = new XSSFWorkbook();
        var cellStyle = _exportExcelService.GetCellStyle(wb, titleFontSize: 14);
        foreach (var dc in NceeUtil.DirectionCourses)
        {
            var table = await GetConvertScoreTable(nceePlanId, dc.Id, org?.Id ?? 0);

            ISheet sheet = wb.CreateSheet($"{dc.Name}类");
            sheet.DisplayGridlines = false;
            // 行索引号
            int rowNum = 0;

            #region 表头
            IRow headerRow = sheet.CreateRow(rowNum++);
            int ci = 0;
            int scoreWidth = 8;
            _exportExcelService.AddCell(orgTitle, headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 13);
            _exportExcelService.AddCell("序号", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 12);
            _exportExcelService.AddCell("总分", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            _exportExcelService.AddCell("总分X", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            if (isExportOrder)
            {
                _exportExcelService.AddCell("联考序X", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                _exportExcelService.AddCell("区序X", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            }
            _exportExcelService.AddCell("语文", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            _exportExcelService.AddCell("数学", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            _exportExcelService.AddCell("英语", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            _exportExcelService.AddCell(dc.Name, headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            _exportExcelService.AddCell("综合", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            _exportExcelService.AddCell("综合X", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            _exportExcelService.AddCell("等级", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth + 2);
            _exportExcelService.AddCell("化学", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            _exportExcelService.AddCell("化学X", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            _exportExcelService.AddCell("等级", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            _exportExcelService.AddCell("生物", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            _exportExcelService.AddCell("生物X", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            _exportExcelService.AddCell("等级", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            _exportExcelService.AddCell("政治", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            _exportExcelService.AddCell("政治X", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            _exportExcelService.AddCell("等级", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            _exportExcelService.AddCell("地理", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            _exportExcelService.AddCell("地理X", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            _exportExcelService.AddCell("等级", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            sheet.CreateFreezePane(0, rowNum);
            #endregion

            #region 数据
            foreach (DataRow dr in table.Rows)
            {
                List<string> cgns = new()
                    {
                        dr["ncee_convert_grade_name_5"].ToString(),
                        dr["ncee_convert_grade_name_6"].ToString(),
                        dr["ncee_convert_grade_name_7"].ToString(),
                        dr["ncee_convert_grade_name_9"].ToString()
                    };
                //cgns = cgns.Where(t => t != "").ToList();
                //string cg = string.Join("", cgns);
                //if (cg != "")
                //{
                //    cg = $"[{cg}]";
                //}
                string cg = GetMergeGradeName(cgns);

                IRow row = sheet.CreateRow(rowNum++);
                int rci = 0;
                _exportExcelService.AddCell(dr["sys_org_short_name"], row, rci++, cellStyle.CenterCellStyle);
                _exportExcelService.AddCell(dr["exam_number"], row, rci++, cellStyle.CenterCellStyle);
                _exportExcelService.AddCell(dr["score"], row, rci++, cellStyle.CenterCellStyle);
                _exportExcelService.AddCell(dr["score_x"], row, rci++, cellStyle.CenterCellStyle);
                if (isExportOrder)
                {
                    _exportExcelService.AddCell(dr["order_in_total_x"], row, rci++, cellStyle.CenterCellStyle);
                    _exportExcelService.AddCell(dr["order_in_org_x"], row, rci++, cellStyle.CenterCellStyle);
                }
                _exportExcelService.AddCell(dr["score_1"], row, rci++, cellStyle.CenterCellStyle);
                _exportExcelService.AddCell(dr["score_2"], row, rci++, cellStyle.CenterCellStyle);
                _exportExcelService.AddCell(dr["score_3"], row, rci++, cellStyle.CenterCellStyle);
                _exportExcelService.AddCell(dr[$"score_{dc.Id}"], row, rci++, cellStyle.CenterCellStyle);
                _exportExcelService.AddCell(dr["comb_score"], row, rci++, cellStyle.CenterCellStyle);
                _exportExcelService.AddCell(dr["comb_score_x"], row, rci++, cellStyle.CenterCellStyle);
                _exportExcelService.AddCell(cg, row, rci++, cellStyle.CenterCellStyle);
                _exportExcelService.AddCell(dr["score_5"], row, rci++, cellStyle.CenterCellStyle);
                _exportExcelService.AddCell(dr["score_x_5"], row, rci++, cellStyle.CenterCellStyle);
                _exportExcelService.AddCell(dr["ncee_convert_grade_name_5"], row, rci++, cellStyle.CenterCellStyle);
                _exportExcelService.AddCell(dr["score_6"], row, rci++, cellStyle.CenterCellStyle);
                _exportExcelService.AddCell(dr["score_x_6"], row, rci++, cellStyle.CenterCellStyle);
                _exportExcelService.AddCell(dr["ncee_convert_grade_name_6"], row, rci++, cellStyle.CenterCellStyle);
                _exportExcelService.AddCell(dr["score_7"], row, rci++, cellStyle.CenterCellStyle);
                _exportExcelService.AddCell(dr["score_x_7"], row, rci++, cellStyle.CenterCellStyle);
                _exportExcelService.AddCell(dr["ncee_convert_grade_name_7"], row, rci++, cellStyle.CenterCellStyle);
                _exportExcelService.AddCell(dr["score_9"], row, rci++, cellStyle.CenterCellStyle);
                _exportExcelService.AddCell(dr["score_x_9"], row, rci++, cellStyle.CenterCellStyle);
                _exportExcelService.AddCell(dr["ncee_convert_grade_name_9"], row, rci++, cellStyle.CenterCellStyle);
            }
            #endregion
        }

        // 导出转换区间
        if (isExportConvertRange)
        {
            await ExportConvertRange(nceePlanId, wb, cellStyle);
        }

        MemoryStream ms = new();
        wb.Write(ms, false);
        ms.Flush();

        return ms.ToArray();
    }
    /// <summary>
    /// 导出汇总班级上线情况
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <param name="baseLineTable"></param>
    /// <returns></returns>
    private async Task<byte[]> ExportClassLine(int nceePlanId, DataTable baseLineTable)
    {
        IWorkbook wb = new XSSFWorkbook();
        var cellStyle = _exportExcelService.GetCellStyle(wb, titleFontSize: 14);

        var cellStyleIndent1 = wb.CreateCellStyle();
        cellStyleIndent1.CloneStyleFrom(cellStyle.LeftCellStyle);
        cellStyleIndent1.Indention = 1;

        var cellStyleIndent2 = wb.CreateCellStyle();
        cellStyleIndent2.CloneStyleFrom(cellStyle.LeftCellStyle);
        cellStyleIndent2.Indention = 2;

        #region 总分上线
        {
            var table = await GetOrgLineTotalTable2(nceePlanId, 0);
            ISheet sheet = wb.CreateSheet("总分上线");
            sheet.DisplayGridlines = false;
            // 行索引号
            int rowNum = 0;
            foreach (var directionCourse in NceeUtil.DirectionCourses)
            {
                var nls = baseLineTable.Select($"direction_course_id = {directionCourse.Id}");

                #region 标题
                IRow titleRow = sheet.CreateRow(rowNum++);
                titleRow.HeightInPoints = 30;
                int ci = 0;
                int scoreWidth = 10;
                _exportExcelService.AddCell($"{directionCourse.Name}类总分上线情况", titleRow, ci++, cellStyle.TitleStyle, sheet, 13);
                _exportExcelService.AddCell("", titleRow, ci++, cellStyle.TitleStyle, sheet, 10);
                foreach (var nl in nls)
                {
                    _exportExcelService.AddCell("", titleRow, ci++, cellStyle.TitleStyle, sheet, scoreWidth);
                    _exportExcelService.AddCell("", titleRow, ci++, cellStyle.TitleStyle, sheet, scoreWidth);
                }
                sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));
                #endregion

                #region 表头
                IRow headerRow = sheet.CreateRow(rowNum++);
                ci = 0;
                _exportExcelService.AddCell("名称", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 13);
                _exportExcelService.AddCell("实考人数", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
                foreach (var nl in nls)
                {
                    _exportExcelService.AddCell($"{nl["ncee_line_level_name"]}({nl["line_score_x"]})", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    _exportExcelService.AddCell("", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 2, ci - 1));
                }

                IRow headerRow2 = sheet.CreateRow(rowNum++);
                ci = 0;
                _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 13);
                _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
                foreach (var nl in nls)
                {
                    _exportExcelService.AddCell("人数", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    _exportExcelService.AddCell("比例(%)", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                }
                sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 1, 0, 0));
                sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 1, 1, 1));
                #endregion

                foreach (DataRow dr in table.Select($"direction_course_id = {directionCourse.Id}"))
                {
                    IRow row = sheet.CreateRow(rowNum++);
                    int rci = 0;

                    short type = (short)dr["type"];
                    string itemName = "";
                    switch (type)
                    {
                        case 1:
                            itemName = $"{dr["sys_org_name"]}";
                            break;
                        case 2:
                            itemName = $"{dr["ncee_course_comb_name"]}";
                            break;
                        case 3:
                            itemName = $"{dr["class_number"]}班";
                            break;
                    }

                    _exportExcelService.AddCell(itemName, row, rci++, type == 1 ? cellStyle.LeftCellStyle : type == 2 ? cellStyleIndent1 : cellStyleIndent2);
                    _exportExcelService.AddCell(dr["total_count_1"], row, rci++, cellStyle.CenterCellStyle);
                    foreach (var nl in nls)
                    {
                        _exportExcelService.AddCell(dr[$"line_count_{nl["ncee_line_level"]}"], row, rci++, cellStyle.CenterCellStyle);
                        _exportExcelService.AddCell(dr[$"line_rate_{nl["ncee_line_level"]}"], row, rci++, cellStyle.PercentCellStyleP2);
                    }
                }

                rowNum += 1;
            }
        }
        #endregion

        #region 双上线
        {
            var table = await GetOrgLineCourseTable2(nceePlanId, 0);
            ISheet sheet = wb.CreateSheet("双上线");
            sheet.DisplayGridlines = false;
            // 行索引号
            int rowNum = 0;
            foreach (var directionCourse in NceeUtil.DirectionCourses)
            {
                var nls = baseLineTable.Select($"direction_course_id = {directionCourse.Id}");

                #region 标题
                IRow titleRow = sheet.CreateRow(rowNum++);
                titleRow.HeightInPoints = 30;
                int ci = 0;
                int scoreWidth = 10;
                _exportExcelService.AddCell($"{directionCourse.Name}类双上线情况", titleRow, ci++, cellStyle.TitleStyle, sheet, 13);
                _exportExcelService.AddCell("", titleRow, ci++, cellStyle.TitleStyle, sheet, 10);
                foreach (var nl in nls)
                {
                    _exportExcelService.AddCell("", titleRow, ci++, cellStyle.TitleStyle, sheet, scoreWidth);
                    _exportExcelService.AddCell("", titleRow, ci++, cellStyle.TitleStyle, sheet, scoreWidth);
                }
                sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));
                #endregion

                var lineScoreTable = await GetLineScoreTable2(nceePlanId);
                foreach (var lst in lineScoreTable.Select($"direction_course_id = {directionCourse.Id} AND course_id <> 100"))
                {
                    #region 表头
                    IRow subTitleRow = sheet.CreateRow(rowNum++);
                    ci = 0;
                    _exportExcelService.AddCell($"{lst["course_name"]}", subTitleRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 13);
                    _exportExcelService.AddCell("", subTitleRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
                    foreach (var nl in nls)
                    {
                        _exportExcelService.AddCell("", subTitleRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                        _exportExcelService.AddCell("", subTitleRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    }
                    sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));

                    IRow headerRow = sheet.CreateRow(rowNum++);
                    ci = 0;
                    _exportExcelService.AddCell("名称", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 13);
                    _exportExcelService.AddCell("实考人数", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
                    foreach (var nl in nls)
                    {
                        _exportExcelService.AddCell($"{nl["ncee_line_level_name"]}({lst[$"line_score_x_{nl["ncee_line_level"]}"]})", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                        _exportExcelService.AddCell("", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 2, ci - 1));
                    }

                    IRow headerRow2 = sheet.CreateRow(rowNum++);
                    ci = 0;
                    _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 13);
                    _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
                    foreach (var nl in nls)
                    {
                        _exportExcelService.AddCell("人数", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                        _exportExcelService.AddCell("比例(%)", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    }
                    sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 1, 0, 0));
                    sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 1, 1, 1));
                    #endregion

                    foreach (DataRow dr in table.Select($"direction_course_id = {directionCourse.Id} AND course_id = {lst["course_id"]}"))
                    {
                        IRow row = sheet.CreateRow(rowNum++);
                        int rci = 0;

                        short type = (short)dr["type"];
                        string itemName = "";
                        switch (type)
                        {
                            case 1:
                                itemName = $"{dr["sys_org_name"]}";
                                break;
                            case 2:
                                itemName = $"{dr["ncee_course_comb_name"]}";
                                break;
                            case 3:
                                itemName = $"{dr["class_number"]}班";
                                break;
                        }

                        _exportExcelService.AddCell(itemName, row, rci++, type == 1 ? cellStyle.LeftCellStyle : type == 2 ? cellStyleIndent1 : cellStyleIndent2);
                        _exportExcelService.AddCell(dr["total_count_1"], row, rci++, cellStyle.CenterCellStyle);
                        foreach (var nl in nls)
                        {
                            _exportExcelService.AddCell(dr[$"line_count_{nl["ncee_line_level"]}"], row, rci++, cellStyle.CenterCellStyle);
                            _exportExcelService.AddCell(dr[$"line_rate_{nl["ncee_line_level"]}"], row, rci++, cellStyle.PercentCellStyleP2);
                        }
                    }
                    rowNum += 1;
                }
            }
        }
        #endregion

        MemoryStream ms = new();
        wb.Write(ms, false);
        ms.Flush();
        return ms.ToArray();
    }
    /// <summary>
    /// 导出汇总班级上线情况（未选科）
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <param name="baseLineTable"></param>
    /// <returns></returns>
    private async Task<byte[]> ExportClassLineUnselected(int nceePlanId, DataTable baseLineTable)
    {
        IWorkbook wb = new XSSFWorkbook();
        var cellStyle = _exportExcelService.GetCellStyle(wb, titleFontSize: 14);

        var cellStyleIndent1 = wb.CreateCellStyle();
        cellStyleIndent1.CloneStyleFrom(cellStyle.LeftCellStyle);
        cellStyleIndent1.Indention = 1;

        var cellStyleIndent2 = wb.CreateCellStyle();
        cellStyleIndent2.CloneStyleFrom(cellStyle.LeftCellStyle);
        cellStyleIndent2.Indention = 2;

        #region 总分上线
        {
            var table = await GetOrgLineTotalTable2(nceePlanId, 0);
            ISheet sheet = wb.CreateSheet("总分上线");
            sheet.DisplayGridlines = false;
            // 行索引号
            int rowNum = 0;
            var nls = baseLineTable.Select($"direction_course_id = 99");

            #region 标题
            IRow titleRow = sheet.CreateRow(rowNum++);
            titleRow.HeightInPoints = 30;
            int ci = 0;
            int scoreWidth = 10;
            _exportExcelService.AddCell($"总分上线情况", titleRow, ci++, cellStyle.TitleStyle, sheet, 13);
            _exportExcelService.AddCell("", titleRow, ci++, cellStyle.TitleStyle, sheet, 10);
            foreach (var nl in nls)
            {
                _exportExcelService.AddCell("", titleRow, ci++, cellStyle.TitleStyle, sheet, scoreWidth);
                _exportExcelService.AddCell("", titleRow, ci++, cellStyle.TitleStyle, sheet, scoreWidth);
            }
            sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));
            #endregion

            #region 表头
            IRow headerRow = sheet.CreateRow(rowNum++);
            ci = 0;
            _exportExcelService.AddCell("名称", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 13);
            _exportExcelService.AddCell("实考人数", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
            foreach (var nl in nls)
            {
                _exportExcelService.AddCell($"{nl["ncee_line_level_name"]}({nl["line_score_x"]})", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                _exportExcelService.AddCell("", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 2, ci - 1));
            }

            IRow headerRow2 = sheet.CreateRow(rowNum++);
            ci = 0;
            _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 13);
            _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
            foreach (var nl in nls)
            {
                _exportExcelService.AddCell("人数", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                _exportExcelService.AddCell("比例(%)", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
            }
            sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 1, 0, 0));
            sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 1, 1, 1));
            #endregion

            foreach (DataRow dr in table.Select($"direction_course_id = 99"))
            {
                short type = (short)dr["type"];
                if (type == 2)
                {
                    continue;
                }

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

                string itemName = "";
                switch (type)
                {
                    case 1:
                        itemName = $"{dr["sys_org_name"]}";
                        break;
                    case 3:
                        itemName = $"{dr["class_number"]}班";
                        break;
                }

                _exportExcelService.AddCell(itemName, row, rci++, type == 1 ? cellStyle.LeftCellStyle : type == 2 ? cellStyleIndent1 : cellStyleIndent2);
                _exportExcelService.AddCell(dr["total_count"], row, rci++, cellStyle.CenterCellStyle);
                foreach (var nl in nls)
                {
                    _exportExcelService.AddCell(dr[$"line_count_{nl["ncee_line_level"]}"], row, rci++, cellStyle.CenterCellStyle);
                    _exportExcelService.AddCell(dr[$"line_rate_{nl["ncee_line_level"]}"], row, rci++, cellStyle.PercentCellStyleP2);
                }
            }
        }
        #endregion

        #region 双上线
        {
            var table = await GetOrgLineCourseTable2(nceePlanId, 0);
            ISheet sheet = wb.CreateSheet("双上线");
            sheet.DisplayGridlines = false;

            // 行索引号
            int rowNum = 0;
            var nls = baseLineTable.Select($"direction_course_id = 99");

            #region 标题
            IRow titleRow = sheet.CreateRow(rowNum++);
            titleRow.HeightInPoints = 30;
            int ci = 0;
            int scoreWidth = 10;
            _exportExcelService.AddCell($"双上线情况", titleRow, ci++, cellStyle.TitleStyle, sheet, 13);
            _exportExcelService.AddCell("", titleRow, ci++, cellStyle.TitleStyle, sheet, 10);
            foreach (var nl in nls)
            {
                _exportExcelService.AddCell("", titleRow, ci++, cellStyle.TitleStyle, sheet, scoreWidth);
                _exportExcelService.AddCell("", titleRow, ci++, cellStyle.TitleStyle, sheet, scoreWidth);
            }
            sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));
            #endregion

            var lineScoreTable = await GetLineScoreTable2(nceePlanId);
            foreach (var lst in lineScoreTable.Select($"direction_course_id = 99 AND course_id <> 100"))
            {
                #region 表头
                IRow subTitleRow = sheet.CreateRow(rowNum++);
                ci = 0;
                _exportExcelService.AddCell($"{lst["course_name"]}", subTitleRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 13);
                _exportExcelService.AddCell("", subTitleRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
                foreach (var nl in nls)
                {
                    _exportExcelService.AddCell("", subTitleRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    _exportExcelService.AddCell("", subTitleRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                }
                sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));

                IRow headerRow = sheet.CreateRow(rowNum++);
                ci = 0;
                _exportExcelService.AddCell("名称", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 13);
                _exportExcelService.AddCell("实考人数", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
                foreach (var nl in nls)
                {
                    _exportExcelService.AddCell($"{nl["ncee_line_level_name"]}({lst[$"line_score_x_{nl["ncee_line_level"]}"]})", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    _exportExcelService.AddCell("", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 2, ci - 1));
                }

                IRow headerRow2 = sheet.CreateRow(rowNum++);
                ci = 0;
                _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 13);
                _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
                foreach (var nl in nls)
                {
                    _exportExcelService.AddCell("人数", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    _exportExcelService.AddCell("比例(%)", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                }
                sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 1, 0, 0));
                sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 1, 1, 1));
                #endregion

                foreach (DataRow dr in table.Select($"direction_course_id = 99 AND course_id = {lst["course_id"]}"))
                {
                    short type = (short)dr["type"];
                    if (type == 2)
                    {
                        continue;
                    }

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

                    string itemName = "";
                    switch (type)
                    {
                        case 1:
                            itemName = $"{dr["sys_org_name"]}";
                            break;
                        case 2:
                            itemName = $"{dr["ncee_course_comb_name"]}";
                            break;
                        case 3:
                            itemName = $"{dr["class_number"]}班";
                            break;
                    }

                    _exportExcelService.AddCell(itemName, row, rci++, type == 1 ? cellStyle.LeftCellStyle : type == 2 ? cellStyleIndent1 : cellStyleIndent2);
                    _exportExcelService.AddCell(dr["total_count"], row, rci++, cellStyle.CenterCellStyle);
                    foreach (var nl in nls)
                    {
                        _exportExcelService.AddCell(dr[$"line_count_{nl["ncee_line_level"]}"], row, rci++, cellStyle.CenterCellStyle);
                        _exportExcelService.AddCell(dr[$"line_rate_{nl["ncee_line_level"]}"], row, rci++, cellStyle.PercentCellStyleP2);
                    }
                }
            }
        }
        #endregion

        MemoryStream ms = new();
        wb.Write(ms, false);
        ms.Flush();
        return ms.ToArray();
    }

    /// <summary>
    /// 导出各机构转换成绩
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <param name="orgList"></param>
    /// <param name="orgTitle"></param>
    /// <param name="isExportConvertRange">导出转换区间</param>
    /// <param name="isExportOrder">导出排名</param>
    /// <returns></returns>
    private async Task<Dictionary<string, byte[]>> ExportOrgConvertScore(int nceePlanId, List<SysOrgOutput> orgList, string orgTitle, bool isExportConvertRange = false, bool isExportOrder = false)
    {
        Dictionary<string, byte[]> ret = new();

        foreach (var org in orgList)
        {
            var bs = await ExportConvertScore(nceePlanId, orgTitle, org, isExportConvertRange, isExportOrder);
            ret.Add($"{org.Code}-{org.ShortName}", bs);
        }
        return ret;
    }
    /// <summary>
    /// 导出各机构班级上线情况
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <param name="orgList"></param>
    /// <param name="baseLineTable"></param>
    /// <param name="courses"></param>
    /// <returns></returns>
    private async Task<Dictionary<string, byte[]>> ExportOrgClassLine(int nceePlanId, List<SysOrgOutput> orgList, DataTable baseLineTable, List<NceeCourseDto> courses)
    {
        Dictionary<string, byte[]> ret = new();

        foreach (var org in orgList)
        {
            IWorkbook wb = new XSSFWorkbook();
            var cellStyle = _exportExcelService.GetCellStyle(wb, titleFontSize: 14);

            var cellStyleIndent1 = wb.CreateCellStyle();
            cellStyleIndent1.CloneStyleFrom(cellStyle.LeftCellStyle);
            cellStyleIndent1.Indention = 1;

            var cellStyleIndent2 = wb.CreateCellStyle();
            cellStyleIndent2.CloneStyleFrom(cellStyle.LeftCellStyle);
            cellStyleIndent2.Indention = 2;

            #region 有效分
            await ExportLineScore(nceePlanId, wb, cellStyle, isExportRate: false);
            #endregion

            #region 平均分
            {
                var tcourses = courses.Where(t => t.Id != 101).ToList();

                ISheet sheet = wb.CreateSheet("平均分");
                sheet.DisplayGridlines = false;

                // 行索引号
                int rowNum = 0;

                foreach (var directionCourse in NceeUtil.DirectionCourses)
                {
                    var table = await GetAvgScoreTable(nceePlanId, directionCourse.Id);

                    #region 表头
                    IRow headerRow1 = sheet.CreateRow(rowNum++);
                    headerRow1.HeightInPoints = 30;
                    int ci = 0;
                    int scoreWidth = 8;
                    _exportExcelService.AddCell($"{directionCourse.Name}类平均分", headerRow1, ci++, cellStyle.TitleStyle, sheet, 10);
                    for (; ci < 19; ci++)
                    {
                        _exportExcelService.AddCell("", headerRow1, ci, cellStyle.TitleStyle, sheet, scoreWidth);
                    }
                    sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));

                    IRow headerRow2 = sheet.CreateRow(rowNum++);
                    headerRow2.HeightInPoints = 15;
                    ci = 0;
                    _exportExcelService.AddCell("学科", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
                    foreach (var course in tcourses)
                    {
                        if (course.IsSkip(directionCourse.Id)) { continue; }
                        string cn = course.Name;
                        if (course.Id == 5 || course.Id == 6 || course.Id == 7 || course.Id == 9 || course.Id == 100)
                        {
                            cn = $"{cn}X";
                        }
                        _exportExcelService.AddCell(cn, headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                        _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                        if (ci % 2 == 1)
                        {
                            sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 2, ci - 1));
                        }
                    }


                    IRow headerRow3 = sheet.CreateRow(rowNum++);
                    headerRow3.HeightInPoints = 15;
                    ci = 0;
                    _exportExcelService.AddCell("学校", headerRow3, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
                    foreach (var course in tcourses)
                    {
                        if (course.IsSkip(directionCourse.Id)) { continue; }
                        _exportExcelService.AddCell("人数", headerRow3, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                        _exportExcelService.AddCell("平均", headerRow3, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    }
                    #endregion

                    #region 数据
                    foreach (DataRow dr in table.Rows)
                    {
                        IRow row = sheet.CreateRow(rowNum++);
                        row.HeightInPoints = 15;
                        int rci = 0;
                        _exportExcelService.AddCell(dr["sys_org_short_name"], row, rci++, cellStyle.CenterCellStyle);
                        foreach (var course in tcourses)
                        {
                            if (course.IsSkip(directionCourse.Id)) { continue; }
                            _exportExcelService.AddCell(dr[$"total_count_{course.Id}"], row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
                            _exportExcelService.AddCell(dr[$"avg_score_x_{course.Id}"], row, rci++, cellStyle.NumberCellStyleP2, zeroToBlank: true);
                        }
                    }
                    #endregion

                    rowNum += 2;
                }
            }
            #endregion

            #region 总分上线
            {
                var table = await GetOrgLineTotalTable2(nceePlanId, org.Id);

                ISheet sheet = wb.CreateSheet("总分上线");
                sheet.DisplayGridlines = false;
                // 行索引号
                int rowNum = 0;

                foreach (var directionCourse in NceeUtil.DirectionCourses)
                {
                    var nls = baseLineTable.Select($"direction_course_id = {directionCourse.Id}");

                    #region 表头
                    IRow titleRow = sheet.CreateRow(rowNum++);
                    titleRow.HeightInPoints = 30;
                    int ci = 0;
                    int scoreWidth = 10;
                    _exportExcelService.AddCell($"{org.ShortName}{directionCourse.Name}类总分上线情况", titleRow, ci++, cellStyle.TitleStyle, sheet, 13);
                    _exportExcelService.AddCell("", titleRow, ci++, cellStyle.TitleStyle, sheet, 10);
                    foreach (var nl in nls)
                    {
                        _exportExcelService.AddCell("", titleRow, ci++, cellStyle.TitleStyle, sheet, scoreWidth);
                        _exportExcelService.AddCell("", titleRow, ci++, cellStyle.TitleStyle, sheet, scoreWidth);
                    }
                    sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));

                    IRow headerRow = sheet.CreateRow(rowNum++);
                    ci = 0;
                    _exportExcelService.AddCell("名称", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 13);
                    _exportExcelService.AddCell("实考人数", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
                    foreach (var nl in nls)
                    {
                        _exportExcelService.AddCell($"{nl["ncee_line_level_name"]}({nl["line_score_x"]})", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                        _exportExcelService.AddCell("", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 2, ci - 1));
                    }

                    IRow headerRow2 = sheet.CreateRow(rowNum++);
                    ci = 0;
                    _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 13);
                    _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
                    foreach (var nl in nls)
                    {
                        _exportExcelService.AddCell("人数", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                        _exportExcelService.AddCell("比例(%)", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    }
                    sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 1, 0, 0));
                    sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 1, 1, 1));
                    #endregion

                    #region 数据
                    foreach (DataRow dr in table.Select($"direction_course_id = {directionCourse.Id}"))
                    {
                        IRow row = sheet.CreateRow(rowNum++);
                        int rci = 0;

                        short type = (short)dr["type"];
                        string itemName = "";
                        switch (type)
                        {
                            case 1:
                                itemName = $"{dr["sys_org_name"]}";
                                break;
                            case 2:
                                itemName = $"{dr["ncee_course_comb_name"]}";
                                break;
                            case 3:
                                itemName = $"{dr["class_number"]}班";
                                break;
                        }

                        _exportExcelService.AddCell(itemName, row, rci++, type == 1 ? cellStyle.LeftCellStyle : type == 2 ? cellStyleIndent1 : cellStyleIndent2);
                        _exportExcelService.AddCell(dr["total_count_1"], row, rci++, cellStyle.CenterCellStyle);
                        foreach (var nl in nls)
                        {
                            _exportExcelService.AddCell(dr[$"line_count_{nl["ncee_line_level"]}"], row, rci++, cellStyle.CenterCellStyle);
                            _exportExcelService.AddCell(dr[$"line_rate_{nl["ncee_line_level"]}"], row, rci++, cellStyle.PercentCellStyleP2);
                        }
                    }
                    #endregion

                    rowNum += 1;
                }
            }
            #endregion

            #region 双上线
            {
                var table = await GetOrgLineCourseTable2(nceePlanId, org.Id);
                var lineScoreTable = await GetLineScoreTable2(nceePlanId);

                ISheet sheet = wb.CreateSheet("双上线");
                sheet.DisplayGridlines = false;
                // 行索引号
                int rowNum = 0;

                foreach (var directionCourse in NceeUtil.DirectionCourses)
                {
                    var nls = baseLineTable.Select($"direction_course_id = {directionCourse.Id}");

                    IRow titleRow = sheet.CreateRow(rowNum++);
                    titleRow.HeightInPoints = 30;
                    int ci = 0;
                    int scoreWidth = 10;
                    _exportExcelService.AddCell($"{org.ShortName}{directionCourse.Name}类双上线情况", titleRow, ci++, cellStyle.TitleStyle, sheet, 13);
                    _exportExcelService.AddCell("", titleRow, ci++, cellStyle.TitleStyle, sheet, 10);
                    foreach (var nl in nls)
                    {
                        _exportExcelService.AddCell("", titleRow, ci++, cellStyle.TitleStyle, sheet, scoreWidth);
                        _exportExcelService.AddCell("", titleRow, ci++, cellStyle.TitleStyle, sheet, scoreWidth);
                    }
                    sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));

                    foreach (var lst in lineScoreTable.Select($"direction_course_id = {directionCourse.Id} AND course_id <> 100"))
                    {
                        #region 表头
                        IRow subTitleRow = sheet.CreateRow(rowNum++);
                        ci = 0;
                        _exportExcelService.AddCell($"{lst["course_name"]}", subTitleRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 13);
                        _exportExcelService.AddCell("", subTitleRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
                        foreach (var nl in nls)
                        {
                            _exportExcelService.AddCell("", subTitleRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                            _exportExcelService.AddCell("", subTitleRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                        }
                        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));

                        IRow headerRow = sheet.CreateRow(rowNum++);
                        ci = 0;
                        _exportExcelService.AddCell("名称", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 13);
                        _exportExcelService.AddCell("实考人数", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
                        foreach (var nl in nls)
                        {
                            _exportExcelService.AddCell($"{nl["ncee_line_level_name"]}({lst[$"line_score_x_{nl["ncee_line_level"]}"]})", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                            _exportExcelService.AddCell("", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                            sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 2, ci - 1));
                        }

                        IRow headerRow2 = sheet.CreateRow(rowNum++);
                        ci = 0;
                        _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 13);
                        _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
                        foreach (var nl in nls)
                        {
                            _exportExcelService.AddCell("人数", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                            _exportExcelService.AddCell("比例(%)", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                        }
                        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 1, 0, 0));
                        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 1, 1, 1));
                        #endregion

                        #region 数据
                        foreach (DataRow dr in table.Select($"direction_course_id = {directionCourse.Id} AND course_id = {lst["course_id"]}"))
                        {
                            IRow row = sheet.CreateRow(rowNum++);
                            int rci = 0;

                            short type = (short)dr["type"];
                            string itemName = "";
                            switch (type)
                            {
                                case 1:
                                    itemName = $"{dr["sys_org_name"]}";
                                    break;
                                case 2:
                                    itemName = $"{dr["ncee_course_comb_name"]}";
                                    break;
                                case 3:
                                    itemName = $"{dr["class_number"]}班";
                                    break;
                            }

                            _exportExcelService.AddCell(itemName, row, rci++, type == 1 ? cellStyle.LeftCellStyle : type == 2 ? cellStyleIndent1 : cellStyleIndent2);
                            _exportExcelService.AddCell(dr["total_count_1"], row, rci++, cellStyle.CenterCellStyle);
                            foreach (var nl in nls)
                            {
                                _exportExcelService.AddCell(dr[$"line_count_{nl["ncee_line_level"]}"], row, rci++, cellStyle.CenterCellStyle);
                                _exportExcelService.AddCell(dr[$"line_rate_{nl["ncee_line_level"]}"], row, rci++, cellStyle.PercentCellStyleP2);
                            }
                        }
                        #endregion

                        rowNum += 1;
                    }
                }
            }
            #endregion

            MemoryStream ms = new();
            wb.Write(ms, false);
            ms.Flush();

            ret.Add($"{org.Code}-{org.ShortName}", ms.ToArray());
        }

        return ret;
    }
    /// <summary>
    /// 导出各机构班级上线情况（未选科）
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <param name="orgList"></param>
    /// <param name="baseLineTable"></param>
    /// <param name="courses"></param>
    /// <returns></returns>
    private async Task<Dictionary<string, byte[]>> ExportOrgClassLineUnselected(int nceePlanId, List<SysOrgOutput> orgList, DataTable baseLineTable, List<NceeCourseDto> courses)
    {
        Dictionary<string, byte[]> ret = new();

        foreach (var org in orgList)
        {
            IWorkbook wb = new XSSFWorkbook();
            var cellStyle = _exportExcelService.GetCellStyle(wb, titleFontSize: 14);

            var cellStyleIndent1 = wb.CreateCellStyle();
            cellStyleIndent1.CloneStyleFrom(cellStyle.LeftCellStyle);
            cellStyleIndent1.Indention = 1;

            var cellStyleIndent2 = wb.CreateCellStyle();
            cellStyleIndent2.CloneStyleFrom(cellStyle.LeftCellStyle);
            cellStyleIndent2.Indention = 2;

            #region 有效分
            var lstable = await GetLineScoreTable(nceePlanId);
            ExportLineScore(wb, cellStyle, lstable, courses);
            #endregion

            #region 平均分
            {
                ISheet sheet = wb.CreateSheet("平均分");
                sheet.DisplayGridlines = false;

                // 行索引号
                int rowNum = 0;

                var table = await GetAvgScoreTable(nceePlanId, 99);

                #region 表头
                IRow headerRow1 = sheet.CreateRow(rowNum++);
                headerRow1.HeightInPoints = 30;
                int ci = 0;
                int scoreWidth = 8;
                _exportExcelService.AddCell($"平均分", headerRow1, ci++, cellStyle.TitleStyle, sheet, 10);
                for (; ci < courses.Count * 2 + 1; ci++)
                {
                    _exportExcelService.AddCell("", headerRow1, ci, cellStyle.TitleStyle, sheet, scoreWidth);
                }
                sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));

                IRow headerRow2 = sheet.CreateRow(rowNum++);
                headerRow2.HeightInPoints = 15;
                ci = 0;
                _exportExcelService.AddCell("学科", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
                foreach (var course in courses)
                {
                    string cn = course.Name;
                    _exportExcelService.AddCell(cn, headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    if (ci % 2 == 1)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 2, ci - 1));
                    }
                }


                IRow headerRow3 = sheet.CreateRow(rowNum++);
                headerRow3.HeightInPoints = 15;
                ci = 0;
                _exportExcelService.AddCell("学校", headerRow3, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
                foreach (var course in courses)
                {
                    _exportExcelService.AddCell("人数", headerRow3, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    _exportExcelService.AddCell("平均", headerRow3, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                }
                #endregion

                #region 数据
                foreach (DataRow dr in table.Rows)
                {
                    IRow row = sheet.CreateRow(rowNum++);
                    row.HeightInPoints = 15;
                    int rci = 0;
                    _exportExcelService.AddCell(dr["sys_org_short_name"], row, rci++, cellStyle.CenterCellStyle);
                    foreach (var course in courses)
                    {
                        _exportExcelService.AddCell(dr[$"total_count_{course.Id}"], row, rci++, cellStyle.CenterCellStyle, zeroToBlank: true);
                        _exportExcelService.AddCell(dr[$"avg_score_x_{course.Id}"], row, rci++, cellStyle.NumberCellStyleP2, zeroToBlank: true);
                    }
                }
                #endregion
            }
            #endregion

            #region 总分上线
            {
                var table = await GetOrgLineTotalTable2(nceePlanId, org.Id);

                ISheet sheet = wb.CreateSheet("总分上线");
                sheet.DisplayGridlines = false;
                // 行索引号
                int rowNum = 0;

                var nls = baseLineTable.Select($"direction_course_id = 99");

                #region 表头
                IRow titleRow = sheet.CreateRow(rowNum++);
                titleRow.HeightInPoints = 30;
                int ci = 0;
                int scoreWidth = 10;
                _exportExcelService.AddCell($"{org.ShortName}总分上线情况", titleRow, ci++, cellStyle.TitleStyle, sheet, 13);
                _exportExcelService.AddCell("", titleRow, ci++, cellStyle.TitleStyle, sheet, 10);
                foreach (var nl in nls)
                {
                    _exportExcelService.AddCell("", titleRow, ci++, cellStyle.TitleStyle, sheet, scoreWidth);
                    _exportExcelService.AddCell("", titleRow, ci++, cellStyle.TitleStyle, sheet, scoreWidth);
                }
                sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));

                IRow headerRow = sheet.CreateRow(rowNum++);
                ci = 0;
                _exportExcelService.AddCell("名称", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 13);
                _exportExcelService.AddCell("实考人数", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
                foreach (var nl in nls)
                {
                    _exportExcelService.AddCell($"{nl["ncee_line_level_name"]}({nl["line_score_x"]})", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    _exportExcelService.AddCell("", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 2, ci - 1));
                }

                IRow headerRow2 = sheet.CreateRow(rowNum++);
                ci = 0;
                _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 13);
                _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
                foreach (var nl in nls)
                {
                    _exportExcelService.AddCell("人数", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    _exportExcelService.AddCell("比例(%)", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                }
                sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 1, 0, 0));
                sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 1, 1, 1));
                #endregion

                #region 数据
                foreach (DataRow dr in table.Select($"direction_course_id = 99"))
                {
                    short type = (short)dr["type"];
                    if (type == 2)
                    {
                        continue;
                    }

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

                    string itemName = "";
                    switch (type)
                    {
                        case 1:
                            itemName = $"{dr["sys_org_name"]}";
                            break;
                        case 3:
                            itemName = $"{dr["class_number"]}班";
                            break;
                    }

                    _exportExcelService.AddCell(itemName, row, rci++, type == 1 ? cellStyle.LeftCellStyle : type == 2 ? cellStyleIndent1 : cellStyleIndent2);
                    _exportExcelService.AddCell(dr["total_count_1"], row, rci++, cellStyle.CenterCellStyle);
                    foreach (var nl in nls)
                    {
                        _exportExcelService.AddCell(dr[$"line_count_{nl["ncee_line_level"]}"], row, rci++, cellStyle.CenterCellStyle);
                        _exportExcelService.AddCell(dr[$"line_rate_{nl["ncee_line_level"]}"], row, rci++, cellStyle.PercentCellStyleP2);
                    }
                }
                #endregion
            }
            #endregion

            #region 双上线
            {
                var table = await GetOrgLineCourseTable2(nceePlanId, org.Id);
                var lineScoreTable = await GetLineScoreTable2(nceePlanId);

                ISheet sheet = wb.CreateSheet("双上线");
                sheet.DisplayGridlines = false;
                // 行索引号
                int rowNum = 0;

                var nls = baseLineTable.Select($"direction_course_id = 99");

                IRow titleRow = sheet.CreateRow(rowNum++);
                titleRow.HeightInPoints = 30;
                int ci = 0;
                int scoreWidth = 10;
                _exportExcelService.AddCell($"{org.ShortName}双上线情况", titleRow, ci++, cellStyle.TitleStyle, sheet, 13);
                _exportExcelService.AddCell("", titleRow, ci++, cellStyle.TitleStyle, sheet, 10);
                foreach (var nl in nls)
                {
                    _exportExcelService.AddCell("", titleRow, ci++, cellStyle.TitleStyle, sheet, scoreWidth);
                    _exportExcelService.AddCell("", titleRow, ci++, cellStyle.TitleStyle, sheet, scoreWidth);
                }
                sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));

                foreach (var lst in lineScoreTable.Select($"direction_course_id = 99 AND course_id <> 100"))
                {
                    #region 表头
                    IRow subTitleRow = sheet.CreateRow(rowNum++);
                    ci = 0;
                    _exportExcelService.AddCell($"{lst["course_name"]}", subTitleRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 13);
                    _exportExcelService.AddCell("", subTitleRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
                    foreach (var nl in nls)
                    {
                        _exportExcelService.AddCell("", subTitleRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                        _exportExcelService.AddCell("", subTitleRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    }
                    sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));

                    IRow headerRow = sheet.CreateRow(rowNum++);
                    ci = 0;
                    _exportExcelService.AddCell("名称", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 13);
                    _exportExcelService.AddCell("实考人数", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
                    foreach (var nl in nls)
                    {
                        _exportExcelService.AddCell($"{nl["ncee_line_level_name"]}({lst[$"line_score_x_{nl["ncee_line_level"]}"]})", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                        _exportExcelService.AddCell("", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 2, ci - 1));
                    }

                    IRow headerRow2 = sheet.CreateRow(rowNum++);
                    ci = 0;
                    _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 13);
                    _exportExcelService.AddCell("", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
                    foreach (var nl in nls)
                    {
                        _exportExcelService.AddCell("人数", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                        _exportExcelService.AddCell("比例(%)", headerRow2, ci++, cellStyle.ColumnFillHeaderStyle, sheet, scoreWidth);
                    }
                    sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 1, 0, 0));
                    sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 1, 1, 1));
                    #endregion

                    #region 数据
                    foreach (DataRow dr in table.Select($"direction_course_id = 99 AND course_id = {lst["course_id"]}"))
                    {
                        short type = (short)dr["type"];
                        if (type == 2)
                        {
                            continue;
                        }

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

                        string itemName = "";
                        switch (type)
                        {
                            case 1:
                                itemName = $"{dr["sys_org_name"]}";
                                break;
                            case 3:
                                itemName = $"{dr["class_number"]}班";
                                break;
                        }

                        _exportExcelService.AddCell(itemName, row, rci++, type == 1 ? cellStyle.LeftCellStyle : type == 2 ? cellStyleIndent1 : cellStyleIndent2);
                        _exportExcelService.AddCell(dr["total_count_1"], row, rci++, cellStyle.CenterCellStyle);
                        foreach (var nl in nls)
                        {
                            _exportExcelService.AddCell(dr[$"line_count_{nl["ncee_line_level"]}"], row, rci++, cellStyle.CenterCellStyle);
                            _exportExcelService.AddCell(dr[$"line_rate_{nl["ncee_line_level"]}"], row, rci++, cellStyle.PercentCellStyleP2);
                        }
                    }
                    #endregion

                    rowNum += 1;
                }
            }
            #endregion

            MemoryStream ms = new();
            wb.Write(ms, false);
            ms.Flush();

            ret.Add($"{org.Code}-{org.ShortName}", ms.ToArray());
        }

        return ret;
    }

    #endregion

    #region 获取数据
    /// <summary>
    /// 获取上线合并数据
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <returns></returns>
    private async Task<DataTable> GetLineCountTable(int nceePlanId)
    {
        var dt = await _rep.SqlQueryAsync($@"
SELECT T1.*,
	T2.d_4_total_count, T2.d_4_line_count_1, T2.d_4_line_count_2, T2.d_4_line_count_3,
	T2.d_4_line_count_1 / T2.d_4_total_count AS d_4_line_rate_1,
	T2.d_4_line_count_2 / T2.d_4_total_count AS d_4_line_rate_2,
	T2.d_4_line_count_3 / T2.d_4_total_count AS d_4_line_rate_3,
	T2.d_8_total_count, T2.d_8_line_count_1, T2.d_8_line_count_2, T2.d_8_line_count_3,
	T2.d_8_line_count_1 / T2.d_8_total_count AS d_8_line_rate_1,
	T2.d_8_line_count_2 / T2.d_8_total_count AS d_8_line_rate_2,
	T2.d_8_line_count_3 / T2.d_8_total_count AS d_8_line_rate_3
FROM
(
	SELECT IFNULL(T2.short_name, '合计') AS sys_org_name, T1.*, 
		T1.line_count_1 / T1.total_count AS line_rate_1,
		T1.line_count_2 / T1.total_count AS line_rate_2,
		T1.line_count_3 / T1.total_count AS line_rate_3
	FROM
	(
		SELECT T1.type, T1.sys_org_id,
			MAX(T1.total_count) AS total_count,
			SUM(CASE WHEN T1.ncee_line_level = 1 THEN T1.line_count ELSE 0 END) AS line_count_1,
			SUM(CASE WHEN T1.ncee_line_level = 2 THEN T1.line_count ELSE 0 END) AS line_count_2,
			SUM(CASE WHEN T1.ncee_line_level = 3 THEN T1.line_count ELSE 0 END) AS line_count_3
		FROM 
		(
			SELECT T1.type, T1.sys_org_id, T1.ncee_line_level, SUM(T1.line_count) AS line_count, SUM(T1.total_count) AS total_count
			FROM ncee_line_total AS T1
			WHERE T1.ncee_plan_id = {nceePlanId} AND (T1.type = 1 OR T1.type = 4) AND T1.ncee_course_comb_id IS NULL
			GROUP BY T1.type, T1.sys_org_id, T1.ncee_line_level
		) AS T1
		GROUP BY T1.type, T1.sys_org_id
	) AS T1
	LEFT JOIN sys_org AS T2 ON T1.sys_org_id = T2.id
) AS T1
LEFT JOIN
(
	SELECT T1.type, T1.sys_org_id,
		MAX(CASE WHEN T1.direction_course_id = 4 THEN T1.total_count ELSE 0 END) AS d_4_total_count,
		SUM(CASE WHEN T1.direction_course_id = 4 AND T1.ncee_line_level = 1 THEN T1.line_count ELSE 0 END) AS d_4_line_count_1,
		SUM(CASE WHEN T1.direction_course_id = 4 AND T1.ncee_line_level = 2 THEN T1.line_count ELSE 0 END) AS d_4_line_count_2,
		SUM(CASE WHEN T1.direction_course_id = 4 AND T1.ncee_line_level = 3 THEN T1.line_count ELSE 0 END) AS d_4_line_count_3,
		MAX(CASE WHEN T1.direction_course_id = 8 THEN T1.total_count ELSE 0 END) AS d_8_total_count,
		SUM(CASE WHEN T1.direction_course_id = 8 AND T1.ncee_line_level = 1 THEN T1.line_count ELSE 0 END) AS d_8_line_count_1,
		SUM(CASE WHEN T1.direction_course_id = 8 AND T1.ncee_line_level = 2 THEN T1.line_count ELSE 0 END) AS d_8_line_count_2,
		SUM(CASE WHEN T1.direction_course_id = 8 AND T1.ncee_line_level = 3 THEN T1.line_count ELSE 0 END) AS d_8_line_count_3
	FROM
	(
		SELECT T1.direction_course_id, T1.type, T1.sys_org_id, T1.ncee_line_level, SUM(T1.line_count) AS line_count, SUM(T1.total_count) AS total_count
		FROM ncee_line_total AS T1
		WHERE T1.ncee_plan_id = {nceePlanId} AND (T1.type = 1 OR T1.type = 4) AND T1.ncee_course_comb_id IS NULL
		GROUP BY T1.direction_course_id, T1.type, T1.sys_org_id, T1.ncee_line_level
	) AS T1
	GROUP BY T1.type, T1.sys_org_id
) AS T2 ON T1.type = T2.type AND IFNULL(T1.sys_org_id, 0) = IFNULL(T2.sys_org_id, 0)
ORDER BY T1.type, T1.sys_org_id

");

        return dt;
    }
    /// <summary>
    /// 获取有效分数据
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <returns></returns>
    private async Task<DataTable> GetLineScoreTable(int nceePlanId)
    {
        var dt = await _rep.SqlQueryAsync(@"
SELECT T2.`name` AS direction_course_name,
	T3.`name` AS ncee_line_level_name,
	T1.*,
    T4.line_rate,
	COUNT(T1.ncee_line_level) OVER(PARTITION BY direction_course_id) AS ncee_level_count
FROM
(
    SELECT T1.ncee_plan_id, T1.direction_course_id, T1.ncee_line_level,
	    MAX(IF(T1.course_id = 1, T1.line_score_x, NULL)) AS line_score_x_1,
	    MAX(IF(T1.course_id = 2, T1.line_score_x, NULL)) AS line_score_x_2,
	    MAX(IF(T1.course_id = 3, T1.line_score_x, NULL)) AS line_score_x_3,
	    MAX(IF(T1.course_id = 4, T1.line_score_x, NULL)) AS line_score_x_4,
	    MAX(IF(T1.course_id = 5, T1.line_score_x, NULL)) AS line_score_x_5,
	    MAX(IF(T1.course_id = 6, T1.line_score_x, NULL)) AS line_score_x_6,
	    MAX(IF(T1.course_id = 7, T1.line_score_x, NULL)) AS line_score_x_7,
	    MAX(IF(T1.course_id = 8, T1.line_score_x, NULL)) AS line_score_x_8,
	    MAX(IF(T1.course_id = 9, T1.line_score_x, NULL)) AS line_score_x_9,
	    MAX(IF(T1.course_id = 101, T1.line_score_x, NULL)) AS line_score_x_101,
	    MAX(IF(T1.course_id = 100, T1.line_score_x, NULL)) AS line_score_x_100
    FROM ncee_course_line_score AS T1
    WHERE T1.ncee_plan_id = @nceePlanId
    GROUP BY T1.direction_course_id, T1.ncee_line_level
) AS T1
JOIN base_course AS T2 ON T1.direction_course_id = T2.id
JOIN (SELECT * FROM sys_dict_data WHERE sys_dict_type_id = 211) AS T3 ON T1.ncee_line_level = T3.`value`
JOIN ncee_base_line AS T4 ON T1.ncee_plan_id = T4.ncee_plan_id AND T1.direction_course_id = T4.direction_course_id AND T1.ncee_line_level = T4.ncee_line_level
ORDER BY T1.direction_course_id, T1.ncee_line_level
", new { NceePlanId = nceePlanId });

        return dt;
    }
    /// <summary>
    /// 获取有效分数据（科目未转置）
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <returns></returns>
    private async Task<DataTable> GetLineScoreTable2(int nceePlanId)
    {
        var dt = await _rep.SqlQueryAsync(@"
SELECT T2.`name` AS course_name, T1.*
FROM
(
	SELECT T1.direction_course_id, T1.course_id,
		MAX(CASE WHEN T1.ncee_line_level = 1 THEN T1.line_score ELSE NULL END) AS line_score_1,
		MAX(CASE WHEN T1.ncee_line_level = 2 THEN T1.line_score ELSE NULL END) AS line_score_2,
		MAX(CASE WHEN T1.ncee_line_level = 3 THEN T1.line_score ELSE NULL END) AS line_score_3,
		MAX(CASE WHEN T1.ncee_line_level = 1 THEN T1.line_score_x ELSE NULL END) AS line_score_x_1,
		MAX(CASE WHEN T1.ncee_line_level = 2 THEN T1.line_score_x ELSE NULL END) AS line_score_x_2,
		MAX(CASE WHEN T1.ncee_line_level = 3 THEN T1.line_score_x ELSE NULL END) AS line_score_x_3
	FROM ncee_course_line_score AS T1
	WHERE T1.ncee_plan_id = @nceePlanId
	GROUP BY T1.direction_course_id, T1.course_id
) AS T1
JOIN base_course AS T2 ON T1.course_id = T2.id
ORDER BY T1.direction_course_id, T1.course_id
", new { NceePlanId = nceePlanId });

        return dt;
    }
    /// <summary>
    /// 获取平均分数据
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <param name="directionCourseId"></param>
    /// <returns></returns>
    private async Task<DataTable> GetAvgScoreTable(int nceePlanId, short directionCourseId)
    {
        var dt = await _rep.SqlQueryAsync(@"
SELECT IFNULL(T2.short_name, '合计') AS sys_org_short_name, T1.*
FROM
(
	SELECT T1.direction_course_id, T1.sys_org_id,
		MAX(T1.total_count) AS total_count,
		SUM(CASE WHEN T1.course_id = 1 THEN T1.total_count ELSE 0 END) AS total_count_1,
		SUM(CASE WHEN T1.course_id = 2 THEN T1.total_count ELSE 0 END) AS total_count_2,
		SUM(CASE WHEN T1.course_id = 3 THEN T1.total_count ELSE 0 END) AS total_count_3,
		SUM(CASE WHEN T1.course_id = 4 THEN T1.total_count ELSE 0 END) AS total_count_4,
		SUM(CASE WHEN T1.course_id = 5 THEN T1.total_count ELSE 0 END) AS total_count_5,
		SUM(CASE WHEN T1.course_id = 6 THEN T1.total_count ELSE 0 END) AS total_count_6,
		SUM(CASE WHEN T1.course_id = 7 THEN T1.total_count ELSE 0 END) AS total_count_7,
		SUM(CASE WHEN T1.course_id = 8 THEN T1.total_count ELSE 0 END) AS total_count_8,
		SUM(CASE WHEN T1.course_id = 9 THEN T1.total_count ELSE 0 END) AS total_count_9,
		SUM(CASE WHEN T1.course_id = 100 THEN T1.total_count ELSE 0 END) AS total_count_100,
		SUM(CASE WHEN T1.course_id = 1 THEN T1.avg_score ELSE 0 END) AS avg_score_1,
		SUM(CASE WHEN T1.course_id = 2 THEN T1.avg_score ELSE 0 END) AS avg_score_2,
		SUM(CASE WHEN T1.course_id = 3 THEN T1.avg_score ELSE 0 END) AS avg_score_3,
		SUM(CASE WHEN T1.course_id = 4 THEN T1.avg_score ELSE 0 END) AS avg_score_4,
		SUM(CASE WHEN T1.course_id = 5 THEN T1.avg_score ELSE 0 END) AS avg_score_5,
		SUM(CASE WHEN T1.course_id = 6 THEN T1.avg_score ELSE 0 END) AS avg_score_6,
		SUM(CASE WHEN T1.course_id = 7 THEN T1.avg_score ELSE 0 END) AS avg_score_7,
		SUM(CASE WHEN T1.course_id = 8 THEN T1.avg_score ELSE 0 END) AS avg_score_8,
		SUM(CASE WHEN T1.course_id = 9 THEN T1.avg_score ELSE 0 END) AS avg_score_9,
		SUM(CASE WHEN T1.course_id = 100 THEN T1.avg_score ELSE 0 END) AS avg_score_100,
		SUM(CASE WHEN T1.course_id = 1 THEN T1.avg_score_x ELSE 0 END) AS avg_score_x_1,
		SUM(CASE WHEN T1.course_id = 2 THEN T1.avg_score_x ELSE 0 END) AS avg_score_x_2,
		SUM(CASE WHEN T1.course_id = 3 THEN T1.avg_score_x ELSE 0 END) AS avg_score_x_3,
		SUM(CASE WHEN T1.course_id = 4 THEN T1.avg_score_x ELSE 0 END) AS avg_score_x_4,
		SUM(CASE WHEN T1.course_id = 5 THEN T1.avg_score_x ELSE 0 END) AS avg_score_x_5,
		SUM(CASE WHEN T1.course_id = 6 THEN T1.avg_score_x ELSE 0 END) AS avg_score_x_6,
		SUM(CASE WHEN T1.course_id = 7 THEN T1.avg_score_x ELSE 0 END) AS avg_score_x_7,
		SUM(CASE WHEN T1.course_id = 8 THEN T1.avg_score_x ELSE 0 END) AS avg_score_x_8,
		SUM(CASE WHEN T1.course_id = 9 THEN T1.avg_score_x ELSE 0 END) AS avg_score_x_9,
		SUM(CASE WHEN T1.course_id = 100 THEN T1.avg_score_x ELSE 0 END) AS avg_score_x_100
	FROM
	(
		SELECT T1.direction_course_id, 9999 AS sys_org_id, 100 AS course_id, COUNT(1) total_count, AVG(T1.score) avg_score, AVG(T1.score_x) avg_score_x
		FROM ncee_student AS T1
		WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId
		GROUP BY T1.direction_course_id
		
		UNION ALL
		SELECT T1.direction_course_id, T1.sys_org_id, 100 AS course_id, COUNT(1) total_count, AVG(T1.score) avg_score, AVG(T1.score_x) avg_score_x
		FROM ncee_student AS T1
		WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId
		GROUP BY T1.direction_course_id, T1.sys_org_id
		
		UNION ALL
		SELECT T1.direction_course_id, 9999 AS sys_org_id, T2.course_id, COUNT(1) total_count, AVG(T2.score) avg_score, AVG(T2.score_x) avg_score_x
		FROM ncee_student AS T1
		JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
		WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId
		GROUP BY T1.direction_course_id, T2.course_id
		
		UNION ALL
		SELECT T1.direction_course_id, T1.sys_org_id, T2.course_id, COUNT(1) total_count, AVG(T2.score) avg_score, AVG(T2.score_x) avg_score_x
		FROM ncee_student AS T1
		JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
		WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId
		GROUP BY T1.direction_course_id, T1.sys_org_id, T2.course_id
	) AS T1
	GROUP BY T1.direction_course_id, T1.sys_org_id
) AS T1
LEFT JOIN sys_org AS T2 ON T1.sys_org_id = T2.id
ORDER BY T1.direction_course_id, T1.sys_org_id
", new { NceePlanId = nceePlanId, DirectionCourseId = directionCourseId });

        return dt;
    }
    /// <summary>
    /// 获取有效分上线（单上线）
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <returns></returns>
    private async Task<DataTable> GetLineTotalTable(int nceePlanId)
    {
        var dt = await _rep.SqlQueryAsync(@"
SELECT IFNULL(T2.short_name, '合计') AS sys_org_short_name, T1.*,
	T3.line_count AS line_count_100, 
	T3.total_count AS total_count_100, 
	T3.line_rate AS line_rate_100
FROM
(
	SELECT T1.type, T1.direction_course_id, IFNULL(T1.sys_org_id, 0) AS sys_org_id, T1.ncee_line_level,	
		MAX(IF(T1.course_id = 1, T1.total_count, NULL)) AS total_count_1,
		MAX(IF(T1.course_id = 2, T1.total_count, NULL)) AS total_count_2,
		MAX(IF(T1.course_id = 3, T1.total_count, NULL)) AS total_count_3,
		MAX(IF(T1.course_id = 4, T1.total_count, NULL)) AS total_count_4,
        MAX(IF(T1.course_id = 5, T1.total_count, NULL)) AS total_count_5,
        MAX(IF(T1.course_id = 6, T1.total_count, NULL)) AS total_count_6,
        MAX(IF(T1.course_id = 7, T1.total_count, NULL)) AS total_count_7,
		MAX(IF(T1.course_id = 8, T1.total_count, NULL)) AS total_count_8,
        MAX(IF(T1.course_id = 9, T1.total_count, NULL)) AS total_count_9,
		MAX(IF(T1.course_id = 101, T1.total_count, NULL)) AS total_count_101,
		
		MAX(IF(T1.course_id = 1, T1.line_count, NULL)) AS line_count_1,
		MAX(IF(T1.course_id = 2, T1.line_count, NULL)) AS line_count_2,
		MAX(IF(T1.course_id = 3, T1.line_count, NULL)) AS line_count_3,
		MAX(IF(T1.course_id = 4, T1.line_count, NULL)) AS line_count_4,
		MAX(IF(T1.course_id = 5, T1.line_count, NULL)) AS line_count_5,
		MAX(IF(T1.course_id = 6, T1.line_count, NULL)) AS line_count_6,
		MAX(IF(T1.course_id = 7, T1.line_count, NULL)) AS line_count_7,
		MAX(IF(T1.course_id = 8, T1.line_count, NULL)) AS line_count_8,
		MAX(IF(T1.course_id = 9, T1.line_count, NULL)) AS line_count_9,
		MAX(IF(T1.course_id = 101, T1.line_count, NULL)) AS line_count_101,
		
		MAX(IF(T1.course_id = 1, T1.line_rate, NULL)) AS line_rate_1,
		MAX(IF(T1.course_id = 2, T1.line_rate, NULL)) AS line_rate_2,
		MAX(IF(T1.course_id = 3, T1.line_rate, NULL)) AS line_rate_3,
		MAX(IF(T1.course_id = 4, T1.line_rate, NULL)) AS line_rate_4,
		MAX(IF(T1.course_id = 5, T1.line_rate, NULL)) AS line_rate_5,
		MAX(IF(T1.course_id = 6, T1.line_rate, NULL)) AS line_rate_6,
		MAX(IF(T1.course_id = 7, T1.line_rate, NULL)) AS line_rate_7,
		MAX(IF(T1.course_id = 8, T1.line_rate, NULL)) AS line_rate_8,
		MAX(IF(T1.course_id = 9, T1.line_rate, NULL)) AS line_rate_9,
		MAX(IF(T1.course_id = 101, T1.line_rate, NULL)) AS line_rate_101
	FROM ncee_line_course AS T1
	WHERE T1.ncee_plan_id = @nceePlanId AND (T1.type = 1 OR T1.type = 4) AND T1.is_double_line = 0
	GROUP BY T1.type, T1.direction_course_id, T1.sys_org_id, T1.ncee_line_level
) AS T1
LEFT JOIN sys_org AS T2 ON T1.sys_org_id = T2.id
LEFT JOIN
(
	SELECT type, IFNULL(sys_org_id, 0) AS sys_org_id, ncee_line_level, direction_course_id, line_count, total_count, line_rate
	FROM ncee_line_total AS T1
	WHERE T1.ncee_plan_id = @nceePlanId AND (T1.type = 1 OR T1.type = 4)
) AS T3 ON T1.type = T3.type AND T1.sys_org_id = T3.sys_org_id AND T1.ncee_line_level = T3.ncee_line_level AND T1.direction_course_id = T3.direction_course_id
ORDER BY T1.ncee_line_level, T1.direction_course_id, T1.type, T1.sys_org_id
", new { NceePlanId = nceePlanId });

        return dt;
    }
    /// <summary>
    /// 获取有效分双上线数据
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <returns></returns>
    private async Task<DataTable> GetLineCourseTable(int nceePlanId)
    {
        var dt = await _rep.SqlQueryAsync(@"
SELECT IFNULL(T2.short_name, '合计') AS sys_org_short_name, T1.*	
FROM
(
	SELECT T1.type, T1.direction_course_id, IFNULL(T1.sys_org_id, 0) AS sys_org_id, T1.ncee_line_level,	
		MAX(IF(T1.course_id = 1, T1.total_count, NULL)) AS total_count_1,
		MAX(IF(T1.course_id = 2, T1.total_count, NULL)) AS total_count_2,
		MAX(IF(T1.course_id = 3, T1.total_count, NULL)) AS total_count_3,
		MAX(IF(T1.course_id = 4, T1.total_count, NULL)) AS total_count_4,
        MAX(IF(T1.course_id = 5, T1.total_count, NULL)) AS total_count_5,
        MAX(IF(T1.course_id = 6, T1.total_count, NULL)) AS total_count_6,
        MAX(IF(T1.course_id = 7, T1.total_count, NULL)) AS total_count_7,
		MAX(IF(T1.course_id = 8, T1.total_count, NULL)) AS total_count_8,
        MAX(IF(T1.course_id = 9, T1.total_count, NULL)) AS total_count_9,
		MAX(IF(T1.course_id = 101, T1.total_count, NULL)) AS total_count_101,
		
		MAX(IF(T1.course_id = 1, T1.line_count, NULL)) AS line_count_1,
		MAX(IF(T1.course_id = 2, T1.line_count, NULL)) AS line_count_2,
		MAX(IF(T1.course_id = 3, T1.line_count, NULL)) AS line_count_3,
		MAX(IF(T1.course_id = 4, T1.line_count, NULL)) AS line_count_4,
		MAX(IF(T1.course_id = 5, T1.line_count, NULL)) AS line_count_5,
		MAX(IF(T1.course_id = 6, T1.line_count, NULL)) AS line_count_6,
		MAX(IF(T1.course_id = 7, T1.line_count, NULL)) AS line_count_7,
		MAX(IF(T1.course_id = 8, T1.line_count, NULL)) AS line_count_8,
		MAX(IF(T1.course_id = 9, T1.line_count, NULL)) AS line_count_9,
		MAX(IF(T1.course_id = 101, T1.line_count, NULL)) AS line_count_101,
		
		MAX(IF(T1.course_id = 1, T1.line_rate, NULL)) AS line_rate_1,
		MAX(IF(T1.course_id = 2, T1.line_rate, NULL)) AS line_rate_2,
		MAX(IF(T1.course_id = 3, T1.line_rate, NULL)) AS line_rate_3,
		MAX(IF(T1.course_id = 4, T1.line_rate, NULL)) AS line_rate_4,
		MAX(IF(T1.course_id = 5, T1.line_rate, NULL)) AS line_rate_5,
		MAX(IF(T1.course_id = 6, T1.line_rate, NULL)) AS line_rate_6,
		MAX(IF(T1.course_id = 7, T1.line_rate, NULL)) AS line_rate_7,
		MAX(IF(T1.course_id = 8, T1.line_rate, NULL)) AS line_rate_8,
		MAX(IF(T1.course_id = 9, T1.line_rate, NULL)) AS line_rate_9,
		MAX(IF(T1.course_id = 101, T1.line_rate, NULL)) AS line_rate_101
	FROM ncee_line_course AS T1
	WHERE T1.ncee_plan_id = @nceePlanId AND (T1.type = 1 OR T1.type = 4) AND T1.is_double_line = 1
	GROUP BY T1.type, T1.direction_course_id, T1.sys_org_id, T1.ncee_line_level
) AS T1
LEFT JOIN sys_org AS T2 ON T1.sys_org_id = T2.id
ORDER BY T1.ncee_line_level, T1.direction_course_id, T1.type, T1.sys_org_id 
", new { NceePlanId = nceePlanId });

        return dt;
    }
    /// <summary>
    /// 获取机构组合班级有效分上线（单上线，含各单科）
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <param name="sysOrgId"></param>
    /// <returns></returns>
    private async Task<DataTable> GetOrgLineTotalTable(int nceePlanId, short sysOrgId)
    {
        var dt = await _rep.SqlQueryAsync(@"
SELECT T2.short_name AS sys_org_name,
	T1.*, 
	T3.line_count AS line_count_100, 
	T3.total_count AS total_count_100, 
	T3.line_rate AS line_rate_100	
FROM
(
	SELECT T1.type, 
		T1.sys_org_id,
		T1.direction_course_id, 
		T1.ncee_course_comb_id,
		T1.class_number,
		T1.ncee_line_level,	
		MAX(IF(T1.course_id = 1, T1.total_count, NULL)) AS total_count_1,
		MAX(IF(T1.course_id = 2, T1.total_count, NULL)) AS total_count_2,
		MAX(IF(T1.course_id = 3, T1.total_count, NULL)) AS total_count_3,
		MAX(IF(T1.course_id = 4, T1.total_count, NULL)) AS total_count_4,
		MAX(IF(T1.course_id = 8, T1.total_count, NULL)) AS total_count_8,
		MAX(IF(T1.course_id = 101, T1.total_count, NULL)) AS total_count_101,
		
		MAX(IF(T1.course_id = 1, T1.line_count, NULL)) AS line_count_1,
		MAX(IF(T1.course_id = 2, T1.line_count, NULL)) AS line_count_2,
		MAX(IF(T1.course_id = 3, T1.line_count, NULL)) AS line_count_3,
		MAX(IF(T1.course_id = 4, T1.line_count, NULL)) AS line_count_4,
		MAX(IF(T1.course_id = 8, T1.line_count, NULL)) AS line_count_8,
		MAX(IF(T1.course_id = 101, T1.line_count, NULL)) AS line_count_101,
		
		MAX(IF(T1.course_id = 1, T1.line_rate, NULL)) AS line_rate_1,
		MAX(IF(T1.course_id = 2, T1.line_rate, NULL)) AS line_rate_2,
		MAX(IF(T1.course_id = 3, T1.line_rate, NULL)) AS line_rate_3,
		MAX(IF(T1.course_id = 4, T1.line_rate, NULL)) AS line_rate_4,
		MAX(IF(T1.course_id = 8, T1.line_rate, NULL)) AS line_rate_8,
		MAX(IF(T1.course_id = 101, T1.line_rate, NULL)) AS line_rate_101
	FROM ncee_line_course AS T1
	WHERE T1.ncee_plan_id = @nceePlanId AND T1.sys_org_id = @sysOrgId AND T1.type < 4 AND T1.is_double_line = 0
	GROUP BY T1.type, T1.direction_course_id, T1.sys_org_id, T1.ncee_line_level, T1.ncee_course_comb_id, T1.class_number
) AS T1
LEFT JOIN sys_org AS T2 ON T1.sys_org_id = T2.id
LEFT JOIN
(
	SELECT sys_org_id, type, ncee_line_level, direction_course_id, ncee_course_comb_id, class_number,line_count, total_count, line_rate
	FROM ncee_line_total AS T1
	WHERE T1.ncee_plan_id = @nceePlanId AND T1.sys_org_id = @sysOrgId AND T1.type < 4
) AS T3 
ON T1.type = T3.type AND T1.sys_org_id = T3.sys_org_id AND 
	T1.ncee_line_level = T3.ncee_line_level AND 
	T1.direction_course_id = T3.direction_course_id AND 
	IFNULL(T1.ncee_course_comb_id, 0) = IFNULL(T3.ncee_course_comb_id, 0) AND 
	IFNULL(T1.class_number, 0) = IFNULL(T3.class_number, 0)
ORDER BY T1.ncee_line_level, T1.direction_course_id, T1.type, T1.ncee_course_comb_id
", new { NceePlanId = nceePlanId, SysOrgId = sysOrgId });

        return dt;
    }
    /// <summary>
    /// 获取机构组合班级有效分上线（总分上线，不含单科）
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <param name="sysOrgId">0表示全部</param>
    /// <returns></returns>
    private async Task<DataTable> GetOrgLineTotalTable2(int nceePlanId, short sysOrgId = 0)
    {
        var dt = await _rep.SqlQueryAsync(@"
SELECT T1.*, IFNULL(T1.total_count_1, IFNULL(T1.total_count_2, T1.total_count_3)) AS total_count
FROM
(
    SELECT 
	    T1.direction_course_id, 
	    T1.type,
	    T1.sys_org_id,
	    T3.short_name AS sys_org_name,
	    T1.ncee_course_comb_id,
	    T2.short_name AS ncee_course_comb_name,
	    T1.class_number,
	    MAX(CASE WHEN T1.ncee_line_level = 1 THEN T1.line_count ELSE NULL END) AS line_count_1,
	    MAX(CASE WHEN T1.ncee_line_level = 1 THEN T1.line_rate ELSE NULL END) AS line_rate_1,
	    MAX(CASE WHEN T1.ncee_line_level = 1 THEN T1.total_count ELSE NULL END) AS total_count_1,
	    MAX(CASE WHEN T1.ncee_line_level = 2 THEN T1.line_count ELSE NULL END) AS line_count_2,
	    MAX(CASE WHEN T1.ncee_line_level = 2 THEN T1.line_rate ELSE NULL END) AS line_rate_2,
	    MAX(CASE WHEN T1.ncee_line_level = 2 THEN T1.total_count ELSE NULL END) AS total_count_2,
	    MAX(CASE WHEN T1.ncee_line_level = 3 THEN T1.line_count ELSE NULL END) AS line_count_3,
	    MAX(CASE WHEN T1.ncee_line_level = 3 THEN T1.line_rate ELSE NULL END) AS line_rate_3,
	    MAX(CASE WHEN T1.ncee_line_level = 3 THEN T1.total_count ELSE NULL END) AS total_count_3
    FROM ncee_line_total AS T1
    LEFT JOIN ncee_course_comb AS T2 ON T1.ncee_course_comb_id = T2.id
    LEFT JOIN sys_org AS T3 ON T1.sys_org_id = T3.id
    WHERE T1.ncee_plan_id = @nceePlanId AND T1.type < 4 AND (T1.sys_org_id = @sysOrgId OR @sysOrgId = 0)
    GROUP BY T1.direction_course_id, T1.type, T1.sys_org_id, T3.short_name, T1.ncee_course_comb_id, T2.short_name, T1.class_number
) AS T1
ORDER BY T1.direction_course_id, T1.sys_org_id, T1.ncee_course_comb_id, T1.class_number
", new { NceePlanId = nceePlanId, SysOrgId = sysOrgId });

        return dt;
    }
    /// <summary>
    /// 获取机构组合班级有效分双上线数据
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <param name="sysOrgId"></param>
    /// <returns></returns>
    private async Task<DataTable> GetOrgLineCourseTable(int nceePlanId, short sysOrgId)
    {
        var dt = await _rep.SqlQueryAsync(@"
SELECT T2.short_name AS sys_org_name, T1.*	
FROM
(
	SELECT T1.type, 
		T1.sys_org_id,
		T1.direction_course_id,
		T1.ncee_course_comb_id,
		T1.class_number,
		T1.ncee_line_level,	
		MAX(IF(T1.course_id = 1, T1.total_count, NULL)) AS total_count_1,
		MAX(IF(T1.course_id = 2, T1.total_count, NULL)) AS total_count_2,
		MAX(IF(T1.course_id = 3, T1.total_count, NULL)) AS total_count_3,
		MAX(IF(T1.course_id = 4, T1.total_count, NULL)) AS total_count_4,
		MAX(IF(T1.course_id = 101, T1.total_count, NULL)) AS total_count_101,
		
		MAX(IF(T1.course_id = 1, T1.line_count, NULL)) AS line_count_1,
		MAX(IF(T1.course_id = 2, T1.line_count, NULL)) AS line_count_2,
		MAX(IF(T1.course_id = 3, T1.line_count, NULL)) AS line_count_3,
		MAX(IF(T1.course_id = 4, T1.line_count, NULL)) AS line_count_4,
		MAX(IF(T1.course_id = 101, T1.line_count, NULL)) AS line_count_101,
		
		MAX(IF(T1.course_id = 1, T1.line_rate, NULL)) AS line_rate_1,
		MAX(IF(T1.course_id = 2, T1.line_rate, NULL)) AS line_rate_2,
		MAX(IF(T1.course_id = 3, T1.line_rate, NULL)) AS line_rate_3,
		MAX(IF(T1.course_id = 4, T1.line_rate, NULL)) AS line_rate_4,
		MAX(IF(T1.course_id = 101, T1.line_rate, NULL)) AS line_rate_101
	FROM ncee_line_course AS T1
	WHERE T1.ncee_plan_id = @nceePlanId AND T1.sys_org_id = @sysOrgId AND T1.type < 4 AND T1.is_double_line = 1
	GROUP BY T1.type, T1.direction_course_id, T1.sys_org_id, T1.ncee_line_level, T1.ncee_course_comb_id, T1.class_number
) AS T1
LEFT JOIN sys_org AS T2 ON T1.sys_org_id = T2.id
ORDER BY T1.ncee_line_level, T1.direction_course_id, T1.type, T1.sys_org_id 
", new { NceePlanId = nceePlanId, SysOrgId = sysOrgId });

        return dt;
    }
    /// <summary>
    /// 获取机构组合班级有效分双上线数据（科目未转置）
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <param name="sysOrgId">0表示全部</param>
    /// <returns></returns>
    private async Task<DataTable> GetOrgLineCourseTable2(int nceePlanId, short sysOrgId = 0)
    {
        var dt = await _rep.SqlQueryAsync(@"
SELECT T1.*, IFNULL(T1.total_count_1, IFNULL(T1.total_count_2, T1.total_count_3)) AS total_count
FROM
(
    SELECT 
	    T1.direction_course_id,
	    T1.course_id,
	    T1.type,
	    T1.sys_org_id,
	    T3.short_name AS sys_org_name,
	    T1.ncee_course_comb_id,
	    T2.short_name AS ncee_course_comb_name,
	    T1.class_number,
	    MAX(CASE WHEN T1.ncee_line_level = 1 THEN T1.line_count ELSE NULL END) AS line_count_1,
	    MAX(CASE WHEN T1.ncee_line_level = 1 THEN T1.line_rate ELSE NULL END) AS line_rate_1,
	    MAX(CASE WHEN T1.ncee_line_level = 1 THEN T1.total_count ELSE NULL END) AS total_count_1,
	    MAX(CASE WHEN T1.ncee_line_level = 2 THEN T1.line_count ELSE NULL END) AS line_count_2,
	    MAX(CASE WHEN T1.ncee_line_level = 2 THEN T1.line_rate ELSE NULL END) AS line_rate_2,
	    MAX(CASE WHEN T1.ncee_line_level = 2 THEN T1.total_count ELSE NULL END) AS total_count_2,
	    MAX(CASE WHEN T1.ncee_line_level = 3 THEN T1.line_count ELSE NULL END) AS line_count_3,
	    MAX(CASE WHEN T1.ncee_line_level = 3 THEN T1.line_rate ELSE NULL END) AS line_rate_3,
	    MAX(CASE WHEN T1.ncee_line_level = 3 THEN T1.total_count ELSE NULL END) AS total_count_3
    FROM ncee_line_course AS T1
    LEFT JOIN ncee_course_comb AS T2 ON T1.ncee_course_comb_id = T2.id
    LEFT JOIN sys_org AS T3 ON T1.sys_org_id = T3.id
    WHERE T1.ncee_plan_id = @nceePlanId AND T1.type < 4 AND T1.is_double_line = 1 AND (T1.sys_org_id = @sysOrgId OR @sysOrgId = 0)
    GROUP BY T1.direction_course_id, T1.course_id, T1.type, T1.sys_org_id, T3.short_name, T1.ncee_course_comb_id, T2.short_name, T1.class_number
) AS T1
ORDER BY T1.direction_course_id, T1.course_id, T1.sys_org_id, T1.ncee_course_comb_id, T1.class_number
", new { NceePlanId = nceePlanId, SysOrgId = sysOrgId });

        return dt;
    }
    /// <summary>
    /// 获取总分分数段统计表
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <param name="directionCourseId"></param>
    /// <param name="orgList"></param>
    /// <returns></returns>
    private async Task<DataTable> GetTotalScoreRangeTable(int nceePlanId, short directionCourseId, List<SysOrgOutput> orgList)
    {
        var orgSumSelects = orgList.Select(t => @$"
	SUM(CASE WHEN T2.sys_org_id = {t.Id} THEN T2.total_count ELSE 0 END) AS total_count_{t.Id}");
        var orgSum = string.Join(",", orgSumSelects);

        var dt = await _rep.SqlQueryAsync(@$"
SELECT T1.id AS exam_score_range_id, T1.`name` AS exam_score_range_name,{orgSum},
	SUM(CASE WHEN T2.sys_org_id = 9998 THEN T2.total_count ELSE 0 END) AS total_count_range_current,
	SUM(CASE WHEN T2.sys_org_id = 9999 THEN T2.total_count ELSE 0 END) AS total_count_range_sum
FROM exam_score_range AS T1
LEFT JOIN
(
	-- 全部分段统计
	SELECT 9998 AS sys_org_id, T1.exam_score_range_id, COUNT(T1.exam_score_range_id) AS total_count
	FROM
	(
		SELECT get_exam_score_range_id_s(1, 10, 200, 750, T1.score_x) AS exam_score_range_id
		FROM ncee_student AS T1
		WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId
	) AS T1
	GROUP BY T1.exam_score_range_id
	
	UNION ALL
	-- 全部分段累计
	SELECT 9999 AS sys_org_id, T1.exam_score_range_id, 
		SUM(T1.total_count) OVER (ORDER BY T1.sequence ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_count
	FROM
	(
		SELECT T1.*, T2.sequence
		FROM
		(
			SELECT T1.exam_score_range_id, SUM(T1.count) AS total_count
			FROM
			(
				SELECT get_exam_score_range_id_s(1, 10, 200, 750, T1.score_x) AS exam_score_range_id, 1 AS count
				FROM ncee_student AS T1
				WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId
				UNION ALL
				SELECT id, 0 FROM exam_score_range WHERE type = 1
			) AS T1		
			GROUP BY T1.exam_score_range_id
		) AS T1
		LEFT JOIN exam_score_range AS T2 ON T1.exam_score_range_id = T2.id
	) AS T1
	GROUP BY T1.exam_score_range_id
	
	UNION ALL
	-- 机构分段统计
	SELECT T1.sys_org_id, T1.exam_score_range_id, COUNT(T1.exam_score_range_id) AS total_count
	FROM
	(
		SELECT T1.sys_org_id, get_exam_score_range_id_s(1, 10, 200, 750, T1.score_x) AS exam_score_range_id
		FROM ncee_student AS T1
		WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId
	) AS T1
	GROUP BY T1.sys_org_id, T1.exam_score_range_id
) AS T2 ON T1.id = T2.exam_score_range_id
WHERE T1.type = 1
GROUP BY T1.id, T1.`name`
ORDER BY T1.sequence
", new { NceePlanId = nceePlanId, DirectionCourseId = directionCourseId });

        return dt;
    }
    /// <summary>
	/// 获取总分分数段统计表（未选科）
	/// </summary>
	/// <param name="nceePlanId"></param>
	/// <param name="orgList"></param>
	/// <returns></returns>
	private async Task<DataTable> GetTotalScoreRangeTableUnselected(int nceePlanId, List<SysOrgOutput> orgList)
    {
        var orgSumSelects = orgList.Select(t => @$"
	SUM(CASE WHEN T2.sys_org_id = {t.Id} THEN T2.total_count ELSE 0 END) AS total_count_{t.Id}");
        var orgSum = string.Join(",", orgSumSelects);

        var dt = await _rep.SqlQueryAsync(@$"
SELECT T1.id AS exam_score_range_id, T1.`name` AS exam_score_range_name,{orgSum},
	SUM(CASE WHEN T2.sys_org_id = 9998 THEN T2.total_count ELSE 0 END) AS total_count_range_current,
	SUM(CASE WHEN T2.sys_org_id = 9999 THEN T2.total_count ELSE 0 END) AS total_count_range_sum
FROM exam_score_range AS T1
LEFT JOIN
(
	-- 全部分段统计
	SELECT 9998 AS sys_org_id, T1.exam_score_range_id, COUNT(T1.exam_score_range_id) AS total_count
	FROM
	(
		SELECT get_exam_score_range_id_s(4, 10, 240, 900, T1.score_x) AS exam_score_range_id
		FROM ncee_student AS T1
		WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId
	) AS T1
	GROUP BY T1.exam_score_range_id
	
	UNION ALL
	-- 全部分段累计
	SELECT 9999 AS sys_org_id, T1.exam_score_range_id, 
		SUM(T1.total_count) OVER (ORDER BY T1.sequence ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_count
	FROM
	(
		SELECT T1.*, T2.sequence
		FROM
		(
			SELECT T1.exam_score_range_id, SUM(T1.count) AS total_count
			FROM
			(
				SELECT get_exam_score_range_id_s(4, 10, 240, 900, T1.score_x) AS exam_score_range_id, 1 AS count
				FROM ncee_student AS T1
				WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId
				UNION ALL
				SELECT id, 0 FROM exam_score_range WHERE type = 4
			) AS T1		
			GROUP BY T1.exam_score_range_id
		) AS T1
		LEFT JOIN exam_score_range AS T2 ON T1.exam_score_range_id = T2.id
	) AS T1
	GROUP BY T1.exam_score_range_id
	
	UNION ALL
	-- 机构分段统计
	SELECT T1.sys_org_id, T1.exam_score_range_id, COUNT(T1.exam_score_range_id) AS total_count
	FROM
	(
		SELECT T1.sys_org_id, get_exam_score_range_id_s(4, 10, 240, 900, T1.score_x) AS exam_score_range_id
		FROM ncee_student AS T1
		WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId
	) AS T1
	GROUP BY T1.sys_org_id, T1.exam_score_range_id
) AS T2 ON T1.id = T2.exam_score_range_id
WHERE T1.type = 4
GROUP BY T1.id, T1.`name`
ORDER BY T1.sequence
", new { NceePlanId = nceePlanId, DirectionCourseId = 99 });

        return dt;
    }
    /// <summary>
    /// 获取单科分数段统计表
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <param name="directionCourseId"></param>
    /// <param name="courseId"></param>
    /// <param name="orgList"></param>
    /// <returns></returns>
    private async Task<DataTable> GetCourseScoreRangeTable(int nceePlanId, short directionCourseId, short courseId, List<SysOrgOutput> orgList)
    {
        int type = 3; // 总分100分
        int rangeScore = 5;
        int highScore = 100;
        if (courseId == 1 || courseId == 2 || courseId == 3)
        {
            type = 2; // 总分150分
            highScore = 150;
        }

        var orgSumSelects = orgList.Select(t => @$"
SUM(CASE WHEN T2.sys_org_id = {t.Id} THEN T2.total_count ELSE 0 END) AS total_count_{t.Id}");
        var orgSum = string.Join(",", orgSumSelects);

        var dt = await _rep.SqlQueryAsync($@"
SELECT T1.id AS exam_score_range_id, T1.`name` AS exam_score_range_name,{orgSum},
	SUM(CASE WHEN T2.sys_org_id = 9998 THEN T2.total_count ELSE 0 END) AS total_count_range_current,
	SUM(CASE WHEN T2.sys_org_id = 9999 THEN T2.total_count ELSE 0 END) AS total_count_range_sum
FROM exam_score_range AS T1
LEFT JOIN
(
	-- 全部分段统计
	SELECT 9998 AS sys_org_id, T1.exam_score_range_id, COUNT(T1.exam_score_range_id) AS total_count
	FROM
	(
		SELECT get_exam_score_range_id_s(@type, @rangeScore, @rangeScore, @highScore, T2.score_x) AS exam_score_range_id
		FROM ncee_student AS T1
		JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
		WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T2.course_id = @courseId
	) AS T1
	GROUP BY T1.exam_score_range_id
	
	UNION ALL
	-- 全部分段累计
	SELECT 9999 AS sys_org_id, T1.exam_score_range_id, 
		SUM(T1.total_count) OVER (ORDER BY T1.sequence ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_count
	FROM
	(
        SELECT T1.*, T2.sequence
		FROM
		(
			SELECT T1.exam_score_range_id, SUM(T1.count) AS total_count
			FROM
			(
				SELECT get_exam_score_range_id_s(@type, @rangeScore, @rangeScore, @highScore, T2.score_x) AS exam_score_range_id, 1 AS count
			    FROM ncee_student AS T1
			    JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
			    WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T2.course_id = @courseId
			    UNION ALL
			    SELECT id, 0 FROM exam_score_range WHERE type = @type
			) AS T1		
			GROUP BY T1.exam_score_range_id
		) AS T1
		LEFT JOIN exam_score_range AS T2 ON T1.exam_score_range_id = T2.id
	) AS T1
	GROUP BY T1.exam_score_range_id
	
	UNION ALL
	-- 机构分段统计
	SELECT T1.sys_org_id, T1.exam_score_range_id, COUNT(T1.exam_score_range_id) AS total_count
	FROM
	(
		SELECT T1.sys_org_id, get_exam_score_range_id_s(@type, @rangeScore, @rangeScore, @highScore, T2.score_x) AS exam_score_range_id
		FROM ncee_student AS T1
		JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
		WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T2.course_id = @courseId
	) AS T1
	GROUP BY T1.sys_org_id, T1.exam_score_range_id
) AS T2 ON T1.id = T2.exam_score_range_id
WHERE T1.type = @type
GROUP BY T1.id, T1.`name`
ORDER BY T1.sequence
", new { NceePlanId = nceePlanId, DirectionCourseId = directionCourseId, CourseId = courseId, Type = type, RangeScore = rangeScore, HighScore = highScore });

        return dt;
    }
    /// <summary>
	/// 获取单科分数段统计表（未选科）
	/// </summary>
	/// <param name="nceePlanId"></param>
	/// <param name="courseId"></param>
	/// <param name="orgList"></param>
	/// <returns></returns>
	private async Task<DataTable> GetCourseScoreRangeTableUnselected(int nceePlanId, short courseId, List<SysOrgOutput> orgList)
    {
        return await GetCourseScoreRangeTable(nceePlanId, 99, courseId, orgList);
    }
    /// <summary>
    /// 获取各科原始分转换区间表
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <returns></returns>
    private async Task<DataTable> GetConvertRangeTable(int nceePlanId)
    {
        var dt = await _rep.SqlQueryAsync(@"
SELECT T2.`name` AS course_name, T1.*
FROM
(
	SELECT course_id,
		MAX(IF(ncee_convert_grade_id = 1, min_score, NULL)) AS min_score_a,
		MAX(IF(ncee_convert_grade_id = 1, max_score, NULL)) AS max_score_a,
		MAX(IF(ncee_convert_grade_id = 2, min_score, NULL)) AS min_score_b,
		MAX(IF(ncee_convert_grade_id = 2, max_score, NULL)) AS max_score_b,
		MAX(IF(ncee_convert_grade_id = 3, min_score, NULL)) AS min_score_c,
		MAX(IF(ncee_convert_grade_id = 3, max_score, NULL)) AS max_score_c,
		MAX(IF(ncee_convert_grade_id = 4, min_score, NULL)) AS min_score_d,
		MAX(IF(ncee_convert_grade_id = 4, max_score, NULL)) AS max_score_d,
		MAX(IF(ncee_convert_grade_id = 5, min_score, NULL)) AS min_score_e,
		MAX(IF(ncee_convert_grade_id = 5, max_score, NULL)) AS max_score_e
	FROM ncee_convert_range
	WHERE ncee_plan_id = @nceePlanId
	GROUP BY course_id
) AS T1 
JOIN base_course AS T2 ON T1.course_id = T2.id
", new { NceePlanId = nceePlanId });

        return dt;
    }
    /// <summary>
    /// 获取转换分明细表
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <param name="directionCourseId"></param>
    /// <param name="sysOrgId"></param>
    /// <returns></returns>
    private async Task<DataTable> GetConvertScoreTable(int nceePlanId, short directionCourseId, short sysOrgId)
    {
        var dt = await _rep.SqlQueryAsync(@"
SELECT 
	T2.`name` AS sys_org_name,
    T2.short_name AS sys_org_short_name,
	T3.`name` AS direction_course_name,
	T4.short_name AS ncee_course_comb_short_name,
	T1.*,
    IFNULL(T1.score_5, 0) + IFNULL(T1.score_6, 0) + IFNULL(T1.score_7, 0) + IFNULL(T1.score_9, 0) AS comb_score,
	IFNULL(T1.score_x_5, 0) + IFNULL(T1.score_x_6, 0) + IFNULL(T1.score_x_7, 0) + IFNULL(T1.score_x_9, 0) AS comb_score_x
FROM
(
	SELECT 
		T1.sys_org_id,		
		T1.direction_course_id,		
		T1.ncee_course_comb_id,		
		T1.class_number,
		T1.id, 
		T1.exam_number, 
		T1.`name`,

		MAX(T1.score) AS score, 
		MAX(T1.score_x) AS score_x,

        MAX(T1.order_in_total) AS order_in_total,
		MAX(T1.order_in_total_x) AS order_in_total_x,
		MAX(T1.order_in_org) AS order_in_org,
		MAX(T1.order_in_org_x) AS order_in_org_x,

		MAX(CASE WHEN T2.course_id = 1 THEN T2.score ELSE NULL END) AS score_1,
		MAX(CASE WHEN T2.course_id = 2 THEN T2.score ELSE NULL END) AS score_2,
		MAX(CASE WHEN T2.course_id = 3 THEN T2.score ELSE NULL END) AS score_3,
		MAX(CASE WHEN T2.course_id = 4 THEN T2.score ELSE NULL END) AS score_4,	
		MAX(CASE WHEN T2.course_id = 5 THEN T2.score ELSE NULL END) AS score_5,
		MAX(CASE WHEN T2.course_id = 6 THEN T2.score ELSE NULL END) AS score_6,
		MAX(CASE WHEN T2.course_id = 7 THEN T2.score ELSE NULL END) AS score_7,
		MAX(CASE WHEN T2.course_id = 8 THEN T2.score ELSE NULL END) AS score_8,
		MAX(CASE WHEN T2.course_id = 9 THEN T2.score ELSE NULL END) AS score_9,
		
		MAX(CASE WHEN T2.course_id = 5 THEN T2.score_x ELSE NULL END) AS score_x_5,
		MAX(CASE WHEN T2.course_id = 6 THEN T2.score_x ELSE NULL END) AS score_x_6,
		MAX(CASE WHEN T2.course_id = 7 THEN T2.score_x ELSE NULL END) AS score_x_7,
		MAX(CASE WHEN T2.course_id = 9 THEN T2.score_x ELSE NULL END) AS score_x_9,
		
		MAX(CASE WHEN T2.course_id = 5 THEN T2.ncee_convert_grade_name ELSE '' END) AS ncee_convert_grade_name_5,
		MAX(CASE WHEN T2.course_id = 6 THEN T2.ncee_convert_grade_name ELSE '' END) AS ncee_convert_grade_name_6,
		MAX(CASE WHEN T2.course_id = 7 THEN T2.ncee_convert_grade_name ELSE '' END) AS ncee_convert_grade_name_7,
		MAX(CASE WHEN T2.course_id = 9 THEN T2.ncee_convert_grade_name ELSE '' END) AS ncee_convert_grade_name_9
	FROM ncee_student AS T1
	JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id	
	WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND (T1.sys_org_id = @sysOrgId OR @sysOrgId = 0)
	GROUP BY T1.sys_org_id, T1.direction_course_id, T1.ncee_course_comb_id, T1.class_number, T1.id, T1.exam_number, T1.`name`
) AS T1
JOIN sys_org AS T2 ON T1.sys_org_id = T2.id
LEFT JOIN base_course AS T3 ON T1.direction_course_id = T3.id
LEFT JOIN ncee_course_comb AS T4 ON T1.ncee_course_comb_id = T4.id
ORDER BY T1.direction_course_id, T1.score_x DESC
", new { NceePlanId = nceePlanId, DirectionCourseId = directionCourseId, SysOrgId = sysOrgId });

        return dt;
    }
    /// <summary>
    /// 获取参与划线机构列表
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <returns></returns>
    private async Task<List<SysOrgOutput>> GetSysOrgList(int nceePlanId)
    {
        var ret = await _rep.Change<NceeStudent>().DetachedEntities.Where(t => t.NceePlanId == nceePlanId && t.Score > 0)
                                                                   .Select(t => t.SysOrg).Distinct()
                                                                   .OrderBy(t => t.Id)
                                                                   .ProjectToType<SysOrgOutput>().ToListAsync();
        return ret;
    }
    /// <summary>
    /// 获取划线依据
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <returns></returns>
    private async Task<DataTable> GetBaseLineTable(int nceePlanId)
    {
        var dt = await _rep.SqlQueryAsync(@"
SELECT T1.ncee_line_level, T2.`name` AS ncee_line_level_name, T1.direction_course_id, T1.line_rate, T1.line_score, T1.line_score_x
FROM ncee_base_line AS T1
JOIN (SELECT * FROM sys_dict_data WHERE sys_dict_type_id = 211) AS T2 ON T1.ncee_line_level = T2.`value`
WHERE T1.ncee_plan_id = @nceePlanId
", new { NceePlanId = nceePlanId });

        return dt;
    }
    #endregion

    #region 公有静态方法
    /// <summary>
    /// 获取等级合并名
    /// </summary>
    /// <param name="gradeNames"></param>
    /// <returns></returns>
    private static string GetMergeGradeName(List<string> gradeNames)
    {
        if (gradeNames == null)
        {
            return "";
        }

        var gns = gradeNames.Where(t => !string.IsNullOrEmpty(t) && !string.IsNullOrWhiteSpace(t)).OrderBy(t => t).ToList();

        Dictionary<string, int> cs = new();
        for (int i = 0; i < gns.Count; i++)
        {
            var g = gns[i];
            if (cs.TryGetValue(g, out int value))
            {
                cs[g] = ++value;
            }
            else
            {
                cs.Add(g, 1);
            }
        }
        string cgn = "";
        foreach (var c in cs)
        {
            cgn = $"{cgn}{c.Value}[{c.Key}]";
        }
        return cgn;
    }
    /// <summary>
    /// 导出柱状图
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="drawing"></param>
    /// <param name="anchor"></param>
    /// <param name="startDataRow"></param>
    /// <param name="endDataRow"></param>
    /// <param name="columnIndex"></param>
    /// <param name="title"></param>
    /// <param name="serieTitle"></param>
    /// <param name="catalogTitle"></param>
    /// <param name="valueTile"></param>
    private static void CreateBarChart(ISheet sheet, IDrawing drawing, IClientAnchor anchor, int startDataRow, int endDataRow, int columnIndex, string title = null, string serieTitle = null, string catalogTitle = null, string valueTile = null)
    {
        XSSFChart chart = (XSSFChart)drawing.CreateChart(anchor);
        if (!string.IsNullOrEmpty(title))
        {
            chart.SetTitle(title);
            chart.GetCTChart().title.tx.rich.p[0].pPr = new NPOI.OpenXmlFormats.Dml.CT_TextParagraphProperties
            {
                defRPr = new NPOI.OpenXmlFormats.Dml.CT_TextCharacterProperties() { sz = 1400 }
            };
        }

        IBarChartData<string, double> barChartData = chart.ChartDataFactory.CreateBarChartData<string, double>();
        IChartLegend legend = chart.GetOrCreateLegend();
        legend.Position = LegendPosition.TopRight;
        legend.IsOverlay = true;

        IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom);
        bottomAxis.MajorTickMark = AxisTickMark.None;
        IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
        leftAxis.Crosses = AxisCrosses.AutoZero;
        leftAxis.SetCrossBetween(AxisCrossBetween.Between);

        IChartDataSource<string> categoryAxis = DataSources.FromStringCellRange(sheet, new CellRangeAddress(startDataRow, endDataRow, 0, 0));
        IChartDataSource<double> valueAxis = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(startDataRow, endDataRow, columnIndex, columnIndex));
        var serie = barChartData.AddSeries(categoryAxis, valueAxis);
        if (!string.IsNullOrEmpty(serieTitle))
        {
            serie.SetTitle(serieTitle);
        }

        chart.Plot(barChartData, bottomAxis, leftAxis);

        var plotArea = chart.GetCTChart().plotArea;
        plotArea.catAx[0].txPr = new CT_TextBody();
        plotArea.catAx[0].txPr.AddNewP().pPr = new NPOI.OpenXmlFormats.Dml.CT_TextParagraphProperties()
        {
            defRPr = new NPOI.OpenXmlFormats.Dml.CT_TextCharacterProperties() { sz = 900 }
        };
        plotArea.catAx[0].majorTickMark = new CT_TickMark() { val = ST_TickMark.@out };
        plotArea.valAx[0].txPr = new CT_TextBody();
        plotArea.valAx[0].txPr.AddNewP().pPr = new NPOI.OpenXmlFormats.Dml.CT_TextParagraphProperties()
        {
            defRPr = new NPOI.OpenXmlFormats.Dml.CT_TextCharacterProperties() { sz = 900 }
        };
        plotArea.valAx[0].majorTickMark = new CT_TickMark() { val = ST_TickMark.@out };

        var barChart = plotArea.barChart.First();
        barChart.barDir = new CT_BarDir { val = ST_BarDir.col };

        if (!string.IsNullOrEmpty(catalogTitle))
        {
            var aTitle = new CT_Title
            {
                tx = new CT_Tx()
            };
            aTitle.tx.rich = new CT_TextBody();
            aTitle.tx.rich.AddNewP().AddNewR().t = catalogTitle;
            plotArea.valAx[0].title = aTitle;
        }
        if (!string.IsNullOrEmpty(valueTile))
        {
            var aTitle = new CT_Title
            {
                tx = new CT_Tx()
            };
            aTitle.tx.rich = new CT_TextBody();
            aTitle.tx.rich.AddNewP().AddNewR().t = valueTile;
            plotArea.catAx[0].title = aTitle;
        }
    }
    #endregion
}