Java Excel 导入导出（二）

本文主要叙述定制导入模板——利用XML解析技术，确定模板样式。

1.确定模板列

2.定义标题（合并单元格）

3.定义列名

4.定义数据区域单元格样式

引入jar包：

一、预期格式类型

二、XML模板格式

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <excel id="student" code="student" name="学生信息导入">
 3     <colgroup>
 4         <col index="A" width="17em"></col>
 5         <col index="B" width="17em"></col>
 6         <col index="C" width="17em"></col>
 7         <col index="D" width="17em"></col>
 8         <col index="E" width="17em"></col>
 9         <col index="F" width="17em"></col>
10     </colgroup>
11     <tile>
12         <tr height="16px">
13             <td rowspan="1" colspan="6" value="学生信息导入"></td>
14         </tr>
15     </tile>
16     <thead>
17         <tr height="16px">
18             <th value="编号"></th>
19             <th value="姓名"></th>
20             <th value="年龄"></th>
21             <th value="性别"></th>
22             <th value="出生日期"></th>
23             <th value="爱好"></th>
24         </tr>
25     </thead>
26     <tbody>
27         <tr height="16px" firstrow="2" firstcol="0" repeat="5" >
28             <td type="string" isnullable="false" maxlength="30"></td><!-- 用户编号 -->
29             <td type="string" isnullable="false" maxlength="50"></td><!-- 姓名 -->
30             <td type="numeric" format="##0" isnullable="false"></td><!-- 年龄 -->
31             <td type="enum" format="男,女" isnullable="true"></td><!-- 性别 -->
32             <td type="date" isnullable="false" maxlength="30"></td><!-- 出生日期 -->
33             <td type="enum" format="足球,篮球,兵乓球" isnullable="true" ></td><!-- 爱好 -->
34         </tr>
35     </tbody>
36 </excel>

二、Java解析XML模板

  1 import java.io.File;
  2 import java.io.FileOutputStream;
  3 import java.util.List;
  4 
  5 import org.apache.commons.io.FileUtils;
  6 import org.apache.commons.lang3.StringUtils;
  7 import org.apache.poi.hssf.usermodel.DVConstraint;
  8 import org.apache.poi.hssf.usermodel.HSSFCell;
  9 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
 10 import org.apache.poi.hssf.usermodel.HSSFDataFormat;
 11 import org.apache.poi.hssf.usermodel.HSSFDataValidation;
 12 import org.apache.poi.hssf.usermodel.HSSFFont;
 13 import org.apache.poi.hssf.usermodel.HSSFRow;
 14 import org.apache.poi.hssf.usermodel.HSSFSheet;
 15 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 16 import org.apache.poi.ss.util.CellRangeAddress;
 17 import org.apache.poi.ss.util.CellRangeAddressList;
 18 import org.jdom.Attribute;
 19 import org.jdom.Document;
 20 import org.jdom.Element;
 21 import org.jdom.input.SAXBuilder;
 22 
 23 public class CreateTemplate {
 24 
 25     /**
 26      * 创建模板文件
 27      * 
 28      * @author
 29      * @param args
 30      */
 31     public static void main(String[] args) {
 32         // 获取解析XML路径
 33         String path = System.getProperty("user.dir") + "/bin/Student.xml";
 34         File file = new File(path);
 35         SAXBuilder builder = new SAXBuilder();
 36         try {
 37             Document parse = builder.build(file);
 38             // 创建工作薄
 39             HSSFWorkbook workbook = new HSSFWorkbook();
 40             HSSFSheet sheet = workbook.createSheet("sheet0");
 41             // 获取Xml根节点
 42             Element root = parse.getRootElement();
 43             // 获取模板名称
 44             String templateName = root.getAttribute("name").getValue();
 45             int rownum = 0;
 46             int column = 0;
 47             // 设置列宽
 48             Element colgroup = root.getChild("colgroup");
 49             setColumnWidth(sheet, colgroup);
 50             // 设置标题
 51             Element title = root.getChild("title");
 52             List<Element> trs = title.getChildren("tr");
 53             for (int i = 0; i < trs.size(); i++) {
 54                 Element tr = trs.get(i);
 55                 List<Element> tds = tr.getChildren("td");
 56                 HSSFRow row = sheet.createRow(rownum);
 57                 // 设置单元格样式
 58                 HSSFCellStyle cellStyle = workbook.createCellStyle();
 59                 // 设置居中
 60                 cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
 61                 for (column = 0; column < tds.size(); column++) {
 62                     Element td = tds.get(column);
 63                     HSSFCell cell = row.createCell(column);
 64                     Attribute rowSpan = td.getAttribute("rowspan");
 65                     Attribute colSpan = td.getAttribute("colspan");
 66                     Attribute value = td.getAttribute("value");
 67                     if (value != null) {
 68                         String val = value.getValue();
 69                         cell.setCellValue(val);
 70                         int rspan = rowSpan.getIntValue() - 1;
 71                         int cspan = colSpan.getIntValue() - 1;
 72                         // 设置字体
 73                         HSSFFont font = workbook.createFont();
 74                         font.setFontName("仿宋_GB2312");
 75                         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 字体加粗
 76                         // font.setFontHeight((short) 12);
 77                         font.setFontHeightInPoints((short) 12);
 78                         cellStyle.setFont(font);
 79                         cell.setCellStyle(cellStyle);
 80                         // 合并单元格
 81                         sheet.addMergedRegion(new CellRangeAddress(rspan, rspan, 0, cspan));
 82                     }
 83                 }
 84                 rownum++;
 85             }
 86             // 设置表头
 87             Element thead = root.getChild("thead");
 88             trs = thead.getChildren("tr");
 89             for (int i = 0; i < trs.size(); i++) {
 90                 Element tr = trs.get(i);
 91                 HSSFRow row = sheet.createRow(rownum);
 92                 List<Element> ths = tr.getChildren("th");
 93                 for (column = 0; column < ths.size(); column++) {
 94                     Element th = ths.get(column);
 95                     Attribute valueAttr = th.getAttribute("value");
 96                     HSSFCell cell = row.createCell(column);
 97                     if (valueAttr != null) {
 98                         String value = valueAttr.getValue();
 99                         cell.setCellValue(value);
100 
101                     }
102                 }
103                 rownum++;
104             }
105             // 设置数据区域样式
106             Element tbody = root.getChild("tbody");
107             Element tr = tbody.getChild("tr");
108             int repeat = tr.getAttribute("repeat").getIntValue();
109             List<Element> tds = tr.getChildren("td");
110             for (int i = 0; i < repeat; i++) {
111                 HSSFRow row = sheet.createRow(rownum);
112                 for (column = 0; column < tds.size(); column++) {
113                     Element td = tds.get(column);
114                     HSSFCell cell = row.createCell(column);
115                     // 设置单元格样式
116                     setType(workbook, cell, td);
117                 }
118                 rownum++;
119             }
120             // 生成Excel导入模板
121             File tempFile = new File("e:/" + templateName + ".xls");
122             tempFile.delete();
123             tempFile.createNewFile();
124             FileOutputStream stream = FileUtils.openOutputStream(tempFile);
125             workbook.write(stream);
126             stream.close();
127 
128         } catch (Exception e) {
129             e.printStackTrace();
130         }
131 
132     }
133 
134     /**
135      * 设置列宽
136      * 
137      * @param sheet
138      * @param colgroup
139      */
140     private static void setColumnWidth(HSSFSheet sheet, Element colgroup) {
141         List<Element> cols = colgroup.getChildren("col");
142         for (int i = 0; i < cols.size(); i++) {
143             Element col = cols.get(i);
144             Attribute width = col.getAttribute("width");
145             String unit = width.getValue().replaceAll("[0-9,\.]", "");
146             String value = width.getValue().replaceAll(unit, "");
147             int v = 0;
148             if (StringUtils.isBlank(unit) || "px".endsWith(unit)) {
149                 v = Math.round(Float.parseFloat(value) * 37F);
150             } else if ("em".endsWith(unit)) {
151                 v = Math.round(Float.parseFloat(value) * 267.5F);
152             }
153             sheet.setColumnWidth(i, v);
154         }
155     }
156 
157     /**
158      * 设置单元格样式
159      * 
160      * @param workbook
161      * @param cell
162      * @param td
163      */
164     private static void setType(HSSFWorkbook workbook, HSSFCell cell, Element td) {
165         // TODO Auto-generated method stub
166         Attribute typeAttr = td.getAttribute("type");
167         String type = typeAttr.getValue();
168         HSSFDataFormat format = workbook.createDataFormat();
169         HSSFCellStyle cellStyle = workbook.createCellStyle();
170         if ("NUMERIC".equalsIgnoreCase(type)) {
171             cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
172             Attribute formatAttr = td.getAttribute("format");
173             String formatValue = formatAttr.getValue();
174             formatValue = StringUtils.isNotBlank(formatValue) ? formatValue : "#,##0.00";
175             cellStyle.setDataFormat(format.getFormat(formatValue));
176         } else if ("STRING".equalsIgnoreCase(type)) {
177             cell.setCellValue("");
178             cell.setCellType(HSSFCell.CELL_TYPE_STRING);
179             cellStyle.setDataFormat(format.getFormat("@"));
180         } else if ("DATE".equalsIgnoreCase(type)) {
181             cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
182             cellStyle.setDataFormat(format.getFormat("yyyy-m-d"));
183         } else if ("ENUM".equalsIgnoreCase(type)) {
184             CellRangeAddressList regions = new CellRangeAddressList(cell.getRowIndex(), cell.getRowIndex(),
185                     cell.getColumnIndex(), cell.getColumnIndex());
186             Attribute enumAttr = td.getAttribute("format");
187             String enumValue = enumAttr.getValue();
188             // 加载下拉列表内容
189             DVConstraint constraint = DVConstraint.createExplicitListConstraint(enumValue.split(","));
190             // 数据有效性对象
191             HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
192             workbook.getSheetAt(0).addValidationData(dataValidation);
193         }
194         cell.setCellStyle(cellStyle);
195     }
196 
197 }

二、Java解析XML模板，实现效果