High Level "Range" and "Cell" Access...

FLX TMS FlexCel for VCL & FMX
1
Hi Adrian,

I've finally completed the conversion of my four key apps from NativeExcel and ReadWriteExcelII to FlexCel - big YIPPEE!! As I used FlexCel, I liked it more and more.  I wish I had started off with it!!

In order to make the conversion easier I did create a subclass which implemented the "Cell" and 'Range" properties.  This may not be quite in the spirit of FlexCel's ultra-fast reading and writing but I found it helpful.  Maybe other will also find it helpful.  The syntax is quite simple

To manipulate a range you would write:


  with XLS.Range[3, 1, 3, 5] do
  begin
    BeginUpdate; // <--Optional
    BackgroundColor := RGB(204, 51, 0);  // Orange
    DrawBox(TFlxBorderStyle.Thin, TExcelColor.FromArgb(102, 0, 0, 0));
    FontColor := clWhite;
    FontStyle := [TFlxFontStyles.Bold];
    EndUpdate;
  end;
  
The "BeginUpdate" and "EndUpdate" make it a little faster.

To handle a Cell the syntax is similar:


  with XLS.Cell[1, 1] do
  begin
    Value := 'FlexCel Example';
    FontName := 'Franklin Gothic Book';
    FontSize20 := 360;
    FontStyle := [TFlxFontStyles.Bold];
  end;
  
You can download a demo and source code here:

http://goo.gl/cmx8g

Feel free to use the code for anything whatsoever (although it is provided "as-is")

Best regards,

Steve
2

Steve,

Thanks for the post and the kind words, I sincerely appreciate them. 

Your code is quite interesting and I am sure it will help other users, I am thinking in what would be the best way to share it so other people can use it / know of its existence.

Now, I will do some "loud thinking" and try to explain a little what my idea for FlexCel is, and how your code fits in there:

1)We have TXlsFile, which is a low level, close to the core API, which has to keep this way, because it is the same API that the rendering engine or the reporting engine use, and it has to be fast. When you are creating a pdf the pdf engine doesn't call any magic api, it calls the public methods on TXlsFile. So it better be fast, if we don't want to spend days generating pdfs.

2) Of course, for most users it makes sense to create a higher level API on top of that, as you did here, and it is something that I actually recommend for many people. Not everybody needs to work at the lowest level.

3)And also as expected, we've had in the perpetual todo list to create another API that just calls XlsFile, as you did here. 
But this API should be a "Drop in" (or as close as possible) replacement for the OLE Automation API, because this is (sadly) what most people use, and what most people migrate from once they get fed up with it.

The reason we haven't looked at it yet is simply that we always have more important things to do , and XlsFile works well, if maybe a bit too low level.

But making an OLE-Automation-Like API is a little more complex (and less performant) than your code, because we have to  deal with a very common idiom (and what many people who ask me for an ole automation like api is thinking of) : People storing "ranges" on variables, and using them later. For example:



procedure TForm2.WriteExcelFile(xFileName: string);
var
  XLS: TsmXLSFile;
  y, i: Integer;
  A1, A2: TsmFlexCell;


begin
  XLS := TsmXLSFile.Create(1, true);


  A1:= XLS.Cell[1, 1];
  A2:= XLS.Cell[1, 2];


  A1.Value := 'A1'; //will actually set A2.
  A2.Value := 'A2';


  //---Save to disc
  XLS.Save(xFileName);


  XLS.Free;
end;

For this to work correctly, we need to create a TSmFlexCell object every time you ask for it (so you will be able to do "A2.Value := 'A1'"  even after you defined A2 and it would still set A1, not A2). This is not only a performance killer (which isn't the case in your code because you don't create an object each time), but also brings the question on who "frees" all of those TSmFlexCell objects that are created every time you ask for an object. The solution in OLE Automation is to use interfaces, but this slows down the thing even more, because interfaces are reference counted, and this refcount needs to be threadsafe, so it locks() the CPU, bringing really bad performance. 

And note that this is not theoretical performance: To give you and idea, the first naive implementation of the Xml parser in Excel took 40 seconds to parse a 3mb file. By changing the strings in the xml parser to pchars (which aren't refcounted and don't lock the cpu) we reduced the time from 40 s to under 1s. Of course the code is much uglier so in general we just use strings, but in the "hot paths" like this case it only makes sense to keep away from refcounted vars. And setting a cell value is a hot path for many apps that just dump huge datasets, even if not as hot as an xml parser.
 
So in resume, we have a low level API, and are planning a high level api which should be compatible with ole automation to help migration. Your solution stands in the middle, being almost as performant as the low level, but not offering the "shareable objects" OLE users are used to, and not being a drop in replacement.
 
I think it can be valuable for many users, so I will see if we can set up some "contributed code" or similar where we could host it. 

Thanks again for taking the time to post this.

Cheers,
   Adrian.
3

Hi Adrian,


I'm pleased you found the code of interest.  As I said, feel free to use it as you see fit.  I hope others benefit.

Best regards,

Steve