is there a free way to write an .xlsx File plain from sratch?
Appreciate your answers
Beside MBS Xojo XL Plugin or Einhugur’s plugin.
You may just write a CSV file.
The problem is that i need colored cells, but thank you! Just wanted to ensure that their is no free solution.
If you don’t need very many features, it’s not that difficult to reverse engineer an xslt file to see how to make one with the formatting that you need.
But if that work takes longer than an hour or two, it may be cheaper to use the plugin.
Hi, I you want to learn how to parse files : do it… but If you really need it for a prof software buy the plugins, or try to find other solutions/ workflows … I know the world is saved with office… and if office is always available try to remote control it.
Thanks to all of you. I’ll see.
You can create html report and give the filename with a xls extension and it will open in excel
If you are a private user, you might want to try do develop it by your own. But it won’t be easy.
HTML is a lazy option too. But if you are planning to do a serious job, use one of the plugins: besides colouring you can set the metadata, inject formulas, inject different type (date, numericvalue, text) and much more. I’m using Einhugur’s Plugin (as I started with that one), but I’m sure that MBS has a lot to offer as well.
Of course it means spending money, but unless you are on a very tight or no budget at all and(!) have enough time to spend during a Covid lock-down don’t waste time into re-inventing the wheel. You already need some time to get into the logic of the plugins you will use.
If you are developing on your own, please make it open-source, and please share it with us ;-).
We are developers - we are lazy by definition. If html works then it’s a wonderful way to start.
Did a quick test - yup, works fine. I had to keep the html extension. But the result is acceptable. I forgot to remove the references to the pictures so that the text in red isn’t very visible:
are lazy by definition, no : do it once and do it right… or sometimes its better not to do it and ask again…
The easiest native Excel file to generate is the older xml format. It was easy to figure out, and easy to generate in a Xojo app. The .xlsx format is messy because the content is spread across several files. If I was going to do this without a plug-in, I would work with the .ods format, because the content is in a single file. There are other files, but they can be generated once, and become boilerplate data that you add to the output folder structure before zipping it.
My quick and dirty reverse engineering method is as follows.
- Use Excel to create a sample spreadsheet formatted the way you want your output file to look, including some sample data.
- Save it as an .ods format file (or .xlsx if you really must).
- Change the extension of the saved file to .zip
- Unzip the file. You’ll now have a folder with several files. The important one is “content.xml”.
- Open content.xml in either an xml editor or a plain text editor.
- Decide for yourself whether this is something that you would be able to generate in Xojo code, or whether you’d rather just buy a plug-in.
for my case, i rename the file to abc.xls and also make sure the content type for the html is as follows
SELECT CASE gExport CASE "Word" gHTMLstr=REPLACEALL(gHTMLstr, "[***ContentType***]", "<meta http-equiv=""Content-Type"" content=""application/msword;charset=utf-8"" />" ) CASE "Excel" gHTMLstr=REPLACEALL(gHTMLstr, "[***ContentType***]", "<meta http-equiv=""Content-Type"" content=""application/vnd.ms-excel;charset=utf-8"" />" ) CASE ELSE gHTMLstr=REPLACEALL(gHTMLstr, "[***ContentType***]", "<meta http-equiv=""Content-Type"" content=""text/html;charset=utf-8"" />" ) END SELECT
i use xl plugin from MBS to include embed images in the xls when client need a list of item with image in excel.
image cannot be include in the export create using html since the image has to be in the right place.
Also the other problem in html you can include your width and height of image you want to include in the tag regardless of the real dimension of image. but once export and open in EXCEL, excel ignore your dimension in the html and simply show the original size of image
If you can create your document from an existing Excel template, and if Excel is installed on the computer running the Xojo program, you can open a new spreadsheet using the template and use Xojo code to modify it and save it.
A long time ago Eugene Dakin published I Wish I Knew How to Program Word 2010 with Real Studio in Windows. My copy is dated May 2013. Not sure if this is still available. You can program Excel the same was as with Word. Also the trick to find out how to program something in Word or Excel is create a Macro, do your thing, then look at the VBA code generated. The VBA will translate easily into Xojo.
sound interesting… never know i can do that.
Yes, it works fine, But(!) it depends on what you are planning to do. If you want to have many sheets in an Excel, work with references or formulas it gets challenging in no time. Comparing this with the TCO for buying a plugin and you will most likely never regret your investment. Plus, you have a lot of helpers in those plugins for type conversions etc. They will work w/o Excel being installed on the computer, they are really fast … plus you can update them if you want, but usually they are just working fine for 99,9% of your requirements for years now.
But we are all different. In my world it is very rare that I don’t need to create an Excel in a particular project sooner or later.
for my application, the excel export is either csv without formatting or html rename and xls with formatting. usually is client who want to save the report as xls instead of pdf. So my requirement is much simpler.
I second that one. And that’s probably the difference between citizen devs and business devs. If you are coding “privately” you might enjoy doing it on your own, as it can be fun (w/o any cynicism).
You might even be able to build something you can sell in future. But those running an own business, should not try re-inventing the wheel, that’s at least my credo. I did not only waste time in the past, but tons of money by being stubborn to try doing everything on my own.
I had to learn this in the hardway ( see Linked In Rainer Greim).
But no one gives to time to do it right. As they know: you are the expert, they expect everything too fast and well tested, and if you starting to write invoices the same people don’t understand, why software takes so much time and is so expensive. Especially when they have seen power ides like Xojo…
And to make them happy , your are using power IDEs and write code in every action method, do copy and paste, and when you do reverse engineering you are wiping your eyes, but you are to afraid to change anything, because its running and making money.
BTW: that’s why I have started these 3 gotcha postings today. Use the tools and scan your Xojo projects ( maybe with help of friends or scripting engine) to get all these cool graphs … ( plantuml, graphwiz…)