/* ==================================================================== | |
Licensed to the Apache Software Foundation (ASF) under one or more | |
contributor license agreements. See the NOTICE file distributed with | |
this work for additional information regarding copyright ownership. | |
The ASF licenses this file to You under the Apache License, Version 2.0 | |
(the "License"); you may not use this file except in compliance with | |
the License. You may obtain a copy of the License at | |
http://www.apache.org/licenses/LICENSE-2.0 | |
Unless required by applicable law or agreed to in writing, software | |
distributed under the License is distributed on an "AS IS" BASIS, | |
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. | |
See the License for the specific language governing permissions and | |
limitations under the License. | |
==================================================================== */ | |
package org.apache.poi.xssf.model; | |
import static org.junit.Assert.assertEquals; | |
import static org.junit.Assert.assertNotNull; | |
import java.io.FileOutputStream; | |
import java.util.LinkedHashMap; | |
import java.util.Locale; | |
import java.util.Map; | |
import org.apache.commons.codec.binary.Hex; | |
import org.apache.poi.ss.usermodel.CellStyle; | |
import org.apache.poi.ss.util.CellReference; | |
import org.apache.poi.xssf.XSSFTestDataSamples; | |
import org.apache.poi.xssf.model.ThemesTable.ThemeElement; | |
import org.apache.poi.xssf.usermodel.XSSFCell; | |
import org.apache.poi.xssf.usermodel.XSSFCellStyle; | |
import org.apache.poi.xssf.usermodel.XSSFColor; | |
import org.apache.poi.xssf.usermodel.XSSFFont; | |
import org.apache.poi.xssf.usermodel.XSSFRow; | |
import org.apache.poi.xssf.usermodel.XSSFSheet; | |
import org.apache.poi.xssf.usermodel.XSSFWorkbook; | |
import org.junit.Test; | |
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTColor; | |
public class TestThemesTable { | |
private final String testFileSimple = "Themes.xlsx"; | |
private final String testFileComplex = "Themes2.xlsx"; | |
// TODO .xls version available too, add HSSF support then check | |
// What colours they should show up as | |
private static String rgbExpected[] = { | |
"ffffff", // Lt1 | |
"000000", // Dk1 | |
"eeece1", // Lt2 | |
"1f497d", // DK2 | |
"4f81bd", // Accent1 | |
"c0504d", // Accent2 | |
"9bbb59", // Accent3 | |
"8064a2", // Accent4 | |
"4bacc6", // Accent5 | |
"f79646", // Accent6 | |
"0000ff", // Hlink | |
"800080" // FolHlink | |
}; | |
@Test | |
public void testThemesTableColors() throws Exception { | |
// Load our two test workbooks | |
XSSFWorkbook simple = XSSFTestDataSamples.openSampleWorkbook(testFileSimple); | |
XSSFWorkbook complex = XSSFTestDataSamples.openSampleWorkbook(testFileComplex); | |
// Save and re-load them, to check for stability across that | |
XSSFWorkbook simpleRS = XSSFTestDataSamples.writeOutAndReadBack(simple); | |
XSSFWorkbook complexRS = XSSFTestDataSamples.writeOutAndReadBack(complex); | |
// Fetch fresh copies to test with | |
simple = XSSFTestDataSamples.openSampleWorkbook(testFileSimple); | |
complex = XSSFTestDataSamples.openSampleWorkbook(testFileComplex); | |
// Files and descriptions | |
Map<String,XSSFWorkbook> workbooks = new LinkedHashMap<String, XSSFWorkbook>(); | |
workbooks.put(testFileSimple, simple); | |
workbooks.put("Re-Saved_" + testFileSimple, simpleRS); | |
workbooks.put(testFileComplex, complex); | |
workbooks.put("Re-Saved_" + testFileComplex, complexRS); | |
// Sanity check | |
assertEquals(rgbExpected.length, rgbExpected.length); | |
// For offline testing | |
boolean createFiles = false; | |
// Check each workbook in turn, and verify that the colours | |
// for the theme-applied cells in Column A are correct | |
for (String whatWorkbook : workbooks.keySet()) { | |
XSSFWorkbook workbook = workbooks.get(whatWorkbook); | |
XSSFSheet sheet = workbook.getSheetAt(0); | |
int startRN = 0; | |
if (whatWorkbook.endsWith(testFileComplex)) startRN++; | |
for (int rn=startRN; rn<rgbExpected.length+startRN; rn++) { | |
XSSFRow row = sheet.getRow(rn); | |
assertNotNull("Missing row " + rn + " in " + whatWorkbook, row); | |
String ref = (new CellReference(rn, 0)).formatAsString(); | |
XSSFCell cell = row.getCell(0); | |
assertNotNull( | |
"Missing cell " + ref + " in " + whatWorkbook, cell); | |
int expectedThemeIdx = rn-startRN; | |
ThemeElement themeElem = ThemeElement.byId(expectedThemeIdx); | |
assertEquals( | |
"Wrong theme at " + ref + " in " + whatWorkbook, | |
themeElem.name.toLowerCase(Locale.ROOT), cell.getStringCellValue()); | |
// Fonts are theme-based in their colours | |
XSSFFont font = cell.getCellStyle().getFont(); | |
CTColor ctColor = font.getCTFont().getColorArray(0); | |
assertNotNull(ctColor); | |
assertEquals(true, ctColor.isSetTheme()); | |
assertEquals(themeElem.idx, ctColor.getTheme()); | |
// Get the colour, via the theme | |
XSSFColor color = font.getXSSFColor(); | |
// Theme colours aren't tinted | |
assertEquals(false, color.hasTint()); | |
// Check the RGB part (no tint) | |
assertEquals( | |
"Wrong theme colour " + themeElem.name + " on " + whatWorkbook, | |
rgbExpected[expectedThemeIdx], Hex.encodeHexString(color.getRGB())); | |
long themeIdx = font.getCTFont().getColorArray(0).getTheme(); | |
assertEquals( | |
"Wrong theme index " + expectedThemeIdx + " on " + whatWorkbook, | |
expectedThemeIdx, themeIdx); | |
if (createFiles) { | |
XSSFCellStyle cs = row.getSheet().getWorkbook().createCellStyle(); | |
cs.setFillForegroundColor(color); | |
cs.setFillPattern(CellStyle.SOLID_FOREGROUND); | |
row.createCell(1).setCellStyle(cs); | |
} | |
} | |
if (createFiles) { | |
FileOutputStream fos = new FileOutputStream("Generated_"+whatWorkbook); | |
workbook.write(fos); | |
fos.close(); | |
} | |
} | |
} | |
/** | |
* Ensure that, for a file with themes, we can correctly | |
* read both the themed and non-themed colours back. | |
* Column A = Theme Foreground | |
* Column B = Theme Foreground | |
* Column C = Explicit Colour Foreground | |
* Column E = Explicit Colour Background, Black Foreground | |
* Column G = Conditional Formatting Backgrounds | |
* | |
* Note - Grey Row has an odd way of doing the styling... | |
*/ | |
@Test | |
public void themedAndNonThemedColours() { | |
XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook(testFileComplex); | |
XSSFSheet sheet = wb.getSheetAt(0); | |
XSSFCellStyle style; | |
XSSFColor color; | |
XSSFCell cell; | |
String[] names = {"White","Black","Grey","Dark Blue","Blue","Red","Green"}; | |
String[] explicitFHexes = { "FFFFFFFF", "FF000000", "FFC0C0C0", "FF002060", | |
"FF0070C0", "FFFF0000", "FF00B050" }; | |
String[] explicitBHexes = { "FFFFFFFF", "FF000000", "FFC0C0C0", "FF002060", | |
"FF0000FF", "FFFF0000", "FF00FF00" }; | |
assertEquals(7, names.length); | |
// Check the non-CF colours in Columns A, B, C and E | |
for (int rn=1; rn<8; rn++) { | |
int idx = rn-1; | |
XSSFRow row = sheet.getRow(rn); | |
assertNotNull("Missing row " + rn, row); | |
// Theme cells come first | |
XSSFCell themeCell = row.getCell(0); | |
ThemeElement themeElem = ThemeElement.byId(idx); | |
assertCellContents(themeElem.name, themeCell); | |
// Sanity check names | |
assertCellContents(names[idx], row.getCell(1)); | |
assertCellContents(names[idx], row.getCell(2)); | |
assertCellContents(names[idx], row.getCell(4)); | |
// Check the colours | |
// A: Theme Based, Foreground | |
style = themeCell.getCellStyle(); | |
color = style.getFont().getXSSFColor(); | |
assertEquals(true, color.isThemed()); | |
assertEquals(idx, color.getTheme()); | |
assertEquals(rgbExpected[idx], Hex.encodeHexString(color.getRGB())); | |
// B: Theme Based, Foreground | |
cell = row.getCell(1); | |
style = cell.getCellStyle(); | |
color = style.getFont().getXSSFColor(); | |
assertEquals(true, color.isThemed()); | |
if (idx != 2) { | |
assertEquals(idx, color.getTheme()); | |
assertEquals(rgbExpected[idx], Hex.encodeHexString(color.getRGB())); | |
} else { | |
assertEquals(1, color.getTheme()); | |
assertEquals(0.50, color.getTint(), 0.001); | |
} | |
// C: Explicit, Foreground | |
cell = row.getCell(2); | |
style = cell.getCellStyle(); | |
color = style.getFont().getXSSFColor(); | |
assertEquals(false, color.isThemed()); | |
assertEquals(explicitFHexes[idx], color.getARGBHex()); | |
// E: Explicit Background, Foreground all Black | |
cell = row.getCell(4); | |
style = cell.getCellStyle(); | |
color = style.getFont().getXSSFColor(); | |
assertEquals(true, color.isThemed()); | |
assertEquals("FF000000", color.getARGBHex()); | |
color = style.getFillForegroundXSSFColor(); | |
assertEquals(false, color.isThemed()); | |
assertEquals(explicitBHexes[idx], color.getARGBHex()); | |
color = style.getFillBackgroundColorColor(); | |
assertEquals(false, color.isThemed()); | |
assertEquals(null, color.getARGBHex()); | |
} | |
// Check the CF colours | |
// TODO | |
} | |
private static void assertCellContents(String expected, XSSFCell cell) { | |
assertNotNull(cell); | |
assertEquals(expected.toLowerCase(Locale.ROOT), | |
cell.getStringCellValue().toLowerCase(Locale.ROOT)); | |
} | |
@Test | |
@SuppressWarnings("resource") | |
public void testAddNew() throws Exception { | |
XSSFWorkbook wb = new XSSFWorkbook(); | |
wb.createSheet(); | |
assertEquals(null, wb.getTheme()); | |
StylesTable styles = wb.getStylesSource(); | |
assertEquals(null, styles.getTheme()); | |
styles.ensureThemesTable(); | |
assertNotNull(styles.getTheme()); | |
assertNotNull(wb.getTheme()); | |
wb = XSSFTestDataSamples.writeOutAndReadBack(wb); | |
styles = wb.getStylesSource(); | |
assertNotNull(styles.getTheme()); | |
assertNotNull(wb.getTheme()); | |
} | |
} |