Working with excel files in Delphi

[SilverWarior]

Hi guys!
While this is not game related I'll still ask here first rather than over Stack Owerflow becouse I'm afraid that I'll simply get "this thread is duplicate" kind of an anser or "this thread is out of topic" answer there.

What I'm looking for is a god working example of working with excel files by using Delphi, preferably one that doesen't use ADO database approach (my expirience of working with databases is pretty low and my knowlege of SQL is terrible :-[).

Features that I require are:
- openin existing excel files. If only .xls format is supported that is fine with me since I can alwys save .xlsx to .xls format with Excel.
- automatically finding the sheet names
- accesing specific cells preferably in form of row number column letter (A14, B56, etc)
- saving of modified excel files
Features that would be handy but are not necessary:
- creating of new files
- creating of new sheets
- working with built in tables
- and probably some other that I don't remember now

Why I need this?
I have several excel documents containing some data which I need to combine and then make some statistical analisies. The problem is that the data isn't equally formated in these documents and some of them actually for relationship connections (similar as relational tables).
So what I need now is to format the data in these files in a proper way so I can combine it.
I already found which columns could be used as relational conections but I need to format the text in them a bit. They contain a case number which is genarally constructed like this:

aaaa-nnnn/yyyy/eeeeeee

a = case type number
n = serial number
y = year in which case was opened
e = employer number to whom the case was assigned

Now the problem is that different documents store this case number in a bit different format. So I have these:
aaaa-nnnn
aaaa-nnnn/yyyy
aaaa - nnnn / yyyy / eeeeeee
I don't belive Excel itself has the ability to automatically format hese for me or does it. So I thought to myself that maybe I would go and bit a small aplication that would do this. And maybe someday in the future I go and upgrade it with even more functionality.

Thanks for your help in advance!

[AthenaOfDelphi]

It just so happens that I can assist :)

One of the applications I created at work can import data directly from Excel spreadsheets. It requires Excel to be installed on the machine, but uses late binding to get around potential version differences.


fExcelApp:=CreateOleObject('Excel.Application');

if (not varIsEmpty(fExcelApp)) then
begin
if (fConfiguration.workbookPassword<>'') then
begin
password:=fConfiguration.workbookPassword;
end
else
begin
password:='ThisIsAnEmptyPassword'; // emptyParam;
end;

try
fExcelApp.Workbooks.Open(sourceFilename,ReadOnly:= true,UpdateLinks:=false,Password:=password);
except
on e:exception do
begin
fExcelApp.Quit
exit;
end;
end;

try
fExcelWorkbook:=fExcelApp.ActiveWorkbook;
except
on e:Exception do
begin
fExcelWorkbook:=null;
fExcelApp.Quit;
exit;
end;
end;

if (fExcelWorkbook.Worksheets.Count>0) then
begin
sheetName:=fConfiguration.worksheetName;

try
fExcelWorksheet:=fExcelWorkbook.Worksheets.Item[sheetName];
except
on e:exception do
begin
fExcelWorksheet:=null;
fExcelWorkbook:=null;
fExcelApp.quit;
exit;
end;
end;
end;

// Access the cells in the open worksheet like this:- fExcelWorksheet.cells.item[1,column]

fExcelWorksheet:=null;
fExcelWorkbook:=null;
fExcelApp.quit;
end;


fExcelApp, fExcelWorkbook and fExcelWorksheet are all OleVariants. I can't post anymore than this snippet (which is lacking ALOT of error checking) as it's code from the product itself. For more information about the various methods etc. available to you, your best bet is the Microsoft Interop References (http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel%28v=office.15%29.as px - For Excel). It can be a real pain in the proverbial backside but once you get used to using them and working in this way you can do an awful lot. The same app that imports from Excel also opens InfoPath forms, renders them to a PDF document and copies all the attachments out to self contained files for later processing... all done in a similar convoluted fashion.

If you have further questions, feel free to ask :)