TestCellFormatPart.java
/* ====================================================================
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.ss.tests.format;
import static java.awt.Color.ORANGE;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertNotNull;
import java.awt.Color;
import java.io.IOException;
import java.util.Locale;
import java.util.TimeZone;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.swing.JLabel;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.ITestDataProvider;
import org.apache.poi.ss.format.CellFormat;
import org.apache.poi.ss.format.CellFormatPart;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.LocaleUtil;
import org.apache.poi.xssf.XSSFITestDataProvider;
import org.junit.jupiter.api.AfterAll;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;
/**
* Class for spreadsheet-based tests, such as are used for cell formatting.
* This reads tests from the spreadsheet, as well as reading
* flags that can be used to parameterize these tests.
* <p>
* Each test has four parts: The expected result (column A), the format string
* (column B), the value to format (column C), and a comma-separated list of
* categories that this test falls in. Normally all tests are run, but if the
* flag "Categories" is not empty, only tests that have at least one category
* listed in "Categories" are run.
*/
class TestCellFormatPart {
private static final Pattern NUMBER_EXTRACT_FMT = Pattern.compile(
"([-+]?[0-9]+)(\\.[0-9]+)?.*(?:(e).*?([+-]?[0-9]+))",
Pattern.CASE_INSENSITIVE);
private static final Color TEST_COLOR = ORANGE.darker();
private static Locale userLocale;
@BeforeAll
public static void setLocale() {
userLocale = LocaleUtil.getUserLocale();
LocaleUtil.setUserLocale(Locale.UK);
}
@AfterAll
public static void unsetLocale() {
LocaleUtil.setUserLocale(userLocale);
}
private final ITestDataProvider _testDataProvider = XSSFITestDataProvider.instance;
private interface CellValue {
Object getValue(Cell cell);
default void equivalent(String expected, String actual, CellFormatPart format) {
assertEquals('"' + expected + '"', '"' + actual + '"', "format \"" + format + "\"");
}
}
@Test
void testGeneralFormat() throws IOException {
runFormatTests("GeneralFormatTests.xlsx", cell -> {
assertNotNull(cell);
switch (CellFormat.ultimateType(cell)) {
case BOOLEAN:
return cell.getBooleanCellValue();
case NUMERIC:
return cell.getNumericCellValue();
default:
return cell.getStringCellValue();
}
});
}
@Test
void testNumberFormat() throws IOException {
runFormatTests("NumberFormatTests.xlsx", Cell::getNumericCellValue);
}
@Test
void testNumberApproxFormat() throws IOException {
runFormatTests("NumberFormatApproxTests.xlsx", new CellValue() {
@Override
public Object getValue(Cell cell) {
return cell.getNumericCellValue();
}
@Override
public void equivalent(String expected, String actual,
CellFormatPart format) {
double expectedVal = extractNumber(expected);
double actualVal = extractNumber(actual);
// equal within 1%
double delta = expectedVal / 100;
assertEquals(expectedVal, actualVal, delta, "format \"" + format + "\"," + expected + " ~= " + actual);
}
});
}
@Test
void testDateFormat() throws IOException {
TimeZone tz = LocaleUtil.getUserTimeZone();
LocaleUtil.setUserTimeZone(TimeZone.getTimeZone("CET"));
try {
runFormatTests("DateFormatTests.xlsx", Cell::getDateCellValue);
} finally {
LocaleUtil.setUserTimeZone(tz);
}
}
@Test
void testDateFormatNumbers() throws IOException {
TimeZone tz = LocaleUtil.getUserTimeZone();
LocaleUtil.setUserTimeZone(TimeZone.getTimeZone("CET"));
try {
runFormatTests("DateFormatNumberTests.xlsx", Cell::getNumericCellValue);
} finally {
LocaleUtil.setUserTimeZone(tz);
}
}
@Test
void testElapsedFormat() throws IOException {
runFormatTests("ElapsedFormatTests.xlsx", Cell::getNumericCellValue);
}
@Test
void testTextFormat() throws IOException {
runFormatTests("TextFormatTests.xlsx", cell ->
(CellFormat.ultimateType(cell) == CellType.BOOLEAN) ? cell.getBooleanCellValue() : cell.getStringCellValue()
);
}
@Test
void testConditions() throws IOException {
runFormatTests("FormatConditionTests.xlsx", Cell::getNumericCellValue);
}
private double extractNumber(String str) {
Matcher m = NUMBER_EXTRACT_FMT.matcher(str);
if (!m.find()) {
throw new IllegalArgumentException("Cannot find number in \"" + str + "\"");
}
StringBuilder sb = new StringBuilder();
// The groups in the pattern are the parts of the number
for (int i = 1; i <= m.groupCount(); i++) {
String part = m.group(i);
if (part != null)
sb.append(part);
}
return Double.parseDouble(sb.toString());
}
protected void runFormatTests(String workbookName, CellValue valueGetter) throws IOException {
try (Workbook workbook = _testDataProvider.openSampleWorkbook(workbookName)) {
workbook.setMissingCellPolicy(Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
Sheet sheet = workbook.getSheet("Tests");
boolean isHeader = true;
for (Row row : sheet) {
// Skip the header row
if (isHeader || row == null) {
isHeader = false;
continue;
}
String expectedText = row.getCell(0).getStringCellValue();
String format = row.getCell(1).getStringCellValue();
Cell value = row.getCell(2);
if (expectedText.isEmpty() && format.isEmpty()) {
continue;
}
Object objVal = valueGetter.getValue(value);
JLabel label = new JLabel();
label.setForeground(TEST_COLOR);
label.setText("xyzzy");
Color origColor = label.getForeground();
CellFormatPart cellFormatPart = new CellFormatPart(format);
// If this doesn't apply, no color change is expected
Color expectedColor = cellFormatPart.apply(label, objVal).applies ? TEST_COLOR : origColor;
String actualText = label.getText();
Color actualColor = label.getForeground();
valueGetter.equivalent(expectedText, actualText, cellFormatPart);
assertEquals(expectedColor, actualColor, "no color");
}
}
}
@Test
void testDecimalFormat() throws Exception {
// 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);
CellFormat cf = CellFormat.getInstance("[<=.01]0.00%;#,##0");
cell.setCellValue(1);
assertEquals("1", cf.apply(cell).text);
cell.setCellValue(.001);
assertEquals("0.10%", cf.apply(cell).text);
}
}
}