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