使用NPOI导出数据到EXCEl
1、首先使用nuget下载npoi的动态库，如图：
2、添加引用：
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
3、编写代码，将datatbale转化为NPOI的workbook
/// <summary>
/// 将datatbale的数据转化为workbook，并设置相关参数
/// </summary>
/// <param name="dt"></param>
/// <param name="workbook"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public static IWorkbook DataTableToExcel(DataTable dt, IWorkbook workbook, string sheetName)
{
ISheet sheet = workbook.CreateSheet(sheetName);
IRow headerRow = sheet.CreateRow(0);
ICellStyle cellstyle = workbook.CreateCellStyle();
cellstyle.BorderBottom = BorderStyle.Thin;
cellstyle.BorderLeft = BorderStyle.Thin;
cellstyle.BorderRight = BorderStyle.Thin;
cellstyle.BorderTop = BorderStyle.Thin;
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 12;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
cellstyle.SetFont(font);
for (int j = 0; j < dt.Columns.Count; j++)
{
string columnName = dt.Columns[j].ColumnName;
ICell cellColumn = headerRow.CreateCell(j);
cellColumn.SetCellValue(columnName);
cellColumn.CellStyle = cellstyle;
int length = Encoding.UTF8.GetBytes(columnName).Length;
sheet.SetColumnWidth(j, (length + 1) * 256);
}
ICellStyle cellstyle1 = workbook.CreateCellStyle();
cellstyle1.BorderBottom = BorderStyle.Thin;
cellstyle1.BorderLeft = BorderStyle.Thin;
cellstyle1.BorderRight = BorderStyle.Thin;
cellstyle1.BorderTop = BorderStyle.Thin;
cellstyle1.VerticalAlignment = VerticalAlignment.Center;
cellstyle1.Alignment = HorizontalAlignment.Center;
IFont font1 = workbook.CreateFont();
font1.FontHeightInPoints = 12;
font1.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal;
cellstyle1.SetFont(font1);
for (int a = 0; a < dt.Rows.Count; a++)
{
DataRow dr = dt.Rows[a];
IRow row = sheet.CreateRow(a + 1);
for (int b = 0; b < dt.Columns.Count; b++)
{
ICell cellRow = row.CreateCell(b);
string str = dr[b] != DBNull.Value ? dr[b].ToString() : string.Empty;
//因导出格式问题，不再转换 updated by dhp at 2018.12.20
//double db = 0;
//if (!str.Contains(",") && double.TryParse(str, out db)) cellRow.SetCellValue(db);
//else cellRow.SetCellValue(str);
cellRow.SetCellValue(str);
cellRow.CellStyle = cellstyle1;
int columnWidth = sheet.GetColumnWidth(b) / 256;
int length = Encoding.UTF8.GetBytes(str).Length;//获取当前单元格的内容宽度
if (columnWidth < length + 1)
{
columnWidth = length + 1;
}
if (columnWidth > 255) columnWidth = 255;
sheet.SetColumnWidth(b, columnWidth * 256);
}
}
sheet.CreateFreezePane(0, 1, 0, 1);
return workbook;
}
4、调用方法，将数据导入到excel
/// <summary>
/// 根据DataTabled导出Excel
/// </summary>
/// <param name="sheetName"></param>
/// <param name="dt">需要生成Excel的数据，形式为</param>
/// <param name="fileFullPath"></param>
/// <returns></returns>
public static string ExportToExcel(string sheetName, DataTable dt, string fileFullPath)
{
IWorkbook workbook = new XSSFWorkbook();
workbook = DataTableToExcel(dt, workbook, sheetName);
using (FileStream fs = System.IO.File.Create(fileFullPath))
{
workbook.Write(fs);
}
return fileFullPath;
}
5、增加扩展方法，泛型集合导入到excel
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sheetName"></param>
/// <param name="list"></param>
/// <param name="fileFullPath"></param>
/// <returns></returns>
public static string ExportToExcelByList<T>(string sheetName, List<T> list, string fileFullPath)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet(sheetName);
System.Reflection.PropertyInfo[] properties = list[0].GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
ICellStyle cellstyle = workbook.CreateCellStyle();
cellstyle.BorderBottom = BorderStyle.Thin;
cellstyle.BorderLeft = BorderStyle.Thin;
cellstyle.BorderRight = BorderStyle.Thin;
cellstyle.BorderTop = BorderStyle.Thin;
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;
IRow headerRow = sheet.CreateRow(0);
int j = 0;
foreach (System.Reflection.PropertyInfo item in properties)
{
string columnName = item.Name;
ICell cellColumn = headerRow.CreateCell(j);
cellColumn.CellStyle = cellstyle;
cellColumn.SetCellValue(columnName);
int length = Encoding.UTF8.GetBytes(columnName).Length;
sheet.SetColumnWidth(j, (length + 1) * 256);
j++;
}
for (int a = 0; a < list.Count; a++)
{
T t = list[a];
IRow row = sheet.CreateRow(a + 1);
int b = 0;
// System.Reflection.PropertyInfo[] properties1 = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
foreach (System.Reflection.PropertyInfo item in properties)
{
ICell cellRow = row.CreateCell(b);
string val = "";
try
{
val = item.GetValue(t, null).ToString();
}
catch
{
val = "";
}
cellRow.SetCellValue(val);
cellRow.CellStyle = cellstyle;
int columnWidth = sheet.GetColumnWidth(b) / 256;
int length = Encoding.UTF8.GetBytes(cellRow.StringCellValue).Length;//获取当前单元格的内容宽度
if (columnWidth < length + 1)
{
columnWidth = length + 1;
}
if (columnWidth > 255) columnWidth = 255;
sheet.SetColumnWidth(b, columnWidth * 256);
b++;
}
}
using (FileStream fs = System.IO.File.Create(fileFullPath))
{
workbook.Write(fs);
}
return fileFullPath;
}
6、增加扩展方法，dataset数据导入到excel
/// <summary>
///
/// </summary>
/// <param name="ds"></param>
/// <param name="fileFullPath"></param>
/// <returns></returns>
public static string ExportToExcelByDataSet(DataSet ds, string fileFullPath, params string[] sheetname)
{
IWorkbook workbook = new XSSFWorkbook();
for (int i = 0; i < ds.Tables.Count; i++)
{
string sn = "sheet" + i;
if (sheetname != null && i < sheetname.Length) sn = sheetname[i];
workbook = DataTableToExcel(ds.Tables[i], workbook, sn);
}
using (FileStream fs = System.IO.File.Create(fileFullPath))
{
workbook.Write(fs);
}
return fileFullPath;
}
7、新建类，将上述方法放入，在需要使用的地方调用即可。