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、新建类，将上述方法放入，在需要使用的地方调用即可。