ExcelToXml

package com.huawei.it.excel;


import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.StringWriter;
import java.io.UnsupportedEncodingException;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.XMLWriter;


public class ExcelToXml {
public static final String FILE_PATCH="d:/lib/test.xls";
public static final String NEW_FILE_PATCH="d:/lib/new.xml";
public int rows = 0;
public HSSFSheet sheet;
public void readExcel() throws Exception{

InputStream is = new FileInputStream(new File(ExcelToXml.FILE_PATCH));

HSSFWorkbook wb = new HSSFWorkbook(is);
sheet = wb.getSheetAt(0);
rows = sheet.getPhysicalNumberOfRows();
HSSFRow oneRow = sheet.getRow(0);
if(oneRow == null){
return;
}
int cells = oneRow.getPhysicalNumberOfCells();
for(int i=0; i<cells; i++){
String title = getStringCellValue(oneRow.getCell(i));
}
createXml();
}

public void createXml(){
Document document = DocumentHelper.createDocument(); 
Element root = document.addElement("root");
for(int i=1; i<rows; i++) {
Element student =  root.addElement("student");
HSSFRow row = sheet.getRow(i);
if(row == null){
continue;
}
String name = getStringCellValue(row.getCell(0));
Element nameEle = student.addElement("name");
nameEle.addText(name);
 
String age = getStringCellValue(row.getCell(1));
Element ageEle = student.addElement("age");
ageEle.addText(age);
 
String sex = getStringCellValue(row.getCell(2));
Element sexEle = student.addElement("sex");
sexEle.addText(sex);
 
String desc = getStringCellValue(row.getCell(3));
Element descEle = student.addElement("desc");
descEle.addText(desc);
}

print(document);
}
/**
     * 获取单元格数据内容为字符串类型的数据
     * 
     * @param cell Excel单元格
     * @return String 单元格数据内容
     */
    private String getStringCellValue(HSSFCell cell) {
        String strCell = "";
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            strCell = cell.getStringCellValue();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            strCell = String.valueOf(cell.getNumericCellValue());
            break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            strCell = String.valueOf(cell.getBooleanCellValue());
            break;
        case HSSFCell.CELL_TYPE_BLANK:
            strCell = "";
            break;
        default:
            strCell = "";
            break;
        }
        if (strCell.equals("") || strCell == null) {
            return "";
        }
        if (cell == null) {
            return "";
        }
        return strCell;
    }
    
    public void print(Document document){
    StringWriter stringWriter = new StringWriter();  
        OutputFormat xmlFormat = new OutputFormat();  
        xmlFormat.setEncoding("GBK"); 
        xmlFormat.setNewlines(true); 
        xmlFormat.setIndent(true); 
        xmlFormat.setIndent("    "); 
//        ByteArrayOutputStream out = new ByteArrayOutputStream();
        FileOutputStream out = null;
try {
out = new FileOutputStream(new File(NEW_FILE_PATCH));
} catch (FileNotFoundException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
        XMLWriter xmlWriter;
try {
xmlWriter = new XMLWriter(out,xmlFormat);
xmlWriter.write(document);  
xmlWriter.close();
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}  
        System.out.println(document.toString()); 
    }
    


}