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: #include "xlsxutility_p.h"
andre@1: #include "xlsxcellreference.h"
andre@1: 
andre@1: #include <QString>
andre@1: #include <QPoint>
andre@1: #include <QRegularExpression>
andre@1: #include <QMap>
andre@1: #include <QStringList>
andre@1: #include <QColor>
andre@1: #include <QDateTime>
andre@1: #include <QDebug>
andre@1: 
andre@1: namespace QXlsx {
andre@1: 
andre@1: bool parseXsdBoolean(const QString &value, bool defaultValue)
andre@1: {
andre@1:     if (value == QLatin1String("1") || value == QLatin1String("true"))
andre@1:         return true;
andre@1:     if (value == QLatin1String("0") || value == QLatin1String("false"))
andre@1:         return false;
andre@1:     return defaultValue;
andre@1: }
andre@1: 
andre@1: QStringList splitPath(const QString &path)
andre@1: {
andre@1:     int idx = path.lastIndexOf(QLatin1Char('/'));
andre@1:     if (idx == -1)
andre@1:         return QStringList()<<QStringLiteral(".")<<path;
andre@1: 
andre@1:     return QStringList()<<path.left(idx)<<path.mid(idx+1);
andre@1: }
andre@1: 
andre@1: /*
andre@1:  * Return the .rel file path based on filePath
andre@1:  */
andre@1: QString getRelFilePath(const QString &filePath)
andre@1: {
andre@1:     int idx = filePath.lastIndexOf(QLatin1Char('/'));
andre@1:     if (idx == -1)
andre@1:         return QString();
andre@1: 
andre@1:     return QString(filePath.left(idx) + QLatin1String("/_rels/")
andre@1:                    + filePath.mid(idx+1) + QLatin1String(".rels"));
andre@1: }
andre@1: 
andre@1: double datetimeToNumber(const QDateTime &dt, bool is1904)
andre@1: {
andre@1:     //Note, for number 0, Excel2007 shown as 1900-1-0, which should be 1899-12-31
andre@1:     QDateTime epoch(is1904 ? QDate(1904, 1, 1): QDate(1899, 12, 31), QTime(0,0));
andre@1: 
andre@1:     double excel_time = epoch.msecsTo(dt) / (1000*60*60*24.0);
andre@1: 
andre@1: #if QT_VERSION >= 0x050200
andre@1:     if (dt.isDaylightTime())    // Add one hour if the date is Daylight
andre@1:         excel_time += 1.0 / 24.0;
andre@1: #endif
andre@1: 
andre@1:     if (!is1904 && excel_time > 59) {//31+28
andre@1:         //Account for Excel erroneously treating 1900 as a leap year.
andre@1:         excel_time += 1;
andre@1:     }
andre@1: 
andre@1:     return excel_time;
andre@1: }
andre@1: 
andre@1: double timeToNumber(const QTime &time)
andre@1: {
andre@1:     return QTime(0,0).msecsTo(time) / (1000*60*60*24.0);
andre@1: }
andre@1: 
andre@1: QDateTime datetimeFromNumber(double num, bool is1904)
andre@1: {
andre@1:     if (!is1904 && num > 60)
andre@1:         num = num - 1;
andre@1: 
andre@1:     qint64 msecs = static_cast<qint64>(num * 1000*60*60*24.0 + 0.5);
andre@1:     QDateTime epoch(is1904 ? QDate(1904, 1, 1): QDate(1899, 12, 31), QTime(0,0));
andre@1: 
andre@1:     QDateTime dt = epoch.addMSecs(msecs);
andre@1: 
andre@1: #if QT_VERSION >= 0x050200
andre@1:     // Remove one hour to see whether the date is Daylight
andre@1:     QDateTime dt2 = dt.addMSecs(-3600);
andre@1:     if (dt2.isDaylightTime())
andre@1:         return dt2;
andre@1: #endif
andre@1: 
andre@1:     return dt;
andre@1: }
andre@1: 
andre@1: /*
andre@1:   Creates a valid sheet name
andre@1:     minimum length is 1
andre@1:     maximum length is 31
andre@1:     doesn't contain special chars: / \ ? * ] [ :
andre@1:     Sheet names must not begin or end with ' (apostrophe)
andre@1: 
andre@1:   Invalid characters are replaced by one space character ' '.
andre@1:  */
andre@1: QString createSafeSheetName(const QString &nameProposal)
andre@1: {
andre@1:     if (nameProposal.isEmpty())
andre@1:         return QString();
andre@1: 
andre@1:     QString ret = nameProposal;
andre@1:     if (nameProposal.length() > 2 && nameProposal.startsWith(QLatin1Char('\'')) && nameProposal.endsWith(QLatin1Char('\'')))
andre@1:         ret = unescapeSheetName(ret);
andre@1: 
andre@1:     //Replace invalid chars with space.
andre@1:     if (nameProposal.contains(QRegularExpression(QStringLiteral("[/\\\\?*\\][:]"))))
andre@1:         ret.replace(QRegularExpression(QStringLiteral("[/\\\\?*\\][:]")), QStringLiteral(" "));
andre@1:     if (ret.startsWith(QLatin1Char('\'')))
andre@1:         ret[0] = QLatin1Char(' ');
andre@1:     if (ret.endsWith(QLatin1Char('\'')))
andre@1:         ret[ret.size()-1] = QLatin1Char(' ');
andre@1: 
andre@1:     if (ret.size() > 31)
andre@1:         ret = ret.left(31);
andre@1:     return ret;
andre@1: }
andre@1: 
andre@1: /*
andre@1:  * When sheetName contains space or apostrophe, escaped is needed by cellFormula/definedName/chartSerials.
andre@1:  */
andre@1: QString escapeSheetName(const QString &sheetName)
andre@1: {
andre@1:     //Already escaped.
andre@1:     Q_ASSERT(!sheetName.startsWith(QLatin1Char('\'')) && !sheetName.endsWith(QLatin1Char('\'')));
andre@1: 
andre@1:     //These is no need to escape
andre@1:     if (!sheetName.contains(QRegularExpression(QStringLiteral("[ +\\-,%^=<>'&]"))))
andre@1:         return sheetName;
andre@1: 
andre@1:     //OK, escape is needed.
andre@1:     QString name = sheetName;
andre@1:     name.replace(QLatin1Char('\''), QLatin1String("\'\'"));
andre@1:     return QLatin1Char('\'') + name + QLatin1Char('\'');
andre@1: }
andre@1: 
andre@1: /*
andre@1:  */
andre@1: QString unescapeSheetName(const QString &sheetName)
andre@1: {
andre@1:     Q_ASSERT(sheetName.length() > 2 && sheetName.startsWith(QLatin1Char('\'')) && sheetName.endsWith(QLatin1Char('\'')));
andre@1: 
andre@1:     QString name = sheetName.mid(1, sheetName.length()-2);
andre@1:     name.replace(QLatin1String("\'\'"), QLatin1String("\'"));
andre@1:     return name;
andre@1: }
andre@1: 
andre@1: /*
andre@1:  * whether the string s starts or ends with space
andre@1:  */
andre@1: bool isSpaceReserveNeeded(const QString &s)
andre@1: {
andre@1:     QString spaces(QStringLiteral(" \t\n\r"));
andre@1:     return !s.isEmpty() && (spaces.contains(s.at(0))||spaces.contains(s.at(s.length()-1)));
andre@1: }
andre@1: 
andre@1: /*
andre@1:  * Convert shared formula for non-root cells.
andre@1:  *
andre@1:  * For example, if "B1:B10" have shared formula "=A1*A1", this function will return "=A2*A2"
andre@1:  * for "B2" cell, "=A3*A3" for "B3" cell, etc.
andre@1:  *
andre@1:  * Note, the formula "=A1*A1" for B1 can also be written as "=RC[-1]*RC[-1]", which is the same
andre@1:  * for all other cells. In other words, this formula is shared.
andre@1:  *
andre@1:  * For long run, we need a formula parser.
andre@1:  */
andre@1: QString convertSharedFormula(const QString &rootFormula, const CellReference &rootCell, const CellReference &cell)
andre@1: {
andre@1:     //Find all the "$?[A-Z]+$?[0-9]+" patterns in the rootFormula.
andre@1:     QList<QPair<QString, int> > segments;
andre@1: 
andre@1:     QString segment;
andre@1:     bool inQuote = false;
andre@1:     enum RefState{INVALID, PRE_AZ, AZ, PRE_09, _09};
andre@1:     RefState refState = INVALID;
andre@1:     int refFlag = 0; // 0x00, 0x01, 0x02, 0x03 ==> A1, $A1, A$1, $A$1
andre@1:     foreach (QChar ch, rootFormula) {
andre@1:         if (inQuote) {
andre@1:             segment.append(ch);
andre@1:             if (ch == QLatin1Char('"'))
andre@1:                 inQuote = false;
andre@1:         } else {
andre@1:             if (ch == QLatin1Char('"')) {
andre@1:                 inQuote = true;
andre@1:                 refState = INVALID;
andre@1:                 segment.append(ch);
andre@1:             } else if (ch == QLatin1Char('$')) {
andre@1:                 if (refState == AZ) {
andre@1:                     segment.append(ch);
andre@1:                     refState = PRE_09;
andre@1:                     refFlag |= 0x02;
andre@1:                 } else {
andre@1:                     segments.append(qMakePair(segment, refState==_09 ? refFlag : -1));
andre@1:                     segment = QString(ch); //Start new segment.
andre@1:                     refState = PRE_AZ;
andre@1:                     refFlag = 0x01;
andre@1:                 }
andre@1:             } else if (ch >= QLatin1Char('A') && ch <=QLatin1Char('Z')) {
andre@1:                 if (refState == PRE_AZ || refState == AZ) {
andre@1:                     segment.append(ch);
andre@1:                 } else {
andre@1:                     segments.append(qMakePair(segment, refState==_09 ? refFlag : -1));
andre@1:                     segment = QString(ch); //Start new segment.
andre@1:                     refFlag = 0x00;
andre@1:                 }
andre@1:                 refState = AZ;
andre@1:             } else if (ch >= QLatin1Char('0') && ch <=QLatin1Char('9')) {
andre@1:                 segment.append(ch);
andre@1: 
andre@1:                 if (refState == AZ || refState == PRE_09 || refState == _09)
andre@1:                     refState = _09;
andre@1:                 else
andre@1:                     refState = INVALID;
andre@1:             } else {
andre@1:                 if (refState == _09) {
andre@1:                     segments.append(qMakePair(segment, refFlag));
andre@1:                     segment = QString(ch); //Start new segment.
andre@1:                 } else {
andre@1:                     segment.append(ch);
andre@1:                 }
andre@1:                 refState = INVALID;
andre@1:             }
andre@1:         }
andre@1:     }
andre@1: 
andre@1:     if (!segment.isEmpty())
andre@1:         segments.append(qMakePair(segment, refState==_09 ? refFlag : -1));
andre@1: 
andre@1:     //Replace "A1", "$A1", "A$1" segment with proper one.
andre@1:     QStringList result;
andre@1:     typedef QPair<QString, int> PairType;
andre@1:     foreach (PairType p, segments) {
andre@1:         //qDebug()<<p.first<<p.second;
andre@1:         if (p.second != -1 && p.second != 3) {
andre@1:             CellReference oldRef(p.first);
andre@1:             int row = p.second & 0x02 ? oldRef.row() : oldRef.row()-rootCell.row()+cell.row();
andre@1:             int col = p.second & 0x01 ? oldRef.column() : oldRef.column()-rootCell.column()+cell.column();
andre@1:             result.append(CellReference(row, col).toString(p.second & 0x02, p.second & 0x01));
andre@1:         } else {
andre@1:             result.append(p.first);
andre@1:         }
andre@1:     }
andre@1: 
andre@1:     //OK
andre@1:     return result.join(QString());
andre@1: }
andre@1: 
andre@1: } //namespace QXlsx