| /* ==================================================================== |
| 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.usermodel; |
| |
| import static org.junit.Assert.assertEquals; |
| import static org.junit.Assert.assertFalse; |
| import static org.junit.Assert.assertNotNull; |
| import static org.junit.Assert.assertNull; |
| import static org.junit.Assert.assertTrue; |
| import static org.junit.Assert.fail; |
| |
| import java.io.IOException; |
| import java.util.Arrays; |
| import java.util.List; |
| |
| import org.apache.poi.ss.ITestDataProvider; |
| import org.apache.poi.ss.util.AreaReference; |
| import org.apache.poi.ss.util.CellReference; |
| import org.apache.poi.util.IOUtils; |
| import org.junit.Test; |
| |
| /** |
| * Tests of implementations of {@link org.apache.poi.ss.usermodel.Name}. |
| * |
| * @author Yegor Kozlov |
| */ |
| public abstract class BaseTestNamedRange { |
| |
| private final ITestDataProvider _testDataProvider; |
| |
| protected BaseTestNamedRange(ITestDataProvider testDataProvider) { |
| _testDataProvider = testDataProvider; |
| } |
| |
| @Test |
| public final void testCreate() throws Exception { |
| // Create a new workbook |
| Workbook wb = _testDataProvider.createWorkbook(); |
| wb.createSheet("Test1"); |
| wb.createSheet("Testing Named Ranges"); |
| |
| Name name1 = wb.createName(); |
| name1.setNameName("testOne"); |
| |
| //setting a duplicate name should throw IllegalArgumentException |
| Name name2 = wb.createName(); |
| try { |
| name2.setNameName("testOne"); |
| fail("expected exception"); |
| } catch (IllegalArgumentException e){ |
| assertEquals("The workbook already contains this name: testOne", e.getMessage()); |
| } |
| //the check for duplicates is case-insensitive |
| try { |
| name2.setNameName("TESTone"); |
| fail("expected exception"); |
| } catch (IllegalArgumentException e){ |
| assertEquals("The workbook already contains this name: TESTone", e.getMessage()); |
| } |
| |
| name2.setNameName("testTwo"); |
| |
| String ref1 = "Test1!$A$1:$B$1"; |
| name1.setRefersToFormula(ref1); |
| assertEquals(ref1, name1.getRefersToFormula()); |
| assertEquals("Test1", name1.getSheetName()); |
| |
| String ref2 = "'Testing Named Ranges'!$A$1:$B$1"; |
| name1.setRefersToFormula(ref2); |
| assertEquals("'Testing Named Ranges'!$A$1:$B$1", name1.getRefersToFormula()); |
| assertEquals("Testing Named Ranges", name1.getSheetName()); |
| |
| assertEquals(-1, name1.getSheetIndex()); |
| name1.setSheetIndex(-1); |
| assertEquals(-1, name1.getSheetIndex()); |
| try { |
| name1.setSheetIndex(2); |
| fail("should throw IllegalArgumentException"); |
| } catch(IllegalArgumentException e){ |
| assertEquals("Sheet index (2) is out of range (0..1)", e.getMessage()); |
| } |
| |
| name1.setSheetIndex(1); |
| assertEquals(1, name1.getSheetIndex()); |
| |
| //-1 means the name applies to the entire workbook |
| name1.setSheetIndex(-1); |
| assertEquals(-1, name1.getSheetIndex()); |
| |
| //names cannot be blank and must begin with a letter or underscore and not contain spaces |
| String[] invalidNames = {"", "123", "1Name", "Named Range"}; |
| for (String name : invalidNames) { |
| try { |
| name1.setNameName(name); |
| fail("should have thrown exceptiuon due to invalid name: " + name); |
| } catch (IllegalArgumentException e) { |
| // expected during successful test |
| } |
| } |
| |
| wb.close(); |
| } |
| |
| @Test |
| public final void testUnicodeNamedRange() throws Exception { |
| Workbook wb1 = _testDataProvider.createWorkbook(); |
| wb1.createSheet("Test"); |
| Name name = wb1.createName(); |
| name.setNameName("\u03B1"); |
| name.setRefersToFormula("Test!$D$3:$E$8"); |
| |
| |
| Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1); |
| Name name2 = wb2.getNameAt(0); |
| |
| assertEquals("\u03B1", name2.getNameName()); |
| assertEquals("Test!$D$3:$E$8", name2.getRefersToFormula()); |
| |
| wb2.close(); |
| wb1.close(); |
| } |
| |
| @Test |
| public final void testAddRemove() throws Exception { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| assertEquals(0, wb.getNumberOfNames()); |
| Name name1 = wb.createName(); |
| name1.setNameName("name1"); |
| assertEquals(1, wb.getNumberOfNames()); |
| |
| Name name2 = wb.createName(); |
| name2.setNameName("name2"); |
| assertEquals(2, wb.getNumberOfNames()); |
| |
| Name name3 = wb.createName(); |
| name3.setNameName("name3"); |
| assertEquals(3, wb.getNumberOfNames()); |
| |
| wb.removeName("name2"); |
| assertEquals(2, wb.getNumberOfNames()); |
| |
| wb.removeName(0); |
| assertEquals(1, wb.getNumberOfNames()); |
| |
| wb.close(); |
| } |
| |
| @Test |
| public final void testScope() throws Exception { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| wb.createSheet(); |
| wb.createSheet(); |
| |
| Name name; |
| |
| name = wb.createName(); |
| name.setNameName("aaa"); |
| name = wb.createName(); |
| try { |
| name.setNameName("aaa"); |
| fail("Expected exception"); |
| } catch(Exception e){ |
| assertEquals("The workbook already contains this name: aaa", e.getMessage()); |
| } |
| |
| name = wb.createName(); |
| name.setSheetIndex(0); |
| name.setNameName("aaa"); |
| name = wb.createName(); |
| name.setSheetIndex(0); |
| try { |
| name.setNameName("aaa"); |
| fail("Expected exception"); |
| } catch(Exception e){ |
| assertEquals("The sheet already contains this name: aaa", e.getMessage()); |
| } |
| |
| name = wb.createName(); |
| name.setSheetIndex(1); |
| name.setNameName("aaa"); |
| name = wb.createName(); |
| name.setSheetIndex(1); |
| try { |
| name.setNameName("aaa"); |
| fail("Expected exception"); |
| } catch(Exception e){ |
| assertEquals("The sheet already contains this name: aaa", e.getMessage()); |
| } |
| |
| assertEquals(3, wb.getNames("aaa").size()); |
| |
| wb.close(); |
| } |
| |
| /** |
| * Test case provided by czhang@cambian.com (Chun Zhang) |
| * <p> |
| * Addresses Bug <a href="http://issues.apache.org/bugzilla/show_bug.cgi?id=13775" target="_bug">#13775</a> |
| */ |
| @Test |
| public final void testMultiNamedRange() throws Exception { |
| |
| // Create a new workbook |
| Workbook wb1 = _testDataProvider.createWorkbook(); |
| |
| // Create a worksheet 'sheet1' in the new workbook |
| wb1.createSheet (); |
| wb1.setSheetName (0, "sheet1"); |
| |
| // Create another worksheet 'sheet2' in the new workbook |
| wb1.createSheet (); |
| wb1.setSheetName (1, "sheet2"); |
| |
| // Create a new named range for worksheet 'sheet1' |
| Name namedRange1 = wb1.createName(); |
| |
| // Set the name for the named range for worksheet 'sheet1' |
| namedRange1.setNameName("RangeTest1"); |
| |
| // Set the reference for the named range for worksheet 'sheet1' |
| namedRange1.setRefersToFormula("sheet1" + "!$A$1:$L$41"); |
| |
| // Create a new named range for worksheet 'sheet2' |
| Name namedRange2 = wb1.createName(); |
| |
| // Set the name for the named range for worksheet 'sheet2' |
| namedRange2.setNameName("RangeTest2"); |
| |
| // Set the reference for the named range for worksheet 'sheet2' |
| namedRange2.setRefersToFormula("sheet2" + "!$A$1:$O$21"); |
| |
| // Write the workbook to a file |
| // Read the Excel file and verify its content |
| Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1); |
| Name nm1 = wb2.getName("RangeTest1"); |
| assertTrue("Name is "+nm1.getNameName(),"RangeTest1".equals(nm1.getNameName())); |
| assertTrue("Reference is "+nm1.getRefersToFormula(),(wb2.getSheetName(0)+"!$A$1:$L$41").equals(nm1.getRefersToFormula())); |
| |
| Name nm2 = wb2.getName("RangeTest2"); |
| assertTrue("Name is "+nm2.getNameName(),"RangeTest2".equals(nm2.getNameName())); |
| assertTrue("Reference is "+nm2.getRefersToFormula(),(wb2.getSheetName(1)+"!$A$1:$O$21").equals(nm2.getRefersToFormula())); |
| |
| wb2.close(); |
| wb1.close(); |
| } |
| |
| /** |
| * Test to see if the print areas can be retrieved/created in memory |
| */ |
| @Test |
| public final void testSinglePrintArea() throws Exception { |
| Workbook workbook = _testDataProvider.createWorkbook(); |
| workbook.createSheet("Test Print Area"); |
| String sheetName = workbook.getSheetName(0); |
| |
| String reference = "$A$1:$B$1"; |
| workbook.setPrintArea(0, reference); |
| |
| String retrievedPrintArea = workbook.getPrintArea(0); |
| |
| assertNotNull("Print Area not defined for first sheet", retrievedPrintArea); |
| assertEquals("'" + sheetName + "'!$A$1:$B$1", retrievedPrintArea); |
| |
| workbook.close(); |
| } |
| |
| /** |
| * For Convenience, don't force sheet names to be used |
| */ |
| @Test |
| public final void testSinglePrintAreaWOSheet() throws Exception |
| { |
| Workbook workbook = _testDataProvider.createWorkbook(); |
| workbook.createSheet("Test Print Area"); |
| String sheetName = workbook.getSheetName(0); |
| |
| String reference = "$A$1:$B$1"; |
| workbook.setPrintArea(0, reference); |
| |
| String retrievedPrintArea = workbook.getPrintArea(0); |
| |
| assertNotNull("Print Area not defined for first sheet", retrievedPrintArea); |
| assertEquals("'" + sheetName + "'!" + reference, retrievedPrintArea); |
| |
| workbook.close(); |
| } |
| |
| /** |
| * Test to see if the print area made it to the file |
| */ |
| @Test |
| public final void testPrintAreaFile() throws Exception { |
| Workbook wb1 = _testDataProvider.createWorkbook(); |
| wb1.createSheet("Test Print Area"); |
| String sheetName = wb1.getSheetName(0); |
| |
| |
| String reference = "$A$1:$B$1"; |
| wb1.setPrintArea(0, reference); |
| |
| Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1); |
| |
| String retrievedPrintArea = wb2.getPrintArea(0); |
| assertNotNull("Print Area not defined for first sheet", retrievedPrintArea); |
| assertEquals("References Match", "'" + sheetName + "'!$A$1:$B$1", retrievedPrintArea); |
| |
| wb2.close(); |
| wb1.close(); |
| } |
| |
| /** |
| * Test to see if multiple print areas made it to the file |
| */ |
| @Test |
| public final void testMultiplePrintAreaFile() throws Exception { |
| Workbook wb1 = _testDataProvider.createWorkbook(); |
| |
| wb1.createSheet("Sheet1"); |
| wb1.createSheet("Sheet2"); |
| wb1.createSheet("Sheet3"); |
| String reference1 = "$A$1:$B$1"; |
| String reference2 = "$B$2:$D$5"; |
| String reference3 = "$D$2:$F$5"; |
| |
| wb1.setPrintArea(0, reference1); |
| wb1.setPrintArea(1, reference2); |
| wb1.setPrintArea(2, reference3); |
| |
| //Check created print areas |
| String retrievedPrintArea; |
| |
| retrievedPrintArea = wb1.getPrintArea(0); |
| assertNotNull("Print Area Not Found (Sheet 1)", retrievedPrintArea); |
| assertEquals("Sheet1!" + reference1, retrievedPrintArea); |
| |
| retrievedPrintArea = wb1.getPrintArea(1); |
| assertNotNull("Print Area Not Found (Sheet 2)", retrievedPrintArea); |
| assertEquals("Sheet2!" + reference2, retrievedPrintArea); |
| |
| retrievedPrintArea = wb1.getPrintArea(2); |
| assertNotNull("Print Area Not Found (Sheet 3)", retrievedPrintArea); |
| assertEquals("Sheet3!" + reference3, retrievedPrintArea); |
| |
| // Check print areas after re-reading workbook |
| Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1); |
| |
| retrievedPrintArea = wb2.getPrintArea(0); |
| assertNotNull("Print Area Not Found (Sheet 1)", retrievedPrintArea); |
| assertEquals("Sheet1!" + reference1, retrievedPrintArea); |
| |
| retrievedPrintArea = wb2.getPrintArea(1); |
| assertNotNull("Print Area Not Found (Sheet 2)", retrievedPrintArea); |
| assertEquals("Sheet2!" + reference2, retrievedPrintArea); |
| |
| retrievedPrintArea = wb2.getPrintArea(2); |
| assertNotNull("Print Area Not Found (Sheet 3)", retrievedPrintArea); |
| assertEquals("Sheet3!" + reference3, retrievedPrintArea); |
| |
| wb2.close(); |
| wb1.close(); |
| } |
| |
| /** |
| * Tests the setting of print areas with coordinates (Row/Column designations) |
| * |
| */ |
| @Test |
| public final void testPrintAreaCoords() throws Exception { |
| Workbook workbook = _testDataProvider.createWorkbook(); |
| workbook.createSheet("Test Print Area"); |
| String sheetName = workbook.getSheetName(0); |
| |
| workbook.setPrintArea(0, 0, 1, 0, 0); |
| |
| String retrievedPrintArea = workbook.getPrintArea(0); |
| |
| assertNotNull("Print Area not defined for first sheet", retrievedPrintArea); |
| assertEquals("'" + sheetName + "'!$A$1:$B$1", retrievedPrintArea); |
| |
| workbook.close(); |
| } |
| |
| |
| /** |
| * Tests the parsing of union area expressions, and re-display in the presence of sheet names |
| * with special characters. |
| */ |
| @Test |
| public final void testPrintAreaUnion() throws Exception { |
| Workbook workbook = _testDataProvider.createWorkbook(); |
| workbook.createSheet("Test Print Area"); |
| |
| String reference = "$A$1:$B$1,$D$1:$F$2"; |
| workbook.setPrintArea(0, reference); |
| String retrievedPrintArea = workbook.getPrintArea(0); |
| assertNotNull("Print Area not defined for first sheet", retrievedPrintArea); |
| assertEquals("'Test Print Area'!$A$1:$B$1,'Test Print Area'!$D$1:$F$2", retrievedPrintArea); |
| |
| workbook.close(); |
| } |
| |
| /** |
| * Verifies an existing print area is deleted |
| * |
| */ |
| @Test |
| public final void testPrintAreaRemove() throws Exception { |
| Workbook workbook = _testDataProvider.createWorkbook(); |
| workbook.createSheet("Test Print Area"); |
| workbook.getSheetName(0); |
| |
| workbook.setPrintArea(0, 0, 1, 0, 0); |
| |
| String retrievedPrintArea = workbook.getPrintArea(0); |
| |
| assertNotNull("Print Area not defined for first sheet", retrievedPrintArea); |
| |
| workbook.removePrintArea(0); |
| assertNull("PrintArea was not removed", workbook.getPrintArea(0)); |
| workbook.close(); |
| } |
| |
| /** |
| * Test that multiple named ranges can be added written and read |
| */ |
| @Test |
| public final void testMultipleNamedWrite() throws Exception { |
| Workbook wb1 = _testDataProvider.createWorkbook(); |
| |
| |
| wb1.createSheet("testSheet1"); |
| String sheetName = wb1.getSheetName(0); |
| |
| assertEquals("testSheet1", sheetName); |
| |
| //Creating new Named Range |
| Name newNamedRange = wb1.createName(); |
| |
| newNamedRange.setNameName("RangeTest"); |
| newNamedRange.setRefersToFormula(sheetName + "!$D$4:$E$8"); |
| |
| //Creating another new Named Range |
| Name newNamedRange2 = wb1.createName(); |
| |
| newNamedRange2.setNameName("AnotherTest"); |
| newNamedRange2.setRefersToFormula(sheetName + "!$F$1:$G$6"); |
| |
| wb1.getNameAt(0); |
| |
| Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1); |
| Name nm =wb2.getName("RangeTest"); |
| assertTrue("Name is "+nm.getNameName(),"RangeTest".equals(nm.getNameName())); |
| assertTrue("Reference is "+nm.getRefersToFormula(),(wb2.getSheetName(0)+"!$D$4:$E$8").equals(nm.getRefersToFormula())); |
| |
| nm = wb2.getName("AnotherTest"); |
| assertTrue("Name is "+nm.getNameName(),"AnotherTest".equals(nm.getNameName())); |
| assertTrue("Reference is "+nm.getRefersToFormula(),newNamedRange2.getRefersToFormula().equals(nm.getRefersToFormula())); |
| |
| wb2.close(); |
| wb1.close(); |
| } |
| /** |
| * Verifies correct functioning for "single cell named range" (aka "named cell") |
| */ |
| @Test |
| public final void testNamedCell_1() throws Exception { |
| |
| // setup for this testcase |
| String sheetName = "Test Named Cell"; |
| String cellName = "named_cell"; |
| String cellValue = "TEST Value"; |
| Workbook wb = _testDataProvider.createWorkbook(); |
| Sheet sheet = wb.createSheet(sheetName); |
| CreationHelper factory = wb.getCreationHelper(); |
| sheet.createRow(0).createCell(0).setCellValue(factory.createRichTextString(cellValue)); |
| |
| // create named range for a single cell using areareference |
| Name namedCell = wb.createName(); |
| namedCell.setNameName(cellName); |
| String reference = "'" + sheetName + "'" + "!A1:A1"; |
| namedCell.setRefersToFormula(reference); |
| |
| // retrieve the newly created named range |
| Name aNamedCell = wb.getName(cellName); |
| assertNotNull(aNamedCell); |
| |
| // retrieve the cell at the named range and test its contents |
| @SuppressWarnings("deprecation") |
| AreaReference aref = new AreaReference(aNamedCell.getRefersToFormula()); |
| assertTrue("Should be exactly 1 cell in the named cell :'" +cellName+"'", aref.isSingleCell()); |
| |
| CellReference cref = aref.getFirstCell(); |
| assertNotNull(cref); |
| Sheet s = wb.getSheet(cref.getSheetName()); |
| assertNotNull(s); |
| Row r = sheet.getRow(cref.getRow()); |
| Cell c = r.getCell(cref.getCol()); |
| String contents = c.getRichStringCellValue().getString(); |
| assertEquals("Contents of cell retrieved by its named reference", contents, cellValue); |
| |
| wb.close(); |
| } |
| |
| /** |
| * Verifies correct functioning for "single cell named range" (aka "named cell") |
| */ |
| @Test |
| public final void testNamedCell_2() throws Exception { |
| |
| // setup for this testcase |
| String sname = "TestSheet", cname = "TestName", cvalue = "TestVal"; |
| Workbook wb = _testDataProvider.createWorkbook(); |
| CreationHelper factory = wb.getCreationHelper(); |
| Sheet sheet = wb.createSheet(sname); |
| sheet.createRow(0).createCell(0).setCellValue(factory.createRichTextString(cvalue)); |
| |
| // create named range for a single cell using cellreference |
| Name namedCell = wb.createName(); |
| namedCell.setNameName(cname); |
| String reference = sname+"!A1"; |
| namedCell.setRefersToFormula(reference); |
| |
| // retrieve the newly created named range |
| Name aNamedCell = wb.getName(cname); |
| assertNotNull(aNamedCell); |
| |
| // retrieve the cell at the named range and test its contents |
| CellReference cref = new CellReference(aNamedCell.getRefersToFormula()); |
| assertNotNull(cref); |
| Sheet s = wb.getSheet(cref.getSheetName()); |
| assertNotNull(s); |
| Row r = sheet.getRow(cref.getRow()); |
| Cell c = r.getCell(cref.getCol()); |
| String contents = c.getRichStringCellValue().getString(); |
| assertEquals("Contents of cell retrieved by its named reference", contents, cvalue); |
| |
| wb.close(); |
| } |
| |
| |
| /** |
| * Bugzilla attachment 23444 (from bug 46973) has a NAME record with the following encoding: |
| * <pre> |
| * 00000000 | 18 00 17 00 00 00 00 08 00 00 00 00 00 00 00 00 | ................ |
| * 00000010 | 00 00 00 55 50 53 53 74 61 74 65 | ...UPSState |
| * </pre> |
| * |
| * This caused trouble for anything that requires {@link Name#getRefersToFormula()} |
| * It is easy enough to re-create the the same data (by not setting the formula). Excel |
| * seems to gracefully remove this uninitialized name record. It would be nice if POI |
| * could do the same, but that would involve adjusting subsequent name indexes across |
| * all formulas. <p/> |
| * |
| * For the moment, POI has been made to behave more sensibly with uninitialized name |
| * records. |
| */ |
| @Test |
| public final void testUninitialisedNameGetRefersToFormula_bug46973() throws Exception { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| Name n = wb.createName(); |
| n.setNameName("UPSState"); |
| String formula = n.getRefersToFormula(); |
| |
| // bug 46973: fails here with IllegalArgumentException |
| // ptgs must not be null |
| |
| assertNull(formula); |
| // according to exact definition of isDeleted() |
| assertFalse(n.isDeleted()); |
| wb.close(); |
| } |
| |
| @Test |
| public final void testDeletedCell() throws Exception { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| Name n = wb.createName(); |
| n.setNameName("MyName"); |
| // contrived example to expose bug: |
| n.setRefersToFormula("if(A1,\"#REF!\", \"\")"); |
| |
| assertFalse("Identified bug in recoginising formulas referring to deleted cells", n.isDeleted()); |
| |
| wb.close(); |
| } |
| |
| @Test |
| public final void testFunctionNames() throws Exception { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| Name n = wb.createName(); |
| assertFalse(n.isFunctionName()); |
| |
| n.setFunction(false); |
| assertFalse(n.isFunctionName()); |
| |
| n.setFunction(true); |
| assertTrue(n.isFunctionName()); |
| |
| n.setFunction(false); |
| assertFalse(n.isFunctionName()); |
| |
| wb.close(); |
| } |
| |
| @Test |
| public final void testDefferedSetting() throws Exception { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| Name n1 = wb.createName(); |
| assertNull(n1.getRefersToFormula()); |
| assertEquals("", n1.getNameName()); |
| |
| Name n2 = wb.createName(); |
| assertNull(n2.getRefersToFormula()); |
| assertEquals("", n2.getNameName()); |
| |
| n1.setNameName("sale_1"); |
| n1.setRefersToFormula("10"); |
| |
| n2.setNameName("sale_2"); |
| n2.setRefersToFormula("20"); |
| |
| try { |
| n2.setNameName("sale_1"); |
| fail("Expected exception"); |
| } catch(Exception e){ |
| assertEquals("The workbook already contains this name: sale_1", e.getMessage()); |
| } |
| |
| wb.close(); |
| } |
| |
| @Test |
| public void testBug56930() throws IOException { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| |
| // x1 on sheet1 defines "x=1" |
| wb.createSheet("sheet1"); |
| Name x1 = wb.createName(); |
| |
| x1.setNameName("x"); |
| x1.setRefersToFormula("1"); |
| x1.setSheetIndex(wb.getSheetIndex("sheet1")); |
| |
| // x2 on sheet2 defines "x=2" |
| wb.createSheet("sheet2"); |
| Name x2 = wb.createName(); |
| x2.setNameName("x"); |
| x2.setRefersToFormula("2"); |
| x2.setSheetIndex(wb.getSheetIndex("sheet2")); |
| |
| List<? extends Name> names = wb.getNames("x"); |
| assertEquals("Had: " + names, 2, names.size()); |
| assertEquals("1", names.get(0).getRefersToFormula()); |
| assertEquals("2", names.get(1).getRefersToFormula()); |
| |
| assertEquals("1", wb.getName("x").getRefersToFormula()); |
| wb.removeName("x"); |
| assertEquals("2", wb.getName("x").getRefersToFormula()); |
| |
| wb.close(); |
| } |
| |
| // bug 56781: name validation only checks for first character's validity and presence of spaces |
| // bug 60246: validate name does not allow DOT in named ranges |
| @Test |
| public void testValid() throws IOException { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| |
| Name name = wb.createName(); |
| for (String valid : Arrays.asList( |
| "Hello", |
| "number1", |
| "_underscore", |
| "underscore_", |
| "p.e.r.o.i.d.s", |
| "\\Backslash", |
| "Backslash\\" |
| )) { |
| name.setNameName(valid); |
| } |
| |
| wb.close(); |
| } |
| |
| @Test |
| public void testInvalid() throws IOException { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| |
| Name name = wb.createName(); |
| try { |
| name.setNameName(""); |
| fail("expected exception: (blank)"); |
| } catch (final IllegalArgumentException e) { |
| assertEquals("Name cannot be blank", e.getMessage()); |
| } |
| |
| for (String invalid : Arrays.asList( |
| "1number", |
| "Sheet1!A1", |
| "Exclamation!", |
| "Has Space", |
| "Colon:", |
| "A-Minus", |
| "A+Plus", |
| "Dollar$", |
| ".periodAtBeginning", |
| "R", //special shorthand |
| "C", //special shorthand |
| "A1", // A1-style cell reference |
| "R1C1", // R1C1-style cell reference |
| "NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters..."+ |
| "NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters..."+ |
| "NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters..."+ |
| "NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters..."+ |
| "NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters" |
| )) { |
| try { |
| name.setNameName(invalid); |
| fail("expected exception: " + invalid); |
| } catch (final IllegalArgumentException e) { |
| assertTrue(invalid, |
| e.getMessage().startsWith("Invalid name: '"+invalid+"'")); |
| } |
| } |
| |
| } |
| |
| // bug 60260: renaming a sheet with a named range referring to a unicode (non-ASCII) sheet name |
| @Test |
| public void renameSheetWithNamedRangeReferringToUnicodeSheetName() { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| wb.createSheet("Sheet\u30FB1"); |
| |
| Name name = wb.createName(); |
| name.setNameName("test_named_range"); |
| name.setRefersToFormula("'Sheet\u30FB201'!A1:A6"); |
| |
| wb.setSheetName(0, "Sheet 1"); |
| IOUtils.closeQuietly(wb); |
| } |
| } |