Blog
All Blog Posts | Next Post | Previous Post
TMS Day followup: Using TMS FlexCel with TMS WEB Core
Wednesday, June 20, 2018
Another TMS day
They say time flies when you are having fun... It is hard to believe it has been about 2 years since my last blog post, which was also about a TMS Day. I see two options here: Either we start doing more TMS Days so I can blog about the sessions, or I just start actually writing down more stuff.On my discharge I'd like to say that I haven't been completely silent this 2 years: I've been writing the stuff I would normally write in a blog post in the "Tips and Tricks" on FlexCel docs. Ok, this is a poor excuse, but an excuse anyway and I have nothing better so I'll stand by it.
Now, it is time to go back to the TMS Day. As always, it was a great experience to be there and speak face to face with customers and sharing experiences . Me sitting here and writing a blog post just isn't the same. And to be brutally honest, speaking with the people did change my mind, and this is not just something I am saying because it sounds nice. When I arrived to the TMS Day I saw WEB Core as an interesting technology, but I saw little connection with it and the FlexCel reporting stuff, which is what I do. Reporting is normally a server-side thing, to be done on the server where the database is, and not something you would do normally client-side in javascript. So, there is little to integrate: you can call FlexCel from WEB Core, but you can call any other reporting solution too. You can also use WEB Core with FlexCel, but you can use any other web solution with FlexCel too. When I arrived, this use case (WEB Core in the client, reports with FlexCel in the server) was the only one I had in mind. When I was there, I spoke with a lot of people and learned about a lot of new use cases I hadn't even thought about. When I left, I had a lot of more plans for a deeper integration. But more on that below.
FlexCel as a server side solution for reporting
We started the session with a very basic description of what FlexCel does. As usual, this was a very packed session and I had only half an hour, so I didn't wanted to waste a lot of time explaining what FlexCel did. We just covered the basics: With FlexCel you create Excel files either with code or by writing tags in an Excel template, and then you can export those files to pdf or html. While we use Excel as a building block for the report, it is not needed to have the result in Excel. And as this session was all about the web, we just created pdf and html reports.1. A simple report
Some time ago I read about a really interesting project on where they used a raspberry pi to record how many times a day a baby would say the word "why". Being a father of a little 4 year old myself, I was very interested in the experiment and I thought it could be an interesting real world example on something that needed reporting. At the same time, with me also being lazy and with more stuff to do than I could possibly finish in five lives, I decided that instead of actually recording my child, I would just create a small database with my own estimates on how many times she would pronounce the word.I ended up with an access database that looked like this:
Yes, it was in Access, and you wouldn't use Access for a real application. Bit for a demo, it is fine to me. Just remember to use a real database in real applications.
The next step was creating two different applications. Since TMS WebCore runs on the client (javascript) and FlexCel is a server side product which runs in the server (delphi), I needed to create both a TMS webcore app for the client, and a Delphi WebBroker app for the server.
1.1. Server side
Server side we created a new WebBroker app by going to File->New->Other... then choosing "WebBroker" in the left panel and "Web Server Application" in the right.The focus on this session wasn't in how to create a FlexCel report or a WebBroker app, so I just used a pre-made example which you can download at the end of this post.
For the Excel report I just created an Excel file that looked like this:
I just wrote tags with the database fields (like <#data.measureddate>) in cells A3 and B3, then defined a name where the report would run and added a chart as to make it a little more interesting. Note: Right now FlexCel renders only charts in xls files to pdf and html, and while we are working in fully supporting xlsx this is not yet ready. So this is why I used an xls file instead of an xlsx.
I also added some conditional formatting just for fun:
After all, one of the nicest parts of doing reports in FlexCel is that you get access to all those simple features in Excel like conditional formats, tables or charts, so why not to use them? Even if in a case like this, I am not generating Excel files at all, only pdfs and html files. That was all I did in the template. Then in the Webbroker app, I added a datamodule to access the database, and the following code to the DefaultHandlerAction event:
procedure TWebModule1.WebModule1DefaultHandlerAction(Sender: TObject;
Request: TWebRequest; Response: TWebResponse; var Handled: Boolean);
var
Report: TFlexCelReport;
Xls: TExcelFile;
Ts: TMemoryStream;
Pdf: TFlexCelPdfExport;
begin
Report := TFlexCelReport.Create(true);
try
Report.AddTable('data', datamodule1.data);
Xls := TXlsFile.Create('....why.xls', true);
try
Ts := TMemoryStream.Create();
try
Report.Run(Xls);
Pdf := TFlexCelPdfExport.Create(Xls, true);
try
Pdf.BeginExport(Ts);
Pdf.ExportSheet;
Pdf.EndExport;
finally
Pdf.Free;
end;
Response.SendStream(Ts);
finally
Ts.Free;
end;
finally
Xls.Free;
end;
finally
Report.Free;
end;
Note that until here, we haven't used TMS WebCore at all. This is just a server side app which runs a report over a database, and returns a pdf when you call it. Note also how we got the conditional formatting we wrote in the template for column B shows in the final report, and the chart is filled with data.
1.2. Client Side
Ok, now what about the client side? Imagine we have a TMS WebCore app, and we want to show the report when an user clicks a button on it. How do we call the server app we coded just moments ago?To answer the question, we will create a new TMS WebCore application, and drop a WebButton:
Then, we double click the button, and write the following code:
uses ... Web,...;
...
procedure TForm1.WebButton1Click(Sender: TObject);
begin
window.open('http://localhost:8080');
end;
2. A more complex integration
In the previous section we saw a very simple example on how to integrate reporting in WebCore. There was basically no integration between WebCore and FlexCel, and all the above can be resumed in the lines:uses ... Web,...;
...
procedure TForm1.WebButton1Click(Sender: TObject);
begin
window.open('http://localhost:8080');
end;
But what if we wanted a little more integration? Well, you can do that too, and this is what we covered in the second demo. As for the first demo we had used PDF, on this second demo we will be showing HTML reporting. And while we could call the html reports also with window.Open as in the first case, here we want to integrate the report inside the TMS WebCore app.
2.1. Server Side
Server side, we are going to use the same template that we used in the first example, but export to HTML instead of PDF. We will use a FlexCelHTMLExport class instead of a FlexCelPDFExport class.We are also going to embed the images in the HTML file (using HTML5) so we don't have to feed separated images and html to the html component that is going to display the report client side. We are also going ot set the image resolution to 192 dpi so the chart looks crisp in the generated file.
procedure TWebModule1.WebModule1DefaultHandlerAction(Sender: TObject;
Request: TWebRequest; Response: TWebResponse; var Handled: Boolean);
var
Report: TFlexCelReport;
Xls: TExcelFile;
Ts: TMemoryStream;
Writer: TStreamWriter;
Html: TFlexCelHtmlExport;
begin
Report := TFlexCelReport.Create(true);
try
Report.AddTable('data', datamodule1.data);
Xls := TXlsFile.Create('......why.xls', true);
try
Ts := TMemoryStream.Create();
try
Writer := TStreamWriter.Create(Ts, TEncoding.UTF8);
try
Report.Run(Xls);
Html := TFlexCelHtmlExport.Create(Xls, true);
try
Html.HtmlVersion := THtmlVersion.Html_5;
Html.EmbedImages := true;
Html.ImageResolution := 192;
Html.Export(Writer, 'report.html', nil);
finally
Html.Free;
end;
Response.ContentStream := Ts;
finally
Writer.Free;
end;
finally
//don't free the content stream
// Ts.Free;
end;
finally
Xls.Free;
end;
finally
Report.Free;
end;
2.2. Client Side
We are going to create a new TMS WebCore application, drop a button, a WebHTMLContainer, and a WebHTTPRequest component:
This time we are going to use the WebHTTPRequest component to get the report. WebHTTPRequest is async, so we need to call it from the button click event, and then when the answer data is available, load it into the WebHTMLContainer. The first step then is to set the server address in the WebHTTPRequest properties:
Note that for this example we will use port 8081 instead of 8080 as in our last example, so we can run both at the same time. This means that we also need to change our server app to serve in port 8081 instead of 8080.
Then, on the button click event let's call the WebHTTPRequest, and let's also write the Response event of the WebHTTPRequest so it fills the WebHTMLContainer with the data:
procedure TForm1.WebButton1Click(Sender: TObject);
begin
WebHttpRequest1.Execute;
WebButton1.Enabled := false;
end;
procedure TForm1.WebHttpRequest1Response(Sender: TObject; AResponse: string);
begin
WebButton1.Enabled := true;
ShowMessage('Ok!');
WebHTMLContainer1.HTML.Text := AResponse;
end;
<style type="text/css">
#TForm1_HTMLContainer1 {overflow: visible!important}
</style>
So after all of this, it is time to run the application! And we get...
CORS
What was that error? Everything was going so well! But let's not panic: The problem here is with something called Cross-Origin Resource Sharing (CORS) Basically, we are trying to access a resource in the server (localhost 8081) from the client (localhost 8000) and this is not allowed by default. We need to specifically allow requests from localhost 8000 into the server.And you allow the requests by setting the headers Access-Control-Allow-Origin and Access-Control-Allow-Headers
The full code in the server should now be:
procedure TWebModule1.WebModule1DefaultHandlerAction(Sender: TObject;
Request: TWebRequest; Response: TWebResponse; var Handled: Boolean);
var
Report: TFlexCelReport;
Xls: TExcelFile;
Ts: TMemoryStream;
Writer: TStreamWriter;
Html: TFlexCelHtmlExport;
HtmlState: TPartialExportState;
begin
Response.SetCustomHeader('Access-Control-Allow-Origin', '*');
Response.SetCustomHeader('Access-Control-Allow-Headers', 'X-Custom-Header, Cache-Control');
Report := TFlexCelReport.Create(true);
try
Report.AddTable('data', datamodule1.data);
Xls := TXlsFile.Create('......why.xls', true);
try
Ts := TMemoryStream.Create();
try
Writer := TStreamWriter.Create(Ts, TEncoding.UTF8);
try
Report.Run(Xls);
Html := TFlexCelHtmlExport.Create(Xls, true);
try
Html.HtmlVersion := THtmlVersion.Html_5;
Html.EmbedImages := true;
Html.ImageResolution := 192;
HtmlState := TPartialExportState.Create(TFlexCelWriter.Null, '');
try
Html.Export(Writer, 'report.html', nil);
finally
Html.Free;
end;
Response.ContentStream := Ts;
finally
Writer.Free;
end;
finally
//don't free the content stream
// Ts.Free;
end;
finally
Xls.Free;
end;
finally
Report.Free;
end;
end;
3. Even more integration
We didn't got to cover this section in the TMS day, as it was already too much to say for 30 minutes, but I would like to mention it here anyway. The last example (the "integrated" one) does work, but it is not really ok. It is embedding a full HTML document (the one created by FlexCel) inside another HTML document (the TMS WebCore app), and that is not valid HTML. We could have used an iframe instead, but iframes are so ridden with security issues that it might be a solution worse than the problem.To do a nice integration, we need to separate the HTML headers from the body. Luckily FlexCel HTML exporting was designed from the start to allow you to get the different parts of the html document so you can integrate the HTML output in your site.
3.1. Server Side
On the FlexCel side, we need to use a TPartialExportState object instead of writing directly to an html file. TPartialExportState allows us to get the different parts of a report instead of the full text.The next thing is that while we want to separate the css from the html so we can write them in different places in the client, we would like to keep everything inside a single request to the server. So we will be sending an encoded response, where the first part is the css, then we have a 4-char 0 separator, and then we have the html. Client side, we will separate this response back to the 2 original parts, and write them in the correct places.
So server side, the code ends up as follows:
procedure TWebModule1.WebModule1DefaultHandlerAction(Sender: TObject;
Request: TWebRequest; Response: TWebResponse; var Handled: Boolean);
var
Report: TFlexCelReport;
Xls: TExcelFile;
Ts: TMemoryStream;
Writer: TStreamWriter;
Html: TFlexCelHtmlExport;
HtmlState: TPartialExportState;
begin
Response.SetCustomHeader('Access-Control-Allow-Origin', '*');
Response.SetCustomHeader('Access-Control-Allow-Headers', 'X-Custom-Header, Cache-Control');
Report := TFlexCelReport.Create(true);
try
Report.AddTable('data', datamodule1.data);
Xls := TXlsFile.Create('......why.xls', true);
try
Ts := TMemoryStream.Create();
try
Writer := TStreamWriter.Create(Ts, TEncoding.UTF8);
try
Report.Run(Xls);
Html := TFlexCelHtmlExport.Create(Xls, true);
try
Html.HtmlVersion := THtmlVersion.Html_5;
Html.EmbedImages := true;
Html.ImageResolution := 192;
HtmlState := TPartialExportState.Create(TFlexCelWriter.Null, '');
try
Html.PartialExportAdd(HtmlState, 'report.html', '', true);
//We will encode the css and body in the same Response.
HtmlState.SaveCss(Writer);
Writer.Write(#0#0#0#0); //Use 4 character 0 as separator between CSS and Body.
HtmlState.SaveBody(Writer, 1, '');
finally
HtmlState.Free;
end;
finally
Html.Free;
end;
Response.ContentStream := Ts;
finally
Writer.Free;
end;
finally
//don't free the content stream
// Ts.Free;
end;
finally
Xls.Free;
end;
finally
Report.Free;
end;
end;
3.2. Client Side
Client side, we need to re-separate the css and the html, then output the html into the Text of the WebHTMLContainer, and the css into the header of the page.To output the html, we will use the same code as in the last example. To output the css, we will use the method AddInstanceStyle in TControl. Since this method exists only in web controls but not in normal Win32 controls, we need to write the method inside {$IFNDEF Win32} defines.
There is one last issue to address: FlexCel returns the full css including the enclosing <style> and </style> tags. But AddInstanceStyle expects the inner HTML without the <style> tags. So for this example, we just had to do some poor man's parsing and manually remove the <style> and </style> tags in the css returned by FlexCel. This should not be necessary in the future, since for FlexCel 6.20 we are adding a new overload TPartialExportState.SaveCss which has a parameter includeStyleDefinition. Once we release 6.20, you will be able to just call SaveCss with includeStyleDefinition = false, and there will not be a need to remove it manually to call AddInstanceStyle.
So finally, the code in the client ends up like this:
procedure TForm1.WebHttpRequest1Response(Sender: TObject; AResponse: string);
var
cssPos: integer;
cssString: string;
innerCssStart, innerCssEnd: integer;
begin
WebButton1.Enabled := true;
cssPos := Pos(#0#0#0#0, AResponse);
if (cssPos < 1) then
begin
ShowMessage('Invalid response from server');
end
else
begin
ShowMessage('Ok!');
end;
WebHTMLContainer1.HTML.Text := copy(AResponse, cssPos + 4, Length(AResponse));
cssString := copy(AResponse, 1, cssPos - 1);
innerCssStart := pos('>', cssString) + 1;
innerCssEnd := pos('', cssString);
{$IFDEF WIN32}
ShowMessage(copy(cssString, innerCssStart, innerCssEnd - innerCssStart));
{$ELSE}
WebHTMLContainer1.AddInstanceStyle(copy(cssString, innerCssStart, innerCssEnd - innerCssStart));
{$ENDIF}
end;
4. Hyperlinks
Ok, why stop now? There is still so much to cover! But well, I only had 30 minutes to speak so in the TMS day I stopped in the second demo. In this post we have a little more time, so I'll cover one big pink elephant in the room I managed to ignore up to now: Hyperlinks.HTML is supposed to be about hyperlinks, it is right there on the first "H" in HTML. Wouldn't it be cool if we could add hyperlinks to our reports? I will answer this for you: indeed it would be cool. We could do for example a drill down report where we list the whys per day as in the previous examples, but when you click on a date, you can see a detail on how the whys evolved during that day. And this is exactly what we are going to try to do in this part.
4.1. Server Side
Server side, we will have to modify our template, and add a new one for the details. We will rename why.xls to why-master.xls, and add an hyperlink at cell A3. The hyperlink will have as target the URL:http://tmsexample.com/detail?day=*.data.measureddate.*
As in Excel you can't write FlexCel tags like <#data.measureddate> inside an hyperlink, FlexCel allows the alternative syntax *.data.measureddate.* instead, and that is what we used above.
We also used a bogus domain name, tmsexample.com because Excel only understands absolute hyperlinks. When exporting to html with FlexCel, we will set the BaseUrl property in the FlexCelReport component to:
html.BaseUrl := 'http://tmsexample.com';
We will also create a why-template.xls which is similar to the master, but with different formatting and a link to go back in cell A1 instead of a link to drill down in cell A3.
Next step would be to add a new DataSet to the datamodule to do the query for one day, instead of the query grouped by days that we had before.
And finally, the server now needs to handle requests for the detail and for the master. We will do this by adding a new action to the webbroker module:
The actions now are like this:
procedure TWebModule1.WebModule1DefaultHandlerAction(Sender: TObject;
Request: TWebRequest; Response: TWebResponse; var Handled: Boolean);
begin
RunReport('why-master.xls', datamodule1.data, TDateTime(0));
end;
procedure TWebModule1.WebModule1DetailHandlerAction(Sender: TObject;
Request: TWebRequest; Response: TWebResponse; var Handled: Boolean);
var
date: TDateTime;
day: string;
begin
day:= Request.QueryFields.Values['day'];
date := Trunc(StrToDate(day));
DataModule1.detail.Parameters.ParamByName('day').Value := date;
RunReport('why-detail.xls', datamodule1.detail, date);
end;
Note: In this particular case I am using the dates without any preprocessing as parameters for the detail report, but this is far from ideal. You can never know if 1/3/1999 is March 1 or January 3, and client and server might understand dates differently, so the best solution is to use a neutral format like "yyyy-mm-dd" or even the date serial number. But to keep this example simple, I just used the localized dates as parameters.
With these modifications, if you now run the server app, you should see a screen like this:
And if you now click on a date, say January 8, you should see the details for that day:
You can click in the arrow at the top to go back to the main report.
4.2 Client Side
Finally, the last thing to study is how to make this work as a TMS WebCore application. At first sight, we might think that the application from the last example should work: after all the master report has relative links to the details, and the details to the master, so it all should work transparently. It is already working if you call the webpages directly.But, if you try the application, you will notice that the master report loads fine, but when you click on the links, you get an error:
The explanation on why this is happening is simple, but it might not be completely intuitive. If you look at the Url in the previous screenshot, you'll see it points to http://localhost:8000/detail?day=1/6/2018. But the server is running in port 8083, so that is why the error. The relative Url in the report is being resolved with the address of the client, not the server.
The approach here works for a separate report (one that you would open with Window.Open) but it just won't work for reports that are integrated in the app. For that, we need to dig a little bit further.
5. Hyperlinks in integrated reports
As we have seen in point 4, when running integrated reports, we can't just have the reports link to other subreports. Those links would take us away from our app, which is running as a single page in the client.What we need to do is to convert those links in the master report to javascript calls, and then modify our app to handle those javascript links and open the correct report inside our application.
5.1. Server side
Server side, we now need to generate Javascript links, not links to a different page.While we are at it, we will use the date serial number instead of the date string as the parameter, as to avoid the problem of what date is "1/2/1999" which we mentioned in the previous section.As the expression in the hyperlink is going to get a little complex, we are going to start by creating a config sheet in the master report template. A config sheet is just a special sheet in a FlexCel report where you can define a lot of the stuff that goes into the report. In our case, we are going to add an expression named detaillink which we will define as:
detaillink = javascript:pas.Unit5.Form1.OpenDetail(<#evaluate(VALUE(<#data.measureddate>))>);
Then we will edit the hyperlink itself, and change it to be:
http://tmsexample.com/*.detaillink.*
The rest of the application is going to be the same as in our last example, but with a small meaningful difference. This time we are going to add a slash at the end of the BaseUrl property.
html.BaseUrl := 'http://tmsexample.com/';
We will do similar modifications to the detail template, so the links call an OpenMaster() javascript method.
5.2. Client Side
Client side is where we have to do more changes. Server side, we are now generating links which call some "OpenDetail" and "OpenMaster" methods in javascript, and pass the serial number of the date as the parameter for OpenDetail. We now need to define those functions in Javascript.Luckily TMS WebCore makes it simple. We are going to just define two new pascal methods as follows:
procedure TForm1.OpenDetail(const Date: integer); begin WebHttpRequest1.URL := 'http://localhost:8084/detail?day=' + IntToStr(Date); WebHttpRequest1.Execute; WebButton1.Enabled := false; end; procedure TForm1.OpenMaster; begin WebHttpRequest1.URL := 'http://localhost:8084'; WebHttpRequest1.Execute; WebButton1.Enabled := false; end;
Now, when we get a link like "javascript:pas.Unit5.Form1.OpenDetail(43104)" from the report, the browser will call the method OpenDetail in our code, and pass 43104 as a parameter. As you could see in the code above, we use that parameter to fetch the correct report from the server, and then it is loaded as any other report.
So to round it all up, and as this is a blog about a tms day, below you can see a small video showing how this small reporting app ended up working:
The future
I wasn't originally planning to speak about the future when I started preparing the presentation, as I was just planning to keep FlexCel server only. But as I mentioned at the start, after speaking with people on the TMS day I got convinced that we need to do more with FlexCel in the Javascript front end.So where do we start? To be realistic, there is little chance that FlexCel will be compiled with TMS WebCore in the near future. FlexCel uses a lot of generics and other stuff that is not supported by the pascal to js compiler, and to be 100% sincere, we can't even compile FlexCel with Lazarus which supposedly has the features we need. (and believe me, we've tried).
But FlexCel is not only FlexCel for Delphi. We also have FlexCel for .NET which could be converted to asm.js or webassembly. Once it is javascript, it doesn't matter if we started from Pascal or C# code, it will work the same.
FlexCel .NET could be an option. But in this TMS day we introduced another possibility, which seems likely to be the one that wins. You know, there has been a third branch of FlexCel living a "secret" life since 2015. This branch is fully written in C++ 11, and C++ converts pretty well to Webassembly. I said it on tms day and I will say it again now: There is no guarantee that FlexCel for C++ will ever reach a stage where we release it. Please don't wait for it. Our priorities are in FlexCel itself, right now more specifically in FlexCel 7 which will have xlsx chart rendering and will let us do the same demos we did here with xlsx files instead of xls and still show the charts. FlexCel C++ is a side project, which gets time only when there isn't anything more urgent, which is very little time.
Now, with the disclaimer out of the way, FlexCel for C++ is actually working for small cases, and the conversion to Webassembly was so seamless that it looked like magic. So I really wanted to show it working. We took some small tests which are passing in C++, compiled them with Webassembly, and saw the results. I am not going to replicate what we did there, as the post would get too long, but I just want to share some screenshots with the final results.
This is the tests running in C++:
And here they are running in node.js:
Yes, they look the same, and no, they aren't even remotely similar. One is running compiled C++ code in a mac, the other is javascript running under node.js. And the tests aren't trivial either, they are reading and creating xlsx files already. And yes, if I change the code, I can see the assertions break in both C++ and Javascript; we did that live on tms day. It does seem kind of magical to me.
So this are the plans, or maybe the dreams right now. What the future will actually bring nobody knows, but it feels good to share those dreams with you. With a little more bit (actually a lot) of effort they could actually come true. I know I am really looking forward to have C++ and Webassembly versions of FlexCel.
Q & A
Sadly this time we didn't got time to do a Q&A, and I feel sad about it. Q&A are the parts that I enjoy more on the presentations. To make up for it, please ask your questions in the comments!You can get the source code for all the demos and the powerpoint slides used in the presentation here: http://www.tmssoftware.biz/flexcel/samples/tmsday-flexcel-and-webcore.zip
Adrian Gallero
This blog post has received 1 comment.
All Blog Posts | Next Post | Previous Post
Holger Flick