ι 版权声明：本文为博主原创文章，未经博主允许不得转载。
效果图：
实现
1.在pom中添加依赖：
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
2.ExcelUtil工具类：
package com.feicuiedu.survey.util; import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;
import java.util.Map; /**
* Created by Bella on 2018/2/5.
*/
public class ExcelUtil {
/**
* 创建excel文档，
* list 数据
* @param keys list中map的key数组集合
* @param columnNames excel的列名
* */
public static HSSFWorkbook createWorkBook(List<Map<String, Object>> list, String []keys, String columnNames[]) {
// 创建excel工作簿
HSSFWorkbook wb = new HSSFWorkbook();
// 创建第一个sheet页，并命名
HSSFSheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
// 设置列宽
for(int i=0;i<keys.length;i++){
//最后一列为附件URL地址,列宽设置大一些
if(i==(keys.length-1)){
sheet.setColumnWidth((short) i, (short) (200*120));
}else{
sheet.setColumnWidth((short) i, (short) (50*60));
}
} // 创建第一行，并设置其单元格格式
HSSFRow row = sheet.createRow((short) 0);
row.setHeight((short)500);
// 单元格格式(用于列名)
HSSFCellStyle cs = wb.createCellStyle();
HSSFFont f = wb.createFont();
f.setFontName("宋体");
f.setFontHeightInPoints((short) 10);
f.setBold(true);
cs.setFont(f);
cs.setAlignment(HorizontalAlignment.CENTER);// 水平居中
cs.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
cs.setLocked(true);
cs.setWrapText(true);//自动换行
//设置列名
for(int i=0;i<columnNames.length;i++){
HSSFCell cell = row.createCell(i);
cell.setCellValue(columnNames[i]);
cell.setCellStyle(cs);
} //设置首行外,每行每列的值(Row和Cell都从0开始)
for (short i = 1; i < list.size(); i++) {
HSSFRow row1 = sheet.createRow((short) i);
String flag = "";
//在Row行创建单元格
for(short j=0;j<keys.length;j++){
HSSFCell cell = row1.createCell(j);
cell.setCellValue(list.get(i).get(keys[j]) == null?" ": list.get(i).get(keys[j]).toString());
if(list.get(i).get(keys[j])!=null){
if("优".equals(list.get(i).get(keys[j]).toString())){
flag = "优";
}else if("差".equals(list.get(i).get(keys[j]).toString())) {
flag = "差";
}
}
}
//设置该行样式
HSSFFont f2 = wb.createFont();
f2.setFontName("宋体");
f2.setFontHeightInPoints((short) 10);
if("优".equals(flag)){
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setFont(f2);
cellStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
cellStyle.setLocked(true);
cellStyle.setWrapText(true);//自动换行
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex());// 设置背景色
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//依次为每个单元格设置样式
for(int m=0;m<keys.length;m++){
HSSFCell hssfCell = row1.getCell(m);
hssfCell.setCellStyle(cellStyle);
}
}else if("差".equals(flag)){
HSSFCellStyle cellStyle2 = wb.createCellStyle();
cellStyle2.setFont(f2);
cellStyle2.setAlignment(HorizontalAlignment.CENTER);// 左右居中
cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
cellStyle2.setLocked(true);
cellStyle2.setWrapText(true);//自动换行
cellStyle2.setFillForegroundColor(HSSFColor.HSSFColorPredefined.RED.getIndex());// 设置背景色
cellStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
for(int m=0;m<keys.length;m++){
HSSFCell hssfCell = row1.getCell(m);
hssfCell.setCellStyle(cellStyle2);
}
}else{
HSSFCellStyle cs2 = wb.createCellStyle();
cs2.setFont(f2);
cs2.setAlignment(HorizontalAlignment.CENTER);// 左右居中
cs2.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
cs2.setLocked(true);
cs2.setWrapText(true);//自动换行
for(int m=0;m<keys.length;m++){
HSSFCell hssfCell = row1.getCell(m);
hssfCell.setCellStyle(cs2);
}
}
}
return wb;
} //生成并下载Excel
public static void downloadWorkBook(List<Map<String,Object>> list,
String keys[],
String columnNames[],
String fileName,
HttpServletResponse response) throws IOException{
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
ExcelUtil.createWorkBook(list,keys,columnNames).write(os);
} catch (IOException e) {
e.printStackTrace();
}
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename="+ new String((fileName + ".xls").getBytes(), "iso-8859-1"));
ServletOutputStream out = response.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (final IOException e) {
throw e;
} finally {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
}
}
}
3.service 接口：
String export(HttpServletResponse response,
Integer regionid,
Integer schoolid,
Integer majorid,
String beginDate,
String endDate);
4.service 接口实现类：
/**
* 导出Excel表
* @param regionid 大区id
* @param schoolid 校区id
* @param majorid 专业id
* @param beginDate 开始日期
* @param endDate 结束日期
* @return
*/
public String export(HttpServletResponse response,
Integer regionid,
Integer schoolid,
Integer majorid,
String beginDate,
String endDate){
try {
List<ProjectAuditListVo> projectAuditListVoList = projectAuditMapper.query(0,regionid,schoolid,majorid,null,beginDate,endDate,null,null);
String fileName="项目审核表";
List<Map<String,Object>> list=createExcelRecord(projectAuditListVoList);
String columnNames[] = {"大区","校区","专业","面授教师","在线教师","班级",
"项目所处阶段","作品人次","项目提交日期","项目得分",
"得分等级","原因","项目评价","附件"};//列名
String keys[] = {"regionName","schoolName","majorName","teacherName","onlineTeacherName",
"className","stage","workNum", "submitTime","score","rank","reason","evaluate","attachment",};//map中的key
ExcelUtil.downloadWorkBook(list,keys,columnNames,fileName,response);
} catch (IOException e) {
e.printStackTrace();
}
return "excel";
} /**
* 创建Excel表中的记录
* @param projectAuditListVoList
* @return
*/
private List<Map<String, Object>> createExcelRecord(List<ProjectAuditListVo> projectAuditListVoList){
List<Map<String, Object>> listmap = new ArrayList<Map<String, Object>>();
try {
Map<String, Object> map = new HashMap<String, Object>();
map.put("sheetName", "sheet1");
listmap.add(map);
for (int j = 0; j < projectAuditListVoList.size(); j++) {
ProjectAuditListVo projectAuditListVo=projectAuditListVoList.get(j);
Map<String, Object> mapValue = new HashMap<String, Object>();
mapValue.put("regionName",projectAuditListVo.getRegionName());
mapValue.put("schoolName",projectAuditListVo.getSchoolName());
mapValue.put("majorName",projectAuditListVo.getMajorName());
mapValue.put("teacherName",projectAuditListVo.getTeacherName());
mapValue.put("onlineTeacherName",projectAuditListVo.getOnlineTeacherName());
mapValue.put("className",projectAuditListVo.getClassName());
mapValue.put("stage",projectAuditListVo.getStage());
mapValue.put("workNum",projectAuditListVo.getWorkNum());
mapValue.put("submitTime", DateTimeUtil.dateToStr(projectAuditListVo.getSubmitTime(),"yyyy-MM-dd"));
mapValue.put("score",projectAuditListVo.getScore());
mapValue.put("rank",projectAuditListVo.getRank());
mapValue.put("reason",projectAuditListVo.getReason());
mapValue.put("evaluate",projectAuditListVo.getEvaluate());
String attachmentURL = projectAuditListVo.getAttachment()==null?"无":FileUtil.getUploadPath()+projectAuditListVo.getAttachment();
mapValue.put("attachment",attachmentURL);
listmap.add(mapValue);
}
} catch (Exception e) {
e.printStackTrace();
}
return listmap;
}
其中，
List<ProjectAuditListVo> projectAuditListVoList = projectAuditMapper.query(0,regionid,schoolid,majorid,null,beginDate,endDate,null,null);
为调用dao 层方法，获取 Excel中数据源。此处，dao层方法省略
5.controller层：
@RequestMapping(value = "/export",produces = {"application/vnd.ms-excel;charset=UTF-8"})
@ResponseBody
public String export(HttpServletResponse response,
@RequestParam(value = "regionid",required = false) Integer regionid,
@RequestParam(value = "schoolid",required = false) Integer schoolid,
@RequestParam(value = "majorid",required = false) Integer majorid,
@RequestParam(value = "beginDate",required = false) String beginDate,
@RequestParam(value = "endDate",required = false) String endDate){
return iProjectAuditService.export(response,regionid,schoolid,majorid,beginDate,endDate);
}
6.访问export接口，则会自动下载生成的Excel文件至本地。Excel文件效果图如上图所示。