Three weeks ago, I
wrote
about programming a thread that would activate Word (in the background, of
course) and cause it to display a file that was preprogrammed as an HTML
page. I wrote then "the next step is to write similar code that will open
Excel in a thread". Well, I wrote the Excel code; this was easy to do once I
had the Word example.
Today I came across an example where the simple 'open Excel in a thread'
code is not sufficient: it often happens that I want to format certain
columns as integers or as dates (although as I
documented
a year ago, it's better not to format date columns) after the data
had been passed to Excel. I would do this with the following two
lines:
xlApp.Columns['B:F'].Select;
xlApp.Selection.NumberFormat:= XLIntFormat;
I could not do this with the simple thread code as the xlApp variable is not
visible to the calling program. The simple solution would be to add an
optional parameter to the code that creates the thread; this parameter would
be passed down the chain and used after the data had been inserted into Excel.
A complication would be that there might be two ranges of columns that need to
be formatted.
Here's a further complication: normally I freeze the top line of an Excel file with the following code:
sheet.Range['A2'].Select;
xlApp.ActiveWindow.FreezePanes:= true;
In a certain case, I wanted to freeze the top two lines, meaning that the
range would have to be A3 and not A2. Could I do this with another optional
parameter? It is my understanding (probably wrong) that there can only be one
optional parameter per procedure. This was exacerbated when I thought that I
would also have to pass another string parameter denoting which columns should
be dates (in the end, I didn't need this). So I decided to use
overloaded procedure definitions, a first for me.
I now have the following:
Procedure ThreadExcel (const AFileName: string); overload;
Procedure ThreadExcel (const AFileName, intformat: string); overload;
Procedure ThreadExcel (const AFileName, intformat: string; freeze: integer);
overload;
...
Procedure ThreadExcel (const AFileName: string);
begin
TExcelThread.Create (AFileName, '', 2);
end;
Procedure ThreadExcel (const AFileName, intformat: string);
begin
TExcelThread.Create (AFileName, intformat, 2);
end;
Procedure ThreadExcel (const AFileName, intformat: string; freeze: integer);
begin
TExcelThread.Create (AFileName, intformat, freeze);
end;
I suppose that I also need a variation where there are no columns to be
formatted, but a different 'freeze number' is required but I'll cross that
bridge when I come to it (actually this could be done by passing afilename, a blank string and the freeze number). The possibility that there might be different types
of format passed and/or multiple column selections is handled in the following
code:
Procedure DoFormat (const fmt: string; aformat: string);
var
i: integer;
cols: string;
begin
repeat
i:= pos (',', aformat);
if i = 0 then
begin
cols:= aformat;
aformat:= '';
end
else
begin
cols:= copy (aformat, 1, i - 1);
aformat:= copy (aformat, i + 1, length (aformat));
end;
xlApp.Columns[cols].Select;
xlApp.Selection.NumberFormat:= fmt;
until aformat = '';
end;
...
if intformat <> '' then DoFormat (XLIntFormat, intformat);
Intformat could be 'A:B,E:F' in order to format two column ranges. Passing the format string as a parameter enables formats other than XLIntFormat ('#,##0') to be used. This procedure uses what might be termed 'old style' programming; modern day coders would probably use a stringlist in the following manner (this is unchecked code, so it may be wrong):
var
i: integer;
alist: tstringlist;
begin
alist:= tstringlist.create;
alist.delimiter:= ',';
alist.delimitedtext:= aformat;
for i:= 0 to alist.count - 1 do
begin
xlApp.Columns[alist[i]].Select;
xlApp.Selection.NumberFormat:= fmt;
end;
alist.free;
end;
I wonder whether I need to add a comma at the end of the list.
