[ Poi Apache] case of cells with carriage return

[erwan]

Hello to all,

I use poi to be able to read an Excel file and generate files csv from
sheets. So it's cool the program worksbut since yesterday I am confronted
with a case that I had not thought of that of the cells(units) which contain
returns in the line (alt+enter under Excel).

Once the generated csv I perceive that it provokes a return in the line in
my file.
I said myself that's normal I did not manage the carriage return, but when
I get back the contents of the cell in my string since I manage not to
remove the carriage return.

Here is the part of the code (I bounded with comments the precise place) :

public void genCSV(String cheminSortie, String SheetName, Vector Col, int
FirstLine, int EndLine) {
CSVWriter writer = null;

//recupere l'onglet
Sheet sheet = wb.getSheet(SheetName);

try{
writer = new CSVWriter(new FileWriter(cheminSortie), ';',
NO_QUOTE_CHARACTER);

//pour chaque ligne
for(int j=FirstLine;j<EndLine;j++)
{
//System.out.println(&quot;ligne : &quot;+(j+1));

//vecteur temporaire qui récupère les valeurs des cellules de la ligne j
Vector temp = new Vector();

//recupère la ligne
Row row = sheet.getRow(j);
if (row!=null) {
//pour chaque colonnes selectionnées
for(short k=0;k&lt;Col.size();k++) {

if(row.getCell((Integer) Col.get(k)) != null) {
System.out.println(&quot;la cellule n'est pas null&quot;);
//recupere la cellule
Cell cell = row.getCell((Integer) Col.get(k));
String value = null;
//traitement selon le type de la cellule
switch (cell.getCellType())
{
case Cell.CELL_TYPE_FORMULA :
double p = Math.pow(10.0, 2);
double val=0;
val = Math.floor(cell.getNumericCellValue()*p)/p;
value = &quot;&quot;+val;
value.replaceAll(&quot; &quot;, &quot;&quot;);
if (value.substring(value.length()-2,
value.length()).equals(&quot;.0&quot;)) {
value=value.substring(0, value.length()-2);
}
System.out.println(value);
temp.add(value);
break;
case Cell.CELL_TYPE_NUMERIC :
if(DateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = new SimpleDateFormat(&quot;dd/MM/yy&quot;);
Date date = new
Date(cell.getDateCellValue().getTime());
value = &quot;&quot;+sdf.format(date);
}
else if (&quot;0%&quot;.equals(
cell.getCellStyle().getDataFormatString() )) {
double round2 = Math.pow(10.0, 2);
double val2=0;
val2 = (Math.floor(cell.getNumericCellValue()*round2)/round2)*100;
value = &quot;&quot;+val2;
value.replaceAll(&quot; &quot;, &quot;&quot;);
if (value.substring(value.length()-2,
value.length()).equals(&quot;.0&quot;)) {
value=value.substring(0, value.length()-2);
}
System.out.println(value);
}
else {
double p2 = Math.pow(10.0, 2);
double val2=0;
val2 = Math.floor(cell.getNumericCellValue()*p2)/p2;
value = &quot;&quot;+val2;
value.replaceAll(&quot; &quot;, &quot;&quot;);
if (value.substring(value.length()-2,
value.length()).equals(&quot;.0&quot;)) {
value=value.substring(0, value.length()-2);
}
System.out.println(value);
}
temp.add(value);
break;
/////////////////////////////////////////////////////////ICI traitement pour
les chaines/////////////////////////////////////////////////////////////////
&lt;b>case Cell.CELL_TYPE_STRING :

value = cell.getRichStringCellValue().getString();
value.replaceAll("chr(10)", " ");
value.replaceAll("
", " ");
value.replaceAll("car(10)", " ");
value.replaceAll("char(10)", " ");
value.replaceAll("chr(13)", " ");
value.replaceAll("
", "");
value.replaceAll("car(13)", " ");
value.replaceAll("char(13)", " ");
String RC = System.getProperty("line.separator" );
value.replaceAll(RC," " );


System.out.println(value);
temp.add(value);
break; *
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
case Cell.CELL_TYPE_BLANK :
value ="";
temp.add(value);
break;
default : value =""; temp.add(value);break;
}
}
else if(row.getCell((Integer) Col.get(k)) == null) {
System.out.println("la cellule est null");
String value = null;
value ="";
System.out.println(value);
temp.add(value);
}
}
//on ajoute la ligne au vecteur qui contient toutes les lignes
this.lignes.add(temp);
}

}

for(int i=0;i<this.lignes.size();i++)
{
System.out.println(this.lignes.size());
Vector temp2 = new Vector((Vector)lignes.get(i));
String[] tab = new String[temp2.size()];
for(int l=0;l<temp2.size();l++) {
tab[l] = (String)temp2.get(l);
System.out.println("insertion "+l+" : "+tab[l]);
}
writer.writeNext(tab);
}

writer.close();
}
catch(IOException ex)
{
System.err.println("Erreur lors de l'écriture : "+ex.getMessage());
}
lignes.removeAllElements();
}

here the part of the treatment of string format:

case Cell.CELL_TYPE_STRING :

value = cell.getRichStringCellValue().getString();
value.replaceAll("\\chr(10)", " ");
value.replaceAll("\\car(10)", " ");
value.replaceAll("\\char(10)", " ");
value.replaceAll("\\chr(13)", " ");
value.replaceAll("\\car(13)", " ");
value.replaceAll("\\char(13)", " ");
String RC = System.getProperty("line.separator" );
value.replaceAll(RC," " );


System.out.println(value);
temp.add(value);
break;

I looked on the Internet and on javadocs but I found nothing which helps me.
Then if you have a solution I am accepting !

Thank you in advance for your help !

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Poi-Apache-case-of-cells-with-carriage-return-tp5709920.html
Sent from the POI - User mailing list archive at Nabble.com.

[Nick Burch]

Post by erwan
I use poi to be able to read an Excel file and generate files csv from
sheets. So it's cool the program worksbut since yesterday I am
confronted with a case that I had not thought of that of the
cells(units) which contain returns in the line (alt+enter under Excel).
Once the generated csv I perceive that it provokes a return in the line in
my file.

What do you want to do for cells with a line break in them? Remove it?
Output it? Change it?

Nick

[erwan]

thank you for your answer
I want to replace the carriage return into a space (something like string.replaceAll(
carriage return , " ") ).
The fact is that I don't know the value of this
carriage return .
Erwan

Date: Thu, 24 May 2012 07:40:22 -0700
From: ml-node+***@n5.nabble.com
To: erwan-***@live.fr
Subject: Re: [ Poi Apache] case of cells with carriage return

Post by erwan
I use poi to be able to read an Excel file and generate files csv from
sheets. So it's cool the program worksbut since yesterday I am
confronted with a case that I had not thought of that of the
cells(units) which contain returns in the line (alt+enter under Excel).
Once the generated csv I perceive that it provokes a return in the line in
my file.

What do you want to do for cells with a line break in them? Remove it?

Output it? Change it?


Nick


---------------------------------------------------------------------

To unsubscribe, e-mail: [hidden email]

For additional commands, e-mail: [hidden email]










If you reply to this email, your message will be added to the discussion below:
http://apache-poi.1045710.n5.nabble.com/Poi-Apache-case-of-cells-with-carriage-return-tp5709920p5709922.html



To unsubscribe from [ Poi Apache] case of cells with carriage return, click here.

NAML


--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Poi-Apache-case-of-cells-with-carriage-return-tp5709920p5709924.html
Sent from the POI - User mailing list archive at Nabble.com.

[erwan]

thank you for your answer

I want to replace the carriage return into a space (something like
string.replaceAll( carriage return , " ") ).

The fact is that I don't know the value of this carriage return .

Erwan

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Poi-Apache-case-of-cells-with-carriage-return-tp5709920p5709925.html
Sent from the POI - User mailing list archive at Nabble.com.

[erwan]

I solved the problem it was stupid I had forgotten that the G-string was
unchanging. We cannot modify it, in particular, value.replaceAll (thing,
thing) cannot modify G-string been worth.
I thought at once that it is a problem of the carriage return due to Excel.

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Poi-Apache-case-of-cells-with-carriage-return-tp5709920p5709934.html
Sent from the POI - User mailing list archive at Nabble.com.