Dépôt officiel du code source de l'ERP OpenConcerto

/*
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 * 
 * Copyright 2011-2019 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 org.openconcerto.utils.StringUtils;

import java.awt.Point;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.ParserConfigurationException;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

public class XLSXSheet {
    private int columnCount;
    private int startX;
    private int startY;
    private int endX;
    private int endY;
    private static final String MINCELL = "\\$?([A-Z]+)\\$?([0-9]+)";
    private static final Pattern minCellPattern = Pattern.compile(MINCELL);
    private final List<List<Object>> rows;
    private String id;
    private String rId;
    private String name;

    public XLSXSheet(XLSXDocument document, String id, String rId, String name, String xml) throws IOException, ParserConfigurationException, SAXException {
        this.id = id;
        this.rId = rId;
        this.name = name;

        final DocumentBuilder dBuilder = document.getDbFactory().newDocumentBuilder();
        final Document doc = dBuilder.parse(new StringInputStream(xml, StandardCharsets.UTF_8.name()));
        doc.getDocumentElement().normalize();

        final NodeList nList = doc.getElementsByTagName("dimension");
        final String dimension = nList.item(0).getAttributes().getNamedItem("ref").getNodeValue();
        final List<String> parts = StringUtils.fastSplit(dimension, ':');

        final Point start = resolve(parts.get(0));
        this.startX = start.x;
        this.startY = start.y;

        // Feuille vierge dimension = A1
        final Point end = resolve(parts.size() == 1 ? parts.get(0) : parts.get(1));
        this.endX = end.x;
        this.endY = end.y;
        this.rows = new ArrayList<>(end.y - start.y);
        this.columnCount = this.endX - this.startX + 1;

        for (int i = start.y; i <= end.y; i++) {
            List<Object> row = new ArrayList<>();
            for (int j = 0; j < this.columnCount; j++) {
                row.add(null);
            }
            this.rows.add(row);
        }
        Calendar calendar = Calendar.getInstance();
        NodeList nListRows = doc.getElementsByTagName("row");
        int l1 = nListRows.getLength();
        for (int i = 0; i < l1; i++) {
            Node r = nListRows.item(i);
            NodeList nListCells = r.getChildNodes();
            int l2 = nListCells.getLength();
            for (int j = 0; j < l2; j++) {
                Node c = nListCells.item(j);
                final String location = c.getAttributes().getNamedItem("r").getNodeValue();
                final Point p = resolve(location);
                if (p == null) {
                    throw new IllegalStateException("unable to parse location : " + location);
                }
                // The index of this cell's style. Style records are stored in the Styles Part.
                // The possible values for this attribute are defined by the W3C XML Schema
                // unsignedInt datatype.
                int style = 0;
                if (c.getAttributes().getNamedItem("s") != null) {
                    style = Integer.parseInt(c.getAttributes().getNamedItem("s").getNodeValue());
                }
                // An enumeration representing the cell's data type.
                // The possible values for this attribute are defined by the ST_CellType simple type
                // (§18.18.11):
                // "b" boolean
                // "d" ISO 8601 date
                // "n" number
                // "e" error
                // "s" strin
                // "str" formula
                // "inlineStr" the cell value is in the is element rather than the v
                // element in the cell
                String type = "n";
                if (c.getAttributes().getNamedItem("t") != null) {
                    type = c.getAttributes().getNamedItem("t").getNodeValue();
                }
                NodeList nListCellParts = c.getChildNodes();
                int l3 = nListCellParts.getLength();
                for (int k = 0; k < l3; k++) {
                    Node part = nListCellParts.item(k);
                    if (part.getNodeName().equals("v")) {
                        String value = part.getTextContent();
                        Object cellValue = null;
                        if (type.equals("n")) {
                            final XLSXFormat format = document.getFormatFromStyle(style);
                            if (format != null) {
                                if (format.isDateFormat()) {
                                    cellValue = stringToDate(calendar, value);
                                } else {
                                    cellValue = new BigDecimal(value);
                                }
                            } else {
                                cellValue = new BigDecimal(value);
                            }
                        } else if (type.equals("s")) {
                            cellValue = document.getSharedString(Integer.parseInt(value));
                        } else if (type.equals("str")) {
                            cellValue = value;
                        }
                        this.rows.get(p.y - this.startY).set(p.x - this.startX, cellValue);
                    }
                }

            }
        }
    }

    public Object getValueAt(int col, int row) {
        return this.rows.get(row).get(col);
    }

    public int getColumnCount() {
        return this.columnCount;
    }

    public int getRowCount() {
        return this.rows.size();
    }

    /**
     * Convert string coordinates into numeric ones.
     * 
     * @param ref the string address, eg "$AA$34" or "AA34".
     * @return the numeric coordinates or <code>null</code> if <code>ref</code> is not valid, eg
     *         {26, 33}.
     */
    static final Point resolve(String ref) {
        final Matcher matcher = minCellPattern.matcher(ref);
        if (!matcher.matches())
            return null;
        return resolve(matcher.group(1), matcher.group(2));
    }

    /**
     * Convert string coordinates into numeric ones. ATTN this method does no checks.
     * 
     * @param letters the column, eg "AA".
     * @param digits the row, eg "34".
     * @return the numeric coordinates, eg {26, 33}.
     */
    static final Point resolve(final String letters, final String digits) {
        return new Point(toInt(letters), Integer.parseInt(digits) - 1);
    }

    // "AA" => 26
    static final int toInt(String col) {
        if (col.length() < 1)
            throw new IllegalArgumentException("x cannot be empty");
        col = col.toUpperCase();

        int x = 0;
        for (int i = 0; i < col.length(); i++) {
            x = x * 26 + (col.charAt(i) - 'A' + 1);
        }

        // zero based
        return x - 1;
    }

    public static final String toStr(int col) {
        if (col < 0)
            throw new IllegalArgumentException("negative column : " + col);
        // one based (i.e. 0 is A)
        col++;

        final int radix = 26;
        final StringBuilder chars = new StringBuilder(4);
        while (col > 0) {
            chars.append((char) ('A' + ((col - 1) % radix)));
            col = (col - 1) / radix;
        }

        return chars.reverse().toString();
    }

    /**
     * Convert numeric coordinates into string ones.
     * 
     * @param p the numeric coordinates, e.g. {26, 33}.
     * @return the string address, e.g. "AA34".
     */
    static final String getAddress(Point p) {
        if (p.x < 0 || p.y < 0)
            throw new IllegalArgumentException("negative coordinates : " + p);
        return toStr(p.x) + (p.y + 1);
    }

    public String getId() {
        return this.id;
    }

    public static Date stringToDate(Calendar c, String d) {
        c.clear();
        c.set(1900, 0, 0);
        BigDecimal b = new BigDecimal(d);
        final BigDecimal days = b.setScale(0, RoundingMode.FLOOR);
        c.add(Calendar.DAY_OF_YEAR, days.intValue() - 1);
        final BigDecimal ms = b.subtract(b.setScale(0, RoundingMode.FLOOR)).multiply(new BigDecimal(24)).multiply(new BigDecimal(36)).movePointRight(5);
        c.add(Calendar.MILLISECOND, ms.setScale(0, RoundingMode.HALF_UP).intValue());
        return c.getTime();
    }

    public int getStartX() {
        return this.startX;
    }

    public int getStartY() {
        return this.startY;
    }

    public int getEndX() {
        return this.endX;
    }

    public int getEndY() {
        return this.endY;
    }

    public String getName() {
        return this.name;
    }

    public String getRId() {
        return this.rId;
    }
}