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

• Download demo - 524.33 KB
• Download source code - 509 KB

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.

1^st Step

Create the Interop.Excel.dll.

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

2^nd Step

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

3^rd 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); 
… 

4^th 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; 
}

5^th 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; 
} 

6^th 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

• 5^th February, 2007: Initial post