public static Byte[] RenderDataToExcel<T>(List<T> SourceList, List<String> filter) where T : new() { XSSFWorkbook workbook = null; MemoryStream ms = null; ISheet sheet = null; XSSFRow headerRow = null; try { workbook = new XSSFWorkbook(); ms = new MemoryStream(); sheet = workbook.CreateSheet(); headerRow = (XSSFRow)sheet.CreateRow(0); PropertyInfo[] arrProperty = RemoveFilterColumn<T>(filter); PropertyInfo pi = null; for (int i = 0; i < arrProperty.Length; i++) { pi = arrProperty[i]; headerRow.CreateCell(i).SetCellValue(GetPropertyDescription(pi)); } int rowIndex = 1; for (int i = 0; i < SourceList.Count; i++) { XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex); for (int j = 0; j < arrProperty.Length; j++) { pi = arrProperty[j]; object piValue = pi.GetValue(SourceList[i], null); if (piValue == null) { dataRow.CreateCell(j).SetCellValue(""); continue; } Type pitype = pi.PropertyType; if (pitype.Name.ToLower().Contains("nullable")) { pitype = Nullable.GetUnderlyingType(pitype); } //var rowNumberAttr = pi.GetCustomAttributes(typeof(Attribute), false); //if (rowNumberAttr != null && rowNumberAttr.Length > 0) //{ // dataRow.CreateCell(j).SetCellValue((i + 1).ToString()); // continue; //} if (pitype == typeof(bool)) { dataRow.CreateCell(j).SetCellValue(Convert.ToBoolean(piValue) ? "是" : "否"); continue; } if (pitype.IsEnum) { dataRow.CreateCell(j).SetCellValue(EnumHelper.GetDescription(pitype, Convert.ToInt32(piValue))); continue; } if (pitype == typeof(DateTime) || pitype == typeof(DateTime?)) { //var showDateTimeAttr = pi.GetCustomAttributes(typeof(DateTimeFormatAttribute), false); //if (showDateTimeAttr != null && showDateTimeAttr.Length > 0) //{ // DateTime nowtime = DateTime.Parse(piValue.ToString()); // arrData[i + 1, j] = nowtime.ToString((showDateTimeAttr[0] as DateTimeFormatAttribute).DataFormatString); // continue; //} //default datetime showformater dataRow.CreateCell(j).SetCellValue(DateTime.Parse(piValue.ToString()).ToString("yyyy-MM-dd HH:mm:ss")); continue; } dataRow.CreateCell(j).SetCellValue(piValue.ToString()); } ++rowIndex; } //列宽自适应，只对英文和数字有效 这个动作比较耗时间 //for (int i = 0; i <= arrProperty.Length; ++i) // sheet.AutoSizeColumn(i); workbook.Write(ms); ms.Flush(); return ms.ToArray(); } catch (Exception ex) { Log.loggeremail.Error("RenderDataTableToExcel Exception："+ex.Message); return null; } finally { ms.Close(); sheet = null; headerRow = null; workbook = null; } } public static void SaveListToExcel<T>(List<T> SourceList, List<String> filter, string filePath) where T : new() { XSSFWorkbook workbook = null; ISheet sheet = null; XSSFRow headerRow = null; try { workbook = new XSSFWorkbook(); sheet = workbook.CreateSheet(); headerRow = (XSSFRow)sheet.CreateRow(0); PropertyInfo[] arrProperty = RemoveFilterColumn<T>(filter); PropertyInfo pi = null; for (int i = 0; i < arrProperty.Length; i++) { pi = arrProperty[i]; headerRow.CreateCell(i).SetCellValue(GetPropertyDescription(pi)); } int rowIndex = 1; for (int i = 0; i < SourceList.Count; i++) { XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex); for (int j = 0; j < arrProperty.Length; j++) { pi = arrProperty[j]; object piValue = pi.GetValue(SourceList[i], null); if (piValue == null) { dataRow.CreateCell(j).SetCellValue(""); continue; } Type pitype = pi.PropertyType; if (pitype.Name.ToLower().Contains("nullable")) { pitype = Nullable.GetUnderlyingType(pitype); } //var rowNumberAttr = pi.GetCustomAttributes(typeof(Attribute), false); //if (rowNumberAttr != null && rowNumberAttr.Length > 0) //{ // dataRow.CreateCell(j).SetCellValue((i + 1).ToString()); // continue; //} if (pitype == typeof(bool)) { dataRow.CreateCell(j).SetCellValue(Convert.ToBoolean(piValue) ? "是" : "否"); continue; } if (pitype.IsEnum) { dataRow.CreateCell(j).SetCellValue(EnumHelper.GetDescription(pitype, Convert.ToInt32(piValue))); continue; } if (pitype == typeof(DateTime) || pitype == typeof(DateTime?)) { //var showDateTimeAttr = pi.GetCustomAttributes(typeof(DateTimeFormatAttribute), false); //if (showDateTimeAttr != null && showDateTimeAttr.Length > 0) //{ // DateTime nowtime = DateTime.Parse(piValue.ToString()); // arrData[i + 1, j] = nowtime.ToString((showDateTimeAttr[0] as DateTimeFormatAttribute).DataFormatString); // continue; //} //default datetime showformater dataRow.CreateCell(j).SetCellValue(DateTime.Parse(piValue.ToString()).ToString("yyyy-MM-dd HH:mm:ss")); continue; } dataRow.CreateCell(j).SetCellValue(piValue.ToString()); } ++rowIndex; } //列宽自适应，只对英文和数字有效 这个动作比较耗时间 //for (int i = 0; i <= arrProperty.Length; ++i) // sheet.AutoSizeColumn(i); using (var file = new FileStream(filePath, FileMode.Create)) { workbook.Write(file); file.Close(); file.Dispose(); } } catch (Exception ex) { throw ex; } finally { sheet = null; headerRow = null; workbook = null; } } /// <summary> /// 创建一个excel /// </summary> /// <returns></returns> public static XSSFWorkbook CreateXSSFWorkbook() { XSSFWorkbook xssfworkbook = new XSSFWorkbook(); return xssfworkbook; } /// <summary> /// 创建一个sheet /// </summary> /// <param name="hssfworkbook">excel</param> /// <param name="sheetName">sheet名称</param> /// <param name="isFreezePane">是否存在冻结</param> /// <param name="colSplit"></param> /// <param name="rowSplit">行数</param> /// <param name="leftmostColumn"></param> /// <param name="topRow">顶上N行</param> /// <returns></returns> public static ISheet CreateSheet(XSSFWorkbook xssfworkbook, string sheetName, bool isFreezePane = false, int colSplit = 0, int rowSplit = 0, int leftmostColumn = 0, int topRow = 0) { ISheet sheet1 = xssfworkbook.CreateSheet(sheetName); if (isFreezePane) { sheet1.CreateFreezePane(colSplit, rowSplit, leftmostColumn, topRow); } return sheet1; } public static IRow CreateRow(ISheet sheet, int rowIndex) { IRow row = sheet.CreateRow(rowIndex); return row; } public static ICell CreateCell(XSSFWorkbook xssfworkbook, IRow row, int cellIndex, string cellValue, bool isLock = true) { ICell cell = row.CreateCell(cellIndex); cell.SetCellValue(cellValue); //加锁 var locked = xssfworkbook.CreateCellStyle(); locked.IsLocked = isLock; cell.CellStyle = locked; return cell; } public static ICellStyle LockedRow(XSSFWorkbook xssfworkbook) { var locked = xssfworkbook.CreateCellStyle(); locked.IsLocked = true; return locked; } public static ICellStyle UnLockedRow(XSSFWorkbook xssfworkbook) { var locked = xssfworkbook.CreateCellStyle(); locked.IsLocked = false; return locked; } /// <summary> /// 获取单元格样式 /// </summary> /// <param name="hssfworkbook">Excel操作类</param> /// <param name="font">单元格字体</param> /// <param name="fillForegroundColor">图案的颜色</param> /// <param name="fillPattern">图案样式</param> /// <param name="fillBackgroundColor">单元格背景</param> /// <param name="ha">垂直对齐方式</param> /// <param name="va">垂直对齐方式</param> /// <returns></returns> public static ICellStyle GetCellStyle(XSSFWorkbook hssfworkbook, IFont font, HSSFColor fillForegroundColor, FillPattern fillPattern, HSSFColor fillBackgroundColor, NPOI.SS.UserModel.HorizontalAlignment ha, VerticalAlignment va, bool hasBorder) { ICellStyle cellstyle = hssfworkbook.CreateCellStyle(); cellstyle.FillPattern = fillPattern; cellstyle.Alignment = ha; cellstyle.VerticalAlignment = va; if (fillForegroundColor != null) { cellstyle.FillForegroundColor = fillForegroundColor.Indexed; } if (fillBackgroundColor != null) { cellstyle.FillBackgroundColor = fillBackgroundColor.Indexed; } if (font != null) { cellstyle.SetFont(font); } if (hasBorder) { //有边框 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; } /// <summary> /// 合并单元格 /// </summary> /// <param name="sheet">要合并单元格所在的sheet</param> /// <param name="rowstart">开始行的索引</param> /// <param name="rowend">结束行的索引</param> /// <param name="colstart">开始列的索引</param> /// <param name="colend">结束列的索引</param> public static void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend) { CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend); sheet.AddMergedRegion(cellRangeAddress); } /// <summary> /// 建立下拉，验证数据有效性 /// </summary> /// <param name="hssfworkbook"></param> /// <param name="sheet"></param> /// <param name="firstRow"></param> /// <param name="lastRow"></param> /// <param name="firstCol"></param> /// <param name="lastCol"></param> /// <param name="refersToFormula"></param> /// <param name="XSSFName"></param> public static void SetValidationData(XSSFWorkbook hssfworkbook, ISheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, string refersToFormula, string XSSFName) { //数据有效性 下拉 XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet as XSSFSheet); //位置 CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol); XSSFName range = (XSSFName)hssfworkbook.CreateName(); range.RefersToFormula = refersToFormula; range.NameName = XSSFName; XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint(XSSFName); XSSFDataValidation dataValidate = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, regions); sheet.AddValidationData(dataValidate); }
调用：
List<String> filter = new List<string>(); filter.Add("LableBatchNo");//过滤列 byte[] byteList = ExcelHelper.RenderToExcel<SecurityLabelModel>(allList, filter); MemoryStream stream = new MemoryStream(byteList); stream.Seek(0, 0); return new FileStreamResult(stream, "application/vnd.ms-excel") { FileDownloadName = HttpUtility.UrlPathEncode(名称 + DateTime.Now.ToString("yyyyMMddHHmmssfff") + 后缀) };