HSSFWorkbook workbook = new HSSFWorkbook();//创建EXCEL文件
HSSFSheet sheet= workbook.createSheet("XXX"); //创建工作表
//设置单元格宽度
sheet.setDefaultColumnWidth(20);
sheet.setColumnWidth(0, "班级名称".getBytes().length*2*256); //自适应宽度
//设置单元格样式
CellStyle boderStyle = workbook.createCellStyle(); // 垂直居中
boderStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
boderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置单元格字体
HSSFFont font = workbook.createFont();
font.setFontName("等线");
font.setFontHeightInPoints((short) 12); //字体大小
boderStyle.setFont(font);
//在sheet里创建第一行
HSSFRow row=sheet.createRow(0);
//创建单元格并设置单元格内容
row.createCell(0).setCellValue("班级名称");
row.createCell(1).setCellValue("姓名");
row.createCell(2).setCellValue("成绩");
row.createCell(3).setCellValue("数量");
//获取排序后的班级
List statisList = getProcuratorateName(beginDate, endDate);
int count = 1;//计入班级的行
for (StatisticsExcelVo stu : statisList) {
//数量小于零的不计入
if(!MatchUtil.isEmpty(stu) && stu.getCaseTotal() > 0){
//班级名称
HSSFRow row2=sheet.createRow(count);
row2.createCell(0).setCellValue(stu.getCourtName());
List userLoginNameList = new ArrayList<>();
List statisUserList = new ArrayList<>();
//合并班级名称单元格
if(statisUserList.size() > 1){
sheet.addMergedRegion(new CellRangeAddress(count,count+statisUserList.size()-1,0,0));
}
int cellNum = 0; //计入用户的行
for (StatisticsExcelVo string : statisUserList) {
//赋值用户姓名等其他值,第一次时应该与当前的班级名称占用一行
if(cellNum == 0){
row2.createCell(1).setCellValue(string.getUserName());
row2.createCell(2).setCellValue(string.getCaseTotal());
row2.createCell(3).setCellValue(string.getJudgeTotal());
}else{
HSSFRow row3=sheet.createRow(count+cellNum);
row3.createCell(1).setCellValue(string.getUserName());
row3.createCell(2).setCellValue(string.getCaseTotal());
row3.createCell(3).setCellValue(string.getJudgeTotal());
}
cellNum ++;
}
count = count+statisUserList.size();
}
}
//设置单元格样式
for (int i = 0; i <= count; i++) {
HSSFRow row2 = sheet.getRow(i);
if (row2 != null) {
for (int j = 0; j <= 3; j++) {
HSSFCell cell_temp = row2.getCell(j);
if (cell_temp != null) {
cell_temp.setCellStyle(boderStyle);
}
}
}
}
//将文件输出
OutputStream ouputStream = response.getOutputStream();
response.reset();
String fileName = "xxx.xlsx";
response.setHeader("Content-disposition", "attachment; filename="+new String(fileName.getBytes("utf-8"), "iso8859-1"));
response.setContentType("application/msexcel");
workbook.write(ouputStream);
ouputStream.flush();
ouputStream.close();
最后说一下POI设置单元格样式的方式,我没有找到设置全局样式的方法,只能算出总行总列然后循环设置了。
借用的是论坛一大神的思路,连接: https://bbs.csdn.net/topics/391890095?page=1