What Is poi-ooxml-5.2.3.jar?
What Is poi-ooxml-5.2.3.jar?
✍: FYIcenter.com
poi-ooxml-5.2.3.jar is one of the JAR files for Apache POI 5.2.3, which
provides an API for Microsoft document files of Word, Excel, PowerPoint, and Visio.
poi-ooxml-5.2.3.jar supports Apache POI components that read and write Microsoft's Open Office XML document format, which is used in recent versions of Microsoft Office tools like Word 2007, Excel 2007, PowerPoint 2007, etc.
poi-ooxml-5.2.3.jar is distributed as part of the poi-bin-5.2.3-20220909.zip download file.
JAR File Size and Download Location:
JAR name: poi-ooxml-5.2.3.jar Target JDK version: 9 Dependency: poi.jar xmlbeans.jar ooxml-schemas.jar commons-collections.jar junit.jar File name: poi-ooxml.jar, poi-ooxml-5.2.3.jar File size: 2010497 bytes Release date: 09-09-2022 Download: Apache POI Website
Here are Java Source Code files for poi-ooxml-5.2.3.jar:
⏎ org/apache/poi/xssf/usermodel/XSSFSheet.java
/* ====================================================================
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.xssf.usermodel;
import static org.apache.poi.ooxml.POIXMLTypeLoader.DEFAULT_XML_OPTIONS;
import static org.apache.poi.xssf.usermodel.helpers.XSSFPasswordHelper.setPassword;
import static org.apache.poi.xssf.usermodel.helpers.XSSFPasswordHelper.validatePassword;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.*;
import javax.xml.namespace.QName;
import javax.xml.stream.XMLStreamException;
import javax.xml.stream.XMLStreamReader;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ooxml.POIXMLException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.exceptions.PartAlreadyExistsException;
import org.apache.poi.openxml4j.opc.*;
import org.apache.poi.poifs.crypt.HashAlgorithm;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.formula.FormulaShifter;
import org.apache.poi.ss.formula.SheetNameFormatter;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.PaneInformation;
import org.apache.poi.ss.util.SSCellRange;
import org.apache.poi.ss.util.SheetUtil;
import org.apache.poi.util.Beta;
import org.apache.poi.util.Internal;
import org.apache.poi.util.Removal;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.model.Comments;
import org.apache.poi.xssf.usermodel.XSSFPivotTable.PivotTableReferenceConfigurator;
import org.apache.poi.xssf.usermodel.helpers.ColumnHelper;
import org.apache.poi.xssf.usermodel.helpers.XSSFColumnShifter;
import org.apache.poi.xssf.usermodel.helpers.XSSFIgnoredErrorHelper;
import org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter;
import org.apache.xmlbeans.XmlCursor;
import org.apache.xmlbeans.XmlException;
import org.apache.xmlbeans.XmlObject;
import org.apache.xmlbeans.XmlOptions;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;
/**
* High level representation of a SpreadsheetML worksheet.
*
* <p>
* Sheets are the central structures within a workbook, and are where a user does most of his spreadsheet work.
* The most common type of sheet is the worksheet, which is represented as a grid of cells. Worksheet cells can
* contain text, numbers, dates, and formulas. Cells can also be formatted.
* </p>
*/
public class XSSFSheet extends POIXMLDocumentPart implements Sheet, OoxmlSheetExtensions {
private static final Logger LOG = LogManager.getLogger(XSSFSheet.class);
private static final double DEFAULT_ROW_HEIGHT = 15.0;
private static final double DEFAULT_MARGIN_HEADER = 0.3;
private static final double DEFAULT_MARGIN_FOOTER = 0.3;
private static final double DEFAULT_MARGIN_TOP = 0.75;
private static final double DEFAULT_MARGIN_BOTTOM = 0.75;
private static final double DEFAULT_MARGIN_LEFT = 0.7;
private static final double DEFAULT_MARGIN_RIGHT = 0.7;
//TODO make the two variable below private!
protected CTSheet sheet;
protected CTWorksheet worksheet;
private final SortedMap<Integer, XSSFRow> _rows = new TreeMap<>();
private List<XSSFHyperlink> hyperlinks;
private ColumnHelper columnHelper;
private Comments sheetComments;
/**
* cache of master shared formulas in this sheet.
* Master shared formula is the first formula in a group of shared formulas is saved in the f element.
*/
private Map<Integer, CTCellFormula> sharedFormulas;
private SortedMap<String,XSSFTable> tables;
private List<CellRangeAddress> arrayFormulas;
private final XSSFDataValidationHelper dataValidationHelper;
private XSSFVMLDrawing xssfvmlDrawing;
private CellRangeAddress dimensionOverride;
/**
* Creates new XSSFSheet - called by XSSFWorkbook to create a sheet from scratch.
*
* @see XSSFWorkbook#createSheet()
*/
protected XSSFSheet() {
super();
dataValidationHelper = new XSSFDataValidationHelper(this);
onDocumentCreate();
}
/**
* Creates an XSSFSheet representing the given package part and relationship.
* Should only be called by XSSFWorkbook when reading in an existing file.
*
* @param part - The package part that holds xml data representing this sheet.
*
* @since POI 3.14-Beta1
*/
protected XSSFSheet(PackagePart part) {
super(part);
dataValidationHelper = new XSSFDataValidationHelper(this);
}
/**
* Returns the parent XSSFWorkbook
*
* @return the parent XSSFWorkbook
*/
@Override
public XSSFWorkbook getWorkbook() {
return (XSSFWorkbook)getParent();
}
/**
* Initialize worksheet data when reading in an exisiting file.
*/
@Override
protected void onDocumentRead() {
try (InputStream stream = getPackagePart().getInputStream()) {
read(stream);
} catch (IOException e){
throw new POIXMLException(e);
}
}
protected void read(InputStream is) throws IOException {
try {
worksheet = WorksheetDocument.Factory.parse(is, DEFAULT_XML_OPTIONS).getWorksheet();
} catch (XmlException e){
throw new POIXMLException(e);
}
initRows(worksheet);
columnHelper = new ColumnHelper(worksheet);
// Look for bits we're interested in
for(RelationPart rp : getRelationParts()){
POIXMLDocumentPart p = rp.getDocumentPart();
if(p instanceof Comments) {
sheetComments = (Comments)p;
sheetComments.setSheet(this);
}
if(p instanceof XSSFTable) {
tables.put( rp.getRelationship().getId(), (XSSFTable)p );
}
if(p instanceof XSSFPivotTable) {
getWorkbook().getPivotTables().add((XSSFPivotTable) p);
}
}
// Process external hyperlinks for the sheet, if there are any
initHyperlinks();
}
/**
* Initialize worksheet data when creating a new sheet.
*/
@Override
protected void onDocumentCreate(){
worksheet = newSheet();
initRows(worksheet);
columnHelper = new ColumnHelper(worksheet);
hyperlinks = new ArrayList<>();
}
private void initRows(CTWorksheet worksheetParam) {
if (worksheetParam.getSheetData() == null || worksheetParam.getSheetData().getRowArray() == null) {
throw new IllegalArgumentException("Had empty sheet data when initializing the sheet");
}
_rows.clear();
tables = new TreeMap<>();
sharedFormulas = new HashMap<>();
arrayFormulas = new ArrayList<>();
for (CTRow row : worksheetParam.getSheetData().getRowArray()) {
XSSFRow r = new XSSFRow(row, this);
// Performance optimization: explicit boxing is slightly faster than auto-unboxing, though may use more memory
//noinspection UnnecessaryBoxing
final Integer rownumI = Integer.valueOf(r.getRowNum()); // NOSONAR
_rows.put(rownumI, r);
}
}
/**
* Read hyperlink relations, link them with CTHyperlink beans in this worksheet
* and initialize the internal array of XSSFHyperlink objects
*/
private void initHyperlinks() {
hyperlinks = new ArrayList<>();
if(!worksheet.isSetHyperlinks()) {
return;
}
try {
PackageRelationshipCollection hyperRels =
getPackagePart().getRelationshipsByType(XSSFRelation.SHEET_HYPERLINKS.getRelation());
// Turn each one into a XSSFHyperlink
for(CTHyperlink hyperlink : worksheet.getHyperlinks().getHyperlinkArray()) {
PackageRelationship hyperRel = null;
if(hyperlink.getId() != null) {
hyperRel = hyperRels.getRelationshipByID(hyperlink.getId());
}
hyperlinks.add( new XSSFHyperlink(hyperlink, hyperRel) );
}
} catch (InvalidFormatException e){
throw new POIXMLException(e);
}
}
/**
* Create a new CTWorksheet instance with all values set to defaults
*
* @return a new instance
*/
private static CTWorksheet newSheet(){
CTWorksheet worksheet = CTWorksheet.Factory.newInstance();
CTSheetFormatPr ctFormat = worksheet.addNewSheetFormatPr();
ctFormat.setDefaultRowHeight(DEFAULT_ROW_HEIGHT);
CTSheetView ctView = worksheet.addNewSheetViews().addNewSheetView();
ctView.setWorkbookViewId(0);
worksheet.addNewDimension().setRef("A1");
worksheet.addNewSheetData();
CTPageMargins ctMargins = worksheet.addNewPageMargins();
ctMargins.setBottom(DEFAULT_MARGIN_BOTTOM);
ctMargins.setFooter(DEFAULT_MARGIN_FOOTER);
ctMargins.setHeader(DEFAULT_MARGIN_HEADER);
ctMargins.setLeft(DEFAULT_MARGIN_LEFT);
ctMargins.setRight(DEFAULT_MARGIN_RIGHT);
ctMargins.setTop(DEFAULT_MARGIN_TOP);
return worksheet;
}
/**
* Provide access to the CTWorksheet bean holding this sheet's data
*
* @return the CTWorksheet bean holding this sheet's data
*/
@Internal
public CTWorksheet getCTWorksheet() {
return this.worksheet;
}
public ColumnHelper getColumnHelper() {
return columnHelper;
}
/**
* Returns the name of this sheet
*
* @return the name of this sheet
*/
@Override
public String getSheetName() {
return sheet.getName();
}
/**
* Adds a merged region of cells on a sheet.
*
* @param region to merge
* @return index of this region
* @throws IllegalArgumentException if region contains fewer than 2 cells
* @throws IllegalStateException if region intersects with a multi-cell array formula
* @throws IllegalStateException if region intersects with an existing region on this sheet
*/
@Override
public int addMergedRegion(CellRangeAddress region) {
return addMergedRegion(region, true);
}
/**
* Adds a merged region of cells (hence those cells form one).
* Skips validation. It is possible to create overlapping merged regions
* or create a merged region that intersects a multi-cell array formula
* with this formula, which may result in a corrupt workbook.
*
* To check for merged regions overlapping array formulas or other merged regions
* after addMergedRegionUnsafe has been called, call {@link #validateMergedRegions()}, which runs in O(n^2) time.
*
* @param region to merge
* @return index of this region
* @throws IllegalArgumentException if region contains fewer than 2 cells
*/
@Override
public int addMergedRegionUnsafe(CellRangeAddress region) {
return addMergedRegion(region, false);
}
/**
* Adds a merged region of cells (hence those cells form one).
* If validate is true, check to make sure adding the merged region to the sheet doesn't create a corrupt workbook
* If validate is false, skips the expensive merged region checks, but may produce a corrupt workbook.
*
* @param region to merge
* @param validate whether to validate merged region
* @return 0-based index of this region
* @throws IllegalArgumentException if region contains fewer than 2 cells (this check is inexpensive and is performed regardless of {@code validate})
* @throws IllegalStateException if region intersects with a multi-cell array formula
* @throws IllegalStateException if region intersects with an existing region on this sheet
*/
private int addMergedRegion(CellRangeAddress region, boolean validate) {
if (region.getNumberOfCells() < 2) {
throw new IllegalArgumentException("Merged region " + region.formatAsString() + " must contain 2 or more cells");
}
region.validate(SpreadsheetVersion.EXCEL2007);
if (validate) {
// throw IllegalStateException if the argument CellRangeAddress intersects with
// a multi-cell array formula defined in this sheet
validateArrayFormulas(region);
// Throw IllegalStateException if the argument CellRangeAddress intersects with
// a merged region already in this sheet
validateMergedRegions(region);
}
CTMergeCells ctMergeCells = worksheet.isSetMergeCells() ? worksheet.getMergeCells() : worksheet.addNewMergeCells();
CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell();
ctMergeCell.setRef(region.formatAsString());
long count = ctMergeCells.getCount();
if (count == 0) {
count=ctMergeCells.sizeOfMergeCellArray();
} else {
count++;
}
// also adjust the number of merged regions overall
ctMergeCells.setCount(count);
return Math.toIntExact(count-1);
}
/**
* Verify that the candidate region does not intersect with an existing multi-cell array formula in this sheet
*
* @param region a region that is validated.
* @throws IllegalStateException if candidate region intersects an existing array formula in this sheet
*/
private void validateArrayFormulas(CellRangeAddress region) {
// FIXME: this may be faster if it looped over array formulas directly rather than looping over each cell in
// the region and searching if that cell belongs to an array formula
int firstRow = region.getFirstRow();
int firstColumn = region.getFirstColumn();
int lastRow = region.getLastRow();
int lastColumn = region.getLastColumn();
// for each cell in sheet, if cell belongs to an array formula, check if merged region intersects array formula cells
for (int rowIn = firstRow; rowIn <= lastRow; rowIn++) {
XSSFRow row = getRow(rowIn);
if (row == null) {
continue;
}
for (int colIn = firstColumn; colIn <= lastColumn; colIn++) {
XSSFCell cell = row.getCell(colIn);
if (cell == null) {
continue;
}
if (cell.isPartOfArrayFormulaGroup()) {
CellRangeAddress arrayRange = cell.getArrayFormulaRange();
if (arrayRange.getNumberOfCells() > 1 && region.intersects(arrayRange)) {
String msg = "The range " + region.formatAsString() + " intersects with a multi-cell array formula. " +
"You cannot merge cells of an array.";
throw new IllegalStateException(msg);
}
}
}
}
}
/**
* Verify that none of the merged regions intersect a multi-cell array formula in this sheet
*
* @throws IllegalStateException if candidate region intersects an existing array formula in this sheet
*/
private void checkForMergedRegionsIntersectingArrayFormulas() {
for (CellRangeAddress region : getMergedRegions()) {
validateArrayFormulas(region);
}
}
/**
* Verify that candidate region does not intersect with an existing merged region in this sheet
*
* @param candidateRegion the range of cells to verify
* @throws IllegalStateException if candidate region intersects an existing merged region in this sheet (or candidateRegion is already merged in this sheet)
*/
private void validateMergedRegions(CellRangeAddress candidateRegion) {
for (final CellRangeAddress existingRegion : getMergedRegions()) {
if (existingRegion.intersects(candidateRegion)) {
throw new IllegalStateException("Cannot add merged region " + candidateRegion.formatAsString() +
" to sheet because it overlaps with an existing merged region (" + existingRegion.formatAsString() + ").");
}
}
}
/**
* Verify that no merged regions intersect another merged region in this sheet.
*
* @throws IllegalStateException if at least one region intersects with another merged region in this sheet
*/
private void checkForIntersectingMergedRegions() {
final List<CellRangeAddress> regions = getMergedRegions();
final int size = regions.size();
for (int i=0; i < size; i++) {
final CellRangeAddress region = regions.get(i);
for (final CellRangeAddress other : regions.subList(i+1, regions.size())) {
if (region.intersects(other)) {
String msg = "The range " + region.formatAsString() +
" intersects with another merged region " +
other.formatAsString() + " in this sheet";
throw new IllegalStateException(msg);
}
}
}
}
/**
* Verify that merged regions do not intersect multi-cell array formulas and
* no merged regions intersect another merged region in this sheet.
*
* @throws IllegalStateException if region intersects with a multi-cell array formula
* @throws IllegalStateException if at least one region intersects with another merged region in this sheet
*/
@Override
public void validateMergedRegions() {
checkForMergedRegionsIntersectingArrayFormulas();
checkForIntersectingMergedRegions();
}
/**
* Adjusts the column width to fit the contents.
*
* This process can be relatively slow on large sheets, so this should
* normally only be called once per column, at the end of your
* processing.
*
* @param column the column index
*/
@Override
public void autoSizeColumn(int column) {
autoSizeColumn(column, false);
}
/**
* Adjusts the column width to fit the contents.
* <p>
* This process can be relatively slow on large sheets, so this should
* normally only be called once per column, at the end of your
* processing.
* </p>
* You can specify whether the content of merged cells should be considered or ignored.
* Default is to ignore merged cells.
*
* @param column the column index
* @param useMergedCells whether to use the contents of merged cells when calculating the width of the column
*/
@Override
public void autoSizeColumn(int column, boolean useMergedCells) {
double width = SheetUtil.getColumnWidth(this, column, useMergedCells);
if (width != -1) {
width *= 256;
int maxColumnWidth = 255*256; // The maximum column width for an individual cell is 255 characters
if (width > maxColumnWidth) {
width = maxColumnWidth;
}
setColumnWidth(column, Math.toIntExact(Math.round(width)));
columnHelper.setColBestFit(column, true);
}
}
/**
* Return the sheet's existing drawing, or null if there isn't yet one.
*
* Use {@link #createDrawingPatriarch()} to get or create
*
* @return a SpreadsheetML drawing
*/
@Override
public XSSFDrawing getDrawingPatriarch() {
CTDrawing ctDrawing = getCTDrawing();
if (ctDrawing != null) {
// Search the referenced drawing in the list of the sheet's relations
for (RelationPart rp : getRelationParts()){
POIXMLDocumentPart p = rp.getDocumentPart();
if (p instanceof XSSFDrawing) {
XSSFDrawing dr = (XSSFDrawing)p;
String drId = rp.getRelationship().getId();
if (drId.equals(ctDrawing.getId())){
return dr;
}
}
}
LOG.atError().log("Can't find drawing with id={} in the list of the sheet's relationships", ctDrawing.getId());
}
return null;
}
/**
* Create a new SpreadsheetML drawing. If this sheet already contains a drawing - return that.
*
* @return a SpreadsheetML drawing
*/
@Override
public XSSFDrawing createDrawingPatriarch() {
XSSFDrawing existingDrawing = getDrawingPatriarch();
if (existingDrawing != null) {
return existingDrawing;
}
// Default drawingNumber = #drawings.size() + 1
int drawingNumber = getPackagePart().getPackage().getPartsByContentType(XSSFRelation.DRAWINGS.getContentType()).size() + 1;
drawingNumber = getNextPartNumber(XSSFRelation.DRAWINGS, drawingNumber);
RelationPart rp = createRelationship(XSSFRelation.DRAWINGS, getWorkbook().getXssfFactory(), drawingNumber, false);
XSSFDrawing drawing = rp.getDocumentPart();
String relId = rp.getRelationship().getId();
//add CT_Drawing element which indicates that this sheet contains drawing components built on the drawingML platform.
//The relationship Id references the part containing the drawingML definitions.
CTDrawing ctDrawing = worksheet.addNewDrawing();
ctDrawing.setId(relId);
// Return the newly created drawing
return drawing;
}
/**
* Get VML drawing for this sheet (aka 'legacy' drawing).
*
* @param autoCreate if true, then a new VML drawing part is created
*
* @return the VML drawing of {@code null} if the drawing was not found and autoCreate=false
*/
@Override
public XSSFVMLDrawing getVMLDrawing(boolean autoCreate) {
if (xssfvmlDrawing == null) {
XSSFVMLDrawing drawing = null;
CTLegacyDrawing ctDrawing = getCTLegacyDrawing();
if(ctDrawing == null) {
if(autoCreate) {
int drawingNumber = getNextPartNumber(XSSFRelation.VML_DRAWINGS,
getPackagePart().getPackage().getPartsByContentType(XSSFRelation.VML_DRAWINGS.getContentType()).size());
RelationPart rp = createRelationship(XSSFRelation.VML_DRAWINGS, getWorkbook().getXssfFactory(), drawingNumber, false);
drawing = rp.getDocumentPart();
String relId = rp.getRelationship().getId();
//add CTLegacyDrawing element which indicates that this sheet contains drawing components built on the drawingML platform.
//The relationship Id references the part containing the drawing definitions.
ctDrawing = worksheet.addNewLegacyDrawing();
ctDrawing.setId(relId);
}
} else {
//search the referenced drawing in the list of the sheet's relations
final String id = ctDrawing.getId();
for (RelationPart rp : getRelationParts()){
POIXMLDocumentPart p = rp.getDocumentPart();
if(p instanceof XSSFVMLDrawing) {
XSSFVMLDrawing dr = (XSSFVMLDrawing)p;
String drId = rp.getRelationship().getId();
if (drId.equals(id)) {
drawing = dr;
break;
}
// do not break here since drawing has not been found yet (see bug 52425)
}
}
if(drawing == null){
LOG.atError().log("Can't find VML drawing with id={} in the list of the sheet's relationships", id);
}
}
xssfvmlDrawing = drawing;
}
return xssfvmlDrawing;
}
protected CTDrawing getCTDrawing() {
return worksheet.getDrawing();
}
protected CTLegacyDrawing getCTLegacyDrawing() {
return worksheet.getLegacyDrawing();
}
/**
* Creates a split (freezepane). Any existing freezepane or split pane is overwritten.
* @param colSplit Horizontal position of split.
* @param rowSplit Vertical position of split.
*/
@Override
public void createFreezePane(int colSplit, int rowSplit) {
createFreezePane( colSplit, rowSplit, colSplit, rowSplit );
}
/**
* Creates a split (freezepane). Any existing freezepane or split pane is overwritten.
*
* <p>
* If both colSplit and rowSplit are zero then the existing freeze pane is removed
* </p>
*
* @param colSplit Horizontal position of split.
* @param rowSplit Vertical position of split.
* @param leftmostColumn Left column visible in right pane.
* @param topRow Top row visible in bottom pane
*/
@Override
public void createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow) {
final boolean removeSplit = colSplit == 0 && rowSplit == 0;
final CTSheetView ctView = getDefaultSheetView(!removeSplit);
if (ctView != null) {
ctView.setSelectionArray(null);
}
// If both colSplit and rowSplit are zero then the existing freeze pane is removed
if (removeSplit) {
if (ctView != null && ctView.isSetPane()) {
ctView.unsetPane();
}
return;
}
assert(ctView != null);
final CTPane pane = (ctView.isSetPane()) ? ctView.getPane() : ctView.addNewPane();
assert(pane != null);
if (colSplit > 0) {
pane.setXSplit(colSplit);
} else if (pane.isSetXSplit()) {
pane.unsetXSplit();
}
if (rowSplit > 0) {
pane.setYSplit(rowSplit);
} else if(pane.isSetYSplit()) {
pane.unsetYSplit();
}
STPane.Enum activePane = STPane.BOTTOM_RIGHT;
int pRow = topRow, pCol = leftmostColumn;
if (rowSplit == 0) {
pRow = 0;
activePane = STPane.TOP_RIGHT;
} else if (colSplit == 0) {
pCol = 0;
activePane = STPane.BOTTOM_LEFT;
}
pane.setState(STPaneState.FROZEN);
pane.setTopLeftCell(new CellReference(pRow, pCol).formatAsString());
pane.setActivePane(activePane);
ctView.addNewSelection().setPane(activePane);
}
/**
* Create a new row within the sheet and return the high level representation
*
* Note: If a row already exists at this position, it is removed/overwritten and
* any existing cell is removed!
*
* @param rownum row number
* @return High level {@link XSSFRow} object representing a row in the sheet
* @see #removeRow(Row)
*/
@Override
public XSSFRow createRow(int rownum) {
// Performance optimization: explicit boxing is slightly faster than auto-unboxing, though may use more memory
//noinspection UnnecessaryBoxing
final Integer rownumI = Integer.valueOf(rownum); // NOSONAR
CTRow ctRow;
XSSFRow prev = _rows.get(rownumI);
if(prev != null){
// the Cells in an existing row are invalidated on-purpose, in order to clean up correctly, we
// need to call the remove, so things like ArrayFormulas and CalculationChain updates are done
// correctly.
// We remove the cell this way as the internal cell-list is changed by the remove call and
// thus would cause ConcurrentModificationException otherwise
while(prev.getFirstCellNum() != -1) {
prev.removeCell(prev.getCell(prev.getFirstCellNum()));
}
ctRow = prev.getCTRow();
ctRow.set(CTRow.Factory.newInstance());
} else {
if(_rows.isEmpty() || rownum > _rows.lastKey()) {
// we can append the new row at the end
ctRow = worksheet.getSheetData().addNewRow();
} else {
// get number of rows where row index < rownum
// --> this tells us where our row should go
int idx = _rows.headMap(rownumI).size();
ctRow = worksheet.getSheetData().insertNewRow(idx);
}
}
XSSFRow r = new XSSFRow(ctRow, this);
r.setRowNum(rownum);
_rows.put(rownumI, r);
return r;
}
/**
* Creates a split pane. Any existing freezepane or split pane is overwritten.
* @param xSplitPos Horizontal position of split (in 1/20th of a point).
* @param ySplitPos Vertical position of split (in 1/20th of a point).
* @param topRow Top row visible in bottom pane
* @param leftmostColumn Left column visible in right pane.
* @param activePane Active pane. One of: PANE_LOWER_RIGHT,
* PANE_UPPER_RIGHT, PANE_LOWER_LEFT, PANE_UPPER_LEFT (but there is a
* <a href="https://bz.apache.org/bugzilla/show_bug.cgi?id=66173">bug</a>, so add 1)
* @see #PANE_LOWER_LEFT
* @see #PANE_LOWER_RIGHT
* @see #PANE_UPPER_LEFT
* @see #PANE_UPPER_RIGHT
* @deprecated use {@link #createSplitPane(int, int, int, int, PaneType)}
*/
@Override
@Deprecated
@Removal(version = "7.0.0")
public void createSplitPane(int xSplitPos, int ySplitPos, int leftmostColumn, int topRow, int activePane) {
createFreezePane(xSplitPos, ySplitPos, leftmostColumn, topRow);
if (xSplitPos > 0 || ySplitPos > 0) {
final CTPane pane = getPane(true);
pane.setState(STPaneState.SPLIT);
pane.setActivePane(STPane.Enum.forInt(activePane));
}
}
/**
* Creates a split pane. Any existing freezepane or split pane is overwritten.
* @param xSplitPos Horizontal position of split (in 1/20th of a point).
* @param ySplitPos Vertical position of split (in 1/20th of a point).
* @param topRow Top row visible in bottom pane
* @param leftmostColumn Left column visible in right pane.
* @param activePane Active pane.
* @see PaneType
* @since POI 5.2.3
*/
@Override
public void createSplitPane(int xSplitPos, int ySplitPos, int leftmostColumn, int topRow, PaneType activePane) {
createFreezePane(xSplitPos, ySplitPos, leftmostColumn, topRow);
if (xSplitPos > 0 || ySplitPos > 0) {
final CTPane pane = getPane(true);
pane.setState(STPaneState.SPLIT);
STPane.Enum stPaneEnum;
switch (activePane) {
case LOWER_RIGHT:
stPaneEnum = STPane.BOTTOM_RIGHT;
break;
case UPPER_RIGHT:
stPaneEnum = STPane.TOP_RIGHT;
break;
case LOWER_LEFT:
stPaneEnum = STPane.BOTTOM_LEFT;
break;
case UPPER_LEFT:
default:
stPaneEnum = STPane.TOP_LEFT;
break;
}
pane.setActivePane(stPaneEnum);
}
}
/**
* Return cell comment at row, column, if one exists. Otherwise returns null.
*
* @param address the location of the cell comment
* @return the cell comment, if one exists. Otherwise, return null.
*/
@Override
public XSSFComment getCellComment(CellAddress address) {
if (sheetComments == null) {
return null;
}
return sheetComments.findCellComment(address);
}
/**
* Returns all cell comments on this sheet.
* @return A map of each Comment in the sheet, keyed on the cell address where
* the comment is located.
*/
@Override
public Map<CellAddress, XSSFComment> getCellComments() {
if (sheetComments == null) {
return Collections.emptyMap();
}
// the cell comments in sheetComments.getCellComments() do not have the client anchors set
Map<CellAddress, XSSFComment> map = new HashMap<>();
for(Iterator<CellAddress> iter = sheetComments.getCellAddresses(); iter.hasNext(); ) {
CellAddress address = iter.next();
map.put(address, getCellComment(address));
}
return map;
}
/**
* Get a Hyperlink in this sheet anchored at row, column
*
* @param row The row where the hyperlink is anchored
* @param column The column where the hyperlink is anchored
* @return hyperlink if there is a hyperlink anchored at row, column; otherwise returns null
*/
@Override
public XSSFHyperlink getHyperlink(int row, int column) {
return getHyperlink(new CellAddress(row, column));
}
/**
* Get a Hyperlink in this sheet located in a cell specified by {code addr}
*
* @param addr The address of the cell containing the hyperlink
* @return hyperlink if there is a hyperlink anchored at {@code addr}; otherwise returns {@code null}
* @since POI 3.15 beta 3
*/
@Override
public XSSFHyperlink getHyperlink(CellAddress addr) {
for (XSSFHyperlink hyperlink : getHyperlinkList()) {
if (addr.getRow() >= hyperlink.getFirstRow() && addr.getRow() <= hyperlink.getLastRow()
&& addr.getColumn() >= hyperlink.getFirstColumn() && addr.getColumn() <= hyperlink.getLastColumn()) {
return hyperlink;
}
}
return null;
}
/**
* Get a list of Hyperlinks in this sheet
*
* @return Hyperlinks for the sheet
*/
@Override
public List<XSSFHyperlink> getHyperlinkList() {
return Collections.unmodifiableList(hyperlinks);
}
private int[] getBreaks(CTPageBreak ctPageBreak) {
CTBreak[] brkArray = ctPageBreak.getBrkArray();
int[] breaks = new int[brkArray.length];
for (int i = 0 ; i < brkArray.length ; i++) {
breaks[i] = Math.toIntExact(brkArray[i].getId() - 1);
}
return breaks;
}
private void removeBreak(int index, CTPageBreak ctPageBreak) {
int index1 = index + 1;
CTBreak[] brkArray = ctPageBreak.getBrkArray();
for (int i = 0 ; i < brkArray.length ; i++) {
if (brkArray[i].getId() == index1) {
ctPageBreak.removeBrk(i);
// TODO: check if we can break here, i.e. if a page can have more than 1 break on the same id
}
}
}
/**
* Vertical page break information used for print layout view, page layout view, drawing print breaks
* in normal view, and for printing the worksheet.
*
* @return column indexes of all the vertical page breaks, never {@code null}
*/
@Override
public int[] getColumnBreaks() {
return worksheet.isSetColBreaks() ? getBreaks(worksheet.getColBreaks()) : new int[0];
}
/**
* Get the actual column width (in units of 1/256th of a character width )
*
* <p>
* Note, the returned value is always greater that {@link #getDefaultColumnWidth()} because the latter does not include margins.
* Actual column width measured as the number of characters of the maximum digit width of the
* numbers 0, 1, 2, ..., 9 as rendered in the normal style's font. There are 4 pixels of margin
* padding (two on each side), plus 1 pixel padding for the gridlines.
* </p>
*
* @param columnIndex - the column to set (0-based)
* @return width - the width in units of 1/256th of a character width
*/
@Override
public int getColumnWidth(int columnIndex) {
CTCol col = columnHelper.getColumn(columnIndex, false);
double width = col == null || !col.isSetWidth() ? getDefaultColumnWidth() : col.getWidth();
return Math.toIntExact(Math.round(width*256));
}
/**
* Get the actual column width in pixels
*
* <p>
* Please note, that this method works correctly only for workbooks
* with the default font size (Calibri 11pt for .xlsx).
* </p>
*/
@Override
public float getColumnWidthInPixels(int columnIndex) {
float widthIn256 = getColumnWidth(columnIndex);
return (float)(widthIn256/256.0*Units.DEFAULT_CHARACTER_WIDTH);
}
/**
* Get the default column width for the sheet (if the columns do not define their own width) in
* characters.
* <p>
* Note, this value is different from {@link #getColumnWidth(int)}. The latter is always greater and includes
* 4 pixels of margin padding (two on each side), plus 1 pixel padding for the gridlines.
* </p>
* @return column width, default value is 8
*/
@Override
public int getDefaultColumnWidth() {
CTSheetFormatPr pr = worksheet.getSheetFormatPr();
return pr == null ? 8 : Math.toIntExact(pr.getBaseColWidth());
}
/**
* Get the default row height for the sheet (if the rows do not define their own height) in
* twips (1/20 of a point)
*
* @return default row height
*/
@Override
public short getDefaultRowHeight() {
return (short)(getDefaultRowHeightInPoints() * Font.TWIPS_PER_POINT);
}
/**
* Get the default row height for the sheet measured in point size (if the rows do not define their own height).
*
* @return default row height in points
*/
@Override
public float getDefaultRowHeightInPoints() {
CTSheetFormatPr pr = worksheet.getSheetFormatPr();
return (float)(pr == null ? 0 : pr.getDefaultRowHeight());
}
private CTSheetFormatPr getSheetTypeSheetFormatPr() {
return worksheet.isSetSheetFormatPr() ?
worksheet.getSheetFormatPr() :
worksheet.addNewSheetFormatPr();
}
/**
* Returns the CellStyle that applies to the given
* (0 based) column, or null if no style has been
* set for that column
*/
@Override
public CellStyle getColumnStyle(int column) {
int idx = columnHelper.getColDefaultStyle(column);
return getWorkbook().getCellStyleAt((short)(idx == -1 ? 0 : idx));
}
/**
* Sets whether the worksheet is displayed from right to left instead of from left to right.
*
* @param value true for right to left, false otherwise.
*/
@Override
public void setRightToLeft(boolean value) {
final CTSheetView dsv = getDefaultSheetView(true);
assert(dsv != null);
dsv.setRightToLeft(value);
}
/**
* Whether the text is displayed in right-to-left mode in the window
*
* @return whether the text is displayed in right-to-left mode in the window
*/
@Override
public boolean isRightToLeft() {
final CTSheetView dsv = getDefaultSheetView(false);
return (dsv != null && dsv.getRightToLeft());
}
/**
* Get whether to display the guts or not,
* default value is true
*
* @return boolean - guts or no guts
*/
@Override
public boolean getDisplayGuts() {
CTSheetPr sheetPr = getSheetTypeSheetPr();
CTOutlinePr outlinePr = sheetPr.getOutlinePr() == null ? CTOutlinePr.Factory.newInstance() : sheetPr.getOutlinePr();
return outlinePr.getShowOutlineSymbols();
}
/**
* Set whether to display the guts or not
*
* @param value - guts or no guts
*/
@Override
public void setDisplayGuts(boolean value) {
CTSheetPr sheetPr = getSheetTypeSheetPr();
CTOutlinePr outlinePr = sheetPr.getOutlinePr() == null ? sheetPr.addNewOutlinePr() : sheetPr.getOutlinePr();
outlinePr.setShowOutlineSymbols(value);
}
/**
* Gets the flag indicating whether the window should show 0 (zero) in cells containing zero value.
* When false, cells with zero value appear blank instead of showing the number zero.
*
* @return whether all zero values on the worksheet are displayed (defaults to true)
*/
@Override
public boolean isDisplayZeros(){
final CTSheetView dsv = getDefaultSheetView(false);
return (dsv == null) || dsv.getShowZeros();
}
/**
* Set whether the window should show 0 (zero) in cells containing zero value.
* When false, cells with zero value appear blank instead of showing the number zero.
*
* @param value whether to display or hide all zero values on the worksheet
*/
@Override
public void setDisplayZeros(boolean value){
final CTSheetView view = getDefaultSheetView(true);
assert(view != null);
view.setShowZeros(value);
}
/**
* Gets the first row on the sheet
*
* @return the number of the first logical row on the sheet, zero based
*/
@Override
public int getFirstRowNum() {
return _rows.isEmpty() ? -1 : _rows.firstKey();
}
/**
* Flag indicating whether the Fit to Page print option is enabled.
*
* @return {@code true}
*/
@Override
public boolean getFitToPage() {
CTSheetPr sheetPr = getSheetTypeSheetPr();
CTPageSetUpPr psSetup = (sheetPr == null || !sheetPr.isSetPageSetUpPr()) ?
CTPageSetUpPr.Factory.newInstance() : sheetPr.getPageSetUpPr();
return psSetup.getFitToPage();
}
private CTSheetPr getSheetTypeSheetPr() {
if (worksheet.getSheetPr() == null) {
worksheet.setSheetPr(CTSheetPr.Factory.newInstance());
}
return worksheet.getSheetPr();
}
private CTHeaderFooter getSheetTypeHeaderFooter() {
if (worksheet.getHeaderFooter() == null) {
worksheet.setHeaderFooter(CTHeaderFooter.Factory.newInstance());
}
return worksheet.getHeaderFooter();
}
/**
* Returns the default footer for the sheet,
* creating one as needed.
* You may also want to look at
* {@link #getFirstFooter()},
* {@link #getOddFooter()} and
* {@link #getEvenFooter()}
*/
@Override
public Footer getFooter() {
// The default footer is an odd footer
return getOddFooter();
}
/**
* Returns the default header for the sheet,
* creating one as needed.
* You may also want to look at
* {@link #getFirstHeader()},
* {@link #getOddHeader()} and
* {@link #getEvenHeader()}
*/
@Override
public Header getHeader() {
// The default header is an odd header
return getOddHeader();
}
/**
* Returns the odd footer. Used on all pages unless
* other footers also present, when used on only
* odd pages.
*/
public Footer getOddFooter() {
return new XSSFOddFooter(getSheetTypeHeaderFooter());
}
/**
* Returns the even footer. Not there by default, but
* when set, used on even pages.
*/
public Footer getEvenFooter() {
return new XSSFEvenFooter(getSheetTypeHeaderFooter());
}
/**
* Returns the first page footer. Not there by
* default, but when set, used on the first page.
*/
public Footer getFirstFooter() {
return new XSSFFirstFooter(getSheetTypeHeaderFooter());
}
/**
* Returns the odd header. Used on all pages unless
* other headers also present, when used on only
* odd pages.
*/
public Header getOddHeader() {
return new XSSFOddHeader(getSheetTypeHeaderFooter());
}
/**
* Returns the even header. Not there by default, but
* when set, used on even pages.
*/
public Header getEvenHeader() {
return new XSSFEvenHeader(getSheetTypeHeaderFooter());
}
/**
* Returns the first page header. Not there by
* default, but when set, used on the first page.
*/
public Header getFirstHeader() {
return new XSSFFirstHeader(getSheetTypeHeaderFooter());
}
/**
* Determine whether printed output for this sheet will be horizontally centered.
*/
@Override
public boolean getHorizontallyCenter() {
CTPrintOptions opts = worksheet.getPrintOptions();
return opts != null && opts.getHorizontalCentered();
}
@Override
public int getLastRowNum() {
// _rows.getLastKey() (O(logN)) or caching last row (O(1))?
// A test with 1_000_000 rows shows that querying getLastRowNum with lastKey() implementation takes ~40 ms,
// and ~1.2 ms with cached implementation. 40 ms is negligible compared to the time of evaluation a million
// cells, and the lastKey implementation is much more elegant and less error prone than caching.
return _rows.isEmpty() ? -1 : _rows.lastKey();
}
@Override
public short getLeftCol() {
String cellRef = worksheet.getSheetViews().getSheetViewArray(0).getTopLeftCell();
if(cellRef == null) {
return 0;
}
CellReference cellReference = new CellReference(cellRef);
return cellReference.getCol();
}
/**
* Gets the size of the margin in inches.
*
* @param margin which margin to get
* @return the size of the margin
* @see Sheet#LeftMargin
* @see Sheet#RightMargin
* @see Sheet#TopMargin
* @see Sheet#BottomMargin
* @see Sheet#HeaderMargin
* @see Sheet#FooterMargin
* @deprecated use {@link #getMargin(PageMargin)}
*/
@Override
@Deprecated
@Removal(version = "7.0.0")
public double getMargin(short margin) {
return getMargin(PageMargin.getByShortValue(margin));
}
/**
* Gets the size of the margin in inches.
*
* @param margin which margin to get
* @return the size of the margin
* @since POI 5.2.3
*/
@Override
public double getMargin(PageMargin margin) {
if (!worksheet.isSetPageMargins()) {
return 0;
}
CTPageMargins pageMargins = worksheet.getPageMargins();
switch (margin) {
case LEFT:
return pageMargins.getLeft();
case RIGHT:
return pageMargins.getRight();
case TOP:
return pageMargins.getTop();
case BOTTOM:
return pageMargins.getBottom();
case HEADER:
return pageMargins.getHeader();
case FOOTER:
return pageMargins.getFooter();
default :
throw new IllegalArgumentException("Unknown margin constant: " + margin);
}
}
/**
* Sets the size of the margin in inches.
*
* @param margin which margin to set
* @param size the size of the margin
* @see Sheet#LeftMargin
* @see Sheet#RightMargin
* @see Sheet#TopMargin
* @see Sheet#BottomMargin
* @see Sheet#HeaderMargin
* @see Sheet#FooterMargin
* @deprecated use {@link #setMargin(PageMargin, double)} instead
*/
@Override
@Deprecated
@Removal(version = "7.0.0")
public void setMargin(short margin, double size) {
final PageMargin pageMargin = PageMargin.getByShortValue(margin);
if (pageMargin == null) {
throw new IllegalArgumentException( "Unknown margin constant: " + margin );
}
setMargin(pageMargin, size);
}
/**
* Sets the size of the margin in inches.
*
* @param margin which margin to set
* @param size the size of the margin
* @since POI 5.2.3
*/
@Override
public void setMargin(PageMargin margin, double size) {
CTPageMargins pageMargins = worksheet.isSetPageMargins() ?
worksheet.getPageMargins() : worksheet.addNewPageMargins();
switch (margin) {
case LEFT:
pageMargins.setLeft(size);
break;
case RIGHT:
pageMargins.setRight(size);
break;
case TOP:
pageMargins.setTop(size);
break;
case BOTTOM:
pageMargins.setBottom(size);
break;
case HEADER:
pageMargins.setHeader(size);
break;
case FOOTER:
pageMargins.setFooter(size);
break;
default:
throw new IllegalArgumentException( "Unknown margin constant: " + margin );
}
}
/**
* Returns the merged region at the specified index. If you want multiple
* regions, it is faster to call {@link #getMergedRegions()} than to call
* this each time.
*
* @return the merged region at the specified index
*/
@Override
public CellRangeAddress getMergedRegion(int index) {
CTMergeCells ctMergeCells = worksheet.getMergeCells();
if(ctMergeCells == null) {
throw new IllegalStateException("This worksheet does not contain merged regions");
}
CTMergeCell ctMergeCell = ctMergeCells.getMergeCellArray(index);
String ref = ctMergeCell.getRef();
return CellRangeAddress.valueOf(ref);
}
/**
* Returns the list of merged regions. If you want multiple regions, this is
* faster than calling {@link #getMergedRegion(int)} each time.
*
* @return the list of merged regions
*/
@Override
public List<CellRangeAddress> getMergedRegions() {
List<CellRangeAddress> addresses = new ArrayList<>();
CTMergeCells ctMergeCells = worksheet.getMergeCells();
if(ctMergeCells == null) {
return addresses;
}
for(CTMergeCell ctMergeCell : ctMergeCells.getMergeCellArray()) {
String ref = ctMergeCell.getRef();
addresses.add(CellRangeAddress.valueOf(ref));
}
return addresses;
}
/**
* Returns the number of merged regions defined in this worksheet
*
* @return number of merged regions in this worksheet
*/
@Override
public int getNumMergedRegions() {
CTMergeCells ctMergeCells = worksheet.getMergeCells();
return ctMergeCells == null ? 0 : ctMergeCells.sizeOfMergeCellArray();
}
public int getNumHyperlinks() {
return hyperlinks.size();
}
/**
* Returns the information regarding the currently configured pane (split or freeze).
*
* @return null if no pane configured, or the pane information.
*/
@Override
public PaneInformation getPaneInformation() {
final CTPane pane = getPane(false);
// no pane configured
if(pane == null) {
return null;
}
short row = 0, col = 0;
if (pane.isSetTopLeftCell()) {
final CellReference cellRef = new CellReference(pane.getTopLeftCell());
row = (short)cellRef.getRow();
col = cellRef.getCol();
}
final short x = (short)pane.getXSplit();
final short y = (short)pane.getYSplit();
final byte active = (byte)(pane.getActivePane().intValue() - 1);
final boolean frozen = pane.getState() == STPaneState.FROZEN;
return new PaneInformation(x, y, row, col, active, frozen);
}
/**
* Returns the number of physically defined rows (NOT the number of rows in the sheet)
*
* @return the number of physically defined rows
*/
@Override
public int getPhysicalNumberOfRows() {
return _rows.size();
}
/**
* Gets the print setup object.
*
* @return The user model for the print setup object.
*/
@Override
public XSSFPrintSetup getPrintSetup() {
return new XSSFPrintSetup(worksheet);
}
/**
* Answer whether protection is enabled or disabled
*
* @return true => protection enabled; false => protection disabled
*/
@Override
public boolean getProtect() {
return isSheetLocked();
}
/**
* Enables sheet protection and sets the password for the sheet.
* Also sets some attributes on the {@link CTSheetProtection} that correspond to
* the default values used by Excel
*
* @param password to set for protection. Pass {@code null} to remove protection
*/
@Override
public void protectSheet(String password) {
if (password != null) {
CTSheetProtection sheetProtection = safeGetProtectionField();
setSheetPassword(password, null); // defaults to xor password
sheetProtection.setSheet(true);
sheetProtection.setScenarios(true);
sheetProtection.setObjects(true);
} else {
worksheet.unsetSheetProtection();
}
}
/**
* Sets the sheet password.
*
* @param password if null, the password will be removed
* @param hashAlgo if null, the password will be set as XOR password (Excel 2010 and earlier)
* otherwise the given algorithm is used for calculating the hash password (Excel 2013)
*/
public void setSheetPassword(String password, HashAlgorithm hashAlgo) {
if (password == null && !isSheetProtectionEnabled()) {
return;
}
setPassword(safeGetProtectionField(), password, hashAlgo, null);
}
/**
* Validate the password against the stored hash, the hashing method will be determined
* by the existing password attributes
* @return true, if the hashes match (... though original password may differ ...)
*/
public boolean validateSheetPassword(String password) {
if (!isSheetProtectionEnabled()) {
return (password == null);
}
return validatePassword(safeGetProtectionField(), password, null);
}
/**
* Returns the logical row ( 0-based). If you ask for a row that is not
* defined you get a null. This is to say row 4 represents the fifth row on a sheet.
*
* @param rownum row to get
* @return {@code XSSFRow} representing the rownumber or {@code null} if its not defined on the sheet
*/
@Override
public XSSFRow getRow(int rownum) {
// Performance optimization: explicit boxing is slightly faster than auto-unboxing, though may use more memory
//noinspection UnnecessaryBoxing
final Integer rownumI = Integer.valueOf(rownum); // NOSONAR
return _rows.get(rownumI);
}
/**
* returns all rows between startRow and endRow, inclusive.
* Rows between startRow and endRow that haven't been created are not included
* in result unless createRowIfMissing is true
*
* @param startRowNum the first row number in this sheet to return
* @param endRowNum the last row number in this sheet to return
* @param createRowIfMissing If missing rows should be created.
* @return All rows between startRow and endRow, inclusive. If createRowIfMissing is false,
* only previously existing rows are returned, otherwise empty rows are added as necessary
* @throws IllegalArgumentException if startRowNum and endRowNum are not in ascending order
*/
private List<XSSFRow> getRows(int startRowNum, int endRowNum, boolean createRowIfMissing) {
if (startRowNum > endRowNum) {
throw new IllegalArgumentException("getRows: startRowNum must be less than or equal to endRowNum");
}
final List<XSSFRow> rows = new ArrayList<>();
if (createRowIfMissing) {
for (int i = startRowNum; i <= endRowNum; i++) {
XSSFRow row = getRow(i);
if (row == null) {
row = createRow(i);
}
rows.add(row);
}
}
else {
// Performance optimization: explicit boxing is slightly faster than auto-unboxing, though may use more memory
//noinspection UnnecessaryBoxing
final Integer startI = Integer.valueOf(startRowNum); // NOSONAR
//noinspection UnnecessaryBoxing
final Integer endI = Integer.valueOf(endRowNum+1); // NOSONAR
final Collection<XSSFRow> inclusive = _rows.subMap(startI, endI).values();
rows.addAll(inclusive);
}
return rows;
}
/**
* Horizontal page break information used for print layout view, page layout view, drawing print breaks in normal
* view, and for printing the worksheet.
*
* @return row indexes of all the horizontal page breaks, never {@code null}
*/
@Override
public int[] getRowBreaks() {
return worksheet.isSetRowBreaks() ? getBreaks(worksheet.getRowBreaks()) : new int[0];
}
/**
* Flag indicating whether summary rows appear below detail in an outline, when applying an outline.
*
* <p>
* When true a summary row is inserted below the detailed data being summarized and a
* new outline level is established on that row.
* </p>
* <p>
* When false a summary row is inserted above the detailed data being summarized and a new outline level
* is established on that row.
* </p>
* @return {@code true} if row summaries appear below detail in the outline
*/
@Override
public boolean getRowSumsBelow() {
CTSheetPr sheetPr = worksheet.getSheetPr();
CTOutlinePr outlinePr = (sheetPr != null && sheetPr.isSetOutlinePr())
? sheetPr.getOutlinePr() : null;
return outlinePr == null || outlinePr.getSummaryBelow();
}
/**
* Flag indicating whether summary rows appear below detail in an outline, when applying an outline.
*
* <p>
* When true a summary row is inserted below the detailed data being summarized and a
* new outline level is established on that row.
* </p>
* <p>
* When false a summary row is inserted above the detailed data being summarized and a new outline level
* is established on that row.
* </p>
* @param value {@code true} if row summaries appear below detail in the outline
*/
@Override
public void setRowSumsBelow(boolean value) {
ensureOutlinePr().setSummaryBelow(value);
}
/**
* Flag indicating whether summary columns appear to the right of detail in an outline, when applying an outline.
*
* <p>
* When true a summary column is inserted to the right of the detailed data being summarized
* and a new outline level is established on that column.
* </p>
* <p>
* When false a summary column is inserted to the left of the detailed data being
* summarized and a new outline level is established on that column.
* </p>
* @return {@code true} if col summaries appear right of the detail in the outline
*/
@Override
public boolean getRowSumsRight() {
CTSheetPr sheetPr = worksheet.getSheetPr();
CTOutlinePr outlinePr = (sheetPr != null && sheetPr.isSetOutlinePr())
? sheetPr.getOutlinePr() : CTOutlinePr.Factory.newInstance();
return outlinePr.getSummaryRight();
}
/**
* Flag indicating whether summary columns appear to the right of detail in an outline, when applying an outline.
*
* <p>
* When true a summary column is inserted to the right of the detailed data being summarized
* and a new outline level is established on that column.
* </p>
* <p>
* When false a summary column is inserted to the left of the detailed data being
* summarized and a new outline level is established on that column.
* </p>
* @param value {@code true} if col summaries appear right of the detail in the outline
*/
@Override
public void setRowSumsRight(boolean value) {
ensureOutlinePr().setSummaryRight(value);
}
/**
* Ensure CTWorksheet.CTSheetPr.CTOutlinePr
*/
private CTOutlinePr ensureOutlinePr(){
CTSheetPr sheetPr = worksheet.isSetSheetPr() ? worksheet.getSheetPr() : worksheet.addNewSheetPr();
return sheetPr.isSetOutlinePr() ? sheetPr.getOutlinePr() : sheetPr.addNewOutlinePr();
}
/**
* A flag indicating whether scenarios are locked when the sheet is protected.
*
* @return true => protection enabled; false => protection disabled
*/
@Override
public boolean getScenarioProtect() {
return worksheet.isSetSheetProtection() && worksheet.getSheetProtection().getScenarios();
}
/**
* The top row in the visible view when the sheet is
* first viewed after opening it in a viewer
*
* @return integer indicating the rownum (0 based) of the top row
*/
@Override
public short getTopRow() {
final CTSheetView dsv = getDefaultSheetView(false);
final String cellRef = (dsv == null) ? null : dsv.getTopLeftCell();
if(cellRef == null) {
return 0;
}
return (short) new CellReference(cellRef).getRow();
}
/**
* Determine whether printed output for this sheet will be vertically centered.
*
* @return whether printed output for this sheet will be vertically centered.
*/
@Override
public boolean getVerticallyCenter() {
CTPrintOptions opts = worksheet.getPrintOptions();
return opts != null && opts.getVerticalCentered();
}
/**
* Group between (0 based) columns
*/
@Override
public void groupColumn(int fromColumn, int toColumn) {
groupColumn1Based(fromColumn+1, toColumn+1);
}
private void groupColumn1Based(int fromColumn, int toColumn) {
CTCols ctCols=worksheet.getColsArray(0);
CTCol ctCol=CTCol.Factory.newInstance();
// copy attributes, as they might be removed by merging with the new column
// TODO: check if this fix is really necessary or if the sweeping algorithm
// in addCleanColIntoCols needs to be adapted ...
CTCol fixCol_before = this.columnHelper.getColumn1Based(toColumn, false);
if (fixCol_before != null) {
fixCol_before = (CTCol)fixCol_before.copy();
}
ctCol.setMin(fromColumn);
ctCol.setMax(toColumn);
this.columnHelper.addCleanColIntoCols(ctCols, ctCol);
CTCol fixCol_after = this.columnHelper.getColumn1Based(toColumn, false);
if (fixCol_before != null && fixCol_after != null) {
this.columnHelper.setColumnAttributes(fixCol_before, fixCol_after);
}
for(int index=fromColumn;index<=toColumn;index++){
CTCol col=columnHelper.getColumn1Based(index, false);
//col must exist
short outlineLevel=col.getOutlineLevel();
col.setOutlineLevel((short)(outlineLevel+1));
index = Math.toIntExact(col.getMax());
}
worksheet.setColsArray(0,ctCols);
setSheetFormatPrOutlineLevelCol();
}
/**
* Do not leave the width attribute undefined (see #52186).
*/
private void setColWidthAttribute(CTCols ctCols) {
for (CTCol col : ctCols.getColArray()) {
if (!col.isSetWidth()) {
col.setWidth(getDefaultColumnWidth());
col.setCustomWidth(false);
}
}
}
/**
* Tie a range of cell together so that they can be collapsed or expanded
*
* @param fromRow start row (0-based)
* @param toRow end row (0-based)
*/
@Override
public void groupRow(int fromRow, int toRow) {
for (int i = fromRow; i <= toRow; i++) {
XSSFRow xrow = getRow(i);
if (xrow == null) {
xrow = createRow(i);
}
CTRow ctrow = xrow.getCTRow();
short outlineLevel = ctrow.getOutlineLevel();
ctrow.setOutlineLevel((short) (outlineLevel + 1));
}
setSheetFormatPrOutlineLevelRow();
}
private short getMaxOutlineLevelRows(){
int outlineLevel = 0;
for (XSSFRow xrow : _rows.values()) {
outlineLevel = Math.max(outlineLevel, xrow.getCTRow().getOutlineLevel());
}
return (short) outlineLevel;
}
private short getMaxOutlineLevelCols() {
CTCols ctCols = worksheet.getColsArray(0);
int outlineLevel = 0;
for (CTCol col : ctCols.getColArray()) {
outlineLevel = Math.max(outlineLevel, col.getOutlineLevel());
}
return (short) outlineLevel;
}
/**
* Determines if there is a page break at the indicated column
*/
@Override
public boolean isColumnBroken(int column) {
for (int colBreak : getColumnBreaks()) {
if (colBreak == column) {
return true;
}
}
return false;
}
/**
* Get the hidden state for a given column.
*
* @param columnIndex - the column to set (0-based)
* @return hidden - {@code false} if the column is visible
*/
@Override
public boolean isColumnHidden(int columnIndex) {
CTCol col = columnHelper.getColumn(columnIndex, false);
return col != null && col.getHidden();
}
/**
* Gets the flag indicating whether this sheet should display formulas.
*
* @return {@code true} if this sheet should display formulas.
*/
@Override
public boolean isDisplayFormulas() {
final CTSheetView dsv = getDefaultSheetView(false);
return dsv != null && dsv.getShowFormulas();
}
/**
* Gets the flag indicating whether this sheet displays the lines
* between rows and columns to make editing and reading easier.
*
* @return {@code true} (default) if this sheet displays gridlines.
* @see #isPrintGridlines() to check if printing of gridlines is turned on or off
*/
@Override
public boolean isDisplayGridlines() {
final CTSheetView dsv = getDefaultSheetView(false);
return (dsv == null) || dsv.getShowGridLines();
}
/**
* Sets the flag indicating whether this sheet should display the lines
* between rows and columns to make editing and reading easier.
* To turn printing of gridlines use {@link #setPrintGridlines(boolean)}
*
*
* @param show {@code true} if this sheet should display gridlines.
* @see #setPrintGridlines(boolean)
*/
@Override
public void setDisplayGridlines(boolean show) {
final CTSheetView dsv = getDefaultSheetView(true);
assert(dsv != null);
dsv.setShowGridLines(show);
}
/**
* Gets the flag indicating whether this sheet should display row and column headings.
* <p>
* Row heading are the row numbers to the side of the sheet
* </p>
* <p>
* Column heading are the letters or numbers that appear above the columns of the sheet
* </p>
*
* @return {@code true} (default) if this sheet should display row and column headings.
*/
@Override
public boolean isDisplayRowColHeadings() {
final CTSheetView dsv = getDefaultSheetView(false);
return (dsv == null) || dsv.getShowRowColHeaders();
}
/**
* Sets the flag indicating whether this sheet should display row and column headings.
* <p>
* Row heading are the row numbers to the side of the sheet
* </p>
* <p>
* Column heading are the letters or numbers that appear above the columns of the sheet
* </p>
*
* @param show {@code true} if this sheet should display row and column headings.
*/
@Override
public void setDisplayRowColHeadings(boolean show) {
final CTSheetView dsv = getDefaultSheetView(true);
assert(dsv != null);
dsv.setShowRowColHeaders(show);
}
/**
* Returns whether gridlines are printed.
*
* @return whether gridlines are printed
*/
@Override
public boolean isPrintGridlines() {
CTPrintOptions opts = worksheet.getPrintOptions();
return opts != null && opts.getGridLines();
}
/**
* Turns on or off the printing of gridlines.
*
* @param value boolean to turn on or off the printing of gridlines
*/
@Override
public void setPrintGridlines(boolean value) {
CTPrintOptions opts = worksheet.isSetPrintOptions() ?
worksheet.getPrintOptions() : worksheet.addNewPrintOptions();
opts.setGridLines(value);
}
/**
* Returns whether row and column headings are printed.
*
* @return whether row and column headings are printed
*/
@Override
public boolean isPrintRowAndColumnHeadings() {
CTPrintOptions opts = worksheet.getPrintOptions();
return opts != null && opts.getHeadings();
}
/**
* Turns on or off the printing of row and column headings.
*
* @param value boolean to turn on or off the printing of row and column headings
*/
@Override
public void setPrintRowAndColumnHeadings(boolean value) {
CTPrintOptions opts = worksheet.isSetPrintOptions() ?
worksheet.getPrintOptions() : worksheet.addNewPrintOptions();
opts.setHeadings(value);
}
/**
* Tests if there is a page break at the indicated row
*
* @param row index of the row to test
* @return {@code true} if there is a page break at the indicated row
*/
@Override
public boolean isRowBroken(int row) {
for (int rowBreak : getRowBreaks()) {
if (rowBreak == row) {
return true;
}
}
return false;
}
private void setBreak(int id, CTPageBreak ctPgBreak, int lastIndex) {
CTBreak brk = ctPgBreak.addNewBrk();
// this is id of the element which is 1-based: <row r="1" ... >
brk.setId(id + 1L);
brk.setMan(true);
// end column of the break
brk.setMax(lastIndex);
int nPageBreaks = ctPgBreak.sizeOfBrkArray();
ctPgBreak.setCount(nPageBreaks);
ctPgBreak.setManualBreakCount(nPageBreaks);
}
/**
* Sets a page break at the indicated row
* Breaks occur above the specified row and left of the specified column inclusive.
*
* For example, {@code sheet.setColumnBreak(2);} breaks the sheet into two parts
* with columns A,B,C in the first and D,E,... in the second. Simuilar, {@code sheet.setRowBreak(2);}
* breaks the sheet into two parts with first three rows (rownum=1...3) in the first part
* and rows starting with rownum=4 in the second.
*
* @param row the row to break, inclusive
*/
@Override
public void setRowBreak(int row) {
if (!isRowBroken(row)) {
CTPageBreak pgBreak = worksheet.isSetRowBreaks() ? worksheet.getRowBreaks() : worksheet.addNewRowBreaks();
setBreak(row, pgBreak, SpreadsheetVersion.EXCEL2007.getLastColumnIndex());
}
}
/**
* Removes a page break at the indicated column
*/
@Override
public void removeColumnBreak(int column) {
if (worksheet.isSetColBreaks()) {
removeBreak(column, worksheet.getColBreaks());
} // else no breaks
}
/**
* Removes a merged region of cells (hence letting them free)
*
* @param index of the region to unmerge
*/
@Override
public void removeMergedRegion(int index) {
if (!worksheet.isSetMergeCells()) {
return;
}
CTMergeCells ctMergeCells = worksheet.getMergeCells();
int size = ctMergeCells.sizeOfMergeCellArray();
assert(0 <= index && index < size);
if (size > 1) {
ctMergeCells.removeMergeCell(index);
} else {
worksheet.unsetMergeCells();
}
}
/**
* Removes a number of merged regions of cells (hence letting them free)
*
* This method can be used to bulk-remove merged regions in a way
* much faster than calling removeMergedRegion() for every single
* merged region.
*
* @param indices A set of the regions to unmerge
*/
@Override
public void removeMergedRegions(Collection<Integer> indices) {
if (!worksheet.isSetMergeCells()) {
return;
}
CTMergeCells ctMergeCells = worksheet.getMergeCells();
List<CTMergeCell> newMergeCells = new ArrayList<>(ctMergeCells.sizeOfMergeCellArray());
int idx = 0;
for (CTMergeCell mc : ctMergeCells.getMergeCellArray()) {
if (!indices.contains(idx++)) {
newMergeCells.add(mc);
}
}
if (newMergeCells.isEmpty()) {
worksheet.unsetMergeCells();
} else {
CTMergeCell[] newMergeCellsArray = new CTMergeCell[newMergeCells.size()];
ctMergeCells.setMergeCellArray(newMergeCells.toArray(newMergeCellsArray));
}
}
/**
* Remove a row from this sheet. All cells contained in the row are removed as well
*
* @param row the row to remove.
*/
@Override
public void removeRow(Row row) {
if (row.getSheet() != this) {
throw new IllegalArgumentException("Specified row does not belong to this sheet");
}
// collect cells into a temporary array to avoid ConcurrentModificationException
ArrayList<XSSFCell> cellsToDelete = new ArrayList<>();
for (Cell cell : row) {
cellsToDelete.add((XSSFCell)cell);
}
for (XSSFCell cell : cellsToDelete) {
row.removeCell(cell);
}
final int rowNum = row.getRowNum();
// Performance optimization: explicit boxing is slightly faster than auto-unboxing, though may use more memory
//noinspection UnnecessaryBoxing
final Integer rowNumI = Integer.valueOf(rowNum); // NOSONAR
// this is not the physical row number!
final int idx = _rows.headMap(rowNumI).size();
_rows.remove(rowNumI);
worksheet.getSheetData().removeRow(idx);
// also remove any comment located in that row
if(sheetComments != null) {
for (CellAddress ref : getCellComments().keySet()) {
if (ref.getRow() == rowNum) {
sheetComments.removeComment(ref);
}
}
}
}
/**
* Removes the page break at the indicated row
*/
@Override
public void removeRowBreak(int row) {
if (worksheet.isSetRowBreaks()) {
removeBreak(row, worksheet.getRowBreaks());
} // else no breaks
}
/**
* Control if Excel should be asked to recalculate all formulas on this sheet
* when the workbook is opened.
*
* <p>
* Calculating the formula values with {@link FormulaEvaluator} is the
* recommended solution, but this may be used for certain cases where
* evaluation in POI is not possible.
* </p>
*
* <p>
* It is recommended to force recalcuation of formulas on workbook level using
* {@link Workbook#setForceFormulaRecalculation(boolean)}
* to ensure that all cross-worksheet formuals and external dependencies are updated.
* </p>
* @param value true if the application will perform a full recalculation of
* this worksheet values when the workbook is opened
*
* @see Workbook#setForceFormulaRecalculation(boolean)
*/
@Override
public void setForceFormulaRecalculation(boolean value) {
CTCalcPr calcPr = getWorkbook().getCTWorkbook().getCalcPr();
if(worksheet.isSetSheetCalcPr()) {
// Change the current setting
CTSheetCalcPr calc = worksheet.getSheetCalcPr();
calc.setFullCalcOnLoad(value);
}
else if(value) {
// Add the Calc block and set it
CTSheetCalcPr calc = worksheet.addNewSheetCalcPr();
calc.setFullCalcOnLoad(value);
}
if(value && calcPr != null && calcPr.getCalcMode() == STCalcMode.MANUAL) {
calcPr.setCalcMode(STCalcMode.AUTO);
}
}
/**
* Whether Excel will be asked to recalculate all formulas of this sheet
* when the workbook is opened.
*
* Note: This just returns if the sheet has the recalculate flag set and
* will still return false even if recalculation is enabled on workbook-level.
*
* @return true if the Sheet has the recalculate-flag set.
*/
@Override
public boolean getForceFormulaRecalculation() {
if(worksheet.isSetSheetCalcPr()) {
CTSheetCalcPr calc = worksheet.getSheetCalcPr();
return calc.getFullCalcOnLoad();
}
return false;
}
/**
* @return an iterator of the PHYSICAL rows. Meaning the 3rd element may not
* be the third row if say for instance the second row is undefined.
* Call getRowNum() on each row if you care which one it is.
*/
@Override
@SuppressWarnings("unchecked")
public Iterator<Row> rowIterator() {
return (Iterator<Row>)(Iterator<? extends Row>) _rows.values().iterator();
}
/**
* @return a spliterator of the PHYSICAL rows. Meaning the 3rd element may not
* be the third row if say for instance the second row is undefined.
* Call getRowNum() on each row if you care which one it is.
*
* @since POI 5.2.0
*/
@Override
@SuppressWarnings("unchecked")
public Spliterator<Row> spliterator() {
return (Spliterator<Row>)(Spliterator<? extends Row>) _rows.values().spliterator();
}
/**
* Flag indicating whether the sheet displays Automatic Page Breaks.
*
* @return {@code true} if the sheet displays Automatic Page Breaks.
*/
@Override
public boolean getAutobreaks() {
CTSheetPr sheetPr = getSheetTypeSheetPr();
CTPageSetUpPr psSetup = (sheetPr == null || !sheetPr.isSetPageSetUpPr()) ?
CTPageSetUpPr.Factory.newInstance() : sheetPr.getPageSetUpPr();
return psSetup.getAutoPageBreaks();
}
/**
* Flag indicating whether the sheet displays Automatic Page Breaks.
*
* @param value {@code true} if the sheet displays Automatic Page Breaks.
*/
@Override
public void setAutobreaks(boolean value) {
CTSheetPr sheetPr = getSheetTypeSheetPr();
CTPageSetUpPr psSetup = sheetPr.isSetPageSetUpPr() ? sheetPr.getPageSetUpPr() : sheetPr.addNewPageSetUpPr();
psSetup.setAutoPageBreaks(value);
}
/**
* Sets a page break at the indicated column.
* Breaks occur above the specified row and left of the specified column inclusive.
*
* For example, {@code sheet.setColumnBreak(2);} breaks the sheet into two parts
* with columns A,B,C in the first and D,E,... in the second. Simuilar, {@code sheet.setRowBreak(2);}
* breaks the sheet into two parts with first three rows (rownum=1...3) in the first part
* and rows starting with rownum=4 in the second.
*
* @param column the column to break, inclusive
*/
@Override
public void setColumnBreak(int column) {
if (!isColumnBroken(column)) {
CTPageBreak pgBreak = worksheet.isSetColBreaks() ? worksheet.getColBreaks() : worksheet.addNewColBreaks();
setBreak(column, pgBreak, SpreadsheetVersion.EXCEL2007.getLastRowIndex());
}
}
@Override
public void setColumnGroupCollapsed(int columnNumber, boolean collapsed) {
if (collapsed) {
collapseColumn(columnNumber);
} else {
expandColumn(columnNumber);
}
}
private void collapseColumn(int columnNumber) {
CTCols cols = worksheet.getColsArray(0);
CTCol col = columnHelper.getColumn(columnNumber, false);
int colInfoIx = columnHelper.getIndexOfColumn(cols, col);
if (colInfoIx == -1) {
return;
}
// Find the start of the group.
int groupStartColInfoIx = findStartOfColumnOutlineGroup(colInfoIx);
CTCol columnInfo = cols.getColArray(groupStartColInfoIx);
// Hide all the columns until the end of the group
int lastColMax = setGroupHidden(groupStartColInfoIx, columnInfo
.getOutlineLevel(), true);
// write collapse field
setColumn(lastColMax + 1, 0, null, null, Boolean.TRUE);
}
private void setColumn(int targetColumnIx, Integer style,
Integer level, Boolean hidden, Boolean collapsed) {
CTCols cols = worksheet.getColsArray(0);
CTCol ci = null;
for (CTCol tci : cols.getColArray()) {
long tciMin = tci.getMin();
long tciMax = tci.getMax();
if (tciMin >= targetColumnIx && tciMax <= targetColumnIx) {
ci = tci;
break;
}
if (tciMin > targetColumnIx) {
// call column infos after k are for later columns
break; // exit now so k will be the correct insert pos
}
}
if (ci == null) {
// okay so there ISN'T a column info record that covers this column
// so lets create one!
CTCol nci = CTCol.Factory.newInstance();
nci.setMin(targetColumnIx);
nci.setMax(targetColumnIx);
unsetCollapsed(collapsed, nci);
this.columnHelper.addCleanColIntoCols(cols, nci);
return;
}
boolean styleChanged = style != null && ci.getStyle() != style;
boolean levelChanged = level != null && ci.getOutlineLevel() != level;
boolean hiddenChanged = hidden != null && ci.getHidden() != hidden;
boolean collapsedChanged = collapsed != null && ci.getCollapsed() != collapsed;
boolean columnChanged = levelChanged || hiddenChanged || collapsedChanged || styleChanged;
if (!columnChanged) {
// do nothing...nothing changed.
return;
}
long ciMin = ci.getMin();
long ciMax = ci.getMax();
if (ciMin == targetColumnIx && ciMax == targetColumnIx) {
// ColumnInfo ci for a single column, the target column
unsetCollapsed(collapsed, ci);
return;
}
if (ciMin == targetColumnIx || ciMax == targetColumnIx) {
// The target column is at either end of the multi-column ColumnInfo ci
// we'll just divide the info and create a new one
if (ciMin == targetColumnIx) {
ci.setMin(targetColumnIx + 1L);
} else {
ci.setMax(targetColumnIx - 1L);
}
CTCol nci = columnHelper.cloneCol(cols, ci);
nci.setMin(targetColumnIx);
unsetCollapsed(collapsed, nci);
this.columnHelper.addCleanColIntoCols(cols, nci);
} else {
// split to 3 records
CTCol ciMid = columnHelper.cloneCol(cols, ci);
CTCol ciEnd = columnHelper.cloneCol(cols, ci);
int lastcolumn = Math.toIntExact(ciMax);
ci.setMax(targetColumnIx - 1L);
ciMid.setMin(targetColumnIx);
ciMid.setMax(targetColumnIx);
unsetCollapsed(collapsed, ciMid);
this.columnHelper.addCleanColIntoCols(cols, ciMid);
ciEnd.setMin(targetColumnIx + 1L);
ciEnd.setMax(lastcolumn);
this.columnHelper.addCleanColIntoCols(cols, ciEnd);
}
}
private void unsetCollapsed(Boolean collapsed, CTCol ci) {
if (collapsed != null && collapsed) {
ci.setCollapsed(true);
} else {
ci.unsetCollapsed();
}
}
/**
* Sets all adjacent columns of the same outline level to the specified
* hidden status.
*
* @param pIdx
* the col info index of the start of the outline group
* @return the column index of the last column in the outline group
*/
private int setGroupHidden(int pIdx, int level, boolean hidden) {
CTCols cols = worksheet.getColsArray(0);
int idx = pIdx;
CTCol[] colArray = cols.getColArray();
CTCol columnInfo = colArray[idx];
while (idx < colArray.length) {
columnInfo.setHidden(hidden);
if (idx + 1 < colArray.length) {
CTCol nextColumnInfo = colArray[idx + 1];
if (!isAdjacentBefore(columnInfo, nextColumnInfo)) {
break;
}
if (nextColumnInfo.getOutlineLevel() < level) {
break;
}
columnInfo = nextColumnInfo;
}
idx++;
}
return Math.toIntExact(columnInfo.getMax());
}
private boolean isAdjacentBefore(CTCol col, CTCol otherCol) {
return col.getMax() == otherCol.getMin() - 1;
}
private int findStartOfColumnOutlineGroup(int pIdx) {
// Find the start of the group.
CTCols cols = worksheet.getColsArray(0);
CTCol[] colArray = cols.getColArray();
CTCol columnInfo = colArray[pIdx];
int level = columnInfo.getOutlineLevel();
int idx = pIdx;
while (idx != 0) {
CTCol prevColumnInfo = colArray[idx - 1];
if (!isAdjacentBefore(prevColumnInfo, columnInfo)) {
break;
}
if (prevColumnInfo.getOutlineLevel() < level) {
break;
}
idx--;
columnInfo = prevColumnInfo;
}
return idx;
}
private int findEndOfColumnOutlineGroup(int colInfoIndex) {
CTCols cols = worksheet.getColsArray(0);
// Find the end of the group.
CTCol[] colArray = cols.getColArray();
CTCol columnInfo = colArray[colInfoIndex];
int level = columnInfo.getOutlineLevel();
int idx = colInfoIndex;
int lastIdx = colArray.length - 1;
while (idx < lastIdx) {
CTCol nextColumnInfo = colArray[idx + 1];
if (!isAdjacentBefore(columnInfo, nextColumnInfo)) {
break;
}
if (nextColumnInfo.getOutlineLevel() < level) {
break;
}
idx++;
columnInfo = nextColumnInfo;
}
return idx;
}
private void expandColumn(int columnIndex) {
CTCols cols = worksheet.getColsArray(0);
CTCol col = columnHelper.getColumn(columnIndex, false);
int colInfoIx = columnHelper.getIndexOfColumn(cols, col);
int idx = col == null ? -1 : findColInfoIdx(Math.toIntExact(col.getMax()), colInfoIx);
if (idx == -1) {
return;
}
// If it is already expanded do nothing.
if (!isColumnGroupCollapsed(idx)) {
return;
}
// Find the start/end of the group.
int startIdx = findStartOfColumnOutlineGroup(idx);
int endIdx = findEndOfColumnOutlineGroup(idx);
// expand:
// collapsed bit must be unset
// hidden bit gets unset _if_ surrounding groups are expanded you can
// determine
// this by looking at the hidden bit of the enclosing group. You will
// have
// to look at the start and the end of the current group to determine
// which
// is the enclosing group
// hidden bit only is altered for this outline level. ie. don't
// uncollapse contained groups
CTCol[] colArray = cols.getColArray();
CTCol columnInfo = colArray[endIdx];
if (!isColumnGroupHiddenByParent(idx)) {
short outlineLevel = columnInfo.getOutlineLevel();
boolean nestedGroup = false;
for (int i = startIdx; i <= endIdx; i++) {
CTCol ci = colArray[i];
if (outlineLevel == ci.getOutlineLevel()) {
ci.unsetHidden();
if (nestedGroup) {
nestedGroup = false;
ci.setCollapsed(true);
}
} else {
nestedGroup = true;
}
}
}
// Write collapse flag (stored in a single col info record after this
// outline group)
setColumn(Math.toIntExact(columnInfo.getMax() + 1), null, null,
Boolean.FALSE, Boolean.FALSE);
}
private boolean isColumnGroupHiddenByParent(int idx) {
CTCols cols = worksheet.getColsArray(0);
// Look out outline details of end
int endLevel = 0;
boolean endHidden = false;
int endOfOutlineGroupIdx = findEndOfColumnOutlineGroup(idx);
CTCol[] colArray = cols.getColArray();
if (endOfOutlineGroupIdx < (colArray.length - 1)) {
CTCol nextInfo = colArray[endOfOutlineGroupIdx + 1];
if (isAdjacentBefore(colArray[endOfOutlineGroupIdx], nextInfo)) {
endLevel = nextInfo.getOutlineLevel();
endHidden = nextInfo.getHidden();
}
}
// Look out outline details of start
int startLevel = 0;
boolean startHidden = false;
int startOfOutlineGroupIdx = findStartOfColumnOutlineGroup(idx);
if (startOfOutlineGroupIdx > 0) {
CTCol prevInfo = colArray[startOfOutlineGroupIdx - 1];
if (isAdjacentBefore(prevInfo, colArray[startOfOutlineGroupIdx])) {
startLevel = prevInfo.getOutlineLevel();
startHidden = prevInfo.getHidden();
}
}
if (endLevel > startLevel) {
return endHidden;
}
return startHidden;
}
private int findColInfoIdx(int columnValue, int fromColInfoIdx) {
CTCols cols = worksheet.getColsArray(0);
if (columnValue < 0) {
throw new IllegalArgumentException(
"column parameter out of range: " + columnValue);
}
if (fromColInfoIdx < 0) {
throw new IllegalArgumentException(
"fromIdx parameter out of range: " + fromColInfoIdx);
}
CTCol[] colArray = cols.getColArray();
for (int k = fromColInfoIdx; k < colArray.length; k++) {
CTCol ci = colArray[k];
if (containsColumn(ci, columnValue)) {
return k;
}
if (ci.getMin() > fromColInfoIdx) {
break;
}
}
return -1;
}
private boolean containsColumn(CTCol col, int columnIndex) {
return col.getMin() <= columnIndex && columnIndex <= col.getMax();
}
/**
* 'Collapsed' state is stored in a single column col info record
* immediately after the outline group
*
* @param idx The column-index to check
* @return a boolean represented if the column is collapsed
*/
private boolean isColumnGroupCollapsed(int idx) {
CTCols cols = worksheet.getColsArray(0);
CTCol[] colArray = cols.getColArray();
int endOfOutlineGroupIdx = findEndOfColumnOutlineGroup(idx);
int nextColInfoIx = endOfOutlineGroupIdx + 1;
if (nextColInfoIx >= colArray.length) {
return false;
}
CTCol nextColInfo = colArray[nextColInfoIx];
CTCol col = colArray[endOfOutlineGroupIdx];
if (!isAdjacentBefore(col, nextColInfo)) {
return false;
}
return nextColInfo.getCollapsed();
}
/**
* Set the visibility state for a given column.
*
* @param columnIndex - the column to get (0-based)
* @param hidden - the visibility state of the column
*/
@Override
public void setColumnHidden(int columnIndex, boolean hidden) {
columnHelper.setColHidden(columnIndex, hidden);
}
/**
* Set the width (in units of 1/256th of a character width)
*
* <p>
* The maximum column width for an individual cell is 255 characters.
* This value represents the number of characters that can be displayed
* in a cell that is formatted with the standard font (first font in the workbook).
* </p>
*
* <p>
* Character width is defined as the maximum digit width
* of the numbers {@code 0, 1, 2, ... 9} as rendered
* using the default font (first font in the workbook).
* <br>
* Unless you are using a very special font, the default character is '0' (zero),
* this is true for Arial (default font font in HSSF) and Calibri (default font in XSSF)
* </p>
*
* <p>
* Please note, that the width set by this method includes 4 pixels of margin padding (two on each side),
* plus 1 pixel padding for the gridlines (Section 3.3.1.12 of the OOXML spec).
* This results is a slightly less value of visible characters than passed to this method (approx. 1/2 of a character).
* </p>
* <p>
* To compute the actual number of visible characters,
* Excel uses the following formula (Section 3.3.1.12 of the OOXML spec):
* </p>
* {@code
* width = Truncate([{Number of Visible Characters} *
* {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256
* }
* <p>Using the Calibri font as an example, the maximum digit width of 11 point font size is 7 pixels (at 96 dpi).
* If you set a column width to be eight characters wide, e.g. {@code setColumnWidth(columnIndex, 8*256)},
* then the actual value of visible characters (the value shown in Excel) is derived from the following equation:
* {@code
Truncate([numChars*7+5]/7*256)/256 = 8;
* }
*
* which gives {@code 7.29}.
*
* @param columnIndex - the column to set (0-based)
* @param width - the width in units of 1/256th of a character width
* @throws IllegalArgumentException if width > 255*256 (the maximum column width in Excel is 255 characters)
*/
@Override
public void setColumnWidth(int columnIndex, int width) {
if(width > 255*256) {
throw new IllegalArgumentException("The maximum column width for an individual cell is 255 characters.");
}
columnHelper.setColWidth(columnIndex, (double)width/256);
columnHelper.setCustomWidth(columnIndex, true);
}
@Override
public void setDefaultColumnStyle(int column, CellStyle style) {
columnHelper.setColDefaultStyle(column, style);
}
/**
* Specifies the number of characters of the maximum digit width of the normal style's font.
* This value does not include margin padding or extra padding for gridlines. It is only the
* number of characters.
*
* @param width the number of characters. Default value is {@code 8}.
*/
@Override
public void setDefaultColumnWidth(int width) {
getSheetTypeSheetFormatPr().setBaseColWidth(width);
}
/**
* Set the default row height for the sheet (if the rows do not define their own height) in
* twips (1/20 of a point)
*
* @param height default row height in twips (1/20 of a point)
*/
@Override
public void setDefaultRowHeight(short height) {
setDefaultRowHeightInPoints((float)height / Font.TWIPS_PER_POINT);
}
/**
* Sets default row height measured in point size.
*
* @param height default row height measured in point size.
*/
@Override
public void setDefaultRowHeightInPoints(float height) {
CTSheetFormatPr pr = getSheetTypeSheetFormatPr();
pr.setDefaultRowHeight(height);
pr.setCustomHeight(true);
}
/**
* Sets the flag indicating whether this sheet should display formulas.
*
* @param show {@code true} if this sheet should display formulas.
*/
@Override
public void setDisplayFormulas(boolean show) {
final CTSheetView dsv = getDefaultSheetView(true);
assert(dsv != null);
dsv.setShowFormulas(show);
}
/**
* Flag indicating whether the Fit to Page print option is enabled.
*
* @param b {@code true} if the Fit to Page print option is enabled.
*/
@Override
public void setFitToPage(boolean b) {
getSheetTypePageSetUpPr().setFitToPage(b);
}
/**
* Center on page horizontally when printing.
*
* @param value whether to center on page horizontally when printing.
*/
@Override
public void setHorizontallyCenter(boolean value) {
CTPrintOptions opts = worksheet.isSetPrintOptions() ?
worksheet.getPrintOptions() : worksheet.addNewPrintOptions();
opts.setHorizontalCentered(value);
}
/**
* Whether the output is vertically centered on the page.
*
* @param value true to vertically center, false otherwise.
*/
@Override
public void setVerticallyCenter(boolean value) {
CTPrintOptions opts = worksheet.isSetPrintOptions() ?
worksheet.getPrintOptions() : worksheet.addNewPrintOptions();
opts.setVerticalCentered(value);
}
/**
* group the row It is possible for collapsed to be false and yet still have
* the rows in question hidden. This can be achieved by having a lower
* outline level collapsed, thus hiding all the child rows. Note that in
* this case, if the lowest level were expanded, the middle level would
* remain collapsed.
*
* @param rowIndex -
* the row involved, 0 based
* @param collapse -
* boolean value for collapse
*/
@Override
public void setRowGroupCollapsed(int rowIndex, boolean collapse) {
if (collapse) {
collapseRow(rowIndex);
} else {
expandRow(rowIndex);
}
}
/**
* @param rowIndex the zero based row index to collapse
*/
private void collapseRow(int rowIndex) {
XSSFRow row = getRow(rowIndex);
if (row != null) {
int startRow = findStartOfRowOutlineGroup(rowIndex);
// Hide all the columns until the end of the group
int lastRow = writeHidden(row, startRow, true);
if (getRow(lastRow) != null) {
getRow(lastRow).getCTRow().setCollapsed(true);
} else {
XSSFRow newRow = createRow(lastRow);
newRow.getCTRow().setCollapsed(true);
}
}
}
/**
* @param rowIndex the zero based row index to find from
*/
private int findStartOfRowOutlineGroup(int rowIndex) {
// Find the start of the group.
short level = getRow(rowIndex).getCTRow().getOutlineLevel();
int currentRow = rowIndex;
while (getRow(currentRow) != null) {
if (getRow(currentRow).getCTRow().getOutlineLevel() < level) {
return currentRow + 1;
}
currentRow--;
}
return currentRow;
}
private int writeHidden(XSSFRow xRow, int rowIndex, boolean hidden) {
short level = xRow.getCTRow().getOutlineLevel();
for (Iterator<Row> it = rowIterator(); it.hasNext();) {
xRow = (XSSFRow) it.next();
// skip rows before the start of this group
if(xRow.getRowNum() < rowIndex) {
continue;
}
if (xRow.getCTRow().getOutlineLevel() >= level) {
xRow.getCTRow().setHidden(hidden);
rowIndex++;
}
}
return rowIndex;
}
/**
* @param rowNumber the zero based row index to expand
*/
private void expandRow(int rowNumber) {
if (rowNumber == -1) {
return;
}
XSSFRow row = getRow(rowNumber);
// If it is already expanded do nothing.
if (!row.getCTRow().isSetHidden()) {
return;
}
// Find the start of the group.
int startIdx = findStartOfRowOutlineGroup(rowNumber);
// Find the end of the group.
int endIdx = findEndOfRowOutlineGroup(rowNumber);
// expand:
// collapsed must be unset
// hidden bit gets unset _if_ surrounding groups are expanded you can
// determine
// this by looking at the hidden bit of the enclosing group. You will
// have
// to look at the start and the end of the current group to determine
// which
// is the enclosing group
// hidden bit only is altered for this outline level. ie. don't
// un-collapse contained groups
short level = row.getCTRow().getOutlineLevel();
if (!isRowGroupHiddenByParent(rowNumber)) {
for (int i = startIdx; i < endIdx; i++) {
if (level == getRow(i).getCTRow().getOutlineLevel()) {
getRow(i).getCTRow().unsetHidden();
} else if (!isRowGroupCollapsed(i)) {
getRow(i).getCTRow().unsetHidden();
}
}
}
// Write collapse field
CTRow ctRow = getRow(endIdx).getCTRow();
// This avoids an IndexOutOfBounds if multiple nested groups are collapsed/expanded
if(ctRow.getCollapsed()) {
ctRow.unsetCollapsed();
}
}
/**
* @param row the zero based row index to find from
*/
public int findEndOfRowOutlineGroup(int row) {
short level = getRow(row).getCTRow().getOutlineLevel();
int currentRow;
final int lastRowNum = getLastRowNum();
for (currentRow = row; currentRow < lastRowNum; currentRow++) {
if (getRow(currentRow) == null
|| getRow(currentRow).getCTRow().getOutlineLevel() < level) {
break;
}
}
return currentRow;
}
/**
* @param row the zero based row index to find from
*/
private boolean isRowGroupHiddenByParent(int row) {
// Look out outline details of end
int endLevel;
boolean endHidden;
int endOfOutlineGroupIdx = findEndOfRowOutlineGroup(row);
if (getRow(endOfOutlineGroupIdx) == null) {
endLevel = 0;
endHidden = false;
} else {
endLevel = getRow(endOfOutlineGroupIdx).getCTRow().getOutlineLevel();
endHidden = getRow(endOfOutlineGroupIdx).getCTRow().getHidden();
}
// Look out outline details of start
int startLevel;
boolean startHidden;
int startOfOutlineGroupIdx = findStartOfRowOutlineGroup(row);
if (startOfOutlineGroupIdx < 0
|| getRow(startOfOutlineGroupIdx) == null) {
startLevel = 0;
startHidden = false;
} else {
startLevel = getRow(startOfOutlineGroupIdx).getCTRow()
.getOutlineLevel();
startHidden = getRow(startOfOutlineGroupIdx).getCTRow()
.getHidden();
}
if (endLevel > startLevel) {
return endHidden;
}
return startHidden;
}
/**
* @param row the zero based row index to find from
*/
private boolean isRowGroupCollapsed(int row) {
int collapseRow = findEndOfRowOutlineGroup(row) + 1;
if (getRow(collapseRow) == null) {
return false;
}
return getRow(collapseRow).getCTRow().getCollapsed();
}
/**
* Window zoom magnification for current view representing percent values.
* Valid values range from 10 to 400. Horizontal & Vertical scale together.
*
* For example:
* <pre>
* 10 - 10%
* 20 - 20%
* ...
* 100 - 100%
* ...
* 400 - 400%
* </pre>
*
* Current view can be Normal, Page Layout, or Page Break Preview.
*
* @param scale window zoom magnification
* @throws IllegalArgumentException if scale is invalid
*/
@Override
public void setZoom(int scale) {
if (scale < 10 || scale > 400) {
throw new IllegalArgumentException("Valid scale values range from 10 to 400");
}
final CTSheetView dsv = getDefaultSheetView(true);
assert(dsv != null);
dsv.setZoomScale(scale);
}
/**
* copyRows rows from srcRows to this sheet starting at destStartRow
*
* Additionally copies merged regions that are completely defined in these
* rows (ie. merged 2 cells on a row to be shifted).
* @param srcRows the rows to copy. Formulas will be offset by the difference
* in the row number of the first row in srcRows and destStartRow (even if srcRows
* are from a different sheet).
* @param destStartRow the row in this sheet to paste the first row of srcRows
* the remainder of srcRows will be pasted below destStartRow per the cell copy policy
* @param policy is the cell copy policy, which can be used to merge the source and destination
* when the source is blank, copy styles only, paste as value, etc
*/
@Beta
public void copyRows(List<? extends Row> srcRows, int destStartRow, CellCopyPolicy policy) {
if (srcRows == null || srcRows.isEmpty()) {
throw new IllegalArgumentException("No rows to copy");
}
final Row srcStartRow = srcRows.get(0);
final Row srcEndRow = srcRows.get(srcRows.size() - 1);
if (srcStartRow == null) {
throw new IllegalArgumentException("copyRows: First row cannot be null");
}
final int srcStartRowNum = srcStartRow.getRowNum();
final int srcEndRowNum = srcEndRow.getRowNum();
// check row numbers to make sure they are continuous and increasing (monotonic)
// and srcRows does not contain null rows
final int size = srcRows.size();
for (int index=1; index < size; index++) {
final Row curRow = srcRows.get(index);
if (curRow == null) {
throw new IllegalArgumentException("srcRows may not contain null rows. Found null row at index " + index + ".");
//} else if (curRow.getRowNum() != prevRow.getRowNum() + 1) {
// throw new IllegalArgumentException("srcRows must contain continuously increasing row numbers. " +
// "Got srcRows[" + (index-1) + "]=Row " + prevRow.getRowNum() + ", srcRows[" + index + "]=Row " + curRow.getRowNum() + ".");
// FIXME: assumes row objects belong to non-null sheets and sheets belong to non-null workbooks.
} else if (srcStartRow.getSheet().getWorkbook() != curRow.getSheet().getWorkbook()) {
throw new IllegalArgumentException("All rows in srcRows must belong to the same sheet in the same workbook. " +
"Expected all rows from same workbook (" + srcStartRow.getSheet().getWorkbook() + "). " +
"Got srcRows[" + index + "] from different workbook (" + curRow.getSheet().getWorkbook() + ").");
} else if (srcStartRow.getSheet() != curRow.getSheet()) {
throw new IllegalArgumentException("All rows in srcRows must belong to the same sheet. " +
"Expected all rows from " + srcStartRow.getSheet().getSheetName() + ". " +
"Got srcRows[" + index + "] from " + curRow.getSheet().getSheetName());
}
}
// FIXME: is special behavior needed if srcRows and destRows belong to the same sheets and the regions overlap?
final CellCopyPolicy options = new CellCopyPolicy(policy);
// avoid O(N^2) performance scanning through all regions for each row
// merged regions will be copied after all the rows have been copied
options.setCopyMergedRegions(false);
// FIXME: if srcRows contains gaps or null values, clear out those rows that will be overwritten
// how will this work with merging (copy just values, leave cell styles in place?)
int r = destStartRow;
for (Row srcRow : srcRows) {
int destRowNum;
if (policy.isCondenseRows()) {
destRowNum = r++;
} else {
final int shift = (srcRow.getRowNum() - srcStartRowNum);
destRowNum = destStartRow + shift;
}
//removeRow(destRowNum); //this probably clears all external formula references to destRow, causing unwanted #REF! errors
final XSSFRow destRow = createRow(destRowNum);
destRow.copyRowFrom(srcRow, options);
}
// ======================
// Only do additional copy operations here that cannot be done with Row.copyFromRow(Row, options)
// reasons: operation needs to interact with multiple rows or sheets
// Copy merged regions that are contained within the copy region
if (policy.isCopyMergedRegions()) {
// FIXME: is this something that rowShifter could be doing?
final int shift = destStartRow - srcStartRowNum;
for (CellRangeAddress srcRegion : srcStartRow.getSheet().getMergedRegions()) {
if (srcStartRowNum <= srcRegion.getFirstRow() && srcRegion.getLastRow() <= srcEndRowNum) {
// srcRegion is fully inside the copied rows
final CellRangeAddress destRegion = srcRegion.copy();
destRegion.setFirstRow(destRegion.getFirstRow() + shift);
destRegion.setLastRow(destRegion.getLastRow() + shift);
addMergedRegion(destRegion);
}
}
}
}
/**
* Copies rows between srcStartRow and srcEndRow to the same sheet, starting at destStartRow
* Convenience function for {@link #copyRows(List, int, CellCopyPolicy)}
*
* Equivalent to copyRows(getRows(srcStartRow, srcEndRow, false), destStartRow, cellCopyPolicy)
*
* @param srcStartRow the index of the first row to copy the cells from in this sheet
* @param srcEndRow the index of the last row to copy the cells from in this sheet
* @param destStartRow the index of the first row to copy the cells to in this sheet
* @param cellCopyPolicy the policy to use to determine how cells are copied
*/
@Beta
public void copyRows(int srcStartRow, int srcEndRow, int destStartRow, CellCopyPolicy cellCopyPolicy) {
final List<XSSFRow> srcRows = getRows(srcStartRow, srcEndRow, false); //FIXME: should be false, no need to create rows where src is only to copy them to dest
copyRows(srcRows, destStartRow, cellCopyPolicy);
}
/**
* Shifts rows between startRow and endRow n number of rows.
* If you use a negative number, it will shift rows up.
* Code ensures that rows don't wrap around.
*
* Calls shiftRows(startRow, endRow, n, false, false);
*
* <p>
* Additionally, shifts merged regions that are completely defined in these
* rows (i.e. merged 2 cells on a row to be shifted).
* @param startRow the row to start shifting
* @param endRow the row to end shifting
* @param n the number of rows to shift
*/
@Override
public void shiftRows(int startRow, int endRow, int n) {
shiftRows(startRow, endRow, n, false, false);
}
/**
* Shifts rows between startRow and endRow n number of rows.
* If you use a negative number, it will shift rows up.
* Code ensures that rows don't wrap around
*
* <p>
* Additionally, shifts merged regions that are completely defined in these
* rows (i.e. merged 2 cells on a row to be shifted). All merged regions that are
* completely overlaid by shifting will be deleted.
*
* @param startRow the row to start shifting
* @param endRow the row to end shifting
* @param n the number of rows to shift
* @param copyRowHeight whether to copy the row height during the shift
* @param resetOriginalRowHeight whether to set the original row's height to the default
*/
@Override
public void shiftRows(int startRow, int endRow, final int n, boolean copyRowHeight, boolean resetOriginalRowHeight) {
List<XSSFTable> overlappingTables = new ArrayList<>();
for (XSSFTable table : getTables()) {
if ((table.getStartRowIndex() < startRow && table.getEndRowIndex() < startRow)
|| (table.getStartRowIndex() > endRow && table.getEndRowIndex() > endRow)) {
// not overlapping
} else {
overlappingTables.add(table);
}
}
int sheetIndex = getWorkbook().getSheetIndex(this);
String sheetName = getWorkbook().getSheetName(sheetIndex);
FormulaShifter formulaShifter = FormulaShifter.createForRowShift(
sheetIndex, sheetName, startRow, endRow, n, SpreadsheetVersion.EXCEL2007);
removeOverwritten(startRow, endRow, n);
shiftCommentsAndRows(startRow, endRow, n);
XSSFRowShifter rowShifter = new XSSFRowShifter(this);
rowShifter.shiftMergedRegions(startRow, endRow, n);
rowShifter.updateNamedRanges(formulaShifter);
rowShifter.updateFormulas(formulaShifter);
rowShifter.updateConditionalFormatting(formulaShifter);
rowShifter.updateHyperlinks(formulaShifter);
rebuildRows();
for (XSSFTable table : overlappingTables) {
rebuildTableFormulas(table);
}
}
/**
* Shifts columns between startColumn and endColumn n number of columns.
* If you use a negative number, it will shift columns left.
* Code ensures that columns don't wrap around
*
* @param startColumn the column to start shifting
* @param endColumn the column to end shifting
* @param n length of the shifting step
*/
@Override
public void shiftColumns(int startColumn, int endColumn, final int n) {
List<XSSFTable> overlappingTables = new ArrayList<>();
for (XSSFTable table : getTables()) {
if ((table.getStartColIndex() < startColumn && table.getEndColIndex() < startColumn)
|| (table.getStartColIndex() > endColumn && table.getEndColIndex() > endColumn)) {
// not overlapping
} else {
overlappingTables.add(table);
}
}
XSSFVMLDrawing vml = getVMLDrawing(false);
shiftCommentsForColumns(vml, startColumn, endColumn, n);
FormulaShifter formulaShifter = FormulaShifter.createForColumnShift(this.getWorkbook().getSheetIndex(this), this.getSheetName(), startColumn, endColumn, n, SpreadsheetVersion.EXCEL2007);
XSSFColumnShifter columnShifter = new XSSFColumnShifter(this);
columnShifter.shiftColumns(startColumn, endColumn, n);
columnShifter.shiftMergedRegions(startColumn, endColumn, n);
columnShifter.updateFormulas(formulaShifter);
columnShifter.updateConditionalFormatting(formulaShifter);
columnShifter.updateHyperlinks(formulaShifter);
columnShifter.updateNamedRanges(formulaShifter);
rebuildRows();
for (XSSFTable table : overlappingTables) {
rebuildTableFormulas(table);
}
}
private void rebuildTableFormulas(XSSFTable table) {
//correct all sheet table-reference-formulas which probably got damaged after shift rows/columns
for (CTTableColumn tableCol : table.getCTTable().getTableColumns().getTableColumnList()) {
if (tableCol.getCalculatedColumnFormula() != null) {
int id = Math.toIntExact(tableCol.getId());
String formula = tableCol.getCalculatedColumnFormula().getStringValue();
int rFirst = table.getStartCellReference().getRow() + table.getHeaderRowCount();
int rLast = table.getEndCellReference().getRow() - table.getTotalsRowCount();
int c = table.getStartCellReference().getCol() + id - 1;
final boolean cellFormulaValidationFlag = getWorkbook().getCellFormulaValidation();
try {
getWorkbook().setCellFormulaValidation(false);
for (int r = rFirst; r <= rLast; r++) {
XSSFRow row = getRow(r);
if (row == null) row = createRow(r);
XSSFCell cell = row.getCell(c, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell.setCellFormula(formula);
}
} finally {
getWorkbook().setCellFormulaValidation(cellFormulaValidationFlag);
}
}
}
}
private void rebuildRows() {
//rebuild the CTSheetData CTRow order
SortedMap<Long, CTRow> ctRows = new TreeMap<>();
CTSheetData sheetData = getCTWorksheet().getSheetData();
for (CTRow ctRow : sheetData.getRowList()) {
Long rownumL = ctRow.getR();
ctRows.put(rownumL, ctRow);
}
List<CTRow> ctRowList = new ArrayList<>(ctRows.values());
CTRow[] ctRowArray = new CTRow[ctRowList.size()];
ctRowArray = ctRowList.toArray(ctRowArray);
sheetData.setRowArray(ctRowArray);
//rebuild the _rows map
_rows.clear();
for (CTRow ctRow : sheetData.getRowList()) {
XSSFRow row = new XSSFRow(ctRow, this);
Integer rownumI = Math.toIntExact(row.getRowNum());
_rows.put(rownumI, row);
}
}
// remove all rows which will be overwritten
private void removeOverwritten(int startRow, int endRow, final int n) {
XSSFVMLDrawing vml = getVMLDrawing(false);
HashSet<Integer> rowsToRemoveSet = new HashSet<>();
for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) {
XSSFRow row = (XSSFRow)it.next();
int rownum = row.getRowNum();
// check if we should remove this row as it will be overwritten by the data later
if (shouldRemoveRow(startRow, endRow, n, rownum)) {
rowsToRemoveSet.add(rownum);
for (Cell c : row) {
if (!c.isPartOfArrayFormulaGroup()) {
//the support for deleting cells that are part of array formulas is not implemented yet
c.setBlank();
}
}
// remove row from worksheet.getSheetData row array
// Performance optimization: explicit boxing is slightly faster than auto-unboxing, though may use more memory
//noinspection UnnecessaryBoxing
final Integer rownumI = Integer.valueOf(row.getRowNum()); // NOSONAR
int idx = _rows.headMap(rownumI).size();
worksheet.getSheetData().removeRow(idx);
// remove row from _rows
it.remove();
}
}
// also remove any comments associated with this row
if (sheetComments != null) {
ArrayList<CellAddress> refsToRemove = new ArrayList<>();
Iterator<CellAddress> commentAddressIterator = sheetComments.getCellAddresses();
while (commentAddressIterator.hasNext()) {
CellAddress ref = commentAddressIterator.next();
// is this comment part of the current row?
if(rowsToRemoveSet.contains(ref.getRow())) {
refsToRemove.add(ref);
}
}
for (CellAddress ref : refsToRemove) {
sheetComments.removeComment(ref);
if (vml != null) {
vml.removeCommentShape(ref.getRow(), ref.getColumn());
}
}
}
// also remove any hyperlinks associated with this row
if (hyperlinks != null) {
for (XSSFHyperlink link : new ArrayList<>(hyperlinks)) {
CellRangeAddress range = CellRangeAddress.valueOf(link.getCellRef());
if (range.getFirstRow() == range.getLastRow() && rowsToRemoveSet.contains(range.getFirstRow())) {
removeHyperlink(link);
} else if (range.getFirstRow() != range.getLastRow()) {
boolean toRemove = true;
for (int i = range.getFirstRow(); i <= range.getLastRow() && toRemove; i++) {
toRemove = rowsToRemoveSet.contains(i);
}
if (toRemove) {
removeHyperlink(link);
}
}
}
}
}
private void shiftCommentsAndRows(int startRow, int endRow, final int n) {
// then do the actual moving and also adjust comments/rowHeight
// we need to sort it in a way so the shifting does not mess up the structures,
// i.e. when shifting down, start from down and go up, when shifting up, vice-versa
SortedMap<XSSFComment, Integer> commentsToShift = new TreeMap<>((o1, o2) -> {
int row1 = o1.getRow();
int row2 = o2.getRow();
if (row1 == row2) {
// ordering is not important when row is equal, but don't return zero to still
// get multiple comments per row into the map
return o1.hashCode() - o2.hashCode();
}
// when shifting down, sort higher row-values first
if (n > 0) {
return row1 < row2 ? 1 : -1;
} else {
// sort lower-row values first when shifting up
return row1 > row2 ? 1 : -1;
}
});
for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) {
XSSFRow row = (XSSFRow)it.next();
int rownum = row.getRowNum();
if(sheetComments != null) {
// calculate the new rownum
int newrownum = shiftedRowNum(startRow, endRow, n, rownum);
// is there a change necessary for the current row?
if(newrownum != rownum) {
Iterator<CellAddress> commentAddressIterator = sheetComments.getCellAddresses();
while (commentAddressIterator.hasNext()) {
CellAddress cellAddress = commentAddressIterator.next();
// is this comment part of the current row?
if(cellAddress.getRow() == rownum) {
XSSFComment oldComment = sheetComments.findCellComment(cellAddress);
if (oldComment != null) {
XSSFComment xssfComment = new XSSFComment(sheetComments, oldComment.getCTComment(),
oldComment.getCTShape());
// we should not perform the shifting right here as we would then find
// already shifted comments and would shift them again...
commentsToShift.put(xssfComment, newrownum);
}
}
}
}
}
if(rownum < startRow || rownum > endRow) {
continue;
}
row.shift(n);
}
// adjust all the affected comment-structures now
// the Map is sorted and thus provides them in the order that we need here,
// i.e. from down to up if shifting down, vice-versa otherwise
for(Map.Entry<XSSFComment, Integer> entry : commentsToShift.entrySet()) {
entry.getKey().setRow(entry.getValue());
}
rebuildRows();
}
private int shiftedRowNum(int startRow, int endRow, int n, int rownum) {
// no change if before any affected row
if(rownum < startRow && (n > 0 || (startRow - rownum) > n)) {
return rownum;
}
// no change if after any affected row
if(rownum > endRow && (n < 0 || (rownum - endRow) > n)) {
return rownum;
}
// row before and things are moved up
if(rownum < startRow) {
// row is moved down by the shifting
return rownum + (endRow - startRow);
}
// row is after and things are moved down
if(rownum > endRow) {
// row is moved up by the shifting
return rownum - (endRow - startRow);
}
// row is part of the shifted block
return rownum + n;
}
private void shiftCommentsForColumns(XSSFVMLDrawing vml, int startColumnIndex, int endColumnIndex, final int n){
// then do the actual moving and also adjust comments/rowHeight
// we need to sort it in a way so the shifting does not mess up the structures,
// i.e. when shifting down, start from down and go up, when shifting up, vice-versa
SortedMap<XSSFComment, Integer> commentsToShift = new TreeMap<>((o1, o2) -> {
int column1 = o1.getColumn();
int column2 = o2.getColumn();
if (column1 == column2) {
// ordering is not important when row is equal, but don't return zero to still
// get multiple comments per row into the map
return o1.hashCode() - o2.hashCode();
}
// when shifting down, sort higher row-values first
if (n > 0) {
return column1 < column2 ? 1 : -1;
} else {
// sort lower-row values first when shifting up
return column1 > column2 ? 1 : -1;
}
});
if (sheetComments != null) {
Iterator<CellAddress> commentAddressIterator = sheetComments.getCellAddresses();
while (commentAddressIterator.hasNext()) {
CellAddress oldCommentAddress = commentAddressIterator.next();
int columnIndex = oldCommentAddress.getColumn();
int newColumnIndex = shiftedRowNum(startColumnIndex, endColumnIndex, n, columnIndex);
if(newColumnIndex != columnIndex) {
XSSFComment oldComment = sheetComments.findCellComment(oldCommentAddress);
if (oldComment != null) {
XSSFComment xssfComment = new XSSFComment(sheetComments, oldComment.getCTComment(),
oldComment.getCTShape());
commentsToShift.put(xssfComment, newColumnIndex);
}
}
}
}
// adjust all the affected comment-structures now
// the Map is sorted and thus provides them in the order that we need here,
// i.e. from down to up if shifting down, vice-versa otherwise
for(Map.Entry<XSSFComment, Integer> entry : commentsToShift.entrySet()) {
entry.getKey().setColumn(entry.getValue());
}
rebuildRows();
}
/**
* Location of the top left visible cell Location of the top left visible cell in the bottom right
* pane (when in Left-to-Right mode).
*
* @param topRow the top row to show in desktop window pane
* @param leftCol the left column to show in desktop window pane
*/
@Override
public void showInPane(int topRow, int leftCol) {
final CellReference cellReference = new CellReference(topRow, leftCol);
final String cellRef = cellReference.formatAsString();
final CTPane pane = getPane(true);
assert(pane != null);
pane.setTopLeftCell(cellRef);
}
@Override
public void ungroupColumn(int fromColumn, int toColumn) {
CTCols cols = worksheet.getColsArray(0);
for (int index = fromColumn; index <= toColumn; index++) {
CTCol col = columnHelper.getColumn(index, false);
if (col != null) {
short outlineLevel = col.getOutlineLevel();
col.setOutlineLevel((short) (outlineLevel - 1));
index = Math.toIntExact(col.getMax());
if (col.getOutlineLevel() <= 0) {
int colIndex = columnHelper.getIndexOfColumn(cols, col);
worksheet.getColsArray(0).removeCol(colIndex);
}
}
}
worksheet.setColsArray(0, cols);
setSheetFormatPrOutlineLevelCol();
}
/**
* Ungroup a range of rows that were previously groupped
*
* @param fromRow start row (0-based)
* @param toRow end row (0-based)
*/
@Override
public void ungroupRow(int fromRow, int toRow) {
for (int i = fromRow; i <= toRow; i++) {
XSSFRow xrow = getRow(i);
if (xrow != null) {
CTRow ctRow = xrow.getCTRow();
int outlineLevel = ctRow.getOutlineLevel();
ctRow.setOutlineLevel((short) (outlineLevel - 1));
//remove a row only if the row has no cell and if the outline level is 0
if (outlineLevel == 1 && xrow.getFirstCellNum() == -1) {
removeRow(xrow);
}
}
}
setSheetFormatPrOutlineLevelRow();
}
private void setSheetFormatPrOutlineLevelRow(){
short maxLevelRow=getMaxOutlineLevelRows();
getSheetTypeSheetFormatPr().setOutlineLevelRow(maxLevelRow);
}
private void setSheetFormatPrOutlineLevelCol(){
short maxLevelCol=getMaxOutlineLevelCols();
getSheetTypeSheetFormatPr().setOutlineLevelCol(maxLevelCol);
}
protected CTSheetViews getSheetTypeSheetViews(final boolean create) {
final CTSheetViews views = (worksheet.isSetSheetViews() || !create)
? worksheet.getSheetViews() : worksheet.addNewSheetViews();
assert(views != null || !create);
if (views == null) {
return null;
}
if (views.sizeOfSheetViewArray() == 0 && create) {
views.addNewSheetView();
}
return views;
}
/**
* Returns a flag indicating whether this sheet is selected.
* <p>
* When only 1 sheet is selected and active, this value should be in synch with the activeTab value.
* In case of a conflict, the Start Part setting wins and sets the active sheet tab.
* </p>
* Note: multiple sheets can be selected, but only one sheet can be active at one time.
*
* @return {@code true} if this sheet is selected
*/
@Override
public boolean isSelected() {
final CTSheetView dsv = getDefaultSheetView(false);
return dsv != null && dsv.getTabSelected();
}
/**
* Sets a flag indicating whether this sheet is selected.
*
* <p>
* When only 1 sheet is selected and active, this value should be in synch with the activeTab value.
* In case of a conflict, the Start Part setting wins and sets the active sheet tab.
* </p>
* Note: multiple sheets can be selected, but only one sheet can be active at one time.
*
* @param value {@code true} if this sheet is selected
*/
@Override
public void setSelected(boolean value) {
final CTSheetViews views = getSheetTypeSheetViews(true);
assert(views != null);
for (CTSheetView view : views.getSheetViewArray()) {
view.setTabSelected(value);
}
}
/**
* Register a hyperlink in the collection of hyperlinks on this sheet.
* Use {@link XSSFCell#setHyperlink(Hyperlink)} if the hyperlink is just for that one cell.
* Use this method if you want to add a Hyperlink that covers a range of sells. If you use
* this method, you will need to call {@link XSSFHyperlink#setCellReference(String)} to
* explicitly cell the value, eg B2 or B2:C3 (the 4 cells with B2 at top left and C3 at bottom right)
*
* @param hyperlink the link to add
*/
public void addHyperlink(XSSFHyperlink hyperlink) {
hyperlinks.add(hyperlink);
}
/**
* Remove a hyperlink in the collection of hyperlinks on this sheet.
* {@link XSSFCell#removeHyperlink()} can be used if the hyperlink is just for that one cell.
*
* @param hyperlink the link to remove
* @since POI 5.1.0
*/
public void removeHyperlink(XSSFHyperlink hyperlink) {
hyperlinks.remove(hyperlink);
}
/**
* Removes a hyperlink in the collection of hyperlinks on this sheet
*
* @param row row index
* @param column column index
*/
@Internal
public void removeHyperlink(int row, int column) {
// CTHyperlinks is regenerated from scratch when writing out the spreadsheet
// so don't worry about maintaining hyperlinks and CTHyperlinks in parallel.
// only maintain hyperlinks
XSSFHyperlink hyperlink = getHyperlink(row, column);
if (hyperlink != null) {
if (hyperlink.getFirstRow() == row && hyperlink.getLastRow() == row
&& hyperlink.getFirstColumn() == column && hyperlink.getLastColumn() == column) {
removeHyperlink(hyperlink);
} else {
//we have a cellRef that spans multiple cells - we just want to remove the hyperlink from one cell
//we delete this hyperlink but add new hyperlinks to cover the other cells that were served
//by the old hyperlink
boolean leftCreated = false;
boolean rightCreated = false;
if (hyperlink.getFirstColumn() < column) {
XSSFHyperlink newLink = new XSSFHyperlink(hyperlink);
newLink.setFirstColumn(hyperlink.getFirstColumn());
newLink.setLastColumn(column - 1);
newLink.setFirstRow(hyperlink.getFirstRow());
newLink.setLastRow(hyperlink.getLastRow());
addHyperlink(newLink);
leftCreated = true;
}
if (hyperlink.getLastColumn() > column) {
XSSFHyperlink newLink = new XSSFHyperlink(hyperlink);
newLink.setFirstColumn(column + 1);
newLink.setLastColumn(hyperlink.getLastColumn());
newLink.setFirstRow(hyperlink.getFirstRow());
newLink.setLastRow(hyperlink.getLastRow());
addHyperlink(newLink);
rightCreated = true;
}
if (hyperlink.getFirstRow() < row) {
XSSFHyperlink newLink = new XSSFHyperlink(hyperlink);
int firstColumn = leftCreated ? row : hyperlink.getFirstColumn();
int lastColumn = rightCreated ? row : hyperlink.getLastColumn();
newLink.setFirstColumn(firstColumn);
newLink.setLastColumn(lastColumn);
newLink.setFirstRow(hyperlink.getFirstRow());
newLink.setLastRow(row - 1);
addHyperlink(newLink);
}
if (hyperlink.getLastRow() > row) {
XSSFHyperlink newLink = new XSSFHyperlink(hyperlink);
int firstColumn = leftCreated ? row : hyperlink.getFirstColumn();
int lastColumn = rightCreated ? row : hyperlink.getLastColumn();
newLink.setFirstColumn(firstColumn);
newLink.setLastColumn(lastColumn);
newLink.setFirstRow(row + 1);
newLink.setLastRow(hyperlink.getLastRow());
addHyperlink(newLink);
}
removeHyperlink(hyperlink);
}
}
}
/**
* Return location of the active cell, e.g. {@code A1}.
*
* @return the location of the active cell.
*/
@Override
public CellAddress getActiveCell() {
final CTSelection sts = getSheetTypeSelection(false);
final String address = (sts != null) ? sts.getActiveCell() : null;
return (address != null) ? new CellAddress(address) : null;
}
/**
* {@inheritDoc}
*/
@Override
public void setActiveCell(CellAddress address) {
final CTSelection ctsel = getSheetTypeSelection(true);
assert(ctsel != null);
String ref = address.formatAsString();
ctsel.setActiveCell(ref);
ctsel.setSqref(Collections.singletonList(ref));
}
/**
* Does this sheet have any comments on it? We need to know,
* so we can decide about writing it to disk or not
*/
public boolean hasComments() {
return sheetComments != null && sheetComments.getNumberOfComments() > 0;
}
protected int getNumberOfComments() {
return sheetComments == null ? 0 : sheetComments.getNumberOfComments();
}
private CTSelection getSheetTypeSelection(final boolean create) {
final CTSheetView dsv = getDefaultSheetView(create);
assert(dsv != null || !create);
if (dsv == null) {
return null;
}
final int sz = dsv.sizeOfSelectionArray();
if (sz == 0) {
return create ? dsv.addNewSelection() : null;
}
return dsv.getSelectionArray(sz - 1);
}
/**
* Return the default sheet view. This is the last one if the sheet's views, according to sec. 3.3.1.83
* of the OOXML spec: "A single sheet view definition. When more than 1 sheet view is defined in the file,
* it means that when opening the workbook, each sheet view corresponds to a separate window within the
* spreadsheet application, where each window is showing the particular sheet. containing the same
* workbookViewId value, the last sheetView definition is loaded, and the others are discarded.
* When multiple windows are viewing the same sheet, multiple sheetView elements (with corresponding
* workbookView entries) are saved."
*/
private CTSheetView getDefaultSheetView(final boolean create) {
final CTSheetViews views = getSheetTypeSheetViews(create);
assert(views != null || !create);
if (views == null) {
return null;
}
final int sz = views.sizeOfSheetViewArray();
assert(sz > 0 || !create);
return (sz == 0) ? null : views.getSheetViewArray(sz - 1);
}
/**
* Returns the sheet's comments object if there is one,
* or null if not
*
* @param create create a new comments table if it does not exist
*/
protected Comments getCommentsTable(boolean create) {
if(sheetComments == null && create){
// Try to create a comments table with the same number as
// the sheet has (i.e. sheet 1 -> comments 1)
try {
sheetComments = (Comments)createRelationship(
XSSFRelation.SHEET_COMMENTS, getWorkbook().getXssfFactory(), Math.toIntExact(sheet.getSheetId()));
} catch(PartAlreadyExistsException e) {
// Technically a sheet doesn't need the same number as
// its comments, and clearly someone has already pinched
// our number! Go for the next available one instead
sheetComments = (Comments)createRelationship(
XSSFRelation.SHEET_COMMENTS, getWorkbook().getXssfFactory(), -1);
}
if (sheetComments != null) {
sheetComments.setSheet(this);
}
}
return sheetComments;
}
private CTPageSetUpPr getSheetTypePageSetUpPr() {
CTSheetPr sheetPr = getSheetTypeSheetPr();
return sheetPr.isSetPageSetUpPr() ? sheetPr.getPageSetUpPr() : sheetPr.addNewPageSetUpPr();
}
private static boolean shouldRemoveRow(int startRow, int endRow, int n, int rownum) {
// is this row in the target-window where the moved rows will land?
if (rownum >= (startRow + n) && rownum <= (endRow + n)) {
// only remove it if the current row is not part of the data that is copied
if (n > 0 && rownum > endRow) {
return true;
}
else {
return n < 0 && rownum < startRow;
}
}
return false;
}
private CTPane getPane(final boolean create) {
final CTSheetView dsv = getDefaultSheetView(create);
assert(dsv != null || !create);
if (dsv == null) {
return null;
}
return (dsv.isSetPane() || !create) ? dsv.getPane() : dsv.addNewPane();
}
/**
* Return a master shared formula by index
*
* @param sid shared group index
* @return a CTCellFormula bean holding shared formula or {@code null} if not found
*/
@Internal
public CTCellFormula getSharedFormula(int sid){
return sharedFormulas.get(sid);
}
void onReadCell(XSSFCell cell){
//collect cells holding shared formulas
CTCell ct = cell.getCTCell();
CTCellFormula f = ct.getF();
if (f != null && f.getT() == STCellFormulaType.SHARED && f.isSetRef() && f.getStringValue() != null) {
// save a detached copy to avoid XmlValueDisconnectedException,
// this may happen when the master cell of a shared formula is changed
CTCellFormula sf = (CTCellFormula)f.copy();
CellRangeAddress sfRef = CellRangeAddress.valueOf(sf.getRef());
CellReference cellRef = new CellReference(cell);
// If the shared formula range precedes the master cell then the preceding part is discarded, e.g.
// if the cell is E60 and the shared formula range is C60:M85 then the effective range is E60:M85
// see more details in https://issues.apache.org/bugzilla/show_bug.cgi?id=51710
if(cellRef.getCol() > sfRef.getFirstColumn() || cellRef.getRow() > sfRef.getFirstRow()){
String effectiveRef = new CellRangeAddress(
Math.max(cellRef.getRow(), sfRef.getFirstRow()), Math.max(cellRef.getRow(), sfRef.getLastRow()),
Math.max(cellRef.getCol(), sfRef.getFirstColumn()), Math.max(cellRef.getCol(), sfRef.getLastColumn()))
.formatAsString();
sf.setRef(effectiveRef);
}
sharedFormulas.put(Math.toIntExact(f.getSi()), sf);
}
if (f != null && f.getT() == STCellFormulaType.ARRAY && f.getRef() != null) {
arrayFormulas.add(CellRangeAddress.valueOf(f.getRef()));
}
}
@Override
protected void commit() throws IOException {
PackagePart part = getPackagePart();
try (OutputStream out = part.getOutputStream()) {
write(out);
}
}
protected void write(OutputStream out) throws IOException {
boolean setToNull = false;
if(worksheet.sizeOfColsArray() == 1) {
CTCols col = worksheet.getColsArray(0);
if(col.sizeOfColArray() == 0) {
setToNull = true;
// this is necessary so that we do not write an empty <cols/> item into the sheet-xml in the xlsx-file
// Excel complains about a corrupted file if this shows up there!
worksheet.setColsArray(null);
} else {
setColWidthAttribute(col);
}
}
// Now re-generate our CTHyperlinks, if needed
if (!hyperlinks.isEmpty()) {
if(worksheet.getHyperlinks() == null) {
worksheet.addNewHyperlinks();
}
CTHyperlink[] ctHls = new CTHyperlink[hyperlinks.size()];
for(int i=0; i<ctHls.length; i++) {
// If our sheet has hyperlinks, have them add
// any relationships that they might need
XSSFHyperlink hyperlink = hyperlinks.get(i);
hyperlink.generateRelationIfNeeded(getPackagePart());
// Now grab their underling object
ctHls[i] = hyperlink.getCTHyperlink();
}
worksheet.getHyperlinks().setHyperlinkArray(ctHls);
}
else {
if (worksheet.getHyperlinks() != null) {
final int count = worksheet.getHyperlinks().sizeOfHyperlinkArray();
for (int i=count-1; i>=0; i--) {
worksheet.getHyperlinks().removeHyperlink(i);
}
// For some reason, we have to remove the hyperlinks one by one from the CTHyperlinks array
// before unsetting the hyperlink array.
// Resetting the hyperlink array seems to break some XML nodes.
//worksheet.getHyperlinks().setHyperlinkArray(new CTHyperlink[0]);
worksheet.unsetHyperlinks();
} /*else {
// nothing to do
}*/
}
CellRangeAddress cellRangeAddress = dimensionOverride;
if (cellRangeAddress == null) {
int minCell = Integer.MAX_VALUE, maxCell = Integer.MIN_VALUE;
for(Map.Entry<Integer, XSSFRow> entry : _rows.entrySet()) {
XSSFRow row = entry.getValue();
// first perform the normal write actions for the row
row.onDocumentWrite();
// then calculate min/max cell-numbers for the worksheet-dimension
if(row.getFirstCellNum() != -1) {
minCell = Math.min(minCell, row.getFirstCellNum());
}
if(row.getLastCellNum() != -1) {
maxCell = Math.max(maxCell, row.getLastCellNum()-1);
}
}
// finally, if we had at least one cell we can populate the optional dimension-field
if(minCell != Integer.MAX_VALUE) {
cellRangeAddress = new CellRangeAddress(getFirstRowNum(), getLastRowNum(), minCell, maxCell);
}
}
if (cellRangeAddress != null) {
if (worksheet.isSetDimension()) {
worksheet.getDimension().setRef(cellRangeAddress.formatAsString());
} else {
worksheet.addNewDimension().setRef(cellRangeAddress.formatAsString());
}
}
XmlOptions xmlOptions = new XmlOptions(DEFAULT_XML_OPTIONS);
xmlOptions.setSaveSyntheticDocumentElement(new QName(CTWorksheet.type.getName().getNamespaceURI(), "worksheet"));
worksheet.save(out, xmlOptions);
// Bug 52233: Ensure that we have a col-array even if write() removed it
if(setToNull) {
worksheet.addNewCols();
}
}
/**
* @return true when Autofilters are locked and the sheet is protected.
*/
public boolean isAutoFilterLocked() {
return isSheetLocked() && safeGetProtectionField().getAutoFilter();
}
/**
* @return true when Deleting columns is locked and the sheet is protected.
*/
public boolean isDeleteColumnsLocked() {
return isSheetLocked() && safeGetProtectionField().getDeleteColumns();
}
/**
* @return true when Deleting rows is locked and the sheet is protected.
*/
public boolean isDeleteRowsLocked() {
return isSheetLocked() && safeGetProtectionField().getDeleteRows();
}
/**
* @return true when Formatting cells is locked and the sheet is protected.
*/
public boolean isFormatCellsLocked() {
return isSheetLocked() && safeGetProtectionField().getFormatCells();
}
/**
* @return true when Formatting columns is locked and the sheet is protected.
*/
public boolean isFormatColumnsLocked() {
return isSheetLocked() && safeGetProtectionField().getFormatColumns();
}
/**
* @return true when Formatting rows is locked and the sheet is protected.
*/
public boolean isFormatRowsLocked() {
return isSheetLocked() && safeGetProtectionField().getFormatRows();
}
/**
* @return true when Inserting columns is locked and the sheet is protected.
*/
public boolean isInsertColumnsLocked() {
return isSheetLocked() && safeGetProtectionField().getInsertColumns();
}
/**
* @return true when Inserting hyperlinks is locked and the sheet is protected.
*/
public boolean isInsertHyperlinksLocked() {
return isSheetLocked() && safeGetProtectionField().getInsertHyperlinks();
}
/**
* @return true when Inserting rows is locked and the sheet is protected.
*/
public boolean isInsertRowsLocked() {
return isSheetLocked() && safeGetProtectionField().getInsertRows();
}
/**
* @return true when Pivot tables are locked and the sheet is protected.
*/
public boolean isPivotTablesLocked() {
return isSheetLocked() && safeGetProtectionField().getPivotTables();
}
/**
* @return true when Sorting is locked and the sheet is protected.
*/
public boolean isSortLocked() {
return isSheetLocked() && safeGetProtectionField().getSort();
}
/**
* @return true when Objects are locked and the sheet is protected.
*/
public boolean isObjectsLocked() {
return isSheetLocked() && safeGetProtectionField().getObjects();
}
/**
* @return true when Scenarios are locked and the sheet is protected.
*/
public boolean isScenariosLocked() {
return isSheetLocked() && safeGetProtectionField().getScenarios();
}
/**
* @return true when Selection of locked cells is locked and the sheet is protected.
*/
public boolean isSelectLockedCellsLocked() {
return isSheetLocked() && safeGetProtectionField().getSelectLockedCells();
}
/**
* @return true when Selection of unlocked cells is locked and the sheet is protected.
*/
public boolean isSelectUnlockedCellsLocked() {
return isSheetLocked() && safeGetProtectionField().getSelectUnlockedCells();
}
/**
* @return true when Sheet is Protected.
*/
public boolean isSheetLocked() {
return worksheet.isSetSheetProtection() && safeGetProtectionField().getSheet();
}
/**
* Enable sheet protection
*/
public void enableLocking() {
safeGetProtectionField().setSheet(true);
}
/**
* Disable sheet protection
*/
public void disableLocking() {
safeGetProtectionField().setSheet(false);
}
/**
* Enable or disable Autofilters locking.
* This does not modify sheet protection status.
* To enforce this un-/locking, call {@link #disableLocking()} or {@link #enableLocking()}
*/
public void lockAutoFilter(boolean enabled) {
safeGetProtectionField().setAutoFilter(enabled);
}
/**
* Enable or disable Deleting columns locking.
* This does not modify sheet protection status.
* To enforce this un-/locking, call {@link #disableLocking()} or {@link #enableLocking()}
*/
public void lockDeleteColumns(boolean enabled) {
safeGetProtectionField().setDeleteColumns(enabled);
}
/**
* Enable or disable Deleting rows locking.
* This does not modify sheet protection status.
* To enforce this un-/locking, call {@link #disableLocking()} or {@link #enableLocking()}
*/
public void lockDeleteRows(boolean enabled) {
safeGetProtectionField().setDeleteRows(enabled);
}
/**
* Enable or disable Formatting cells locking.
* This does not modify sheet protection status.
* To enforce this un-/locking, call {@link #disableLocking()} or {@link #enableLocking()}
*/
public void lockFormatCells(boolean enabled) {
safeGetProtectionField().setFormatCells(enabled);
}
/**
* Enable or disable Formatting columns locking.
* This does not modify sheet protection status.
* To enforce this un-/locking, call {@link #disableLocking()} or {@link #enableLocking()}
*/
public void lockFormatColumns(boolean enabled) {
safeGetProtectionField().setFormatColumns(enabled);
}
/**
* Enable or disable Formatting rows locking.
* This does not modify sheet protection status.
* To enforce this un-/locking, call {@link #disableLocking()} or {@link #enableLocking()}
*/
public void lockFormatRows(boolean enabled) {
safeGetProtectionField().setFormatRows(enabled);
}
/**
* Enable or disable Inserting columns locking.
* This does not modify sheet protection status.
* To enforce this un-/locking, call {@link #disableLocking()} or {@link #enableLocking()}
*/
public void lockInsertColumns(boolean enabled) {
safeGetProtectionField().setInsertColumns(enabled);
}
/**
* Enable or disable Inserting hyperlinks locking.
* This does not modify sheet protection status.
* To enforce this un-/locking, call {@link #disableLocking()} or {@link #enableLocking()}
*/
public void lockInsertHyperlinks(boolean enabled) {
safeGetProtectionField().setInsertHyperlinks(enabled);
}
/**
* Enable or disable Inserting rows locking.
* This does not modify sheet protection status.
* To enforce this un-/locking, call {@link #disableLocking()} or {@link #enableLocking()}
*/
public void lockInsertRows(boolean enabled) {
safeGetProtectionField().setInsertRows(enabled);
}
/**
* Enable or disable Pivot Tables locking.
* This does not modify sheet protection status.
* To enforce this un-/locking, call {@link #disableLocking()} or {@link #enableLocking()}
*/
public void lockPivotTables(boolean enabled) {
safeGetProtectionField().setPivotTables(enabled);
}
/**
* Enable or disable Sort locking.
* This does not modify sheet protection status.
* To enforce this un-/locking, call {@link #disableLocking()} or {@link #enableLocking()}
*/
public void lockSort(boolean enabled) {
safeGetProtectionField().setSort(enabled);
}
/**
* Enable or disable Objects locking.
* This does not modify sheet protection status.
* To enforce this un-/locking, call {@link #disableLocking()} or {@link #enableLocking()}
*/
public void lockObjects(boolean enabled) {
safeGetProtectionField().setObjects(enabled);
}
/**
* Enable or disable Scenarios locking.
* This does not modify sheet protection status.
* To enforce this un-/locking, call {@link #disableLocking()} or {@link #enableLocking()}
*/
public void lockScenarios(boolean enabled) {
safeGetProtectionField().setScenarios(enabled);
}
/**
* Enable or disable Selection of locked cells locking.
* This does not modify sheet protection status.
* To enforce this un-/locking, call {@link #disableLocking()} or {@link #enableLocking()}
*/
public void lockSelectLockedCells(boolean enabled) {
safeGetProtectionField().setSelectLockedCells(enabled);
}
/**
* Enable or disable Selection of unlocked cells locking.
* This does not modify sheet protection status.
* To enforce this un-/locking, call {@link #disableLocking()} or {@link #enableLocking()}
*/
public void lockSelectUnlockedCells(boolean enabled) {
safeGetProtectionField().setSelectUnlockedCells(enabled);
}
/**
* Reads the dimensions of the sheet data
* @return dimensions of the sheet data as a Cell Range (can be null)
* @since POI 5.2.3
*/
public CellRangeAddress getDimension() {
if (dimensionOverride != null) {
return dimensionOverride;
}
CTSheetDimension ctSheetDimension = worksheet.getDimension();
String ref = ctSheetDimension == null ? null : ctSheetDimension.getRef();
if (ref != null) {
return CellRangeAddress.valueOf(ref);
}
return null;
}
private CTSheetProtection safeGetProtectionField() {
if (!isSheetProtectionEnabled()) {
return worksheet.addNewSheetProtection();
}
return worksheet.getSheetProtection();
}
/* package */ boolean isSheetProtectionEnabled() {
return (worksheet.isSetSheetProtection());
}
/* package */ boolean isCellInArrayFormulaContext(XSSFCell cell) {
for (CellRangeAddress range : arrayFormulas) {
if (range.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
return true;
}
}
return false;
}
/* package */ XSSFCell getFirstCellInArrayFormula(XSSFCell cell) {
for (CellRangeAddress range : arrayFormulas) {
if (range.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
return getRow(range.getFirstRow()).getCell(range.getFirstColumn());
}
}
return null;
}
/**
* Also creates cells if they don't exist
*/
private CellRange<XSSFCell> getCellRange(CellRangeAddress range) {
int firstRow = range.getFirstRow();
int firstColumn = range.getFirstColumn();
int lastRow = range.getLastRow();
int lastColumn = range.getLastColumn();
int height = lastRow - firstRow + 1;
int width = lastColumn - firstColumn + 1;
List<XSSFCell> temp = new ArrayList<>(height * width);
for (int rowIn = firstRow; rowIn <= lastRow; rowIn++) {
for (int colIn = firstColumn; colIn <= lastColumn; colIn++) {
XSSFRow row = getRow(rowIn);
if (row == null) {
row = createRow(rowIn);
}
XSSFCell cell = row.getCell(colIn);
if (cell == null) {
cell = row.createCell(colIn);
}
temp.add(cell);
}
}
return SSCellRange.create(firstRow, firstColumn, height, width, temp, XSSFCell.class);
}
@Override
public CellRange<XSSFCell> setArrayFormula(String formula, CellRangeAddress range) {
CellRange<XSSFCell> cr = getCellRange(range);
XSSFCell mainArrayFormulaCell = cr.getTopLeftCell();
mainArrayFormulaCell.setCellArrayFormula(formula, range);
arrayFormulas.add(range);
return cr;
}
@Override
public CellRange<XSSFCell> removeArrayFormula(Cell cell) {
if (cell.getSheet() != this) {
throw new IllegalArgumentException("Specified cell does not belong to this sheet.");
}
for (CellRangeAddress range : arrayFormulas) {
if (range.isInRange(cell)) {
arrayFormulas.remove(range);
CellRange<XSSFCell> cr = getCellRange(range);
for (XSSFCell c : cr) {
c.setBlank();
}
return cr;
}
}
String ref = new CellReference(cell).formatAsString();
throw new IllegalArgumentException("Cell " + ref + " is not part of an array formula.");
}
@Override
public DataValidationHelper getDataValidationHelper() {
return dataValidationHelper;
}
@Override
public List<XSSFDataValidation> getDataValidations() {
List<XSSFDataValidation> xssfValidations = new ArrayList<>();
CTDataValidations dataValidations = this.worksheet.getDataValidations();
if (dataValidations != null) {
for (CTDataValidation ctDataValidation : dataValidations.getDataValidationArray()) {
CellRangeAddressList addressList = new CellRangeAddressList();
@SuppressWarnings("unchecked")
List<String> sqref = ctDataValidation.getSqref();
for (String stRef : sqref) {
String[] regions = stRef.split(" ");
for (String region : regions) {
String[] parts = region.split(":");
CellReference begin = new CellReference(parts[0]);
CellReference end = parts.length > 1 ? new CellReference(parts[1]) : begin;
CellRangeAddress cellRangeAddress = new CellRangeAddress(begin.getRow(), end.getRow(), begin.getCol(), end.getCol());
addressList.addCellRangeAddress(cellRangeAddress);
}
}
XSSFDataValidation xssfDataValidation = new XSSFDataValidation(addressList, ctDataValidation);
xssfValidations.add(xssfDataValidation);
}
}
return xssfValidations;
}
@Override
public void addValidationData(DataValidation dataValidation) {
XSSFDataValidation xssfDataValidation = (XSSFDataValidation)dataValidation;
CTDataValidations dataValidations = worksheet.getDataValidations();
if( dataValidations==null ) {
dataValidations = worksheet.addNewDataValidations();
}
int currentCount = dataValidations.sizeOfDataValidationArray();
CTDataValidation newval = dataValidations.addNewDataValidation();
newval.set(xssfDataValidation.getCtDataValidation());
dataValidations.setCount(currentCount + 1L);
}
@Override
public XSSFAutoFilter setAutoFilter(CellRangeAddress range) {
CTAutoFilter af = worksheet.getAutoFilter();
if(af == null) {
af = worksheet.addNewAutoFilter();
}
CellRangeAddress norm = new CellRangeAddress(range.getFirstRow(), range.getLastRow(),
range.getFirstColumn(), range.getLastColumn());
String ref = norm.formatAsString();
af.setRef(ref);
XSSFWorkbook wb = getWorkbook();
int sheetIndex = getWorkbook().getSheetIndex(this);
XSSFName name = wb.getBuiltInName(XSSFName.BUILTIN_FILTER_DB, sheetIndex);
if (name == null) {
name = wb.createBuiltInName(XSSFName.BUILTIN_FILTER_DB, sheetIndex);
}
name.getCTName().setHidden(true);
CellReference r1 = new CellReference(getSheetName(), range.getFirstRow(), range.getFirstColumn(), true, true);
CellReference r2 = new CellReference(null, range.getLastRow(), range.getLastColumn(), true, true);
String fmla = r1.formatAsString() + ":" + r2.formatAsString();
name.setRefersToFormula(fmla);
return new XSSFAutoFilter(this);
}
/**
* Creates a new Table, and associates it with this Sheet.
* <p>
* The table is assigned a default display name (since 4.1.1) which can be overridden
* by calling {@code setDisplayName}. The default display name is guaranteed to not conflict
* with the names of any {@code XSSFName} or {@code XSSFTable} in the workbook.
*
* @param tableArea
* the area that the table should cover, should not be null
* @return the created table
* @since 4.0.0
*/
public XSSFTable createTable(AreaReference tableArea) {
if (!worksheet.isSetTableParts()) {
worksheet.addNewTableParts();
}
CTTableParts tblParts = worksheet.getTableParts();
CTTablePart tbl = tblParts.addNewTablePart();
// Table numbers need to be unique in the file, not just
// unique within the sheet. Find the next one
int tableNumber = getPackagePart().getPackage().getPartsByContentType(XSSFRelation.TABLE.getContentType()).size() + 1;
// the id could already be taken after insertion/deletion of different tables
boolean loop = true;
while(loop) {
loop = false;
for (PackagePart packagePart : getPackagePart().getPackage().getPartsByContentType(XSSFRelation.TABLE.getContentType())) {
String fileName = XSSFRelation.TABLE.getFileName(tableNumber);
if(fileName.equals(packagePart.getPartName().getName())) {
// duplicate found, increase the number and start iterating again
tableNumber++;
loop = true;
}
}
}
RelationPart rp = createRelationship(XSSFRelation.TABLE, getWorkbook().getXssfFactory(), tableNumber, false);
XSSFTable table = rp.getDocumentPart();
tbl.setId(rp.getRelationship().getId());
table.getCTTable().setId(tableNumber);
tables.put(tbl.getId(), table);
if(tableArea != null && table.supportsAreaReference(tableArea)) {
table.setArea(tableArea);
}
// Set the default name of the table. This must not conflict with any defined names.
while(tableNumber<Integer.MAX_VALUE) {
final String displayName="Table"+tableNumber;
if(getWorkbook().getTable(displayName) == null &&
getWorkbook().getName(displayName) == null) {
table.setDisplayName(displayName);
table.setName(displayName);
break;
}
++tableNumber;
}
return table;
}
/**
* Returns any tables associated with this Sheet
*/
public List<XSSFTable> getTables() {
return new ArrayList<>(tables.values());
}
/**
* Remove table references and relations
* @param t table to remove
*/
public void removeTable(XSSFTable t) {
String rId = getRelationId(t);
long id = t.getCTTable().getId();
Map.Entry<String, XSSFTable> toDelete = null;
for (Map.Entry<String, XSSFTable> entry : tables.entrySet()) {
if (entry.getValue().getCTTable().getId() == id) toDelete = entry;
}
if (toDelete != null) {
removeRelation(getRelationById(toDelete.getKey()), true);
tables.remove(toDelete.getKey());
toDelete.getValue().onTableDelete();
CTTableParts tblParts = worksheet.getTableParts();
int matchedPos = -1;
if (rId != null) {
for (int i = 0; i < tblParts.sizeOfTablePartArray(); i++) {
if (rId.equals(tblParts.getTablePartArray(i).getId())) {
matchedPos = i;
break;
}
}
}
if (matchedPos != -1) {
tblParts.removeTablePart(matchedPos);
}
}
}
@Override
public XSSFSheetConditionalFormatting getSheetConditionalFormatting(){
return new XSSFSheetConditionalFormatting(this);
}
/**
* Get background color of the sheet tab.
* Returns {@code null} if no sheet tab color is set.
*
* @return the background color of the sheet tab
*/
public XSSFColor getTabColor() {
CTSheetPr pr = worksheet.getSheetPr();
if(pr == null) {
pr = worksheet.addNewSheetPr();
}
if (!pr.isSetTabColor()) {
return null;
}
return XSSFColor.from(pr.getTabColor(), getWorkbook().getStylesSource().getIndexedColors());
}
/**
* Set background color of the sheet tab
*
* @param color the color to set
*/
public void setTabColor(XSSFColor color) {
CTSheetPr pr = worksheet.getSheetPr();
if(pr == null) {
pr = worksheet.addNewSheetPr();
}
pr.setTabColor(color.getCTColor());
}
@Override
public CellRangeAddress getRepeatingRows() {
return getRepeatingRowsOrColumns(true);
}
@Override
public CellRangeAddress getRepeatingColumns() {
return getRepeatingRowsOrColumns(false);
}
@Override
public void setRepeatingRows(CellRangeAddress rowRangeRef) {
CellRangeAddress columnRangeRef = getRepeatingColumns();
setRepeatingRowsAndColumns(rowRangeRef, columnRangeRef);
}
@Override
public void setRepeatingColumns(CellRangeAddress columnRangeRef) {
CellRangeAddress rowRangeRef = getRepeatingRows();
setRepeatingRowsAndColumns(rowRangeRef, columnRangeRef);
}
private void setRepeatingRowsAndColumns(
CellRangeAddress rowDef, CellRangeAddress colDef) {
int col1 = -1;
int col2 = -1;
int row1 = -1;
int row2 = -1;
if (rowDef != null) {
row1 = rowDef.getFirstRow();
row2 = rowDef.getLastRow();
if ((row1 == -1 && row2 != -1)
|| row1 < -1 || row2 < -1 || row1 > row2) {
throw new IllegalArgumentException("Invalid row range specification");
}
}
if (colDef != null) {
col1 = colDef.getFirstColumn();
col2 = colDef.getLastColumn();
if ((col1 == -1 && col2 != -1)
|| col1 < -1 || col2 < -1 || col1 > col2) {
throw new IllegalArgumentException(
"Invalid column range specification");
}
}
int sheetIndex = getWorkbook().getSheetIndex(this);
boolean removeAll = rowDef == null && colDef == null;
XSSFName name = getWorkbook().getBuiltInName(
XSSFName.BUILTIN_PRINT_TITLE, sheetIndex);
if (removeAll) {
if (name != null) {
getWorkbook().removeName(name);
}
return;
}
if (name == null) {
name = getWorkbook().createBuiltInName(
XSSFName.BUILTIN_PRINT_TITLE, sheetIndex);
}
String reference = getReferenceBuiltInRecord(
name.getSheetName(), col1, col2, row1, row2);
name.setRefersToFormula(reference);
// If the print setup isn't currently defined, then add it
// in but without printer defaults
// If it's already there, leave it as-is!
if (worksheet.isSetPageSetup() && worksheet.isSetPageMargins()) {
// Everything we need is already there
} else {
// Have initial ones put in place
getPrintSetup().setValidSettings(false);
}
}
private static String getReferenceBuiltInRecord(
String sheetName, int startC, int endC, int startR, int endR) {
// Excel example for built-in title:
// 'second sheet'!$E:$F,'second sheet'!$2:$3
CellReference colRef =
new CellReference(sheetName, 0, startC, true, true);
CellReference colRef2 =
new CellReference(sheetName, 0, endC, true, true);
CellReference rowRef =
new CellReference(sheetName, startR, 0, true, true);
CellReference rowRef2 =
new CellReference(sheetName, endR, 0, true, true);
String escapedName = SheetNameFormatter.format(sheetName);
String c = "";
String r = "";
if (startC != -1 || endC != -1) {
String col1 = colRef.getCellRefParts()[2];
String col2 = colRef2.getCellRefParts()[2];
c = escapedName + "!$" + col1 + ":$" + col2;
}
if (startR != -1 || endR != -1) {
String row1 = rowRef.getCellRefParts()[1];
String row2 = rowRef2.getCellRefParts()[1];
if (!row1.equals("0") && !row2.equals("0")) {
r = escapedName + "!$" + row1 + ":$" + row2;
}
}
StringBuilder rng = new StringBuilder();
rng.append(c);
if(rng.length() > 0 && r.length() > 0) {
rng.append(',');
}
rng.append(r);
return rng.toString();
}
private CellRangeAddress getRepeatingRowsOrColumns(boolean rows) {
int sheetIndex = getWorkbook().getSheetIndex(this);
XSSFName name = getWorkbook().getBuiltInName(
XSSFName.BUILTIN_PRINT_TITLE, sheetIndex);
if (name == null ) {
return null;
}
String refStr = name.getRefersToFormula();
if (refStr == null) {
return null;
}
String[] parts = refStr.split(",");
int maxRowIndex = SpreadsheetVersion.EXCEL2007.getLastRowIndex();
int maxColIndex = SpreadsheetVersion.EXCEL2007.getLastColumnIndex();
for (String part : parts) {
CellRangeAddress range = CellRangeAddress.valueOf(part);
if ((range.getFirstColumn() == 0
&& range.getLastColumn() == maxColIndex)
|| (range.getFirstColumn() == -1
&& range.getLastColumn() == -1)) {
if (rows) {
return range;
}
} else if (range.getFirstRow() == 0
&& range.getLastRow() == maxRowIndex
|| (range.getFirstRow() == -1
&& range.getLastRow() == -1)) {
if (!rows) {
return range;
}
}
}
return null;
}
/**
* Creates an empty XSSFPivotTable and sets up all its relationships
* including: pivotCacheDefinition, pivotCacheRecords
* @return returns a pivotTable
*/
@Beta
private XSSFPivotTable createPivotTable() {
XSSFWorkbook wb = getWorkbook();
List<XSSFPivotTable> pivotTables = wb.getPivotTables();
int tableId = getWorkbook().getPivotTables().size()+1;
//Create relationship between pivotTable and the worksheet
XSSFPivotTable pivotTable = (XSSFPivotTable) createRelationship(XSSFRelation.PIVOT_TABLE,
getWorkbook().getXssfFactory(), tableId);
pivotTable.setParentSheet(this);
pivotTables.add(pivotTable);
XSSFWorkbook workbook = getWorkbook();
//Create relationship between the pivot cache defintion and the workbook
XSSFPivotCacheDefinition pivotCacheDefinition = (XSSFPivotCacheDefinition) workbook.
createRelationship(XSSFRelation.PIVOT_CACHE_DEFINITION, getWorkbook().getXssfFactory(), tableId);
String rId = workbook.getRelationId(pivotCacheDefinition);
//Create relationship between pivotTable and pivotCacheDefinition without creating a new instance
PackagePart pivotPackagePart = pivotTable.getPackagePart();
pivotPackagePart.addRelationship(pivotCacheDefinition.getPackagePart().getPartName(),
TargetMode.INTERNAL, XSSFRelation.PIVOT_CACHE_DEFINITION.getRelation());
pivotTable.setPivotCacheDefinition(pivotCacheDefinition);
//Create pivotCache and sets up it's relationship with the workbook
pivotTable.setPivotCache(new XSSFPivotCache(workbook.addPivotCache(rId)));
//Create relationship between pivotcacherecord and pivotcachedefinition
XSSFPivotCacheRecords pivotCacheRecords = (XSSFPivotCacheRecords) pivotCacheDefinition.
createRelationship(XSSFRelation.PIVOT_CACHE_RECORDS, getWorkbook().getXssfFactory(), tableId);
//Set relationships id for pivotCacheDefinition to pivotCacheRecords
pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().setId(pivotCacheDefinition.getRelationId(pivotCacheRecords));
wb.setPivotTables(pivotTables);
return pivotTable;
}
/**
* Create a pivot table using the AreaReference range on sourceSheet, at the given position.
* If the source reference contains a sheet name, it must match the sourceSheet
* @param source location of pivot data
* @param position A reference to the top left cell where the pivot table will start
* @param sourceSheet The sheet containing the source data, if the source reference doesn't contain a sheet name
* @throws IllegalArgumentException if source references a sheet different than sourceSheet
* @return The pivot table
*/
@Beta
public XSSFPivotTable createPivotTable(final AreaReference source, CellReference position, Sheet sourceSheet) {
final String sourceSheetName = source.getFirstCell().getSheetName();
if(sourceSheetName != null && !sourceSheetName.equalsIgnoreCase(sourceSheet.getSheetName())) {
throw new IllegalArgumentException("The area is referenced in another sheet than the "
+ "defined source sheet " + sourceSheet.getSheetName() + ".");
}
return createPivotTable(position, sourceSheet, wsSource -> {
final String[] firstCell = source.getFirstCell().getCellRefParts();
final String firstRow = firstCell[1];
final String firstCol = firstCell[2];
final String[] lastCell = source.getLastCell().getCellRefParts();
final String lastRow = lastCell[1];
final String lastCol = lastCell[2];
final String ref = firstCol+firstRow+':'+lastCol+lastRow; //or just source.formatAsString()
wsSource.setRef(ref);
});
}
/**
* Create a pivot table using the AreaReference or named/table range on sourceSheet, at the given position.
* If the source reference contains a sheet name, it must match the sourceSheet.
* @param position A reference to the top left cell where the pivot table will start
* @param sourceSheet The sheet containing the source data, if the source reference doesn't contain a sheet name
* @param refConfig A reference to the pivot table configurator
* @throws IllegalArgumentException if source references a sheet different than sourceSheet
* @return The pivot table
*/
private XSSFPivotTable createPivotTable(CellReference position, Sheet sourceSheet, PivotTableReferenceConfigurator refConfig) {
XSSFPivotTable pivotTable = createPivotTable();
//Creates default settings for the pivot table
pivotTable.setDefaultPivotTableDefinition();
//Set sources and references
pivotTable.createSourceReferences(position, sourceSheet, refConfig);
//Create cachefield/s and empty SharedItems - must be after creating references
pivotTable.getPivotCacheDefinition().createCacheFields(sourceSheet);
pivotTable.createDefaultDataColumns();
return pivotTable;
}
/**
* Create a pivot table using the AreaReference range, at the given position.
* If the source reference contains a sheet name, that sheet is used, otherwise this sheet is assumed as the source sheet.
* @param source location of pivot data
* @param position A reference to the top left cell where the pivot table will start
* @return The pivot table
*/
@Beta
public XSSFPivotTable createPivotTable(AreaReference source, CellReference position){
final String sourceSheetName = source.getFirstCell().getSheetName();
if(sourceSheetName != null && !sourceSheetName.equalsIgnoreCase(this.getSheetName())) {
final XSSFSheet sourceSheet = getWorkbook().getSheet(sourceSheetName);
return createPivotTable(source, position, sourceSheet);
}
return createPivotTable(source, position, this);
}
/**
* Create a pivot table using the Name range reference on sourceSheet, at the given position.
* If the source reference contains a sheet name, it must match the sourceSheet
* @param source location of pivot data
* @param position A reference to the top left cell where the pivot table will start
* @param sourceSheet The sheet containing the source data, if the source reference doesn't contain a sheet name
* @throws IllegalArgumentException if source references a sheet different than sourceSheet
* @return The pivot table
*/
@Beta
public XSSFPivotTable createPivotTable(final Name source, CellReference position, Sheet sourceSheet) {
if(source.getSheetName() != null && !source.getSheetName().equals(sourceSheet.getSheetName())) {
throw new IllegalArgumentException("The named range references another sheet than the "
+ "defined source sheet " + sourceSheet.getSheetName() + ".");
}
return createPivotTable(position, sourceSheet, wsSource -> wsSource.setName(source.getNameName()));
}
/**
* Create a pivot table using the Name range, at the given position.
* If the source reference contains a sheet name, that sheet is used, otherwise this sheet is assumed as the source sheet.
* @param source location of pivot data
* @param position A reference to the top left cell where the pivot table will start
* @return The pivot table
*/
@Beta
public XSSFPivotTable createPivotTable(Name source, CellReference position) {
return createPivotTable(source, position, getWorkbook().getSheet(source.getSheetName()));
}
/**
* Create a pivot table using the Table, at the given position.
* Tables are required to have a sheet reference, so no additional logic around reference sheet is needed.
* @param source location of pivot data
* @param position A reference to the top left cell where the pivot table will start
* @return The pivot table
*/
@Beta
public XSSFPivotTable createPivotTable(final Table source, CellReference position) {
return createPivotTable(position, getWorkbook().getSheet(source.getSheetName()), wsSource -> wsSource.setName(source.getName()));
}
/**
* Returns all the pivot tables for this Sheet
*/
@Beta
public List<XSSFPivotTable> getPivotTables() {
List<XSSFPivotTable> tables = new ArrayList<>();
for (XSSFPivotTable table : getWorkbook().getPivotTables()) {
if (table.getParent() == this) {
tables.add(table);
}
}
return tables;
}
@Override
public int getColumnOutlineLevel(int columnIndex) {
CTCol col = columnHelper.getColumn(columnIndex, false);
if (col == null) {
return 0;
}
return col.getOutlineLevel();
}
/**
* Add ignored errors (usually to suppress them in the UI of a consuming
* application).
*
* @param cell Cell.
* @param ignoredErrorTypes Types of error to ignore there.
*/
public void addIgnoredErrors(CellReference cell, IgnoredErrorType... ignoredErrorTypes) {
addIgnoredErrors(cell.formatAsString(false), ignoredErrorTypes);
}
/**
* Ignore errors across a range of cells.
*
* @param region Range of cells.
* @param ignoredErrorTypes Types of error to ignore there.
*/
public void addIgnoredErrors(CellRangeAddress region, IgnoredErrorType... ignoredErrorTypes) {
region.validate(SpreadsheetVersion.EXCEL2007);
addIgnoredErrors(region.formatAsString(), ignoredErrorTypes);
}
/**
* Returns the errors currently being ignored and the ranges
* where they are ignored.
*
* @return Map of error type to the range(s) where they are ignored.
*/
public Map<IgnoredErrorType, Set<CellRangeAddress>> getIgnoredErrors() {
Map<IgnoredErrorType, Set<CellRangeAddress>> result = new LinkedHashMap<>();
if (worksheet.isSetIgnoredErrors()) {
for (CTIgnoredError err : worksheet.getIgnoredErrors().getIgnoredErrorList()) {
for (IgnoredErrorType errType : XSSFIgnoredErrorHelper.getErrorTypes(err)) {
if (!result.containsKey(errType)) {
result.put(errType, new LinkedHashSet<>());
}
for (Object ref : err.getSqref()) {
result.get(errType).add(CellRangeAddress.valueOf(ref.toString()));
}
}
}
}
return result;
}
private void addIgnoredErrors(String ref, IgnoredErrorType... ignoredErrorTypes) {
CTIgnoredErrors ctIgnoredErrors = worksheet.isSetIgnoredErrors() ? worksheet.getIgnoredErrors() : worksheet.addNewIgnoredErrors();
CTIgnoredError ctIgnoredError = ctIgnoredErrors.addNewIgnoredError();
XSSFIgnoredErrorHelper.addIgnoredErrors(ctIgnoredError, ref, ignoredErrorTypes);
}
/**
* called when a sheet is being deleted/removed from a workbook, to clean up relations and other document pieces tied to the sheet
*/
protected void onSheetDelete() {
for (RelationPart part : getRelationParts()) {
if (part.getDocumentPart() instanceof XSSFTable) {
// call table delete
removeTable(part.getDocumentPart());
continue;
}
removeRelation(part.getDocumentPart(), true);
}
}
/**
* when a cell with a 'master' shared formula is removed, the next cell in the range becomes the master
* @param cell The cell that is removed
* @param evalWb BaseXSSFEvaluationWorkbook in use, if one exists
*/
protected void onDeleteFormula(XSSFCell cell, BaseXSSFEvaluationWorkbook evalWb){
CTCellFormula f = cell.getCTCell().getF();
if (f != null && f.getT() == STCellFormulaType.SHARED && f.isSetRef() && f.getStringValue() != null) {
CellRangeAddress ref = CellRangeAddress.valueOf(f.getRef());
if(ref.getNumberOfCells() > 1){
DONE:
for(int i = cell.getRowIndex(); i <= ref.getLastRow(); i++){
XSSFRow row = getRow(i);
if(row != null) {
for(int j = cell.getColumnIndex(); j <= ref.getLastColumn(); j++){
XSSFCell nextCell = row.getCell(j);
if(nextCell != null && nextCell != cell && nextCell.getCellType() == CellType.FORMULA) {
CTCellFormula nextF = nextCell.getCTCell().getF();
if (nextF.getT() == STCellFormulaType.SHARED && nextF.getSi() == f.getSi()) {
nextF.setStringValue(nextCell.getCellFormula(evalWb));
CellRangeAddress nextRef = new CellRangeAddress(
nextCell.getRowIndex(), ref.getLastRow(),
nextCell.getColumnIndex(), ref.getLastColumn());
nextF.setRef(nextRef.formatAsString());
sharedFormulas.put(Math.toIntExact(nextF.getSi()), nextF);
break DONE;
}
}
}
}
}
}
}
}
/**
* Determine the OleObject which links shapes with embedded resources
*
* @param shapeId the shape id
* @return the CTOleObject of the shape
*/
protected CTOleObject readOleObject(long shapeId) {
if (!getCTWorksheet().isSetOleObjects()) {
return null;
}
// we use a XmlCursor here to handle oleObject with-/out AlternateContent wrappers
String xquery = "declare namespace p='"+XSSFRelation.NS_SPREADSHEETML+"' .//p:oleObject";
try (XmlCursor cur = getCTWorksheet().getOleObjects().newCursor()) {
cur.selectPath(xquery);
CTOleObject coo = null;
while (cur.toNextSelection()) {
String sId = cur.getAttributeText(new QName(null, "shapeId"));
if (sId == null || Long.parseLong(sId) != shapeId) {
continue;
}
XmlObject xObj = cur.getObject();
if (xObj instanceof CTOleObject) {
// the unusual case ...
coo = (CTOleObject)xObj;
} else {
XMLStreamReader reader = cur.newXMLStreamReader();
try {
CTOleObjects coos = CTOleObjects.Factory.parse(reader);
if (coos.sizeOfOleObjectArray() == 0) {
continue;
}
coo = coos.getOleObjectArray(0);
} catch (XmlException e) {
LOG.atInfo().withThrowable(e).log("can't parse CTOleObjects");
} finally {
try {
reader.close();
} catch (XMLStreamException e) {
LOG.atInfo().withThrowable(e).log("can't close reader");
}
}
}
// there are choice and fallback OleObject ... we prefer the one having the objectPr element,
// which is in the choice element
if (cur.toChild(XSSFRelation.NS_SPREADSHEETML, "objectPr")) {
break;
}
}
return coo;
}
}
public XSSFHeaderFooterProperties getHeaderFooterProperties() {
return new XSSFHeaderFooterProperties(getSheetTypeHeaderFooter());
}
/**
* Currently, this is for internal use. Overrides the default dimensions of the sheet.
* @param dimension {@link CellRangeAddress}, <code>null</code> removes the existing override
* @since POI 5.2.3
*/
@Beta
public void setDimensionOverride(CellRangeAddress dimension) {
this.dimensionOverride = dimension;
}
static void cloneTables(XSSFSheet sheet) {
for (XSSFTable table : sheet.getTables()) {
// clone table; XSSFTable.setArea fails and throws exception for too small tables
XSSFTable clonedTable = null;
if (table.supportsAreaReference(table.getArea())) {
clonedTable = sheet.createTable(table.getArea());
}
if (clonedTable != null) {
clonedTable.updateHeaders();
// clone style
clonedTable.setStyleName(table.getStyleName());
XSSFTableStyleInfo style = (XSSFTableStyleInfo)table.getStyle();
XSSFTableStyleInfo clonedStyle = (XSSFTableStyleInfo)clonedTable.getStyle();
if (style != null && clonedStyle != null) {
clonedStyle.setShowColumnStripes(style.isShowColumnStripes());
clonedStyle.setShowRowStripes(style.isShowRowStripes());
clonedStyle.setFirstColumn(style.isShowFirstColumn());
clonedStyle.setLastColumn(style.isShowLastColumn());
}
//clone autofilter
clonedTable.getCTTable().setAutoFilter(table.getCTTable().getAutoFilter());
//clone totalsrow
int totalsRowCount = table.getTotalsRowCount();
if (totalsRowCount == 1) { // never seen more than one totals row
XSSFRow totalsRow = sheet.getRow(clonedTable.getEndCellReference().getRow());
if (clonedTable.getCTTable().getTableColumns() != null
&& !clonedTable.getCTTable().getTableColumns().getTableColumnList().isEmpty()) {
clonedTable.getCTTable().setTotalsRowCount(totalsRowCount);
for (int i = 0; i < clonedTable.getCTTable().getTableColumns().getTableColumnList().size(); i++) {
CTTableColumn tableCol = table.getCTTable().getTableColumns().getTableColumnList().get(i);
CTTableColumn clonedTableCol = clonedTable.getCTTable().getTableColumns().getTableColumnList().get(i);
clonedTableCol.setTotalsRowFunction(tableCol.getTotalsRowFunction());
int intTotalsRowFunction = clonedTableCol.getTotalsRowFunction().intValue();
sheet.getWorkbook().setCellFormulaValidation(false);
if (intTotalsRowFunction == 10) { //custom
CTTableFormula totalsRowFormula = tableCol.getTotalsRowFormula();
clonedTableCol.setTotalsRowFormula(totalsRowFormula);
totalsRow.getCell(clonedTable.getStartCellReference().getCol()+i).setCellFormula(totalsRowFormula.getStringValue());
} else if (intTotalsRowFunction == 1) { //none
//totalsRow.getCell(clonedTable.getStartCellReference().getCol()+i).setBlank();
} else {
String subtotalFormulaStart = getSubtotalFormulaStartFromTotalsRowFunction(intTotalsRowFunction);
if (subtotalFormulaStart != null)
totalsRow.getCell(clonedTable.getStartCellReference().getCol()+i).setCellFormula(subtotalFormulaStart + "," + clonedTable.getName() +"[" + clonedTableCol.getName()+ "])");
}
}
}
}
// clone calculated column formulas
if (clonedTable.getCTTable().getTableColumns() != null
&& !clonedTable.getCTTable().getTableColumns().getTableColumnList().isEmpty()) {
clonedTable.getCTTable().setTotalsRowCount(totalsRowCount);
for (int i = 0; i < clonedTable.getCTTable().getTableColumns().getTableColumnList().size(); i++) {
CTTableColumn tableCol = table.getCTTable().getTableColumns().getTableColumnList().get(i);
CTTableColumn clonedTableCol = clonedTable.getCTTable().getTableColumns().getTableColumnList().get(i);
if (tableCol.getCalculatedColumnFormula() != null) {
clonedTableCol.setCalculatedColumnFormula(tableCol.getCalculatedColumnFormula());
CTTableFormula calculatedColumnFormula = clonedTableCol.getCalculatedColumnFormula();
String formula = tableCol.getCalculatedColumnFormula().getStringValue();
String clonedFormula = formula.replace(table.getName(), clonedTable.getName());
calculatedColumnFormula.setStringValue(clonedFormula);
int rFirst = clonedTable.getStartCellReference().getRow() + clonedTable.getHeaderRowCount();
int rLast = clonedTable.getEndCellReference().getRow() - clonedTable.getTotalsRowCount();
int c = clonedTable.getStartCellReference().getCol() + i;
sheet.getWorkbook().setCellFormulaValidation(false);
for (int r = rFirst; r <= rLast; r++) {
XSSFRow row = sheet.getRow(r);
if (row == null) row = sheet.createRow(r);
XSSFCell cell = row.getCell(c, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell.setCellFormula(clonedFormula);
}
}
}
}
}
// remove old table
sheet.removeTable(table);
}
}
private static String getSubtotalFormulaStartFromTotalsRowFunction(int intTotalsRowFunction) {
final int INT_NONE = 1;
final int INT_SUM = 2;
final int INT_MIN = 3;
final int INT_MAX = 4;
final int INT_AVERAGE = 5;
final int INT_COUNT = 6;
final int INT_COUNT_NUMS = 7;
final int INT_STD_DEV = 8;
final int INT_VAR = 9;
final int INT_CUSTOM = 10;
String subtotalFormulaStart = null;
switch (intTotalsRowFunction) {
case INT_NONE:
subtotalFormulaStart = null;
break;
case INT_SUM:
subtotalFormulaStart = "SUBTOTAL(109";
break;
case INT_MIN:
subtotalFormulaStart = "SUBTOTAL(105";
break;
case INT_MAX:
subtotalFormulaStart = "SUBTOTAL(104";
break;
case INT_AVERAGE:
subtotalFormulaStart = "SUBTOTAL(101";
break;
case INT_COUNT:
subtotalFormulaStart = "SUBTOTAL(103";
break;
case INT_COUNT_NUMS:
subtotalFormulaStart = "SUBTOTAL(102";
break;
case INT_STD_DEV:
subtotalFormulaStart = "SUBTOTAL(107";
break;
case INT_VAR:
subtotalFormulaStart = "SUBTOTAL(110";
break;
case INT_CUSTOM:
subtotalFormulaStart = null;
break;
default:
subtotalFormulaStart = null;
}
return subtotalFormulaStart;
}
}
⏎ org/apache/poi/xssf/usermodel/XSSFSheet.java
Or download all of them as a single archive file:
File name: poi-ooxml-5.2.3-src.zip File size: 1396572 bytes Release date: 2022-09-09 Download
⇒ What Is poi-excelant-5.2.3.jar?
2017-04-01, 23689👍, 0💬
Related Topics: