jdom解析xml，生成Excel模板

需要新添加jar包：jdom.jar、commons-lang3-3.1.jar
java代码

package com.csdn.excel;import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import java.util.List;import jxl.Workbook;import org.apache.commons.io.FileUtils;import org.apache.commons.lang3.StringUtils;import org.apache.poi.hssf.usermodel.DVConstraint;import org.apache.poi.hssf.usermodel.HSSFBorderFormatting;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.HSSFDataValidation;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.CellRangeAddressList;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.util.StringUtil;import org.jdom.Attribute;import org.jdom.Document;import org.jdom.Element;import org.jdom.JDOMException;import org.jdom.input.SAXBuilder;/** * jdom解析xml * @author Administrator */public class createTemplate {    public static void main(String[] args) {        //获取xml文件路径        String path = System.getProperty("user.dir")+"/bin/student.xml";        System.out.println(path);        File file = new File(path);        SAXBuilder builder = new SAXBuilder();        try {            //解析xml文件            Document parse = builder.build(file);            //创建一个Excel            HSSFWorkbook workbook = new HSSFWorkbook();            //创建sheet            HSSFSheet sheet = workbook.createSheet("sheet0");            //获取根节点            Element root = parse.getRootElement();            //获取模板的名称            String templateName = root.getAttributeValue("name");            int rownum = 0;            int column = 0;            //设置列宽            Element colgroup = root.getChild("colgroup");            setColumnWidth(sheet,colgroup);            /**             * 设置标题以及样式 <title></title>             */            Element title = root.getChild("title");            List<Element> trs = title.getChildren("tr");//获取标题下的tr标签            for(int i=0;i<trs.size();i++){//遍历tr                Element tr = trs.get(i);                List<Element> tds = tr.getChildren("td");//获取tr标签下的td标签                HSSFRow row = sheet.createRow(rownum);                //设置样式                HSSFCellStyle cellStyle = workbook.createCellStyle();                //单元格居中                cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);                for (column = 0; column < tds.size(); column++) {//遍历td                    Element td = tds.get(column);                    HSSFCell cell = row.createCell(column);                    Attribute rowSpan = td.getAttribute("rowspan");                    Attribute colSpan = td.getAttribute("colspan");                    Attribute value = td.getAttribute("value");                    if(null!=value){                        String val = value.getValue();                        cell.setCellValue(val);                        //设置字体                        HSSFFont font = workbook.createFont();                        font.setFontName("仿宋_GB2312");                        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗//                      font.setFontHeight((short)12);                        font.setFontHeightInPoints((short)12);                        cellStyle.setFont(font);                        cell.setCellStyle(cellStyle);                        //合并单元格居中                        int rspan = rowSpan.getIntValue()-1;                        int cspan = colSpan.getIntValue()-1;                        //4个参数：开始行、结束行、开始列、结束列                        sheet.addMergedRegion(new CellRangeAddress(rspan,rspan,0,cspan));                    }                }                rownum++;            }            /**             * 设置表头信息   <thead></thead>             */            Element thead = root.getChild("thead");            trs = thead.getChildren("tr");            for (int i = 0; i < trs.size(); i++) {                Element tr = trs.get(i);                //创建行数据                HSSFRow row = sheet.createRow(rownum);                List<Element> ths = tr.getChildren("th");                for (column = 0; column < ths.size(); column++) {                    Element th = ths.get(column);                    Attribute valueAttr = th.getAttribute("value");                    //创建单元格                    HSSFCell cell = row.createCell(column);                    if(null != valueAttr){                        String value = valueAttr.getValue();                        cell.setCellValue(value);                    }                }                rownum++;            }            /**             * 设置数据区域<tbody></tbody>             */            Element tbody = root.getChild("tbody");            Element tr = tbody.getChild("tr");            int repeat = tr.getAttribute("repeat").getIntValue();            List<Element> tds = tr.getChildren("td");            System.out.println("repeat:  "+repeat);            for (int i = 0; i < repeat; i++) {                HSSFRow row = sheet.createRow(rownum);                for(column = 0;column<tds.size();column++){                    Element td = tds.get(column);                    HSSFCell cell = row.createCell(column);                    setType(workbook,cell,td);                }                rownum++;            }            //生成模板            File tempFile = new File("e:/"+templateName+".xls");            tempFile.delete();            tempFile.createNewFile();            FileOutputStream stream = FileUtils.openOutputStream(tempFile);            workbook.write(stream);            stream.close();        } catch (Exception e) {            e.printStackTrace();        }    }    /**     * 设置单元格样式     * @param workbook     * @param cell     * @param td     */    private static void setType(HSSFWorkbook workbook, HSSFCell cell, Element td) {        Attribute typeAttr = td.getAttribute("type");        String type = typeAttr.getValue();        HSSFDataFormat format = workbook.createDataFormat();        HSSFCellStyle cellStyle = workbook.createCellStyle();        if("numeric".equalsIgnoreCase(type) ){            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);            Attribute formatAttr = td.getAttribute("format");            String formatValue = formatAttr.getValue();            formatValue = StringUtils.isNotBlank(formatValue)?formatValue:"#,##0.00";//判断value是否为空，设置默认值            cellStyle.setDataFormat(format.getFormat(formatValue));        }else if("string".equalsIgnoreCase(type)){            cell.setCellValue("");            cell.setCellType(HSSFCell.CELL_TYPE_STRING);            cellStyle.setDataFormat(format.getFormat("@"));//设置成文本        }else if("date".equalsIgnoreCase(type)){            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);//日期            cellStyle.setDataFormat(format.getFormat("yyyy-m-d"));        }else if("enum".equalsIgnoreCase(type)){            CellRangeAddressList regions =                 new CellRangeAddressList(cell.getRowIndex(), cell.getRowIndex(),cell.getColumnIndex(), cell.getColumnIndex());            Attribute enumAttr = td.getAttribute("format");            String enumValue = enumAttr.getValue();            //加载下拉列表内容            DVConstraint constraint =                 DVConstraint.createExplicitListConstraint(enumValue.split(","));            //数据有效性对象            HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);            workbook.getSheetAt(0).addValidationData(dataValidation);        }        cell.setCellStyle(cellStyle);    }    /**     * 设置列宽     * @param sheet     * @param colgroup     */    private static void setColumnWidth(HSSFSheet sheet, Element colgroup) {        // TODO Auto-generated method stub        List<Element> cols = colgroup.getChildren("col");        for (int i = 0; i < cols.size(); i++) {            Element col = cols.get(i);            Attribute width = col.getAttribute("width");            String unit = width.getValue().replaceAll("[0-9,\\.]", "");//正则表达式截取数字、小数点            String value = width.getValue().replaceAll(unit, "");            int v = 0;            if(StringUtils.isBlank(unit)|| "px".endsWith(unit)){//如果unit为空或者最后单位是像素的                v = Math.round(Float.parseFloat(value)*37F);             }else if("em".endsWith(unit)){//如果unit最后单位是em                v = Math.round(Float.parseFloat(value)*267.5F);             }            sheet.setColumnWidth(i, v);//设置宽度        }    }}

studen.xml文件

<?xml version="1.0" encoding="UTF-8"?><excel id="student" code="student" name="学生信息导入">    <colgroup>        <col index="A" width='17em'></col>        <col index="B" width='17em'></col>        <col index="C" width='17em'></col>        <col index="D" width='17em'></col>        <col index="E" width='17em'></col>        <col index="F" width='17em'></col>            </colgroup>    <title>        <tr height="16px">            <td rowspan="1" colspan="6" value="学生信息导入" />        </tr>    </title>    <thead>        <tr height="16px">            <th value="编号" />            <th value="姓名" />            <th value="年龄" />            <th value="性别" />            <th value="出生日期" />            <th value=" 爱好" />                    </tr>    </thead>    <tbody>        <tr height="16px" firstrow="2" firstcol="0" repeat="5">            <td type="string" isnullable="false" maxlength="30" /><!--用户编号 -->            <td type="string" isnullable="false" maxlength="50" /><!--姓名 -->            <td type="numeric" format="##0" isnullable="false" /><!--年龄 -->            <td type="enum" format="男,女" isnullable="true" /><!--性别 -->            <td type="date" isnullable="false" maxlength="30" /><!--出生日期 -->            <td type="enum" format="足球,篮球,乒乓球" isnullable="true" /><!--爱好 -->        </tr>    </tbody></excel>