HEGII.ss.mes.wcf/DK.Basics/BaseControls/ExportExcel/ExportExcel_EPPlus.cs

using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.ComponentModel;
using System.Drawing;
using System.IO;
using System.Windows.Forms;
using System.Threading.Tasks;
using Dongke.IBOSS.PRD.Basics.BaseControls.ExportExcel;
using Curtain.Log;

public class ExportExcel_EPPlus : IDisposable
{
    private ExportExcelProgress _form;

    private BackgroundWorker _worker;
    private ProgressBar _progressBar;
    private Label _statusLabel;

    public ExportParameters _exportParams;
    private string _title;

    public ExportExcel_EPPlus()
    {
        _form = new ExportExcelProgress();
        _progressBar = _form.StatusBar;
        _statusLabel = _form.StatusLabel;

        InitializeBackgroundWorker();
    }

    private void InitializeBackgroundWorker()
    {
        _worker = new BackgroundWorker
        {
            WorkerReportsProgress = true,
            WorkerSupportsCancellation = true
        };

        _worker.DoWork += Worker_DoWork;
        _worker.ProgressChanged += Worker_ProgressChanged;
        _worker.RunWorkerCompleted += Worker_RunWorkerCompleted;
    }
    public static void ExportExcel(DataGridView dataGridView, string sheetName, string filePath, bool includeHiddenColumns = false)
    {
        ExportExcel_EPPlus _exporter = new ExportExcel_EPPlus();
        _exporter.ExportAsync(dataGridView, sheetName, filePath, false);
    }

    //public async Task
    public void ExportAsync(DataGridView dataGridView, string sheetName, string filePath, bool includeHiddenColumns = false)
    {
        _title = sheetName;
        using (SaveFileDialog saveFileDialog = new SaveFileDialog())
        {
            saveFileDialog.Title = "请选择要导出Excel文件的路径和文件名";
            saveFileDialog.AddExtension = true;
            saveFileDialog.OverwritePrompt = false;
            //saveFileDialog.DefaultExt = "xls";
            //saveFileDialog.Filter = "97-2003 Excel文件(*.xls)|*.xls|所有文件(*.*)|*.*";
            saveFileDialog.DefaultExt = "xlsx";
            saveFileDialog.Filter = "Excel文件(*.xlsx)|*.xlsx|所有文件(*.*)|*.*";
            saveFileDialog.FileName = filePath;

            if (saveFileDialog.ShowDialog() == DialogResult.Cancel)
            {
                return;
            }
            filePath = saveFileDialog.FileName;
        }
        if (File.Exists(filePath))
        {
            DialogResult dr = MessageBox.Show("选择的文件【" + filePath + "】已经存在，您确定要覆盖原来的文件吗？注意：覆盖原文件后，原文件中的全部数据都将丢失！\r\n单击[是]按钮，将覆盖原文件，单击[否]按钮，导出数据将以追加的形式添加到文件中，单击[取消]按钮将取消导出"
                , _title, MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button3);
            switch (dr)
            {
                case DialogResult.Cancel:
                    return;
                case DialogResult.Yes:
                    try
                    {
                        File.Delete(filePath);
                    }
                    catch (Exception projectError)
                    {
                        Logger.Error(projectError, "ExportExcel_EPPlus");
                        //MessageBox.Show(projectError.Message, sheetName, MessageBoxButtons.OK, MessageBoxIcon.Error);
                        MessageBox.Show("数据导出失败，原因是文件【" + filePath + "】无法覆盖，可能该文件正在使用中，请关闭该文件后再试", _title, MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        return;
                    }
                    break;
            }
        }

        if (_worker.IsBusy)
        {
            MessageBox.Show("导出正在进行中，请稍候...", _title);
            return;
        }

        _form._eeep = this;
        _form.Text += _title;
        _form.Show();
        UpdateStatus("准备导出...");
        UpdateProgress(0);

        try
        {
            _exportParams = new ExportParameters
            {
                DataGridView = dataGridView,
                SheetName = sheetName,
                FilePath = filePath,
                IncludeHiddenColumns = includeHiddenColumns
            };

            //await Task.Run(() => _worker.RunWorkerAsync(exportParams));
            _worker.RunWorkerAsync(_exportParams);
        }
        catch (Exception ex)
        {
            MessageBox.Show($"导出失败: {ex.Message}", _title);
            UpdateStatus("导出失败");
            UpdateProgress(0);
        }
    }

    public void CancelExport()
    {
        if (_worker.IsBusy)
        {
            _worker.CancelAsync();
            UpdateStatus("正在取消...");
        }
    }

    private void Worker_DoWork(object sender, DoWorkEventArgs e)
    {
        var parameters = (ExportParameters)e.Argument;
        var dataGridView = parameters.DataGridView;
        var filePath = parameters.FilePath;

        //ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

        using (var excelPackage = new ExcelPackage())
        {
            var worksheet = excelPackage.Workbook.Worksheets.Add(parameters.SheetName);

            // 导出列标题
            _worker.ReportProgress(0, "导出开始");
            ExportHeaders(worksheet, dataGridView, parameters.IncludeHiddenColumns);
            _worker.ReportProgress(10, "导出表头完成");

            if (_worker.CancellationPending)
            {
                e.Cancel = true;
                return;
            }

            // 导出数据行
            ExportDataRows(worksheet, dataGridView, parameters.IncludeHiddenColumns, _worker);

            if (_worker.CancellationPending)
            {
                e.Cancel = true;
                return;
            }

            _worker.ReportProgress(85, "保存文件中...");
            // 应用格式
            ApplyFormatting(worksheet, dataGridView, parameters.IncludeHiddenColumns);
            _worker.ReportProgress(95, "保存文件中。。。");

            // 保存文件
            excelPackage.SaveAs(new FileInfo(filePath));
            _worker.ReportProgress(100, "保存文件完成");
        }
    }

    private void ExportHeaders(ExcelWorksheet worksheet, DataGridView dataGridView, bool includeHiddenColumns)
    {
        int colIndex = 1;

        foreach (DataGridViewColumn column in dataGridView.Columns)
        {
            if (column.Visible || includeHiddenColumns)
            {
                worksheet.Cells[1, colIndex].Value = column.HeaderText;
                colIndex++;
            }
        }

        // 设置标题行样式
        using (var range = worksheet.Cells[1, 1, 1, colIndex - 1])
        {
            range.Style.Font.Name = "宋体";
            range.Style.Font.Bold = true;
            range.Style.Font.Size = 9;//12;
            range.Style.Font.Color.SetColor(Color.White);
            range.Style.Fill.PatternType = ExcelFillStyle.Solid;
            //range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189));
            range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(48, 58, 70));
            range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
        }
    }

    private void ExportDataRows(ExcelWorksheet worksheet, DataGridView dataGridView,
                              bool includeHiddenColumns, BackgroundWorker worker)
    {
        int totalRows = dataGridView.Rows.Count;
        int exportedRows = 0;

        for (int rowIndex = 0; rowIndex < dataGridView.Rows.Count; rowIndex++)
        {
            if (dataGridView.Rows[rowIndex].IsNewRow)
                continue;

            int colIndex = 1;

            foreach (DataGridViewColumn column in dataGridView.Columns)
            {
                if (column.Visible || includeHiddenColumns)
                {
                    var cellValue = dataGridView.Rows[rowIndex].Cells[column.Index].Value;
                    worksheet.Cells[rowIndex + 2, colIndex].Value = FormatCellValue(cellValue, column.ValueType);
                    colIndex++;
                }
            }

            exportedRows++;

            // 每处理10行报告一次进度
            if (exportedRows % 10 == 0 || exportedRows == totalRows)
            {
                int progress = 10 + (int)((double)exportedRows / totalRows * 70);
                worker.ReportProgress(progress, $"正在导出数据 ({exportedRows}/{totalRows})");

                if (worker.CancellationPending)
                    return;
            }
        }
    }

    private object FormatCellValue(object value, Type valueType)
    {
        if (value == null || value == DBNull.Value)
            return string.Empty;

        if (valueType == typeof(DateTime) && value is DateTime dateTime)
            return dateTime;

        if (valueType == typeof(bool) && value is bool boolValue)
            return boolValue ? "是" : "否";

        if (valueType == typeof(decimal) || valueType == typeof(double) || valueType == typeof(float))
            return Convert.ToDecimal(value);

        return value.ToString();
    }

    private void ApplyFormatting(ExcelWorksheet worksheet, DataGridView dataGridView, bool includeHiddenColumns)
    {
        if (worksheet.Dimension == null)
            return;

        // 自动调整列宽
        //worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();

        // 设置数据区域样式
        using (var range = worksheet.Cells[2, 1, worksheet.Dimension.End.Row, worksheet.Dimension.End.Column])
        {
            range.Style.Font.Name = "宋体";
            range.Style.Font.Size = 9;

            range.Style.Border.Top.Style = ExcelBorderStyle.Thin;
            range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
            range.Style.Border.Right.Style = ExcelBorderStyle.Thin;

            range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
            range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
        }

        // 设置数字格式
        int excelColIndex = 1;
        foreach (DataGridViewColumn column in dataGridView.Columns)
        {
            if (column.Visible || includeHiddenColumns)
            {
                var excelColumn = worksheet.Column(excelColIndex);

                if (column.ValueType == typeof(DateTime))
                {
                    excelColumn.Style.Numberformat.Format = "yyyy-MM-dd hh:mm:ss";
                    excelColumn.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                }
                else if (column.ValueType == typeof(decimal) || column.ValueType == typeof(double))
                {
                    //excelColumn.Style.Numberformat.Format = "#,##0.00";
                    excelColumn.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                }
                else if (column.ValueType == typeof(int))
                {
                    //excelColumn.Style.Numberformat.Format = "#,##0";
                    excelColumn.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                }

                //excelColumn.AutoFit();
                excelColIndex++;
            }
        }

        // 设置交替行颜色
        /*for (int row = 2; row <= worksheet.Dimension.End.Row; row++)
        {
            if (row % 2 == 0) // 偶数行
            {
                using (var range = worksheet.Cells[row, 1, row, worksheet.Dimension.End.Column])
                {
                    range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                    range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(242, 242, 242));
                }
            }
        }*/

        // 自动调整列宽
        //worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
        // 冻结首行
        worksheet.View.FreezePanes(2, 1);
    }

    private void Worker_ProgressChanged(object sender, ProgressChangedEventArgs e)
    {
        UpdateProgress(e.ProgressPercentage);

        if (e.UserState != null)
        {
            UpdateStatus(e.UserState.ToString());
        }
    }

    private void Worker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
    {
        this._form.Done();

        if (e.Cancelled)
        {
            UpdateStatus("导出已取消");
            MessageBox.Show("导出操作已取消。", _title);
        }
        else if (e.Error != null)
        {
            UpdateStatus("导出失败");
            MessageBox.Show($"导出过程中发生错误: {e.Error.Message}", _title);
        }
        else
        {
            UpdateStatus("导出完成");
            MessageBox.Show("数据导出成功！", _title);
        }

        //UpdateProgress(0);
    }

    private void UpdateProgress(int value)
    {
        if (_progressBar != null && !_progressBar.IsDisposed)
        {
            _progressBar.Invoke(new Action(() =>
            {
                _progressBar.Value = value;
            }));
        }
    }

    private void UpdateStatus(string message)
    {
        if (_statusLabel != null && !_statusLabel.IsDisposed)
        {
            _statusLabel.Invoke(new Action(() =>
            {
                _statusLabel.Text = message;
            }));
        }
    }

    public void Dispose()
    {
        if (_worker != null)
        {
            _worker.Dispose();
        }
    }

    public class ExportParameters
    {
        public DataGridView DataGridView
        {
            get; set;
        }
        public string SheetName
        {
            get; set;
        }
        public string FilePath
        {
            get; set;
        }
        public bool IncludeHiddenColumns
        {
            get; set;
        }
    }
}