private CTSheet addSheet(String sheetname) { CTSheet sheet = workbook.getSheets().addNewSheet(); sheet.setName(sheetname); return sheet; }
/** * Determines whether a workbook contains the provided sheet name. * For the purpose of comparison, long names are truncated to 31 chars. * * @param name the name to test (case insensitive match) * @param excludeSheetIdx the sheet to exclude from the check or -1 to include all sheets in the check. * @return true if the sheet contains the name, false otherwise. */ private boolean containsSheet(String name, int excludeSheetIdx) { //noinspection deprecation CTSheet[] ctSheetArray = workbook.getSheets().getSheetArray(); if (name.length() > MAX_SENSITIVE_SHEET_NAME_LEN) { name = name.substring(0, MAX_SENSITIVE_SHEET_NAME_LEN); } for (int i = 0; i < ctSheetArray.length; i++) { String ctName = ctSheetArray[i].getName(); if (ctName.length() > MAX_SENSITIVE_SHEET_NAME_LEN) { ctName = ctName.substring(0, MAX_SENSITIVE_SHEET_NAME_LEN); } if (excludeSheetIdx != i && name.equalsIgnoreCase(ctName)) { return true; } } return false; }
/** * sets the order of appearance for a given sheet. * * @param sheetname the name of the sheet to reorder * @param pos the position that we want to insert the sheet into (0 based) */ @Override public void setSheetOrder(String sheetname, int pos) { int idx = getSheetIndex(sheetname); sheets.add(pos, sheets.remove(idx)); // Reorder CTSheets CTSheets ct = workbook.getSheets(); XmlObject cts = ct.getSheetArray(idx).copy(); workbook.getSheets().removeSheet(idx); CTSheet newcts = ct.insertNewSheet(pos); newcts.set(cts); //notify sheets //noinspection deprecation CTSheet[] sheetArray = ct.getSheetArray(); for(int i=0; i < sheetArray.length; i++) { sheets.get(i).sheet = sheetArray[i]; } updateNamedRangesAfterSheetReorder(idx, pos); updateActiveSheetAfterSheetReorder(idx, pos); }
utils.updateSheetName(sheetIndex, oldSheetName, sheetname); workbook.getSheets().getSheetArray(sheetIndex).setName(sheetname);
workbook.getSheets().removeSheet(index);
for (CTSheet ctSheet : this.workbook.getSheets().getSheetArray()) { parseSheet(shIdMap, ctSheet);
private CTSheet addSheet(String sheetname) { CTSheet sheet = workbook.getSheets().addNewSheet(); sheet.setName(sheetname); return sheet; }
private CTSheet addSheet(String sheetname) { CTSheet sheet = workbook.getSheets().addNewSheet(); sheet.setName(sheetname); return sheet; }
/** * Determines whether a workbook contains the provided sheet name. * For the purpose of comparison, long names are truncated to 31 chars. * * @param name the name to test (case insensitive match) * @param excludeSheetIdx the sheet to exclude from the check or -1 to include all sheets in the check. * @return true if the sheet contains the name, false otherwise. */ @SuppressWarnings("deprecation") // getXYZArray() array accessors are deprecated private boolean containsSheet(String name, int excludeSheetIdx) { CTSheet[] ctSheetArray = workbook.getSheets().getSheetArray(); if (name.length() > MAX_SENSITIVE_SHEET_NAME_LEN) { name = name.substring(0, MAX_SENSITIVE_SHEET_NAME_LEN); } for (int i = 0; i < ctSheetArray.length; i++) { String ctName = ctSheetArray[i].getName(); if (ctName.length() > MAX_SENSITIVE_SHEET_NAME_LEN) { ctName = ctName.substring(0, MAX_SENSITIVE_SHEET_NAME_LEN); } if (excludeSheetIdx != i && name.equalsIgnoreCase(ctName)) return true; } return false; }
/** * Determines whether a workbook contains the provided sheet name. * For the purpose of comparison, long names are truncated to 31 chars. * * @param name the name to test (case insensitive match) * @param excludeSheetIdx the sheet to exclude from the check or -1 to include all sheets in the check. * @return true if the sheet contains the name, false otherwise. */ private boolean containsSheet(String name, int excludeSheetIdx) { //noinspection deprecation CTSheet[] ctSheetArray = workbook.getSheets().getSheetArray(); if (name.length() > MAX_SENSITIVE_SHEET_NAME_LEN) { name = name.substring(0, MAX_SENSITIVE_SHEET_NAME_LEN); } for (int i = 0; i < ctSheetArray.length; i++) { String ctName = ctSheetArray[i].getName(); if (ctName.length() > MAX_SENSITIVE_SHEET_NAME_LEN) { ctName = ctName.substring(0, MAX_SENSITIVE_SHEET_NAME_LEN); } if (excludeSheetIdx != i && name.equalsIgnoreCase(ctName)) { return true; } } return false; }
/** * sets the order of appearance for a given sheet. * * @param sheetname the name of the sheet to reorder * @param pos the position that we want to insert the sheet into (0 based) */ public void setSheetOrder(String sheetname, int pos) { int idx = getSheetIndex(sheetname); sheets.add(pos, sheets.remove(idx)); // Reorder CTSheets CTSheets ct = workbook.getSheets(); XmlObject cts = ct.getSheetArray(idx).copy(); workbook.getSheets().removeSheet(idx); CTSheet newcts = ct.insertNewSheet(pos); newcts.set(cts); //notify sheets for(int i=0; i < sheets.size(); i++) { sheets.get(i).sheet = ct.getSheetArray(i); } }
/** * sets the order of appearance for a given sheet. * * @param sheetname the name of the sheet to reorder * @param pos the position that we want to insert the sheet into (0 based) */ @Override public void setSheetOrder(String sheetname, int pos) { int idx = getSheetIndex(sheetname); sheets.add(pos, sheets.remove(idx)); // Reorder CTSheets CTSheets ct = workbook.getSheets(); XmlObject cts = ct.getSheetArray(idx).copy(); workbook.getSheets().removeSheet(idx); CTSheet newcts = ct.insertNewSheet(pos); newcts.set(cts); //notify sheets //noinspection deprecation CTSheet[] sheetArray = ct.getSheetArray(); for(int i=0; i < sheetArray.length; i++) { sheets.get(i).sheet = sheetArray[i]; } updateNamedRangesAfterSheetReorder(idx, pos); updateActiveSheetAfterSheetReorder(idx, pos); }
/** * Set the sheet name. * * @param sheetIndex sheet number (0 based) * @param sheetname the new sheet name * @throws IllegalArgumentException if the name is null or invalid * or workbook already contains a sheet with this name * @see #createSheet(String) * @see org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal) */ public void setSheetName(int sheetIndex, String sheetname) { validateSheetIndex(sheetIndex); // YK: Mimic Excel and silently truncate sheet names longer than 31 characters if(sheetname != null && sheetname.length() > 31) sheetname = sheetname.substring(0, 31); WorkbookUtil.validateSheetName(sheetname); if (containsSheet(sheetname, sheetIndex )) throw new IllegalArgumentException( "The workbook already contains a sheet of this name" ); XSSFFormulaUtils utils = new XSSFFormulaUtils(this); utils.updateSheetName(sheetIndex, sheetname); workbook.getSheets().getSheetArray(sheetIndex).setName(sheetname); }
/** * Gracefully remove references to the sheet being deleted * * @param index the 0-based index of the sheet to delete */ private void onSheetDelete(int index) { //delete the CTSheet reference from workbook.xml workbook.getSheets().removeSheet(index); //calculation chain is auxiliary, remove it as it may contain orphan references to deleted cells if(calcChain != null) { removeRelation(calcChain); calcChain = null; } //adjust indices of names ranges for (Iterator<XSSFName> it = namedRanges.iterator(); it.hasNext();) { XSSFName nm = it.next(); CTDefinedName ct = nm.getCTName(); if(!ct.isSetLocalSheetId()) continue; if (ct.getLocalSheetId() == index) { it.remove(); } else if (ct.getLocalSheetId() > index){ // Bump down by one, so still points at the same sheet ct.setLocalSheetId(ct.getLocalSheetId()-1); } } }
utils.updateSheetName(sheetIndex, oldSheetName, sheetname); workbook.getSheets().getSheetArray(sheetIndex).setName(sheetname);
/** * Construct a new SheetIterator * * @param wb package part holding workbook.xml */ private SheetIterator(PackagePart wb) throws IOException { /** * The order of sheets is defined by the order of CTSheet elements in workbook.xml */ try { //step 1. Map sheet's relationship Id and the corresponding PackagePart sheetMap = new HashMap<String, PackagePart>(); for(PackageRelationship rel : wb.getRelationships()){ if(rel.getRelationshipType().equals(XSSFRelation.WORKSHEET.getRelation()) || rel.getRelationshipType().equals(XSSFRelation.CHARTSHEET.getRelation())){ PackagePartName relName = PackagingURIHelper.createPartName(rel.getTargetURI()); sheetMap.put(rel.getId(), wb.getPackage().getPart(relName)); } } //step 2. Read array of CTSheet elements, wrap it in a ArayList and construct an iterator //Note, using XMLBeans might be expensive, consider refactoring to use SAX or a plain regexp search CTWorkbook wbBean = WorkbookDocument.Factory.parse(wb.getInputStream()).getWorkbook(); sheetIterator = wbBean.getSheets().getSheetList().iterator(); } catch (InvalidFormatException e){ throw new POIXMLException(e); } catch (XmlException e){ throw new POIXMLException(e); } }
private void translateSheetName(XSSFSheet sheet, XLXContext context) throws Exception { for( CTSheet next : sheet.getWorkbook().getCTWorkbook().getSheets().getSheetArray()){ if(next.getName().equals(sheet.getSheetName())){ String rawName = next.getName(); XLSXExpression exp = CellExpressionParser.parseExpression(next.getName()); String translatedName = (String) exp.evaluate(context); next.setName(translatedName); translateSheetName(sheet, rawName, translatedName); break; } } }
for (CTSheet ctSheet : this.workbook.getSheets().getSheetArray()) { XSSFSheet sh = shIdMap.get(ctSheet.getId()); if(sh == null) {
workbook.getSheets().removeSheet(index);
for (CTSheet ctSheet : this.workbook.getSheets().getSheetArray()) { parseSheet(shIdMap, ctSheet);