ExcelToXml
来源:互联网 发布:gre模考软件 编辑:程序博客网 时间:2024/06/09 16:47
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());
}
}
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());
}
}
0 0
- ExcelToXml
- ExcelToXml小工具
- 求两个数最大公约数 (辗转相除法)
- BZOJ 题目1878: [SDOI2009]HH的项链(树状数组离线求区间不同种类数)
- 实现empList的序列化和反序列化
- [RealTime]Java即时编译技术的了解
- BeautifulSoup解析文档只有部分内容
- ExcelToXml
- javascript对象知识总结
- LeetCode:Linked List Cycle && Linked List Cycle II
- 数据库设计(5)-理解用户需求
- *Codeforces Round #316 (Div. 2)- D. Tree Requests (dfs+二分)
- tesseract-ocr识别中文扫描图片实例讲解
- java的匿名内部类
- 【JavaSE】day11_Reader和Writer_PrintWriter_BufferedReader
- 虚拟机安装的UBUNTU全屏的方法