BorlandTalk.com

Sudesh · Guest

Hi:
so this is the corcect newsgroup
anybody has examples about working with MS Excel with cppb 5.0?

basically i am looking at
1. create/ open a new excel file
2. format cells
3. transfer data from & to spreadsheet
4. save/ close excel file

any solid examples are appreciated.

Thanks,
Sudesh

Alexandre Turgeon · Guest

Here is a class I wrote to communicate with excel. All calls to these class
function must be in a try / catch block to handle errors.

class TExcelFile
{
private:
Variant c_vExcel; // excel application
Variant c_vWorkbooks; // workbooks collection (all files opened)
Variant c_vWorkbook; // active workbook (one of the opened files)
Variant c_vWorksheets; // all worksheets of a workbook
Variant c_vWorksheet; // active worksheet
AnsiString c_asFileName;

public:
// all functions listed here...
};

__fastcall TExcelFile::TExcelFile(AnsiString p_asFileName)
{
// Function that creates an Excel application object and
// assigns the workbooks collection to c_vWorkbooks class variable
// p_asFileName: file name you wish to open, pass an empty string to
create a new file

try
{
c_vExcel = Variant::CreateObject("Excel.Application");

if(p_asFileName!= "")
c_vExcel.OlePropertySet("Visible", false);
else
c_vExcel.OlePropertySet("Visible", true);
}
catch(Exception *e)
{
throw Exception("Unable to open excel file");
}

c_asFileName = p_asFileName;

try
{
c_vWorkbooks = c_vExcel.OlePropertyGet("Workbooks");

if(p_asFileName!= "")
c_vWorkbook = c_vWorkbooks.OleFunction("Open", c_asFileName);
else
{
c_vWorkbook = c_vWorkbooks.OleFunction("Add");
ActivateSheet("");
}
}
catch(Exception *e)
{
throw Exception("File '" + c_asFichier + "'" not found");
}
}
//--------------------------------------------------------------------------
-
void TExcelFile::ActivateSheet(AnsiString p_asSheetName)
{
// Function that activates a worksheet, to be able to read from / write
to
// p_asSheetName: worksheet's name, pass an empty string to activate the
first sheet

try
{
c_vWorksheets = c_vWorkbook.OlePropertyGet("Worksheets");
if(p_asSheetName!= "")
c_vWorksheet = c_vWorksheets.OlePropertyGet("Item",
p_asSheetName);
else
{
// for Excel french version
p_asSheetName= "Feuil1";
try
{
c_vWorksheet = c_vWorksheets.OlePropertyGet("Item",
p_asSheetName);
}
catch(Exception *e)
{
p_asSheetName= "Sheet1";
c_vWorksheet = c_vWorksheets.OlePropertyGet("Item",
p_asSheetName);
}
}
}
catch(Exception *e)
{
throw Exception("Unable to activate worksheet '" +
p_asSheetName+ "' from '" + c_asFileName+ "' file";
}
}
//--------------------------------------------------------------------------
-
AnsiString TExcelFile::ReadValue(int p_iRow , int p_iColumn) // WroteValue
explained here. Extra parameter: AnsiString p_asValue
{
// Function that returns the text of a cell
// p_iRow : row
// p_iColumn: column

AnsiString asValue= "";
Variant vCell;

try
{
vCell = c_vWorksheet.OlePropertyGet("Cells");
vCell = vCell.OlePropertyGet("Item", p_iRow , p_iColumn);

// to read the value
asValue = vCell.OlePropertyGet("Value");

// to write a value
vCell.OlePropertySet("Value", p_asValue);

vCell = Unassigned;
}
catch(Exception *e)
{
vCell = Unassigned;
throw Exception("Unable to read cell '" + AnsiString(p_iRow) + "," +
AnsiString(p_iColumn) + "' from file'" + c_asFileName+ "'";
}
return asValue;
}
//--------------------------------------------------------------------------
-
void TExcelFile::Save()
{
// Function that saves modifications

try
{
c_vWorkbook.OleProcedure("Save");
}
catch (Exception *e)
{
throw Exception("Unable to save file '" + c_asFileName + "'");
}
}
//--------------------------------------------------------------------------
-

To format cells, go in excel, record a new macro and do the formatting you
wish. Go see the generated code and try to figure out how to translate it
to Borland using function like

Variant vSelection = c_vWorksheet.OlePropertyGet("Range", "A1:E1");
vSelection.OlePropertySet("HorizontalAlignment", xlRightAlignment); //
constant defined in header file, see in excel macro code to get value

vFont = vSelection.OlePropertyGet("Font");

vFont.OlePropertySet("Size", 16);
vFont.OlePropertySet("Bold", true);
vFont.OlePropertySet("Italic", false);
vFont.OlePropertySet("Underline", true);

Functions you need to work around are
OlePropertyGet(property name);
OlePropertySet(property name,value) ;
OleProcedure(procedure name); // like Save, Open, SaveAs, Quit

I mostly wrote these functions by trying on and on until I got it.

Good luck
Alex

"Sudesh" <sudeshc (AT) vfemail (DOT) net> a écrit dans le message de
news:407e7183 (AT) newsgroups (DOT) borland.com...

Alex Bakaev [TeamB] · Guest

Sudesh wrote:

Sudesh · Guest

Thanks a million Alex. will try this.
Sudesh

"Alexandre Turgeon" <aturgeon (AT) slb (DOT) com> wrote

Sudesh · Guest

HI Alex:
Could you pl. tell me what should be search line when you say "Search this
group (using google)"?
I do this search in google.com correct??
I would like to see some more examples.

Also are there example of handling MS Access Database?

Thanks
Sudesh

"Alex Bakaev [TeamB]" <zxtt (AT) att (DOT) net> wrote

Sudesh · Guest

ok got it.
pl. forget this post.
Thanks,
Sudesh

"Sudesh" <sudeshc (AT) vfemail (DOT) net> wrote