Blog

All Blog Posts | Next Post | Previous Post

TMS Day followup: TMS FlexCel

Sunday, March 20, 2016

Back from TMS day

I am personally very happy with everything about the TMS day. It took 26 hours flying plus 6 hours in a car trip to Germany so I could speak for an hour, but it was completely worth it. There is still something magical about face to face communications, something that is lost in written words. I speak daily with tens of customers via email, I also write posts like this very one, and still there is something missing when you don't see the face of the human you are speaking to.

I also was happy about how the sessions worked: Instead of someone standing there and reading a powerpoint, the sessions were interactive and people in the room asked many interesting questions. This was how it was intended to be, and I'd like to think that we are all -the tms team and the people who assisted- happy about it. But of course we are also aware that things are never perfect, and a lot of stuff can be made better for the next time. You only get better if you correctly identify what can be made better first.

So let's stop with the good parts (which were a lot) and let's focus for a little in what can be improved. The main weak point, from my own impressions and from the impressions I got from the people I spoke in the breaks, seems to be that the sessions didn't go too much in depth in the technical part. This is kind of reasonable if you think about it: To cover an item in depth one hour isn't too much, and there was a *lot* of stuff that we wanted to cover. I could have spoken for a week about FlexCel and there would be still stuff left to be said.

The fact that the sessions were interactive didn't help either: It is hard to plan a presentation when you don't know in advance how much questions will be asked. In this point I must say that even if I was aware that the time in the actual presentation is much shorter than the time when you plan it, I still grossly miscalculated how much stuff I could fit in an hour, and how many interesting questions would appear. I presented just half of the stuff I had prepared and I feel bad about that.

And the last problem is about the technical differences between attendees. Being a TMS day, some people came there to hear in depth stuff about some specific TMS products and have an overview of the others. So some people were heavy FlexCel users while others hadn't heard about it. If you make the presentation too technical, the guys who came for the "overview" will get a good nap. If you make it too general, then the guys who eat FlexCel <#tags> and __ranges__ for breakfast will be bored.

So now, about the hard part: Solutions.

As said the main issue was time, and of course the obvious solution would be to do a "TMS week" not a "TMS day", but this is not really viable. We could show less stuff and focus more, and I think this is something to consider for next time. I also would like to have larger breaks because I think the breaks are the best part of any event. In them you get to speak casually with the guys doing the sessions, and also with other customers which are using the same solutions as you. And we get to speak casually with you. But it is a compromise: the larger the breaks you make, the less sessions you can make in one tms day. Even so, I would vote for longer breaks.

About the unpredictability of the sessions, we could make the sessions not interactive and leave all the questions for the end. But I don't think that is a great idea; in my opinion the greatest thing about the tms day is the interactivity. To watch a 1-hour lecture about something we could just upload a video to youtube and you could watch it from the comfort of your home. You could then ask the questions in the comments of the video. It does work, but it loses the magic. I much prefer to cover half of the themes and have a nice talk with the guys in the room, than to cover everything and not interact.

About the diversity of experience in the room, well, I don't see too much solution about that. I tried to do a mix of both general information and technical, but I ended up speaking more of the general parts than of the more technical stuff. And one thing I didn't know in advance is that most of the people in the room were using FlexCel so I could have skipped the introductory parts. I was surprised by that: I knew which products the people were using, but most people coming were using VCL subs or TMS all access, which doesn't gave me extra information. I honestly wasn't expecting so many FlexCel users, and even if that makes me very happy, I need to apologize: I'll try to make the presentation more technical the next time.

And finally, about all the stuff I couldn't say or expand because of the limited time: That bothers me a lot. There is so much stuff that I wanted to say and I couldn't! But at least I got a solution for this: I will be using the rest of this post to write some of the things I would have said if I had the time to expand even more in the questions that were asked. So without further delay, let's go down to business:

That Excel yellow warning

One of the questions raised when I was showing how to create Excel files with FlexCel is why when I opened them in Excel, Excel wasn't showing a warning that the file wasn't created with Excel. What I answered is that Excel doesn't do any check that the file is a "genuine" Excel file or anything like this. As long as the file conforms to the spec (and even many files which don't conform) Excel will be happy to open it.

There are two types of warnings that Excel might show when opening a file:

1)The "Red" warnings:

This is not a very common warning, and it happens when the file contains invalid data. You should never get this warning in files created with FlexCel, and if you do, you should contact us so we fix it.

2)The "Yellow" warnings:

This might be because the file was downloaded from the internet, because it has macros, or in general when the file is valid but there are some security concerns. The particular question was about the "file is downloaded from the internet" warning, and so this is what I will cover here. First of all, note that the warning isn't related to FlexCel, it just happens whenever you donwload a file from the internet, no matter if it was created with Excel, FlexCel or whatever, and it looks like this:

alt="TMS Software Delphi Components ">
In the session I commented that the flag that triggers this warning is not really stored in the file, but in a separate hidden NTFS stream. But I didn't had the time to expand more into it. So today, I'll take the opportunity to dive a little bit deeper.

Firs of all, you can see the streams in a file by issuing this command in a command prompt:

dir /r

For a file downloaded from the internet, you should see something like this:

TMS Software Delphi Components

The tools to manipulate the stream in DOS are a little bit limited, but you can see the stream content in notepad by doing:

notepad  test_yellow_box.xlsx:Zone.Identifier

And there is also a "streams" utility by Mark Russinovich which you can use to explore NTFS streams: https://technet.microsoft.com/en-us/sysinternals/bb897440.aspx

But of course, this was a Delphi session, so we care more about on how to do it from Delphi itself. And it turns out it isn't difficult, as you can just delete the stream:

  DeleteFile('test_yellow_box.xlsx:Zone.Identifier');

This should get rid of the stream and the yellow warning on the file. Of course this isn't too useful since the files you generate locally don't have that stream, and you don't want to blindly remove the warning in the files you download from the internet. But it might be useful for some workflows, and actually, sometimes you really need to remove that stream in order to work with the files in certain apps.

The "Do you want to save changes?" warning and Excel 2016

Unable to leave a good thing alone, I spoke about other warning that I know many users care about (and you can already start to understand why 1 hour was never going to be enough to cover everything I wanted to cover). The warning dialog shows when you close the file immediately after opening it and without modifying it. The exact text of the warning varies with the type of file (xls or xlsx) and the Excel version, but normally looks like this:

TMS Software Delphi Components

Or sometimes like this:

TMS Software Delphi Components

It happens whenever you have formulas in your file and the Excel version the file declares it was saved with is different from the Excel version that you open the file with. What happens under the hood is that Excel recalculates the file when it was saved by an older version because the older version might have bugs in the calculated values. So then it offers to save the new recalculated values in the file, even if nothing did change. And it will also identify the new saved file as "Saved with the new Excel version" so it doesn't need to be recalculated again when you reopen it.

Again this is not specific to FlexCel: If you save a file with formulas in Excel 2010 and open it in Excel 2016, you will get a warning when closing the file.

Of course, FlexCel is not Excel 2010 or 2016, but we do have a property that allows us to "identify" the file as being created by an specific Excel version. So I showed first how the dialog appears in a simple file created with FlexCel. This happens because by default FlexCel identifies the file as created by an unknown Excel version, so it is always recalculated when you open it.

But then, I went to show on how you could make the warning disappear by setting the property:

xls.RecalcVersion := TRecalcVersion.Excel2016;

So I rerun the code and... as we could expect in a live event, the dialog stubbornly showed up. By that time we were already late in our schedule, so I had to move on to the next thing, but now I want to expand in what happened.

In short: There was a "January Update" of Excel 2016 which happened to introduce a bunch of new functions. (see https://support.office.com/en-us/article/What-s-new-in-Excel-2016-for-Windows-5fdb9208-ff33-45b6-9e08-1f5cdb3a6c73?ui=en-US&rs=en-US&ad=US

We were aware of this update, and since the functions introduced are probably the first useful new functions introduced since Excel 2007, we already had implemented recalculation for them all at the tms day timeframe (even if we hadn't yet released an update to the public). But what I hadn't realized is that this "January Update" also changed the recalculation id that must be saved with the file.

Files created with a "Pre-January-Update" Excel 2016 (or with FlexCel reporting as Excel 2016) would have a diffefent recalculation ID and trigger the save dialog when you opened them in "Post-January-Update" Excel 2016.

This was the reason the demo failed: FlexCel was still writing the "Pre-January-Update" ID into the file, but the Excel I used in the presentation had silently updated some days ago to the "January Update" and was recalculating the "old Excel 2016" file.

So we just released an update to FlexCel (6.7.16) which will:

1)Add full support for all the new functions in the Excel 2016 january update.

2)Identify the files saved with a RecalcID of Excel2016 with the "Post-January-2016" Id.

3)Add a new member "LatestKnownExcelVersion" to the TRecalcVersion enum. Now you can set:

xls.RecalcVersion := TRecalcVersion.LatestKnownExcelVersion;

And FlexCel will identify the file as saved with the latest version of Excel it knows about. So when Excel 2018 or whatever else comes and you update FlexCel, the new files you create will all automatically get the latest Recalc ID.

Virtual Datasets

This is one of the slides I had planned to show in the presentation, but which I didn't had time to:

TMS Software Delphi Components

And it was a shame, since many people contacted me after the session to inquire about this specific topic. So again, I will use the time I have in this blog post to expand on it:

As you might know if you are a FlexCelReport user, FlexCel can use any TDataSet, any TList and any TArray as datasources for the reports. But those 3 types aren't hardcoded anywhere, and you might have your own data containers which you want to use directly instead of copying the data to TList or a TDataSet. And it is not difficult to do so.

As you can see on the slide, FlexCel actually gets all of its data from 2 abstract classes: TVirtualDataTable and TVirtualDataTableState. FlexCel comes with specialized classes derived from them which implement it for a TDataSet, TList and TArray, but nothing is stopping you from creating your own specializations of TVirtualDataTable and TVirtualDataTableState.

Also mentioned in the slide there was an example project which creates specializations that allow you to use a TStringList as a datasource in your FlexCel Reports. You can get it here: http://www.tmssoftware.biz/flexcel/samples/stringlisttable.zip

To create a new datasource of FlexCel, you need to answer some questions like "how many records my datasource has?", or "what are the names of the columns for my datasource?". To do so, you need to create 2 different classes, descending from TVirtualDataTable and TVirtualDataTableState. Why 2 different classes? The difference between TVirtualDataTable and TVirtualDataTableState is that the first contains information which is static and can be used in different threads or different datasources in the same report without worries, while the second contains state information which changes for each dataset used, even if it the dataset is the same. FlexCel can keep a single copy of the TVirtualDataTable in memory and use it for all similar tables in a report, but it needs to create different TVirtualDataTableState classes for each table.

For example, a DataTable will have always the same columns and column names, no matter how many times it is used inside the same report. So the question: "what are the names of the columns for my datasource?" is answered in the TVirtualDataTable abstract class, by overriding the Get_ColumnCount, GetColumn, GetColumnName and GetColumnCaption methods.

For example, the TStringList datasource has a single column with the data (as it is just a list of strings). We will name this column "Data" and so the methods look like this:

function TStringListProvider.GetColumn(const columnName: UTF16String): Int32;
begin
  if not SameText(columnName, 'DATA') then raise Exception.Create('Unknown Column: ' + columnName);
  Result := 0;
end;

function TStringListProvider.GetColumnCaption(
  const columnIndex: Int32): UTF16String;
begin
  Result := GetColumnName(columnIndex);
end;

function TStringListProvider.GetColumnName(
  const columnIndex: Int32): UTF16String;
begin
  Result := 'Data';
end;

function TStringListProvider.Get_ColumnCount: Int32;
begin
  Result := 1;
end;

On the other hand, the question "how many records my datasource has?" depends in the master-state relationship and must be answered by a TVirtualDataTableState. For example, a detail table in a master-detail relationship might have 3 records for the first master record, but 5 for the second. In our TStringList example, we just override the Get_RowCount to return the number of items in the string list:

function TStringListStateProvider.Get_RowCount: Int32;
begin
  Result := FStringList.Count;
end;

If you now look at the source for our TStringList example, you will see that we have already covered almost everything. The only thing missing is to provide FlexCel with the actual values for a row and column. You do this by overriding the methods:

function TStringListStateProvider.GetValue(const column: Int32): TReportValue;
begin
  Result := FStringList[Position];
end;

function TStringListStateProvider.GetValue(const row,
  column: Int32): TReportValue;
begin
  Result := FStringList[row];
end;

And that is mostly it. The only method in the example that we did not cover is a method that will create a state from a TVirtualDataTable:

function TStringListProvider.CreateState(const sort: UTF16String;
  const masterDetailLinks: TMasterDetailLinkArray;
  const splitLink: TSplitLink): TVirtualDataTableState;
begin
   Result := TStringListStateProvider.Create(FStringList, self);
end;

And that is used by FlexCel to create the multiple VirtualDataTableState instances it needs from the single VirtualDataTable which you add with FlexCelReport.AddTable.

Of course this only shows the simplest virtual datatable wrapper that you can create. There are many other methods available for overriding in TVirthalDataTable and TVirtualDataTableState, which allow for advanced functionality. For example, if you want to do a lookup in the datasource with the <#lookup> tag, you might want to override the Lookup method (even when the default implementation is good). Some other functionality like DISTINCT might not be available if you don't override the corresponding method. But for most reports, what we covered here is enough.

The report designer

Another thing I got asked about is the report designer I had installed in Excel during the presentation. This is an Excel addin which appears as a new tab in the ribbon:

TMS Software Delphi Components

It allows you to design a report template in a simpler way, by dragging and dropping fields from a tag pane. The bad news is that it has been in its current state (not yet finished) for more than a year already, and I never seem to get the time to finish it. There are always more important things to do. But as I've been asked by many people in the tms day about the possibility to get a beta, I will do my best to get a beta released in a couple of weeks. Not all functionality will be working, but the basics should be there.

Adrian Gallero

This blog post has not received any comments yet.