Use 4D and PHPExcel to create xslx documents

xslx This is a topic that many people are looking for. is a channel providing useful information about learning, life, digital marketing and online courses …. it will help you have an overview and solid multi-faceted knowledge . Today, would like to introduce to you Use 4D and PHPExcel to create xslx documents. Following along are instructions in the video below:

“This is brent raymond and this is a demo of how to use php and and 4d to convert an older format excel document with the dot xls extension to current dot xlsx format. Which is the the new open document format that excel uses to be compatible with all other applications that have same kind of excel data. My name is brent raymond and from the 4t method for t in wakanda user group you can get to our website. Here for t method comm.

So just in a quick review. It s possible to create older format documents xls to be specific with with some with a variety of different ways actually currently i use me acos plugin specifically. I use the xls simple plug in to generate spreadsheets you can have several sheets you can set styles you can do everything. But it s the old format.

So i was looking for a way to create the new xlsx formats with with with 40 and you can get into the actual structure of the document and edit all the xml. However it gets really pretty hairy quickly and unless you know what you re doing you can corrupt a document. Pretty quickly so. So i did notice that there is this tech.

Note phpexcel library. With four tv12. Which went over how to import data from excel. Using php.

The ph p. Library phpexcel and 4d and here s you can find the website for php excel. At this. Web at this address and and get the current.

Version which is 18. As well we ll be using for the demo. Okay so right so you could say use php to export create and export. The all of you know the the current version excel document or if if you ve got existing code using other methods for creating the document and don t want to rewrite it at risk possibly having to call php commands hundreds of thousands of times.


Which can have it their own memory. Cost you could just create the excel document with the older older. Plugins and convert the document to the current version with the php excel library. So let s check it out so.

I have a database here called excel demo and i ll upload this to my github and post a link on 40 method. But it s again loosely based on some of the code that was found and the tech note. Which is pretty interesting if you want to see how to do some other things with get other information from excel. However i basically extended it to have a way to convert documents so within the package.

The 40 data bass packaged in the resources folder. You ll have your php. Dot. Ini .

And the php folder with the classes. This is just the the standard folder of classes that you ll get from php excel. When you download the project and the php excel dot php. Main script and this is the only thing that you have to actually add.

Which is the php excel wrappers. So let s take a look at that real quick so in this wrapper document. I have one function defined and let s take a quick step through that the functions called excel up verte you feed it an excel file path. The first step is just setting up some cell caching phpexcel is known for not being very memory friendly.

The documents can require a lot of memory. And so you have to do a few jump through a few hoops if you re gonna work with larger excel documents. If you re interested see this stack overflow link and look up how to use these cached object storage factory lookup to docs on that but anyway so we re setting up some caching. Then we ll create a new reader object to read the old excel file.


There s a few other interesting memory tricks that you can do if you re not concerned about any cell formatting like bold anor fonts or italics that kind of stuff you can have the script. Only read the data so you would uncomment this line again. If you re only interested in one sheet or another in an excel document. You can have it only load.

One cheater. Another or you can put in actually an array of sheets so getting down to the meat of the of the script here you load. The old excel file into this object. You created the reader object.

Then you create a writer object for with the excel 2007 format. Which is actually the xlsx format. And then you create name. Which is basically just replacing the extension and you save it out with with the the save function for the the writer and then this will just return.

The new path to the document. Now there s a lot that can be done here as far as hardening up the script for any kind of errors or try wrapping it and tries. I ll let you guys do this as it s just a proof of cat. How this is run from within 40.

So here is our our demo database. I have a test method. Which is it will access the demo data and the resources folder that a old format excel document it will upconvert it and then it ll show it on disk let s see what this method actually does it s quite simple. It s looking for the path to the wrapper document that has the the function that i defined in there.

It s getting the parameter currently. I just took the the flow from the tech note formatting. The the path for for use on the mac. However there s no reason why this wouldn t work on windows.


Although i have not tried it yet please let me know if it doesn t if you do try it yourself right. So. The this php executes calls the up firt excel up for function in this wrapper document. It is getting a return value of the new path to the document and it is feeding it a parameter of the old document path.

If it fails then there s a handy dandy a viewer for seeing what errors. The excel are the script take you back. And then the function returns. The new path to the document.

Again error handling you can can harden this up in a lot of ways to make sure everything actually worked and you can also clean up by deleting. The old xls document. If you like but again this is just a proof of concept. So let s go ahead and run.

It and see what it did okay it looks like it did it. But it did not actually show it on disk. So okay so so it did work not sure why it didn t show it on disk. But so this is the original document.

It s all the data is bolded and then this is the new document. We can go ahead and go in i ll just delete that and i ll show you how you can edit the the the script. So that you only convert the data instead of copying over all the bolded styles. So let s go ahead and run it again.

I ll put a little stop there just in case. There it s trying to show that okay yeah. Well that s why the show on disk didn t work it s actually the posix path. But we can as we can see it did actually work and we ll go and open that one more time in this case.


It did not actually copy the formatting. So if you re concerned about memory. That is a good way to limit the memory usage. And you can handle much larger documents with less chance of failure okay that s the demo converting old format excel documents to current open excel xml format excel decks to mini axis excel documents and if you have any questions or comments.

Let me know otherwise we ll see you at the forty method next. Forty method user group meeting thanks zipper part that i was just talking about which is which has this soft velvet part in front of it and then you get to the back compartment. Which which has these two different things they call it the cushion zone. Because it s between well between this area here and this area here.

So that if you have a laptop such as this one. And this will be the exact laptop that will go in this bag. So hopefully. This fits.

This is a 15 inch left on a 15 inch laptops aren t this big anymore. This is the old one. But it fits just fine with no problems and i have so many pockets on here. There will be no issue you know putting the power supply in here and any accessories that i want so yeah definitely this laptop will be the laptop.

They will be used with this bag thing and directly in front of it you have what they call the tech spot. And this is a pocket here so that you can put an ipad or android tablet or something right here you know it s any kind of tablet or if you had another small computer. I guess it would work there as well this one is not cushioned like this one. Here this right here has a cushion.

I would say probably a quarter of an inch between where the laptop sits and the tablet ” ..

Thank you for watching all the articles on the topic Use 4D and PHPExcel to create xslx documents. All shares of are very good. We hope you are satisfied with the article. For any questions, please leave a comment below. Hopefully you guys support our website even more.


Leave a Comment