1 // xlsxworksheet.cpp
2
3 #include <QtGlobal>
4 #include <QVariant>
5 #include <QDateTime>
6 #include <QDate>
7 #include <QTime>
8 #include <QPoint>
9 #include <QFile>
10 #include <QUrl>
11 #include <QDebug>
12 #include <QBuffer>
13 #include <QXmlStreamWriter>
14 #include <QXmlStreamReader>
15 #include <QTextDocument>
16 #include <QDir>
17 #include <QMapIterator>
18 #include <QMap>
19
20 #include <cmath>
21
22 #include "xlsxrichstring.h"
23 #include "xlsxcellreference.h"
24 #include "xlsxworksheet.h"
25 #include "xlsxworksheet_p.h"
26 #include "xlsxworkbook.h"
27 #include "xlsxformat.h"
28 #include "xlsxformat_p.h"
29 #include "xlsxutility_p.h"
30 #include "xlsxsharedstrings_p.h"
31 #include "xlsxdrawing_p.h"
32 #include "xlsxstyles_p.h"
33 #include "xlsxcell.h"
34 #include "xlsxcell_p.h"
35 #include "xlsxcellrange.h"
36 #include "xlsxconditionalformatting_p.h"
37 #include "xlsxdrawinganchor_p.h"
38 #include "xlsxchart.h"
39 #include "xlsxcellformula.h"
40 #include "xlsxcellformula_p.h"
41 #include "xlsxcelllocation.h"
42
43 QT_BEGIN_NAMESPACE_XLSX
44
WorksheetPrivate(Worksheet * p,Worksheet::CreateFlag flag)45 WorksheetPrivate::WorksheetPrivate(Worksheet *p, Worksheet::CreateFlag flag)
46 : AbstractSheetPrivate(p, flag),
47 windowProtection(false),
48 showFormulas(false),
49 showGridLines(true),
50 showRowColHeaders(true),
51 showZeros(true),
52 rightToLeft(false),
53 tabSelected(false),
54 showRuler(false),
55 showOutlineSymbols(true),
56 showWhiteSpace(true),
57 urlPattern(QStringLiteral("^([fh]tt?ps?://)|(mailto:)|(file://)"))
58 {
59 previous_row = 0;
60
61 outline_row_level = 0;
62 outline_col_level = 0;
63
64 default_row_height = 15;
65 default_row_zeroed = false;
66 }
67
~WorksheetPrivate()68 WorksheetPrivate::~WorksheetPrivate()
69 {
70 }
71
72 /*
73 Calculate the "spans" attribute of the <row> tag. This is an
74 XLSX optimisation and isn't strictly required. However, it
75 makes comparing files easier. The span is the same for each
76 block of 16 rows.
77 */
calculateSpans() const78 void WorksheetPrivate::calculateSpans() const
79 {
80 row_spans.clear();
81 int span_min = XLSX_COLUMN_MAX+1;
82 int span_max = -1;
83
84 for (int row_num = dimension.firstRow(); row_num <= dimension.lastRow(); row_num++) {
85 auto it = cellTable.constFind(row_num);
86 if (it != cellTable.constEnd()) {
87 for (int col_num = dimension.firstColumn(); col_num <= dimension.lastColumn(); col_num++) {
88 if (it->contains(col_num)) {
89 if (span_max == -1) {
90 span_min = col_num;
91 span_max = col_num;
92 } else {
93 if (col_num < span_min)
94 span_min = col_num;
95 else if (col_num > span_max)
96 span_max = col_num;
97 }
98 }
99 }
100 }
101 auto cIt = comments.constFind(row_num);
102 if (cIt != comments.constEnd()) {
103 for (int col_num = dimension.firstColumn(); col_num <= dimension.lastColumn(); col_num++) {
104 if (cIt->contains(col_num)) {
105 if (span_max == -1) {
106 span_min = col_num;
107 span_max = col_num;
108 } else {
109 if (col_num < span_min)
110 span_min = col_num;
111 else if (col_num > span_max)
112 span_max = col_num;
113 }
114 }
115 }
116 }
117
118 if (row_num%16 == 0 || row_num == dimension.lastRow()) {
119 if (span_max != -1) {
120 row_spans[row_num / 16] = QStringLiteral("%1:%2").arg(span_min).arg(span_max);
121 span_min = XLSX_COLUMN_MAX+1;
122 span_max = -1;
123 }
124 }
125 }
126 }
127
128
generateDimensionString() const129 QString WorksheetPrivate::generateDimensionString() const
130 {
131 if (!dimension.isValid())
132 return QStringLiteral("A1");
133 else
134 return dimension.toString();
135 }
136
137 /*
138 Check that row and col are valid and store the max and min
139 values for use in other methods/elements. The ignore_row /
140 ignore_col flags is used to indicate that we wish to perform
141 the dimension check without storing the value. The ignore
142 flags are use by setRow() and dataValidate.
143 */
checkDimensions(int row,int col,bool ignore_row,bool ignore_col)144 int WorksheetPrivate::checkDimensions(int row, int col, bool ignore_row, bool ignore_col)
145 {
146 Q_ASSERT_X(row!=0, "checkDimensions", "row should start from 1 instead of 0");
147 Q_ASSERT_X(col!=0, "checkDimensions", "column should start from 1 instead of 0");
148
149 if (row > XLSX_ROW_MAX || row < 1 || col > XLSX_COLUMN_MAX || col < 1)
150 return -1;
151
152 if (!ignore_row) {
153 if (row < dimension.firstRow() || dimension.firstRow() == -1) dimension.setFirstRow(row);
154 if (row > dimension.lastRow()) dimension.setLastRow(row);
155 }
156 if (!ignore_col) {
157 if (col < dimension.firstColumn() || dimension.firstColumn() == -1) dimension.setFirstColumn(col);
158 if (col > dimension.lastColumn()) dimension.setLastColumn(col);
159 }
160
161 return 0;
162 }
163
164 /*!
165 \class Worksheet
166 \inmodule QtXlsx
167 \brief Represent one worksheet in the workbook.
168 */
169
170 /*!
171 * \internal
172 */
Worksheet(const QString & name,int id,Workbook * workbook,CreateFlag flag)173 Worksheet::Worksheet(const QString &name, int id, Workbook *workbook, CreateFlag flag)
174 :AbstractSheet(name, id, workbook, new WorksheetPrivate(this, flag))
175 {
176 if (!workbook) //For unit test propose only. Ignore the memery leak.
177 d_func()->workbook = new Workbook(flag);
178 }
179
180 /*!
181 * \internal
182 *
183 * Make a copy of this sheet.
184 */
185
copy(const QString & distName,int distId) const186 Worksheet *Worksheet::copy(const QString &distName, int distId) const
187 {
188 Q_D(const Worksheet);
189 Worksheet *sheet = new Worksheet(distName, distId, d->workbook, F_NewFromScratch);
190 WorksheetPrivate *sheet_d = sheet->d_func();
191
192 sheet_d->dimension = d->dimension;
193
194 QMapIterator<int, QMap<int, QSharedPointer<Cell> > > it(d->cellTable);
195 while (it.hasNext())
196 {
197 it.next();
198 int row = it.key();
199 QMapIterator<int, QSharedPointer<Cell> > it2(it.value());
200 while (it2.hasNext())
201 {
202 it2.next();
203 int col = it2.key();
204
205 QSharedPointer<Cell> cell(new Cell(it2.value().data()));
206 cell->d_ptr->parent = sheet;
207
208 if (cell->cellType() == Cell::SharedStringType)
209 d->workbook->sharedStrings()->addSharedString(cell->d_ptr->richString);
210
211 sheet_d->cellTable[row][col] = cell;
212 }
213 }
214
215 sheet_d->merges = d->merges;
216 // sheet_d->rowsInfo = d->rowsInfo;
217 // sheet_d->colsInfo = d->colsInfo;
218 // sheet_d->colsInfoHelper = d->colsInfoHelper;
219 // sheet_d->dataValidationsList = d->dataValidationsList;
220 // sheet_d->conditionalFormattingList = d->conditionalFormattingList;
221
222 return sheet;
223 }
224
225 /*!
226 * Destroys this workssheet.
227 */
~Worksheet()228 Worksheet::~Worksheet()
229 {
230 }
231
232 /*!
233 * Returns whether sheet is protected.
234 */
isWindowProtected() const235 bool Worksheet::isWindowProtected() const
236 {
237 Q_D(const Worksheet);
238 return d->windowProtection;
239 }
240
241 /*!
242 * Protects/unprotects the sheet based on \a protect.
243 */
setWindowProtected(bool protect)244 void Worksheet::setWindowProtected(bool protect)
245 {
246 Q_D(Worksheet);
247 d->windowProtection = protect;
248 }
249
250 /*!
251 * Return whether formulas instead of their calculated results shown in cells
252 */
isFormulasVisible() const253 bool Worksheet::isFormulasVisible() const
254 {
255 Q_D(const Worksheet);
256 return d->showFormulas;
257 }
258
259 /*!
260 * Show formulas in cells instead of their calculated results when \a visible is true.
261 */
setFormulasVisible(bool visible)262 void Worksheet::setFormulasVisible(bool visible)
263 {
264 Q_D(Worksheet);
265 d->showFormulas = visible;
266 }
267
268 /*!
269 * Return whether gridlines is shown or not.
270 */
isGridLinesVisible() const271 bool Worksheet::isGridLinesVisible() const
272 {
273 Q_D(const Worksheet);
274 return d->showGridLines;
275 }
276
277 /*!
278 * Show or hide the gridline based on \a visible
279 */
setGridLinesVisible(bool visible)280 void Worksheet::setGridLinesVisible(bool visible)
281 {
282 Q_D(Worksheet);
283 d->showGridLines = visible;
284 }
285
286 /*!
287 * Return whether is row and column headers is vislbe.
288 */
isRowColumnHeadersVisible() const289 bool Worksheet::isRowColumnHeadersVisible() const
290 {
291 Q_D(const Worksheet);
292 return d->showRowColHeaders;
293 }
294
295 /*!
296 * Show or hide the row column headers based on \a visible
297 */
setRowColumnHeadersVisible(bool visible)298 void Worksheet::setRowColumnHeadersVisible(bool visible)
299 {
300 Q_D(Worksheet);
301 d->showRowColHeaders = visible;
302 }
303
304
305 /*!
306 * Return whether the sheet is shown right-to-left or not.
307 */
isRightToLeft() const308 bool Worksheet::isRightToLeft() const
309 {
310 Q_D(const Worksheet);
311 return d->rightToLeft;
312 }
313
314 /*!
315 * Enable or disable the right-to-left based on \a enable.
316 */
setRightToLeft(bool enable)317 void Worksheet::setRightToLeft(bool enable)
318 {
319 Q_D(Worksheet);
320 d->rightToLeft = enable;
321 }
322
323 /*!
324 * Return whether is cells that have zero value show a zero.
325 */
isZerosVisible() const326 bool Worksheet::isZerosVisible() const
327 {
328 Q_D(const Worksheet);
329 return d->showZeros;
330 }
331
332 /*!
333 * Show a zero in cells that have zero value if \a visible is true.
334 */
setZerosVisible(bool visible)335 void Worksheet::setZerosVisible(bool visible)
336 {
337 Q_D(Worksheet);
338 d->showZeros = visible;
339 }
340
341 /*!
342 * Return whether this tab is selected.
343 */
isSelected() const344 bool Worksheet::isSelected() const
345 {
346 Q_D(const Worksheet);
347 return d->tabSelected;
348 }
349
350 /*!
351 * Select this sheet if \a select is true.
352 */
setSelected(bool select)353 void Worksheet::setSelected(bool select)
354 {
355 Q_D(Worksheet);
356 d->tabSelected = select;
357 }
358
359 /*!
360 * Return whether is ruler is shown.
361 */
isRulerVisible() const362 bool Worksheet::isRulerVisible() const
363 {
364 Q_D(const Worksheet);
365 return d->showRuler;
366
367 }
368
369 /*!
370 * Show or hide the ruler based on \a visible.
371 */
setRulerVisible(bool visible)372 void Worksheet::setRulerVisible(bool visible)
373 {
374 Q_D(Worksheet);
375 d->showRuler = visible;
376
377 }
378
379 /*!
380 * Return whether is outline symbols is shown.
381 */
isOutlineSymbolsVisible() const382 bool Worksheet::isOutlineSymbolsVisible() const
383 {
384 Q_D(const Worksheet);
385 return d->showOutlineSymbols;
386 }
387
388 /*!
389 * Show or hide the outline symbols based ib \a visible.
390 */
setOutlineSymbolsVisible(bool visible)391 void Worksheet::setOutlineSymbolsVisible(bool visible)
392 {
393 Q_D(Worksheet);
394 d->showOutlineSymbols = visible;
395 }
396
397 /*!
398 * Return whether is white space is shown.
399 */
isWhiteSpaceVisible() const400 bool Worksheet::isWhiteSpaceVisible() const
401 {
402 Q_D(const Worksheet);
403 return d->showWhiteSpace;
404 }
405
406 /*!
407 * Show or hide the white space based on \a visible.
408 */
setWhiteSpaceVisible(bool visible)409 void Worksheet::setWhiteSpaceVisible(bool visible)
410 {
411 Q_D(Worksheet);
412 d->showWhiteSpace = visible;
413 }
414
415 /*!
416 * Write \a value to cell (\a row, \a column) with the \a format.
417 * Both \a row and \a column are all 1-indexed value.
418 *
419 * Returns true on success.
420 */
write(int row,int column,const QVariant & value,const Format & format)421 bool Worksheet::write(int row, int column, const QVariant &value, const Format &format)
422 {
423 Q_D(Worksheet);
424
425 if (d->checkDimensions(row, column))
426 return false;
427
428 bool ret = true;
429 if (value.isNull())
430 {
431 //Blank
432 ret = writeBlank(row, column, format);
433 }
434 else if (value.userType() == QMetaType::QString)
435 {
436 //String
437 QString token = value.toString();
438 bool ok;
439
440 if (token.startsWith(QLatin1String("=")))
441 {
442 //convert to formula
443 ret = writeFormula(row, column, CellFormula(token), format);
444 }
445 else if (d->workbook->isStringsToHyperlinksEnabled() && token.contains(d->urlPattern))
446 {
447 //convert to url
448 ret = writeHyperlink(row, column, QUrl(token));
449 }
450 else if (d->workbook->isStringsToNumbersEnabled() && (value.toDouble(&ok), ok))
451 {
452 //Try convert string to number if the flag enabled.
453 ret = writeString(row, column, value.toString(), format);
454 }
455 else
456 {
457 //normal string now
458 ret = writeString(row, column, token, format);
459 }
460 }
461 else if (value.userType() == qMetaTypeId<RichString>())
462 {
463 ret = writeString(row, column, value.value<RichString>(), format);
464 }
465 else if (value.userType() == QMetaType::Int || value.userType() == QMetaType::UInt
466 || value.userType() == QMetaType::LongLong || value.userType() == QMetaType::ULongLong
467 || value.userType() == QMetaType::Double || value.userType() == QMetaType::Float)
468 {
469 //Number
470
471 ret = writeNumeric(row, column, value.toDouble(), format);
472 }
473 else if (value.userType() == QMetaType::Bool)
474 {
475 //Bool
476 ret = writeBool(row,column, value.toBool(), format);
477 }
478 else if (value.userType() == QMetaType::QDateTime ) // dev67
479 {
480 //DateTime, Date
481 // note that, QTime cann't convert to QDateTime
482 ret = writeDateTime(row, column, value.toDateTime(), format);
483 }
484 else if ( value.userType() == QMetaType::QDate ) // dev67
485 {
486 ret = writeDate(row, column, value.toDate(), format);
487 }
488 else if (value.userType() == QMetaType::QTime)
489 {
490 //Time
491 ret = writeTime(row, column, value.toTime(), format);
492 }
493 else if (value.userType() == QMetaType::QUrl)
494 {
495 //Url
496 ret = writeHyperlink(row, column, value.toUrl(), format);
497 }
498 else
499 {
500 //Wrong type
501 return false;
502 }
503
504 return ret;
505 }
506
507 /*!
508 * \overload
509 * Write \a value to cell \a row_column with the \a format.
510 * Both row and column are all 1-indexed value.
511 * Returns true on success.
512 */
write(const CellReference & row_column,const QVariant & value,const Format & format)513 bool Worksheet::write(const CellReference &row_column, const QVariant &value, const Format &format)
514 {
515 if (!row_column.isValid())
516 return false;
517
518 return write(row_column.row(), row_column.column(), value, format);
519 }
520
521 /*!
522 \overload
523 Return the contents of the cell \a row_column.
524 */
read(const CellReference & row_column) const525 QVariant Worksheet::read(const CellReference &row_column) const
526 {
527 if (!row_column.isValid())
528 return QVariant();
529
530 return read(row_column.row(), row_column.column());
531 }
532
533 /*!
534 Return the contents of the cell (\a row, \a column).
535 */
read(int row,int column) const536 QVariant Worksheet::read(int row, int column) const
537 {
538 Q_D(const Worksheet);
539
540 Cell *cell = cellAt(row, column);
541 if (!cell)
542 return QVariant();
543
544 if (cell->hasFormula())
545 {
546 if (cell->formula().formulaType() == CellFormula::NormalType)
547 {
548 return QVariant(QLatin1String("=")+cell->formula().formulaText());
549 }
550 else if (cell->formula().formulaType() == CellFormula::SharedType)
551 {
552 if (!cell->formula().formulaText().isEmpty())
553 {
554 return QVariant(QLatin1String("=")+cell->formula().formulaText());
555 }
556 else
557 {
558 int si = cell->formula().sharedIndex();
559 const CellFormula &rootFormula = d->sharedFormulaMap[ si ];
560 CellReference rootCellRef = rootFormula.reference().topLeft();
561 QString rootFormulaText = rootFormula.formulaText();
562 QString newFormulaText = convertSharedFormula(rootFormulaText, rootCellRef, CellReference(row, column));
563 return QVariant(QLatin1String("=")+newFormulaText);
564 }
565 }
566 }
567
568 if (cell->isDateTime())
569 {
570 QVariant vDateTime = cell->dateTime();
571 return vDateTime;
572 }
573
574 return cell->value();
575 }
576
577 /*!
578 * Returns the cell at the given \a row_column. If there
579 * is no cell at the specified position, the function returns 0.
580 */
cellAt(const CellReference & row_column) const581 Cell *Worksheet::cellAt(const CellReference &row_column) const
582 {
583 if (!row_column.isValid())
584 return Q_NULLPTR;
585
586 return cellAt(row_column.row(), row_column.column());
587 }
588
589 /*!
590 * Returns the cell at the given \a row and \a column. If there
591 * is no cell at the specified position, the function returns 0.
592 */
cellAt(int row,int col) const593 Cell *Worksheet::cellAt(int row, int col) const
594 {
595 Q_D(const Worksheet);
596 auto it = d->cellTable.constFind(row);
597 if (it == d->cellTable.constEnd())
598 return Q_NULLPTR;
599 if (!it->contains(col))
600 return Q_NULLPTR;
601
602 return (*it)[col].data();
603 }
604
cellFormat(int row,int col) const605 Format WorksheetPrivate::cellFormat(int row, int col) const
606 {
607 auto it = cellTable.constFind(row);
608 if (it == cellTable.constEnd())
609 return Format();
610 if (!it->contains(col))
611 return Format();
612 return (*it)[col]->format();
613 }
614
615 /*!
616 \overload
617 Write string \a value to the cell \a row_column with the \a format.
618
619 Returns true on success.
620 */
writeString(const CellReference & row_column,const RichString & value,const Format & format)621 bool Worksheet::writeString(const CellReference &row_column, const RichString &value, const Format &format)
622 {
623 if (!row_column.isValid())
624 return false;
625
626 return writeString(row_column.row(), row_column.column(), value, format);
627 }
628
629 /*!
630 Write string \a value to the cell (\a row, \a column) with the \a format.
631 Returns true on success.
632 */
writeString(int row,int column,const RichString & value,const Format & format)633 bool Worksheet::writeString(int row, int column, const RichString &value, const Format &format)
634 {
635 Q_D(Worksheet);
636 // QString content = value.toPlainString();
637 if (d->checkDimensions(row, column))
638 return false;
639
640 // if (content.size() > d->xls_strmax) {
641 // content = content.left(d->xls_strmax);
642 // error = -2;
643 // }
644
645 d->sharedStrings()->addSharedString(value);
646 Format fmt = format.isValid() ? format : d->cellFormat(row, column);
647 if (value.fragmentCount() == 1 && value.fragmentFormat(0).isValid())
648 fmt.mergeFormat(value.fragmentFormat(0));
649 d->workbook->styles()->addXfFormat(fmt);
650 QSharedPointer<Cell> cell = QSharedPointer<Cell>(new Cell(value.toPlainString(), Cell::SharedStringType, fmt, this));
651 cell->d_ptr->richString = value;
652 d->cellTable[row][column] = cell;
653 return true;
654 }
655
656 /*!
657 \overload
658 Write string \a value to the cell \a row_column with the \a format.
659 */
writeString(const CellReference & row_column,const QString & value,const Format & format)660 bool Worksheet::writeString(const CellReference &row_column, const QString &value, const Format &format)
661 {
662 if (!row_column.isValid())
663 return false;
664
665 return writeString(row_column.row(), row_column.column(), value, format);
666 }
667
668 /*!
669 \overload
670
671 Write string \a value to the cell (\a row, \a column) with the \a format.
672 Returns true on success.
673 */
writeString(int row,int column,const QString & value,const Format & format)674 bool Worksheet::writeString(int row, int column, const QString &value, const Format &format)
675 {
676 Q_D(Worksheet);
677 if (d->checkDimensions(row, column))
678 return false;
679
680 RichString rs;
681 if (d->workbook->isHtmlToRichStringEnabled() && Qt::mightBeRichText(value))
682 rs.setHtml(value);
683 else
684 rs.addFragment(value, Format());
685
686 return writeString(row, column, rs, format);
687 }
688
689 /*!
690 \overload
691 Write string \a value to the cell \a row_column with the \a format
692 */
writeInlineString(const CellReference & row_column,const QString & value,const Format & format)693 bool Worksheet::writeInlineString(const CellReference &row_column, const QString &value, const Format &format)
694 {
695 if (!row_column.isValid())
696 return false;
697
698 return writeInlineString(row_column.row(), row_column.column(), value, format);
699 }
700
701 /*!
702 Write string \a value to the cell (\a row, \a column) with the \a format.
703 Returns true on success.
704 */
writeInlineString(int row,int column,const QString & value,const Format & format)705 bool Worksheet::writeInlineString(int row, int column, const QString &value, const Format &format)
706 {
707 Q_D(Worksheet);
708 //int error = 0;
709 QString content = value;
710 if (d->checkDimensions(row, column))
711 return false;
712
713 if (value.size() > XLSX_STRING_MAX) {
714 content = value.left(XLSX_STRING_MAX);
715 //error = -2;
716 }
717
718 Format fmt = format.isValid() ? format : d->cellFormat(row, column);
719 d->workbook->styles()->addXfFormat(fmt);
720 d->cellTable[row][column] = QSharedPointer<Cell>(new Cell(value, Cell::InlineStringType, fmt, this));
721 return true;
722 }
723
724 /*!
725 \overload
726 Write numeric \a value to the cell \a row_column with the \a format.
727 Returns true on success.
728 */
writeNumeric(const CellReference & row_column,double value,const Format & format)729 bool Worksheet::writeNumeric(const CellReference &row_column, double value, const Format &format)
730 {
731 if (!row_column.isValid())
732 return false;
733
734 return writeNumeric(row_column.row(), row_column.column(), value, format);
735 }
736
737 /*!
738 Write numeric \a value to the cell (\a row, \a column) with the \a format.
739 Returns true on success.
740 */
writeNumeric(int row,int column,double value,const Format & format)741 bool Worksheet::writeNumeric(int row, int column, double value, const Format &format)
742 {
743 Q_D(Worksheet);
744 if (d->checkDimensions(row, column))
745 return false;
746
747 Format fmt = format.isValid() ? format : d->cellFormat(row, column);
748 d->workbook->styles()->addXfFormat(fmt);
749 d->cellTable[row][column] = QSharedPointer<Cell>(new Cell(value, Cell::NumberType, fmt, this));
750 return true;
751 }
752
753
754 /*!
755 \overload
756 Write \a formula to the cell \a row_column with the \a format and \a result.
757 Returns true on success.
758 */
writeFormula(const CellReference & row_column,const CellFormula & formula,const Format & format,double result)759 bool Worksheet::writeFormula(const CellReference &row_column, const CellFormula &formula, const Format &format, double result)
760 {
761 if (!row_column.isValid())
762 return false;
763
764 return writeFormula(row_column.row(), row_column.column(), formula, format, result);
765 }
766
767 /*!
768 Write \a formula_ to the cell (\a row, \a column) with the \a format and \a result.
769 Returns true on success.
770 */
writeFormula(int row,int column,const CellFormula & formula_,const Format & format,double result)771 bool Worksheet::writeFormula(int row, int column, const CellFormula &formula_, const Format &format, double result)
772 {
773 Q_D(Worksheet);
774
775 if (d->checkDimensions(row, column))
776 return false;
777
778 Format fmt = format.isValid() ? format : d->cellFormat(row, column);
779 d->workbook->styles()->addXfFormat(fmt);
780
781 CellFormula formula = formula_;
782 formula.d->ca = true;
783 if (formula.formulaType() == CellFormula::SharedType)
784 {
785 //Assign proper shared index for shared formula
786 int si = 0;
787 while ( d->sharedFormulaMap.contains(si) )
788 {
789 ++si;
790 }
791 formula.d->si = si;
792 d->sharedFormulaMap[si] = formula;
793 }
794
795 QSharedPointer<Cell> data = QSharedPointer<Cell>(new Cell(result, Cell::NumberType, fmt, this));
796 data->d_ptr->formula = formula;
797 d->cellTable[row][column] = data;
798
799 CellRange range = formula.reference();
800 if (formula.formulaType() == CellFormula::SharedType) {
801 CellFormula sf(QString(), CellFormula::SharedType);
802 sf.d->si = formula.sharedIndex();
803 for (int r=range.firstRow(); r<=range.lastRow(); ++r) {
804 for (int c=range.firstColumn(); c<=range.lastColumn(); ++c) {
805 if (!(r==row && c==column)) {
806 if(Cell *cell = cellAt(r, c)) {
807 cell->d_ptr->formula = sf;
808 } else {
809 QSharedPointer<Cell> newCell = QSharedPointer<Cell>(new Cell(result, Cell::NumberType, fmt, this));
810 newCell->d_ptr->formula = sf;
811 d->cellTable[r][c] = newCell;
812 }
813 }
814 }
815 }
816 } else if (formula.formulaType() == CellFormula::SharedType) {
817
818 }
819
820 return true;
821 }
822
823 /*!
824 \overload
825 Write a empty cell \a row_column with the \a format.
826 Returns true on success.
827 */
writeBlank(const CellReference & row_column,const Format & format)828 bool Worksheet::writeBlank(const CellReference &row_column, const Format &format)
829 {
830 if (!row_column.isValid())
831 return false;
832
833 return writeBlank(row_column.row(), row_column.column(), format);
834 }
835
836 /*!
837 Write a empty cell (\a row, \a column) with the \a format.
838 Returns true on success.
839 */
writeBlank(int row,int column,const Format & format)840 bool Worksheet::writeBlank(int row, int column, const Format &format)
841 {
842 Q_D(Worksheet);
843 if (d->checkDimensions(row, column))
844 return false;
845
846 Format fmt = format.isValid() ? format : d->cellFormat(row, column);
847 d->workbook->styles()->addXfFormat(fmt);
848
849 //Note: NumberType with an invalid QVariant value means blank.
850 d->cellTable[row][column] = QSharedPointer<Cell>(new Cell(QVariant(), Cell::NumberType, fmt, this));
851
852 return true;
853 }
854 /*!
855 \overload
856 Write a bool \a value to the cell \a row_column with the \a format.
857 Returns true on success.
858 */
writeBool(const CellReference & row_column,bool value,const Format & format)859 bool Worksheet::writeBool(const CellReference &row_column, bool value, const Format &format)
860 {
861 if (!row_column.isValid())
862 return false;
863
864 return writeBool(row_column.row(), row_column.column(), value, format);
865 }
866
867 /*!
868 Write a bool \a value to the cell (\a row, \a column) with the \a format.
869 Returns true on success.
870 */
writeBool(int row,int column,bool value,const Format & format)871 bool Worksheet::writeBool(int row, int column, bool value, const Format &format)
872 {
873 Q_D(Worksheet);
874 if (d->checkDimensions(row, column))
875 return false;
876
877 Format fmt = format.isValid() ? format : d->cellFormat(row, column);
878 d->workbook->styles()->addXfFormat(fmt);
879 d->cellTable[row][column] = QSharedPointer<Cell>(new Cell(value, Cell::BooleanType, fmt, this));
880
881 return true;
882 }
883 /*!
884 \overload
885 Write a QDateTime \a dt to the cell \a row_column with the \a format.
886 Returns true on success.
887 */
writeDateTime(const CellReference & row_column,const QDateTime & dt,const Format & format)888 bool Worksheet::writeDateTime(const CellReference &row_column, const QDateTime &dt, const Format &format)
889 {
890 if (!row_column.isValid())
891 return false;
892
893 return writeDateTime(row_column.row(), row_column.column(), dt, format);
894 }
895
896 /*!
897 Write a QDateTime \a dt to the cell (\a row, \a column) with the \a format.
898 Returns true on success.
899 */
writeDateTime(int row,int column,const QDateTime & dt,const Format & format)900 bool Worksheet::writeDateTime(int row, int column, const QDateTime &dt, const Format &format)
901 {
902 Q_D(Worksheet);
903 if (d->checkDimensions(row, column))
904 return false;
905
906 Format fmt = format.isValid() ? format : d->cellFormat(row, column);
907 if (!fmt.isValid() || !fmt.isDateTimeFormat())
908 fmt.setNumberFormat(d->workbook->defaultDateFormat());
909 d->workbook->styles()->addXfFormat(fmt);
910
911 double value = datetimeToNumber(dt, d->workbook->isDate1904());
912
913 d->cellTable[row][column] = QSharedPointer<Cell>(new Cell(value, Cell::NumberType, fmt, this));
914
915 return true;
916 }
917
918 // dev67
writeDate(const CellReference & row_column,const QDate & dt,const Format & format)919 bool Worksheet::writeDate(const CellReference &row_column, const QDate &dt, const Format &format)
920 {
921 if (!row_column.isValid())
922 return false;
923
924 return writeDate(row_column.row(), row_column.column(), dt, format);
925 }
926
927 // dev67
writeDate(int row,int column,const QDate & dt,const Format & format)928 bool Worksheet::writeDate(int row, int column, const QDate &dt, const Format &format)
929 {
930 Q_D(Worksheet);
931 if (d->checkDimensions(row, column))
932 return false;
933
934 Format fmt = format.isValid() ? format : d->cellFormat(row, column);
935
936 if (!fmt.isValid() || !fmt.isDateTimeFormat())
937 fmt.setNumberFormat(d->workbook->defaultDateFormat());
938
939 d->workbook->styles()->addXfFormat(fmt);
940
941 double value = datetimeToNumber(QDateTime(dt, QTime(0,0,0)), d->workbook->isDate1904());
942
943 d->cellTable[row][column] = QSharedPointer<Cell>(new Cell(value, Cell::NumberType, fmt, this));
944
945 return true;
946 }
947
948 /*!
949 \overload
950 Write a QTime \a t to the cell \a row_column with the \a format.
951 Returns true on success.
952 */
writeTime(const CellReference & row_column,const QTime & t,const Format & format)953 bool Worksheet::writeTime(const CellReference &row_column, const QTime &t, const Format &format)
954 {
955 if (!row_column.isValid())
956 return false;
957
958 return writeTime(row_column.row(), row_column.column(), t, format);
959 }
960
961 /*!
962 Write a QTime \a t to the cell (\a row, \a column) with the \a format.
963 Returns true on success.
964 */
writeTime(int row,int column,const QTime & t,const Format & format)965 bool Worksheet::writeTime(int row, int column, const QTime &t, const Format &format)
966 {
967 Q_D(Worksheet);
968 if (d->checkDimensions(row, column))
969 return false;
970
971 Format fmt = format.isValid() ? format : d->cellFormat(row, column);
972 if (!fmt.isValid() || !fmt.isDateTimeFormat())
973 fmt.setNumberFormat(QStringLiteral("hh:mm:ss"));
974 d->workbook->styles()->addXfFormat(fmt);
975
976 d->cellTable[row][column] = QSharedPointer<Cell>(new Cell(timeToNumber(t), Cell::NumberType, fmt, this));
977
978 return true;
979 }
980
981 /*!
982 \overload
983 Write a QUrl \a url to the cell \a row_column with the given \a format \a display and \a tip.
984 Returns true on success.
985 */
writeHyperlink(const CellReference & row_column,const QUrl & url,const Format & format,const QString & display,const QString & tip)986 bool Worksheet::writeHyperlink(const CellReference &row_column, const QUrl &url, const Format &format, const QString &display, const QString &tip)
987 {
988 if (!row_column.isValid())
989 return false;
990
991 return writeHyperlink(row_column.row(), row_column.column(), url, format, display, tip);
992 }
993
994 /*!
995 Write a QUrl \a url to the cell (\a row, \a column) with the given \a format \a display and \a tip.
996 Returns true on success.
997 */
writeHyperlink(int row,int column,const QUrl & url,const Format & format,const QString & display,const QString & tip)998 bool Worksheet::writeHyperlink(int row, int column, const QUrl &url, const Format &format, const QString &display, const QString &tip)
999 {
1000 Q_D(Worksheet);
1001 if (d->checkDimensions(row, column))
1002 return false;
1003
1004 //int error = 0;
1005
1006 QString urlString = url.toString();
1007
1008 //Generate proper display string
1009 QString displayString = display.isEmpty() ? urlString : display;
1010 if (displayString.startsWith(QLatin1String("mailto:")))
1011 displayString.replace(QLatin1String("mailto:"), QString());
1012 if (displayString.size() > XLSX_STRING_MAX) {
1013 displayString = displayString.left(XLSX_STRING_MAX);
1014 //error = -2;
1015 }
1016
1017 /*
1018 Location within target. If target is a workbook (or this workbook)
1019 this shall refer to a sheet and cell or a defined name. Can also
1020 be an HTML anchor if target is HTML file.
1021
1022 c:\temp\file.xlsx#Sheet!A1
1023 http://a.com/aaa.html#aaaaa
1024 */
1025 QString locationString;
1026 if (url.hasFragment()) {
1027 locationString = url.fragment();
1028 urlString = url.toString(QUrl::RemoveFragment);
1029 }
1030
1031 Format fmt = format.isValid() ? format : d->cellFormat(row, column);
1032 //Given a default style for hyperlink
1033 if (!fmt.isValid()) {
1034 fmt.setVerticalAlignment(Format::AlignVCenter);
1035 fmt.setFontColor(Qt::blue);
1036 fmt.setFontUnderline(Format::FontUnderlineSingle);
1037 }
1038 d->workbook->styles()->addXfFormat(fmt);
1039
1040 //Write the hyperlink string as normal string.
1041 d->sharedStrings()->addSharedString(displayString);
1042 d->cellTable[row][column] = QSharedPointer<Cell>(new Cell(displayString, Cell::SharedStringType, fmt, this));
1043
1044 //Store the hyperlink data in a separate table
1045 d->urlTable[row][column] = QSharedPointer<XlsxHyperlinkData>(new XlsxHyperlinkData(XlsxHyperlinkData::External, urlString, locationString, QString(), tip));
1046
1047 return true;
1048 }
1049
1050 /*!
1051 * Add one DataValidation \a validation to the sheet.
1052 * Returns true on success.
1053 */
addDataValidation(const DataValidation & validation)1054 bool Worksheet::addDataValidation(const DataValidation &validation)
1055 {
1056 Q_D(Worksheet);
1057 if (validation.ranges().isEmpty() || validation.validationType()==DataValidation::None)
1058 return false;
1059
1060 d->dataValidationsList.append(validation);
1061 return true;
1062 }
1063
1064 /*!
1065 * Add one ConditionalFormatting \a cf to the sheet.
1066 * Returns true on success.
1067 */
addConditionalFormatting(const ConditionalFormatting & cf)1068 bool Worksheet::addConditionalFormatting(const ConditionalFormatting &cf)
1069 {
1070 Q_D(Worksheet);
1071 if (cf.ranges().isEmpty())
1072 return false;
1073
1074 for (int i=0; i<cf.d->cfRules.size(); ++i) {
1075 const QSharedPointer<XlsxCfRuleData> &rule = cf.d->cfRules[i];
1076 if (!rule->dxfFormat.isEmpty())
1077 d->workbook->styles()->addDxfFormat(rule->dxfFormat);
1078 rule->priority = 1;
1079 }
1080 d->conditionalFormattingList.append(cf);
1081 return true;
1082 }
1083
1084 /*!
1085 * Insert an \a image at the position \a row, \a column
1086 * Returns true on success.
1087 */
insertImage(int row,int column,const QImage & image)1088 int Worksheet::insertImage(int row, int column, const QImage &image)
1089 {
1090 Q_D(Worksheet);
1091
1092 int imageIndex = 0;
1093
1094 if (image.isNull())
1095 return imageIndex;
1096
1097 if (!d->drawing)
1098 {
1099 d->drawing = QSharedPointer<Drawing>(new Drawing(this, F_NewFromScratch));
1100 }
1101
1102 DrawingOneCellAnchor* anchor = new DrawingOneCellAnchor(d->drawing.data(), DrawingAnchor::Picture);
1103
1104 /*
1105 The size are expressed as English Metric Units (EMUs).
1106 EMU is 1/360 000 of centimiter.
1107 */
1108 anchor->from = XlsxMarker(row, column, 0, 0);
1109 float scaleX = 36e6f / std::max(1,image.dotsPerMeterX());
1110 float scaleY = 36e6f / std::max(1,image.dotsPerMeterY());
1111 anchor->ext = QSize( int(image.width() * scaleX), int(image.height() * scaleY) );
1112
1113 anchor->setObjectPicture(image);
1114
1115 imageIndex = anchor->getm_id();
1116
1117 return imageIndex;
1118 }
1119
getImage(int imageIndex,QImage & img)1120 bool Worksheet::getImage(int imageIndex, QImage& img)
1121 {
1122 Q_D(Worksheet);
1123
1124 if( imageIndex <= (-1) )
1125 {
1126 return false;
1127 }
1128
1129 if ( d->drawing == nullptr )
1130 {
1131 return false;
1132 }
1133
1134 int realImageIndex = imageIndex - 1; // minus one
1135
1136 DrawingAnchor* danchor = d->drawing->anchors.at( realImageIndex );
1137 // QSharedPointer<Drawing> // for multithread
1138 if ( danchor == nullptr )
1139 {
1140 return false;
1141 }
1142
1143 bool ret= danchor->getObjectPicture(img);
1144 return ret;
1145 }
1146
getImage(int row,int column,QImage & img)1147 bool Worksheet::getImage(int row, int column, QImage &img)
1148 {
1149 Q_D(Worksheet);
1150
1151 if ( d->drawing == nullptr )
1152 {
1153 return false;
1154 }
1155
1156 for(int i = 0; i < d->drawing->anchors.size(); i++)
1157 {
1158 if(d->drawing->anchors[i]->row() == row && d->drawing->anchors[i]->col() == column)
1159 {
1160 DrawingAnchor* danchor = d->drawing->anchors.at( i );
1161
1162 if ( danchor == nullptr )
1163 {
1164 return false;
1165 }
1166
1167 bool ret= danchor->getObjectPicture(img);
1168 return ret;
1169 }
1170 }
1171 return false;
1172 }
1173
getImageCount()1174 uint Worksheet::getImageCount()
1175 {
1176 Q_D(Worksheet);
1177
1178 if ( d->drawing == nullptr )
1179 {
1180 return false;
1181 }
1182
1183 int size = d->drawing->anchors.size();
1184 return uint(size);
1185 }
1186
1187
1188
1189 /*!
1190 * Creates an chart with the given \a size and insert
1191 * at the position \a row, \a column.
1192 * The chart will be returned.
1193 */
insertChart(int row,int column,const QSize & size)1194 Chart *Worksheet::insertChart(int row, int column, const QSize &size)
1195 {
1196 Q_D(Worksheet);
1197
1198 if (!d->drawing)
1199 d->drawing = QSharedPointer<Drawing>(new Drawing(this, F_NewFromScratch));
1200
1201 DrawingOneCellAnchor *anchor = new DrawingOneCellAnchor(d->drawing.data(), DrawingAnchor::Picture);
1202
1203 /*
1204 The size are expressed as English Metric Units (EMUs). There are
1205 12,700 EMUs per point. Therefore, 12,700 * 3 /4 = 9,525 EMUs per
1206 pixel
1207 */
1208 anchor->from = XlsxMarker(row, column, 0, 0);
1209 anchor->ext = size * 9525;
1210
1211 QSharedPointer<Chart> chart = QSharedPointer<Chart>(new Chart(this, F_NewFromScratch));
1212 anchor->setObjectGraphicFrame(chart);
1213
1214 return chart.data();
1215 }
1216
1217 /*!
1218 Merge a \a range of cells. The first cell should contain the data and the others should
1219 be blank. All cells will be applied the same style if a valid \a format is given.
1220 Returns true on success.
1221
1222 \note All cells except the top-left one will be cleared.
1223 */
mergeCells(const CellRange & range,const Format & format)1224 bool Worksheet::mergeCells(const CellRange &range, const Format &format)
1225 {
1226 Q_D(Worksheet);
1227 if (range.rowCount() < 2 && range.columnCount() < 2)
1228 return false;
1229
1230 if (d->checkDimensions(range.firstRow(), range.firstColumn()))
1231 return false;
1232
1233 if (format.isValid())
1234 {
1235 d->workbook->styles()->addXfFormat(format);
1236 }
1237
1238 for (int row = range.firstRow(); row <= range.lastRow(); ++row)
1239 {
1240 for (int col = range.firstColumn(); col <= range.lastColumn(); ++col)
1241 {
1242 if (row == range.firstRow() && col == range.firstColumn())
1243 {
1244 Cell *cell = cellAt(row, col);
1245 if (cell)
1246 {
1247 if (format.isValid())
1248 cell->d_ptr->format = format;
1249 }
1250 else
1251 {
1252 writeBlank(row, col, format);
1253 }
1254 }
1255 else
1256 {
1257 writeBlank(row, col, format);
1258 }
1259 }
1260 }
1261
1262 d->merges.append(range);
1263 return true;
1264 }
1265
1266 /*!
1267 Unmerge the cells in the \a range. Returns true on success.
1268
1269 */
unmergeCells(const CellRange & range)1270 bool Worksheet::unmergeCells(const CellRange &range)
1271 {
1272 Q_D(Worksheet);
1273 return d->merges.removeOne(range);
1274 }
1275
1276 /*!
1277 Returns all the merged cells.
1278 */
mergedCells() const1279 QList<CellRange> Worksheet::mergedCells() const
1280 {
1281 Q_D(const Worksheet);
1282
1283 // dev57
1284
1285 QList<CellRange> emptyList;
1286
1287 if ( d->type == AbstractSheet::ST_WorkSheet )
1288 {
1289 return d->merges;
1290 }
1291 else if ( d->type == AbstractSheet::ST_ChartSheet )
1292 {
1293 }
1294 else if ( d->type == AbstractSheet::ST_DialogSheet )
1295 {
1296 }
1297 else if ( d->type == AbstractSheet::ST_MacroSheet )
1298 {
1299 }
1300 else
1301 { // undefined
1302 }
1303
1304 return emptyList;
1305 }
1306
1307 /*!
1308 * \internal
1309 */
saveToXmlFile(QIODevice * device) const1310 void Worksheet::saveToXmlFile(QIODevice *device) const
1311 {
1312 Q_D(const Worksheet);
1313 d->relationships->clear();
1314
1315 QXmlStreamWriter writer(device);
1316
1317 writer.writeStartDocument(QStringLiteral("1.0"), true);
1318 writer.writeStartElement(QStringLiteral("worksheet"));
1319 writer.writeAttribute(QStringLiteral("xmlns"), QStringLiteral("http://schemas.openxmlformats.org/spreadsheetml/2006/main"));
1320 writer.writeAttribute(QStringLiteral("xmlns:r"), QStringLiteral("http://schemas.openxmlformats.org/officeDocument/2006/relationships"));
1321
1322 //for Excel 2010
1323 // writer.writeAttribute("xmlns:mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
1324 // writer.writeAttribute("xmlns:x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
1325 // writer.writeAttribute("mc:Ignorable", "x14ac");
1326
1327 writer.writeStartElement(QStringLiteral("dimension"));
1328 writer.writeAttribute(QStringLiteral("ref"), d->generateDimensionString());
1329 writer.writeEndElement();//dimension
1330
1331 writer.writeStartElement(QStringLiteral("sheetViews"));
1332 writer.writeStartElement(QStringLiteral("sheetView"));
1333 if (d->windowProtection)
1334 writer.writeAttribute(QStringLiteral("windowProtection"), QStringLiteral("1"));
1335 if (d->showFormulas)
1336 writer.writeAttribute(QStringLiteral("showFormulas"), QStringLiteral("1"));
1337 if (!d->showGridLines)
1338 writer.writeAttribute(QStringLiteral("showGridLines"), QStringLiteral("0"));
1339 if (!d->showRowColHeaders)
1340 writer.writeAttribute(QStringLiteral("showRowColHeaders"), QStringLiteral("0"));
1341 if (!d->showZeros)
1342 writer.writeAttribute(QStringLiteral("showZeros"), QStringLiteral("0"));
1343 if (d->rightToLeft)
1344 writer.writeAttribute(QStringLiteral("rightToLeft"), QStringLiteral("1"));
1345 if (d->tabSelected)
1346 writer.writeAttribute(QStringLiteral("tabSelected"), QStringLiteral("1"));
1347 if (!d->showRuler)
1348 writer.writeAttribute(QStringLiteral("showRuler"), QStringLiteral("0"));
1349 if (!d->showOutlineSymbols)
1350 writer.writeAttribute(QStringLiteral("showOutlineSymbols"), QStringLiteral("0"));
1351 if (!d->showWhiteSpace)
1352 writer.writeAttribute(QStringLiteral("showWhiteSpace"), QStringLiteral("0"));
1353 writer.writeAttribute(QStringLiteral("workbookViewId"), QStringLiteral("0"));
1354 writer.writeEndElement();//sheetView
1355 writer.writeEndElement();//sheetViews
1356
1357 writer.writeStartElement(QStringLiteral("sheetFormatPr"));
1358 writer.writeAttribute(QStringLiteral("defaultRowHeight"), QString::number(d->default_row_height));
1359 if (d->default_row_height != 15)
1360 writer.writeAttribute(QStringLiteral("customHeight"), QStringLiteral("1"));
1361 if (d->default_row_zeroed)
1362 writer.writeAttribute(QStringLiteral("zeroHeight"), QStringLiteral("1"));
1363 if (d->outline_row_level)
1364 writer.writeAttribute(QStringLiteral("outlineLevelRow"), QString::number(d->outline_row_level));
1365 if (d->outline_col_level)
1366 writer.writeAttribute(QStringLiteral("outlineLevelCol"), QString::number(d->outline_col_level));
1367 //for Excel 2010
1368 // writer.writeAttribute("x14ac:dyDescent", "0.25");
1369 writer.writeEndElement();//sheetFormatPr
1370
1371 if (!d->colsInfo.isEmpty())
1372 {
1373 writer.writeStartElement(QStringLiteral("cols"));
1374 QMapIterator<int, QSharedPointer<XlsxColumnInfo> > it(d->colsInfo);
1375 while (it.hasNext())
1376 {
1377 it.next();
1378 QSharedPointer<XlsxColumnInfo> col_info = it.value();
1379 writer.writeStartElement(QStringLiteral("col"));
1380 writer.writeAttribute(QStringLiteral("min"), QString::number(col_info->firstColumn));
1381 writer.writeAttribute(QStringLiteral("max"), QString::number(col_info->lastColumn));
1382 if (col_info->width > 0.)
1383 writer.writeAttribute(QStringLiteral("width"), QString::number(col_info->width, 'g', 15));
1384 if (!col_info->format.isEmpty())
1385 writer.writeAttribute(QStringLiteral("style"), QString::number(col_info->format.xfIndex()));
1386 if (col_info->hidden)
1387 writer.writeAttribute(QStringLiteral("hidden"), QStringLiteral("1"));
1388 if (col_info->width > 0.)
1389 writer.writeAttribute(QStringLiteral("customWidth"), QStringLiteral("1"));
1390 if (col_info->outlineLevel)
1391 writer.writeAttribute(QStringLiteral("outlineLevel"), QString::number(col_info->outlineLevel));
1392 if (col_info->collapsed)
1393 writer.writeAttribute(QStringLiteral("collapsed"), QStringLiteral("1"));
1394 writer.writeEndElement();//col
1395 }
1396 writer.writeEndElement();//cols
1397 }
1398
1399 writer.writeStartElement(QStringLiteral("sheetData"));
1400 if (d->dimension.isValid())
1401 d->saveXmlSheetData(writer);
1402 writer.writeEndElement();//sheetData
1403
1404 d->saveXmlMergeCells(writer);
1405 for (const ConditionalFormatting &cf : d->conditionalFormattingList)
1406 cf.saveToXml(writer);
1407 d->saveXmlDataValidations(writer);
1408
1409 //{{ liufeijin : write pagesettings add by liufeijin 20181028
1410
1411 // fixed by j2doll [dev18]
1412 // NOTE: empty element is not problem. but, empty structure of element is not parsed by Excel.
1413
1414 // pageMargins
1415 if ( false == d->PMleft.isEmpty() &&
1416 false == d->PMright.isEmpty() &&
1417 false == d->PMtop.isEmpty() &&
1418 false == d->PMbotton.isEmpty() &&
1419 false == d->PMheader.isEmpty() &&
1420 false == d->PMfooter.isEmpty()
1421 )
1422 {
1423 writer.writeStartElement(QStringLiteral("pageMargins"));
1424
1425 writer.writeAttribute(QStringLiteral("left"), d->PMleft );
1426 writer.writeAttribute(QStringLiteral("right"), d->PMright );
1427 writer.writeAttribute(QStringLiteral("top"), d->PMtop );
1428 writer.writeAttribute(QStringLiteral("bottom"), d->PMbotton );
1429 writer.writeAttribute(QStringLiteral("header"), d->PMheader );
1430 writer.writeAttribute(QStringLiteral("footer"), d->PMfooter );
1431
1432 writer.writeEndElement(); // pageMargins
1433 }
1434
1435 // dev57
1436 if ( !d->Prid.isEmpty() )
1437 {
1438 writer.writeStartElement(QStringLiteral("pageSetup")); // pageSetup
1439
1440 writer.writeAttribute(QStringLiteral("r:id"), d->Prid);
1441
1442 if ( !d->PverticalDpi.isEmpty() )
1443 {
1444 writer.writeAttribute(QStringLiteral("verticalDpi"), d->PverticalDpi);
1445 }
1446
1447 if ( !d->PhorizontalDpi.isEmpty() )
1448 {
1449 writer.writeAttribute(QStringLiteral("horizontalDpi"), d->PhorizontalDpi);
1450 }
1451
1452 if ( !d->PuseFirstPageNumber.isEmpty() )
1453 {
1454 writer.writeAttribute(QStringLiteral("useFirstPageNumber"), d->PuseFirstPageNumber);
1455 }
1456
1457 if ( !d->PfirstPageNumber.isEmpty() )
1458 {
1459 writer.writeAttribute(QStringLiteral("firstPageNumber"), d->PfirstPageNumber);
1460 }
1461
1462 if ( !d->Pscale.isEmpty() )
1463 {
1464 writer.writeAttribute(QStringLiteral("scale"), d->Pscale);
1465 }
1466
1467 if ( !d->PpaperSize.isEmpty() )
1468 {
1469 writer.writeAttribute(QStringLiteral("paperSize"), d->PpaperSize);
1470 }
1471
1472 if ( !d->Porientation.isEmpty() )
1473 {
1474 writer.writeAttribute(QStringLiteral("orientation"), d->Porientation);
1475 }
1476
1477 if(!d->Pcopies.isEmpty())
1478 {
1479 writer.writeAttribute(QStringLiteral("copies"), d->Pcopies);
1480 }
1481
1482 writer.writeEndElement(); // pageSetup
1483
1484 } // if ( !d->Prid.isEmpty() )
1485
1486 // headerFooter
1487 if( !(d->MoodFooter.isNull()) ||
1488 !(d->MoodFooter.isNull()) )
1489 {
1490 writer.writeStartElement(QStringLiteral("headerFooter")); // headerFooter
1491
1492 if ( !d->MoodalignWithMargins.isEmpty() )
1493 {
1494 writer.writeAttribute(QStringLiteral("alignWithMargins"), d->MoodalignWithMargins);
1495 }
1496
1497 if ( !d->ModdHeader.isNull() )
1498 {
1499 writer.writeStartElement(QStringLiteral("oddHeader"));
1500 writer.writeCharacters(d->ModdHeader);
1501 writer.writeEndElement(); // oddHeader
1502 }
1503
1504 if ( !d->MoodFooter.isNull() )
1505 {
1506 writer.writeTextElement(QStringLiteral("oddFooter"), d->MoodFooter);
1507 }
1508
1509 writer.writeEndElement(); // headerFooter
1510 }
1511
1512 d->saveXmlHyperlinks(writer);
1513 d->saveXmlDrawings(writer);
1514
1515 writer.writeEndElement(); // worksheet
1516 writer.writeEndDocument();
1517 }
1518
1519 //{{ liufeijin
setStartPage(int spagen)1520 bool Worksheet::setStartPage(int spagen)
1521 {
1522 Q_D(Worksheet);
1523
1524 d->PfirstPageNumber=QString::number(spagen);
1525
1526 return true;
1527 }
1528 //}}
1529
saveXmlSheetData(QXmlStreamWriter & writer) const1530 void WorksheetPrivate::saveXmlSheetData(QXmlStreamWriter &writer) const
1531 {
1532 calculateSpans();
1533 for (int row_num = dimension.firstRow(); row_num <= dimension.lastRow(); row_num++)
1534 {
1535 auto ctIt = cellTable.constFind(row_num);
1536 auto riIt = rowsInfo.constFind(row_num);
1537 if (ctIt == cellTable.constEnd() && riIt == rowsInfo.constEnd() && !comments.contains(row_num))
1538 {
1539 //Only process rows with cell data / comments / formatting
1540 continue;
1541 }
1542
1543 int span_index = (row_num-1) / 16;
1544 QString span;
1545 auto rsIt = row_spans.constFind(span_index);
1546 if (rsIt != row_spans.constEnd())
1547 span = rsIt.value();
1548
1549 writer.writeStartElement(QStringLiteral("row"));
1550 writer.writeAttribute(QStringLiteral("r"), QString::number(row_num));
1551
1552 if (!span.isEmpty())
1553 writer.writeAttribute(QStringLiteral("spans"), span);
1554
1555 if (riIt != rowsInfo.constEnd())
1556 {
1557 QSharedPointer<XlsxRowInfo> rowInfo = riIt.value();
1558 if (!rowInfo->format.isEmpty())
1559 {
1560 writer.writeAttribute(QStringLiteral("s"), QString::number(rowInfo->format.xfIndex()));
1561 writer.writeAttribute(QStringLiteral("customFormat"), QStringLiteral("1"));
1562 }
1563
1564 //!Todo: support customHeight from info struct
1565 //!Todo: where does this magic number '15' come from?
1566 if (rowInfo->customHeight) {
1567 writer.writeAttribute(QStringLiteral("ht"), QString::number(rowInfo->height));
1568 writer.writeAttribute(QStringLiteral("customHeight"), QStringLiteral("1"));
1569 } else {
1570 writer.writeAttribute(QStringLiteral("customHeight"), QStringLiteral("0"));
1571 }
1572
1573 if (rowInfo->hidden)
1574 writer.writeAttribute(QStringLiteral("hidden"), QStringLiteral("1"));
1575 if (rowInfo->outlineLevel > 0)
1576 writer.writeAttribute(QStringLiteral("outlineLevel"), QString::number(rowInfo->outlineLevel));
1577 if (rowInfo->collapsed)
1578 writer.writeAttribute(QStringLiteral("collapsed"), QStringLiteral("1"));
1579 }
1580
1581 //Write cell data if row contains filled cells
1582 if (ctIt != cellTable.constEnd())
1583 {
1584 for (int col_num = dimension.firstColumn(); col_num <= dimension.lastColumn(); col_num++)
1585 {
1586 if (ctIt->contains(col_num))
1587 {
1588 saveXmlCellData(writer, row_num, col_num, (*ctIt)[col_num]);
1589 }
1590 }
1591 }
1592 writer.writeEndElement(); //row
1593 }
1594 }
1595
saveXmlCellData(QXmlStreamWriter & writer,int row,int col,QSharedPointer<Cell> cell) const1596 void WorksheetPrivate::saveXmlCellData(QXmlStreamWriter &writer, int row, int col, QSharedPointer<Cell> cell) const
1597 {
1598 Q_Q(const Worksheet);
1599
1600 //This is the innermost loop so efficiency is important.
1601 QString cell_pos = CellReference(row, col).toString();
1602
1603 writer.writeStartElement(QStringLiteral("c"));
1604 writer.writeAttribute(QStringLiteral("r"), cell_pos);
1605
1606 QMap<int, QSharedPointer<XlsxRowInfo> >::ConstIterator rIt;
1607 QMap<int, QSharedPointer<XlsxColumnInfo> >::ConstIterator cIt;
1608
1609 //Style used by the cell, row or col
1610 if (!cell->format().isEmpty())
1611 writer.writeAttribute(QStringLiteral("s"), QString::number(cell->format().xfIndex()));
1612 else if ((rIt = rowsInfo.constFind(row)) != rowsInfo.constEnd() && !(*rIt)->format.isEmpty())
1613 writer.writeAttribute(QStringLiteral("s"), QString::number((*rIt)->format.xfIndex()));
1614 else if ((cIt = colsInfoHelper.constFind(col)) != colsInfoHelper.constEnd() && !(*cIt)->format.isEmpty())
1615 writer.writeAttribute(QStringLiteral("s"), QString::number((*cIt)->format.xfIndex()));
1616
1617 if (cell->cellType() == Cell::SharedStringType) // 's'
1618 {
1619 int sst_idx;
1620 if (cell->isRichString())
1621 sst_idx = sharedStrings()->getSharedStringIndex(cell->d_ptr->richString);
1622 else
1623 sst_idx = sharedStrings()->getSharedStringIndex(cell->value().toString());
1624
1625 writer.writeAttribute(QStringLiteral("t"), QStringLiteral("s"));
1626 writer.writeTextElement(QStringLiteral("v"), QString::number(sst_idx));
1627 }
1628 else if (cell->cellType() == Cell::InlineStringType) // 'inlineStr'
1629 {
1630 writer.writeAttribute(QStringLiteral("t"), QStringLiteral("inlineStr"));
1631 writer.writeStartElement(QStringLiteral("is"));
1632 if (cell->isRichString())
1633 {
1634 //Rich text string
1635 RichString string = cell->d_ptr->richString;
1636 for (int i=0; i<string.fragmentCount(); ++i)
1637 {
1638 writer.writeStartElement(QStringLiteral("r"));
1639 if (string.fragmentFormat(i).hasFontData())
1640 {
1641 writer.writeStartElement(QStringLiteral("rPr"));
1642 //:Todo
1643 writer.writeEndElement();// rPr
1644 }
1645 writer.writeStartElement(QStringLiteral("t"));
1646 if (isSpaceReserveNeeded(string.fragmentText(i)))
1647 writer.writeAttribute(QStringLiteral("xml:space"), QStringLiteral("preserve"));
1648 writer.writeCharacters(string.fragmentText(i));
1649 writer.writeEndElement();// t
1650 writer.writeEndElement(); // r
1651 }
1652 }
1653 else
1654 {
1655 writer.writeStartElement(QStringLiteral("t"));
1656 QString string = cell->value().toString();
1657 if (isSpaceReserveNeeded(string))
1658 writer.writeAttribute(QStringLiteral("xml:space"), QStringLiteral("preserve"));
1659 writer.writeCharacters(string);
1660 writer.writeEndElement(); // t
1661 }
1662 writer.writeEndElement();//is
1663 }
1664 else if (cell->cellType() == Cell::NumberType) // 'n'
1665 {
1666 writer.writeAttribute(QStringLiteral("t"), QStringLiteral("n")); // dev67
1667
1668 if (cell->hasFormula())
1669 {
1670 QString strFormula = cell->formula().d->formula;
1671 Q_UNUSED(strFormula);
1672 cell->formula().saveToXml(writer);
1673 }
1674
1675 if (cell->value().isValid())
1676 { //note that, invalid value means 'v' is blank
1677 double value = cell->value().toDouble();
1678 writer.writeTextElement(QStringLiteral("v"), QString::number(value, 'g', 15));
1679 }
1680 }
1681 else if (cell->cellType() == Cell::StringType) // 'str'
1682 {
1683 writer.writeAttribute(QStringLiteral("t"), QStringLiteral("str"));
1684 if (cell->hasFormula())
1685 cell->formula().saveToXml(writer);
1686
1687 writer.writeTextElement(QStringLiteral("v"), cell->value().toString());
1688 }
1689 else if (cell->cellType() == Cell::BooleanType) // 'b'
1690 {
1691 writer.writeAttribute(QStringLiteral("t"), QStringLiteral("b"));
1692
1693 // dev34
1694
1695 if (cell->hasFormula())
1696 {
1697 QString strFormula = cell->formula().d->formula;
1698 Q_UNUSED(strFormula);
1699 cell->formula().saveToXml(writer);
1700 }
1701
1702 writer.writeTextElement(QStringLiteral("v"), cell->value().toBool() ? QStringLiteral("1") : QStringLiteral("0"));
1703 }
1704 else if (cell->cellType() == Cell::DateType) // 'd'
1705 {
1706 // dev67
1707
1708 double num = cell->value().toDouble();
1709 bool is1904 = q->workbook()->isDate1904();
1710 if (!is1904 && num > 60) // for mac os excel
1711 {
1712 num = num - 1;
1713 }
1714
1715 // number type. see for 18.18.11 ST_CellType (Cell Type) more information.
1716 writer.writeAttribute(QStringLiteral("t"), QStringLiteral("n"));
1717 writer.writeTextElement(QStringLiteral("v"), cell->value().toString() );
1718
1719 }
1720 else if (cell->cellType() == Cell::ErrorType) // 'e'
1721 {
1722 writer.writeAttribute(QStringLiteral("t"), QStringLiteral("e"));
1723 writer.writeTextElement(QStringLiteral("v"), cell->value().toString() );
1724 }
1725 else // if (cell->cellType() == Cell::CustomType)
1726 {
1727 // custom type
1728
1729 if (cell->hasFormula())
1730 {
1731 QString strFormula = cell->formula().d->formula;
1732 Q_UNUSED(strFormula);
1733 cell->formula().saveToXml(writer);
1734 }
1735
1736 if (cell->value().isValid())
1737 { //note that, invalid value means 'v' is blank
1738 double value = cell->value().toDouble();
1739 writer.writeTextElement(QStringLiteral("v"), QString::number(value, 'g', 15));
1740 }
1741 }
1742
1743 writer.writeEndElement(); // c
1744 }
1745
saveXmlMergeCells(QXmlStreamWriter & writer) const1746 void WorksheetPrivate::saveXmlMergeCells(QXmlStreamWriter &writer) const
1747 {
1748 if (merges.isEmpty())
1749 return;
1750
1751 writer.writeStartElement(QStringLiteral("mergeCells"));
1752 writer.writeAttribute(QStringLiteral("count"), QString::number(merges.size()));
1753
1754 for (const CellRange &range : merges)
1755 {
1756 writer.writeEmptyElement(QStringLiteral("mergeCell"));
1757 writer.writeAttribute(QStringLiteral("ref"), range.toString());
1758 }
1759
1760 writer.writeEndElement(); //mergeCells
1761 }
1762
saveXmlDataValidations(QXmlStreamWriter & writer) const1763 void WorksheetPrivate::saveXmlDataValidations(QXmlStreamWriter &writer) const
1764 {
1765 if (dataValidationsList.isEmpty())
1766 return;
1767
1768 writer.writeStartElement(QStringLiteral("dataValidations"));
1769 writer.writeAttribute(QStringLiteral("count"), QString::number(dataValidationsList.size()));
1770
1771 for (const DataValidation &validation : dataValidationsList)
1772 validation.saveToXml(writer);
1773
1774 writer.writeEndElement(); //dataValidations
1775 }
1776
saveXmlHyperlinks(QXmlStreamWriter & writer) const1777 void WorksheetPrivate::saveXmlHyperlinks(QXmlStreamWriter &writer) const
1778 {
1779 if (urlTable.isEmpty())
1780 return;
1781
1782 writer.writeStartElement(QStringLiteral("hyperlinks"));
1783 QMapIterator<int, QMap< int, QSharedPointer<XlsxHyperlinkData> > > it(urlTable);
1784
1785 while (it.hasNext())
1786 {
1787 it.next();
1788 int row = it.key();
1789 QMapIterator< int, QSharedPointer<XlsxHyperlinkData> > it2(it.value());
1790
1791 while (it2.hasNext())
1792 {
1793 it2.next();
1794 int col = it2.key();
1795 QSharedPointer<XlsxHyperlinkData> data = it2.value();
1796 QString ref = CellReference(row, col).toString();
1797
1798 // dev57
1799 // writer.writeEmptyElement(QStringLiteral("hyperlink"));
1800 writer.writeStartElement(QStringLiteral("hyperlink"));
1801
1802 writer.writeAttribute(QStringLiteral("ref"), ref); // required field
1803
1804 if ( data->linkType == XlsxHyperlinkData::External )
1805 {
1806 // Update relationships
1807 relationships->addWorksheetRelationship(QStringLiteral("/hyperlink"), data->target, QStringLiteral("External"));
1808
1809 writer.writeAttribute(QStringLiteral("r:id"), QStringLiteral("rId%1").arg(relationships->count()));
1810 }
1811
1812 if (!data->location.isEmpty())
1813 {
1814 writer.writeAttribute(QStringLiteral("location"), data->location);
1815 }
1816
1817 if (!data->display.isEmpty())
1818 {
1819 writer.writeAttribute(QStringLiteral("display"), data->display);
1820 }
1821
1822 if (!data->tooltip.isEmpty())
1823 {
1824 writer.writeAttribute(QStringLiteral("tooltip"), data->tooltip);
1825 }
1826
1827 // dev57
1828 writer.writeEndElement(); // hyperlink
1829 }
1830 }
1831
1832 writer.writeEndElement(); // hyperlinks
1833 }
1834
saveXmlDrawings(QXmlStreamWriter & writer) const1835 void WorksheetPrivate::saveXmlDrawings(QXmlStreamWriter &writer) const
1836 {
1837 if (!drawing)
1838 return;
1839
1840 int idx = workbook->drawings().indexOf(drawing.data());
1841 relationships->addWorksheetRelationship(QStringLiteral("/drawing"), QStringLiteral("../drawings/drawing%1.xml").arg(idx+1));
1842
1843 writer.writeEmptyElement(QStringLiteral("drawing"));
1844 writer.writeAttribute(QStringLiteral("r:id"), QStringLiteral("rId%1").arg(relationships->count()));
1845 }
1846
splitColsInfo(int colFirst,int colLast)1847 void WorksheetPrivate::splitColsInfo(int colFirst, int colLast)
1848 {
1849 // Split current columnInfo, for example, if "A:H" has been set,
1850 // we are trying to set "B:D", there should be "A", "B:D", "E:H".
1851 // This will be more complex if we try to set "C:F" after "B:D".
1852 {
1853 QMapIterator<int, QSharedPointer<XlsxColumnInfo> > it(colsInfo);
1854 while (it.hasNext()) {
1855 it.next();
1856 QSharedPointer<XlsxColumnInfo> info = it.value();
1857 if (colFirst > info->firstColumn && colFirst <= info->lastColumn) {
1858 //split the range,
1859 QSharedPointer<XlsxColumnInfo> info2(new XlsxColumnInfo(*info));
1860 info->lastColumn = colFirst - 1;
1861 info2->firstColumn = colFirst;
1862 colsInfo.insert(colFirst, info2);
1863 for (int c = info2->firstColumn; c <= info2->lastColumn; ++c)
1864 colsInfoHelper[c] = info2;
1865
1866 break;
1867 }
1868 }
1869 }
1870 {
1871 QMapIterator<int, QSharedPointer<XlsxColumnInfo> > it(colsInfo);
1872 while (it.hasNext()) {
1873 it.next();
1874 QSharedPointer<XlsxColumnInfo> info = it.value();
1875 if (colLast >= info->firstColumn && colLast < info->lastColumn) {
1876 QSharedPointer<XlsxColumnInfo> info2(new XlsxColumnInfo(*info));
1877 info->lastColumn = colLast;
1878 info2->firstColumn = colLast + 1;
1879 colsInfo.insert(colLast + 1, info2);
1880 for (int c = info2->firstColumn; c <= info2->lastColumn; ++c)
1881 colsInfoHelper[c] = info2;
1882
1883 break;
1884 }
1885 }
1886 }
1887 }
1888
isColumnRangeValid(int colFirst,int colLast)1889 bool WorksheetPrivate::isColumnRangeValid(int colFirst, int colLast)
1890 {
1891 bool ignore_row = true;
1892 bool ignore_col = false;
1893
1894 if (colFirst > colLast)
1895 return false;
1896
1897 if (checkDimensions(1, colLast, ignore_row, ignore_col))
1898 return false;
1899 if (checkDimensions(1, colFirst, ignore_row, ignore_col))
1900 return false;
1901
1902 return true;
1903 }
1904
getColumnIndexes(int colFirst,int colLast)1905 QList<int> WorksheetPrivate ::getColumnIndexes(int colFirst, int colLast)
1906 {
1907 splitColsInfo(colFirst, colLast);
1908
1909 QList<int> nodes;
1910 nodes.append(colFirst);
1911 for (int col = colFirst; col <= colLast; ++col)
1912 {
1913 auto it = colsInfo.constFind(col);
1914 if (it != colsInfo.constEnd())
1915 {
1916 if (nodes.last() != col)
1917 nodes.append(col);
1918
1919 int nextCol = (*it)->lastColumn + 1;
1920 if (nextCol <= colLast)
1921 nodes.append(nextCol);
1922 }
1923 }
1924
1925 return nodes;
1926 }
1927
1928 /*!
1929 Sets width in characters of a \a range of columns to \a width.
1930 Returns true on success.
1931 */
setColumnWidth(const CellRange & range,double width)1932 bool Worksheet::setColumnWidth(const CellRange &range, double width)
1933 {
1934 if (!range.isValid())
1935 return false;
1936
1937 return setColumnWidth(range.firstColumn(), range.lastColumn(), width);
1938 }
1939
1940 /*!
1941 Sets format property of a \a range of columns to \a format. Columns are 1-indexed.
1942 Returns true on success.
1943 */
setColumnFormat(const CellRange & range,const Format & format)1944 bool Worksheet::setColumnFormat(const CellRange& range, const Format &format)
1945 {
1946 if (!range.isValid())
1947 return false;
1948
1949 return setColumnFormat(range.firstColumn(), range.lastColumn(), format);
1950 }
1951
1952 /*!
1953 Sets hidden property of a \a range of columns to \a hidden. Columns are 1-indexed.
1954 Hidden columns are not visible.
1955 Returns true on success.
1956 */
setColumnHidden(const CellRange & range,bool hidden)1957 bool Worksheet::setColumnHidden(const CellRange &range, bool hidden)
1958 {
1959 if (!range.isValid())
1960 return false;
1961
1962 return setColumnHidden(range.firstColumn(), range.lastColumn(), hidden);
1963 }
1964
1965 /*!
1966 Sets width in characters for columns [\a colFirst, \a colLast] to \a width.
1967 Columns are 1-indexed.
1968 Returns true on success.
1969 */
setColumnWidth(int colFirst,int colLast,double width)1970 bool Worksheet::setColumnWidth(int colFirst, int colLast, double width)
1971 {
1972 Q_D(Worksheet);
1973
1974 const QList <QSharedPointer<XlsxColumnInfo> > columnInfoList = d->getColumnInfoList(colFirst, colLast);
1975 for (const QSharedPointer<XlsxColumnInfo> &columnInfo : columnInfoList)
1976 {
1977 columnInfo->width = width;
1978 }
1979
1980 return (columnInfoList.count() > 0);
1981 }
1982
1983 /*!
1984 Sets format property of a range of columns [\a colFirst, \a colLast] to \a format.
1985 Columns are 1-indexed.
1986 Returns true on success.
1987 */
setColumnFormat(int colFirst,int colLast,const Format & format)1988 bool Worksheet::setColumnFormat(int colFirst, int colLast, const Format &format)
1989 {
1990 Q_D(Worksheet);
1991
1992 const QList <QSharedPointer<XlsxColumnInfo> > columnInfoList = d->getColumnInfoList(colFirst, colLast);
1993 for (const QSharedPointer<XlsxColumnInfo> &columnInfo : columnInfoList)
1994 columnInfo->format = format;
1995
1996 if(columnInfoList.count() > 0) {
1997 d->workbook->styles()->addXfFormat(format);
1998 return true;
1999 }
2000
2001 return false;
2002 }
2003
2004 /*!
2005 Sets hidden property of a range of columns [\a colFirst, \a colLast] to \a hidden.
2006 Columns are 1-indexed. Returns true on success.
2007 */
setColumnHidden(int colFirst,int colLast,bool hidden)2008 bool Worksheet::setColumnHidden(int colFirst, int colLast, bool hidden)
2009 {
2010 Q_D(Worksheet);
2011
2012 const QList <QSharedPointer<XlsxColumnInfo> > columnInfoList = d->getColumnInfoList(colFirst, colLast);
2013 for (const QSharedPointer<XlsxColumnInfo> &columnInfo : columnInfoList)
2014 columnInfo->hidden = hidden;
2015
2016 return (columnInfoList.count() > 0);
2017 }
2018
2019 /*!
2020 Returns width of the \a column in characters of the normal font. Columns are 1-indexed.
2021 */
columnWidth(int column)2022 double Worksheet::columnWidth(int column)
2023 {
2024 Q_D(Worksheet);
2025
2026 QList< QSharedPointer<XlsxColumnInfo> > columnInfoList = d->getColumnInfoList(column, column);
2027
2028 // [dev54]
2029 if ( columnInfoList.size() == 0 )
2030 {
2031 // column information is not found
2032 // qDebug() << "[debug]" << __FUNCTION__ << "column (info) is not found. " << column;
2033 }
2034
2035 if (columnInfoList.count() == 1)
2036 {
2037 // column information is found
2038 // qDebug() << "[debug]" << __FUNCTION__ << "column (info) is found. " << column << oneColWidth;
2039 double oneColWidth = columnInfoList.at(0)->width;
2040 bool isSetWidth = columnInfoList.at(0)->isSetWidth;
2041 if ( isSetWidth )
2042 {
2043 return oneColWidth;
2044 }
2045 }
2046
2047 // use default width
2048 double defaultColWidth = d->sheetFormatProps.defaultColWidth;
2049 return defaultColWidth;
2050 }
2051
2052 /*!
2053 Returns formatting of the \a column. Columns are 1-indexed.
2054 */
columnFormat(int column)2055 Format Worksheet::columnFormat(int column)
2056 {
2057 Q_D(Worksheet);
2058
2059 QList <QSharedPointer<XlsxColumnInfo> > columnInfoList = d->getColumnInfoList(column, column);
2060 if (columnInfoList.count() == 1)
2061 return columnInfoList.at(0)->format;
2062
2063 return Format();
2064 }
2065
2066 /*!
2067 Returns true if \a column is hidden. Columns are 1-indexed.
2068 */
isColumnHidden(int column)2069 bool Worksheet::isColumnHidden(int column)
2070 {
2071 Q_D(Worksheet);
2072
2073 QList <QSharedPointer<XlsxColumnInfo> > columnInfoList = d->getColumnInfoList(column, column);
2074 if (columnInfoList.count() == 1)
2075 return columnInfoList.at(0)->hidden;
2076
2077 return false;
2078 }
2079
2080 /*!
2081 Sets the \a height of the rows including and between \a rowFirst and \a rowLast.
2082 Row height measured in point size.
2083 Rows are 1-indexed.
2084
2085 Returns true if success.
2086 */
setRowHeight(int rowFirst,int rowLast,double height)2087 bool Worksheet::setRowHeight(int rowFirst,int rowLast, double height)
2088 {
2089 Q_D(Worksheet);
2090
2091 const QList <QSharedPointer<XlsxRowInfo> > rowInfoList = d->getRowInfoList(rowFirst,rowLast);
2092 for (const QSharedPointer<XlsxRowInfo> &rowInfo : rowInfoList) {
2093 rowInfo->height = height;
2094 rowInfo->customHeight = true;
2095 }
2096
2097 return rowInfoList.count() > 0;
2098 }
2099
2100 /*!
2101 Sets the \a format of the rows including and between \a rowFirst and \a rowLast.
2102 Rows are 1-indexed.
2103
2104 Returns true if success.
2105 */
setRowFormat(int rowFirst,int rowLast,const Format & format)2106 bool Worksheet::setRowFormat(int rowFirst,int rowLast, const Format &format)
2107 {
2108 Q_D(Worksheet);
2109
2110 const QList <QSharedPointer<XlsxRowInfo> > rowInfoList = d->getRowInfoList(rowFirst,rowLast);
2111 for (const QSharedPointer<XlsxRowInfo> &rowInfo : rowInfoList)
2112 rowInfo->format = format;
2113
2114 d->workbook->styles()->addXfFormat(format);
2115 return rowInfoList.count() > 0;
2116 }
2117
2118 /*!
2119 Sets the \a hidden proeprty of the rows including and between \a rowFirst and \a rowLast.
2120 Rows are 1-indexed. If hidden is true rows will not be visible.
2121
2122 Returns true if success.
2123 */
setRowHidden(int rowFirst,int rowLast,bool hidden)2124 bool Worksheet::setRowHidden(int rowFirst,int rowLast, bool hidden)
2125 {
2126 Q_D(Worksheet);
2127
2128 const QList <QSharedPointer<XlsxRowInfo> > rowInfoList = d->getRowInfoList(rowFirst,rowLast);
2129 for (const QSharedPointer<XlsxRowInfo> &rowInfo : rowInfoList)
2130 rowInfo->hidden = hidden;
2131
2132 return rowInfoList.count() > 0;
2133 }
2134
2135 /*!
2136 Returns height of \a row in points.
2137 */
rowHeight(int row)2138 double Worksheet::rowHeight(int row)
2139 {
2140 Q_D(Worksheet);
2141 const int min_col = d->dimension.isValid() ? d->dimension.firstColumn() : 1;
2142
2143 auto it = d->rowsInfo.constFind(row);
2144 if (d->checkDimensions(row, min_col, false, true) || it == d->rowsInfo.constEnd())
2145 {
2146 return d->sheetFormatProps.defaultRowHeight; //return default on invalid row
2147 }
2148
2149 return (*it)->height;
2150 }
2151
2152 /*!
2153 Returns format of \a row.
2154 */
rowFormat(int row)2155 Format Worksheet::rowFormat(int row)
2156 {
2157 Q_D(Worksheet);
2158 const int min_col = d->dimension.isValid() ? d->dimension.firstColumn() : 1;
2159 auto it = d->rowsInfo.constFind(row);
2160 if (d->checkDimensions(row, min_col, false, true) || it == d->rowsInfo.constEnd())
2161 return Format(); //return default on invalid row
2162
2163 return (*it)->format;
2164 }
2165
2166 /*!
2167 Returns true if \a row is hidden.
2168 */
isRowHidden(int row)2169 bool Worksheet::isRowHidden(int row)
2170 {
2171 Q_D(Worksheet);
2172 const int min_col = d->dimension.isValid() ? d->dimension.firstColumn() : 1;
2173 auto it = d->rowsInfo.constFind(row);
2174 if (d->checkDimensions(row, min_col, false, true) || it == d->rowsInfo.constEnd())
2175 return false; //return default on invalid row
2176
2177 return (*it)->hidden;
2178 }
2179
2180 /*!
2181 Groups rows from \a rowFirst to \a rowLast with the given \a collapsed.
2182
2183 Returns false if error occurs.
2184 */
groupRows(int rowFirst,int rowLast,bool collapsed)2185 bool Worksheet::groupRows(int rowFirst, int rowLast, bool collapsed)
2186 {
2187 Q_D(Worksheet);
2188
2189 for (int row=rowFirst; row<=rowLast; ++row) {
2190 auto it = d->rowsInfo.find(row);
2191 if (it != d->rowsInfo.end()) {
2192 (*it)->outlineLevel += 1;
2193 } else {
2194 QSharedPointer<XlsxRowInfo> info(new XlsxRowInfo);
2195 info->outlineLevel += 1;
2196 it = d->rowsInfo.insert(row, info);
2197 }
2198 if (collapsed)
2199 (*it)->hidden = true;
2200 }
2201 if (collapsed) {
2202 auto it = d->rowsInfo.find(rowLast+1);
2203 if (it == d->rowsInfo.end())
2204 it = d->rowsInfo.insert(rowLast+1, QSharedPointer<XlsxRowInfo>(new XlsxRowInfo));
2205 (*it)->collapsed = true;
2206 }
2207 return true;
2208 }
2209
2210 /*!
2211 \overload
2212
2213 Groups columns with the given \a range and \a collapsed.
2214 */
groupColumns(const CellRange & range,bool collapsed)2215 bool Worksheet::groupColumns(const CellRange &range, bool collapsed)
2216 {
2217 if (!range.isValid())
2218 return false;
2219
2220 return groupColumns(range.firstColumn(), range.lastColumn(), collapsed);
2221 }
2222
2223 /*!
2224 Groups columns from \a colFirst to \a colLast with the given \a collapsed.
2225 Returns false if error occurs.
2226 */
groupColumns(int colFirst,int colLast,bool collapsed)2227 bool Worksheet::groupColumns(int colFirst, int colLast, bool collapsed)
2228 {
2229 Q_D(Worksheet);
2230
2231 d->splitColsInfo(colFirst, colLast);
2232
2233 QList<int> nodes;
2234 nodes.append(colFirst);
2235 for (int col = colFirst; col <= colLast; ++col) {
2236 auto it = d->colsInfo.constFind(col);
2237 if (it != d->colsInfo.constEnd()) {
2238 if (nodes.last() != col)
2239 nodes.append(col);
2240 int nextCol = (*it)->lastColumn + 1;
2241 if (nextCol <= colLast)
2242 nodes.append(nextCol);
2243 }
2244 }
2245
2246 for (int idx = 0; idx < nodes.size(); ++idx)
2247 {
2248 int colStart = nodes[idx];
2249 auto it = d->colsInfo.constFind(colStart);
2250 if (it != d->colsInfo.constEnd())
2251 {
2252 (*it)->outlineLevel += 1;
2253 if (collapsed)
2254 (*it)->hidden = true;
2255 }
2256 else
2257 {
2258 int colEnd = (idx == nodes.size() - 1) ? colLast : nodes[idx+1] - 1;
2259 QSharedPointer<XlsxColumnInfo> info(new XlsxColumnInfo(colStart, colEnd, false));
2260 info->outlineLevel += 1;
2261 d->colsInfo.insert(colFirst, info);
2262 if (collapsed)
2263 info->hidden = true;
2264 for (int c = colStart; c <= colEnd; ++c)
2265 d->colsInfoHelper[c] = info;
2266 }
2267 }
2268
2269 if (collapsed) {
2270 int col = colLast+1;
2271 d->splitColsInfo(col, col);
2272 auto it = d->colsInfo.constFind(col);
2273 if (it != d->colsInfo.constEnd())
2274 (*it)->collapsed = true;
2275 else {
2276 QSharedPointer<XlsxColumnInfo> info(new XlsxColumnInfo(col, col, false));
2277 info->collapsed = true;
2278 d->colsInfo.insert(col, info);
2279 d->colsInfoHelper[col] = info;
2280 }
2281 }
2282
2283 return false;
2284 }
2285
2286 /*!
2287 Return the range that contains cell data.
2288 */
dimension() const2289 CellRange Worksheet::dimension() const
2290 {
2291 Q_D(const Worksheet);
2292 return d->dimension;
2293 }
2294
2295 /*
2296 Convert the height of a cell from user's units to pixels. If the
2297 height hasn't been set by the user we use the default value. If
2298 the row is hidden it has a value of zero.
2299 */
rowPixelsSize(int row) const2300 int WorksheetPrivate::rowPixelsSize(int row) const
2301 {
2302 double height;
2303 auto it = row_sizes.constFind(row);
2304 if (it != row_sizes.constEnd())
2305 height = it.value();
2306 else
2307 height = default_row_height;
2308 return static_cast<int>(4.0 / 3.0 *height);
2309 }
2310
2311 /*
2312 Convert the width of a cell from user's units to pixels. Excel rounds
2313 the column width to the nearest pixel. If the width hasn't been set
2314 by the user we use the default value. If the column is hidden it
2315 has a value of zero.
2316 */
colPixelsSize(int col) const2317 int WorksheetPrivate::colPixelsSize(int col) const
2318 {
2319 double max_digit_width = 7.0; //For Calabri 11
2320 double padding = 5.0;
2321 int pixels = 0;
2322
2323 auto it = col_sizes.constFind(col);
2324 if (it != col_sizes.constEnd()) {
2325 double width = it.value();
2326 if (width < 1)
2327 pixels = static_cast<int>(width * (max_digit_width + padding) + 0.5);
2328 else
2329 pixels = static_cast<int>(width * max_digit_width + 0.5) + padding;
2330 } else {
2331 pixels = 64;
2332 }
2333 return pixels;
2334 }
2335
loadXmlSheetData(QXmlStreamReader & reader)2336 void WorksheetPrivate::loadXmlSheetData(QXmlStreamReader &reader)
2337 {
2338 Q_Q(Worksheet);
2339
2340 Q_ASSERT(reader.name() == QLatin1String("sheetData"));
2341
2342 while (!reader.atEnd() && !(reader.name() == QLatin1String("sheetData") && reader.tokenType() == QXmlStreamReader::EndElement))
2343 {
2344 if (reader.readNextStartElement())
2345 {
2346 if (reader.name() == QLatin1String("row"))
2347 {
2348 QXmlStreamAttributes attributes = reader.attributes();
2349
2350 if (attributes.hasAttribute(QLatin1String("customFormat"))
2351 || attributes.hasAttribute(QLatin1String("customHeight"))
2352 || attributes.hasAttribute(QLatin1String("hidden"))
2353 || attributes.hasAttribute(QLatin1String("outlineLevel"))
2354 || attributes.hasAttribute(QLatin1String("collapsed")))
2355 {
2356
2357 QSharedPointer<XlsxRowInfo> info(new XlsxRowInfo);
2358 if (attributes.hasAttribute(QLatin1String("customFormat")) &&
2359 attributes.hasAttribute(QLatin1String("s")))
2360 {
2361 int idx = attributes.value(QLatin1String("s")).toString().toInt();
2362 info->format = workbook->styles()->xfFormat(idx);
2363 }
2364
2365 if (attributes.hasAttribute(QLatin1String("customHeight")))
2366 {
2367 info->customHeight = attributes.value(QLatin1String("customHeight")) == QLatin1String("1");
2368 //Row height is only specified when customHeight is set
2369 if(attributes.hasAttribute(QLatin1String("ht")))
2370 {
2371 info->height = attributes.value(QLatin1String("ht")).toString().toDouble();
2372 }
2373 }
2374
2375 //both "hidden" and "collapsed" default are false
2376 info->hidden = attributes.value(QLatin1String("hidden")) == QLatin1String("1");
2377 info->collapsed = attributes.value(QLatin1String("collapsed")) == QLatin1String("1");
2378
2379 if (attributes.hasAttribute(QLatin1String("outlineLevel")))
2380 info->outlineLevel = attributes.value(QLatin1String("outlineLevel")).toString().toInt();
2381
2382 //"r" is optional too.
2383 if (attributes.hasAttribute(QLatin1String("r")))
2384 {
2385 int row = attributes.value(QLatin1String("r")).toString().toInt();
2386 rowsInfo[row] = info;
2387 }
2388 }
2389
2390 }
2391 else if (reader.name() == QLatin1String("c")) // Cell
2392 {
2393
2394 //Cell
2395 QXmlStreamAttributes attributes = reader.attributes();
2396 QString r = attributes.value(QLatin1String("r")).toString();
2397 CellReference pos(r);
2398
2399 //get format
2400 Format format;
2401 qint32 styleIndex = -1;
2402 if (attributes.hasAttribute(QLatin1String("s"))) // Style (defined in the styles.xml file)
2403 {
2404 //"s" == style index
2405 int idx = attributes.value(QLatin1String("s")).toString().toInt();
2406 format = workbook->styles()->xfFormat(idx);
2407 styleIndex = idx;
2408 }
2409
2410 // Cell::CellType cellType = Cell::NumberType;
2411 Cell::CellType cellType = Cell::CustomType;
2412
2413 if (attributes.hasAttribute(QLatin1String("t"))) // Type
2414 {
2415 QString typeString = attributes.value(QLatin1String("t")).toString();
2416 if (typeString == QLatin1String("s")) // Shared string
2417 {
2418 cellType = Cell::SharedStringType;
2419 }
2420 else if (typeString == QLatin1String("inlineStr")) // Inline String
2421 {
2422 cellType = Cell::InlineStringType;
2423 }
2424 else if (typeString == QLatin1String("str")) // String
2425 {
2426 cellType = Cell::StringType;
2427 }
2428 else if (typeString == QLatin1String("b")) // Boolean
2429 {
2430 cellType = Cell::BooleanType;
2431 }
2432 else if (typeString == QLatin1String("e")) // Error
2433 {
2434 cellType = Cell::ErrorType;
2435 }
2436 else if (typeString == QLatin1String("d")) // Date
2437 {
2438 cellType = Cell::DateType;
2439 }
2440 else if (typeString == QLatin1String("n")) // Number
2441 {
2442 cellType = Cell::NumberType;
2443 }
2444 else
2445 {
2446 // custom type
2447 cellType = Cell::CustomType;
2448 }
2449 }
2450
2451 // [dev54] temp cell for checking datetype
2452 Cell tempCell(QVariant(), cellType, format, q, styleIndex);
2453 if ( tempCell.isDateTime() )
2454 {
2455 cellType = Cell::DateType;
2456 }
2457
2458 // create a heap of new cell
2459 QSharedPointer<Cell> cell(new Cell(QVariant(), cellType, format, q, styleIndex));
2460
2461 while (!reader.atEnd() &&
2462 !(reader.name() == QLatin1String("c") &&
2463 reader.tokenType() == QXmlStreamReader::EndElement))
2464 {
2465 if (reader.readNextStartElement())
2466 {
2467 if (reader.name() == QLatin1String("f")) // formula
2468 {
2469 CellFormula &formula = cell->d_func()->formula;
2470 formula.loadFromXml(reader);
2471 if (formula.formulaType() == CellFormula::SharedType &&
2472 !formula.formulaText().isEmpty())
2473 {
2474 int si = formula.sharedIndex();
2475 sharedFormulaMap[ si ] = formula;
2476 }
2477 }
2478 else if (reader.name() == QLatin1String("v")) // Value
2479 {
2480 QString value = reader.readElementText();
2481 if (cellType == Cell::SharedStringType)
2482 {
2483 int sst_idx = value.toInt();
2484 sharedStrings()->incRefByStringIndex(sst_idx);
2485 RichString rs = sharedStrings()->getSharedString(sst_idx);
2486 QString strPlainString = rs.toPlainString();
2487 cell->d_func()->value = strPlainString;
2488 if (rs.isRichString())
2489 cell->d_func()->richString = rs;
2490 }
2491 else if (cellType == Cell::NumberType)
2492 {
2493 cell->d_func()->value = value.toDouble();
2494 }
2495 else if (cellType == Cell::BooleanType)
2496 {
2497 cell->d_func()->value = value.toInt() ? true : false;
2498 }
2499 else if (cellType == Cell::DateType)
2500 {
2501 // [dev54] DateType
2502
2503 double dValue = value.toDouble(); // days from 1900(or 1904)
2504 bool bIsDate1904 = q->workbook()->isDate1904();
2505
2506 QVariant vDatetimeValue = datetimeFromNumber( dValue, bIsDate1904 );
2507 Q_UNUSED(vDatetimeValue);
2508 // cell->d_func()->value = vDatetimeValue;
2509 cell->d_func()->value = dValue; // dev67
2510 }
2511 else
2512 {
2513 // ELSE type
2514 cell->d_func()->value = value;
2515 }
2516
2517 }
2518 else if (reader.name() == QLatin1String("is"))
2519 {
2520 while (!reader.atEnd() &&
2521 !(reader.name() == QLatin1String("is") &&
2522 reader.tokenType() == QXmlStreamReader::EndElement))
2523 {
2524 if (reader.readNextStartElement())
2525 {
2526 //:Todo, add rich text read support
2527 if (reader.name() == QLatin1String("t"))
2528 {
2529 cell->d_func()->value = reader.readElementText();
2530 }
2531 }
2532 }
2533 }
2534 else if (reader.name() == QLatin1String("extLst"))
2535 {
2536 //skip extLst element
2537 while ( !reader.atEnd() &&
2538 !(reader.name() == QLatin1String("extLst") &&
2539 reader.tokenType() == QXmlStreamReader::EndElement))
2540 {
2541 reader.readNextStartElement();
2542 }
2543 }
2544 }
2545 }
2546
2547 cellTable[ pos.row() ][ pos.column() ] = cell;
2548
2549 }
2550 }
2551 }
2552 }
2553
loadXmlColumnsInfo(QXmlStreamReader & reader)2554 void WorksheetPrivate::loadXmlColumnsInfo(QXmlStreamReader &reader)
2555 {
2556 Q_ASSERT(reader.name() == QLatin1String("cols"));
2557
2558 while (!reader.atEnd() &&
2559 !(reader.name() == QLatin1String("cols") &&
2560 reader.tokenType() == QXmlStreamReader::EndElement))
2561 {
2562 reader.readNextStartElement();
2563 if (reader.tokenType() == QXmlStreamReader::StartElement)
2564 {
2565 if (reader.name() == QLatin1String("col"))
2566 {
2567 QSharedPointer<XlsxColumnInfo> info(new XlsxColumnInfo(0, 1, false));
2568
2569 QXmlStreamAttributes colAttrs = reader.attributes();
2570 int min = colAttrs.value(QLatin1String("min")).toString().toInt();
2571 int max = colAttrs.value(QLatin1String("max")).toString().toInt();
2572 info->firstColumn = min;
2573 info->lastColumn = max;
2574
2575 //Flag indicating that the column width for the affected column(s) is different from the
2576 // default or has been manually set
2577 if(colAttrs.hasAttribute(QLatin1String("customWidth")))
2578 {
2579 info->customWidth = colAttrs.value(QLatin1String("customWidth")) == QLatin1String("1");
2580 }
2581
2582 //Note, node may have "width" without "customWidth"
2583 // [dev54]
2584 if (colAttrs.hasAttribute(QLatin1String("width")))
2585 {
2586 double width = colAttrs.value(QLatin1String("width")).toString().toDouble();
2587 info->width = width;
2588 info->isSetWidth = true; // [dev54]
2589 }
2590
2591 info->hidden = colAttrs.value(QLatin1String("hidden")) == QLatin1String("1");
2592 info->collapsed = colAttrs.value(QLatin1String("collapsed")) == QLatin1String("1");
2593
2594 if (colAttrs.hasAttribute(QLatin1String("style")))
2595 {
2596 int idx = colAttrs.value(QLatin1String("style")).toString().toInt();
2597 info->format = workbook->styles()->xfFormat(idx);
2598 }
2599
2600 if (colAttrs.hasAttribute(QLatin1String("outlineLevel")))
2601 {
2602 info->outlineLevel = colAttrs.value(QLatin1String("outlineLevel")).toString().toInt();
2603 }
2604
2605 // qDebug() << "[debug] " << __FUNCTION__ << min << max << info->width << hasWidth;
2606
2607 colsInfo.insert(min, info);
2608 for (int col = min ; col <= max ; ++col)
2609 {
2610 colsInfoHelper[col] = info;
2611 }
2612 }
2613 }
2614 }
2615 }
2616
loadXmlMergeCells(QXmlStreamReader & reader)2617 void WorksheetPrivate::loadXmlMergeCells(QXmlStreamReader &reader)
2618 {
2619 // issue #173 https://github.com/QtExcel/QXlsx/issues/173
2620
2621 Q_ASSERT(reader.name() == QLatin1String("mergeCells"));
2622
2623 QXmlStreamAttributes attributes = reader.attributes();
2624
2625 bool isCount = attributes.hasAttribute(QLatin1String("count"));
2626 int count = 0;
2627 if ( !isCount )
2628 {
2629 qWarning("no count");
2630 }
2631 else
2632 {
2633 count = attributes.value(QLatin1String("count")).toString().toInt();
2634 }
2635
2636 while ( !reader.atEnd() &&
2637 !(reader.name() == QLatin1String("mergeCells") &&
2638 reader.tokenType() == QXmlStreamReader::EndElement) )
2639 {
2640 reader.readNextStartElement();
2641 if (reader.tokenType() == QXmlStreamReader::StartElement)
2642 {
2643 if (reader.name() == QLatin1String("mergeCell"))
2644 {
2645 QXmlStreamAttributes attrs = reader.attributes();
2646 QString rangeStr = attrs.value(QLatin1String("ref")).toString();
2647 merges.append(CellRange(rangeStr));
2648 }
2649 }
2650 }
2651
2652 if (isCount)
2653 {
2654 int mergesSize = merges.size();
2655 if ( mergesSize != count )
2656 {
2657 qWarning("read merge cells error");
2658 }
2659 }
2660
2661 }
2662
loadXmlDataValidations(QXmlStreamReader & reader)2663 void WorksheetPrivate::loadXmlDataValidations(QXmlStreamReader &reader)
2664 {
2665 Q_ASSERT(reader.name() == QLatin1String("dataValidations"));
2666 QXmlStreamAttributes attributes = reader.attributes();
2667 int count = attributes.value(QLatin1String("count")).toString().toInt();
2668
2669 while (!reader.atEnd() && !(reader.name() == QLatin1String("dataValidations")
2670 && reader.tokenType() == QXmlStreamReader::EndElement)) {
2671 reader.readNextStartElement();
2672 if (reader.tokenType() == QXmlStreamReader::StartElement
2673 && reader.name() == QLatin1String("dataValidation")) {
2674 dataValidationsList.append(DataValidation::loadFromXml(reader));
2675 }
2676 }
2677
2678 if (dataValidationsList.size() != count)
2679 qDebug("read data validation error");
2680 }
2681
loadXmlSheetViews(QXmlStreamReader & reader)2682 void WorksheetPrivate::loadXmlSheetViews(QXmlStreamReader &reader)
2683 {
2684 Q_ASSERT(reader.name() == QLatin1String("sheetViews"));
2685
2686 while (!reader.atEnd() && !(reader.name() == QLatin1String("sheetViews")
2687 && reader.tokenType() == QXmlStreamReader::EndElement)) {
2688 reader.readNextStartElement();
2689 if (reader.tokenType() == QXmlStreamReader::StartElement && reader.name() == QLatin1String("sheetView")) {
2690 QXmlStreamAttributes attrs = reader.attributes();
2691 //default false
2692 windowProtection = attrs.value(QLatin1String("windowProtection")) == QLatin1String("1");
2693 showFormulas = attrs.value(QLatin1String("showFormulas")) == QLatin1String("1");
2694 rightToLeft = attrs.value(QLatin1String("rightToLeft")) == QLatin1String("1");
2695 tabSelected = attrs.value(QLatin1String("tabSelected")) == QLatin1String("1");
2696 //default true
2697 showGridLines = attrs.value(QLatin1String("showGridLines")) != QLatin1String("0");
2698 showRowColHeaders = attrs.value(QLatin1String("showRowColHeaders")) != QLatin1String("0");
2699 showZeros = attrs.value(QLatin1String("showZeros")) != QLatin1String("0");
2700 showRuler = attrs.value(QLatin1String("showRuler")) != QLatin1String("0");
2701 showOutlineSymbols = attrs.value(QLatin1String("showOutlineSymbols")) != QLatin1String("0");
2702 showWhiteSpace = attrs.value(QLatin1String("showWhiteSpace")) != QLatin1String("0");
2703 }
2704 }
2705 }
2706
loadXmlSheetFormatProps(QXmlStreamReader & reader)2707 void WorksheetPrivate::loadXmlSheetFormatProps(QXmlStreamReader &reader)
2708 {
2709 Q_ASSERT(reader.name() == QLatin1String("sheetFormatPr"));
2710
2711 const QXmlStreamAttributes attributes = reader.attributes();
2712 XlsxSheetFormatProps formatProps;
2713 bool isSetWidth = false;
2714
2715 // Retain default values
2716 for (const QXmlStreamAttribute &attrib : attributes)
2717 {
2718 if(attrib.name() == QLatin1String("baseColWidth") )
2719 {
2720 formatProps.baseColWidth = attrib.value().toString().toInt();
2721 }
2722 else if(attrib.name() == QLatin1String("customHeight"))
2723 {
2724 formatProps.customHeight = attrib.value() == QLatin1String("1");
2725 }
2726 else if(attrib.name() == QLatin1String("defaultColWidth"))
2727 {
2728 double dDefaultColWidth = attrib.value().toString().toDouble();
2729 formatProps.defaultColWidth = dDefaultColWidth;
2730 isSetWidth = true;
2731 }
2732 else if(attrib.name() == QLatin1String("defaultRowHeight"))
2733 {
2734 formatProps.defaultRowHeight = attrib.value().toString().toDouble();
2735 }
2736 else if(attrib.name() == QLatin1String("outlineLevelCol"))
2737 {
2738 formatProps.outlineLevelCol = attrib.value().toString().toInt();
2739 }
2740 else if(attrib.name() == QLatin1String("outlineLevelRow"))
2741 {
2742 formatProps.outlineLevelRow = attrib.value().toString().toInt();
2743 }
2744 else if(attrib.name() == QLatin1String("thickBottom"))
2745 {
2746 formatProps.thickBottom = attrib.value() == QLatin1String("1");
2747 }
2748 else if(attrib.name() == QLatin1String("thickTop"))
2749 {
2750 formatProps.thickTop = attrib.value() == QLatin1String("1");
2751 }
2752 else if(attrib.name() == QLatin1String("zeroHeight"))
2753 {
2754 formatProps.zeroHeight = attrib.value() == QLatin1String("1");
2755 }
2756 }
2757
2758 // if (formatProps.defaultColWidth == 0.0)
2759 if ( !isSetWidth )
2760 {
2761 //not set
2762 double dCalcWidth = WorksheetPrivate::calculateColWidth(formatProps.baseColWidth);
2763 formatProps.defaultColWidth = dCalcWidth;
2764 }
2765
2766 // [dev54]
2767 // Where is code of setting 'formatProps'?
2768 this->sheetFormatProps = formatProps;
2769
2770 }
calculateColWidth(int characters)2771 double WorksheetPrivate::calculateColWidth(int characters)
2772 {
2773 // //!Todo
2774 //Take normal style' font maximum width and add padding and margin pixels
2775 // return characters + 0.5;
2776 return characters;
2777 }
2778
loadXmlHyperlinks(QXmlStreamReader & reader)2779 void WorksheetPrivate::loadXmlHyperlinks(QXmlStreamReader &reader)
2780 {
2781 Q_ASSERT(reader.name() == QLatin1String("hyperlinks"));
2782
2783 while (!reader.atEnd() && !(reader.name() == QLatin1String("hyperlinks")
2784 && reader.tokenType() == QXmlStreamReader::EndElement)) {
2785 reader.readNextStartElement();
2786 if (reader.tokenType() == QXmlStreamReader::StartElement && reader.name() == QLatin1String("hyperlink")) {
2787 QXmlStreamAttributes attrs = reader.attributes();
2788 CellReference pos(attrs.value(QLatin1String("ref")).toString());
2789 if (pos.isValid()) { //Valid
2790 QSharedPointer<XlsxHyperlinkData> link(new XlsxHyperlinkData);
2791 link->display = attrs.value(QLatin1String("display")).toString();
2792 link->tooltip = attrs.value(QLatin1String("tooltip")).toString();
2793 link->location = attrs.value(QLatin1String("location")).toString();
2794
2795 if (attrs.hasAttribute(QLatin1String("r:id"))) {
2796 link->linkType = XlsxHyperlinkData::External;
2797 XlsxRelationship ship = relationships->getRelationshipById(attrs.value(QLatin1String("r:id")).toString());
2798 link->target = ship.target;
2799 } else {
2800 link->linkType = XlsxHyperlinkData::Internal;
2801 }
2802
2803 urlTable[pos.row()][pos.column()] = link;
2804 }
2805 }
2806 }
2807 }
2808
getColumnInfoList(int colFirst,int colLast)2809 QList <QSharedPointer<XlsxColumnInfo> > WorksheetPrivate::getColumnInfoList(int colFirst, int colLast)
2810 {
2811 QList <QSharedPointer<XlsxColumnInfo> > columnsInfoList;
2812 if (isColumnRangeValid(colFirst,colLast))
2813 {
2814 QList<int> nodes = getColumnIndexes(colFirst, colLast);
2815
2816 for (int idx = 0; idx < nodes.size(); ++idx)
2817 {
2818 int colStart = nodes[idx];
2819 auto it = colsInfo.constFind(colStart);
2820 if (it != colsInfo.constEnd())
2821 {
2822 columnsInfoList.append(*it);
2823 }
2824 else
2825 {
2826 int colEnd = (idx == nodes.size() - 1) ? colLast : nodes[idx+1] - 1;
2827 QSharedPointer<XlsxColumnInfo> info(new XlsxColumnInfo(colStart, colEnd, false));
2828 colsInfo.insert(colFirst, info);
2829 columnsInfoList.append(info);
2830 for (int c = colStart; c <= colEnd; ++c)
2831 {
2832 colsInfoHelper[c] = info;
2833 }
2834 }
2835 }
2836 }
2837
2838 return columnsInfoList;
2839 }
2840
getRowInfoList(int rowFirst,int rowLast)2841 QList <QSharedPointer<XlsxRowInfo> > WorksheetPrivate::getRowInfoList(int rowFirst, int rowLast)
2842 {
2843 QList <QSharedPointer<XlsxRowInfo> > rowInfoList;
2844
2845 int min_col = dimension.firstColumn() < 1 ? 1 : dimension.firstColumn();
2846
2847 for(int row = rowFirst; row <= rowLast; ++row) {
2848 if (checkDimensions(row, min_col, false, true))
2849 continue;
2850
2851 QSharedPointer<XlsxRowInfo> rowInfo;
2852 if ((rowsInfo[row]).isNull()){
2853 rowsInfo[row] = QSharedPointer<XlsxRowInfo>(new XlsxRowInfo());
2854 }
2855 rowInfoList.append(rowsInfo[row]);
2856 }
2857
2858 return rowInfoList;
2859 }
2860
loadFromXmlFile(QIODevice * device)2861 bool Worksheet::loadFromXmlFile(QIODevice *device)
2862 {
2863 Q_D(Worksheet);
2864
2865 QXmlStreamReader reader(device);
2866 while (!reader.atEnd())
2867 {
2868 reader.readNextStartElement();
2869 if (reader.tokenType() == QXmlStreamReader::StartElement)
2870 {
2871 if (reader.name() == QLatin1String("dimension"))
2872 {
2873 QXmlStreamAttributes attributes = reader.attributes();
2874 QString range = attributes.value(QLatin1String("ref")).toString();
2875 d->dimension = CellRange(range);
2876 }
2877 else if (reader.name() == QLatin1String("sheetViews"))
2878 {
2879 d->loadXmlSheetViews(reader);
2880 }
2881 else if (reader.name() == QLatin1String("sheetFormatPr"))
2882 {
2883 d->loadXmlSheetFormatProps(reader);
2884 }
2885 else if (reader.name() == QLatin1String("cols"))
2886 {
2887 d->loadXmlColumnsInfo(reader);
2888 }
2889 else if (reader.name() == QLatin1String("sheetData"))
2890 {
2891 d->loadXmlSheetData(reader);
2892 }
2893 else if (reader.name() == QLatin1String("mergeCells"))
2894 {
2895 d->loadXmlMergeCells(reader);
2896 }
2897 else if (reader.name() == QLatin1String("dataValidations"))
2898 {
2899 d->loadXmlDataValidations(reader);
2900 }
2901 else if (reader.name() == QLatin1String("conditionalFormatting"))
2902 {
2903 ConditionalFormatting cf;
2904 cf.loadFromXml(reader, workbook()->styles());
2905 d->conditionalFormattingList.append(cf);
2906 }
2907 else if (reader.name() == QLatin1String("hyperlinks"))
2908 {
2909 d->loadXmlHyperlinks(reader);
2910 }
2911 else if(reader.name() == QLatin1String("pageSetup"))
2912 {
2913 QXmlStreamAttributes attributes = reader.attributes();
2914
2915 d->PpaperSize = attributes.value(QLatin1String("paperSize")).toString().trimmed();
2916 d->Pscale = attributes.value(QLatin1String("scale")).toString().trimmed();
2917 d->PfirstPageNumber = attributes.value(QLatin1String("firstPageNumber")).toString().trimmed();
2918 d->Porientation = attributes.value(QLatin1String("orientation")).toString().trimmed();
2919 d->PuseFirstPageNumber = attributes.value(QLatin1String("useFirstPageNumber")).toString().trimmed();
2920 d->PhorizontalDpi = attributes.value(QLatin1String("horizontalDpi")).toString().trimmed();
2921 d->PverticalDpi = attributes.value(QLatin1String("verticalDpi")).toString().trimmed();
2922 d->Prid = attributes.value(QLatin1String("r:id")).toString().trimmed();
2923 d->Pcopies = attributes.value(QLatin1String("copies")).toString().trimmed();
2924 }
2925 else if(reader.name() == QLatin1String("pageMargins"))
2926 {
2927 QXmlStreamAttributes attributes = reader.attributes();
2928
2929 d->PMfooter= attributes.value(QLatin1String("footer")).toString().trimmed();
2930 d->PMheader = attributes.value(QLatin1String("header")).toString().trimmed();
2931 d->PMbotton = attributes.value(QLatin1String("bottom")).toString().trimmed();
2932 d->PMtop = attributes.value(QLatin1String("top")).toString().trimmed();
2933 d->PMright = attributes.value(QLatin1String("right")).toString().trimmed();
2934 d->PMleft = attributes.value(QLatin1String("left")).toString().trimmed();
2935 }
2936 else if(reader.name() == QLatin1String("headerFooter"))
2937 {
2938 // dev40
2939 while (reader.readNextStartElement())
2940 {
2941 if (reader.name() == QLatin1String("oddHeader"))
2942 d->ModdHeader = reader.readElementText();
2943
2944 if (reader.name() == QLatin1String("oddFooter"))
2945 d->MoodFooter = reader.readElementText();
2946 }
2947 }
2948 else if (reader.name() == QLatin1String("drawing"))
2949 {
2950 QString rId = reader.attributes().value(QStringLiteral("r:id")).toString();
2951 QString name = d->relationships->getRelationshipById(rId).target;
2952
2953 QString str = *( splitPath(filePath()).begin() );
2954 str = str + QLatin1String("/");
2955 str = str + name;
2956 QString path = QDir::cleanPath( str );
2957
2958 d->drawing = QSharedPointer<Drawing>(new Drawing(this, F_LoadFromExists));
2959 d->drawing->setFilePath(path);
2960 }
2961 else if (reader.name() == QLatin1String("extLst"))
2962 {
2963 //Todo: add extLst support
2964 while ( !reader.atEnd() &&
2965 !(reader.name() == QLatin1String("extLst") &&
2966 reader.tokenType() == QXmlStreamReader::EndElement))
2967 {
2968 reader.readNextStartElement();
2969 }
2970 }
2971 }
2972 }
2973
2974 d->validateDimension();
2975 return true;
2976 }
2977
2978 /*
2979 * Documents imported from Google Docs does not contain dimension data.
2980 */
validateDimension()2981 void WorksheetPrivate::validateDimension()
2982 {
2983 if (dimension.isValid() || cellTable.isEmpty())
2984 return;
2985
2986 const auto firstRow = cellTable.constBegin().key();
2987
2988 const auto lastRow = (--cellTable.constEnd()).key();
2989
2990 int firstColumn = -1;
2991 int lastColumn = -1;
2992
2993 for ( auto&& it = cellTable.constBegin()
2994 ; it != cellTable.constEnd()
2995 ; ++it )
2996 {
2997 Q_ASSERT(!it.value().isEmpty());
2998
2999 if (firstColumn == -1 || it.value().constBegin().key() < firstColumn)
3000 firstColumn = it.value().constBegin().key();
3001
3002 if (lastColumn == -1 || (--it.value().constEnd()).key() > lastColumn)
3003 {
3004 lastColumn = (--it.value().constEnd()).key();
3005 }
3006
3007 }
3008
3009 CellRange cr(firstRow, firstColumn, lastRow, lastColumn);
3010
3011 if (cr.isValid())
3012 dimension = cr;
3013 }
3014
3015 /*!
3016 * \internal
3017 * Unit test can use this member to get sharedString object.
3018 */
sharedStrings() const3019 SharedStrings *WorksheetPrivate::sharedStrings() const
3020 {
3021 return workbook->sharedStrings();
3022 }
3023
getFullCells(int * maxRow,int * maxCol)3024 QVector<CellLocation> Worksheet::getFullCells(int* maxRow, int* maxCol)
3025 {
3026 Q_D(const Worksheet);
3027
3028 // return values
3029 (*maxRow) = -1;
3030 (*maxCol) = -1;
3031 QVector<CellLocation> ret;
3032
3033 // QString privateName = d->name; // name of sheet (not object type)
3034 // qDebug() << privateName ;
3035
3036 if ( d->type == AbstractSheet::ST_WorkSheet )
3037 {
3038 // use current sheet
3039 }
3040 else if ( d->type == AbstractSheet::ST_ChartSheet )
3041 {
3042 return ret;
3043 }
3044 else
3045 {
3046 qWarning("unsupported sheet type.");
3047 Q_ASSERT(false);
3048 return ret;
3049 }
3050
3051 QMapIterator< int, QMap< int, QSharedPointer<Cell> > > _it( d->cellTable );
3052
3053 while ( _it.hasNext() )
3054 {
3055 _it.next();
3056
3057 int keyI = _it.key(); // key (cell row)
3058 QMapIterator<int, QSharedPointer<Cell> > _iit( _it.value() ); // value
3059
3060 while ( _iit.hasNext() )
3061 {
3062 _iit.next();
3063
3064 int keyII = _iit.key(); // key (cell column)
3065 QSharedPointer<Cell> ptrCell = _iit.value(); // value
3066
3067 CellLocation cl;
3068
3069 cl.row = keyI;
3070 if ( keyI > (*maxRow) )
3071 {
3072 (*maxRow) = keyI;
3073 }
3074
3075 cl.col = keyII;
3076 if ( keyII > (*maxCol) )
3077 {
3078 (*maxCol) = keyII;
3079 }
3080
3081 cl.cell = ptrCell;
3082
3083 ret.push_back( cl );
3084 }
3085 }
3086
3087 return ret;
3088 }
3089
3090 QT_END_NAMESPACE_XLSX
3091