Bug 66365 - [PATCH] getStringCellValue returns an index in shared strings instead of value
Summary: [PATCH] getStringCellValue returns an index in shared strings instead of value
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 5.2.2-FINAL
Hardware: PC All
: P2 normal with 2 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2022-11-28 11:40 UTC by agalatyn
Modified: 2022-11-28 18:15 UTC (History)
1 user (show)



Attachments
Example of array formula where POI returns shared string index instead of value on read (4.64 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2022-11-28 11:40 UTC, agalatyn
Details
PATCH Fix and test for 66365 (10.92 KB, patch)
2022-11-28 12:15 UTC, Espen Amble Kolstad
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description agalatyn 2022-11-28 11:40:21 UTC
Created attachment 38440 [details]
Example of array formula where POI returns shared string index instead of value on read

Check the attached file there are 2 columns.
Column 1 has two strings cells: "Bob" and "Alice".
Column 2 has a formula of array type and refer to column 1.
This means that in column two we should read same values.
But with getStringCellValue fn we read "Bob" and "1" instead of "Bob" and "Alice".
IF we look at shared strings, there are 2 values:
	<si>
		<t>Alice</t>
	</si>
	<si>
		<t>Bob</t>
	</si>

And if we look at row 2:

		<row r="2">
			<c r="A2" s="1" t="s">
				<v>1</v>
			</c>
			<c r="B2" s="2" t="s">
				<v>1</v>
			</c>
		</row>

Both cells here refer to same shared string "Alice", what is correct.
But getStringCellValue reads them as "Alice" and "1".
Seems because the cell is under formula, its shared string index is not resolved to the value.

Row 1 with formula:

		<row r="1">
			<c r="A1" s="1" t="s">
				<v>0</v>
			</c>
			<c r="B1" s="2" t="str">
				<f t="array" ref="B1:B2">A1:A2</f>
				<v>Alice</v>
			</c>
		</row>
Comment 1 agalatyn 2022-11-28 11:56:56 UTC
I posted a mistake in initial comment.
What I wrote  : we read a second row as "Alice" and "1".
What should be: we read a second row as "Bob" and "1".
Comment 2 Espen Amble Kolstad 2022-11-28 12:15:29 UTC
Created attachment 38441 [details]
PATCH Fix and test for 66365

[PATCH]
This fixes this issue without breaking any tests on trunk.
There's a test using the already attached xlsx-file

The patch was created from a git clone - please let me know if I need to change this.
Comment 3 PJ Fanning 2022-11-28 13:06:09 UTC
Thanks - added with r1905571
Comment 4 PJ Fanning 2022-11-28 14:14:48 UTC
also added r1905572 (XSSFExcelExtractor)
Comment 5 PJ Fanning 2022-11-28 14:23:40 UTC
and test (r1905574)
Comment 6 Espen Amble Kolstad 2022-11-28 17:05:21 UTC
Wow that's impressive response-time \o/
Thanks