2.2.4.12.14 Datasheet::ImportExcel

Description

Import an Excel sheet (97-2003 *.xls format) by replacing existing data

Syntax

int ImportExcel( LPCSTR lpcszFile, int nSheet, XLSReadCntrl * pst )

Parameters

lpcszFile
[input] fullpath filename to an existing Excel file
nSheet
[input] 0-offset index of the Excel sheet to import
pst
[input] option on import

Return

0 if no error, otherwise <0 error codes, can one of EXCEL_ERR_* enumeration;

Examples

EX1

#include <ocGrid.h>
//run by
//dlgfile g:=*.xls;excel_import fname$;
void excel_import(string strFile, bool bCellFormats = true, bool bAutoSize = false)
{
    vector<string> vsSheets;
    int nn = og_get_excel_sheet_names(strFile, &vsSheets);
    if(nn <= 0)
        return; // not a valid Excel file
    //import to active book
    WorksheetPage wp = Project.Pages();
    if(!wp)
        return;

    ExcelImportOptions stR;
    stR.xlsctrl.m_dwRXC = RXC_VALUE;
    if(bCellFormats)
        stR.xlsctrl.m_dwRXC |= RXC_STYLE;
    
    stR.xlsctrl.m_nMaxNumEmptyColumnsBeyondLastData = 0;
    
    for(int ii = 0; ii < vsSheets.GetSize(); ii++)
    {
        Worksheet wks = wp.Layers(vsSheets[ii]);
        if(!wks)
        {
            int nSht = page_find_empty_sheet(wp);
            if(nSht < 0)
                nSht = wp.AddLayer();
            wks = wp.Layers(nSht);
            wks.SetName(vsSheets[ii]);
        }
        int nErr = wks.ImportExcel(strFile, ii, &stR);
        if(nErr < 0)
        {
            printf("import failed when trying to read %s\n", vsSheets[ii]);
            return;
        }
        if(bAutoSize)
            autosize_rowcol(wks);
    }
}

Remark

To import Excel sheet with *.xlsx format, use Excel COM.

See Also

Header to Include

origin.h