Question

如何用java实现xml文件转excel

Answer 1

package com.wds.excelxml;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.NumberFormat;
import java.text.ParseException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import nu.xom.Attribute;
import nu.xom.Document;
import nu.xom.Element;
import nu.xom.Elements;
import nu.xom.Serializer;
public class Excelxml {

public static void main(String[] args) {
excelxml();
}

/**
* 从Excel到xml
* 从xml到Excel
*/
private static void excelxml(){
/*
* 首先创建一个xml文档
* 要创建xml文档，首先创建一个根元素
*/
Element reportRoot=new Element("sheet");
Document xmlReport=new Document(reportRoot);

try {
//读取Excel文件
FileInputStream excelFIS=new FileInputStream("D:\\JavaTest\\Employee_List.xls");
//创建Excel工作表
HSSFWorkbook excelWB=new HSSFWorkbook(excelFIS);
//获得Excel工作簿
HSSFSheet excelSheet=excelWB.getSheetAt(0);
//获得工作簿的行数
int rows=excelSheet.getPhysicalNumberOfRows();
//遍历工作簿的行
for(int rowIndex=0; rowIndex<rows;rowIndex++){
HSSFRow oneRow=excelSheet.getRow(rowIndex);
if(oneRow==null){
continue;
}
//在迭代每一行的时候，创建xml的行元素
Element rowElement=new Element("row");
//获得当前行的单元格数
int cells=oneRow.getPhysicalNumberOfCells();
//遍历行中的每一个单元格
for(int cellIndex=0;cellIndex<cells;cellIndex++){
HSSFCell oneCell=oneRow.getCell(cellIndex);
if(oneCell==null){
continue;
}
//设置元素的默认名称
String elementName="header";
//获得单元格所在列位置
int cellColumnIndex=oneCell.getColumnIndex();
if(rowIndex>0){
elementName=reportRoot.getFirstChildElement("row").getChild(cellColumnIndex).getValue();
}
/*
* 去掉非法字符
*/
elementName = elementName.replaceAll("[\\P{ascii}]","");
elementName = elementName.replaceAll(" ", "");

Element cellElement = new Element(elementName);
//添加属性和元素
//String attributeValue=oneCell.getCellStyle().getDataFormatString();
//Attribute dataFormatAttribute=new Attribute("dataFormat", attributeValue);
//cellElement.addAttribute(dataFormatAttribute);

/*
* 根据不同的属性添加
*/
Attribute strTypeAttribute=null;
switch (oneCell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
strTypeAttribute=new Attribute("dataType","String");
cellElement.addAttribute(strTypeAttribute);
cellElement.appendChild(oneCell.getStringCellValue());
rowElement.appendChild(cellElement);
break;
case HSSFCell.CELL_TYPE_NUMERIC:
strTypeAttribute=new Attribute("dataType","Numeric");
cellElement.addAttribute(strTypeAttribute);
HSSFDataFormatter dataFormatter=new HSSFDataFormatter();
String cellFormatted=dataFormatter.formatCellValue(oneCell);
cellElement.appendChild(cellFormatted);
rowElement.appendChild(cellElement);
break;
}
}
if(rowElement.getChildCount()>0){
reportRoot.appendChild(rowElement);
}
//System.out.println(xmlReport.toXML());
}

Answer 2

/**
*
* ExcelXML.java
* IBM_Developer_POI(Excel,Word) */
package com.wds.excelxml;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.NumberFormat;
import java.text.ParseException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import nu.xom.Attribute;
import nu.xom.Document;
import nu.xom.Element;
import nu.xom.Elements;
import nu.xom.Serializer;
public class ExcelXML {

public static void main(String[] args) {
excelXML();
}

/**
* 从Excel到XML
* 从XML到Excel
*/
private static void excelXML(){
/*
* 首先创建一个XML文档
* 要创建XML文档，首先创建一个根元素
*/
Element reportRoot=new Element("sheet");
Document xmlReport=new Document(reportRoot);

try {
//读取Excel文件
FileInputStream excelFIS=new FileInputStream("D:\\JavaTest\\Employee_List.xls");
//创建Excel工作表
HSSFWorkbook excelWB=new HSSFWorkbook(excelFIS);
//获得Excel工作簿
HSSFSheet excelSheet=excelWB.getSheetAt(0);
//获得工作簿的行数
int rows=excelSheet.getPhysicalNumberOfRows();
//遍历工作簿的行
for(int rowIndex=0; rowIndex<rows;rowIndex++){
HSSFRow oneRow=excelSheet.getRow(rowIndex);
if(oneRow==null){
continue;
}
//在迭代每一行的时候，创建xml的行元素
Element rowElement=new Element("row");
//获得当前行的单元格数
int cells=oneRow.getPhysicalNumberOfCells();
//遍历行中的每一个单元格
for(int cellIndex=0;cellIndex<cells;cellIndex++){
HSSFCell oneCell=oneRow.getCell(cellIndex);
if(oneCell==null){
continue;
}
//设置元素的默认名称
String elementName="header";
//获得单元格所在列位置
int cellColumnIndex=oneCell.getColumnIndex();
if(rowIndex>0){
elementName=reportRoot.getFirstChildElement("row").getChild(cellColumnIndex).getValue();
}
/*
* 去掉非法字符
*/
elementName = elementName.replaceAll("[\\P{ASCII}]","");
elementName = elementName.replaceAll(" ", "");

Element cellElement = new Element(elementName);
//添加属性和元素
//String attributeValue=oneCell.getCellStyle().getDataFormatString();
//Attribute dataFormatAttribute=new Attribute("dataFormat", attributeValue);
//cellElement.addAttribute(dataFormatAttribute);

/*
* 根据不同的属性添加
*/
Attribute strTypeAttribute=null;
switch (oneCell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
strTypeAttribute=new Attribute("dataType","String");
cellElement.addAttribute(strTypeAttribute);
cellElement.appendChild(oneCell.getStringCellValue());
rowElement.appendChild(cellElement);
break;
case HSSFCell.CELL_TYPE_NUMERIC:
strTypeAttribute=new Attribute("dataType","Numeric");
cellElement.addAttribute(strTypeAttribute);
HSSFDataFormatter dataFormatter=new HSSFDataFormatter();
String cellFormatted=dataFormatter.formatCellValue(oneCell);
cellElement.appendChild(cellFormatted);
rowElement.appendChild(cellElement);
break;
}
}
if(rowElement.getChildCount()>0){
reportRoot.appendChild(rowElement);
}
//System.out.println(xmlReport.toXML());
}

Answer 3

JDOM\XPATH 等方法，解析XML，，，，，POI导出到EXCEL