MedicalInsurance/HuBeiMI/Common/EPPlusToExcel.cs


using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Windows.Forms;

namespace PTMedicalInsurance.Common
{   

    /// <summary>
    /// EPPlus DataTable导出Excel工具类
    /// 使用前需安装: Install-Package EPPlus
    /// </summary>
    public static class EPPlusExcelExporter
    {
        // 静态构造函数设置EPPlus许可证（EPPlus 5+需要）
        static EPPlusExcelExporter()
        {
            // EPPlus 5+ 需要设置许可证类型
            // 非商业用途使用：
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            // 商业用途请购买许可证后使用：
            // ExcelPackage.LicenseContext = LicenseContext.Commercial;
        }

        /// <summary>
        /// DataTable导出Excel（基础版本）
        /// </summary>
        /// <param name="dataTable">数据源</param>
        /// <param name="filePath">导出路径</param>
        /// <param name="sheetName">Sheet名称</param>
        public static void ExportToExcel(DataTable dataTable, string filePath, string sheetName = "Sheet1")
        {
            if (dataTable == null || dataTable.Rows.Count == 0)
                throw new ArgumentException("DataTable为空或没有数据");

            // 确保目录存在
            string directory = Path.GetDirectoryName(filePath);
            if (!Directory.Exists(directory))
                Directory.CreateDirectory(directory);

            using (var package = new ExcelPackage())
            {
                var worksheet = package.Workbook.Worksheets.Add(sheetName);

                // 写入数据（包含表头）
                worksheet.Cells[1, 1].LoadFromDataTable(dataTable, true);

                // 设置表头样式
                FormatHeader(worksheet, dataTable.Columns.Count);

                // 自动调整列宽
                worksheet.Cells.AutoFitColumns();

                // 保存文件
                package.SaveAs(new FileInfo(filePath));
            }
        }

        /// <summary>
        /// DataTable导出Excel（分批处理版本 - 每500行处理一次）
        /// </summary>
        /// <param name="dataTable">数据源</param>
        /// <param name="filePath">导出路径</param>
        /// <param name="batchSize">每批处理行数（默认500）</param>
        /// <param name="sheetName">Sheet名称</param>
        public static void ExportToExcelBatch(DataTable dataTable, string filePath, int batchSize = 500)
        {
            string sheetName = "Sheet1";
            if (dataTable == null || dataTable.Rows.Count == 0)
                throw new ArgumentException("DataTable为空或没有数据");

            // 确保目录存在
            string directory = Path.GetDirectoryName(filePath);
            if (!Directory.Exists(directory))
                Directory.CreateDirectory(directory);

            int totalRows = dataTable.Rows.Count;
            int columnCount = dataTable.Columns.Count;

            using (var package = new ExcelPackage())
            {
                var worksheet = package.Workbook.Worksheets.Add(sheetName);

                // 写入表头
                for (int col = 0; col < columnCount; col++)
                {
                    worksheet.Cells[1, col + 1].Value = dataTable.Columns[col].ColumnName;
                }
                FormatHeader(worksheet, columnCount);

                // 分批写入数据
                int currentRow = 2; // 从第2行开始（第1行是表头）
                int batchCount = (int)Math.Ceiling((double)totalRows / batchSize);

                Console.WriteLine($"总数据: {totalRows} 行, 分批: {batchCount} 批, 每批: {batchSize} 行");

                for (int batch = 0; batch < batchCount; batch++)
                {
                    int startRow = batch * batchSize;
                    int endRow = Math.Min(startRow + batchSize, totalRows);
                    int rowsInBatch = endRow - startRow;

                    Console.WriteLine($"处理第 {batch + 1}/{batchCount} 批: 行 {startRow + 1} - {endRow}");

                    // 当前批次的数据数组
                    object[,] batchData = new object[rowsInBatch, columnCount];

                    for (int i = 0; i < rowsInBatch; i++)
                    {
                        for (int j = 0; j < columnCount; j++)
                        {
                            batchData[i, j] = dataTable.Rows[startRow + i][j];
                        }
                    }

                    // 写入当前批次到Excel
                    var range = worksheet.Cells[currentRow, 1, currentRow + rowsInBatch - 1, columnCount];
                    range.Value = batchData;

                    currentRow += rowsInBatch;
                }

                // 格式化数据区域
                FormatDataRange(worksheet, totalRows, columnCount);

                // 自动调整列宽
                worksheet.Cells.AutoFitColumns();

                // 冻结首行
                worksheet.View.FreezePanes(2, 1);

                // 保存文件
                package.SaveAs(new FileInfo(filePath));
                Console.WriteLine($"导出完成: {filePath}");
                MessageBox.Show("  导出完毕："+filePath);
            }
        }

        /// <summary>
        /// 大数据导出（自动分Sheet，每个Sheet最多100万行）
        /// </summary>
        public static void ExportLargeData(DataTable dataTable, string filePath, int rowsPerSheet = 1000000)
        {
            if (dataTable == null || dataTable.Rows.Count == 0)
                throw new ArgumentException("DataTable为空或没有数据");

            string directory = Path.GetDirectoryName(filePath);
            if (!Directory.Exists(directory))
                Directory.CreateDirectory(directory);

            int totalRows = dataTable.Rows.Count;
            int columnCount = dataTable.Columns.Count;
            int sheetCount = (int)Math.Ceiling((double)totalRows / rowsPerSheet);

            using (var package = new ExcelPackage())
            {
                for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
                {
                    string sheetName = sheetCount == 1 ? "数据" : $"数据_{sheetIndex + 1}";
                    var worksheet = package.Workbook.Worksheets.Add(sheetName);

                    // 当前Sheet的数据范围
                    int startRow = sheetIndex * rowsPerSheet;
                    int endRow = Math.Min(startRow + rowsPerSheet, totalRows);
                    int rowsInSheet = endRow - startRow;

                    // 写入表头
                    for (int col = 0; col < columnCount; col++)
                    {
                        worksheet.Cells[1, col + 1].Value = dataTable.Columns[col].ColumnName;
                    }
                    FormatHeader(worksheet, columnCount);

                    // 分批写入数据（每批500行）
                    int batchSize = 500;
                    int currentRow = 2;
                    int batchesInSheet = (int)Math.Ceiling((double)rowsInSheet / batchSize);

                    for (int batch = 0; batch < batchesInSheet; batch++)
                    {
                        int batchStart = startRow + batch * batchSize;
                        int batchEnd = Math.Min(batchStart + batchSize, endRow);
                        int rowsInBatch = batchEnd - batchStart;

                        object[,] batchData = new object[rowsInBatch, columnCount];
                        for (int i = 0; i < rowsInBatch; i++)
                        {
                            for (int j = 0; j < columnCount; j++)
                            {
                                batchData[i, j] = dataTable.Rows[batchStart + i][j];
                            }
                        }

                        var range = worksheet.Cells[currentRow, 1, currentRow + rowsInBatch - 1, columnCount];
                        range.Value = batchData;
                        currentRow += rowsInBatch;
                    }

                    FormatDataRange(worksheet, rowsInSheet, columnCount);
                    worksheet.Cells.AutoFitColumns();
                    worksheet.View.FreezePanes(2, 1);
                }

                package.SaveAs(new FileInfo(filePath));
            }
        }

        /// <summary>
        /// DataTable转内存流（适用于Web下载）
        /// </summary>
        public static MemoryStream ExportToStream(DataTable dataTable, string sheetName = "Sheet1")
        {
            if (dataTable == null || dataTable.Rows.Count == 0)
                throw new ArgumentException("DataTable为空或没有数据");

            using (var package = new ExcelPackage())
            {
                var worksheet = package.Workbook.Worksheets.Add(sheetName);
                worksheet.Cells[1, 1].LoadFromDataTable(dataTable, true);
                FormatHeader(worksheet, dataTable.Columns.Count);
                worksheet.Cells.AutoFitColumns();

                var stream = new MemoryStream();
                package.SaveAs(stream);
                stream.Position = 0;
                return stream;
            }
        }

        #region 私有方法

        /// <summary>
        /// 设置表头样式
        /// </summary>
        private static void FormatHeader(ExcelWorksheet worksheet, int columnCount)
        {
            var headerRange = worksheet.Cells[1, 1, 1, columnCount];

            headerRange.Style.Font.Bold = true;
            headerRange.Style.Font.Size = 11;
            headerRange.Style.Fill.PatternType = ExcelFillStyle.Solid;
            headerRange.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(192, 192, 192)); // 灰色
            headerRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            headerRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            headerRange.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
        }

        /// <summary>
        /// 设置数据区域样式
        /// </summary>
        private static void FormatDataRange(ExcelWorksheet worksheet, int dataRows, int columnCount)
        {
            var dataRange = worksheet.Cells[2, 1, dataRows + 1, columnCount];

            dataRange.Style.Font.Size = 10;
            dataRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
            dataRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;

            // 添加边框
            dataRange.Style.Border.Top.Style = ExcelBorderStyle.Thin;
            dataRange.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            dataRange.Style.Border.Left.Style = ExcelBorderStyle.Thin;
            dataRange.Style.Border.Right.Style = ExcelBorderStyle.Thin;

            // 设置行高
            worksheet.DefaultRowHeight = 18;
        }

        #endregion
    }
}