trunk/src/ooxml/testcases/org/apache/poi/xssf/model/TestThemesTable.java - poi - Git at Google

 /* ====================================================================
    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());
     }
 }
	/* ====================================================================
	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());
	}
	}