search

jmcnamara / excel-writer-xlsx

Perl module to create Excel XLSX files.
https://metacpan.org/pod/distribution/Excel-Writer-XLSX/lib/Excel/Writer/XLSX.pm
Other
102 stars 51 forks source link

R1C1 formulas needed #298

Closed pdean closed 5 months ago

pdean commented 5 months ago

I'm moving some scripts from tcl to perl. The CAWT library for tcl allows me to use R1C1 relative addressing. This make it so easy to generate spreadsheets with huge numbers of rows where a cell has the same formula in every row. Painful to achieve with absolute addressing.

jmcnamara commented 5 months ago

This is not a very frequently requested feature but I looked at this in the past and there was some technical difficult that made it tricky to implement or use. I don't remember which but I'll look again.

In the meantime have a look at the Excel::Writer::XLSX utility functions for creating A1 ranges: https://metacpan.org/pod/Excel::Writer::XLSX::Utility

jmcnamara commented 5 months ago

Here was the previous analysis that I did for a similar request:

https://github.com/jmcnamara/libxlsxwriter/issues/185#issuecomment-395403931

Basically, R1C1 isn't supported and it is unlikely that it will be. The main issue is that Excel doesn't store the formulas in R1C1 format. If a user writes a formula in cell B5 like=R[-3]C+R[-2]C[4] it is actually stored in the file as =B2+F3.

This means that an xlsx file writer, like libxlsxwriter, would have to convert all formulas from R1C1 to A1, which would mean parsing and converting all formulas. That isn't practical to implement.

I'll double check this is still true but if it is then this is a can't/won't fix issue.

pdean commented 5 months ago

O.k, thanks for the explanation. The tcl library only works on windows and does everything via twapi. That must be why it can do it.

I'll try using the utilities. A subroutine for each formula may even be clearer and more maintainable in the long term.