Example #1
0
        /// <summary>
        /// Goes through the Wokrbook, optimising the cell styles
        /// by removing duplicate ones.
        /// For best results, optimise the fonts via a call to
        /// OptimiseFonts(HSSFWorkbook) first
        /// </summary>
        /// <param name="workbook">The workbook in which to optimise the cell styles</param>
        public static void OptimiseCellStyles(HSSFWorkbook workbook)
        {
            // Where each style has ended up, and if we need to
            //  delete the record for it. Start off with no change
            short[] newPos =
                new short[workbook.Workbook.NumExFormats];
            bool[] zapRecords = new bool[newPos.Length];
            for (int i = 0; i < newPos.Length; i++)
            {
                newPos[i] = (short)i;
                zapRecords[i] = false;
            }

            // Get each style record, so we can do deletes
            //  without Getting confused
            ExtendedFormatRecord[] xfrs = new ExtendedFormatRecord[newPos.Length];
            for (int i = 0; i < newPos.Length; i++)
            {
                xfrs[i] = workbook.Workbook.GetExFormatAt(i);
            }

            // Loop over each style, seeing if it is the same
            //  as an earlier one. If it is, point users of the
            //  later duplicate copy to the earlier one, and 
            //  mark the later one as needing deleting
            // Only work on user added ones, which come after 20
            for (int i = 21; i < newPos.Length; i++)
            {
                // Check this one for being a duplicate
                //  of an earlier one
                int earlierDuplicate = -1;
                for (int j = 0; j < i && earlierDuplicate == -1; j++)
                {
                    ExtendedFormatRecord xfCheck = workbook.Workbook.GetExFormatAt(j);
                    if (xfCheck.Equals(xfrs[i]))
                    {
                        earlierDuplicate = j;
                    }
                }

                // If we got a duplicate, mark it as such
                if (earlierDuplicate != -1)
                {
                    newPos[i] = (short)earlierDuplicate;
                    zapRecords[i] = true;
                }
            }

            // Update the new positions based on
            //  deletes that have occurred between
            //  the start and them
            // Only work on user added ones, which come after 20
            for (int i = 21; i < newPos.Length; i++)
            {
                // Find the number deleted to that
                //  point, and adjust
                short preDeletePos = newPos[i];
                short newPosition = preDeletePos;
                for (int j = 0; j < preDeletePos; j++)
                {
                    if (zapRecords[j]) newPosition--;
                }

                // Update the new position
                newPos[i] = newPosition;
            }

            // Zap the un-needed user style records
            for (int i = 21; i < newPos.Length; i++)
            {
                if (zapRecords[i])
                {
                    workbook.Workbook.RemoveExFormatRecord(
                            xfrs[i]
                    );
                }
            }

            // Finally, update the cells to point at
            //  their new extended format records
            for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++)
            {
                HSSFSheet s = (HSSFSheet)workbook.GetSheetAt(sheetNum);
                IEnumerator rIt = s.GetRowEnumerator();
                while (rIt.MoveNext())
                {
                    HSSFRow row = (HSSFRow)rIt.Current;
                    IEnumerator cIt = row.GetEnumerator();
                    while (cIt.MoveNext())
                    {
                        ICell cell = (HSSFCell)cIt.Current;
                        short oldXf = ((HSSFCell)cell).CellValueRecord.XFIndex;
                        LF.Utils.NPOI.SS.UserModel.ICellStyle newStyle = workbook.GetCellStyleAt(
                                newPos[oldXf]
                        );
                        cell.CellStyle = (newStyle);
                    }
                }
            }
        }
Example #2
0
 /// <summary>
 /// Initializes a new instance of the <see cref="HSSFCellStyle"/> class.
 /// </summary>
 /// <param name="index">The index.</param>
 /// <param name="rec">The record.</param>
 /// <param name="workbook">The workbook.</param>
 public HSSFCellStyle(short index, ExtendedFormatRecord rec, HSSFWorkbook workbook)
     :this(index, rec, workbook.Workbook)
 {
     
 }
Example #3
0
 /// <summary>
 /// Initializes a new instance of the <see cref="HSSFCellStyle"/> class.
 /// </summary>
 /// <param name="index">The index.</param>
 /// <param name="rec">The record.</param>
 /// <param name="workbook">The workbook.</param>
 public HSSFCellStyle(short index, ExtendedFormatRecord rec, LF.Utils.NPOI.HSSF.Model.InternalWorkbook workbook)
 {
     this.workbook = workbook;
     this.index = index;
     format = rec;
 }
        /**
 * Clones all the style information from another
 *  ExtendedFormatRecord, onto this one. This 
 *  will then hold all the same style options.
 *  
 * If The source ExtendedFormatRecord comes from
 *  a different Workbook, you will need to sort
 *  out the font and format indicies yourself!
 */
        public void CloneStyleFrom(ExtendedFormatRecord source)
        {
            field_1_font_index = source.field_1_font_index;
            field_2_format_index = source.field_2_format_index;
            field_3_cell_options = source.field_3_cell_options;
            field_4_alignment_options = source.field_4_alignment_options;
            field_5_indention_options = source.field_5_indention_options;
            field_6_border_options = source.field_6_border_options;
            field_7_palette_options = source.field_7_palette_options;
            field_8_adtl_palette_options = source.field_8_adtl_palette_options;
            field_9_fill_palette_options = source.field_9_fill_palette_options;
        }