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

/*

 * 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 org.openconcerto.utils.StringUtils;

import java.awt.Point;

import java.io.IOException;

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;

import com.ibm.icu.math.BigDecimal;

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));

                        }

                        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);

        c.add(Calendar.DAY_OF_YEAR, Integer.parseInt(d) - 1);

        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;

    }

}