Servoy and Excel – Importing and Exporting data
Servoy and Excel – Importing and Exporting data
Today one of my favorites topics something that you’ve been able to do very for a very long time in in ServoyBut in the browser things work differently so today is dedicated to excel and doing integrations in a WebRAS forgetting data in and out so Sean Over to you. Is it number 36 already number 37? That’s wrong number 37. Yeah last week was our last one was number 36 Right number 37 excel integration Correct. Um my screen visible, right? Yes Okay, yeah, so Like we always do we’re gonna start with just a few demos To show you what you can do and then we’ll jump into Bit of the source code so you can see how it was done and then we do a brief recap With slides so you get to you get to wrap it up and and understand everything I think today’s a Topic will be fairly short. We’re just going to do a bit of examples doing exporting and importing with excel The reason I picked this topic is you know, it just comes up all the time With customers who are working with or prospects that are evaluating ServoyThey want to know if it supports excel integration So I think this is a good one to just get out there in a tech webinar get it recorded and have it on hand So that everyone can Understand what it’s about So we’re gonna jump into the first demo Which is going to be basic exporting? After that we’ll do like sort of formatted exporting and then we’ll do do a bit of importing So you can see I have an example solution up here in my browser. It has Just a list of order records Let’s say I want to export what’s what’s in the found set of the form so Right now I have everything but I could also do a search and I don’t know put in comita and we get fewer records now And now I want to do a basic basic export So I built a little field chooser here. It is showing me the columns in this table But because Servoyallows you to look across the data model even at runtime I decided to also show calculations and some related data providers going one level deep So I’m gonna just pick a few here Order date as a primary column Order total is a calculated value and then we get down to some of the relations so we’ll export the customer name and the Maybe the employee first name and last name After I click okay, you can see that there’s a download here And because I’m too cheap to pay for Microsoft Office. I have some trial version But you can see here that we get We get our columns here with including the Related data providers also the order total over here. You can see is a calculated value So that’s your basic Excel export. This is an XLS file. We also support XLS X The newer XML version as well. So both file formats are supported for reading it writing Okay, let’s go to the next example or let’s look at the code rather So I have On that form I have a method called Excuse me let me close all these It was called basic export that was called when I clicked that toolbar button And I did I did make a field chooser Which is gonna show and return a list of fields I don’t think that that’s really worth looking at because it doesn’t really have anything to do with Excel That’s just looking at my data model Once we get the fields I have a generic method here and this is actually in a base form. You’ll notice this is in excel based.js. So really I could create another form and the export functionality would work the same. It’s fairly generic So it allows us to pass in what data providers who want to export if we want to override the column names with some something nicer and then Pretty much it’s one line of code right here and it’s The Excel export plugin. This is the most basic way to export in excel and this generates xls format and The you can just bind it to the found setting you can specify which data providers you want to export You’ll notice there’s a third option for template That’s optional That’s if you have an existing excel file that you want to If you want to use or write to so if you already have something format at the way you like it You can just you can have a template, you know, and then fill it and export it One note because we are in the browser Writing files for download is a bit different as yon mentioned So I did create a sort of sub method here private method that I reused a lot But it’s also just one line of code When you do the plugins right file option in the web clients in the ng client If you pass in a simple file name like export dot xls It actually dynamically converts that into a download for you So I didn’t have to do anything special To get the download. I just do plugins dot file right file And because it’s not an absolute path Then it’s going to and we’re in the web client. I’m using the string argument It’s going to convert that into a dynamic download So you’ll notice that that just went right to My browser is download Over here and you can see the URL is generated dynamically with some uid So that’s handy to know anytime you want to download a file in in In the ng client including excel files Okay, so that that example was very simple It was one line of code to generate the export in another line of code to write the file and make it available for download However, you notice that there was no formatting in the excel although I could have specified a template We do support another api for reading and writing excel files at a very granular level So you know sheets and rows and and cells and formatting and all that sort of thing So I think it’s worth looking at an example That does a formatted export and I did something very simple Which is just to highlight the header columns and make them bold and change the background color So let’s take a look at that example This time I’ll I’ll get the same few fields maybe in the order total. Let’s do display address. That’s another Another calculated value. We’ll do company name Okay So now you can see that I’ve done a bit of formatting here. We make the column headers are are orange and Orange background color and bold font That’s just one example of course you can get pretty granular you can get into cell formatting in terms of values you can You know anything that you can do in excel really you can do programmatically through formatting Let’s take a look at the source code for that We have another method in the base class that I made here called Excel export formatted Again, it takes a list of those data providers. So we’re telling it what columns to export um And you can see that this time we didn’t call the excel export plug-in. We’re calling a Scope, so this is an extra library that you can add to your project through the web package manager The way that you would do that is you would you would go to your project You would go over to the web packages node in the solution explorer right clicking Download web packages and Just a moment we should get a list of the web packages now. Typically this is used to download Components and things for the browser, but you can also just get modules for servoic solutions and If you get the sfy utils package With the excel extension this one right here It’ll import it and include it as a module in your in your application You’ll be ready to go. There’s one caveat to that you also have to get a Library to put in your uh in your server lib folder, so this is based on an The Apache poi project, which is a Java extension Uh, and we just have to add that Manually and I’ll show you where to do that in a minute But let’s take a look at the code for the export So now that we included that project we get this scope and we can programmatically create an excel workbook Excuse me. I’m basing that off of Data set so I’m calling convert to data set with my found set for this particular form again This is in an arid form and I specify which data providers we want to export And then the format here is going to be excel xl sx. So the XML version of the excel file format Then we can we can create sheets So I we can create as many sheets as we want But this is sort of a quick way to do it because it’s it’s creating it from the data set We can also build it up like row by row cell by cell as well Um, so if you look at the next few lines here We have create some header style I set the font to be bold. That’s what this one here is Um, and the fill background being light orange, etc Um, really you get code completed on all this stuff once you start to work with it. So Um, you know, I could set borders, etc Um, and um You know fill patterns and things like that I can hide and show columns This is just on the this the styling It doesn’t even get into the data Anyway, it’s a pretty extensive API So I don’t want to get into all the all the options You can go to our wiki for that on the on the project homepage, which I’ll show you Uh, toward the end of the the presentation Uh, anyway, it’s just a little bit uh, more work than the plug-in version Which is one line of code, but here you get more control Uh, at the very end, um, we call workbook get bytes, which returns A byte array and then I reuse that same Uh, method to write the file for download. You could also call workbook I’m not getting code complete here It’s right file And and then you can pass in a file path. So if you’re working on a network file system And you don’t want this for download each one or write it somewhere. You can you can write Write it out to a file So that’s the formatted export. Uh, let’s talk next about uh, importing Excel files Because that’s a bit trickier because you don’t know what you’re going to get when you import it So I made another routine here Uh, with the import you see I get a file selection dialog I’m going to go and grab Let’s grab this one that I created earlier And after I upload it you see that it’s actually going to import it and show it in um In a grid here. So this is just some arbitrary data set that was generated from that that Excel file um The code for that is in another um, method here in In the base form Uh, and you can see that I’m calling the file plug into show file open dialog. Now again This works a bit differently in a browser Because a browser never has access to just freely browse across your file system It always has to be in the form of you’re about to upload a file in a you know a file form post So um, what this method does is it it allows you to specify a callback Because really it’s going to you’re going to have to receive the file as an upload So I pass in the on upload method which is right down here and this gets an array of files that were uploaded Because it could be multiple In this case we’re just assuming one Uh, and I made another form called Excel import grid Which is really just a form with an empty grid on it And it has this open method Uh, so the first thing it’s going to do is uh, is get the workbook for the file that was passed in And the next thing it’s going to do is uh, get the first sheet And then after that it’s going to get the data for the sheet Uh, pretty much that gives us a uh, jazed data set and we’re going to then um, create a data source from that so that’s done Somewhere down here right here online 28 So um, you can also at the low level read over rows and columns But just calling sheet get data or sorry sheet get sheet data Well return a jazed data set which is you know you can convert to an in-memory data source And use that anywhere like bound to a form So uh, that’s a shortcut i could get in there and you know do things like sheet You know uh get a row that some index and you know get a cell And get a value Or read something about a cell formula or you know Comments or formatting or something like that Uh, but we don’t need to do that because we have this sort of um shortcut method to just get the sheet data directly Um, so then we did create an in-memory data source You can do that for many jazed data set in this case. It’s coming from the excel import uh, and then what I did is I I Um I show this form in a dialogue and it has some logic to programmatically add columns So down here it’s it’s adding columns to that grid But that’s unrelated to this so this would work on any on any Excel spreadsheet I didn’t I didn’t have a canned form that was mapped to those particular columns So that’s how we do the import example Um, just want to take you over a sort of a an overview of of what we saw And then we’ll take some questions um, so for the The basic the basic import uh, we use the excel export plugin this ships with Servoyby default So you don’t have to do anything to set this up You should find it in any project that you’re you’re using and it’s one line of code. It’s um It’s called excel export and you give it your found set And you give it an array of data providers that you want to export You can optionally give it a template Uh, and it’ll it’ll fill uh Uh, an existing spreadsheet, you know with you know, that’s formatted the way you want it uh with the data The other example we saw was was doing a more formatted export where we have control over over the formatting The example I showed you um, called this method create workbook from data set So really we took a js data set which had generated from the forms found set And once we get that we can just generate the workbook and Without doing anything else you could just write it to a file And and and we showed how we could download that But the benefit of doing it this way is that you can get into some formatting So one thing you could do is set the cell styles or the row styles or the header styles Again, this is a pretty uh comprehensive API that covers the excel spec So I didn’t want to show you everything I just showed you how to change the background color and the font But it would look something like this And that’s that comes with this excel you tilt extension that you can get through the web package manager On the import side, um, it’s the same extension and um, all you have to do is get a workbook object for a file So once I handle the file upload Um, then I’m able to just get a workbook and I can start reading through that I can read down at the individual level Or I can do something quick and dirty like uh sheet get sheet data returns a js data set And that’s um what we’re able to generate a form to you know form that’s bound to etc Um, one thing that I would point out is the setup for the The um Excel utils There is um really good instructions here from my colleague Patrick On how to get the uh the underlying library in because right now the web package manager doesn’t import plugins It just imports uh web components and modules Uh so you can import the module uh through the web package manager But you’re going to need to add your um your dependency on a patchy poi Which is the underlying library that does the excel parsing So there’s really good instructions here on how to do that The reason it’s a little complicated is there’s different versions of it and there’s also other Extensions that use the same library for example the the suroy jasper reports So depending on your setup uh you may take different steps And that’s why I recommend that you really go to the the home page on the sui utils project page and Sort of review these instructions uh to get set up So I’ll leave up this slide which has the links on it Well, I take some questions. Yonder. We have any? Hello, yeah, and can you hear me? It does help if I click unmute on my mic. I was already talking um You see technical people and buttons is always a challenge And there was a question on the the py version Which but I see a follow-up that it has been answered And this question from Sandler which says are XLS X files also supported I guess he asked us before you pointed out that yes, they are supported Yeah So it looks like um So the reason we are not shipping the Most recent py because it’s Conflicts with jasper reports is it right because I don’t understand that either Uh, yeah, so I mean it’s not really just the most it’s not a matter of recent There’s sort of extent and extended version of it And we do ship boy by default which is what what is used in the The Excel port plug-in excel export plug-in But yeah, we also ship it in the jasper report which has the extended Support for excels x files for example So to really get into it. That’s the one you need Basically if you if you install the jasper reports plug-in you get the libraries that you need But you also need to then remove a reference to The the older point the point version that’s in the default shipment so maybe someday we should probably resolve this may in you know in what’s shipped But it’s it’s one of those things where which version do you want depends on on what you’re doing But if you want to use the sfy utils excel extension Then just follow the instructions there you basically Get the one that comes with the jasper reports plug-in And make a few changes to your configuration and your set All right Okay, there’s the last question from Sandler other presentations used in webinar Also available for download or suggest a video that you guys post Yeah, we generally post slides as well to the forum. There’s a running forum thread For this topic. We love to hear feedback there. It’s been kind of quiet. So I will post the slides for this and links to the recordings in the forum So if you visit us there, please leave us some feedback and you can get the slides there as well Cool and it’s this module gonna be downloadable as well that you demo does Yeah, we can make this downloadable and put it on the demo that’s servo.com as well Oh, that’s very cool. Yeah, that’s something we don’t advertise very often guys, but demo.servo.com is a very handy reference place to go to and Not only look at things in action. Maybe you could bring it up a shanfrak quick So people can see what it is So I love to go to demo.servo.com if I need a To show something or see something in in action However, you can also download code right there. So if you’re like, oh, I like this component or I like this chart Then there is a button right at the top That you can that you can click on and you can actually download a module or a sample or a piece of code and Put it right in your own application so the code is right there So if you’re lazy like me and you know, not write all the code by yourself then you just go there and copy past it