/* ====================================================================
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.format;
import static org.junit.Assert.*;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Locale;
import java.util.TimeZone;
import javax.swing.JLabel;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
public class TestCellFormat {
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 static final String _255_POUND_SIGNS;
static {
StringBuilder sb = new StringBuilder();
for (int i = 1; i <= 255; i++) {
sb.append('#');
}
_255_POUND_SIGNS = sb.toString();
}
@Test
public void testSome() {
JLabel l = new JLabel();
CellFormat fmt = CellFormat.getInstance(
"\"$\"#,##0.00_);[Red]\\(\"$\"#,##0.00\\)");
fmt.apply(l, 1.1);
}
@Test
public void testPositiveFormatHasOnePart() {
CellFormat fmt = CellFormat.getInstance("0.00");
CellFormatResult result = fmt.apply(12.345);
assertEquals("12.35", result.text);
}
@Test
public void testNegativeFormatHasOnePart() {
CellFormat fmt = CellFormat.getInstance("0.00");
CellFormatResult result = fmt.apply(-12.345);
assertEquals("-12.35", result.text);
}
@Test
public void testZeroFormatHasOnePart() {
CellFormat fmt = CellFormat.getInstance("0.00");
CellFormatResult result = fmt.apply(0.0);
assertEquals("0.00", result.text);
}
@Test
public void testPositiveFormatHasPosAndNegParts() {
CellFormat fmt = CellFormat.getInstance("0.00;-0.00");
CellFormatResult result = fmt.apply(12.345);
assertEquals("12.35", result.text);
}
@Test
public void testNegativeFormatHasPosAndNegParts() {
CellFormat fmt = CellFormat.getInstance("0.00;-0.00");
CellFormatResult result = fmt.apply(-12.345);
assertEquals("-12.35", result.text);
}
@Test
public void testNegativeFormatHasPosAndNegParts2() {
CellFormat fmt = CellFormat.getInstance("0.00;(0.00)");
CellFormatResult result = fmt.apply(-12.345);
assertEquals("(12.35)", result.text);
}
@Test
public void testZeroFormatHasPosAndNegParts() {
CellFormat fmt = CellFormat.getInstance("0.00;-0.00");
CellFormatResult result = fmt.apply(0.0);
assertEquals("0.00", result.text);
}
@Test
public void testFormatWithThreeSections() {
CellFormat fmt = CellFormat.getInstance("0.00;-0.00;-");
assertEquals("12.35", fmt.apply(12.345).text);
assertEquals("-12.35", fmt.apply(-12.345).text);
assertEquals("-", fmt.apply(0.0).text);
assertEquals("abc", fmt.apply("abc").text);
}
@Test
public void testFormatWithFourSections() {
CellFormat fmt = CellFormat.getInstance("0.00;-0.00;-; @ ");
assertEquals("12.35", fmt.apply(12.345).text);
assertEquals("-12.35", fmt.apply(-12.345).text);
assertEquals("-", fmt.apply(0.0).text);
assertEquals(" abc ", fmt.apply("abc").text);
}
@Test
public void testApplyCellForGeneralFormat() throws Exception {
// Create a workbook, row and cell to test with
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell0 = row.createCell(0);
Cell cell1 = row.createCell(1);
Cell cell2 = row.createCell(2);
Cell cell3 = row.createCell(3);
Cell cell4 = row.createCell(4);
CellFormat cf = CellFormat.getInstance("General");
// case CellType.BLANK
CellFormatResult result0 = cf.apply(cell0);
assertEquals("", result0.text);
// case CellType.BOOLEAN
cell1.setCellValue(true);
CellFormatResult result1 = cf.apply(cell1);
assertEquals("TRUE", result1.text);
// case CellType.NUMERIC
cell2.setCellValue(1.23);
CellFormatResult result2 = cf.apply(cell2);
assertEquals("1.23", result2.text);
cell3.setCellValue(123.0);
CellFormatResult result3 = cf.apply(cell3);
assertEquals("123", result3.text);
// case CellType.STRING
cell4.setCellValue("abc");
CellFormatResult result4 = cf.apply(cell4);
assertEquals("abc", result4.text);
wb.close();
}
@Test
public void testApplyCellForAtFormat() throws Exception {
// Create a workbook, row and cell to test with
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell0 = row.createCell(0);
Cell cell1 = row.createCell(1);
Cell cell2 = row.createCell(2);
Cell cell3 = row.createCell(3);
Cell cell4 = row.createCell(4);
CellFormat cf = CellFormat.getInstance("@");
// case CellType.BLANK
CellFormatResult result0 = cf.apply(cell0);
assertEquals("", result0.text);
// case CellType.BOOLEAN
cell1.setCellValue(true);
CellFormatResult result1 = cf.apply(cell1);
assertEquals("TRUE", result1.text);
// case CellType.NUMERIC
cell2.setCellValue(1.23);
CellFormatResult result2 = cf.apply(cell2);
assertEquals("1.23", result2.text);
cell3.setCellValue(123.0);
CellFormatResult result3 = cf.apply(cell3);
assertEquals("123", result3.text);
// case CellType.STRING
cell4.setCellValue("abc");
CellFormatResult result4 = cf.apply(cell4);
assertEquals("abc", result4.text);
wb.close();
}
@Test
public void testApplyCellForDateFormat() throws Exception {
// Create a workbook, row and cell to test with
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell0 = row.createCell(0);
Cell cell1 = row.createCell(1);
CellFormat cf = CellFormat.getInstance("dd/mm/yyyy");
cell0.setCellValue(10);
CellFormatResult result0 = cf.apply(cell0);
assertEquals("10/01/1900", result0.text);
cell1.setCellValue(-1);
CellFormatResult result1 = cf.apply(cell1);
assertEquals(_255_POUND_SIGNS, result1.text);
wb.close();
}
@Test
public void testApplyCellForTimeFormat() throws Exception {
// Create a workbook, row and cell to test with
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
CellFormat cf = CellFormat.getInstance("hh:mm");
cell.setCellValue(DateUtil.convertTime("03:04:05"));
CellFormatResult result = cf.apply(cell);
assertEquals("03:04", result.text);
wb.close();
}
@Test
public void testApplyCellForDateFormatAndNegativeFormat() throws Exception {
// Create a workbook, row and cell to test with
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell0 = row.createCell(0);
Cell cell1 = row.createCell(1);
CellFormat cf = CellFormat.getInstance("dd/mm/yyyy;(0)");
cell0.setCellValue(10);
CellFormatResult result0 = cf.apply(cell0);
assertEquals("10/01/1900", result0.text);
cell1.setCellValue(-1);
CellFormatResult result1 = cf.apply(cell1);
assertEquals("(1)", result1.text);
wb.close();
}
@Test
public void testApplyJLabelCellForGeneralFormat() throws Exception {
// Create a workbook, row and cell to test with
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell0 = row.createCell(0);
Cell cell1 = row.createCell(1);
Cell cell2 = row.createCell(2);
Cell cell3 = row.createCell(3);
Cell cell4 = row.createCell(4);
CellFormat cf = CellFormat.getInstance("General");
JLabel label0 = new JLabel();
JLabel label1 = new JLabel();
JLabel label2 = new JLabel();
JLabel label3 = new JLabel();
JLabel label4 = new JLabel();
// case CellType.BLANK
CellFormatResult result0 = cf.apply(label0, cell0);
assertEquals("", result0.text);
assertEquals("", label0.getText());
// case CellType.BOOLEAN
cell1.setCellValue(true);
CellFormatResult result1 = cf.apply(label1, cell1);
assertEquals("TRUE", result1.text);
assertEquals("TRUE", label1.getText());
// case CellType.NUMERIC
cell2.setCellValue(1.23);
CellFormatResult result2 = cf.apply(label2, cell2);
assertEquals("1.23", result2.text);
assertEquals("1.23", label2.getText());
cell3.setCellValue(123.0);
CellFormatResult result3 = cf.apply(label3, cell3);
assertEquals("123", result3.text);
assertEquals("123", label3.getText());
// case CellType.STRING
cell4.setCellValue("abc");
CellFormatResult result4 = cf.apply(label4, cell4);
assertEquals("abc", result4.text);
assertEquals("abc", label4.getText());
wb.close();
}
@Test
public void testApplyJLabelCellForAtFormat() throws Exception {
// Create a workbook, row and cell to test with
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell0 = row.createCell(0);
Cell cell1 = row.createCell(1);
Cell cell2 = row.createCell(2);
Cell cell3 = row.createCell(3);
Cell cell4 = row.createCell(4);
CellFormat cf = CellFormat.getInstance("@");
JLabel label0 = new JLabel();
JLabel label1 = new JLabel();
JLabel label2 = new JLabel();
JLabel label3 = new JLabel();
JLabel label4 = new JLabel();
// case CellType.BLANK
CellFormatResult result0 = cf.apply(label0, cell0);
assertEquals("", result0.text);
assertEquals("", label0.getText());
// case CellType.BOOLEAN
cell1.setCellValue(true);
CellFormatResult result1 = cf.apply(label1, cell1);
assertEquals("TRUE", result1.text);
assertEquals("TRUE", label1.getText());
// case CellType.NUMERIC
cell2.setCellValue(1.23);
CellFormatResult result2 = cf.apply(label2, cell2);
assertEquals("1.23", result2.text);
assertEquals("1.23", label2.getText());
cell3.setCellValue(123.0);
CellFormatResult result3 = cf.apply(label3, cell3);
assertEquals("123", result3.text);
assertEquals("123", label3.getText());
// case CellType.STRING
cell4.setCellValue("abc");
CellFormatResult result4 = cf.apply(label4, cell4);
assertEquals("abc", result4.text);
assertEquals("abc", label4.getText());
wb.close();
}
@Test
public void testApplyJLabelCellForDateFormat() throws Exception {
// Create a workbook, row and cell to test with
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell0 = row.createCell(0);
Cell cell1 = row.createCell(1);
CellFormat cf = CellFormat.getInstance("dd/mm/yyyy");
JLabel label0 = new JLabel();
JLabel label1 = new JLabel();
cell0.setCellValue(10);
CellFormatResult result0 = cf.apply(label0, cell0);
assertEquals("10/01/1900", result0.text);
assertEquals("10/01/1900", label0.getText());
cell1.setCellValue(-1);
CellFormatResult result1 = cf.apply(label1, cell1);
assertEquals(_255_POUND_SIGNS, result1.text);
assertEquals(_255_POUND_SIGNS, label1.getText());
wb.close();
}
@Test
public void testApplyJLabelCellForTimeFormat() throws Exception {
// Create a workbook, row and cell to test with
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
CellFormat cf = CellFormat.getInstance("hh:mm");
JLabel label = new JLabel();
cell.setCellValue(DateUtil.convertTime("03:04:05"));
CellFormatResult result = cf.apply(label, cell);
assertEquals("03:04", result.text);
assertEquals("03:04", label.getText());
wb.close();
}
@Test
public void testApplyJLabelCellForDateFormatAndNegativeFormat() throws Exception {
// Create a workbook, row and cell to test with
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell0 = row.createCell(0);
Cell cell1 = row.createCell(1);
CellFormat cf = CellFormat.getInstance("dd/mm/yyyy;(0)");
JLabel label0 = new JLabel();
JLabel label1 = new JLabel();
cell0.setCellValue(10);
CellFormatResult result0 = cf.apply(label0, cell0);
assertEquals("10/01/1900", result0.text);
assertEquals("10/01/1900", label0.getText());
cell1.setCellValue(-1);
CellFormatResult result1 = cf.apply(label1, cell1);
assertEquals("(1)", result1.text);
assertEquals("(1)", label1.getText());
wb.close();
}
@Test
public void testApplyFormatHasOnePartAndPartHasCondition() throws Exception {
// Create a workbook, row and cell to test with
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
CellFormat cf = CellFormat.getInstance("[>=100]0.00");
cell.setCellValue(100);
assertEquals("100.00", cf.apply(cell).text);
cell.setCellValue(10);
assertEquals("10", cf.apply(cell).text);
cell.setCellValue(0.123456789012345);
assertEquals("0.123456789", cf.apply(cell).text);
cell.setCellValue(0);
assertEquals("0", cf.apply(cell).text);
cell.setCellValue("abc");
assertEquals("abc", cf.apply(cell).text);
wb.close();
}
@Test
public void testApplyFormatHasTwoPartsFirstHasCondition() throws Exception {
// Create a workbook, row and cell to test with
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
CellFormat cf = CellFormat.getInstance("[>=100]0.00;0.000");
cell.setCellValue(100);
assertEquals("100.00", cf.apply(cell).text);
cell.setCellValue(10);
assertEquals("10.000", cf.apply(cell).text);
cell.setCellValue(0.123456789012345);
assertEquals("0.123", cf.apply(cell).text);
cell.setCellValue(0);
assertEquals("0.000", cf.apply(cell).text);
cell.setCellValue(-10);
assertEquals("-10.000", cf.apply(cell).text);
cell.setCellValue("abc");
assertEquals("abc", cf.apply(cell).text);
cell.setCellValue("TRUE");
assertEquals("TRUE", cf.apply(cell).text);
wb.close();
}
@Test
public void testApplyFormatHasTwoPartsBothHaveCondition() throws Exception {
// Create a workbook, row and cell to test with
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
CellFormat cf = CellFormat.getInstance("[>=100]0.00;[>=10]0.000");
cell.setCellValue(100);
assertEquals("100.00", cf.apply(cell).text);
cell.setCellValue(10);
assertEquals("10.000", cf.apply(cell).text);
cell.setCellValue(0);
assertEquals(_255_POUND_SIGNS, cf.apply(cell).text);
cell.setCellValue(-0.123456789012345);
assertEquals(_255_POUND_SIGNS, cf.apply(cell).text);
cell.setCellValue(-10);
assertEquals(_255_POUND_SIGNS, cf.apply(cell).text);
cell.setCellValue("abc");
assertEquals("abc", cf.apply(cell).text);
wb.close();
}
@Test
public void testApplyFormatHasThreePartsFirstHasCondition() throws Exception {
// Create a workbook, row and cell to test with
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
CellFormat cf = CellFormat.getInstance("[>=100]0.00;0.000;0.0000");
cell.setCellValue(100);
assertEquals("100.00", cf.apply(cell).text);
cell.setCellValue(10);
assertEquals("10.0000", cf.apply(cell).text);
cell.setCellValue(0.123456789012345);
assertEquals("0.1235", cf.apply(cell).text);
cell.setCellValue(0);
assertEquals("0.0000", cf.apply(cell).text);
// Second format part ('0.000') is used for negative numbers
// so result does not have a minus sign
cell.setCellValue(-10);
assertEquals("10.000", cf.apply(cell).text);
cell.setCellValue("abc");
assertEquals("abc", cf.apply(cell).text);
wb.close();
}
@Test
public void testApplyFormatHasThreePartsFirstTwoHaveCondition() throws Exception {
// Create a workbook, row and cell to test with
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
CellFormat cf = CellFormat.getInstance("[>=100]0.00;[>=10]0.000;0.0000");
cell.setCellValue(100);
assertEquals("100.00", cf.apply(cell).text);
cell.setCellValue(10);
assertEquals("10.000", cf.apply(cell).text);
cell.setCellValue(0);
assertEquals("0.0000", cf.apply(cell).text);
cell.setCellValue(-10);
assertEquals("-10.0000", cf.apply(cell).text);
cell.setCellValue("abc");
assertEquals("abc", cf.apply(cell).text);
wb.close();
}
@Test
public void testApplyFormatHasThreePartsFirstIsDateFirstTwoHaveCondition() throws Exception {
// Create a workbook, row and cell to test with
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
CellFormat cf = CellFormat.getInstance("[>=100]0.00;[>=10]dd/mm/yyyy;0.0");
cell.setCellValue(100);
assertEquals("100.00", cf.apply(cell).text);
cell.setCellValue(10);
assertEquals("10/01/1900", cf.apply(cell).text);
cell.setCellValue(0);
assertEquals("0.0", cf.apply(cell).text);
cell.setCellValue(-10);
assertEquals("-10.0", cf.apply(cell).text);
cell.setCellValue("abc");
assertEquals("abc", cf.apply(cell).text);
wb.close();
}
@Test
public void testApplyFormatHasTwoPartsFirstHasConditionSecondIsGeneral() throws Exception {
// Create a workbook, row and cell to test with
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
CellFormat cf = CellFormat.getInstance("[>=100]0.00;General");
cell.setCellValue(100);
assertEquals("100.00", cf.apply(cell).text);
cell.setCellValue(10);
assertEquals("10", cf.apply(cell).text);
cell.setCellValue(0);
assertEquals("0", cf.apply(cell).text);
cell.setCellValue(-10);
assertEquals("-10", cf.apply(cell).text);
cell.setCellValue("abc");
assertEquals("abc", cf.apply(cell).text);
wb.close();
}
@Test
public void testApplyFormatHasThreePartsFirstTwoHaveConditionThirdIsGeneral() throws Exception {
// Create a workbook, row and cell to test with
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
CellFormat cf = CellFormat.getInstance("[>=100]0.00;[>=10]0.000;General");
cell.setCellValue(100);
assertEquals("100.00", cf.apply(cell).text);
cell.setCellValue(10);
assertEquals("10.000", cf.apply(cell).text);
cell.setCellValue(0);
assertEquals("0", cf.apply(cell).text);
cell.setCellValue(-10);
assertEquals("-10", cf.apply(cell).text);
cell.setCellValue("abc");
assertEquals("abc", cf.apply(cell).text);
wb.close();
}
@Test
public void testApplyFormatHasFourPartsFirstHasCondition() throws Exception {
// Create a workbook, row and cell to test with
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
CellFormat cf = CellFormat.getInstance("[>=100]0.00;0.000;0.0000;~~@~~");
cell.setCellValue(100);
assertEquals("100.00", cf.apply(cell).text);
cell.setCellValue(10);
assertEquals("10.0000", cf.apply(cell).text);
cell.setCellValue(0.123456789012345);
assertEquals("0.1235", cf.apply(cell).text);
cell.setCellValue(0);
assertEquals("0.0000", cf.apply(cell).text);
// Second format part ('0.000') is used for negative numbers
// so result does not have a minus sign
cell.setCellValue(-10);
assertEquals("10.000", cf.apply(cell).text);
cell.setCellValue("abc");
assertEquals("~~abc~~", cf.apply(cell).text);
wb.close();
}
@Test
public void testApplyFormatHasFourPartsSecondHasCondition() throws Exception {
// Create a workbook, row and cell to test with
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
CellFormat cf = CellFormat.getInstance("0.00;[>=100]0.000;0.0000;~~@~~");
cell.setCellValue(100);
assertEquals("100.00", cf.apply(cell).text);
cell.setCellValue(10);
assertEquals("10.00", cf.apply(cell).text);
cell.setCellValue(0.123456789012345);
assertEquals("0.12", cf.apply(cell).text);
cell.setCellValue(0);
assertEquals("0.0000", cf.apply(cell).text);
cell.setCellValue(-10);
assertEquals("-10.0000", cf.apply(cell).text);
cell.setCellValue("abc");
assertEquals("~~abc~~", cf.apply(cell).text);
cell.setCellValue(true);
assertEquals("~~TRUE~~", cf.apply(cell).text);
wb.close();
}
@Test
public void testApplyFormatHasFourPartsFirstTwoHaveCondition() throws Exception {
// Create a workbook, row and cell to test with
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
CellFormat cf = CellFormat.getInstance("[>=100]0.00;[>=10]0.000;0.0000;~~@~~");
cell.setCellValue(100);
assertEquals("100.00", cf.apply(cell).text);
cell.setCellValue(10);
assertEquals("10.000", cf.apply(cell).text);
cell.setCellValue(0);
assertEquals("0.0000", cf.apply(cell).text);
cell.setCellValue(-10);
assertEquals("-10.0000", cf.apply(cell).text);
cell.setCellValue("abc");
assertEquals("~~abc~~", cf.apply(cell).text);
cell.setCellValue(true);
assertEquals("~~TRUE~~", cf.apply(cell).text);
wb.close();
}
/*
* Test apply(Object value) with a number as parameter
*/
@Test
public void testApplyObjectNumber() {
CellFormat cf1 = CellFormat.getInstance("0.000");
assertEquals("1.235", cf1.apply(1.2345).text);
assertEquals("-1.235", cf1.apply(-1.2345).text);
CellFormat cf2 = CellFormat.getInstance("0.000;(0.000)");
assertEquals("1.235", cf2.apply(1.2345).text);
assertEquals("(1.235)", cf2.apply(-1.2345).text);
CellFormat cf3 = CellFormat.getInstance("[>1]0.000;0.0000");
assertEquals("1.235", cf3.apply(1.2345).text);
assertEquals("-1.2345", cf3.apply(-1.2345).text);
CellFormat cf4 = CellFormat.getInstance("0.000;[>1]0.0000");
assertEquals("1.235", cf4.apply(1.2345).text);
assertEquals(_255_POUND_SIGNS, cf4.apply(-1.2345).text);
}
/*
* Test apply(Object value) with a Date as parameter
*/
@Test
public void testApplyObjectDate() throws ParseException {
CellFormat cf1 = CellFormat.getInstance("m/d/yyyy");
Date date1 = new SimpleDateFormat("M/d/y", Locale.ROOT).parse("01/11/2012");
assertEquals("1/11/2012", cf1.apply(date1).text);
}
@Test
public void testApplyCellForDateFormatWithConditions() throws Exception {
// Create a workbook, row and cell to test with
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
CellFormat cf = CellFormat.getInstance("[<1]hh:mm:ss AM/PM;[>=1]dd/mm/yyyy hh:mm:ss AM/PM;General");
cell.setCellValue(0.5);
assertEquals("12:00:00 PM", cf.apply(cell).text);
cell.setCellValue(1.5);
assertEquals("01/01/1900 12:00:00 PM", cf.apply(cell).text);
cell.setCellValue(-1);
assertEquals(_255_POUND_SIGNS, cf.apply(cell).text);
wb.close();
}
/*
* Test apply(Object value) with a String as parameter
*/
@Test
public void testApplyObjectString() {
CellFormat cf = CellFormat.getInstance("0.00");
assertEquals("abc", cf.apply("abc").text);
}
/*
* Test apply(Object value) with a Boolean as parameter
*/
@Test
public void testApplyObjectBoolean() {
CellFormat cf1 = CellFormat.getInstance("0");
CellFormat cf2 = CellFormat.getInstance("General");
CellFormat cf3 = CellFormat.getInstance("@");
assertEquals("TRUE", cf1.apply(true).text);
assertEquals("FALSE", cf2.apply(false).text);
assertEquals("TRUE", cf3.apply(true).text);
}
@Test
public void testSimpleFractionFormat() throws IOException {
CellFormat cf1 = CellFormat.getInstance("# ?/?");
// Create a workbook, row and cell to test with
Workbook wb = new HSSFWorkbook();
try {
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue(123456.6);
//System.out.println(cf1.apply(cell).text);
assertEquals("123456 3/5", cf1.apply(cell).text);
} finally {
wb.close();
}
}
@Test
public void testAccountingFormats() throws IOException {
char pound = '\u00A3';
char euro = '\u20AC';
// Accounting -> 0 decimal places, default currency symbol
String formatDft = "_-\"$\"* #,##0_-;\\-\"$\"* #,##0_-;_-\"$\"* \"-\"_-;_-@_-";
// Accounting -> 0 decimal places, US currency symbol
String formatUS = "_-[$$-409]* #,##0_ ;_-[$$-409]* -#,##0 ;_-[$$-409]* \"-\"_-;_-@_-";
// Accounting -> 0 decimal places, UK currency symbol
String formatUK = "_-[$"+pound+"-809]* #,##0_-;\\-[$"+pound+"-809]* #,##0_-;_-[$"+pound+"-809]* \"-\"??_-;_-@_-";
// French style accounting, euro sign comes after not before
String formatFR = "_-#,##0* [$"+euro+"-40C]_-;\\-#,##0* [$"+euro+"-40C]_-;_-\"-\"??* [$"+euro+"-40C] _-;_-@_-";
// Has +ve, -ve and zero rules
CellFormat cfDft = CellFormat.getInstance(formatDft);
CellFormat cfUS = CellFormat.getInstance(formatUS);
CellFormat cfUK = CellFormat.getInstance(formatUK);
CellFormat cfFR = CellFormat.getInstance(formatFR);
// For +ve numbers, should be Space + currency symbol + spaces + whole number with commas + space
// (Except French, which is mostly reversed...)
assertEquals(" $ 12 ", cfDft.apply(Double.valueOf(12.33)).text);
assertEquals(" $ 12 ", cfUS.apply(Double.valueOf(12.33)).text);
assertEquals(" "+pound+" 12 ", cfUK.apply(Double.valueOf(12.33)).text);
assertEquals(" 12 "+euro+" ", cfFR.apply(Double.valueOf(12.33)).text);
assertEquals(" $ 16,789 ", cfDft.apply(Double.valueOf(16789.2)).text);
assertEquals(" $ 16,789 ", cfUS.apply(Double.valueOf(16789.2)).text);
assertEquals(" "+pound+" 16,789 ", cfUK.apply(Double.valueOf(16789.2)).text);
assertEquals(" 16,789 "+euro+" ", cfFR.apply(Double.valueOf(16789.2)).text);
// For -ve numbers, gets a bit more complicated...
assertEquals("-$ 12 ", cfDft.apply(Double.valueOf(-12.33)).text);
assertEquals(" $ -12 ", cfUS.apply(Double.valueOf(-12.33)).text);
assertEquals("-"+pound+" 12 ", cfUK.apply(Double.valueOf(-12.33)).text);
assertEquals("-12 "+euro+" ", cfFR.apply(Double.valueOf(-12.33)).text);
assertEquals("-$ 16,789 ", cfDft.apply(Double.valueOf(-16789.2)).text);
assertEquals(" $ -16,789 ", cfUS.apply(Double.valueOf(-16789.2)).text);
assertEquals("-"+pound+" 16,789 ", cfUK.apply(Double.valueOf(-16789.2)).text);
assertEquals("-16,789 "+euro+" ", cfFR.apply(Double.valueOf(-16789.2)).text);
// For zero, should be Space + currency symbol + spaces + Minus + spaces
assertEquals(" $ - ", cfDft.apply(Double.valueOf(0)).text);
// TODO Fix the exception this incorrectly triggers
//assertEquals(" $ - ", cfUS.apply(Double.valueOf(0)).text);
// TODO Fix these to not have an incorrect bonus 0 on the end
//assertEquals(" "+pound+" - ", cfUK.apply(Double.valueOf(0)).text);
//assertEquals(" - "+euro+" ", cfFR.apply(Double.valueOf(0)).text);
}
@Test
public void testThreePartComplexFormat1() {
// verify a rather complex format found e.g. in http://wahl.land-oberoesterreich.gv.at/Downloads/bp10.xls
CellFormatPart posPart = new CellFormatPart("[$-F400]h:mm:ss\\ AM/PM");
assertNotNull(posPart);
assertEquals("1:00:12 AM", posPart.apply(new Date(12345)).text);
CellFormatPart negPart = new CellFormatPart("[$-F40]h:mm:ss\\ AM/PM");
assertNotNull(negPart);
assertEquals("1:00:12 AM", posPart.apply(new Date(12345)).text);
//assertNotNull(new CellFormatPart("_-* \"\"??_-;_-@_-"));
CellFormat instance = CellFormat.getInstance("[$-F400]h:mm:ss\\ AM/PM;[$-F40]h:mm:ss\\ AM/PM;_-* \"\"??_-;_-@_-");
assertNotNull(instance);
assertEquals("1:00:12 AM", instance.apply(new Date(12345)).text);
}
@Test
public void testThreePartComplexFormat2() {
// verify a rather complex format found e.g. in http://wahl.land-oberoesterreich.gv.at/Downloads/bp10.xls
CellFormatPart posPart = new CellFormatPart("dd/mm/yyyy");
assertNotNull(posPart);
assertEquals("01/01/1970", posPart.apply(new Date(12345)).text);
CellFormatPart negPart = new CellFormatPart("dd/mm/yyyy");
assertNotNull(negPart);
assertEquals("01/01/1970", posPart.apply(new Date(12345)).text);
//assertNotNull(new CellFormatPart("_-* \"\"??_-;_-@_-"));
CellFormat instance = CellFormat.getInstance("dd/mm/yyyy;dd/mm/yyyy;_-* \"\"??_-;_-@_-");
assertNotNull(instance);
assertEquals("01/01/1970", instance.apply(new Date(12345)).text);
}
}