search

doy / spreadsheet-parsexlsx

parse XLSX files
http://metacpan.org/release/Spreadsheet-ParseXLSX
27 stars 34 forks source link

Shared formulas not parsed #28

Open milcs opened 9 years ago

milcs commented 9 years ago

Environment: perl: v5.20.1 built for MSWin32-x86-multi-thread-64int Spreadsheet::ParseXLSX is up to date (0.16). XML::Twig is up to date (3.48). Spreadsheet::ParseExcel is up to date (0.65).

When crating an excel using copy paste formula, excel will create a shared formula. Using Spreadsheet::ParseXLSX to parse such excel, results in:

Inspecting the XLSX source xml files and looking at the ParseXLSX.pm, the Formula is parsed from the text of the <f> tag.

The source data of the <c> tags with share formula example: <c r="H13" s="31" t="str"> <f t="shared" ref="H13:H27" si="0">IF(ISNUMBER(G13/D13),G13/D13,"NaN")</f> <v>NaN</v> </c> In this example, the formula was copied from H13 to H14..H27. The data for H14..H27 is: <c r="H14" s="36" t="str"> <f t="shared" si="0"/> <v>NaN</v> </c> Cell H14 is just referencing the shared formula. The Spreadsheet::ParseXLSX failed to cope with that.

doy commented 9 years ago

Can you provide me with a sample worksheet?

milcs commented 9 years ago

Download a demo perl script with sample excel file from http://stubljar.com/repository/Shared-formulas-not-parsed-example.7z When run, it will use example.xlsx to clone it into result.xlsx.

doy commented 8 years ago

Unfortunately, as far as I can tell, supporting shared formulas will require a full formula parser in order to determine what the actual modified formula is in each cell, which is not something that I have current plans to do. I'd be willing to look over pull requests for this, but it's likely to be a quite large project.

milcs commented 8 years ago

Luckily I found a great approach creating formulas in my templates preventing excel optimisation into shared formulas.