using System.Collections;
using System.Data;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;


namespace Gs.Toolbox;

/// <summary>
///     xls操作类
/// </summary>
public class ExcelHelper
{
    /// <summary>
    ///     DataTable导出到Excel的MemoryStream
    /// </summary>
    /// <param name="dtSource">源DataTable</param>
    /// <param name="strHeaderText">表头文本</param>
    /// <returns></returns>
    public static MemoryStream Export(DataTable dtSource, string strHeaderText)
    {
        IWorkbook workbook = new HSSFWorkbook();
        var sheet = workbook.CreateSheet();
        var dateStyle = workbook.CreateCellStyle();
        var format = workbook.CreateDataFormat();
        //dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
        //取得列宽
        var arrColWidth = new int[dtSource.Columns.Count];
        foreach (DataColumn item in dtSource.Columns)
            arrColWidth[item.Ordinal] = Encoding.GetEncoding(936)
                .GetBytes(item.ColumnName).Length;
        for (var i = 0; i < dtSource.Rows.Count; i++)
            for (var j = 0; j < dtSource.Columns.Count; j++)
            {
                var intTemp = Encoding.GetEncoding(936)
                    .GetBytes(dtSource.Rows[i][j].ToString()).Length;
                if (intTemp > arrColWidth[j]) arrColWidth[j] = intTemp;
            }

        var rowIndex = 0;
        foreach (DataRow row in dtSource.Rows)
        {
            #region 新建表，填充表头，填充列头，样式

            if (rowIndex == 65535 || rowIndex == 0)
            {
                if (rowIndex != 0) sheet = workbook.CreateSheet();

                #region 列头及样式

                {
                    var headerRow = sheet.CreateRow(0);
                    var headStyle = workbook.CreateCellStyle();
                    headStyle.Alignment = HorizontalAlignment.Center;
                    var font = workbook.CreateFont();
                    font.FontHeightInPoints = 10;
                    font.Boldweight = 700;
                    headStyle.SetFont(font);
                    foreach (DataColumn column in dtSource.Columns)
                    {
                        headerRow.CreateCell(column.Ordinal)
                            .SetCellValue(column.ColumnName);
                        headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                        //设置列宽
                        sheet.SetColumnWidth(column.Ordinal,
                            (arrColWidth[column.Ordinal] + 1) * 256);
                    }
                }

                #endregion

                rowIndex = 1;
            }

            #endregion

            #region 填充内容

            foreach (DataColumn column in dtSource.Columns)
            {
                var dataRow = sheet.CreateRow(rowIndex);
                var newCell = dataRow.CreateCell(column.Ordinal);
                var drValue = row[column].ToString();
                switch (column.DataType.ToString())
                {
                    case "System.String": //字符串类型
                        newCell.SetCellValue(drValue);
                        break;
                    case "System.DateTime": //日期类型
                        DateTime dateV;
                        DateTime.TryParse(drValue, out dateV);
                        newCell.SetCellValue(dateV);
                        newCell.CellStyle = dateStyle; //格式化显示
                        break;
                    case "System.Boolean": //布尔型
                        var boolV = false;
                        bool.TryParse(drValue, out boolV);
                        newCell.SetCellValue(boolV);
                        break;
                    case "System.Int16": //整型
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        var intV = 0;
                        int.TryParse(drValue, out intV);
                        newCell.SetCellValue(intV);
                        break;
                    case "System.Decimal": //浮点型

                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(drValue, out doubV);
                        newCell.SetCellValue(doubV);
                        break;
                    case "System.DBNull": //空值处理
                        newCell.SetCellValue("");
                        break;
                    default:
                        newCell.SetCellValue("");
                        break;
                }
            }

            #endregion

            rowIndex++;
        }

        using (var ms = new MemoryStream())
        {
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            return ms;
        }
    }

    /// <summary>
    ///     DataTable导出到Excel文件
    /// </summary>
    /// <param name="dtSource">源DataTable</param>
    /// <param name="strHeaderText">表头文本</param>
    /// <param name="strFileName">保存位置</param>
    public static void Export(DataTable dtSource, string strHeaderText,
        string strFileName)
    {
        using (var ms = Export(dtSource, strHeaderText))
        {
            using (var fs = new FileStream(strFileName, FileMode.Create,
                       FileAccess.Write))
            {
                var data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
            }
        }
    }

    /// <summary>
    /// </summary>
    /// <param name="dtSource"></param>
    /// <param name="aryHeader"></param>
    /// <param name="strFileName"></param>
    public static void ExportAryHead(DataTable dtSource
        , ArrayList aryHeader
        , string strFileName
        , ArrayList aryFotter = null
        , ArrayList aryFotter2 = null
        , int fixW = 0)
    {
        var roct = aryHeader.Count;
        IWorkbook workbook = new HSSFWorkbook();
        var sheet = workbook.CreateSheet();
        //取得列宽
        var arrColWidth = new int[dtSource.Columns.Count];
        foreach (DataColumn item in dtSource.Columns)
            arrColWidth[item.Ordinal] = Encoding.GetEncoding(936)
                .GetBytes(item.ColumnName).Length;
        if (fixW <= 0)
            for (var i = 0; i < dtSource.Rows.Count; i++)
                for (var j = 0; j < dtSource.Columns.Count; j++)
                {
                    var intTemp = Encoding.GetEncoding(936)
                        .GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j]) arrColWidth[j] = intTemp;
                }

        var cusRow = sheet.CreateRow(0);
        var _rr = 0;
        foreach (string _hh in aryHeader)
        {
            cusRow = sheet.CreateRow(_rr);
            cusRow.CreateCell(0).SetCellValue(_hh);
            //cellRangAddress参数说明：起始行，结束行，起始列，结束列
            sheet.AddMergedRegion(new CellRangeAddress(_rr, _rr, 0,
                dtSource.Columns.Count - 1));
            // sheet.AddMergedRegion(new Region(0, 0, _rr, dtSource.Columns.Count - 1));
            _rr++;
        }

        //填充表头
        var headerRow = sheet.CreateRow(roct);
        var headStyle = workbook.CreateCellStyle();
        headStyle.Alignment = HorizontalAlignment.Center;
        headStyle.FillPattern = FillPattern.SolidForeground;
        headStyle.FillForegroundColor = HSSFColor.LightBlue.Index;
        var font = workbook.CreateFont();
        font.Color = HSSFColor.White.Index;
        font.FontHeightInPoints = 12;
        font.Boldweight = 700;
        headStyle.SetFont(font);
        foreach (DataColumn column in dtSource.Columns)
        {
            headerRow.CreateCell(column.Ordinal)
                .SetCellValue(column.ColumnName);
            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
            sheet.SetColumnWidth(column.Ordinal,
                (arrColWidth[column.Ordinal] + 6) * 256);
        }

        //sheet.SetAutoFilter(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); //首行筛选
        // if (fixW <= 0)
        sheet.CreateFreezePane(dtSource.Columns.Count,
            1 + aryHeader.Count); //首行冻结
        //填充内容
        var dataRow = sheet.CreateRow(roct + 1);
        for (var i = 0; i < dtSource.Rows.Count; i++)
        {
            dataRow = sheet.CreateRow(i + roct + 1);
            for (var j = 0; j < dtSource.Columns.Count; j++)
                dataRow.CreateCell(j)
                    .SetCellValue(dtSource.Rows[i][j].ToString());
        }

        //填充footer
        if (aryFotter != null && aryFotter.Count > 0)
        {
            var _rot = roct + dtSource.Rows.Count + 2;
            var ftRow = sheet.CreateRow(_rot);
            var _ftidx = 0;
            var _ftw = dtSource.Columns.Count / 2;
            foreach (string _hh in aryFotter)
            {
                var ftStyle = workbook.CreateCellStyle();
                ftStyle.FillPattern = FillPattern.SolidForeground;
                ftStyle.FillForegroundColor = HSSFColor.White.Index;
                if (_ftidx == 0)
                    ftStyle.BorderTop = BorderStyle.Thin;
                else
                    ftStyle.BorderTop = BorderStyle.None;
                if (_ftidx == aryFotter.Count - 1)
                    ftStyle.BorderBottom = BorderStyle.Thin;
                else
                    ftStyle.BorderBottom = BorderStyle.None;
                ftRow = sheet.CreateRow(_rot);
                ftRow.CreateCell(0).SetCellValue(_hh);
                ftRow.GetCell(0).CellStyle = ftStyle;
                //cellRangAddress参数说明：起始行，结束行，起始列，结束列
                sheet.AddMergedRegion(
                    new CellRangeAddress(_rot, _rot, 0, _ftw - 1));
                ftRow.CreateCell(_ftw)
                    .SetCellValue(aryFotter2[_ftidx].ToString());
                ftRow.GetCell(_ftw).CellStyle = ftStyle;
                sheet.AddMergedRegion(new CellRangeAddress(_rot, _rot, _ftw,
                    dtSource.Columns.Count - 1));
                for (var c = 0; c < dtSource.Columns.Count; c++)
                {
                    var cell = HSSFCellUtil.GetCell(ftRow, c);
                    cell.CellStyle = ftStyle;
                }

                _rot++;
                _ftidx++;
            }
        }

        //保存
        using (var ms = new MemoryStream())
        {
            using (var fs = new FileStream(strFileName, FileMode.Create,
                       FileAccess.Write))
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                var data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
            }
        }
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="workbook"></param>
    /// <param name="fontSize">字体大小</param>
    /// <param name="isBold">是否粗体</param>
    /// <param name="isCenter">是否中间</param>
    /// <param name="border">是否边框</param>
    /// <returns></returns>
    private static ICellStyle getStyle(HSSFWorkbook workbook, double fontSize = 11, bool isBold = false, bool isCenter = false, bool border = true)
    {
        IFont font = workbook.CreateFont();
        font.FontName = "宋体";
        font.FontHeightInPoints = fontSize;
        if (isBold == true)
            font.Boldweight = (short)FontBoldWeight.Bold;
        // 创建单元格样式 - 带边框和指定字体
        ICellStyle style = workbook.CreateCellStyle();
        style.SetFont(font);
        if (border == true)
        {
            style.BorderTop = BorderStyle.Thin;
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
        }
        if (isCenter == true)
            style.Alignment = HorizontalAlignment.Center;
        style.VerticalAlignment = VerticalAlignment.Center;
        return style;
    }

    /// <summary>
    /// 设置单元格的值
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="rowIdx"></param>
    /// <param name="colIdx"></param>
    /// <param name="val"></param>
    private static void setCellVal(ISheet sheet, int rowIdx, int colIdx, string val)
    {
        IRow row = sheet.GetRow(rowIdx) ?? sheet.CreateRow(rowIdx);
        ICell cell = row.GetCell(colIdx) ?? row.CreateCell(colIdx);
        cell.SetCellValue(val); // 可以设置字符串、数字等类型的数据

    }
    /// <summary>
    /// 生成首检
    /// </summary>
    /// <param name="dtSource"></param>
    public static void ExportShouJian(DataSet dtSource, string strFileName)
    {
        //行高
        int rowHeight = 25;
        //总7列
        int maxCols = 7;
        // 创建工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        ISheet sheet = workbook.CreateSheet("Sheet1");

        // 创建单元格样式 - 带边框和指定字体
        ICellStyle style = getStyle(workbook);
        ICellStyle styleHeader = getStyle(workbook, 20, true, true);
        ICellStyle style12 = getStyle(workbook, 11, false, true);

        // 设置列宽（6列）
        sheet.SetColumnWidth(0, 4000);  // 第一列稍宽
        sheet.SetColumnWidth(1, 3000);
        sheet.SetColumnWidth(2, 3000);
        sheet.SetColumnWidth(3, 3000);
        sheet.SetColumnWidth(4, 3000);
        sheet.SetColumnWidth(5, 3000);
        sheet.SetColumnWidth(6, 3000);
        // 创建20行
        for (int rowIndex = 0; rowIndex <= 6; rowIndex++)
        {
            IRow row = sheet.CreateRow(rowIndex);
            // 设置行高（所有行相同高度）
            row.HeightInPoints = rowHeight;
            // 创建6个单元格
            for (int colIndex = 0; colIndex < maxCols; colIndex++)
            {
                ICell cell = row.CreateCell(colIndex);
                if (rowIndex == 0)
                    cell.CellStyle = styleHeader;
                else
                    cell.CellStyle = style;
            }
        }
        //这是表头
        DataTable tb0 = dtSource.Tables[0];
        DataRow row0 = tb0.Rows[0];
        // 1. 第一行列合并
        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 6));
        // 2. 第二行列合并
        sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 6));
        // 第三行列合并
        sheet.AddMergedRegion(new CellRangeAddress(2, 2, 5, 6));
        // 3. 第4行中的2至6列合并
        sheet.AddMergedRegion(new CellRangeAddress(3, 3, 1, 6));
        // 4. 第5行中，2，3列合并，5，6列合并
        sheet.AddMergedRegion(new CellRangeAddress(4, 4, 1, 2));
        sheet.AddMergedRegion(new CellRangeAddress(4, 4, 4, 6));
        //****合并好后开始赋值beg**************
        setCellVal(sheet, 0, 0, row0["title"].ToString());
        setCellVal(sheet, 1, 0, row0["title2"].ToString() + row0["title3"].ToString());
        setCellVal(sheet, 2, 0, "工单号");
        setCellVal(sheet, 2, 1, row0["daaNo"].ToString());
        setCellVal(sheet, 2, 2, "产品名称");
        setCellVal(sheet, 2, 3, row0["itemName"].ToString());
        setCellVal(sheet, 2, 4, "规格型号");
        setCellVal(sheet, 2, 5, row0["itemModel"].ToString());
        setCellVal(sheet, 3, 0, "本批唯一码");
        setCellVal(sheet, 3, 1, row0["wyb"].ToString());
        setCellVal(sheet, 4, 0, "首件唯一码");
        setCellVal(sheet, 4, 1, row0["sjwym"].ToString());
        setCellVal(sheet, 4, 3, "首样条形码");
        setCellVal(sheet, 4, 4, row0["txm"].ToString());
        setCellVal(sheet, 5, 0, "生产单位");
        setCellVal(sheet, 5, 1, "生产车间");
        setCellVal(sheet, 5, 2, row0["sccj"].ToString());
        setCellVal(sheet, 5, 3, "批量");
        setCellVal(sheet, 5, 4, row0["pl"].ToString());
        setCellVal(sheet, 5, 5, "生产日期");
        setCellVal(sheet, 5, 6, row0["scrq"].ToString());
        string[] ary = { "项目", "技术/品质要求", "测试数据", "检验结果", "A", "B", "C" };
        for (int i = 0; i < ary.Length; i++)
        {
            setCellVal(sheet, 6, i, ary[i]);
        }
        //从第7行开始，就是循环数据库
        int _idx = 7;
        DataTable tb1 = dtSource.Tables[1];
        foreach (DataRow rrr in tb1.Rows)
        {
            IRow row7 = sheet.CreateRow(_idx);
            row7.HeightInPoints = rowHeight;
            for (int i = 0; i < ary.Length; i++)
            {
                ICell cell7 = row7.CreateCell(i);
                cell7.CellStyle = style;
                cell7.SetCellValue(rrr[ary[i]].ToString());

            }
            _idx++;
        }
        IRow row8 = sheet.CreateRow(_idx);
        row8.HeightInPoints = rowHeight;
        for (int colIndex = 0; colIndex < maxCols; colIndex++)
        {
            ICell cell = row8.CreateCell(colIndex);
            cell.CellStyle = style12;
        }
        sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 5));
        setCellVal(sheet, _idx, 0, "综合判定");
        setCellVal(sheet, _idx, 1, "合格");
        _idx++;
        IRow row9 = sheet.CreateRow(_idx);
        row9.HeightInPoints = rowHeight;
        for (int colIndex = 0; colIndex < maxCols; colIndex++)
        {
            ICell cell = row9.CreateCell(colIndex);
            cell.CellStyle = style;
        }
        sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 3));
        sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 4, 6));
        setCellVal(sheet, _idx, 1, "工艺  已确认：合格");
        setCellVal(sheet, _idx, 4, "生产  已确认：合格");
        sheet.AddMergedRegion(new CellRangeAddress(_idx - 1, _idx, 0, 0));
        _idx++;

        IRow row10 = sheet.CreateRow(_idx);
        row10.HeightInPoints = rowHeight;
        for (int colIndex = 0; colIndex < maxCols; colIndex++)
        {
            ICell cell = row10.CreateCell(colIndex);
            cell.CellStyle = style12;
        }
        sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 5));
        setCellVal(sheet, _idx, 0, "审核意见");
        setCellVal(sheet, _idx, 1, "合格");
        _idx++;

        IRow row11 = sheet.CreateRow(_idx);
        row11.HeightInPoints = rowHeight;
        for (int colIndex = 0; colIndex < maxCols; colIndex++)
        {
            ICell cell = row11.CreateCell(colIndex);
            cell.CellStyle = style12;
        }
        sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, 6));
        setCellVal(sheet, _idx, 0, "首件填写,来料检验");
        //****合并好后开始赋值end**************

        // 5. 第8到10行中的第一列应被合并
        //sheet.AddMergedRegion(new CellRangeAddress(7, 9, 0, 0));

        //// 6. 第17，18行中的第一列合并
        //sheet.AddMergedRegion(new CellRangeAddress(16, 17, 0, 0));

        //// 7. 第17行中的2，3，4，5列合并
        //sheet.AddMergedRegion(new CellRangeAddress(16, 16, 1, 4));

        //// 8. 第18行中的，2，3列合并，4，5，6列合并
        //sheet.AddMergedRegion(new CellRangeAddress(17, 17, 1, 2));
        //sheet.AddMergedRegion(new CellRangeAddress(17, 17, 3, 5));

        //// 9. 第19行中的，列2，3，4合并
        //sheet.AddMergedRegion(new CellRangeAddress(18, 18, 1, 3));

        //// 10. 第20行中的列全部合并
        //sheet.AddMergedRegion(new CellRangeAddress(19, 19, 0, 5));

        //保存
        using (var ms = new MemoryStream())
        {
            using (var fs = new FileStream(strFileName, FileMode.Create,
                       FileAccess.Write))
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                var data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
            }
        }
    }

    /// <summary>
    /// 生成iqc
    /// </summary>
    /// <param name="dtSource"></param>
    public static void ExportIqc(DataSet dtSource, string strFileName)
    {
        //行高
        int rowHeight = 25;
        //总列
        int maxCols = 8;
        // 创建工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        ISheet sheet = workbook.CreateSheet("Sheet1");

        // 创建单元格样式 - 带边框和指定字体
        ICellStyle style = getStyle(workbook);
        ICellStyle styleHeader = getStyle(workbook, 20, true, true);
        ICellStyle style12 = getStyle(workbook, 11, false, true);

        // 设置列宽（6列）
        sheet.SetColumnWidth(0, 3000);  // 第一列稍宽
        sheet.SetColumnWidth(1, 3000);
        sheet.SetColumnWidth(2, 3000);
        sheet.SetColumnWidth(3, 3000);
        sheet.SetColumnWidth(4, 3000);
        sheet.SetColumnWidth(5, 3000);
        sheet.SetColumnWidth(6, 3000);
        sheet.SetColumnWidth(7, 3000);
        // 创建9行
        for (int rowIndex = 0; rowIndex <= 8; rowIndex++)
        {
            IRow row = sheet.CreateRow(rowIndex);
            // 设置行高（所有行相同高度）
            row.HeightInPoints = rowHeight;
            // 创建个单元格
            for (int colIndex = 0; colIndex < maxCols; colIndex++)
            {
                ICell cell = row.CreateCell(colIndex);
                if (rowIndex == 1)
                    cell.CellStyle = styleHeader;
                else
                    cell.CellStyle = style;
                // 添加一些示例数据（可根据需要修改）
                cell.SetCellValue($"行{rowIndex + 1}列{colIndex + 1}");
            }
        }
        //这是表头
        DataTable tb0 = dtSource.Tables[0];
        DataRow row0 = tb0.Rows[0];
        //第一行列合并
        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 3));
        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 4, 7));
        //第二行列合并
        sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 7));
        //第三行列合并
        sheet.AddMergedRegion(new CellRangeAddress(2, 2, 0, 1));
        sheet.AddMergedRegion(new CellRangeAddress(2, 2, 2, 5));
        //第4
        sheet.AddMergedRegion(new CellRangeAddress(3, 3, 0, 1));
        sheet.AddMergedRegion(new CellRangeAddress(3, 3, 2, 5));
        //第5行
        sheet.AddMergedRegion(new CellRangeAddress(4, 4, 0, 1));
        sheet.AddMergedRegion(new CellRangeAddress(4, 4, 2, 5));
        //抽样依据
        sheet.AddMergedRegion(new CellRangeAddress(5, 5, 0, 1));
        sheet.AddMergedRegion(new CellRangeAddress(5, 5, 2, 7));
        ////6,7行抽样数合并
        sheet.AddMergedRegion(new CellRangeAddress(6, 7, 4, 4));
        sheet.AddMergedRegion(new CellRangeAddress(6, 7, 5, 5));
        sheet.AddMergedRegion(new CellRangeAddress(6, 7, 6, 6));
        sheet.AddMergedRegion(new CellRangeAddress(6, 7, 7, 7));
        ////第8行标题行
        sheet.AddMergedRegion(new CellRangeAddress(8, 8, 1, 5));
        //****合并好后开始赋值beg**************
        setCellVal(sheet, 0, 0, row0["title"].ToString());
        setCellVal(sheet, 0, 4, row0["title2"].ToString());
        setCellVal(sheet, 1, 0, row0["title3"].ToString());
        setCellVal(sheet, 2, 0, "物料名称");
        setCellVal(sheet, 2, 2, row0["itemName"].ToString());
        setCellVal(sheet, 2, 6, "物料编码");
        setCellVal(sheet, 2, 7, row0["itemNo"].ToString());
        setCellVal(sheet, 3, 0, "送货单位");
        setCellVal(sheet, 3, 2, row0["shdw"].ToString());
        setCellVal(sheet, 3, 6, "送检日期");
        setCellVal(sheet, 3, 7, row0["sjrq"].ToString());
        setCellVal(sheet, 4, 0, "型号规格");
        setCellVal(sheet, 4, 2, row0["itemModel"].ToString());
        setCellVal(sheet, 4, 6, "批量");
        setCellVal(sheet, 4, 7, row0["pl"].ToString());
        setCellVal(sheet, 5, 0, "抽样依据");
        setCellVal(sheet, 5, 2, row0["cyyj"].ToString());
        setCellVal(sheet, 6, 0, "AQL");
        setCellVal(sheet, 6, 1, "CR");
        setCellVal(sheet, 6, 2, "MA");
        setCellVal(sheet, 6, 3, "MI");
        setCellVal(sheet, 6, 4, "抽样数");
        setCellVal(sheet, 6, 5, row0["cys"].ToString());
        setCellVal(sheet, 6, 6, "不合格数");
        setCellVal(sheet, 6, 7, row0["bhgs"].ToString());
        setCellVal(sheet, 7, 0, "判定Ac/Re");
        setCellVal(sheet, 7, 1, "0/1");
        setCellVal(sheet, 7, 2, "0/1");
        setCellVal(sheet, 7, 3, "0/1");
        setCellVal(sheet, 8, 0, "项目");
        setCellVal(sheet, 8, 1, "质量要求");
        setCellVal(sheet, 8, 6, "检验记录");
        setCellVal(sheet, 8, 7, "检验结果");
        ////从第9行开始，就是循环数据库
        int _idx = 9;
        DataTable tb1 = dtSource.Tables[1];
        foreach (DataRow rrr in tb1.Rows)
        {
            IRow row7 = sheet.CreateRow(_idx);
            row7.HeightInPoints = rowHeight;
            for (int i = 0; i < maxCols; i++)
            {
                ICell cell7 = row7.CreateCell(i);
                cell7.CellStyle = style;
                // cell7.SetCellValue(rrr[ary[i]].ToString());
            }
            sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 5));
            setCellVal(sheet, _idx, 0, rrr["项目"].ToString());
            setCellVal(sheet, _idx, 1, rrr["质量要求"].ToString());
            setCellVal(sheet, _idx, 6, rrr["检验记录"].ToString());
            setCellVal(sheet, _idx, 7, rrr["检验结果"].ToString());
            _idx++;
        }
        //备注
        IRow row8 = sheet.CreateRow(_idx);
        row8.HeightInPoints = rowHeight;
        for (int colIndex = 0; colIndex < maxCols; colIndex++)
        {
            ICell cell = row8.CreateCell(colIndex);
            cell.CellStyle = style12;
        }
        sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 7));
        setCellVal(sheet, _idx, 0, "备注：");
        _idx++;
        //综合判定
        for (int i = 0; i < 2; i++)
        {
            IRow row9 = sheet.CreateRow(_idx);
            row9.HeightInPoints = rowHeight;
            for (int colIndex = 0; colIndex < maxCols; colIndex++)
            {
                ICell cell = row9.CreateCell(colIndex);
                cell.CellStyle = style;
            }
            _idx++;
        }
        sheet.AddMergedRegion(new CellRangeAddress(_idx - 2, _idx - 1, 0, 0));
        sheet.AddMergedRegion(new CellRangeAddress(_idx - 2, _idx - 2, 1, 7));
        sheet.AddMergedRegion(new CellRangeAddress(_idx - 1, _idx - 1, 1, 7));
        setCellVal(sheet, _idx - 2, 0, "综合判定：");
        setCellVal(sheet, _idx - 2, 1, row0["zhpd"].ToString());
        setCellVal(sheet, _idx - 1, 1, row0["zhpdqz"].ToString());
        //审核意见：
        for (int i = 0; i < 2; i++)
        {
            IRow row9 = sheet.CreateRow(_idx);
            row9.HeightInPoints = rowHeight;
            for (int colIndex = 0; colIndex < maxCols; colIndex++)
            {
                ICell cell = row9.CreateCell(colIndex);
                cell.CellStyle = style;
            }
            _idx++;
        }
        sheet.AddMergedRegion(new CellRangeAddress(_idx - 2, _idx - 1, 0, 0));
        sheet.AddMergedRegion(new CellRangeAddress(_idx - 2, _idx - 2, 1, 7));
        sheet.AddMergedRegion(new CellRangeAddress(_idx - 1, _idx - 1, 1, 7));
        setCellVal(sheet, _idx - 2, 0, "审核意见：");
        setCellVal(sheet, _idx - 2, 1, row0["shyj"].ToString());
        setCellVal(sheet, _idx - 1, 1, row0["shyjqz"].ToString());
        string[] ary = { "采购部意见", "销售部意见", "技术部意见", "生产部意见", "品质部意见" };
        for (int i = 0; i < ary.Length; i++)
        {
            IRow row9 = sheet.CreateRow(_idx);
            row9.HeightInPoints = rowHeight;
            for (int colIndex = 0; colIndex < maxCols; colIndex++)
            {
                ICell cell = row9.CreateCell(colIndex);
                cell.CellStyle = style;
            }
            // sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, 1));
            sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 2, 7));
            _idx++;
        }
        setCellVal(sheet, _idx - ary.Length, 0, "不合格评审：");
        sheet.AddMergedRegion(new CellRangeAddress(_idx - ary.Length, _idx - 1, 0, 0));
        for (int i = 0; i < ary.Length; i++)
        {
            setCellVal(sheet, _idx - i - 1, 1, ary[i]);
            setCellVal(sheet, _idx - i - 1, 2, "□ 同意           □ 不同意        签名/日期：");
        }
        //****合并好后开始赋值end**************
        //保存
        using (var ms = new MemoryStream())
        {
            using (var fs = new FileStream(strFileName, FileMode.Create,
                       FileAccess.Write))
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                var data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
            }
        }
    }


    /// <summary>
    /// 生成巡检
    /// </summary>
    /// <param name="dtSource"></param>
    public static void ExportXunJian(DataSet dtSource, string strFileName)
    {
        DataTable tb0 = dtSource.Tables[0];
        DataRow row0 = tb0.Rows[0];
        DataTable tb1 = dtSource.Tables[1];
        //行高
        int rowHeight = 25;
        //总列
        int maxCols = tb1.Columns.Count;
        HSSFWorkbook workbook = new HSSFWorkbook();
        ISheet sheet = workbook.CreateSheet("Sheet1");

        // 创建单元格样式 - 带边框和指定字体
        ICellStyle style = getStyle(workbook);
        ICellStyle styleHeader = getStyle(workbook, 20, true, true);
        ICellStyle style12 = getStyle(workbook, 11, false, true);

        // 设置列宽（6列）
        sheet.SetColumnWidth(0, 3000);  // 第一列稍宽
        sheet.SetColumnWidth(1, 3000);
        sheet.SetColumnWidth(2, 3000);
        sheet.SetColumnWidth(3, 3000);
        sheet.SetColumnWidth(4, 3000);
        sheet.SetColumnWidth(5, 3000);
        sheet.SetColumnWidth(6, 3000);
        sheet.SetColumnWidth(7, 3000);
        // 创建行
        for (int rowIndex = 0; rowIndex <= 1; rowIndex++)
        {
            IRow row = sheet.CreateRow(rowIndex);
            // 设置行高（所有行相同高度）
            row.HeightInPoints = rowHeight;
            // 创建个单元格
            for (int colIndex = 0; colIndex < maxCols; colIndex++)
            {
                ICell cell = row.CreateCell(colIndex);
                if (rowIndex == 0)
                    cell.CellStyle = styleHeader;
                else
                    cell.CellStyle = style;
            }
        }
        //第一行列合并
        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 7));
        setCellVal(sheet, 0, 0, row0["title"].ToString());
        //循环数据库
        int _idx = 1;
        IRow row6 = sheet.CreateRow(_idx);
        for (int colIndex = 0; colIndex < maxCols; colIndex++)
        {
            ICell cell6 = row6.CreateCell(colIndex);
            cell6.CellStyle = style;
            cell6.SetCellValue(tb1.Columns[colIndex].Caption.ToString().Trim());
        }
        _idx++;
        foreach (DataRow rrr in tb1.Rows)
        {
            IRow row7 = sheet.CreateRow(_idx);
            row7.HeightInPoints = rowHeight;
            for (int colIndex = 0; colIndex < tb1.Columns.Count; colIndex++)
            {
                ICell cell7 = row7.CreateCell(colIndex);
                cell7.CellStyle = style;
                cell7.SetCellValue(rrr[tb1.Columns[colIndex]].ToString());
            }
            _idx++;
        }
        //备注
        IRow row8 = sheet.CreateRow(_idx);
        row8.HeightInPoints = rowHeight;
        for (int colIndex = 0; colIndex < maxCols; colIndex++)
        {
            ICell cell = row8.CreateCell(colIndex);
            cell.CellStyle = style12;
        }
        sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, 2));
        setCellVal(sheet, _idx, 0, "巡检人：");
        for (int colIndex = 2; colIndex < tb1.Columns.Count; colIndex++)
        {
            setCellVal(sheet, _idx, colIndex, "余吉林");
        }
        //保存
        using (var ms = new MemoryStream())
        {
            using (var fs = new FileStream(strFileName, FileMode.Create,
                       FileAccess.Write))
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                var data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
            }
        }
    }


    /// <summary>
    ///     Excel导入成Datable
    /// </summary>
    /// <param name="file">导入路径(包含文件名与扩展名)</param>
    /// <returns></returns>
    public static DataTable ExcelToTable(string file)
    {
        var dt = new DataTable();
        IWorkbook workbook;
        var fileExt = Path.GetExtension(file).ToLower();
        using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
        {
            //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
            if (fileExt == ".xlsx")
                workbook = new XSSFWorkbook(fs);
            else if (fileExt == ".xls")
                workbook = new HSSFWorkbook(fs);
            else
                workbook = null;
            if (workbook == null) return null;
            //  int numberOfSheets = SXSSFWorkbook.getNumberOfSheets();
            var numberOfSheets = workbook.NumberOfSheets; //获取表的数量

            var sheet = workbook.GetSheetAt(0);
            //表头  
            var header = sheet.GetRow(sheet.FirstRowNum + 1);
            var columns = new List<int>();
            for (var i = 0; i < header.LastCellNum; i++)
            {
                var obj = GetValueType(header.GetCell(i));
                if (obj == null || obj.ToString() == string.Empty)
                    dt.Columns.Add(new DataColumn("Columns" + i));
                else
                    dt.Columns.Add(new DataColumn(obj.ToString().Trim()));
                columns.Add(i);
            }

            for (var act = 0; act < numberOfSheets; act++)
            {
                var _sheet = workbook.GetSheetAt(act);
                //数据  
                for (var i = _sheet.FirstRowNum + 1 + 1;
                     i <= _sheet.LastRowNum;
                     i++)
                {
                    var dr = dt.NewRow();
                    var hasValue = false;
                    foreach (var j in columns)
                    {
                        dr[j] = GetValueType(_sheet.GetRow(i).GetCell(j));
                        if (dr[j] != null && dr[j].ToString() != string.Empty)
                            hasValue = true;
                    }

                    if (hasValue) dt.Rows.Add(dr);
                }
            }
        }

        return dt;
    }

    /// <summary>
    ///     获取单元格类型
    /// </summary>
    /// <param name="cell"></param>
    /// <returns></returns>
    private static object GetValueType(ICell cell)
    {
        if (cell == null)
            return null;
        switch (cell.CellType)
        {
            case CellType.Blank: //BLANK:  
                return null;
            case CellType.Boolean: //BOOLEAN:  
                return cell.BooleanCellValue;
            case CellType.Numeric: //NUMERIC:  
                return cell.NumericCellValue;
            case CellType.String: //STRING:  
                return cell.StringCellValue.Trim();
            case CellType.Error: //ERROR:  
                return cell.ErrorCellValue;
            case CellType.Formula: //FORMULA:  
            default:
                return "=" + cell.CellFormula;
        }
    }
}