Huge Exploding Size Too Big Excel 2003 file - 34 MB: Solution or Alternatives?

I'm thinking I may be asking too much from Excel. I'm really using it as a Direct Access File and while avoiding the use of databases, maybe I have no alternative but to embrace them.

To avoid using databases, maybe I could reduce the Excel file size somehow (any way to make single precision?) or do everything in memory while keep the data in a binary file (easy in FORTRAN, but obtuse in Delphi.)

Here are some details of the .XLS file:

This .XLS file is used to collect output from a Delphi program that makes 3000 write then save loops. It has no formats but it does have cell formulas.

The content of the XLS file has

500 sheets (identical but with different numbers)

Each sheet has

200 rows
14 columns

Of the 14 columns, 6 of them have formulas (Average, Min, Max, Stdev, etc.) which process the last 6 columns.

That is a grand total of 500 * 200 * 14 = 1,400,000 numbers

Estimating size just based on double precision of 8 bytes (64 bits) per number, we have 1,400,000 * 8 Bytes = 11.2 MB

The Number of Cells with Formulas = 500 * 200 * 6 = 600,000

After going through these numbers, I'm not surprised it is 34 MB in size and frankly, there may be nothing that can be done about that except perhaps for the single precision thought which is probably impossible.

The issue really isn't the size, its the time it takes to save which must be done on each pass of the automating program. If I open this file myself in XL, it opens pretty quickly. Saving is much longer. But saving from within the Delphi program seems to be multiples longer.

Maybe adding Memory to the PC might help. I confess I'm running XP w/2GB. (with 2GB I should be able to hold these numbers in memory which suggests a stored binary file, loaded and saved each pass would be feasible.)

Suggestions?

ANY would be Appreciated SO MUCH!
Tom

Do you need to have those formulae active in all 500 sheets? If you use Copy | Paste Special | Values on some of the sheets to get rid of the formulae then this would speed things up.

Do you need to use the .XLS format for the file? If you have XL2007 or later then you can make use of the .XLSX format, which gives you more rows and columns (if you need them), but also saves the file more efficiently, so it will open and close more quickly.

Perhaps instead of 500 separate sheets you can aggregate the data onto fewer sheets, using another column to indicate the sheet name where the data came from. This will speed up any formulae which access the 500 sheets and make reporting easier - with XL2007 you can have over 1 million rows of data, so you could put all 500 sheets of data (*200 rows) onto one sheet with plenty to spare.

Hope this helps.

Pete

I was using the Sheets as the 3rd dimension of a 3-dimensional array of data.

I'm really using it like a database or a Direct Access File (FORTRAN.)

There are Delphi components that allow and XLS to be read or written w/o
creating an Excel OLEapplication. I don't know if that would be a good idea.

What is interesting is that once this XL file is created, it opens fairly quickly
but does take maybe 5-10x longer to close. It is still possible this file may
be useful for post-processing, the problem, only in its creation.
_______________________________

I did not remember that this XLS file contained a chart on every sheet.

After I had this Excel failure where the program completed, but writing to the XL file
stopped after 2/3rds the way through, I just realized all the charts were gone.

But the GIF's of them were still being stored. (I believe I used the HTML SaveAs to
get the GIF's.

So I can't explain a lot of this. It could be that killing the EXEL process resulted in
all the missing entries and charts.
_______________________________

I could eliminate all the formulas, I would have to recalculate them each time one of
n-results (6 for this example) are added to the Sheel Row. This would require more writes.
_______________________________

I could also (and with good reason) reduce the number of rows from 200 to 10. That would
solve the problem directly and perhaps is the best way to go. Some program controls (which
should be there anyway) would have to be written.

In fact, this may be the most expeditious way out of this.
________________________________

The whole reason I am committed to Excel is that my program automates another program
which uses Excel format input and output files. If I didn't have that issue, I would never
use Excel automation in my program.

Perhaps I should look more into these XLS direct I/O w/o Excel?

Thanks
Tom