/** * 重命名当前sheet * * @param sheetName 新的sheet名 * @return this * @since 4.1.8 */ public ExcelWriter renameSheet(String sheetName) { return renameSheet(this.workbook.getSheetIndex(this.sheet), sheetName); }
/** * 重命名当前sheet * * @param sheetName 新的sheet名 * @return this * @since 4.1.8 */ public ExcelWriter renameSheet(String sheetName) { return renameSheet(this.workbook.getSheetIndex(this.sheet), sheetName); }
protected void adjustCellReferencesInsideFormula(Cell cell, Sheet destSheet, int deltaX, int deltaY) { FormulaRecordAggregate fra = (FormulaRecordAggregate)((HSSFCell)cell).getCellValueRecord(); int destSheetIndex = destSheet.getWorkbook().getSheetIndex(destSheet); Ptg[] ptgs = fra.getFormulaTokens(); if(adjustInBothDirections(ptgs, destSheetIndex, deltaX, deltaY)) fra.setParsedExpression(ptgs); } }
/*package*/ static void updateHyperlinks(Sheet sheet, FormulaShifter formulaShifter) { int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet); List<? extends Hyperlink> hyperlinkList = sheet.getHyperlinkList(); for (Hyperlink hyperlink : hyperlinkList) { XSSFHyperlink xhyperlink = (XSSFHyperlink) hyperlink; String cellRef = xhyperlink.getCellRef(); CellRangeAddress cra = CellRangeAddress.valueOf(cellRef); CellRangeAddress shiftedRange = BaseRowColShifter.shiftRange(formulaShifter, cra, sheetIndex); if (shiftedRange != null && shiftedRange != cra) { // shiftedRange should not be null. If shiftedRange is null, that means // that a hyperlink wasn't deleted at the beginning of shiftRows when // identifying rows that should be removed because they will be overwritten xhyperlink.setCellReference(shiftedRange.formatAsString()); } } }
private void copyRange(CellRangeAddress sourceRange, int deltaX, int deltaY, Sheet sourceClone) { //NOSONAR, it's a bit complex but monolith method, does not make much sense to divide it if(deltaX != 0) horizontalFormulaShifter = FormulaShifter.createForColumnCopy(sourceSheet.getWorkbook().getSheetIndex(sourceSheet), sourceSheet.getSheetName(), sourceRange.getFirstColumn(), sourceRange.getLastColumn(), deltaX, sourceSheet.getWorkbook().getSpreadsheetVersion()); if(deltaY != 0) verticalFormulaShifter = FormulaShifter.createForRowCopy(sourceSheet.getWorkbook().getSheetIndex(sourceSheet), sourceSheet.getSheetName(), sourceRange.getFirstRow(), sourceRange.getLastRow(), deltaY, sourceSheet.getWorkbook().getSpreadsheetVersion()); for(int rowNo = sourceRange.getFirstRow(); rowNo <= sourceRange.getLastRow(); rowNo++) { Row sourceRow = sourceClone.getRow(rowNo); // copy from source copy, original source might be overridden in process! for (int columnIndex = sourceRange.getFirstColumn(); columnIndex <= sourceRange.getLastColumn(); columnIndex++) { Cell sourceCell = sourceRow.getCell(columnIndex); if(sourceCell == null) continue; Row destRow = destSheet.getRow(rowNo + deltaY); if(destRow == null) destRow = destSheet.createRow(rowNo + deltaY); Cell newCell = destRow.getCell(columnIndex + deltaX); if(newCell != null) newCell.setCellType(sourceCell.getCellType()); else newCell = destRow.createCell(columnIndex + deltaX, sourceCell.getCellType()); cloneCellContent(sourceCell, newCell, null); if(newCell.getCellType() == CellType.FORMULA) adjustCellReferencesInsideFormula(newCell, destSheet, deltaX, deltaY); } } }
Sheet sourceCopy = sourceSheet.getWorkbook().cloneSheet(sourceSheet.getWorkbook().getSheetIndex(sourceSheet)); int sourceWidthMinus1 = tilePatternRange.getLastColumn() - tilePatternRange.getFirstColumn(); int sourceHeightMinus1 = tilePatternRange.getLastRow() - tilePatternRange.getFirstRow(); } while (nextRowIndexToCopy <= tileDestRange.getLastRow()); int tempCopyIndex = sourceSheet.getWorkbook().getSheetIndex(sourceCopy); sourceSheet.getWorkbook().removeSheetAt(tempCopyIndex);
/** * Shift a formula using the supplied FormulaShifter * * @param row the row of the cell this formula belongs to. Used to get a reference to the parent workbook. * @param formula the formula to shift * @param formulaShifter the FormulaShifter object that operates on the parsed formula tokens * @return the shifted formula if the formula was changed, * <code>null</code> if the formula wasn't modified */ /*package*/ static String shiftFormula(Row row, String formula, FormulaShifter formulaShifter) { Sheet sheet = row.getSheet(); Workbook wb = sheet.getWorkbook(); int sheetIndex = wb.getSheetIndex(sheet); final int rowIndex = row.getRowNum(); XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb); try { Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex, rowIndex); String shiftedFmla = null; if (formulaShifter.adjustFormula(ptgs, sheetIndex)) { shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs); } return shiftedFmla; } catch (FormulaParseException fpe) { // Log, but don't change, rather than breaking logger.log(POILogger.WARN, "Error shifting formula on row ", row.getRowNum(), fpe); return formula; } }
replacingSheetAt = data.wb.getSheetIndex( data.wb.getSheet( data.realSheetname ) ); data.wb.removeSheetAt( replacingSheetAt ); throw new KettleException( BaseMessages.getString( PKG, "ExcelWriterStep.Exception.TemplateNotFound", data.realTemplateSheetName ) ); data.sheet = data.wb.cloneSheet( data.wb.getSheetIndex( ts ) ); data.wb.setSheetName( data.wb.getSheetIndex( data.sheet ), data.realSheetname ); data.wb.setSheetHidden( data.wb.getSheetIndex( data.sheet ), false ); if ( meta.isTemplateSheetHidden() ) { data.wb.setSheetHidden( data.wb.getSheetIndex( ts ), true ); int sheetIndex = data.wb.getSheetIndex( data.sheet ); data.wb.setActiveSheet( sheetIndex ); data.wb.setSelectedTab( sheetIndex );
@Override public void setHidden(String sheetName, boolean hidden) { int sheetIndex = workbook.getSheetIndex(sheetName); workbook.setSheetHidden(sheetIndex, hidden); }
@Override public void deleteWorksheet(final SpreadsheetWorksheet worksheet) { final int worksheetIndex = this.workbook.getSheetIndex(((ExcelWorksheet) worksheet).sheet); this.workbook.removeSheetAt(worksheetIndex); }
/** * 重命名当前sheet * * @param sheetName 新的sheet名 * @return this * @since 4.1.8 */ public ExcelWriter renameSheet(String sheetName) { return renameSheet(this.workbook.getSheetIndex(this.sheet), sheetName); }
@Override public boolean deleteSheet(String sheetName) { if (super.deleteSheet(sheetName)) { int sheetIndex = workbook.getSheetIndex(sheetName); workbook.removeSheetAt(sheetIndex); return true; } else { logger.warn("Failed to find '{}' worksheet in a sheet map. Skipping the deletion.", sheetName); return false; } }
public void setName(String name) { Workbook workbook = sheet.getWorkbook(); workbook.setSheetName(workbook.getSheetIndex(sheet), name); }
/** * 检测Excel工作簿是否采用1904日期系统 */ private boolean isDate1904() { Sheet sheet = workbook.createSheet(); int sheetIndex = workbook.getSheetIndex(sheet); Row row = sheet.createRow(0); Cell cell = row.createCell(0); cell.setCellValue(0.0); boolean is1994 = isDate1904(cell); workbook.removeSheetAt(sheetIndex); return is1994; }
protected void removeSheet(String name) { int index = getWorkbook(true).getSheetIndex(name); if (index != -1) { getWorkbook(true).removeSheetAt(index); _sheetsModified = true; } }
protected void removeSheet(String name) { int index = getWorkbook(true).getSheetIndex(name); if (index != -1) { getWorkbook(true).removeSheetAt(index); _sheetsModified = true; } }
protected void removeSheet(String name) { int index = getWorkbook(true).getSheetIndex(name); if (index != -1) { getWorkbook(true).removeSheetAt(index); _sheetsModified = true; } }
public void loadSheetItem(Workbook workBook,String sheetName,String catalogId) throws ConfigException{ int sheetIndex = workBook.getSheetIndex(sheetName); if(sheetIndex >=0 ){ Sheet sheet = workBook.getSheetAt(sheetIndex); ExcelImportConfig configItem = new ExcelImportConfig(); //至少保证不少于7行,才有能列对照项的存在 if(sheet.getLastRowNum()>=6){ fillConfigItem(configItem,sheet); }else{ throw new ConfigException(MessageFormat.format("配置数据不得少于7行,请检查配置", sheetName)); } configMap.put(catalogId, configItem); }else{ throw new ConfigException(MessageFormat.format("找不到名称为[{0}]的sheet页存在", sheetName)); } }
protected void adjustCellReferencesInsideFormula(Cell cell, Sheet destSheet, int deltaX, int deltaY) { FormulaRecordAggregate fra = (FormulaRecordAggregate)((HSSFCell)cell).getCellValueRecord(); int destSheetIndex = destSheet.getWorkbook().getSheetIndex(destSheet); Ptg[] ptgs = fra.getFormulaTokens(); if(adjustInBothDirections(ptgs, destSheetIndex, deltaX, deltaY)) fra.setParsedExpression(ptgs); } }
/*package*/ static void updateHyperlinks(Sheet sheet, FormulaShifter formulaShifter) { int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet); List<? extends Hyperlink> hyperlinkList = sheet.getHyperlinkList(); for (Hyperlink hyperlink : hyperlinkList) { XSSFHyperlink xhyperlink = (XSSFHyperlink) hyperlink; String cellRef = xhyperlink.getCellRef(); CellRangeAddress cra = CellRangeAddress.valueOf(cellRef); CellRangeAddress shiftedRange = BaseRowColShifter.shiftRange(formulaShifter, cra, sheetIndex); if (shiftedRange != null && shiftedRange != cra) { // shiftedRange should not be null. If shiftedRange is null, that means // that a hyperlink wasn't deleted at the beginning of shiftRows when // identifying rows that should be removed because they will be overwritten xhyperlink.setCellReference(shiftedRange.formatAsString()); } } }