使用POI和dom4j来解析Excel生成Xml

工作中有时候我们需要使用Apache的POI来读写Excel文件，而dom4j则用来解析或者生成Xml文件，由于涉及到IO流，所以又使用了commons-io提供的IOUtils来关闭流。

需要的Jar包

POI和dom4j需要的jar包

一个应用POI和dom4j的简单例子

import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.util.ArrayList;import java.util.List;import org.apache.commons.io.IOUtils;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.dom4j.Document;import org.dom4j.DocumentHelper;import org.dom4j.Element;import org.dom4j.io.OutputFormat;import org.dom4j.io.XMLWriter;public class MyPOI {    private boolean elemStarted = false;    private boolean attrStarted = false;    Element tabNode;    Element elemsNode;    Element elemNode;    Element subNode;    private final List<String> list = new ArrayList<String>();    public void parseExcel(final String src, final String target) throws Exception {        XSSFWorkbook workbook = null;        XSSFSheet sheet = null;        XMLWriter writer = null;        InputStream fin = null;        OutputStream fos = null;        final File srcFile = new File(src);        final String fileName = srcFile.getName();        final Document doc = DocumentHelper.createDocument();        // create the root element        final Element root = doc.addElement(fileName.substring(0, fileName.indexOf('.')));        root.addAttribute("position", fileName);        try {            fin = new FileInputStream(srcFile);            fos = new FileOutputStream(target);            workbook = new XSSFWorkbook(fin);            final int sheetNum = workbook.getNumberOfSheets();            for (int i = 0; i < sheetNum; i++) {                sheet = workbook.getSheetAt(i);                if (sheet == null) {                    continue;                }                // create the sheet element                final Element sheetNode = root.addElement("sheet");                sheetNode.addAttribute("id", sheet.getSheetName());                sheetNode.addAttribute("position", fileName + "," + sheet.getSheetName());                final int rowNum = sheet.getLastRowNum();                for (int j = 0; j <= rowNum; j++) {                    final XSSFRow row = sheet.getRow(j);                    if (row == null) {                        continue;                    }                    final XSSFCell firstCell = row.getCell(0);                    final String firstCellValue = getCellValue(firstCell);                    if (firstCell == null || "".equals(firstCellValue)) {                        continue;                    }                    if (firstCellValue.startsWith("#end_attr")) {                        attrStarted = false;                        continue;                    }                    if (firstCellValue.startsWith("#end_elem")) {                        elemStarted = false;                        continue;                    }                    if (firstCellValue.startsWith("##")) {                        // create the tab element                        tabNode = sheetNode.addElement(firstCellValue.substring(2));                        tabNode.addAttribute("position", fileName + "," + sheet.getSheetName() + "," + (j+1));                    } else if (firstCellValue.startsWith("#begin")) {                        j++;                        final XSSFRow nextRow = sheet.getRow(j);                        final int nextRowCellNum = nextRow.getLastCellNum();                        if (firstCellValue.startsWith("#begin_attr")) {                            attrStarted = true;                        } else if (firstCellValue.startsWith("#begin_elem")) {                            elemStarted = true;                            // create the elements element                            elemsNode = tabNode.addElement("elements");                            if (firstCellValue.indexOf(":") == -1) {                                elemsNode.addAttribute("id", "default");                            } else {                                elemsNode.addAttribute("id", firstCellValue.split(":")[1]);                            }                        }                        if (!list.isEmpty()) {                            list.clear();                        }                        for (int k = 0; k < nextRowCellNum; k++) {                            final XSSFCell nextRowCell = nextRow.getCell(k);                            String cellValue = getCellValue(nextRowCell);                            if (nextRowCell != null && !"".equals(cellValue)) {                                if (cellValue.endsWith("*")) {                                    cellValue = cellValue.substring(0, cellValue.length() - 1);                                }                                list.add(cellValue);                            }                        }                    } else {                        processRow(row);                    }                }            }            writer = new XMLWriter(fos, OutputFormat.createPrettyPrint());            writer.write(doc);        } finally {            if (workbook != null) {                try {                    workbook.close();                } catch (final IOException e) {                    e.printStackTrace();                }            }            IOUtils.closeQuietly(fin);            IOUtils.closeQuietly(fos);        }    }    private String getCellValue(final Cell cell) {        String value = "";        if (cell == null) {            return value;        }        if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){            cell.setCellType(Cell.CELL_TYPE_STRING);        }        switch (cell.getCellType()) {        case Cell.CELL_TYPE_STRING:            if (cell.getStringCellValue().startsWith("( *")) {                value = "";            } else {                value = cell.getStringCellValue();            }            break;        case Cell.CELL_TYPE_NUMERIC:            value = String.valueOf(cell.getNumericCellValue());            break;        case Cell.CELL_TYPE_BOOLEAN:            value = String.valueOf(cell.getBooleanCellValue());            break;        case Cell.CELL_TYPE_FORMULA:            value = String.valueOf(cell.getCellFormula());            break;        case Cell.CELL_TYPE_BLANK:            value = "";            break;        default:            break;        }        return value;    }    private void processRow(final XSSFRow row) {        if (attrStarted) {            for (int i = 0; i < list.size(); i++) {                final XSSFCell cell = row.getCell(i);                tabNode.addAttribute(list.get(i), getCellValue(cell));            }        }        if (elemStarted) {            elemNode = elemsNode.addElement("element");            final String tabPosition = tabNode.attributeValue("position");            final String positionPrefix = tabPosition.substring(0, tabPosition.lastIndexOf(","));            elemNode.addAttribute("position", positionPrefix + "," + (row.getRowNum() + 1));            for (int i = 0; i < list.size(); i++) {                final XSSFCell cell = row.getCell(i);                subNode = elemNode.addElement(list.get(i));                if (!"".equals(getCellValue(cell))) {                    subNode.setText(getCellValue(cell));                }            }        }    }}

在上边的类中，要注意流的关闭，不要直接在try块里边关闭，应该在finally里边使用IOUtils的closeQuietly方法来关闭，这样就可以不用自己去判断流是否为null，也不用自己再去try-catch流的close方法。接着是一个测试类。

public class POITest {    public static void main(final String[] args) {        final ResourceBundle rb = ResourceBundle.getBundle("xml-generator");        final String src = rb.getString("gen.excel.path");        final String target = rb.getString("gen.target.path");        try {            new LewisPOI().parseExcel(src, target);        } catch (final Exception e) {            e.printStackTrace();        }    }}

在POITest类，为了避免路径字符串的硬编码，我将路径存放到了一个名为xml-generator的properties文件中，该配置文件存放在项目的src目录下。该配置文件存放的是要读取的excel的路径，以及要生成的xml文件的路径，这里需要注意两个文件的目录是否正确，否则会无法正确的读取excel或者生成xml（可以自己先在代码中判断目录是否存在，若不存在则创建该目录）

gen.excel.path=D:/**/**.xlsxgen.target.path=D:/**/**.xml