NPOI _导出exl(简单应用)
1. 导出exl表格,创建表格导出到客户端
public static MemoryStream Export_Table<T>(List<T> datalist) { MemoryStream ms = new MemoryStream(); var members = typeof(T).GetProperties(); var workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(); NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0); int order = 1; foreach (var meber in members)//初始化标题 { string titlevalue = ""; var name = meber.GetCustomAttributes(typeof(TableAttribulate), false); if (name.Length == 0) continue; var pro = name[0] as TableAttribulate; if (pro == null) continue; titlevalue = pro.CName; var cell = headerRow.CreateCell(order); cell.SetCellValue(titlevalue); if(pro.Weight==0) sheet.AutoSizeColumn(order,true); else { sheet.SetColumnWidth(order,pro.Weight); } cell.CellStyle = GetStyle(workbook); cell.CellStyle.Alignment = pro.HorizontalAlignment; var cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; cell.CellStyle.SetFont(cellfont); order++; } int rowIndex = 1; foreach (var row in datalist) { NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex); int colIndex = 0; var cellindex = dataRow.CreateCell(colIndex); cellindex.SetCellValue(rowIndex); cellindex.CellStyle = GetStyle(workbook); var cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal; cellfont.Color = NPOI.HSSF.Util.HSSFColor.LightBlue.Index; cellindex.CellStyle.SetFont(cellfont); colIndex++; foreach (var meber in members) { var name = meber.GetCustomAttributes(typeof(TableAttribulate), false); if (name.Length == 0) continue; var pro = name[0] as TableAttribulate; if (pro == null) continue; //if (pro.Weight == 0) // sheet.AutoSizeColumn(colIndex, true); //设置成动态的,自动大小时就很慢,这里注掉,如果要用,想别的办法 var cell = dataRow.CreateCell(colIndex); var mebervalue = meber.GetValue(row); cell.SetCellValue(mebervalue == null ? "" : mebervalue.ToString()); cell.CellStyle = GetStyle(workbook); cell.CellStyle.Alignment = pro.HorizontalAlignment; colIndex++; } rowIndex++; } workbook.Write(ms); ms.Seek(0, SeekOrigin.Begin); return ms;// }
2.代码总的特性
public class TableAttribulate:Attribute { public TableAttribulate(string name, int weight = 0, NPOI.SS.UserModel.HorizontalAlignment hoalign = NPOI.SS.UserModel.HorizontalAlignment.Center) { CName = name; Weight = weight; HorizontalAlignment = hoalign; } public string CName { get; set; } public int Weight { get; set; } public NPOI.SS.UserModel.HorizontalAlignment HorizontalAlignment { get; set; }//对齐方式 }
3.函数样式
public static NPOI.SS.UserModel.ICellStyle GetStyle(NPOI.HSSF.UserModel.HSSFWorkbook workbook) { var cs = workbook.CreateCellStyle(); cs.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("@"); cs.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cs.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cs.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cs.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cs.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cs.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; var cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal; cs.SetFont(cellfont); return cs; }
老版的:
public static NPOI.SS.UserModel.ICellStyle GetDefaultStyle(XSSFWorkbook workbook) { var cs = workbook.CreateCellStyle(); cs.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("@"); cs.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cs.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cs.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cs.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cs.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cs.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; var cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal; cs.SetFont(cellfont); return cs; }
4.js调用实例
function func_queryExportExl() { var url = '@Url.Action("ExportExcel", "ResidentsSigned"); window.location.href = url; }
5.后端调用
List<TableExel> query = dbContext.Database.SqlQuery<TableExel>(sql).ToList();
var ms = CPSYS.Web.Common.FileOption.Export_Table<TableExel>(query); return File(ms, "application/vnd.ms-excel", "text.xls");
6.合并单元格后的边框格式问题
public void SetTyleThin(NPOI.HSSF.UserModel.HSSFWorkbook workbook,NPOI.SS.UserModel.ISheet sheet,int lastrow) { NPOI.SS.Util.CellRangeAddress region = new NPOI.SS.Util.CellRangeAddress(4, lastrow, 0, 23); ((NPOI.HSSF.UserModel.HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); }
7.读入模板
HSSFWorkbook workbook = null; var path = AppDomain.CurrentDomain.BaseDirectory + "/ExcelTemplate/重点孕妇登记随访本.xls"; FileStream filest = new FileStream(path, FileMode.Open, FileAccess.Read); workbook = new HSSFWorkbook(filest); filest.Close(); NPOI.SS.UserModel.ISheet sheet = workbook.GetSheet("重点孕妇登记随访本");