Insert => Delphi Array => Into Excel Spreadsheet 1

[gibben]

Hi there,

I am having a few problems with the context to insert the data from an array within delphi into an excel spreadsheet.

I have a couple of snippets of code that I have been playing with from an online tutorial but I can't seem to get them to behave in the way I want .....

The first will create the worksheet and put the first set of an array in but won't allow it to be looped through as the second time around it tries to recreate the sheet:

sCopy := 'SELECT "Harry","Jim","Bill" INTO ["Excel 8.0;Database=' + Edit1.Text + '"].[' + IntToStr(Year) + ']';


The second snippet will append but it has been set up to take the data from an access DB and copy it to a worksheet, I am having probs getting it to use an array as the source of info and loop through that:

sAppend:='INSERT INTO [Sheet2$] IN "' + Edit1.Text + '" "Excel 8.0;" SELECT AuthorEmail, Title, Description FROM Articles';

I realise it is in the context of the query but it appears that delphi works with the ADO in a slightly different way, especially when the data recipient is an excel workbook.

Any help would be greatly appreciated, thanks.

 

[lespaul]

Here's a method (some snippets) that takes an entire array and drops it into an excel spreadsheet:

ProcessList : variant;
ProcessList := VarArrayCreate([0, #ofrows, 0, #ofcolumns], varOleStr);
//loop through dataset and fill array
For i := 0 to (personcount - 1) do
begin
  ProcessList[i , 0] := ProperCase(qry.FieldByName('FIRSTNAME').AsString);
  ProcessList[i , 1] := ProperCase(qry.FieldByName('LASTNAME').AsString);
qry.Next;
end;

excelapp:= CreateOleObject('Excel.Application');
excelapp.Visible := False;
//if you know the name:
excelapp.Workbooks.Open('R:\Case Management\JMS\Forms\Vouchers.xls');
excelsht :=  excelapp.WorkSheets.Item['ImportData'];
//if you just need a blank worksheet
excelapp.Workbooks.Add();
excelsht :=  excelapp.WorkSheets.Item['Sheet1'];

excelsht.Activate;
//set excel range to size of ProcessList array and transfer array in full

excelsht.Range[excelsht.Cells.Item[2, 1], excelsht.Cells.Item[(personcount + 1), 8]].Value := ProcessList;

The red section above is set up like:

excelsht.Range[excelsht.Cells.Item[put the column & row to START], excelsht.Cells.Item[put the column & row to end]]

HTH

Leslie

 

[gibben]

Hi thanks for your reply,

A little tweaking for my application and it is working great, I was trying to see if there was a commands to save and close the workbooks .... but have not found any info on that.

You wouldnt happen to have that info??

Thanks for your help, it is appreciated.

Here is the code I am using now:

var
excelapp : variant;
excelsht : variant;

i : integer;
j : integer;

bookName : string;
sheetName : string;

begin

bookName := 'C:\Methane Inventory\Methane Inventory\Projects\Dairy_Output.xls';
sheetName := 'Sheet2';

excelapp:= CreateOleObject('Excel.Application');
excelapp.Visible := True;
{if you know the name:}
excelapp.Workbooks.Open(bookName);
excelsht := excelapp.WorkSheets.Item[sheetName];

{if you just need a blank worksheet}
{elapp.Workbooks.Add();}
{elsht := excelapp.WorkSheets.Item[sheetName];}

excelsht.Activate;

for i := 1 to 12 do
begin
for j := 1 to 4 do
begin
{set excel range to size of ProcessList array and transfer array in full}
excelsht.Range[excelsht.Cells.Item[(i+1), j], excelsht.Cells.Item[(i+1), j]].Value := outputArray[i,j];
end;
end;
//code to close & save the workbook here

end;

 

[lespaul]

I have that at work, and I'm at home now! I'll post that in the morning if there isn't already an answer.

Glad it worked out for you.

Leslie

 

[lespaul]

//saves the workbook

excelsht.SaveAs('R:\Case Management\JMS\Forms\Labels.xls');
//closes the workbook, but not the Excel application
excelapp.Workbooks.Close;

//if you have visible set to FALSE so that the user doesn't see excel, the following line sets excel to not show messages like "Do you want to save this workbook before closing Excel?"

excelapp.displayalerts := False;

//closes the application
excelapp.quit;

There you go!

Let me know if you need anything else!

leslie