Package io.keikai.axyra.sheets

Class Sheet

java.lang.Object

io.keikai.axyra.sheets.Sheet

public final class Sheet
extends Object

A worksheet: a lightweight view bound to one tab of a Workbook. The view follows its tab when other tabs are inserted, moved or deleted. It holds no native resource; all work goes through the owning workbook's handle, so a Sheet needs no closing.

Coordinates are 0-based and given as (row, col): cell A1 is (0, 0). Rectangles are inclusive at both ends.

Use Range for bulk work. A Range crosses into the engine once per rectangle: Range.setValues(io.keikai.axyra.sheets.CellValue[][]), Range.setNumbers(double[]), Range.values() and Range.setStyle(io.keikai.axyra.sheets.style.CellStyle) read or write a whole block in one call. cell(int, int), value(int, int) and setNumber(int, int, double) cross once per cell; do not loop over them for large blocks.

Once the tab is deleted from its workbook, every method throws IllegalStateException.

 try (Workbook wb = Workbook.create()) {
     Sheet s = wb.createSheet("Sales");
     // Bulk writes: one engine call per block.
     s.range("A1:B1").setValues(new CellValue[][] {
         {CellValue.text("Item"), CellValue.text("Qty")}});
     s.range("B2:B4").setNumbers(new double[] {3, 5, 8});
     // A formula and a style.
     s.cell(4, 1).setFormula("SUM(B2:B4)");
     s.range("A1:B1").setStyle(CellStyle.builder().bold().build());
     s.setColumnWidth(0, 20);
     CellValue total = s.value(4, 1);   // 16
     wb.save(Path.of("sales.xlsx"));
 }
 

Method Summary

Modifier and Type	Method	Description
String	activeCell()	Returns the view's active cell as an A1 reference, such as "B3" (<sheetView><selection activeCell>, ECMA-376 §18.3.1.78), or null when the sheet has no selection.
int	addChart(Chart chart)	Adds a chart to this sheet.
int	addCheckbox(String name, int fromRow, int fromColumn, int toRow, int toColumn, String linkedCell)	Adds a check box form control over a cell rectangle, optionally bound to a linked cell that holds its state.
void	addColumnPageBreak(int column)	Inserts a manual vertical page break left of the 0-based column.
int	addConnector(String preset, int fromRow, int fromCol, int toRow, int toCol)	Add a connector shape (<xdr:cxnSp>) with the given preset geometry name (e.g.
int	addFormControl(FormControl control)	Adds a form control: check box, option button, button, combo box, list box, scroll bar, spin button, edit box, label or group box.
int	addLinkedPicture(String target, String format, int r1, int c1, int r2, int c2)	Adds a linked picture over the cell rectangle from (r1, c1) to (r2, c2): the image is referenced by an external target, a URL or a path, through <a:blip r:link> instead of embedding bytes (ECMA-376 Part 1 §20.1.8.13).
int	addPicture(byte[] imageData, String format, int fromRow, int fromColumn, int toRow, int toColumn)	Inserts an embedded image over the cell rectangle from (fromRow, fromColumn) to (toRow, toColumn).
int	addPicture(byte[] imageData, String format, int fromRow, int fromColumn, int fromRowOffset, int fromColumnOffset, int toRow, int toColumn, int toRowOffset, int toColumnOffset)	Inserts an embedded image with a full two-cell anchor, offsets included.
int	addPivotChart(int pivotIndex, String chartType, int fromRow, int fromCol, int toRow, int toCol)	Add a PivotChart bound to the pivot table at pivotIndex on this sheet, anchored over the given cell rectangle.
int	addPivotTable(String name, String location, String sourceRef)	Creates an empty pivot table whose top-left cell is location (A1, such as "H1") over the source range sourceRef, such as "Data!A1:D100".
void	addProtectedRange(String name, String sqref)	Adds an allow-edit range without a password; see addProtectedRange(String, String, String).
void	addProtectedRange(String name, String sqref, String password)	Adds an allow-users-to-edit-range (protectedRange — ECMA-376 Part 1 §18.3.1.72): the named range sqref stays editable while the sheet is protected.
void	addRowPageBreak(int row)	Inserts a manual horizontal page break above the 0-based row.
int	addScenario(String name, String[] cellRefs, String[] values)	Adds a what-if scenario to this sheet.
int	addShape(String shapeType, String text, int fromRow, int fromCol, int toRow, int toCol)	Adds a drawing shape, such as "rect", "ellipse" or "roundRect", with optional text, over the cell rectangle from (fromRow, fromCol) to (toRow, toCol), 0-based.
int	addSparkline(String type, String dataRef, String locationRef)	Add a sparkline group ("line", "column", or "stacked") that draws dataRef (e.g.
int	addTextBox(String text, int fromRow, int fromCol, int toRow, int toCol)	Add a text box holding text, anchored over the cell rectangle from (fromRow, fromCol) to (toRow, toCol), 0-based; returns the new shape's 0-based index.
boolean	anchorOleObject(int objectIndex, byte[] iconBytes, String iconFormat, int fromRow, int fromCol, int toRow, int toCol)	Display the workbook-level embedded OLE object at objectIndex (see Workbook.addOleObject(java.lang.String, java.lang.String, byte[])) as an icon on this sheet, anchored over the cell rectangle (fromRow, fromCol)–(toRow, toCol) (0-based, exclusive bottom-right edge like a drawing anchor).
int	applyAdvancedFilter(Range listRange, Range criteriaRange, boolean uniqueOnly)	Run an advanced filter (Excel's Data > Advanced) over listRange in place, hiding the data rows that do not match criteriaRange.
int	applyAdvancedFilterCopyTo(Range listRange, Range criteriaRange, Range destination, boolean uniqueOnly)	Advanced filter in "Copy to another location" mode: instead of hiding source rows, writes the list header plus each matching row (see applyAdvancedFilter(Range, Range, boolean) for the criteria semantics) starting at the top-left cell of destination.
int	applyAutoFilter()	Execute the auto-filter: hides every data row failing any column's criteria and shows the rest.
boolean	autoExpandTables(int row, int col, String typedText)	Applies Excel's table auto-expansion for an entry typed at (row, col).
Range	autoFilterRange()	Returns the filtered block, header row included, or null when this sheet has no auto-filter.
void	autoFitColumn(int col)	Sets a column's width to fit the widest text its cells display in the sheet's used rows.
void	autoFitRow(int row)	Sets a row's height to fit its tallest content, as Excel's AutoFit Row Height does.
byte[]	backgroundImage()	Returns the encoded bytes of the sheet background image, or null when there is none.
String	backgroundImageFormat()	Returns the format token of the sheet background image, such as "png", or null when there is none.
int	baseColumnWidth()	Returns the declared baseColWidth in characters, or 0 when the sheet declares none; the spec default of 8 then applies.
Cell	cell(int row, int col)	Returns a single-cell view at (row, col).
ChartView	chart(int chartIndex)	Returns a live view of the chart at chartIndex, its 0-based position in charts().
List<Chart>	charts()	Returns every chart on this sheet, in drawing order.
void	clearAutoFilter()	Removes the auto-filter and its criteria.
boolean	clearBackgroundImage()	Removes the sheet background image.
void	clearCharts()	Removes every chart from this sheet.
void	clearColumnFilter(int columnIndex)	Removes the criterion from one column and runs the filter, so rows that now pass are shown again.
void	clearConditionalFormats()	Removes every conditional-formatting rule from this sheet.
void	clearPageBreaks()	Removes every page break on this sheet: the manual ones and the automatic ones kept from the source file.
void	clearPaperSize()	Removes the paper-size declaration, so the saved sheet has no paperSize attribute and Excel uses the printer's default paper.
void	clearPictures()	Removes every picture from this sheet, embedded and linked.
void	clearValidations()	Removes every data-validation rule from this sheet; the cell values stay.
int[]	columnPageBreaks()	Returns the 0-based columns a vertical page break sits left of, ascending, as rowPageBreaks() does for rows.
CellStyle	columnStyle(int col)	Returns the column-level style: the style that cells in this column without their own style render with (<col style>).
int	columnStyleId(int col)	Returns the column-level style id, or 0 when the column has no column-level style.
double	columnWidth(int col)	Returns a column's width in character units.
List<ConditionalFormat>	conditionalFormats()	Returns every conditional-formatting rule on this sheet, in stored order.
int[]	consolidate(int destRow, int destColumn, ConsolidateFunction function, Range... sources)	Consolidates several source ranges of the same shape, possibly on other sheets, into this sheet at (destRow, destColumn), as Excel's Data > Consolidate does by position.
boolean	convertTableToRange(String name)	Converts the named table back to an ordinary range, as Excel's Table Design > Convert to Range does: drops the table definition and keeps every cell value and style in place.
double	defaultColumnWidth()	Returns the sheet's declared default column width in character units, or 0 when none is declared; column widths then derive from baseColumnWidth().
double	defaultRowHeight()	Returns the sheet's default row height in points: the height of every row without a height of its own.
void	deleteColumns(int startCol, int count)	Deletes count columns starting at startCol, shifting the columns to the right of them left.
void	deleteRows(int startRow, int count)	Deletes count rows starting at startRow, shifting the rows below up.
boolean	differentFirst()	Returns whether the first printed page uses its own header and footer (ECMA-376 §18.3.1.46 differentFirst).
boolean	differentOddEven()	Returns whether odd and even printed pages use different headers and footers (ECMA-376 §18.3.1.46 differentOddEven).
double	dyDescent()	Returns the sheet-level x14ac:dyDescent text-descent hint on <sheetFormatPr>, or 0 when absent.
String	evenFooter()	Returns the even-page footer text, or null when none is set.
String	evenHeader()	Returns the even-page header text, or null when none is set.
int[][]	find(String text)	Finds every text cell whose text contains text, ignoring case, across the whole sheet.
int[][]	find(String text, boolean caseSensitive, boolean matchEntire, boolean wildcard)	Finds text cells as find(String) does, with Excel's Find options.
int[][]	find(String text, boolean caseSensitive, boolean matchEntire, boolean wildcard, FindFormat format)	Finds cells by text and format, as find(String, FindFormat) does, with Excel's Find options.
int[][]	find(String text, FindFormat format)	Finds cells as find(String) does, keeping only the cells whose resolved style matches format: Excel's Find with Format.
int[][]	findRegex(String pattern)	Finds every text cell whose text matches the regular expression pattern, ignoring case, across the whole sheet.
int[][]	findRegex(String pattern, boolean caseSensitive)	Finds text cells as findRegex(String) does, with a choice of case sensitivity.
String	firstFooter()	Returns the first-page footer text, or null when none is set.
String	firstHeader()	Returns the first-page header text, or null when none is set.
int	firstPageNumber()	Returns the number printed on the first page, or 0 when numbering is automatic.
int	fitToHeight()	Returns the number of pages tall the printout is fitted to, or 0 for no limit (ECMA-376 §18.3.1.63 fitToHeight).
void	fitToPage(int width, int height)	Scales the printout to fit width pages wide by height pages tall, and turns on fit-to-page, so pageScale() is ignored.
int	fitToWidth()	Returns the number of pages wide the printout is fitted to, or 0 for no limit (ECMA-376 §18.3.1.63 fitToWidth).
String	footer()	Returns the page footer text, in Excel's header codes, or null when none is set.
int[]	formattedColumns()	Returns the 0-based indexes, ascending, of the columns that carry a column-level record: a width, a hidden flag, a column style or an outline level.
int[]	formattedRows()	Returns the 0-based indexes, ascending, of the rows that carry a row-level record: a height, a hidden flag, a row style or an outline level.
int	formControlCount()	Returns the number of form controls on this sheet, the size of formControls().
List<FormControl>	formControls()	Returns every form control on this sheet, in z-order.
int	freezeColumn()	Returns the number of frozen columns at the left, or 0 when no columns are frozen.
void	freezePanes(int freezeRow, int freezeCol)	Freezes the top freezeRow rows and the left freezeCol columns, as Excel's View > Freeze Panes does with the cell at (freezeRow, freezeCol) selected.
int	freezeRow()	Returns the number of frozen rows at the top, or 0 when no rows are frozen.
void	groupColumns(int startCol, int endCol)	Groups columns startCol to endCol, 0-based and inclusive, at outline level 1.
void	groupRows(int startRow, int endRow)	Groups rows startRow to endRow, 0-based and inclusive, at outline level 1.
int	groupShapes(String name, int... shapeIndexes)	Group existing shapes into one <xdr:grpSp> container (Excel's "Group").
boolean	hasAutoFilter()	Returns whether this sheet has an auto-filter.
boolean	hasPageSetup()	Returns whether the sheet carries a <pageSetup> element (ECMA-376 §18.3.1.63).
String	header()	Returns the page header text, or null when none is set.
byte[]	headerFooterPictureData(String section)	Returns the image bytes of the header or footer picture in section, a token as for setHeaderFooterPicture(java.lang.String, byte[], java.lang.String, double, double), or null when that section has no picture.
<T> int	importData(int startRow, int startCol, List<T> items)	Imports a collection with ImportOptions.defaults(): a header row, then every discovered property as a column.
<T> int	importData(int startRow, int startCol, List<T> items, ImportOptions options)	Import a collection of Java objects into a range.
int	index()	Returns the 0-based position of this sheet's tab in its workbook, as it is now.
void	insertColumns(int startCol, int count)	Inserts count blank columns before startCol, shifting the columns to the right.
void	insertRows(int startRow, int count)	Inserts count blank rows before startRow, shifting the rows below down.
boolean	isColumnHidden(int col)	Returns whether the column is hidden.
boolean	isColumnWidthCustom(int col)	Returns whether the column has its own width, set by setColumnWidth(int, double) or by <col width> in the source file, instead of following the sheet default.
boolean	isDefaultRowHeightCustom()	Returns whether the default row height is flagged as fixed (the sheetFormatPr customHeight flag).
boolean	isFitToPage()	Returns whether fit-to-page scaling is on, as fitToPage(int, int) sets it.
boolean	isLandscape()	Returns whether the sheet prints in landscape; false means portrait, the default.
boolean	isPaperSizeDeclared()	Returns whether this sheet declares a paper size.
boolean	isProtected()	Returns whether this sheet is protected.
boolean	isProtectedWithPassword()	Returns whether the sheet's protection carries a password.
boolean	isRowHeightCustom(int row)	Returns whether the row has its own height instead of following the sheet default.
boolean	isRowHidden(int row)	Returns whether the row's hidden flag is set, by setRowHidden(int, boolean), by a filter or a collapsed group, or by the source file.
boolean	isTextBox(int shapeIndex)	Returns whether the shape at shapeIndex is a text box (<xdr:cNvSpPr txBox="1"/>): one added by addTextBox(java.lang.String, int, int, int, int) or read from a file.
List<Range>	mergedRegions()	Returns the merged-cell regions on this sheet, one Range per region.
String	name()	Returns the sheet's tab name, as shown in Excel.
PageMargins	pageMargins()	Returns the six page margins in inches, as a snapshot.
boolean	pageOrderOverThenDown()	Returns whether pages print across, then down; false means down, then across.
int	pageScale()	Returns the print scale in percent; 100 means actual size.
int	paperSize()	Returns the paper size as its ECMA-376 numeric code; 9 (A4) when none is declared.
int[]	pictureAnchor(int pictureIndex)	Returns {fromRow, fromCol, toRow, toCol} of the picture's two-cell anchor, 0-based, or an empty array when the index is out of range.
int[]	pictureAnchorFull(int pictureIndex)	Returns {fromRow, fromCol, fromRowOffset, fromColOffset, toRow, toCol, toRowOffset, toColOffset} of the picture's anchor: 0-based cells and offsets in EMU.
int	pictureCount()	Returns the number of pictures on this sheet, embedded and linked.
byte[]	pictureData(int pictureIndex)	Returns the encoded image bytes of the picture at pictureIndex, or null when the index is out of range.
String	pictureFormat(int pictureIndex)	Returns the format token of the picture at pictureIndex, such as "png" or "jpeg", or null when the index is out of range.
String	pictureLinkTarget(int pictureIndex)	Returns the external link target of the picture at pictureIndex, or null when it is an embedded picture or the index is out of range.
int	pivotCount()	Returns the number of pivot tables on this sheet.
String	pivotLocation(int pivotIndex)	Returns the location of the pivot table at pivotIndex as an A1 reference, such as "H1".
String	pivotName(int pivotIndex)	Returns the name of the pivot table at pivotIndex.
String	pivotSource(int pivotIndex)	Returns the source data reference of the pivot table at pivotIndex, such as "Data!A1:D100".
PivotTableView	pivotTable(int pivotIndex)	Returns a live view of the pivot table at pivotIndex: field areas (row, column, filter, values), aggregation functions, layout, grand totals and PivotTableView.refresh().
PivotTableView	pivotTable(String name)	Returns a live view of the pivot table named name.
String	printArea()	Returns the print area as an A1 range string, or null when the sheet has none.
boolean	printBlackAndWhite()	Returns whether the sheet prints in black and white.
int	printComments()	Returns where cell comments print, as the code setPrintComments(int) takes.
boolean	printDraft()	Returns whether the sheet prints in draft quality.
int	printErrors()	Returns how error cells print, as the code setPrintErrors(int) takes.
boolean	printGridlines()	Returns whether gridlines are printed (ECMA-376 §18.3.1.65 printOptions/gridLines).
boolean	printHeadings()	Returns whether row numbers and column letters are printed.
boolean	printHorizontallyCentered()	Returns whether the printout is centered horizontally on the page (<printOptions horizontalCentered>, ECMA-376 §18.3.1.70).
int	printResolution()	Returns the printer resolution in dots per inch.
String	printTitleColumns()	Returns the columns repeated at the left of every printed page, such as "$A:$A", or null when none are set.
String	printTitleRows()	Returns the rows repeated at the top of every printed page, such as "$1:$2", or null when none are set.
boolean	printVerticallyCentered()	Returns whether the printout is centered vertically on the page (<printOptions verticalCentered>, ECMA-376 §18.3.1.70).
void	protect()	Protects this sheet with Excel's default allowances and no password.
void	protect(String password)	Protects this sheet with a password and Excel's default allowances.
void	protect(String password, SheetProtection options)	Protects this sheet with the given allowances (ECMA-376 §18.3.1.85) and an optional password.
List<ProtectedRange>	protectedRanges()	Returns the sheet's allow-edit ranges, in the order they were defined.
SheetProtection	protection()	Returns the protection options, as what users may still do, or null when this sheet is not protected.
Range	range(int r1, int c1, int r2, int c2)	Returns a range over the rectangle from (r1, c1) to (r2, c2), both corners inclusive and 0-based.
Range	range(String a1)	Returns a range parsed from A1 notation on this sheet: "A1", "$A$1:C10", a column band "B:D" or a row band "2:5".
boolean	removeChart(int chartIndex)	Removes the chart at chartIndex, its 0-based position in charts().
boolean	removeConditionalFormat(int formatIndex)	Removes the conditional-formatting rule at formatIndex, its 0-based position in conditionalFormats().
boolean	removeFormControl(int controlIndex)	Removes the form control at controlIndex, its 0-based position in formControls().
boolean	removeHeaderFooterPicture(String section)	Removes the header or footer picture in section, a token as for setHeaderFooterPicture(java.lang.String, byte[], java.lang.String, double, double).
boolean	removePicture(int pictureIndex)	Removes the picture at pictureIndex, the 0-based index that pictureData(int) uses.
boolean	removeProtectedRange(String name)	Removes the allow-edit range named name.
boolean	removeShape(int shapeIndex)	Removes the shape at shapeIndex, the 0-based index that shapeType(int) uses.
boolean	removeTable(String name)	Removes the table definition named name and leaves its cells, values and styles in place.
boolean	removeValidation(int validationIndex)	Removes the data-validation rule at validationIndex, its 0-based position in validations().
byte[]	renderChartToPng(int chartIndex, int width, int height)	Renders the chart at chartIndex to PNG bytes.
String	renderChartToSvg(int chartIndex, int width, int height)	Renders the chart at chartIndex to a standalone SVG document.
int	replaceAll(String find, String replace)	Replaces find with replace in every text cell of the sheet that contains it, ignoring case.
int	replaceAll(String find, String replace, boolean caseSensitive, boolean matchEntire, boolean wildcard)	Replaces text as replaceAll(String, String) does, with Excel's Replace options.
int	replaceAll(String find, String replace, boolean caseSensitive, boolean matchEntire, boolean wildcard, FindFormat format)	Replaces text by format, as replaceAll(String, String, FindFormat) does, with Excel's Replace options.
int	replaceAll(String find, String replace, FindFormat format)	Replaces text as replaceAll(String, String) does, only in the cells whose resolved style matches format.
int	replaceAllRegex(String pattern, String replacement)	Replaces every match of the regular expression pattern, ignoring case, in every text cell of the sheet.
int	replaceAllRegex(String pattern, String replacement, boolean caseSensitive)	Replaces regular-expression matches as replaceAllRegex(String, String) does, with a choice of case sensitivity.
void	replaceChart(int chartIndex, Chart chart)	Apply chart's spec onto the chart already at chartIndex, in place, keeping its position in charts().
boolean	resizeTable(String name, Range newRange)	Resizes the named table to newRange, as Excel's Table Design > Resize Table does.
boolean	rightToLeft()	Returns whether this sheet is displayed right-to-left: column A at the right edge.
double	rowHeight(int row)	Returns a row's height in points (1/72 inch).
int	rowOutlineLevel(int row)	Returns a row's outline level, from 0 (not grouped) to 7.
int[]	rowPageBreaks()	Returns the 0-based rows a horizontal page break sits above, ascending: the breaks added here and those read from an opened file, automatic ones included.
CellStyle	rowStyle(int row)	Returns the row-level style: the style that cells in this row without their own style render with.
int	rowStyleId(int row)	Returns the row-level style id (<row s="N" customFormat="1">), or 0 when the row has no row-level style.
int	scenarioCount()	Returns the number of what-if scenarios on this sheet.
String	scenarioName(int scenarioIndex)	Returns the name of the scenario at scenarioIndex.
int	scrollColumn()	Returns the 0-based first visible column of an unfrozen view; see scrollRow().
int	scrollRow()	Returns the 0-based first visible row of an unfrozen view: the row part of <sheetView topLeftCell> (ECMA-376 Part 1 §18.3.1.87).
String	selectionSqref()	Returns the view's selected ranges (<selection sqref>) as a space-separated list of A1 ranges, such as "A1:B2 D4", or null when nothing is selected.
void	setActiveCell(String cell, String sqref)	Moves the cursor and the selection.
void	setAutoFilter(Range range)	Defines an auto-filter over range; its top row holds the filter buttons.
void	setBackgroundImage(byte[] imageData, String format)	Sets the sheet background image.
void	setBaseColumnWidth(int width)	Sets the baseColWidth in characters.
void	setColumnColorFilter(int columnIndex, boolean cellColor, Color color)	Filters a column by cell fill color, or by font color when cellColor is false: only rows whose cell has color stay visible.
void	setColumnCustomFilter(int columnIndex, FilterOperator op, String value)	Sets a one-condition custom filter on a column, such as "greater than 100", and runs the filter.
void	setColumnCustomFilter(int columnIndex, FilterOperator op1, String value1, FilterOperator op2, String value2, boolean andJoin)	Sets a two-condition custom filter on a column and runs the filter.
void	setColumnDateFilter(int columnIndex, boolean includeBlank, DateGroupItem... items)	Sets a date grouping filter as setColumnDateFilter(int, DateGroupItem...) does, optionally keeping blank cells too.
void	setColumnDateFilter(int columnIndex, DateGroupItem... items)	A date grouping filter (ECMA-376 §18.3.2.4) on a column: a date cell stays visible if it falls inside any of items.
void	setColumnDynamicFilter(int columnIndex, DynamicFilterType type)	A dynamic filter (ECMA-376 §18.3.2.5) on a column: today / thisWeek / lastMonth / yearToDate / aboveAverage / and so on.
void	setColumnFilter(int columnIndex, String... values)	Keeps only the rows whose cell in the filter column is one of values.
void	setColumnGroupCollapsed(int startCol, int endCol, boolean collapsed)	Collapses or expands a grouped column range, as setRowGroupCollapsed(int, int, boolean) does for rows.
void	setColumnHidden(int col, boolean hidden)	Hides or shows a column.
void	setColumnsOutlineLevel(int startCol, int endCol, int level)	Sets the outline level on columns startCol to endCol, 0-based and inclusive.
void	setColumnStyle(int col, CellStyle style)	Sets the column-level style: the style that cells in this column without their own style render with (<col style>).
void	setColumnTop10Filter(int columnIndex, boolean percent, double value, boolean bottom)	A Top-10 filter (ECMA-376 §18.3.2.10) on a column: keep the top value items — or value percent of the items when percent is true; bottom flips to the smallest values.
void	setColumnWidth(int col, double width)	Sets a column's width in Excel character units: the number of '0' digits of the workbook's default font that fit in the column.
void	setDefaultColumnWidth(double width)	Sets the default column width in character units.
void	setDefaultRowHeight(double points)	Sets the default row height in points.
void	setDefaultRowHeightCustom(boolean custom)	Sets the sheetFormatPr customHeight flag; see isDefaultRowHeightCustom().
void	setDifferentFirst(boolean on)	Sets whether the first printed page uses its own header and footer, Excel's "Different first page" option.
void	setDifferentOddEven(boolean on)	Sets whether odd and even printed pages use different headers and footers, Excel's "Different odd and even pages" option.
void	setDyDescent(double dy)	Sets the sheet-level x14ac:dyDescent hint; see dyDescent().
void	setEvenFooter(String text)	Sets the even-page footer text.
void	setEvenHeader(String text)	Sets the even-page header text.
void	setFirstFooter(String text)	Sets the first-page footer text.
void	setFirstHeader(String text)	Sets the first-page header text.
void	setFirstPageNumber(int number)	Sets the number printed on the first page (ECMA-376 §18.3.1.63 firstPageNumber together with useFirstPageNumber).
void	setFooter(String text)	Sets the page footer text, in Excel's header codes (see setHeader(java.lang.String)).
void	setHeader(String text)	Sets the page header text, in Excel's header codes, for example "&CQuarterly report&RPage &P".
boolean	setHeaderFooterPicture(String section, byte[] data, String format, double widthPt, double heightPt)	Place a picture in a header/footer section (ECMA-376 §18.3.1.32 legacyDrawingHF).
void	setLandscape(boolean landscape)	Sets the print orientation: true for landscape, false for portrait.
void	setNumber(int row, int col, double value)	Writes a number into one cell.
void	setPageMargins(double left, double right, double top, double bottom, double header, double footer)	Sets the six page margins in inches (ECMA-376 §18.3.1.62 pageMargins).
void	setPageMargins(PageMargins m)	Sets the page margins from a PageMargins value, in inches.
void	setPageOrderOverThenDown(boolean overThenDown)	Sets the page order: true prints across, then down ("Over, then down"); false prints down, then across, Excel's default (ECMA-376 §18.3.1.63 pageOrder).
void	setPageScale(int scalePercent)	Sets the print scale in percent and turns fit-to-page off.
void	setPaperSize(int paperSize)	Sets the paper size from its ECMA-376 numeric code (§18.18.50 ST_PaperSize): 1 = Letter, 5 = Legal, 8 = A3, 9 = A4, 11 = A5, and so on.
boolean	setPictureLinkTarget(int pictureIndex, String target)	Repoints a linked picture at a new source.
void	setPrintArea(String a1Range)	Sets the print area as an A1 range on this sheet, such as "$A$1:$F$40".
void	setPrintBlackAndWhite(boolean on)	Sets whether the sheet prints in black and white (ECMA-376 §18.3.1.63 blackAndWhite).
void	setPrintComments(int mode)	Sets where cell comments print (ECMA-376 §18.3.1.63 cellComments).
void	setPrintDraft(boolean on)	Sets whether the sheet prints in draft quality, without graphics (ECMA-376 §18.3.1.63 draft).
void	setPrintErrors(int mode)	Sets how error cells print (ECMA-376 §18.3.1.70 printOptions/errors).
void	setPrintGridlines(boolean on)	Sets whether gridlines are printed.
void	setPrintHeadings(boolean on)	Sets whether row numbers and column letters are printed (ECMA-376 §18.3.1.70 printOptions/headings).
void	setPrintHorizontallyCentered(boolean centered)	Sets whether the printout is centered horizontally on the page.
void	setPrintResolution(int dpi)	Sets the printer resolution in dots per inch, for both axes.
void	setPrintTitleColumns(String columnsRange)	Sets the columns repeated at the left of every printed page, such as "$A:$A".
void	setPrintTitleRows(String rowsRange)	Sets the rows repeated at the top of every printed page, such as "$1:$2".
void	setPrintVerticallyCentered(boolean centered)	Sets whether the printout is centered vertically on the page.
void	setRightToLeft(boolean rightToLeft)	Sets whether this sheet is displayed right-to-left: column A at the right edge.
void	setRowGroupCollapsed(int startRow, int endRow, boolean collapsed)	Collapse or expand a grouped row range (Excel's outline +/- button): hides or shows the detail rows and sets the collapsed flag on the group's summary row: the row below the group when summaryBelow() is true, else the row above.
void	setRowHeight(int row, double points)	Sets a row's height in points (1/72 inch) and marks the height as the row's own, so Excel keeps it on open.
void	setRowHidden(int row, boolean hidden)	Hides or shows a row.
void	setRowsOutlineLevel(int startRow, int endRow, int level)	Sets the outline level on rows startRow to endRow, 0-based and inclusive.
void	setRowStyle(int row, CellStyle style)	Sets the row-level style: the style that cells in this row without their own style render with.
boolean	setScrollPosition(int row, int column)	Scrolls an unfrozen view so that (row, column) sits at the top left.
void	setShapeAltText(int shapeIndex, String text)	Sets, or clears with null, the shape's alternative text.
void	setShapeFillColor(int shapeIndex, Color color)	Sets the shape's fill to a solid color, replacing any gradient or pattern fill.
void	setShapeFlip(int shapeIndex, boolean flipHorizontal, boolean flipVertical)	Sets the shape's horizontal and vertical flip flags.
void	setShapeHyperlink(int shapeIndex, String url)	Sets, or clears with null, the shape's hyperlink URL.
void	setShapeLineColor(int shapeIndex, Color color)	Sets the shape's outline color.
boolean	setShapeName(int shapeIndex, String name)	Renames a shape.
void	setShapeRotation(int shapeIndex, double degrees)	Sets the shape's rotation in degrees, clockwise.
void	setShapeStyleXml(int shapeIndex, String xml)	Sets, or clears with null, the shape's <xdr:style> theme style-matrix reference from raw inner XML (the lnRef / fillRef / effectRef / fontRef children, without the enclosing <xdr:style> tags).
void	setShapeText(int shapeIndex, String text)	Replaces the shape's text, or clears it with null.
void	setShowFormulas(boolean on)	Turns the show-formulas display mode on or off; see showFormulas().
void	setShowGridlines(boolean on)	Shows or hides gridlines in the on-screen view; printing is set by setPrintGridlines(boolean).
void	setShowRowColHeaders(boolean on)	Shows or hides the row numbers and column letters in the on-screen view; printing is set by setPrintHeadings(boolean).
void	setShowZeros(boolean on)	Sets whether cells holding zero show 0 (true) or blank; the values do not change.
boolean	setSourceSheetId(int id)	Renumbers the sheet's <sheet sheetId>.
boolean	setSparklineStyle(int group, SparklineStyle style)	Replaces the whole style of the sparkline group at group: colors, point-highlight flags and axis settings.
void	setSummaryBelow(boolean below)	Sets whether row-group summaries sit below their detail rows.
void	setSummaryRight(boolean right)	Sets whether column-group summaries sit right of their detail columns.
void	setTabColor(Color color)	Sets the tab color.
boolean	setTableColumnTotals(String tableName, String columnName, TotalsRowFunction function)	Sets the totals-row function of one column of the named table (ECMA-376 §18.18.92 ST_TotalsRowFunction).
boolean	setTableColumnTotals(String tableName, String columnName, TotalsRowFunction function, String label, String formula)	Sets the totals-row function, label and custom formula of one column of the named table.
boolean	setTableHeaderRow(String tableName, boolean shown)	Shows or hides the named table's header row (ECMA-376 §18.5.1.2 headerRowCount).
boolean	setTableStyle(String tableName, TableStyleInfo style)	Replaces the style settings of the named table (ECMA-376 §18.5.1.20 tableStyleInfo): the style name and the banding and first/last-column flags.
boolean	setTableTotalsRow(String tableName, boolean shown)	Shows or hides the named table's totals row (ECMA-376 §18.5.1.2 totalsRowCount).
void	setViewType(String view)	Sets the view the tab opens in.
void	setVisibility(Visibility visibility)	Sets whether this sheet's tab is visible, hidden or very hidden.
void	setZoom(int percent)	Sets the on-screen zoom in percent.
String	shapeAltText(int shapeIndex)	Returns the shape's alternative text (cNvPr/@descr), or null when it has none.
int[]	shapeAnchor(int shapeIndex)	Returns {fromRow, fromCol, toRow, toCol} of the shape's anchor, 0-based, or an empty array when the index has no shape.
int	shapeCount()	Returns the number of shapes on this sheet, text boxes, connectors and groups included.
Color	shapeFillColor(int shapeIndex)	Returns the shape's solid fill color as an RGB color, or null when the shape has no solid RGB fill: no fill, a gradient, a pattern or a theme color.
boolean[]	shapeFlip(int shapeIndex)	Returns {flipHorizontal, flipVertical}, or an empty array when the index has no shape.
String	shapeHyperlink(int shapeIndex)	Returns the hyperlink URL attached to the shape through DrawingML hlinkClick, or null when the shape has no hyperlink.
Color	shapeLineColor(int shapeIndex)	Returns the shape's outline color as an RGB color, or null when it has no solid RGB outline.
String	shapeName(int shapeIndex)	Returns the shape's drawing name (<xdr:cNvPr name>, ECMA-376 Part 1 §20.5.2.16): what Excel shows in the Name Box and what a macro uses to address the shape.
double	shapeRotation(int shapeIndex)	Returns the shape's rotation in degrees, clockwise.
String	shapeStyleXml(int shapeIndex)	Returns the raw inner XML of the shape's <xdr:style> theme style-matrix reference (the lnRef / fillRef / effectRef / fontRef children), or null when the shape has no such block.
String	shapeText(int shapeIndex)	Returns the shape's text, or null when it has none or the index has no shape.
String	shapeType(int shapeIndex)	Returns the geometry token of the shape at shapeIndex, such as "rect".
SheetType	sheetType()	Returns what kind of tab this is: worksheet, chart sheet, dialog sheet or Excel 4.0 macro sheet (international or not).
boolean	showFormulas()	Returns whether the grid shows formula text instead of results (<sheetView showFormulas>).
boolean	showGridlines()	Returns whether the on-screen view draws gridlines (<sheetView showGridLines>, ECMA-376 §18.3.1.87).
boolean	showRowColHeaders()	Returns whether the on-screen view shows the row numbers and column letters (showRowColHeaders).
boolean	showScenario(int scenarioIndex)	Applies the scenario at scenarioIndex: writes each of its changing-cell values into the sheet.
boolean	showZeros()	Returns whether a cell holding zero shows 0 (true) or shows blank (<sheetView showZeros>, ECMA-376 §18.3.1.87).
int	smartArtCount()	Returns the number of SmartArt diagrams anchored on this sheet.
List<SmartArt>	smartArts()	Returns the SmartArt diagrams anchored on this sheet as read-only descriptors: layout family, anchor and visible node texts.
void	sortByColor(Range range, int sortColumnIndex, boolean cellColor, boolean hasHeader, Color... colors)	Sort the rows of range by cell fill color (or font color when cellColor is false) at sortColumnIndex (0-based, relative to the range start).
int	sourceSheetId()	Returns the workbook-level id this sheet was read with (<sheet sheetId> in xl/workbook.xml), or 0 for a sheet created through this API; the writer allocates an id for those on save.
int	sparklineGroupCount()	Returns the number of sparkline groups on this sheet.
List<SparklineGroup>	sparklines()	All sparkline groups on this sheet (ECMA-376 Part 4 §18.3.1.32, x14:sparklineGroups): the group type, basic marker/highlight style, series color and each sparkline's data range and draw location.
int	splitColumn()	Returns the 0-based column just right of the vertical split, or 0 when there is none.
void	splitPanes(int splitRow, int splitColumn)	Splits the view into panes that scroll on their own, as Excel's View > Split does.
int	splitRow()	Returns the 0-based row just below the horizontal split, or 0 when there is none.
int	subtotal(int firstRow, int lastRow, int groupColumn, boolean hasHeaders, SubtotalFunction function, int... subtotalColumns)	Insert subtotals into [firstRow, lastRow] as above.
int	subtotal(int firstRow, int lastRow, int groupColumn, SubtotalFunction function, int... subtotalColumns)	Insert subtotals into the sorted data block [firstRow, lastRow]: after each run of equal values in groupColumn, a summary row of function over subtotalColumns is inserted (plus a grand total).
boolean	summaryBelow()	Returns whether a row group's summary row sits below its detail rows (<outlinePr summaryBelow>, ECMA-376 §18.3.1.61).
boolean	summaryRight()	Returns whether a column group's summary column sits right of its detail columns (<outlinePr summaryRight>).
Color	tabColor()	Returns the tab color, or null when the tab has none.
List<Table>	tables()	Returns every table on this sheet (Excel's ListObjects), with its range, columns, style and totals state.
int	textToColumns(String srcRange, int destRow, int destColumn, String delimiters, boolean treatConsecutiveAsOne)	Splits text as textToColumns(String, String) does, writing the fields to another place: the fields of source row i go to row destRow + i, starting at destColumn.
int	textToColumns(String srcRange, String delimiters)	Splits text in place, as Excel's Text to Columns does in delimited mode.
int	textToColumns(String srcRange, String delimiters, boolean treatConsecutiveAsOne)	Splits text in place as textToColumns(String, String) does, optionally treating a run of delimiters as one split point: Excel's "Treat consecutive delimiters as one", useful for space-aligned text.
void	unfreeze()	Removes any frozen panes; the same as freezePanes(0, 0).
boolean	ungroupShapes(int shapeIndex)	Dissolve a group created by groupShapes(java.lang.String, int...): members return to standalone emission and the group entry is removed.
String	uniqueId()	Returns the sheet's revision GUID (Excel's xr:uid on the sheet part root), braces included, for example "{B9A12774-C2A8-4D3E-B631-D37318C68856}".
void	unprotect()	Removes sheet protection without checking any password.
boolean	unprotect(String password)	Disable sheet protection after verifying password against the stored hash (ISO hash, or the legacy 16-bit password attribute of transitional files).
boolean	updateFormControl(int controlIndex, FormControl control)	Replaces the form control at controlIndex, its 0-based position in formControls().
Range	usedRange()	Returns the smallest range that covers every stored cell, or null when the sheet has no cells.
List<Validation>	validations()	Returns every data-validation rule on this sheet, in stored order.
CellValue	value(int row, int col)	Returns the value of one cell: its cached result for a formula cell, CellValue.BLANK for an empty cell.
boolean	verifyProtectedRangePassword(String name, String password)	Returns whether password matches the stored hash of the allow-edit range name, matched case-insensitively.
boolean	verifyProtectionPassword(String password)	Returns whether password matches the sheet's stored hash.
String	viewType()	Returns the view the tab opens in (<sheetView view>, ECMA-376 §18.3.1.87): "normal", "pageBreakPreview" or "pageLayout".
Visibility	visibility()	Returns whether this sheet's tab is visible, hidden or very hidden.
int	zoom()	Returns the on-screen zoom in percent; 100 by default.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Method Details

name

public String name()

Returns the sheet's tab name, as shown in Excel.

index

public int index()

Returns the 0-based position of this sheet's tab in its workbook, as it is now. A Sheet is bound to its tab, not to a position: after Workbook.insertSheet(int, java.lang.String), Workbook.moveSheet(int, int) or a delete of another sheet it keeps referring to the same tab, and this value changes.

Throws:

IllegalStateException - if this sheet has been deleted

visibility

public Visibility visibility()

Returns whether this sheet's tab is visible, hidden or very hidden.

setVisibility

public void setVisibility(Visibility visibility)

Sets whether this sheet's tab is visible, hidden or very hidden. A Visibility.VERY_HIDDEN tab cannot be unhidden from Excel's user interface; only code can show it again. Nothing stops you from hiding the last visible sheet. Keep at least one sheet visible, because Excel needs one.

Parameters:

visibility - the new state; null acts as Visibility.HIDDEN

sheetType

public SheetType sheetType()

Returns what kind of tab this is: worksheet, chart sheet, dialog sheet or Excel 4.0 macro sheet (international or not).

The engine models worksheet content only, so a chart, dialog or macro tab reads back with an empty grid. A chart sheet survives a save as a chart sheet: an imported one is written back unchanged, and one added with Workbook.addChartSheet(java.lang.String, io.keikai.axyra.sheets.content.Chart) is generated from its chart. A dialog or macro tab is written back as an ordinary worksheet.

uniqueId

public String uniqueId()

Returns the sheet's revision GUID (Excel's xr:uid on the sheet part root), braces included, for example "{B9A12774-C2A8-4D3E-B631-D37318C68856}". A sheet created through this API gets one at creation, a copied sheet gets its own, and an imported sheet keeps the file's. The id survives renames and a save to .xlsx / .xlsm and reopen, so callers can key data to a sheet across renames; the .xlsb, .xls and .ods writers do not carry it, and a sheet read from those formats gets a new id. Returns null only for an imported sheet whose source declared none: files written before Excel 2014 and dialog sheets.

rightToLeft

public boolean rightToLeft()

Returns whether this sheet is displayed right-to-left: column A at the right edge.

setRightToLeft

public void setRightToLeft(boolean rightToLeft)

Sets whether this sheet is displayed right-to-left: column A at the right edge.

tabColor

public Color tabColor()

Returns the tab color, or null when the tab has none. RGB, theme (with tint) and legacy indexed colors are all returned as they are stored; check the kind with Color.isRgb(), Color.isTheme() or Color.isIndexed() before reading its components.

setTabColor

public void setTabColor(Color color)

Sets the tab color. RGB, theme and indexed colors are all accepted and kept as their kind; a theme color keeps its tint to four decimal places.

Parameters:

color - the color; null or Color.auto() clears it, since a tab has no automatic color

header

public String header()

Returns the page header text, or null when none is set. The text uses Excel's header codes, for example &L, &C and &R for the sections and &P for the page number. When differentOddEven() is on, this is the odd-page header.

setHeader

public void setHeader(String text)

Sets the page header text, in Excel's header codes, for example "&CQuarterly report&RPage &P".

Parameters:

text - the header text; null clears it

footer

public String footer()

Returns the page footer text, in Excel's header codes, or null when none is set.

setFooter

public void setFooter(String text)

Sets the page footer text, in Excel's header codes (see setHeader(java.lang.String)).

Parameters:

text - the footer text; null clears it

firstHeader

public String firstHeader()

Returns the first-page header text, or null when none is set. It prints only when differentFirst() is on.

setFirstHeader

public void setFirstHeader(String text)

Sets the first-page header text. It prints only when differentFirst() is on; see setDifferentFirst(boolean).

Parameters:

text - the header text; null clears it

firstFooter

public String firstFooter()

Returns the first-page footer text, or null when none is set. It prints only when differentFirst() is on.

setFirstFooter

public void setFirstFooter(String text)

Sets the first-page footer text. It prints only when differentFirst() is on; see setDifferentFirst(boolean).

Parameters:

text - the footer text; null clears it

differentFirst

public boolean differentFirst()

Returns whether the first printed page uses its own header and footer (ECMA-376 §18.3.1.46 differentFirst). When true, page 1 prints firstHeader() and firstFooter() instead of the default ones, for example to leave the page number off a cover page.

setDifferentFirst

public void setDifferentFirst(boolean on)

Sets whether the first printed page uses its own header and footer, Excel's "Different first page" option. Set the text with setFirstHeader(java.lang.String) and setFirstFooter(java.lang.String).

evenHeader

public String evenHeader()

Returns the even-page header text, or null when none is set. It prints only when differentOddEven() is on.

setEvenHeader

public void setEvenHeader(String text)

Sets the even-page header text. It prints only when differentOddEven() is on; see setDifferentOddEven(boolean).

Parameters:

text - the header text; null clears it

evenFooter

public String evenFooter()

Returns the even-page footer text, or null when none is set. It prints only when differentOddEven() is on.

setEvenFooter

public void setEvenFooter(String text)

Sets the even-page footer text. It prints only when differentOddEven() is on; see setDifferentOddEven(boolean).

Parameters:

text - the footer text; null clears it

differentOddEven

public boolean differentOddEven()

Returns whether odd and even printed pages use different headers and footers (ECMA-376 §18.3.1.46 differentOddEven). When true, evenHeader() and evenFooter() print on pages 2, 4, 6, … and the default ones on pages 1, 3, 5, …, for example for mirrored layouts in a bound document.

setDifferentOddEven

public void setDifferentOddEven(boolean on)

Sets whether odd and even printed pages use different headers and footers, Excel's "Different odd and even pages" option. Set the even-page text with setEvenHeader(java.lang.String) and setEvenFooter(java.lang.String).

range

public Range range(int r1,
 int c1,
 int r2,
 int c2)

Returns a range over the rectangle from (r1, c1) to (r2, c2), both corners inclusive and 0-based. The range is a view: it reads and writes this sheet's current cells. Use it for bulk reads and writes, such as Range.setValues(io.keikai.axyra.sheets.CellValue[][]) and Range.values().

Parameters:

r1 - 0-based first row

c1 - 0-based first column

r2 - 0-based last row, inclusive

c2 - 0-based last column, inclusive

Throws:

IndexOutOfBoundsException - if a coordinate is negative or past XFD1048576

IllegalArgumentException - if r2 < r1 or c2 < c1

range

public Range range(String a1)

Returns a range parsed from A1 notation on this sheet: "A1", "$A$1:C10", a column band "B:D" or a row band "2:5". A band covers the whole grid on its other axis. A sheet-qualified reference such as "Sheet1!A1" is not accepted.

Parameters:

a1 - the reference in A1 notation

Throws:

IllegalArgumentException - if a1 is null, mixes a column and a row endpoint, or cannot be parsed

IndexOutOfBoundsException - if a coordinate is past XFD1048576

cell

public Cell cell(int row,
 int col)

Returns a single-cell view at (row, col). The view reads and writes the live cell. Each call on it crosses into the engine once; for many cells, use range(int, int, int, int) instead.

Parameters:

row - 0-based row index

col - 0-based column index

Throws:

IndexOutOfBoundsException - if a coordinate is negative or past XFD1048576

value

public CellValue value(int row,
 int col)

Returns the value of one cell: its cached result for a formula cell, CellValue.BLANK for an empty cell. One engine call per cell; for a block, use Range.values().

Parameters:

row - 0-based row index

col - 0-based column index

setNumber

public void setNumber(int row,
 int col,
 double value)

Writes a number into one cell. Only the value changes: the cell keeps its style, comment and hyperlink, and a formula there is dropped, as with Cell.setValue(io.keikai.axyra.sheets.CellValue). A NaN clears the cell. One engine call per cell; for a block, use Range.setNumbers(double[]).

Parameters:

row - 0-based row index

col - 0-based column index

importData

public <T> int importData(int startRow,
 int startCol,
 List<T> items,
 ImportOptions options)

Import a collection of Java objects into a range. Each element becomes one row starting at (startRow, startCol); the object's properties become columns. Properties are discovered by reflection — Java records first (component order), else JavaBean getters (getX/isX, ordered by property name), else public instance fields (declared order). An element that is a Map is a row keyed by column name: its keys become the columns, in the order they are first met across the rows (pass ImportOptions.columns or use a LinkedHashMap for a fixed order), and a missing key leaves its cell blank. Values map to CellValue: numbers stay numeric, Boolean to bool, CharSequence to text, LocalDate/LocalDateTime/Date to Excel date serials, null to blank, anything else to its toString().

Rows are written through the bulk Range.setValues(io.keikai.axyra.sheets.CellValue[][]) path in chunks, one engine call per chunk.

Dates are written as serial numbers in the workbook's date system (1900 or 1904), with no number format. Apply a date format to those columns, for example with Range.setStyle(io.keikai.axyra.sheets.style.CellStyle), or they display as plain numbers. A Date is converted in the JVM's default time zone. An empty items list writes only a header row, labelled from columnLabels or columns; with neither set, nothing is written and the method returns 0.

Parameters:

startRow - 0-based row of the first written row: the header row when headers are on

startCol - 0-based column of the first property

items - the objects to import; must all be the same class as the first element, or all be maps

options - column selection, header, labels, and header style; when null, ImportOptions.defaults() apply

Returns:

the number of rows written, including the header row when enabled

Throws:

AxyraException - if items is null, the elements are of mixed types, an element is a plain value (a string, number, date, collection or array) rather than a row, or an options.columns() name is unknown

importData

public <T> int importData(int startRow,
 int startCol,
 List<T> items)

Imports a collection with ImportOptions.defaults(): a header row, then every discovered property as a column. See importData(int, int, List, ImportOptions) for the mapping.

Parameters:

startRow - 0-based row of the header row

startCol - 0-based column of the first property

Returns:

the number of rows written, the header row included

insertRows

public void insertRows(int startRow,
 int count)

Inserts count blank rows before startRow, shifting the rows below down. Like Excel, it updates every reference to the moved cells across the workbook: formulas on this and other sheets, defined names (print areas and titles too), chart series, conditional-format and validation ranges, pivot sources and locations, and sparklines. An insert inside a referenced area extends the area.

Parameters:

startRow - 0-based row before which the rows go

count - number of rows; 0 does nothing, and a negative value is not checked

deleteRows

public void deleteRows(int startRow,
 int count)

Deletes count rows starting at startRow, shifting the rows below up. References across the workbook are updated as in insertRows(int, int). A reference that pointed only into the deleted rows becomes #REF!, and an area that crossed them shrinks.

Parameters:

startRow - 0-based first row to delete

count - number of rows; 0 does nothing

Throws:

AxyraException - if the rows include a table's header row but not the whole table; Excel refuses that delete too

insertColumns

public void insertColumns(int startCol,
 int count)

Inserts count blank columns before startCol, shifting the columns to the right. References across the workbook are updated as in insertRows(int, int).

Parameters:

startCol - 0-based column before which the columns go

count - number of columns; 0 does nothing, and a negative value is not checked

deleteColumns

public void deleteColumns(int startCol,
 int count)

Deletes count columns starting at startCol, shifting the columns to the right of them left. References across the workbook are updated as in deleteRows(int, int); a reference that pointed only into the deleted columns becomes #REF!.

Parameters:

startCol - 0-based first column to delete

count - number of columns; 0 does nothing

setRowHeight

public void setRowHeight(int row,
 double points)

Sets a row's height in points (1/72 inch) and marks the height as the row's own, so Excel keeps it on open. The value is stored as given: it is not clamped to Excel's 0 to 409 range. To fit the height to the content, use autoFitRow(int).

Parameters:

row - 0-based row index; not bounds-checked

points - height in points

rowHeight

public double rowHeight(int row)

Returns a row's height in points (1/72 inch). For a row with no height record, returns defaultRowHeight(), or 15 when the sheet declares no default.

Parameters:

row - 0-based row index

isRowHeightCustom

public boolean isRowHeightCustom(int row)

Returns whether the row has its own height instead of following the sheet default. The height is the row's own after setRowHeight(int, double), or when the source file declared <row ht>. Excel keeps such a height when it opens the file. A row without one is auto-fitted to its font on open, even when the two values are equal.

The method also returns true when the row has a record whose height differs from the sheet default. A row record that setRowHidden(int, boolean) or setRowStyle(int, io.keikai.axyra.sheets.style.CellStyle) created holds a height of 15 points, so on a sheet whose default is not 15 such a row reports true.

Parameters:

row - 0-based row index

setColumnWidth

public void setColumnWidth(int col,
 double width)

Sets a column's width in Excel character units: the number of '0' digits of the workbook's default font that fit in the column. Marks the width as the column's own. To fit the width to the content, use autoFitColumn(int).

Parameters:

col - 0-based column index; must be below 16,384 (column XFD), and is not checked

width - width in character units

columnWidth

public double columnWidth(int col)

Returns a column's width in character units. A column with no width of its own returns the sheet's effective default (see defaultColumnWidth()).

Parameters:

col - 0-based column index

defaultRowHeight

public double defaultRowHeight()

Returns the sheet's default row height in points: the height of every row without a height of its own. Returns 15 for a new sheet, and 0 when a loaded sheet declares no default; Excel then derives the height from the default font.

setDefaultRowHeight

public void setDefaultRowHeight(double points)

Sets the default row height in points. Rows with their own height keep it.

Parameters:

points - the height; 0 clears the declaration, so the saved sheet carries no default

isDefaultRowHeightCustom

public boolean isDefaultRowHeightCustom()

Returns whether the default row height is flagged as fixed (the sheetFormatPr customHeight flag). Without the flag, Excel may replace the default with one derived from the default font.

setDefaultRowHeightCustom

public void setDefaultRowHeightCustom(boolean custom)

Sets the sheetFormatPr customHeight flag; see isDefaultRowHeightCustom().

defaultColumnWidth

public double defaultColumnWidth()

Returns the sheet's declared default column width in character units, or 0 when none is declared; column widths then derive from baseColumnWidth(). A new sheet reports Excel's stock 8.43. columnWidth(int) on a column with no width of its own returns the effective width either way.

setDefaultColumnWidth

public void setDefaultColumnWidth(double width)

Sets the default column width in character units. Columns with their own width keep it.

Parameters:

width - the width; 0 clears the declaration, and widths then derive from baseColumnWidth()

baseColumnWidth

public int baseColumnWidth()

Returns the declared baseColWidth in characters, or 0 when the sheet declares none; the spec default of 8 then applies. Excel derives the default column width from it when no default width is declared.

setBaseColumnWidth

public void setBaseColumnWidth(int width)

Sets the baseColWidth in characters.

Parameters:

width - the base width; 0 clears the declaration

formattedColumns

public int[] formattedColumns()

Returns the 0-based indexes, ascending, of the columns that carry a column-level record: a width, a hidden flag, a column style or an outline level. A new sheet returns an empty array. A column keeps its record after a setter restores the default value, for example setColumnHidden(c, false) or setColumnStyle(c, null), so such a column is still listed.

Returns:

a new array; later edits do not change it

formattedRows

public int[] formattedRows()

Returns the 0-based indexes, ascending, of the rows that carry a row-level record: a height, a hidden flag, a row style or an outline level. This is the row counterpart of formattedColumns(). A row with a set height and no cells, such as a top spacer row, is listed even though usedRange() does not cover it. A row keeps its record after a setter restores the default value, for example setRowHidden(r, false), so such a row is still listed.

Returns:

a new array; later edits do not change it

isColumnWidthCustom

public boolean isColumnWidthCustom(int col)

Returns whether the column has its own width, set by setColumnWidth(int, double) or by <col width> in the source file, instead of following the sheet default.

Parameters:

col - 0-based column index

columnStyleId

public int columnStyleId(int col)

Returns the column-level style id, or 0 when the column has no column-level style. The id is the workbook's internal index of a shared style, the same kind Range.styleIds() returns; it is valid only within this workbook. Use columnStyle(int) for the style itself.

Parameters:

col - 0-based column index

columnStyle

public CellStyle columnStyle(int col)

Returns the column-level style: the style that cells in this column without their own style render with (<col style>).

Parameters:

col - 0-based column index

Returns:

a snapshot of the style, or null when the column has no column-level style

setColumnStyle

public void setColumnStyle(int col,
 CellStyle style)

Sets the column-level style: the style that cells in this column without their own style render with (<col style>). Cells that already have a style keep it. Excel's "format whole column" changes those cells too; to match it, also apply the style to the used cells with Range.setStyle(io.keikai.axyra.sheets.style.CellStyle). Each distinct style is stored once and shared.

Parameters:

col - 0-based column index

style - the style, or null to remove the column-level style

rowStyleId

public int rowStyleId(int row)

Returns the row-level style id (<row s="N" customFormat="1">), or 0 when the row has no row-level style. The row counterpart of columnStyleId(int); use rowStyle(int) for the style itself.

Parameters:

row - 0-based row index

rowStyle

public CellStyle rowStyle(int row)

Returns the row-level style: the style that cells in this row without their own style render with.

Parameters:

row - 0-based row index

Returns:

a snapshot of the style, or null when the row has no row-level style

setRowStyle

public void setRowStyle(int row,
 CellStyle style)

Sets the row-level style: the style that cells in this row without their own style render with. Cells that already have a style keep it. Excel's "format whole row" changes those cells too; to match it, also apply the style to the used cells with Range.setStyle(io.keikai.axyra.sheets.style.CellStyle).

Parameters:

row - 0-based row index

style - the style, or null to remove the row-level style

showGridlines

public boolean showGridlines()

Returns whether the on-screen view draws gridlines (<sheetView showGridLines>, ECMA-376 §18.3.1.87). This is not printGridlines(), which controls printing.

setShowGridlines

public void setShowGridlines(boolean on)

Shows or hides gridlines in the on-screen view; printing is set by setPrintGridlines(boolean).

showRowColHeaders

public boolean showRowColHeaders()

Returns whether the on-screen view shows the row numbers and column letters (showRowColHeaders).

setShowRowColHeaders

public void setShowRowColHeaders(boolean on)

Shows or hides the row numbers and column letters in the on-screen view; printing is set by setPrintHeadings(boolean).

showZeros

public boolean showZeros()

Returns whether a cell holding zero shows 0 (true) or shows blank (<sheetView showZeros>, ECMA-376 §18.3.1.87). A sheet that never declared the attribute reports true, its default.

setShowZeros

public void setShowZeros(boolean on)

Sets whether cells holding zero show 0 (true) or blank; the values do not change.

showFormulas

public boolean showFormulas()

Returns whether the grid shows formula text instead of results (<sheetView showFormulas>). It is a display mode only: the stored values do not change, and nothing recalculates when it changes.

setShowFormulas

public void setShowFormulas(boolean on)

Turns the show-formulas display mode on or off; see showFormulas().

viewType

public String viewType()

Returns the view the tab opens in (<sheetView view>, ECMA-376 §18.3.1.87): "normal", "pageBreakPreview" or "pageLayout". A sheet with no such attribute reports "normal", its default.

setViewType

public void setViewType(String view)

Sets the view the tab opens in. "normal" clears the attribute rather than writing it, so a sheet that never had one does not gain a view="normal" it did not carry before. The value is not checked: anything other than "normal", "pageBreakPreview" or "pageLayout" is written as given and makes the file invalid.

Parameters:

view - the view token; not null

Throws:

AxyraException - if view is null

summaryBelow

public boolean summaryBelow()

Returns whether a row group's summary row sits below its detail rows (<outlinePr summaryBelow>, ECMA-376 §18.3.1.61). true is the default. Grouping reads the flag when it places a summary, so changing it changes where later groups collapse to.

setSummaryBelow

public void setSummaryBelow(boolean below)

Sets whether row-group summaries sit below their detail rows.

summaryRight

public boolean summaryRight()

Returns whether a column group's summary column sits right of its detail columns (<outlinePr summaryRight>). true is the default.

setSummaryRight

public void setSummaryRight(boolean right)

Sets whether column-group summaries sit right of their detail columns.

activeCell

public String activeCell()

Returns the view's active cell as an A1 reference, such as "B3" (<sheetView><selection activeCell>, ECMA-376 §18.3.1.78), or null when the sheet has no selection.

selectionSqref

public String selectionSqref()

Returns the view's selected ranges (<selection sqref>) as a space-separated list of A1 ranges, such as "A1:B2 D4", or null when nothing is selected.

setActiveCell

public void setActiveCell(String cell,
 String sqref)

Moves the cursor and the selection. sqref may name several space-separated ranges; null selects the active cell alone.

Any per-pane selection list read from the source file is dropped, because it records the old cursor. Keeping it would make the saved file reopen at the old position and discard this call.

Parameters:

cell - the active cell in A1 form, such as "B3"; not null, and not checked

sqref - space-separated A1 ranges to select, or null to select only the active cell; not checked

Throws:

AxyraException - if cell is null

sourceSheetId

public int sourceSheetId()

Returns the workbook-level id this sheet was read with (<sheet sheetId> in xl/workbook.xml), or 0 for a sheet created through this API; the writer allocates an id for those on save.

Pivot caches, defined names and external links address a sheet by this id rather than by tab position, so it is not index().

setSourceSheetId

public boolean setSourceSheetId(int id)

Renumbers the sheet's <sheet sheetId>.

Returns false, and changes nothing, for 0 and for an id another sheet already holds. The writer reads 0 as "unset" and replaces it with an allocated id. Sheet ids are unique within a workbook, and a duplicate makes the package invalid.

Parameters:

id - the new id, 1 or greater

Returns:

true if the id was changed

scrollRow

public int scrollRow()

Returns the 0-based first visible row of an unfrozen view: the row part of <sheetView topLeftCell> (ECMA-376 Part 1 §18.3.1.87). Returns 0 when the view is not scrolled.

On a sheet with frozen panes, the same attribute anchors the frozen corner and the scroll position is not saved. A frozen sheet read from a file reports 0. A scroll position set before freezePanes(int, int) is still reported but is not written on save. For a frozen sheet, read freezeRow() and freezeColumn().

scrollColumn

public int scrollColumn()

Returns the 0-based first visible column of an unfrozen view; see scrollRow().

setScrollPosition

public boolean setScrollPosition(int row,
 int column)

Scrolls an unfrozen view so that (row, column) sits at the top left.

Returns false, and changes nothing, for a sheet with frozen panes: there the attribute belongs to the frozen corner, so writing a scroll position into it would move the freeze rather than the window.

Parameters:

row - 0-based first visible row

column - 0-based first visible column

Returns:

true if the position was set

hasPageSetup

public boolean hasPageSetup()

Returns whether the sheet carries a <pageSetup> element (ECMA-376 §18.3.1.63). Its presence changes Excel's print layout both ways: an extra element switches the default paper to Letter, and a missing one shifts the printed body up. Any page-setup setter, such as orientation, scale, fit-to-page or paper size, makes the element present.

dyDescent

public double dyDescent()

Returns the sheet-level x14ac:dyDescent text-descent hint on <sheetFormatPr>, or 0 when absent. Excel stores the default font's descent here and uses it to size rows without a height of their own. A copy of the sheet without the hint can fit a different number of rows per printed page, which moves every page break of a long table.

setDyDescent

public void setDyDescent(double dy)

Sets the sheet-level x14ac:dyDescent hint; see dyDescent().

Parameters:

dy - the descent hint; 0 removes it

setRowHidden

public void setRowHidden(int row,
 boolean hidden)

Hides or shows a row. When the hidden state changes, formulas that depend on the row's cells, such as SUBTOTAL(109, ...), are recalculated.

Parameters:

row - 0-based row index

isRowHidden

public boolean isRowHidden(int row)

Returns whether the row's hidden flag is set, by setRowHidden(int, boolean), by a filter or a collapsed group, or by the source file.

Parameters:

row - 0-based row index

setColumnHidden

public void setColumnHidden(int col,
 boolean hidden)

Hides or shows a column. Formulas are not recalculated: Excel's SUBTOTAL ignores hidden columns.

Parameters:

col - 0-based column index; must be below 16,384

isColumnHidden

public boolean isColumnHidden(int col)

Returns whether the column is hidden.

Parameters:

col - 0-based column index

freezePanes

public void freezePanes(int freezeRow,
 int freezeCol)

Freezes the top freezeRow rows and the left freezeCol columns, as Excel's View > Freeze Panes does with the cell at (freezeRow, freezeCol) selected. Pass 0 on an axis to leave it unfrozen; freezePanes(0, 0) is unfreeze(). On save, a freeze replaces any split from splitPanes(int, int).

Parameters:

freezeRow - number of rows to freeze at the top

freezeCol - number of columns to freeze at the left

unfreeze

public void unfreeze()

Removes any frozen panes; the same as freezePanes(0, 0).

freezeRow

public int freezeRow()

Returns the number of frozen rows at the top, or 0 when no rows are frozen.

freezeColumn

public int freezeColumn()

Returns the number of frozen columns at the left, or 0 when no columns are frozen.

splitPanes

public void splitPanes(int splitRow,
 int splitColumn)

Splits the view into panes that scroll on their own, as Excel's View > Split does. Unlike a freeze, every pane scrolls.

The split sits on the grid line above 0-based row splitRow and left of 0-based column splitColumn. On save it is written as a distance in twentieths of a point, measured from the current row heights and column widths. When frozen panes are also set, the freeze is saved and the split is dropped.

Parameters:

splitRow - 0-based row below the split, or 0 for no horizontal split

splitColumn - 0-based column right of the split, or 0 for no vertical split

splitRow

public int splitRow()

Returns the 0-based row just below the horizontal split, or 0 when there is none.

splitColumn

public int splitColumn()

Returns the 0-based column just right of the vertical split, or 0 when there is none.

addRowPageBreak

public void addRowPageBreak(int row)

Inserts a manual horizontal page break above the 0-based row. It is saved as the sheet's <rowBreaks> and paginates PDF and image output. Row 0 and rows past the grid are ignored.

addColumnPageBreak

public void addColumnPageBreak(int column)

Inserts a manual vertical page break left of the 0-based column. It is saved as <colBreaks>. Column 0 and columns past the grid are ignored.

rowPageBreaks

public int[] rowPageBreaks()

Returns the 0-based rows a horizontal page break sits above, ascending: the breaks added here and those read from an opened file, automatic ones included.

Returns:

a new array; later edits do not change it

columnPageBreaks

public int[] columnPageBreaks()

Returns the 0-based columns a vertical page break sits left of, ascending, as rowPageBreaks() does for rows.

Returns:

a new array; later edits do not change it

clearPageBreaks

public void clearPageBreaks()

Removes every page break on this sheet: the manual ones and the automatic ones kept from the source file.

mergedRegions

public List<Range> mergedRegions()

Returns the merged-cell regions on this sheet, one Range per region. Merge or unmerge with Range.merge() and Range.unmerge().

Returns:

a new list; later merges do not change it

usedRange

public Range usedRange()

Returns the smallest range that covers every stored cell, or null when the sheet has no cells. Rows and columns that only carry a height, width or style are not covered; see formattedRows() and formattedColumns().

find

public int[][] find(String text)

Finds every text cell whose text contains text, ignoring case, across the whole sheet. Only text values are searched: a number such as 42 is not found by "42". A formula cell matches on its cached text result, not on its formula.

Parameters:

text - the text to look for

Returns:

{row, col} pairs, 0-based, in row-major order; an empty array when nothing matches

find

public int[][] find(String text,
 boolean caseSensitive,
 boolean matchEntire,
 boolean wildcard)

Finds text cells as find(String) does, with Excel's Find options.

Parameters:

text - the text to look for

caseSensitive - match letter case exactly

matchEntire - the whole cell text must match, not a substring

wildcard - treat *, ? and ~ as Excel wildcards

Returns:

{row, col} pairs, 0-based, in row-major order

findRegex

public int[][] findRegex(String pattern)

Finds every text cell whose text matches the regular expression pattern, ignoring case, across the whole sheet. Only text values are searched: numbers, booleans and errors never match. A formula cell matches on its cached text result. The pattern uses the Rust regex crate syntax; a (?-i) group turns case-insensitive matching off for its part.

Parameters:

pattern - the regular expression

Returns:

{row, col} pairs, 0-based, in row-major order; an empty array when nothing matches

Throws:

AxyraException - if pattern is not a valid regular expression

findRegex

public int[][] findRegex(String pattern,
 boolean caseSensitive)

Finds text cells as findRegex(String) does, with a choice of case sensitivity.

Parameters:

pattern - the regular expression

caseSensitive - match letter case exactly

Returns:

{row, col} pairs, 0-based, in row-major order

Throws:

AxyraException - if pattern is not a valid regular expression

find

public int[][] find(String text,
 FindFormat format)

Finds cells as find(String) does, keeping only the cells whose resolved style matches format: Excel's Find with Format. See FindFormat for how styles are matched. An empty text with a non-empty format finds by format only: it returns every cell that carries the format, whatever its value type.

Parameters:

text - the text to look for; empty to find by format only

format - the style to match; not null

Returns:

{row, col} pairs, 0-based, in row-major order

find

public int[][] find(String text,
 boolean caseSensitive,
 boolean matchEntire,
 boolean wildcard,
 FindFormat format)

Finds cells by text and format, as find(String, FindFormat) does, with Excel's Find options.

Parameters:

caseSensitive - match letter case exactly

matchEntire - the whole cell text must match, not a substring

wildcard - treat *, ? and ~ as Excel wildcards

format - the style to match; not null

Returns:

{row, col} pairs, 0-based, in row-major order

replaceAll

public int replaceAll(String find,
 String replace)

Replaces find with replace in every text cell of the sheet that contains it, ignoring case. Formula cells are skipped, even when their result matches. A changed cell keeps its style and holds the new text as text: a result that looks like a number is not converted. Formulas that read the changed cells see the new values.

Returns:

the number of cells changed

replaceAll

public int replaceAll(String find,
 String replace,
 boolean caseSensitive,
 boolean matchEntire,
 boolean wildcard)

Replaces text as replaceAll(String, String) does, with Excel's Replace options.

Parameters:

caseSensitive - match letter case exactly

matchEntire - replace only in cells whose whole text matches

wildcard - treat *, ? and ~ in find as Excel wildcards

Returns:

the number of cells changed

replaceAllRegex

public int replaceAllRegex(String pattern,
 String replacement)

Replaces every match of the regular expression pattern, ignoring case, in every text cell of the sheet. replacement may refer to capture groups, as "$1" or "${name}". Formula cells are skipped. The pattern uses the Rust regex crate syntax. Changed cells keep their style, as with replaceAll(String, String).

Returns:

the number of cells changed

Throws:

AxyraException - if pattern is not a valid regular expression

replaceAllRegex

public int replaceAllRegex(String pattern,
 String replacement,
 boolean caseSensitive)

Replaces regular-expression matches as replaceAllRegex(String, String) does, with a choice of case sensitivity.

Parameters:

caseSensitive - match letter case exactly

Returns:

the number of cells changed

Throws:

AxyraException - if pattern is not a valid regular expression

replaceAll

public int replaceAll(String find,
 String replace,
 FindFormat format)

Replaces text as replaceAll(String, String) does, only in the cells whose resolved style matches format. See FindFormat for how styles are matched. An empty find text replaces nothing.

Parameters:

format - the style to match; not null

Returns:

the number of cells changed

replaceAll

public int replaceAll(String find,
 String replace,
 boolean caseSensitive,
 boolean matchEntire,
 boolean wildcard,
 FindFormat format)

Replaces text by format, as replaceAll(String, String, FindFormat) does, with Excel's Replace options.

Parameters:

caseSensitive - match letter case exactly

matchEntire - replace only in cells whose whole text matches

wildcard - treat *, ? and ~ in find as Excel wildcards

format - the style to match; not null

Returns:

the number of cells changed

textToColumns

public int textToColumns(String srcRange,
 String delimiters)

Splits text in place, as Excel's Text to Columns does in delimited mode. Each text cell of the one-column srcRange, such as "A1:A10", is split on the delimiters characters. The fields fill the cells to the right, starting in the source column, and overwrite what is there without a prompt.

Number and boolean source cells are copied to the destination unchanged. Blank cells are skipped. A field that parses as a number is written as a number. An empty field clears its destination cell. Text inside double quotes is not split.

Parameters:

srcRange - one-column source range in A1 notation

delimiters - the split characters, such as "," or ";\t": tab, semicolon, comma and space, plus at most one other character

Returns:

the number of output columns of the widest row

Throws:

IllegalArgumentException - if srcRange is more than one column wide or cannot be parsed

AxyraException - if delimiters names no delimiter, or more than one character besides tab, semicolon, comma and space

textToColumns

public int textToColumns(String srcRange,
 String delimiters,
 boolean treatConsecutiveAsOne)

Splits text in place as textToColumns(String, String) does, optionally treating a run of delimiters as one split point: Excel's "Treat consecutive delimiters as one", useful for space-aligned text.

Parameters:

treatConsecutiveAsOne - true to merge runs of delimiters

Returns:

the number of output columns of the widest row

textToColumns

public int textToColumns(String srcRange,
 int destRow,
 int destColumn,
 String delimiters,
 boolean treatConsecutiveAsOne)

Splits text as textToColumns(String, String) does, writing the fields to another place: the fields of source row i go to row destRow + i, starting at destColumn. The source range must be exactly one column wide, as in Excel.

Parameters:

destRow - 0-based row that receives source row 0

destColumn - 0-based first output column

treatConsecutiveAsOne - true to merge runs of delimiters

Returns:

the number of output columns of the widest row

Throws:

IllegalArgumentException - if srcRange is more than one column wide or cannot be parsed

AxyraException - if delimiters names no delimiter, or more than one character besides tab, semicolon, comma and space

validations

public List<Validation> validations()

Returns every data-validation rule on this sheet, in stored order. Add a rule with Range.setValidation(io.keikai.axyra.sheets.content.Validation).

Returns:

a new list of snapshots; later edits do not change it

clearValidations

public void clearValidations()

Removes every data-validation rule from this sheet; the cell values stay.

removeValidation

public boolean removeValidation(int validationIndex)

Removes the data-validation rule at validationIndex, its 0-based position in validations(). Later rules move down one place.

Returns:

true if a rule was removed; false when the index is negative or at or past the rule count

conditionalFormats

public List<ConditionalFormat> conditionalFormats()

Returns every conditional-formatting rule on this sheet, in stored order. Add a rule with Range.addConditionalFormat(io.keikai.axyra.sheets.content.ConditionalFormat).

Returns:

a new list of snapshots; later edits do not change it

clearConditionalFormats

public void clearConditionalFormats()

Removes every conditional-formatting rule from this sheet.

removeConditionalFormat

public boolean removeConditionalFormat(int formatIndex)

Removes the conditional-formatting rule at formatIndex, its 0-based position in conditionalFormats(). Later rules move down one place.

Returns:

true if a rule was removed; false when the index is negative or at or past the rule count

tables

public List<Table> tables()

Returns every table on this sheet (Excel's ListObjects), with its range, columns, style and totals state. Create one with Range.createTable(String, String...).

Returns:

a new list of snapshots; later edits do not change it

removeTable

public boolean removeTable(String name)

Removes the table definition named name and leaves its cells, values and styles in place. It is the same operation as convertTableToRange(java.lang.String). Formulas that use structured references to the table, such as Sales[Amount], are not rewritten and stop resolving. The name match is case-sensitive.

Returns:

true if a table was removed

convertTableToRange

public boolean convertTableToRange(String name)

Converts the named table back to an ordinary range, as Excel's Table Design > Convert to Range does: drops the table definition and keeps every cell value and style in place. This is the inverse of Range.createTable(String, String...), and the same operation as removeTable(java.lang.String).

Unlike Excel, formulas that use structured references to the table are not rewritten to A1 references, so they stop resolving. The name match is case-sensitive.

Returns:

true if a table was converted

resizeTable

public boolean resizeTable(String name,
 Range newRange)

Resizes the named table to newRange, as Excel's Table Design > Resize Table does. The header row must stay where it is, and at least one data row must remain. Columns are matched by sheet position: a kept position keeps its column definition, totals and formulas included. A new position takes its header cell's text as its name, or a generated unique ColumnN name when that cell is empty.

Parameters:

newRange - the new table area on this sheet, header row included

Returns:

true if the table was resized; false when no table has that name or the new range breaks a rule above

autoExpandTables

public boolean autoExpandTables(int row,
 int col,
 String typedText)

Applies Excel's table auto-expansion for an entry typed at (row, col). Typing one row below a table's last data row, inside its column span, grows the table by one row when it has no totals row. Typing in the header row one column right of its last column grows the table by one column named typedText, unless another table occupies that column. Only the table definition changes; write the cell value separately.

Parameters:

row - 0-based row of the entry

col - 0-based column of the entry

typedText - the typed text, used as a new column's name; null acts as an empty string

Returns:

true if any table expanded

smartArtCount

public int smartArtCount()

Returns the number of SmartArt diagrams anchored on this sheet.

smartArts

public List<SmartArt> smartArts()

Returns the SmartArt diagrams anchored on this sheet as read-only descriptors: layout family, anchor and visible node texts. Diagrams read from a file render in PDF and image output and are saved back unchanged. The API has no method that creates or edits a diagram.

Returns:

a new list; empty when the sheet has none

setTableStyle

public boolean setTableStyle(String tableName,
 TableStyleInfo style)

Replaces the style settings of the named table (ECMA-376 §18.5.1.20 tableStyleInfo): the style name and the banding and first/last-column flags. The table part is regenerated on the next save.

Parameters:

style - the new settings; not null

Returns:

true if a table with that name exists

setTableTotalsRow

public boolean setTableTotalsRow(String tableName,
 boolean shown)

Shows or hides the named table's totals row (ECMA-376 §18.5.1.2 totalsRowCount). Showing extends the table range one row down and writes the configured totals cells, SUBTOTAL formulas and labels, into that row, as Excel does. What was in that row is overwritten. Hiding shrinks the range back and empties the cells of the old totals row. Set the per-column totals with setTableColumnTotals(java.lang.String, java.lang.String, io.keikai.axyra.sheets.TotalsRowFunction).

Returns:

true if a table with that name exists

setTableHeaderRow

public boolean setTableHeaderRow(String tableName,
 boolean shown)

Shows or hides the named table's header row (ECMA-376 §18.5.1.2 headerRowCount). Unlike the totals row, this does not resize the table range: the first row of the range is read as the header (shown) or as the first data row (hidden). Range.createTable(java.lang.String, java.lang.String...) always makes a table with a header row. To rebuild a table that has none, pass false here; otherwise Excel treats the first data row as the header and may refuse to open the file.

Returns:

true if a table with that name exists

setTableColumnTotals

public boolean setTableColumnTotals(String tableName,
 String columnName,
 TotalsRowFunction function)

Sets the totals-row function of one column of the named table (ECMA-376 §18.18.92 ST_TotalsRowFunction). A built-in function becomes a SUBTOTAL(101..110, Table[Column]) cell when the totals row is shown; when it is shown already, the cell is rewritten at once. Use setTableColumnTotals(String, String, TotalsRowFunction, String, String) for CUSTOM, which needs a formula, or for a label. Table and column names match case-sensitively.

Parameters:

function - the function; null removes the column's total

Returns:

true if the table and column exist; false for CUSTOM, which needs the five-argument form

setTableColumnTotals

public boolean setTableColumnTotals(String tableName,
 String columnName,
 TotalsRowFunction function,
 String label,
 String formula)

Sets the totals-row function, label and custom formula of one column of the named table. When the totals row is shown, its cell for this column is rewritten at once: the function's formula, else the label. Table and column names match case-sensitively.

Parameters:

function - the function, or null for a label only

label - the text for totalsRowLabel, or null

formula - the totals formula; required for TotalsRowFunction.CUSTOM and ignored otherwise

Returns:

true if the totals were set; false when the table or column does not exist, or when function is CUSTOM and formula is null

addChart

public int addChart(Chart chart)

Adds a chart to this sheet. Build the spec with Chart.of(java.lang.String), or from an existing chart with Chart.toBuilder(). The chart's drawing part is generated on save.

Returns:

the new chart's 0-based index in charts()

addPivotChart

public int addPivotChart(int pivotIndex,
 String chartType,
 int fromRow,
 int fromCol,
 int toRow,
 int toCol)

Add a PivotChart bound to the pivot table at pivotIndex on this sheet, anchored over the given cell rectangle. The pivot must have been refreshed first so its output range is known; the chart's series are derived from that output (one series per data column, categories from the row-label column, grand totals excluded) and the chart carries Excel's pivotSource binding (ECMA-376 §21.2.2.153) so it stays linked to the pivot on open.

Parameters:

pivotIndex - the pivot table's 0-based index on this sheet

chartType - a chart type token accepted by Chart.of(java.lang.String), such as "column", "bar" or "pie"; an unknown token gives a column chart

fromRow - 0-based top row of the chart anchor

fromCol - 0-based left column of the chart anchor

toRow - 0-based bottom row of the chart anchor

toCol - 0-based right column of the chart anchor

Returns:

the new chart's 0-based index on this sheet, or -1 when the pivot does not exist (a negative index included), has not been refreshed, or its output has no value column or no body row

charts

public List<Chart> charts()

Returns every chart on this sheet, in drawing order. Edit one with chart(int) or replaceChart(int, io.keikai.axyra.sheets.content.Chart).

Returns:

a new list of snapshots; later edits do not change it

chart

public ChartView chart(int chartIndex)

Returns a live view of the chart at chartIndex, its 0-based position in charts(). Getters read the engine state captured when the view was made or last reloaded; each setter writes to the engine at once.

Parameters:

chartIndex - 0-based chart index

Throws:

AxyraException - if chartIndex is negative or at or past the chart count

replaceChart

public void replaceChart(int chartIndex,
 Chart chart)

Apply chart's spec onto the chart already at chartIndex, in place, keeping its position in charts().

The engine overlays the spec rather than swapping the chart out, so detail the spec does not carry survives — per-series trendlines, error bars and data labels when the matching series is silent about them, plus the parts of an imported chart the spec has no field for (3-D view angles, manual layouts, an Excel-2016 extended chart's preserved apparatus, a PivotChart binding). Marks the sheet's drawing dirty, so the saved package carries the edit.

This is the read–modify–write counterpart of addChart(io.keikai.axyra.sheets.content.Chart): read with charts(), edit through Chart.toBuilder(), write back here. Editing one field at a time through chart(int) does the same thing one round trip at a time.

Parameters:

chartIndex - 0-based index in charts()

chart - the spec to apply

Throws:

AxyraException - if chartIndex is negative or at or past the chart count

clearCharts

public void clearCharts()

Removes every chart from this sheet.

removeChart

public boolean removeChart(int chartIndex)

Removes the chart at chartIndex, its 0-based position in charts(). Later charts move down one place.

Returns:

true if a chart was removed; false when the index is negative or at or past the chart count

addCheckbox

public int addCheckbox(String name,
 int fromRow,
 int fromColumn,
 int toRow,
 int toColumn,
 String linkedCell)

Adds a check box form control over a cell rectangle, optionally bound to a linked cell that holds its state. A shortcut for addFormControl(FormControl) with a FormControl.checkBox(String) spec.

Parameters:

name - the check box caption

fromRow - 0-based top row of the anchor

fromColumn - 0-based left column of the anchor

toRow - 0-based bottom row of the anchor

toColumn - 0-based right column of the anchor

linkedCell - A1 reference of the cell that receives TRUE or FALSE, or null for none

Returns:

the new control's 0-based index in formControls()

formControlCount

public int formControlCount()

Returns the number of form controls on this sheet, the size of formControls(). Cheaper than reading the full list.

addFormControl

public int addFormControl(FormControl control)

Adds a form control: check box, option button, button, combo box, list box, scroll bar, spin button, edit box, label or group box. The control's parts (control properties, legacy drawing shape and worksheet registration) are generated on save.

Returns:

the new control's 0-based index in formControls()

Throws:

AxyraException - if the control spec cannot be encoded

formControls

public List<FormControl> formControls()

Returns every form control on this sheet, in z-order.

Returns:

a new list of snapshots; later edits do not change it

updateFormControl

public boolean updateFormControl(int controlIndex,
 FormControl control)

Replaces the form control at controlIndex, its 0-based position in formControls(). The replacement is complete: read a control, change it through FormControl.toBuilder(), and write it back.

Parameters:

controlIndex - 0-based control index

Returns:

true if a control was replaced; false when controlIndex is negative or at or past the control count

Throws:

AxyraException - if the control spec cannot be encoded

removeFormControl

public boolean removeFormControl(int controlIndex)

Removes the form control at controlIndex, its 0-based position in formControls(). Later controls move down one place.

Returns:

true if a control was removed; false when the index is negative or at or past the control count

addPivotTable

public int addPivotTable(String name,
 String location,
 String sourceRef)

Creates an empty pivot table whose top-left cell is location (A1, such as "H1") over the source range sourceRef, such as "Data!A1:D100". Add fields through pivotTable(int). Its output is built by PivotTableView.refresh(). A pivot created or changed through the API and not refreshed since is also refreshed automatically before the workbook is saved or rendered.

The name, location and source are not checked here; a bad source reference fails when the pivot is refreshed.

Returns:

the new pivot's 0-based index, for pivotTable(int)

pivotCount

public int pivotCount()

Returns the number of pivot tables on this sheet. Valid pivot indexes are 0 to pivotCount() - 1.

pivotName

public String pivotName(int pivotIndex)

Returns the name of the pivot table at pivotIndex.

Parameters:

pivotIndex - 0-based pivot index

Returns:

the name, or null when pivotIndex is negative or at or past pivotCount()

pivotLocation

public String pivotLocation(int pivotIndex)

Returns the location of the pivot table at pivotIndex as an A1 reference, such as "H1".

Parameters:

pivotIndex - 0-based pivot index

Returns:

the location, or null when the index is out of range

pivotSource

public String pivotSource(int pivotIndex)

Returns the source data reference of the pivot table at pivotIndex, such as "Data!A1:D100".

Parameters:

pivotIndex - 0-based pivot index

Returns:

the reference, or null when the index is out of range

pivotTable

public PivotTableView pivotTable(int pivotIndex)

Returns a live view of the pivot table at pivotIndex: field areas (row, column, filter, values), aggregation functions, layout, grand totals and PivotTableView.refresh().

Parameters:

pivotIndex - 0-based pivot index

Throws:

AxyraException - if pivotIndex is negative or at or past pivotCount()

pivotTable

public PivotTableView pivotTable(String name)

Returns a live view of the pivot table named name. An exact match wins; otherwise the name matches case-insensitively.

Throws:

AxyraException - if no pivot table on this sheet has that name

addScenario

public int addScenario(String name,
 String[] cellRefs,
 String[] values)

Adds a what-if scenario to this sheet. cellRefs[i], an A1 reference on this sheet such as "B2", gets values[i] when the scenario is shown; extra entries in the longer array are ignored. A scenario with the same name as an existing one is added as a second scenario. The sheet's cells do not change until showScenario(int).

Parameters:

name - the scenario name

cellRefs - the changing cells, as A1 references on this sheet

values - the value for each changing cell, as text

Returns:

the new scenario's 0-based index

scenarioCount

public int scenarioCount()

Returns the number of what-if scenarios on this sheet.

scenarioName

public String scenarioName(int scenarioIndex)

Returns the name of the scenario at scenarioIndex.

Parameters:

scenarioIndex - 0-based scenario index

Returns:

the name, or null when scenarioIndex is negative or at or past scenarioCount()

showScenario

public boolean showScenario(int scenarioIndex)

Applies the scenario at scenarioIndex: writes each of its changing-cell values into the sheet. A value that parses as a number is written as a number; any other value is written as text, never as a formula. Formulas that read those cells recalculate as for any cell write.

Parameters:

scenarioIndex - 0-based scenario index

Returns:

true if the scenario was applied; false when scenarioIndex is negative or at or past scenarioCount()

addPicture

public int addPicture(byte[] imageData,
 String format,
 int fromRow,
 int fromColumn,
 int toRow,
 int toColumn)

Inserts an embedded image over the cell rectangle from (fromRow, fromColumn) to (toRow, toColumn). All indexes are 0-based. The picture's bottom-right corner sits on the top-left edge of (toRow, toColumn), so that cell is outside the picture, as in a DrawingML two-cell anchor. Use the 10-argument overload for sub-cell offsets.

Parameters:

imageData - the encoded image bytes

format - png, jpeg (or jpg), gif, bmp, tiff, emf, wmf, svg or webp; any other token is stored as an unknown format, and pictureFormat(int) reports it as "png"

Returns:

the new picture's 0-based index

addPicture

public int addPicture(byte[] imageData,
 String format,
 int fromRow,
 int fromColumn,
 int fromRowOffset,
 int fromColumnOffset,
 int toRow,
 int toColumn,
 int toRowOffset,
 int toColumnOffset)

Inserts an embedded image with a full two-cell anchor, offsets included. Cell indexes are 0-based. Offsets are in EMU (914,400 per inch, 12,700 per point), measured from the top-left corner of the given cell. They carry a sub-cell position and size that a plain cell rectangle cannot express. An image anchored to one cell uses toRow == fromRow and toColumn == fromColumn and carries its size in the to*Offset values. Read the eight values back with pictureAnchorFull(int).

Parameters:

format - the image format, as for addPicture(byte[], String, int, int, int, int)

Returns:

the new picture's 0-based index

pictureCount

public int pictureCount()

Returns the number of pictures on this sheet, embedded and linked.

addLinkedPicture

public int addLinkedPicture(String target,
 String format,
 int r1,
 int c1,
 int r2,
 int c2)

Adds a linked picture over the cell rectangle from (r1, c1) to (r2, c2): the image is referenced by an external target, a URL or a path, through <a:blip r:link> instead of embedding bytes (ECMA-376 Part 1 §20.1.8.13). Indexes are 0-based; the cell (r2, c2) is outside the picture, as with addPicture(byte[], java.lang.String, int, int, int, int). Use addPicture(byte[], java.lang.String, int, int, int, int) for the usual embedded case.

Returns:

the new picture's 0-based index, or -1 when the picture could not be added

pictureLinkTarget

public String pictureLinkTarget(int pictureIndex)

Returns the external link target of the picture at pictureIndex, or null when it is an embedded picture or the index is out of range.

setPictureLinkTarget

public boolean setPictureLinkTarget(int pictureIndex,
 String target)

Repoints a linked picture at a new source.

Returns false for an embedded picture: its bytes live in the package, so giving it a link would leave the file carrying both the image and a reference to another one. Use addLinkedPicture(String, String, int, int, int, int) to add a linked picture in the first place.

Returns:

true if the target was changed; false also when target is empty or pictureIndex is out of range

setHeaderFooterPicture

public boolean setHeaderFooterPicture(String section,
 byte[] data,
 String format,
 double widthPt,
 double heightPt)

Place a picture in a header/footer section (ECMA-376 §18.3.1.32 legacyDrawingHF). section is "LH" / "CH" / "RH" for the left/center/right header, or "LF" / "CF" / "RF" for the footer; width/height are display points. The picture prints where the section's text contains the &G field code — set the header/footer string accordingly, e.g. setHeader("&C&G") for a centered header image. An existing picture in the same section is replaced.

section may carry a FIRST or EVEN suffix, for example "CHFIRST", for the first-page or even-page header or footer. Such a picture prints only when the sheet also uses setDifferentFirst(boolean) or setDifferentOddEven(boolean), and the matching first-page or even-page text holds &G.

Parameters:

format - the image format, as for addPicture(byte[], String, int, int, int, int)

Returns:

true if the picture was placed; false when the section token is unknown or data is empty

headerFooterPictureData

public byte[] headerFooterPictureData(String section)

Returns the image bytes of the header or footer picture in section, a token as for setHeaderFooterPicture(java.lang.String, byte[], java.lang.String, double, double), or null when that section has no picture.

removeHeaderFooterPicture

public boolean removeHeaderFooterPicture(String section)

Removes the header or footer picture in section, a token as for setHeaderFooterPicture(java.lang.String, byte[], java.lang.String, double, double). The &G code in the header or footer text stays.

Returns:

true if a picture was removed

anchorOleObject

public boolean anchorOleObject(int objectIndex,
 byte[] iconBytes,
 String iconFormat,
 int fromRow,
 int fromCol,
 int toRow,
 int toCol)

Display the workbook-level embedded OLE object at objectIndex (see Workbook.addOleObject(java.lang.String, java.lang.String, byte[])) as an icon on this sheet, anchored over the cell rectangle (fromRow, fromCol)–(toRow, toCol) (0-based, exclusive bottom-right edge like a drawing anchor). iconBytes is the image shown on the sheet (iconFormat: "png", "jpeg", "gif", "bmp", "tiff", "emf", "wmf"); double-clicking the icon in Excel opens the embedded content.

Returns whether the anchor was added — false when the object index does not exist, the icon bytes are empty, or this sheet already carries OLE anchors imported from an opened XLSX (merging authored icons into an imported legacy drawing is not supported).

pictureData

public byte[] pictureData(int pictureIndex)

Returns the encoded image bytes of the picture at pictureIndex, or null when the index is out of range.

pictureFormat

public String pictureFormat(int pictureIndex)

Returns the format token of the picture at pictureIndex, such as "png" or "jpeg", or null when the index is out of range. An unknown format reads back as "png".

pictureAnchor

public int[] pictureAnchor(int pictureIndex)

Returns {fromRow, fromCol, toRow, toCol} of the picture's two-cell anchor, 0-based, or an empty array when the index is out of range. Offsets are left out, so the image may reach into the to cell; use pictureAnchorFull(int) for an exact copy.

pictureAnchorFull

public int[] pictureAnchorFull(int pictureIndex)

Returns {fromRow, fromCol, fromRowOffset, fromColOffset, toRow, toCol, toRowOffset, toColOffset} of the picture's anchor: 0-based cells and offsets in EMU. Returns an empty array when the index is out of range. Pass these eight values to the 10-argument addPicture(byte[], String, int, int, int, int, int, int, int, int) to place a copy at exactly the same position.

clearPictures

public void clearPictures()

Removes every picture from this sheet, embedded and linked.

removePicture

public boolean removePicture(int pictureIndex)

Removes the picture at pictureIndex, the 0-based index that pictureData(int) uses. Later pictures move down one place.

Returns:

true if a picture was removed; false when the index is negative or at or past pictureCount()

setBackgroundImage

public void setBackgroundImage(byte[] imageData,
 String format)

Sets the sheet background image. Excel tiles the image behind the cells on screen; it is never printed. Replaces any existing background.

Parameters:

imageData - the encoded image bytes

format - the image format, as for addPicture(byte[], String, int, int, int, int); an unknown token is saved as PNG

backgroundImage

public byte[] backgroundImage()

Returns the encoded bytes of the sheet background image, or null when there is none.

backgroundImageFormat

public String backgroundImageFormat()

Returns the format token of the sheet background image, such as "png", or null when there is none.

clearBackgroundImage

public boolean clearBackgroundImage()

Removes the sheet background image.

Returns:

true if an image was removed

setLandscape

public void setLandscape(boolean landscape)

Sets the print orientation: true for landscape, false for portrait.

isLandscape

public boolean isLandscape()

Returns whether the sheet prints in landscape; false means portrait, the default.

setPrintArea

public void setPrintArea(String a1Range)

Sets the print area as an A1 range on this sheet, such as "$A$1:$F$40". The string is stored as given and not checked. A Print_Area name read from the file for this sheet is replaced.

Parameters:

a1Range - the range, or null to clear the print area

printArea

public String printArea()

Returns the print area as an A1 range string, or null when the sheet has none.

setPrintTitleRows

public void setPrintTitleRows(String rowsRange)

Sets the rows repeated at the top of every printed page, such as "$1:$2". The string is stored as given and not checked. A Print_Titles name read from the file for this sheet is replaced.

Parameters:

rowsRange - the row band, or null to clear it

printTitleRows

public String printTitleRows()

Returns the rows repeated at the top of every printed page, such as "$1:$2", or null when none are set.

setPrintTitleColumns

public void setPrintTitleColumns(String columnsRange)

Sets the columns repeated at the left of every printed page, such as "$A:$A". The string is stored as given and not checked.

Parameters:

columnsRange - the column band, or null to clear it

printTitleColumns

public String printTitleColumns()

Returns the columns repeated at the left of every printed page, such as "$A:$A", or null when none are set.

setPrintGridlines

public void setPrintGridlines(boolean on)

Sets whether gridlines are printed. The on-screen gridlines are set by setShowGridlines(boolean).

printGridlines

public boolean printGridlines()

Returns whether gridlines are printed (ECMA-376 §18.3.1.65 printOptions/gridLines).

setPrintHeadings

public void setPrintHeadings(boolean on)

Sets whether row numbers and column letters are printed (ECMA-376 §18.3.1.70 printOptions/headings). It controls the printout only; the on-screen headings are set by setShowRowColHeaders(boolean).

setPageOrderOverThenDown

public void setPageOrderOverThenDown(boolean overThenDown)

Sets the page order: true prints across, then down ("Over, then down"); false prints down, then across, Excel's default (ECMA-376 §18.3.1.63 pageOrder).

pageOrderOverThenDown

public boolean pageOrderOverThenDown()

Returns whether pages print across, then down; false means down, then across.

setPrintBlackAndWhite

public void setPrintBlackAndWhite(boolean on)

Sets whether the sheet prints in black and white (ECMA-376 §18.3.1.63 blackAndWhite).

printBlackAndWhite

public boolean printBlackAndWhite()

Returns whether the sheet prints in black and white.

setPrintDraft

public void setPrintDraft(boolean on)

Sets whether the sheet prints in draft quality, without graphics (ECMA-376 §18.3.1.63 draft).

printDraft

public boolean printDraft()

Returns whether the sheet prints in draft quality.

setPrintErrors

public void setPrintErrors(int mode)

Sets how error cells print (ECMA-376 §18.3.1.70 printOptions/errors).

Parameters:

mode - 0 as displayed, 1 blank, 2 as a dash, 3 as #N/A; any other code is treated as 0

printErrors

public int printErrors()

Returns how error cells print, as the code setPrintErrors(int) takes.

setFirstPageNumber

public void setFirstPageNumber(int number)

Sets the number printed on the first page (ECMA-376 §18.3.1.63 firstPageNumber together with useFirstPageNumber).

Parameters:

number - the first page number; 0 restores automatic numbering

firstPageNumber

public int firstPageNumber()

Returns the number printed on the first page, or 0 when numbering is automatic.

setPrintComments

public void setPrintComments(int mode)

Sets where cell comments print (ECMA-376 §18.3.1.63 cellComments).

Parameters:

mode - 0 not at all, 1 as displayed on the sheet, 2 at the end of the printout; any other code is treated as 0

printComments

public int printComments()

Returns where cell comments print, as the code setPrintComments(int) takes.

setPrintResolution

public void setPrintResolution(int dpi)

Sets the printer resolution in dots per inch, for both axes.

Parameters:

dpi - the resolution, such as 600

printResolution

public int printResolution()

Returns the printer resolution in dots per inch.

printHeadings

public boolean printHeadings()

Returns whether row numbers and column letters are printed.

fitToPage

public void fitToPage(int width,
 int height)

Scales the printout to fit width pages wide by height pages tall, and turns on fit-to-page, so pageScale() is ignored. setPageScale(int) turns fit-to-page off again.

Parameters:

width - pages wide; 0 means no limit in that direction, as Excel's blank "pages wide" box, and a negative value is treated as 0

height - pages tall; 0 means no limit, and a negative value is treated as 0

isFitToPage

public boolean isFitToPage()

Returns whether fit-to-page scaling is on, as fitToPage(int, int) sets it. When false, the sheet prints at pageScale(). The flag is <pageSetUpPr fitToPage="1"/> (ECMA-376 §18.3.1.70).

fitToWidth

public int fitToWidth()

Returns the number of pages wide the printout is fitted to, or 0 for no limit (ECMA-376 §18.3.1.63 fitToWidth). It applies only when isFitToPage() is true.

fitToHeight

public int fitToHeight()

Returns the number of pages tall the printout is fitted to, or 0 for no limit (ECMA-376 §18.3.1.63 fitToHeight). It applies only when isFitToPage() is true.

setPageScale

public void setPageScale(int scalePercent)

Sets the print scale in percent and turns fit-to-page off. Excel accepts 10 to 400; this method does not check the value, and a negative value is stored as 0. The on-screen zoom is set by setZoom(int).

Parameters:

scalePercent - the scale in percent, 100 for actual size

pageScale

public int pageScale()

Returns the print scale in percent; 100 means actual size.

setPaperSize

public void setPaperSize(int paperSize)

Sets the paper size from its ECMA-376 numeric code (§18.18.50 ST_PaperSize): 1 = Letter, 5 = Legal, 8 = A3, 9 = A4, 11 = A5, and so on.

Throws:

AxyraException - if paperSize is below 1

paperSize

public int paperSize()

Returns the paper size as its ECMA-376 numeric code; 9 (A4) when none is declared. Check isPaperSizeDeclared() to tell the two apart.

isPaperSizeDeclared

public boolean isPaperSizeDeclared()

Returns whether this sheet declares a paper size. A loaded sheet whose <pageSetup> has no paperSize reports false. Excel then prints it on the printer's default paper, whatever paperSize() reports.

clearPaperSize

public void clearPaperSize()

Removes the paper-size declaration, so the saved sheet has no paperSize attribute and Excel uses the printer's default paper. The next setPaperSize(int) declares a size again.

printHorizontallyCentered

public boolean printHorizontallyCentered()

Returns whether the printout is centered horizontally on the page (<printOptions horizontalCentered>, ECMA-376 §18.3.1.70).

setPrintHorizontallyCentered

public void setPrintHorizontallyCentered(boolean centered)

Sets whether the printout is centered horizontally on the page.

printVerticallyCentered

public boolean printVerticallyCentered()

Returns whether the printout is centered vertically on the page (<printOptions verticalCentered>, ECMA-376 §18.3.1.70).

setPrintVerticallyCentered

public void setPrintVerticallyCentered(boolean centered)

Sets whether the printout is centered vertically on the page.

setPageMargins

public void setPageMargins(double left,
 double right,
 double top,
 double bottom,
 double header,
 double footer)

Sets the six page margins in inches (ECMA-376 §18.3.1.62 pageMargins). header and footer are the distances from the page edge to the header and footer text.

Throws:

AxyraException - if any value is negative

setPageMargins

public void setPageMargins(PageMargins m)

Sets the page margins from a PageMargins value, in inches. It is the counterpart of pageMargins(), so a read, change and write needs no unpacking into six arguments.

Parameters:

m - the margins; not null

Throws:

AxyraException - if any value is negative

pageMargins

public PageMargins pageMargins()

Returns the six page margins in inches, as a snapshot.

setZoom

public void setZoom(int percent)

Sets the on-screen zoom in percent. It does not affect printing; use setPageScale(int) for the print scale.

Parameters:

percent - the zoom, from 10 to 400

Throws:

AxyraException - if percent is outside 10 to 400

zoom

public int zoom()

Returns the on-screen zoom in percent; 100 by default.

protect

public void protect()

Protects this sheet with Excel's default allowances and no password. Replaces any existing protection, its password and options included. Protection is saved to the file and enforced by Excel; the SDK does not block edits made through this API.

protect

public void protect(String password)

Protects this sheet with a password and Excel's default allowances. The password is stored as an ISO write-protection hash (SHA-512 with 100,000 spins, [MS-OFFCRYPTO] §2.4.2.2). Replaces any existing protection. The SDK does not block edits made through this API.

Parameters:

password - the password; null or empty protects without a password

protect

public void protect(String password,
 SheetProtection options)

Protects this sheet with the given allowances (ECMA-376 §18.3.1.85) and an optional password. Replaces any existing protection. The SDK does not block edits made through this API.

Parameters:

password - the password; null or empty protects without a password

options - what users may still do; null for Excel's defaults

unprotect

public void unprotect()

Removes sheet protection without checking any password. To check the password first, use unprotect(String).

unprotect

public boolean unprotect(String password)

Disable sheet protection after verifying password against the stored hash (ISO hash, or the legacy 16-bit password attribute of transitional files). Returns true when the sheet ends up unprotected — including when it was not protected or carries no password; returns false (sheet stays protected) on a mismatch.

isProtected

public boolean isProtected()

Returns whether this sheet is protected.

isProtectedWithPassword

public boolean isProtectedWithPassword()

Returns whether the sheet's protection carries a password.

Read-only: unlike unprotect(String) this leaves the sheet protected, so a caller can decide whether to prompt without changing the workbook to find out.

verifyProtectionPassword

public boolean verifyProtectionPassword(String password)

Returns whether password matches the sheet's stored hash. The protection stays in place either way.

Answers true whenever there is no hash to disagree with — both for a sheet protected without a password and for a sheet that is not protected at all. Read as "supplying this password would let you through", which is what a caller gating an unprotect() on it needs; do not read it as "this sheet has that password". The two cases are told apart by isProtectedWithPassword(), which is false for both of them.

protection

public SheetProtection protection()

Returns the protection options, as what users may still do, or null when this sheet is not protected.

Returns:

a snapshot; later changes do not update it

addProtectedRange

public void addProtectedRange(String name,
 String sqref,
 String password)

Adds an allow-users-to-edit-range (protectedRange — ECMA-376 Part 1 §18.3.1.72): the named range sqref stays editable while the sheet is protected. Pass a password to gate it, stored as an ISO write-protection hash, or null for an ungated range. An empty password is the same as null. Adding a range whose name matches an existing one case-insensitively replaces it.

Parameters:

name - the range name

sqref - space-separated A1 ranges on this sheet, such as "A1:B5 D1"

Throws:

AxyraException - if name or sqref is empty or blank

addProtectedRange

public void addProtectedRange(String name,
 String sqref)

Adds an allow-edit range without a password; see addProtectedRange(String, String, String).

Throws:

AxyraException - if name or sqref is empty or blank

removeProtectedRange

public boolean removeProtectedRange(String name)

Removes the allow-edit range named name.

Returns:

true if a range was removed

protectedRanges

public List<ProtectedRange> protectedRanges()

Returns the sheet's allow-edit ranges, in the order they were defined.

Returns:

a new list of snapshots; later edits do not change it

verifyProtectedRangePassword

public boolean verifyProtectedRangePassword(String name,
 String password)

Returns whether password matches the stored hash of the allow-edit range name, matched case-insensitively. Returns false when no range has that name and when the range has no password. Unlike verifyProtectionPassword(String), a missing password does not count as a match.

setAutoFilter

public void setAutoFilter(Range range)

Defines an auto-filter over range; its top row holds the filter buttons. Replaces any existing auto-filter on this sheet, with its column criteria. This call does not hide or show rows. Set a column criterion with setColumnFilter(int, java.lang.String...) or another setColumn*Filter method, which runs the filter at once, or call applyAutoFilter().

autoFilterRange

public Range autoFilterRange()

Returns the filtered block, header row included, or null when this sheet has no auto-filter.

Read counterpart of setAutoFilter(Range): a caller that did not set the filter itself (an imported workbook, for example) needs to know which block the dropdowns cover.

hasAutoFilter

public boolean hasAutoFilter()

Returns whether this sheet has an auto-filter. Use autoFilterRange() to get the filtered block.

clearAutoFilter

public void clearAutoFilter()

Removes the auto-filter and its criteria. Rows it hid stay hidden; show them with setRowHidden(int, boolean) or clear the criteria first with clearColumnFilter(int).

setColumnFilter

public void setColumnFilter(int columnIndex,
 String... values)

Keeps only the rows whose cell in the filter column is one of values. Takes effect at once, as in Excel: rows that fail are hidden and rows that pass are shown. Every setColumn*Filter and clearColumnFilter(int) runs the filter this way. A criterion replaces any earlier criterion on the same column. Does nothing when this sheet has no auto-filter; call setAutoFilter(Range) first.

Parameters:

columnIndex - 0-based column, counted from the first column of the auto-filter range

values - the cell texts to keep; null entries are ignored

setColumnCustomFilter

public void setColumnCustomFilter(int columnIndex,
 FilterOperator op,
 String value)

Sets a one-condition custom filter on a column, such as "greater than 100", and runs the filter. Behaves as setColumnFilter(int, java.lang.String...) for replacement and for a sheet without an auto-filter.

Parameters:

columnIndex - 0-based column, counted from the first column of the auto-filter range

value - the value to compare with

setColumnCustomFilter

public void setColumnCustomFilter(int columnIndex,
 FilterOperator op1,
 String value1,
 FilterOperator op2,
 String value2,
 boolean andJoin)

Sets a two-condition custom filter on a column and runs the filter. Behaves as setColumnFilter(int, java.lang.String...) for replacement and for a sheet without an auto-filter.

Parameters:

columnIndex - 0-based column, counted from the first column of the auto-filter range

andJoin - true to require both conditions, false to require either

setColumnColorFilter

public void setColumnColorFilter(int columnIndex,
 boolean cellColor,
 Color color)

Filters a column by cell fill color, or by font color when cellColor is false: only rows whose cell has color stay visible. Runs the filter at once and behaves as setColumnFilter(int, java.lang.String...) for replacement and for a sheet without an auto-filter. Only the RGB part of color is used; its alpha is dropped.

Parameters:

columnIndex - 0-based column, counted from the first column of the auto-filter range

color - an RGB color; not null

Throws:

IllegalStateException - if color is a theme, indexed or automatic color

AxyraException - if color is null

setColumnTop10Filter

public void setColumnTop10Filter(int columnIndex,
 boolean percent,
 double value,
 boolean bottom)

A Top-10 filter (ECMA-376 §18.3.2.10) on a column: keep the top value items — or value percent of the items when percent is true; bottom flips to the smallest values. The threshold is computed from the column data each time the filter runs; non-numeric cells are hidden, and ties at the threshold stay visible. Runs the filter at once.

Parameters:

columnIndex - 0-based column, counted from the first column of the auto-filter range

percent - true to read value as a percentage of the items

value - the number of items, or the percentage

bottom - true to keep the smallest values instead

setColumnDynamicFilter

public void setColumnDynamicFilter(int columnIndex,
 DynamicFilterType type)

A dynamic filter (ECMA-376 §18.3.2.5) on a column: today / thisWeek / lastMonth / yearToDate / aboveAverage / and so on. Runs the filter at once. Date ranges resolve against the current date on the machine's clock, and the column average is computed, each time the filter runs.

Parameters:

columnIndex - 0-based column, counted from the first column of the auto-filter range

setColumnDateFilter

public void setColumnDateFilter(int columnIndex,
 DateGroupItem... items)

A date grouping filter (ECMA-376 §18.3.2.4) on a column: a date cell stays visible if it falls inside any of items. Blank cells are hidden; use the includeBlank overload to keep them. Runs the filter at once.

Parameters:

columnIndex - 0-based column, counted from the first column of the auto-filter range

setColumnDateFilter

public void setColumnDateFilter(int columnIndex,
 boolean includeBlank,
 DateGroupItem... items)

Sets a date grouping filter as setColumnDateFilter(int, DateGroupItem...) does, optionally keeping blank cells too.

Parameters:

columnIndex - 0-based column, counted from the first column of the auto-filter range

includeBlank - true to keep rows whose cell is blank

clearColumnFilter

public void clearColumnFilter(int columnIndex)

Removes the criterion from one column and runs the filter, so rows that now pass are shown again.

Parameters:

columnIndex - 0-based column, counted from the first column of the auto-filter range

sortByColor

public void sortByColor(Range range,
 int sortColumnIndex,
 boolean cellColor,
 boolean hasHeader,
 Color... colors)

Sort the rows of range by cell fill color (or font color when cellColor is false) at sortColumnIndex (0-based, relative to the range start). Rows are ordered by their color's position in colors; rows with unlisted colors sort last and keep their order. When hasHeader is true, the first row stays in place. Only the RGB part of each color is used; its alpha is dropped.

Throws:

IllegalStateException - if a color is a theme, indexed or automatic color

applyAutoFilter

public int applyAutoFilter()

Execute the auto-filter: hides every data row failing any column's criteria and shows the rest. Returns the number of rows hidden. All filter kinds are evaluated — values, custom, color, Top-10, dynamic (dates resolve against the current date; weeks start on Sunday), and date grouping.

Setting or clearing a column criterion already runs it; call this to run the filter again after the data under it changed. Formulas that read the affected rows, such as SUBTOTAL, recalculate.

Returns:

the number of data rows hidden after the run, not only the newly hidden ones; 0 when this sheet has no auto-filter

applyAdvancedFilter

public int applyAdvancedFilter(Range listRange,
 Range criteriaRange,
 boolean uniqueOnly)

Run an advanced filter (Excel's Data > Advanced) over listRange in place, hiding the data rows that do not match criteriaRange. Both ranges include a header row; criteria headers are matched to list columns by name. Criteria rows are OR-ed and the cells within one row AND-ed; each criterion cell is empty (no constraint), a comparison (>100, <=50, <>East, =East), or a bare value (numbers match by equality, text "begins with", both honoring */? wildcards). When uniqueOnly is set, duplicate matching rows are also hidden, keeping the first.

Returns:

the number of rows hidden

applyAdvancedFilterCopyTo

public int applyAdvancedFilterCopyTo(Range listRange,
 Range criteriaRange,
 Range destination,
 boolean uniqueOnly)

Advanced filter in "Copy to another location" mode: instead of hiding source rows, writes the list header plus each matching row (see applyAdvancedFilter(Range, Range, boolean) for the criteria semantics) starting at the top-left cell of destination. Cells are copied whole: value, style and formula.

Returns:

the number of data rows written, the header row not counted

groupRows

public void groupRows(int startRow,
 int endRow)

Groups rows startRow to endRow, 0-based and inclusive, at outline level 1. The rows are not hidden; collapse the group with setRowGroupCollapsed(int, int, boolean).

setRowGroupCollapsed

public void setRowGroupCollapsed(int startRow,
 int endRow,
 boolean collapsed)

Collapse or expand a grouped row range (Excel's outline +/- button): hides or shows the detail rows and sets the collapsed flag on the group's summary row: the row below the group when summaryBelow() is true, else the row above.

Parameters:

startRow - 0-based first row of the group

endRow - 0-based last row of the group, inclusive

setColumnGroupCollapsed

public void setColumnGroupCollapsed(int startCol,
 int endCol,
 boolean collapsed)

Collapses or expands a grouped column range, as setRowGroupCollapsed(int, int, boolean) does for rows.

Parameters:

startCol - 0-based first column of the group

endCol - 0-based last column of the group, inclusive

setRowsOutlineLevel

public void setRowsOutlineLevel(int startRow,
 int endRow,
 int level)

Sets the outline level on rows startRow to endRow, 0-based and inclusive. 0 ungroups; values above 7 are stored as 7. Rows are not hidden; use setRowGroupCollapsed(int, int, boolean) to collapse a group.

groupColumns

public void groupColumns(int startCol,
 int endCol)

Groups columns startCol to endCol, 0-based and inclusive, at outline level 1. The columns are not hidden; collapse the group with setColumnGroupCollapsed(int, int, boolean).

setColumnsOutlineLevel

public void setColumnsOutlineLevel(int startCol,
 int endCol,
 int level)

Sets the outline level on columns startCol to endCol, 0-based and inclusive. 0 ungroups; values above 7 are stored as 7. Columns are not hidden; use setColumnGroupCollapsed(int, int, boolean) to collapse a group.

rowOutlineLevel

public int rowOutlineLevel(int row)

Returns a row's outline level, from 0 (not grouped) to 7.

Parameters:

row - 0-based row index

autoFitRow

public void autoFitRow(int row)

Sets a row's height to fit its tallest content, as Excel's AutoFit Row Height does. Each cell contributes one line of its font, times its line count when the cell wraps text. Cells inside a merged region are ignored, as in Excel. A row with no content fits to the row style's font, or to the workbook's default font.

Parameters:

row - 0-based row index

autoFitColumn

public void autoFitColumn(int col)

Sets a column's width to fit the widest text its cells display in the sheet's used rows. The width is measured on the formatted value, for example 8,060.00, so a number never shows ####. Does nothing on an empty sheet.

Parameters:

col - 0-based column index

subtotal

public int subtotal(int firstRow,
 int lastRow,
 int groupColumn,
 SubtotalFunction function,
 int... subtotalColumns)

Insert subtotals into the sorted data block [firstRow, lastRow]: after each run of equal values in groupColumn, a summary row of function over subtotalColumns is inserted (plus a grand total). Data rows get outline level 1. Rows are 0-based and inclusive; groupColumn and subtotalColumns are 0-based sheet columns. Returns the number of rows inserted.

Like Excel, the grand total is one SUBTOTAL over the whole data body rather than a SUM of the group summaries: SUBTOTAL ignores the summaries caught inside that span, so the span keeps totalling correctly when a row is later inserted into a group.

Every row of the block is data — pass hasHeaders to the overload below for Excel's Data > Subtotal behaviour instead.

subtotal

public int subtotal(int firstRow,
 int lastRow,
 int groupColumn,
 boolean hasHeaders,
 SubtotalFunction function,
 int... subtotalColumns)

Insert subtotals into [firstRow, lastRow] as above. With hasHeaders the block follows Excel's Data > Subtotal contract: leading blank rows and the first non-blank row (the header) stay in place and never form a group of their own. With hasHeaders false every row of the block is data. Returns the number of rows inserted.

consolidate

public int[] consolidate(int destRow,
 int destColumn,
 ConsolidateFunction function,
 Range... sources)

Consolidates several source ranges of the same shape, possibly on other sheets, into this sheet at (destRow, destColumn), as Excel's Data > Consolidate does by position. Cell (i, j) of the result aggregates cell (i, j) of every source with function.

Each source must have the same shape as the first; other sources are skipped. Only numeric cells are read; other cells count as empty, so ConsolidateFunction.COUNT_A counts numeric cells only, the same as COUNT. The result is written as numbers, not as linked formulas, so later source edits do not update it.

Parameters:

destRow - 0-based top row of the result

destColumn - 0-based left column of the result

Returns:

{rows, columns} written; {0, 0} when no source could be used

addSparkline

public int addSparkline(String type,
 String dataRef,
 String locationRef)

Add a sparkline group ("line", "column", or "stacked") that draws dataRef (e.g. "Sheet1!A1:A10") into locationRef (e.g. "B1"), the way Excel's Insert > Sparklines does: each cell of a one-row or one-column location gets its own sparkline over one row (or column) of the data, so "B2:G4" into "H2:H4" is three sparklines. A dataRef without a sheet name refers to this sheet; the data is stored sheet-qualified. An unrecognized type creates a line sparkline.

Returns:

the new group's 0-based index

Throws:

AxyraException - if the location is not one row or column, is on another sheet, or the data has no row or column count matching it, or a reference cannot be parsed

setSparklineStyle

public boolean setSparklineStyle(int group,
 SparklineStyle style)

Replaces the whole style of the sparkline group at group: colors, point-highlight flags and axis settings. A null color or axis value in style clears that setting; it does not keep the old one. Read the current style with sparklines() and SparklineGroup.style(), change it, and pass it back.

Parameters:

group - 0-based sparkline group index

style - the new style; not null

Returns:

true if the group was restyled; false when group is negative or at or past sparklineGroupCount()

sparklineGroupCount

public int sparklineGroupCount()

Returns the number of sparkline groups on this sheet.

sparklines

public List<SparklineGroup> sparklines()

All sparkline groups on this sheet (ECMA-376 Part 4 §18.3.1.32, x14:sparklineGroups): the group type, basic marker/highlight style, series color and each sparkline's data range and draw location. The read-side counterpart of addSparkline(String, String, String).

Returns:

a new list of snapshots; later edits do not change it

addShape

public int addShape(String shapeType,
 String text,
 int fromRow,
 int fromCol,
 int toRow,
 int toCol)

Adds a drawing shape, such as "rect", "ellipse" or "roundRect", with optional text, over the cell rectangle from (fromRow, fromCol) to (toRow, toCol), 0-based. The shape gets the fill, outline and text settings Excel gives a newly inserted shape in this workbook's theme. Passing "textBox" adds a text box, as addTextBox(java.lang.String, int, int, int, int) does.

Parameters:

text - the shape's text, or null for none

Returns:

the new shape's 0-based index

groupShapes

public int groupShapes(String name,
 int... shapeIndexes)

Group existing shapes into one <xdr:grpSp> container (Excel's "Group"). shapeIndexes are this sheet's shape indexes; at least two ungrouped, non-group shapes are required. The members keep their indexes and the group is appended as a new shape entry whose anchor is the members' bounding box. Returns the group's shape index, or -1 when the set is not groupable.

ungroupShapes

public boolean ungroupShapes(int shapeIndex)

Dissolve a group created by groupShapes(java.lang.String, int...): members return to standalone emission and the group entry is removed. Returns whether shapeIndex was such a group (imported verbatim groups are not dissolvable — their children exist only inside the captured XML).

addTextBox

public int addTextBox(String text,
 int fromRow,
 int fromCol,
 int toRow,
 int toCol)

Add a text box holding text, anchored over the cell rectangle from (fromRow, fromCol) to (toRow, toCol), 0-based; returns the new shape's 0-based index. It is written the way Excel's Insert > Text Box writes one (a rect flagged <xdr:cNvSpPr txBox="1"/>), so readers tell it from a shape: isTextBox(int) answers true, while shapeType(int) keeps reporting its geometry.

addConnector

public int addConnector(String preset,
 int fromRow,
 int fromCol,
 int toRow,
 int toCol)

Add a connector shape (<xdr:cxnSp>) with the given preset geometry name (e.g. "straightConnector1", "bentConnector3", "curvedConnector3", "line"), anchored over the cell rectangle from (fromRow, fromCol) to (toRow, toCol), 0-based. The connector shares the shape index space with addShape(java.lang.String, java.lang.String, int, int, int, int); shapeType(int) reports it as "connector:<preset>".

Returns:

the new shape's 0-based index

shapeCount

public int shapeCount()

Returns the number of shapes on this sheet, text boxes, connectors and groups included.

removeShape

public boolean removeShape(int shapeIndex)

Removes the shape at shapeIndex, the 0-based index that shapeType(int) uses. Later shapes move down one place.

Returns:

true if a shape was removed

shapeType

public String shapeType(int shapeIndex)

Returns the geometry token of the shape at shapeIndex, such as "rect". A connector is reported as "connector:<preset>", for example "connector:straightConnector1", and a group as "group".

isTextBox

public boolean isTextBox(int shapeIndex)

Returns whether the shape at shapeIndex is a text box (<xdr:cNvSpPr txBox="1"/>): one added by addTextBox(java.lang.String, int, int, int, int) or read from a file. false for an unknown index.

shapeText

public String shapeText(int shapeIndex)

Returns the shape's text, or null when it has none or the index has no shape.

shapeFillColor

public Color shapeFillColor(int shapeIndex)

Returns the shape's solid fill color as an RGB color, or null when the shape has no solid RGB fill: no fill, a gradient, a pattern or a theme color.

shapeLineColor

public Color shapeLineColor(int shapeIndex)

Returns the shape's outline color as an RGB color, or null when it has no solid RGB outline.

shapeStyleXml

public String shapeStyleXml(int shapeIndex)

Returns the raw inner XML of the shape's <xdr:style> theme style-matrix reference (the lnRef / fillRef / effectRef / fontRef children), or null when the shape has no such block. The reference is theme-relative, so it round-trips as long as the workbook theme is preserved.

shapeRotation

public double shapeRotation(int shapeIndex)

Returns the shape's rotation in degrees, clockwise.

shapeFlip

public boolean[] shapeFlip(int shapeIndex)

Returns {flipHorizontal, flipVertical}, or an empty array when the index has no shape.

shapeAnchor

public int[] shapeAnchor(int shapeIndex)

Returns {fromRow, fromCol, toRow, toCol} of the shape's anchor, 0-based, or an empty array when the index has no shape.

setShapeFillColor

public void setShapeFillColor(int shapeIndex,
 Color color)

Sets the shape's fill to a solid color, replacing any gradient or pattern fill. Only the RGB part of color is used; its alpha is dropped. Does nothing when shapeIndex has no shape.

Parameters:

color - an RGB color, or null to clear the fill

Throws:

IllegalStateException - if color is a theme, indexed or automatic color

setShapeLineColor

public void setShapeLineColor(int shapeIndex,
 Color color)

Sets the shape's outline color. Only the RGB part of color is used; its alpha is dropped. Does nothing when shapeIndex has no shape.

Parameters:

color - an RGB color, or null to clear the outline color

Throws:

IllegalStateException - if color is a theme, indexed or automatic color

setShapeStyleXml

public void setShapeStyleXml(int shapeIndex,
 String xml)

Sets, or clears with null, the shape's <xdr:style> theme style-matrix reference from raw inner XML (the lnRef / fillRef / effectRef / fontRef children, without the enclosing <xdr:style> tags). Pair with shapeStyleXml(int) to carry a theme-relative fill, line, effect and font to a copy. The XML is not checked. Does nothing when shapeIndex has no shape.

setShapeRotation

public void setShapeRotation(int shapeIndex,
 double degrees)

Sets the shape's rotation in degrees, clockwise. Does nothing when shapeIndex has no shape.

setShapeText

public void setShapeText(int shapeIndex,
 String text)

Replaces the shape's text, or clears it with null. The new text takes the formatting of the old first paragraph and first run; the formatting of other runs is dropped. A \n starts a new paragraph. Does nothing when shapeIndex has no shape.

setShapeFlip

public void setShapeFlip(int shapeIndex,
 boolean flipHorizontal,
 boolean flipVertical)

Sets the shape's horizontal and vertical flip flags. Does nothing when shapeIndex has no shape.

shapeHyperlink

public String shapeHyperlink(int shapeIndex)

Returns the hyperlink URL attached to the shape through DrawingML hlinkClick, or null when the shape has no hyperlink.

setShapeHyperlink

public void setShapeHyperlink(int shapeIndex,
 String url)

Sets, or clears with null, the shape's hyperlink URL. It is saved as a drawing-level external hyperlink relationship plus the <a:hlinkClick r:id> element on the shape. Does nothing when shapeIndex has no shape.

shapeAltText

public String shapeAltText(int shapeIndex)

Returns the shape's alternative text (cNvPr/@descr), or null when it has none.

setShapeAltText

public void setShapeAltText(int shapeIndex,
 String text)

Sets, or clears with null, the shape's alternative text. Does nothing when shapeIndex has no shape.

shapeName

public String shapeName(int shapeIndex)

Returns the shape's drawing name (<xdr:cNvPr name>, ECMA-376 Part 1 §20.5.2.16): what Excel shows in the Name Box and what a macro uses to address the shape. A shape added through this SDK gets a generated name. Returns null when there is no shape at that index.

setShapeName

public boolean setShapeName(int shapeIndex,
 String name)

Renames a shape. Returns false, and changes nothing, for an empty name or an index with no shape: a blank cNvPr/@name would leave the shape unreachable from both the Name Box and any macro.

Returns:

true if the shape was renamed

renderChartToPng

public byte[] renderChartToPng(int chartIndex,
 int width,
 int height)

Renders the chart at chartIndex to PNG bytes. Pivot tables changed through the API and not refreshed since are refreshed first. Under license enforcement without a license, the image carries the evaluation banner and counts against the evaluation export budget.

Parameters:

chartIndex - 0-based chart index on this sheet

width - image width in pixels; values below 1 are raised to 1

height - image height in pixels; values below 1 are raised to 1

Returns:

the PNG bytes, or null when the index is out of range

Throws:

AxyraException - if the evaluation export budget is used up

renderChartToSvg

public String renderChartToSvg(int chartIndex,
 int width,
 int height)

Renders the chart at chartIndex to a standalone SVG document.

The vector counterpart of renderChartToPng(int, int, int): it draws with the same chart renderer, so every chart type the PNG draws is drawn here too. Text comes out as <text> elements and the plot as paths, so the picture scales and prints at any resolution. Pivot tables changed through the API and not refreshed since are refreshed first. The document counts against the evaluation export budget under license enforcement; it carries no evaluation banner.

Parameters:

chartIndex - 0-based chart index on this sheet

width - document width in pixels; values below 1 are raised to 1

height - document height in pixels; values below 1 are raised to 1

Returns:

the SVG text, or null when the index is out of range

Throws:

AxyraException - if the evaluation export budget is used up