Mercurial > clickerconvert > clickerconvert

comparison src/xlsx/xlsxutility.cpp @ 1:93d3106bb9a4

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add qt xlsx library
author Andre Heinecke <andre.heinecke@intevation.de>
date Tue, 22 Mar 2016 10:38:08 +0100
parents
children
comparison
equal deleted inserted replaced
0:49cd5cc0b072 1:93d3106bb9a4
1 /****************************************************************************
2 ** Copyright (c) 2013-2014 Debao Zhang <hello@debao.me>
3 ** All right reserved.
4 **
5 ** Permission is hereby granted, free of charge, to any person obtaining
6 ** a copy of this software and associated documentation files (the
7 ** "Software"), to deal in the Software without restriction, including
8 ** without limitation the rights to use, copy, modify, merge, publish,
9 ** distribute, sublicense, and/or sell copies of the Software, and to
10 ** permit persons to whom the Software is furnished to do so, subject to
11 ** the following conditions:
12 **
13 ** The above copyright notice and this permission notice shall be
14 ** included in all copies or substantial portions of the Software.
15 **
16 ** THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
17 ** EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
18 ** MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
19 ** NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
20 ** LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
21 ** OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
22 ** WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
23 **
24 ****************************************************************************/
25 #include "xlsxutility_p.h"
26 #include "xlsxcellreference.h"
27
28 #include <QString>
29 #include <QPoint>
30 #include <QRegularExpression>
31 #include <QMap>
32 #include <QStringList>
33 #include <QColor>
34 #include <QDateTime>
35 #include <QDebug>
36
37 namespace QXlsx {
38
39 bool parseXsdBoolean(const QString &value, bool defaultValue)
40 {
41 if (value == QLatin1String("1") || value == QLatin1String("true"))
42 return true;
43 if (value == QLatin1String("0") || value == QLatin1String("false"))
44 return false;
45 return defaultValue;
46 }
47
48 QStringList splitPath(const QString &path)
49 {
50 int idx = path.lastIndexOf(QLatin1Char('/'));
51 if (idx == -1)
52 return QStringList()<<QStringLiteral(".")<<path;
53
54 return QStringList()<<path.left(idx)<<path.mid(idx+1);
55 }
56
57 /*
58 * Return the .rel file path based on filePath
59 */
60 QString getRelFilePath(const QString &filePath)
61 {
62 int idx = filePath.lastIndexOf(QLatin1Char('/'));
63 if (idx == -1)
64 return QString();
65
66 return QString(filePath.left(idx) + QLatin1String("/_rels/")
67 + filePath.mid(idx+1) + QLatin1String(".rels"));
68 }
69
70 double datetimeToNumber(const QDateTime &dt, bool is1904)
71 {
72 //Note, for number 0, Excel2007 shown as 1900-1-0, which should be 1899-12-31
73 QDateTime epoch(is1904 ? QDate(1904, 1, 1): QDate(1899, 12, 31), QTime(0,0));
74
75 double excel_time = epoch.msecsTo(dt) / (1000*60*60*24.0);
76
77 #if QT_VERSION >= 0x050200
78 if (dt.isDaylightTime()) // Add one hour if the date is Daylight
79 excel_time += 1.0 / 24.0;
80 #endif
81
82 if (!is1904 && excel_time > 59) {//31+28
83 //Account for Excel erroneously treating 1900 as a leap year.
84 excel_time += 1;
85 }
86
87 return excel_time;
88 }
89
90 double timeToNumber(const QTime &time)
91 {
92 return QTime(0,0).msecsTo(time) / (1000*60*60*24.0);
93 }
94
95 QDateTime datetimeFromNumber(double num, bool is1904)
96 {
97 if (!is1904 && num > 60)
98 num = num - 1;
99
100 qint64 msecs = static_cast<qint64>(num * 1000*60*60*24.0 + 0.5);
101 QDateTime epoch(is1904 ? QDate(1904, 1, 1): QDate(1899, 12, 31), QTime(0,0));
102
103 QDateTime dt = epoch.addMSecs(msecs);
104
105 #if QT_VERSION >= 0x050200
106 // Remove one hour to see whether the date is Daylight
107 QDateTime dt2 = dt.addMSecs(-3600);
108 if (dt2.isDaylightTime())
109 return dt2;
110 #endif
111
112 return dt;
113 }
114
115 /*
116 Creates a valid sheet name
117 minimum length is 1
118 maximum length is 31
119 doesn't contain special chars: / \ ? * ] [ :
120 Sheet names must not begin or end with ' (apostrophe)
121
122 Invalid characters are replaced by one space character ' '.
123 */
124 QString createSafeSheetName(const QString &nameProposal)
125 {
126 if (nameProposal.isEmpty())
127 return QString();
128
129 QString ret = nameProposal;
130 if (nameProposal.length() > 2 && nameProposal.startsWith(QLatin1Char('\'')) && nameProposal.endsWith(QLatin1Char('\'')))
131 ret = unescapeSheetName(ret);
132
133 //Replace invalid chars with space.
134 if (nameProposal.contains(QRegularExpression(QStringLiteral("[/\\\\?*\\][:]"))))
135 ret.replace(QRegularExpression(QStringLiteral("[/\\\\?*\\][:]")), QStringLiteral(" "));
136 if (ret.startsWith(QLatin1Char('\'')))
137 ret[0] = QLatin1Char(' ');
138 if (ret.endsWith(QLatin1Char('\'')))
139 ret[ret.size()-1] = QLatin1Char(' ');
140
141 if (ret.size() > 31)
142 ret = ret.left(31);
143 return ret;
144 }
145
146 /*
147 * When sheetName contains space or apostrophe, escaped is needed by cellFormula/definedName/chartSerials.
148 */
149 QString escapeSheetName(const QString &sheetName)
150 {
151 //Already escaped.
152 Q_ASSERT(!sheetName.startsWith(QLatin1Char('\'')) && !sheetName.endsWith(QLatin1Char('\'')));
153
154 //These is no need to escape
155 if (!sheetName.contains(QRegularExpression(QStringLiteral("[ +\\-,%^=<>'&]"))))
156 return sheetName;
157
158 //OK, escape is needed.
159 QString name = sheetName;
160 name.replace(QLatin1Char('\''), QLatin1String("\'\'"));
161 return QLatin1Char('\'') + name + QLatin1Char('\'');
162 }
163
164 /*
165 */
166 QString unescapeSheetName(const QString &sheetName)
167 {
168 Q_ASSERT(sheetName.length() > 2 && sheetName.startsWith(QLatin1Char('\'')) && sheetName.endsWith(QLatin1Char('\'')));
169
170 QString name = sheetName.mid(1, sheetName.length()-2);
171 name.replace(QLatin1String("\'\'"), QLatin1String("\'"));
172 return name;
173 }
174
175 /*
176 * whether the string s starts or ends with space
177 */
178 bool isSpaceReserveNeeded(const QString &s)
179 {
180 QString spaces(QStringLiteral(" \t\n\r"));
181 return !s.isEmpty() && (spaces.contains(s.at(0))||spaces.contains(s.at(s.length()-1)));
182 }
183
184 /*
185 * Convert shared formula for non-root cells.
186 *
187 * For example, if "B1:B10" have shared formula "=A1*A1", this function will return "=A2*A2"
188 * for "B2" cell, "=A3*A3" for "B3" cell, etc.
189 *
190 * Note, the formula "=A1*A1" for B1 can also be written as "=RC[-1]*RC[-1]", which is the same
191 * for all other cells. In other words, this formula is shared.
192 *
193 * For long run, we need a formula parser.
194 */
195 QString convertSharedFormula(const QString &rootFormula, const CellReference &rootCell, const CellReference &cell)
196 {
197 //Find all the "$?[A-Z]+$?[0-9]+" patterns in the rootFormula.
198 QList<QPair<QString, int> > segments;
199
200 QString segment;
201 bool inQuote = false;
202 enum RefState{INVALID, PRE_AZ, AZ, PRE_09, _09};
203 RefState refState = INVALID;
204 int refFlag = 0; // 0x00, 0x01, 0x02, 0x03 ==> A1, $A1, A$1, $A$1
205 foreach (QChar ch, rootFormula) {
206 if (inQuote) {
207 segment.append(ch);
208 if (ch == QLatin1Char('"'))
209 inQuote = false;
210 } else {
211 if (ch == QLatin1Char('"')) {
212 inQuote = true;
213 refState = INVALID;
214 segment.append(ch);
215 } else if (ch == QLatin1Char('$')) {
216 if (refState == AZ) {
217 segment.append(ch);
218 refState = PRE_09;
219 refFlag |= 0x02;
220 } else {
221 segments.append(qMakePair(segment, refState==_09 ? refFlag : -1));
222 segment = QString(ch); //Start new segment.
223 refState = PRE_AZ;
224 refFlag = 0x01;
225 }
226 } else if (ch >= QLatin1Char('A') && ch <=QLatin1Char('Z')) {
227 if (refState == PRE_AZ || refState == AZ) {
228 segment.append(ch);
229 } else {
230 segments.append(qMakePair(segment, refState==_09 ? refFlag : -1));
231 segment = QString(ch); //Start new segment.
232 refFlag = 0x00;
233 }
234 refState = AZ;
235 } else if (ch >= QLatin1Char('0') && ch <=QLatin1Char('9')) {
236 segment.append(ch);
237
238 if (refState == AZ || refState == PRE_09 || refState == _09)
239 refState = _09;
240 else
241 refState = INVALID;
242 } else {
243 if (refState == _09) {
244 segments.append(qMakePair(segment, refFlag));
245 segment = QString(ch); //Start new segment.
246 } else {
247 segment.append(ch);
248 }
249 refState = INVALID;
250 }
251 }
252 }
253
254 if (!segment.isEmpty())
255 segments.append(qMakePair(segment, refState==_09 ? refFlag : -1));
256
257 //Replace "A1", "$A1", "A$1" segment with proper one.
258 QStringList result;
259 typedef QPair<QString, int> PairType;
260 foreach (PairType p, segments) {
261 //qDebug()<<p.first<<p.second;
262 if (p.second != -1 && p.second != 3) {
263 CellReference oldRef(p.first);
264 int row = p.second & 0x02 ? oldRef.row() : oldRef.row()-rootCell.row()+cell.row();
265 int col = p.second & 0x01 ? oldRef.column() : oldRef.column()-rootCell.column()+cell.column();
266 result.append(CellReference(row, col).toString(p.second & 0x02, p.second & 0x01));
267 } else {
268 result.append(p.first);
269 }
270 }
271
272 //OK
273 return result.join(QString());
274 }
275
276 } //namespace QXlsx
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)