| /* ==================================================================== |
| Licensed to the Apache Software Foundation (ASF) under one or more |
| contributor license agreements. See the NOTICE file distributed with |
| this work for additional information regarding copyright ownership. |
| The ASF licenses this file to You under the Apache License, Version 2.0 |
| (the "License"); you may not use this file except in compliance with |
| the License. You may obtain a copy of the License at |
| |
| http://www.apache.org/licenses/LICENSE-2.0 |
| |
| Unless required by applicable law or agreed to in writing, software |
| distributed under the License is distributed on an "AS IS" BASIS, |
| WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| See the License for the specific language governing permissions and |
| limitations under the License. |
| ==================================================================== */ |
| package org.apache.poi.examples.xssf.eventusermodel; |
| |
| import java.io.InputStream; |
| import java.util.Iterator; |
| import java.util.LinkedHashMap; |
| import java.util.Map; |
| |
| import javax.xml.parsers.ParserConfigurationException; |
| |
| import org.apache.poi.openxml4j.opc.OPCPackage; |
| import org.apache.poi.openxml4j.opc.PackageAccess; |
| import org.apache.poi.util.XMLHelper; |
| import org.apache.poi.xssf.eventusermodel.XSSFReader; |
| import org.apache.poi.xssf.model.SharedStringsTable; |
| import org.xml.sax.Attributes; |
| import org.xml.sax.ContentHandler; |
| import org.xml.sax.InputSource; |
| import org.xml.sax.SAXException; |
| import org.xml.sax.XMLReader; |
| import org.xml.sax.helpers.DefaultHandler; |
| |
| /** |
| * XSSF and SAX (Event API) basic example. |
| * See {@link XLSX2CSV} for a fuller example of doing |
| * XSLX processing with the XSSF Event code. |
| */ |
| @SuppressWarnings({"java:S106","java:S4823","java:S1192"}) |
| public class FromHowTo { |
| public void processFirstSheet(String filename) throws Exception { |
| try (OPCPackage pkg = OPCPackage.open(filename, PackageAccess.READ)) { |
| XSSFReader r = new XSSFReader(pkg); |
| SharedStringsTable sst = r.getSharedStringsTable(); |
| |
| XMLReader parser = fetchSheetParser(sst); |
| |
| // process the first sheet |
| try (InputStream sheet = r.getSheetsData().next()) { |
| InputSource sheetSource = new InputSource(sheet); |
| parser.parse(sheetSource); |
| } |
| } |
| } |
| |
| public void processAllSheets(String filename) throws Exception { |
| try (OPCPackage pkg = OPCPackage.open(filename, PackageAccess.READ)) { |
| XSSFReader r = new XSSFReader(pkg); |
| SharedStringsTable sst = r.getSharedStringsTable(); |
| |
| XMLReader parser = fetchSheetParser(sst); |
| |
| Iterator<InputStream> sheets = r.getSheetsData(); |
| while (sheets.hasNext()) { |
| System.out.println("Processing new sheet:\n"); |
| try (InputStream sheet = sheets.next()) { |
| InputSource sheetSource = new InputSource(sheet); |
| parser.parse(sheetSource); |
| } |
| System.out.println(); |
| } |
| } |
| } |
| |
| public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException, ParserConfigurationException { |
| XMLReader parser = XMLHelper.newXMLReader(); |
| ContentHandler handler = new SheetHandler(sst); |
| parser.setContentHandler(handler); |
| return parser; |
| } |
| |
| /** |
| * See org.xml.sax.helpers.DefaultHandler javadocs |
| */ |
| private static class SheetHandler extends DefaultHandler { |
| private final SharedStringsTable sst; |
| private String lastContents; |
| private boolean nextIsString; |
| private boolean inlineStr; |
| private final LruCache<Integer,String> lruCache = new LruCache<>(50); |
| |
| private static class LruCache<A,B> extends LinkedHashMap<A, B> { |
| private final int maxEntries; |
| |
| public LruCache(final int maxEntries) { |
| super(maxEntries + 1, 1.0f, true); |
| this.maxEntries = maxEntries; |
| } |
| |
| @Override |
| protected boolean removeEldestEntry(final Map.Entry<A, B> eldest) { |
| return super.size() > maxEntries; |
| } |
| } |
| |
| private SheetHandler(SharedStringsTable sst) { |
| this.sst = sst; |
| } |
| |
| @Override |
| public void startElement(String uri, String localName, String name, |
| Attributes attributes) throws SAXException { |
| // c => cell |
| if(name.equals("c")) { |
| // Print the cell reference |
| System.out.print(attributes.getValue("r") + " - "); |
| // Figure out if the value is an index in the SST |
| String cellType = attributes.getValue("t"); |
| nextIsString = cellType != null && cellType.equals("s"); |
| inlineStr = cellType != null && cellType.equals("inlineStr"); |
| } |
| // Clear contents cache |
| lastContents = ""; |
| } |
| |
| @Override |
| public void endElement(String uri, String localName, String name) |
| throws SAXException { |
| // Process the last contents as required. |
| // Do now, as characters() may be called more than once |
| if(nextIsString && !lastContents.trim().isEmpty()) { |
| Integer idx = Integer.valueOf(lastContents); |
| lastContents = lruCache.get(idx); |
| if (lastContents == null && !lruCache.containsKey(idx)) { |
| lastContents = sst.getItemAt(idx).getString(); |
| lruCache.put(idx, lastContents); |
| } |
| nextIsString = false; |
| } |
| |
| // v => contents of a cell |
| // Output after we've seen the string contents |
| if(name.equals("v") || (inlineStr && name.equals("c"))) { |
| System.out.println(lastContents); |
| } |
| } |
| |
| @Override |
| public void characters(char[] ch, int start, int length) throws SAXException { // NOSONAR |
| lastContents += new String(ch, start, length); |
| } |
| } |
| |
| public static void main(String[] args) throws Exception { |
| FromHowTo howto = new FromHowTo(); |
| howto.processFirstSheet(args[0]); |
| howto.processAllSheets(args[0]); |
| } |
| } |