using NPOI.SS.UserModel;
namespace Common.CommonFunc
{
    /// <summary>
    /// 用于解决超过65535行数据的内存溢出问题
    /// add by spw 2025-4-29 07:58:49
    /// </summary>
    public static class ExportXlsHelper
    {
        /// <summary>
        /// 分批次处理dataset，并导出多sheet
        /// </summary>
        /// <param name="workbook">工作簿</param>
        /// <param name="dsExport">需要导出的dataset</param>
        public static void ExportLargeDataToExcel(IWorkbook workbook, DataSet dsExport)
        {
            const int maxRowsPerSheet = 65535; // Excel2003单sheet最大行数65536-1（列头）
            foreach (DataTable dtItem in dsExport.Tables)
            {
                int tabFlag = 1; // 区分析出的datatable的名字
                int currentRowCount = 0;
                DataTable currentDt = dtItem.Clone(); // 初始化datatable结构
                foreach (DataRow drItem in dtItem.Rows)
                {
                    currentDt.ImportRow(drItem); // 导入数据行
                    currentRowCount++;
                    
                    // 动态拆分datatable
                    if (currentRowCount >= maxRowsPerSheet)// 其实就是等于的时候
                    {
                    	// 如果超过1页，先创建第一页
                    	if (tabFlag == 1 )
                        {
                             // 将当前datatable写入excel并释放内存
                        	WriteDataTableToSheet(workbook, currentDt);
                        	currentDt.Dispose();
                        	currentDt = dtItem.Clone(); // 初始化datatable结构
                        	currentRowCount = 0;
                        }
                        else
                        {
                        // 第一页之后每一页需要变更表名标识
                        if (!string.IsNullOrEmpty(currentDt.TableName))
                        {
                            currentDt.TableName = currentDt.TableName + tabFlag;
                        }
                        // 将当前datatable写入excel并释放内存
                        WriteDataTableToSheet(workbook, currentDt);
                        currentDt.Dispose();
                        currentDt = dtItem.Clone(); // 初始化datatable结构
                        currentRowCount = 0;
						}
						tabFlag++;
                    }

                }
                // 如果不满第一页，或最后一页未满
                if (currentDt.Rows.Count > 0)
                {
                	// 第一页名字不变，大于第一页后缀数字加1
                    if (tabFlag > 1 && !string.IsNullOrEmpty(currentDt.TableName))
                    {
                        currentDt.TableName = currentDt.TableName + tabFlag;
                    }
                    WriteDataTableToSheet(workbook, currentDt);
                    currentDt.Dispose();
                }
            }
        }


        /// <summary>
        /// 将DataTable写入excel的一个sheet
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="table"></param>
        public static void WriteDataTableToSheet(IWorkbook workbook, DataTable table)
        {
            ISheet sheet = workbook.CreateSheet(string.IsNullOrEmpty(table.TableName) ? "sheet" + workbook.NumberOfSheets : table.TableName);
            int rowCount = table.Rows.Count;
            int columnCount = table.Columns.Count;

            IRow row = sheet.CreateRow(0);
            // 写入列头
            for (int c = 0; c < columnCount; c++)
            {
                ICell cell = row.CreateCell(c);
                cell.SetCellValue(table.Columns[c].ColumnName);
            }

            // 写入数据行
            for (int i = 0; i < rowCount; i++)
            {
                row = sheet.CreateRow(i + 1);
                for (int j = 0; j < columnCount; j++)
                {
                    ICell cell = row.CreateCell(j);
                    cell.SetCellValue(table.Rows[i][j].ToString());
                }
            }
        }

    }

}