001package com.hfg.xml.msofficexml.xlsx;
002
003
004import java.io.*;
005import java.util.HashMap;
006import java.util.List;
007import java.util.Map;
008import java.util.logging.Level;
009import java.util.logging.Logger;
010import java.util.zip.ZipEntry;
011import java.util.zip.ZipInputStream;
012
013import com.hfg.datetime.DateUtil;
014import com.hfg.util.StringUtil;
015import com.hfg.util.collection.CollectionUtil;
016import com.hfg.util.collection.OrderedMap;
017import com.hfg.util.io.NoCloseBufferedInputStream;
018import com.hfg.util.io.RuntimeIOException;
019import com.hfg.util.io.StreamUtil;
020import com.hfg.xml.XMLDoc;
021import com.hfg.xml.XMLNode;
022import com.hfg.xml.XMLTag;
023import com.hfg.xml.msofficexml.OfficeOpenXmlDocument;
024import com.hfg.xml.msofficexml.OfficeOpenXmlException;
025import com.hfg.xml.msofficexml.OfficeXML;
026import com.hfg.xml.msofficexml.RelationshipType;
027import com.hfg.xml.msofficexml.docx.RelationshipXML;
028import com.hfg.xml.msofficexml.xlsx.part.SharedStringsPart;
029import com.hfg.xml.msofficexml.xlsx.part.SsmlDrawingPart;
030import com.hfg.xml.msofficexml.xlsx.spreadsheetml.style.StylesPart;
031import com.hfg.xml.msofficexml.xlsx.part.WorkbookPart;
032import com.hfg.xml.msofficexml.xlsx.part.WorkbookRelationshipPart;
033import com.hfg.xml.msofficexml.xlsx.spreadsheetml.SsmlWorkbook;
034import com.hfg.xml.msofficexml.xlsx.spreadsheetml.SsmlWorksheet;
035import com.hfg.xml.msofficexml.xlsx.spreadsheetml.SsmlXML;
036
037//------------------------------------------------------------------------------
038/**
039 Office Open XML format Excel document.
040
041 @author J. Alex Taylor, hairyfatguy.com
042 */
043//------------------------------------------------------------------------------
044// com.hfg XML/HTML Coding Library
045//
046// This library is free software; you can redistribute it and/or
047// modify it under the terms of the GNU Lesser General Public
048// License as published by the Free Software Foundation; either
049// version 2.1 of the License, or (at your option) any later version.
050//
051// This library is distributed in the hope that it will be useful,
052// but WITHOUT ANY WARRANTY; without even the implied warranty of
053// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
054// Lesser General Public License for more details.
055//
056// You should have received a copy of the GNU Lesser General Public
057// License along with this library; if not, write to the Free Software
058// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
059//
060// J. Alex Taylor, President, Founder, CEO, COO, CFO, OOPS hairyfatguy.com
061// jataylor@hairyfatguy.com
062//------------------------------------------------------------------------------
063// For various specifications and limits see:
064//      https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3
065
066public class Xlsx  extends OfficeOpenXmlDocument
067{
068   private XlsxConfig               mConfig = new XlsxConfig();
069
070   private Workbook                 mWorkbook;
071   private WorkbookPart             mWorkbookPart;
072   private WorkbookRelationshipPart mWorkbookRelationshipPart;
073   private StylesPart               mStylesPart;
074   private SharedStringsPart        mSharedStringsPart;
075
076   private int mDrawingIndex = 1;
077
078   private static String sDefaultName = "Untitled.xlsx";
079
080   private final static Logger LOGGER = Logger.getLogger(OfficeXML.class.getPackage().getName());
081
082   //##########################################################################
083   // CONSTRUCTORS
084   //##########################################################################
085
086   //---------------------------------------------------------------------------
087   public Xlsx()
088   {
089      super();
090
091      init();
092   }
093
094   //---------------------------------------------------------------------------
095   // TODO: Work in progress
096   public Xlsx(File inFile)
097   {
098      if (! inFile.exists())
099      {
100         throw new RuntimeIOException("The specified .xlsx file "
101               + StringUtil.singleQuote(inFile.getPath()) + " doesn't exist!");
102      }
103      else if (! inFile.canRead())
104      {
105         throw new RuntimeIOException("No read permissions for the specified .xlsx file "
106               + StringUtil.singleQuote(inFile.getPath()) + "!");
107      }
108
109      try
110      {
111         readFromStream(new FileInputStream(inFile));
112      }
113      catch (Exception e)
114      {
115         throw new RuntimeIOException(e);
116      }
117   }
118
119   //---------------------------------------------------------------------------
120   public Xlsx(InputStream inFileStream)
121   {
122      readFromStream(inFileStream);
123   }
124
125   //---------------------------------------------------------------------------
126   private void init()
127   {
128      // Setup essential relationships
129      getPackageRelationshipPart().addRelationship(RelationshipType.OFFICE_DOCUMENT, SsmlXML.WORKBOOK_FILE);
130/*
131      getContentTypesPart().addOverride(new OfficeXMLPart(this).setFile(new File("/docProps/core.xml")),    OfficeOpenXMLContentType.CORE_PROPERTIES);
132      getContentTypesPart().addOverride(new OfficeXMLPart(this).setFile(new File("/docProps/app.xml")),     OfficeOpenXMLContentType.EXTENDED_PROPERTIES);
133      getContentTypesPart().addOverride(new OfficeXMLPart(this).setFile(new File("/xl/workbook.xml")),      SsmlContentType.WORKBOOK);
134      getContentTypesPart().addOverride(new OfficeXMLPart(this).setFile(new File("/xl/styles.xml")),        SsmlContentType.SPREADSHEET_STYLES);
135      getContentTypesPart().addOverride(new OfficeXMLPart(this).setFile(new File("/xl/sharedStrings.xml")), SsmlContentType.SPREADSHEET_SHARED_STRINGS);
136*/
137      mWorkbookPart = new WorkbookPart(this);
138//      addPart(mWorkbookPart);
139   }
140
141   //##########################################################################
142   // PUBLIC METHODS
143   //##########################################################################
144
145   //---------------------------------------------------------------------------
146   public Xlsx setConfig(XlsxConfig inValue)
147   {
148      if (null == mConfig)
149      {
150         throw new OfficeOpenXmlException("The config object cannot be null!");
151      }
152
153      mConfig = inValue;
154      return this;
155   }
156
157   //---------------------------------------------------------------------------
158   public XlsxConfig getConfig()
159   {
160      return mConfig;
161   }
162
163   //---------------------------------------------------------------------------
164   public static String getDefaultName()
165   {
166      return sDefaultName;
167   }
168
169   //---------------------------------------------------------------------------
170   @Override
171   public String name()
172   {
173      String name = super.name();
174      if (! StringUtil.isSet(name))
175      {
176         name = getDefaultName();
177         setName(name);
178      }
179
180      return name;
181   }
182
183   //---------------------------------------------------------------------------
184   public WorkbookRelationshipPart getWorkbookRelationshipPart()
185   {
186      if (null == mWorkbookRelationshipPart)
187      {
188         mWorkbookRelationshipPart = new WorkbookRelationshipPart(this);
189      }
190
191      return mWorkbookRelationshipPart;
192   }
193
194   //---------------------------------------------------------------------------
195   public WorkbookPart getWorkbookPart()
196   {
197      if (null == mWorkbookPart)
198      {
199         mWorkbookPart = new WorkbookPart(this);
200      }
201
202      return mWorkbookPart;
203   }
204
205   //---------------------------------------------------------------------------
206   public StylesPart getStylesPart()
207   {
208      if (null == mStylesPart)
209      {
210         mStylesPart = new StylesPart(this);
211         mStylesPart.setDefaults();
212      }
213
214      return mStylesPart;
215   }
216
217   //---------------------------------------------------------------------------
218   public SharedStringsPart getSharedStringsPart()
219   {
220      if (null == mSharedStringsPart)
221      {
222         mSharedStringsPart = new SharedStringsPart(this);
223      }
224
225      return mSharedStringsPart;
226   }
227
228   //---------------------------------------------------------------------------
229   public Workbook getWorkbook()
230   {
231      if (null == mWorkbook)
232      {
233         mWorkbook = new Workbook(this);
234      }
235
236      return mWorkbook;
237   }
238
239   //---------------------------------------------------------------------------
240   public void addDrawingPart(SsmlDrawingPart inValue)
241   {
242      // The part has already been added, we just need to assign a drawing index.
243      inValue.setDrawingIndex(mDrawingIndex++);
244   }
245
246   //---------------------------------------------------------------------------
247   @Override
248   public void write(OutputStream inStream)
249         throws IOException
250   {
251      finalizeWorkbook();
252      super.write(inStream);
253   }
254
255   //##########################################################################
256   // PRIVATE METHODS
257   //##########################################################################
258
259   //---------------------------------------------------------------------------
260   // This method is just parsing the basics at the moment.
261   private void parse(ZipInputStream inStream)
262      throws IOException
263   {
264      mWorkbookPart = new WorkbookPart(this);
265
266      ZipEntry zipEntry;
267
268      // This is to prevent the closes from the file processing from closing the main zip stream
269      NoCloseBufferedInputStream noCloseStream = new NoCloseBufferedInputStream(inStream);
270
271      XMLTag workbookTag = null;
272      XMLTag workbookRelationshipsTag = null;
273
274      Map<File, XMLTag> sheetMap = new HashMap<>(25);
275
276      while ((zipEntry = inStream.getNextEntry()) != null)
277      {
278         File zipFile = new File(zipEntry.getName()); // Create a File object to compare paths ignoring separator differences
279         try
280         {
281            if (zipFile.equals(SsmlXML.WORKBOOK_FILE))
282            {
283               XMLDoc doc = new XMLDoc(noCloseStream);
284               workbookTag = (XMLTag) doc.getRootNode();
285               mWorkbookPart.setRootNode(new SsmlWorkbook(this, workbookTag));
286            }
287            else if (zipFile.equals(SsmlXML.WORKBOOK_RELATIONSHIP_FILE))
288            {
289               XMLDoc doc = new XMLDoc(noCloseStream);
290               workbookRelationshipsTag = (XMLTag) doc.getRootNode();
291            }
292            else if (SsmlXML.WORKSHEETS_DIR.equals(zipFile.getParentFile()))
293            {
294               sheetMap.put(new File(zipEntry.getName()), (XMLTag) new XMLDoc(noCloseStream).getRootNode());
295            }
296            else if (zipFile.equals(SsmlXML.SHARED_STRINGS_FILE))
297            {
298               mSharedStringsPart = new SharedStringsPart(this, noCloseStream);
299            }
300            else if (zipFile.equals(SsmlXML.STYLES_FILE))
301            {
302               mStylesPart = new StylesPart(this, noCloseStream);
303            }
304         }
305         catch (Exception e)
306         {
307            throw new RuntimeIOException("Problem parsing " + zipEntry.getName() + "!", e);
308         }
309
310         inStream.closeEntry();
311      }
312
313      // Add the sheets to the workbook (in the proper order)
314      OrderedMap<File, String> sheetNameMap = buildSheetNameMap(workbookTag, workbookRelationshipsTag);
315      for (File sheetFile : sheetNameMap.keySet())
316      {
317         getWorkbook().addWorksheet(sheetNameMap.get(sheetFile), null, sheetMap.get(sheetFile));
318      }
319
320   }
321
322   //---------------------------------------------------------------------------
323   private OrderedMap<File, String> buildSheetNameMap(XMLTag inWorkbookTag, XMLTag inWorkbookRelationshipsTag)
324   {
325      OrderedMap<File, String> sheetNameMap = new OrderedMap<>(25);
326
327      XMLTag sheetsTag = inWorkbookTag.getRequiredSubtagByName(SsmlXML.SHEETS);
328      for (XMLNode sheetTag : sheetsTag.getSubtagsByName(SsmlXML.SHEET))
329      {
330         String relationshipRef = sheetTag.getAttributeValue(RelationshipXML.ID_ATT);
331
332         for (XMLNode relationshipTag : inWorkbookRelationshipsTag.getSubtagsByName(OfficeXML.RELATIONSHIP))
333         {
334            if (relationshipTag.getAttributeValue(OfficeXML.ID_ATT).equals(relationshipRef))
335            {
336               File file = new File(SsmlXML.XL_DIR, relationshipTag.getAttributeValue(OfficeXML.TARGET_ATT));
337               sheetNameMap.put(file, sheetTag.getAttributeValue(SsmlXML.NAME_ATT));
338               break;
339            }
340         }
341      }
342
343      return sheetNameMap;
344   }
345
346
347   //---------------------------------------------------------------------------
348   private void finalizeWorkbook()
349   {
350      long startTime = System.currentTimeMillis();
351
352      // Ensure that any changes to sheet names are saved
353      List<SsmlWorksheet> worksheets = getWorkbook().getWorksheets();
354      if (CollectionUtil.hasValues(worksheets))
355      {
356         List<XMLTag> sheetTags = getWorkbookPart().getRootNode().getOptionalSubtagByName(SsmlXML.SHEETS).getSubtags();
357
358         for (SsmlWorksheet worksheet : worksheets)
359         {
360            for (XMLTag sheetTag : sheetTags)
361            {
362               if (Integer.parseInt(sheetTag.getAttributeValue(SsmlXML.SHEET_ID_ATT)) == worksheet.getParentWorksheetPart().getSheetIndex())
363               {
364                  sheetTag.setAttribute(SsmlXML.NAME_ATT, worksheet.getName());
365                  break;
366               }
367            }
368         }
369      }
370
371      if (getConfig().getFeature(XlsxConfig.Feature.CONDENSE_STYLES))
372      {
373         getStylesPart().finalizeStyles();
374      }
375
376      if (LOGGER.isLoggable(Level.INFO))
377      {
378         LOGGER.log(sSQLLoggingLevel, "finalizeWorkbook() timing: " + DateUtil.generateElapsedTimeString(startTime));
379      }
380   }
381
382   //---------------------------------------------------------------------------
383   private void readFromStream(InputStream inFileStream)
384   {
385      ZipInputStream zipStream = null;
386      try
387      {
388         zipStream = new ZipInputStream(inFileStream);
389         parse(zipStream);
390      }
391      catch (Exception e)
392      {
393         throw new RuntimeIOException(e);
394      }
395      finally
396      {
397         StreamUtil.close(zipStream);
398      }
399   }
400
401}