X
[XaToA]
i have the bellow code. with this code i want to parse one XML String and to
convert into one EXCEL sheet using the apache POI classes.
the code creates the sheet, but it doesn't put the values into the cells.
the XML String has several <registro> nodes, foreach registro node i want to
create one row. Each registro node has several nodes (can be 1 or x nodes),
and foreach node into the registro node i wanto to create one cell into the
registro row.
can you help me for solving this problem?
i am working into this problem 7 days and i dont solve it.
please help me.
thanks
import java.io.*;
import java.util.*;
import java.util.Date;
import java.text.SimpleDateFormat;
import javax.xml.parsers.*;
import org.w3c.dom.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
public class convierteXML2EXCEL {
StringBuffer sb = new StringBuffer();
String delim = "-";
String nl = System.getProperty("line.separator");
SimpleDateFormat fechabase = new SimpleDateFormat("dd-MM-yyyy");
String strhoy = fechabase.format(new Date());
short rowNum = 0;
short colNum = 0;
public String getEXCEL(Document doc) {
buscar(doc, 0);
sb.setLength(sb.length()-1);
return sb.toString();
}
public void buscar(Node node, int level) {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFRow row = null;
HSSFSheet sheet = wb.createSheet("Preguntas-soporte, "+strhoy);
NodeList listar = node.getChildNodes();
row = sheet.createRow(rowNum);
for (int i=0; i<listar.getLength(); i++) {
Node childNode = listar.item(i);
if (level==3 && childNode.getNodeType()==3) {
row.createCell(colNum).setCellValue(childNode.getNodeValue());
sb.append("__ "+childNode.getNodeValue() + delim);
colNum++;
} else if (level==1 && childNode.getNodeType()==1 && sb.length()>1)
{
rowNum++;
colNum=0;
sb.setLength(sb.length()-1);
sb.append(nl);
}
buscar(childNode, level+1);
}
try{
FileOutputStream fileOut = new
FileOutputStream("c:\\"+strhoy+".xls");
wb.write(fileOut);
fileOut.close();
}catch(Exception e){
System.out.println("sss "+e.getMessage());
}
}
public static void main(String[] args) throws Exception {
String f ="<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>"+
"<registros>"+
"<registro><nombre>nombre1</nombre><apellido>apellido1</apellido><apellido2>
apellido2</apellido2></registro>"+
"<registro><nombre>nombre2</nombre><apellido>apellido2</apellido></registro>
"+
"</registros>";
Document doc = null;
DocumentBuilder db =
DocumentBuilderFactory.newInstance().newDocumentBuilder();
if (args.length == 1)
doc = db.parse( args[0] );
else
doc = db.parse(new ByteArrayInputStream(f.getBytes()));
convierteXML2EXCEL k = new convierteXML2EXCEL();
f = k.getEXCEL(doc);
System.out.println("."+f+".");
}
}
convert into one EXCEL sheet using the apache POI classes.
the code creates the sheet, but it doesn't put the values into the cells.
the XML String has several <registro> nodes, foreach registro node i want to
create one row. Each registro node has several nodes (can be 1 or x nodes),
and foreach node into the registro node i wanto to create one cell into the
registro row.
can you help me for solving this problem?
i am working into this problem 7 days and i dont solve it.
please help me.
thanks
import java.io.*;
import java.util.*;
import java.util.Date;
import java.text.SimpleDateFormat;
import javax.xml.parsers.*;
import org.w3c.dom.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
public class convierteXML2EXCEL {
StringBuffer sb = new StringBuffer();
String delim = "-";
String nl = System.getProperty("line.separator");
SimpleDateFormat fechabase = new SimpleDateFormat("dd-MM-yyyy");
String strhoy = fechabase.format(new Date());
short rowNum = 0;
short colNum = 0;
public String getEXCEL(Document doc) {
buscar(doc, 0);
sb.setLength(sb.length()-1);
return sb.toString();
}
public void buscar(Node node, int level) {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFRow row = null;
HSSFSheet sheet = wb.createSheet("Preguntas-soporte, "+strhoy);
NodeList listar = node.getChildNodes();
row = sheet.createRow(rowNum);
for (int i=0; i<listar.getLength(); i++) {
Node childNode = listar.item(i);
if (level==3 && childNode.getNodeType()==3) {
row.createCell(colNum).setCellValue(childNode.getNodeValue());
sb.append("__ "+childNode.getNodeValue() + delim);
colNum++;
} else if (level==1 && childNode.getNodeType()==1 && sb.length()>1)
{
rowNum++;
colNum=0;
sb.setLength(sb.length()-1);
sb.append(nl);
}
buscar(childNode, level+1);
}
try{
FileOutputStream fileOut = new
FileOutputStream("c:\\"+strhoy+".xls");
wb.write(fileOut);
fileOut.close();
}catch(Exception e){
System.out.println("sss "+e.getMessage());
}
}
public static void main(String[] args) throws Exception {
String f ="<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>"+
"<registros>"+
"<registro><nombre>nombre1</nombre><apellido>apellido1</apellido><apellido2>
apellido2</apellido2></registro>"+
"<registro><nombre>nombre2</nombre><apellido>apellido2</apellido></registro>
"+
"</registros>";
Document doc = null;
DocumentBuilder db =
DocumentBuilderFactory.newInstance().newDocumentBuilder();
if (args.length == 1)
doc = db.parse( args[0] );
else
doc = db.parse(new ByteArrayInputStream(f.getBytes()));
convierteXML2EXCEL k = new convierteXML2EXCEL();
f = k.getEXCEL(doc);
System.out.println("."+f+".");
}
}