Hi,
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 );
tmprange.Free?
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:
var
xls: TXlsFile;
fmt: TFlxFormat;
ApplyFmt: TFlxApplyFormat;
begin
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.FillPattern.SetAllMembers(true);
ApplyFmt.Borders.SetAllMembers(true);
ApplyFmt.Format := true;
xls.SetCellFormat(1, 1, 5, 7, fmt, ApplyFmt, true);
xls.Save('text.xls');
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(...)
begin
//code I just sent.
end;
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.