xml文件
<tr height="16px"> <td rowspan="1" colspan="6" value="学生信息导入" /> </tr>
execel的行和列以0开头
设置单元格居中
HSSFCellStyle cellStyle = wb.createCellStyle();//创建单元格样式
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置单元格对齐方式设置单元格字体
HSSFFont font = wb.createFont();font.setFontName("仿宋_GB2312");font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体加粗//font.setFontHeight((short)12);font.setFontHeightInPoints((short)12);cellStyle.setFont(font);cell.setCellStyle(cellStyle); //合并单元格居中
sheet.addMergedRegion(new CellRangeAddress(rspan, rspan, 0, cspan));
设置单元格数据类型
/** * 测试单元格样式 * @author David * @param wb * @param cell * @param td */ private static void setType(HSSFWorkbook wb, HSSFCell cell, Element td) { Attribute typeAttr = td.getAttribute("type"); String type = typeAttr.getValue(); HSSFDataFormat format = wb.createDataFormat(); HSSFCellStyle cellStyle = wb.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"; 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); wb.getSheetAt(0).addValidationData(dataValidation); } cell.setCellStyle(cellStyle); } 设置下拉列表类型
封装设置数据有效性方法
/** * 方法名称：SetDataValidation * 内容摘要：设置数据有效性 * @param sheet excel sheet內容 * @param textList 下拉列表 * @param firstRow 單元格範圍 * @param firstCol * @param endRow * @param endCol */ private static HSSFDataValidation setDataValidation(HSSFSheet sheet,String[] textList,short firstRow,short firstCol, short endRow, short endCol) { //加载下拉列表内容 DVConstraint constraint = DVConstraint.createExplicitListConstraint(textList); //设置数据有效性加载在哪个单元格上。 //四个参数分别是：起始行、终止行、起始列、终止列 CellRangeAddressList regions = new CellRangeAddressList(firstRow,endRow, firstCol, endCol); //数据有效性对象 HSSFDataValidation data_validation = new HSSFDataValidation(regions, constraint); sheet.addValidationData(data_validation); return data_validation; } 设置列宽方法封装
/** * 设置列宽 * @author David * @param sheet * @param colgroup */ private static void setColumnWidth(HSSFSheet sheet, Element colgroup) { List 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)){//如果单位为空或等于px v = Math.round(Float.parseFloat(value) * 37F); }else if ("em".endsWith(unit)){//如果单位为em v = Math.round(Float.parseFloat(value) * 267.5F); } sheet.setColumnWidth(i, v);//设置第i列宽度为v } } 完整代码
package com.imooc.excel;import org.apache.commons.io.FileUtils;import org.apache.commons.lang3.StringUtils;import org.apache.poi.hssf.usermodel.*;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 java.io.File;import java.io.FileOutputStream;import java.util.List;/** * Created by chenld1 on 2015/10/6. */public class CreateTemplate { /** * 创建模板文件 * @author David * @param args */ public static void main(String[] args) { //获取解析xml文件路径 String path = System.getProperty("user.dir") + "/student2.xml"; File file = new File(path); SAXBuilder builder = new SAXBuilder(); try { //解析xml文件 Document parse = builder.build(file); //创建Excel HSSFWorkbook wb = new HSSFWorkbook(); //创建sheet HSSFSheet sheet = wb.createSheet("Sheet0"); //获取xml文件跟节点 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 trs = title.getChildren("tr"); for (int i = 0; i < trs.size(); i++) { Element tr = trs.get(i); List tds = tr.getChildren("td"); HSSFRow row = sheet.createRow(rownum); HSSFCellStyle cellStyle = wb.createCellStyle();//创建单元格样式 cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置单元格对齐方式 for(column = 0;column 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 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("e:/" + templateName + ".xls"); tempFile.delete(); tempFile.createNewFile(); FileOutputStream stream = FileUtils.openOutputStream(tempFile); wb.write(stream); stream.close(); } catch (Exception e) { e.printStackTrace(); } } /** * 设置单元格数据类型 * @author David * @param wb * @param cell * @param td */ private static void setType(HSSFWorkbook wb, HSSFCell cell, Element td) { Attribute typeAttr = td.getAttribute("type"); String type = typeAttr.getValue(); //HSSFDataformat HSSFDataFormat format = wb.createDataFormat(); HSSFCellStyle cellStyle = wb.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"; 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); wb.getSheetAt(0).addValidationData(dataValidation); } cell.setCellStyle(cellStyle); } /** * 设置列宽 * @author David * @param sheet * @param colgroup */ private static void setColumnWidth(HSSFSheet sheet, Element colgroup) { List 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)){//如果单位为空或等于px v = Math.round(Float.parseFloat(value) * 37F); }else if ("em".endsWith(unit)){//如果单位为em v = Math.round(Float.parseFloat(value) * 267.5F); } sheet.setColumnWidth(i, v);//设置第i列宽度为v } } /** * 方法名称：SetDataValidation * 内容摘要：设置数据有效性 * @param sheet excel sheet內容 * @param textList 下拉列表 * @param firstRow 單元格範圍 * @param firstCol * @param endRow * @param endCol */ private static HSSFDataValidation setDataValidation(HSSFSheet sheet,String[] textList,short firstRow,short firstCol, short endRow, short endCol) { //加载下拉列表内容 DVConstraint constraint = DVConstraint.createExplicitListConstraint(textList); //设置数据有效性加载在哪个单元格上。 //四个参数分别是：起始行、终止行、起始列、终止列 CellRangeAddressList regions = new CellRangeAddressList(firstRow,endRow, firstCol, endCol); //数据有效性对象 HSSFDataValidation data_validation = new HSSFDataValidation(regions, constraint); sheet.addValidationData(data_validation); return data_validation; }}