42
42
//used in Cell::encodeFormula and
43
43
// dialogs/kspread_dlg_paperlayout.cc
44
int KSpread::Util::decodeColumnLabelText( const QString &_col )
44
int KSpread::Util::decodeColumnLabelText(const QString &labelText)
47
const int offset = 'a' -'A';
48
48
int counterColumn = 0;
49
for ( int i=0; i < _col.length(); i++ )
51
counterColumn = (int) ::pow(26.0 , static_cast<int>(_col.length() - i - 1));
52
if( _col[i] >= 'A' && _col[i] <= 'Z' )
53
col += counterColumn * ( _col[i].toLatin1() - 'A' + 1); // okay here (Werner)
54
else if( _col[i] >= 'a' && _col[i] <= 'z' )
55
col += counterColumn * ( _col[i].toLatin1() - 'A' - offset + 1 );
49
const uint totalLength = labelText.length();
51
for (labelTextLength = 0; labelTextLength < totalLength; labelTextLength++) {
52
const char c = labelText[labelTextLength].toLatin1();
53
if (!((c >= 'A' && c <= 'Z') || (c >= 'a' && c <= 'z')))
56
if (labelTextLength == 0) {
57
kWarning(36001) << "No column label text found for col:" << labelText;
60
for (uint i = 0; i < labelTextLength; i++) {
61
const char c = labelText[i].toLatin1();
62
counterColumn = (int) ::pow(26.0 , static_cast<int>(labelTextLength - i - 1));
63
if (c >= 'A' && c <= 'Z')
64
col += counterColumn * (c - 'A' + 1); // okay here (Werner)
65
else if (c >= 'a' && c <= 'z')
66
col += counterColumn * (c - 'A' - offset + 1);
71
int KSpread::Util::decodeRowLabelText(const QString &labelText)
73
QRegExp rx("([A-Za-z]+)([0-9]+)");
74
if(rx.exactMatch(labelText))
75
return rx.cap(2).toInt();
79
QString KSpread::Util::encodeColumnLabelText(int column)
81
return Cell::columnName(column);
84
QDomElement KSpread::NativeFormat::createElement(const QString & tagName, const QFont & font, QDomDocument & doc)
86
QDomElement e(doc.createElement(tagName));
88
e.setAttribute("family", font.family());
89
e.setAttribute("size", font.pointSize());
90
e.setAttribute("weight", font.weight());
92
e.setAttribute("bold", "yes");
94
e.setAttribute("italic", "yes");
96
e.setAttribute("underline", "yes");
98
e.setAttribute("strikeout", "yes");
99
//e.setAttribute( "charset", KGlobal::charsets()->name( font ) );
104
QDomElement KSpread::NativeFormat::createElement(const QString & tagname, const QPen & pen, QDomDocument & doc)
106
QDomElement e(doc.createElement(tagname));
107
e.setAttribute("color", pen.color().name());
108
e.setAttribute("style", (int)pen.style());
109
e.setAttribute("width", (int)pen.width());
113
QFont KSpread::NativeFormat::toFont(KoXmlElement & element)
116
int size = element.attribute("size").toInt(&ok);
121
f.setFamily(element.attribute("family"));
123
f.setPointSize(size);
125
f.setWeight(element.attribute("weight").toInt(&ok));
129
if (element.hasAttribute("italic") && element.attribute("italic") == "yes")
132
if (element.hasAttribute("bold") && element.attribute("bold") == "yes")
135
if (element.hasAttribute("underline") && element.attribute("underline") == "yes")
136
f.setUnderline(true);
138
if (element.hasAttribute("strikeout") && element.attribute("strikeout") == "yes")
139
f.setStrikeOut(true);
141
/* Uncomment when charset is added to kspread_dlg_layout
142
+ save a document-global charset
143
if ( element.hasAttribute( "charset" ) )
144
KGlobal::charsets()->setQFont( f, element.attribute("charset") );
57
kDebug(36001) <<"Util::decodeColumnLabelText: Wrong characters in label text for col:'" << _col << '\'';
62
QDomElement KSpread::NativeFormat::createElement( const QString & tagName, const QFont & font, QDomDocument & doc )
64
QDomElement e( doc.createElement( tagName ) );
66
e.setAttribute( "family", font.family() );
67
e.setAttribute( "size", font.pointSize() );
68
e.setAttribute( "weight", font.weight() );
70
e.setAttribute( "bold", "yes" );
72
e.setAttribute( "italic", "yes" );
73
if ( font.underline() )
74
e.setAttribute( "underline", "yes" );
75
if ( font.strikeOut() )
76
e.setAttribute( "strikeout", "yes" );
77
//e.setAttribute( "charset", KGlobal::charsets()->name( font ) );
82
QDomElement KSpread::NativeFormat::createElement( const QString & tagname, const QPen & pen, QDomDocument & doc )
84
QDomElement e( doc.createElement( tagname ) );
85
e.setAttribute( "color", pen.color().name() );
86
e.setAttribute( "style", (int)pen.style() );
87
e.setAttribute( "width", (int)pen.width() );
91
QFont KSpread::NativeFormat::toFont( KoXmlElement & element )
94
int size = element.attribute("size").toInt( &ok );
99
f.setFamily( element.attribute( "family" ) );
101
f.setPointSize( size );
103
f.setWeight( element.attribute("weight").toInt( &ok ) );
107
if ( element.hasAttribute( "italic" ) && element.attribute("italic") == "yes" )
110
if ( element.hasAttribute( "bold" ) && element.attribute("bold") == "yes" )
113
if ( element.hasAttribute( "underline" ) && element.attribute("underline") == "yes" )
114
f.setUnderline( true );
116
if ( element.hasAttribute( "strikeout" ) && element.attribute("strikeout") == "yes" )
117
f.setStrikeOut( true );
119
/* Uncomment when charset is added to kspread_dlg_layout
120
+ save a document-global charset
121
if ( element.hasAttribute( "charset" ) )
122
KGlobal::charsets()->setQFont( f, element.attribute("charset") );
125
// ######## Not needed anymore in 3.0?
126
//KGlobal::charsets()->setQFont( f, KGlobal::locale()->charset() );
131
QPen KSpread::NativeFormat::toPen( KoXmlElement & element )
136
p.setStyle( (Qt::PenStyle)element.attribute("style").toInt( &ok ) );
140
p.setWidth( element.attribute("width").toInt( &ok ) );
144
p.setColor( QColor( element.attribute("color") ) );
149
bool util_isPointValid( const QPoint& point )
153
&& point.x() <= KS_colMax
154
&& point.y() <= KS_rowMax
147
// ######## Not needed anymore in 3.0?
148
//KGlobal::charsets()->setQFont( f, KGlobal::locale()->charset() );
153
QPen KSpread::NativeFormat::toPen(KoXmlElement & element)
158
p.setStyle((Qt::PenStyle)element.attribute("style").toInt(&ok));
162
p.setWidth(element.attribute("width").toInt(&ok));
166
p.setColor(QColor(element.attribute("color")));
171
bool util_isPointValid(const QPoint& point)
175
&& point.x() <= KS_colMax
176
&& point.y() <= KS_rowMax
161
bool util_isRectValid( const QRect& rect )
183
bool util_isRectValid(const QRect& rect)
163
if ( util_isPointValid( rect.topLeft() )
164
&& util_isPointValid( rect.bottomRight() )
185
if (util_isPointValid(rect.topLeft())
186
&& util_isPointValid(rect.bottomRight())
172
194
//not used anywhere
173
int KSpread::Util::penCompare( QPen const & pen1, QPen const & pen2 )
195
int KSpread::Util::penCompare(QPen const & pen1, QPen const & pen2)
175
if ( pen1.style() == Qt::NoPen && pen2.style() == Qt::NoPen )
197
if (pen1.style() == Qt::NoPen && pen2.style() == Qt::NoPen)
200
if (pen1.style() == Qt::NoPen)
203
if (pen2.style() == Qt::NoPen)
206
if (pen1.width() < pen2.width())
209
if (pen1.width() > pen2.width())
212
if (pen1.style() < pen2.style())
215
if (pen1.style() > pen2.style())
218
if (pen1.color().name() < pen2.color().name())
221
if (pen1.color().name() > pen2.color().name())
178
if ( pen1.style() == Qt::NoPen )
181
if ( pen2.style() == Qt::NoPen )
184
if ( pen1.width() < pen2.width() )
187
if ( pen1.width() > pen2.width() )
190
if ( pen1.style() < pen2.style() )
193
if ( pen1.style() > pen2.style() )
196
if ( pen1.color().name() < pen2.color().name() )
199
if ( pen1.color().name() > pen2.color().name() )
206
QString KSpread::Odf::convertRefToBase( const QString & sheet, const QRect & rect )
208
QPoint bottomRight( rect.bottomRight() );
213
s += Cell::columnName( bottomRight.x() );
215
s += QString::number( bottomRight.y() );
220
QString KSpread::Odf::convertRefToRange( const QString & sheet, const QRect & rect )
222
QPoint topLeft( rect.topLeft() );
223
QPoint bottomRight( rect.bottomRight() );
225
if ( topLeft == bottomRight )
226
return Odf::convertRefToBase( sheet, rect );
231
s += /*Util::encodeColumnLabelText*/Cell::columnName( topLeft.x() );
233
s += QString::number( topLeft.y() );
235
s += /*Util::encodeColumnLabelText*/Cell::columnName( bottomRight.x() );
237
s += QString::number( bottomRight.y() );
242
// e.g.: Sheet4.A1:Sheet4.E28
243
//used in Sheet::saveOdf
244
QString KSpread::Odf::convertRangeToRef( const QString & sheetName, const QRect & _area )
246
return sheetName + '.' + Cell::name( _area.left(), _area.top() ) + ':' + sheetName + '.'+ Cell::name( _area.right(), _area.bottom() );
249
QString KSpread::Odf::encodePen( const QPen & pen )
228
QString KSpread::Odf::convertRefToBase(const QString & sheet, const QRect & rect)
230
QPoint bottomRight(rect.bottomRight());
235
s += Cell::columnName(bottomRight.x());
237
s += QString::number(bottomRight.y());
242
QString KSpread::Odf::convertRefToRange(const QString & sheet, const QRect & rect)
244
QPoint topLeft(rect.topLeft());
245
QPoint bottomRight(rect.bottomRight());
247
if (topLeft == bottomRight)
248
return Odf::convertRefToBase(sheet, rect);
253
s += /*Util::encodeColumnLabelText*/Cell::columnName(topLeft.x());
255
s += QString::number(topLeft.y());
257
s += /*Util::encodeColumnLabelText*/Cell::columnName(bottomRight.x());
259
s += QString::number(bottomRight.y());
264
// e.g.: Sheet4.A1:Sheet4.E28
265
//used in Sheet::saveOdf
266
QString KSpread::Odf::convertRangeToRef(const QString & sheetName, const QRect & _area)
268
return sheetName + '.' + Cell::name(_area.left(), _area.top()) + ':' + sheetName + '.' + Cell::name(_area.right(), _area.bottom());
271
QString KSpread::Odf::encodePen(const QPen & pen)
251
273
// kDebug()<<"encodePen( const QPen & pen ) :"<<pen;
252
274
// NOTE Stefan: QPen api docs:
253
275
// A line width of zero indicates a cosmetic pen. This means
254
276
// that the pen width is always drawn one pixel wide,
255
277
// independent of the transformation set on the painter.
256
QString s = QString( "%1pt " ).arg( (pen.width() == 0) ? 1 : pen.width() );
257
switch( pen.style() )
278
QString s = QString("%1pt ").arg((pen.width() == 0) ? 1 : pen.width());
279
switch (pen.style()) {
261
282
case Qt::SolidLine:
264
285
case Qt::DashLine:
267
288
case Qt::DotLine:
270
291
case Qt::DashDotLine:
273
294
case Qt::DashDotDotLine:
278
kDebug()<<" encodePen :"<<s;
279
if ( pen.color().isValid() )
282
s+=Style::colorName(pen.color());
299
kDebug() << " encodePen :" << s;
300
if (pen.color().isValid()) {
302
s += Style::colorName(pen.color());
287
QPen KSpread::Odf::decodePen( const QString &border )
307
QPen KSpread::Odf::decodePen(const QString &border)
290
310
//string like "0.088cm solid #800000"
291
if (border.isEmpty() || border=="none" || border=="hidden") // in fact no border
293
pen.setStyle( Qt::NoPen );
311
if (border.isEmpty() || border == "none" || border == "hidden") { // in fact no border
312
pen.setStyle(Qt::NoPen);
296
315
//code from koborder, for the moment kspread doesn't use koborder
299
318
QByteArray _style = border.section(' ', 1, 1).toLatin1();
300
319
QString _color = border.section(' ', 2, 2);
302
pen.setWidth( ( int )( KoUnit::parseValue( _width, 1.0 ) ) );
304
if ( _style =="none" )
305
pen.setStyle( Qt::NoPen );
306
else if ( _style =="solid" )
307
pen.setStyle( Qt::SolidLine );
308
else if ( _style =="dashed" )
309
pen.setStyle( Qt::DashLine );
310
else if ( _style =="dotted" )
311
pen.setStyle( Qt::DotLine );
312
else if ( _style =="dot-dash" )
313
pen.setStyle( Qt::DashDotLine );
314
else if ( _style =="dot-dot-dash" )
315
pen.setStyle( Qt::DashDotDotLine );
317
kDebug()<<" style undefined :"<<_style;
319
if ( _color.isEmpty() )
320
pen.setColor( QColor() );
322
pen.setColor( QColor( _color ) );
321
pen.setWidth((int)(KoUnit::parseValue(_width, 1.0)));
323
if (_style == "none")
324
pen.setStyle(Qt::NoPen);
325
else if (_style == "solid")
326
pen.setStyle(Qt::SolidLine);
327
else if (_style == "dashed")
328
pen.setStyle(Qt::DashLine);
329
else if (_style == "dotted")
330
pen.setStyle(Qt::DotLine);
331
else if (_style == "dot-dash")
332
pen.setStyle(Qt::DashDotLine);
333
else if (_style == "dot-dot-dash")
334
pen.setStyle(Qt::DashDotDotLine);
336
kDebug() << " style undefined :" << _style;
338
if (_color.isEmpty())
339
pen.setColor(QColor());
341
pen.setColor(QColor(_color));
327
346
//Return true when it's a reference to cell from sheet.
328
bool KSpread::Util::localReferenceAnchor( const QString &_ref )
347
bool KSpread::Util::localReferenceAnchor(const QString &_ref)
330
349
bool isLocalRef = (_ref.indexOf("http://") != 0 &&
331
350
_ref.indexOf("https://") != 0 &&
332
351
_ref.indexOf("mailto:") != 0 &&
333
352
_ref.indexOf("ftp://") != 0 &&
334
_ref.indexOf("file:") != 0 );
353
_ref.indexOf("file:") != 0);
335
354
return isLocalRef;
339
QString KSpread::Odf::decodeFormula(const QString& expression, const KLocale* locale)
358
QString KSpread::Odf::decodeFormula(const QString& expression, const KLocale* locale, QString namespacePrefix)
342
361
enum { Start, InNumber, InString, InIdentifier, InReference, InSheetName } state = Start;
348
367
QString reference;
351
if ((!expression.isEmpty()) && (expression[0] == '='))
370
if ((!expression.isEmpty()) && (expression[0] == '=')) {
358
while ( i < expression.length() )
365
if ( expression[i].isDigit() )
369
else if ( expression[i] == '"' )
372
result.append( expression[i++] );
376
while (i < expression.length()) {
380
if (expression[i].isDigit())
384
else if (expression[i] == '"') {
386
result.append(expression[i++]);
390
else if (expression[i] == '.')
393
// beginning with alphanumeric ?
394
// could be identifier, cell, range, or function...
395
else if (isIdentifier(expression[i]))
396
state = InIdentifier;
398
// [ marks sheet name for 3-d cell, e.g ['Sales Q3'.A4]
399
else if (expression[i].unicode() == '[') {
402
// NOTE Stefan: As long as KSpread does not support fixed sheets eat the dollar sign.
403
if (expression[i] == '$') ++i;
406
// look for operator match
411
// check for two-chars operator, such as '<=', '>=', etc
412
s.append(expression[i]);
413
if (i + 1 < expression.length())
414
s.append(expression[i+1]);
415
op = matchOperator(s);
417
// check for one-char operator, such as '+', ';', etc
418
if (op == Token::InvalidOp) {
419
s = QString(expression[i]);
420
op = matchOperator(s);
375
// beginning with alphanumeric ?
376
// could be identifier, cell, range, or function...
377
else if ( isIdentifier( expression[i] ) )
378
state = InIdentifier;
380
// [ marks sheet name for 3-d cell, e.g ['Sales Q3'.A4]
381
else if ( expression[i].unicode() == '[' )
423
// any matched operator ?
424
if (op == Token::Equal)
428
if (op != Token::InvalidOp) {
429
int len = s.length();
439
if (expression[i] == ']') {
440
result.append(Region::loadOdf(reference));
443
} else if (expression[i] == '\'') {
444
reference.append('\'');
447
reference.append(expression[i]);
452
reference.append(expression[i]);
453
if (expression[i] == '\'') {
454
// an escaped apostrophe?
455
if (i + 1 < expression.count() && expression[i+1] == '\'')
384
458
state = InReference;
385
// NOTE Stefan: As long as KSpread does not support fixed sheets eat the dollar sign.
386
if ( expression[i] == '$' ) ++i;
390
else if ( expression[i] == '.' )
393
// look for operator match
399
// check for two-chars operator, such as '<=', '>=', etc
400
s.append( expression[i] );
401
if (i+1 < expression.length())
402
s.append( expression[i+1] );
403
op = matchOperator( s );
405
// check for one-char operator, such as '+', ';', etc
406
if ( op == Token::InvalidOp )
408
s = QString( expression[i] );
409
op = matchOperator( s );
412
// any matched operator ?
413
if ( op == Token::Equal )
414
result.append( "==" );
417
if ( op != Token::InvalidOp )
419
int len = s.length();
432
if (expression[i] == ']')
434
result.append(Region::loadOdf(reference));
438
else if (expression[i] == '\'')
440
reference.append('\'');
444
reference.append(expression[i]);
450
reference.append(expression[i]);
451
if (expression[i] == '\'')
453
// an escaped apostrophe?
454
if (i+1 < expression.count() && expression[i+1] == '\'')
464
// consume as long as it's digit
465
if ( expression[i].isDigit() )
466
result.append( expression[i++] );
467
// convert '.' to decimal separator
468
else if ( expression[i] == '.' )
470
result.append( decimal );
474
else if ( expression[i].toUpper() == 'E' )
476
result.append( 'E' );
479
// we're done with integer number
487
if ( expression[i] != '"' )
488
result.append( expression[i++] );
492
result.append( expression[i] );
500
// handle problematic functions
501
if ( expression.mid(i ).startsWith( "ERROR.TYPE" ) ) {
503
result.append( "ERRORTYPE" );
504
i+=10; // number of characters in "ERROR.TYPE"
505
} else if ( expression.mid(i ).startsWith( "LEGACY.NORMSDIST" ) ) {
507
result.append( "LEGACYNORMSDIST" );
508
i+=16; // number of characters in "LEGACY.NORMSDIST"
509
} else if ( expression.mid(i ).startsWith( "LEGACY.NORMSINV" ) ) {
511
result.append( "LEGACYNORMSINV" );
512
i+=15; // number of characters in "LEGACY.NORMSINV"
516
// consume as long as alpha, dollar sign, underscore, or digit
517
if ( isIdentifier( expression[i] ) || expression[i].isDigit() )
518
result.append( expression[i++] );
464
// consume as long as it's digit
465
if (expression[i].isDigit())
466
result.append(expression[i++]);
467
// convert '.' to decimal separator
468
else if (expression[i] == '.') {
469
result.append(decimal);
473
else if (expression[i].toUpper() == 'E') {
477
// we're done with integer number
484
if (expression[i] != '"')
485
result.append(expression[i++]);
488
result.append(expression[i]);
495
// handle problematic functions
496
if (expression.mid(i).startsWith("ERROR.TYPE")) {
498
result.append("ERRORTYPE");
499
i += 10; // number of characters in "ERROR.TYPE"
500
} else if (expression.mid(i).startsWith("LEGACY.NORMSDIST")) {
502
result.append("LEGACYNORMSDIST");
503
i += 16; // number of characters in "LEGACY.NORMSDIST"
504
} else if (expression.mid(i).startsWith("LEGACY.NORMSINV")) {
506
result.append("LEGACYNORMSINV");
507
i += 15; // number of characters in "LEGACY.NORMSINV"
508
} else if (namespacePrefix == "oooc:" && expression.mid(i).startsWith("TABLE") && !isIdentifier(expression[i+5])) {
509
result.append("MULTIPLE.OPERATIONS");
514
// consume as long as alpha, dollar sign, underscore, or digit
515
if (isIdentifier(expression[i]) || expression[i].isDigit())
516
result.append(expression[i++]);
531
QString KSpread::Odf::encodeFormula( const QString& expr, const KLocale* locale )
529
QString KSpread::Odf::encodeFormula(const QString& expr, const KLocale* locale)
533
531
// use locale settings
534
532
const QString decimal = locale ? locale->decimalSymbol() : ".";
536
534
QString result('=');
539
Tokens tokens = formula.scan( expr, locale );
537
Tokens tokens = formula.scan(expr, locale);
541
if ( !tokens.valid() || tokens.count() == 0 )
539
if (!tokens.valid() || tokens.count() == 0)
542
540
return expr; // no altering on error
544
for ( int i = 0; i < tokens.count(); ++i )
542
for (int i = 0; i < tokens.count(); ++i) {
546
543
const QString tokenText = tokens[i].text();
547
544
const Token::Type type = tokens[i].type();
551
547
case Token::Cell:
554
result.append( '[' );
555
550
// FIXME Stefan: Hack to get the apostrophes right. Fix and remove!
556
551
const int pos = tokenText.lastIndexOf('!');
557
552
if (pos != -1 && tokenText.left(pos).contains(' '))
558
553
result.append(Region::saveOdf('\'' + tokenText.left(pos) + '\'' + tokenText.mid(pos)));
560
555
result.append(Region::saveOdf(tokenText));
561
result.append( ']' );
566
QString tmp( tokenText );
567
result.append( tmp.replace( decimal, "." ) );
570
case Token::Operator:
572
if ( tokens[i].asOperator() == Token::Equal )
573
result.append( '=' );
560
QString tmp(tokenText);
561
result.append(tmp.replace(decimal, "."));
564
case Token::Operator: {
565
if (tokens[i].asOperator() == Token::Equal)
575
result.append( tokenText );
568
result.append(tokenText);
578
case Token::Identifier:
580
if ( tokenText == "ERRORTYPE" ) {
571
case Token::Identifier: {
572
if (tokenText == "ERRORTYPE") {
581
573
// need to replace this
582
result.append( "ERROR.TYPE" );
583
} else if ( tokenText == "LEGACYNORMSDIST" ) {
584
result.append( "LEGACY.NORMSDIST" );
585
} else if ( tokenText == "LEGACYNORMSINV" ) {
586
result.append( "LEGACY.NORMSINV" );
574
result.append("ERROR.TYPE");
575
} else if (tokenText == "LEGACYNORMSDIST") {
576
result.append("LEGACY.NORMSDIST");
577
} else if (tokenText == "LEGACYNORMSINV") {
578
result.append("LEGACY.NORMSINV");
588
580
// dump it out unchanged
589
result.append( tokenText );
581
result.append(tokenText);