public final void testShiftWithNames() { Workbook wb = _testDataProvider.createWorkbook(); Sheet sheet1 = wb.createSheet("Sheet1"); wb.createSheet("Sheet2"); Row row = sheet1.createRow(0); row.createCell(0).setCellValue(1.1); row.createCell(1).setCellValue(2.2); Name name1 = wb.createName(); name1.setNameName("name1"); name1.setRefersToFormula("Sheet1!$A$1+Sheet1!$B$1"); Name name2 = wb.createName(); name2.setNameName("name2"); name2.setRefersToFormula("Sheet1!$A$1"); // refers to A1 but on Sheet2. Should stay unaffected. Name name3 = wb.createName(); name3.setNameName("name3"); name3.setRefersToFormula("Sheet2!$A$1"); // The scope of this one is Sheet2. Should stay unaffected. Name name4 = wb.createName(); name4.setNameName("name4"); name4.setRefersToFormula("A1"); name4.setSheetIndex(1); sheet1.shiftRows(0, 1, 2); // shift down the top row on Sheet1. name1 = wb.getNameAt(0); assertEquals("Sheet1!$A$3+Sheet1!$B$3", name1.getRefersToFormula()); name2 = wb.getNameAt(1); assertEquals("Sheet1!$A$3", name2.getRefersToFormula()); // name3 and name4 refer to Sheet2 and should not be affected name3 = wb.getNameAt(2); assertEquals("Sheet2!$A$1", name3.getRefersToFormula()); name4 = wb.getNameAt(3); assertEquals("A1", name4.getRefersToFormula()); }
/** * Name should be created after sheets created. A special defined name, _xlnm._FilterDatabase * (xlsx) or _FilterDatabase (xls), stores the selected cells for auto-filter */ protected void importNamedRange() { for (int i = 0; i < workbook.getNumberOfNames(); i++) { Name definedName = workbook.getNameAt(i); if (skipName(definedName)) { continue; } SName namedRange = null; if (definedName.getSheetIndex() == -1) { // workbook scope namedRange = book.createName(definedName.getNameName()); } else { namedRange = book.createName(definedName.getNameName(), definedName.getSheetName()); } namedRange.setRefersToFormula(definedName.getRefersToFormula()); } }
protected boolean skipName(Name definedName) { String namename = definedName.getNameName(); if (namename == null) { return true; } // ignore defined name of functions, they are macro functions that we don't support if (definedName.isFunctionName()) { return true; } if (definedName.getRefersToFormula() == null) { // ignore defined name with null formula, don't know when will have this case return true; } return false; }