本文主要叙述定制导入模板——利用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模板，实现效果
