NPOI导出Excel

安装npoi nuget包，在设置列宽时，不使用自动设置AutoSizeColumn，这个设了也未必准且有性能问题。

设置单元格的自定义格式，可以参考excel。

using NPOI.HSSF.UserModel;using NPOI.SS.UserModel;using System;using System.Collections.Generic;using System.Data;using System.Globalization;using System.IO;using System.Linq;using System.Text;namespace ConsoleApp1{ internal class Program {  public static void Main()  {   DataTable table = new DataTable();   table.Columns.Add("客户");   table.Columns.Add("XX份额");   table.Columns.Add("XX占比");   table.Rows.Add("科比","8000000000000", "0.9");   table.Rows.Add("科比2","8000000000000.94", "0.7");   table.Rows.Add("科比3","8000000000000.886", "0.5");   IWorkbook workbook = new HSSFWorkbook();   string fileName = @"C:\Users\s-huangsb\Desktop\xxx.xls";   ExportExcel(table, fileName, workbook);   try   {    using (FileStream file = new FileStream(fileName, FileMode.OpenOrCreate))    {     workbook.Write(file);     file.Flush();     file.Close();    }   }   catch (Exception ex)   {    //handle exception   }  }  private static void ExportExcel(DataTable table, string fileName, IWorkbook workbook)  {   ISheet sheet = workbook.CreateSheet("客户信息");   ICellStyle headercellStyle = GetHeaderStyle(workbook);   NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();   cellfont.IsBold = false;   cellfont.FontName = "宋体";   cellfont.FontHeightInPoints = 11;   ICellStyle cellStyle = GetCellStyle(workbook);   cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");   cellStyle.SetFont(cellfont);   ICellStyle numCellStyle = GetCellStyle(workbook);   numCellStyle.SetFont(cellfont);   numCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;   numCellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0.00");   ICellStyle ratioCellStyle = GetCellStyle(workbook);   ratioCellStyle.SetFont(cellfont);   ratioCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;   ratioCellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");   int iRowIndex = 0;   int icolIndex = 0;   IRow headerRow = sheet.CreateRow(iRowIndex);   foreach (DataColumn item in table.Columns)   {    ICell cell = headerRow.CreateCell(icolIndex);    cell.SetCellValue(item.ColumnName);    cell.CellStyle = headercellStyle;    icolIndex++;   }   iRowIndex++;   int iCellIndex = 0;   foreach (DataRow row in table.Rows)   {    IRow DataRow = sheet.CreateRow(iRowIndex);    foreach (DataColumn colItem in table.Columns)    {     ICell cell = DataRow.CreateCell(iCellIndex);     if (colItem.ColumnName.Contains("份额"))     {      cell.SetCellValue(ToDoubleEx(row[colItem]));      cell.CellStyle = numCellStyle;     }     else if (colItem.ColumnName.Contains("占比"))     {      cell.SetCellValue(Convert.ToDouble(row[colItem]));      cell.CellStyle = ratioCellStyle;     }     else     {      cell.SetCellValue(row[colItem].ToString());      cell.CellStyle = cellStyle;     }     iCellIndex++;    }    iCellIndex = 0;    iRowIndex++;   }      List<int> colsLength = new List<int>();   foreach (DataColumn column in table.Columns)   {    var length = table.AsEnumerable().Max(row => row[column].ToString().Length);    colsLength.Add(length);   }   AutoColumnWidth(sheet, table.Columns.Count, colsLength.ToArray(), 9);  }  private static void AutoColumnWidth(ISheet sheet, int cols, int[] colLength, int addlength)  {   for (int col = 0; col < cols; col++)   {    var columnWidth = colLength[col] * 256 + 30 * 256;    sheet.SetColumnWidth(col, columnWidth);   }  }  private static ICellStyle GetCellStyle(IWorkbook workbook)  {   ICellStyle cellStyle = workbook.CreateCellStyle();   cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;   cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;   cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;   cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;   return cellStyle;  }  private static ICellStyle GetHeaderStyle(IWorkbook workbook)  {   ICellStyle headercellStyle = workbook.CreateCellStyle();   headercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;   headercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;   headercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;   headercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;   headercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;      headercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;   headercellStyle.FillPattern = FillPattern.SolidForeground;   NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();   headerfont.IsBold = true;   headerfont.FontName = "宋体";   headerfont.FontHeightInPoints = 11;   headercellStyle.SetFont(headerfont);   return headercellStyle;  }  private static double ToDoubleEx(object obj)  {   if (obj == DBNull.Value)   {    return 0;   }   string str = obj.ToString();   if (str == null || str.Trim() == string.Empty)   {    return 0;   }   else   {    return Convert.ToDouble(str);   }  } }}

 

NPOI导出Excel