POI使用Excel模版文件导出

最新推荐文章于 2024-05-09 20:43:47 发布

weixin_34252686

最新推荐文章于 2024-05-09 20:43:47 发布

阅读量49

点赞数

文章标签： python

原文链接：https://my.oschina.net/u/2354306/blog/619276

版权

2019独角兽企业重金招聘Python工程师标准>>>

首先从客户需求入手，制定标准的excel表头样式，如下图

然后就是获取数据，引用文件生成临时workbook

List<ExportDetailVO> details = this.getPostsService().getExportList();
InputStream fis = new FileInputStream(new File(Constants.LOGO_PATH + File.separator + "ExportTamplate.xlsx"));
XSSFWorkbook workbook = new XSSFWorkbook(fis);
Sheet sheet = workbook.getSheetAt(0);

设置表中数据部分的样式以及超链接的样式

CellStyle bodyStyle = workbook.createCellStyle();
bodyStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
bodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
bodyStyle.setBorderBottom(HSSFCellStyle. BORDER_THIN);
bodyStyle.setBottomBorderColor(new HSSFColor.BLACK().getIndex());
bodyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
bodyStyle.setRightBorderColor(new HSSFColor.BLACK().getIndex());
Font bodyFont = workbook.createFont();
bodyFont.setFontHeightInPoints((short)10);
bodyFont.setFontName("微软雅黑");
bodyFont.setBoldweight(Font.BOLDWEIGHT_NORMAL); 
bodyFont.setColor(HSSFColor.BLACK.index);    
bodyStyle.setFont(bodyFont);
bodyStyle.setWrapText(true);
CellStyle linkStyle = workbook.createCellStyle();
linkStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
linkStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
linkStyle.setBorderBottom(HSSFCellStyle. BORDER_THIN);
linkStyle.setBottomBorderColor(new HSSFColor.BLACK().getIndex());
linkStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
linkStyle.setRightBorderColor(new HSSFColor.BLACK().getIndex());
XSSFFont cellFont= workbook.createFont();
cellFont.setColor(HSSFColor.BLUE.index);
linkStyle.setFont(cellFont);

在接下来就是循环插入数据，并生成excel文件了

for(int i=0; i<details.size(); i++){
ExportDetailVO vo = details.get(i);
Row tempRow = sheet.createRow(i+6);
for(int j=0;j<23;j++){
	Cell tempCell = tempRow.createCell(j);
	tempCell.setCellStyle(bodyStyle);
	switch(j){
		case 0:
			tempCell.setCellValue(i+1);
			break;
		case 1:
			tempCell.setCellValue(vo.getTitle());
			break;
		case 2:
			tempCell.setCellStyle(linkStyle);
			tempCell.setCellFormula("HYPERLINK(\"" + vo.getPostUrl() + "\",\"" + "【原】"+ "\")");
			break;
		case 3:
			tempCell.setCellValue(vo.getForumName());
			break;
		case 4:
			tempCell.setCellValue(vo.getEffect());
			break;
		case 5:
			tempCell.setCellValue(vo.getHits());
			break;
		case 6:
			tempCell.setCellValue(vo.getReply());
			break;
		case 7:
			tempCell.setCellValue(vo.getPuName());
			break;
		case 8:
			tempCell.setCellValue(vo.getForumNick());
			break;
		case 9:
			if(vo.getGender()){
			    tempCell.setCellValue("男");
			}else{
		            tempCell.setCellValue("女");
			}
			break;
		case 10:
			tempCell.setCellValue(vo.getPhone());
			break;
		case 11:
			tempCell.setCellValue(vo.getQqId());
			break;
		case 12:
			tempCell.setCellValue(vo.getWeChatId());
			break;
		case 13:
			tempCell.setCellValue(vo.getPuAge());
			break;
		case 14:
			tempCell.setCellValue(vo.getIndustry());
			break;
		case 15:
			tempCell.setCellValue(vo.getCareer());
			break;
		case 16:
			tempCell.setCellValue(vo.getVehicleModel());
			break;
		case 17:
			tempCell.setCellValue(vo.getLiciencePlate());
			break;
		case 18:
			tempCell.setCellValue(vo.getFrameNumber());
			break;
		case 19:
			tempCell.setCellValue(vo.getPicupLocation());
			break;
		case 20:
			tempCell.setCellValue(vo.getClub());
			break;
		case 21:
			tempCell.setCellValue(vo.getClubRole());
			break;
		case 22:
			tempCell.setCellValue(vo.getAddress());
			break;
		default:
			break;
	        }
        }
}
String fileName = "吉利博瑞车主口碑邀约名单.xls";
response.setHeader("content-disposition", "attachment; filename="  + new String(fileName.getBytes("gb2312"), "ISO8859-1" ));
response.setContentType("application/octet-stream");
OutputStream os = response.getOutputStream();
workbook.write(os);

转载于:https://my.oschina.net/u/2354306/blog/619276