Click here to Skip to main content
Click here to Skip to main content

Class Library (DLL) to Automate Excel in C++

, 5 Feb 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
Class Library (DLL) to automate Excel in C++

Introduction

I have been using some samples that I found on CodeProject. Now it's my turn to contribute something. In this small project, I am automating Excel with .NET C++ CLR.

1st Step

Create the Interop.Excel.dll.

C:\Program Files\Microsoft Office\OFFICE11>tlbimp excel.exe /out:Interop.Excel.dll

2nd Step

The DLL Class Library: Create a new class library project. In the Project/Properties Add Reference Interop.Excel.dll.

3rd Step

To process the Excel Automation, I have created the TakeCareExcel class. This class will initiate the Excel application, and open the chosen file that gets the worksheet data.

public ref class TakeCareEXCEL 
{ 
public: 
TakeCareEXCEL(void); 
TakeCareEXCEL(bool visible); 
public: 
void IniciaExcel(void); 
String ^OpenFile(String ^filename, String ^passwd); 
void CloseFile(void); 
void GetExcelSheets(void); 
bool FindExcelWorksheet(String ^ worksheetname); 
array<String^>^ GetRange(String ^ range); 
String^ GetRangeS(String ^ range); 
String^ GetRangeS(String ^ range,ERowInfo e_works); 
… 

4th Step

I am going to show the GetRangeS method. This uses the Excel::Range to get the data from the Excel file, converts it to XML using the CreateXML class.

String^ TakeCareEXCEL::GetRangeS(String ^ range,ERowInfo e_works) 
{ 
Excel::Range ^workingRangeCells = excelWorksheet->
Range::get(range,Type::Missing); 

array<Object^,2> ^arrRetCells = 
safe_cast<array<Object^,2>^> (workingRangeCells->Cells->Value2); 
CreateXML cMakeXML; 
String^ strXMLRetValue; 
switch (e_works) 
{ 
case ERowInfo::eNone: 
{ 
strXMLRetValue=cMakeXML.ConvertToXML(arrRetCells); 
break; 
} 
case ERowInfo::eUseFirstRowHasTags: 
{ 
strXMLRetValue=cMakeXML.ConvertToXMLSI(arrRetCells); 
break; 
} 
case ERowInfo::eIgnoreFirstRow: 
{ 
array<Object^,2>^ arrCellsNoHeader = 
gcnew array<Object^,2>((arrRetCells->Length/
            arrRetCells->GetUpperBound(1))-1,
            arrRetCells->GetUpperBound(1)); 
Array::ConstrainedCopy( arrRetCells, 
arrRetCells->GetUpperBound(1)+1, 
arrCellsNoHeader, 
0, 
arrRetCells->Length-arrRetCells->GetUpperBound(1)); 
arrRetCells = nullptr; 
strXMLRetValue=cMakeXML.ConvertToXML(arrCellsNoHeader); 
break; 
} 
default: 
{ 
throw gcnew Exception(L"Invalid ERowInfo value"); 
break; 
} 
} 
return strXMLRetValue; 
}

5th Step

The CreateXML class:

String ^CreateXML::ConvertToXMLSI(System::Array ^ valor) 
{ 
String ^xmlString; 
String ^xmlStringheader; 
StringWriter^ stringWriter = gcnew StringWriter(); 
XmlWriterSettings^ settings = gcnew XmlWriterSettings(); 
settings->OmitXmlDeclaration = false; 
settings->ConformanceLevel = ConformanceLevel::Document; 
settings->NewLineOnAttributes = true; 
settings->Indent = true; 
settings->IndentChars = L"\t"; 
settings->Encoding = Encoding::Unicode; 
XmlWriter^ writer = XmlWriter::Create(stringWriter, settings); 
writer->WriteStartElement("bk", "workbook", "urn:books"); 
int index=0; 
for(int i = valor->GetLowerBound(0);i<=valor->GetUpperBound(0);i++) 
{ 
writer->WriteStartElement("elem"+i); 
for (int j = valor->GetLowerBound(1);j<=valor->GetUpperBound(1);j++) 
{ 
xmlStringheader=(System::String^)valor->
        GetValue(1,j)->ToString()->
        Replace(L' ',L'_'); 
if(valor->GetValue(i,j)==nullptr) 
{ 
writer->WriteElementString(xmlStringheader,L""); 
} 
else 
{ 
writer->WriteElementString(xmlStringheader, 
    (System::String^)valor->GetValue(i,j)->ToString()); 
} 
index++; 
} 
writer->WriteEndElement(); 
} 
writer->WriteEndElement(); 
writer->Flush(); 
xmlString = stringWriter->ToString(); 
return xmlString; 
} 

6th Step

To use the DLL, I created a simple CLR console Application.

Go to the project properties and Add reference for the WEDLL.dll created before:

#include "stdafx.h" 
using namespace System; 
using namespace WEDLL; 
int main(array<System::String ^> ^args) 
{ 
WEDLL::TakeCareEXCEL ^excel = gcnew WEDLL::TakeCareEXCEL(); 
String ^result; 
String ^myXML; 
excel->IniciaExcel(); 
result=excel->OpenFile(L"c:\\exceltable.xls",L""); 
if (result->CompareTo(L"OK")==0) 
{ 
excel->GetExcelSheets(); 
if(excel->FindExcelWorksheet(L"Prices")) 
{ 
myXML=excel->GetRangeS(L"A1:C1:C40:A2",
WEDLL::ERowInfo::eUseFirstRowHasTags); 
Console::Write( "\t{0}", myXML ); 
excel->CloseFile(); 
} 
} 
Console::WriteLine(L"Hello World"); 
return 0; 
} 

Have fun!

History

  • 5th February, 2007: Initial post 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Antonio Pedro Dinis
Web Developer
Portugal Portugal
i started programming in 1996 when i got in the Technical Institute of Coimbra Portugal - ISEC
now i am programming in C++ for the Telecom Industry.

Comments and Discussions

 
General[ANN] Libexcel library for writing xls-files (no OLE automation) Pinmemberchipmunk15-Aug-08 4:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141216.1 | Last Updated 5 Feb 2007
Article Copyright 2007 by Antonio Pedro Dinis
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid