JAVA 生成导入EXCEL模板

student.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>

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.jdom.Attribute;
import org.jdom.Document;
import org.jdom.Element;
import org.jdom.input.SAXBuilder;
import org.junit.Test;

import java.io.File;
import java.io.FileOutputStream;
import java.util.List;

/**
 * 创建Excel导入模板
 */

public class CreteTemplate {
    @Test
    public void test() {
        {
            String path = System.getProperty("user.dir") + "\\student.xml";
            System.out.println("解析的XML路径为：" + path);
            File file = new File(path);
            SAXBuilder builder = new SAXBuilder();
            try {
                //解析XML
                Document parse = builder.build(file);
                HSSFWorkbook wb = new HSSFWorkbook();
                HSSFSheet sheet = wb.createSheet("Sheet0");
                Element root = parse.getRootElement();
                String templateName = root.getAttribute("name").getValue();
                int rownum = 0;
                int column = 0;

                //设置列宽
                Element colgroup = root.getChild("colgroup");
                setColumnWidth(sheet, colgroup);

                //设置标题
                Element title = root.getChild("title");
                List<Element> trs = title.getChildren("tr");
                for (int i = 0; i < trs.size(); i++) {
                    Element tr = trs.get(i);
                    List<Element> tds = tr.getChildren("td");
                    HSSFRow row = sheet.createRow(rownum);
                    HSSFCellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    for (column = 0; column < tds.size(); column++) {
                        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 (value != null) {
                            String val = value.getValue();
                            cell.setCellValue(val);
                            int rspan = rowSpan.getIntValue() - 1;
                            int cspan = colSpan.getIntValue() - 1;

                            //设置字体
                            HSSFFont font = wb.createFont();
                            font.setFontName("仿宋_GB2312");
                            font.setBold(true);
                            font.setFontHeightInPoints((short) 12);
                            cellStyle.setFont(font);
                            cell.setCellStyle(cellStyle);
                            //合并单元格居中
                            sheet.addMergedRegion(new CellRangeAddress(rspan, rspan, 0, cspan));
                        }
                    }
                    rownum++;
                }
                //设置表头
                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 (valueAttr != null) {
                            String value = valueAttr.getValue();
                            cell.setCellValue(value);
                        }
                    }
                    rownum++;
                }

                //设置数据区域样式
                Element tbody = root.getChild("tbody");
                Element tr = tbody.getChild("tr");
                int repeat = tr.getAttribute("repeat").getIntValue();

                List<Element> tds = tr.getChildren("td");
                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(wb, cell, td);
                    }
                    rownum++;
                }

                //生成Excel导入模板
                File tempFile = new File("./" + templateName + ".xls");
                tempFile.delete();
                tempFile.createNewFile();
                FileOutputStream stream = FileUtils.openOutputStream(tempFile);
                wb.write(stream);
                stream.close();

            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 设置单元格样式
     *
     * @param wb
     * @param cell
     * @param td
     */
    private static void setType(HSSFWorkbook wb, HSSFCell cell, Element td) {
        Attribute cellTypeAttr = td.getAttribute("type");
        String cellType = cellTypeAttr.getValue();
        HSSFDataFormat format = wb.createDataFormat();
        HSSFCellStyle cellStyle = wb.createCellStyle();
        if ("NUMERIC".equalsIgnoreCase(cellType)) {
            cell.setCellType(CellType.NUMERIC);
            Attribute cellFormatAttr = td.getAttribute("format");
            String cellFormatValue = cellFormatAttr.getValue();
            cellFormatValue = StringUtils.isNotBlank(cellFormatValue) ? cellFormatValue : "#,##0.00";
            cellStyle.setDataFormat(format.getFormat(cellFormatValue));
        } else if ("STRING".equalsIgnoreCase(cellType)) {
            cell.setCellValue("");
            cell.setCellType(CellType.STRING);
            cellStyle.setDataFormat(format.getFormat("@"));
        } else if ("DATE".equalsIgnoreCase(cellType)) {
            cell.setCellType(CellType.NUMERIC);
            cellStyle.setDataFormat(format.getFormat("yyyy-m-d"));
        } else if ("ENUM".equalsIgnoreCase(cellType)) {
            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);
            wb.getSheetAt(0).addValidationData(dataValidation);
        }
        cell.setCellStyle(cellStyle);
    }


    /**
     * 设置列宽
     *
     * @param sheet    待的Excel sheet
     * @param colgroup XML模板属性
     */
    private static void setColumnWidth(HSSFSheet sheet, Element colgroup) {
        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)) {
                v = Math.round(Float.parseFloat(value) * 37F);
            } else if ("em".endsWith(unit)) {
                v = Math.round(Float.parseFloat(value) * 267.5F);
            }
            sheet.setColumnWidth(i, v);
        }
    }

}

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>vip.fkandy</groupId>
    <artifactId>excel_export_import</artifactId>
    <version>1.0-SNAPSHOT</version>
    <dependencies>
        <!-- JXL -->
        <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.12</version>
        </dependency>

        <!-- poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.6</version>
        </dependency>

        <!-- jdom -->
        <dependency>
            <groupId>jdom</groupId>
            <artifactId>jdom</artifactId>
            <version>1.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.7</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

</project>