Wbexport to Excel and formulas

[Maurizio Scarpis]

Hi Thomas.

I tried to use wbexport to Excel using a SELECT that generates a column like an Excel formula. This is a sample:

wbexport -type=xlsx -file=C:\TEMP\x.xlsx -header=true

;

select tabid, '=1*2'

from systables where tabid < 10

;

Excel does not calculate the formula because the cell is not recognized as a formula (I read it from POI documentation; I've read https://stackoverflow.com/questions/2339238/how-to-set-formulas-in-cells-using-apache-poi).

Is there any trick to bypass the problem ?

Is it possible to implement the feature, in a way of your preference ?

Thanks in advance

Maurizio 

  

[Thomas Kellerer]

Hi Maurizio,

I don't think there is a workaround for this. When I encounter a String value during export, I explicitely define the cell/column to have character content.

But it can can certainly be done.

I think adding a parameter to WbExport that specifies which columns should be treated as a formula makes the most sense.

Something like this:

WbExport -formulaColumns=some_col,other_column -type=xlsx;
select tabaid, '=1*2' as some_col, '=$A1 * 10' as other_column
from ...

Regards
Thomas

Maurizio Scarpis schrieb am 27.05.2021 um 13:49:
> Hi Thomas.
> I tried to use wbexport to Excel using a SELECT that generates a column like an Excel formula. This is a sample:

> /wbexport -type=xlsx -file=C:\TEMP\x.xlsx -header=true/
> /;/
> /select tabid, '=1*2'/
> /from systables where tabid < 10/
> /;/

[Maurizio Scarpis]

Hi Thomas.

Your proposal is very good for me.

Thanks.

Maurizio

[Maurizio Scarpis]

Hi Thomas.

I just tried new version 127.6 for formula columns.

I have some formulas that cause error "Cannot get a STRING value from a FORMULA cell", 

Query1 (OK)

wbexport -type=xlsx -file=C:\tmp\exp.xlsx -formulaColumns=col3

;

select tabid, tabname,

"ROUND(ROW(A2),2)" col3

from systables 

where tabid < 3

;

Query 2 (my goal, KO)

wbexport -type=xlsx -file=C:\tmp\exp.xlsx -formulaColumns=col3

;

select tabid, tabname,

"INDIRECT(ADDRESS(ROW()-1,COLUMN()))" col3

from systables 

where tabid < 3

Query 3 (a "very" simple version with the same error, KO)

wbexport -type=xlsx -file=C:\tmp\exp.xlsx -formulaColumns=col3

;

select tabid, tabname,

"CONCATENATE(B2)" col3

from systables 

where tabid < 3

Any ideas ?

Regards

Maurizio

[Thomas Kellerer]

The error occurs when the column width is calculated to optimize the width for each column.

I am not sure if this is a bug in the POI library or in the way I generate the formula column.

For the time being you can workaround this by disabling that automatic column width using -autoColWidth=false

Thomas

Maurizio Scarpis schrieb am 24.08.2021 um 14:56:
> Hi Thomas.
> I just tried new version 127.6 for formula columns.
> I have some formulas that cause error "Cannot get a STRING value from a FORMULA cell", 
>

> *Query1 (OK)*

> wbexport -type=xlsx -file=C:\tmp\exp.xlsx -formulaColumns=col3
> ;
> select tabid, tabname,
> "ROUND(ROW(A2),2)" col3
> from systables 
> where tabid < 3
> ;
>

> *Query 2 (my goal, KO)*

> wbexport -type=xlsx -file=C:\tmp\exp.xlsx -formulaColumns=col3
> ;
> select tabid, tabname,
> "INDIRECT(ADDRESS(ROW()-1,COLUMN()))" col3
> from systables 
> where tabid < 3
>
>

> *Query 3 (a "very" simple version with the same error, KO)*

> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbenc...@googlegroups.com>.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sql-workbench/2bb92112-0a64-4dd2-be81-54ef12699b6en%40googlegroups.com <https://groups.google.com/d/msgid/sql-workbench/2bb92112-0a64-4dd2-be81-54ef12699b6en%40googlegroups.com?utm_medium=email&utm_source=footer>.

[Maurizio Scarpis]

Yes, the workaround runs good.

Obviuosly it would be better to use the useful option "autocolwidth=true", if possible.

Regards

Maurizio 

[Thomas Kellerer]

So this seems to be a limitatation of the "streaming" interface to write XLSX files through POI.

The advantage of the streaming API is, that is uses less memory (it doesn't keep the whole file in memory)
But apparently there is some problem with aut-sizing the columns (even if I explictely not enable it for the formula columns).
Not sure if I am doing something wrong or if it is a problem with the streaming API.

You can disable the use of the streaming API through

wbsetconfig workbench.export.xslx.use.sxssf=false;

then you can use -autoColWidth=true again.

Regards
Thomas

> To view this discussion on the web visit https://groups.google.com/d/msgid/sql-workbench/8f3aa846-b628-41a1-a783-30616395da4bn%40googlegroups.com <https://groups.google.com/d/msgid/sql-workbench/8f3aa846-b628-41a1-a783-30616395da4bn%40googlegroups.com?utm_medium=email&utm_source=footer>.