/// <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); } } } }
/// <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) { }
/// <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; }