Bug 55269 - Create cell name formula text from CellReference
Summary: Create cell name formula text from CellReference
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.9-FINAL
Hardware: PC Windows NT
: P2 enhancement (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2013-07-15 17:53 UTC by sheene
Modified: 2015-09-11 19:26 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description sheene 2013-07-15 17:53:41 UTC
Hi

I struggle with creating formula text for named cells in Excel 2013.

The problem: surprisingly, the definition of the named cell changes depending on the selected cell in excel. As if it was defined relative to the selected cell.

Using the recipe  in the quick guide
http://poi.apache.org/spreadsheet/quick-guide.html#NamedRanges
doesn't work. It creates a reference as "sheetname!A1:A1".

When creating a named cell in Excel, the reference is absolute, i.e. sheet!$A$0

Ideally, I'd wish an idiom such as:
cell.setFormulaText(CellReference(name));
would work.

I created two Gists that demostrate the problem:
This doesn't work with the desired idiom and the cookbook solution:
https://gist.github.com/dschien/6001876

This works (explicitly makes the ref absolute by inserting the '$' signs):
https://gist.github.com/dschien/6001918

Thanks,
Dan