/** * This method attempt to find an already existing HSSFCellStyle that matches * what you want the style to be. If it does not find the style, then it * Creates a new one. If it does Create a new one, then it applies the * propertyName and propertyValue to the style. This is necessary because * Excel has an upper limit on the number of Styles that it supports. * *@param workbook The workbook that is being worked with. *@param propertyName The name of the property that is to be * changed. *@param propertyValue The value of the property that is to be * changed. *@param cell The cell that needs it's style changes *@exception NestableException Thrown if an error happens. */ public static void SetCellStyleProperty(ICell cell, HSSFWorkbook workbook, String propertyName, Object propertyValue) { ICellStyle originalStyle = cell.CellStyle; ICellStyle newStyle = null; Hashtable values = GetFormatProperties(originalStyle); values[propertyName] = propertyValue; // index seems like what index the cellstyle is in the list of styles for a workbook. // not good to compare on! short numberCellStyles = workbook.NumCellStyles; for (short i = 0; i < numberCellStyles; i++) { ICellStyle wbStyle = workbook.GetCellStyleAt(i); Hashtable wbStyleMap = GetFormatProperties(wbStyle); // if (wbStyleMap.Equals(values)) if (CompareHashTableKeyValueIsEqual(wbStyleMap, values)) { newStyle = wbStyle; break; } } if (newStyle == null) { newStyle = workbook.CreateCellStyle(); SetFormatProperties(newStyle, workbook, values); } cell.CellStyle = (newStyle); }
public void TestSetRemoveStyle() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.CreateSheet() as HSSFSheet; HSSFRow row = sheet.CreateRow(0) as HSSFRow; HSSFCell cell = row.CreateCell(0) as HSSFCell; HSSFCellStyle defaultStyle = wb.GetCellStyleAt((short)15) as HSSFCellStyle; // Starts out with the default style Assert.AreEqual(defaultStyle, cell.CellStyle); // Create some styles, no change HSSFCellStyle style1 = wb.CreateCellStyle() as HSSFCellStyle; HSSFCellStyle style2 = wb.CreateCellStyle() as HSSFCellStyle; style1.DataFormat = (/*setter*/ (short)2); style2.DataFormat = (/*setter*/ (short)3); Assert.AreEqual(defaultStyle, cell.CellStyle); // Apply one, Changes cell.CellStyle = (/*setter*/ style1); Assert.AreEqual(style1, cell.CellStyle); // Apply the other, Changes cell.CellStyle = (/*setter*/ style2); Assert.AreEqual(style2, cell.CellStyle); // Remove, goes back to default cell.CellStyle = (/*setter*/ null); Assert.AreEqual(defaultStyle, cell.CellStyle); // Add back, returns cell.CellStyle = (/*setter*/ style2); Assert.AreEqual(style2, cell.CellStyle); }
/// <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; NPOI.SS.UserModel.ICellStyle newStyle = workbook.GetCellStyleAt( newPos[oldXf] ); cell.CellStyle = (newStyle); } } } }
/// <summary> /// Goes through the Wokrbook, optimising the cell styles /// by removing duplicate ones and ones that aren't used. /// 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[] isUsed = new bool[newPos.Length]; bool[] zapRecords = new bool[newPos.Length]; for (int i = 0; i < newPos.Length; i++) { isUsed[i] = false; 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; } // If we got a duplicate, mark the one we're keeping as used if (earlierDuplicate != -1) { isUsed[earlierDuplicate] = true; } } // Loop over all the cells in the file, and identify any user defined // styles aren't actually being used (don't touch built-in ones) for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++) { HSSFSheet s = (HSSFSheet)workbook.GetSheetAt(sheetNum); foreach (IRow row in s) { foreach (ICell cellI in row) { HSSFCell cell = (HSSFCell)cellI; short oldXf = cell.CellValueRecord.XFIndex; isUsed[oldXf] = true; } } } // Mark any that aren't used as needing zapping for (int i = 21; i < isUsed.Length; i++) { if (!isUsed[i]) { // Un-used style, can be removed zapRecords[i] = true; newPos[i] = 0; } } // 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 // removing by index, because removing by object may delete // styles we did not intend to (the ones that _were_ duplicated and not the duplicates) int max = newPos.Length; int removed = 0; // to adjust index after deletion for (int i = 21; i < max; i++) { if (zapRecords[i + removed]) { workbook.Workbook.RemoveExFormatRecord(i); i--; max--; removed++; } } // 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()) foreach (IRow row in s) { //HSSFRow row = (HSSFRow)rIt.Current; //IEnumerator cIt = row.GetEnumerator(); //while (cIt.MoveNext()) foreach (ICell cell in row) { //ICell cell = (HSSFCell)cIt.Current; short oldXf = ((HSSFCell)cell).CellValueRecord.XFIndex; Npoi.Core.SS.UserModel.ICellStyle newStyle = workbook.GetCellStyleAt( newPos[oldXf] ); cell.CellStyle = (newStyle); } } } }