1/* This file is part of the KDE project
2 Copyright 2006,2007 Stefan Nikolaus <stefan.nikolaus@kdemail.net>
3 Copyright 1998,1999 Torben Weis <weis@kde.org>
4
5 This library is free software; you can redistribute it and/or
6 modify it under the terms of the GNU Library General Public
7 License as published by the Free Software Foundation; either
8 version 2 of the License, or (at your option) any later version.
9
10 This library is distributed in the hope that it will be useful,
11 but WITHOUT ANY WARRANTY; without even the implied warranty of
12 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
13 Library General Public License for more details.
14
15 You should have received a copy of the GNU Library General Public License
16 along with this library; see the file COPYING.LIB. If not, write to
17 the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
18 Boston, MA 02110-1301, USA.
19*/
20
21// Local
22#include "Util.h"
23
24#include <ctype.h>
25
26
27#include <kdebug.h>
28
29#include "Formula.h"
30#include "calligra_sheets_limits.h"
31#include "Localization.h"
32#include "Map.h"
33#include "NamedAreaManager.h"
34#include "Region.h"
35#include "Sheet.h"
36#include "Style.h"
37
38#include <QPen>
39
40using namespace Calligra::Sheets;
41
42
43//used in Cell::encodeFormula and
44// dialogs/kspread_dlg_paperlayout.cc
45int Calligra::Sheets::Util::decodeColumnLabelText(const QString &labelText)
46{
47 int col = 0;
48 const int offset = 'a' - 'A';
49 int counterColumn = 0;
50 const uint totalLength = labelText.length();
51 uint labelTextLength = 0;
52 for ( ; labelTextLength < totalLength; labelTextLength++) {
53 const char c = labelText[labelTextLength].toLatin1();
54 if (labelTextLength == 0 && c == '$')
55 continue; // eat an absolute reference char that could be at the beginning only
56 if (!((c >= 'A' && c <= 'Z') || (c >= 'a' && c <= 'z')))
57 break;
58 }
59 if (labelTextLength == 0) {
60 kWarning(36001) << "No column label text found for col:" << labelText;
61 return 0;
62 }
63 for (uint i = 0; i < labelTextLength; i++) {
64 const char c = labelText[i].toLatin1();
65 counterColumn = (int) ::pow(26.0 , static_cast<int>(labelTextLength - i - 1));
66 if (c >= 'A' && c <= 'Z')
67 col += counterColumn * (c - 'A' + 1); // okay here (Werner)
68 else if (c >= 'a' && c <= 'z')
69 col += counterColumn * (c - 'A' - offset + 1);
70 }
71 return col;
72}
73
74int Calligra::Sheets::Util::decodeRowLabelText(const QString &labelText)
75{
76 QRegExp rx("(|\\$)([A-Za-z]+)(|\\$)([0-9]+)");
77 if(rx.exactMatch(labelText))
78 return rx.cap(4).toInt();
79 return 0;
80}
81
82QString Calligra::Sheets::Util::encodeColumnLabelText(int column)
83{
84 return Cell::columnName(column);
85}
86
87bool Calligra::Sheets::Util::isCellReference(const QString &text, int startPos)
88{
89 int length = text.length();
90 if (length < 1 || startPos >= length)
91 return false;
92
93 const QChar *data = text.constData();
94
95 if (startPos > 0) {
96 data += startPos;
97 }
98
99 if (*data == QChar('$', 0)) {
100 ++data;
101 }
102
103 bool letterFound = false;
104 while (1) {
105 if (data->isNull()) {
106 return false;
107 }
108
109 ushort c = data->unicode();
110 if ((c < 'A' || c > 'Z') && (c < 'a' || c > 'z'))
111 break;
112
113 letterFound = true;
114 ++data;
115 }
116
117 if (!letterFound) {
118 return false;
119 }
120
121 if (*data == QChar('$', 0)) {
122 ++data;
123 }
124
125 bool numberFound = false;
126 while (!data->isNull()) {
127 ushort c = data->unicode();
128 if (c < '0' || c > '9')
129 break;
130 numberFound = true;
131 ++data;
132 }
133
134 return numberFound && data->isNull(); // we found the number and reached end
135}
136
137QDomElement Calligra::Sheets::NativeFormat::createElement(const QString & tagName, const QFont & font, QDomDocument & doc)
138{
139 QDomElement e(doc.createElement(tagName));
140
141 e.setAttribute("family", font.family());
142 e.setAttribute("size", font.pointSize());
143 e.setAttribute("weight", font.weight());
144 if (font.bold())
145 e.setAttribute("bold", "yes");
146 if (font.italic())
147 e.setAttribute("italic", "yes");
148 if (font.underline())
149 e.setAttribute("underline", "yes");
150 if (font.strikeOut())
151 e.setAttribute("strikeout", "yes");
152 //e.setAttribute( "charset", KGlobal::charsets()->name( font ) );
153
154 return e;
155}
156
157QDomElement Calligra::Sheets::NativeFormat::createElement(const QString & tagname, const QPen & pen, QDomDocument & doc)
158{
159 QDomElement e(doc.createElement(tagname));
160 e.setAttribute("color", pen.color().name());
161 e.setAttribute("style", (int)pen.style());
162 e.setAttribute("width", (int)pen.width());
163 return e;
164}
165
166QFont Calligra::Sheets::NativeFormat::toFont(KoXmlElement & element)
167{
168 QFont f;
169 f.setFamily(element.attribute("family"));
170
171 bool ok;
172 const int size = element.attribute("size").toInt(&ok);
173 if (ok)
174 f.setPointSize(size);
175
176 const int weight = element.attribute("weight").toInt(&ok);
177 if (!ok)
178 f.setWeight(weight);
179
180 if (element.hasAttribute("italic") && element.attribute("italic") == "yes")
181 f.setItalic(true);
182
183 if (element.hasAttribute("bold") && element.attribute("bold") == "yes")
184 f.setBold(true);
185
186 if (element.hasAttribute("underline") && element.attribute("underline") == "yes")
187 f.setUnderline(true);
188
189 if (element.hasAttribute("strikeout") && element.attribute("strikeout") == "yes")
190 f.setStrikeOut(true);
191
192 /* Uncomment when charset is added to kspread_dlg_layout
193 + save a document-global charset
194 if ( element.hasAttribute( "charset" ) )
195 KGlobal::charsets()->setQFont( f, element.attribute("charset") );
196 else
197 */
198 // ######## Not needed anymore in 3.0?
199 //KGlobal::charsets()->setQFont( f, KGlobal::locale()->charset() );
200
201 return f;
202}
203
204QPen Calligra::Sheets::NativeFormat::toPen(KoXmlElement & element)
205{
206 bool ok;
207 QPen p;
208
209 p.setStyle((Qt::PenStyle)element.attribute("style").toInt(&ok));
210 if (!ok)
211 return QPen();
212
213 p.setWidth(element.attribute("width").toInt(&ok));
214 if (!ok)
215 return QPen();
216
217 p.setColor(QColor(element.attribute("color")));
218
219 return p;
220}
221
222bool util_isPointValid(const QPoint& point)
223{
224 if (point.x() >= 0
225 && point.y() >= 0
226 && point.x() <= KS_colMax
227 && point.y() <= KS_rowMax
228 )
229 return true;
230 else
231 return false;
232}
233
234bool util_isRectValid(const QRect& rect)
235{
236 if (util_isPointValid(rect.topLeft())
237 && util_isPointValid(rect.bottomRight())
238 )
239 return true;
240 else
241 return false;
242}
243
244
245//not used anywhere
246int Calligra::Sheets::Util::penCompare(QPen const & pen1, QPen const & pen2)
247{
248 if (pen1.style() == Qt::NoPen && pen2.style() == Qt::NoPen)
249 return 0;
250
251 if (pen1.style() == Qt::NoPen)
252 return -1;
253
254 if (pen2.style() == Qt::NoPen)
255 return 1;
256
257 if (pen1.width() < pen2.width())
258 return -1;
259
260 if (pen1.width() > pen2.width())
261 return 1;
262
263 if (pen1.style() < pen2.style())
264 return -1;
265
266 if (pen1.style() > pen2.style())
267 return 1;
268
269 if (pen1.color().name() < pen2.color().name())
270 return -1;
271
272 if (pen1.color().name() > pen2.color().name())
273 return 1;
274
275 return 0;
276}
277
278
279QString Calligra::Sheets::Odf::convertRefToBase(const QString & sheet, const QRect & rect)
280{
281 QPoint bottomRight(rect.bottomRight());
282
283 QString s = '$' +
284 sheet +
285 ".$" +
286 Cell::columnName(bottomRight.x()) +
287 '$' +
288 QString::number(bottomRight.y());
289
290 return s;
291}
292
293QString Calligra::Sheets::Odf::convertRefToRange(const QString & sheet, const QRect & rect)
294{
295 QPoint topLeft(rect.topLeft());
296 QPoint bottomRight(rect.bottomRight());
297
298 if (topLeft == bottomRight)
299 return Odf::convertRefToBase(sheet, rect);
300
301 QString s = '$' +
302 sheet +
303 ".$" +
304 /*Util::encodeColumnLabelText*/Cell::columnName(topLeft.x()) +
305 '$' +
306 QString::number(topLeft.y()) +
307 ":.$" +
308 /*Util::encodeColumnLabelText*/Cell::columnName(bottomRight.x()) +
309 '$' +
310 QString::number(bottomRight.y());
311
312 return s;
313}
314
315// e.g.: Sheet4.A1:Sheet4.E28
316//used in Sheet::saveOdf
317QString Calligra::Sheets::Odf::convertRangeToRef(const QString & sheetName, const QRect & _area)
318{
319 return sheetName + '.' + Cell::name(_area.left(), _area.top()) + ':' + sheetName + '.' + Cell::name(_area.right(), _area.bottom());
320}
321
322QString Calligra::Sheets::Odf::encodePen(const QPen & pen)
323{
324// kDebug()<<"encodePen( const QPen & pen ) :"<<pen;
325 // NOTE Stefan: QPen api docs:
326 // A line width of zero indicates a cosmetic pen. This means
327 // that the pen width is always drawn one pixel wide,
328 // independent of the transformation set on the painter.
329 QString s = QString("%1pt ").arg((pen.width() == 0) ? 1 : pen.width());
330 switch (pen.style()) {
331 case Qt::NoPen:
332 return "none";
333 case Qt::SolidLine:
334 s += "solid";
335 break;
336 case Qt::DashLine:
337 s += "dashed";
338 break;
339 case Qt::DotLine:
340 s += "dotted";
341 break;
342 case Qt::DashDotLine:
343 s += "dot-dash";
344 break;
345 case Qt::DashDotDotLine:
346 s += "dot-dot-dash";
347 break;
348 default: break;
349 }
350 //kDebug() << " encodePen :" << s;
351 if (pen.color().isValid()) {
352 s += ' ' + Style::colorName(pen.color());
353 }
354 return s;
355}
356
357QPen Calligra::Sheets::Odf::decodePen(const QString &border)
358{
359 QPen pen;
360 //string like "0.088cm solid #800000"
361 if (border.isEmpty() || border == "none" || border == "hidden") { // in fact no border
362 pen.setStyle(Qt::NoPen);
363 return pen;
364 }
365 //code from koborder, for the moment kspread doesn't use koborder
366 // ## isn't it faster to use QStringList::split than parse it 3 times?
367 QString _width = border.section(' ', 0, 0);
368 QByteArray _style = border.section(' ', 1, 1).toLatin1();
369 QString _color = border.section(' ', 2, 2);
370
371 pen.setWidth((int)(KoUnit::parseValue(_width, 1.0)));
372
373 if (_style == "none")
374 pen.setStyle(Qt::NoPen);
375 else if (_style == "solid")
376 pen.setStyle(Qt::SolidLine);
377 else if (_style == "dashed")
378 pen.setStyle(Qt::DashLine);
379 else if (_style == "dotted")
380 pen.setStyle(Qt::DotLine);
381 else if (_style == "dot-dash")
382 pen.setStyle(Qt::DashDotLine);
383 else if (_style == "dot-dot-dash")
384 pen.setStyle(Qt::DashDotDotLine);
385 else
386 kDebug() << " style undefined :" << _style;
387
388 if (_color.isEmpty())
389 pen.setColor(QColor());
390 else
391 pen.setColor(QColor(_color));
392
393 return pen;
394}
395
396//Return true when it's a reference to cell from sheet.
397bool Calligra::Sheets::Util::localReferenceAnchor(const QString &_ref)
398{
399 bool isLocalRef = (_ref.indexOf("http://") != 0 &&
400 _ref.indexOf("https://") != 0 &&
401 _ref.indexOf("mailto:") != 0 &&
402 _ref.indexOf("ftp://") != 0 &&
403 _ref.indexOf("file:") != 0);
404 return isLocalRef;
405}
406
407
408QString Calligra::Sheets::Odf::decodeFormula(const QString& expression_, const KLocale *locale, const QString &namespacePrefix)
409{
410 // parsing state
411 enum { Start, InNumber, InString, InIdentifier, InReference, InSheetName } state = Start;
412
413 QString expression = expression_;
414 if (namespacePrefix == "msoxl:") {
415 expression = MSOOXML::convertFormula(expression);
416 }
417
418 // use locale settings
419 QString decimal = locale ? locale->decimalSymbol() : ".";
420
421 const QChar *data = expression.constData();
422 const QChar *start = data;
423
424 if (data->isNull()) {
425 return QString();
426 }
427
428 int length = expression.length() * 2;
429 QString result(length, QChar());
430 result.reserve(length);
431 QChar * out = result.data();
432 QChar * outStart = result.data();
433
434 if (*data == QChar('=', 0)) {
435 *out = *data;
436 ++data;
437 ++out;
438 }
439
440 const QChar *pos = data;
441 while (!data->isNull()) {
442 switch (state) {
443 case Start: {
444 if (data->isDigit()) { // check for number
445 state = InNumber;
446 *out++ = *data++;
447 }
448 else if (*data == QChar('.', 0)) {
449 state = InNumber;
450 *out = decimal[0];
451 ++out;
452 ++data;
453 }
454 else if (isIdentifier(*data)) {
455 // beginning with alphanumeric ?
456 // could be identifier, cell, range, or function...
457 state = InIdentifier;
458 int i = data - start;
459 const static QString errorTypeReplacement("ERRORTYPE");
460 const static QString legacyNormsdistReplacement("LEGACYNORMSDIST");
461 const static QString legacyNormsinvReplacement("LEGACYNORMSINV");
462 const static QString multipleOperations("MULTIPLE.OPERATIONS");
463 if (expression.midRef(i,10).compare(QLatin1String("ERROR.TYPE")) == 0) {
464 // replace it
465 int outPos = out - outStart;
466 result.replace(outPos, 9, errorTypeReplacement);
467 data += 10; // number of characters in "ERROR.TYPE"
468 out += 9;
469 }
470 else if (expression.midRef(i, 12).compare(QLatin1String("LEGACY.NORMS")) == 0) {
471 if (expression.midRef(i + 12, 4).compare(QLatin1String("DIST")) == 0) {
472 // replace it
473 int outPos = out - outStart;
474 result.replace(outPos, 15, legacyNormsdistReplacement);
475 data += 16; // number of characters in "LEGACY.NORMSDIST"
476 out += 15;
477 }
478 else if (expression.midRef(i + 12, 3).compare(QLatin1String("INV")) == 0) {
479 // replace it
480 int outPos = out - outStart;
481 result.replace(outPos, 14, legacyNormsinvReplacement);
482 data += 15; // number of characters in "LEGACY.NORMSINV"
483 out += 14;
484 }
485 }
486 else if (namespacePrefix == "oooc:" && expression.midRef(i, 5).compare(QLatin1String("TABLE")) == 0 && !isIdentifier(expression[i+5])) {
487 int outPos = out - outStart;
488 result.replace(outPos, 19, multipleOperations);
489 data += 5;
490 out += 19;
491 }
492 else if (expression.midRef(i, 3).compare(QLatin1String("NEG")) == 0) {
493 *out = QChar('-', 0);
494 data += 3;
495 ++out;
496 }
497 }
498 else {
499 switch (data->unicode()) {
500 case '"': // a string ?
501 state = InString;
502 *out++ = *data++;
503 break;
504 case '[': // [ marks sheet name for 3-d cell, e.g ['Sales Q3'.A4]
505 state = InReference;
506 ++data;
507 // NOTE: As long as Calligra::Sheets does not support fixed sheets eat the dollar sign.
508 if (*data == QChar('$', 0)) {
509 ++data;
510 }
511 pos = data;
512 break;
513 default:
514 const QChar *operatorStart = data;
515 if (!parseOperator(data, out)) {
516 *out++ = *data++;
517 }
518 else if (*operatorStart == QChar('=', 0) && data - operatorStart == 1) { // only one =
519 *out++ = QChar('=', 0);
520 }
521 break;
522 }
523 }
524 } break;
525 case InNumber:
526 if (data->isDigit()) {
527 *out++ = *data++;
528 }
529 else if (*data == QChar('.', 0)) {
530 const QChar *decimalChar = decimal.constData();
531 while (!decimalChar->isNull()) {
532 *out++ = *decimalChar++;
533 }
534 ++data;
535 }
536 else if (*data == QChar('E', 0) || *data == QChar('e', 0)) {
537 *out++ = QChar('E', 0);
538 ++data;
539 }
540 else {
541 state = Start;
542 }
543
544 break;
545 case InString:
546 if (*data == QChar('"', 0)) {
547 state = Start;
548 }
549 *out++ = *data++;
550 break;
551 case InIdentifier: {
552 if (isIdentifier(*data) || data->isDigit()) {
553 *out++ = *data++;
554 }
555 else {
556 state = Start;
557 }
558 } break;
559 case InReference:
560 switch (data->unicode()) {
561 case ']':
562 Region::loadOdf(pos, data, out);
563 pos = data;
564 state = Start;
565 break;
566 case '\'':
567 state = InSheetName;
568 break;
569 default:
570 break;
571 }
572 ++data;
573 break;
574 case InSheetName:
575 if (*data == QChar('\'', 0)) {
576 ++data;
577 if (!data->isNull() && *data == QChar('\'', 0)) {
578 ++data;
579 }
580 else {
581 state = InReference;
582 }
583 }
584 else {
585 ++data;
586 }
587 break;
588 }
589 }
590 result.resize(out - outStart);
591 return result;
592}
593
594QString Calligra::Sheets::Odf::encodeFormula(const QString& expr, const KLocale* locale)
595{
596 // use locale settings
597 const QString decimal = locale ? locale->decimalSymbol() : ".";
598
599 QString result('=');
600
601 Formula formula;
602 Tokens tokens = formula.scan(expr, locale);
603
604 if (!tokens.valid() || tokens.count() == 0)
605 return expr; // no altering on error
606
607 for (int i = 0; i < tokens.count(); ++i) {
608 const QString tokenText = tokens[i].text();
609 const Token::Type type = tokens[i].type();
610
611 switch (type) {
612 case Token::Cell:
613 case Token::Range: {
614 result.append('[');
615 // FIXME Stefan: Hack to get the apostrophes right. Fix and remove!
616 const int pos = tokenText.lastIndexOf('!');
617 if (pos != -1 && tokenText.left(pos).contains(' '))
618 result.append(Region::saveOdf('\'' + tokenText.left(pos) + '\'' + tokenText.mid(pos)));
619 else
620 result.append(Region::saveOdf(tokenText));
621 result.append(']');
622 break;
623 }
624 case Token::Float: {
625 QString tmp(tokenText);
626 result.append(tmp.replace(decimal, "."));
627 break;
628 }
629 case Token::Operator: {
630 if (tokens[i].asOperator() == Token::Equal)
631 result.append('=');
632 else
633 result.append(tokenText);
634 break;
635 }
636 case Token::Identifier: {
637 if (tokenText == "ERRORTYPE") {
638 // need to replace this
639 result.append("ERROR.TYPE");
640 } else if (tokenText == "LEGACYNORMSDIST") {
641 result.append("LEGACY.NORMSDIST");
642 } else if (tokenText == "LEGACYNORMSINV") {
643 result.append("LEGACY.NORMSINV");
644 } else {
645 // dump it out unchanged
646 result.append(tokenText);
647 }
648 break;
649
650 }
651 case Token::Boolean:
652 case Token::Integer:
653 case Token::String:
654 default:
655 result.append(tokenText);
656 break;
657 }
658 }
659 return result;
660}
661
662static bool isCellnameCharacter(const QChar &c)
663{
664 return c.isDigit() || c.isLetter() || c == '$';
665}
666
667static void replaceFormulaReference(int referencedRow, int referencedColumn, int thisRow, int thisColumn, QString &result, int cellReferenceStart, int cellReferenceLength)
668{
669 const QString ref = result.mid(cellReferenceStart, cellReferenceLength);
670 QRegExp rx("(|\\$)[A-Za-z]+(|\\$)[0-9]+");
671 if (rx.exactMatch(ref)) {
672 int c = Calligra::Sheets::Util::decodeColumnLabelText(ref);
673 int r = Calligra::Sheets::Util::decodeRowLabelText(ref);
674 if (rx.cap(1) != "$") // absolute or relative column?
675 c += thisColumn - referencedColumn;
676 if (rx.cap(2) != "$") // absolute or relative row?
677 r += thisRow - referencedRow;
678 result.replace(cellReferenceStart,
679 cellReferenceLength,
680 rx.cap(1) + Calligra::Sheets::Util::encodeColumnLabelText(c) +
681 rx.cap(2) + QString::number(r) );
682 }
683}
684
685QString Calligra::Sheets::Util::adjustFormulaReference(const QString& formula, int referencedRow, int referencedColumn, int thisRow, int thisColumn)
686{
687 QString result = formula;
688 if (result.isEmpty())
689 return QString();
690 enum { InStart, InCellReference, InString, InSheetOrAreaName } state;
691 state = InStart;
692 int cellReferenceStart = 0;
693 for(int i = 1; i < result.length(); ++i) {
694 QChar ch = result[i];
695 switch (state) {
696 case InStart:
697 if (ch == '"')
698 state = InString;
699 else if (ch.unicode() == '\'')
700 state = InSheetOrAreaName;
701 else if (isCellnameCharacter(ch)) {
702 state = InCellReference;
703 cellReferenceStart = i;
704 }
705 break;
706 case InString:
707 if (ch == '"')
708 state = InStart;
709 break;
710 case InSheetOrAreaName:
711 if (ch == '\'')
712 state = InStart;
713 break;
714 case InCellReference:
715 if (!isCellnameCharacter(ch)) {
716 // We need to update cell-references according to the position of the referenced cell and this
717 // cell. This means that if the referenced cell is for example at C5 and contains the formula
718 // "=SUM(K22)" and if thisCell is at E6 then thisCell will get the formula "=SUM(L23)".
719 if (ch != '(') /* skip formula-names */ {
720 replaceFormulaReference(referencedRow, referencedColumn, thisRow, thisColumn, result, cellReferenceStart, i - cellReferenceStart);
721 }
722 state = InStart;
723 --i; // decrement again to handle the current char in the InStart-switch.
724 }
725 break;
726 };
727 }
728 if(state == InCellReference) {
729 replaceFormulaReference(referencedRow, referencedColumn, thisRow, thisColumn, result, cellReferenceStart, result.length() - cellReferenceStart);
730 }
731 return result;
732}
733
734QString Calligra::Sheets::MSOOXML::convertFormula(const QString& formula)
735{
736 if (formula.isEmpty())
737 return QString();
738 enum { InStart, InArguments, InParenthesizedArgument, InString, InSheetOrAreaName, InCellReference } state;
739 state = InStart;
740 int cellReferenceStart = 0;
741 int sheetOrAreaNameDelimiterCount = 0;
742 QString result = formula.startsWith('=') ? formula : '=' + formula;
743 for(int i = 1; i < result.length(); ++i) {
744 QChar ch = result[i];
745 switch (state) {
746 case InStart:
747 if(ch == '(')
748 state = InArguments;
749 break;
750 case InArguments:
751 if (ch == '"')
752 state = InString;
753 else if (ch.unicode() == '\'') {
754 sheetOrAreaNameDelimiterCount = 1;
755 for(int j = i + 1; j < result.length(); ++j) {
756 if (result[j].unicode() != '\'')
757 break;
758 ++sheetOrAreaNameDelimiterCount;
759 }
760 if (sheetOrAreaNameDelimiterCount >= 2)
761 result.remove(i + 1, sheetOrAreaNameDelimiterCount - 1);
762 state = InSheetOrAreaName;
763 } else if (isCellnameCharacter(ch)) {
764 state = InCellReference;
765 cellReferenceStart = i;
766 } else if (ch == ',')
767 result[i] = ';'; // replace argument delimiter
768 else if (ch == '(' && !result[i-1].isLetterOrNumber())
769 state = InParenthesizedArgument;
770 else if (ch == ' ') {
771 // check if it might be an intersection operator
772 // for it to be an intersection operator the next non-space char must be a cell-name-character or '
773 // and previous converted char cannot be ';'
774 int firstNonSpace = i+1;
775 while (firstNonSpace < result.length() && result[firstNonSpace] == ' ') {
776 firstNonSpace++;
777 }
778 bool wasDelimeter = (i-1 > 0) && (result[i-1] == ';');
779 bool isIntersection = !wasDelimeter && firstNonSpace < result.length() && (result[firstNonSpace].isLetter() || result[firstNonSpace] == '$' || result[firstNonSpace] == '\'');
780 if (isIntersection) {
781 result[i] = '!';
782 i = firstNonSpace-1;
783 }
784 }
785 break;
786 case InParenthesizedArgument:
787 if (ch == ',')
788 result[i] = '~'; // union operator
789 else if (ch == ' ')
790 result[i] = '!'; // intersection operator
791 else if (ch == ')')
792 state = InArguments;
793 break;
794 case InString:
795 if (ch == '"')
796 state = InArguments;
797 break;
798 case InSheetOrAreaName:
799 Q_ASSERT( i >= 1 );
800 if (ch == '\'' && result[i - 1].unicode() != '\\') {
801 int count = 1;
802 for(int j = i + 1; count < sheetOrAreaNameDelimiterCount && j < result.length(); ++j) {
803 if (result[j].unicode() != '\'')
804 break;
805 ++count;
806 }
807 if (count == sheetOrAreaNameDelimiterCount) {
808 if (sheetOrAreaNameDelimiterCount >= 2)
809 result.remove(i + 1, sheetOrAreaNameDelimiterCount - 1);
810 state = InArguments;
811 } else {
812 result.insert(i, '\'');
813 ++i;
814 }
815 }
816 break;
817 case InCellReference:
818 if (!isCellnameCharacter(ch)) {
819 if (ch != '(') /* skip formula-names */ {
820 // Excel is able to use only the column-name to define a column
821 // where all rows are selected. Since that is not supproted in
822 // ODF we add to such definitions the minimum/maximum row-number.
823 // So, something like "A:B" would become "A$1:B$65536". Note that
824 // such whole column-definitions are only allowed for ranges like
825 // "A:B" but not for single column definitions like "A" or "B".
826 const QString ref = result.mid(qMax(0, cellReferenceStart - 1), i - cellReferenceStart + 2);
827 QRegExp rxStart(".*(|\\$)[A-Za-z]+\\:");
828 QRegExp rxEnd("\\:(|\\$)[A-Za-z]+(|(|\\$)[0-9]+).*");
829 if (rxEnd.exactMatch(ref) && rxEnd.cap(2).isEmpty()) {
830 result.insert(i, "$65536");
831 i += 6;
832 } else if (rxStart.exactMatch(ref)) {
833 result.insert(i, "$1");
834 i += 2;
835 }
836 }
837 state = InArguments;
838 --i; // decrement again to handle the current char in the InArguments-switch.
839 }
840 break;
841 };
842 };
843 return result;
844}
845