Copying cells with formats to other workbook in C++

Home Documentation Writing Reading Pictures1 Pictures2 Formulas DateTime SheetByName
Merging Grouping InsertRowCol NumFormats Formats Fonts Buffer1 Buffer2 Copying TopNFilter
Sorting StringFilter NumberFilter FilterByValues Protection Replacing RichString BeginWith
ColorScale OpRule AltRows

Copying cells with formats to other workbook

This example shows how to copy formats and cells from one workbook to other one.

#include "libxl.h"
#include <map>

using namespace libxl;

int main()
{
    Book* srcBook = xlCreateBook();
    
    bool b = srcBook->load(L"data.xls");

    Sheet* srcSheet = srcBook->getSheet(0);

    Book* dstBook = xlCreateBook();
        
    Sheet* dstSheet = dstBook->addSheet(L"my");
    
    // setting column widths
    for(int col = srcSheet->firstCol(); col < srcSheet->lastCol(); ++col)
    {
        dstSheet->setCol(col, col, srcSheet->colWidth(col), 0, srcSheet->colHidden(col));
    }

    // copying merging blocks
    for (int i = 0; i < srcSheet->mergeSize(); ++i)
    {
        int rowFirst, rowLast, colFirst, colLast;
        if (srcSheet->merge(i, &rowFirst, &rowLast, &colFirst, &colLast))
        {
            dstSheet->setMerge(rowFirst, rowLast, colFirst, colLast);
        }
    }

    std::map<Format*, Format*> formats;    
    
    for(int row = srcSheet->firstRow(); row < srcSheet->lastRow(); ++row)
    {
        // setting row heights
        dstSheet->setRow(row, srcSheet->rowHeight(row), 0, srcSheet->rowHidden(row));

        for(int col = srcSheet->firstCol(); col < srcSheet->lastCol(); ++col)
        {
            // copying formats
            Format *srcFormat, *dstFormat;

            srcFormat = srcSheet->cellFormat(row, col);
            if(!srcFormat) continue;
        
            // checking formats
            if(formats.count(srcFormat) == 0)
            {                    
                // format is not found, creating it in the output file
                dstFormat = dstBook->addFormat(srcFormat);
                formats[srcFormat] = dstFormat;
            }
            else
            {
                // format was already created
                dstFormat = formats[srcFormat];
            }
                    
            // copying cell's values
            CellType ct = srcSheet->cellType(row, col);
            switch(ct)
            {
                case CELLTYPE_NUMBER:
                {
                    double value = srcSheet->readNum(row, col, &srcFormat);                         
                    dstSheet->writeNum(row, col, value, dstFormat);
                    break;
                }
                case CELLTYPE_BOOLEAN:
                {
                    bool value = srcSheet->readBool(row, col, &srcFormat);
                    dstSheet->writeBool(row, col, value, dstFormat);
                    break;
                }
                case CELLTYPE_STRING:
                {
                    const wchar_t* s = srcSheet->readStr(row, col, &srcFormat);                
                    dstSheet->writeStr(row, col, s, dstFormat);
                    break;
                }
                case CELLTYPE_BLANK:                    
                {
                    srcSheet->readBlank(row, col, &srcFormat);
                    dstSheet->writeBlank(row, col, dstFormat);            
                    break;
                }
            }                   
        }
    }
   
    dstBook->save(L"out.xls");

    dstBook->release();
    
    srcBook->release();

    return 0;
}