Newbie Question...

OK - I've bought FlexCell - Woohoo!!

I now the the inevitable stream of question.  The first one is to do with ranges.  As an example, how can I specify a range, set the number format to "$#,##0", set the background to clBlue and put a medium thickness gray border around it?

i'd expect to be able to do something like:
with xls.Range(1, 1, 5,7) do

  NumberFormat := '$#,##0';
  Background := clBlue;
  DrawBorder(xlsLineMedium, clGray);

Is this possible?  What's the FlexCel way of doing this?



No, this isn't how FlexCel works, the FlexCel API is "Flat". 
Your example is what you would write in OLE Automation (or any tool that looks like OLE), but it has 2 problems:

1)Objects are coupled. In order to write range.NumberFormat := 'something' and have it modify the Excel file, the "range" object would have to know (have a reference) to the xlsfile object, and it would belong to that object. 
In FlexCel objects are loosely coupled, and a range is just a record with 4 coordinates. (top, left, right and bottom). If you modify the range, you modify the record, not the xlsfile object. And you can use this range in a different xlsfile object, since it isn't tied to an specific one.

2)Performance is bad. When you do "xls.Range(...).NumberFormat := '' we would need to create a temporary object and return this tmp object to you. You hide it with the "with" call i your example, but the example is equivalent to this:

tmprange := xls.Range(1,1,5,7);
tmprange.NumberFormat := '$#,##0';  
tmprange.Background := clBlue;
tmprange.DrawBorder(xlsLineMedium, clGray );

Here we are creating a tmprange object that needs to be freed, or be an interface if we want it to free automatically. But interfaces are really slow because the reference counting will put a lot of lock in your code. Now what you want it you are doing millions of calls.
But even worse, you are doing 3 method calls, each one of which has to modify all the cells in the range. 
tmprange.NumberFormat := '$#,##0'; 
will have to loop in all cells, and modify the format. Then the next line will have to loop again, and at the end, this innocent looking code has looped 3 times instead of one.
This is just an example but this style of coding leads to very slow code (like the one you get when you code in OLE Automation) Lots of allocations and frees of temp objects, many repeated calls.

We aren't philosophically opposed to provide an "OLE Automation like" API,  but the FlexCel API is at the core of all the other functionality, and it has to be lighting fast. We use the API ourselves for exporting to pdf/html or doing the reports (both of things are not yet ready in vcl but coming very soon), so we need xlsfile to be a "low level" API, not a higher level API which lets you write a little less code at the cost of much inefficiency.

In the future we might even provide a Ole automation like API which would use XlsFile to do the work, for people migrating ole code or who is doing small spreadsheets and doesn't worry about slower code. But we would still keep XlsFile low-level and flat. If XlsFile is slow, then reports will be even slower, and so will be pdf exporting.

In your case, what you could use is the following code:
  xls: TXlsFile; 
 fmt: TFlxFormat;
  ApplyFmt: TFlxApplyFormat;

 xls := Txlsfile.Create(1, true);
 fmt := xls.GetDefaultFormat;
 fmt.Format := '$#,##0';
 fmt.FillPattern.Pattern := TFlxPatternStyle.Solid;
 fmt.FillPattern.FgColor := clBlue;
 fmt.Borders.SetAllBorders(TFlxBorderStyle.Medium, clGray);

 ApplyFmt.SetAllMembers(false); //only change borders, pattern and format of the cell
 ApplyFmt.Format := true;
 xls.SetCellFormat(1, 1, 5, 7, fmt, ApplyFmt, true);


While I agree is more wordy than your example, it will loop over the cells just once. And you can wrap this in your own method:

procedure PaintRangeBlue(...)
//code I just sent.

As said, being XlsFile a low level interface, it won't provide you with many convenience methods, but nothing forbids you of creating your own, based in what you actually need/use.

A last thing. In this particular case, and as setting the format of a range of cells is a common feature, we have an overload that does it. But in other cases you might have a method that only applies to a cell, for example SetCellValue (because it isn't that common to set the value of a range of cells to a single value). For  those cases, just loop over all the range and call SetCellValue in each cell. Different form OLE Automation, this will be very fast in FlexCel. (And if you look at what the SetCellFormat(range) does, it just loops in the cells anyway, so speed is the same if you looped yourself and set every cell format).

In FlexCel API, looping through cells is a common and fast operation. In OLE it is slow (due to all the tmp objects created), and so they have some "optimizations" like setting all the cells into an array and then doing a single call to copy the array in Excel. In FlexCel this would be slower. Looping is also very powerful because you can add any logic to the loop easily. For example, if you wanted to paint only odd rows blue, and you had a loop calling SetCelFormat,you would need just to add an "if (row mod 2 = 0) then..." while it would be impossible with your code or the SetCellFormat(Range). And a last benefit of loops is that they don't hide the performance hotspots. In your code, it would be difficult to see that you are looping 3 times in all the range. With an explicit loop, it gets self-evident. So don't fear to use loops in FlexCel, you are supposed to!

Well, I hope the explanation made sense. As said, doing an "OLE like" api is in the plans for some day, for people who prefers it or who needs to port code, and don't need ultimate performance. But it isn't a big priority right now, we have many more interesting things that we want to do first.

Hi Adrian,

Thanks for the prompt and detailed reply!  I'll go though it carefully and respond with comments or suggestions.

Thanks again,


Hi Adrian,

Thanks again for the support.  After fiddling for a couple of days, here are my thoughts.  

Overall FlexCell seems quite powerful and will certainly suffice for our purposes.  However, I doubt many people are really interested in super fast speed.  I've used other Excel components and they all seem "fast enough".  I'd suggest creating some higher level routine for formatted.  Also I have to say the documentation seems weak.  The help files cannot even be viewed using Windows 7 (btw, what do you recommend using to view the helpfiles on Windows 7).  As an example, the TFlxApplyFormat object isn't mentioned in any of the PDF documentation, yet seems like a core class for creating formatted worksheets.  Hopefully the documentation can be improved.  But overall it FlexCell does the job.

Thanks again,

Thanks for the feedback. 
Actually, many users care a lot about the speed, I just got a mail 5 minutes ago about an user who needed to read 5 million cells and needed to make it faster.  Yesterday I got another from an user creating 100mb files, and I get similar mails all the time. 

You know, the main problem with performance is that it isn't linear, and as long as you are in the sub-second times, it doesn't really matter if it takes 0.3 seconds or 1 second. But when you move to millions of cells, 0.3 minutes or 1 minute do make a difference. And the relation doesn't even keep constant, it it took 0.3sec/1sec for a small file, it will be more like 0.3 min/5 min for a bigger one. But I agree a higher level approach can be useful to other users, so that's why we have this in a todo list. Btw, FlexCel does offer a higher level approach, reports, where you design your file directly in Excel and FlexCel fills the data, but this isn't yet ready for FlexCel 5 VCL (it is in 3.6 and in FlexCel.NET, and it is coming soon to 5 VCL too). 

About the help, of course it can be run in windows 7, it is a standard help 2 file, which is what Delphi needs in oder to show the help files integrated inside the IDE. Sadly the Help2 file format is really crappy and you can't just double click the file and have it display, it has to be registered and opened from a help viewer. But that's not our fault, this is Microsoft's design, and also Embarcadero's fault for using such a braindead file format. We just need to use what Delphi does if we want to integrate the help with it. (and as a matter of fact, Visual studio doens't use it anymore since VS2010)

But you can read the help in different ways:
1)Just press F1 on TFlxApplyFormat inside the delphi ide. If you registered the help at setup, you will see it. 

2) You can also browse it online at

TFlxApplyFormat is here:
and the information in the method that calls it is here:

3)You can ctrl click in TFlxApply format, it will send you to a "proxy class" which forwards it to where it really is defined. If you ctrl-click again in this class, you will see the documentation directly in the code, since all the reference material is written as xmldoc in the code itself. This is what I actually use myself.

About why it isn't in the pdf reference, well, it is just because it isn't a core class to create formatted spreadsheets, it is a helper class. In fact, we don't use it anywhere in our own code. The need for an "ApplyFormat" comes because we provide a function that can format many cells in one pass. But in FlexCel the idea is that you loop over your data, and go formatting and setting the values as you pass, so you don't really need any apply format: you read the format in the cell, change it and write it back,  Pdf reference is for big concepts,  not for everything you can do (or they would be thousands pages long) And ApplyFormat isn't that complex either, it is just a struct that has fields that when true are applied, when false they are left as they were in the cell.

Anyway, documentation in FlexCel 5 is still a work in progress, but I don't think it is really bad. Every single method and class is documented, we provide APIMate which is a great help to learn how to do things, we provide many demos with a demo browser and a complete explanation in each one, and we provide the conceptual pdfs. As said, it still can (and will) be improved but it isn't that bad. 

The main problem we have right now with the docs is not that there isn't enough, but more that there is too much and people can get lost without finding how to do simple things hidden among everything else. That's why we try some other solutions that just add even more documentation. One example is  APIMate, it provides a simpler way to find "how to do this", even if it isn't perfect and wouldn't have helped with ApplyFormat, I consider it a very important part of the documentation. 

Other example is the "demo browser" and the search facility on it, since it allows you to search for specific demos that show specific concepts. For example, if you write "TFlxApplyFormat" in the search box in the MainDemo app, you will see it shows you "Advanced API" demo, which shows how to use it. 

Documentation isn't just pdf/help files, and we are always looking at "other ways" to show how to do things besides plain .hlp/pdf files. But it is hard to do, as said, not because the effort writing it, but more because if you write too much the people starts not finding the simplest things. And FlexCel is a complex beast, no matter how you look at it. But well, I guess that's why we provide newsgroup and mail support :)