Dépôt officiel du code source de l'ERP OpenConcerto
svn://code.openconcerto.org/openconcerto
(root)/trunk/OpenConcerto/src/org/openconcerto/utils/ooxml/XLSXDocument.java - Rev 174
Blame | Last modification | View Log | RSS feed
/** DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.** Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.** The contents of this file are subject to the terms of the GNU General Public License Version 3* only ("GPL"). You may not use this file except in compliance with the License. You can obtain a* copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific* language governing permissions and limitations under the License.** When distributing the software, include this License Header Notice in each file.*/package org.openconcerto.utils.ooxml;import org.openconcerto.utils.StringInputStream;import java.io.ByteArrayOutputStream;import java.io.File;import java.io.IOException;import java.io.InputStream;import java.nio.charset.StandardCharsets;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.zip.ZipEntry;import java.util.zip.ZipFile;import javax.xml.XMLConstants;import javax.xml.parsers.DocumentBuilder;import javax.xml.parsers.DocumentBuilderFactory;import javax.xml.parsers.ParserConfigurationException;import org.w3c.dom.Document;import org.w3c.dom.NamedNodeMap;import org.w3c.dom.Node;import org.w3c.dom.NodeList;import org.xml.sax.SAXException;public class XLSXDocument {private File file;private final List<XLSXSheet> sheets = new ArrayList<>();private final Map<String, String> relationships = new HashMap<>();private final List<String> sharedString = new ArrayList<>();private final Map<Integer, XLSXFormat> mapFormats = new HashMap<>();private final List<XLSXFormat> customFormats = new ArrayList<>();private final List<XLSXStyle> styles = new ArrayList<>();private final DocumentBuilderFactory dbFactory;public static XLSXDocument createFromFile(File f) throws IOException {XLSXDocument doc = new XLSXDocument();doc.load(f);return doc;}public XLSXDocument() {this.mapFormats.put(0, new XLSXFormat(0, "General", false));this.mapFormats.put(1, new XLSXFormat(1, "0", false));this.mapFormats.put(2, new XLSXFormat(2, "0.00", false));this.mapFormats.put(3, new XLSXFormat(3, "#,##0", false));this.mapFormats.put(4, new XLSXFormat(4, "#,##0.00", false));this.mapFormats.put(9, new XLSXFormat(9, "0%", false));this.mapFormats.put(10, new XLSXFormat(10, "0.00%", false));this.mapFormats.put(11, new XLSXFormat(11, "0.00E+00", false));this.mapFormats.put(12, new XLSXFormat(12, "# ?/?", false));this.mapFormats.put(13, new XLSXFormat(13, "# ??/??", false));this.mapFormats.put(14, new XLSXFormat(14, "mm-dd-yy", true));this.mapFormats.put(15, new XLSXFormat(15, "d-mmm-yy", true));this.mapFormats.put(16, new XLSXFormat(16, "d-mmm", true));this.mapFormats.put(17, new XLSXFormat(17, "mmm-yy", true));this.mapFormats.put(18, new XLSXFormat(18, "h:mm AM/PM", false));this.mapFormats.put(19, new XLSXFormat(19, "h:mm:ss AM/PM", false));this.mapFormats.put(20, new XLSXFormat(20, "h:mm", false));this.mapFormats.put(21, new XLSXFormat(21, "h:mm:ss", false));this.mapFormats.put(22, new XLSXFormat(22, "m/d/yy h:mm", true));this.mapFormats.put(27, new XLSXFormat(27, "[$-404]e/m/d", true));this.mapFormats.put(30, new XLSXFormat(30, "m/d/yy", true));this.mapFormats.put(36, new XLSXFormat(36, "[$-404]e/m/d", true));this.mapFormats.put(37, new XLSXFormat(37, "#,##0 ;(#,##0)", false));this.mapFormats.put(38, new XLSXFormat(38, "#,##0 ;[Red](#,##0)", false));this.mapFormats.put(39, new XLSXFormat(39, "#,##0.00;(#,##0.00)", false));this.mapFormats.put(40, new XLSXFormat(40, "#,##0.00;[Red](#,##0.00)", false));this.mapFormats.put(44, new XLSXFormat(44, "_(\"$\"* #,##0.00_);_(\"$\"* (#,##0.00);_(\"$\"* \"-\"??_);_(@_)", false));this.mapFormats.put(45, new XLSXFormat(45, "mm:ss", false));this.mapFormats.put(46, new XLSXFormat(46, "[h]:mm:ss", false));this.mapFormats.put(47, new XLSXFormat(47, "mmss.0", false));this.mapFormats.put(48, new XLSXFormat(48, "##0.0E+0", false));this.mapFormats.put(49, new XLSXFormat(49, "@", false));this.mapFormats.put(50, new XLSXFormat(50, "[$-404]e/m/d", true));this.mapFormats.put(57, new XLSXFormat(57, "[$-404]e/m/d", true));this.mapFormats.put(59, new XLSXFormat(59, "t0", false));this.mapFormats.put(60, new XLSXFormat(60, "t0.00", false));this.mapFormats.put(61, new XLSXFormat(61, "t#,##0", false));this.mapFormats.put(62, new XLSXFormat(62, "t#,##0.00", false));this.mapFormats.put(67, new XLSXFormat(67, "t0%", false));this.mapFormats.put(68, new XLSXFormat(68, "t0.00%", false));this.mapFormats.put(69, new XLSXFormat(69, "t# ?/?", false));this.mapFormats.put(70, new XLSXFormat(70, "t# ??/??", false));this.dbFactory = DocumentBuilderFactory.newInstance();try {this.dbFactory.setFeature(XMLConstants.FEATURE_SECURE_PROCESSING, true);} catch (ParserConfigurationException e) {throw new IllegalStateException(e);}try {this.dbFactory.setAttribute(XMLConstants.ACCESS_EXTERNAL_DTD, "");this.dbFactory.setAttribute(XMLConstants.ACCESS_EXTERNAL_STYLESHEET, "");} catch (Exception e) {// ignore}this.dbFactory.setNamespaceAware(true);}public File getFile() {return this.file;}private void load(File f) throws IOException {this.file = f;try (ZipFile zipFile = new ZipFile(f)) {try {parseSharedStrings(zipFile);parseStyles(zipFile);parseWorkBook(zipFile);} catch (ParserConfigurationException | SAXException e) {throw new IOException(e);}}}private void parseWorkBook(ZipFile zipFile) throws ParserConfigurationException, SAXException, IOException {final String relationxXML = getContent(zipFile, "xl/_rels/workbook.xml.rels");final DocumentBuilder dBuilder1 = this.dbFactory.newDocumentBuilder();final Document doc1 = dBuilder1.parse(new StringInputStream(relationxXML, StandardCharsets.UTF_8.name()));doc1.getDocumentElement().normalize();final NodeList nList = doc1.getElementsByTagName("Relationship");for (int i = 0; i < nList.getLength(); i++) {final NamedNodeMap attributes = nList.item(i).getAttributes();final String rId = attributes.getNamedItem("Id").getNodeValue();final String target = attributes.getNamedItem("Target").getNodeValue();this.relationships.put(rId, target);}final String workbookXML = getContent(zipFile, "xl/workbook.xml");final DocumentBuilder dBuilder2 = this.dbFactory.newDocumentBuilder();final Document doc2 = dBuilder2.parse(new StringInputStream(workbookXML, StandardCharsets.UTF_8.name()));doc2.getDocumentElement().normalize();doc2.getElementsByTagName("sheets");final NodeList nListSheet = doc2.getElementsByTagName("sheet");for (int i = 0; i < nListSheet.getLength(); i++) {final NamedNodeMap attributes = nListSheet.item(i).getAttributes();final String sheetId = attributes.getNamedItem("sheetId").getNodeValue();final String name = attributes.getNamedItem("name").getNodeValue();final String rId = attributes.getNamedItemNS("http://schemas.openxmlformats.org/officeDocument/2006/relationships", "id").getNodeValue();final String target = "xl/" + this.relationships.get(rId);final String xml = getContent(zipFile, target);this.sheets.add(new XLSXSheet(this, sheetId, rId, name, xml));}}private void parseSharedStrings(ZipFile zipFile) throws ParserConfigurationException, SAXException, IOException {final String relationxXML = getContent(zipFile, "xl/sharedStrings.xml");final DocumentBuilder dBuilder1 = this.dbFactory.newDocumentBuilder();final Document doc1 = dBuilder1.parse(new StringInputStream(relationxXML, StandardCharsets.UTF_8.name()));doc1.getDocumentElement().normalize();final NodeList nList = doc1.getElementsByTagName("si");for (int i = 0; i < nList.getLength(); i++) {final Node siNode = nList.item(i);StringBuilder b = new StringBuilder();NodeList list = siNode.getChildNodes();for (int j = 0; j < list.getLength(); j++) {Node n = list.item(j);if (n.getLocalName().equals("t")) {b.append(n.getTextContent());} else {NodeList subList = n.getChildNodes();for (int k = 0; k < subList.getLength(); k++) {Node nn = subList.item(k);if (nn.getLocalName().equals("t")) {b.append(nn.getTextContent());}}}}this.sharedString.add(b.toString());}}private void parseStyles(ZipFile zipFile) throws ParserConfigurationException, SAXException, IOException {final String relationxXML = getContent(zipFile, "xl/styles.xml");final DocumentBuilder dBuilder1 = this.dbFactory.newDocumentBuilder();final Document doc1 = dBuilder1.parse(new StringInputStream(relationxXML, StandardCharsets.UTF_8.name()));doc1.getDocumentElement().normalize();final NodeList nListFormats = doc1.getElementsByTagName("numFmts");for (int i = 0; i < nListFormats.getLength(); i++) {final Node siNode = nListFormats.item(i);NodeList list = siNode.getChildNodes();for (int j = 0; j < list.getLength(); j++) {Node n = list.item(j);NamedNodeMap m = n.getAttributes();int id = Integer.parseInt(m.getNamedItem("numFmtId").getNodeValue());String format = m.getNamedItem("formatCode").getNodeValue();final XLSXFormat f = new XLSXFormat(id, format);this.mapFormats.put(id, f);this.customFormats.add(f);}}final NodeList nListCellXfs = doc1.getElementsByTagName("cellXfs");for (int i = 0; i < nListCellXfs.getLength(); i++) {final Node siNode = nListCellXfs.item(i);NodeList list = siNode.getChildNodes();for (int j = 0; j < list.getLength(); j++) {// xfNode n = list.item(j);NamedNodeMap m = n.getAttributes();int id = Integer.parseInt(m.getNamedItem("numFmtId").getNodeValue());this.styles.add(new XLSXStyle(id));}}}private String getContent(ZipFile zipFile, String string) throws IOException {final ZipEntry entry = zipFile.getEntry(string);if (entry == null) {throw new IOException("no entry : " + string);}final InputStream in = zipFile.getInputStream(entry);final ByteArrayOutputStream b = new ByteArrayOutputStream();final byte[] bytes = new byte[8192];while (in.available() > 0) {final int length = in.read(bytes);b.write(bytes, 0, length);}return b.toString(StandardCharsets.UTF_8.name());}public XLSXSheet getSheet(int sheetNumber) {return this.sheets.get(sheetNumber);}public int getSheetCount() {return this.sheets.size();}public String getSharedString(int index) {return this.sharedString.get(index);}public XLSXFormat getFormatFromStyle(int styleIndex) {final XLSXStyle style = this.styles.get(styleIndex);if (style == null) {return null;}return this.mapFormats.get(style.getFormatId());}public DocumentBuilderFactory getDbFactory() {return this.dbFactory;}}