【JAVA-POI】自定义动态表头EXCEL导出
需求:根据前端页面勾选的表头信息导出相应的字段信息列表
后端代码
相关实体类
public class EmpBasicExcelVo{
//名称
private String name;
//身份证号码
private String idCard;
//id (id1,id2,id3)多选导出
private String ids;
//.........其他查询参数
//表头列表
private List<CustomFieldVo> customFieldVoList;
}
/**
* 自定义表头实体类
*/
public class CustomFieldVo {
/**
* 字段
*/
private String field;
/*
* 字段名称
* */
private String fieldName;
}
导出方法
/**
*
* @param response
* @param customFieldVoList 自定义表头数据
* @param empMapInfo 导出结果数据
* @param fileName 文件名称
*/
public void exportCustomExcel(HttpServletResponse response,List<CustomFieldVo> customFieldVoList,List<Map<String, Object>> empMapInfo,String fileName){
Workbook workbook = new XSSFWorkbook();
//创建sheet
Sheet sheet = workbook.createSheet("人员信息");
Row headerRow = sheet.createRow(0);
//填充自定义表头名称
for (int colNum = 0;colNum < customFieldVoList.size();colNum++){
Cell cell = headerRow.createCell(colNum);
cell.setCellValue(customFieldVoList.get(colNum).getFieldName());
}
//填充数据
int rowNum=1;
for (Map<String, Object> stringObjectMap : empMapInfo) {
//创建表格行
Row row = sheet.createRow(rowNum++);
for (int colNum = 0;colNum < customFieldVoList.size();colNum++){
//创建单元格
Cell cell = row.createCell(colNum);
//根据自定义表头获取到字段名(数据库字段)
String field = customFieldVoList.get(colNum).getField();
if (isNotNull(stringObjectMap.get(field))){
//根据字段名(数据库字段)获取到数据并填充至单元格
cell.setCellValue(stringObjectMap.get(field).toString());
}else {
//如果查询的数据中没有字段则设置空值
cell.setCellValue("");
}
}
}
OutputStream outputStream = null;
try{
outputStream = response.getOutputStream();
response.reset();
fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
// 设定输出文件头
response.setHeader("Content-disposition", "attachment; filename=" + fileName);
// 定义输出类型
response.setContentType("application/msexcel");
workbook.write(outputStream);
}catch (Exception e){
throw new ServiceException("人员信息导出失败");
}finally {
try{
if (outputStream != null){
outputStream.flush();
outputStream.close();
}
}catch (Exception e){
}
}
}