OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Go to most recent revision | Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
174 ilm 1
/*
2
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
3
 *
4
 * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
5
 *
6
 * The contents of this file are subject to the terms of the GNU General Public License Version 3
7
 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
8
 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
9
 * language governing permissions and limitations under the License.
10
 *
11
 * When distributing the software, include this License Header Notice in each file.
12
 */
13
 
14
 package org.openconcerto.utils.ooxml;
15
 
16
import org.openconcerto.utils.StringInputStream;
17
import org.openconcerto.utils.StringUtils;
18
 
19
import java.awt.Point;
20
import java.io.IOException;
21
import java.nio.charset.StandardCharsets;
22
import java.util.ArrayList;
23
import java.util.Calendar;
24
import java.util.Date;
25
import java.util.List;
26
import java.util.regex.Matcher;
27
import java.util.regex.Pattern;
28
 
29
import javax.xml.parsers.DocumentBuilder;
30
import javax.xml.parsers.ParserConfigurationException;
31
 
32
import org.w3c.dom.Document;
33
import org.w3c.dom.Node;
34
import org.w3c.dom.NodeList;
35
import org.xml.sax.SAXException;
36
 
37
import com.ibm.icu.math.BigDecimal;
38
 
39
public class XLSXSheet {
40
    private int columnCount;
41
    private int startX;
42
    private int startY;
43
    private int endX;
44
    private int endY;
45
    private static final String MINCELL = "\\$?([A-Z]+)\\$?([0-9]+)";
46
    private static final Pattern minCellPattern = Pattern.compile(MINCELL);
47
    private final List<List<Object>> rows;
48
    private String id;
49
    private String rId;
50
    private String name;
51
 
52
    public XLSXSheet(XLSXDocument document, String id, String rId, String name, String xml) throws IOException, ParserConfigurationException, SAXException {
53
        this.id = id;
54
        this.rId = rId;
55
        this.name = name;
56
 
57
        final DocumentBuilder dBuilder = document.getDbFactory().newDocumentBuilder();
58
        final Document doc = dBuilder.parse(new StringInputStream(xml, StandardCharsets.UTF_8.name()));
59
        doc.getDocumentElement().normalize();
60
 
61
        final NodeList nList = doc.getElementsByTagName("dimension");
62
        final String dimension = nList.item(0).getAttributes().getNamedItem("ref").getNodeValue();
63
        final List<String> parts = StringUtils.fastSplit(dimension, ':');
64
 
65
        final Point start = resolve(parts.get(0));
66
        this.startX = start.x;
67
        this.startY = start.y;
68
 
69
        final Point end = resolve(parts.get(1));
70
        this.endX = end.x;
71
        this.endY = end.y;
72
        this.rows = new ArrayList<>(end.y - start.y);
73
        this.columnCount = this.endX - this.startX + 1;
74
 
75
        for (int i = start.y; i <= end.y; i++) {
76
            List<Object> row = new ArrayList<>();
77
            for (int j = 0; j < this.columnCount; j++) {
78
                row.add(null);
79
            }
80
            this.rows.add(row);
81
        }
82
        Calendar calendar = Calendar.getInstance();
83
        NodeList nListRows = doc.getElementsByTagName("row");
84
        int l1 = nListRows.getLength();
85
        for (int i = 0; i < l1; i++) {
86
            Node r = nListRows.item(i);
87
            NodeList nListCells = r.getChildNodes();
88
            int l2 = nListCells.getLength();
89
            for (int j = 0; j < l2; j++) {
90
                Node c = nListCells.item(j);
91
                final String location = c.getAttributes().getNamedItem("r").getNodeValue();
92
                final Point p = resolve(location);
93
                if (p == null) {
94
                    throw new IllegalStateException("unable to parse location : " + location);
95
                }
96
                // The index of this cell's style. Style records are stored in the Styles Part.
97
                // The possible values for this attribute are defined by the W3C XML Schema
98
                // unsignedInt datatype.
99
                int style = 0;
100
                if (c.getAttributes().getNamedItem("s") != null) {
101
                    style = Integer.parseInt(c.getAttributes().getNamedItem("s").getNodeValue());
102
                }
103
                // An enumeration representing the cell's data type.
104
                // The possible values for this attribute are defined by the ST_CellType simple type
105
                // (§18.18.11):
106
                // "b" boolean
107
                // "d" ISO 8601 date
108
                // "n" number
109
                // "e" error
110
                // "s" strin
111
                // "str" formula
112
                // "inlineStr" the cell value is in the is element rather than the v
113
                // element in the cell
114
                String type = "n";
115
                if (c.getAttributes().getNamedItem("t") != null) {
116
                    type = c.getAttributes().getNamedItem("t").getNodeValue();
117
                }
118
                NodeList nListCellParts = c.getChildNodes();
119
                int l3 = nListCellParts.getLength();
120
                for (int k = 0; k < l3; k++) {
121
                    Node part = nListCellParts.item(k);
122
                    if (part.getNodeName().equals("v")) {
123
                        String value = part.getTextContent();
124
                        Object cellValue = null;
125
                        if (type.equals("n")) {
126
                            final XLSXFormat format = document.getFormatFromStyle(style);
127
                            if (format != null) {
128
                                if (format.isDateFormat()) {
129
                                    cellValue = stringToDate(calendar, value);
130
                                } else {
131
                                    cellValue = new BigDecimal(value);
132
                                }
133
                            } else {
134
                                cellValue = new BigDecimal(value);
135
                            }
136
                        } else if (type.equals("s")) {
137
                            cellValue = document.getSharedString(Integer.parseInt(value));
138
                        }
139
                        this.rows.get(p.y - this.startY).set(p.x - this.startX, cellValue);
140
                    }
141
                }
142
 
143
            }
144
        }
145
    }
146
 
147
    public Object getValueAt(int col, int row) {
148
        return this.rows.get(row).get(col);
149
    }
150
 
151
    public int getColumnCount() {
152
        return this.columnCount;
153
    }
154
 
155
    public int getRowCount() {
156
        return this.rows.size();
157
    }
158
 
159
    /**
160
     * Convert string coordinates into numeric ones.
161
     *
162
     * @param ref the string address, eg "$AA$34" or "AA34".
163
     * @return the numeric coordinates or <code>null</code> if <code>ref</code> is not valid, eg
164
     *         {26, 33}.
165
     */
166
    static final Point resolve(String ref) {
167
        final Matcher matcher = minCellPattern.matcher(ref);
168
        if (!matcher.matches())
169
            return null;
170
        return resolve(matcher.group(1), matcher.group(2));
171
    }
172
 
173
    /**
174
     * Convert string coordinates into numeric ones. ATTN this method does no checks.
175
     *
176
     * @param letters the column, eg "AA".
177
     * @param digits the row, eg "34".
178
     * @return the numeric coordinates, eg {26, 33}.
179
     */
180
    static final Point resolve(final String letters, final String digits) {
181
        return new Point(toInt(letters), Integer.parseInt(digits) - 1);
182
    }
183
 
184
    // "AA" => 26
185
    static final int toInt(String col) {
186
        if (col.length() < 1)
187
            throw new IllegalArgumentException("x cannot be empty");
188
        col = col.toUpperCase();
189
 
190
        int x = 0;
191
        for (int i = 0; i < col.length(); i++) {
192
            x = x * 26 + (col.charAt(i) - 'A' + 1);
193
        }
194
 
195
        // zero based
196
        return x - 1;
197
    }
198
 
199
    public static final String toStr(int col) {
200
        if (col < 0)
201
            throw new IllegalArgumentException("negative column : " + col);
202
        // one based (i.e. 0 is A)
203
        col++;
204
 
205
        final int radix = 26;
206
        final StringBuilder chars = new StringBuilder(4);
207
        while (col > 0) {
208
            chars.append((char) ('A' + ((col - 1) % radix)));
209
            col = (col - 1) / radix;
210
        }
211
 
212
        return chars.reverse().toString();
213
    }
214
 
215
    /**
216
     * Convert numeric coordinates into string ones.
217
     *
218
     * @param p the numeric coordinates, e.g. {26, 33}.
219
     * @return the string address, e.g. "AA34".
220
     */
221
    static final String getAddress(Point p) {
222
        if (p.x < 0 || p.y < 0)
223
            throw new IllegalArgumentException("negative coordinates : " + p);
224
        return toStr(p.x) + (p.y + 1);
225
    }
226
 
227
    public String getId() {
228
        return this.id;
229
    }
230
 
231
    public static Date stringToDate(Calendar c, String d) {
232
        c.clear();
233
        c.set(1900, 0, 0);
234
        c.add(Calendar.DAY_OF_YEAR, Integer.parseInt(d) - 1);
235
        return c.getTime();
236
    }
237
 
238
    public int getStartX() {
239
        return this.startX;
240
    }
241
 
242
    public int getStartY() {
243
        return this.startY;
244
    }
245
 
246
    public int getEndX() {
247
        return this.endX;
248
    }
249
 
250
    public int getEndY() {
251
        return this.endY;
252
    }
253
 
254
    public String getName() {
255
        return this.name;
256
    }
257
 
258
    public String getRId() {
259
        return this.rId;
260
    }
261
}