diff options
10 files changed, 310 insertions, 39 deletions
diff --git a/poi-integration/src/test/java/org/apache/poi/stress/TestAllFiles.java b/poi-integration/src/test/java/org/apache/poi/stress/TestAllFiles.java index 957b804ff5..8988c8a180 100644 --- a/poi-integration/src/test/java/org/apache/poi/stress/TestAllFiles.java +++ b/poi-integration/src/test/java/org/apache/poi/stress/TestAllFiles.java @@ -100,6 +100,9 @@ public class TestAllFiles { // stress docs "document/deep-table-cell.docx", + // invalid files + "spreadsheet/bug69769.xlsx", + // NOTE: Expected failures should usually be added in file "stress.xls" instead // of being listed here in order to also verify the expected exception details! }; diff --git a/poi-ooxml/src/main/java/org/apache/poi/ooxml/POIXMLTypeLoader.java b/poi-ooxml/src/main/java/org/apache/poi/ooxml/POIXMLTypeLoader.java index e848e383cd..5a4ca30c49 100644 --- a/poi-ooxml/src/main/java/org/apache/poi/ooxml/POIXMLTypeLoader.java +++ b/poi-ooxml/src/main/java/org/apache/poi/ooxml/POIXMLTypeLoader.java @@ -43,6 +43,9 @@ public class POIXMLTypeLoader { DEFAULT_XML_OPTIONS.setCharacterEncoding("UTF-8"); DEFAULT_XML_OPTIONS.setDisallowDocTypeDeclaration(true); DEFAULT_XML_OPTIONS.setEntityExpansionLimit(1); + DEFAULT_XML_OPTIONS.setLoadStripProcinsts(true); + DEFAULT_XML_OPTIONS.setLoadStripComments(true); + // JAXP is used for parsing // so only user code using XmlObject/XmlToken.Factory.parse // directly can bypass the entity check, which is probably unlikely (... and not within our responsibility :)) diff --git a/poi-ooxml/src/main/java/org/apache/poi/xssf/eventusermodel/XSSFSheetXMLHandler.java b/poi-ooxml/src/main/java/org/apache/poi/xssf/eventusermodel/XSSFSheetXMLHandler.java index dbe3bcdee6..110d343d7b 100644 --- a/poi-ooxml/src/main/java/org/apache/poi/xssf/eventusermodel/XSSFSheetXMLHandler.java +++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/eventusermodel/XSSFSheetXMLHandler.java @@ -253,7 +253,7 @@ public class XSSFSheetXMLHandler extends DefaultHandler { } else if ("row".equals(localName)) { String rowNumStr = attributes.getValue("r"); if (rowNumStr != null) { - rowNum = Integer.parseInt(rowNumStr) - 1; + rowNum = parseInt(rowNumStr) - 1; } else { rowNum = nextRowNum; } @@ -284,7 +284,7 @@ public class XSSFSheetXMLHandler extends DefaultHandler { XSSFCellStyle style = null; if (stylesTable != null) { if (cellStyleStr != null) { - int styleIndex = Integer.parseInt(cellStyleStr); + int styleIndex = parseInt(cellStyleStr); style = stylesTable.getStyleAt(styleIndex); } else if (stylesTable.getNumCellStyles() > 0) { style = stylesTable.getStyleAt(0); @@ -392,7 +392,7 @@ public class XSSFSheetXMLHandler extends DefaultHandler { if (this.formatString != null) { try { // Try to use the value as a formattable number - double d = Double.parseDouble(fv); + double d = parseDouble(fv); thisStr = formatter.formatRawCellContents(d, this.formatIndex, this.formatString); } catch (Exception e) { // Formula is a String result not a Numeric one @@ -416,10 +416,10 @@ public class XSSFSheetXMLHandler extends DefaultHandler { break; case SST_STRING: - String sstIndex = value.toString(); + String sstIndex = value.toString().trim(); if (!sstIndex.isEmpty()) { try { - int idx = Integer.parseInt(sstIndex); + int idx = parseInt(sstIndex); RichTextString rtss = sharedStringsTable.getItemAt(idx); thisStr = rtss.toString(); } catch (NumberFormatException ex) { @@ -433,7 +433,7 @@ public class XSSFSheetXMLHandler extends DefaultHandler { if (this.formatString != null && !n.isEmpty()) { try { thisStr = formatter.formatRawCellContents( - Double.parseDouble(n), this.formatIndex, this.formatString); + parseDouble(n), this.formatIndex, this.formatString); } catch (Exception e) { LOG.atInfo().log( "Error formatting cell '{}' - will use its raw value instead (format '{}')", @@ -515,7 +515,6 @@ public class XSSFSheetXMLHandler extends DefaultHandler { } } - /** * Output an empty-cell comment. */ @@ -524,6 +523,14 @@ public class XSSFSheetXMLHandler extends DefaultHandler { output.cell(cellRef.formatAsString(), null, comment); } + private static int parseInt(String value) throws NumberFormatException { + return Integer.parseInt(value.trim()); + } + + private static double parseDouble(String value) throws NumberFormatException { + return Double.parseDouble(value.trim()); + } + private enum EmptyCellCommentsCheckType { CELL, END_OF_ROW, diff --git a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCell.java b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCell.java index 29088a5b48..d23375fda5 100644 --- a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCell.java +++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCell.java @@ -21,6 +21,8 @@ import java.time.LocalDateTime; import java.util.Calendar; import java.util.Date; +import org.apache.logging.log4j.Logger; +import org.apache.poi.logging.PoiLogManager; import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.formula.FormulaParser; import org.apache.poi.ss.formula.FormulaRenderer; @@ -73,6 +75,7 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellType; */ public final class XSSFCell extends CellBase { + private static final Logger LOG = PoiLogManager.getLogger(XSSFCell.class); private static final String FALSE_AS_STRING = "0"; private static final String TRUE_AS_STRING = "1"; private static final String FALSE = "FALSE"; @@ -244,7 +247,7 @@ public final class XSSFCell extends CellBase { return 0.0; } try { - return Double.parseDouble(v); + return parseDouble(v); } catch(NumberFormatException e) { throw typeMismatch(CellType.NUMERIC, CellType.STRING, false); } @@ -330,12 +333,13 @@ public final class XSSFCell extends CellBase { } else { if (_cell.isSetV()) { try { - int idx = Integer.parseInt(_cell.getV()); + int idx = parseInt(_cell.getV()); rt = (XSSFRichTextString)_sharedStringSource.getItemAt(idx); } catch (Throwable t) { if (ExceptionUtil.isFatal(t)) { ExceptionUtil.rethrow(t); } + LOG.atError().withThrowable(t).log("Failed to parse SST index '{}'", _cell.getV()); rt = new XSSFRichTextString(""); } } else { @@ -1122,12 +1126,12 @@ public final class XSSFCell extends CellBase { case BOOLEAN: return TRUE_AS_STRING.equals(_cell.getV()); case STRING: - int sstIndex = Integer.parseInt(_cell.getV()); + int sstIndex = parseInt(_cell.getV()); RichTextString rt = _sharedStringSource.getItemAt(sstIndex); String text = rt.getString(); return Boolean.parseBoolean(text); case NUMERIC: - return Double.parseDouble(_cell.getV()) != 0; + return parseDouble(_cell.getV()) != 0; case ERROR: // fall-through @@ -1149,13 +1153,14 @@ public final class XSSFCell extends CellBase { return TRUE_AS_STRING.equals(_cell.getV()) ? TRUE : FALSE; case STRING: try { - int sstIndex = Integer.parseInt(_cell.getV()); + int sstIndex = parseInt(_cell.getV()); RichTextString rt = _sharedStringSource.getItemAt(sstIndex); return rt.getString(); } catch (Throwable t) { if (ExceptionUtil.isFatal(t)) { ExceptionUtil.rethrow(t); } + LOG.atError().withThrowable(t).log("Failed to parse SST index '{}'", _cell.getV()); return ""; } case NUMERIC: @@ -1227,4 +1232,12 @@ public final class XSSFCell extends CellBase { ctCell.setR(r); } + private static int parseInt(String value) throws NumberFormatException { + return Integer.parseInt(value.trim()); + } + + private static double parseDouble(String value) throws NumberFormatException { + return Double.parseDouble(value.trim()); + } + } diff --git a/poi-ooxml/src/test/java/org/apache/poi/xssf/eventusermodel/TestXSSFSheetXMLHandler.java b/poi-ooxml/src/test/java/org/apache/poi/xssf/eventusermodel/TestXSSFSheetXMLHandler.java index b3f1215925..7e5a3071f2 100644 --- a/poi-ooxml/src/test/java/org/apache/poi/xssf/eventusermodel/TestXSSFSheetXMLHandler.java +++ b/poi-ooxml/src/test/java/org/apache/poi/xssf/eventusermodel/TestXSSFSheetXMLHandler.java @@ -26,7 +26,9 @@ import org.xml.sax.InputSource; import org.xml.sax.XMLReader; import java.io.InputStream; +import java.util.HashMap; import java.util.Iterator; +import java.util.Map; import static org.junit.jupiter.api.Assertions.assertDoesNotThrow; import static org.junit.jupiter.api.Assertions.assertEquals; @@ -100,4 +102,39 @@ class TestXSSFSheetXMLHandler { } } } + + @Test + void testSstStrayWhitespace() throws Exception { + try (OPCPackage xlsxPackage = OPCPackage.open(_ssTests.openResourceAsStream("bug69769.xlsx"))) { + final XSSFReader reader = new XSSFReader(xlsxPackage); + final Iterator<InputStream> iter = reader.getSheetsData(); + final Map<String, String> cellValues = new HashMap<>(); + + try (InputStream stream = iter.next()) { + final XMLReader sheetParser = XMLHelper.getSaxParserFactory().newSAXParser().getXMLReader(); + + sheetParser.setContentHandler(new XSSFSheetXMLHandler(reader.getStylesTable(), + new ReadOnlySharedStringsTable(xlsxPackage), new SheetContentsHandler() { + @Override + public void startRow(final int rowNum) { + } + + @Override + public void endRow(final int rowNum) { + } + + @Override + public void cell(final String cellReference, final String formattedValue, + final XSSFComment comment) { + cellValues.put(cellReference, formattedValue); + } + }, false)); + + assertDoesNotThrow(() -> sheetParser.parse(new InputSource(stream))); + + assertEquals(4, cellValues.size()); + assertEquals("Mustermann", cellValues.get("B2")); + } + } + } } diff --git a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFBugs.java b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFBugs.java index ef8c8542a0..22a9ec10e2 100644 --- a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFBugs.java +++ b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFBugs.java @@ -3910,6 +3910,34 @@ public final class TestXSSFBugs extends BaseTestBugzillaIssues { } } + @Test + void testBug69769() throws Exception { + final int expectedCount = 3; + try (XSSFWorkbook wb = openSampleWorkbook("bug69769.xlsx")) { + SharedStringsTable sst = wb.getSharedStringSource(); + assertNotNull(sst); + assertEquals(expectedCount, sst.getCount()); + for (int i = 0; i < expectedCount; i++) { + assertNotNull(sst.getItemAt(i)); + } + XSSFSheet ws = wb.getSheetAt(0); + int nRowCount = ws.getLastRowNum(); + DataFormatter df = new DataFormatter(); + for (int r = 0; r <= nRowCount; r++) { + XSSFRow row = ws.getRow(r); + if (row != null) { + for (Cell cell : row) { + String cellValue = df.formatCellValue(cell); + assertNotNull(cellValue, "Cell value should not be null"); + if (cell.getRowIndex() == 1 && cell.getColumnIndex() == 1) { + assertEquals("Mustermann", cellValue); + } + } + } + } + } + } + private static void readByCommonsCompress(File temp_excel_poi) throws IOException { /* read by commons-compress*/ try (ZipFile zipFile = ZipFile.builder().setFile(temp_excel_poi).get()) { diff --git a/poi/src/main/java/org/apache/poi/ss/format/CellFormatPart.java b/poi/src/main/java/org/apache/poi/ss/format/CellFormatPart.java index ea8eaa37b4..d8f59b70a7 100644 --- a/poi/src/main/java/org/apache/poi/ss/format/CellFormatPart.java +++ b/poi/src/main/java/org/apache/poi/ss/format/CellFormatPart.java @@ -26,8 +26,10 @@ import javax.swing.*; import java.awt.*; import java.util.*; +import java.util.List; import java.util.regex.Matcher; import java.util.regex.Pattern; +import java.util.stream.Collectors; import static org.apache.poi.ss.format.CellFormatter.quote; @@ -50,29 +52,13 @@ public class CellFormatPart { private static final Logger LOG = PoiLogManager.getLogger(CellFormatPart.class); static final Map<String, Color> NAMED_COLORS; + static final List<Color> INDEXED_COLORS; private final Color color; private final CellFormatCondition condition; private final CellFormatter format; private final CellFormatType type; - static { - NAMED_COLORS = new TreeMap<>( - String.CASE_INSENSITIVE_ORDER); - - for (HSSFColor.HSSFColorPredefined color : HSSFColor.HSSFColorPredefined.values()) { - String name = color.name(); - short[] rgb = color.getTriplet(); - Color c = new Color(rgb[0], rgb[1], rgb[2]); - NAMED_COLORS.put(name, c); - if (name.indexOf('_') > 0) - NAMED_COLORS.put(name.replace('_', ' '), c); - if (name.indexOf("_PERCENT") > 0) - NAMED_COLORS.put(name.replace("_PERCENT", "%").replace('_', - ' '), c); - } - } - /** Pattern for the color part of a cell format part. */ public static final Pattern COLOR_PAT; /** Pattern for the condition part of a cell format part. */ @@ -103,6 +89,50 @@ public class CellFormatPart { public static final int SPECIFICATION_GROUP; static { + // Build indexed color list, in order, from 1 to 56 + Integer[] indexedColors = new Integer[] { + 0x000000, 0xFFFFFF, 0xFF0000, 0x00FF00, 0x0000FF, 0xFFFF00, 0xFF00FF, 0x00FFFF, + 0x800000, 0x008000, 0x000080, 0x808000, 0x800080, 0x008080, 0xC0C0C0, 0x808080, + 0x9999FF, 0x993366, 0xFFFFCC, 0xCCFFFF, 0x660066, 0xFF8080, 0x0066CC, 0xCCCCFF, + 0x000080, 0xFF00FF, 0xFFFF00, 0x00FFFF, 0x800080, 0x800000, 0x008080, 0x0000FF, + 0x00CCFF, 0xCCFFFF, 0xCCFFCC, 0xFFFF99, 0x99CCFF, 0xFF99CC, 0xCC99FF, 0xFFCC99, + 0x3366FF, 0x33CCCC, 0x99CC00, 0xFFCC00, 0xFF9900, 0xFF6600, 0x666699, 0x969696, + 0x003366, 0x339966, 0x003300, 0x333300, 0x993300, 0x993366, 0x333399, 0x333333 + }; + INDEXED_COLORS = Collections.unmodifiableList( + Arrays.asList(indexedColors) + .stream().map(Color::new) + .collect(Collectors.toList())); + + // Build initial named color map + Map<String, Color> namedColors = new TreeMap<>( + String.CASE_INSENSITIVE_ORDER); + + // Retain compatibility with original implementation + for (HSSFColor.HSSFColorPredefined color : HSSFColor.HSSFColorPredefined.values()) { + String name = color.name(); + short[] rgb = color.getTriplet(); + Color c = new Color(rgb[0], rgb[1], rgb[2]); + namedColors.put(name, c); + if (name.indexOf('_') > 0) + namedColors.put(name.replace('_', ' '), c); + if (name.indexOf("_PERCENT") > 0) + namedColors.put(name.replace("_PERCENT", "%").replace('_', + ' '), c); + } + + // Add missing color values and replace incorrectly defined standard colors + // used in Excel, Google Sheets, etc. The first eight indexed colors correspond + // exactly to named colors. + namedColors.put("black", INDEXED_COLORS.get(0)); + namedColors.put("white", INDEXED_COLORS.get(1)); + namedColors.put("red", INDEXED_COLORS.get(2)); + namedColors.put("green", INDEXED_COLORS.get(3)); + namedColors.put("blue", INDEXED_COLORS.get(4)); + namedColors.put("yellow", INDEXED_COLORS.get(5)); + namedColors.put("magenta", INDEXED_COLORS.get(6)); + namedColors.put("cyan", INDEXED_COLORS.get(7)); + // A condition specification String condition = "([<>=]=?|!=|<>) # The operator\n" + " \\s*(-?([0-9]+(?:\\.[0-9]*)?)|(\\.[0-9]*))\\s* # The constant to test against\n"; @@ -110,8 +140,16 @@ public class CellFormatPart { // A currency symbol / string, in a specific locale String currency = "(\\[\\$.{0,3}(-[0-9a-f]{3,4})?])"; - String color = - "\\[(black|blue|cyan|green|magenta|red|white|yellow|color [0-9]+)]"; + // Build the color code matching expression. We should match any named color + // in the set as well as a string in the form of "Color 8" or "Color 15". + String color = "\\[("; + for (String key : namedColors.keySet()) { + // Escape special characters in the color name + color += key.replaceAll("([^a-zA-Z0-9])", "\\\\$1") + "|"; + } + // Match the indexed color table (accept both e.g. COLOR2 and COLOR 2) + // Both formats are accepted as input in other products + color += "color\\s*[0-9]+)\\]"; // A number specification // Note: careful that in something like ##, that the trailing comma is not caught up in the integer part @@ -159,6 +197,17 @@ public class CellFormatPart { CONDITION_OPERATOR_GROUP = findGroup(FORMAT_PAT, "[>=1]@", ">="); CONDITION_VALUE_GROUP = findGroup(FORMAT_PAT, "[>=1]@", "1"); SPECIFICATION_GROUP = findGroup(FORMAT_PAT, "[Blue][>1]\\a ?", "\\a ?"); + + // Once patterns have been compiled, add indexed colors to + // namedColors so they can be easily picked up by getColor(). + for (int i = 0; i < INDEXED_COLORS.size(); ++i) { + namedColors.put("color" + (i + 1), INDEXED_COLORS.get(i)); + // Also support space between "color" and number. + namedColors.put("color " + (i + 1), INDEXED_COLORS.get(i)); + } + + // Store namedColors as NAMED_COLORS + NAMED_COLORS = Collections.unmodifiableMap(namedColors); } interface PartHandler { @@ -250,12 +299,23 @@ public class CellFormatPart { * @return The color specification or {@code null}. */ private static Color getColor(Matcher m) { - String cdesc = m.group(COLOR_GROUP); - if (cdesc == null || cdesc.isEmpty()) + return getColor(m.group(COLOR_GROUP)); + } + + /** + * Get the Color object matching a color name, or {@code null} if the + * color name is not recognized. + * + * @param cname Color name, such as "red" or "Color 15" + * + * @return a Color object or {@code null}. + */ + static Color getColor(String cname) { + if (cname == null || cname.isEmpty()) return null; - Color c = NAMED_COLORS.get(cdesc); + Color c = NAMED_COLORS.get(cname); if (c == null) { - LOG.warn("Unknown color: {}", quote(cdesc)); + LOG.warn("Unknown color: {}", quote(cname)); } return c; } diff --git a/poi/src/test/java/org/apache/poi/ss/format/TestCellFormat.java b/poi/src/test/java/org/apache/poi/ss/format/TestCellFormat.java index d9572117f4..feb56716a7 100644 --- a/poi/src/test/java/org/apache/poi/ss/format/TestCellFormat.java +++ b/poi/src/test/java/org/apache/poi/ss/format/TestCellFormat.java @@ -20,6 +20,7 @@ import static org.junit.jupiter.api.Assertions.assertEquals; import static org.junit.jupiter.api.Assertions.assertNotNull; import static org.junit.jupiter.api.Assertions.assertTrue; +import java.awt.Color; import java.io.IOException; import java.text.ParseException; import java.text.SimpleDateFormat; @@ -33,6 +34,7 @@ import javax.swing.JLabel; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; @@ -1041,11 +1043,129 @@ class TestCellFormat { @Test void testNamedColors() { - assertTrue(CellFormatPart.NAMED_COLORS.size() >= HSSFColor.HSSFColorPredefined.values().length); - Stream.of("GREEN", "Green", "RED", "Red", "BLUE", "Blue", "YELLOW", "Yellow") - .map(CellFormatPart.NAMED_COLORS::get) + // Make sure we have all standard named colors defined + // and are returned as non-null regardless of case + Stream.of("black", "white", "red", "green", "blue", "yellow", "magenta", "cyan", + "Black", "White", "Red", "Green", "Blue", "Yellow", "Magenta", "Cyan", + "BLACK", "WHITE", "RED", "GREEN", "BLUE", "YELLOW", "MAGENTA", "CYAN") + .map(CellFormatPart::getColor) .forEach(Assertions::assertNotNull); } + + @Test + void testIndexedColorsExist() { + // Make sure the standard indexed colors are returned correctly and regardless of case + for (int i = 0; i < 56; ++i) { + assertNotNull(CellFormatPart.getColor("Color " + (i + 1))); + assertNotNull(CellFormatPart.getColor("COLOR" + (i + 1))); + assertNotNull(CellFormatPart.getColor("color" + (i + 1))); + } + } + + @Test + void verifyNamedColors() { + assertEquals(CellFormatPart.getColor("Black"), new Color(0x000000)); + assertEquals(CellFormatPart.getColor("white"), new Color(0xFFFFFF)); + assertEquals(CellFormatPart.getColor("RED"), new Color(0xFF0000)); + assertEquals(CellFormatPart.getColor("Green"), new Color(0x00FF00)); + assertEquals(CellFormatPart.getColor("blue"), new Color(0x0000FF)); + assertEquals(CellFormatPart.getColor("YELLOW"), new Color(0xFFFF00)); + assertEquals(CellFormatPart.getColor("Magenta"), new Color(0xFF00FF)); + assertEquals(CellFormatPart.getColor("cyan"), new Color(0x00FFFF)); + } + + @Test + void verifyIndexedColors() { + assertEquals(CellFormatPart.getColor("Color1"), CellFormatPart.getColor("black")); + assertEquals(CellFormatPart.getColor("color2"), CellFormatPart.getColor("white")); + assertEquals(CellFormatPart.getColor("Color3"), CellFormatPart.getColor("red")); + assertEquals(CellFormatPart.getColor("color4"), CellFormatPart.getColor("green")); + assertEquals(CellFormatPart.getColor("Color5"), CellFormatPart.getColor("blue")); + assertEquals(CellFormatPart.getColor("color6"), CellFormatPart.getColor("yellow")); + assertEquals(CellFormatPart.getColor("Color7"), CellFormatPart.getColor("magenta")); + assertEquals(CellFormatPart.getColor("color8"), CellFormatPart.getColor("cyan")); + assertEquals(CellFormatPart.getColor("Color9"), new Color(0x800000)); + assertEquals(CellFormatPart.getColor("color10"), new Color(0x008000)); + assertEquals(CellFormatPart.getColor("Color11"), new Color(0x000080)); + assertEquals(CellFormatPart.getColor("color12"), new Color(0x808000)); + assertEquals(CellFormatPart.getColor("Color13"), new Color(0x800080)); + assertEquals(CellFormatPart.getColor("color14"), new Color(0x008080)); + assertEquals(CellFormatPart.getColor("Color15"), new Color(0xC0C0C0)); + assertEquals(CellFormatPart.getColor("color16"), new Color(0x808080)); + assertEquals(CellFormatPart.getColor("Color17"), new Color(0x9999FF)); + assertEquals(CellFormatPart.getColor("COLOR18"), new Color(0x993366)); + assertEquals(CellFormatPart.getColor("Color19"), new Color(0xFFFFCC)); + assertEquals(CellFormatPart.getColor("color20"), new Color(0xCCFFFF)); + assertEquals(CellFormatPart.getColor("Color21"), new Color(0x660066)); + assertEquals(CellFormatPart.getColor("COLOR22"), new Color(0xFF8080)); + assertEquals(CellFormatPart.getColor("Color23"), new Color(0x0066CC)); + assertEquals(CellFormatPart.getColor("color24"), new Color(0xCCCCFF)); + assertEquals(CellFormatPart.getColor("Color25"), new Color(0x000080)); + assertEquals(CellFormatPart.getColor("color26"), new Color(0xFF00FF)); + assertEquals(CellFormatPart.getColor("Color27"), new Color(0xFFFF00)); + assertEquals(CellFormatPart.getColor("COLOR28"), new Color(0x00FFFF)); + assertEquals(CellFormatPart.getColor("Color29"), new Color(0x800080)); + assertEquals(CellFormatPart.getColor("color30"), new Color(0x800000)); + assertEquals(CellFormatPart.getColor("Color31"), new Color(0x008080)); + assertEquals(CellFormatPart.getColor("Color32"), new Color(0x0000FF)); + assertEquals(CellFormatPart.getColor("Color33"), new Color(0x00CCFF)); + assertEquals(CellFormatPart.getColor("Color34"), new Color(0xCCFFFF)); + assertEquals(CellFormatPart.getColor("Color35"), new Color(0xCCFFCC)); + assertEquals(CellFormatPart.getColor("Color36"), new Color(0xFFFF99)); + assertEquals(CellFormatPart.getColor("Color37"), new Color(0x99CCFF)); + assertEquals(CellFormatPart.getColor("Color38"), new Color(0xFF99CC)); + assertEquals(CellFormatPart.getColor("Color39"), new Color(0xCC99FF)); + assertEquals(CellFormatPart.getColor("Color40"), new Color(0xFFCC99)); + assertEquals(CellFormatPart.getColor("Color41"), new Color(0x3366FF)); + assertEquals(CellFormatPart.getColor("Color42"), new Color(0x33CCCC)); + assertEquals(CellFormatPart.getColor("Color43"), new Color(0x99CC00)); + assertEquals(CellFormatPart.getColor("Color44"), new Color(0xFFCC00)); + assertEquals(CellFormatPart.getColor("Color45"), new Color(0xFF9900)); + assertEquals(CellFormatPart.getColor("Color46"), new Color(0xFF6600)); + assertEquals(CellFormatPart.getColor("Color47"), new Color(0x666699)); + assertEquals(CellFormatPart.getColor("Color48"), new Color(0x969696)); + assertEquals(CellFormatPart.getColor("Color49"), new Color(0x003366)); + assertEquals(CellFormatPart.getColor("Color50"), new Color(0x339966)); + assertEquals(CellFormatPart.getColor("Color51"), new Color(0x003300)); + assertEquals(CellFormatPart.getColor("Color52"), new Color(0x333300)); + assertEquals(CellFormatPart.getColor("Color53"), new Color(0x993300)); + assertEquals(CellFormatPart.getColor("Color54"), new Color(0x993366)); + assertEquals(CellFormatPart.getColor("Color55"), new Color(0x333399)); + assertEquals(CellFormatPart.getColor("Color56"), new Color(0x333333)); + } + + @Test + void testColorsInWorkbook() throws IOException { + // Create a workbook, row and cell to test with + try (Workbook wb = new HSSFWorkbook()) { + Sheet sheet = wb.createSheet(); + Row row = sheet.createRow(0); + Cell cell = row.createCell(0); + CellFormatResult result; + CellFormat cf = CellFormat.getInstance( + "[GREEN]#,##0.0;[RED]\\(#,##0.0\\);[COLOR22]\"===\";[COLOR 8]\\\"@\\\""); + + cell.setCellValue(100.0); + result = cf.apply(cell); + assertEquals("100.0", result.text); + assertEquals(result.textColor, CellFormatPart.getColor("color 4")); + + cell.setCellValue(-50.0); + result = cf.apply(cell); + assertEquals("(50.0)", result.text); + assertEquals(result.textColor, CellFormatPart.getColor("red")); + + cell.setCellValue("foo"); + result = cf.apply(cell); + assertEquals("\"foo\"", result.text); + assertEquals(result.textColor, CellFormatPart.getColor("cyan")); + + cell.setCellValue(0.0); + result = cf.apply(cell); + assertEquals("===", result.text); + assertEquals(result.textColor, CellFormatPart.getColor("color 22")); + } + } @Test void testElapsedSecondsRound() { diff --git a/poi/src/test/java/org/apache/poi/util/TestIOUtils.java b/poi/src/test/java/org/apache/poi/util/TestIOUtils.java index 05ac383a17..9c026b14d4 100644 --- a/poi/src/test/java/org/apache/poi/util/TestIOUtils.java +++ b/poi/src/test/java/org/apache/poi/util/TestIOUtils.java @@ -580,7 +580,7 @@ final class TestIOUtils { void testNewFile() throws IOException { final File parent = TempFile.createTempDirectory("create-file-test"); try { - final String path0 = "path/to/file.txt"; + final String path0 = windowsPathIfNecessary("path/to/file.txt"); final File outFile = IOUtils.newFile(parent, path0); assertTrue(outFile.getAbsolutePath().endsWith(path0), "unexpected path: " + outFile.getAbsolutePath()); diff --git a/test-data/spreadsheet/bug69769.xlsx b/test-data/spreadsheet/bug69769.xlsx Binary files differnew file mode 100644 index 0000000000..39262dfff3 --- /dev/null +++ b/test-data/spreadsheet/bug69769.xlsx |