com.hfg.xml.msofficexml.xlsx.spreadsheetml.SsmlCell 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.time.LocalDate;
import java.time.ZoneId;
import java.time.temporal.ChronoUnit;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;
import com.hfg.datetime.DateUtil;
import com.hfg.util.BooleanUtil;
import com.hfg.util.StringUtil;
import com.hfg.util.collection.CollectionUtil;
import com.hfg.xml.XMLAttribute;
import com.hfg.xml.XMLTag;
import com.hfg.xml.XMLUtil;
import com.hfg.xml.msofficexml.xlsx.CellRange;
import com.hfg.xml.msofficexml.xlsx.CellRef;
import com.hfg.xml.msofficexml.xlsx.spreadsheetml.style.SsmlCellFormat;
//------------------------------------------------------------------------------
/**
Represents an Office Open XML worksheet data cell (<ssml:c>) 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 SsmlCell extends SsmlXMLTag
{
private SsmlWorksheet mParentWorksheet;
private SsmlCellFormat mCellFormat;
private CellRange mMergedCellRange;
private Object mValue;
private String mStringValue;
private XMLTag mValueTag;
private XMLTag mFormulaTag;
private static final LocalDate REFERENCE_DATE;
static
{
Calendar cal = new GregorianCalendar();
cal.set(1900, Calendar.JANUARY, 1);
REFERENCE_DATE = cal.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
}
//###########################################################################
// CONSTRUCTORS
//###########################################################################
//---------------------------------------------------------------------------
public SsmlCell(SsmlWorksheet inParentWorksheet)
{
super(SsmlXML.CELL, inParentWorksheet.getParentDoc());
mParentWorksheet = inParentWorksheet;
}
//---------------------------------------------------------------------------
public SsmlCell(SsmlWorksheet inParentWorksheet, XMLTag inXMLTag)
{
this(inParentWorksheet);
inXMLTag.verifyTagName(SsmlXML.CELL);
if (CollectionUtil.hasValues(inXMLTag.getAttributes()))
{
for (XMLAttribute attr : inXMLTag.getAttributes())
{
setAttribute(attr);
}
}
// Copy subtags over to this tag
if (CollectionUtil.hasValues(inXMLTag.getSubtags()))
{
for (XMLTag subtag : (List) (Object) inXMLTag.getSubtags())
{
addSubtag(subtag);
}
}
}
//###########################################################################
// PUBLIC METHODS
//###########################################################################
//---------------------------------------------------------------------------
public String toString()
{
Object value = getValue();
return (value != null ? value.toString() : null);
}
//---------------------------------------------------------------------------
public SsmlCellFormat getFormat()
{
if (null == mCellFormat)
{
// Check if it has been added via setAttribute()...
if (hasAttribute(SsmlXML.STYLE_IDX_ATT))
{
int cellFormatId = Integer.parseInt(getAttributeValue(SsmlXML.STYLE_IDX_ATT));
mCellFormat = getParentDoc().getStylesPart().getCellFormats().get(cellFormatId);
}
else
{
setFormat(new SsmlCellFormat(getParentDoc()));
}
}
return mCellFormat;
}
//---------------------------------------------------------------------------
public SsmlCell setFormat(SsmlCellFormat inValue)
{
mCellFormat = inValue;
if (inValue != null)
{
setAttribute(SsmlXML.STYLE_IDX_ATT, inValue.getIndex());
}
else
{
removeAttribute(SsmlXML.STYLE_IDX_ATT.getLocalName());
}
return this;
}
//---------------------------------------------------------------------------
public SsmlCell setRef(CellRef inValue)
{
if (inValue != null)
{
setAttribute(SsmlXML.REF_ATT, inValue);
}
else
{
removeAttribute(SsmlXML.REF_ATT.getLocalName());
}
return this;
}
//---------------------------------------------------------------------------
public CellRef getRef()
{
CellRef value = null;
String stringValue = getAttributeValue(SsmlXML.REF_ATT);
if (StringUtil.isSet(stringValue))
{
value = new CellRef(stringValue);
}
return value;
}
//---------------------------------------------------------------------------
public SsmlCell mergeDown(int inValue)
{
CellRef cellRef = getRef();
if (null == cellRef)
{
throw new RuntimeException("A CellRef must be specified for the cell before it can be merged!");
}
int endColIndex = cellRef.getColIndex();
if (mMergedCellRange != null)
{
mParentWorksheet.removeMergeCell(mMergedCellRange);
endColIndex = mMergedCellRange.getEndCell().getColIndex();
}
CellRef refForEndingCell = new CellRef().setColIndex(endColIndex).setRowIndex(cellRef.getRowIndex() + inValue);
mMergedCellRange = new CellRange(cellRef, refForEndingCell);
mParentWorksheet.mergeCells(mMergedCellRange);
// Ensure that the rest of the cells being merge have been created
ensureMergedCellsExist(mMergedCellRange);
return this;
}
//---------------------------------------------------------------------------
public SsmlCell mergeRight(int inValue)
{
CellRef cellRef = getRef();
if (null == cellRef)
{
throw new RuntimeException("A CellRef must be specified for the cell before it can be merged!");
}
int endRowIndex = cellRef.getRowIndex();
if (mMergedCellRange != null)
{
mParentWorksheet.removeMergeCell(mMergedCellRange);
endRowIndex = mMergedCellRange.getEndCell().getRowIndex();
}
CellRef refForEndingCell = new CellRef().setColIndex(cellRef.getColIndex() + inValue).setRowIndex(endRowIndex);
mMergedCellRange = new CellRange(cellRef, refForEndingCell);
mParentWorksheet.mergeCells(mMergedCellRange);
// Ensure that the rest of the cells being merge have been created
ensureMergedCellsExist(mMergedCellRange);
return this;
}
//---------------------------------------------------------------------------
public void addComment(XlsxComment inComment)
{
XlsxComment comment = inComment.clone().setCellRef(getRef());
mParentWorksheet.getCommentsPart().addComment(comment);
}
//---------------------------------------------------------------------------
@Override
public String getContent()
{
return mStringValue;
}
//---------------------------------------------------------------------------
@Override
public SsmlCell setContent(CharSequence inContent)
{
setValue(inContent);
return this;
}
//---------------------------------------------------------------------------
@Override
public SsmlCell addContent(CharSequence inContent)
{
return setValue((mStringValue != null ? mStringValue : "") + inContent.toString());
}
//---------------------------------------------------------------------------
public SsmlCellType getCellType()
{
String attrValueString = getAttributeValue(SsmlXML.CELL_DATA_TYPE_ATT);
return StringUtil.isSet(attrValueString) ? SsmlCellType.valueOf(attrValueString) : null;
}
/*
//---------------------------------------------------------------------------
public SsmlCell setValue(String inValue)
{
setAttribute(SsmlXML.CELL_DATA_TYPE_ATT, DataType.inlineStr);
XMLTag inlineStrTag = new XMLTag(SsmlXML.INLINE_STRING);
addSubtag(inlineStrTag);
XMLTag textTag = new XMLTag(SsmlXML.TEXT);
textTag.setContent(inValue);
inlineStrTag.addSubtag(textTag);
return this;
}
*/
//---------------------------------------------------------------------------
public SsmlCell setValue(Object inValue)
{
if (inValue != null)
{
if (inValue instanceof Integer)
{
setValue(((Integer)inValue).intValue());
}
else if (inValue instanceof Long)
{
setValue(((Long)inValue).longValue());
}
else if (inValue instanceof Float)
{
setValue(((Float)inValue).floatValue());
}
else if (inValue instanceof Double)
{
setValue(((Double)inValue).doubleValue());
}
else if (inValue instanceof Date)
{
// Store dates w/o a type and with a number value that is the number of days after Jan 1, 1900
LocalDate inputDate = ((Date)inValue).toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
long days = ChronoUnit.DAYS.between(REFERENCE_DATE, inputDate);
// For some obscure reason, Excel files built on a mac use 1904 as the reference date...
if (getParentDoc().getWorkbook().getProperties().get1904BasedDates())
{
days -= (365 * 4);
}
else
{
// Not exactly sure why this is necessary but it is
days += 2;
}
getValueTag().setContent(days + "");
// Remember the string value
mStringValue = DateUtil.getISO_8601_Date((Date)inValue);
// Make sure that the cell is formatted as a date
if (null == mCellFormat)
{
// inValue)
{
setAttribute(SsmlXML.CELL_DATA_TYPE_ATT, SsmlCellType.s);
int index = getParentDoc().getSharedStringsPart().defineString(inValue);
getValueTag().setContent(index + "");
// Remember the string value
StringBuilder xml = new StringBuilder();
for (SsmlTextRun run : inValue)
{
xml.append(run.toXML());
}
mStringValue = xml.toString();
return this;
}
//---------------------------------------------------------------------------
public SsmlCell setValue(String inValue)
{
if (inValue != null)
{
setAttribute(SsmlXML.CELL_DATA_TYPE_ATT, SsmlCellType.s);
int index = getParentDoc().getSharedStringsPart().defineString(inValue);
getValueTag().setContent(index + "");
// Remember the string value
mStringValue = inValue;
mValue = inValue;
}
else
{
removeAttribute(SsmlXML.CELL_DATA_TYPE_ATT);
if (mValueTag != null)
{
removeSubtag(mValueTag);
mValueTag = null;
mValue = null;
mStringValue = null;
}
}
return this;
}
//---------------------------------------------------------------------------
public SsmlCell setFormula(String inValue)
{
// Clear any value
removeAttribute(SsmlXML.CELL_DATA_TYPE_ATT);
if (mValueTag != null)
{
removeSubtag(mValueTag);
mValueTag = null;
mValue = null;
mStringValue = null;
}
// Set the formula
getFormulaTag().setContent(inValue);
return this;
}
//---------------------------------------------------------------------------
public SsmlCell setValue(int inValue)
{
return setPrimitiveNumberValue(inValue + "");
}
//---------------------------------------------------------------------------
public SsmlCell setValue(long inValue)
{
return setPrimitiveNumberValue(inValue + "");
}
//---------------------------------------------------------------------------
public SsmlCell setValue(float inValue)
{
return setPrimitiveNumberValue(inValue + "");
}
//---------------------------------------------------------------------------
public SsmlCell setValue(double inValue)
{
return setPrimitiveNumberValue(inValue + "");
}
//---------------------------------------------------------------------------
public Object getValue()
{
if (null == mValue)
{
XMLTag valueTag = getValueTag();
Object value = null;
if (StringUtil.isSet(valueTag.getContent()))
{
SsmlCellType cellType = getCellType();
if (null == cellType)
{
cellType = SsmlCellType.n;
}
switch (cellType)
{
case s: // shared string
value = XMLUtil.unescapeContent(getParentDoc().getSharedStringsPart().getString(Integer.parseInt(valueTag.getContent())));
break;
case str: // string stored in the value tag
value = valueTag.getUnescapedContent();
break;
case n: // number
try
{
Double doubleValue = Double.parseDouble(valueTag.getContent());
if (doubleValue.intValue() == doubleValue)
{
value = doubleValue.intValue();
}
else
{
value = doubleValue;
}
}
catch (Exception e)
{
value = valueTag.getUnescapedContent();
}
// Is it a date in disguise?
if (getFormat() != null
&& value instanceof Integer
&& getFormat().getNumberFormat() != null
&& StringUtil.isSet(getFormat().getNumberFormat().getFormatCode())
&& getFormat().getNumberFormat().getFormatCode().toLowerCase().indexOf("y") >= 0)
{
// Dates are stored as a number value that is the number of days after Jan 1, 1900
value = getDateFromDays((Integer) value);
}
break;
case b:
value = BooleanUtil.valueOf(valueTag.getUnescapedContent());
break;
default:
System.err.println("Support for cell type " + cellType + " not yet added! Returning null.");
// TODO
}
mValue = value;
}
}
return mValue;
}
//---------------------------------------------------------------------------
private Date getDateFromDays(int inDays)
{
Date date = null;
try
{
// For some obscure reason, Excel files built on a mac use 1904 as the reference date...
if (getParentDoc().getWorkbook().getProperties().get1904BasedDates())
{
inDays += (365 * 4);
}
else
{
// Not exactly sure why this is necessary but it is
inDays -= 2;
}
LocalDate localDate = REFERENCE_DATE.plus(inDays, ChronoUnit.DAYS);
date = Date.from(localDate.atStartOfDay(ZoneId.systemDefault()).toInstant());
}
catch (Exception e)
{
// Igore
}
return date;
}
//---------------------------------------------------------------------------
private XMLTag getValueTag()
{
if (null == mValueTag)
{
// Check if it has been added via addSubtag()...
mValueTag = getOptionalSubtagByName(SsmlXML.VALUE);
if (null == mValueTag)
{
mValueTag = new XMLTag(SsmlXML.VALUE);
addSubtag(mValueTag);
}
}
return mValueTag;
}
//---------------------------------------------------------------------------
private XMLTag getFormulaTag()
{
if (null == mFormulaTag)
{
// Check if it has been added via addSubtag()...
mFormulaTag = getOptionalSubtagByName(SsmlXML.CELL_FORMULA);
if (null == mFormulaTag)
{
mFormulaTag = new XMLTag(SsmlXML.CELL_FORMULA);
addSubtag(mFormulaTag);
}
}
return mFormulaTag;
}
//---------------------------------------------------------------------------
private SsmlCell setPrimitiveNumberValue(String inStringValue)
{
setAttribute(SsmlXML.CELL_DATA_TYPE_ATT, SsmlCellType.n);
getValueTag().setContent(inStringValue);
// Remember the string value
mStringValue = inStringValue;
return this;
}
//---------------------------------------------------------------------------
private void ensureMergedCellsExist(CellRange inCellRange)
{
SsmlSheetData sheetData = (SsmlSheetData) getParentNode().getParentNode();
int startingRowIndex = inCellRange.getBeginCell().getRowIndex();
int endingRowIndex = inCellRange.getEndCell().getRowIndex();
for (int i = startingRowIndex; i <= endingRowIndex; i++)
{
SsmlRow row = sheetData.getRowByRowIndex(i);
if (null == row)
{
row = sheetData.addRow(i);
}
for (int colIndx = inCellRange.getBeginCell().getColIndex(); colIndx <= inCellRange.getEndCell().getColIndex(); colIndx++)
{
SsmlCell cell = row.getCellByColIndex(colIndx);
if (null == cell)
{
row.addCellByColIndex(colIndx);
}
}
}
}
}