You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ce...@apache.org on 2017/11/05 20:33:28 UTC
svn commit: r1814373 - in /poi/trunk/src:
integrationtest/org/apache/poi/stress/ java/org/apache/poi/hssf/model/
java/org/apache/poi/hssf/record/aggregates/
java/org/apache/poi/hssf/usermodel/ testcases/org/apache/poi/hssf/usermodel/
Author: centic
Date: Sun Nov 5 20:33:28 2017
New Revision: 1814373
URL: http://svn.apache.org/viewvc?rev=1814373&view=rev
Log:
Bug #57517: Fix various things in HSSFOptimiser to make many more cases work fine: Column styles, row styles, user defined styles, ...
Also call optimise in integration-tests and handle some cases of invalid content in files.
Modified:
poi/trunk/src/integrationtest/org/apache/poi/stress/HSSFFileHandler.java
poi/trunk/src/java/org/apache/poi/hssf/model/InternalSheet.java
poi/trunk/src/java/org/apache/poi/hssf/model/InternalWorkbook.java
poi/trunk/src/java/org/apache/poi/hssf/record/aggregates/ColumnInfoRecordsAggregate.java
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFOptimiser.java
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFOptimiser.java
Modified: poi/trunk/src/integrationtest/org/apache/poi/stress/HSSFFileHandler.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/integrationtest/org/apache/poi/stress/HSSFFileHandler.java?rev=1814373&r1=1814372&r2=1814373&view=diff
==============================================================================
--- poi/trunk/src/integrationtest/org/apache/poi/stress/HSSFFileHandler.java (original)
+++ poi/trunk/src/integrationtest/org/apache/poi/stress/HSSFFileHandler.java Sun Nov 5 20:33:28 2017
@@ -17,6 +17,7 @@
package org.apache.poi.stress;
import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertNotNull;
import java.io.File;
import java.io.FileInputStream;
@@ -27,11 +28,13 @@ import java.io.PrintStream;
import java.util.HashSet;
import java.util.Set;
-import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.OldExcelFormatException;
import org.apache.poi.hssf.dev.BiffViewer;
+import org.apache.poi.hssf.usermodel.HSSFOptimiser;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
-import org.apache.poi.util.RecordFormatException;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
import org.junit.Test;
public class HSSFFileHandler extends SpreadsheetHandler {
@@ -40,7 +43,7 @@ public class HSSFFileHandler extends Spr
public void handleFile(InputStream stream, String path) throws Exception {
HSSFWorkbook wb = new HSSFWorkbook(stream);
handleWorkbook(wb);
-
+
// TODO: some documents fail currently...
// Note - as of Bugzilla 48036 (svn r828244, r828247) POI is capable of evaluating
// IntersectionPtg. However it is still not capable of parsing it.
@@ -52,6 +55,15 @@ public class HSSFFileHandler extends Spr
// also try to see if some of the Records behave incorrectly
// TODO: still fails on some records... RecordsStresser.handleWorkbook(wb);
+
+ HSSFOptimiser.optimiseCellStyles(wb);
+ for(Sheet sheet : wb) {
+ for (Row row : sheet) {
+ for (Cell cell : row) {
+ assertNotNull(cell.getCellStyle());
+ }
+ }
+ }
}
private static final Set<String> EXPECTED_ADDITIONAL_FAILURES = new HashSet<>();
@@ -86,14 +98,6 @@ public class HSSFFileHandler extends Spr
EXPECTED_ADDITIONAL_FAILURES.contains(file.getParentFile().getName() + "/" + file.getName()));
} catch (OldExcelFormatException e) {
// old excel formats are not supported here
- } catch (EncryptedDocumentException e) {
- if(!EXPECTED_ADDITIONAL_FAILURES.contains(file.getParentFile().getName() + "/" + file.getName())) {
- throw e;
- }
- } catch (RecordFormatException e) {
- if(!EXPECTED_ADDITIONAL_FAILURES.contains(file.getParentFile().getName() + "/" + file.getName())) {
- throw e;
- }
} catch (RuntimeException e) {
if(!EXPECTED_ADDITIONAL_FAILURES.contains(file.getParentFile().getName() + "/" + file.getName())) {
throw e;
@@ -107,12 +111,9 @@ public class HSSFFileHandler extends Spr
@Test
public void test() throws Exception {
File file = new File("test-data/spreadsheet/49219.xls");
-
- InputStream stream = new FileInputStream(file);
- try {
+
+ try (InputStream stream = new FileInputStream(file)) {
handleFile(stream, file.getPath());
- } finally {
- stream.close();
}
}
Modified: poi/trunk/src/java/org/apache/poi/hssf/model/InternalSheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/model/InternalSheet.java?rev=1814373&r1=1814372&r2=1814373&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/model/InternalSheet.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/model/InternalSheet.java Sun Nov 5 20:33:28 2017
@@ -1680,4 +1680,12 @@ public final class InternalSheet {
public int getColumnOutlineLevel(int columnIndex) {
return _columnInfos.getOutlineLevel(columnIndex);
}
+
+ public int getMinColumnIndex() {
+ return _columnInfos.getMinColumnIndex();
+ }
+
+ public int getMaxColumnIndex() {
+ return _columnInfos.getMaxColumnIndex();
+ }
}
Modified: poi/trunk/src/java/org/apache/poi/hssf/model/InternalWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/model/InternalWorkbook.java?rev=1814373&r1=1814372&r2=1814373&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/model/InternalWorkbook.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/model/InternalWorkbook.java Sun Nov 5 20:33:28 2017
@@ -937,6 +937,27 @@ public final class InternalWorkbook {
}
/**
+ * Update the StyleRecord to point to the new
+ * given index.
+ *
+ * @param oldXf the extended format index that was previously associated with this StyleRecord
+ * @param newXf the extended format index that is now associated with this StyleRecord
+ */
+ public void updateStyleRecord(int oldXf, int newXf) {
+ // Style records always follow after
+ // the ExtendedFormat records
+ for(int i=records.getXfpos(); i<records.size(); i++) {
+ Record r = records.get(i);
+ if (r instanceof StyleRecord) {
+ StyleRecord sr = (StyleRecord)r;
+ if (sr.getXFIndex() == oldXf) {
+ sr.setXFIndex(newXf);
+ }
+ }
+ }
+ }
+
+ /**
* Creates a new StyleRecord, for the given Extended
* Format index, and adds it onto the end of the
* records collection
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/aggregates/ColumnInfoRecordsAggregate.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/aggregates/ColumnInfoRecordsAggregate.java?rev=1814373&r1=1814372&r2=1814373&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/aggregates/ColumnInfoRecordsAggregate.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/aggregates/ColumnInfoRecordsAggregate.java Sun Nov 5 20:33:28 2017
@@ -489,6 +489,7 @@ public final class ColumnInfoRecordsAggr
setColumn(i, null, null, Integer.valueOf(level), null, null);
}
}
+
/**
* Finds the <tt>ColumnInfoRecord</tt> which contains the specified columnIndex
* @param columnIndex index of the column (not the index of the ColumnInfoRecord)
@@ -504,6 +505,7 @@ public final class ColumnInfoRecordsAggr
}
return null;
}
+
public int getMaxOutlineLevel() {
int result = 0;
int count=records.size();
@@ -513,6 +515,7 @@ public final class ColumnInfoRecordsAggr
}
return result;
}
+
public int getOutlineLevel(int columnIndex) {
ColumnInfoRecord ci = findColumnInfo(columnIndex);
if (ci != null) {
@@ -521,4 +524,34 @@ public final class ColumnInfoRecordsAggr
return 0;
}
}
+
+ public int getMinColumnIndex() {
+ if(records.isEmpty()) {
+ return 0;
+ }
+
+ int minIndex = Integer.MAX_VALUE;
+ int nInfos = records.size();
+ for(int i=0; i< nInfos; i++) {
+ ColumnInfoRecord ci = getColInfo(i);
+ minIndex = Math.min(minIndex, ci.getFirstColumn());
+ }
+
+ return minIndex;
+ }
+
+ public int getMaxColumnIndex() {
+ if(records.isEmpty()) {
+ return 0;
+ }
+
+ int maxIndex = 0;
+ int nInfos = records.size();
+ for(int i=0; i< nInfos; i++) {
+ ColumnInfoRecord ci = getColInfo(i);
+ maxIndex = Math.max(maxIndex, ci.getLastColumn());
+ }
+
+ return maxIndex;
+ }
}
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFOptimiser.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFOptimiser.java?rev=1814373&r1=1814372&r2=1814373&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFOptimiser.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFOptimiser.java Sun Nov 5 20:33:28 2017
@@ -20,6 +20,7 @@ import java.util.HashSet;
import org.apache.poi.hssf.record.ExtendedFormatRecord;
import org.apache.poi.hssf.record.FontRecord;
+import org.apache.poi.hssf.record.StyleRecord;
import org.apache.poi.hssf.record.common.UnicodeString;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
@@ -185,50 +186,81 @@ public class HSSFOptimiser {
// Get each style record, so we can do deletes
// without getting confused
- ExtendedFormatRecord[] xfrs = new ExtendedFormatRecord[newPos.length];
+ ExtendedFormatRecord[] xfrs = new ExtendedFormatRecord[newPos.length];
for(int i=0; i<newPos.length; i++) {
xfrs[i] = workbook.getWorkbook().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.getWorkbook().getExFormatAt(j);
- if(xfCheck.equals(xfrs[i])) {
- earlierDuplicate = j;
- }
- }
+ // 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.getWorkbook().getExFormatAt(j);
+ if (xfCheck.equals(xfrs[i]) &&
+ // newer duplicate user defined styles
+ !isUserDefined(workbook, j)) {
+ 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.getNumberOfSheets(); sheetNum++) {
- HSSFSheet s = workbook.getSheetAt(sheetNum);
- for (Row row : s) {
- for (Cell cellI : row) {
- HSSFCell cell = (HSSFCell)cellI;
- short oldXf = cell.getCellValueRecord().getXFIndex();
- isUsed[oldXf] = 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.getNumberOfSheets(); sheetNum++) {
+ HSSFSheet s = workbook.getSheetAt(sheetNum);
+ for (Row row : s) {
+ for (Cell cellI : row) {
+ HSSFCell cell = (HSSFCell) cellI;
+ short oldXf = cell.getCellValueRecord().getXFIndex();
+ // some documents contain invalid values here
+ if(oldXf < newPos.length) {
+ isUsed[oldXf] = true;
+ }
+ }
+
+ // also mark row style as being used
+ short oldXf = ((HSSFRow) row).getRowRecord().getXFIndex();
+ // some documents contain invalid values here
+ if(oldXf < newPos.length) {
+ isUsed[oldXf] = true;
+ }
+ }
+
+ // also mark column styles as being used
+ for (int col = s.getSheet().getMinColumnIndex(); col <= s.getSheet().getMaxColumnIndex(); col++) {
+ short oldXf = s.getSheet().getXFIndexForColAt((short) col);
+ // some documents contain invalid values here
+ if(oldXf < newPos.length) {
+ isUsed[oldXf] = true;
+ }
+ }
+ }
+
+ // Propagate isUsed for duplicates and always set user styles to being used to never optimize them away
+ for (int i = 21; i < isUsed.length; i++) {
+ // user defined styles are always "used"
+ if (isUserDefined(workbook, i)) {
+ isUsed[i] = true;
+ }
+
+ // If we got a duplicate which is used, mark the one we're keeping as used
+ if(newPos[i] != i && isUsed[i]) {
+ isUsed[newPos[i]] = true;
+ }
+ }
+
// Mark any that aren't used as needing zapping
for (int i=21; i<isUsed.length; i++) {
if (! isUsed[i]) {
@@ -251,9 +283,21 @@ public class HSSFOptimiser {
if(zapRecords[j]) newPosition--;
}
- // Update the new position
- newPos[i] = newPosition;
- }
+ // Update the new position
+ newPos[i] = newPosition;
+ // also update StyleRecord and Parent-link
+ if (i != newPosition && newPosition != 0) {
+ workbook.getWorkbook().updateStyleRecord(i, newPosition);
+
+ ExtendedFormatRecord exFormat = workbook.getWorkbook().getExFormatAt(i);
+ short oldParent = exFormat.getParentIndex();
+ // some documents contain invalid values here
+ if(oldParent < newPos.length) {
+ short newParent = newPos[oldParent];
+ exFormat.setParentIndex(newParent);
+ }
+ }
+ }
// Zap the un-needed user style records
// removing by index, because removing by object may delete
@@ -269,20 +313,47 @@ public class HSSFOptimiser {
}
}
- // Finally, update the cells to point at their new extended format records
- for(int sheetNum=0; sheetNum<workbook.getNumberOfSheets(); sheetNum++) {
- HSSFSheet s = workbook.getSheetAt(sheetNum);
- for (Row row : s) {
- for (Cell cellI : row) {
- HSSFCell cell = (HSSFCell)cellI;
- short oldXf = cell.getCellValueRecord().getXFIndex();
-
- HSSFCellStyle newStyle = workbook.getCellStyleAt(
- newPos[oldXf]
- );
- cell.setCellStyle(newStyle);
- }
- }
- }
+ // Finally, update the cells to point at their new extended format records
+ for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
+ HSSFSheet s = workbook.getSheetAt(sheetNum);
+ for (Row row : s) {
+ for (Cell cell : row) {
+ short oldXf = ((HSSFCell) cell).getCellValueRecord().getXFIndex();
+ // some documents contain invalid values here
+ if(oldXf >= newPos.length) {
+ continue;
+ }
+ HSSFCellStyle newStyle = workbook.getCellStyleAt(newPos[oldXf]);
+ cell.setCellStyle(newStyle);
+ }
+
+ // adjust row column style
+ short oldXf = ((HSSFRow) row).getRowRecord().getXFIndex();
+ // some documents contain invalid values here
+ if(oldXf >= newPos.length) {
+ continue;
+ }
+ HSSFCellStyle newStyle = workbook.getCellStyleAt(newPos[oldXf]);
+ row.setRowStyle(newStyle);
+ }
+
+ // adjust cell column style
+ for (int col = s.getSheet().getMinColumnIndex(); col <= s.getSheet().getMaxColumnIndex(); col++) {
+ short oldXf = s.getSheet().getXFIndexForColAt((short) col);
+ // some documents contain invalid values here
+ if(oldXf >= newPos.length) {
+ continue;
+ }
+ HSSFCellStyle newStyle = workbook.getCellStyleAt(newPos[oldXf]);
+ s.setDefaultColumnStyle(col, newStyle);
+ }
+ }
}
+
+ private static boolean isUserDefined(HSSFWorkbook workbook, int index) {
+ StyleRecord styleRecord = workbook.getWorkbook().getStyleRecord(index);
+ return styleRecord != null &&
+ !styleRecord.isBuiltin() &&
+ styleRecord.getName() != null;
+ }
}
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFOptimiser.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFOptimiser.java?rev=1814373&r1=1814372&r2=1814373&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFOptimiser.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFOptimiser.java Sun Nov 5 20:33:28 2017
@@ -16,12 +16,16 @@
==================================================================== */
package org.apache.poi.hssf.usermodel;
-import org.apache.poi.ss.usermodel.BorderStyle;
-import org.apache.poi.ss.usermodel.HorizontalAlignment;
+import java.io.IOException;
-import junit.framework.TestCase;
+import org.apache.poi.ss.usermodel.*;
+import org.junit.Test;
-public final class TestHSSFOptimiser extends TestCase {
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertNotNull;
+
+public final class TestHSSFOptimiser {
+ @Test
public void testDoesNoHarmIfNothingToDo() {
HSSFWorkbook wb = new HSSFWorkbook();
@@ -60,6 +64,7 @@ public final class TestHSSFOptimiser ext
assertEquals(f, s.getFont(wb));
}
+ @Test
public void testOptimiseFonts() {
HSSFWorkbook wb = new HSSFWorkbook();
@@ -158,6 +163,7 @@ public final class TestHSSFOptimiser ext
assertEquals(8, r.getCell(1).getRichStringCellValue().getFontAtIndex(7));
}
+ @Test
public void testOptimiseStyles() {
HSSFWorkbook wb = new HSSFWorkbook();
@@ -274,6 +280,7 @@ public final class TestHSSFOptimiser ext
assertEquals(21, r.getCell(8).getCellValueRecord().getXFIndex());
}
+ @Test
public void testOptimiseStylesCheckActualStyles() {
HSSFWorkbook wb = new HSSFWorkbook();
@@ -313,4 +320,383 @@ public final class TestHSSFOptimiser ext
assertEquals(BorderStyle.DASH_DOT, r.getCell(1).getCellStyle().getBorderBottom());
assertEquals(BorderStyle.THICK, r.getCell(2).getCellStyle().getBorderBottom());
}
+
+ @Test
+ public void testColumnAndRowStyles() {
+ HSSFWorkbook wb = new HSSFWorkbook();
+ assertEquals("Usually we have 21 pre-defined styles in a newly created Workbook, see InternalWorkbook.createWorkbook()",
+ 21, wb.getNumCellStyles());
+
+ HSSFSheet sheet = wb.createSheet();
+
+ Row row = sheet.createRow(0);
+ row.createCell(0);
+ row.createCell(1);
+ row.setRowStyle(createColorStyle(wb, IndexedColors.RED));
+
+ row = sheet.createRow(1);
+ row.createCell(0);
+ row.createCell(1);
+ row.setRowStyle(createColorStyle(wb, IndexedColors.RED));
+
+ sheet.setDefaultColumnStyle(0, createColorStyle(wb, IndexedColors.RED));
+ sheet.setDefaultColumnStyle(1, createColorStyle(wb, IndexedColors.RED));
+
+ // now the color should be equal for those two columns and rows
+ checkColumnStyles(sheet, 0, 1, false);
+ checkRowStyles(sheet, 0, 1, false);
+
+ // Optimise styles
+ HSSFOptimiser.optimiseCellStyles(wb);
+
+ // We should have the same style-objects for these two columns and rows
+ checkColumnStyles(sheet, 0, 1, true);
+ checkRowStyles(sheet, 0, 1, true);
+ }
+
+ @Test
+ public void testUnusedStyle() {
+ HSSFWorkbook wb = new HSSFWorkbook();
+ assertEquals("Usually we have 21 pre-defined styles in a newly created Workbook, see InternalWorkbook.createWorkbook()",
+ 21, wb.getNumCellStyles());
+
+ HSSFSheet sheet = wb.createSheet();
+
+ Row row = sheet.createRow(0);
+ row.createCell(0);
+ row.createCell(1).setCellStyle(
+ createColorStyle(wb, IndexedColors.GREEN));
+
+
+ row = sheet.createRow(1);
+ row.createCell(0);
+ row.createCell(1).setCellStyle(
+ createColorStyle(wb, IndexedColors.RED));
+
+
+ // Create style. But don't use it.
+ for (int i = 0; i < 3; i++) {
+ // Set Cell Color : AQUA
+ createColorStyle(wb, IndexedColors.AQUA);
+ }
+
+ assertEquals(21 + 2 + 3, wb.getNumCellStyles());
+ assertEquals(IndexedColors.GREEN.getIndex(), sheet.getRow(0).getCell(1).getCellStyle().getFillForegroundColor());
+ assertEquals(IndexedColors.RED.getIndex(), sheet.getRow(1).getCell(1).getCellStyle().getFillForegroundColor());
+
+ // Optimise styles
+ HSSFOptimiser.optimiseCellStyles(wb);
+
+ assertEquals(21 + 2, wb.getNumCellStyles());
+ assertEquals(IndexedColors.GREEN.getIndex(), sheet.getRow(0).getCell(1).getCellStyle().getFillForegroundColor());
+ assertEquals(IndexedColors.RED.getIndex(), sheet.getRow(1).getCell(1).getCellStyle().getFillForegroundColor());
+ }
+
+ @Test
+ public void testUnusedStyleOneUsed() {
+ HSSFWorkbook wb = new HSSFWorkbook();
+ assertEquals("Usually we have 21 pre-defined styles in a newly created Workbook, see InternalWorkbook.createWorkbook()",
+ 21, wb.getNumCellStyles());
+
+ HSSFSheet sheet = wb.createSheet();
+
+ Row row = sheet.createRow(0);
+ row.createCell(0);
+ row.createCell(1).setCellStyle(
+ createColorStyle(wb, IndexedColors.GREEN));
+
+ // Create style. But don't use it.
+ for (int i = 0; i < 3; i++) {
+ // Set Cell Color : AQUA
+ createColorStyle(wb, IndexedColors.AQUA);
+ }
+
+ row = sheet.createRow(1);
+ row.createCell(0).setCellStyle(createColorStyle(wb, IndexedColors.AQUA));
+ row.createCell(1).setCellStyle(
+ createColorStyle(wb, IndexedColors.RED));
+
+ assertEquals(21 + 3 + 3, wb.getNumCellStyles());
+ assertEquals(IndexedColors.GREEN.getIndex(), sheet.getRow(0).getCell(1).getCellStyle().getFillForegroundColor());
+ assertEquals(IndexedColors.AQUA.getIndex(), sheet.getRow(1).getCell(0).getCellStyle().getFillForegroundColor());
+ assertEquals(IndexedColors.RED.getIndex(), sheet.getRow(1).getCell(1).getCellStyle().getFillForegroundColor());
+
+ // Optimise styles
+ HSSFOptimiser.optimiseCellStyles(wb);
+
+ assertEquals(21 + 3, wb.getNumCellStyles());
+ assertEquals(IndexedColors.GREEN.getIndex(), sheet.getRow(0).getCell(1).getCellStyle().getFillForegroundColor());
+ assertEquals(IndexedColors.AQUA.getIndex(), sheet.getRow(1).getCell(0).getCellStyle().getFillForegroundColor());
+ assertEquals(IndexedColors.RED.getIndex(), sheet.getRow(1).getCell(1).getCellStyle().getFillForegroundColor());
+ }
+
+ @Test
+ public void testDefaultColumnStyleWitoutCell() throws IOException {
+ HSSFWorkbook wb = new HSSFWorkbook();
+ assertEquals("Usually we have 21 pre-defined styles in a newly created Workbook, see InternalWorkbook.createWorkbook()",
+ 21, wb.getNumCellStyles());
+
+ HSSFSheet sheet = wb.createSheet();
+
+ //Set CellStyle and RowStyle and ColumnStyle
+ for (int i = 0; i < 2; i++) {
+ sheet.createRow(i);
+ }
+
+ // Create a test font and style, and use them
+ int obj_cnt = wb.getNumCellStyles();
+ int cnt = wb.getNumCellStyles();
+
+ // Set Column Color : Red
+ sheet.setDefaultColumnStyle(3,
+ createColorStyle(wb, IndexedColors.RED));
+ obj_cnt++;
+
+ // Set Column Color : Red
+ sheet.setDefaultColumnStyle(4,
+ createColorStyle(wb, IndexedColors.RED));
+ obj_cnt++;
+
+ assertEquals(obj_cnt, wb.getNumCellStyles());
+
+ // now the color should be equal for those two columns and rows
+ checkColumnStyles(sheet, 3, 4, false);
+
+ // Optimise styles
+ HSSFOptimiser.optimiseCellStyles(wb);
+
+ // We should have the same style-objects for these two columns and rows
+ checkColumnStyles(sheet, 3, 4, true);
+
+ // (GREEN + RED + BLUE + CORAL) + YELLOW(2*2)
+ assertEquals(cnt + 1, wb.getNumCellStyles());
+ }
+
+ @Test
+ public void testUserDefinedStylesAreNeverOptimizedAway() throws IOException {
+ HSSFWorkbook wb = new HSSFWorkbook();
+ assertEquals("Usually we have 21 pre-defined styles in a newly created Workbook, see InternalWorkbook.createWorkbook()",
+ 21, wb.getNumCellStyles());
+
+ HSSFSheet sheet = wb.createSheet();
+
+ //Set CellStyle and RowStyle and ColumnStyle
+ for (int i = 0; i < 2; i++) {
+ sheet.createRow(i);
+ }
+
+ // Create a test font and style, and use them
+ int obj_cnt = wb.getNumCellStyles();
+ int cnt = wb.getNumCellStyles();
+ for (int i = 0; i < 3; i++) {
+ HSSFCellStyle s = null;
+ if (i == 0) {
+ // Set cell color : +2(user style + proxy of it)
+ s = (HSSFCellStyle) createColorStyle(wb,
+ IndexedColors.YELLOW);
+ s.setUserStyleName("user define");
+ obj_cnt += 2;
+ }
+
+ HSSFRow row = sheet.getRow(1);
+ row.createCell(i).setCellStyle(s);
+ }
+
+ // Create style. But don't use it.
+ for (int i = 3; i < 6; i++) {
+ // Set Cell Color : AQUA
+ createColorStyle(wb, IndexedColors.AQUA);
+ obj_cnt++;
+ }
+
+ // Set cell color : +2(user style + proxy of it)
+ HSSFCellStyle s = (HSSFCellStyle) createColorStyle(wb,IndexedColors.YELLOW);
+ s.setUserStyleName("user define2");
+ obj_cnt += 2;
+
+ sheet.createRow(10).createCell(0).setCellStyle(s);
+
+ assertEquals(obj_cnt, wb.getNumCellStyles());
+
+ // Confirm user style name
+ checkUserStyles(sheet);
+
+ // Optimise styles
+ HSSFOptimiser.optimiseCellStyles(wb);
+
+ // Confirm user style name
+ checkUserStyles(sheet);
+
+ // (GREEN + RED + BLUE + CORAL) + YELLOW(2*2)
+ assertEquals(cnt + 2 * 2, wb.getNumCellStyles());
+ }
+
+ @Test
+ public void testBug57517() throws IOException {
+ HSSFWorkbook wb = new HSSFWorkbook();
+ assertEquals("Usually we have 21 pre-defined styles in a newly created Workbook, see InternalWorkbook.createWorkbook()",
+ 21, wb.getNumCellStyles());
+
+ HSSFSheet sheet = wb.createSheet();
+
+ //Set CellStyle and RowStyle and ColumnStyle
+ for (int i = 0; i < 2; i++) {
+ sheet.createRow(i);
+ }
+
+ // Create a test font and style, and use them
+ int obj_cnt = wb.getNumCellStyles();
+ int cnt = wb.getNumCellStyles();
+ for (int i = 0; i < 3; i++) {
+ // Set Cell Color : GREEN
+ HSSFRow row = sheet.getRow(0);
+ row.createCell(i).setCellStyle(
+ createColorStyle(wb, IndexedColors.GREEN));
+ obj_cnt++;
+
+ // Set Column Color : Red
+ sheet.setDefaultColumnStyle(i + 3,
+ createColorStyle(wb, IndexedColors.RED));
+ obj_cnt++;
+
+ // Set Row Color : Blue
+ row = sheet.createRow(i + 3);
+ row.setRowStyle(createColorStyle(wb, IndexedColors.BLUE));
+ obj_cnt++;
+
+ HSSFCellStyle s = null;
+ if (i == 0) {
+ // Set cell color : +2(user style + proxy of it)
+ s = (HSSFCellStyle) createColorStyle(wb,
+ IndexedColors.YELLOW);
+ s.setUserStyleName("user define");
+ obj_cnt += 2;
+ }
+
+ row = sheet.getRow(1);
+ row.createCell(i).setCellStyle(s);
+
+ }
+
+ // Create style. But don't use it.
+ for (int i = 3; i < 6; i++) {
+ // Set Cell Color : AQUA
+ createColorStyle(wb, IndexedColors.AQUA);
+ obj_cnt++;
+ }
+
+ // Set CellStyle and RowStyle and ColumnStyle
+ for (int i = 9; i < 11; i++) {
+ sheet.createRow(i);
+ }
+
+ //Set 0 or 255 index of ColumnStyle.
+ HSSFCellStyle s = (HSSFCellStyle) createColorStyle(wb, IndexedColors.CORAL);
+ obj_cnt++;
+ sheet.setDefaultColumnStyle(0, s);
+ sheet.setDefaultColumnStyle(255, s);
+
+ // Create a test font and style, and use them
+ for (int i = 3; i < 6; i++) {
+ // Set Cell Color : GREEN
+ HSSFRow row = sheet.getRow(0 + 9);
+ row.createCell(i - 3).setCellStyle(
+ createColorStyle(wb, IndexedColors.GREEN));
+ obj_cnt++;
+
+ // Set Column Color : Red
+ sheet.setDefaultColumnStyle(i + 3,
+ createColorStyle(wb, IndexedColors.RED));
+ obj_cnt++;
+
+ // Set Row Color : Blue
+ row = sheet.createRow(i + 3);
+ row.setRowStyle(createColorStyle(wb, IndexedColors.BLUE));
+ obj_cnt++;
+
+ if (i == 3) {
+ // Set cell color : +2(user style + proxy of it)
+ s = (HSSFCellStyle) createColorStyle(wb,
+ IndexedColors.YELLOW);
+ s.setUserStyleName("user define2");
+ obj_cnt += 2;
+ }
+
+ row = sheet.getRow(1 + 9);
+ row.createCell(i - 3).setCellStyle(s);
+ }
+
+ assertEquals(obj_cnt, wb.getNumCellStyles());
+
+ // now the color should be equal for those two columns and rows
+ checkColumnStyles(sheet, 3, 4, false);
+ checkRowStyles(sheet, 3, 4, false);
+
+ // Confirm user style name
+ checkUserStyles(sheet);
+
+// out = new FileOutputStream(new File(tmpDirName, "out.xls"));
+// wb.write(out);
+// out.close();
+
+ // Optimise styles
+ HSSFOptimiser.optimiseCellStyles(wb);
+
+// out = new FileOutputStream(new File(tmpDirName, "out_optimised.xls"));
+// wb.write(out);
+// out.close();
+
+ // We should have the same style-objects for these two columns and rows
+ checkColumnStyles(sheet, 3, 4, true);
+ checkRowStyles(sheet, 3, 4, true);
+
+ // Confirm user style name
+ checkUserStyles(sheet);
+
+ // (GREEN + RED + BLUE + CORAL) + YELLOW(2*2)
+ assertEquals(cnt + 4 + 2 * 2, wb.getNumCellStyles());
+ }
+
+ private void checkUserStyles(HSSFSheet sheet) {
+ HSSFCellStyle parentStyle1 = sheet.getRow(1).getCell(0).getCellStyle().getParentStyle();
+ assertNotNull(parentStyle1);
+ assertEquals(parentStyle1.getUserStyleName(), "user define");
+
+ HSSFCellStyle parentStyle10 = sheet.getRow(10).getCell(0).getCellStyle().getParentStyle();
+ assertNotNull(parentStyle10);
+ assertEquals(parentStyle10.getUserStyleName(), "user define2");
+ }
+
+ private void checkColumnStyles(HSSFSheet sheet, int col1, int col2, boolean checkEquals) {
+ // we should have the same color for the column styles
+ HSSFCellStyle columnStyle1 = sheet.getColumnStyle(col1);
+ assertNotNull(columnStyle1);
+ HSSFCellStyle columnStyle2 = sheet.getColumnStyle(col2);
+ assertNotNull(columnStyle2);
+ assertEquals(columnStyle1.getFillForegroundColor(), columnStyle2.getFillForegroundColor());
+ if(checkEquals) {
+ assertEquals(columnStyle1.getIndex(), columnStyle2.getIndex());
+ assertEquals(columnStyle1, columnStyle2);
+ }
+ }
+
+ private void checkRowStyles(HSSFSheet sheet, int row1, int row2, boolean checkEquals) {
+ // we should have the same color for the row styles
+ HSSFCellStyle rowStyle1 = sheet.getRow(row1).getRowStyle();
+ assertNotNull(rowStyle1);
+ HSSFCellStyle rowStyle2 = sheet.getRow(row2).getRowStyle();
+ assertNotNull(rowStyle2);
+ assertEquals(rowStyle1.getFillForegroundColor(), rowStyle2.getFillForegroundColor());
+ if(checkEquals) {
+ assertEquals(rowStyle1.getIndex(), rowStyle2.getIndex());
+ assertEquals(rowStyle1, rowStyle2);
+ }
+ }
+
+ private CellStyle createColorStyle(Workbook wb, IndexedColors c) {
+ CellStyle cs = wb.createCellStyle();
+ cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ cs.setFillForegroundColor(c.getIndex());
+ return cs;
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org