Mercurial > clickerconvert > clickerconvert

comparison src/xlsx/xlsxconditionalformatting.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 4ecb91aaf33f
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
26 #include "xlsxconditionalformatting.h"
27 #include "xlsxconditionalformatting_p.h"
28 #include "xlsxworksheet.h"
29 #include "xlsxcellrange.h"
30 #include "xlsxstyles_p.h"
31
32 #include <QXmlStreamReader>
33 #include <QXmlStreamWriter>
34 #include <QDebug>
35
36 QT_BEGIN_NAMESPACE_XLSX
37
38 ConditionalFormattingPrivate::ConditionalFormattingPrivate()
39 {
40
41 }
42
43 ConditionalFormattingPrivate::ConditionalFormattingPrivate(const ConditionalFormattingPrivate &other)
44 :QSharedData(other)
45 {
46
47 }
48
49 ConditionalFormattingPrivate::~ConditionalFormattingPrivate()
50 {
51
52 }
53
54 void ConditionalFormattingPrivate::writeCfVo(QXmlStreamWriter &writer, const XlsxCfVoData &cfvo) const
55 {
56 writer.writeEmptyElement(QStringLiteral("cfvo"));
57 QString type;
58 switch(cfvo.type) {
59 case ConditionalFormatting::VOT_Formula: type=QStringLiteral("formula"); break;
60 case ConditionalFormatting::VOT_Max: type=QStringLiteral("max"); break;
61 case ConditionalFormatting::VOT_Min: type=QStringLiteral("min"); break;
62 case ConditionalFormatting::VOT_Num: type=QStringLiteral("num"); break;
63 case ConditionalFormatting::VOT_Percent: type=QStringLiteral("percent"); break;
64 case ConditionalFormatting::VOT_Percentile: type=QStringLiteral("percentile"); break;
65 default: break;
66 }
67 writer.writeAttribute(QStringLiteral("type"), type);
68 writer.writeAttribute(QStringLiteral("val"), cfvo.value);
69 if (!cfvo.gte)
70 writer.writeAttribute(QStringLiteral("gte"), QStringLiteral("0"));
71 }
72
73 /*!
74 * \class ConditionalFormatting
75 * \brief Conditional formatting for single cell or ranges
76 * \inmodule QtXlsx
77 *
78 * The conditional formatting can be applied to a single cell or ranges of cells.
79 */
80
81
82 /*!
83 \enum ConditionalFormatting::HighlightRuleType
84
85 \value Highlight_LessThan
86 \value Highlight_LessThanOrEqual
87 \value Highlight_Equal
88 \value Highlight_NotEqual
89 \value Highlight_GreaterThanOrEqual
90 \value Highlight_GreaterThan
91 \value Highlight_Between
92 \value Highlight_NotBetween
93
94 \value Highlight_ContainsText
95 \value Highlight_NotContainsText
96 \value Highlight_BeginsWith
97 \value Highlight_EndsWith
98
99 \value Highlight_TimePeriod
100
101 \value Highlight_Duplicate
102 \value Highlight_Unique
103
104 \value Highlight_Blanks
105 \value Highlight_NoBlanks
106 \value Highlight_Errors
107 \value Highlight_NoErrors
108
109 \value Highlight_Top
110 \value Highlight_TopPercent
111 \value Highlight_Bottom
112 \value Highlight_BottomPercent
113
114 \value Highlight_AboveAverage
115 \value Highlight_AboveOrEqualAverage
116 \value Highlight_BelowAverage
117 \value Highlight_BelowOrEqualAverage
118 \value Highlight_AboveStdDev1
119 \value Highlight_AboveStdDev2
120 \value Highlight_AboveStdDev3
121 \value Highlight_BelowStdDev1
122 \value Highlight_BelowStdDev2
123 \value Highlight_BelowStdDev3
124
125 \value Highlight_Expression
126 */
127
128 /*!
129 \enum ConditionalFormatting::ValueObjectType
130
131 \value VOT_Formula
132 \value VOT_Max
133 \value VOT_Min
134 \value VOT_Num
135 \value VOT_Percent
136 \value VOT_Percentile
137 */
138
139 /*!
140 Construct a conditional formatting object
141 */
142 ConditionalFormatting::ConditionalFormatting()
143 :d(new ConditionalFormattingPrivate())
144 {
145
146 }
147
148 /*!
149 Constructs a copy of \a other.
150 */
151 ConditionalFormatting::ConditionalFormatting(const ConditionalFormatting &other)
152 :d(other.d)
153 {
154
155 }
156
157 /*!
158 Assigns \a other to this conditional formatting and returns a reference to
159 this conditional formatting.
160 */
161 ConditionalFormatting &ConditionalFormatting::operator=(const ConditionalFormatting &other)
162 {
163 this->d = other.d;
164 return *this;
165 }
166
167
168 /*!
169 * Destroy the object.
170 */
171 ConditionalFormatting::~ConditionalFormatting()
172 {
173 }
174
175 /*!
176 * Add a hightlight rule with the given \a type, \a formula1, \a formula2,
177 * \a format and \a stopIfTrue.
178 * Return false if failed.
179 */
180 bool ConditionalFormatting::addHighlightCellsRule(HighlightRuleType type, const QString &formula1, const QString &formula2, const Format &format, bool stopIfTrue)
181 {
182 if (format.isEmpty())
183 return false;
184
185 bool skipFormula = false;
186
187 QSharedPointer<XlsxCfRuleData> cfRule(new XlsxCfRuleData);
188 if (type >= Highlight_LessThan && type <= Highlight_NotBetween) {
189 cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("cellIs");
190 QString op;
191 switch (type) {
192 case Highlight_Between: op = QStringLiteral("between"); break;
193 case Highlight_Equal: op = QStringLiteral("equal"); break;
194 case Highlight_GreaterThan: op = QStringLiteral("greaterThan"); break;
195 case Highlight_GreaterThanOrEqual: op = QStringLiteral("greaterThanOrEqual"); break;
196 case Highlight_LessThan: op = QStringLiteral("lessThan"); break;
197 case Highlight_LessThanOrEqual: op = QStringLiteral("lessThanOrEqual"); break;
198 case Highlight_NotBetween: op = QStringLiteral("notBetween"); break;
199 case Highlight_NotEqual: op = QStringLiteral("notEqual"); break;
200 default: break;
201 }
202 cfRule->attrs[XlsxCfRuleData::A_operator] = op;
203 } else if (type >= Highlight_ContainsText && type <= Highlight_EndsWith) {
204 if (type == Highlight_ContainsText) {
205 cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("containsText");
206 cfRule->attrs[XlsxCfRuleData::A_operator] = QStringLiteral("containsText");
207 cfRule->attrs[XlsxCfRuleData::A_formula1_temp] = QStringLiteral("NOT(ISERROR(SEARCH(\"%1\",%2)))").arg(formula1);
208 } else if (type == Highlight_NotContainsText) {
209 cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("notContainsText");
210 cfRule->attrs[XlsxCfRuleData::A_operator] = QStringLiteral("notContains");
211 cfRule->attrs[XlsxCfRuleData::A_formula1_temp] = QStringLiteral("ISERROR(SEARCH(\"%2\",%1))").arg(formula1);
212 } else if (type == Highlight_BeginsWith) {
213 cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("beginsWith");
214 cfRule->attrs[XlsxCfRuleData::A_operator] = QStringLiteral("beginsWith");
215 cfRule->attrs[XlsxCfRuleData::A_formula1_temp] = QStringLiteral("LEFT(%2,LEN(\"%1\"))=\"%1\"").arg(formula1);
216 } else {
217 cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("endsWith");
218 cfRule->attrs[XlsxCfRuleData::A_operator] = QStringLiteral("endsWith");
219 cfRule->attrs[XlsxCfRuleData::A_formula1_temp] = QStringLiteral("RIGHT(%2,LEN(\"%1\"))=\"%1\"").arg(formula1);
220 }
221 cfRule->attrs[XlsxCfRuleData::A_text] = formula1;
222 skipFormula = true;
223 } else if (type == Highlight_TimePeriod) {
224 cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("timePeriod");
225 //:Todo
226 return false;
227 } else if (type == Highlight_Duplicate) {
228 cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("duplicateValues");
229 } else if (type == Highlight_Unique) {
230 cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("uniqueValues");
231 } else if (type == Highlight_Errors) {
232 cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("containsErrors");
233 cfRule->attrs[XlsxCfRuleData::A_formula1_temp] = QStringLiteral("ISERROR(%1)");
234 skipFormula = true;
235 } else if (type == Highlight_NoErrors) {
236 cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("notContainsErrors");
237 cfRule->attrs[XlsxCfRuleData::A_formula1_temp] = QStringLiteral("NOT(ISERROR(%1))");
238 skipFormula = true;
239 } else if (type == Highlight_Blanks) {
240 cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("containsBlanks");
241 cfRule->attrs[XlsxCfRuleData::A_formula1_temp] = QStringLiteral("LEN(TRIM(%1))=0");
242 skipFormula = true;
243 } else if (type == Highlight_NoBlanks) {
244 cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("notContainsBlanks");
245 cfRule->attrs[XlsxCfRuleData::A_formula1_temp] = QStringLiteral("LEN(TRIM(%1))>0");
246 skipFormula = true;
247 } else if (type >= Highlight_Top && type <= Highlight_BottomPercent) {
248 cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("top10");
249 if (type == Highlight_Bottom || type == Highlight_BottomPercent)
250 cfRule->attrs[XlsxCfRuleData::A_bottom] = QStringLiteral("1");
251 if (type == Highlight_TopPercent || type == Highlight_BottomPercent)
252 cfRule->attrs[XlsxCfRuleData::A_percent] = QStringLiteral("1");
253 cfRule->attrs[XlsxCfRuleData::A_rank] = !formula1.isEmpty() ? formula1 : QStringLiteral("10");
254 skipFormula = true;
255 } else if (type >= Highlight_AboveAverage && type <= Highlight_BelowStdDev3) {
256 cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("aboveAverage");
257 if (type >= Highlight_BelowAverage && type <= Highlight_BelowStdDev3)
258 cfRule->attrs[XlsxCfRuleData::A_aboveAverage] = QStringLiteral("0");
259 if (type == Highlight_AboveOrEqualAverage || type == Highlight_BelowOrEqualAverage)
260 cfRule->attrs[XlsxCfRuleData::A_equalAverage] = QStringLiteral("1");
261 if (type == Highlight_AboveStdDev1 || type == Highlight_BelowStdDev1)
262 cfRule->attrs[XlsxCfRuleData::A_stdDev] = QStringLiteral("1");
263 else if (type == Highlight_AboveStdDev2 || type == Highlight_BelowStdDev2)
264 cfRule->attrs[XlsxCfRuleData::A_stdDev] = QStringLiteral("2");
265 else if (type == Highlight_AboveStdDev3 || type == Highlight_BelowStdDev3)
266 cfRule->attrs[XlsxCfRuleData::A_stdDev] = QStringLiteral("3");
267 } else if (type == Highlight_Expression){
268 cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("expression");
269 } else {
270 return false;
271 }
272
273 cfRule->dxfFormat = format;
274 if (stopIfTrue)
275 cfRule->attrs[XlsxCfRuleData::A_stopIfTrue] = true;
276 if (!skipFormula) {
277 if (!formula1.isEmpty())
278 cfRule->attrs[XlsxCfRuleData::A_formula1] = formula1.startsWith(QLatin1String("=")) ? formula1.mid(1) : formula1;
279 if (!formula2.isEmpty())
280 cfRule->attrs[XlsxCfRuleData::A_formula2] = formula2.startsWith(QLatin1String("=")) ? formula2.mid(1) : formula2;
281 }
282 d->cfRules.append(cfRule);
283 return true;
284 }
285
286 /*!
287 * \overload
288 *
289 * Add a hightlight rule with the given \a type \a format and \a stopIfTrue.
290 */
291 bool ConditionalFormatting::addHighlightCellsRule(HighlightRuleType type, const Format &format, bool stopIfTrue)
292 {
293 if ((type >= Highlight_AboveAverage && type <= Highlight_BelowStdDev3)
294 || (type >= Highlight_Duplicate && type <= Highlight_NoErrors)) {
295 return addHighlightCellsRule(type, QString(), QString(), format, stopIfTrue);
296 }
297
298 return false;
299 }
300
301 /*!
302 * \overload
303 *
304 * Add a hightlight rule with the given \a type, \a formula, \a format and \a stopIfTrue.
305 * Return false if failed.
306 */
307 bool ConditionalFormatting::addHighlightCellsRule(HighlightRuleType type, const QString &formula, const Format &format, bool stopIfTrue)
308 {
309 if (type == Highlight_Between || type == Highlight_NotBetween)
310 return false;
311
312 return addHighlightCellsRule(type, formula, QString(), format, stopIfTrue);
313 }
314
315 /*!
316 * Add a dataBar rule with the given \a color, \a type1, \a val1
317 * , \a type2, \a val2, \a showData and \a stopIfTrue.
318 * Return false if failed.
319 */
320 bool ConditionalFormatting::addDataBarRule(const QColor &color, ValueObjectType type1, const QString &val1, ValueObjectType type2, const QString &val2, bool showData, bool stopIfTrue)
321 {
322 QSharedPointer<XlsxCfRuleData> cfRule(new XlsxCfRuleData);
323
324 cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("dataBar");
325 cfRule->attrs[XlsxCfRuleData::A_color1] = XlsxColor(color);
326 if (stopIfTrue)
327 cfRule->attrs[XlsxCfRuleData::A_stopIfTrue] = true;
328 if (!showData)
329 cfRule->attrs[XlsxCfRuleData::A_hideData] = true;
330
331 XlsxCfVoData cfvo1(type1, val1);
332 XlsxCfVoData cfvo2(type2, val2);
333 cfRule->attrs[XlsxCfRuleData::A_cfvo1] = QVariant::fromValue(cfvo1);
334 cfRule->attrs[XlsxCfRuleData::A_cfvo2] = QVariant::fromValue(cfvo2);
335
336 d->cfRules.append(cfRule);
337 return true;
338 }
339
340 /*!
341 * \overload
342 * Add a dataBar rule with the given \a color, \a showData and \a stopIfTrue.
343 */
344 bool ConditionalFormatting::addDataBarRule(const QColor &color, bool showData, bool stopIfTrue)
345 {
346 return addDataBarRule(color, VOT_Min, QStringLiteral("0"), VOT_Max, QStringLiteral("0"), showData, stopIfTrue);
347 }
348
349 /*!
350 * Add a colorScale rule with the given \a minColor, \a maxColor and \a stopIfTrue.
351 * Return false if failed.
352 */
353 bool ConditionalFormatting::add2ColorScaleRule(const QColor &minColor, const QColor &maxColor, bool stopIfTrue)
354 {
355 ValueObjectType type1 = VOT_Min;
356 ValueObjectType type2 = VOT_Max;
357 QString val1 = QStringLiteral("0");
358 QString val2 = QStringLiteral("0");
359
360 QSharedPointer<XlsxCfRuleData> cfRule(new XlsxCfRuleData);
361
362 cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("colorScale");
363 cfRule->attrs[XlsxCfRuleData::A_color1] = XlsxColor(minColor);
364 cfRule->attrs[XlsxCfRuleData::A_color2] = XlsxColor(maxColor);
365 if (stopIfTrue)
366 cfRule->attrs[XlsxCfRuleData::A_stopIfTrue] = true;
367
368 XlsxCfVoData cfvo1(type1, val1);
369 XlsxCfVoData cfvo2(type2, val2);
370 cfRule->attrs[XlsxCfRuleData::A_cfvo1] = QVariant::fromValue(cfvo1);
371 cfRule->attrs[XlsxCfRuleData::A_cfvo2] = QVariant::fromValue(cfvo2);
372
373 d->cfRules.append(cfRule);
374 return true;
375 }
376
377 /*!
378 * Add a colorScale rule with the given \a minColor, \a midColor, \a maxColor and \a stopIfTrue.
379 * Return false if failed.
380 */
381 bool ConditionalFormatting::add3ColorScaleRule(const QColor &minColor, const QColor &midColor, const QColor &maxColor, bool stopIfTrue)
382 {
383 ValueObjectType type1 = VOT_Min;
384 ValueObjectType type2 = VOT_Percent;
385 ValueObjectType type3 = VOT_Max;
386 QString val1 = QStringLiteral("0");
387 QString val2 = QStringLiteral("50");
388 QString val3 = QStringLiteral("0");
389
390 QSharedPointer<XlsxCfRuleData> cfRule(new XlsxCfRuleData);
391
392 cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("colorScale");
393 cfRule->attrs[XlsxCfRuleData::A_color1] = XlsxColor(minColor);
394 cfRule->attrs[XlsxCfRuleData::A_color2] = XlsxColor(midColor);
395 cfRule->attrs[XlsxCfRuleData::A_color3] = XlsxColor(maxColor);
396
397 if (stopIfTrue)
398 cfRule->attrs[XlsxCfRuleData::A_stopIfTrue] = true;
399
400 XlsxCfVoData cfvo1(type1, val1);
401 XlsxCfVoData cfvo2(type2, val2);
402 XlsxCfVoData cfvo3(type3, val3);
403 cfRule->attrs[XlsxCfRuleData::A_cfvo1] = QVariant::fromValue(cfvo1);
404 cfRule->attrs[XlsxCfRuleData::A_cfvo2] = QVariant::fromValue(cfvo2);
405 cfRule->attrs[XlsxCfRuleData::A_cfvo3] = QVariant::fromValue(cfvo3);
406
407 d->cfRules.append(cfRule);
408 return true;
409 }
410
411 /*!
412 Returns the ranges on which the validation will be applied.
413 */
414 QList<CellRange> ConditionalFormatting::ranges() const
415 {
416 return d->ranges;
417 }
418
419 /*!
420 Add the \a cell on which the conditional formatting will apply to.
421 */
422 void ConditionalFormatting::addCell(const CellReference &cell)
423 {
424 d->ranges.append(CellRange(cell, cell));
425 }
426
427 /*!
428 \overload
429 Add the cell(\a row, \a col) on which the conditional formatting will apply to.
430 */
431 void ConditionalFormatting::addCell(int row, int col)
432 {
433 d->ranges.append(CellRange(row, col, row, col));
434 }
435
436 /*!
437 \overload
438 Add the range(\a firstRow, \a firstCol, \a lastRow, \a lastCol) on
439 which the conditional formatting will apply to.
440 */
441 void ConditionalFormatting::addRange(int firstRow, int firstCol, int lastRow, int lastCol)
442 {
443 d->ranges.append(CellRange(firstRow, firstCol, lastRow, lastCol));
444 }
445
446 /*!
447 Add the \a range on which the conditional formatting will apply to.
448 */
449 void ConditionalFormatting::addRange(const CellRange &range)
450 {
451 d->ranges.append(range);
452 }
453
454 bool ConditionalFormattingPrivate::readCfRule(QXmlStreamReader &reader, XlsxCfRuleData *rule, Styles *styles)
455 {
456 Q_ASSERT(reader.name() == QLatin1String("cfRule"));
457 QXmlStreamAttributes attrs = reader.attributes();
458 if (attrs.hasAttribute(QLatin1String("type")))
459 rule->attrs[XlsxCfRuleData::A_type] = attrs.value(QLatin1String("type")).toString();
460 if (attrs.hasAttribute(QLatin1String("dxfId"))) {
461 int id = attrs.value(QLatin1String("dxfId")).toString().toInt();
462 if (styles)
463 rule->dxfFormat = styles->dxfFormat(id);
464 else
465 rule->dxfFormat.setDxfIndex(id);
466 }
467 rule->priority = attrs.value(QLatin1String("priority")).toString().toInt();
468 if (attrs.value(QLatin1String("stopIfTrue")) == QLatin1String("1")) {
469 //default is false
470 rule->attrs[XlsxCfRuleData::A_stopIfTrue] = QLatin1String("1");
471 }
472 if (attrs.value(QLatin1String("aboveAverage")) == QLatin1String("0")) {
473 //default is true
474 rule->attrs[XlsxCfRuleData::A_aboveAverage] = QLatin1String("0");
475 }
476 if (attrs.value(QLatin1String("percent")) == QLatin1String("1")) {
477 //default is false
478 rule->attrs[XlsxCfRuleData::A_percent] = QLatin1String("1");
479 }
480 if (attrs.value(QLatin1String("bottom")) == QLatin1String("1")) {
481 //default is false
482 rule->attrs[XlsxCfRuleData::A_bottom] = QLatin1String("1");
483 }
484 if (attrs.hasAttribute(QLatin1String("operator")))
485 rule->attrs[XlsxCfRuleData::A_operator] = attrs.value(QLatin1String("operator")).toString();
486
487 if (attrs.hasAttribute(QLatin1String("text")))
488 rule->attrs[XlsxCfRuleData::A_text] = attrs.value(QLatin1String("text")).toString();
489
490 if (attrs.hasAttribute(QLatin1String("timePeriod")))
491 rule->attrs[XlsxCfRuleData::A_timePeriod] = attrs.value(QLatin1String("timePeriod")).toString();
492
493 if (attrs.hasAttribute(QLatin1String("rank")))
494 rule->attrs[XlsxCfRuleData::A_rank] = attrs.value(QLatin1String("rank")).toString();
495
496 if (attrs.hasAttribute(QLatin1String("stdDev")))
497 rule->attrs[XlsxCfRuleData::A_stdDev] = attrs.value(QLatin1String("stdDev")).toString();
498
499 if (attrs.value(QLatin1String("equalAverage")) == QLatin1String("1")) {
500 //default is false
501 rule->attrs[XlsxCfRuleData::A_equalAverage] = QLatin1String("1");
502 }
503
504 while (!reader.atEnd()) {
505 reader.readNextStartElement();
506 if (reader.tokenType() == QXmlStreamReader::StartElement) {
507 if (reader.name() == QLatin1String("formula")) {
508 QString f = reader.readElementText();
509 if (!rule->attrs.contains(XlsxCfRuleData::A_formula1))
510 rule->attrs[XlsxCfRuleData::A_formula1] = f;
511 else if (!rule->attrs.contains(XlsxCfRuleData::A_formula2))
512 rule->attrs[XlsxCfRuleData::A_formula2] = f;
513 else if (!rule->attrs.contains(XlsxCfRuleData::A_formula3))
514 rule->attrs[XlsxCfRuleData::A_formula3] = f;
515 } else if (reader.name() == QLatin1String("dataBar")) {
516 readCfDataBar(reader, rule);
517 } else if (reader.name() == QLatin1String("colorScale")) {
518 readCfColorScale(reader, rule);
519 }
520 }
521 if (reader.tokenType() == QXmlStreamReader::EndElement
522 && reader.name() == QStringLiteral("conditionalFormatting")) {
523 break;
524 }
525 }
526 return true;
527 }
528
529 bool ConditionalFormattingPrivate::readCfDataBar(QXmlStreamReader &reader, XlsxCfRuleData *rule)
530 {
531 Q_ASSERT(reader.name() == QLatin1String("dataBar"));
532 QXmlStreamAttributes attrs = reader.attributes();
533 if (attrs.value(QLatin1String("showValue")) == QLatin1String("0"))
534 rule->attrs[XlsxCfRuleData::A_hideData] = QStringLiteral("1");
535
536 while (!reader.atEnd()) {
537 reader.readNextStartElement();
538 if (reader.tokenType() == QXmlStreamReader::StartElement) {
539 if (reader.name() == QLatin1String("cfvo")) {
540 XlsxCfVoData data;
541 readCfVo(reader, data);
542 if (!rule->attrs.contains(XlsxCfRuleData::A_cfvo1))
543 rule->attrs[XlsxCfRuleData::A_cfvo1] = QVariant::fromValue(data);
544 else
545 rule->attrs[XlsxCfRuleData::A_cfvo2] = QVariant::fromValue(data);
546 } else if (reader.name() == QLatin1String("color")) {
547 XlsxColor color;
548 color.loadFromXml(reader);
549 rule->attrs[XlsxCfRuleData::A_color1] = color;
550 }
551 }
552 if (reader.tokenType() == QXmlStreamReader::EndElement
553 && reader.name() == QStringLiteral("dataBar")) {
554 break;
555 }
556 }
557
558 return true;
559 }
560
561 bool ConditionalFormattingPrivate::readCfColorScale(QXmlStreamReader &reader, XlsxCfRuleData *rule)
562 {
563 Q_ASSERT(reader.name() == QLatin1String("colorScale"));
564
565 while (!reader.atEnd()) {
566 reader.readNextStartElement();
567 if (reader.tokenType() == QXmlStreamReader::StartElement) {
568 if (reader.name() == QLatin1String("cfvo")) {
569 XlsxCfVoData data;
570 readCfVo(reader, data);
571 if (!rule->attrs.contains(XlsxCfRuleData::A_cfvo1))
572 rule->attrs[XlsxCfRuleData::A_cfvo1] = QVariant::fromValue(data);
573 else if (!rule->attrs.contains(XlsxCfRuleData::A_cfvo2))
574 rule->attrs[XlsxCfRuleData::A_cfvo2] = QVariant::fromValue(data);
575 else
576 rule->attrs[XlsxCfRuleData::A_cfvo2] = QVariant::fromValue(data);
577 } else if (reader.name() == QLatin1String("color")) {
578 XlsxColor color;
579 color.loadFromXml(reader);
580 if (!rule->attrs.contains(XlsxCfRuleData::A_color1))
581 rule->attrs[XlsxCfRuleData::A_color1] = color;
582 else if (!rule->attrs.contains(XlsxCfRuleData::A_color2))
583 rule->attrs[XlsxCfRuleData::A_color2] = color;
584 else
585 rule->attrs[XlsxCfRuleData::A_color3] = color;
586 }
587 }
588 if (reader.tokenType() == QXmlStreamReader::EndElement
589 && reader.name() == QStringLiteral("colorScale")) {
590 break;
591 }
592 }
593
594 return true;
595 }
596
597 bool ConditionalFormattingPrivate::readCfVo(QXmlStreamReader &reader, XlsxCfVoData &cfvo)
598 {
599 Q_ASSERT(reader.name() == QStringLiteral("cfvo"));
600
601 QXmlStreamAttributes attrs = reader.attributes();
602
603 QString type = attrs.value(QLatin1String("type")).toString();
604 ConditionalFormatting::ValueObjectType t;
605 if (type == QLatin1String("formula"))
606 t = ConditionalFormatting::VOT_Formula;
607 else if (type == QLatin1String("max"))
608 t = ConditionalFormatting::VOT_Max;
609 else if (type == QLatin1String("min"))
610 t = ConditionalFormatting::VOT_Min;
611 else if (type == QLatin1String("num"))
612 t = ConditionalFormatting::VOT_Num;
613 else if (type == QLatin1String("percent"))
614 t = ConditionalFormatting::VOT_Percent;
615 else //if (type == QLatin1String("percentile"))
616 t = ConditionalFormatting::VOT_Percentile;
617
618 cfvo.type = t;
619 cfvo.value = attrs.value(QLatin1String("val")).toString();
620 if (attrs.value(QLatin1String("gte")) == QLatin1String("0")) {
621 //default is true
622 cfvo.gte = false;
623 }
624 return true;
625 }
626
627 bool ConditionalFormatting::loadFromXml(QXmlStreamReader &reader, Styles *styles)
628 {
629 Q_ASSERT(reader.name() == QStringLiteral("conditionalFormatting"));
630
631 d->ranges.clear();
632 d->cfRules.clear();
633 QXmlStreamAttributes attrs = reader.attributes();
634 QString sqref = attrs.value(QLatin1String("sqref")).toString();
635 foreach (QString range, sqref.split(QLatin1Char(' ')))
636 this->addRange(range);
637
638 while (!reader.atEnd()) {
639 reader.readNextStartElement();
640 if (reader.tokenType() == QXmlStreamReader::StartElement) {
641 if (reader.name() == QLatin1String("cfRule")) {
642 QSharedPointer<XlsxCfRuleData> cfRule(new XlsxCfRuleData);
643 d->readCfRule(reader, cfRule.data(), styles);
644 d->cfRules.append(cfRule);
645 }
646 }
647 if (reader.tokenType() == QXmlStreamReader::EndElement
648 && reader.name() == QStringLiteral("conditionalFormatting")) {
649 break;
650 }
651 }
652
653
654 return true;
655 }
656
657 bool ConditionalFormatting::saveToXml(QXmlStreamWriter &writer) const
658 {
659 writer.writeStartElement(QStringLiteral("conditionalFormatting"));
660 QStringList sqref;
661 foreach (CellRange range, ranges())
662 sqref.append(range.toString());
663 writer.writeAttribute(QStringLiteral("sqref"), sqref.join(QLatin1Char(' ')));
664
665 for (int i=0; i<d->cfRules.size(); ++i) {
666 const QSharedPointer<XlsxCfRuleData> &rule = d->cfRules[i];
667 writer.writeStartElement(QStringLiteral("cfRule"));
668 writer.writeAttribute(QStringLiteral("type"), rule->attrs[XlsxCfRuleData::A_type].toString());
669 if (rule->dxfFormat.dxfIndexValid())
670 writer.writeAttribute(QStringLiteral("dxfId"), QString::number(rule->dxfFormat.dxfIndex()));
671 writer.writeAttribute(QStringLiteral("priority"), QString::number(rule->priority));
672 if (rule->attrs.contains(XlsxCfRuleData::A_stopIfTrue))
673 writer.writeAttribute(QStringLiteral("stopIfTrue"), rule->attrs[XlsxCfRuleData::A_stopIfTrue].toString());
674 if (rule->attrs.contains(XlsxCfRuleData::A_aboveAverage))
675 writer.writeAttribute(QStringLiteral("aboveAverage"), rule->attrs[XlsxCfRuleData::A_aboveAverage].toString());
676 if (rule->attrs.contains(XlsxCfRuleData::A_percent))
677 writer.writeAttribute(QStringLiteral("percent"), rule->attrs[XlsxCfRuleData::A_percent].toString());
678 if (rule->attrs.contains(XlsxCfRuleData::A_bottom))
679 writer.writeAttribute(QStringLiteral("bottom"), rule->attrs[XlsxCfRuleData::A_bottom].toString());
680 if (rule->attrs.contains(XlsxCfRuleData::A_operator))
681 writer.writeAttribute(QStringLiteral("operator"), rule->attrs[XlsxCfRuleData::A_operator].toString());
682 if (rule->attrs.contains(XlsxCfRuleData::A_text))
683 writer.writeAttribute(QStringLiteral("text"), rule->attrs[XlsxCfRuleData::A_text].toString());
684 if (rule->attrs.contains(XlsxCfRuleData::A_timePeriod))
685 writer.writeAttribute(QStringLiteral("timePeriod"), rule->attrs[XlsxCfRuleData::A_timePeriod].toString());
686 if (rule->attrs.contains(XlsxCfRuleData::A_rank))
687 writer.writeAttribute(QStringLiteral("rank"), rule->attrs[XlsxCfRuleData::A_rank].toString());
688 if (rule->attrs.contains(XlsxCfRuleData::A_stdDev))
689 writer.writeAttribute(QStringLiteral("stdDev"), rule->attrs[XlsxCfRuleData::A_stdDev].toString());
690 if (rule->attrs.contains(XlsxCfRuleData::A_equalAverage))
691 writer.writeAttribute(QStringLiteral("equalAverage"), rule->attrs[XlsxCfRuleData::A_equalAverage].toString());
692
693 if (rule->attrs[XlsxCfRuleData::A_type] == QLatin1String("dataBar")) {
694 writer.writeStartElement(QStringLiteral("dataBar"));
695 if (rule->attrs.contains(XlsxCfRuleData::A_hideData))
696 writer.writeAttribute(QStringLiteral("showValue"), QStringLiteral("0"));
697 d->writeCfVo(writer, rule->attrs[XlsxCfRuleData::A_cfvo1].value<XlsxCfVoData>());
698 d->writeCfVo(writer, rule->attrs[XlsxCfRuleData::A_cfvo2].value<XlsxCfVoData>());
699 rule->attrs[XlsxCfRuleData::A_color1].value<XlsxColor>().saveToXml(writer);
700 writer.writeEndElement();//dataBar
701 } else if (rule->attrs[XlsxCfRuleData::A_type] == QLatin1String("colorScale")) {
702 writer.writeStartElement(QStringLiteral("colorScale"));
703 d->writeCfVo(writer, rule->attrs[XlsxCfRuleData::A_cfvo1].value<XlsxCfVoData>());
704 d->writeCfVo(writer, rule->attrs[XlsxCfRuleData::A_cfvo2].value<XlsxCfVoData>());
705 if (rule->attrs.contains(XlsxCfRuleData::A_cfvo3))
706 d->writeCfVo(writer, rule->attrs[XlsxCfRuleData::A_cfvo3].value<XlsxCfVoData>());
707
708 rule->attrs[XlsxCfRuleData::A_color1].value<XlsxColor>().saveToXml(writer);
709 rule->attrs[XlsxCfRuleData::A_color2].value<XlsxColor>().saveToXml(writer);
710 if (rule->attrs.contains(XlsxCfRuleData::A_color3))
711 rule->attrs[XlsxCfRuleData::A_color3].value<XlsxColor>().saveToXml(writer);
712
713 writer.writeEndElement();//colorScale
714 }
715
716
717 if (rule->attrs.contains(XlsxCfRuleData::A_formula1_temp)) {
718 QString startCell = ranges()[0].toString().split(QLatin1Char(':'))[0];
719 writer.writeTextElement(QStringLiteral("formula"), rule->attrs[XlsxCfRuleData::A_formula1_temp].toString().arg(startCell));
720 } else if (rule->attrs.contains(XlsxCfRuleData::A_formula1)) {
721 writer.writeTextElement(QStringLiteral("formula"), rule->attrs[XlsxCfRuleData::A_formula1].toString());
722 }
723 if (rule->attrs.contains(XlsxCfRuleData::A_formula2))
724 writer.writeTextElement(QStringLiteral("formula"), rule->attrs[XlsxCfRuleData::A_formula2].toString());
725 if (rule->attrs.contains(XlsxCfRuleData::A_formula3))
726 writer.writeTextElement(QStringLiteral("formula"), rule->attrs[XlsxCfRuleData::A_formula3].toString());
727
728 writer.writeEndElement(); //cfRule
729 }
730
731 writer.writeEndElement(); //conditionalFormatting
732 return true;
733 }
734
735 QT_END_NAMESPACE_XLSX
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)