/* ====================================================================
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.hssf.usermodel;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertTrue;
import java.text.DecimalFormat;
import java.text.DecimalFormatSymbols;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Iterator;
import java.util.Locale;
import java.util.TimeZone;
import org.apache.poi.hssf.HSSFTestDataSamples;
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.util.LocaleUtil;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
/**
* Unit tests for HSSFDataFormatter.java
*/
public final class TestHSSFDataFormatter {
private static TimeZone userTimeZone;
@BeforeClass
public static void setTimeZone() {
userTimeZone = LocaleUtil.getUserTimeZone();
LocaleUtil.setUserTimeZone(TimeZone.getTimeZone("CET"));
LocaleUtil.setUserLocale(Locale.US);
}
@AfterClass
public static void resetTimeZone() {
LocaleUtil.setUserTimeZone(userTimeZone);
LocaleUtil.setUserLocale(Locale.ROOT);
}
private final HSSFDataFormatter formatter;
private final HSSFWorkbook wb;
public TestHSSFDataFormatter() {
// create the formatter to test
formatter = new HSSFDataFormatter();
// create a workbook to test with
wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFDataFormat format = wb.createDataFormat();
// create a row and put some cells in it
HSSFRow row = sheet.createRow(0);
// date value for July 8 1901 1:19 PM
double dateNum = 555.555;
// date value for July 8 1901 11:23 AM
double timeNum = 555.47431;
//valid date formats -- all should have "Jul" in output
String[] goodDatePatterns = {
"[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy",
"mmm/d/yy\\ h:mm PM;@",
"mmmm/d/yy\\ h:mm;@",
"mmmm/d;@",
"mmmm/d/yy;@",
"mmm/dd/yy;@",
"[$-409]d\\-mmm;@",
"[$-409]d\\-mmm\\-yy;@",
"[$-409]dd\\-mmm\\-yy;@",
"[$-409]mmm\\-yy;@",
"[$-409]mmmm\\-yy;@",
"[$-409]mmmm\\ d\\,\\ yyyy;@",
"[$-409]mmm/d/yy\\ h:mm:ss;@",
"[$-409]mmmm/d/yy\\ h:mm:ss am;@",
"[$-409]mmmmm;@",
"[$-409]mmmmm\\-yy;@",
"mmmm/d/yyyy;@",
"[$-409]d\\-mmm\\-yyyy;@",
"[$-409]d\\-mmm;[$-3]d\\-mmm;@", // international three-part
"[$-41f]d\\-mmm;[$-41f]d\\-mmm;@", // turkish international three-part
"[$-F40f]d\\-mmm;[$-F40f]d\\-mmm;@", // custom international three-part
"[$-F40f]d\\-mmm;[$-F40f]d\\-mmm;0;@" // custom international four-part
};
//valid time formats - all should have 11:23 in output
String[] goodTimePatterns = {
"HH:MM",
"HH:MM:SS",
"HH:MM;HH:MM;HH:MM",
// This is fun - blue if positive time,
// red if negative time or green for zero!
"[BLUE]HH:MM;[RED]HH:MM;[GREEN]HH:MM",
"yyyy-mm-dd hh:mm",
"yyyy-mm-dd hh:mm:ss",
};
// valid number formats
String[] goodNumPatterns = {
"#,##0.0000",
"#,##0;[Red]#,##0",
"(#,##0.00_);(#,##0.00)",
"($#,##0.00_);[Red]($#,##0.00)",
"$#,##0.00",
"[$-809]#,##0.00", // international format
"[$-2]#,##0.00", // international format
"[$-041f]#,##0.00", // international format
"0000.00000%",
"0.000E+00",
"0.00E+00",
"[BLACK]0.00;[COLOR 5]##.##",
"[>999999]#,,\"M\";[>999]#,\"K\";#", // num/K/M
"[>999999]#.000,,\"M\";[>999]#.000,\"K\";#.000", // with decimals
"[$-809]#,##0.00;[$-809]#,##0.00", // two-part international format
"[$-809]#,##0.00;[$-809]#,##0.00;0", // three-part international format
"[$-809]#,##0.00;[$-809]#,##0.00;0;@", // four-part international format
};
// invalid date formats -- will throw exception in DecimalFormat ctor
String[] badNumPatterns = {
"#,#$'#0.0000",
"'#','#ABC#0;##,##0",
"000 '123 4'5'6 000",
"#''0#0'1#10L16EE"
};
// create cells with good date patterns
for (int i = 0; i < goodDatePatterns.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellValue(dateNum);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(format.getFormat(goodDatePatterns[i]));
cell.setCellStyle(cellStyle);
}
row = sheet.createRow(1);
// create cells with time patterns
for (int i = 0; i < goodTimePatterns.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellValue(timeNum);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(format.getFormat(goodTimePatterns[i]));
cell.setCellStyle(cellStyle);
}
row = sheet.createRow(2);
// create cells with num patterns
for (int i = 0; i < goodNumPatterns.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellValue(-1234567890.12345);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(format.getFormat(goodNumPatterns[i]));
cell.setCellStyle(cellStyle);
}
row = sheet.createRow(3);
// create cells with bad num patterns
for (int i = 0; i < badNumPatterns.length; i++) {
HSSFCell cell = row.createCell(i);
// If the '.' is any later, ExcelGeneralNumberFormat will render an integer, as Excel does.
cell.setCellValue(12345678.9012345);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(format.getFormat(badNumPatterns[i]));
cell.setCellStyle(cellStyle);
}
// Built in formats
{ // Zip + 4 format
row = sheet.createRow(4);
HSSFCell cell = row.createCell(0);
cell.setCellValue(123456789);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(format.getFormat("00000-0000"));
cell.setCellStyle(cellStyle);
}
{ // Phone number format
row = sheet.createRow(5);
HSSFCell cell = row.createCell(0);
cell.setCellValue(5551234567D);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(format.getFormat("[<=9999999]###-####;(###) ###-####"));
cell.setCellStyle(cellStyle);
}
{ // SSN format
row = sheet.createRow(6);
HSSFCell cell = row.createCell(0);
cell.setCellValue(444551234);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(format.getFormat("000-00-0000"));
cell.setCellStyle(cellStyle);
}
{ // formula cell
row = sheet.createRow(7);
HSSFCell cell = row.createCell(0);
cell.setCellType(CellType.FORMULA);
cell.setCellFormula("SUM(12.25,12.25)/100");
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(format.getFormat("##.00%;"));
cell.setCellStyle(cellStyle);
}
}
/**
* Test getting formatted values from numeric and date cells.
*/
@Test
public void testGetFormattedCellValueHSSFCell() {
// Valid date formats -- cell values should be date formatted & not "555.555"
HSSFRow row = wb.getSheetAt(0).getRow(0);
Iterator<Cell> it = row.cellIterator();
log("==== VALID DATE FORMATS ====");
while (it.hasNext()) {
Cell cell = it.next();
String fmtval = formatter.formatCellValue(cell);
log(fmtval);
// should not be equal to "555.555"
assertTrue( DateUtil.isCellDateFormatted(cell) );
assertTrue( ! "555.555".equals(fmtval));
String fmt = cell.getCellStyle().getDataFormatString();
//assert the correct month form, as in the original Excel format
String monthPtrn = fmt.contains("mmmm") ? "MMMM" : "MMM";
// this line is intended to compute how "July" would look like in the current locale
SimpleDateFormat sdf = new SimpleDateFormat(monthPtrn, LocaleUtil.getUserLocale());
sdf.setTimeZone(LocaleUtil.getUserTimeZone());
Calendar calDef = LocaleUtil.getLocaleCalendar(2010, 6, 15, 0, 0, 0);
String jul = sdf.format(calDef.getTime());
// special case for MMMMM = 1st letter of month name
if(fmt.contains("mmmmm")) {
jul = jul.substring(0,1);
}
// check we found july properly
assertTrue("Format came out incorrect - " + fmt, fmtval.contains(jul));
}
row = wb.getSheetAt(0).getRow(1);
it = row.cellIterator();
log("==== VALID TIME FORMATS ====");
while (it.hasNext()) {
Cell cell = it.next();
String fmt = cell.getCellStyle().getDataFormatString();
String fmtval = formatter.formatCellValue(cell);
log(fmtval);
// should not be equal to "555.47431"
assertTrue( DateUtil.isCellDateFormatted(cell) );
assertTrue( ! "555.47431".equals(fmtval));
// check we found the time properly
assertTrue("Format came out incorrect - " + fmt + " - found " + fmtval +
", but expected to find '11:23'", fmtval.contains("11:23"));
}
// test number formats
row = wb.getSheetAt(0).getRow(1);
it = row.cellIterator();
log("\n==== VALID NUMBER FORMATS ====");
while (it.hasNext()) {
HSSFCell cell = (HSSFCell) it.next();
final String formatted = formatter.formatCellValue(cell);
log(formatted);
// should not include "12345678" - note that the input value was negative
assertTrue(formatted != null && ! formatted.contains("12345678"));
}
// test bad number formats
row = wb.getSheetAt(0).getRow(3);
it = row.cellIterator();
log("\n==== INVALID NUMBER FORMATS ====");
while (it.hasNext()) {
HSSFCell cell = (HSSFCell) it.next();
log(formatter.formatCellValue(cell));
// in some locales the the decimal delimiter is a comma, not a dot
char decimalSeparator = DecimalFormatSymbols.getInstance(LocaleUtil.getUserLocale()).getDecimalSeparator();
assertEquals("12345678" + decimalSeparator + "9", formatter.formatCellValue(cell));
}
// test Zip+4 format
row = wb.getSheetAt(0).getRow(4);
HSSFCell cell = row.getCell(0);
log("\n==== ZIP FORMAT ====");
log(formatter.formatCellValue(cell));
assertEquals("12345-6789", formatter.formatCellValue(cell));
// test phone number format
row = wb.getSheetAt(0).getRow(5);
cell = row.getCell(0);
log("\n==== PHONE FORMAT ====");
log(formatter.formatCellValue(cell));
assertEquals("(555) 123-4567", formatter.formatCellValue(cell));
// test SSN format
row = wb.getSheetAt(0).getRow(6);
cell = row.getCell(0);
log("\n==== SSN FORMAT ====");
log(formatter.formatCellValue(cell));
assertEquals("444-55-1234", formatter.formatCellValue(cell));
// null test-- null cell should result in empty String
assertEquals(formatter.formatCellValue(null), "");
// null test-- null cell should result in empty String
assertEquals(formatter.formatCellValue(null), "");
}
@Test
public void testGetFormattedCellValueHSSFCellHSSFFormulaEvaluator() {
// test formula format
HSSFRow row = wb.getSheetAt(0).getRow(7);
HSSFCell cell = row.getCell(0);
log("\n==== FORMULA CELL ====");
// first without a formula evaluator
log(formatter.formatCellValue(cell) + "\t (without evaluator)");
assertEquals("SUM(12.25,12.25)/100", formatter.formatCellValue(cell));
// now with a formula evaluator
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
log(formatter.formatCellValue(cell, evaluator) + "\t\t\t (with evaluator)");
char decimalSeparator = DecimalFormatSymbols.getInstance(LocaleUtil.getUserLocale()).getDecimalSeparator();
assertEquals("24" + decimalSeparator + "50%", formatter.formatCellValue(cell,evaluator));
}
/**
* Test using a default number format. The format should be used when a
* format pattern cannot be parsed by DecimalFormat.
*/
@Test
public void testSetDefaultNumberFormat() {
HSSFRow row = wb.getSheetAt(0).getRow(3);
Iterator<Cell> it = row.cellIterator();
DecimalFormatSymbols dfs = DecimalFormatSymbols.getInstance(LocaleUtil.getUserLocale());
Format defaultFormat = new DecimalFormat("Balance $#,#00.00 USD;Balance -$#,#00.00 USD", dfs);
formatter.setDefaultNumberFormat(defaultFormat);
log("\n==== DEFAULT NUMBER FORMAT ====");
while (it.hasNext()) {
Cell cell = it.next();
cell.setCellValue(cell.getNumericCellValue() * Math.random() / 1000000 - 1000);
log(formatter.formatCellValue(cell));
String formatted = formatter.formatCellValue(cell);
assertTrue("Doesn't start with Balance: " + formatted, formatted.startsWith("Balance "));
assertTrue("Doesn't end with USD: " + formatted, formatted.endsWith(" USD"));
}
}
/**
* A format of "@" means use the general format
*/
@Test
public void testGeneralAtFormat() {
HSSFWorkbook workbook = HSSFTestDataSamples.openSampleWorkbook("47154.xls");
HSSFSheet sheet = workbook.getSheetAt(0);
HSSFRow row = sheet.getRow(0);
HSSFCell cellA1 = row.getCell(0);
assertEquals(CellType.NUMERIC, cellA1.getCellTypeEnum());
assertEquals(2345.0, cellA1.getNumericCellValue(), 0.0001);
assertEquals("@", cellA1.getCellStyle().getDataFormatString());
HSSFDataFormatter f = new HSSFDataFormatter();
assertEquals("2345", f.formatCellValue(cellA1));
}
/**
* Tests various formattings of dates and numbers
*/
@Test
public void testFromFile() {
HSSFWorkbook workbook = HSSFTestDataSamples.openSampleWorkbook("Formatting.xls");
HSSFSheet sheet = workbook.getSheetAt(0);
HSSFDataFormatter f = new HSSFDataFormatter();
// This one is one of the nasty auto-locale changing ones...
assertEquals("dd/mm/yyyy", sheet.getRow(1).getCell(0).getStringCellValue());
assertEquals("m/d/yy", sheet.getRow(1).getCell(1).getCellStyle().getDataFormatString());
assertEquals("11/24/06", f.formatCellValue(sheet.getRow(1).getCell(1)));
assertEquals("yyyy/mm/dd", sheet.getRow(2).getCell(0).getStringCellValue());
assertEquals("yyyy/mm/dd", sheet.getRow(2).getCell(1).getCellStyle().getDataFormatString());
assertEquals("2006/11/24", f.formatCellValue(sheet.getRow(2).getCell(1)));
assertEquals("yyyy-mm-dd", sheet.getRow(3).getCell(0).getStringCellValue());
assertEquals("yyyy\\-mm\\-dd", sheet.getRow(3).getCell(1).getCellStyle().getDataFormatString());
assertEquals("2006-11-24", f.formatCellValue(sheet.getRow(3).getCell(1)));
assertEquals("yy/mm/dd", sheet.getRow(4).getCell(0).getStringCellValue());
assertEquals("yy/mm/dd", sheet.getRow(4).getCell(1).getCellStyle().getDataFormatString());
assertEquals("06/11/24", f.formatCellValue(sheet.getRow(4).getCell(1)));
// Another builtin fun one
assertEquals("dd/mm/yy", sheet.getRow(5).getCell(0).getStringCellValue());
assertEquals("d/m/yy;@", sheet.getRow(5).getCell(1).getCellStyle().getDataFormatString());
assertEquals("24/11/06", f.formatCellValue(sheet.getRow(5).getCell(1)));
assertEquals("dd-mm-yy", sheet.getRow(6).getCell(0).getStringCellValue());
assertEquals("dd\\-mm\\-yy", sheet.getRow(6).getCell(1).getCellStyle().getDataFormatString());
assertEquals("24-11-06", f.formatCellValue(sheet.getRow(6).getCell(1)));
// Another builtin fun one
assertEquals("nn.nn", sheet.getRow(9).getCell(0).getStringCellValue());
assertEquals("General", sheet.getRow(9).getCell(1).getCellStyle().getDataFormatString());
assertEquals("10.52", f.formatCellValue(sheet.getRow(9).getCell(1)));
// text isn't quite the format rule...
assertEquals("nn.nnn", sheet.getRow(10).getCell(0).getStringCellValue());
assertEquals("0.000", sheet.getRow(10).getCell(1).getCellStyle().getDataFormatString());
assertEquals("10.520", f.formatCellValue(sheet.getRow(10).getCell(1)));
// text isn't quite the format rule...
assertEquals("nn.n", sheet.getRow(11).getCell(0).getStringCellValue());
assertEquals("0.0", sheet.getRow(11).getCell(1).getCellStyle().getDataFormatString());
assertEquals("10.5", f.formatCellValue(sheet.getRow(11).getCell(1)));
// text isn't quite the format rule...
assertEquals("\u00a3nn.nn", sheet.getRow(12).getCell(0).getStringCellValue());
assertEquals("\"\u00a3\"#,##0.00", sheet.getRow(12).getCell(1).getCellStyle().getDataFormatString());
assertEquals("\u00a310.52", f.formatCellValue(sheet.getRow(12).getCell(1)));
}
private static void log(@SuppressWarnings("UnusedParameters") String msg) {
// if (false) { // successful tests should be silent
// System.out.println(msg);
// }
}
}