使用C# ASP.Net与ClosedXML.Excel导出Excel数据显示异常问题

你的问题根源在MySQL存储过程的UNION ALL操作上。MySQL执行UNION ALL时会自动统一合并列的数据类型，遵循类型优先级规则——数值、日期类型的优先级高于字符串类型。你的存储过程里，_Date_Hour（日期/时间型）、_ID（数值型）、_Area（字符串型）被合并到同一列col2，MySQL会把整个col2的类型统一为数值型，导致原本的字符串数据（比如_Area的内容）被强制转换成数字（无法转换的会变成0或其他数值）。虽然数据库客户端查询时显示正常，但返回给DataTable的是数值类型数据，ClosedXML导出自然会输出数字。

修改MySQL存储过程，把所有查询的col2字段显式转为字符串类型，确保UNION ALL后的列类型统一为字符串，同时修正原存储过程中列别名写错的问题：

SELECT
    col1,
    col2 
FROM
    (
    SELECT
        '_Date_Hour' col1,
        CAST(_Date_Hour AS CHAR) AS col2
    FROM
        check_list 
    WHERE
        _ID = @ID UNION ALL
    SELECT
        '_ID' AS col1,
        CAST(_ID AS CHAR) AS col2
    FROM
        check_list
    WHERE
        _ID = @ID UNION ALL
    SELECT
        '_Area' AS col1,
        CAST(_Area AS CHAR) AS col2
    FROM
        check_list 
    WHERE
        _ID = @ID   ) src;

• 避免硬编码路径：把导出路径放到Web.config的appSettings里，方便后期修改：

  <appSettings>
    <add key="ExcelExportPath" value="D:\inetpub\wwwroot\PUBLIC\ExcelFiles\" />
  </appSettings>
  

  代码中读取：

  string appLocation = ConfigurationManager.AppSettings["ExcelExportPath"];
  

• 移除不必要的延迟：Thread.Sleep(3000)完全多余，wb.SaveAs()执行完成后文件已写入磁盘，不需要等待。

• 统一文件名变量：多次拼接文件名容易出错，提前生成变量复用：

  string fileName = $"Export_{DateTime.Now.ToString("ddMMyyyyHHmm")}_{guid}.xlsx";
  string filePath = Path.Combine(appLocation, fileName);
  

• 简化资源释放：XLWorkbook在using块中会自动释放资源，无需手动调用wb.Dispose()。

修改后的ExportDataSetToExcel方法示例：

public static void ExportDataSetToExcel(DataSet ds)
{
    string appLocation = ConfigurationManager.AppSettings["ExcelExportPath"];
    string guid = Guid.NewGuid().ToString().ToUpper().Replace("-", "_");
    string fileName = $"Export_{DateTime.Now.ToString("ddMMyyyyHHmm")}_{guid}.xlsx";
    string filePath = Path.Combine(appLocation, fileName);

    var cookie = new HttpCookie("guid", guid)
    {
        Expires = DateTime.Now.AddMinutes(3)
    };
    HttpContext.Current.Response.Cookies.Add(cookie);

    using (XLWorkbook wb = new XLWorkbook())
    {
        foreach (DataTable table in ds.Tables)
        {
            wb.Worksheets.Add(table, table.TableName);
        }
        wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        wb.Style.Font.Bold = true;
        wb.SaveAs(filePath);
    }

    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.Buffer = true;
    HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    HttpContext.Current.Response.AppendHeader("Content-Disposition", $"attachment; filename={fileName}");
    HttpContext.Current.Response.TransmitFile(filePath);

    var downloadFlagCookie = new HttpCookie("ExcelDownloadFlag", "Flag")
    {
        Expires = DateTime.Now.AddDays(1)
    };
    HttpContext.Current.Response.AppendCookie(downloadFlagCookie);
    HttpContext.Current.Response.End();
}

内容的提问来源于stack exchange，提问作者Hamamelis