com.hfg.xml.msofficexml.xlsx.spreadsheetml.SsmlWorksheet Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of com_hfg Show documentation
Show all versions of com_hfg Show documentation
com.hfg xml, html, svg, and bioinformatics utility library
package com.hfg.xml.msofficexml.xlsx.spreadsheetml;
import java.io.Writer;
import java.util.*;
import java.util.regex.Pattern;
import com.hfg.exception.InvalidValueException;
import com.hfg.exception.ProgrammingException;
import com.hfg.util.StringBuilderPlus;
import com.hfg.util.collection.CollectionUtil;
import com.hfg.util.StringUtil;
import com.hfg.xml.XMLNamespace;
import com.hfg.xml.XMLNode;
import com.hfg.xml.XMLTag;
import com.hfg.xml.msofficexml.OfficeOpenXmlException;
import com.hfg.xml.msofficexml.OfficeXML;
import com.hfg.xml.msofficexml.docx.RelationshipXML;
import com.hfg.xml.msofficexml.xlsx.CellRef;
import com.hfg.xml.msofficexml.xlsx.spreadsheetDrawing.WorksheetDrawing;
import com.hfg.xml.msofficexml.xlsx.CellRange;
import com.hfg.xml.msofficexml.xlsx.Xlsx;
import com.hfg.xml.msofficexml.xlsx.part.SsmlCommentsPart;
import com.hfg.xml.msofficexml.xlsx.part.SsmlDrawingPart;
import com.hfg.xml.msofficexml.xlsx.part.TablePart;
import com.hfg.xml.msofficexml.xlsx.part.WorksheetPart;
//------------------------------------------------------------------------------
/**
Represents an Office Open XML sheet (<ssml:worksheet>) tag.
@author J. Alex Taylor, hairyfatguy.com
*/
//------------------------------------------------------------------------------
// com.hfg XML/HTML Coding Library
//
// This library is free software; you can redistribute it and/or
// modify it under the terms of the GNU Lesser General Public
// License as published by the Free Software Foundation; either
// version 2.1 of the License, or (at your option) any later version.
//
// This library is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
// Lesser General Public License for more details.
//
// You should have received a copy of the GNU Lesser General Public
// License along with this library; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
//
// J. Alex Taylor, President, Founder, CEO, COO, CFO, OOPS hairyfatguy.com
// [email protected]
//------------------------------------------------------------------------------
public class SsmlWorksheet extends SsmlXMLTag
{
//###########################################################################
// PRIVATE FIELDS
//###########################################################################
private WorksheetPart mParentWorksheetPart;
private SsmlCommentsPart mCommentsPart;
private String mName;
private SsmlSheetData mSheetData;
private SsmlPageMargins mPageMargins;
private SsmlSheetFormatProperties mSheetFormatPropertiesTag;
private XMLTag mSheetViewsTag;
private XMLTag mTablePartsTag;
private XMLTag mMergeCellsTag;
private XMLTag mColumnsTag;
private XMLTag mDimensionTag;
private XMLTag mDataValidationsTag;
private SsmlHeaderFooter mHeaderFooterTag;
private XMLTag mExtLst;
private SsmlSortState mSortState;
private Map mTableParts;
private Map mDrawingParts;
// Each sheet needs a unique GUID
private final String mUID = UUID.randomUUID().toString().toUpperCase();
// There are seven bad characters for sheet names: \ / * [ ] : ?
private static final Pattern BAD_SHEET_NAME_CHAR_PATTERN = Pattern.compile("[\\\\\\/\\*\\[\\]\\:\\?+]");
private static final int MAX_SHEET_NAME_LENGTH = 31;
private static final String PRINT_AREA_NAME = "_xlnm.Print_Area";
//###########################################################################
// CONSTRUCTORS
//###########################################################################
//---------------------------------------------------------------------------
public SsmlWorksheet(WorksheetPart inParentWorksheetPart)
{
super(SsmlXML.WORKSHEET, (Xlsx) inParentWorksheetPart.getParentDoc());
mParentWorksheetPart = inParentWorksheetPart;
addXMLNamespaceDeclaration(RelationshipXML.RELATIONSHIP_NAMESPACE);
addXMLNamespaceDeclaration(OfficeXML.MARKUP_CAPABILITY_NAMESPACE);
addXMLNamespaceDeclaration(OfficeXML.MAC_VML_NAMESPACE);
addXMLNamespaceDeclaration(SsmlXML.SPREADSHEETML_2009AC_NAMESPACE);
addXMLNamespaceDeclaration(SsmlXML.SPREADSHEETML_REVISION1_NAMESPACE);
addXMLNamespaceDeclaration(SsmlXML.SPREADSHEETML_REVISION2_NAMESPACE);
addXMLNamespaceDeclaration(SsmlXML.SPREADSHEETML_REVISION3_NAMESPACE);
StringBuilderPlus ignorableBuffer = new StringBuilderPlus().setDelimiter(" ")
.delimitedAppend(SsmlXML.SPREADSHEETML_2009AC_NAMESPACE.getPrefix())
.delimitedAppend(SsmlXML.SPREADSHEETML_REVISION1_NAMESPACE.getPrefix())
.delimitedAppend(SsmlXML.SPREADSHEETML_REVISION2_NAMESPACE.getPrefix())
.delimitedAppend(SsmlXML.SPREADSHEETML_REVISION3_NAMESPACE.getPrefix());
setAttribute(OfficeXML.IGNORABLE_ATT, ignorableBuffer.toString());
// Specify the sheet's GUID
setAttribute(SsmlXML.XR_UID_ATT, "{" + mUID + "}");
getPageMargins();
}
//---------------------------------------------------------------------------
public SsmlWorksheet(WorksheetPart inParentWorksheetPart, String inName, XMLTag inSheetTag)
{
this(inParentWorksheetPart);
String name = inSheetTag.getAttributeValue(SsmlXML.NAME_ATT);
setName(StringUtil.isSet(name) ? name : inName);
try
{
XMLTag sheetDataTag = inSheetTag.getRequiredSubtagByName(SsmlXML.SHEET_DATA);
mSheetData = new SsmlSheetData(this, sheetDataTag);
// TODO: Parse other pieces
}
catch (Exception e)
{
throw new OfficeOpenXmlException("Problem parsing worksheet " + StringUtil.singleQuote(getName()) + "!", e);
}
}
//###########################################################################
// PUBLIC METHODS
//###########################################################################
//---------------------------------------------------------------------------
public List toTSV()
{
List lines = new ArrayList<>();
for (SsmlRow row : getSheetData().getRows())
{
// Fill in missing lines
while (lines.size() < row.getRowIndex() - 1)
{
lines.add("");
}
StringBuilderPlus lineBuffer = new StringBuilderPlus().setDelimiter("\t");
List cells = row.getCells();
if (CollectionUtil.hasValues(cells))
{
SsmlCell prevCell = null;
int cellCount = 0;
for (SsmlCell cell : cells)
{
cellCount++;
int leadingEmptyCells = cell.getRef().getColIndex() - (prevCell != null ? prevCell.getRef().getColIndex() : 0) - 1;
if (leadingEmptyCells > 0)
{
lineBuffer.delimitedAppend(StringUtil.polyChar('\t', leadingEmptyCells));
}
Object value = cell.getValue();
String stringValue = (value != null ? value.toString() : "");
// Handle multi-line cells
if (stringValue.indexOf('\n', 0) > 0)
{
stringValue = StringUtil.quote(StringUtil.replaceAllRegexp(stringValue, "\r?\n", "\\n"));
}
// Was using delimitedAppend() but it won't separate blank cells
if (cellCount > 1)
{
lineBuffer.append("\t");
}
lineBuffer.append(stringValue);
prevCell = cell;
}
lines.add(lineBuffer.toString());
}
}
return lines;
}
//---------------------------------------------------------------------------
@Override
public String toString()
{
return getName();
}
//---------------------------------------------------------------------------
public WorksheetPart getParentWorksheetPart()
{
return mParentWorksheetPart;
}
//---------------------------------------------------------------------------
public SsmlCommentsPart getCommentsPart()
{
if (null == mCommentsPart)
{
mCommentsPart = new SsmlCommentsPart(this);
}
return mCommentsPart;
}
//---------------------------------------------------------------------------
public SsmlWorksheet setName(String inValue)
{
mName = null;
if (StringUtil.isSet(inValue))
{
mName = inValue;
// Remove illegal sheet name characters
mName = StringUtil.replaceAllRegexp(mName, BAD_SHEET_NAME_CHAR_PATTERN, "_");
// Truncate sheet names that are too long
if (mName.length() > MAX_SHEET_NAME_LENGTH)
{
mName = mName.substring(0, MAX_SHEET_NAME_LENGTH);
}
}
setAttribute(SsmlXML.NAME_ATT, mName);
return this;
}
//---------------------------------------------------------------------------
public String getName()
{
return mName;
}
//---------------------------------------------------------------------------
public SsmlSheetData getSheetData()
{
if (null == mSheetData)
{
// Check it it has been added via addSubtag()...
mSheetData = getOptionalSubtagByName(SsmlXML.SHEET_DATA);
if (null == mSheetData)
{
mSheetData = new SsmlSheetData(this);
addSubtag(mSheetData);
}
}
return mSheetData;
}
//---------------------------------------------------------------------------
public void mergeCells(CellRange inCellRange)
{
XMLTag mergeCellsTag = getMergeCellsTag();
XMLTag mergeCellTag = new XMLTag(SsmlXML.MERGE_CELL);
mergeCellTag.setAttribute(SsmlXML.REF_RANGE_ATT, inCellRange);
mergeCellsTag.addSubtag(mergeCellTag);
mergeCellsTag.setAttribute(SsmlXML.COUNT_ATT, mergeCellsTag.getSubtags().size());
}
//---------------------------------------------------------------------------
public void removeMergeCell(CellRange inCellRange)
{
XMLTag mergeCellsTag = getMergeCellsTag();
for (XMLNode mergeCellTag : mergeCellsTag.getSubtagsByName(SsmlXML.MERGE_CELL))
{
if (mergeCellTag.getAttributeValue(SsmlXML.REF_RANGE_ATT).equals(inCellRange.toString()))
{
mergeCellsTag.removeSubtag(mergeCellTag);
break;
}
}
mergeCellsTag.setAttribute(SsmlXML.COUNT_ATT, mergeCellsTag.getSubtags().size());
}
//---------------------------------------------------------------------------
public SsmlCol addColumn()
{
if (null == mColumnsTag)
{
// Check it it has been added via addSubtag()...
mColumnsTag = getOptionalSubtagByName(SsmlXML.COLUMNS);
if (null == mColumnsTag)
{
mColumnsTag = new XMLTag(SsmlXML.COLUMNS);
addSubtag(mColumnsTag);
}
}
SsmlCol col = new SsmlCol(this);
mColumnsTag.addSubtag(col);
return col;
}
//---------------------------------------------------------------------------
public SsmlTable addTable()
{
if (null == mTableParts)
{
mTableParts = new HashMap<>(10);
mTablePartsTag = new XMLTag(SsmlXML.TABLE_PARTS);
addSubtag(mTablePartsTag);
}
TablePart tablePart = new TablePart(getParentWorksheetPart()).setTableIndex(mTableParts.size() + 1);
addTablePart(tablePart);
return tablePart.getRootNode();
}
//---------------------------------------------------------------------------
public SsmlDataValidation addDataValidation(CellRange inCellRange)
{
if (null == mDataValidationsTag)
{
// Check it it has been added via addSubtag()...
mDataValidationsTag = getOptionalSubtagByName(SsmlXML.DATA_VALIDATIONS);
if (null == mDataValidationsTag)
{
mDataValidationsTag = new XMLTag(SsmlXML.DATA_VALIDATIONS);
addSubtag(mDataValidationsTag);
}
}
SsmlDataValidation dataValidation = new SsmlDataValidation(getParentDoc(), inCellRange);
mDataValidationsTag.addSubtag(dataValidation);
mDataValidationsTag.setAttribute(SsmlXML.COUNT_ATT, mDataValidationsTag.getSubtagsByName(SsmlXML.DATA_VALIDATION).size());
return dataValidation;
}
//---------------------------------------------------------------------------
/**
Returns all table definitions.
*/
public Collection getTables()
{
ArrayList tables = new ArrayList(mTableParts.size());
if (CollectionUtil.hasValues(mTableParts))
{
for (TablePart part : mTableParts.values())
{
tables.add(part.getRootNode());
}
}
return tables;
}
//---------------------------------------------------------------------------
/**
Returns a table by name.
* @param inTableName the name of the table
* @return SsmlTable
*/
public SsmlTable getTable(String inTableName)
{
TablePart part = mTableParts.get(inTableName);
return (part != null ? part.getRootNode() : null);
}
//---------------------------------------------------------------------------
public SsmlWorksheet setDimension(CellRange inCellRange)
{
if (null == mDimensionTag)
{
// Check it it has been added via addSubtag()...
mDimensionTag = getOptionalSubtagByName(SsmlXML.DIMENSION);
if (null == mDimensionTag)
{
mDimensionTag = new XMLTag(SsmlXML.DIMENSION);
addSubtag(mDimensionTag);
}
}
mDimensionTag.setAttribute(SsmlXML.REF_RANGE_ATT, inCellRange);
return this;
}
//---------------------------------------------------------------------------
public SsmlSheetFormatProperties getSheetFormatProperties()
{
if (null == mSheetFormatPropertiesTag)
{
// Check it it has been added via addSubtag()...
mSheetFormatPropertiesTag = getOptionalSubtagByName(SsmlXML.SHEET_FORMAT_PROPS);
if (null == mSheetFormatPropertiesTag)
{
mSheetFormatPropertiesTag = new SsmlSheetFormatProperties(getParentDoc());
addSubtag(mSheetFormatPropertiesTag);
}
}
return mSheetFormatPropertiesTag;
}
//---------------------------------------------------------------------------
public SsmlPageMargins getPageMargins()
{
if (null == mPageMargins)
{
// Check it it has been added via addSubtag()...
mPageMargins = getOptionalSubtagByName(SsmlXML.PAGE_MARGINS);
if (null == mPageMargins)
{
mPageMargins = new SsmlPageMargins(getParentDoc());
addSubtag(mPageMargins);
}
}
return mPageMargins;
}
//---------------------------------------------------------------------------
public SsmlSheetView addSheetView()
{
if (null == mSheetViewsTag)
{
mSheetViewsTag = new XMLTag(SsmlXML.SHEET_VIEWS);
addSubtag(mSheetViewsTag);
}
SsmlSheetView sheetView = new SsmlSheetView(getParentDoc());
mSheetViewsTag.addSubtag(sheetView);
return sheetView;
}
//---------------------------------------------------------------------------
public List getSheetViews()
{
List views = null;
if (mSheetViewsTag != null)
{
views = mSheetViewsTag.getSubtagsByName(SsmlXML.SHEET_VIEW);
}
return views;
}
//---------------------------------------------------------------------------
public SsmlWorksheet setHeader(String inValue)
{
if (null == mHeaderFooterTag)
{
// Check it it has been added via addSubtag()...
mHeaderFooterTag = getOptionalSubtagByName(SsmlXML.HEADER_FOOTER);
}
if (StringUtil.isSet(inValue))
{
if (null == mHeaderFooterTag)
{
mHeaderFooterTag = new SsmlHeaderFooter(getParentDoc());
addSubtag(mHeaderFooterTag);
}
mHeaderFooterTag.setHeader(inValue);
}
else if (mHeaderFooterTag != null)
{
mHeaderFooterTag.setHeader(null);
}
return this;
}
//---------------------------------------------------------------------------
public String getHeader()
{
if (null == mHeaderFooterTag)
{
// Check it it has been added via addSubtag()...
mHeaderFooterTag = getOptionalSubtagByName(SsmlXML.HEADER_FOOTER);
}
return (mHeaderFooterTag != null ? mHeaderFooterTag.getHeader() : null);
}
//---------------------------------------------------------------------------
public SsmlWorksheet setFooter(String inValue)
{
if (null == mHeaderFooterTag)
{
// Check it it has been added via addSubtag()...
mHeaderFooterTag = getOptionalSubtagByName(SsmlXML.HEADER_FOOTER);
}
if (StringUtil.isSet(inValue))
{
if (null == mHeaderFooterTag)
{
mHeaderFooterTag = new SsmlHeaderFooter(getParentDoc());
addSubtag(mHeaderFooterTag);
}
mHeaderFooterTag.setFooter(inValue);
}
else if (mHeaderFooterTag != null)
{
mHeaderFooterTag.setFooter(null);
}
return this;
}
//---------------------------------------------------------------------------
public String getFooter()
{
if (null == mHeaderFooterTag)
{
// Check it it has been added via addSubtag()...
mHeaderFooterTag = getOptionalSubtagByName(SsmlXML.HEADER_FOOTER);
}
return (mHeaderFooterTag != null ? mHeaderFooterTag.getFooter() : null);
}
//---------------------------------------------------------------------------
public WorksheetDrawing addDrawing()
{
SsmlDrawingPart drawingPart = new SsmlDrawingPart(getParentWorksheetPart());
getParentDoc().addDrawingPart(drawingPart);// This will set the drawing part's index
addDrawingPart(drawingPart);
return drawingPart.getRootNode();
}
//---------------------------------------------------------------------------
public SsmlConditionalFormatting addConditionalFormatting(CellRange inCellRange)
{
SsmlConditionalFormatting conditionalFormatting = new SsmlConditionalFormatting(this, inCellRange);
addSubtag(conditionalFormatting);
return conditionalFormatting;
}
//---------------------------------------------------------------------------
public SsmlExtension getOrAddExtension(XMLNamespace inNamespace, SsmlExtension.URI_Type inURI_Type)
{
// Extensions are collected under an extLst tag
if (null == mExtLst)
{
// Check if it has been added via addSubtag()...
mExtLst = getOptionalSubtagByName(SsmlXML.EXTENSION_LIST);
if (null == mExtLst)
{
mExtLst = new XMLTag(SsmlXML.EXTENSION_LIST);
addSubtag(mExtLst);
}
}
SsmlExtension extension = mExtLst.getSubtagByAttribute(SsmlXML.URI_ATT, inURI_Type.getURI());
if (null == extension)
{
extension = new SsmlExtension(this, inNamespace, inURI_Type);
mExtLst.addSubtag(extension);
}
return extension;
}
//---------------------------------------------------------------------------
public void setPrintArea(CellRange inPrintArea)
{
getParentDoc().getWorkbookPart().getRootNode()
.setDefinedName(PRINT_AREA_NAME, this, StringUtil.singleQuote(getName())
+ "!$" + inPrintArea.getBeginCell().getCol() + "$" + inPrintArea.getBeginCell().getRowIndex()
+ ":$" + inPrintArea.getEndCell().getCol() + "$" + inPrintArea.getEndCell().getRowIndex());
}
//---------------------------------------------------------------------------
@Override
public void toXML(Writer inWriter)
{
finalizeSheet();
super.toXML(inWriter);
}
//---------------------------------------------------------------------------
@Override
public void toIndentedXML(Writer inWriter, int inInitialIndentLevel, int inIndentSize)
{
finalizeSheet();
super.toIndentedXML(inWriter, inInitialIndentLevel, inIndentSize);
}
//---------------------------------------------------------------------------
public SsmlWorksheet setSortState(SsmlSortState inValue)
{
mSortState = inValue;
if (inValue != null)
{
addSubtag(mSortState);
}
else
{
// Remove the subtag if it was already added
XMLTag subtag = getOptionalSubtagByName(SsmlXML.SORT_STATE);
if (subtag != null)
{
removeSubtag(subtag);
}
}
return this;
}
//###########################################################################
// PROTECTED METHODS
//###########################################################################
//---------------------------------------------------------------------------
/**
Called by the table when the user calls setName().
*/
protected void renameTable(String inOldName, String inNewName)
{
if (! mTableParts.containsKey(inOldName))
{
throw new ProgrammingException("No table was found in this sheet with name " + StringUtil.singleQuote(inOldName) + "!");
}
else if (mTableParts.containsKey(inNewName))
{
throw new InvalidValueException("A table with name " + StringUtil.singleQuote(inNewName) + " already exists in this worksheet!");
}
TablePart tablePart = mTableParts.remove(inOldName);
mTableParts.put(inNewName, tablePart);
}
//###########################################################################
// PRIVATE METHODS
//###########################################################################
//---------------------------------------------------------------------------
private void addTablePart(TablePart inValue)
{
if (inValue != null)
{
SsmlTable table = inValue.getRootNode();
// Ensure that the table has a unique name
if (! StringUtil.isSet(table.getName()))
{
table.setName("Table" + (mTableParts.size() + 1));
}
if (mTableParts.containsKey(table.getName()))
{
throw new OfficeOpenXmlException("The sheet name " + StringUtil.singleQuote(table.getName()) + " must be unique!");
}
mTableParts.put(table.getName(), inValue);
String relationshipId = getParentWorksheetPart().getWorksheetRelationshipPart().addTable(inValue);
XMLTag tablePartTag = new XMLTag(SsmlXML.TABLE_PART);
tablePartTag.setAttribute(RelationshipXML.ID_ATT, relationshipId);
mTablePartsTag.addSubtag(tablePartTag);
mTablePartsTag.setAttribute(SsmlXML.COUNT_ATT, mTableParts.size());
}
}
//---------------------------------------------------------------------------
private void addDrawingPart(SsmlDrawingPart inValue)
{
if (inValue != null)
{
String relationshipId = getParentWorksheetPart().getWorksheetRelationshipPart().addDrawing(inValue);
XMLTag drawingTag = new XMLTag(SsmlXML.DRAWING);
drawingTag.setAttribute(RelationshipXML.ID_ATT, relationshipId);
addSubtag(drawingTag);
}
}
//---------------------------------------------------------------------------
// Called before writing the sheet to XML.
private void finalizeSheet()
{
setDimension();
if (null == mSheetViewsTag)
{
addSheetView();
}
}
//---------------------------------------------------------------------------
// Adds (or adjusts) a dimension tag (ex: rowTags = getSheetData().getSubtagsByName(SsmlXML.ROW);
if (CollectionUtil.hasValues(rowTags))
{
Integer minRow = null;
Integer maxRow = null;
Integer minCol = null;
Integer maxCol = null;
for (XMLTag rowTag : rowTags)
{
List cellTags = rowTag.getSubtagsByName(SsmlXML.CELL);
if (CollectionUtil.hasValues(cellTags))
{
CellRef firstCell = new CellRef(cellTags.get(0).getAttributeValue(SsmlXML.REF_ATT));
if (null == minRow)
{
minRow = firstCell.getRowIndex();
}
maxRow = firstCell.getRowIndex();
if (null == minCol
|| firstCell.getColIndex() < minCol)
{
minCol = firstCell.getColIndex();
}
CellRef lastCell = new CellRef(cellTags.get(cellTags.size() - 1).getAttributeValue(SsmlXML.REF_ATT));
if (null == maxCol
|| lastCell.getColIndex() > maxCol)
{
maxCol = lastCell.getColIndex();
}
}
}
CellRef minCell = new CellRef().setRowIndex(minRow).setColIndex(minCol);
CellRef maxCell = new CellRef().setRowIndex(maxRow).setColIndex(maxCol);
/*
String minCell = "";
List cellTags = rowTags.get(0).getSubtagsByName(SsmlXML.CELL);
if (CollectionUtil.hasValues(cellTags))
{
minCell = cellTags.get(0).getAttributeValue(SsmlXML.REF_ATT);
}
String maxCell = "";
cellTags = rowTags.get(rowTags.size() - 1).getSubtagsByName(SsmlXML.CELL);
if (CollectionUtil.hasValues(cellTags))
{
maxCell = cellTags.get(cellTags.size() - 1).getAttributeValue(SsmlXML.REF_ATT);
}
*/
setDimension(new CellRange(minCell, maxCell));
}
}
//---------------------------------------------------------------------------
private XMLTag getMergeCellsTag()
{
if (null == mMergeCellsTag)
{
// Check if it has been added via addSubtag()...
mMergeCellsTag = getOptionalSubtagByName(SsmlXML.MERGE_CELLS);
if (null == mMergeCellsTag)
{
mMergeCellsTag = new XMLTag(SsmlXML.MERGE_CELLS);
addSubtag(mMergeCellsTag);
}
}
return mMergeCellsTag;
}
}