Can you explain how to add a sheet to an excel file?
AddSheet() always seems to return 1 no matter what number you pass it and the sheetcount is never incremented.
There doesn't seem to be a meniton of AddSheet anywhere in the demos
Hi,
Sorry about that: what happens is that AddSheet(sheetcount: integer) isn't supposed to be a public method.
To add a simple sheet at the end, use
xls.AddSheet;
instead, but no
xls.AddSheet(1);
Or to add an arbitrary number of sheets anywhere, use xls.InsertAndCopySheets. In fact, if you look at the code for AddSheet it is:
/// <summary>
/// Inserts an empty sheet at the end of the file. This is equivalent to calling InsertAndCopySheets(0, SheetCount + 1, 1).
/// If you need to insert more than one sheet, or insert it at the middle of existing sheets, use <see cref="InsertAndCopySheets(int, int, int)"/> instead.
/// </summary>
procedure AddSheet; overload;
procedure TExcelFile.AddSheet;
begin
InsertAndCopySheets(0, SheetCount + 1, 1);
end;
The idea about manipulating cells/sheets in FlexCel is (as explained in the docs), to have a minimum set of methods that can do everything, instead of millions of more specific methods. As they are little, it is easy to remember them and they don't pollute the XlsFile with thousands of extra methods.
Those methods are:
InsertAndCopyRange: Inserts or copies or InsertsAndCopies a range of cells, or a range of columns, or a range of rows from one sheet to other place of the sheet, to another sheet or to another workbook. That's why we don't have InsertRow, CopyRow, InsertColumn, InsertCellRange... etc, etc.
MoveRange
DeleteRange
InsertAndCopySheets
DeleteSheet
On that idea, "AddSheet" is really redundant, as it can be done with InsertAndCopySheets. But as it is not really intuitive, we've added a method to AddSheet anyway, which explains that you can call InsertAndCopySheet for more information.
Now, with this explained, let me tell you why we have this "AddSheet(integer)" method which shouldn't be public. Much of the FlexCel VCL code comes from .NET and it is automatically updated everytime we fix something or add new functionality. This is quite an interesting system on its own, which involves a hacked mono compiler, and which I should write a blog someday about it. It allows us for example to add SVG exporting this next week in FlexCel .NET 6.3, and have SVG exporting in VCL the same day (6.3 for VCL will take a little longer, but this is just because we need to add some VCL only functionality to it before release)
And in .NET, AddSheet(int) is an internal method:
Sadly, when we automatically convert this to delphi, we can't put it into an "internal" delclaration, because Delphi doesn't have that. So it must be exported as public.
Currently, there are some methods in the API which are internal in .NET but exposed in Delphi, because we have no choice. They aren't much, and normally have names that show they are internal, but in this case of AddSheet it can be confusing.
The way to know right now if a method is internal or not in Delphi is to press F1 and look at the docs. 100% of the public API is documented, so if you see "This method is for internal use.", then it is internal.
For the future, we have been toying in the last weeks with some other ways to hide those methods, which aren't much, but as in this case can be confusing. The easiest way would be to have the converted to convert them as "_AddSheet", so you know methods with an underscore are internal. but I am thinking also in using a class helper to completely hide those methods. We've just haven't got time to dedicate to this yet, FlexCel is a huge project (1600 units), I have to maintain it in VCL and .NET, and there is other more pressing stuff to do. (as we spoke about last week, for example adding tables support).
Again sorry about that, and in short, use AddSheet; or InsertAndCopySheets, don't use AddSheet(number).
Very interesting about the .net angle. I'd love to see you run that compiler on the SpreadsheetLite assembly to get true 100% XLSX support.
I don't know about spreadsheetlite, but truth is, nobody has true 100% xlsx support, not even Excel (Excel for mac, or office 365 web clients for example lack a lot of stuff). They might have features you need (like tables) and probably don't have others that you don't (for example do they support what-if tables, which we fully support? Or array formulas, which we also do? Recalculation of linked files? Circular recalculation? I haven't looked at spreadsheet lite, but I am sure they do have a lot of stuff we don't and they also miss stuff we do.
It is just that some product will fit better to your needs than another. For some other users the reverse is true, they find FlexCel a better fit. And I know because I get a lot of mails from people switching from other products to FlexCel, both in .NET and VCL.
And about the converter, it wouldn't be as simple as to take an assembly and run through the converter. While probably 90% of the FlexCel VCL code is autogenerated, there is a lot of manually written optimized code too. To give you an idea, the original port of FlexCel took about 40 secs to open a multimb xlsx file. After lots of manual optimization, we cut that time to under 1 sec, which was faster than the time Excel took to open the file. Also in memory usage, FlexCel VCL has lower mem usage than FlexCel.NET, using normally less memory than Excel, because we've optimized the memory layer to use the native things we can use in Delphi that we can't in .NET. We've invested many years in getting the FlexCel VCL port up to the quality standards we have (which are very high, and mean also passing the whole test suite from .net, deal correctly with corrupt files, etc). Most likely if we had said "ok, forget about vcl users and focus in .NET", we would have already implemented tables and conditional formats in .NET. Those 2 missing features are also missing in .NET, and that's because we had to cut them in order to focus in VCL. Now that both VCL and .NET are almost in synch (we still miss to port reports to VCL), and the conversion tech is oiled, we are going to focus in what's missing, for both .NET and VCL.