jxl 生成excel文件采用模板动态插入行

最新推荐文章于 2023-10-26 17:42:01 发布

原创最新推荐文章于 2023-10-26 17:42:01 发布 · 2.1k 阅读

0 ·

CC 4.0 BY-SA版权

Java相关专栏收录该内容

11 篇文章

订阅专栏

本文介绍了一种利用Excel模板和特定数据对象自动生成Excel文件的方法。该方法通过替换模板中的占位符并调整单元格格式来创建新的Excel文档。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/*
*  tempPath: excel模板文件路径（包含文件名）
*  dataForm: 数据对象
*  saveFilePath: 生成完excel文件后保存的路径（包含文件名）
*/
private static void createExcelByTemp(String tempPath, DataForm dataForm, String saveFilePath){
	WritableWorkbook wwb = null;
	FileOutputStream out = null;
	try {
		int rows = 8, cols = 4;
		out = new FileOutputStream(saveFilePath);
		Workbook wb = Workbook.getWorkbook(new File(tempPath));
		wwb = Workbook.createWorkbook(out, wb);
		WritableSheet sheet = wwb.getSheet(0);
		for (int i = 1; i < rows; i++){
			for (int j = 0; j < cols; j++){
				Cell cell = sheet.getCell(j,i); //得到每个单元格对象
				String cen = cell.getContents(); //得到单元格中的内容
				cen = cen.replace("${yearCode}", dataForm.getYearCode())  //替换内容
						.replace("${year}", String.valueOf(dataForm.getYear()))
						.replace("${yearNumber}", String.valueOf(dataForm.getYearNumber()))
						//.replace("${visitor_tr}", trs)
						.replace("${field}", dataForm.getField())
						.replace("${printTo}", dataForm.getPrintTo())
						.replace("${digest}", dataForm.getDigest())
						.replace("${memo}", dataForm.getMemo())
						.replace("${cosignTo}", dataForm.getCosignTo());

				Label lbl = new Label(j, i, cen);//将第一个单元格的值改为“修改后的值”
				CellFormat cf = cell.getCellFormat();//获取第一个单元格的格式
				lbl.setCellFormat(cf);//将修改后的单元格的格式设定成跟原来一样
				sheet.addCell(lbl);/* 通过设置自动换行来解决内容中的换行符“\n”不生效的方式，*/
/* CellFormat cf = cell.getCellFormat();
WritableCellFormat wcf = new WritableCellFormat(cf);					wcf.setBorder(Border.RIGHT, BorderLineStyle.THIN);
								wcf.setWrap(true);
								lbl.setCellFormat(wcf);*/

}}String string1 = dataForm.getString1();String string3 = dataForm.getString3();String[] visitors = string1.split("@@@");String[] companyDutys = string3.split("@@@");String trs = "";Cell cell = null;int height = 25;CellFormat[] cf = new CellFormat[4];for (int i = 0; i < visitors.length; i++) { //动态插入行String visitor = visitors[i];String companyDuty = companyDutys[i];int r = i +3, c = 0;if (i == 0) { //给动态行设置一个样式行，便于给以后动态插入的行添加相同的样式。//sheet.insertRow(r);height = sheet.getRowHeight(r); //取得行高cell = sheet.getCell(c, r);cf[c] = cell.getCellFormat();Label lbl = new Label(c, r, visitor);lbl.setCellFormat(cf[c++]);sheet.addCell(lbl);cell = sheet.getCell(c, r);cf[c] = cell.getCellFormat();lbl = new Label(c, r, "团长");lbl.setCellFormat(cf[c++]);sheet.addCell(lbl);cell = sheet.getCell(c, r);cf[c] = cell.getCellFormat();lbl = new Label(c, r, companyDuty);lbl.setCellFormat(cf[c++]);sheet.addCell(lbl);cell = sheet.getCell(c, r);cf[c] = cell.getCellFormat();} else {sheet.insertRow(r);sheet.setRowView(r, height);sheet.mergeCells(2, r, 3, r); //合并单元格//Cell cell = sheet.getCell(c, r);Label lbl = new Label(c, r, visitor);lbl.setCellFormat(cf[c++]);sheet.addCell(lbl);lbl = new Label(c, r, "团员");lbl.setCellFormat(cf[c++]);sheet.addCell(lbl);lbl = new Label(c, r, companyDuty);lbl.setCellFormat(cf[c++]);sheet.addCell(lbl);lbl = new Label(c, r, "");lbl.setCellFormat(cf[c++]);sheet.addCell(lbl);}}//WritableCell cell =ws.getWritableCell(0, 0);//获取第一个单元格wwb.write();out.flush();} catch (Exception e) {e.printStackTrace();} finally {try {wwb.close();} catch (IOException e) {}try {out.close();} catch (IOException e) {}}}

对应模板如下图：