- 前言
- 一、maven如下
- 二、解析类
- SAXHandler类
- XmlConvertExcel类
- XmlRow类
- 三、测试
- 四、效果
旧版的 excel 文件 Office XML是xml类型的，也成为SpreadsheetML类型，很古老的excel类型了是2002年左右的格式，现在的格式都是用的 xls 或者 xlsx。遇到的问题就是要把xml类型的 Office XML转化为 xlsx的excel，所以写了本篇文章方便以后遇到这个问题的人。
xml转化为excel可以采用安装JODConverter+OpenOffice ，然后使用JODConverter来将xml转化为excel，例如：
public static void main(String[] args) {
LocalOfficeManager officeManager = LocalOfficeManager.install();
try {
officeManager.start();
File source = new File("C:\tmp\jodc\2019_enlarged.xml");
File target = new File("C:\tmp\jodc\2019_enlarged.xlsx");
LocalConverter.builder().build().convert(source).to(target).execute();
} catch (Exception e) {
OfficeUtils.stopQuietly(officeManager);
}
}
只不过OpenOffice安装包100多M太大了，所以本文通过另一种方式手动解析xml来转换为excel文件
（注意，本文的代码只支持横向的单元格合并，纵向合并的单元格因为会受到横向合并的单元格而对不齐）
二、解析类 SAXHandler类org.apache.poi poi 4.1.2 org.apache.logging.log4j log4j-api 2.17.1 org.apache.logging.log4j log4j-core 2.17.1 org.apache.commons commons-math3 3.6.1 org.apache.poi poi-ooxml 4.1.2 org.apache.xmlbeans xmlbeans 3.1.0 org.apache.commons commons-collections4 4.4 org.apache.poi poi-ooxml-schemas 4.1.2 org.apache.commons commons-compress 1.18 com.alibaba fastjson 1.2.83 commons-io commons-io 2.8.0
package com.diff;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;
import java.util.ArrayList;
import java.util.List;
public class SAXHandler extends DefaultHandler {
private List xmlRowList = new ArrayList<>();
//存储所有Worksheet的内容
public List> xmlSheetList = new ArrayList<>();
//存储所有Worksheet的ss:Name属性值
public List sheetNameList = new ArrayList<>();
XmlRow xmlRow = null;
String content = null;
Integer mergeAcross = null;
Integer mergeDown = null;
@Override
//当开始标签被找到时
public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {
int size = attributes.getLength();
switch (qName) {
//Create a new Row object when the start tag is found
case "Worksheet":
xmlRowList.clear();
for (int i = 0; i < size; i++) {
String attName = attributes.getQName(i);
if ("ss:Name".equals(attName)) {
sheetNameList.add(attributes.getValue(i));
}
}
break;
case "Row":
xmlRow = new XmlRow();
break;
case "Cell":
mergeAcross = mergeDown = 0;
for (int i = 0; i < size; i++) {
String attName = attributes.getQName(i);
if ("ss:MergeAcross".equals(attName)) {
mergeAcross = Integer.parseInt(attributes.getValue(i));
}
if ("ss:MergeDown".equals(attName)) {
mergeDown = Integer.parseInt(attributes.getValue(i));
}
}
break;
}
}
@Override
public void endElement(String uri, String localName, String qName) throws SAXException {
switch (qName) {
case "Worksheet":
//xmlRowList深拷贝为newXmlRowList
String listStr = JSONObject.toJSONString(xmlRowList);
List newXmlRowList = JSON.parseArray(listStr, XmlRow.class);
xmlSheetList.add(newXmlRowList);
break;
case "Row":
xmlRowList.add(xmlRow);
break;
case "Cell":
xmlRow.cellMergeAcrossList.add(mergeAcross);
xmlRow.cellMergeDownList.add(mergeDown);
break;
case "Data":
xmlRow.cellList.add(content);
break;
}
}
@Override
public void characters(char[] ch, int start, int length) throws SAXException {
content = String.copyValueOf(ch, start, length).trim();
}
}
XmlConvertExcel类
package com.diff;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.TypeReference;
import org.apache.commons.io.IOUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.xml.sax.SAXException;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import java.io.*;
import java.nio.charset.Charset;
import java.util.List;
public class XmlConvertExcel {
public Workbook workbook = null;
public Sheet sheet = null;
public CellStyle style = null;
public XmlConvertExcel() {
workbook = new XSSFWorkbook();
style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
}
public SAXHandler parseXmlData(String xmlPath) throws ParserConfigurationException, SAXException, IOException {
//解析xml文件内容为 handler.xmlRowList ,xmlRowList中包含了单元格内容，横向合并单元格数量和纵向合并单元格数量
File file = new File(xmlPath);
SAXParserFactory parserFactor = SAXParserFactory.newInstance();
SAXParser parser = parserFactor.newSAXParser();
SAXHandler handler = new SAXHandler();
String fileContent = IOUtils.toString(new FileInputStream(file), Charset.defaultCharset());
ByteArrayInputStream bis = new ByteArrayInputStream(fileContent.getBytes());
parser.parse(bis, handler);
return handler;
}
public void xmlToExcel(String xmlPath, String outExcelPath) throws IOException, ParserConfigurationException, SAXException {
//解析xml获得具体数据
SAXHandler handler = parseXmlData(xmlPath);
handleSheet(handler);
//数据写入到最终xlsx文件
FileOutputStream fout = new FileOutputStream(outExcelPath);
workbook.write(fout);
workbook.close();
fout.close();
}
public void handleSheet(SAXHandler handler) {
//深拷贝xmlSheetList 为 newXmlSheetList .
String listStr = JSONObject.toJSONString(handler.xmlSheetList);
List> newXmlSheetList = JSON.parseObject(listStr, new TypeReference>>() {
});
int sheetIndex = 0;
for (List xmlRowList : newXmlSheetList) {
String sheetName = handler.sheetNameList.get(sheetIndex);
sheet = workbook.createSheet(sheetName);
handleRow(xmlRowList);
sheetIndex++;
}
}
public void handleRow(List xmlRowList) {
//深拷贝xmlSheetList 为 newXmlSheetList .
String listStr = JSONObject.toJSONString(xmlRowList);
List newXmlRowList = JSON.parseObject(listStr, new TypeReference>() {
});
//遍历每一行处理要合并的单元格（即如果该单元格是要合并n个单元格就在其后插入n个空内容）
for (int i = 0; i < xmlRowList.size(); i++) {
//每一行的数据包括单元格内容、横向要合并的单元格数量、纵向合并的单元格数量
XmlRow subsRow = xmlRowList.get(i);
List mergeAcrossList = subsRow.cellMergeAcrossList;
List mergeDownList = subsRow.cellMergeDownList;
List cellList = subsRow.cellList;
//拷贝后的每一行的数据包括单元格内容、横向要合并的单元格数量、纵向合并的单元格数量
XmlRow newSubsRow = newXmlRowList.get(i);
//该行每个单元格的内容
List newCellList = newSubsRow.cellList;
//该行每个单元格的横向合并单元格的数量
List newMergeAcrossList = newSubsRow.cellMergeAcrossList;
//该行每个单元格的纵向合并单元格的数量
List newMergeDownList = newSubsRow.cellMergeDownList;
int cellCount = 0;
int addSum = 0;
//遍历该行单元格
for (int j = 0; j < cellList.size(); j++) {
String cellValue = cellList.get(j);//该单元格内容
Integer cellMergeAcross = mergeAcrossList.get(j);//该单元格横向合并单元格数量
Integer cellMergeDown = mergeDownList.get(j);//该单元格纵向合并单元格数量
if (cellMergeAcross != 0) {
Integer temp = cellCount + addSum;
for (int n = 0; n < cellMergeAcross; n++) {
temp = temp + 1;
//该单元格后要合并的单元格填充为“”
newCellList.add(temp, "");
addSum++;
}
}
if (cellMergeDown != 0) {
for (int n = 0; n < cellMergeDown; n++) {
//修改原数据的下一行数据的横合并数据
XmlRow nextRow = xmlRowList.get(i + n + 1);
List nextMergeAcrossList = nextRow.cellMergeAcrossList;
nextMergeAcrossList.set(j, cellMergeAcross);
}
}
cellCount++;
}
}
writeCell(xmlRowList, newXmlRowList);
}
public void writeCell(List xmlRowList, List newXmlRowList) {
//数据写入单元格
int rowCount = 0;
for (XmlRow subsRow : newXmlRowList) {
Row row = sheet.createRow(rowCount);
int cellCount = 0;
for (String cellValue : subsRow.cellList) {
Cell cell = row.createCell(cellCount);
cell.setCellValue(cellValue);
cell.setCellStyle(style);
cellCount++;
}
rowCount++;
}
//合并单元格
rowCount = 0;
int rowMerge = 0;
for (XmlRow subsRow : xmlRowList) {
int cellCount = 0;
for (String cellValue : subsRow.cellList) {
Integer mergeAcross = subsRow.cellMergeAcrossList.get(cellCount);
Integer mergeDown = subsRow.cellMergeDownList.get(cellCount);
if (mergeAcross != 0 || mergeDown != 0) {
int firstCoumn = rowMerge == 0 ? cellCount + mergeDown : cellCount + rowMerge;
int endCoumn = rowMerge == 0 ? cellCount + mergeAcross : cellCount + rowMerge + mergeAcross;
//不支持合并纵向单元格
if (endCoumn >= firstCoumn) {
CellRangeAddress region = new CellRangeAddress(rowCount, rowCount + mergeDown, firstCoumn, endCoumn);
sheet.addMergedRegion(region);
}
if (mergeAcross != 0) {
rowMerge += mergeAcross;
}
}
cellCount++;
}
rowMerge = 0;
rowCount++;
}
}
}
XmlRow类
package com.diff;
import java.util.ArrayList;
public class XmlRow {
//存储Cell中Data的数据
public ArrayList cellList = new ArrayList<>();
//存储Cell中ss:MergeAcross的属性值
public ArrayList cellMergeAcrossList = new ArrayList<>();
//存储Cell中ss:MergeDown的属性值
public ArrayList cellMergeDownList = new ArrayList<>();
@Override
public String toString() {
return cellList.toString();
}
}
三、测试
public static void main(String[] args) throws Exception {
XmlConvertExcel xmlConvertExcel=new XmlConvertExcel();
xmlConvertExcel.xmlToExcel("F:\excels\bbb.xml","F:\excels\bbb.xlsx");
}
四、效果
参考：
How to load old Microsoft Office XML file (Excel) using Java
Reading Excel Spreadsheet XML
