Use the Excel.Application in C++Builder(bcc64)[JAPAN]

cppcolors | Embarcadero RAD Studio Delphi C++Builder Blogs

Author: Pixelot12345

How to use the Excel.Application in C++Builder. Target device is Win64.

Use the ComObj, call in CreateOleObject (“Excel.Application”).

Including ComObj is required.

In C++Builder it is below.

////
#include "System.Win.ComObj.hpp"

1 2	//// #include "System.Win.ComObj.hpp"

Import from CSV file to Excel file.

////
constexpr wchar_t const def_filename[] 		= L"C:EmbarcaderoExcel_testBook1.xlsx";
constexpr wchar_t const csv_file[]       	= L"C:EmbarcaderoExcel_testcsv.csv";

////

constexpr wchar_t const def_filename[] = L"C:EmbarcaderoExcel_testBook1.xlsx";

constexpr wchar_t const csv_file[] = L"C:EmbarcaderoExcel_testcsv.csv";

We will implement this process.

Prepare the TButton.

TButton is one in a simple form.

Next is the TButton OnClick event.

/////
constexpr wchar_t const def_excel_name[]	= L"Excel.Application";
void __fastcall TForm1::Button1Click(TObject *Sender)
{
	Variant ExcelApp = CreateOleObject(def_excel_name);
	try
	{
		Variant excel_book;
		Variant excel_sheet;
		UnicodeString excel_filename{def_filename};
		try
		{
			ExcelApp.OlePropertySet("Visible", true);   //setup Show Excel.
			excel_book	= ExcelApp.OlePropertyGet("Workbooks").
				OleFunction("Open",static_cast＜WideString＞(excel_filename));   	 		//Open Excel File.

			excel_sheet	= excel_book.OlePropertyGet("WorkSheets").
				OlePropertyGet("Item", static_cast＜WideString＞("Sheet1"));      		//Select Sheet Name or Number.
			/*
			excel_sheet.OlePropertyGet("Cells").
				OlePropertyGet("Item",1,2).OlePropertySet("Value",(WideString)L"文字"); //Write String Cell.

			cell_set(excel_sheet.OlePropertyGet("Cells"), 1, 1, "a");                   //One Cell SetValue.
			*/
			load_csv_toCell(csv_file,excel_sheet.OlePropertyGet("Cells"));
			excel_book.OleProcedure("Save");
		}
		__finally
		{
			excel_book  = Unassigned();
			excel_sheet = Unassigned();
		}
		ExcelApp.OleProcedure("Quit");  //Quit Excel.
	}
	__finally
	{
		ExcelApp = Unassigned();
	}
}

/////

constexpr wchar_t const def_excel_name[] = L"Excel.Application";

void __fastcall TForm1::Button1Click(TObject *Sender)

{

Variant ExcelApp = CreateOleObject(def_excel_name);

try

{

Variant excel_book;

Variant excel_sheet;

UnicodeString excel_filename{def_filename};

try

{

ExcelApp.OlePropertySet("Visible", true); //setup Show Excel.

excel_book = ExcelApp.OlePropertyGet("Workbooks").

OleFunction("Open",static_cast＜WideString＞(excel_filename)); //Open Excel File.

excel_sheet = excel_book.OlePropertyGet("WorkSheets").

OlePropertyGet("Item", static_cast＜WideString＞("Sheet1")); //Select Sheet Name or Number.

excel_sheet.OlePropertyGet("Cells").

OlePropertyGet("Item",1,2).OlePropertySet("Value",(WideString)L"文字"); //Write String Cell.

cell_set(excel_sheet.OlePropertyGet("Cells"), 1, 1, "a"); //One Cell SetValue.

load_csv_toCell(csv_file,excel_sheet.OlePropertyGet("Cells"));

excel_book.OleProcedure("Save");

}

__finally

{

excel_book = Unassigned();

excel_sheet = Unassigned();

}

ExcelApp.OleProcedure("Quit"); //Quit Excel.

}

__finally

{

ExcelApp = Unassigned();

}

Sheet name is “Sheet 1”.

Next we will create two methods.

////
void __fastcall TForm1::cell_set(System::Variant cells, int row_, int col, String value_)
{
	try
	{
		//Write to one of the cell.
		cells.OlePropertyGet("Item",row_,col)
			.OlePropertySet("Value",static_cast＜WideString＞(value_));
	}
	catch(...){}
}

////

void __fastcall TForm1::cell_set(System::Variant cells, int row_, int col, String value_)

{

try

{

//Write to one of the cell.

cells.OlePropertyGet("Item",row_,col)

.OlePropertySet("Value",static_cast＜WideString＞(value_));

}

catch(...){}

}

Read the file with TStringList.And, you write to the specified cell.

////

void __fastcall TForm1::load_csv_toCell(String csv_filename, System::Variant cells)
{
	auto csv_ = {std::make_unique＜TStringList＞()};
	std::wstringstream cols_;
	std::wstring value_ = L"";
	int column_count_{0};
	try
	{
		(*csv_.begin())->LoadFromFile(csv_filename);    //Read file.
		for (int i=0; i < (*csv_.begin())->Count; i++)  //Retrieve one row.
		{
			//Separate one line.
			column_count_   = 0;    //Column Count = 0.
			cols_.clear();          //Clear Rows.
			cols_.str(StringReplace((*csv_.begin())->Strings[i],L""", "",TReplaceFlags()＜＜rfReplaceAll).w_str());
			while (std::getline(cols_, value_, L',')){
				column_count_++;
				cell_set(cells,i + 1,column_count_,value_.c_str());
			};
		}
	}
	catch(...){}
}

////

void __fastcall TForm1::load_csv_toCell(String csv_filename, System::Variant cells)

{

auto csv_ = {std::make_unique＜TStringList＞()};

std::wstringstream cols_;

std::wstring value_ = L"";

int column_count_{0};

try

{

(*csv_.begin())->LoadFromFile(csv_filename); //Read file.

for (int i=0; i < (*csv_.begin())->Count; i++) //Retrieve one row.

{

//Separate one line.

column_count_ = 0; //Column Count = 0.

cols_.clear(); //Clear Rows.

cols_.str(StringReplace((*csv_.begin())->Strings[i],L""", "",TReplaceFlags()＜＜rfReplaceAll).w_str());

while (std::getline(cols_, value_, L',')){

column_count_++;

cell_set(cells,i + 1,column_count_,value_.c_str());

};

}

catch(...){}

}

Write the imported CSV file to Excel.

Reduce development time and get to market faster with RAD Studio, Delphi, or C++Builder.
Design. Code. Compile. Deploy.
Start Free Trial Upgrade Today

Free Delphi Community Edition Free C++Builder Community Edition

2 Comments

Emilio

January 23, 2025 at 9:19 am

Where do you close the excel object crated with CreateOleObject?

Reply

Ian Barker

January 23, 2025 at 9:30 am

The CreateOleObject doesn’t actually create an object, as such, it returns an interface to the object and this is reference-counted.

The line; ExcelApp = Unassigned(); sets the OLE object to nil (unassigned) which correctly releases the interface.


Emilio

January 23, 2025 at 9:19 am

Where do you close the excel object crated with CreateOleObject?
- Reply
  
  Ian Barker
  
  January 23, 2025 at 9:30 am
  
  The CreateOleObject doesn’t actually create an object, as such, it returns an interface to the object and this is reference-counted.
  
  The line; ExcelApp = Unassigned(); sets the OLE object to nil (unassigned) which correctly releases the interface.

Use the Excel.Application in C++Builder(bcc64)[JAPAN]

2 Comments

Leave a ReplyCancel reply

Search

Something Fresh

Who Uses Delphi? The Silent Success Behind Astronauts, Theme Parks, Satellites, And A Multi-billion Ultimate Payday!

Instruction Sets in C++Builder 12.3!

New in 12.3: Scripts for Migration from InterBase Express to FireDAC

Popular Posts

RAD Studio 12.2 Athens Inline Patch 1 Available

Announcing the Availability of RAD Studio 12.2 Athens

New in 12.3: Scripts for Migration from InterBase Express to FireDAC

AI-Powered Smart CodeInsight in RAD Studio 12.3

Announcing the Availability of RAD Studio 12.3 Athens

RAD Studio 12.2 Athens Inline Patch 1 Available

Announcing the Availability of RAD Studio 12.2 Athens

New in 12.3: Scripts for Migration from InterBase Express to FireDAC

AI-Powered Smart CodeInsight in RAD Studio 12.3

Categories

Popular From News

New in 10.3.2: C++17 for Win64 - target all Windows with the C++17 Clang compiler

Delphi 12 And C++Builder 12 Community Editions Released!

We've Updated The HUGE Delphi Anniversary “Innovation Timeline” Infographic. Grab it Now!

Embarcadero InterBase 2020 Update 6 Released!

3 x 12 VCL Enhancements in Delphi 12

C++Builder @ stackoverflow

Delphi @ stackoverflow

InterBase @ stackoverflow

Categories

Useful Links

Follow us

Use the Excel.Application in C++Builder(bcc64)[JAPAN]

2 Comments

Leave a ReplyCancel reply

Join Our Global Developer Community

Search

Something Fresh

Popular Posts

Categories

Popular From News

Categories

Useful Links

Follow us