0

I have Delphi (Delphi 2009) application, that uses Excel_TLB commands for writing into Excel file. I need not only write the data in the cells, but also assign custom format to the cells. It is done cell-by-cell bases and the typical code for one cell is:

    FWS.Cells.Item[CurrLine, 4].Value2:='some data';
    FWS.Cells.Item[CurrLine, 4].Font.Name:='Times New Roman';
    FWS.Cells.Item[CurrLine, 4].Font.Size:=9;
    FWS.Cells.Item[CurrLine, 4].Font.Bold:=True;
    FWS.Cells.Item[CurrLine, 4].Font.Italic:=True;
    FWS.Cells.Item[CurrLine, 4].HorizontalAlignment:=xlHAlignLeft;

I have approximately 6000*5 cells and such writing and formatting takes up to 1 minute of time. That is lot of time. The data writing itself is very fast, but formatting makes the most of time. I know that assignment of formats to the entire regions makes things faster, but that is not option for me. My regions are very complex and not in one bulk.

Are there options for Excel to disable some processing during formatting so, that I can speed up my formatting? I do all the work in background/invisible state, then save the file to the disk and then I open it with ShellExecute.

TomR
  • 2,696
  • 6
  • 34
  • 87
  • 3
    Instead of repeatedly doing `FWS.Cells.Item[CurrLine, 4]`, which onvloves a lot of marshalling calls, get a reference to the item, then do the operations on that. Also look up the call to disable screen updates on the Excel object (I forget its name right now). – MartynA Oct 06 '19 at 20:59
  • 2
    @MartynA Are you thinking about `Application.ScreenUpdating = False` and `True` to enable again. – Tom Brunberg Oct 06 '19 at 21:04
  • 3
    @TomBrunberg, probably, and thanks. And on reflection, I'd also look into setting a range over all the cells being updated and setting the formatting for that rather than individual cells. – MartynA Oct 06 '19 at 21:08
  • 1
    You could speed up your code a bit by creating a local TFont variable set desirable font propertiees to the local variable and then pass the local variable to excel using `FWS.Cells.Item[CurrLine, 4].Font := LocalFont`. This can save you three calls to excel. Also what is the type of `FWS.Cells.Item`? if it is a record then you could create and prepare a local copy od cell data and send it to excel using just one command. – SilverWarior Oct 07 '19 at 08:24
  • 2
    The better way is not to use Excel at all and generate the xlsx file in pure delphi code. – David Heffernan Oct 07 '19 at 08:46
  • 1
    @SilverWarior `Font` property of `Range` object is read only, so that won't work. `Style` could be used to assign all the formatting in one go, but it is not quite the same. – BrakNicku Oct 07 '19 at 09:12
  • 1
    Have a look at my answer to question [Fastest way to format multiple rows in an excel in C#](https://stackoverflow.com/questions/57058935/fastest-way-to-format-multiple-rows-in-an-excel-in-c-sharp/57074884#57074884). – Peter Wolf Oct 07 '19 at 12:56
  • @david-heffernan And what are those other options - just by name (I will dig further myself)? Can they be used for xls as well? I have seen dreadful code that generates binary xls or smth like that! – TomR Oct 08 '19 at 18:28
  • 1
    There's no point creating xls these days. As for then xlsx libraries there are a few, but I've never used delphi libraries, I use my own code to generate xlsx files. – David Heffernan Oct 08 '19 at 19:17
  • Just for others: https://stackoverflow.com/questions/2493071/exporting-to-excel-from-delphi-without-having-excel is compilation of projects that to Excel export from Delphi. All of those projects are ether commercial packages or too old to be usable. OExport seems to be the most promising, but there is no support for Delphi 2009 and it is commercial as well (as far as I understand). There is lacking open source export library. – TomR Oct 09 '19 at 09:08

0 Answers0