com.hfg.xml.msofficexml.xlsx.Xlsx 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;
import java.io.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.zip.ZipEntry;
import java.util.zip.ZipInputStream;
import com.hfg.datetime.DateUtil;
import com.hfg.util.StringUtil;
import com.hfg.util.collection.CollectionUtil;
import com.hfg.util.collection.OrderedMap;
import com.hfg.util.io.NoCloseBufferedInputStream;
import com.hfg.util.io.RuntimeIOException;
import com.hfg.util.io.StreamUtil;
import com.hfg.xml.XMLDoc;
import com.hfg.xml.XMLNode;
import com.hfg.xml.XMLTag;
import com.hfg.xml.msofficexml.OfficeOpenXmlDocument;
import com.hfg.xml.msofficexml.OfficeOpenXmlException;
import com.hfg.xml.msofficexml.OfficeXML;
import com.hfg.xml.msofficexml.RelationshipType;
import com.hfg.xml.msofficexml.docx.RelationshipXML;
import com.hfg.xml.msofficexml.part.DocumentPropertiesPart;
import com.hfg.xml.msofficexml.xlsx.part.*;
import com.hfg.xml.msofficexml.xlsx.spreadsheetml.style.StylesPart;
import com.hfg.xml.msofficexml.xlsx.spreadsheetml.SsmlWorkbook;
import com.hfg.xml.msofficexml.xlsx.spreadsheetml.SsmlWorksheet;
import com.hfg.xml.msofficexml.xlsx.spreadsheetml.SsmlXML;
//------------------------------------------------------------------------------
/**
Office Open XML format Excel document.
@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]
//------------------------------------------------------------------------------
// For various specifications and limits see:
// https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3
public class Xlsx extends OfficeOpenXmlDocument
{
private XlsxConfig mConfig = new XlsxConfig();
private Workbook mWorkbook;
private WorkbookPart mWorkbookPart;
private WorkbookRelationshipPart mWorkbookRelationshipPart;
private StylesPart mStylesPart;
private SharedStringsPart mSharedStringsPart;
private int mDrawingIndex = 1;
private static String sDefaultName = "Untitled.xlsx";
public static Level sSQLLoggingLevel = Level.FINE;
private final static Logger LOGGER = Logger.getLogger(OfficeXML.class.getPackage().getName());
//##########################################################################
// CONSTRUCTORS
//##########################################################################
//---------------------------------------------------------------------------
public Xlsx()
{
super();
init();
}
//---------------------------------------------------------------------------
// TODO: Work in progress
public Xlsx(File inFile)
{
this();
if (! inFile.exists())
{
throw new RuntimeIOException("The specified .xlsx file "
+ StringUtil.singleQuote(inFile.getPath()) + " doesn't exist!");
}
else if (! inFile.canRead())
{
throw new RuntimeIOException("No read permissions for the specified .xlsx file "
+ StringUtil.singleQuote(inFile.getPath()) + "!");
}
try
{
readFromStream(new FileInputStream(inFile));
}
catch (Exception e)
{
throw new RuntimeIOException(e);
}
}
//---------------------------------------------------------------------------
public Xlsx(InputStream inFileStream)
{
this();
readFromStream(inFileStream);
}
//---------------------------------------------------------------------------
private void init()
{
// Setup essential relationships
getPackageRelationshipPart().addRelationship(RelationshipType.OFFICE_DOCUMENT, SsmlXML.WORKBOOK_FILE);
/*
getContentTypesPart().addOverride(new OfficeXMLPart(this).setFile(new File("/docProps/core.xml")), OfficeOpenXMLContentType.CORE_PROPERTIES);
getContentTypesPart().addOverride(new OfficeXMLPart(this).setFile(new File("/docProps/app.xml")), OfficeOpenXMLContentType.EXTENDED_PROPERTIES);
getContentTypesPart().addOverride(new OfficeXMLPart(this).setFile(new File("/xl/workbook.xml")), SsmlContentType.WORKBOOK);
getContentTypesPart().addOverride(new OfficeXMLPart(this).setFile(new File("/xl/styles.xml")), SsmlContentType.SPREADSHEET_STYLES);
getContentTypesPart().addOverride(new OfficeXMLPart(this).setFile(new File("/xl/sharedStrings.xml")), SsmlContentType.SPREADSHEET_SHARED_STRINGS);
*/
mWorkbookPart = new WorkbookPart(this);
// addPart(mWorkbookPart);
}
//##########################################################################
// PUBLIC METHODS
//##########################################################################
//---------------------------------------------------------------------------
public Xlsx setConfig(XlsxConfig inValue)
{
if (null == mConfig)
{
throw new OfficeOpenXmlException("The config object cannot be null!");
}
mConfig = inValue;
return this;
}
//---------------------------------------------------------------------------
public XlsxConfig getConfig()
{
return mConfig;
}
//---------------------------------------------------------------------------
public static String getDefaultName()
{
return sDefaultName;
}
//---------------------------------------------------------------------------
@Override
public String name()
{
String name = super.name();
if (! StringUtil.isSet(name))
{
name = getDefaultName();
setName(name);
}
return name;
}
//---------------------------------------------------------------------------
public WorkbookRelationshipPart getWorkbookRelationshipPart()
{
if (null == mWorkbookRelationshipPart)
{
mWorkbookRelationshipPart = new WorkbookRelationshipPart(this);
}
return mWorkbookRelationshipPart;
}
//---------------------------------------------------------------------------
public WorkbookPart getWorkbookPart()
{
if (null == mWorkbookPart)
{
mWorkbookPart = new WorkbookPart(this);
}
return mWorkbookPart;
}
//---------------------------------------------------------------------------
public StylesPart getStylesPart()
{
if (null == mStylesPart)
{
mStylesPart = new StylesPart(this);
mStylesPart.setDefaults();
}
return mStylesPart;
}
//---------------------------------------------------------------------------
public SharedStringsPart getSharedStringsPart()
{
if (null == mSharedStringsPart)
{
mSharedStringsPart = new SharedStringsPart(this);
}
return mSharedStringsPart;
}
//---------------------------------------------------------------------------
public Workbook getWorkbook()
{
if (null == mWorkbook)
{
mWorkbook = new Workbook(this);
}
return mWorkbook;
}
//---------------------------------------------------------------------------
public void addDrawingPart(SsmlDrawingPart inValue)
{
// The part has already been added, we just need to assign a drawing index.
inValue.setDrawingIndex(mDrawingIndex++);
}
//---------------------------------------------------------------------------
@Override
public void write(OutputStream inStream)
throws IOException
{
finalizeWorkbook();
super.write(inStream);
}
//##########################################################################
// PRIVATE METHODS
//##########################################################################
//---------------------------------------------------------------------------
// This method is just parsing the basics at the moment.
private void parse(ZipInputStream inStream)
throws IOException
{
ZipEntry zipEntry;
// This is to prevent the closes from the file processing from closing the main zip stream
NoCloseBufferedInputStream noCloseStream = new NoCloseBufferedInputStream(inStream);
XMLTag workbookTag = null;
XMLTag workbookRelationshipsTag = null;
Map sheetMap = new HashMap<>(25);
while ((zipEntry = inStream.getNextEntry()) != null)
{
File zipFile = new File(zipEntry.getName()); // Create a File object to compare paths ignoring separator differences
if (LOGGER.isLoggable(Level.FINE))
{
LOGGER.log(Level.FINE, String.format("Parsing %s...", zipEntry.getName()));
}
long startTime = System.currentTimeMillis();
try
{
if (zipFile.equals(SsmlXML.WORKBOOK_FILE))
{
XMLDoc doc = new XMLDoc(noCloseStream);
workbookTag = (XMLTag) doc.getRootNode();
mWorkbookPart.setRootNode(new SsmlWorkbook(this, workbookTag));
}
else if (zipFile.equals(SsmlXML.WORKBOOK_RELATIONSHIP_FILE))
{
XMLDoc doc = new XMLDoc(noCloseStream);
workbookRelationshipsTag = (XMLTag) doc.getRootNode();
}
else if (SsmlXML.WORKSHEETS_DIR.equals(zipFile.getParentFile()))
{
sheetMap.put(new File(zipEntry.getName()), (XMLTag) new XMLDoc(noCloseStream).getRootNode());
}
else if (zipFile.equals(SsmlXML.SHARED_STRINGS_FILE))
{
mSharedStringsPart = new SharedStringsPart(this, noCloseStream);
}
else if (zipFile.equals(SsmlXML.STYLES_FILE))
{
mStylesPart = new StylesPart(this, noCloseStream);
}
else if (zipFile.equals(OfficeXML.CORE_PROPERTIES_FILE))
{
parseDocumentProperties(this, noCloseStream);
}
else if ( SsmlXML.THEME_DIR.equals(zipFile.getParentFile()))
{
new SsmlThemePart(this, noCloseStream);
}
}
catch (Exception e)
{
throw new RuntimeIOException("Problem parsing " + zipEntry.getName() + "!", e);
}
inStream.closeEntry();
if (LOGGER.isLoggable(Level.FINE))
{
LOGGER.log(Level.FINE, DateUtil.generateElapsedTimeString(startTime));
}
}
// Add the sheets to the workbook (in the proper order)
OrderedMap sheetNameMap = buildSheetNameMap(workbookTag, workbookRelationshipsTag);
for (File sheetFile : sheetNameMap.keySet())
{
getWorkbook().addWorksheet(sheetNameMap.get(sheetFile), null, sheetMap.get(sheetFile));
}
}
//---------------------------------------------------------------------------
private OrderedMap buildSheetNameMap(XMLTag inWorkbookTag, XMLTag inWorkbookRelationshipsTag)
{
OrderedMap sheetNameMap = new OrderedMap<>(25);
XMLTag sheetsTag = inWorkbookTag.getRequiredSubtagByName(SsmlXML.SHEETS);
for (XMLNode sheetTag : sheetsTag.getSubtagsByName(SsmlXML.SHEET))
{
String relationshipRef = sheetTag.getAttributeValue(RelationshipXML.ID_ATT);
for (XMLNode relationshipTag : inWorkbookRelationshipsTag.getSubtagsByName(OfficeXML.RELATIONSHIP))
{
if (relationshipTag.getAttributeValue(OfficeXML.ID_ATT).equals(relationshipRef))
{
File file = new File(SsmlXML.XL_DIR, relationshipTag.getAttributeValue(OfficeXML.TARGET_ATT));
sheetNameMap.put(file, sheetTag.getAttributeValue(SsmlXML.NAME_ATT));
break;
}
}
}
return sheetNameMap;
}
//---------------------------------------------------------------------------
private void finalizeWorkbook()
{
long startTime = System.currentTimeMillis();
// Ensure that any changes to sheet names are saved
List worksheets = getWorkbook().getWorksheets();
if (CollectionUtil.hasValues(worksheets))
{
List sheetTags = getWorkbookPart().getRootNode().getOptionalSubtagByName(SsmlXML.SHEETS).getSubtags();
for (SsmlWorksheet worksheet : worksheets)
{
worksheet.getPageMargins(); // Also ensure that margins are set
for (XMLTag sheetTag : sheetTags)
{
if (Integer.parseInt(sheetTag.getAttributeValue(SsmlXML.SHEET_ID_ATT)) == worksheet.getParentWorksheetPart().getSheetIndex())
{
sheetTag.setAttribute(SsmlXML.NAME_ATT, worksheet.getName());
break;
}
}
}
}
if (getConfig().getFeature(XlsxConfig.Feature.CONDENSE_STYLES))
{
getStylesPart().finalizeStyles();
}
if (LOGGER.isLoggable(Level.INFO))
{
LOGGER.log(sSQLLoggingLevel, "finalizeWorkbook() timing: " + DateUtil.generateElapsedTimeString(startTime));
}
}
//---------------------------------------------------------------------------
private void readFromStream(InputStream inFileStream)
{
if (null == inFileStream)
{
throw new RuntimeIOException("The specified stream is null!");
}
if (! (inFileStream instanceof BufferedInputStream))
{
inFileStream = new BufferedInputStream(inFileStream);
}
ZipInputStream zipStream = null;
try
{
zipStream = new ZipInputStream(inFileStream);
parse(zipStream);
}
catch (Exception e)
{
throw new RuntimeIOException(e);
}
finally
{
StreamUtil.close(zipStream);
}
}
}