OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 174 | Details | Compare with Previous | 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
 
177 ilm 69
        // Feuille vierge dimension = A1
70
        final Point end = resolve(parts.size() == 1 ? parts.get(0) : parts.get(1));
174 ilm 71
        this.endX = end.x;
72
        this.endY = end.y;
73
        this.rows = new ArrayList<>(end.y - start.y);
74
        this.columnCount = this.endX - this.startX + 1;
75
 
76
        for (int i = start.y; i <= end.y; i++) {
77
            List<Object> row = new ArrayList<>();
78
            for (int j = 0; j < this.columnCount; j++) {
79
                row.add(null);
80
            }
81
            this.rows.add(row);
82
        }
83
        Calendar calendar = Calendar.getInstance();
84
        NodeList nListRows = doc.getElementsByTagName("row");
85
        int l1 = nListRows.getLength();
86
        for (int i = 0; i < l1; i++) {
87
            Node r = nListRows.item(i);
88
            NodeList nListCells = r.getChildNodes();
89
            int l2 = nListCells.getLength();
90
            for (int j = 0; j < l2; j++) {
91
                Node c = nListCells.item(j);
92
                final String location = c.getAttributes().getNamedItem("r").getNodeValue();
93
                final Point p = resolve(location);
94
                if (p == null) {
95
                    throw new IllegalStateException("unable to parse location : " + location);
96
                }
97
                // The index of this cell's style. Style records are stored in the Styles Part.
98
                // The possible values for this attribute are defined by the W3C XML Schema
99
                // unsignedInt datatype.
100
                int style = 0;
101
                if (c.getAttributes().getNamedItem("s") != null) {
102
                    style = Integer.parseInt(c.getAttributes().getNamedItem("s").getNodeValue());
103
                }
104
                // An enumeration representing the cell's data type.
105
                // The possible values for this attribute are defined by the ST_CellType simple type
106
                // (§18.18.11):
107
                // "b" boolean
108
                // "d" ISO 8601 date
109
                // "n" number
110
                // "e" error
111
                // "s" strin
112
                // "str" formula
113
                // "inlineStr" the cell value is in the is element rather than the v
114
                // element in the cell
115
                String type = "n";
116
                if (c.getAttributes().getNamedItem("t") != null) {
117
                    type = c.getAttributes().getNamedItem("t").getNodeValue();
118
                }
119
                NodeList nListCellParts = c.getChildNodes();
120
                int l3 = nListCellParts.getLength();
121
                for (int k = 0; k < l3; k++) {
122
                    Node part = nListCellParts.item(k);
123
                    if (part.getNodeName().equals("v")) {
124
                        String value = part.getTextContent();
125
                        Object cellValue = null;
126
                        if (type.equals("n")) {
127
                            final XLSXFormat format = document.getFormatFromStyle(style);
128
                            if (format != null) {
129
                                if (format.isDateFormat()) {
130
                                    cellValue = stringToDate(calendar, value);
131
                                } else {
132
                                    cellValue = new BigDecimal(value);
133
                                }
134
                            } else {
135
                                cellValue = new BigDecimal(value);
136
                            }
137
                        } else if (type.equals("s")) {
138
                            cellValue = document.getSharedString(Integer.parseInt(value));
139
                        }
140
                        this.rows.get(p.y - this.startY).set(p.x - this.startX, cellValue);
141
                    }
142
                }
143
 
144
            }
145
        }
146
    }
147
 
148
    public Object getValueAt(int col, int row) {
149
        return this.rows.get(row).get(col);
150
    }
151
 
152
    public int getColumnCount() {
153
        return this.columnCount;
154
    }
155
 
156
    public int getRowCount() {
157
        return this.rows.size();
158
    }
159
 
160
    /**
161
     * Convert string coordinates into numeric ones.
162
     *
163
     * @param ref the string address, eg "$AA$34" or "AA34".
164
     * @return the numeric coordinates or <code>null</code> if <code>ref</code> is not valid, eg
165
     *         {26, 33}.
166
     */
167
    static final Point resolve(String ref) {
168
        final Matcher matcher = minCellPattern.matcher(ref);
169
        if (!matcher.matches())
170
            return null;
171
        return resolve(matcher.group(1), matcher.group(2));
172
    }
173
 
174
    /**
175
     * Convert string coordinates into numeric ones. ATTN this method does no checks.
176
     *
177
     * @param letters the column, eg "AA".
178
     * @param digits the row, eg "34".
179
     * @return the numeric coordinates, eg {26, 33}.
180
     */
181
    static final Point resolve(final String letters, final String digits) {
182
        return new Point(toInt(letters), Integer.parseInt(digits) - 1);
183
    }
184
 
185
    // "AA" => 26
186
    static final int toInt(String col) {
187
        if (col.length() < 1)
188
            throw new IllegalArgumentException("x cannot be empty");
189
        col = col.toUpperCase();
190
 
191
        int x = 0;
192
        for (int i = 0; i < col.length(); i++) {
193
            x = x * 26 + (col.charAt(i) - 'A' + 1);
194
        }
195
 
196
        // zero based
197
        return x - 1;
198
    }
199
 
200
    public static final String toStr(int col) {
201
        if (col < 0)
202
            throw new IllegalArgumentException("negative column : " + col);
203
        // one based (i.e. 0 is A)
204
        col++;
205
 
206
        final int radix = 26;
207
        final StringBuilder chars = new StringBuilder(4);
208
        while (col > 0) {
209
            chars.append((char) ('A' + ((col - 1) % radix)));
210
            col = (col - 1) / radix;
211
        }
212
 
213
        return chars.reverse().toString();
214
    }
215
 
216
    /**
217
     * Convert numeric coordinates into string ones.
218
     *
219
     * @param p the numeric coordinates, e.g. {26, 33}.
220
     * @return the string address, e.g. "AA34".
221
     */
222
    static final String getAddress(Point p) {
223
        if (p.x < 0 || p.y < 0)
224
            throw new IllegalArgumentException("negative coordinates : " + p);
225
        return toStr(p.x) + (p.y + 1);
226
    }
227
 
228
    public String getId() {
229
        return this.id;
230
    }
231
 
232
    public static Date stringToDate(Calendar c, String d) {
233
        c.clear();
234
        c.set(1900, 0, 0);
235
        c.add(Calendar.DAY_OF_YEAR, Integer.parseInt(d) - 1);
236
        return c.getTime();
237
    }
238
 
239
    public int getStartX() {
240
        return this.startX;
241
    }
242
 
243
    public int getStartY() {
244
        return this.startY;
245
    }
246
 
247
    public int getEndX() {
248
        return this.endX;
249
    }
250
 
251
    public int getEndY() {
252
        return this.endY;
253
    }
254
 
255
    public String getName() {
256
        return this.name;
257
    }
258
 
259
    public String getRId() {
260
        return this.rId;
261
    }
262
}