LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: gauravdb

Hi Guys,

I am having some issues with the Output Excel node. Basically, I have a templateInputFile which I am using to output some data.

Here are some issues I have:

1. The Output file is unable to retain its formatting, for example I have borders in one of the sheets in Template File. But when the data is written onto it, all the borders are getting deleted.

2. I have some formulas in my template file but when the data is written onto it, the formula does not produce any results but just shows zero instead. The formula itself is not getting deleted, its just that I have to manually click on every single column and press F2+Enter for the formula to fetch values.

Anyone have any idea on this please

N.B: I have tried to set FormatOutput to True as well as False, but it did not help much.

Thanks
GK

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Tim Meagher

Hey,

What version of the software are you using and what type of Excel template file (XLS, XLSX) are you using?
There have been some issues raised about this in the past, however I believe a fix to at least most of these formatting problems should be available in LAE 4.6.2.
The fix will also be available in LAE 5.0.1 and LAE 5.1.0 when they are released (issue # 4722).

Part of the fix involves the introduction of the PreserveCellStyles parameter which allows you to preserve the styling information in the spreadsheet while still formatting the output data (i.e. date output with date formatting, integer with integer formatting etc).
If you are using LAE 4.6.2, then try setting the PreserveCellStyles parameter to true.

I am not sure about why the formulas wouldn't be getting recalculated...whenever the Excel file is written, and there is a template in use, or another file being appended to, a flag is set to force the recalculation of all formulas on loading the workbook.
However, it does appear that this is a sheet-based setting only.
When using an XLSX file it looks like for me at least this always works.
However, when using an XLS file rather than XLSX, it looks like if the formulas are on a sheet which was not modified by the node, the flag is not set on that sheet, and the formulas will need to be manually recalculated.

Can you confirm whether or not this is the problem you are seeing?

Regards,
Tim.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: gauravdb

Thanks for the prompt reply Tim.

I am using 4.6.1.2 at the moment and template is in XLSX version.

I am using the same template for few months and there was no problem at all but now for few days I am getting the problem where the formulas are not getting calculated. Although, I did make some changes in the template few days ago but those changes were irrelevant to the problem we are discussing.

I guess I will manually have to refresh the column to fetch the value for time being and then just wait for the updated LAE to be installed here.

Thanks
GK

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rhallmark

I've had the same issue as the first question you raised and I was able to get around it by setting up some conditional formatting so that when cell(s) are not empty then the border is applied.