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 | |
40 | using namespace Calligra::Sheets; |
41 | |
42 | |
43 | //used in Cell::encodeFormula and |
44 | // dialogs/kspread_dlg_paperlayout.cc |
45 | int 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 | |
74 | int 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 | |
82 | QString Calligra::Sheets::Util::encodeColumnLabelText(int column) |
83 | { |
84 | return Cell::columnName(column); |
85 | } |
86 | |
87 | bool 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 | |
137 | QDomElement 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 | |
157 | QDomElement 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 | |
166 | QFont 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 | |
204 | QPen 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 | |
222 | bool 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 | |
234 | bool 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 |
246 | int 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 | |
279 | QString 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 | |
293 | QString 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 |
317 | QString 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 | |
322 | QString 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 | |
357 | QPen 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. |
397 | bool 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 | |
408 | QString 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 | |
594 | QString 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 | |
662 | static bool isCellnameCharacter(const QChar &c) |
663 | { |
664 | return c.isDigit() || c.isLetter() || c == '$'; |
665 | } |
666 | |
667 | static 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 | |
685 | QString 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 | |
734 | QString 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 | |