tmssoftware.com

Adding data to tables in Excel File

FLX FLX Feature Requests
#1

Hi,

Using Delphi 11.2 and the latest Flexcel VCL.

I'm using Flexcel to open some Excel files that have data tables. These tables contain a couple of blank rows and I need to add lots of rows to the table. The data is held in various Delphi classes and I'm not using databases at all.

I'm wondering what the best way of doing this is? I did have a look through the examples but couldn't find much relating to Excel tables.

So far I have just entered the data using SetCellValue and then set the Bottom of the table range to the last row. The problem with this is column formulae do not get applied to the data in the new rows, so I'm thinking there may be a better way to achieve what I am trying to do? I've tried expanding the table range before adding the data but this doesn't work either. Do I need to manually add the formula to the new rows? I'd like to avoid doing this as sometimes my end users need to add further rows and in this case they rely on the column formula being present.

A further problem I have encountered is that the column formula in one column in the table refers to the sixth column in the same table by column name (e.g. =[@[Col name6]), but when I open the file it is now referring to the fourth column (e.g. =[@[Col name4]). If I enter the formula directly using SetCellValue with TFormula.Create('=My_Table_Name[Col name6]') it still references the fourth column when I open the file! Any ideas what's going wrong there?

Many thanks.

Richard

#2

Hi,
As usual, most of the examples in FlexCel are inside APIMate: Just create the table in Excel, save the file, open it with APIMate, and it should tell you how to add that table.

But the thing with tables is that you have 2 formulas: The one in the cell (that you set with SetCellValue), and the one in the Column. If those formulas don't agree, you might see something like this, where you set one of them, but you see the value of the other.

I was just investigating some weird behavior with tables when I got this notification, and I haven't investigated enough to tell you if there is a bug or not, but I'll let you know as soon as I find out. In any case, if you can send me a small example of what you see it would help to make sure that it is fixed when we ship the next version. You can post it here or email it to adrian@tmssoftware.com

#3

Hi, Thanks for your quick reply.

I did use APIMate initially but it only showed me how to add a table from scratch. I need to add data to an existing table and as there are pivot tables feeding off these tables I don't want to break that link. Is there a way to use APIMate to see how to modify an existing table?

I worked out from the help file that I can use TXLSFile.GetTable to get an ITableDefinition and then I modify the Range of that, setting Bottom to the last row of the added data. This works in the sense that the table is then encompassing all the data, but the column formula doesn't extend to the new rows. I'm not writing anything to the cells of this column so I don't think it's a case of me overwriting the column formula. I can write the formula using SetCellValue but as you say this adds the formula at the cell level and I'd rather the column formula was extended.

With regard to the column referencing issue, I'll see if I can put together a small example and send it to you.

Thanks.

Richard

© tmssoftware.com