LibXL

• Home
• Download
• Documentation
• Purchase
• Contact

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));
    }

    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 merging blocks
            int rowFirst, rowLast, colFirst, colLast;
            if(srcSheet->getMerge(row, col, &rowFirst, &rowLast, &colFirst, &colLast))
            {              
                dstSheet->setMerge(rowFirst, rowLast, colFirst, colLast);              
            }

            // 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;
}