解析xml文件并写入Excel表
类似于如下格式的xml数据，解析之后将数据写入Excel中
<?xml version="1.0" encoding="UTF-8"?>
<DATASETS>
<DATASET>
<education>undergraduate</education>
<occupation>programmer</occupation>
<name>张三</name>
<weight>150</weight>
<age>23</age>
<height>173</height>
<hobby>basketball</hobby>
</DATASET>
<DATASET>
<education>undergraduate</education>
<occupation>programmer</occupation>
<name>王一</name>
<weight>120</weight>
<age>22</age>
<height>168</height>
<hobby>game</hobby>
</DATASET>
<DATASET>
<education>undergraduate</education>
<occupation>programmer</occupation>
<name>魏某</name>
<weight>125</weight>
<age>22</age>
<height>170</height>
<hobby>novel</hobby>
</DATASET>
</DATASETS>
一. 构建springboot项目，导入依赖
<!--JDom解析xml -->
<dependency>
<groupId>org.jdom</groupId>
<artifactId>jdom</artifactId>
<version>1.1.3</version>
</dependency>
<!-- 读写Excel文件依赖 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
二. 直接上方法
/**
* xml文件转为Excel文件
* 返回：字符串
* @param xmlPosition (xml文件的位置)
* @param excelName （默认生成扩展名为xls的文件，默认位置生成在桌面）
* @return
*/
public static void xmlToExcel(String xmlPosition,String excelName){
//1.创建SAXBuilder对象
SAXBuilder saxBuilder = new SAXBuilder();
//2.创建输入流
InputStream is = null;
try {
is = new FileInputStream(new File(xmlPosition));
}catch (Exception e){
e.printStackTrace();
System.out.println("文件位置未找到！");
return;
}
//3.将输入流加载到build中
Document document = null;
try {
document = saxBuilder.build(is);
}catch (Exception e){
e.printStackTrace();
System.out.println("文件内容构建失败！");
System.out.println("请检查文件内容是否符合xml格式");
}
//4.获取根节点
Element rootElement = document.getRootElement();
//5.获取子节点
List<Element> children = rootElement.getChildren();
ArrayList<HashMap<String, Object>> list = new ArrayList<>();
for (Element child : children) {
// System.out.println("通过rollno获取属性值:"+child.getAttribute("属性名"));
// List<Attribute> attributes = child.getAttributes();
//打印属性
// for (Attribute attr : attributes) {
// System.out.println(attr.getName()+":"+attr.getValue());
// }
// 标签的属性，可以没有
List<Element> childrenList = child.getChildren();
HashMap<String, Object> map = new HashMap<>();
for (Element o : childrenList) {
// System.out.println("节点名:"+o.getName()+"---"+"节点值:"+o.getValue());
map.put(o.getName(), o.getValue());
}
list.add(map);
}
// System.out.println(list);
if (list.size() > 0){
// 新建excel文件（如果存在就提示换个名字）
File file = new File("C:\\Users\\Asus\\Desktop\\" + excelName + ".xls");
if (file.exists()){
System.out.println("该位置存在相同名字的文件，请重新命名！");
return;
}else {
try {
boolean newFile = file.createNewFile();
if (newFile){
System.out.println("文件创建成功！");
System.out.println("开始导入数据！");
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet1");
for (int i = 0; i <= list.size(); i++) {
HSSFRow row = sheet.createRow(i);
if (i == 0){
Set<Map.Entry<String, Object>> entries = list.get(i).entrySet();
int j = 0;
for (Map.Entry<String, Object> entry : entries) {
HSSFCell cell = row.createCell(j);
cell.setCellValue(entry.getKey());
j++;
}
}else {
Set<Map.Entry<String, Object>> entries = list.get(i-1).entrySet();
int j = 0;
for (Map.Entry<String, Object> entry : entries) {
HSSFCell cell = row.createCell(j);
cell.setCellValue(entry.getValue().toString());
j++;
}
}
}
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
wb.write(os);
}catch (IOException e){
System.out.println("导入失败！");
e.printStackTrace();
}
byte[] content = os.toByteArray();
OutputStream fos = null;
try {
fos = new FileOutputStream(file);
fos.write(content);
os.close();
fos.close();
System.out.println("数据导入成功！");
}catch (IOException e){
System.out.println("导入失败！");
e.printStackTrace();
}
}else {
System.out.println("文件创建失败！");
}
} catch (IOException e) {
e.printStackTrace();
System.out.println("excel文件创建失败！");
}
}
}else {
System.out.println("源文件没有数据！");
return;
}
三. 最后excel文件
四. 扩展： 上面提到了写入excel，下面来试试读
public void pushData() {
// 读取本地Excel表格数据
File file = new File("excel文件的地址");
Workbook wb = null;
try {
wb = readExcel(new FileInputStream(file), file.getName());
Row row = null;
if(wb != null){
//获取第一个sheet
Sheet sheet = wb.getSheetAt(0);
// 读取出来放在list里
ArrayList<HashMap<String, Object>> list = new ArrayList<>();
// 最大行数
int rows = sheet.getPhysicalNumberOfRows();
// 获取表格最后一行的行数
int lastRowNum = sheet.getLastRowNum();
// 获取第一行
Row firstRow = sheet.getRow(0);
// 获取第一行最后一列号
short lastCellNum = firstRow.getLastCellNum();
// 第一行第一列
Cell cell = firstRow.getCell(0);
// 第一行第一列的值
String value = cell.getStringCellValue();
// 根据实际情况可以把数据放在list中，通过行和列循环
}
} catch (FileNotFoundException e) {
System.out.println("读取文件失败！");
e.printStackTrace();
}
}
public Workbook readExcel(InputStream is, String extString){
Workbook wb = null;
extString = extString.substring(extString.lastIndexOf("."));
try {
if(".xls".equals(extString)){
return new HSSFWorkbook(is);
}else if(".xlsx".equals(extString)){
return new XSSFWorkbook(is);
}else {
return null;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return wb;
}
