You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by mouss4rs <ou...@gmail.com> on 2012/03/01 16:57:15 UTC
How to write the Formula of division with 2007 excel files and POI
Hi,
Everything is said in the subject.
I would like to divide the contents of a cell by the other one.
I do this:
row2.setRowNum(20);
cell2 = row2.createCell(8);
*cell2.setCellFormula("I3/B3");* cellStyle = wb2.createCellStyle();
AND I WHEN I OPEN MY FILE
http://apache-poi.1045710.n5.nabble.com/file/n5528227/erreur.jpg
I READ THIS:
This cell(unit) is inconsistent with the formula of column.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-write-the-Formula-of-division-with-2007-excel-files-and-POI-tp5528227p5528227.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: How to write the Formula of division with 2007 excel files and
POI
Posted by mouss4rs <ou...@gmail.com>.
Yes, you are right. I removed the 3 setRowNum()
Now i did this:
if(rowx.getRowNum() == 20){
...
}
Thanks
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-write-the-Formula-of-division-with-2007-excel-files-and-POI-tp5528227p5550477.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: How to write the Formula of division with 2007 excel files and
POI
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Now that I understand what you are doing - getting a row from a sheet,
changing it's index number and then populating cells on that row - I can say
that I would have expected this to work.
So, I went away and did a few tests and it looks as though the setRowNum()
method may not be working correctly. Firstly, I created an Excel file that
had a single row - row 2 - and on that row one cell - cell A2 - was
populated with data. Next, I write a simple program that opened this file
using POI, created row 0 and populated cells A1 and B1 with data. Next, the
program recovered row 2 from the sheet, created cell B2 and populated that
with a formula. After wrting the file away to disk again, I tried to open it
- using OpenOffice - and say that row 2 had not in fact been moved at all,
the contents of cell B2 where still there. My next step was to unzip the
xlsx file and take a look at the xml, the key part of which is below;
<row r="11" spans="1:1" x14ac:dyDescent="0.25">
<c r="A2" t="s"><v>0</v></c>
<c r="B11"><f>A1/B1</f></c>
</row>
which, as you can see shows row 11 and it contains the markup for two cells,
cell A2 and B11. Seemingly, the call to setRowNum() did not actually change
the indices for any cells that already existed on the row that was read from
the file. I wonder if this is the problem you are seeing? The easy way to
confirm this would be to unzip the xlsx file (this is much harder to do with
an xls file) you are creating with poi and to look into the contents of the
sheetn.xml files to see if the markup is similarly malformed.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-write-the-Formula-of-division-with-2007-excel-files-and-POI-tp5528227p5547212.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: How to write the Formula of division with 2007 excel files and
POI
Posted by mouss4rs <ou...@gmail.com>.
Because i did this before:
for (Iterator it = sheetx.rowIterator(); it.hasNext();){
rowx = (XSSFRow) it.next();
So i don't do this in bold:
*row = sheet.createRow(10); *cell = row.createCell(0);
cell.setCellValue("Test");
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-write-the-Formula-of-division-with-2007-excel-files-and-POI-tp5528227p5546592.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: How to write the Formula of division with 2007 excel files and
POI
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
The reason I asked that question is quite simple. Say you do this;
row = sheet.createRow(10);
cell = row.createCell(0);
cell.setCellValue("Test");
and then do this;
row.setRowNumber(20);
when POI comes to write the file out, it will not be able to create
correctly formed xml; it will try, not complain nor throw and exception.
I have not had the time to look through your code and so do not know if you
are doing this but it could - and that is *could* - be the source of the
error. I tried something similar myself as a test and Excel did issue a
warning when it tried to open the file. If you wanted to put the totals on
row 20 - for example - and row 20 did not yet exist on the sheet you should
create it first. If row 20 does already exist on the sheet then you have to
get the row from the sheet object.
As I said, I do not know if this is the source of your error but it just
struck me as strange to see the call to the setRowNumber() method without
any other context and that was the reason I asked.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-write-the-Formula-of-division-with-2007-excel-files-and-POI-tp5528227p5544754.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: How to write the Formula of division with 2007 excel files and
POI
Posted by mouss4rs <ou...@gmail.com>.
Mark Beardsley wrote
>
> Can I ask, why do you call the setRowNumber() method in the first line of
> the chunk of code you are posting?
-> Because, i would like to set row at ligne 20.
The total is on this line.
In fact, I do this:
int nbfeuille=0;
int ligne_trouvee;
//version .xlsx
InputStream ist = new
FileInputStream("C:/Donnees/Aide_Manager/Projection_2012_Eq2.xlsx");
OPCPackage opc=OPCPackage.open(ist);//version .xlsx
XSSFWorkbook wb2= new XSSFWorkbook(opc);
XSSFSheet sheet2 = wb2.getSheetAt(0);//onglet 0
nbfeuille= wb2.getNumberOfSheets();//retourne le nombre de feuille
renseigné
XSSFRow row2 = null;
XSSFCell cell2 = null;
XSSFCellStyle cellStyle = null;
XSSFDataFormat fmt = wb2.createDataFormat(); //version .xlsx
//pour ouvrir un .xlsx
FormulaEvaluator evaluator =
wb2.getCreationHelper().createFormulaEvaluator();
evaluator.evaluate(cell2);
String prenom_proj;
Collator usCollator;
System.out.println("wb2.getNumberOfNames(): "+nbfeuille);
// il faut définir la feuille qui sera alimenté:
//Parcourir les feuilles du document
//si la semaine correspond au libellé d'une feuille
for (int g=0; g<nbfeuille; g++){//boucle de parcours des onglets
sheet2 = wb2.getSheetAt(g);
onglet=sheet2.getSheetName();// on récupère le nom de la feuille excel
en String
if(onglet.startsWith("S")){//si ca commence par
"S"
//on enleve le "S"
semaine_en_string=onglet.substring(1);
// on converti le nom de l'onglet en Int
onglet_conv = Integer.parseInt(semaine_en_string);
System.out.println("onglet: "+onglet_conv);
if(onglet_conv == semaine){//si la semaine de l'onglet du doc
"extract.xls"
//correspond à la semaine de l'année trouver dans le fichier
"Projection_2012_Eq2.xls"
System.out.println("onglet de la semaine
"+onglet_conv+" TROUVEE !");
for (Iterator rowIt = sheet2.rowIterator();
rowIt.hasNext();){//boucle de parcours du fichier de ligne en ligne
row2 = (XSSFRow) rowIt.next();
if(row2.getCell(0)!= null && row2.getRowNum() <= 19){//
si la ligne est différent de null et inférieur ou égale à la 20ème ligne
if(!row2.getCell(0).getStringCellValue().equals("Noms")
&& !row2.getCell(0).getStringCellValue().equals("EQUIPE")
&& !row2.getCell(0).getStringCellValue().equals("")){
//on récupère le 1er prénom du fichier Projection
prenom_proj=row2.getCell(0).getStringCellValue().toUpperCase();
for (int d=0; d<tab.length;d++){
if(tab[0][d] != null || tab[1][d]!=null){//si l'une des cellules
n'est pas null
//System.out.println("Tab"+tab[0][d]);
//System.out.println("prénom: "+prenom_proj);
//Get the Collator for US English and set its strength to
PRIMARY
usCollator = Collator.getInstance(Locale.FRANCE);
usCollator.setStrength(Collator.PRIMARY);
if( usCollator.compare(prenom_proj, tab[0][d]) == 0 ){//si le
prénom trouvé dans le fichier 'Projection_2012_Eq2.xlsx' correspond au
prenom de l'extract Hermes
//System.out.println("Strings are equivalent");
System.out.println("Agent: "+tab[0][d]+"
Validation: "+tabvalidation[1][d]);
//on récupère la ligne du prénom
ligne_trouvee= row2.getCell(0).getRowIndex();
ligne_trouvee++;//pour écrire dans la bonne ligne
//System.out.println("ligne TROUVEE:
"+ligne_trouvee);
//on écrit dans la colonne I la validation du prénom
trouvé
cell2 = row2.getCell(8);// colonne(I)
cell2 = row2.createCell(8);
cell2.setCellType(Cell.CELL_TYPE_NUMERIC);
cell2.setCellValue(tabvalidation[1][d]);
//on ré-écrit la formule pour la colonne J
cell2 = row2.getCell(9);// colonne(J)
cell2 = row2.createCell(9);
cell2.setCellFormula("I"+ligne_trouvee+"/B"+ligne_trouvee);
//System.out.println("Formule:
I"+ligne_trouvee+"/B"+ligne_trouvee);
//arrondir à 2 chiffre après la virgule
cellStyle = wb2.createCellStyle();
cellStyle.setDataFormat(fmt.getFormat("#,##0.00"));
//cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));//version
.xls
cell2.setCellStyle(cellStyle);
//on ré-écrit la formule pour la colonne K
cell2 = row2.getCell(10);// colonne(K)
cell2 = row2.createCell(10);
cell2.setCellFormula("I"+ligne_trouvee+"/C"+ligne_trouvee);
//arrondir à 2 chiffre après la virgule
cellStyle = wb2.createCellStyle();
cellStyle.setDataFormat(fmt.getFormat("0.0%"));
cell2.setCellStyle(cellStyle);
//on ré-écrit la formule pour la colonne L
cell2 = row2.getCell(11);// colonne(L)
cell2 = row2.createCell(11);
cell2.setCellFormula("J"+ligne_trouvee+"*7");
//System.out.println("Formule:
J"+ligne_trouvee+"*7");
//arrondir à 2 chiffre après la virgule
cellStyle = wb2.createCellStyle();
cellStyle.setDataFormat(fmt.getFormat("#,##0.00"));
cell2.setCellStyle(cellStyle);
ligne_trouvee--;//pour retrouver ma ligne
}
}
}
}
}
}
//on réécrit la formule du total colonne I
<b>row2.setRowNum(20);* //System.out.println("on doit se
fixer à la ligne 20: "+row2.getRowNum());
cell2 = row2.createCell(8);
String colonneI = "I3:I20";
cell2.setCellFormula("SUM("+colonneI+")");
//format EQUIPE de la colonne I
cellStyle = wb2.createCellStyle();
cellStyle.setDataFormat(fmt.getFormat("#,##0"));//format 1 000
par ex.
cell2.setCellStyle(cellStyle);
//on réécrit la formule du total colonne J
//row2.setRowNum(20);
//System.out.println("on doit se fixer à la ligne 20:
"+row2.getRowNum());
cell2 = row2.createCell(9);//colonne J
String resultatParH = "SUM(I3:I20)/SUM(B3:B20)";
cell2.setCellFormula(resultatParH);
//arrondir à 2 chiffre après la virgule
cellStyle = wb2.createCellStyle();
cellStyle.setDataFormat(fmt.getFormat("#,##0.00"));
cell2.setCellStyle(cellStyle);
//on réécrit la formule du total colonne K
//row2.setRowNum(20);
//System.out.println("on doit se fixer à la ligne 20:
"+row2.getRowNum());
cell2 = row2.createCell(10);//colonne K
String txtransfo = "(SUM(I3:I20)/SUM(C3:C20))/100";//j'ai rajouté
/100 pour prendre en compte le % dans l'ecriture du format
cell2.setCellFormula(txtransfo);
//arrondir à 2 chiffre après la virgule
cellStyle = wb2.createCellStyle();
cellStyle.setDataFormat(fmt.getFormat("0.00%"));
cell2.setCellStyle(cellStyle);
//on réécrit la formule du total colonne L
//row2.setRowNum(20);
//System.out.println("on doit se fixer à la ligne 20:
"+row2.getRowNum());
cell2 = row2.createCell(11);//colonne L
String resultatetp = "J21*7";
cell2.setCellFormula(resultatetp);
//arrondir à 2 chiffre après la virgule
cellStyle = wb2.createCellStyle();
cellStyle.setDataFormat(fmt.getFormat("0.00"));
cell2.setCellStyle(cellStyle);
}
}
}//for
//pour ecrire dans le même fichier
FileOutputStream fileOut = new
FileOutputStream("C:/Donnees/Aide_Manager/Projection_2012_Eq2.xlsx");
wb2.write(fileOut);
fileOut.close();
} catch (FileNotFoundException ex){
ex.printStackTrace();
} catch (IOException ex) {
ex.printStackTrace();
} catch (InvalidFormatException ex) {
// TODO Auto-generated catch block
ex.printStackTrace();
} catch (ParseException ex) {
// TODO Auto-generated catch block
ex.printStackTrace();
}
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-write-the-Formula-of-division-with-2007-excel-files-and-POI-tp5528227p5543437.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: How to write the Formula of division with 2007 excel files and
POI
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
I do not think that it has anything at all to do with the formula you are
using but suspect something else is going on with your code. Can I ask, why
do you call the setRowNumber() method in the first line of the chunk of code
you are posting? It may be that the code you have written partially creates
some of the information for one row and then corrupts that in some way so
that Excel is unable to interpret your intentions. This is a guess but I
would suggest you read through your code carefully and see if it is doing
what you think it is doing. Think of Excels' error just as you would a
compiler error; the actual cause may well not be found on the line the
compiler is indicating.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-write-the-Formula-of-division-with-2007-excel-files-and-POI-tp5528227p5528702.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: How to write the Formula of division with 2007 excel files and
POI
Posted by mouss4rs <ou...@gmail.com>.
I notice that when i do division of 2 cells content, there are this:
=Table13451522[[#This line];[Retours S04]]/Table13451522[[#This
line];[Heures S02]]
What is this ???
it's not write : =I4/B4
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-write-the-Formula-of-division-with-2007-excel-files-and-POI-tp5528227p5528345.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: How to write the Formula of division with 2007 excel files and POI
Posted by Ashley Westwell <as...@logicbox.ca>.
Try wrapping it in SUM()
-----Original Message-----
From: mouss4rs [mailto:oukna.mustapha@gmail.com]
Sent: Thursday, March 01, 2012 10:57 AM
To: user@poi.apache.org
Subject: How to write the Formula of division with 2007 excel files and
POI
Hi,
Everything is said in the subject.
I would like to divide the contents of a cell by the other one.
I do this:
row2.setRowNum(20);
cell2 = row2.createCell(8);
*cell2.setCellFormula("I3/B3");* cellStyle = wb2.createCellStyle();
AND I WHEN I OPEN MY FILE
http://apache-poi.1045710.n5.nabble.com/file/n5528227/erreur.jpg
I READ THIS:
This cell(unit) is inconsistent with the formula of column.
--
View this message in context:
http://apache-poi.1045710.n5.nabble.com/How-to-write-the-Formula-of-divisi
on-with-2007-excel-files-and-POI-tp5528227p5528227.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org For additional
commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org