Copy sheet of one workbook to another

[feschenko-nelya]

I need to copy a sheet from one document to another.

I've found two ways to do it:
1- write a similar method of Worksheet::copy,
2- upload and download xml data of worksheet: upload sheet from workbook A and download its xml-data to worksheet of workbook B.

1.

Method Worksheet::copy works almost fine inside one workbook, except rows and columns sizes (and maybe other properties, because I saw commented code there).
I take this method as a foundation for new method Worksheet::clone. It makes copy of current sheet with parent of another workbook.

I create a new worksheet with workbook B parent.
Copy all properties of workbook A sheet to it.

As a result all data was copied, but failed next:

• rows sizes,
• cell formats.

main.cpp

QXlsx::Document docA("docA.xlsx");
if (!docA.load())
{
        qFatal("File not loaded.");
        return EXIT_FAILURE;
}

QXlsx::Document docB;

auto sheetClone = docA.currentWorksheet()->clone(docB.workbook());
docB.addSheet(sheetClone);

docB.saveAs("docB.xlsx);

return EXIT_SUCCESS;

Worksheet.cpp

Worksheet *Worksheet::clone(Workbook *dstWb)
{
    Q_D(const Worksheet);

    auto newSheet                = new Worksheet(d_func()->name, 0, dstWb, F_LoadFromExists);
    WorksheetPrivate *dNewSheet = newSheet->d_func();

    dNewSheet->sheetFormatProps = d->sheetFormatProps;

    qInfo() << "cols info: " << dNewSheet->colsInfo.size() << d->colsInfo.size();
    qInfo() << "rows info: " << dNewSheet->rowsInfo.size() << d->rowsInfo.size();
    qInfo() << "rows sizes: " << dNewSheet->row_sizes.size() << d->row_sizes.size();

    dNewSheet->colsInfo = d->colsInfo;
    dNewSheet->colsInfoHelper = d->colsInfoHelper;
    dNewSheet->rowsInfo = d->rowsInfo;
    dNewSheet->row_spans = d->row_spans;
    dNewSheet->row_sizes = d->row_sizes;
    dNewSheet->col_sizes = d->col_sizes;
    dNewSheet->dimension = d->dimension;

    for (auto it = d->cellTable.cells.begin(); it != d->cellTable.cells.end(); ++it) {
        int row = it.key();
        // newSheet->setRowHeight(row, row, rowHeight(row));

        for (auto it2 = it.value().begin(); it2 != it.value().end(); ++it2) {
            int col = it2.key();

            auto srcCell = it2.value();
            auto srcCellFormat = srcCell->format();

            if (srcCell->hasFormula())
            {
                newSheet->writeFormula(row, col, srcCell->formula(), srcCellFormat);
            }
            else
            {
                switch (srcCell->cellType())
                {
                case Cell::BooleanType:
                    newSheet->writeBool(row, col, srcCell->value().toBool(), srcCellFormat);
                    break;
                case Cell::InlineStringType:
                    newSheet->writeInlineString(row, col, srcCell->value().toString(), srcCellFormat);
                    break;
                case Cell::StringType:
                case Cell::SharedStringType:
                    newSheet->writeString(row, col, srcCell->value().toString(), srcCellFormat);
                    break;
                case Cell::CustomType:
                    if (!srcCell->value().isValid() || srcCell->value().isNull())
                    {
                        newSheet->writeBlank(row, col, srcCellFormat);
                    }
                    else
                    {
                        newSheet->write(row, col, srcCell->value(), srcCellFormat);
                    }
                    break;
                default:
                    break;
                }
            }
        }
    }

dNewSheet->merges = d->merges;

return newSheet;
}

All data has copied correct. But cells formats, rows sizes missed.

Workbook A

Workbook B

And it looks like that it has random effect. Each time cells' format changes.

Workbook B

And I have a message from Excel that sheet lost data when workbook is opened.

2.

The second way I've tried is to save sheet's data of workbook A to xml and load it in sheet of workbook B. It is the smallest and convenient way, but it doesnt work too.

    QXlsx::Document docA("docA.xlsx");
    if (!docA.load())
    {
        qFatal("File not loaded.");
        return EXIT_FAILURE;
    }

    QXlsx::Document docB;

    docB.addSheet();
    docB.currentWorksheet()->loadFromXmlData(docA.currentWorksheet()->saveToXmlData());

    docB.saveAs("docB.xlsx");

    return EXIT_SUCCESS;

What I'm doing wrong? Any advice?
Can you help to finish one of these ways, please?

[feschenko-nelya]

By the way,

    QXlsx::Document docA("docA.xlsx");
    if (!docA.load())
    {
        qFatal("File not loaded.");
        return EXIT_FAILURE;
    }

    QXlsx::Document docB;

    auto sheetData = docA.currentWorksheet()->saveToXmlData();
    auto sheet = docA.workbook()->addSheet("sheet");
    sheet->loadFromXmlData(sheetData);
    docA.saveAs("docA-2.xlsx");

Excellent works for copy one sheet inside one workbook.

[david980508]

For the first way.
It works good inside one workbook because they use the same style setting.You may try to modify the original sheet's style and the copied one will change as well.

For the second way.
xml just copy datas,not all styles,so styles will be losted.

More
Some of styles storaged by work book.

If you want to copy the sheet completely,you must copy worksheet values of
**rowsInfo**&**colsInfo**&**colsInfoHelper** because they only store pointers
and copy **dataValidationsList**&**conditionalFormattingList**&**row_spans row_sizes**&**col_sizes**they stored vlaues,and workbook values of **styles** **theme**because they only store pointers.

Here is one way to solve,but remember still something lost,like decimal point,but most of format can be saved

Worksheet *Worksheet::clone(const QString &distName, int distId, Workbook *workbook)
{
    Q_D(const Worksheet);

    auto newSheet = (Worksheet*)workbook->insertSheet(distId,distName);
    WorksheetPrivate *sheet_d = newSheet->d_func();

    // rowsInfo deep copy
    for (auto pair = d->rowsInfo.constBegin();pair != d->rowsInfo.constEnd();pair++) {
        if (pair.value()) {
            sheet_d->rowsInfo[pair.key()] = std::make_shared<XlsxRowInfo>(*pair.value());
        }
    }

    // colsInfo deep copy
    for (auto pair = d->colsInfo.constBegin();pair != d->colsInfo.constEnd();pair++) {
        if (pair.value()) {
            sheet_d->colsInfo[pair.key()] = std::make_shared<XlsxColumnInfo>(*pair.value());
        }
    }

    // colsInfoHelper deep copy
    for (auto pair = d->colsInfoHelper.constBegin();pair != d->colsInfoHelper.constEnd();pair++) {
        if (pair.value()) {
            sheet_d->colsInfoHelper[pair.key()] = std::make_shared<XlsxColumnInfo>(*pair.value());
        }
    }
    sheet_d->row_spans = d->row_spans;
    sheet_d->row_sizes = d->row_sizes;
    sheet_d->col_sizes = d->col_sizes;
    sheet_d->dimension = d->dimension;

    for (auto it = d->cellTable.cells.begin(); it != d->cellTable.cells.end(); ++it) {
        int row = it.key();
        // newSheet->setRowHeight(row, row, rowHeight(row));

        for (auto it2 = it.value().begin(); it2 != it.value().end(); ++it2) {
            int col = it2.key();

            auto srcCell = it2.value();
            auto srcCellFormat = srcCell->format();

            if (srcCell->hasFormula())
            {
                newSheet->writeFormula(row, col, srcCell->formula(), srcCellFormat);
            }
            else
            {
                switch (srcCell->cellType())
                {
                case Cell::BooleanType:
                    newSheet->writeBool(row, col, srcCell->value().toBool(), srcCellFormat);
                    break;
                case Cell::InlineStringType:
                    newSheet->writeInlineString(row, col, srcCell->value().toString(), srcCellFormat);
                    break;
                case Cell::StringType:
                case Cell::SharedStringType:
                    newSheet->writeString(row, col, srcCell->value().toString(), srcCellFormat);
                    break;
                case Cell::CustomType:
                    if (!srcCell->value().isValid() || srcCell->value().isNull())
                    {
                        newSheet->writeBlank(row, col, srcCellFormat);
                    }
                    else
                    {
                        newSheet->write(row, col, srcCell->value(), srcCellFormat);
                    }
                    break;
                default:
                    break;
                }
            }
        }
    }

    // for (auto it = d->cellTable.cells.begin(); it != d->cellTable.cells.end(); ++it) {
    //     auto cell           = std::make_shared<Cell>(it.value().get());
    //     cell->d_ptr->parent = sheet;

    //     if (cell->cellType() == Cell::SharedStringType)
    //         d->workbook->sharedStrings()->addSharedString(cell->d_ptr->richString);

    //     sheet_d->cellTable.setValue(CellTable::row(it.key()), CellTable::column(it.key()), cell);
    // }

    sheet_d->merges = d->merges;
    sheet_d->comments = d->comments;

    sheet_d->PpaperSize = d->PpaperSize;
    sheet_d->Pscale = d->Pscale;
    sheet_d->PfirstPageNumber = d->PfirstPageNumber;
    sheet_d->Porientation = d->Porientation;
    sheet_d->PuseFirstPageNumber = d->PuseFirstPageNumber;
    sheet_d->PhorizontalDpi = d->PhorizontalDpi;
    sheet_d->PverticalDpi = d->PverticalDpi;
    sheet_d->Prid = d->Prid;
    sheet_d->Pcopies = d->Pcopies;

    sheet_d->PMheader = d->PMheader;
    sheet_d->PMfooter = d->PMfooter;
    sheet_d->PMtop = d->PMtop;
    sheet_d->PMbotton = d->PMbotton;
    sheet_d->PMleft = d->PMleft;
    sheet_d->PMright = d->PMright;

    sheet_d->MoodFooter = d->MoodFooter;
    sheet_d->ModdHeader = d->ModdHeader;
    sheet_d->MoodalignWithMargins = d->MoodalignWithMargins;

    sheet_d->sheetFormatProps = d->sheetFormatProps;

    sheet_d->windowProtection = d->windowProtection;
    sheet_d->showFormulas = d->showFormulas;
    sheet_d->showGridLines = d->showGridLines;
    sheet_d->showRowColHeaders = d->showRowColHeaders;
    sheet_d->showZeros = d->showZeros;
    sheet_d->rightToLeft = d->rightToLeft;
    sheet_d->tabSelected = d->tabSelected;
    sheet_d->showRuler = d->showRuler;
    sheet_d->showOutlineSymbols = d->showOutlineSymbols;
    sheet_d->showWhiteSpace = d->showWhiteSpace;

    sheet_d->urlPattern = d->urlPattern;

    sheet_d->dataValidationsList = d->dataValidationsList;
    sheet_d->conditionalFormattingList = d->conditionalFormattingList;

    sheet_d->sharedFormulaMap = d->sharedFormulaMap;
    //deep copy shared value
    // urlTable deep copy
    QHash<int, std::shared_ptr<XlsxHyperlinkData>> rowCopy;
    for (auto rowPair = d->urlTable.constBegin();rowPair != d->urlTable.constEnd();rowPair++) {
        for (auto colPair = rowPair.value().constBegin();colPair != rowPair.value().constEnd();colPair++){
            rowCopy[colPair.key()] = std::make_shared<XlsxHyperlinkData>(*colPair.value());
        }
        sheet_d->urlTable[rowPair.key()] = rowCopy;
    }
    WorkbookPrivate* mBookPri = this->workbook()->d_func();
    WorkbookPrivate* wBookPri = workbook->d_func();
    wBookPri->styles = std::make_shared<Styles>(*mBookPri->styles);
    wBookPri->theme = std::make_shared<Theme>(*mBookPri->theme);
    return newSheet;
}

enjoy