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

利用NPOI进行Excel的工作表(Sheet)复制时,如果复制的工作表(Sheet)较多(100个左右),会报告 workbook 的 cellstyle 创建不能超过4000 的错误.

The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook

代码如下:

public static void CopySheet(ISheet fromSheet, ISheet toSheet, bool copyValueFlag)
        {
            //合并区域处理  
            MergerRegion(fromSheet, toSheet);
             rows = ();
            while (())
            {

                IRow row = null;
                if ( is HSSFWorkbook)
                    row =  as HSSFRow;
                else
                    row =  as HSSFRow;
                IRow newRow = ();
                CopyRow(, , row, newRow, copyValueFlag);
                
            }
        }

 

public static void CopyRow(IWorkbook fromWb, IWorkbook toWb, IRow fromRow, IRow toRow, bool copyValueFlag)
        {
             cells = (); //.GetRowEnumerator();  
             = ;
 
            while (())
            {
                
                ICell cell = null;
                //ICell cell = (wb is HSSFWorkbook) ?  as HSSFCell :  as ;  
                if (toWb is HSSFWorkbook)
                    cell =  as HSSFCell;
                else
                    cell =  as HSSFCell;
                ICell newCell = ();
                CopyCell(fromWb, toWb, cell, newCell, copyValueFlag);
  
                
            }
        }

public static void CopyCell(IWorkbook fromWb,IWorkbook toWb, ICell srcCell, ICell distCell, bool copyValueFlag)
        {
            ICellStyle newstyle = ();  
            CopyCellStyle(fromWb,toWb, , newstyle);
            //样式  
             = newstyle;
            //评论  
            if ( != null)
            {
                 = ;
            }
            // 不同数据类型处理  
            CellType srcCellType = ;
            (srcCellType);
            if (copyValueFlag)
            {
                if (srcCellType == )
                {
                    if ((srcCell))
                    {
                        ();
                    }
                    else
                    {
                        ();
                    }
                }
                else if (srcCellType == )
                {
                    ();
                }
                else if (srcCellType == )
                {
                    // nothing21  
                }
                else if (srcCellType == )
                {
                    ();
                }
                else if (srcCellType == )
                {
                    ();
                }
                else if (srcCellType == )
                {
                    ();
                }
                else
                {
                    // nothing29  
                }
            }
        }

public static void CopyCellStyle(IWorkbook fromWb, IWorkbook toWb, ICellStyle fromStyle, ICellStyle toStyle)
        {
             = ;
            //边框和边框颜色  
             = ;
             = ;
             = ;
             = ;
             = ;
             = ;
             = ;
             = ;

            //背景和前景  
             = ;
             = ;

             = ;
             = ;
             = ;
             = ;//首行缩进  
             = ;
             = ;//旋转  
             = ;
             = ;
            //IFont fromFont = (fromWb);//字体
            //(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 
                        (
                             == 
                            //边框和边框颜色  
                            &&  == 
                            &&  == 
                            &&  == 
                            &&  == 
                            &&  == 
                            &&  == 
                            &&  == 
                            &&  == 
                            //背景和前景  
                            &&  == 
                            &&  ==                             
                            &&  ==                             
                            &&  == 
                            //&&  == 
                            //&&  == //首行缩进  
                            //&&  == 
                            //&&  == //旋转  
                            //&&  == 
                            //&&  ==  
                        )
                    {
                        return toStyle;
                    }

                }
                return null;
            }
            set
            {
                (fromStyle);
            }
        }
    }

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

public static void CopyCell(IWorkbook fromWb,IWorkbook toWb, ICell srcCell, ICell distCell, bool copyValueFlag)
        {
             //ICellStyle newstyle = ();  
            ICellStyle newstyle = CreateCellStyle(toWb, );
            //复制样式
            CopyCellStyle(fromWb,toWb, , newstyle);
            //样式  
             = newstyle;
            //评论  
            if ( != null)
            {
                 = ;
            }
            // 不同数据类型处理  
            CellType srcCellType = ;
            (srcCellType);
            if (copyValueFlag)
            {
                if (srcCellType == )
                {
                    if ((srcCell))
                    {
                        ();
                    }
                    else
                    {
                        ();
                    }
                }
                else if (srcCellType == )
                {
                    ();
                }
                else if (srcCellType == )
                {
                    // nothing21  
                }
                else if (srcCellType == )
                {
                    ();
                }
                else if (srcCellType == )
                {
                    ();
                }
                else if (srcCellType == )
                {
                    ();
                }
                else
                {
                    // nothing29  
                }
            }
        }

测试通过.