Java根据模板创建excel文件

最新推荐文章于 2024-07-24 09:54:02 发布

programmeryu

最新推荐文章于 2024-07-24 09:54:02 发布

阅读量4k

点赞数

分类专栏： java 文章标签： java excel

java 专栏收录该内容

17 篇文章 1 订阅

订阅专栏

1.首先导入xml文件，src下建包xml,将student.xml文件放入此文件夹中


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

2.导入架包

1）commons-lang3-3.1.jar

2）jdom.jar

3）commons-io-2.2.jar

4）poi-3.11-20141221.jar

3.创建测试类

1）sax解析xml文件

//获取解析xml文件路径
		String path = System.getProperty("user.dir") + "/xml/student.xml";
		File file = new File(path);
		SAXBuilder builder = new SAXBuilder();
		//解析xml文件
		Document parse = builder.build(file);

2）创建excel

//创建Excel
		HSSFWorkbook wb = new HSSFWorkbook();
		//创建sheet
		HSSFSheet sheet = wb.createSheet("Sheet0");

3）从xml文件中取值

//获取xml文件跟节点
		Element root = parse.getRootElement();
		//获取模板名称
		String templateName = root.getAttribute("name").getValue();

4）设置excel列宽

int rownum = 0;
		int column = 0;
		//设置列宽
		Element colgroup = root.getChild("colgroup");
		setColumnWidth(sheet,colgroup);
		此处设置列宽，将其封装成一个方法
	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);
			//获取col的设置
			Attribute width = col.getAttribute("width");
			//正则表达式截取字符串，获取xml中列的单位
			String unit = width.getValue().replaceAll("[0-9,\\.]", "");
			//获取宽度值
			String value = width.getValue().replaceAll(unit, "");
			int v=0;
			//poi宽度转化为excel宽度
			if(StringUtils.isBlank(unit) || "px".endsWith(unit)){
				//空或者px单位的宽度转换为excel宽度
				v = Math.round(Float.parseFloat(value) * 37F);
				//em单位的宽度转换为excel宽度
			}else if ("em".endsWith(unit)){
				v = Math.round(Float.parseFloat(value) * 267.5F);
			}
			sheet.setColumnWidth(i, v);
		}
	}

5）设置标题

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);
				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;
						//合并单元格居中	(开始行,结束行,开始列,结束列)						
						sheet.addMergedRegion(new CellRangeAddress(rspan, rspan, 0, cspan));
					}
				}
				rownum ++;
			}

6）设置单元格样式

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);

7）设置表头

Element thead = root.getChild("thead");
			trs = thead.getChildren("tr");
			//循环得到节点信息
			for (int i = 0; i < trs.size(); i++) {
				Element tr = trs.get(i);
				//创建excel行
				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++;
			}

8）设置区域样式

//设置数据区域样式
			Element tbody = root.getChild("tbody");
			Element tr = tbody.getChild("tr");
			//repeat:初始化行数
			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++;
			}


			设置属性及样式封装的方法
	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);
	}

9）将文件保存到本地

File tempFile = new File("e:/" + templateName + ".xls");
			tempFile.delete();
			tempFile.createNewFile();
			FileOutputStream stream = FileUtils.openOutputStream(tempFile);
			wb.write(stream);
			stream.close();