andre@1: /****************************************************************************
andre@1: ** Copyright (c) 2013-2014 Debao Zhang <hello@debao.me>
andre@1: ** All right reserved.
andre@1: **
andre@1: ** Permission is hereby granted, free of charge, to any person obtaining
andre@1: ** a copy of this software and associated documentation files (the
andre@1: ** "Software"), to deal in the Software without restriction, including
andre@1: ** without limitation the rights to use, copy, modify, merge, publish,
andre@1: ** distribute, sublicense, and/or sell copies of the Software, and to
andre@1: ** permit persons to whom the Software is furnished to do so, subject to
andre@1: ** the following conditions:
andre@1: **
andre@1: ** The above copyright notice and this permission notice shall be
andre@1: ** included in all copies or substantial portions of the Software.
andre@1: **
andre@1: ** THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
andre@1: ** EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
andre@1: ** MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
andre@1: ** NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
andre@1: ** LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
andre@1: ** OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
andre@1: ** WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
andre@1: **
andre@1: ****************************************************************************/
andre@1: 
andre@1: #include "xlsxconditionalformatting.h"
andre@1: #include "xlsxconditionalformatting_p.h"
andre@1: #include "xlsxworksheet.h"
andre@1: #include "xlsxcellrange.h"
andre@1: #include "xlsxstyles_p.h"
andre@1: 
andre@1: #include <QXmlStreamReader>
andre@1: #include <QXmlStreamWriter>
andre@1: #include <QDebug>
andre@1: 
andre@1: QT_BEGIN_NAMESPACE_XLSX
andre@1: 
andre@1: ConditionalFormattingPrivate::ConditionalFormattingPrivate()
andre@1: {
andre@1: 
andre@1: }
andre@1: 
andre@1: ConditionalFormattingPrivate::ConditionalFormattingPrivate(const ConditionalFormattingPrivate &other)
andre@1:     :QSharedData(other)
andre@1: {
andre@1: 
andre@1: }
andre@1: 
andre@1: ConditionalFormattingPrivate::~ConditionalFormattingPrivate()
andre@1: {
andre@1: 
andre@1: }
andre@1: 
andre@1: void ConditionalFormattingPrivate::writeCfVo(QXmlStreamWriter &writer, const XlsxCfVoData &cfvo) const
andre@1: {
andre@1:     writer.writeEmptyElement(QStringLiteral("cfvo"));
andre@1:     QString type;
andre@1:     switch(cfvo.type) {
andre@1:     case ConditionalFormatting::VOT_Formula: type=QStringLiteral("formula"); break;
andre@1:     case ConditionalFormatting::VOT_Max: type=QStringLiteral("max"); break;
andre@1:     case ConditionalFormatting::VOT_Min: type=QStringLiteral("min"); break;
andre@1:     case ConditionalFormatting::VOT_Num: type=QStringLiteral("num"); break;
andre@1:     case ConditionalFormatting::VOT_Percent: type=QStringLiteral("percent"); break;
andre@1:     case ConditionalFormatting::VOT_Percentile: type=QStringLiteral("percentile"); break;
andre@1:     default: break;
andre@1:     }
andre@1:     writer.writeAttribute(QStringLiteral("type"), type);
andre@1:     writer.writeAttribute(QStringLiteral("val"), cfvo.value);
andre@1:     if (!cfvo.gte)
andre@1:         writer.writeAttribute(QStringLiteral("gte"), QStringLiteral("0"));
andre@1: }
andre@1: 
andre@1: /*!
andre@1:  * \class ConditionalFormatting
andre@1:  * \brief Conditional formatting for single cell or ranges
andre@1:  * \inmodule QtXlsx
andre@1:  *
andre@1:  * The conditional formatting can be applied to a single cell or ranges of cells.
andre@1:  */
andre@1: 
andre@1: 
andre@1: /*!
andre@1:     \enum ConditionalFormatting::HighlightRuleType
andre@1: 
andre@1:     \value Highlight_LessThan
andre@1:     \value Highlight_LessThanOrEqual
andre@1:     \value Highlight_Equal
andre@1:     \value Highlight_NotEqual
andre@1:     \value Highlight_GreaterThanOrEqual
andre@1:     \value Highlight_GreaterThan
andre@1:     \value Highlight_Between
andre@1:     \value Highlight_NotBetween
andre@1: 
andre@1:     \value Highlight_ContainsText
andre@1:     \value Highlight_NotContainsText
andre@1:     \value Highlight_BeginsWith
andre@1:     \value Highlight_EndsWith
andre@1: 
andre@1:     \value Highlight_TimePeriod
andre@1: 
andre@1:     \value Highlight_Duplicate
andre@1:     \value Highlight_Unique
andre@1: 
andre@1:     \value Highlight_Blanks
andre@1:     \value Highlight_NoBlanks
andre@1:     \value Highlight_Errors
andre@1:     \value Highlight_NoErrors
andre@1: 
andre@1:     \value Highlight_Top
andre@1:     \value Highlight_TopPercent
andre@1:     \value Highlight_Bottom
andre@1:     \value Highlight_BottomPercent
andre@1: 
andre@1:     \value Highlight_AboveAverage
andre@1:     \value Highlight_AboveOrEqualAverage
andre@1:     \value Highlight_BelowAverage
andre@1:     \value Highlight_BelowOrEqualAverage
andre@1:     \value Highlight_AboveStdDev1
andre@1:     \value Highlight_AboveStdDev2
andre@1:     \value Highlight_AboveStdDev3
andre@1:     \value Highlight_BelowStdDev1
andre@1:     \value Highlight_BelowStdDev2
andre@1:     \value Highlight_BelowStdDev3
andre@1: 
andre@1:     \value Highlight_Expression
andre@1: */
andre@1: 
andre@1: /*!
andre@1:     \enum ConditionalFormatting::ValueObjectType
andre@1: 
andre@1:     \value VOT_Formula
andre@1:     \value VOT_Max
andre@1:     \value VOT_Min
andre@1:     \value VOT_Num
andre@1:     \value VOT_Percent
andre@1:     \value VOT_Percentile
andre@1: */
andre@1: 
andre@1: /*!
andre@1:     Construct a conditional formatting object
andre@1: */
andre@1: ConditionalFormatting::ConditionalFormatting()
andre@1:     :d(new ConditionalFormattingPrivate())
andre@1: {
andre@1: 
andre@1: }
andre@1: 
andre@1: /*!
andre@1:     Constructs a copy of \a other.
andre@1: */
andre@1: ConditionalFormatting::ConditionalFormatting(const ConditionalFormatting &other)
andre@1:     :d(other.d)
andre@1: {
andre@1: 
andre@1: }
andre@1: 
andre@1: /*!
andre@1:     Assigns \a other to this conditional formatting and returns a reference to
andre@1:     this conditional formatting.
andre@1:  */
andre@1: ConditionalFormatting &ConditionalFormatting::operator=(const ConditionalFormatting &other)
andre@1: {
andre@1:     this->d = other.d;
andre@1:     return *this;
andre@1: }
andre@1: 
andre@1: 
andre@1: /*!
andre@1:  * Destroy the object.
andre@1:  */
andre@1: ConditionalFormatting::~ConditionalFormatting()
andre@1: {
andre@1: }
andre@1: 
andre@1: /*!
andre@1:  * Add a hightlight rule with the given \a type, \a formula1, \a formula2,
andre@1:  * \a format and \a stopIfTrue.
andre@1:  * Return false if failed.
andre@1:  */
andre@1: bool ConditionalFormatting::addHighlightCellsRule(HighlightRuleType type, const QString &formula1, const QString &formula2, const Format &format, bool stopIfTrue)
andre@1: {
andre@1:     if (format.isEmpty())
andre@1:         return false;
andre@1: 
andre@1:     bool skipFormula = false;
andre@1: 
andre@1:     QSharedPointer<XlsxCfRuleData> cfRule(new XlsxCfRuleData);
andre@1:     if (type >= Highlight_LessThan && type <= Highlight_NotBetween) {
andre@1:         cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("cellIs");
andre@1:         QString op;
andre@1:         switch (type) {
andre@1:         case Highlight_Between: op = QStringLiteral("between"); break;
andre@1:         case Highlight_Equal: op = QStringLiteral("equal"); break;
andre@1:         case Highlight_GreaterThan: op = QStringLiteral("greaterThan"); break;
andre@1:         case Highlight_GreaterThanOrEqual: op = QStringLiteral("greaterThanOrEqual"); break;
andre@1:         case Highlight_LessThan: op = QStringLiteral("lessThan"); break;
andre@1:         case Highlight_LessThanOrEqual: op = QStringLiteral("lessThanOrEqual"); break;
andre@1:         case Highlight_NotBetween: op = QStringLiteral("notBetween"); break;
andre@1:         case Highlight_NotEqual: op = QStringLiteral("notEqual"); break;
andre@1:         default: break;
andre@1:         }
andre@1:         cfRule->attrs[XlsxCfRuleData::A_operator] = op;
andre@1:     } else if (type >= Highlight_ContainsText && type <= Highlight_EndsWith) {
andre@1:         if (type == Highlight_ContainsText) {
andre@1:             cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("containsText");
andre@1:             cfRule->attrs[XlsxCfRuleData::A_operator] = QStringLiteral("containsText");
andre@1:             cfRule->attrs[XlsxCfRuleData::A_formula1_temp] = QStringLiteral("NOT(ISERROR(SEARCH(\"%1\",%2)))").arg(formula1);
andre@1:         } else if (type == Highlight_NotContainsText) {
andre@1:             cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("notContainsText");
andre@1:             cfRule->attrs[XlsxCfRuleData::A_operator] = QStringLiteral("notContains");
andre@1:             cfRule->attrs[XlsxCfRuleData::A_formula1_temp] = QStringLiteral("ISERROR(SEARCH(\"%2\",%1))").arg(formula1);
andre@1:         } else if (type == Highlight_BeginsWith) {
andre@1:             cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("beginsWith");
andre@1:             cfRule->attrs[XlsxCfRuleData::A_operator] = QStringLiteral("beginsWith");
andre@1:             cfRule->attrs[XlsxCfRuleData::A_formula1_temp] = QStringLiteral("LEFT(%2,LEN(\"%1\"))=\"%1\"").arg(formula1);
andre@1:         } else {
andre@1:             cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("endsWith");
andre@1:             cfRule->attrs[XlsxCfRuleData::A_operator] = QStringLiteral("endsWith");
andre@1:             cfRule->attrs[XlsxCfRuleData::A_formula1_temp] = QStringLiteral("RIGHT(%2,LEN(\"%1\"))=\"%1\"").arg(formula1);
andre@1:         }
andre@1:         cfRule->attrs[XlsxCfRuleData::A_text] = formula1;
andre@1:         skipFormula = true;
andre@1:     } else if (type == Highlight_TimePeriod) {
andre@1:         cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("timePeriod");
andre@1:         //:Todo
andre@1:         return false;
andre@1:     } else if (type == Highlight_Duplicate) {
andre@1:         cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("duplicateValues");
andre@1:     } else if (type == Highlight_Unique) {
andre@1:         cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("uniqueValues");
andre@1:     } else if (type == Highlight_Errors) {
andre@1:         cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("containsErrors");
andre@1:         cfRule->attrs[XlsxCfRuleData::A_formula1_temp] = QStringLiteral("ISERROR(%1)");
andre@1:         skipFormula = true;
andre@1:     } else if (type == Highlight_NoErrors) {
andre@1:         cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("notContainsErrors");
andre@1:         cfRule->attrs[XlsxCfRuleData::A_formula1_temp] = QStringLiteral("NOT(ISERROR(%1))");
andre@1:         skipFormula = true;
andre@1:     } else if (type == Highlight_Blanks) {
andre@1:         cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("containsBlanks");
andre@1:         cfRule->attrs[XlsxCfRuleData::A_formula1_temp] = QStringLiteral("LEN(TRIM(%1))=0");
andre@1:         skipFormula = true;
andre@1:     } else if (type == Highlight_NoBlanks) {
andre@1:         cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("notContainsBlanks");
andre@1:         cfRule->attrs[XlsxCfRuleData::A_formula1_temp] = QStringLiteral("LEN(TRIM(%1))>0");
andre@1:         skipFormula = true;
andre@1:     } else if (type >= Highlight_Top && type <= Highlight_BottomPercent) {
andre@1:         cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("top10");
andre@1:         if (type == Highlight_Bottom || type == Highlight_BottomPercent)
andre@1:             cfRule->attrs[XlsxCfRuleData::A_bottom] = QStringLiteral("1");
andre@1:         if (type == Highlight_TopPercent || type == Highlight_BottomPercent)
andre@1:             cfRule->attrs[XlsxCfRuleData::A_percent] = QStringLiteral("1");
andre@1:         cfRule->attrs[XlsxCfRuleData::A_rank] = !formula1.isEmpty() ? formula1 : QStringLiteral("10");
andre@1:         skipFormula = true;
andre@1:     } else if (type >= Highlight_AboveAverage && type <= Highlight_BelowStdDev3) {
andre@1:         cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("aboveAverage");
andre@1:         if (type >= Highlight_BelowAverage && type <= Highlight_BelowStdDev3)
andre@1:             cfRule->attrs[XlsxCfRuleData::A_aboveAverage] = QStringLiteral("0");
andre@1:         if (type == Highlight_AboveOrEqualAverage || type == Highlight_BelowOrEqualAverage)
andre@1:             cfRule->attrs[XlsxCfRuleData::A_equalAverage] = QStringLiteral("1");
andre@1:         if (type == Highlight_AboveStdDev1 || type == Highlight_BelowStdDev1)
andre@1:             cfRule->attrs[XlsxCfRuleData::A_stdDev] = QStringLiteral("1");
andre@1:         else if (type == Highlight_AboveStdDev2 || type == Highlight_BelowStdDev2)
andre@1:             cfRule->attrs[XlsxCfRuleData::A_stdDev] = QStringLiteral("2");
andre@1:         else if (type == Highlight_AboveStdDev3 || type == Highlight_BelowStdDev3)
andre@1:             cfRule->attrs[XlsxCfRuleData::A_stdDev] = QStringLiteral("3");
andre@1:     } else if (type == Highlight_Expression){
andre@1:         cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("expression");
andre@1:     } else {
andre@1:         return false;
andre@1:     }
andre@1: 
andre@1:     cfRule->dxfFormat = format;
andre@1:     if (stopIfTrue)
andre@1:         cfRule->attrs[XlsxCfRuleData::A_stopIfTrue] = true;
andre@1:     if (!skipFormula) {
andre@1:         if (!formula1.isEmpty())
andre@1:             cfRule->attrs[XlsxCfRuleData::A_formula1] = formula1.startsWith(QLatin1String("=")) ? formula1.mid(1) : formula1;
andre@1:         if (!formula2.isEmpty())
andre@1:             cfRule->attrs[XlsxCfRuleData::A_formula2] = formula2.startsWith(QLatin1String("=")) ? formula2.mid(1) : formula2;
andre@1:     }
andre@1:     d->cfRules.append(cfRule);
andre@1:     return true;
andre@1: }
andre@1: 
andre@1: /*!
andre@1:  * \overload
andre@1:  *
andre@1:  * Add a hightlight rule with the given \a type \a format and \a stopIfTrue.
andre@1:  */
andre@1: bool ConditionalFormatting::addHighlightCellsRule(HighlightRuleType type, const Format &format, bool stopIfTrue)
andre@1: {
andre@1:     if ((type >= Highlight_AboveAverage && type <= Highlight_BelowStdDev3)
andre@1:             || (type >= Highlight_Duplicate && type <= Highlight_NoErrors)) {
andre@1:         return addHighlightCellsRule(type, QString(), QString(), format, stopIfTrue);
andre@1:     }
andre@1: 
andre@1:     return false;
andre@1: }
andre@1: 
andre@1: /*!
andre@1:  * \overload
andre@1:  *
andre@1:  * Add a hightlight rule with the given \a type, \a formula, \a format and \a stopIfTrue.
andre@1:  * Return false if failed.
andre@1:  */
andre@1: bool ConditionalFormatting::addHighlightCellsRule(HighlightRuleType type, const QString &formula, const Format &format, bool stopIfTrue)
andre@1: {
andre@1:     if (type == Highlight_Between || type == Highlight_NotBetween)
andre@1:         return false;
andre@1: 
andre@1:     return addHighlightCellsRule(type, formula, QString(), format, stopIfTrue);
andre@1: }
andre@1: 
andre@1: /*!
andre@1:  * Add a dataBar rule with the given \a color, \a type1, \a val1
andre@1:  * , \a type2, \a val2, \a showData and \a stopIfTrue.
andre@1:  * Return false if failed.
andre@1:  */
andre@1: bool ConditionalFormatting::addDataBarRule(const QColor &color, ValueObjectType type1, const QString &val1, ValueObjectType type2, const QString &val2, bool showData, bool stopIfTrue)
andre@1: {
andre@1:     QSharedPointer<XlsxCfRuleData> cfRule(new XlsxCfRuleData);
andre@1: 
andre@1:     cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("dataBar");
andre@1:     cfRule->attrs[XlsxCfRuleData::A_color1] = XlsxColor(color);
andre@1:     if (stopIfTrue)
andre@1:         cfRule->attrs[XlsxCfRuleData::A_stopIfTrue] = true;
andre@1:     if (!showData)
andre@1:         cfRule->attrs[XlsxCfRuleData::A_hideData] = true;
andre@1: 
andre@1:     XlsxCfVoData cfvo1(type1, val1);
andre@1:     XlsxCfVoData cfvo2(type2, val2);
andre@1:     cfRule->attrs[XlsxCfRuleData::A_cfvo1] = QVariant::fromValue(cfvo1);
andre@1:     cfRule->attrs[XlsxCfRuleData::A_cfvo2] = QVariant::fromValue(cfvo2);
andre@1: 
andre@1:     d->cfRules.append(cfRule);
andre@1:     return true;
andre@1: }
andre@1: 
andre@1: /*!
andre@1:  * \overload
andre@1:  * Add a dataBar rule with the given \a color, \a showData and \a stopIfTrue.
andre@1:  */
andre@1: bool ConditionalFormatting::addDataBarRule(const QColor &color, bool showData, bool stopIfTrue)
andre@1: {
andre@1:     return addDataBarRule(color, VOT_Min, QStringLiteral("0"), VOT_Max, QStringLiteral("0"), showData, stopIfTrue);
andre@1: }
andre@1: 
andre@1: /*!
andre@1:  * Add a colorScale rule with the given \a minColor, \a maxColor and \a stopIfTrue.
andre@1:  * Return false if failed.
andre@1:  */
andre@1: bool ConditionalFormatting::add2ColorScaleRule(const QColor &minColor, const QColor &maxColor, bool stopIfTrue)
andre@1: {
andre@1:     ValueObjectType type1 = VOT_Min;
andre@1:     ValueObjectType type2 = VOT_Max;
andre@1:     QString val1 = QStringLiteral("0");
andre@1:     QString val2 = QStringLiteral("0");
andre@1: 
andre@1:     QSharedPointer<XlsxCfRuleData> cfRule(new XlsxCfRuleData);
andre@1: 
andre@1:     cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("colorScale");
andre@1:     cfRule->attrs[XlsxCfRuleData::A_color1] = XlsxColor(minColor);
andre@1:     cfRule->attrs[XlsxCfRuleData::A_color2] = XlsxColor(maxColor);
andre@1:     if (stopIfTrue)
andre@1:         cfRule->attrs[XlsxCfRuleData::A_stopIfTrue] = true;
andre@1: 
andre@1:     XlsxCfVoData cfvo1(type1, val1);
andre@1:     XlsxCfVoData cfvo2(type2, val2);
andre@1:     cfRule->attrs[XlsxCfRuleData::A_cfvo1] = QVariant::fromValue(cfvo1);
andre@1:     cfRule->attrs[XlsxCfRuleData::A_cfvo2] = QVariant::fromValue(cfvo2);
andre@1: 
andre@1:     d->cfRules.append(cfRule);
andre@1:     return true;
andre@1: }
andre@1: 
andre@1: /*!
andre@1:  * Add a colorScale rule with the given \a minColor, \a midColor, \a maxColor and \a stopIfTrue.
andre@1:  * Return false if failed.
andre@1:  */
andre@1: bool ConditionalFormatting::add3ColorScaleRule(const QColor &minColor, const QColor &midColor, const QColor &maxColor, bool stopIfTrue)
andre@1: {
andre@1:     ValueObjectType type1 = VOT_Min;
andre@1:     ValueObjectType type2 = VOT_Percent;
andre@1:     ValueObjectType type3 = VOT_Max;
andre@1:     QString val1 = QStringLiteral("0");
andre@1:     QString val2 = QStringLiteral("50");
andre@1:     QString val3 = QStringLiteral("0");
andre@1: 
andre@1:     QSharedPointer<XlsxCfRuleData> cfRule(new XlsxCfRuleData);
andre@1: 
andre@1:     cfRule->attrs[XlsxCfRuleData::A_type] = QStringLiteral("colorScale");
andre@1:     cfRule->attrs[XlsxCfRuleData::A_color1] = XlsxColor(minColor);
andre@1:     cfRule->attrs[XlsxCfRuleData::A_color2] = XlsxColor(midColor);
andre@1:     cfRule->attrs[XlsxCfRuleData::A_color3] = XlsxColor(maxColor);
andre@1: 
andre@1:     if (stopIfTrue)
andre@1:         cfRule->attrs[XlsxCfRuleData::A_stopIfTrue] = true;
andre@1: 
andre@1:     XlsxCfVoData cfvo1(type1, val1);
andre@1:     XlsxCfVoData cfvo2(type2, val2);
andre@1:     XlsxCfVoData cfvo3(type3, val3);
andre@1:     cfRule->attrs[XlsxCfRuleData::A_cfvo1] = QVariant::fromValue(cfvo1);
andre@1:     cfRule->attrs[XlsxCfRuleData::A_cfvo2] = QVariant::fromValue(cfvo2);
andre@1:     cfRule->attrs[XlsxCfRuleData::A_cfvo3] = QVariant::fromValue(cfvo3);
andre@1: 
andre@1:     d->cfRules.append(cfRule);
andre@1:     return true;
andre@1: }
andre@1: 
andre@1: /*!
andre@1:     Returns the ranges on which the validation will be applied.
andre@1:  */
andre@1: QList<CellRange> ConditionalFormatting::ranges() const
andre@1: {
andre@1:     return d->ranges;
andre@1: }
andre@1: 
andre@1: /*!
andre@1:     Add the \a cell on which the conditional formatting will apply to.
andre@1:  */
andre@1: void ConditionalFormatting::addCell(const CellReference &cell)
andre@1: {
andre@1:     d->ranges.append(CellRange(cell, cell));
andre@1: }
andre@1: 
andre@1: /*!
andre@1:     \overload
andre@1:     Add the cell(\a row, \a col) on which the conditional formatting will apply to.
andre@1:  */
andre@1: void ConditionalFormatting::addCell(int row, int col)
andre@1: {
andre@1:     d->ranges.append(CellRange(row, col, row, col));
andre@1: }
andre@1: 
andre@1: /*!
andre@1:     \overload
andre@1:     Add the range(\a firstRow, \a firstCol, \a lastRow, \a lastCol) on
andre@1:     which the conditional formatting will apply to.
andre@1:  */
andre@1: void ConditionalFormatting::addRange(int firstRow, int firstCol, int lastRow, int lastCol)
andre@1: {
andre@1:     d->ranges.append(CellRange(firstRow, firstCol, lastRow, lastCol));
andre@1: }
andre@1: 
andre@1: /*!
andre@1:     Add the \a range on which the conditional formatting will apply to.
andre@1:  */
andre@1: void ConditionalFormatting::addRange(const CellRange &range)
andre@1: {
andre@1:     d->ranges.append(range);
andre@1: }
andre@1: 
andre@1: bool ConditionalFormattingPrivate::readCfRule(QXmlStreamReader &reader, XlsxCfRuleData *rule, Styles *styles)
andre@1: {
andre@1:     Q_ASSERT(reader.name() == QLatin1String("cfRule"));
andre@1:     QXmlStreamAttributes attrs = reader.attributes();
andre@1:     if (attrs.hasAttribute(QLatin1String("type")))
andre@1:         rule->attrs[XlsxCfRuleData::A_type] = attrs.value(QLatin1String("type")).toString();
andre@1:     if (attrs.hasAttribute(QLatin1String("dxfId"))) {
andre@1:         int id = attrs.value(QLatin1String("dxfId")).toString().toInt();
andre@1:         if (styles)
andre@1:             rule->dxfFormat = styles->dxfFormat(id);
andre@1:         else
andre@1:             rule->dxfFormat.setDxfIndex(id);
andre@1:     }
andre@1:     rule->priority = attrs.value(QLatin1String("priority")).toString().toInt();
andre@1:     if (attrs.value(QLatin1String("stopIfTrue")) == QLatin1String("1")) {
andre@1:         //default is false
andre@1:         rule->attrs[XlsxCfRuleData::A_stopIfTrue] = QLatin1String("1");
andre@1:     }
andre@1:     if (attrs.value(QLatin1String("aboveAverage")) == QLatin1String("0")) {
andre@1:         //default is true
andre@1:         rule->attrs[XlsxCfRuleData::A_aboveAverage] = QLatin1String("0");
andre@1:     }
andre@1:     if (attrs.value(QLatin1String("percent")) == QLatin1String("1")) {
andre@1:         //default is false
andre@1:         rule->attrs[XlsxCfRuleData::A_percent] = QLatin1String("1");
andre@1:     }
andre@1:     if (attrs.value(QLatin1String("bottom")) == QLatin1String("1")) {
andre@1:         //default is false
andre@1:         rule->attrs[XlsxCfRuleData::A_bottom] = QLatin1String("1");
andre@1:     }
andre@1:     if (attrs.hasAttribute(QLatin1String("operator")))
andre@1:         rule->attrs[XlsxCfRuleData::A_operator] = attrs.value(QLatin1String("operator")).toString();
andre@1: 
andre@1:     if (attrs.hasAttribute(QLatin1String("text")))
andre@1:         rule->attrs[XlsxCfRuleData::A_text] = attrs.value(QLatin1String("text")).toString();
andre@1: 
andre@1:     if (attrs.hasAttribute(QLatin1String("timePeriod")))
andre@1:         rule->attrs[XlsxCfRuleData::A_timePeriod] = attrs.value(QLatin1String("timePeriod")).toString();
andre@1: 
andre@1:     if (attrs.hasAttribute(QLatin1String("rank")))
andre@1:         rule->attrs[XlsxCfRuleData::A_rank] = attrs.value(QLatin1String("rank")).toString();
andre@1: 
andre@1:     if (attrs.hasAttribute(QLatin1String("stdDev")))
andre@1:         rule->attrs[XlsxCfRuleData::A_stdDev] = attrs.value(QLatin1String("stdDev")).toString();
andre@1: 
andre@1:     if (attrs.value(QLatin1String("equalAverage")) == QLatin1String("1")) {
andre@1:         //default is false
andre@1:         rule->attrs[XlsxCfRuleData::A_equalAverage] = QLatin1String("1");
andre@1:     }
andre@1: 
andre@1:     while (!reader.atEnd()) {
andre@1:         reader.readNextStartElement();
andre@1:         if (reader.tokenType() == QXmlStreamReader::StartElement) {
andre@1:             if (reader.name() == QLatin1String("formula")) {
andre@1:                 QString f = reader.readElementText();
andre@1:                 if (!rule->attrs.contains(XlsxCfRuleData::A_formula1))
andre@1:                     rule->attrs[XlsxCfRuleData::A_formula1] = f;
andre@1:                 else if (!rule->attrs.contains(XlsxCfRuleData::A_formula2))
andre@1:                     rule->attrs[XlsxCfRuleData::A_formula2] = f;
andre@1:                 else if (!rule->attrs.contains(XlsxCfRuleData::A_formula3))
andre@1:                     rule->attrs[XlsxCfRuleData::A_formula3] = f;
andre@1:             } else if (reader.name() == QLatin1String("dataBar")) {
andre@1:                 readCfDataBar(reader, rule);
andre@1:             } else if (reader.name() == QLatin1String("colorScale")) {
andre@1:                 readCfColorScale(reader, rule);
andre@1:             }
andre@1:         }
andre@1:         if (reader.tokenType() == QXmlStreamReader::EndElement
andre@1:                 && reader.name() == QStringLiteral("conditionalFormatting")) {
andre@1:             break;
andre@1:         }
andre@1:     }
andre@1:     return true;
andre@1: }
andre@1: 
andre@1: bool ConditionalFormattingPrivate::readCfDataBar(QXmlStreamReader &reader, XlsxCfRuleData *rule)
andre@1: {
andre@1:     Q_ASSERT(reader.name() == QLatin1String("dataBar"));
andre@1:     QXmlStreamAttributes attrs = reader.attributes();
andre@1:     if (attrs.value(QLatin1String("showValue")) == QLatin1String("0"))
andre@1:         rule->attrs[XlsxCfRuleData::A_hideData] = QStringLiteral("1");
andre@1: 
andre@1:     while (!reader.atEnd()) {
andre@1:         reader.readNextStartElement();
andre@1:         if (reader.tokenType() == QXmlStreamReader::StartElement) {
andre@1:             if (reader.name() == QLatin1String("cfvo")) {
andre@1:                 XlsxCfVoData data;
andre@1:                 readCfVo(reader, data);
andre@1:                 if (!rule->attrs.contains(XlsxCfRuleData::A_cfvo1))
andre@1:                     rule->attrs[XlsxCfRuleData::A_cfvo1] = QVariant::fromValue(data);
andre@1:                 else
andre@1:                     rule->attrs[XlsxCfRuleData::A_cfvo2] = QVariant::fromValue(data);
andre@1:             } else if (reader.name() == QLatin1String("color")) {
andre@1:                 XlsxColor color;
andre@1:                 color.loadFromXml(reader);
andre@1:                 rule->attrs[XlsxCfRuleData::A_color1] = color;
andre@1:             }
andre@1:         }
andre@1:         if (reader.tokenType() == QXmlStreamReader::EndElement
andre@1:                 && reader.name() == QStringLiteral("dataBar")) {
andre@1:             break;
andre@1:         }
andre@1:     }
andre@1: 
andre@1:     return true;
andre@1: }
andre@1: 
andre@1: bool ConditionalFormattingPrivate::readCfColorScale(QXmlStreamReader &reader, XlsxCfRuleData *rule)
andre@1: {
andre@1:     Q_ASSERT(reader.name() == QLatin1String("colorScale"));
andre@1: 
andre@1:     while (!reader.atEnd()) {
andre@1:         reader.readNextStartElement();
andre@1:         if (reader.tokenType() == QXmlStreamReader::StartElement) {
andre@1:             if (reader.name() == QLatin1String("cfvo")) {
andre@1:                 XlsxCfVoData data;
andre@1:                 readCfVo(reader, data);
andre@1:                 if (!rule->attrs.contains(XlsxCfRuleData::A_cfvo1))
andre@1:                     rule->attrs[XlsxCfRuleData::A_cfvo1] = QVariant::fromValue(data);
andre@1:                 else if (!rule->attrs.contains(XlsxCfRuleData::A_cfvo2))
andre@1:                     rule->attrs[XlsxCfRuleData::A_cfvo2] = QVariant::fromValue(data);
andre@1:                 else
andre@1:                     rule->attrs[XlsxCfRuleData::A_cfvo2] = QVariant::fromValue(data);
andre@1:             } else if (reader.name() == QLatin1String("color")) {
andre@1:                 XlsxColor color;
andre@1:                 color.loadFromXml(reader);
andre@1:                 if (!rule->attrs.contains(XlsxCfRuleData::A_color1))
andre@1:                     rule->attrs[XlsxCfRuleData::A_color1] = color;
andre@1:                 else if (!rule->attrs.contains(XlsxCfRuleData::A_color2))
andre@1:                     rule->attrs[XlsxCfRuleData::A_color2] = color;
andre@1:                 else
andre@1:                     rule->attrs[XlsxCfRuleData::A_color3] = color;
andre@1:             }
andre@1:         }
andre@1:         if (reader.tokenType() == QXmlStreamReader::EndElement
andre@1:                 && reader.name() == QStringLiteral("colorScale")) {
andre@1:             break;
andre@1:         }
andre@1:     }
andre@1: 
andre@1:     return true;
andre@1: }
andre@1: 
andre@1: bool ConditionalFormattingPrivate::readCfVo(QXmlStreamReader &reader, XlsxCfVoData &cfvo)
andre@1: {
andre@1:     Q_ASSERT(reader.name() == QStringLiteral("cfvo"));
andre@1: 
andre@1:     QXmlStreamAttributes attrs = reader.attributes();
andre@1: 
andre@1:     QString type = attrs.value(QLatin1String("type")).toString();
andre@1:     ConditionalFormatting::ValueObjectType t;
andre@1:     if (type == QLatin1String("formula"))
andre@1:         t = ConditionalFormatting::VOT_Formula;
andre@1:     else if (type == QLatin1String("max"))
andre@1:         t = ConditionalFormatting::VOT_Max;
andre@1:     else if (type == QLatin1String("min"))
andre@1:         t = ConditionalFormatting::VOT_Min;
andre@1:     else if (type == QLatin1String("num"))
andre@1:         t = ConditionalFormatting::VOT_Num;
andre@1:     else if (type == QLatin1String("percent"))
andre@1:         t = ConditionalFormatting::VOT_Percent;
andre@1:     else //if (type == QLatin1String("percentile"))
andre@1:         t = ConditionalFormatting::VOT_Percentile;
andre@1: 
andre@1:     cfvo.type = t;
andre@1:     cfvo.value = attrs.value(QLatin1String("val")).toString();
andre@1:     if (attrs.value(QLatin1String("gte")) == QLatin1String("0")) {
andre@1:         //default is true
andre@1:         cfvo.gte = false;
andre@1:     }
andre@1:     return true;
andre@1: }
andre@1: 
andre@1: bool ConditionalFormatting::loadFromXml(QXmlStreamReader &reader, Styles *styles)
andre@1: {
andre@1:     Q_ASSERT(reader.name() == QStringLiteral("conditionalFormatting"));
andre@1: 
andre@1:     d->ranges.clear();
andre@1:     d->cfRules.clear();
andre@1:     QXmlStreamAttributes attrs = reader.attributes();
andre@1:     QString sqref = attrs.value(QLatin1String("sqref")).toString();
andre@1:     foreach (QString range, sqref.split(QLatin1Char(' ')))
andre@1:         this->addRange(range);
andre@1: 
andre@1:     while (!reader.atEnd()) {
andre@1:         reader.readNextStartElement();
andre@1:         if (reader.tokenType() == QXmlStreamReader::StartElement) {
andre@1:             if (reader.name() == QLatin1String("cfRule")) {
andre@1:                 QSharedPointer<XlsxCfRuleData> cfRule(new XlsxCfRuleData);
andre@1:                 d->readCfRule(reader, cfRule.data(), styles);
andre@1:                 d->cfRules.append(cfRule);
andre@1:             }
andre@1:         }
andre@1:         if (reader.tokenType() == QXmlStreamReader::EndElement
andre@1:                 && reader.name() == QStringLiteral("conditionalFormatting")) {
andre@1:             break;
andre@1:         }
andre@1:     }
andre@1: 
andre@1: 
andre@1:     return true;
andre@1: }
andre@1: 
andre@1: bool ConditionalFormatting::saveToXml(QXmlStreamWriter &writer) const
andre@1: {
andre@1:     writer.writeStartElement(QStringLiteral("conditionalFormatting"));
andre@1:     QStringList sqref;
andre@1:     foreach (CellRange range, ranges())
andre@1:         sqref.append(range.toString());
andre@1:     writer.writeAttribute(QStringLiteral("sqref"), sqref.join(QLatin1Char(' ')));
andre@1: 
andre@19:     const QString extNS = QStringLiteral("http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
andre@19:     const QString xmNS = QStringLiteral("http://schemas.microsoft.com/office/excel/2006/main");
andre@19: 
andre@1:     for (int i=0; i<d->cfRules.size(); ++i) {
andre@1:         const QSharedPointer<XlsxCfRuleData> &rule = d->cfRules[i];
andre@1:         writer.writeStartElement(QStringLiteral("cfRule"));
andre@1:         writer.writeAttribute(QStringLiteral("type"), rule->attrs[XlsxCfRuleData::A_type].toString());
andre@1:         if (rule->dxfFormat.dxfIndexValid())
andre@1:             writer.writeAttribute(QStringLiteral("dxfId"), QString::number(rule->dxfFormat.dxfIndex()));
andre@1:         writer.writeAttribute(QStringLiteral("priority"), QString::number(rule->priority));
andre@1:         if (rule->attrs.contains(XlsxCfRuleData::A_stopIfTrue))
andre@1:             writer.writeAttribute(QStringLiteral("stopIfTrue"), rule->attrs[XlsxCfRuleData::A_stopIfTrue].toString());
andre@1:         if (rule->attrs.contains(XlsxCfRuleData::A_aboveAverage))
andre@1:             writer.writeAttribute(QStringLiteral("aboveAverage"), rule->attrs[XlsxCfRuleData::A_aboveAverage].toString());
andre@1:         if (rule->attrs.contains(XlsxCfRuleData::A_percent))
andre@1:             writer.writeAttribute(QStringLiteral("percent"), rule->attrs[XlsxCfRuleData::A_percent].toString());
andre@1:         if (rule->attrs.contains(XlsxCfRuleData::A_bottom))
andre@1:             writer.writeAttribute(QStringLiteral("bottom"), rule->attrs[XlsxCfRuleData::A_bottom].toString());
andre@1:         if (rule->attrs.contains(XlsxCfRuleData::A_operator))
andre@1:             writer.writeAttribute(QStringLiteral("operator"), rule->attrs[XlsxCfRuleData::A_operator].toString());
andre@1:         if (rule->attrs.contains(XlsxCfRuleData::A_text))
andre@1:             writer.writeAttribute(QStringLiteral("text"), rule->attrs[XlsxCfRuleData::A_text].toString());
andre@1:         if (rule->attrs.contains(XlsxCfRuleData::A_timePeriod))
andre@1:             writer.writeAttribute(QStringLiteral("timePeriod"), rule->attrs[XlsxCfRuleData::A_timePeriod].toString());
andre@1:         if (rule->attrs.contains(XlsxCfRuleData::A_rank))
andre@1:             writer.writeAttribute(QStringLiteral("rank"), rule->attrs[XlsxCfRuleData::A_rank].toString());
andre@1:         if (rule->attrs.contains(XlsxCfRuleData::A_stdDev))
andre@1:             writer.writeAttribute(QStringLiteral("stdDev"), rule->attrs[XlsxCfRuleData::A_stdDev].toString());
andre@1:         if (rule->attrs.contains(XlsxCfRuleData::A_equalAverage))
andre@1:             writer.writeAttribute(QStringLiteral("equalAverage"), rule->attrs[XlsxCfRuleData::A_equalAverage].toString());
andre@1: 
andre@1:         if (rule->attrs[XlsxCfRuleData::A_type] == QLatin1String("dataBar")) {
andre@1:             writer.writeStartElement(QStringLiteral("dataBar"));
andre@1:             if (rule->attrs.contains(XlsxCfRuleData::A_hideData))
andre@1:                 writer.writeAttribute(QStringLiteral("showValue"), QStringLiteral("0"));
andre@1:             d->writeCfVo(writer, rule->attrs[XlsxCfRuleData::A_cfvo1].value<XlsxCfVoData>());
andre@1:             d->writeCfVo(writer, rule->attrs[XlsxCfRuleData::A_cfvo2].value<XlsxCfVoData>());
andre@1:             rule->attrs[XlsxCfRuleData::A_color1].value<XlsxColor>().saveToXml(writer);
andre@1:             writer.writeEndElement();//dataBar
andre@19:             writer.writeStartElement(QStringLiteral("extLst"));
andre@19:             writer.writeStartElement(QStringLiteral("ext"));
andre@19:             writer.writeNamespace(extNS, QStringLiteral("x14"));
andre@19:             writer.writeAttribute(QStringLiteral("uri"),
andre@19:                                   QStringLiteral("{B025F937-C7B1-47D3-B67F-A62EFF666E3E}"));
andre@19:             writer.writeStartElement(extNS, QStringLiteral("id"));
andre@19:             writer.writeCharacters(QStringLiteral("{00000000-000E-0000-0000-000001000000}"));
andre@19:             writer.writeEndElement(); //id
andre@19:             writer.writeEndElement(); //ext
andre@19:             writer.writeEndElement(); //extLst
andre@1:         } else if (rule->attrs[XlsxCfRuleData::A_type] == QLatin1String("colorScale")) {
andre@1:             writer.writeStartElement(QStringLiteral("colorScale"));
andre@1:             d->writeCfVo(writer, rule->attrs[XlsxCfRuleData::A_cfvo1].value<XlsxCfVoData>());
andre@1:             d->writeCfVo(writer, rule->attrs[XlsxCfRuleData::A_cfvo2].value<XlsxCfVoData>());
andre@1:             if (rule->attrs.contains(XlsxCfRuleData::A_cfvo3))
andre@1:                 d->writeCfVo(writer, rule->attrs[XlsxCfRuleData::A_cfvo3].value<XlsxCfVoData>());
andre@1: 
andre@1:             rule->attrs[XlsxCfRuleData::A_color1].value<XlsxColor>().saveToXml(writer);
andre@1:             rule->attrs[XlsxCfRuleData::A_color2].value<XlsxColor>().saveToXml(writer);
andre@1:             if (rule->attrs.contains(XlsxCfRuleData::A_color3))
andre@1:                 rule->attrs[XlsxCfRuleData::A_color3].value<XlsxColor>().saveToXml(writer);
andre@1: 
andre@1:             writer.writeEndElement();//colorScale
andre@1:         }
andre@1: 
andre@1: 
andre@1:         if (rule->attrs.contains(XlsxCfRuleData::A_formula1_temp)) {
andre@1:             QString startCell = ranges()[0].toString().split(QLatin1Char(':'))[0];
andre@1:             writer.writeTextElement(QStringLiteral("formula"), rule->attrs[XlsxCfRuleData::A_formula1_temp].toString().arg(startCell));
andre@1:         } else if (rule->attrs.contains(XlsxCfRuleData::A_formula1)) {
andre@1:             writer.writeTextElement(QStringLiteral("formula"), rule->attrs[XlsxCfRuleData::A_formula1].toString());
andre@1:         }
andre@1:         if (rule->attrs.contains(XlsxCfRuleData::A_formula2))
andre@1:             writer.writeTextElement(QStringLiteral("formula"), rule->attrs[XlsxCfRuleData::A_formula2].toString());
andre@1:         if (rule->attrs.contains(XlsxCfRuleData::A_formula3))
andre@1:             writer.writeTextElement(QStringLiteral("formula"), rule->attrs[XlsxCfRuleData::A_formula3].toString());
andre@1: 
andre@1:         writer.writeEndElement(); //cfRule
andre@1:     }
andre@1: 
andre@1:     writer.writeEndElement(); //conditionalFormatting
andre@19:     writer.writeStartElement(QStringLiteral("extLst"));
andre@19:     writer.writeStartElement(QStringLiteral("ext"));
andre@19:     writer.writeNamespace(extNS, QStringLiteral("x14"));
andre@19:     writer.writeAttribute(QStringLiteral("uri"),
andre@19:                           QStringLiteral("{78C0D931-6437-407d-A8EE-F0AAD7539E65}"));
andre@19:     writer.writeStartElement(extNS, QStringLiteral("conditionalFormattings"));
andre@19:     writer.writeStartElement(extNS, QStringLiteral("conditionalFormatting"));
andre@19:     writer.writeNamespace(xmNS, QStringLiteral("xm"));
andre@19:     writer.writeStartElement(extNS, QStringLiteral("cfRule"));
andre@19:     writer.writeAttribute("type", "dataBar");
andre@19:     writer.writeAttribute("id", "{00000000-000E-0000-0000-000001000000}");
andre@19:     writer.writeStartElement(extNS, "dataBar");
andre@19:     writer.writeAttribute("gradient", "0");
andre@19:     writer.writeAttribute("negativeBarColorSameAsPositive", "1");
andre@19:     writer.writeAttribute("axisPosition", "none");
andre@19:     writer.writeStartElement(extNS, "cfvo");
andre@19:     writer.writeAttribute("type", "num");
andre@19:     writer.writeStartElement(xmNS, "f");
andre@19:     writer.writeCharacters("0");
andre@19:     writer.writeEndElement(); // f
andre@19:     writer.writeEndElement(); // cfvo
andre@19: 
andre@19:     writer.writeStartElement(extNS, "cfvo");
andre@19:     writer.writeAttribute("type", "num");
andre@19:     writer.writeStartElement(xmNS, "f");
andre@19:     writer.writeCharacters("100");
andre@19:     writer.writeEndElement(); // f
andre@19:     writer.writeEndElement(); // cfvo
andre@19:     writer.writeEndElement(); // dataBar
andre@19:     writer.writeEndElement(); // cfrule
andre@19: 
andre@19:     writer.writeStartElement(xmNS, "sqref");
andre@19:     writer.writeCharacters(sqref.join(QLatin1Char(' ')));
andre@19:     writer.writeEndElement(); // sqref
andre@19: 
andre@19:     writer.writeEndElement(); // conditionalFormatting
andre@19:     writer.writeEndElement(); // conditionalFormattings
andre@19: 
andre@19:     writer.writeEndElement(); //ext
andre@19:     writer.writeEndElement(); //extLst
andre@1:     return true;
andre@1: }
andre@1: 
andre@1: QT_END_NAMESPACE_XLSX