Mercurial > clickerconvert

annotate src/xlsx/xlsxutility.cpp @ 55:aaaf8cdbc85c

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