NPOI 导出到Excel时 解决数字为文本需要再转换的问题

/// <summary>
/// 单个DataTable导出成Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="file">导出路径(包括文件名与扩展名)</param>
public static void TableToExcel(DataTable dt, string file)
{
    IWorkbook workbook;
    string fileExt = Path.GetExtension(file).ToLower();
    if (fileExt == ".xlsx")
    {
        workbook = new XSSFWorkbook();
    }
    else if (fileExt == ".xls")
    {
        workbook = new HSSFWorkbook();
    }
    else
    {
        workbook = null;
    }
    if (workbook == null)
    {
        return;
    }

    ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);

    ICellStyle style = workbook.CreateCellStyle();//设置表格样式
    style.BorderBottom = BorderStyle.Thin;//下边框线（给表格加入背景颜色后，就会覆盖原来的边框，直接设置边框样式可以解决这一问题）
    style.BorderLeft = BorderStyle.Thin; //左边框线
    style.BorderRight = BorderStyle.Thin;//右边框线
    style.BorderTop = BorderStyle.Thin;//上边框线

    sheet.PrintSetup.PaperSize = 9;//A4纸张打印
    sheet.PrintSetup.Landscape = true;//横向打印（默认为false即竖向）

    //设置自动调整为一页宽度
    sheet.Autobreaks = false;//必须设置
    sheet.FitToPage = true; //一页宽   
    sheet.PrintSetup.FitWidth = 1;
    sheet.PrintSetup.FitHeight = short.MaxValue;

    //表头、标题行  
    IRow row = sheet.CreateRow(0);
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        ICell cell = row.CreateCell(i);
        cell.SetCellValue(dt.Columns[i].ColumnName);
        cell.CellStyle = style;
    }



    //数据  
    for (int j = 0; j < dt.Rows.Count; j++)
    {
        IRow row1 = sheet.CreateRow(j + 1);
        for (int k = 0; k < dt.Columns.Count; k++)
        {
            ICell cell = row1.CreateCell(k);
            cell.CellStyle = style;

            if (dt.Columns[k].DataType.Equals(typeof(bool)) && bool.TryParse(dt.Rows[j][k].ToString(), out bool b))
            {
                cell.SetCellValue(b);
            }
            else if (double.TryParse(dt.Rows[j][k].ToString(), out double d))//数字格式
            {
                cell.SetCellValue(d);
            }
            else
            {
                cell.SetCellValue(dt.Rows[j][k].ToString());
            }
        }

    }

    //转为字节数组  
    MemoryStream stream = new MemoryStream();
    workbook.Write(stream);
    var buf = stream.ToArray();

    //保存为Excel文件  
    using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
    {
        fs.Write(buf, 0, buf.Length);
        fs.Flush();
    }
}

/// <summary>
/// 多个Datable导出成Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="file">导出路径(包括文件名与扩展名)</param>
public static void TablesToExcel(List<DataTable> dts, string file)
{
    IWorkbook workbook;
    string fileExt = Path.GetExtension(file).ToLower();
    if (fileExt == ".xlsx")
    {
        workbook = new XSSFWorkbook();
    }
    else if (fileExt == ".xls")
    {
        workbook = new HSSFWorkbook();
    }
    else
    {
        workbook = null;
    }
    if (workbook == null)
    {
        return;
    }
    for (int i = 0; i < dts.Count; i++)
    {
        CreateSheet(workbook, dts[i], i);//默认导出为横向Excel
    }

    //转为字节数组  
    MemoryStream stream = new MemoryStream();
    workbook.Write(stream);
    var buf = stream.ToArray();

    //保存为Excel文件  
    using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
    {
        fs.Write(buf, 0, buf.Length);
        fs.Flush();
    }
}

public static void CreateSheet(IWorkbook workbook, DataTable dt, int num)
{
    ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet" + num) : workbook.CreateSheet(dt.TableName);

    ICellStyle style = workbook.CreateCellStyle();//设置表格样式
    style.BorderBottom = BorderStyle.Thin;//下边框线（给表格加入背景颜色后，就会覆盖原来的边框，直接设置边框样式可以解决这一问题）
    style.BorderLeft = BorderStyle.Thin; //左边框线
    style.BorderRight = BorderStyle.Thin;//右边框线
    style.BorderTop = BorderStyle.Thin;//上边框线

    sheet.PrintSetup.PaperSize = 9;//A4纸张打印
    sheet.PrintSetup.Landscape = true;//横向打印（默认为false即竖向）

    //设置自动调整为一页宽度
    sheet.Autobreaks = false;//必须设置
    sheet.FitToPage = true; //一页宽   
    sheet.PrintSetup.FitWidth = 1;
    sheet.PrintSetup.FitHeight = short.MaxValue;

    //表头  
    IRow row = sheet.CreateRow(0);
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        ICell cell = row.CreateCell(i);
        cell.SetCellValue(dt.Columns[i].ColumnName);
        cell.CellStyle = style;
    }

    //数据  
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        IRow row1 = sheet.CreateRow(i + 1);
        for (int j = 0; j < dt.Columns.Count; j++)
        {
            ICell cell = row1.CreateCell(j);
            cell.CellStyle = style;

            if (dt.Columns[j].DataType.Equals(typeof(bool)) && bool.TryParse(dt.Rows[i][j].ToString(), out bool b))
            {
                cell.SetCellValue(b);
            }
            else if (double.TryParse(dt.Rows[i][j].ToString(), out double d))//数字格式
            {
                cell.SetCellValue(d);
            }
            else
            {
                cell.SetCellValue(dt.Rows[i][j].ToString());
            }
        }
    }

    for (int columnIndex = 0; columnIndex < dt.Rows.Count; columnIndex++)
    {
        sheet.AutoSizeColumn(columnIndex); //列宽自适应
    }

}