| /* ==================================================================== |
| 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.apache.poi.POITestCase.assertContains; |
| import static org.apache.poi.hssf.HSSFTestDataSamples.openSampleWorkbook; |
| import static org.apache.poi.hssf.HSSFTestDataSamples.writeOutAndReadBack; |
| import static org.junit.jupiter.api.Assertions.*; |
| |
| import java.awt.image.BufferedImage; |
| import java.io.ByteArrayInputStream; |
| import java.io.File; |
| import java.io.IOException; |
| import java.net.URL; |
| import java.util.ArrayList; |
| import java.util.Arrays; |
| import java.util.Calendar; |
| import java.util.HashSet; |
| import java.util.Iterator; |
| import java.util.LinkedHashMap; |
| import java.util.List; |
| import java.util.Locale; |
| import java.util.Map; |
| import java.util.Objects; |
| import java.util.Set; |
| import java.util.TimeZone; |
| import java.util.function.Consumer; |
| import java.util.stream.Collectors; |
| import java.util.stream.IntStream; |
| |
| import javax.imageio.ImageIO; |
| |
| import org.apache.commons.io.output.UnsynchronizedByteArrayOutputStream; |
| import org.apache.poi.hpsf.PropertySet; |
| import org.apache.poi.hpsf.SummaryInformation; |
| import org.apache.poi.hssf.HSSFITestDataProvider; |
| import org.apache.poi.hssf.HSSFTestDataSamples; |
| import org.apache.poi.hssf.extractor.ExcelExtractor; |
| import org.apache.poi.hssf.model.InternalSheet; |
| import org.apache.poi.hssf.model.InternalWorkbook; |
| import org.apache.poi.hssf.record.CellValueRecordInterface; |
| import org.apache.poi.hssf.record.CommonObjectDataSubRecord; |
| import org.apache.poi.hssf.record.EmbeddedObjectRefSubRecord; |
| import org.apache.poi.hssf.record.NameRecord; |
| import org.apache.poi.hssf.record.TabIdRecord; |
| import org.apache.poi.hssf.record.UnknownRecord; |
| import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate; |
| import org.apache.poi.hssf.record.aggregates.PageSettingsBlock; |
| import org.apache.poi.hssf.record.common.UnicodeString; |
| import org.apache.poi.hssf.record.crypto.Biff8EncryptionKey; |
| import org.apache.poi.poifs.filesystem.DocumentEntry; |
| import org.apache.poi.poifs.filesystem.DocumentInputStream; |
| import org.apache.poi.poifs.filesystem.POIFSFileSystem; |
| import org.apache.poi.ss.formula.ptg.Area3DPtg; |
| import org.apache.poi.ss.formula.ptg.DeletedArea3DPtg; |
| import org.apache.poi.ss.formula.ptg.Ptg; |
| import org.apache.poi.ss.usermodel.BaseTestBugzillaIssues; |
| import org.apache.poi.ss.usermodel.BorderStyle; |
| import org.apache.poi.ss.usermodel.Cell; |
| import org.apache.poi.ss.usermodel.CellStyle; |
| import org.apache.poi.ss.usermodel.CellType; |
| import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType; |
| import org.apache.poi.ss.usermodel.ComparisonOperator; |
| import org.apache.poi.ss.usermodel.DataFormatter; |
| import org.apache.poi.ss.usermodel.FormulaError; |
| import org.apache.poi.ss.usermodel.FormulaEvaluator; |
| import org.apache.poi.ss.usermodel.Name; |
| import org.apache.poi.ss.usermodel.Row; |
| import org.apache.poi.ss.usermodel.Sheet; |
| import org.apache.poi.ss.usermodel.SheetVisibility; |
| import org.apache.poi.ss.usermodel.Workbook; |
| import org.apache.poi.ss.util.CellRangeAddress; |
| import org.apache.poi.util.LocaleUtil; |
| import org.junit.jupiter.api.AfterAll; |
| import org.junit.jupiter.api.Assumptions; |
| import org.junit.jupiter.api.BeforeAll; |
| import org.junit.jupiter.api.Disabled; |
| import org.junit.jupiter.api.Test; |
| import org.junit.jupiter.params.ParameterizedTest; |
| import org.junit.jupiter.params.provider.CsvSource; |
| |
| /** |
| * Testcases for bugs entered in bugzilla |
| * the Test name contains the bugzilla bug id |
| * <p> |
| * <b>YK: If a bug can be tested in terms of common ss interfaces, |
| * define the test in the base class {@link BaseTestBugzillaIssues}</b> |
| */ |
| final class TestBugs extends BaseTestBugzillaIssues { |
| |
| public TestBugs() { |
| super(HSSFITestDataProvider.instance); |
| } |
| |
| private static final Map<String,HSSFFormulaEvaluator> SIMPLE_REFS = new LinkedHashMap<>(); |
| |
| // References used for the simpleTest convenience method |
| @BeforeAll |
| public static void initSimpleRefs() { |
| String[] refs = { |
| "Calculations", |
| "/Documents and Settings/crawformk.EUU/Local Settings/Temporary Internet Files/OLK64/Daily Status Report Generation Files/DST - Daily Data Transfer Sheet - 2002.xls", |
| "Sheet1", |
| "/Documents and Settings/donnag/Local Settings/Temporary Internet Files/OLK7/0231324V1-1.xls", |
| "Sheet1", |
| "refs/airport.xls", |
| "Sheet1", |
| "9http://www.principlesofeconometrics.com/excel/airline.xls", |
| "Sheet1", |
| "C:Documents and Settings/Yegor/My Documents/csco.xls", |
| }; |
| |
| for (int i=0; i<refs.length; i+=2) { |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| wb.createSheet(refs[i]); |
| String fileName = refs[i+1]; |
| if (!fileName.contains("://")) { |
| fileName = fileName.replace("/", File.separator); |
| } |
| SIMPLE_REFS.put(fileName, wb.getCreationHelper().createFormulaEvaluator()); |
| } |
| |
| LocaleUtil.setUserLocale(Locale.US); |
| } |
| |
| @AfterAll |
| public static void cleanUpRefs() { |
| SIMPLE_REFS.clear(); |
| } |
| |
| |
| private static void setCellText(HSSFCell cell, String text) { |
| cell.setCellValue(new HSSFRichTextString(text)); |
| } |
| |
| /** |
| * test rewriting a file with large number of unique strings |
| * open resulting file in Excel to check results! |
| */ |
| @Test |
| void bug15375() throws Exception { |
| try (HSSFWorkbook wb1 = openSampleWorkbook("15375.xls")) { |
| HSSFSheet sheet = wb1.getSheetAt(0); |
| |
| HSSFRow row = sheet.getRow(5); |
| HSSFCell cell = row.getCell(3); |
| if (cell == null) { |
| cell = row.createCell(3); |
| } |
| |
| // Write test |
| setCellText(cell, "a test"); |
| |
| // change existing numeric cell value |
| |
| HSSFRow oRow = sheet.getRow(14); |
| oRow.getCell(4).setCellValue(75); |
| setCellText(oRow.getCell(5), "0.3"); |
| |
| try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { |
| HSSFRow oRow2 = wb2.getSheetAt(0).getRow(14); |
| assertEquals(75, oRow2.getCell(4).getNumericCellValue()); |
| assertEquals("0.3", oRow2.getCell(5).getStringCellValue()); |
| } |
| } |
| } |
| |
| /** |
| * Double byte strings |
| */ |
| @Test |
| void bug15556() throws Exception { |
| simpleTest("15556.xls", wb -> assertNotNull(wb.getSheetAt(0).getRow(45))); |
| } |
| |
| @Test |
| void bug24215() throws Exception { |
| try (HSSFWorkbook wb = openSampleWorkbook("24215.xls")) { |
| |
| for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) { |
| HSSFSheet sheet = wb.getSheetAt(sheetIndex); |
| assertNotNull(sheet); |
| int rows = sheet.getLastRowNum(); |
| |
| for (int rowIndex = 0; rowIndex < rows; rowIndex++) { |
| HSSFRow row = sheet.getRow(rowIndex); |
| assertNotNull(row); |
| int cells = row.getLastCellNum(); |
| |
| for (int cellIndex = 0; cellIndex < cells; cellIndex++) { |
| row.getCell(cellIndex); |
| } |
| } |
| } |
| } |
| } |
| |
| /** |
| * Tests read and write of Unicode strings in formula results |
| * bug and testcase submitted by Sompop Kumnoonsate |
| * The file contains THAI unicode characters. |
| */ |
| @Test |
| void bugUnicodeStringFormulaRead() throws Exception { |
| |
| try (HSSFWorkbook w = openSampleWorkbook("25695.xls")) { |
| |
| HSSFCell a1 = w.getSheetAt(0).getRow(0).getCell(0); |
| HSSFCell a2 = w.getSheetAt(0).getRow(0).getCell(1); |
| HSSFCell b1 = w.getSheetAt(0).getRow(1).getCell(0); |
| HSSFCell b2 = w.getSheetAt(0).getRow(1).getCell(1); |
| HSSFCell c1 = w.getSheetAt(0).getRow(2).getCell(0); |
| HSSFCell c2 = w.getSheetAt(0).getRow(2).getCell(1); |
| HSSFCell d1 = w.getSheetAt(0).getRow(3).getCell(0); |
| HSSFCell d2 = w.getSheetAt(0).getRow(3).getCell(1); |
| |
| /* |
| // THAI code page |
| System.out.println("a1="+unicodeString(a1)); |
| System.out.println("a2="+unicodeString(a2)); |
| // US code page |
| System.out.println("b1="+unicodeString(b1)); |
| System.out.println("b2="+unicodeString(b2)); |
| // THAI+US |
| System.out.println("c1="+unicodeString(c1)); |
| System.out.println("c2="+unicodeString(c2)); |
| // US+THAI |
| System.out.println("d1="+unicodeString(d1)); |
| System.out.println("d2="+unicodeString(d2)); |
| */ |
| |
| confirmSameCellText(a1, a2); |
| confirmSameCellText(b1, b2); |
| confirmSameCellText(c1, c2); |
| confirmSameCellText(d1, d2); |
| |
| try (HSSFWorkbook rw = writeOutAndReadBack(w)) { |
| |
| HSSFCell ra1 = rw.getSheetAt(0).getRow(0).getCell(0); |
| HSSFCell ra2 = rw.getSheetAt(0).getRow(0).getCell(1); |
| HSSFCell rb1 = rw.getSheetAt(0).getRow(1).getCell(0); |
| HSSFCell rb2 = rw.getSheetAt(0).getRow(1).getCell(1); |
| HSSFCell rc1 = rw.getSheetAt(0).getRow(2).getCell(0); |
| HSSFCell rc2 = rw.getSheetAt(0).getRow(2).getCell(1); |
| HSSFCell rd1 = rw.getSheetAt(0).getRow(3).getCell(0); |
| HSSFCell rd2 = rw.getSheetAt(0).getRow(3).getCell(1); |
| |
| confirmSameCellText(a1, ra1); |
| confirmSameCellText(b1, rb1); |
| confirmSameCellText(c1, rc1); |
| confirmSameCellText(d1, rd1); |
| |
| confirmSameCellText(a1, ra2); |
| confirmSameCellText(b1, rb2); |
| confirmSameCellText(c1, rc2); |
| confirmSameCellText(d1, rd2); |
| } |
| } |
| } |
| |
| private static void confirmSameCellText(HSSFCell a, HSSFCell b) { |
| assertEquals(a.getRichStringCellValue().getString(), b.getRichStringCellValue().getString()); |
| } |
| |
| /** |
| * names and macros |
| */ |
| @Test |
| void bug27852() throws Exception { |
| try (HSSFWorkbook wb = openSampleWorkbook("27852.xls")) { |
| for (HSSFName name : wb.getAllNames()) { |
| assertNotNull(name.getNameName()); |
| if (!name.isFunctionName()) { |
| assertNotNull(name.getRefersToFormula()); |
| } |
| } |
| } |
| } |
| |
| /** |
| * Bug 29206: NPE on HSSFSheet.getRow for blank rows |
| */ |
| @Test |
| void bug29206() throws Exception { |
| //the first check with blank workbook |
| try (HSSFWorkbook wb = openSampleWorkbook("Simple.xls")) { |
| HSSFSheet sheet = wb.createSheet(); |
| for (int i = 1; i < 400; i++) { |
| HSSFRow row = sheet.getRow(i); |
| assertNull(row); |
| } |
| } |
| } |
| |
| /** |
| * Bug 29942: Importing Excel files that have been created by Open Office on Linux |
| */ |
| @Test |
| void bug29942() throws Exception { |
| try (HSSFWorkbook wb = openSampleWorkbook("29942.xls")) { |
| |
| HSSFSheet sheet = wb.getSheetAt(0); |
| int count = 0; |
| for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { |
| HSSFRow row = sheet.getRow(i); |
| if (row != null) { |
| HSSFCell cell = row.getCell(0); |
| assertEquals(CellType.STRING, cell.getCellType()); |
| count++; |
| } |
| } |
| assertEquals(85, count); //should read 85 rows |
| |
| try (HSSFWorkbook wb2 = writeOutAndReadBack(wb)) { |
| assertNotNull(wb2.getSheetAt(0)); |
| } |
| } |
| } |
| |
| /** |
| * Bug 30540: HSSFSheet.setRowBreak throws NullPointerException |
| */ |
| @Test |
| void bug30540() throws Exception { |
| try (HSSFWorkbook wb = openSampleWorkbook("30540.xls")) { |
| |
| HSSFSheet s1 = wb.getSheetAt(0); |
| s1.setRowBreak(1); |
| try (HSSFWorkbook wb2 = writeOutAndReadBack(wb)) { |
| HSSFSheet s2 = wb2.getSheetAt(0); |
| int[] act = s2.getRowBreaks(); |
| int[] exp = { 1 }; |
| assertArrayEquals(exp, act); |
| } |
| } |
| } |
| |
| /** |
| * Bug 35564: HSSFCell.java: NullPtrExc in isGridsPrinted() and getProtect() |
| * when HSSFWorkbook is created from file |
| */ |
| @Test |
| void bug35564() throws Exception { |
| simpleTest("35564.xls", wb -> { |
| HSSFSheet sheet = wb.getSheetAt(0); |
| assertFalse(sheet.isGridsPrinted()); |
| assertFalse(sheet.getProtect()); |
| }); |
| } |
| |
| /** |
| * Bug 35565: HSSFCell.java: NullPtrExc in getColumnBreaks() when HSSFWorkbook is created from file |
| */ |
| @Test |
| void bug35565() throws Exception { |
| simpleTest("35565.xls", wb -> assertNotNull(wb.getSheetAt(0))); |
| } |
| |
| /** |
| * Bug 40285: CellIterator Skips First Column |
| */ |
| @Test |
| void bug40285() throws Exception { |
| try (HSSFWorkbook wb = openSampleWorkbook("40285.xls")) { |
| |
| HSSFSheet sheet = wb.getSheetAt(0); |
| int rownum = 0; |
| for (Iterator<Row> it = sheet.rowIterator(); it.hasNext(); rownum++) { |
| Row row = it.next(); |
| assertEquals(rownum, row.getRowNum()); |
| int cellNum = 0; |
| for (Iterator<Cell> it2 = row.cellIterator(); it2.hasNext(); cellNum++) { |
| Cell cell = it2.next(); |
| assertEquals(cellNum, cell.getColumnIndex()); |
| } |
| } |
| } |
| } |
| |
| /** |
| * Test bug 38266: NPE when adding a row break |
| * <p> |
| * User's diagnosis: |
| * 1. Manually (i.e., not using POI) create an Excel Workbook, making sure it |
| * contains a sheet that doesn't have any row breaks |
| * 2. Using POI, create a new HSSFWorkbook from the template in step #1 |
| * 3. Try adding a row break (via sheet.setRowBreak()) to the sheet mentioned in step #1 |
| * 4. Get a NullPointerException |
| */ |
| @Test |
| void bug38266() throws Exception { |
| String[] files = {"Simple.xls", "SimpleMultiCell.xls", "duprich1.xls"}; |
| for (String file : files) { |
| try (HSSFWorkbook wb = openSampleWorkbook(file)) { |
| |
| HSSFSheet sheet = wb.getSheetAt(0); |
| int[] breaks = sheet.getRowBreaks(); |
| assertEquals(0, breaks.length); |
| |
| //add 3 row breaks |
| for (int j = 1; j <= 3; j++) { |
| sheet.setRowBreak(j * 20); |
| } |
| } |
| } |
| } |
| |
| /** |
| * Bug 44200: Sheet not cloneable when Note added to excel cell |
| */ |
| @Test |
| void bug44200() throws Exception { |
| simpleTest("44200.xls", wb -> assertDoesNotThrow(() -> wb.cloneSheet(0))); |
| } |
| |
| /** |
| * Bug 41546: Constructing HSSFWorkbook is failed, |
| * Unknown Ptg in Formula: 0x1a (26) |
| */ |
| @Test |
| void bug41546() throws Exception { |
| simpleTest("41546.xls", wb -> assertEquals(1, wb.getNumberOfSheets())); |
| } |
| |
| /** |
| * Bug 42618: RecordFormatException reading a file containing |
| * =CHOOSE(2,A2,A3,A4) |
| */ |
| @Test |
| void bug42618() throws Exception { |
| try (HSSFWorkbook wb1 = openSampleWorkbook("SimpleWithChoose.xls"); |
| HSSFWorkbook wb2 = writeOutAndReadBack(wb1)){ |
| // Check we detect the string properly too |
| HSSFSheet s = wb2.getSheetAt(0); |
| |
| // Textual value |
| HSSFRow r1 = s.getRow(0); |
| HSSFCell c1 = r1.getCell(1); |
| assertEquals("=CHOOSE(2,A2,A3,A4)", c1.getRichStringCellValue().toString()); |
| |
| // Formula Value |
| HSSFRow r2 = s.getRow(1); |
| HSSFCell c2 = r2.getCell(1); |
| assertEquals(25, (int) c2.getNumericCellValue()); |
| |
| try { |
| assertEquals("CHOOSE(2,A2,A3,A4)", c2.getCellFormula()); |
| } catch (IllegalStateException e) { |
| if (e.getMessage().startsWith("Too few arguments") |
| && e.getMessage().indexOf("ConcatPtg") > 0) { |
| fail("identified bug 44306"); |
| } |
| } |
| } |
| } |
| |
| /** |
| * Something up with the FileSharingRecord |
| */ |
| @Test |
| void bug43251() throws Exception { |
| // Used to blow up with an IllegalArgumentException when creating a FileSharingRecord |
| simpleTest("43251.xls", wb -> assertEquals(1, wb.getNumberOfSheets())); |
| } |
| |
| /** |
| * Crystal reports generates files with short |
| * StyleRecords, which is against the spec |
| */ |
| @Test |
| void bug44471() throws Exception { |
| // Used to blow up with an ArrayIndexOutOfBounds when creating a StyleRecord |
| simpleTest("OddStyleRecord.xls", wb -> assertEquals(1, wb.getNumberOfSheets())); |
| } |
| |
| /** |
| * Files with "read only recommended" were giving |
| * grief on the FileSharingRecord |
| */ |
| @Test |
| void bug44536() throws Exception { |
| // Used to blow up with an IllegalArgumentException when creating a FileSharingRecord |
| simpleTest("ReadOnlyRecommended.xls", wb -> { |
| // Check read only advised |
| assertEquals(3, wb.getNumberOfSheets()); |
| assertTrue(wb.isWriteProtected()); |
| }); |
| |
| |
| // But also check that another wb isn't |
| simpleTest("SimpleWithChoose.xls", wb -> assertFalse(wb.isWriteProtected())); |
| } |
| |
| /** |
| * Some files were having problems with the DVRecord, |
| * probably due to dropdowns |
| */ |
| @Test |
| void bug44593() throws Exception { |
| // Used to blow up with an IllegalArgumentException when creating a DVRecord |
| // Now won't, but no idea if this means we have rubbish in the DVRecord or not... |
| simpleTest("44593.xls", wb -> assertEquals(2, wb.getNumberOfSheets())); |
| } |
| |
| /** |
| * Used to give problems due to trying to read a zero |
| * length string, but that's now properly handled |
| */ |
| @Test |
| void bug44643() throws Exception { |
| // Used to blow up with an IllegalArgumentException |
| simpleTest("44643.xls", wb -> assertEquals(1, wb.getNumberOfSheets())); |
| } |
| |
| /** |
| * User reported the wrong number of rows from the |
| * iterator, but we can't replicate that |
| */ |
| @Test |
| void bug44693() throws Exception { |
| try (HSSFWorkbook wb = openSampleWorkbook("44693.xls")) { |
| HSSFSheet s = wb.getSheetAt(0); |
| |
| // Rows are 1 to 713 |
| assertEquals(0, s.getFirstRowNum()); |
| assertEquals(712, s.getLastRowNum()); |
| assertEquals(713, s.getPhysicalNumberOfRows()); |
| |
| // Now check the iterator |
| int rowsSeen = 0; |
| for (Iterator<Row> i = s.rowIterator(); i.hasNext(); ) { |
| Row r = i.next(); |
| assertNotNull(r); |
| rowsSeen++; |
| } |
| assertEquals(713, rowsSeen); |
| } |
| } |
| |
| /** |
| * Problems with extracting check boxes from |
| * HSSFObjectData |
| */ |
| @Test |
| void bug44840() throws Exception { |
| try (HSSFWorkbook wb = openSampleWorkbook("WithCheckBoxes.xls")) { |
| |
| // Take a look at the embedded objects |
| List<HSSFObjectData> objects = wb.getAllEmbeddedObjects(); |
| assertEquals(1, objects.size()); |
| |
| HSSFObjectData obj = objects.get(0); |
| assertNotNull(obj); |
| |
| // Peek inside the underlying record |
| EmbeddedObjectRefSubRecord rec = obj.findObjectRecord(); |
| assertNotNull(rec); |
| |
| // assertEquals(32, rec.field_1_stream_id_offset); |
| assertEquals(0, rec.getStreamId().intValue()); // WRONG! |
| assertEquals("Forms.CheckBox.1", rec.getOLEClassName()); |
| assertEquals(12, rec.getObjectData().length); |
| |
| // Doesn't have a directory |
| assertFalse(obj.hasDirectoryEntry()); |
| assertNotNull(obj.getObjectData()); |
| assertEquals(12, obj.getObjectData().length); |
| assertEquals("Forms.CheckBox.1", obj.getOLE2ClassName()); |
| |
| assertThrows(IllegalArgumentException.class, obj::getDirectory); |
| } |
| } |
| |
| /** |
| * Test that we can delete sheets without |
| * breaking the build in named ranges |
| * used for printing stuff. |
| */ |
| @Test |
| void bug30978() throws Exception { |
| try (HSSFWorkbook wb1 = openSampleWorkbook("30978-alt.xls")) { |
| assertEquals(1, wb1.getNumberOfNames()); |
| assertEquals(3, wb1.getNumberOfSheets()); |
| |
| // Check all names fit within range, and use |
| // DeletedArea3DPtg |
| InternalWorkbook w = wb1.getWorkbook(); |
| assertNames(wb1, w); |
| |
| // Delete the 2nd sheet |
| wb1.removeSheetAt(1); |
| |
| // Re-check |
| assertEquals(1, wb1.getNumberOfNames()); |
| assertEquals(2, wb1.getNumberOfSheets()); |
| assertNames(wb1, w); |
| |
| // Save and re-load |
| try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { |
| w = wb2.getWorkbook(); |
| |
| assertEquals(1, wb2.getNumberOfNames()); |
| assertEquals(2, wb2.getNumberOfSheets()); |
| |
| assertNames(wb2, w); |
| } |
| } |
| } |
| |
| private void assertNames(HSSFWorkbook wb1, InternalWorkbook w) { |
| for (int i = 0; i < w.getNumNames(); i++) { |
| NameRecord r = w.getNameRecord(i); |
| assertTrue(r.getSheetNumber() <= wb1.getNumberOfSheets()); |
| |
| Ptg[] nd = r.getNameDefinition(); |
| assertEquals(1, nd.length); |
| assertTrue(nd[0] instanceof DeletedArea3DPtg); |
| } |
| } |
| |
| /** |
| * Test that fonts get added properly |
| */ |
| @Test |
| void bug45338() throws IOException { |
| try (HSSFWorkbook wb = new HSSFWorkbook()) { |
| assertEquals(4, wb.getNumberOfFonts()); |
| |
| HSSFSheet s = wb.createSheet(); |
| s.createRow(0); |
| s.createRow(1); |
| s.getRow(0).createCell(0); |
| s.getRow(1).createCell(0); |
| |
| assertEquals(4, wb.getNumberOfFonts()); |
| |
| HSSFFont f1 = wb.getFontAt(0); |
| assertFalse(f1.getBold()); |
| |
| // Check that asking for the same font multiple times gives you the same thing. |
| // Otherwise, our tests wouldn't work! |
| assertSame(wb.getFontAt(0), wb.getFontAt(0)); |
| assertEquals(wb.getFontAt(0), wb.getFontAt(0)); |
| assertEquals(wb.getFontAt(2), wb.getFontAt(2)); |
| assertNotSame(wb.getFontAt(0), wb.getFontAt(2)); |
| |
| // Look for a new font we have yet to add |
| assertNull(wb.findFont(false, (short) 123, (short) 22, "Thingy", false, true, (short) 2, (byte) 2)); |
| |
| HSSFFont nf = wb.createFont(); |
| assertEquals(5, wb.getNumberOfFonts()); |
| |
| assertEquals(5, nf.getIndex()); |
| assertEquals(nf, wb.getFontAt(5)); |
| |
| nf.setBold(false); |
| nf.setColor((short) 123); |
| nf.setFontHeight((short) 22); |
| nf.setFontName("Thingy"); |
| nf.setItalic(false); |
| nf.setStrikeout(true); |
| nf.setTypeOffset((short) 2); |
| nf.setUnderline((byte) 2); |
| |
| assertEquals(5, wb.getNumberOfFonts()); |
| assertEquals(nf, wb.getFontAt(5)); |
| |
| // Find it now |
| assertNotNull(wb.findFont(false, (short) 123, (short) 22, "Thingy", false, true, (short) 2, (byte) 2)); |
| HSSFFont font = wb.findFont(false, (short) 123, (short) 22, "Thingy", false, true, (short) 2, (byte) 2); |
| assertNotNull(font); |
| assertEquals(5, font.getIndex()); |
| assertEquals(nf, wb.findFont(false, (short) 123, (short) 22, "Thingy", false, true, (short) 2, (byte) 2)); |
| } |
| } |
| |
| /** |
| * From the mailing list - ensure we can handle a formula |
| * containing a zip code, eg ="70164" |
| */ |
| @Test |
| void bugZipCodeFormulas() throws Exception { |
| try (HSSFWorkbook wb1 = new HSSFWorkbook()) { |
| HSSFSheet s = wb1.createSheet(); |
| s.createRow(0); |
| HSSFCell c1 = s.getRow(0).createCell(0); |
| HSSFCell c2 = s.getRow(0).createCell(1); |
| HSSFCell c3 = s.getRow(0).createCell(2); |
| |
| // As number and string |
| c1.setCellFormula("70164"); |
| c2.setCellFormula("\"70164\""); |
| c3.setCellFormula("\"90210\""); |
| |
| // Check the formulas |
| assertEquals("70164", c1.getCellFormula()); |
| assertEquals("\"70164\"", c2.getCellFormula()); |
| |
| // And check the values - blank |
| confirmCachedValue(0.0, c1); |
| confirmCachedValue(0.0, c2); |
| confirmCachedValue(0.0, c3); |
| |
| // Try changing the cached value on one of the string |
| // formula cells, so we can see it updates properly |
| c3.setCellValue(new HSSFRichTextString("test")); |
| confirmCachedValue("test", c3); |
| IllegalStateException e = assertThrows(IllegalStateException.class, c3::getNumericCellValue); |
| assertEquals("Cannot get a NUMERIC value from a STRING formula cell", e.getMessage()); |
| |
| // Now evaluate, they should all be changed |
| HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb1); |
| eval.evaluateFormulaCell(c1); |
| eval.evaluateFormulaCell(c2); |
| eval.evaluateFormulaCell(c3); |
| |
| // Check that the cells now contain |
| // the correct values |
| confirmCachedValue(70164.0, c1); |
| confirmCachedValue("70164", c2); |
| confirmCachedValue("90210", c3); |
| |
| |
| // Write and read |
| try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { |
| HSSFSheet ns = wb2.getSheetAt(0); |
| HSSFCell nc1 = ns.getRow(0).getCell(0); |
| HSSFCell nc2 = ns.getRow(0).getCell(1); |
| HSSFCell nc3 = ns.getRow(0).getCell(2); |
| |
| // Re-check |
| confirmCachedValue(70164.0, nc1); |
| confirmCachedValue("70164", nc2); |
| confirmCachedValue("90210", nc3); |
| |
| int i = 0; |
| for (Iterator<CellValueRecordInterface> it = ns.getSheet().getCellValueIterator(); it.hasNext(); i++) { |
| CellValueRecordInterface cvr = it.next(); |
| if (cvr instanceof FormulaRecordAggregate) { |
| FormulaRecordAggregate fr = (FormulaRecordAggregate) cvr; |
| |
| if (i == 0) { |
| assertEquals(70164.0, fr.getFormulaRecord().getValue(), 0.0001); |
| assertNull(fr.getStringRecord()); |
| } else if (i == 1) { |
| assertEquals(0.0, fr.getFormulaRecord().getValue(), 0.0001); |
| assertNotNull(fr.getStringRecord()); |
| assertEquals("70164", fr.getStringRecord().getString()); |
| } else { |
| assertEquals(0.0, fr.getFormulaRecord().getValue(), 0.0001); |
| assertNotNull(fr.getStringRecord()); |
| assertEquals("90210", fr.getStringRecord().getString()); |
| } |
| } |
| } |
| assertEquals(3, i); |
| } |
| } |
| } |
| |
| private static void confirmCachedValue(double expectedValue, HSSFCell cell) { |
| assertEquals(CellType.FORMULA, cell.getCellType()); |
| assertEquals(CellType.NUMERIC, cell.getCachedFormulaResultType()); |
| assertEquals(expectedValue, cell.getNumericCellValue(), 0.0); |
| } |
| |
| private static void confirmCachedValue(String expectedValue, HSSFCell cell) { |
| assertEquals(CellType.FORMULA, cell.getCellType()); |
| assertEquals(CellType.STRING, cell.getCachedFormulaResultType()); |
| assertEquals(expectedValue, cell.getRichStringCellValue().getString()); |
| } |
| |
| /** |
| * Problem with "Vector Rows", eg a whole |
| * column which is set to the result of |
| * {=sin(B1:B9)}(9,1), so that each cell is |
| * shown to have the contents |
| * {=sin(B1:B9){9,1)[rownum][0] |
| * In this sample file, the vector column |
| * is C, and the data column is B. |
| * <p> |
| * Expected ExpPtg to be converted from Shared to Non-Shared... |
| */ |
| @Disabled("For now, blows up with an exception from ExtPtg") |
| @Test |
| void test43623() throws Exception { |
| try (HSSFWorkbook wb1 = openSampleWorkbook("43623.xls")) { |
| assertEquals(1, wb1.getNumberOfSheets()); |
| |
| HSSFSheet s1 = wb1.getSheetAt(0); |
| |
| HSSFCell c1 = s1.getRow(0).getCell(2); |
| HSSFCell c2 = s1.getRow(1).getCell(2); |
| HSSFCell c3 = s1.getRow(2).getCell(2); |
| |
| // These formula contents are a guess... |
| assertEquals("{=sin(B1:B9){9,1)[0][0]", c1.getCellFormula()); |
| assertEquals("{=sin(B1:B9){9,1)[1][0]", c2.getCellFormula()); |
| assertEquals("{=sin(B1:B9){9,1)[2][0]", c3.getCellFormula()); |
| |
| // Save and re-open, ensure it still works |
| try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { |
| HSSFSheet ns1 = wb2.getSheetAt(0); |
| HSSFCell nc1 = ns1.getRow(0).getCell(2); |
| HSSFCell nc2 = ns1.getRow(1).getCell(2); |
| HSSFCell nc3 = ns1.getRow(2).getCell(2); |
| |
| assertEquals("{=sin(B1:B9){9,1)[0][0]", nc1.getCellFormula()); |
| assertEquals("{=sin(B1:B9){9,1)[1][0]", nc2.getCellFormula()); |
| assertEquals("{=sin(B1:B9){9,1)[2][0]", nc3.getCellFormula()); |
| } |
| } |
| } |
| |
| /** |
| * People are all getting confused about the last |
| * row and cell number |
| */ |
| @Test |
| void bug30635() throws IOException { |
| try (HSSFWorkbook wb = new HSSFWorkbook()) { |
| HSSFSheet s = wb.createSheet(); |
| |
| // No rows, first/last return -1 |
| assertEquals(-1, s.getFirstRowNum()); |
| assertEquals(-1, s.getLastRowNum()); |
| assertEquals(0, s.getPhysicalNumberOfRows()); |
| |
| // One row, most things are 0, physical is 1 |
| s.createRow(0); |
| assertEquals(0, s.getFirstRowNum()); |
| assertEquals(0, s.getLastRowNum()); |
| assertEquals(1, s.getPhysicalNumberOfRows()); |
| |
| // And another, things change |
| s.createRow(4); |
| assertEquals(0, s.getFirstRowNum()); |
| assertEquals(4, s.getLastRowNum()); |
| assertEquals(2, s.getPhysicalNumberOfRows()); |
| |
| |
| // Now start on cells |
| HSSFRow r = s.getRow(0); |
| assertEquals(-1, r.getFirstCellNum()); |
| assertEquals(-1, r.getLastCellNum()); |
| assertEquals(0, r.getPhysicalNumberOfCells()); |
| |
| // Add a cell, things move off -1 |
| r.createCell(0); |
| assertEquals(0, r.getFirstCellNum()); |
| assertEquals(1, r.getLastCellNum()); // last cell # + 1 |
| assertEquals(1, r.getPhysicalNumberOfCells()); |
| |
| r.createCell(1); |
| assertEquals(0, r.getFirstCellNum()); |
| assertEquals(2, r.getLastCellNum()); // last cell # + 1 |
| assertEquals(2, r.getPhysicalNumberOfCells()); |
| |
| r.createCell(4); |
| assertEquals(0, r.getFirstCellNum()); |
| assertEquals(5, r.getLastCellNum()); // last cell # + 1 |
| assertEquals(3, r.getPhysicalNumberOfCells()); |
| |
| } |
| } |
| |
| /** |
| * Data Tables - ptg 0x2 |
| */ |
| @Test |
| void bug44958() throws Exception { |
| try (HSSFWorkbook wb = openSampleWorkbook("44958.xls")) { |
| HSSFSheet s; |
| HSSFRow r; |
| HSSFCell c; |
| |
| // Check the contents of the formulas |
| |
| // E4 to G9 of sheet 4 make up the table |
| s = wb.getSheet("OneVariable Table Completed"); |
| r = s.getRow(3); |
| c = r.getCell(4); |
| assertEquals(CellType.FORMULA, c.getCellType()); |
| |
| // TODO - check the formula once tables and |
| // arrays are properly supported |
| |
| |
| // E4 to H9 of sheet 5 make up the table |
| s = wb.getSheet("TwoVariable Table Example"); |
| r = s.getRow(3); |
| c = r.getCell(4); |
| assertEquals(CellType.FORMULA, c.getCellType()); |
| |
| // TODO - check the formula once tables and |
| // arrays are properly supported |
| } |
| } |
| |
| /** |
| * 45322: HSSFSheet.autoSizeColumn fails when style.getDataFormat() returns -1 |
| */ |
| @Test |
| void bug45322() throws Exception { |
| try (HSSFWorkbook wb = openSampleWorkbook("44958.xls")) { |
| HSSFSheet sh = wb.getSheetAt(0); |
| for (short i = 0; i < 30; i++) { |
| sh.autoSizeColumn(i); |
| assertNotEquals(0, sh.getColumnWidth(i)); |
| } |
| } |
| } |
| |
| /** |
| * We used to add too many UncalcRecords to sheets |
| * with diagrams on. Don't any more |
| */ |
| @Test |
| void bug45414() throws Exception { |
| try (HSSFWorkbook wb1 = openSampleWorkbook("WithThreeCharts.xls")) { |
| wb1.getSheetAt(0).setForceFormulaRecalculation(true); |
| wb1.getSheetAt(1).setForceFormulaRecalculation(false); |
| wb1.getSheetAt(2).setForceFormulaRecalculation(true); |
| |
| // Write out and back in again |
| // This used to break |
| try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { |
| |
| // Check now set as it should be |
| assertTrue(wb2.getSheetAt(0).getForceFormulaRecalculation()); |
| assertFalse(wb2.getSheetAt(1).getForceFormulaRecalculation()); |
| assertTrue(wb2.getSheetAt(2).getForceFormulaRecalculation()); |
| } |
| } |
| } |
| |
| /** |
| * Very hidden sheets not displaying as such |
| */ |
| @Test |
| void bug45761() throws Exception { |
| try (HSSFWorkbook wb1 = openSampleWorkbook("45761.xls")) { |
| assertEquals(3, wb1.getNumberOfSheets()); |
| |
| assertFalse(wb1.isSheetHidden(0)); |
| assertFalse(wb1.isSheetVeryHidden(0)); |
| assertTrue(wb1.isSheetHidden(1)); |
| assertFalse(wb1.isSheetVeryHidden(1)); |
| assertFalse(wb1.isSheetHidden(2)); |
| assertTrue(wb1.isSheetVeryHidden(2)); |
| |
| // Change sheet 0 to be very hidden, and re-load |
| wb1.setSheetVisibility(0, SheetVisibility.VERY_HIDDEN); |
| |
| try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { |
| assertFalse(wb2.isSheetHidden(0)); |
| assertTrue(wb2.isSheetVeryHidden(0)); |
| assertTrue(wb2.isSheetHidden(1)); |
| assertFalse(wb2.isSheetVeryHidden(1)); |
| assertFalse(wb2.isSheetHidden(2)); |
| assertTrue(wb2.isSheetVeryHidden(2)); |
| } |
| } |
| } |
| |
| /** |
| * The resolution for bug 45777 assumed that the maximum text length in a header / footer |
| * record was 256 bytes. This assumption appears to be wrong. Since the fix for bug 47244, |
| * POI now supports header / footer text lengths beyond 256 bytes. |
| */ |
| @Test |
| void bug45777() throws Exception { |
| try (HSSFWorkbook wb = new HSSFWorkbook()) { |
| HSSFSheet s = wb.createSheet(); |
| |
| char[] cc248 = new char[248]; |
| Arrays.fill(cc248, 'x'); |
| String s248 = new String(cc248); |
| |
| String s249 = s248 + "1"; |
| String s250 = s248 + "12"; |
| String s251 = s248 + "123"; |
| assertEquals(248, s248.length()); |
| assertEquals(249, s249.length()); |
| assertEquals(250, s250.length()); |
| assertEquals(251, s251.length()); |
| |
| |
| // Try on headers |
| s.getHeader().setCenter(s248); |
| assertEquals(254, s.getHeader().getRawText().length()); |
| writeOutAndReadBack(wb).close(); |
| |
| s.getHeader().setCenter(s251); |
| assertEquals(257, s.getHeader().getRawText().length()); |
| writeOutAndReadBack(wb).close(); |
| |
| // header can be more than 256 bytes |
| s.getHeader().setCenter(s250); // 256 bytes required |
| s.getHeader().setCenter(s251); // 257 bytes required |
| |
| // Now try on footers |
| s.getFooter().setCenter(s248); |
| assertEquals(254, s.getFooter().getRawText().length()); |
| writeOutAndReadBack(wb).close(); |
| |
| s.getFooter().setCenter(s251); |
| assertEquals(257, s.getFooter().getRawText().length()); |
| writeOutAndReadBack(wb).close(); |
| |
| // footer can be more than 256 bytes |
| s.getFooter().setCenter(s250); // 256 bytes required |
| s.getFooter().setCenter(s251); // 257 bytes required |
| |
| } |
| } |
| |
| /** |
| * Charts with long titles |
| */ |
| @Test |
| void bug45784() throws Exception { |
| simpleTest("45784.xls", wb -> assertEquals(1, wb.getNumberOfSheets())); |
| } |
| |
| /** |
| * Cell background colours |
| */ |
| @Test |
| void bug45492() throws Exception { |
| try (HSSFWorkbook wb = openSampleWorkbook("45492.xls")) { |
| HSSFSheet s = wb.getSheetAt(0); |
| HSSFRow r = s.getRow(0); |
| HSSFPalette p = wb.getCustomPalette(); |
| |
| HSSFCell auto = r.getCell(0); |
| HSSFCell grey = r.getCell(1); |
| HSSFCell red = r.getCell(2); |
| HSSFCell blue = r.getCell(3); |
| HSSFCell green = r.getCell(4); |
| |
| assertEquals(64, auto.getCellStyle().getFillForegroundColor()); |
| assertEquals(64, auto.getCellStyle().getFillBackgroundColor()); |
| assertEquals("0:0:0", p.getColor(64).getHexString()); |
| |
| assertEquals(22, grey.getCellStyle().getFillForegroundColor()); |
| assertEquals(64, grey.getCellStyle().getFillBackgroundColor()); |
| assertEquals("C0C0:C0C0:C0C0", p.getColor(22).getHexString()); |
| |
| assertEquals(10, red.getCellStyle().getFillForegroundColor()); |
| assertEquals(64, red.getCellStyle().getFillBackgroundColor()); |
| assertEquals("FFFF:0:0", p.getColor(10).getHexString()); |
| |
| assertEquals(12, blue.getCellStyle().getFillForegroundColor()); |
| assertEquals(64, blue.getCellStyle().getFillBackgroundColor()); |
| assertEquals("0:0:FFFF", p.getColor(12).getHexString()); |
| |
| assertEquals(11, green.getCellStyle().getFillForegroundColor()); |
| assertEquals(64, green.getCellStyle().getFillBackgroundColor()); |
| assertEquals("0:FFFF:0", p.getColor(11).getHexString()); |
| } |
| } |
| |
| /** |
| * ContinueRecord after EOF |
| */ |
| @Test |
| void bug46137() throws Exception { |
| simpleTest("46137.xls", wb -> assertEquals(7, wb.getNumberOfSheets())); |
| } |
| |
| /** |
| * Odd POIFS blocks issue: |
| * block[ 44 ] already removed from org.apache.poi.poifs.storage.BlockListImpl.remove |
| */ |
| @Test |
| void bug45290() throws Exception { |
| simpleTest("45290.xls", wb -> assertEquals(1, wb.getNumberOfSheets())); |
| } |
| |
| /** |
| * In POI-2.5 user reported exception when parsing a name with a custom VBA function: |
| * =MY_VBA_FUNCTION("lskdjflsk") |
| */ |
| @Test |
| void bug30070() throws Exception { |
| //contains custom VBA function 'Commission' |
| try (HSSFWorkbook wb = openSampleWorkbook("30070.xls")) { |
| HSSFSheet sh = wb.getSheetAt(0); |
| HSSFCell cell = sh.getRow(0).getCell(1); |
| |
| //B1 uses VBA in the formula |
| assertEquals("Commission(A1)", cell.getCellFormula()); |
| |
| //name sales_1 refers to Commission(Sheet0!$A$1) |
| int idx = wb.getNameIndex("sales_1"); |
| assertTrue(idx != -1); |
| |
| HSSFName name = wb.getNameAt(idx); |
| assertEquals("Commission(Sheet0!$A$1)", name.getRefersToFormula()); |
| } |
| } |
| |
| /** |
| * The link formulas which is referring to other books cannot be taken (the bug existed prior to POI-3.2) |
| * Expected: |
| * <p> |
| * [link_sub.xls]Sheet1!$A$1 |
| * [link_sub.xls]Sheet1!$A$2 |
| * [link_sub.xls]Sheet1!$A$3 |
| * <p> |
| * POI-3.1 output: |
| * <p> |
| * Sheet1!$A$1 |
| * Sheet1!$A$2 |
| * Sheet1!$A$3 |
| */ |
| @Test |
| void bug27364() throws Exception { |
| try (HSSFWorkbook wb = openSampleWorkbook("27364.xls")) { |
| HSSFSheet sheet = wb.getSheetAt(0); |
| |
| assertEquals("[link_sub.xls]Sheet1!$A$1", sheet.getRow(0).getCell(0).getCellFormula()); |
| assertEquals("[link_sub.xls]Sheet1!$A$2", sheet.getRow(1).getCell(0).getCellFormula()); |
| assertEquals("[link_sub.xls]Sheet1!$A$3", sheet.getRow(2).getCell(0).getCellFormula()); |
| } |
| } |
| |
| /** |
| * Similar to bug#27364: |
| * HSSFCell.getCellFormula() fails with references to external workbooks |
| */ |
| @Test |
| void bug31661() throws Exception { |
| try (HSSFWorkbook wb = openSampleWorkbook("31661.xls")) { |
| HSSFSheet sheet = wb.getSheetAt(0); |
| HSSFCell cell = sheet.getRow(11).getCell(10); //K11 |
| assertEquals("+'[GM Budget.xls]8085.4450'!$B$2", cell.getCellFormula()); |
| } |
| } |
| |
| /** |
| * Incorrect handling of non-ISO 8859-1 characters in Windows ANSII Code Page 1252 |
| */ |
| @Test |
| void bug27394() throws Exception { |
| try (HSSFWorkbook wb = openSampleWorkbook("27394.xls")) { |
| assertEquals("\u0161\u017E", wb.getSheetName(0)); |
| assertEquals("\u0161\u017E\u010D\u0148\u0159", wb.getSheetName(1)); |
| HSSFSheet sheet = wb.getSheetAt(0); |
| |
| assertEquals("\u0161\u017E", sheet.getRow(0).getCell(0).getStringCellValue()); |
| assertEquals("\u0161\u017E\u010D\u0148\u0159", sheet.getRow(1).getCell(0).getStringCellValue()); |
| } |
| } |
| |
| /** |
| * Multiple calls of HSSFWorkbook.write result in corrupted xls |
| */ |
| @Test |
| void bug32191() throws IOException { |
| try (HSSFWorkbook wb = openSampleWorkbook("27394.xls"); |
| UnsynchronizedByteArrayOutputStream out1 = new UnsynchronizedByteArrayOutputStream(); |
| UnsynchronizedByteArrayOutputStream out2 = new UnsynchronizedByteArrayOutputStream(); |
| UnsynchronizedByteArrayOutputStream out3 = new UnsynchronizedByteArrayOutputStream()) { |
| wb.write(out1); |
| wb.write(out2); |
| wb.write(out3); |
| assertArrayEquals(out1.toByteArray(), out2.toByteArray()); |
| assertArrayEquals(out2.toByteArray(), out3.toByteArray()); |
| } |
| } |
| |
| /** |
| * java.lang.NegativeArraySizeException reading long |
| * non-unicode data for a name record |
| */ |
| @Test |
| void bug47034() throws Exception { |
| simpleTest("47034.xls", wb -> { |
| assertEquals(893, wb.getNumberOfNames()); |
| assertEquals("Matthew\\Matthew11_1\\Matthew2331_1\\Matthew2351_1\\Matthew2361_1___lab", wb.getNameName(300)); |
| }); |
| } |
| |
| /** |
| * HSSFRichTextString.length() returns negative for really long strings. |
| * The test file was created in OpenOffice 3.0 as Excel does not allow cell text longer than 32,767 characters |
| */ |
| @Test |
| void bug46368() throws Exception { |
| try (HSSFWorkbook wb = openSampleWorkbook("46368.xls")) { |
| HSSFSheet s = wb.getSheetAt(0); |
| HSSFCell cell1 = s.getRow(0).getCell(0); |
| assertEquals(32770, cell1.getStringCellValue().length()); |
| |
| HSSFCell cell2 = s.getRow(2).getCell(0); |
| assertEquals(32766, cell2.getStringCellValue().length()); |
| } |
| } |
| |
| /** |
| * Short records on certain sheets with charts in them |
| */ |
| @Test |
| void bug48180() throws Exception { |
| try (HSSFWorkbook wb = openSampleWorkbook("48180.xls")) { |
| |
| HSSFSheet s = wb.getSheetAt(0); |
| HSSFCell cell1 = s.getRow(0).getCell(0); |
| assertEquals("test ", cell1.getStringCellValue()); |
| |
| HSSFCell cell2 = s.getRow(0).getCell(1); |
| assertEquals(1.0, cell2.getNumericCellValue(), 0.0); |
| } |
| } |
| |
| /** |
| * Round trip a file with an unusual UnicodeString/ExtRst record parts |
| */ |
| @Test |
| void bug47847() throws Exception { |
| try (HSSFWorkbook wb1 = openSampleWorkbook("47847.xls")) { |
| assertEquals(3, wb1.getNumberOfSheets()); |
| |
| // Find the SST record |
| UnicodeString withExt = wb1.getWorkbook().getSSTString(0); |
| UnicodeString withoutExt = wb1.getWorkbook().getSSTString(31); |
| |
| assertEquals("O:Alloc:Qty", withExt.getString()); |
| assertEquals(0x0004, (withExt.getOptionFlags() & 0x0004)); |
| |
| assertEquals("RT", withoutExt.getString()); |
| assertEquals(0x0000, (withoutExt.getOptionFlags() & 0x0004)); |
| |
| // Something about continues... |
| |
| |
| // Write out and re-read |
| try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { |
| assertEquals(3, wb2.getNumberOfSheets()); |
| |
| // Check it's the same now |
| withExt = wb2.getWorkbook().getSSTString(0); |
| withoutExt = wb2.getWorkbook().getSSTString(31); |
| |
| assertEquals("O:Alloc:Qty", withExt.getString()); |
| assertEquals(0x0004, (withExt.getOptionFlags() & 0x0004)); |
| |
| assertEquals("RT", withoutExt.getString()); |
| assertEquals(0x0000, (withoutExt.getOptionFlags() & 0x0004)); |
| } |
| } |
| } |
| |
| /** |
| * Problem with cloning a sheet with a chart |
| * contained in it. |
| */ |
| @Test |
| void bug49096() throws Exception { |
| try (HSSFWorkbook wb1 = openSampleWorkbook("49096.xls")) { |
| assertEquals(1, wb1.getNumberOfSheets()); |
| |
| assertNotNull(wb1.getSheetAt(0)); |
| wb1.cloneSheet(0); |
| assertEquals(2, wb1.getNumberOfSheets()); |
| |
| try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { |
| assertEquals(2, wb2.getNumberOfSheets()); |
| } |
| } |
| } |
| |
| /** |
| * Newly created sheets need to get a |
| * proper TabID, otherwise print setup |
| * gets confused on them. |
| * Also ensure that print setup refs are |
| * by reference not value |
| */ |
| @Test |
| void bug46664() throws Exception { |
| try (HSSFWorkbook wb1 = new HSSFWorkbook()) { |
| HSSFSheet sheet = wb1.createSheet("new_sheet"); |
| HSSFRow row = sheet.createRow((short) 0); |
| row.createCell(0).setCellValue(new HSSFRichTextString("Column A")); |
| row.createCell(1).setCellValue(new HSSFRichTextString("Column B")); |
| row.createCell(2).setCellValue(new HSSFRichTextString("Column C")); |
| row.createCell(3).setCellValue(new HSSFRichTextString("Column D")); |
| row.createCell(4).setCellValue(new HSSFRichTextString("Column E")); |
| row.createCell(5).setCellValue(new HSSFRichTextString("Column F")); |
| |
| //set print area from column a to column c (on first row) |
| wb1.setPrintArea( |
| 0, //sheet index |
| 0, //start column |
| 2, //end column |
| 0, //start row |
| 0 //end row |
| ); |
| |
| try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { |
| // Ensure the tab index |
| TabIdRecord tr = null; |
| for (org.apache.poi.hssf.record.Record r : wb2.getWorkbook().getRecords()) { |
| if (r instanceof TabIdRecord) { |
| tr = (TabIdRecord) r; |
| } |
| } |
| assertNotNull(tr); |
| assertEquals(1, tr.getTabIdSize()); |
| assertEquals(0, tr.getTabIdAt(0)); |
| |
| // Ensure the print setup |
| assertEquals("new_sheet!$A$1:$C$1", wb2.getPrintArea(0)); |
| HSSFName printArea = wb2.getName("Print_Area"); |
| assertNotNull(printArea); |
| assertEquals("new_sheet!$A$1:$C$1", printArea.getRefersToFormula()); |
| |
| // Needs reference not value |
| NameRecord nr = wb2.getWorkbook().getNameRecord( |
| wb2.getNameIndex("Print_Area") |
| ); |
| assertEquals("Print_Area", nr.getNameText()); |
| assertEquals(1, nr.getNameDefinition().length); |
| assertEquals( |
| "new_sheet!$A$1:$C$1", |
| ((Area3DPtg) nr.getNameDefinition()[0]).toFormulaString(HSSFEvaluationWorkbook.create(wb2)) |
| ); |
| assertEquals('R', nr.getNameDefinition()[0].getRVAType()); |
| } |
| } |
| } |
| |
| /** |
| * Problems with formula references to |
| * sheets via URLs |
| */ |
| @Test |
| void bug45970() throws Exception { |
| try (HSSFWorkbook wb1 = openSampleWorkbook("FormulaRefs.xls")) { |
| assertEquals(3, wb1.getNumberOfSheets()); |
| |
| HSSFSheet s = wb1.getSheetAt(0); |
| HSSFRow row; |
| |
| row = s.getRow(0); |
| assertEquals(CellType.NUMERIC, row.getCell(1).getCellType()); |
| assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); |
| |
| row = s.getRow(1); |
| assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); |
| assertEquals("B1", row.getCell(1).getCellFormula()); |
| assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); |
| |
| row = s.getRow(2); |
| assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); |
| assertEquals("Sheet1!B1", row.getCell(1).getCellFormula()); |
| assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); |
| |
| row = s.getRow(3); |
| assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); |
| assertEquals("[Formulas2.xls]Sheet1!B2", row.getCell(1).getCellFormula()); |
| assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); |
| |
| row = s.getRow(4); |
| assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); |
| assertEquals("'[$http://gagravarr.org/FormulaRefs.xls]Sheet1'!B1", row.getCell(1).getCellFormula()); |
| assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); |
| |
| // Link our new workbook |
| Workbook externalWb1 = new HSSFWorkbook(); |
| externalWb1.createSheet("Sheet1"); |
| assertEquals(4, wb1.linkExternalWorkbook("$http://gagravarr.org/FormulaRefs2.xls", externalWb1)); |
| |
| // Change 4 |
| row.getCell(1).setCellFormula("'[$http://gagravarr.org/FormulaRefs2.xls]Sheet1'!B2"); |
| row.getCell(1).setCellValue(123.0); |
| |
| // Link our new workbook |
| Workbook externalWb2 = new HSSFWorkbook(); |
| externalWb2.createSheet("Sheet1"); |
| assertEquals(5, wb1.linkExternalWorkbook("$http://example.com/FormulaRefs.xls", externalWb2)); |
| |
| // Add 5 |
| row = s.createRow(5); |
| row.createCell(1, CellType.FORMULA); |
| row.getCell(1).setCellFormula("'[$http://example.com/FormulaRefs.xls]Sheet1'!B1"); |
| row.getCell(1).setCellValue(234.0); |
| |
| // Re-test |
| try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { |
| s = wb2.getSheetAt(0); |
| |
| row = s.getRow(0); |
| assertEquals(CellType.NUMERIC, row.getCell(1).getCellType()); |
| assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); |
| |
| row = s.getRow(1); |
| assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); |
| assertEquals("B1", row.getCell(1).getCellFormula()); |
| assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); |
| |
| row = s.getRow(2); |
| assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); |
| assertEquals("Sheet1!B1", row.getCell(1).getCellFormula()); |
| assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); |
| |
| row = s.getRow(3); |
| assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); |
| assertEquals("[Formulas2.xls]Sheet1!B2", row.getCell(1).getCellFormula()); |
| assertEquals(112.0, row.getCell(1).getNumericCellValue(), 0); |
| |
| row = s.getRow(4); |
| assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); |
| assertEquals("'[$http://gagravarr.org/FormulaRefs2.xls]Sheet1'!B2", row.getCell(1).getCellFormula()); |
| assertEquals(123.0, row.getCell(1).getNumericCellValue(), 0); |
| |
| row = s.getRow(5); |
| assertEquals(CellType.FORMULA, row.getCell(1).getCellType()); |
| assertEquals("'[$http://example.com/FormulaRefs.xls]Sheet1'!B1", row.getCell(1).getCellFormula()); |
| assertEquals(234.0, row.getCell(1).getNumericCellValue(), 0); |
| |
| } |
| } |
| } |
| |
| /** |
| * Test for a file with NameRecord with NameCommentRecord comments |
| */ |
| @Test |
| void bug49185() throws Exception { |
| try (HSSFWorkbook wb1 = openSampleWorkbook("49185.xls")) { |
| Name name = wb1.getName("foobarName"); |
| assertNotNull(name); |
| assertEquals("This is a comment", name.getComment()); |
| |
| // Rename the name, comment comes with it |
| name.setNameName("ChangedName"); |
| assertEquals("This is a comment", name.getComment()); |
| |
| // Save and re-check |
| try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { |
| name = wb2.getName("ChangedName"); |
| assertNotNull(name); |
| assertEquals("This is a comment", name.getComment()); |
| |
| // Now try to change it |
| name.setComment("Changed Comment"); |
| assertEquals("Changed Comment", name.getComment()); |
| |
| // Save and re-check |
| try (HSSFWorkbook wb3 = writeOutAndReadBack(wb2)) { |
| name = wb3.getName("ChangedName"); |
| assertNotNull(name); |
| assertEquals("Changed Comment", name.getComment()); |
| } |
| } |
| } |
| } |
| |
| /** Vertically aligned text */ |
| @Test |
| void bug49524() throws Exception { |
| try (HSSFWorkbook wb1 = openSampleWorkbook("49524.xls")) { |
| Sheet s = wb1.getSheetAt(0); |
| Row r = s.getRow(0); |
| Cell rotated = r.getCell(0); |
| Cell normal = r.getCell(1); |
| |
| // Check the current ones |
| assertEquals(0, normal.getCellStyle().getRotation()); |
| assertEquals(0xff, rotated.getCellStyle().getRotation()); |
| |
| // Add a new style, also rotated |
| CellStyle cs = wb1.createCellStyle(); |
| cs.setRotation((short) 0xff); |
| Cell nc = r.createCell(2); |
| nc.setCellValue("New Rotated Text"); |
| nc.setCellStyle(cs); |
| assertEquals(0xff, nc.getCellStyle().getRotation()); |
| |
| // Write out and read back |
| try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { |
| // Re-check |
| s = wb2.getSheetAt(0); |
| r = s.getRow(0); |
| rotated = r.getCell(0); |
| normal = r.getCell(1); |
| nc = r.getCell(2); |
| |
| assertEquals(0, normal.getCellStyle().getRotation()); |
| assertEquals(0xff, rotated.getCellStyle().getRotation()); |
| assertEquals(0xff, nc.getCellStyle().getRotation()); |
| } |
| } |
| } |
| |
| /** |
| * Setting the user style name on custom styles |
| */ |
| @Test |
| void bug49689() throws Exception { |
| try (HSSFWorkbook wb1 = new HSSFWorkbook()) { |
| HSSFSheet s = wb1.createSheet("Test"); |
| HSSFRow r = s.createRow(0); |
| HSSFCell c = r.createCell(0); |
| |
| HSSFCellStyle cs1 = wb1.createCellStyle(); |
| HSSFCellStyle cs2 = wb1.createCellStyle(); |
| HSSFCellStyle cs3 = wb1.createCellStyle(); |
| |
| assertEquals(21, cs1.getIndex()); |
| cs1.setUserStyleName("Testing"); |
| |
| assertEquals(22, cs2.getIndex()); |
| cs2.setUserStyleName("Testing 2"); |
| |
| assertEquals(23, cs3.getIndex()); |
| cs3.setUserStyleName("Testing 3"); |
| |
| // Set one |
| c.setCellStyle(cs1); |
| |
| // Write out and read back |
| try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { |
| // Re-check |
| assertEquals("Testing", wb2.getCellStyleAt((short) 21).getUserStyleName()); |
| assertEquals("Testing 2", wb2.getCellStyleAt((short) 22).getUserStyleName()); |
| assertEquals("Testing 3", wb2.getCellStyleAt((short) 23).getUserStyleName()); |
| } |
| } |
| } |
| |
| @Test |
| void bug49751() throws Exception { |
| Set<String> exp = new HashSet<>(Arrays.asList( |
| "20% - Accent1", "20% - Accent2", "20% - Accent3", "20% - Accent4", "20% - Accent5", |
| "20% - Accent6", "40% - Accent1", "40% - Accent2", "40% - Accent3", "40% - Accent4", |
| "40% - Accent5", "40% - Accent6", "60% - Accent1", "60% - Accent2", "60% - Accent3", |
| "60% - Accent4", "60% - Accent5", "60% - Accent6", "Accent1", "Accent2", "Accent3", |
| "Accent4", "Accent5", "Accent6", "Bad", "Calculation", "Check Cell", "Explanatory Text", |
| "Good", "Heading 1", "Heading 2", "Heading 3", "Heading 4", "Input", "Linked Cell", |
| "Neutral", "Note", "Output", "Title", "Total", "Warning Text")); |
| |
| try (HSSFWorkbook wb = openSampleWorkbook("49751.xls")) { |
| Set<String> act = IntStream |
| .range(0, wb.getNumCellStyles()) |
| .mapToObj(wb::getCellStyleAt) |
| .map(HSSFCellStyle::getUserStyleName) |
| .filter(Objects::nonNull) |
| .collect(Collectors.toSet()); |
| assertEquals(exp, act); |
| } |
| } |
| |
| /** |
| * Regression with the PageSettingsBlock |
| */ |
| @Test |
| void bug49931() throws Exception { |
| simpleTest("49931.xls", wb -> { |
| assertEquals(1, wb.getNumberOfSheets()); |
| assertEquals("Foo", wb.getSheetAt(0).getRow(0).getCell(0).getRichStringCellValue().toString()); |
| }); |
| } |
| |
| /** |
| * Missing left/right/centre options on a footer |
| */ |
| @Test |
| void bug48325() throws Exception { |
| try (HSSFWorkbook wb = openSampleWorkbook("48325.xls")) { |
| HSSFSheet sh = wb.getSheetAt(0); |
| HSSFFooter f = sh.getFooter(); |
| |
| // Will show as the center, as that is what excel does |
| // with an invalid footer lacking left/right/center details |
| assertEquals("", f.getLeft(), "Left text should be empty"); |
| assertEquals("", f.getRight(), "Right text should be empty"); |
| assertEquals("BlahBlah blah blah ", f.getCenter(), |
| "Center text should contain the illegal value"); |
| } |
| } |
| |
| /** |
| * Last row number when shifting rows |
| */ |
| @Test |
| void bug50416LastRowNumber() throws IOException { |
| // Create the workbook with 1 sheet which contains 3 rows |
| try (HSSFWorkbook wb = new HSSFWorkbook()) { |
| Sheet sheet = wb.createSheet("Bug50416"); |
| Row row1 = sheet.createRow(0); |
| Cell cellA_1 = row1.createCell(0, CellType.STRING); |
| cellA_1.setCellValue("Cell A,1"); |
| Row row2 = sheet.createRow(1); |
| Cell cellA_2 = row2.createCell(0, CellType.STRING); |
| cellA_2.setCellValue("Cell A,2"); |
| Row row3 = sheet.createRow(2); |
| Cell cellA_3 = row3.createCell(0, CellType.STRING); |
| cellA_3.setCellValue("Cell A,3"); |
| |
| // Test the last Row number it currently correct |
| assertEquals(2, sheet.getLastRowNum()); |
| |
| // Shift the first row to the end |
| sheet.shiftRows(0, 0, 3); |
| assertEquals(3, sheet.getLastRowNum()); |
| assertEquals(-1, sheet.getRow(0).getLastCellNum()); |
| assertEquals("Cell A,2", sheet.getRow(1).getCell(0).getStringCellValue()); |
| assertEquals("Cell A,3", sheet.getRow(2).getCell(0).getStringCellValue()); |
| assertEquals("Cell A,1", sheet.getRow(3).getCell(0).getStringCellValue()); |
| |
| // Shift the 2nd row up to the first one |
| sheet.shiftRows(1, 1, -1); |
| assertEquals(3, sheet.getLastRowNum()); |
| assertEquals("Cell A,2", sheet.getRow(0).getCell(0).getStringCellValue()); |
| assertEquals(-1, sheet.getRow(1).getLastCellNum()); |
| assertEquals("Cell A,3", sheet.getRow(2).getCell(0).getStringCellValue()); |
| assertEquals("Cell A,1", sheet.getRow(3).getCell(0).getStringCellValue()); |
| |
| // Shift the 4th row up into the gap in the 3rd row |
| sheet.shiftRows(3, 3, -2); |
| assertEquals(2, sheet.getLastRowNum()); |
| assertEquals("Cell A,2", sheet.getRow(0).getCell(0).getStringCellValue()); |
| assertEquals("Cell A,1", sheet.getRow(1).getCell(0).getStringCellValue()); |
| assertEquals("Cell A,3", sheet.getRow(2).getCell(0).getStringCellValue()); |
| assertEquals(-1, sheet.getRow(3).getLastCellNum()); |
| |
| // Now zap the empty 4th row - won't do anything |
| sheet.removeRow(sheet.getRow(3)); |
| |
| // Test again the last row number which should be 2 |
| assertEquals(2, sheet.getLastRowNum()); |
| assertEquals("Cell A,2", sheet.getRow(0).getCell(0).getStringCellValue()); |
| assertEquals("Cell A,1", sheet.getRow(1).getCell(0).getStringCellValue()); |
| assertEquals("Cell A,3", sheet.getRow(2).getCell(0).getStringCellValue()); |
| |
| } |
| } |
| |
| /** |
| * If you send a file between Excel and OpenOffice enough, something |
| * will turn the "General" format into "GENERAL" |
| */ |
| @Test |
| void bug50756() throws Exception { |
| try (HSSFWorkbook wb = openSampleWorkbook("50756.xls")) { |
| HSSFSheet s = wb.getSheetAt(0); |
| HSSFRow r17 = s.getRow(16); |
| HSSFRow r18 = s.getRow(17); |
| HSSFDataFormatter df = new HSSFDataFormatter(); |
| |
| assertEquals(10.0, r17.getCell(1).getNumericCellValue(), 0); |
| assertEquals(20.0, r17.getCell(2).getNumericCellValue(), 0); |
| assertEquals(20.0, r17.getCell(3).getNumericCellValue(), 0); |
| assertEquals("GENERAL", r17.getCell(1).getCellStyle().getDataFormatString()); |
| assertEquals("GENERAL", r17.getCell(2).getCellStyle().getDataFormatString()); |
| assertEquals("GENERAL", r17.getCell(3).getCellStyle().getDataFormatString()); |
| assertEquals("10", df.formatCellValue(r17.getCell(1))); |
| assertEquals("20", df.formatCellValue(r17.getCell(2))); |
| assertEquals("20", df.formatCellValue(r17.getCell(3))); |
| |
| assertEquals(16.0, r18.getCell(1).getNumericCellValue(), 0); |
| assertEquals(35.0, r18.getCell(2).getNumericCellValue(), 0); |
| assertEquals(123.0, r18.getCell(3).getNumericCellValue(), 0); |
| assertEquals("GENERAL", r18.getCell(1).getCellStyle().getDataFormatString()); |
| assertEquals("GENERAL", r18.getCell(2).getCellStyle().getDataFormatString()); |
| assertEquals("GENERAL", r18.getCell(3).getCellStyle().getDataFormatString()); |
| assertEquals("16", df.formatCellValue(r18.getCell(1))); |
| assertEquals("35", df.formatCellValue(r18.getCell(2))); |
| assertEquals("123", df.formatCellValue(r18.getCell(3))); |
| } |
| } |
| |
| /** |
| * A protected sheet with comments, when written out by |
| * POI, ends up upsetting excel. |
| * TODO Identify the cause and add extra asserts for |
| * the bit excel cares about |
| */ |
| @Test |
| void bug50833() throws Exception { |
| HSSFWorkbook wb1 = openSampleWorkbook("50833.xls"); |
| HSSFSheet s = wb1.getSheetAt(0); |
| assertEquals("Sheet1", s.getSheetName()); |
| assertFalse(s.getProtect()); |
| |
| HSSFCell c = s.getRow(0).getCell(0); |
| assertEquals("test cell value", c.getRichStringCellValue().getString()); |
| |
| HSSFComment cmt = c.getCellComment(); |
| assertNotNull(cmt); |
| assertEquals("Robert Lawrence", cmt.getAuthor()); |
| assertEquals("Robert Lawrence:\ntest comment", cmt.getString().getString()); |
| |
| // Reload |
| HSSFWorkbook wb2 = writeOutAndReadBack(wb1); |
| wb1.close(); |
| s = wb2.getSheetAt(0); |
| c = s.getRow(0).getCell(0); |
| |
| // Re-check the comment |
| cmt = c.getCellComment(); |
| assertNotNull(cmt); |
| assertEquals("Robert Lawrence", cmt.getAuthor()); |
| assertEquals("Robert Lawrence:\ntest comment", cmt.getString().getString()); |
| |
| // TODO Identify what excel doesn't like, and check for that |
| wb2.close(); |
| } |
| |
| /** |
| * The spec says that ChartEndObjectRecord has 6 reserved |
| * bytes on the end, but we sometimes find files without... |
| */ |
| @Test |
| void bug50939() throws Exception { |
| simpleTest("50939.xls", wb -> assertEquals(2, wb.getNumberOfSheets())); |
| } |
| |
| @Test |
| void bug49219() throws Exception { |
| simpleTest("49219.xls", wb -> { |
| assertEquals(1, wb.getNumberOfSheets()); |
| assertEquals("DGATE", wb.getSheetAt(0).getRow(1).getCell(0).getStringCellValue()); |
| }); |
| } |
| |
| @Test |
| void bug48968() throws Exception { |
| TimeZone userTimeZone = LocaleUtil.getUserTimeZone(); |
| LocaleUtil.setUserTimeZone(TimeZone.getTimeZone("CET")); |
| try { |
| HSSFWorkbook wb = openSampleWorkbook("48968.xls"); |
| assertEquals(1, wb.getNumberOfSheets()); |
| |
| DataFormatter fmt = new DataFormatter(); |
| |
| // Check the dates |
| HSSFSheet s = wb.getSheetAt(0); |
| Cell cell_d20110325 = s.getRow(0).getCell(0); |
| Cell cell_d19000102 = s.getRow(11).getCell(0); |
| Cell cell_d19000100 = s.getRow(21).getCell(0); |
| assertEquals(s.getRow(0).getCell(3).getStringCellValue(), fmt.formatCellValue(cell_d20110325)); |
| assertEquals(s.getRow(11).getCell(3).getStringCellValue(), fmt.formatCellValue(cell_d19000102)); |
| // There is no such thing as 00/01/1900... |
| assertEquals("00/01/1900 06:14:24", s.getRow(21).getCell(3).getStringCellValue()); |
| assertEquals("31/12/1899 06:14:24", fmt.formatCellValue(cell_d19000100)); |
| |
| // Check the cached values |
| assertEquals("HOUR(A1)", s.getRow(5).getCell(0).getCellFormula()); |
| assertEquals(11.0, s.getRow(5).getCell(0).getNumericCellValue(), 0); |
| assertEquals("MINUTE(A1)", s.getRow(6).getCell(0).getCellFormula()); |
| assertEquals(39.0, s.getRow(6).getCell(0).getNumericCellValue(), 0); |
| assertEquals("SECOND(A1)", s.getRow(7).getCell(0).getCellFormula()); |
| assertEquals(54.0, s.getRow(7).getCell(0).getNumericCellValue(), 0); |
| |
| // Re-evaluate and check |
| HSSFFormulaEvaluator.evaluateAllFormulaCells(wb); |
| assertEquals("HOUR(A1)", s.getRow(5).getCell(0).getCellFormula()); |
| assertEquals(11.0, s.getRow(5).getCell(0).getNumericCellValue(), 0); |
| assertEquals("MINUTE(A1)", s.getRow(6).getCell(0).getCellFormula()); |
| assertEquals(39.0, s.getRow(6).getCell(0).getNumericCellValue(), 0); |
| assertEquals("SECOND(A1)", s.getRow(7).getCell(0).getCellFormula()); |
| assertEquals(54.0, s.getRow(7).getCell(0).getNumericCellValue(), 0); |
| |
| // Push the time forward a bit and check |
| double date = s.getRow(0).getCell(0).getNumericCellValue(); |
| s.getRow(0).getCell(0).setCellValue(date + 1.26); |
| |
| HSSFFormulaEvaluator.evaluateAllFormulaCells(wb); |
| assertEquals("HOUR(A1)", s.getRow(5).getCell(0).getCellFormula()); |
| assertEquals(11.0 + 6.0, s.getRow(5).getCell(0).getNumericCellValue(), 0); |
| assertEquals("MINUTE(A1)", s.getRow(6).getCell(0).getCellFormula()); |
| assertEquals(39.0 + 14.0 + 1, s.getRow(6).getCell(0).getNumericCellValue(), 0); |
| assertEquals("SECOND(A1)", s.getRow(7).getCell(0).getCellFormula()); |
| assertEquals(54.0 + 24.0 - 60, s.getRow(7).getCell(0).getNumericCellValue(), 0); |
| |
| wb.close(); |
| } finally { |
| LocaleUtil.setUserTimeZone(userTimeZone); |
| } |
| } |
| |
| |
| /** |
| * Mixture of Ascii and Unicode strings in a |
| * NameComment record |
| */ |
| @Test |
| void bug51143() throws Exception { |
| simpleTest("51143.xls", wb -> assertEquals(1, wb.getNumberOfSheets())); |
| } |
| |
| /** |
| * File with exactly 256 data blocks (+header block) |
| * shouldn't break on POIFS loading |
| */ |
| @Test |
| void bug51461() throws Exception { |
| byte[] data = HSSFITestDataProvider.instance.getTestDataFileContent("51461.xls"); |
| |
| HSSFWorkbook wbPOIFS = new HSSFWorkbook(new POIFSFileSystem( |
| new ByteArrayInputStream(data)).getRoot(), false); |
| HSSFWorkbook wbPOIFS2 = new HSSFWorkbook(new POIFSFileSystem( |
| new ByteArrayInputStream(data)).getRoot(), false); |
| |
| assertEquals(2, wbPOIFS.getNumberOfSheets()); |
| assertEquals(2, wbPOIFS2.getNumberOfSheets()); |
| } |
| |
| @Test |
| void bug51535() throws Exception { |
| try (HSSFWorkbook wb = openSampleWorkbook("51535.xls")) { |
| assertEquals(3, wb.getNumberOfSheets()); |
| |
| // Check directly |
| HSSFSheet s = wb.getSheetAt(0); |
| assertEquals("Top Left Cell", s.getRow(0).getCell(0).getStringCellValue()); |
| assertEquals("Top Right Cell", s.getRow(0).getCell(255).getStringCellValue()); |
| assertEquals("Bottom Left Cell", s.getRow(65535).getCell(0).getStringCellValue()); |
| assertEquals("Bottom Right Cell", s.getRow(65535).getCell(255).getStringCellValue()); |
| |
| // Extract and check |
| try (ExcelExtractor ex = new ExcelExtractor(wb)) { |
| String text = ex.getText(); |
| assertContains(text, "Top Left Cell"); |
| assertContains(text, "Top Right Cell"); |
| assertContains(text, "Bottom Left Cell"); |
| assertContains(text, "Bottom Right Cell"); |
| } |
| } |
| } |
| |
| /** |
| * Sum across multiple workbooks |
| * eg =SUM($Sheet2.A1:$Sheet3.A1) |
| */ |
| @Test |
| void test48703() throws Exception { |
| try (HSSFWorkbook wb = openSampleWorkbook("48703.xls")) { |
| assertEquals(3, wb.getNumberOfSheets()); |
| |
| // Check reading the formula |
| Sheet sheet = wb.getSheetAt(0); |
| Row r = sheet.getRow(0); |
| Cell c = r.getCell(0); |
| |
| assertEquals("SUM(Sheet2:Sheet3!A1)", c.getCellFormula()); |
| assertEquals(4.0, c.getNumericCellValue(), 0); |
| |
| // Check the evaluated result |
| HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb); |
| eval.evaluateFormulaCell(c); |
| assertEquals(4.0, c.getNumericCellValue(), 0); |
| } |
| } |
| |
| @Test |
| void bug49896() throws Exception { |
| String exp = "VLOOKUP(A2,'[C:Documents and Settings/Yegor/My Documents/csco.xls]Sheet1'!$A$2:$B$3,2,FALSE)" |
| .replace("/", File.separator); |
| simpleTest("49896.xls", wb -> assertEquals(exp, wb.getSheetAt(0).getRow(1).getCell(1).getCellFormula())); |
| } |
| |
| @Test |
| void bug49529() throws Exception { |
| // user code reported in Bugzilla #49529 |
| simpleTest("49529.xls", wb -> { |
| wb.getSheetAt(0).createDrawingPatriarch(); |
| // prior to the fix the line below failed with |
| // java.lang.IllegalStateException: EOF - next record not available |
| wb.cloneSheet(0); |
| }); |
| } |
| |
| @Test |
| void bug49612_part() throws IOException { |
| try (HSSFWorkbook wb = openSampleWorkbook("49612.xls")) { |
| HSSFSheet sh = wb.getSheetAt(0); |
| HSSFRow row = sh.getRow(0); |
| HSSFCell c1 = row.getCell(2); |
| HSSFCell d1 = row.getCell(3); |
| HSSFCell e1 = row.getCell(2); |
| |
| assertEquals("SUM(BOB+JIM)", c1.getCellFormula()); |
| |
| // Problem 1: See TestUnfixedBugs#test49612() |
| // Problem 2: TestUnfixedBugs#test49612() |
| |
| // Problem 3: These used to fail, now pass |
| HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb); |
| assertEquals(30.0, eval.evaluate(c1).getNumberValue(), 0.001, "evaluating c1"); |
| assertEquals(30.0, eval.evaluate(d1).getNumberValue(), 0.001, "evaluating d1"); |
| assertEquals(30.0, eval.evaluate(e1).getNumberValue(), 0.001, "evaluating e1"); |
| } |
| } |
| |
| @Test |
| void bug51675() throws Exception { |
| try (HSSFWorkbook wb = openSampleWorkbook("51675.xls")) { |
| HSSFSheet sh = wb.getSheetAt(0); |
| InternalSheet ish = HSSFTestHelper.getSheetForTest(sh); |
| PageSettingsBlock psb = (PageSettingsBlock) ish.getRecords().get(13); |
| List<Short> list = new ArrayList<>(); |
| psb.visitContainedRecords(r -> list.add(r.getSid())); |
| assertEquals(UnknownRecord.BITMAP_00E9, list.get(list.size() - 1).intValue()); |
| assertEquals(UnknownRecord.HEADER_FOOTER_089C, list.get(list.size() - 2).intValue()); |
| } |
| } |
| |
| @Test |
| void bug52272() throws IOException { |
| try (HSSFWorkbook wb = new HSSFWorkbook()) { |
| HSSFSheet sh = wb.createSheet(); |
| HSSFPatriarch p = sh.createDrawingPatriarch(); |
| |
| HSSFSimpleShape s = p.createSimpleShape(new HSSFClientAnchor()); |
| s.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE); |
| |
| HSSFSheet sh2 = wb.cloneSheet(0); |
| assertNotNull(sh2.getDrawingPatriarch()); |
| } |
| } |
| |
| @Test |
| void bug53432() throws IOException { |
| try (HSSFWorkbook wb1 = new HSSFWorkbook()) { |
| wb1.addPicture(new byte[]{123, 22}, Workbook.PICTURE_TYPE_JPEG); |
| assertEquals(1, wb1.getAllPictures().size()); |
| } |
| try (HSSFWorkbook wb1 = new HSSFWorkbook()) { |
| try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { |
| assertEquals(0, wb2.getAllPictures().size()); |
| wb2.addPicture(new byte[]{123, 22}, Workbook.PICTURE_TYPE_JPEG); |
| assertEquals(1, wb2.getAllPictures().size()); |
| |
| try (HSSFWorkbook wb3 = writeOutAndReadBack(wb2)) { |
| assertEquals(1, wb3.getAllPictures().size()); |
| } |
| } |
| } |
| } |
| |
| @Test |
| void bug46250() throws Exception { |
| try (HSSFWorkbook wb = openSampleWorkbook("46250.xls")) { |
| Sheet sh = wb.getSheet("Template"); |
| Sheet cSh = wb.cloneSheet(wb.getSheetIndex(sh)); |
| int sIdx = wb.getSheetIndex(cSh); |
| |
| HSSFPatriarch patriarch = (HSSFPatriarch) cSh.createDrawingPatriarch(); |
| HSSFTextbox tb = (HSSFTextbox) patriarch.getChildren().get(2); |
| |
| tb.setString(new HSSFRichTextString("POI test")); |
| tb.setAnchor(new HSSFClientAnchor(0, 0, 0, 0, (short) 0, 0, (short) 10, 10)); |
| |
| try (HSSFWorkbook wb2 = writeOutAndReadBack(wb)) { |
| HSSFSheet sh2 = wb2.getSheetAt(sIdx); |
| assertNotNull(sh2); |
| assertNotNull(sh2.getDrawingPatriarch()); |
| } |
| } |
| } |
| |
| @Test |
| void bug53404() throws Exception { |
| try (HSSFWorkbook wb = openSampleWorkbook("53404.xls")) { |
| Sheet sheet = wb.getSheet("test-sheet"); |
| int rowCount = sheet.getLastRowNum() + 1; |
| int newRows = 5; |
| Calendar cal = LocaleUtil.getLocaleCalendar(); |
| for (int r = rowCount; r < rowCount + newRows; r++) { |
| Row row = sheet.createRow((short) r); |
| row.createCell(0).setCellValue(1.03 * (r + 7)); |
| row.createCell(1).setCellValue(cal.getTime()); |
| row.createCell(2).setCellValue(cal); |
| row.createCell(3).setCellValue(String.format(Locale.ROOT, "row:%d/col:%d", r, 3)); |
| row.createCell(4).setCellValue(true); |
| row.createCell(5).setCellErrorValue(FormulaError.NUM.getCode()); |
| row.createCell(6).setCellValue("added cells."); |
| } |
| |
| try (HSSFWorkbook wb2 = writeOutAndReadBack(wb)) { |
| Sheet sheet2 = wb2.getSheet("test-sheet"); |
| Row row2 = sheet2.getRow(5); |
| assertNotNull(row2); |
| assertEquals(cal.getTime(),row2.getCell(2).getDateCellValue()); |
| } |
| } |
| } |
| |
| /** |
| * Row style information is 12 not 16 bits |
| */ |
| @Test |
| void bug49237() throws Exception { |
| try (Workbook wb = openSampleWorkbook("49237.xls")) { |
| Sheet sheet = wb.getSheetAt(0); |
| Row row = sheet.getRow(0); |
| CellStyle rstyle = row.getRowStyle(); |
| assertNotNull(rstyle); |
| assertEquals(BorderStyle.DOUBLE, rstyle.getBorderBottom()); |
| } |
| } |
| |
| /** |
| * POI does now support the RC4 CryptoAPI encryption header structure |
| */ |
| @ParameterizedTest |
| @CsvSource({ |
| "xor-encryption-abc.xls, abc", |
| "35897-type4.xls, freedom" |
| }) |
| void bug35897(String file, String pass) throws Exception { |
| Biff8EncryptionKey.setCurrentUserPassword(pass); |
| try { |
| simpleTest(file, null); |
| } finally { |
| Biff8EncryptionKey.setCurrentUserPassword(null); |
| } |
| } |
| |
| @Test |
| void bug56450() throws Exception { |
| try (HSSFWorkbook wb = openSampleWorkbook("56450.xls")) { |
| HSSFSheet sheet = wb.getSheetAt(0); |
| int comments = 0; |
| for (Row r : sheet) { |
| for (Cell c : r) { |
| if (c.getCellComment() != null) { |
| assertNotNull(c.getCellComment().getString().getString()); |
| comments++; |
| } |
| } |
| } |
| assertEquals(0, comments); |
| } |
| } |
| |
| /** |
| * Files initially created with Excel 2010 can have >3 CF rules |
| */ |
| @Test |
| void bug56482() throws Exception { |
| try (HSSFWorkbook wb = openSampleWorkbook("56482.xls")) { |
| assertEquals(1, wb.getNumberOfSheets()); |
| |
| HSSFSheet sheet = wb.getSheetAt(0); |
| HSSFSheetConditionalFormatting cf = sheet.getSheetConditionalFormatting(); |
| |
| assertEquals(5, cf.getNumConditionalFormattings()); |
| } |
| } |
| |
| @Test |
| void bug56325() throws IOException { |
| try (HSSFWorkbook wb1 = openSampleWorkbook("56325.xls")) { |
| assertEquals(3, wb1.getNumberOfSheets()); |
| wb1.removeSheetAt(0); |
| assertEquals(2, wb1.getNumberOfSheets()); |
| |
| try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { |
| assertEquals(2, wb2.getNumberOfSheets()); |
| wb2.removeSheetAt(0); |
| assertEquals(1, wb2.getNumberOfSheets()); |
| wb2.removeSheetAt(0); |
| assertEquals(0, wb2.getNumberOfSheets()); |
| |
| try (HSSFWorkbook wb3 = writeOutAndReadBack(wb2)) { |
| assertEquals(0, wb3.getNumberOfSheets()); |
| } |
| } |
| } |
| } |
| |
| @Test |
| void bug56325a() throws IOException { |
| try (HSSFWorkbook wb1 = openSampleWorkbook("56325a.xls")) { |
| |
| HSSFSheet sheet = wb1.cloneSheet(2); |
| wb1.setSheetName(3, "Clone 1"); |
| sheet.setRepeatingRows(CellRangeAddress.valueOf("2:3")); |
| wb1.setPrintArea(3, "$A$4:$C$10"); |
| |
| sheet = wb1.cloneSheet(2); |
| wb1.setSheetName(4, "Clone 2"); |
| sheet.setRepeatingRows(CellRangeAddress.valueOf("2:3")); |
| wb1.setPrintArea(4, "$A$4:$C$10"); |
| |
| wb1.removeSheetAt(2); |
| |
| try (Workbook wb2 = writeOutAndReadBack(wb1)) { |
| assertEquals(4, wb2.getNumberOfSheets()); |
| } |
| } |
| } |
| |
| /** |
| * Formulas which reference named ranges, either in other |
| * sheets, or workbook scoped but in other workbooks. |
| * Used to fail with |
| * java.lang.RuntimeException: Unexpected eval class (org.apache.poi.ss.formula.eval.NameXEval) |
| */ |
| @Test |
| void bug56737() throws IOException { |
| try (Workbook wb = openSampleWorkbook("56737.xls")) { |
| |
| // Check the named range definitions |
| Name nSheetScope = wb.getName("NR_To_A1"); |
| Name nWBScope = wb.getName("NR_Global_B2"); |
| |
| assertNotNull(nSheetScope); |
| assertNotNull(nWBScope); |
| |
| assertEquals("Defines!$A$1", nSheetScope.getRefersToFormula()); |
| assertEquals("Defines!$B$2", nWBScope.getRefersToFormula()); |
| |
| // Check the different kinds of formulas |
| Sheet s = wb.getSheetAt(0); |
| Cell cRefSName = s.getRow(1).getCell(3); |
| Cell cRefWName = s.getRow(2).getCell(3); |
| |
| assertEquals("Defines!NR_To_A1", cRefSName.getCellFormula()); |
| |
| // TODO Correct this, so that the filename is shown too, see bug #56742 |
| // This is what Excel itself shows |
| //assertEquals("'56737.xls'!NR_Global_B2", cRefWName.getCellFormula()); |
| // TODO This isn't right, but it's what we currently generate.... |
| assertEquals("NR_Global_B2", cRefWName.getCellFormula()); |
| |
| // Try to evaluate them |
| FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator(); |
| assertEquals("Test A1", eval.evaluate(cRefSName).getStringValue()); |
| assertEquals(142, (int) eval.evaluate(cRefWName).getNumberValue()); |
| |
| // Try to evaluate everything |
| eval.evaluateAll(); |
| } |
| } |
| |
| /** |
| * ClassCastException in HSSFOptimiser - StyleRecord cannot be cast to |
| * ExtendedFormatRecord when removing un-used styles |
| */ |
| @Test |
| void bug54443() throws Exception { |
| try (HSSFWorkbook workbook = new HSSFWorkbook()) { |
| HSSFCellStyle style = workbook.createCellStyle(); |
| HSSFCellStyle newStyle = workbook.createCellStyle(); |
| |
| HSSFSheet mySheet = workbook.createSheet(); |
| HSSFRow row = mySheet.createRow(0); |
| HSSFCell cell = row.createCell(0); |
| |
| // Use style |
| cell.setCellStyle(style); |
| // Switch to newStyle, style is now un-used |
| cell.setCellStyle(newStyle); |
| |
| // Optimize |
| int nbrOfCellStyles = workbook.getNumCellStyles(); |
| HSSFOptimiser.optimiseCellStyles(workbook); |
| assertEquals(nbrOfCellStyles-1, workbook.getNumCellStyles()); |
| } |
| } |
| |
| /** |
| * Intersection formula ranges, eg =(C2:D3 D3:E4) |
| */ |
| @Test |
| void bug52111() throws Exception { |
| try (Workbook wb = openSampleWorkbook("Intersection-52111.xls")) { |
| Sheet s = wb.getSheetAt(0); |
| assertFormula(wb, s.getRow(2).getCell(0), "(C2:D3 D3:E4)", "4.0"); |
| assertFormula(wb, s.getRow(6).getCell(0), "Tabelle2!E:E Tabelle2!$A11:$IV11", "5.0"); |
| assertFormula(wb, s.getRow(8).getCell(0), "Tabelle2!E:F Tabelle2!$A11:$IV12", null); |
| } |
| } |
| |
| @Test |
| void bug42016() throws Exception { |
| try (Workbook wb = openSampleWorkbook("42016.xls")) { |
| Sheet s = wb.getSheetAt(0); |
| for (int row = 0; row < 7; row++) { |
| assertEquals("A$1+B$1", s.getRow(row).getCell(2).getCellFormula()); |
| } |
| } |
| } |
| |
| /** |
| * Unexpected record type (org.apache.poi.hssf.record.ColumnInfoRecord) |
| */ |
| @Test |
| void bug53984() throws Exception { |
| try (Workbook wb = openSampleWorkbook("53984.xls")) { |
| Sheet s = wb.getSheetAt(0); |
| assertEquals("International Communication Services SA", s.getRow(2).getCell(0).getStringCellValue()); |
| assertEquals("Saudi Arabia-Riyadh", s.getRow(210).getCell(0).getStringCellValue()); |
| } |
| } |
| |
| /** |
| * Read, write, read for formulas point to cells in other files. |
| * See bug46670() for the main test, this just |
| * covers reading an existing file and checking it. |
| * |
| * See base-test-class for some related tests that still fail |
| */ |
| @Test |
| void bug46670_existing() throws Exception { |
| // Expected values |
| String refLocal = "'[refs" + File.separator + "airport.xls]Sheet1'!$A$2"; |
| String refHttp = "'[9http://www.principlesofeconometrics.com/excel/airline.xls]Sheet1'!$A$2"; |
| |
| // Check we can read them correctly |
| simpleTest("46670_local.xls", |
| wb -> assertEquals(refLocal, wb.getSheetAt(0).getRow(0).getCell(0).getCellFormula())); |
| |
| simpleTest("46670_http.xls", |
| wb -> assertEquals(refHttp, wb.getSheetAt(0).getRow(0).getCell(0).getCellFormula())); |
| |
| // Now try to set them to the same values, and ensure that |
| // they end up as they did before, even with a save and re-load |
| try (HSSFWorkbook wb3 = openSampleWorkbook("46670_local.xls")) { |
| Sheet s = wb3.getSheetAt(0); |
| Cell c = s.getRow(0).getCell(0); |
| c.setCellFormula(refLocal); |
| assertEquals(refLocal, c.getCellFormula()); |
| |
| try (HSSFWorkbook wb4 = writeOutAndReadBack(wb3)) { |
| s = wb4.getSheetAt(0); |
| assertEquals(refLocal, s.getRow(0).getCell(0).getCellFormula()); |
| } |
| } |
| |
| try (HSSFWorkbook wb5 = openSampleWorkbook("46670_http.xls")) { |
| Sheet s = wb5.getSheetAt(0); |
| Cell c = s.getRow(0).getCell(0); |
| c.setCellFormula(refHttp); |
| assertEquals(refHttp, c.getCellFormula()); |
| |
| try (Workbook wb6 = writeOutAndReadBack(wb5)) { |
| s = wb6.getSheetAt(0); |
| assertEquals(refHttp, s.getRow(0).getCell(0).getCellFormula()); |
| } |
| } |
| } |
| |
| @Test |
| void test57163() throws IOException { |
| simpleTest("57163.xls", wb -> { |
| while (wb.getNumberOfSheets() > 1) { |
| wb.removeSheetAt(1); |
| } |
| }); |
| } |
| |
| @Test |
| void test48043() throws IOException { |
| try (HSSFWorkbook wb = openSampleWorkbook("56325a.xls")) { |
| |
| wb.removeSheetAt(2); |
| wb.removeSheetAt(1); |
| |
| //Sheet s = wb.createSheet("sheetname"); |
| Sheet s = wb.getSheetAt(0); |
| Row row = s.createRow(0); |
| Cell cell = row.createCell(0); |
| |
| cell.setCellFormula( |
| "IF(AND(ISBLANK(A10)," + |
| "ISBLANK(B10)),\"\"," + |
| "CONCATENATE(A10,\"-\",B10))"); |
| |
| FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator(); |
| |
| eval.evaluateAll(); |
| try (Workbook wbBack = writeOutAndReadBack(wb)) { |
| assertNotNull(wbBack); |
| } |
| } |
| } |
| |
| @Test |
| void test57925() throws IOException { |
| try (Workbook wb = openSampleWorkbook("57925.xls")) { |
| wb.getCreationHelper().createFormulaEvaluator().evaluateAll(); |
| DataFormatter df = new DataFormatter(); |
| for (Sheet sheet : wb) { |
| for (Row row : sheet) { |
| for (Cell cell : row) { |
| assertNotNull(df.formatCellValue(cell)); |
| } |
| } |
| } |
| } |
| } |
| |
| @Test |
| void test46515() throws IOException { |
| try (Workbook wb = openSampleWorkbook("46515.xls")) { |
| |
| // Get structure from webservice |
| String urlString = "https://poi.apache.org/components/spreadsheet/images/calendar.jpg"; |
| URL structURL = new URL(urlString); |
| BufferedImage bimage; |
| try { |
| bimage = ImageIO.read(structURL); |
| } catch (IOException e) { |
| Assumptions.assumeFalse(true, "Downloading a jpg from poi.apache.org should work"); |
| return; |
| } |
| |
| // Convert BufferedImage to byte[] |
| UnsynchronizedByteArrayOutputStream imageBAOS = new UnsynchronizedByteArrayOutputStream(); |
| ImageIO.write(bimage, "jpeg", imageBAOS); |
| imageBAOS.flush(); |
| byte[] imageBytes = imageBAOS.toByteArray(); |
| imageBAOS.close(); |
| |
| // Pop structure into Structure HSSFSheet |
| int pict = wb.addPicture(imageBytes, HSSFWorkbook.PICTURE_TYPE_JPEG); |
| Sheet sheet = wb.getSheet("Structure"); |
| assertNotNull(sheet, "Did not find sheet"); |
| HSSFPatriarch patriarch = (HSSFPatriarch) sheet.createDrawingPatriarch(); |
| HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 10, 22); |
| anchor.setAnchorType(AnchorType.MOVE_DONT_RESIZE); |
| patriarch.createPicture(anchor, pict); |
| } |
| } |
| |
| @Test |
| void test55668() throws IOException { |
| try (Workbook wb = openSampleWorkbook("55668.xls")) { |
| |
| Sheet sheet = wb.getSheetAt(0); |
| Row row = sheet.getRow(0); |
| Cell cell = row.getCell(0); |
| assertEquals(CellType.FORMULA, cell.getCellType()); |
| assertEquals("IF(TRUE,\"\",\"\")", cell.getCellFormula()); |
| assertEquals("", cell.getStringCellValue()); |
| //noinspection deprecation |
| cell.setCellType(CellType.STRING); |
| |
| assertEquals(CellType.BLANK, cell.getCellType()); |
| assertEquals("", cell.getStringCellValue()); |
| |
| assertThrows(IllegalStateException.class, cell::getCellFormula); |
| } |
| } |
| |
| @Test |
| void test55982() throws IOException { |
| simpleTest("55982.xls", wb -> assertNotNull(wb.cloneSheet(1))); |
| } |
| |
| /** |
| * Test generator of ids for the CommonObjectDataSubRecord record. |
| */ |
| @Test |
| void test51332() { |
| HSSFClientAnchor anchor = new HSSFClientAnchor(); |
| HSSFSimpleShape shape; |
| CommonObjectDataSubRecord cmo; |
| |
| shape = new HSSFTextbox(null, anchor); |
| shape.setShapeId(1025); |
| cmo = (CommonObjectDataSubRecord) shape.getObjRecord().getSubRecords().get(0); |
| assertEquals(1, cmo.getObjectId()); |
| |
| shape = new HSSFPicture(null, anchor); |
| shape.setShapeId(1026); |
| cmo = (CommonObjectDataSubRecord) shape.getObjRecord().getSubRecords().get(0); |
| assertEquals(2, cmo.getObjectId()); |
| |
| shape = new HSSFComment(null, anchor); |
| shape.setShapeId(1027); |
| cmo = (CommonObjectDataSubRecord) shape.getObjRecord().getSubRecords().get(0); |
| assertEquals(1027, cmo.getObjectId()); |
| } |
| |
| // As of POI 3.15 beta 2, LibreOffice does not display the diagonal border while it does display the bottom border |
| // I have not checked Excel to know if this is a LibreOffice or a POI problem. |
| @Test |
| void test53564() throws IOException { |
| try (HSSFWorkbook wb = new HSSFWorkbook()) { |
| HSSFSheet sheet = wb.createSheet("Page 1"); |
| final short BLUE = 30; |
| |
| HSSFSheetConditionalFormatting scf = sheet.getSheetConditionalFormatting(); |
| HSSFConditionalFormattingRule rule = scf.createConditionalFormattingRule(ComparisonOperator.GT, "10"); |
| |
| HSSFBorderFormatting bord = rule.createBorderFormatting(); |
| bord.setBorderDiagonal(BorderStyle.THICK); |
| assertEquals(BorderStyle.THICK, bord.getBorderDiagonal()); |
| |
| bord.setBackwardDiagonalOn(true); |
| assertTrue(bord.isBackwardDiagonalOn()); |
| |
| bord.setForwardDiagonalOn(true); |
| assertTrue(bord.isForwardDiagonalOn()); |
| |
| bord.setDiagonalBorderColor(BLUE); |
| assertEquals(BLUE, bord.getDiagonalBorderColor()); |
| |
| // Create the bottom border style so we know what a border is supposed to look like |
| bord.setBorderBottom(BorderStyle.THICK); |
| assertEquals(BorderStyle.THICK, bord.getBorderBottom()); |
| bord.setBottomBorderColor(BLUE); |
| assertEquals(BLUE, bord.getBottomBorderColor()); |
| |
| CellRangeAddress[] A2_D4 = {new CellRangeAddress(1, 3, 0, 3)}; |
| scf.addConditionalFormatting(A2_D4, rule); |
| |
| // Set a cell value within the conditional formatting range whose rule would resolve to True. |
| Cell C3 = sheet.createRow(2).createCell(2); |
| C3.setCellValue(30.0); |
| } |
| } |
| |
| @Test |
| void test61287() throws IOException { |
| try (HSSFWorkbook wb = openSampleWorkbook("61287.xls"); |
| ExcelExtractor ex = new ExcelExtractor(wb)) { |
| String text = ex.getText(); |
| assertContains(text, "\u8D44\u4EA7\u8D1F\u503A\u8868"); |
| } |
| } |
| |
| @Test |
| void test61300() throws Exception { |
| try (POIFSFileSystem poifs = new POIFSFileSystem(HSSFTestDataSamples.openSampleFileStream("61300.xls"))) { |
| |
| DocumentEntry entry = |
| (DocumentEntry) poifs.getRoot().getEntry(SummaryInformation.DEFAULT_STREAM_NAME); |
| |
| RuntimeException ex = assertThrows( |
| RuntimeException.class, |
| () -> new PropertySet(new DocumentInputStream(entry)) |
| ); |
| assertEquals("Can't read negative number of bytes, but had: -218103608", ex.getMessage()); |
| } |
| } |
| |
| @Test |
| void test51262() throws IOException { |
| try (HSSFWorkbook wb = openSampleWorkbook("51262.xls")) { |
| Sheet sheet = wb.getSheetAt(0); |
| Row row = sheet.getRow(2); |
| |
| Cell cell = row.getCell(1); |
| CellStyle style = cell.getCellStyle(); |
| assertEquals(26, style.getFontIndex()); |
| |
| row = sheet.getRow(3); |
| cell = row.getCell(1); |
| style = cell.getCellStyle(); |
| assertEquals(28, style.getFontIndex()); |
| |
| // check the two fonts |
| HSSFFont font = wb.getFontAt(26); |
| assertTrue(font.getBold()); |
| assertEquals(10, font.getFontHeightInPoints()); |
| assertEquals("\uFF2D\uFF33 \uFF30\u30B4\u30B7\u30C3\u30AF", font.getFontName()); |
| |
| font = wb.getFontAt(28); |
| assertTrue(font.getBold()); |
| assertEquals(10, font.getFontHeightInPoints()); |
| assertEquals("\uFF2D\uFF33 \uFF30\u30B4\u30B7\u30C3\u30AF", font.getFontName()); |
| } |
| } |
| |
| @Test |
| void test60460() throws IOException { |
| try (final Workbook wb = openSampleWorkbook("60460.xls")) { |
| assertEquals(2, wb.getAllNames().size()); |
| |
| Name rangedName = wb.getAllNames().get(0); |
| assertFalse(rangedName.isFunctionName()); |
| assertEquals("'[\\\\HEPPC3\\gt$\\Teaching\\Syn\\physyn.xls]#REF'!$AK$70:$AL$70", |
| // replace '/' to make test work equally on Windows and Linux |
| rangedName.getRefersToFormula().replace("/", "\\")); |
| |
| rangedName = wb.getAllNames().get(1); |
| assertFalse(rangedName.isFunctionName()); |
| assertEquals("Questionnaire!$A$1:$L$65", rangedName.getRefersToFormula()); |
| } |
| } |
| |
| @Test |
| void test63819() throws IOException { |
| LocaleUtil.setUserLocale(Locale.UK); |
| try { |
| simpleTest("63819.xls", null); |
| } finally { |
| LocaleUtil.resetUserLocale(); |
| } |
| } |
| |
| /** |
| * Test that VALUE behaves properly as array function and its result is handled by aggregate function |
| */ |
| @Test |
| void testValueAsArrayFunction() throws IOException { |
| try (final Workbook wb = openSampleWorkbook("TestValueAsArrayFunction.xls")) { |
| wb.getCreationHelper().createFormulaEvaluator().evaluateAll(); |
| Sheet sheet = wb.getSheetAt(0); |
| Row row = sheet.getRow(0); |
| Cell cell = row.getCell(0); |
| assertEquals(6.0, cell.getNumericCellValue(), 0.0); |
| } |
| } |
| |
| // a simple test which rewrites the file once and evaluates its formulas |
| @ParameterizedTest |
| @CsvSource({ |
| "15228.xls", "13796.xls", "14460.xls", "14330-1.xls", "14330-2.xls", "22742.xls", "12561-1.xls", "12561-2.xls", |
| "12843-1.xls", "12843-2.xls", "13224.xls", "19599-1.xls", "19599-2.xls", "32822.xls", "15573.xls", |
| "33082.xls", "34775.xls", "37630.xls", "25183.xls", "26100.xls", "27933.xls", "29675.xls", "29982.xls", |
| "31749.xls", "37376.xls", "SimpleWithAutofilter.xls", "44201.xls", "37684-1.xls", "37684-2.xls", |
| "41139.xls", "ex42564-21435.xls", "ex42564-21503.xls", "28774.xls", "44891.xls", "44235.xls", "36947.xls", |
| "39634.xls", "47701.xls", "48026.xls", "47251.xls", "47251_1.xls", "50020.xls", "50426.xls", "50779_1.xls", |
| "50779_2.xls", "51670.xls", "54016.xls", "57456.xls", "53109.xls", "com.aida-tour.www_SPO_files_maldives%20august%20october.xls", |
| "named-cell-in-formula-test.xls", "named-cell-test.xls", "bug55505.xls", "SUBSTITUTE.xls", "64261.xls" |
| }) |
| void simpleTest(String fileName) throws IOException { |
| simpleTest(fileName, null); |
| } |
| |
| @SuppressWarnings("unchecked") |
| @ParameterizedTest |
| @CsvSource({ |
| "46904.xls, org.apache.poi.hssf.OldExcelFormatException, The supplied spreadsheet seems to be Excel", |
| "51832.xls, org.apache.poi.EncryptedDocumentException, Default password is invalid for salt/verifier/verifierHash" |
| }) |
| void simpleTest(String fileName, String exClazz, String exMessage) throws ClassNotFoundException { |
| Class<? extends Exception> ex = (Class<? extends Exception>)Class.forName(exClazz); |
| Exception e = assertThrows(ex, () -> simpleTest(fileName, null)); |
| assertTrue(e.getMessage().startsWith(exMessage)); |
| } |
| |
| private void simpleTest(String fileName, Consumer<HSSFWorkbook> addTest) throws IOException { |
| try (HSSFWorkbook wb1 = openSampleWorkbook(fileName)) { |
| if (addTest != null) { |
| addTest.accept(wb1); |
| } |
| |
| HSSFSheet s = wb1.getSheetAt(0); |
| HSSFRow r = s.createRow(10_000); |
| HSSFCell c = r.createCell(0); |
| c.setCellValue(10); |
| |
| HSSFSheet tmpSheet = wb1.createSheet("POITESTSHEET"); |
| tmpSheet.createRow(10).createCell(10).setCellValue("Test"); |
| wb1.removeSheetAt(wb1.getSheetIndex(tmpSheet)); |
| |
| simpleTestHelper(wb1, fileName); |
| |
| try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { |
| if (addTest != null) { |
| addTest.accept(wb2); |
| } |
| |
| double act = wb2.getSheetAt(0).getRow(10_000).getCell(0).getNumericCellValue(); |
| assertEquals(10, act, 0); |
| simpleTestHelper(wb2, fileName); |
| } |
| } |
| } |
| |
| private void simpleTestHelper(HSSFWorkbook wb, String fileName) { |
| List<String> files = new ArrayList<>(); |
| files.add(fileName); |
| files.addAll(SIMPLE_REFS.keySet()); |
| List<HSSFFormulaEvaluator> evals = new ArrayList<>(); |
| evals.add(wb.getCreationHelper().createFormulaEvaluator()); |
| evals.addAll(SIMPLE_REFS.values()); |
| |
| try { |
| HSSFFormulaEvaluator.setupEnvironment(files.toArray(new String[0]), evals.toArray(new HSSFFormulaEvaluator[0])); |
| evals.get(0).evaluateAll(); |
| } catch (RuntimeException e) { |
| throw new RuntimeException("While handling files " + files + " and evals " + evals, e); |
| } |
| } |
| |
| @Test |
| void test52447() throws IOException { |
| try (Workbook wb = openSampleWorkbook("52447.xls")) { |
| assertNotNull(wb); |
| } |
| } |
| } |