CODEWITHSUNDEEP
c++excel
Fei
Fei

Reputation: 1061

How to write data into an Excel file in C++

I am programming with Microsoft Visual studio 2012 C++. My professor asks me to put the result of my program into an excel file instead of a text file. Because it is easier for others to understand. Is there some interface I can use in my C++ program to directly generate an excel file and put the data in it?

Upvotes: 5

Views: 60846

Answers (7)

Brecht Sanders
Brecht Sanders

Reputation: 7287

There is a portable C/C++ library called XLSX I/O that can write to .xlsx files, without even having Excel installed. See: https://github.com/brechtsanders/xlsxio

Upvotes: 2

siddse7en
siddse7en

Reputation: 111

I am late to the party but I tried few of the above solutions and this is what seems to be working well for me. I have taken inspiration from other sites too.

First piece was to use MS Component Object Model (COM). Refer this paper - Excel C++

#import "C:\Program Files (x86)\Common Files\microsoft shared\OFFICE14\mso.dll"  \
rename_namespace("Office2003") \
rename("RGB","RGBmso") \
rename("DocumentProperties","DocumentPropertiesmso")
#import "C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.olb"  \
rename_namespace("VBE6")
#pragma warning (disable:4278)
#import "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" \
exclude("IFont", "IPicture") \
rename("RGB","RGBxl") \
rename("DialogBox", "DlgBoxxl")

Next create Excel pointers for Excel::_ApplicationPtr, Excel::_WorkbookPtr, and Excel::_WorksheetPtr

Initialize your dll using AfxInitExtensionModule()

Create a pointer to Excel Range - Excel::RangePtr. This is what is used to access data at cell level.

Expose APIs to your client code for reading and writing. This is a good guide to do that https://github.com/jmcnamara/libxlsxwriter CSpreadSheet

For performance POV, I was able to write ~33k records in 155 seconds.

Upvotes: 1

SmallSoft
SmallSoft

Reputation: 728

On Windows you can use ODBC, which allow working with Excel sheets as regular database tables. It only deals with data though, not formatting, diagrams etc. Standard ODBC driver supports only xls files; to create xlsx file Microsoft Access 2010+ Database Engine Redistributable must be installed.

Example:

#include <stdio.h>
#include <tchar.h>
#include <locale.h>
#include <Windows.h>
#include <sqlext.h>

WCHAR szDSN[] = L"Driver={Microsoft Excel Driver (*.xls)};DSN='';CREATE_DB=\"C:\\test\\newfile.xls\";DBQ=C:\\test\\newfile.xls;READONLY=0;";

BOOL ExecuteSql(HDBC hDbc, LPWSTR query){

    RETCODE rc;
    HSTMT hStmt;
    WCHAR bufstate[10]=L"";
    WCHAR buferr[1024]=L"";
    SQLINTEGER i;
    SQLSMALLINT cch;
    BOOL result;

    wprintf(L">%s\n", query);

    /* Prepare SQL query */
    rc = SQLAllocStmt(hDbc,&hStmt);

    if(!SQL_SUCCEEDED(rc)){
        wprintf(L"SQLAllocStmt failed\n");
        return FALSE;
    }

    rc = SQLPrepare(hStmt, query, SQL_NTS);   

    if(!SQL_SUCCEEDED(rc)){
        wprintf(L"SQLPrepare failed\n");
        SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
        return FALSE;
    }

    /* Excecute the query */
    rc = SQLExecute(hStmt); 

    if (SQL_SUCCEEDED(rc)) {
        wprintf(L"SQL Success\n");
        result = TRUE;
    }
    else{       
        SQLGetDiagRec(SQL_HANDLE_STMT,hStmt,1,bufstate,&i,buferr,sizeof(buferr)/sizeof(buferr[0]),&cch);
        wprintf(L"SQL Error. Code: %d; Message: %s\n",i,buferr);    
        result = FALSE;
    }

    SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
    return result;
}

int _tmain(int argc, _TCHAR* argv[])
{    
setlocale(LC_ALL,"Russian");
HENV    hEnv;
HDBC    hDbc;

/* ODBC API return status */
RETCODE rc;

int     iConnStrLength2Ptr;
WCHAR    szConnStrOut[256];

/* Allocate an environment handle */
rc = SQLAllocEnv(&hEnv);
/* Allocate a connection handle */
rc = SQLAllocConnect(hEnv, &hDbc);

/* Connect to the database */
rc = SQLDriverConnect(hDbc, NULL, (WCHAR*)szDSN, 
    SQL_NTS, (WCHAR*)szConnStrOut, 
    255, (SQLSMALLINT*)&iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);

if (SQL_SUCCEEDED(rc)) 
{
    wprintf(L"Successfully connected to database. Data source name: \n  %s\n", 
        szConnStrOut);  

    ExecuteSql(hDbc,L"CREATE TABLE [Test] ([Name] TEXT, [Surname] TEXT)");  
    ExecuteSql(hDbc,L"INSERT INTO [Test] VALUES ('John','Smith')");    
}
else
{
    wprintf(L"Couldn't connect to %s.\n",szDSN);
}

/* Disconnect and free up allocated handles */
SQLDisconnect(hDbc);
SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
SQLFreeHandle(SQL_HANDLE_ENV, hEnv);

getchar();
return 0;
}

Upvotes: 2

Nicolay77
Nicolay77

Reputation: 2154

I have been using this library in Linux:

https://github.com/jmcnamara/libxlsxwriter.git

It works well, and of course, it is fast.

The 'use csv' advice stops working as soon as you need more than one sheet in the file, as it is my case.

Upvotes: 3

Jo&#227;o Augusto
Jo&#227;o Augusto

Reputation: 2305

If you don't want to use a third party library or automation, and you need some kind of formatting (colors, fonts, etc...) you can simply create a open office xml. http://en.wikipedia.org/wiki/Microsoft_Office_2003_XML_formats

Upvotes: 0

Bathsheba
Bathsheba

Reputation: 234665

Would your professor accept a csv file? This is a Character Separated Value; often a comma is used as the delimiter although Excel will accept tab characters too. Excel can load these things automatically and splits the input into separate rows and columns. I suspect that's what the professor is after.

Otherwise, using the Component Object Model (COM) for Excel is another route. But that's far more involved.

Upvotes: 3

rodrigo
rodrigo

Reputation: 98348

Modern EXCEL versions can read a variety of formats, not only the native EXCEL format, and some of them are easier to produce than others.

You can produce native EXCEL files using automation, but that is difficult, cumbersome and fragile (sometimes it stops working without a reason).

If you simply want to fill a few cells the easiest way is to write a CSV file.

If you want more control on the format or layout of your sheets you can use the Excel 2003 XML format. Just create a template of the file as you like, and use it to create your own: it is plain XML, relatively simple to understand.

Upvotes: 1

Related Questions