Write Data Into Excel Sheet

[auzziez]

Hi,
I need to automate an Excel sheet. The requirement is that I need to read data from various CSV Files, maipulate them and place the results into specific cells in an Excel Sheet. This excel sheet is already prepared with lots of text. Only specific columns are left blank and which need to be filled through the new code. I am comforatable with reading data from CSV (as this has been implemented by one of my colleague). But I am not sure of how to write the data into an Excel Workbook. This excel workbook has two worksheets with specific names. So I will have to be able to place data into specific sheet as per requirement.

Note:
1. The system where I will be running this Pascal code does NOT have Microsoft Office(Excel) installed in it. Should it still be possible to achieve the required automation?

2. The excel sheet has some formatting which needs to be preserved.

I am quite new to Pascal so please bare with me. I might not be able to get you on the first instance itself.

[LizR]

To achieve proper automation and to use excel spreadsheets it would require excel.

[auzziez]

Hi,
Actually the scenario is that I'll have to run the code for Automation at one of my test site(which has no excel). Once the code is able to perform the automation i'll just copy this excel sheet(completely filled by the automation process) to my local system(which has Office suite). Still is it necessary to have excel at my test site?... I will never open the excel sheet at the site itself.

[LizR]

if you're populating the spreadsheet, the only thing thats going to open it to allow automation of filling it in, will be excel. So yes

[auzziez]

ok..thanks for the info.....i'll first get excel installed onto the PC and then carry on forward....

[auzziez]

and i am pretty comfortable with Java coding.... since pascal is quite new to me.... can i write the population code in java and call the class file in my pascal code?

[LizR]

only if your java code can be run from the equivelent of a command line then you can just call that from pascal.

[auzziez]

i think that should surely be possible(running my java code from a command line)..... could you please help me how to go abt this technique?

[LizR]

Look up shellexec

[auzziez]

hi...just for testing purpose i inserted the following line in one of my procedures:

ShellExecute(Handle, 'open', 'C:\Throughput.csv', nil, nil, SW_SHOWNORMAL);

but it gave error that "Undeclared identifier Handle".
I suppose this Handle refers to some form. How can I call ShellExec from a procedure?

[LizR]

Was it in a form? or was that a console app?

[auzziez]

hi...i got that working....just replaced "Handle" with "0"(zero)...and it could open the file....it was not in a form... it was in an application....

[LizR]

ok, so it was a console application - and no that doesnt have a handle, all forms in a windows application have a handle.

[jsosnowski]

I have a question for LizR here. I have read and written into Excel spreadsheets using an ADO table and Clientdataset setup. It is my thinking that this would note require Excel to work with the Excel file.

The set-up for doing this takes some knowledge of ADO databases to work with it, but it is possible to access (not a Microsoft Office pun) the spreadsheet represented within a DBGrid in an independent Delphi program.

[LizR]

It would normally need excel to have the drivers for excel.

[auzziez]

Hi,

That ShellExecute thing worked...but now in one of the functions(delphi/pascal) i need to connect to excel. Currently i am running on my PC which has Excel2003. But when i run the following code....it gives a runtime exception(EAccessViolation) at:
"AdoConnection1.Connected:=False;"

Function UpdateReport : Boolean;
begin
strConn:='Provider=Microsoft.Jet.OLEDB.4.0;' +
'Data Source= ' + 'C:\original_multiplaytrafficendtoend.xls' + ';' +
'Extended Properties=Excel 8.0;';

AdoConnection1.Connected:=False;
AdoConnection1.ConnectionString:=strConn;
try
AdoConnection1.Open;
AdoConnection1.GetTableNames(tableList,True);
except
ShowMessage('Unable to connect to Excel, make sure the workbook exist!');
raise;
end;
end;

[delphiman]

Hi, I need to automate an Excel sheet. The requirement is that I need to read data from various CSV Files, maipulate them and place the results into specific cells in an Excel Sheet. This excel sheet is already prepared with lots of text. Only specific columns are left blank and which need to be filled through the new code. I am comforatable with reading data from CSV (as this has been implemented by one of my colleague). But I am not sure of how to write the data into an Excel Workbook. This excel workbook has two worksheets with specific names. So I will have to be able to place data into specific sheet as per requirement.

Note: 1. The system where I will be running this Pascal code does NOT have Microsoft Office(Excel) installed in it. Should it still be possible to achieve the required automation?

2. The excel sheet has some formatting which needs to be preserved.

I am quite new to Pascal so please bare with me. I might not be able to get you on the first instance itself.

1.
You need to accumulate the "From" data into a PivotTable wherein the fields correspond with your already prepared spreadsheet.
2.
When you use the procedure below the result will be an XL table called "Book1".
3.
Save that to OVERWRITE an INTERFACE [XL] table which you have already prepared and to which your exisitng spread-sheet relates.

You will probably have to experiment with the following to suit your needs.

NOTE: This procedure is dependant upon your having MS-Excel but with some juggling you could probably acheive the same result with another spread-sheet.

procedure frmYourForm.btnSprdSheetClick(Sender: TObject);
var
AsAtDate : TDate;
AsAtYear, AsAtMonth, AsAtDay : Word;
RangeE: Excel2000.Range;
I, Row: Integer;
Bookmark: TBookmarkStr;
begin
AsAtDate := tblUsrTfrAsAtDate.value;
DecodeDate (AsAtDate, AsAtYear, AsAtMonth, AsAtDay);
begin
with qryPivotTable do
// Load Details from your FROM table into the PivotTable as follows
PopulatePivotTable; // You need to create this procedure
// create and show
ExcelApplication1.Visible [0] := True;
ExcelApplication1.Workbooks.Add (NULL, 0);
// fill is the first row with field titles
RangeE := ExcelApplication1.ActiveCell;
for I := 0 to qryPivotTable.Fields.Count - 1 do
begin
RangeE.Value := qryPivotTable.Fields[I].DisplayLabel;
RangeE := RangeE.Next;
end;
// add field data in following rows
qryPivotTable.DisableControls;
try
Bookmark := qryPivotTable.Bookmark;
try
qryPivotTable.First;
Row := 2;
while not qryPivotTable.EOF do
begin
RangeE := ExcelApplication1.Range ['A' + IntToStr (Row),
'A' + IntToStr (Row)];
for I := 0 to qryPivotTable.Fields.Count - 1 do
begin
RangeE.Value := qryPivotTable.Fields [I].AsString;
RangeE := RangeE.Next;
end;
qryPivotTable.Next;
Inc (Row);
end;
finally
qryPivotTable.Bookmark := Bookmark;
end;
finally
qryPivotTable.EnableControls;
// format the section
RangeE := ExcelApplication1.Range ['A1', 'E' + IntToStr (Row - 1)];
RangeE.AutoFormat (3, NULL, NULL, NULL, NULL, NULL, NULL);
end;
end;
end;
end;

initialization
CoInitialize (nil);

[delphiman]

SORRY! I have just noticed that you are using Excell 2003. I suspect the suggestion I gave you will only work with Exell 2007

[LizR]

So you had to wake up a 6 week old post?

[delphiman]

So you had to wake up a 6 week old post?

I did not "wake up" to the post. In fact I hardly ever come here but being at a loose end [2nd January!] I visited and found this problem. My solution works perfectly for me and I hope it solves HIS problem.

If you were so "awake" why didn't YOU solve [as described] during the past 6 weeks..Hmmmmm?