Categories > OpenTBS with XLSX >

OpenTBS with xlsx - Deleting formulas in xlsx templates after values have been calculated

The forum is closed. Please use Stack Overflow for submitting new questions. Use tags: tinybutstrong , opentbs
By: Mayhaffs
Date: 2014-05-01
Time: 02:41

OpenTBS with xlsx - Deleting formulas in xlsx templates after values have been calculated

I'm currently working on exporting form data to a massive xlsx template that is heavily formatted. The biggest obstacle so far has been formatting cells like paragraphs with multiple line breaks. I was able to get this formatting working through excel formulas on a hidden sheet combined with a formula on each of the destination cells.

Unfortunately I've now realized that I cannot add any visible formulas or macros to the template and there is no way to show a value in a remote cell without using some sort of visible alteration like a formula or a macro in excel itself.

Does anyone know how to delete formulas in an xlsx template and replace those cells with only a text values?

Any ideas or suggestions would be greatly appreciated.
By: Skrol29
Date: 2014-05-01
Time: 23:11

Re: OpenTBS with xlsx - Deleting formulas in xlsx templates after values have been calculated

Hi,

In Ms Excel you can copy the cells with formula and then "copy as value".

If yu need to do this with the new merged value, this cannot be done with OpenTBS because OpenTBS is not able to calculate the result of formulas with the news values. Only Ms Excel can.
By: Mayhaffs
Date: 2014-05-06
Time: 02:23

Re: OpenTBS with xlsx - Deleting formulas in xlsx templates after values have been calculated

Thanks Skrol,

That makes sense, like an order of operations: OpenTBS then => Ms Excel. I found a workaround with a macro, but it's still a little clunky.

Do you know if it's possible to add multiple line breaks(ALT + ENTER) inside a single cell with OpenTBS that is cross-compatible with Windows and OSX Excel?
I didn't have much luck with inserting "\n","\r\n", or char(13)/chr(13).

Any methods for inserting multiple line breaks that you would recommend?