HEGII.ss.mes.wcf/DK.Service/CommonModuleLogic/ExportExcel_EPPlus.cs

using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;

public class ExcelExportService
{
    public static byte[] ExportDataTableToBytes(DataTable dataTable, Dictionary<string, string> customHeaders = null)
    {
        try
        {
            if (dataTable == null || dataTable.Rows.Count == 0)
            {
                throw new ArgumentException("数据表不能为空");
            }

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

                // 写入列标题
                WriteHeaders(worksheet, dataTable, customHeaders);

                // 写入数据行
                WriteData(worksheet, dataTable);

                // 应用格式
                ApplyFormatting(worksheet, dataTable);

                return package.GetAsByteArray();
            }
        }
        catch (Exception ex)
        {
            throw new Exception($"导出失败: {ex.Message}", ex);
        }
    }
    /// <summary>
    /// 导出DataTable到Excel并返回文件信息
    /// </summary>
    public static ExportResult ExportDataTable(DataTable dataTable, string exportPath,
                                              string fileName = null,
                                              Dictionary<string, string> customHeaders = null)
    {
        try
        {
            if (dataTable == null || dataTable.Rows.Count == 0)
            {
                return ExportResult.Error("数据表不能为空");
            } 

            // 确保目录存在
            if (!Directory.Exists(exportPath))
            {
                Directory.CreateDirectory(exportPath);
            }

            // 生成文件名
            if (string.IsNullOrEmpty(fileName))
            {
                fileName = $"QC检验数据_{DateTime.Now:yyyyMMdd_HHmmss}.xlsx";
            }
            else if (!fileName.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase))
            {
                fileName += ".xlsx";
            }

            string filePath = Path.Combine(exportPath, fileName);

            // 确保文件名唯一
            filePath = GetUniqueFilePath(filePath);

            // 导出Excel
            using (var package = new ExcelPackage())
            {
                var worksheet = package.Workbook.Worksheets.Add("Sheet1");

                // 写入列标题
                WriteHeaders(worksheet, dataTable, customHeaders);

                // 写入数据行
                WriteData(worksheet, dataTable);

                // 应用格式
                ApplyFormatting(worksheet, dataTable);

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

            // 返回导出结果
            return ExportResult.SuccessResult(filePath, fileName, dataTable.Rows.Count);
        }
        catch (Exception ex)
        {
            return ExportResult.Error($"导出失败: {ex.Message}");
        }
    }
      
    /// <summary>
    /// 获取唯一文件名
    /// </summary>
    private static string GetUniqueFilePath(string filePath)
    {
        if (!File.Exists(filePath))
            return filePath;

        string directory = Path.GetDirectoryName(filePath);
        string fileNameWithoutExt = Path.GetFileNameWithoutExtension(filePath);
        string extension = Path.GetExtension(filePath);

        int counter = 1;
        string newFilePath;

        do
        {
            newFilePath = Path.Combine(directory,
                $"{fileNameWithoutExt}_{counter}{extension}");
            counter++;
        } while (File.Exists(newFilePath));

        return newFilePath;
    }

    /// <summary>
    /// 写入列标题
    /// </summary>
    private static void WriteHeaders(ExcelWorksheet worksheet, DataTable dataTable,
                                   Dictionary<string, string> customHeaders)
    {
        for (int i = 0; i < dataTable.Columns.Count; i++)
        {
            string headerText = dataTable.Columns[i].ColumnName;

            if (customHeaders != null && customHeaders.ContainsKey(headerText))
            {
                headerText = customHeaders[headerText];
            }

            worksheet.Cells[1, i + 1].Value = headerText;
        }

        // 设置标题样式
        using (var range = worksheet.Cells[1, 1, 1, dataTable.Columns.Count])
        {
            range.Style.Font.Bold = true;
            range.Style.Font.Size = 11;
            range.Style.Fill.PatternType = ExcelFillStyle.Solid;
            range.Style.Fill.BackgroundColor.SetColor(Color.LightBlue);
            range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
        }
    }

    /// <summary>
    /// 写入数据
    /// </summary>
    private static void WriteData(ExcelWorksheet worksheet, DataTable dataTable)
    {
        for (int row = 0; row < dataTable.Rows.Count; row++)
        {
            for (int col = 0; col < dataTable.Columns.Count; col++)
            {
                object value = dataTable.Rows[row][col];

                if (value == DBNull.Value || value == null)
                {
                    worksheet.Cells[row + 2, col + 1].Value = "";
                }
                else if (dataTable.Columns[col].DataType == typeof(DateTime))
                {
                    worksheet.Cells[row + 2, col + 1].Value = value;
                    worksheet.Cells[row + 2, col + 1].Style.Numberformat.Format = "yyyy-mm-dd hh:mm:ss";
                }
                else if (dataTable.Columns[col].DataType == typeof(bool))
                {
                    worksheet.Cells[row + 2, col + 1].Value = (bool)value ? "是" : "否";
                }
                else
                {
                    worksheet.Cells[row + 2, col + 1].Value = value;
                }
            }
        }
    }

    /// <summary>
    /// 应用格式
    /// </summary>
    private static void ApplyFormatting(ExcelWorksheet worksheet, DataTable dataTable)
    {
        if (worksheet.Dimension == null) return;

        // 设置数据区域边框
        using (var range = worksheet.Cells[worksheet.Dimension.Address])
        {
            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;
        }

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

    /// <summary>
    /// 获取默认导出目录
    /// </summary>
    public static string GetDefaultExportPath(string filename)
    {
        string desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
        string exportFolder = Path.Combine(desktopPath, filename);

        if (!Directory.Exists(exportFolder))
        {
            Directory.CreateDirectory(exportFolder);
        }

        return exportFolder;
    }
}

/// <summary>
/// 导出结果
/// </summary>
public class ExportResult
{
    public bool Success { get; set; }
    public string Message { get; set; }
    public string FilePath { get; set; }
    public string FileName { get; set; }
    public string FullPath { get; set; }
    public int RecordCount { get; set; }
    public DateTime ExportTime { get; set; }
    public long FileSize { get; set; }

    public static ExportResult SuccessResult(string filePath, string fileName, int recordCount)
    {
        var fileInfo = new FileInfo(filePath);

        return new ExportResult
        {
            Success = true,
            Message = "导出成功",
            FilePath = filePath,
            FileName = fileName,
            FullPath = filePath,
            RecordCount = recordCount,
            ExportTime = DateTime.Now,
            FileSize = fileInfo.Exists ? fileInfo.Length : 0
        };
    }

    public static ExportResult Error(string errorMessage)
    {
        return new ExportResult
        {
            Success = false,
            Message = errorMessage,
            ExportTime = DateTime.Now
        };
    }
}