Same named cells in several sheets

[adile]

First of all i'm sorry for my bad english :)

I have apache POI API in my struts2 webb app and it's works !

However i face problems.

I have to read (and | or) generate excel (xls) files.

My files have several identical sheets with the same named cell.

for example :
-Sheet1 have a named cell "MyCell" at $A$12
-Sheet2 have a named cell "MyCell" at $A$40
-Sheet2 have a named cell "MyCell" at $A$14

how i can select my cell with this name ? (like : i want "MyCell" on sheet1
OR sheet2)
i try the example given in the apache guide but it return a wrong position
(it's return MyCell position of one sheet but not the one i would like).


Secondly, when i try to clone a sheet, it works (content and style) but not
named cell.

I try to write it myself but apache POI doesn't accept to create named cell
already existing in the workbook...

So how can i create cell name in a specific sheet and not workbook, in
excel i just have to write Sheet!MyCellName

Thank you for helping me

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Same-named-cells-in-several-sheets-tp5683541.html
Sent from the POI - User mailing list archive at Nabble.com.

[Mark Beardsley]

This is off of the top of my head so forgive me if there are a couple of
areas that I am unclear on.

1. You will have to work from the HSSFWorkbook object and call the
getName(String) method defined on it. The String you pass to that method
will be the name of the cell - or named region - preceded by the name of the
sheet; something like Sheet1!MyCell
2. This method call should return to you an instance of the HSSFName class.
It has methods defined on it that allow you to determine the index or name
of the sheet the named region is on. To get at the cells address, it is
likely that you will have to call the getRefersToFormula(0 of the HSSFname
instance and then parse the String that will return to pull out the address
of the cell.

Hope that helps, if not, let us know and I will try to put a simple example
together for you.

Yours

Mark B

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Same-named-cells-in-several-sheets-tp5683541p5683580.html
Sent from the POI - User mailing list archive at Nabble.com.

[adile]

hi, thank you a lot for your reply.

I try your suggestion but that doesn't work...

my example for reading :

InputStream inp = new FileInputStream(file);
HSSFWorkbook wb = new HSSFWorkbook(inp);

HSSFName name = wb.getName("MySheet!aLibellé");

if(name != null)
System.out.println("RefersFormula "+name.getRefersToFormula());
else
System.out.println("name is null");

when i try with MySheet!aLibellé i have name null and when i try without
MySheet! it's return me the aLibellé adress but in wrong sheet


my example for writting :


HSSFName namedCell = wb.createName();
namedCell.setNameName("id_product");
String reference = "sheet1!$A$1"; // area reference
namedCell.setRefersToFormula(reference);

Name namedCell2 = wb.createName();
namedCell2.setNameName("id_product");
String reference2 = "sheet2!$A$1"; // area reference
namedCell2.setRefersToFormula(reference2);

this code throw an exception :
java.lang.IllegalArgumentException: The workbook already contains this name:
id_product


thank youu


--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Same-named-cells-in-several-sheets-tp5683541p5685173.html
Sent from the POI - User mailing list archive at Nabble.com.

[Mark Beardsley]

Ah, OK. Let me have a play with some code and get back to you. It should be
possible to have areas/cells with the same name on different sheets as names
can heave either sheet or book scope in Excel so I am a little confused by
what is going on. Will try to see what I can find and post if I have any
further information.

Yours

Mark B

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Same-named-cells-in-several-sheets-tp5683541p5686163.html
Sent from the POI - User mailing list archive at Nabble.com.

[adile]

Ok, thank you a lot


--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Same-named-cells-in-several-sheets-tp5683541p5686195.html
Sent from the POI - User mailing list archive at Nabble.com.

[Mark Beardsley]

Have been playing a little this morning and can confirm that it is not
possible to create regions/cells with the same name but on different
worksheets using POI. Can I please ask you to confirm that it is possible to
do this using Excel itself? By this, I mean would you use Excel and try to
create a cell with the name 'MyCell' on two or more sheets? I cannot test
this myself as we use Libre/OpenOffice and it does not support this feature;
names have to be unique within the workbook.

If you do manage to create such a workbook, could I ask you to email a copy
to me at ***@apache.org please so that I have something to work with. It is
not possible, as you found, to get at the named cell directly but I have put
together a small utility that should (might, it's still waiting a test,
hence that Excel workbook) allow you to do something similar. Put simply, it
iterates through all of the named cells/regions in the workbook until it
finds the one you are after at which point it will return this cell.

If you do find, that you cannot use Excel to create cells with the same name
but on different sheets, can I ask you to let us know please. As far as I
have been able to discover, this ought to be possible with Excel but i could
well be wrong.

Yours

Mark B

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Same-named-cells-in-several-sheets-tp5683541p5687768.html
Sent from the POI - User mailing list archive at Nabble.com.

[Mark Beardsley]

Reading the javadoc again for the HSSFName class (always a good idea) and I
have found mention of the setSheetIndex(0 method. Calling this method, and
passing the index of the sheet to it, ensures that the name has sheet rather
than workbook scope. Will play with the method tonight if I get the chnace
nut it would appear that this will allow us to create cells with the same
name on different sheets.

Yours

Mark B

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Same-named-cells-in-several-sheets-tp5683541p5688205.html
Sent from the POI - User mailing list archive at Nabble.com.

[adile]

Hi, thank you again

I can try right know but i don't think that your previous code will work
with my example (for the reading).

For writting, an exception was catch with something like "cannot create an
existing named cell"



The HSSFName setSheetIndex is maybe the solution, i'll try wednesday :)


--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Same-named-cells-in-several-sheets-tp5683541p5688379.html
Sent from the POI - User mailing list archive at Nabble.com.

[Mark Beardsley]

Just experimented with your test file (thanks for that by the way) and
understand where your concern was coming from. In that workbook, even though
the names existed the cells did not. The previous iteration of the code did
not handle this problem but the newest version (the one above) does do so.
Now, if the name exists within the workbook but the corresponding cell is
null, then the code will create and return a blank cell that you can
initialise with a value. Try adding this method into the class above (you
will also need to modify the constructor so that this method is called
instead of the other two of course);

private void modifyExisting(String filename) throws IOException {
File file = null;
FileInputStream fis = null;
FileOutputStream fos = null;
HSSFWorkbook workbook = null;
HSSFCell cell = null;
try {
// Open the workbook.
file = new File(filename);
fis = new FileInputStream(file);
workbook = new HSSFWorkbook(fis);
fis.close();
fis = null;

// Get the named cell. This code simply writes the name of the
// sheet/cell into the cell to demonstrate that it has been
changed.
cell = this.getNamedCell("Feuil1!MyRange", workbook);
if(cell != null) {
cell.setCellValue("Feuil1!MyRange");
}
cell = this.getNamedCell("Feuil2!MyRange", workbook);
if(cell != null) {
cell.setCellValue("Feuil2!MyRange");
}
cell = this.getNamedCell("Feuil3!MyRange", workbook);
if(cell != null) {
// Just to show you are not limited to writing Strings into
the
// cell, set this one to a numeric value.
cell.setCellValue(1234.56);
}

// Write the workbook out again. Here, the file is being
overwritten
// but there is nothing preventing you from supplying a
different
// name/location for the file and writing it there.
fos = new FileOutputStream(file);
workbook.write(fos);
}
finally {
if(fos != null) {
fos.close();
fos = null;
}
}
}

If you run this against the test file that you forwarded to me, you should
see that it sets the values of each of the named cells before re-saving the
file away. You can write any sort of value you want into the cell that the
getNamedCell() method returns and can set the style/format as you wish; just
treat it as you would a cell created by a call to the row.createCell(int)
method.

Hope this helps.

Yours

Mark B

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Same-named-cells-in-several-sheets-tp5683541p5689322.html
Sent from the POI - User mailing list archive at Nabble.com.

[adile]

Hi Mark

I just try your example and that works !!!!!!!!!!

Thank you a lot for your help ! I just discover Apache POI and you help me a
lot :)



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Same-named-cells-in-several-sheets-tp5683541p5696778.html
Sent from the POI - User mailing list archive at Nabble.com.

[adile]

I have some questions,

When i have my cellRef, can i just change the position to read the cell at
my left for example ? Or i have to get my sheet then get my row and finaly
get my column+1 ?

Regarding to the cloneSheet method, it's just cloning data and not named
cell... There is a specific method to do it or i have to implement it with
your example ?

thank you

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Same-named-cells-in-several-sheets-tp5683541p5696882.html
Sent from the POI - User mailing list archive at Nabble.com.

[adile]

to answer at my question, i do like this :

HSSFRow row = sheet.getRow(cellRef.getRowIndex()+1);
HSSFCell cell = row.getCell(cellRef.getColumnIndex()+6);

so i can read others cells

regarding to the cloneSheet question, i'll add named cell one by one.

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Same-named-cells-in-several-sheets-tp5683541p5697613.html
Sent from the POI - User mailing list archive at Nabble.com.