npoi workbook 的 cellstyle 创建不能超过4000的解决方法

本文主要是介绍npoi workbook 的 cellstyle 创建不能超过4000的解决方法，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

代码如下:

public static void CopySheet(ISheet fromSheet, ISheet toSheet, bool copyValueFlag){//合并区域处理  MergerRegion(fromSheet, toSheet);System.Collections.IEnumerator rows = fromSheet.GetRowEnumerator();while (rows.MoveNext()){IRow row = null;if (fromSheet.Workbook is HSSFWorkbook)row = rows.Current as HSSFRow;elserow = rows.Current as HSSFRow;IRow newRow = toSheet.CreateRow(row.RowNum);CopyRow(fromSheet.Workbook, toSheet.Workbook, row, newRow, copyValueFlag);}}

 

public static void CopyRow(IWorkbook fromWb, IWorkbook toWb, IRow fromRow, IRow toRow, bool copyValueFlag){System.Collections.IEnumerator cells = fromRow.GetEnumerator(); //.GetRowEnumerator();  toRow.Height = fromRow.Height;while (cells.MoveNext()){ICell cell = null;//ICell cell = (wb is HSSFWorkbook) ? cells.Current as HSSFCell : cells.Current as NPOI.XSSF.UserModel.XSSFCell;  if (toWb is HSSFWorkbook)cell = cells.Current as HSSFCell;elsecell = cells.Current as HSSFCell;ICell newCell = toRow.CreateCell(cell.ColumnIndex);CopyCell(fromWb, toWb, cell, newCell, copyValueFlag);}}

public static void CopyCell(IWorkbook fromWb,IWorkbook toWb, ICell srcCell, ICell distCell, bool copyValueFlag){ICellStyle newstyle = toWb.CreateCellStyle();  CopyCellStyle(fromWb,toWb, srcCell.CellStyle, newstyle);//样式  distCell.CellStyle = newstyle;//评论  if (srcCell.CellComment != null){distCell.CellComment = srcCell.CellComment;}// 不同数据类型处理  CellType srcCellType = srcCell.CellType;distCell.SetCellType(srcCellType);if (copyValueFlag){if (srcCellType == CellType.Numeric){if (HSSFDateUtil.IsCellDateFormatted(srcCell)){distCell.SetCellValue(srcCell.DateCellValue);}else{distCell.SetCellValue(srcCell.NumericCellValue);}}else if (srcCellType == CellType.String){distCell.SetCellValue(srcCell.RichStringCellValue);}else if (srcCellType == CellType.Blank){// nothing21  }else if (srcCellType == CellType.Boolean){distCell.SetCellValue(srcCell.BooleanCellValue);}else if (srcCellType == CellType.Error){distCell.SetCellErrorValue(srcCell.ErrorCellValue);}else if (srcCellType == CellType.Formula){distCell.SetCellFormula(srcCell.CellFormula);}else{// nothing29  }}}

public static void CopyCellStyle(IWorkbook fromWb, IWorkbook toWb, ICellStyle fromStyle, ICellStyle toStyle){toStyle.Alignment = fromStyle.Alignment;//边框和边框颜色  toStyle.BorderBottom = fromStyle.BorderBottom;toStyle.BorderLeft = fromStyle.BorderLeft;toStyle.BorderRight = fromStyle.BorderRight;toStyle.BorderTop = fromStyle.BorderTop;toStyle.TopBorderColor = fromStyle.TopBorderColor;toStyle.BottomBorderColor = fromStyle.BottomBorderColor;toStyle.RightBorderColor = fromStyle.RightBorderColor;toStyle.LeftBorderColor = fromStyle.LeftBorderColor;//背景和前景  toStyle.FillBackgroundColor = fromStyle.FillBackgroundColor;toStyle.FillForegroundColor = fromStyle.FillForegroundColor;toStyle.DataFormat = fromStyle.DataFormat;toStyle.FillPattern = fromStyle.FillPattern;toStyle.IsHidden = fromStyle.IsHidden;toStyle.Indention = fromStyle.Indention;//首行缩进  toStyle.IsLocked = fromStyle.IsLocked;toStyle.Rotation = fromStyle.Rotation;//旋转  toStyle.VerticalAlignment = fromStyle.VerticalAlignment;toStyle.WrapText = fromStyle.WrapText;//IFont fromFont = fromStyle.GetFont(fromWb);//字体//toStyle.SetFont(fromFont);}

网上有方法说要把CreateCellStyle放在循环外面,这个方法不适用于复制的工作表(Sheet)较多(100个左右)的场景,且不是解决问题的根本方法.

为了最大限度的复用CellStyle,且控制在4000个之内.构造了一个缓存对象.来缓存创建的CellStyle,所有的CellStyle获取,先通过从缓存取,如果不存在再创建.代码如下:

public class CellStyleCache:ArrayList{public ICellStyle this[ICellStyle fromStyle]{get{foreach (object o in this){ICellStyle toStyle = o as ICellStyle;if (toStyle.Alignment == fromStyle.Alignment//边框和边框颜色  && toStyle.BorderBottom == fromStyle.BorderBottom&& toStyle.BorderLeft == fromStyle.BorderLeft&& toStyle.BorderRight == fromStyle.BorderRight&& toStyle.BorderTop == fromStyle.BorderTop&& toStyle.TopBorderColor == fromStyle.TopBorderColor&& toStyle.BottomBorderColor == fromStyle.BottomBorderColor&& toStyle.RightBorderColor == fromStyle.RightBorderColor&& toStyle.LeftBorderColor == fromStyle.LeftBorderColor//背景和前景  && toStyle.FillBackgroundColor == fromStyle.FillBackgroundColor&& toStyle.FillForegroundColor == fromStyle.FillForegroundColor                            && toStyle.IsHidden == fromStyle.IsHidden                            && toStyle.VerticalAlignment == fromStyle.VerticalAlignment//&& toStyle.WrapText == fromStyle.WrapText//&& toStyle.Indention == fromStyle.Indention//首行缩进  //&& toStyle.IsLocked == fromStyle.IsLocked//&& toStyle.Rotation == fromStyle.Rotation//旋转  //&& toStyle.DataFormat == fromStyle.DataFormat//&& toStyle.FillPattern == fromStyle.FillPattern ){return toStyle;}}return null;}set{this.Add(fromStyle);}}}

public static ICellStyle CreateCellStyle(IWorkbook wb,ICellStyle fromStyle){ICellStyle newStyle = styleCache[fromStyle];if (newStyle == null){newStyle = wb.CreateCellStyle();styleCache[newStyle] = newStyle;}//ICellStyle newStyle = wb.CreateCellStyle(); return newStyle;}

public static void CopyCell(IWorkbook fromWb,IWorkbook toWb, ICell srcCell, ICell distCell, bool copyValueFlag){//ICellStyle newstyle = toWb.CreateCellStyle();  ICellStyle newstyle = CreateCellStyle(toWb, srcCell.CellStyle);//复制样式CopyCellStyle(fromWb,toWb, srcCell.CellStyle, newstyle);//样式  distCell.CellStyle = newstyle;//评论  if (srcCell.CellComment != null){distCell.CellComment = srcCell.CellComment;}// 不同数据类型处理  CellType srcCellType = srcCell.CellType;distCell.SetCellType(srcCellType);if (copyValueFlag){if (srcCellType == CellType.Numeric){if (HSSFDateUtil.IsCellDateFormatted(srcCell)){distCell.SetCellValue(srcCell.DateCellValue);}else{distCell.SetCellValue(srcCell.NumericCellValue);}}else if (srcCellType == CellType.String){distCell.SetCellValue(srcCell.RichStringCellValue);}else if (srcCellType == CellType.Blank){// nothing21  }else if (srcCellType == CellType.Boolean){distCell.SetCellValue(srcCell.BooleanCellValue);}else if (srcCellType == CellType.Error){distCell.SetCellErrorValue(srcCell.ErrorCellValue);}else if (srcCellType == CellType.Formula){distCell.SetCellFormula(srcCell.CellFormula);}else{// nothing29  }}}

测试通过.

这篇关于npoi workbook 的 cellstyle 创建不能超过4000的解决方法的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---