/* ====================================================================
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 org.junit.BeforeClass;
import org.junit.Test;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;
import java.io.IOException;
import java.util.Map;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.xssf.XSSFTestDataSamples;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public final class TestStylesTable {
private static final String testFile = "Formatting.xlsx";
private static final String customDataFormat = "YYYY-mm-dd";
@BeforeClass
public static void assumeCustomDataFormatIsNotBuiltIn() {
assertEquals(-1, BuiltinFormats.getBuiltinFormat(customDataFormat));
}
@Test
public void testCreateNew() {
StylesTable st = new StylesTable();
// Check defaults
assertNotNull(st.getCTStylesheet());
assertEquals(1, st._getXfsSize());
assertEquals(1, st._getStyleXfsSize());
assertEquals(0, st.getNumDataFormats());
}
@Test
public void testCreateSaveLoad() {
XSSFWorkbook wb = new XSSFWorkbook();
StylesTable st = wb.getStylesSource();
assertNotNull(st.getCTStylesheet());
assertEquals(1, st._getXfsSize());
assertEquals(1, st._getStyleXfsSize());
assertEquals(0, st.getNumDataFormats());
st = XSSFTestDataSamples.writeOutAndReadBack(wb).getStylesSource();
assertNotNull(st.getCTStylesheet());
assertEquals(1, st._getXfsSize());
assertEquals(1, st._getStyleXfsSize());
assertEquals(0, st.getNumDataFormats());
assertNotNull(XSSFTestDataSamples.writeOutAndReadBack(wb));
}
@Test
public void testLoadExisting() {
XSSFWorkbook workbook = XSSFTestDataSamples.openSampleWorkbook(testFile);
assertNotNull(workbook.getStylesSource());
StylesTable st = workbook.getStylesSource();
doTestExisting(st);
assertNotNull(XSSFTestDataSamples.writeOutAndReadBack(workbook));
}
@Test
public void testLoadSaveLoad() {
XSSFWorkbook workbook = XSSFTestDataSamples.openSampleWorkbook(testFile);
assertNotNull(workbook.getStylesSource());
StylesTable st = workbook.getStylesSource();
doTestExisting(st);
st = XSSFTestDataSamples.writeOutAndReadBack(workbook).getStylesSource();
doTestExisting(st);
}
public void doTestExisting(StylesTable st) {
// Check contents
assertNotNull(st.getCTStylesheet());
assertEquals(11, st._getXfsSize());
assertEquals(1, st._getStyleXfsSize());
assertEquals(8, st.getNumDataFormats());
assertEquals(2, st.getFonts().size());
assertEquals(2, st.getFills().size());
assertEquals(1, st.getBorders().size());
assertEquals("yyyy/mm/dd", st.getNumberFormatAt((short)165));
assertEquals("yy/mm/dd", st.getNumberFormatAt((short)167));
assertNotNull(st.getStyleAt(0));
assertNotNull(st.getStyleAt(1));
assertNotNull(st.getStyleAt(2));
assertEquals(0, st.getStyleAt(0).getDataFormat());
assertEquals(14, st.getStyleAt(1).getDataFormat());
assertEquals(0, st.getStyleAt(2).getDataFormat());
assertEquals(165, st.getStyleAt(3).getDataFormat());
assertEquals("yyyy/mm/dd", st.getStyleAt(3).getDataFormatString());
}
@Test
public void populateNew() {
XSSFWorkbook wb = new XSSFWorkbook();
StylesTable st = wb.getStylesSource();
assertNotNull(st.getCTStylesheet());
assertEquals(1, st._getXfsSize());
assertEquals(1, st._getStyleXfsSize());
assertEquals(0, st.getNumDataFormats());
int nf1 = st.putNumberFormat("yyyy-mm-dd");
int nf2 = st.putNumberFormat("yyyy-mm-DD");
assertEquals(nf1, st.putNumberFormat("yyyy-mm-dd"));
st.putStyle(new XSSFCellStyle(st));
// Save and re-load
st = XSSFTestDataSamples.writeOutAndReadBack(wb).getStylesSource();
assertNotNull(st.getCTStylesheet());
assertEquals(2, st._getXfsSize());
assertEquals(1, st._getStyleXfsSize());
assertEquals(2, st.getNumDataFormats());
assertEquals("yyyy-mm-dd", st.getNumberFormatAt((short)nf1));
assertEquals(nf1, st.putNumberFormat("yyyy-mm-dd"));
assertEquals(nf2, st.putNumberFormat("yyyy-mm-DD"));
assertNotNull(XSSFTestDataSamples.writeOutAndReadBack(wb));
}
@Test
public void populateExisting() {
XSSFWorkbook workbook = XSSFTestDataSamples.openSampleWorkbook(testFile);
assertNotNull(workbook.getStylesSource());
StylesTable st = workbook.getStylesSource();
assertEquals(11, st._getXfsSize());
assertEquals(1, st._getStyleXfsSize());
assertEquals(8, st.getNumDataFormats());
int nf1 = st.putNumberFormat("YYYY-mm-dd");
int nf2 = st.putNumberFormat("YYYY-mm-DD");
assertEquals(nf1, st.putNumberFormat("YYYY-mm-dd"));
st = XSSFTestDataSamples.writeOutAndReadBack(workbook).getStylesSource();
assertEquals(11, st._getXfsSize());
assertEquals(1, st._getStyleXfsSize());
assertEquals(10, st.getNumDataFormats());
assertEquals("YYYY-mm-dd", st.getNumberFormatAt((short)nf1));
assertEquals(nf1, st.putNumberFormat("YYYY-mm-dd"));
assertEquals(nf2, st.putNumberFormat("YYYY-mm-DD"));
assertNotNull(XSSFTestDataSamples.writeOutAndReadBack(workbook));
}
@Test
public void exceedNumberFormatLimit() throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
try {
StylesTable styles = wb.getStylesSource();
for (int i = 0; i < styles.getMaxNumberOfDataFormats(); i++) {
wb.getStylesSource().putNumberFormat("\"test" + i + " \"0");
}
try {
wb.getStylesSource().putNumberFormat("\"anotherformat \"0");
} catch (final IllegalStateException e) {
if (e.getMessage().startsWith("The maximum number of Data Formats was exceeded.")) {
//expected
}
else {
throw e;
}
}
} finally {
wb.close();
}
}
private static final <K,V> void assertNotContainsKey(Map<K,V> map, K key) {
assertFalse(map.containsKey(key));
}
private static final <K,V> void assertNotContainsValue(Map<K,V> map, V value) {
assertFalse(map.containsValue(value));
}
@Test
public void removeNumberFormat() throws IOException {
XSSFWorkbook wb1 = new XSSFWorkbook();
try {
final String fmt = customDataFormat;
final short fmtIdx = (short) wb1.getStylesSource().putNumberFormat(fmt);
Cell cell = wb1.createSheet("test").createRow(0).createCell(0);
cell.setCellValue(5.25);
CellStyle style = wb1.createCellStyle();
style.setDataFormat(fmtIdx);
cell.setCellStyle(style);
assertEquals(fmt, cell.getCellStyle().getDataFormatString());
assertEquals(fmt, wb1.getStylesSource().getNumberFormatAt(fmtIdx));
// remove the number format from the workbook
wb1.getStylesSource().removeNumberFormat(fmt);
// number format in CellStyles should be restored to default number format
final short defaultFmtIdx = 0;
final String defaultFmt = BuiltinFormats.getBuiltinFormat(0);
assertEquals(defaultFmtIdx, style.getDataFormat());
assertEquals(defaultFmt, style.getDataFormatString());
// The custom number format should be entirely removed from the workbook
Map<Short,String> numberFormats = wb1.getStylesSource().getNumberFormats();
assertNotContainsKey(numberFormats, fmtIdx);
assertNotContainsValue(numberFormats, fmt);
// The default style shouldn't be added back to the styles source because it's built-in
assertEquals(0, wb1.getStylesSource().getNumDataFormats());
cell = null; style = null; numberFormats = null;
XSSFWorkbook wb2 = XSSFTestDataSamples.writeOutCloseAndReadBack(wb1);
cell = wb2.getSheet("test").getRow(0).getCell(0);
style = cell.getCellStyle();
// number format in CellStyles should be restored to default number format
assertEquals(defaultFmtIdx, style.getDataFormat());
assertEquals(defaultFmt, style.getDataFormatString());
// The custom number format should be entirely removed from the workbook
numberFormats = wb2.getStylesSource().getNumberFormats();
assertNotContainsKey(numberFormats, fmtIdx);
assertNotContainsValue(numberFormats, fmt);
// The default style shouldn't be added back to the styles source because it's built-in
assertEquals(0, wb2.getStylesSource().getNumDataFormats());
wb2.close();
} finally {
wb1.close();
}
}
@Test
public void maxNumberOfDataFormats() throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
try {
StylesTable styles = wb.getStylesSource();
// Check default limit
int n = styles.getMaxNumberOfDataFormats();
// https://support.office.com/en-us/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3
assertTrue(200 <= n);
assertTrue(n <= 250);
// Check upper limit
n = Integer.MAX_VALUE;
styles.setMaxNumberOfDataFormats(n);
assertEquals(n, styles.getMaxNumberOfDataFormats());
// Check negative (illegal) limits
try {
styles.setMaxNumberOfDataFormats(-1);
fail("Expected to get an IllegalArgumentException(\"Maximum Number of Data Formats must be greater than or equal to 0\")");
} catch (final IllegalArgumentException e) {
if (e.getMessage().startsWith("Maximum Number of Data Formats must be greater than or equal to 0")) {
// expected
} else {
throw e;
}
}
}
finally {
wb.close();
}
}
@Test
public void addDataFormatsBeyondUpperLimit() throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
try {
StylesTable styles = wb.getStylesSource();
styles.setMaxNumberOfDataFormats(0);
// Try adding a format beyond the upper limit
try {
styles.putNumberFormat("\"test \"0");
fail("Expected to raise IllegalStateException");
} catch (final IllegalStateException e) {
if (e.getMessage().startsWith("The maximum number of Data Formats was exceeded.")) {
// expected
} else {
throw e;
}
}
}
finally {
wb.close();
}
}
@Test
public void decreaseUpperLimitBelowCurrentNumDataFormats() throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
try {
StylesTable styles = wb.getStylesSource();
styles.putNumberFormat(customDataFormat);
// Try decreasing the upper limit below the current number of formats
try {
styles.setMaxNumberOfDataFormats(0);
fail("Expected to raise IllegalStateException");
} catch (final IllegalStateException e) {
if (e.getMessage().startsWith("Cannot set the maximum number of data formats less than the current quantity.")) {
// expected
} else {
throw e;
}
}
}
finally {
wb.close();
}
}
}