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

• Download source code - 524.3 Kb
• Download source code - 509 Kb

Introduction

I have been using some samples that I have found in the codeproject now it's my turn to put in something. In this small project I am automating Excel with .NET C++ CLR.

1º Step

Create the Interop.Excel.dll

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

2º Step

The DLL Class Library

Create a new class library project

In the Project/Properties Add Reference Interop.Excel.dll

3º Step

To process the Excel Automation I have created the TakeCareExcel class, this class will initiate the Excel application, and open the chosen file 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º Step

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º Step

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º Step

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