Mercurial > clickerconvert

annotate src/xlsx/xlsxutility.cpp @ 39:41cd27a64365

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Write empty columns for XSLX to avoid strange 0 formatting Excel shows a pretty large bar for zero values
author Andre Heinecke <andre.heinecke@intevation.de>
date Fri, 15 Apr 2016 15:24:07 +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)