QXlsx/source/xlsxconditionalformatting.cpp

0001 // xlsxconditionalformatting.cpp
0002
0003 #include <QtGlobal>
0004 #include <QXmlStreamReader>
0005 #include <QXmlStreamWriter>
0006 #include <QDebug>
0007
0008 #include "xlsxconditionalformatting.h"
0009 #include "xlsxconditionalformatting_p.h"
0010 #include "xlsxworksheet.h"
0011 #include "xlsxcellrange.h"
0012 #include "xlsxstyles_p.h"
0013
0014 QT_BEGIN_NAMESPACE_XLSX
0015
0016 ConditionalFormattingPrivate::ConditionalFormattingPrivate()
0017 {
0018
0019 }
0020
0021 ConditionalFormattingPrivate::ConditionalFormattingPrivate(const ConditionalFormattingPrivate &other)
0022     :QSharedData(other)
0023 {
0024
0025 }
0026
0027 ConditionalFormattingPrivate::~ConditionalFormattingPrivate()
0028 {
0029
0030 }
0031
0032 void ConditionalFormattingPrivate::writeCfVo(QXmlStreamWriter &writer, const XlsxCfVoData &cfvo) const
0033 {
0034     writer.writeEmptyElement(QStringLiteral("cfvo"));
0035     QString type;
0036     switch(cfvo.type) {
0037     case ConditionalFormatting::VOT_Formula: type=QStringLiteral("formula"); break;
0038     case ConditionalFormatting::VOT_Max: type=QStringLiteral("max"); break;
0039     case ConditionalFormatting::VOT_Min: type=QStringLiteral("min"); break;
0040     case ConditionalFormatting::VOT_Num: type=QStringLiteral("num"); break;
0041     case ConditionalFormatting::VOT_Percent: type=QStringLiteral("percent"); break;
0042     case ConditionalFormatting::VOT_Percentile: type=QStringLiteral("percentile"); break;
0043     default: break;
0044     }
0045     writer.writeAttribute(QStringLiteral("type"), type);
0046     writer.writeAttribute(QStringLiteral("val"), cfvo.value);
0047     if (!cfvo.gte)
0048         writer.writeAttribute(QStringLiteral("gte"), QStringLiteral("0"));
0049 }
0050
0051 /*!
0052  * \class ConditionalFormatting
0053  * \brief Conditional formatting for single cell or ranges
0054  * \inmodule QtXlsx
0055  *
0056  * The conditional formatting can be applied to a single cell or ranges of cells.
0057  */
0058
0059
0060 /*!
0061     \enum ConditionalFormatting::HighlightRuleType
0062
0063     \value Highlight_LessThan
0064     \value Highlight_LessThanOrEqual
0065     \value Highlight_Equal
0066     \value Highlight_NotEqual
0067     \value Highlight_GreaterThanOrEqual
0068     \value Highlight_GreaterThan
0069     \value Highlight_Between
0070     \value Highlight_NotBetween
0071
0072     \value Highlight_ContainsText
0073     \value Highlight_NotContainsText
0074     \value Highlight_BeginsWith
0075     \value Highlight_EndsWith
0076
0077     \value Highlight_TimePeriod
0078
0079     \value Highlight_Duplicate
0080     \value Highlight_Unique
0081
0082     \value Highlight_Blanks
0083     \value Highlight_NoBlanks
0084     \value Highlight_Errors
0085     \value Highlight_NoErrors
0086
0087     \value Highlight_Top
0088     \value Highlight_TopPercent
0089     \value Highlight_Bottom
0090     \value Highlight_BottomPercent
0091
0092     \value Highlight_AboveAverage
0093     \value Highlight_AboveOrEqualAverage
0094     \value Highlight_BelowAverage
0095     \value Highlight_BelowOrEqualAverage
0096     \value Highlight_AboveStdDev1
0097     \value Highlight_AboveStdDev2
0098     \value Highlight_AboveStdDev3
0099     \value Highlight_BelowStdDev1
0100     \value Highlight_BelowStdDev2
0101     \value Highlight_BelowStdDev3
0102
0103     \value Highlight_Expression
0104 */
0105
0106 /*!
0107     \enum ConditionalFormatting::ValueObjectType
0108
0109     \value VOT_Formula
0110     \value VOT_Max
0111     \value VOT_Min
0112     \value VOT_Num
0113     \value VOT_Percent
0114     \value VOT_Percentile
0115 */
0116
0117 /*!
0118     Construct a conditional formatting object
0119 */
0120 ConditionalFormatting::ConditionalFormatting()
0121     :d(new ConditionalFormattingPrivate())
0122 {
0123
0124 }
0125
0126 /*!
0127     Constructs a copy of \a other.
0128 */
0129 ConditionalFormatting::ConditionalFormatting(const ConditionalFormatting &other)
0130     :d(other.d)
0131 {
0132
0133 }
0134
0135 /*!
0136     Assigns \a other to this conditional formatting and returns a reference to
0137     this conditional formatting.
0138  */
0139 ConditionalFormatting &ConditionalFormatting::operator=(const ConditionalFormatting &other)
0140 {
0141     this->d = other.d;
0142     return *this;
0143 }
0144
0145
0146 /*!
0147  * Destroy the object.
0148  */
0149 ConditionalFormatting::~ConditionalFormatting()
0150 {
0151 }
0152
0153 /*!
0154  * Add a hightlight rule with the given \a type, \a formula1, \a formula2,
0155  * \a format and \a stopIfTrue.
0156  * Return false if failed.
0157  */
0158 bool ConditionalFormatting::addHighlightCellsRule(HighlightRuleType type, const QString &formula1, const QString &formula2, const Format &format, bool stopIfTrue)
0159 {
0160     if (format.isEmpty())
0161         return false;
0162
0163     bool skipFormula = false;
0164
0165     auto cfRule = std::make_shared<XlsxCfRuleData>();
0166     if (type >= Highlight_LessThan && type <= Highlight_NotBetween) {
0167         cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("cellIs");
0168         QString op;
0169         switch (type) {
0170         case Highlight_Between: op = QStringLiteral("between"); break;
0171         case Highlight_Equal: op = QStringLiteral("equal"); break;
0172         case Highlight_GreaterThan: op = QStringLiteral("greaterThan"); break;
0173         case Highlight_GreaterThanOrEqual: op = QStringLiteral("greaterThanOrEqual"); break;
0174         case Highlight_LessThan: op = QStringLiteral("lessThan"); break;
0175         case Highlight_LessThanOrEqual: op = QStringLiteral("lessThanOrEqual"); break;
0176         case Highlight_NotBetween: op = QStringLiteral("notBetween"); break;
0177         case Highlight_NotEqual: op = QStringLiteral("notEqual"); break;
0178         default: break;
0179         }
0180         cfRule->attrs[XlsxCfRuleData::A_operator] = op;
0181     } else if (type >= Highlight_ContainsText && type <= Highlight_EndsWith) {
0182         if (type == Highlight_ContainsText) {
0183             cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("containsText");
0184             cfRule->attrs[XlsxCfRuleData::A_operator] = QStringLiteral("containsText");
0185             cfRule->attrs[XlsxCfRuleData::A_formula1_temp] = QStringLiteral("NOT(ISERROR(SEARCH(\"%1\",%2)))").arg(formula1);
0186         } else if (type == Highlight_NotContainsText) {
0187             cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("notContainsText");
0188             cfRule->attrs[XlsxCfRuleData::A_operator] = QStringLiteral("notContains");
0189             cfRule->attrs[XlsxCfRuleData::A_formula1_temp] = QStringLiteral("ISERROR(SEARCH(\"%2\",%1))").arg(formula1);
0190         } else if (type == Highlight_BeginsWith) {
0191             cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("beginsWith");
0192             cfRule->attrs[XlsxCfRuleData::A_operator] = QStringLiteral("beginsWith");
0193             cfRule->attrs[XlsxCfRuleData::A_formula1_temp] = QStringLiteral("LEFT(%2,LEN(\"%1\"))=\"%1\"").arg(formula1);
0194         } else {
0195             cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("endsWith");
0196             cfRule->attrs[XlsxCfRuleData::A_operator] = QStringLiteral("endsWith");
0197             cfRule->attrs[XlsxCfRuleData::A_formula1_temp] = QStringLiteral("RIGHT(%2,LEN(\"%1\"))=\"%1\"").arg(formula1);
0198         }
0199         cfRule->attrs[XlsxCfRuleData::A_text] = formula1;
0200         skipFormula = true;
0201     } else if (type == Highlight_TimePeriod) {
0202         cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("timePeriod");
0203         //:Todo
0204         return false;
0205     } else if (type == Highlight_Duplicate) {
0206         cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("duplicateValues");
0207     } else if (type == Highlight_Unique) {
0208         cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("uniqueValues");
0209     } else if (type == Highlight_Errors) {
0210         cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("containsErrors");
0211         cfRule->attrs[XlsxCfRuleData::A_formula1_temp] = QStringLiteral("ISERROR(%1)");
0212         skipFormula = true;
0213     } else if (type == Highlight_NoErrors) {
0214         cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("notContainsErrors");
0215         cfRule->attrs[XlsxCfRuleData::A_formula1_temp] = QStringLiteral("NOT(ISERROR(%1))");
0216         skipFormula = true;
0217     } else if (type == Highlight_Blanks) {
0218         cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("containsBlanks");
0219         cfRule->attrs[XlsxCfRuleData::A_formula1_temp] = QStringLiteral("LEN(TRIM(%1))=0");
0220         skipFormula = true;
0221     } else if (type == Highlight_NoBlanks) {
0222         cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("notContainsBlanks");
0223         cfRule->attrs[XlsxCfRuleData::A_formula1_temp] = QStringLiteral("LEN(TRIM(%1))>0");
0224         skipFormula = true;
0225     } else if (type >= Highlight_Top && type <= Highlight_BottomPercent) {
0226         cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("top10");
0227         if (type == Highlight_Bottom || type == Highlight_BottomPercent)
0228             cfRule->attrs[XlsxCfRuleData::A_bottom] = QStringLiteral("1");
0229         if (type == Highlight_TopPercent || type == Highlight_BottomPercent)
0230             cfRule->attrs[XlsxCfRuleData::A_percent] = QStringLiteral("1");
0231         cfRule->attrs[XlsxCfRuleData::A_rank] = !formula1.isEmpty() ? formula1 : QStringLiteral("10");
0232         skipFormula = true;
0233     } else if (type >= Highlight_AboveAverage && type <= Highlight_BelowStdDev3) {
0234         cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("aboveAverage");
0235         if (type >= Highlight_BelowAverage && type <= Highlight_BelowStdDev3)
0236             cfRule->attrs[XlsxCfRuleData::A_aboveAverage] = QStringLiteral("0");
0237         if (type == Highlight_AboveOrEqualAverage || type == Highlight_BelowOrEqualAverage)
0238             cfRule->attrs[XlsxCfRuleData::A_equalAverage] = QStringLiteral("1");
0239         if (type == Highlight_AboveStdDev1 || type == Highlight_BelowStdDev1)
0240             cfRule->attrs[XlsxCfRuleData::A_stdDev] = QStringLiteral("1");
0241         else if (type == Highlight_AboveStdDev2 || type == Highlight_BelowStdDev2)
0242             cfRule->attrs[XlsxCfRuleData::A_stdDev] = QStringLiteral("2");
0243         else if (type == Highlight_AboveStdDev3 || type == Highlight_BelowStdDev3)
0244             cfRule->attrs[XlsxCfRuleData::A_stdDev] = QStringLiteral("3");
0245     } else if (type == Highlight_Expression){
0246         cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("expression");
0247     } else {
0248         return false;
0249     }
0250
0251     cfRule->dxfFormat = format;
0252     if (stopIfTrue)
0253         cfRule->attrs[XlsxCfRuleData::A_stopIfTrue] = true;
0254     if (!skipFormula) {
0255         if (!formula1.isEmpty())
0256             cfRule->attrs[XlsxCfRuleData::A_formula1] = formula1.startsWith(QLatin1String("=")) ? formula1.mid(1) : formula1;
0257         if (!formula2.isEmpty())
0258             cfRule->attrs[XlsxCfRuleData::A_formula2] = formula2.startsWith(QLatin1String("=")) ? formula2.mid(1) : formula2;
0259     }
0260     d->cfRules.append(cfRule);
0261     return true;
0262 }
0263
0264 /*!
0265  * \overload
0266  *
0267  * Add a hightlight rule with the given \a type \a format and \a stopIfTrue.
0268  */
0269 bool ConditionalFormatting::addHighlightCellsRule(HighlightRuleType type, const Format &format, bool stopIfTrue)
0270 {
0271     if ((type >= Highlight_AboveAverage && type <= Highlight_BelowStdDev3)
0272             || (type >= Highlight_Duplicate && type <= Highlight_NoErrors)) {
0273         return addHighlightCellsRule(type, QString(), QString(), format, stopIfTrue);
0274     }
0275
0276     return false;
0277 }
0278
0279 /*!
0280  * \overload
0281  *
0282  * Add a hightlight rule with the given \a type, \a formula, \a format and \a stopIfTrue.
0283  * Return false if failed.
0284  */
0285 bool ConditionalFormatting::addHighlightCellsRule(HighlightRuleType type, const QString &formula, const Format &format, bool stopIfTrue)
0286 {
0287     if (type == Highlight_Between || type == Highlight_NotBetween)
0288         return false;
0289
0290     return addHighlightCellsRule(type, formula, QString(), format, stopIfTrue);
0291 }
0292
0293 /*!
0294  * Add a dataBar rule with the given \a color, \a type1, \a val1
0295  * , \a type2, \a val2, \a showData and \a stopIfTrue.
0296  * Return false if failed.
0297  */
0298 bool ConditionalFormatting::addDataBarRule(const QColor &color, ValueObjectType type1, const QString &val1, ValueObjectType type2, const QString &val2, bool showData, bool stopIfTrue)
0299 {
0300     auto cfRule = std::make_shared<XlsxCfRuleData>();
0301
0302     cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("dataBar");
0303     cfRule->attrs[XlsxCfRuleData::A_color1] = XlsxColor(color);
0304     if (stopIfTrue)
0305         cfRule->attrs[XlsxCfRuleData::A_stopIfTrue] = true;
0306     if (!showData)
0307         cfRule->attrs[XlsxCfRuleData::A_hideData] = true;
0308
0309     XlsxCfVoData cfvo1(type1, val1);
0310     XlsxCfVoData cfvo2(type2, val2);
0311     cfRule->attrs[XlsxCfRuleData::A_cfvo1] = QVariant::fromValue(cfvo1);
0312     cfRule->attrs[XlsxCfRuleData::A_cfvo2] = QVariant::fromValue(cfvo2);
0313
0314     d->cfRules.append(cfRule);
0315     return true;
0316 }
0317
0318 /*!
0319  * \overload
0320  * Add a dataBar rule with the given \a color, \a showData and \a stopIfTrue.
0321  */
0322 bool ConditionalFormatting::addDataBarRule(const QColor &color, bool showData, bool stopIfTrue)
0323 {
0324     return addDataBarRule(color, VOT_Min, QStringLiteral("0"), VOT_Max, QStringLiteral("0"), showData, stopIfTrue);
0325 }
0326
0327 /*!
0328  * Add a colorScale rule with the given \a minColor, \a maxColor and \a stopIfTrue.
0329  * Return false if failed.
0330  */
0331 bool ConditionalFormatting::add2ColorScaleRule(const QColor &minColor, const QColor &maxColor, bool stopIfTrue)
0332 {
0333     ValueObjectType type1 = VOT_Min;
0334     ValueObjectType type2 = VOT_Max;
0335     QString val1 = QStringLiteral("0");
0336     QString val2 = QStringLiteral("0");
0337
0338     auto cfRule = std::make_shared<XlsxCfRuleData>();
0339
0340     cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("colorScale");
0341     cfRule->attrs[XlsxCfRuleData::A_color1] = XlsxColor(minColor);
0342     cfRule->attrs[XlsxCfRuleData::A_color2] = XlsxColor(maxColor);
0343     if (stopIfTrue)
0344         cfRule->attrs[XlsxCfRuleData::A_stopIfTrue] = true;
0345
0346     XlsxCfVoData cfvo1(type1, val1);
0347     XlsxCfVoData cfvo2(type2, val2);
0348     cfRule->attrs[XlsxCfRuleData::A_cfvo1] = QVariant::fromValue(cfvo1);
0349     cfRule->attrs[XlsxCfRuleData::A_cfvo2] = QVariant::fromValue(cfvo2);
0350
0351     d->cfRules.append(cfRule);
0352     return true;
0353 }
0354
0355 /*!
0356  * Add a colorScale rule with the given \a minColor, \a midColor, \a maxColor and \a stopIfTrue.
0357  * Return false if failed.
0358  */
0359 bool ConditionalFormatting::add3ColorScaleRule(const QColor &minColor, const QColor &midColor, const QColor &maxColor, bool stopIfTrue)
0360 {
0361     ValueObjectType type1 = VOT_Min;
0362     ValueObjectType type2 = VOT_Percent;
0363     ValueObjectType type3 = VOT_Max;
0364     QString val1 = QStringLiteral("0");
0365     QString val2 = QStringLiteral("50");
0366     QString val3 = QStringLiteral("0");
0367
0368     auto cfRule = std::make_shared<XlsxCfRuleData>();
0369
0370     cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("colorScale");
0371     cfRule->attrs[XlsxCfRuleData::A_color1] = XlsxColor(minColor);
0372     cfRule->attrs[XlsxCfRuleData::A_color2] = XlsxColor(midColor);
0373     cfRule->attrs[XlsxCfRuleData::A_color3] = XlsxColor(maxColor);
0374
0375     if (stopIfTrue)
0376         cfRule->attrs[XlsxCfRuleData::A_stopIfTrue] = true;
0377
0378     XlsxCfVoData cfvo1(type1, val1);
0379     XlsxCfVoData cfvo2(type2, val2);
0380     XlsxCfVoData cfvo3(type3, val3);
0381     cfRule->attrs[XlsxCfRuleData::A_cfvo1] = QVariant::fromValue(cfvo1);
0382     cfRule->attrs[XlsxCfRuleData::A_cfvo2] = QVariant::fromValue(cfvo2);
0383     cfRule->attrs[XlsxCfRuleData::A_cfvo3] = QVariant::fromValue(cfvo3);
0384
0385     d->cfRules.append(cfRule);
0386     return true;
0387 }
0388
0389 /*!
0390     Returns the ranges on which the validation will be applied.
0391  */
0392 QList<CellRange> ConditionalFormatting::ranges() const
0393 {
0394     return d->ranges;
0395 }
0396
0397 /*!
0398     Add the \a cell on which the conditional formatting will apply to.
0399  */
0400 void ConditionalFormatting::addCell(const CellReference &cell)
0401 {
0402     d->ranges.append(CellRange(cell, cell));
0403 }
0404
0405 /*!
0406     \overload
0407     Add the cell(\a row, \a col) on which the conditional formatting will apply to.
0408  */
0409 void ConditionalFormatting::addCell(int row, int col)
0410 {
0411     d->ranges.append(CellRange(row, col, row, col));
0412 }
0413
0414 /*!
0415     \overload
0416     Add the range(\a firstRow, \a firstCol, \a lastRow, \a lastCol) on
0417     which the conditional formatting will apply to.
0418  */
0419 void ConditionalFormatting::addRange(int firstRow, int firstCol, int lastRow, int lastCol)
0420 {
0421     d->ranges.append(CellRange(firstRow, firstCol, lastRow, lastCol));
0422 }
0423
0424 /*!
0425     Add the \a range on which the conditional formatting will apply to.
0426  */
0427 void ConditionalFormatting::addRange(const CellRange &range)
0428 {
0429     d->ranges.append(range);
0430 }
0431
0432 bool ConditionalFormattingPrivate::readCfRule(QXmlStreamReader &reader, XlsxCfRuleData *rule, Styles *styles)
0433 {
0434     Q_ASSERT(reader.name() == QLatin1String("cfRule"));
0435     QXmlStreamAttributes attrs = reader.attributes();
0436     if (attrs.hasAttribute(QLatin1String("type")))
0437         rule->attrs[XlsxCfRuleData::A_type] = attrs.value(QLatin1String("type")).toString();
0438     if (attrs.hasAttribute(QLatin1String("dxfId"))) {
0439         int id = attrs.value(QLatin1String("dxfId")).toInt();
0440         if (styles)
0441             rule->dxfFormat = styles->dxfFormat(id);
0442         else
0443             rule->dxfFormat.setDxfIndex(id);
0444     }
0445     rule->priority = attrs.value(QLatin1String("priority")).toInt();
0446     if (attrs.value(QLatin1String("stopIfTrue")) == QLatin1String("1")) {
0447         //default is false
0448         rule->attrs[XlsxCfRuleData::A_stopIfTrue] = QLatin1String("1");
0449     }
0450     if (attrs.value(QLatin1String("aboveAverage")) == QLatin1String("0")) {
0451         //default is true
0452         rule->attrs[XlsxCfRuleData::A_aboveAverage] = QLatin1String("0");
0453     }
0454     if (attrs.value(QLatin1String("percent")) == QLatin1String("1")) {
0455         //default is false
0456         rule->attrs[XlsxCfRuleData::A_percent] = QLatin1String("1");
0457     }
0458     if (attrs.value(QLatin1String("bottom")) == QLatin1String("1")) {
0459         //default is false
0460         rule->attrs[XlsxCfRuleData::A_bottom] = QLatin1String("1");
0461     }
0462     if (attrs.hasAttribute(QLatin1String("operator")))
0463         rule->attrs[XlsxCfRuleData::A_operator] = attrs.value(QLatin1String("operator")).toString();
0464
0465     if (attrs.hasAttribute(QLatin1String("text")))
0466         rule->attrs[XlsxCfRuleData::A_text] = attrs.value(QLatin1String("text")).toString();
0467
0468     if (attrs.hasAttribute(QLatin1String("timePeriod")))
0469         rule->attrs[XlsxCfRuleData::A_timePeriod] = attrs.value(QLatin1String("timePeriod")).toString();
0470
0471     if (attrs.hasAttribute(QLatin1String("rank")))
0472         rule->attrs[XlsxCfRuleData::A_rank] = attrs.value(QLatin1String("rank")).toString();
0473
0474     if (attrs.hasAttribute(QLatin1String("stdDev")))
0475         rule->attrs[XlsxCfRuleData::A_stdDev] = attrs.value(QLatin1String("stdDev")).toString();
0476
0477     if (attrs.value(QLatin1String("equalAverage")) == QLatin1String("1")) {
0478         //default is false
0479         rule->attrs[XlsxCfRuleData::A_equalAverage] = QLatin1String("1");
0480     }
0481
0482     while (!reader.atEnd()) {
0483         reader.readNextStartElement();
0484         if (reader.tokenType() == QXmlStreamReader::StartElement) {
0485             if (reader.name() == QLatin1String("formula")) {
0486                 const QString f = reader.readElementText();
0487                 if (!rule->attrs.contains(XlsxCfRuleData::A_formula1))
0488                     rule->attrs[XlsxCfRuleData::A_formula1] = f;
0489                 else if (!rule->attrs.contains(XlsxCfRuleData::A_formula2))
0490                     rule->attrs[XlsxCfRuleData::A_formula2] = f;
0491                 else if (!rule->attrs.contains(XlsxCfRuleData::A_formula3))
0492                     rule->attrs[XlsxCfRuleData::A_formula3] = f;
0493             } else if (reader.name() == QLatin1String("dataBar")) {
0494                 readCfDataBar(reader, rule);
0495             } else if (reader.name() == QLatin1String("colorScale")) {
0496                 readCfColorScale(reader, rule);
0497             }
0498         }
0499         if (reader.tokenType() == QXmlStreamReader::EndElement
0500                 && reader.name() == QStringLiteral("conditionalFormatting")) {
0501             break;
0502         }
0503     }
0504     return true;
0505 }
0506
0507 bool ConditionalFormattingPrivate::readCfDataBar(QXmlStreamReader &reader, XlsxCfRuleData *rule)
0508 {
0509     Q_ASSERT(reader.name() == QLatin1String("dataBar"));
0510     QXmlStreamAttributes attrs = reader.attributes();
0511     if (attrs.value(QLatin1String("showValue")) == QLatin1String("0"))
0512         rule->attrs[XlsxCfRuleData::A_hideData] = QStringLiteral("1");
0513
0514     while (!reader.atEnd()) {
0515         reader.readNextStartElement();
0516         if (reader.tokenType() == QXmlStreamReader::StartElement) {
0517             if (reader.name() == QLatin1String("cfvo")) {
0518                 XlsxCfVoData data;
0519                 readCfVo(reader, data);
0520                 if (!rule->attrs.contains(XlsxCfRuleData::A_cfvo1))
0521                     rule->attrs[XlsxCfRuleData::A_cfvo1] = QVariant::fromValue(data);
0522                 else
0523                     rule->attrs[XlsxCfRuleData::A_cfvo2] = QVariant::fromValue(data);
0524             } else if (reader.name() == QLatin1String("color")) {
0525                 XlsxColor color;
0526                 color.loadFromXml(reader);
0527                 rule->attrs[XlsxCfRuleData::A_color1] = color;
0528             }
0529         }
0530         if (reader.tokenType() == QXmlStreamReader::EndElement
0531                 && reader.name() == QStringLiteral("dataBar")) {
0532             break;
0533         }
0534     }
0535
0536     return true;
0537 }
0538
0539 bool ConditionalFormattingPrivate::readCfColorScale(QXmlStreamReader &reader, XlsxCfRuleData *rule)
0540 {
0541     Q_ASSERT(reader.name() == QLatin1String("colorScale"));
0542
0543     while (!reader.atEnd()) {
0544         reader.readNextStartElement();
0545         if (reader.tokenType() == QXmlStreamReader::StartElement) {
0546             if (reader.name() == QLatin1String("cfvo")) {
0547                 XlsxCfVoData data;
0548                 readCfVo(reader, data);
0549                 if (!rule->attrs.contains(XlsxCfRuleData::A_cfvo1))
0550                     rule->attrs[XlsxCfRuleData::A_cfvo1] = QVariant::fromValue(data);
0551                 else if (!rule->attrs.contains(XlsxCfRuleData::A_cfvo2))
0552                     rule->attrs[XlsxCfRuleData::A_cfvo2] = QVariant::fromValue(data);
0553                 else
0554                     rule->attrs[XlsxCfRuleData::A_cfvo3] = QVariant::fromValue(data);
0555             } else if (reader.name() == QLatin1String("color")) {
0556                 XlsxColor color;
0557                 color.loadFromXml(reader);
0558                 if (!rule->attrs.contains(XlsxCfRuleData::A_color1))
0559                     rule->attrs[XlsxCfRuleData::A_color1] = color;
0560                 else if (!rule->attrs.contains(XlsxCfRuleData::A_color2))
0561                     rule->attrs[XlsxCfRuleData::A_color2] = color;
0562                 else
0563                     rule->attrs[XlsxCfRuleData::A_color3] = color;
0564             }
0565         }
0566         if (reader.tokenType() == QXmlStreamReader::EndElement
0567                 && reader.name() == QStringLiteral("colorScale")) {
0568             break;
0569         }
0570     }
0571
0572     return true;
0573 }
0574
0575 bool ConditionalFormattingPrivate::readCfVo(QXmlStreamReader &reader, XlsxCfVoData &cfvo)
0576 {
0577     Q_ASSERT(reader.name() == QStringLiteral("cfvo"));
0578
0579     QXmlStreamAttributes attrs = reader.attributes();
0580
0581     QString type = attrs.value(QLatin1String("type")).toString();
0582     ConditionalFormatting::ValueObjectType t;
0583     if (type == QLatin1String("formula"))
0584         t = ConditionalFormatting::VOT_Formula;
0585     else if (type == QLatin1String("max"))
0586         t = ConditionalFormatting::VOT_Max;
0587     else if (type == QLatin1String("min"))
0588         t = ConditionalFormatting::VOT_Min;
0589     else if (type == QLatin1String("num"))
0590         t = ConditionalFormatting::VOT_Num;
0591     else if (type == QLatin1String("percent"))
0592         t = ConditionalFormatting::VOT_Percent;
0593     else //if (type == QLatin1String("percentile"))
0594         t = ConditionalFormatting::VOT_Percentile;
0595
0596     cfvo.type = t;
0597     cfvo.value = attrs.value(QLatin1String("val")).toString();
0598     if (attrs.value(QLatin1String("gte")) == QLatin1String("0")) {
0599         //default is true
0600         cfvo.gte = false;
0601     }
0602     return true;
0603 }
0604
0605 bool ConditionalFormatting::loadFromXml(QXmlStreamReader &reader, Styles *styles)
0606 {
0607     Q_ASSERT(reader.name() == QStringLiteral("conditionalFormatting"));
0608
0609     d->ranges.clear();
0610     d->cfRules.clear();
0611     QXmlStreamAttributes attrs = reader.attributes();
0612     const QString sqref = attrs.value(QLatin1String("sqref")).toString();
0613     const auto sqrefParts = sqref.split(QLatin1Char(' '));
0614     for (const QString &range : sqrefParts) {
0615         this->addRange(range);
0616     }
0617
0618     while (!reader.atEnd()) {
0619         reader.readNextStartElement();
0620         if (reader.tokenType() == QXmlStreamReader::StartElement) {
0621             if (reader.name() == QLatin1String("cfRule")) {
0622                 auto cfRule = std::make_shared<XlsxCfRuleData>();
0623                 d->readCfRule(reader, cfRule.get(), styles);
0624                 d->cfRules.append(cfRule);
0625             }
0626         }
0627         if (reader.tokenType() == QXmlStreamReader::EndElement
0628                 && reader.name() == QStringLiteral("conditionalFormatting")) {
0629             break;
0630         }
0631     }
0632
0633
0634     return true;
0635 }
0636
0637 bool ConditionalFormatting::saveToXml(QXmlStreamWriter &writer) const
0638 {
0639     writer.writeStartElement(QStringLiteral("conditionalFormatting"));
0640     QStringList sqref;
0641     const auto rangeList = ranges();
0642     for (const CellRange &range : rangeList) {
0643         sqref.append(range.toString());
0644     }
0645     writer.writeAttribute(QStringLiteral("sqref"), sqref.join(QLatin1String(" ")));
0646
0647     for (int i=0; i<d->cfRules.size(); ++i) {
0648         const std::shared_ptr<XlsxCfRuleData> &rule = d->cfRules[i];
0649         writer.writeStartElement(QStringLiteral("cfRule"));
0650         writer.writeAttribute(QStringLiteral("type"), rule->attrs[XlsxCfRuleData::A_type].toString());
0651         if (rule->dxfFormat.dxfIndexValid())
0652             writer.writeAttribute(QStringLiteral("dxfId"), QString::number(rule->dxfFormat.dxfIndex()));
0653         writer.writeAttribute(QStringLiteral("priority"), QString::number(rule->priority));
0654
0655         auto it = rule->attrs.constFind(XlsxCfRuleData::A_stopIfTrue);
0656         if (it != rule->attrs.constEnd())
0657             writer.writeAttribute(QStringLiteral("stopIfTrue"), it.value().toString());
0658
0659         it = rule->attrs.constFind(XlsxCfRuleData::A_aboveAverage);
0660         if (it != rule->attrs.constEnd())
0661             writer.writeAttribute(QStringLiteral("aboveAverage"), it.value().toString());
0662
0663         it = rule->attrs.constFind(XlsxCfRuleData::A_percent);
0664         if (it != rule->attrs.constEnd())
0665             writer.writeAttribute(QStringLiteral("percent"), it.value().toString());
0666
0667         it = rule->attrs.constFind(XlsxCfRuleData::A_bottom);
0668         if (it != rule->attrs.constEnd())
0669             writer.writeAttribute(QStringLiteral("bottom"), it.value().toString());
0670
0671         it = rule->attrs.constFind(XlsxCfRuleData::A_operator);
0672         if (it != rule->attrs.constEnd())
0673             writer.writeAttribute(QStringLiteral("operator"), it.value().toString());
0674
0675         it = rule->attrs.constFind(XlsxCfRuleData::A_text);
0676         if (it != rule->attrs.constEnd())
0677             writer.writeAttribute(QStringLiteral("text"), it.value().toString());
0678
0679         it = rule->attrs.constFind(XlsxCfRuleData::A_timePeriod);
0680         if (it != rule->attrs.constEnd())
0681             writer.writeAttribute(QStringLiteral("timePeriod"), it.value().toString());
0682
0683         it = rule->attrs.constFind(XlsxCfRuleData::A_rank);
0684         if (it != rule->attrs.constEnd())
0685             writer.writeAttribute(QStringLiteral("rank"), it.value().toString());
0686
0687         it = rule->attrs.constFind(XlsxCfRuleData::A_stdDev);
0688         if (it != rule->attrs.constEnd())
0689             writer.writeAttribute(QStringLiteral("stdDev"), it.value().toString());
0690
0691         it = rule->attrs.constFind(XlsxCfRuleData::A_equalAverage);
0692         if (it != rule->attrs.constEnd())
0693             writer.writeAttribute(QStringLiteral("equalAverage"), it.value().toString());
0694
0695         if (rule->attrs[XlsxCfRuleData::A_type] == QLatin1String("dataBar")) {
0696             writer.writeStartElement(QStringLiteral("dataBar"));
0697             if (rule->attrs.contains(XlsxCfRuleData::A_hideData))
0698                 writer.writeAttribute(QStringLiteral("showValue"), QStringLiteral("0"));
0699             d->writeCfVo(writer, rule->attrs[XlsxCfRuleData::A_cfvo1].value<XlsxCfVoData>());
0700             d->writeCfVo(writer, rule->attrs[XlsxCfRuleData::A_cfvo2].value<XlsxCfVoData>());
0701             rule->attrs[XlsxCfRuleData::A_color1].value<XlsxColor>().saveToXml(writer);
0702             writer.writeEndElement();//dataBar
0703         } else if (rule->attrs[XlsxCfRuleData::A_type] == QLatin1String("colorScale")) {
0704             writer.writeStartElement(QStringLiteral("colorScale"));
0705             d->writeCfVo(writer, rule->attrs[XlsxCfRuleData::A_cfvo1].value<XlsxCfVoData>());
0706             d->writeCfVo(writer, rule->attrs[XlsxCfRuleData::A_cfvo2].value<XlsxCfVoData>());
0707
0708             it = rule->attrs.constFind(XlsxCfRuleData::A_cfvo3);
0709             if (it != rule->attrs.constEnd())
0710                 d->writeCfVo(writer, it.value().value<XlsxCfVoData>());
0711
0712             rule->attrs[XlsxCfRuleData::A_color1].value<XlsxColor>().saveToXml(writer);
0713             rule->attrs[XlsxCfRuleData::A_color2].value<XlsxColor>().saveToXml(writer);
0714
0715             it = rule->attrs.constFind(XlsxCfRuleData::A_color3);
0716             if (it != rule->attrs.constEnd())
0717                 it.value().value<XlsxColor>().saveToXml(writer);
0718
0719             writer.writeEndElement();//colorScale
0720         }
0721
0722
0723         it = rule->attrs.constFind(XlsxCfRuleData::A_formula1_temp);
0724         if (it != rule->attrs.constEnd()) {
0725             const auto _ranges = ranges();
0726             const auto begin = _ranges.begin();
0727             if (begin != _ranges.end()) {
0728                 QString str = begin->toString();
0729                 QString startCell = str.mid(0, str.indexOf(u':'));
0730                 writer.writeTextElement(QStringLiteral("formula"), it.value().toString().arg(startCell));
0731             }
0732         } else if ((it = rule->attrs.constFind(XlsxCfRuleData::A_formula1)) != rule->attrs.constEnd()) {
0733             writer.writeTextElement(QStringLiteral("formula"), it.value().toString());
0734         }
0735
0736         it = rule->attrs.constFind(XlsxCfRuleData::A_formula2);
0737         if (it != rule->attrs.constEnd())
0738             writer.writeTextElement(QStringLiteral("formula"), it.value().toString());
0739
0740         it = rule->attrs.constFind(XlsxCfRuleData::A_formula3);
0741         if (it != rule->attrs.constEnd())
0742             writer.writeTextElement(QStringLiteral("formula"), it.value().toString());
0743
0744         writer.writeEndElement(); //cfRule
0745     }
0746
0747     writer.writeEndElement(); //conditionalFormatting
0748     return true;
0749 }
0750
0751 QT_END_NAMESPACE_XLSX