springboot + poi导出EXCEL表格 (基于维度信息动态生成excel表格)(响应浏览器和本地测试都可借鉴)
1,引入响应的pom
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>
2,引入响应的工具类
public class ExportExcelUtil {
/**
*
* @param title 标题
* @param headers 表头
* @param values 表中元素
* @return
*/
public static XSSFWorkbook getHSSFWorkbook(String title, String headers[], String [][] values){
//注意此处用XSSFWorkbook 是为了生成 .xlsx格式文件
//HssfWorkbook 生成.xls 格式文件(按自己要求来)
//创建一个HSSFWorkbook,对应一个Excel文件
XSSFWorkbook hssfWorkbook = new XSSFWorkbook();
//在workbook中添加一个sheet,对应Excel文件中的sheet(就是标题头)
XSSFSheet hssfSheet = hssfWorkbook.createSheet();
//设置列宽
hssfSheet.setDefaultColumnWidth((short) 15);
//创建标题合并行(我的需求不需要标题头,全部剔除)
//hssfSheet.addMergedRegion(new CellRangeAddress(0,(short)0,0,(short)headers.length - 1));
//hssfSheet.autoSizeColumn(1, true);
//设置标题样式
//XSSFCellStyle style = hssfWorkbook.createCellStyle();
//style.setAlignment(HorizontalAlignment.CENTER); //设置居中样式
// style.setVerticalAlignment(VerticalAlignment.CENTER);
//设置标题字体
//Font titleFont = hssfWorkbook.createFont();
//titleFont.setFontHeightInPoints((short) 14);
// style.setFont(titleFont);
//设置值表头样式 设置表头居中
XSSFCellStyle hssfCellStyle = hssfWorkbook.createCellStyle();
hssfCellStyle.setAlignment(HorizontalAlignment.CENTER); //设置居中样式
hssfCellStyle.setBorderBottom(BorderStyle.THIN);
hssfCellStyle.setBorderLeft(BorderStyle.THIN);
hssfCellStyle.setBorderRight(BorderStyle.THIN);
hssfCellStyle.setBorderTop(BorderStyle.THIN);
XSSFFont font2 = hssfWorkbook.createFont();
font2.setFontName("仿宋_GB2312");
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
font2.setFontHeightInPoints((short) 12);
hssfCellStyle.setFont(font2);
//设置表内容样式
//创建单元格,并设置值表头 设置表头居中
XSSFCellStyle style1 = hssfWorkbook.createCellStyle();
//数据信息的边框
style1.setBorderBottom(BorderStyle.THIN);
style1.setBorderLeft(BorderStyle.THIN);
style1.setBorderRight(BorderStyle.THIN);
style1.setBorderTop(BorderStyle.THIN);
//产生标题行
//XSSFRow hssfRow = hssfSheet.createRow(0);
//XSSFCell cell = hssfRow.createCell(0);
//cell.setCellValue(title);
//cell.setCellStyle(style);
//产生表头
XSSFRow row1 = hssfSheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
XSSFCell hssfCell = row1.createCell(i);
hssfCell.setCellValue(headers[i]);
hssfCell.setCellStyle(hssfCellStyle);
//hssfCell.setCellStyle(style2);
}
//创建内容
for (int i = 0; i <values.length; i++){
row1 = hssfSheet.createRow(i +1);
for (int j = 0; j < values[i].length; j++){
//将内容按顺序赋给对应列对象
XSSFCell hssfCell = row1.createCell(j);
hssfCell.setCellValue(values[i][j]);
hssfCell.setCellStyle(style1);
}
}
return hssfWorkbook;
}
}
3,控制器层(我这需求是前面的数据固定,后面的列数据是动态改变的)
//idStr是前台勾选列数据的id,用英文逗号隔开(下面有样例)
PostMapping("/export")
public void export(HttpServletRequest request,HttpServletResponse response,String idStr){
String dataSource = dataSourceUtils.getDataSource(request);
//String idStr="10913,10922,10974,11007,11012,11023,11099,11060,11134,11009";
//excel标题
String title = "报告下载列表";
//excel表名 固定标题
String [] headers = {"报告名称", "报告生成时间", "规则名称","任务类型","任务名称","任务数据开始时间","任务数据结束时间","任务创建时间","任务创建人","录音总量","命中量","命中率"};
//此处将数组转换成集合是为了方便操作,上面是人懒,这样写的。
List<String> resultList= new ArrayList<>(Arrays.asList(headers));
SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMddHHmmss");
String exportTime=sdf.format(new Date());
//excel文件名
String fileName = "报告明细下载_" + exportTime+".xlsx";
//根据id获取相应到数据(此处具体省略,数据库操作)
List<ReportInfoVO> list = service.listReportInfoByIds(idStr,dataSource);
//创建一个获取每条维度数据长度的 集合
List<Integer> lenList = new ArrayList<>();
//维度数据的集合
List<List<Map>> jsonList = new ArrayList<>();
for (ReportInfoVO reportInfoVO : list) {//获取所有条件风险率集合的长度
if(reportInfoVO.getDetailJson()!=null && !reportInfoVO.getDetailJson().equals("")){
List<Map> detailList = JSONArray.parseArray(reportInfoVO.getDetailJson(),Map.class);//将维度JSON信息转换为map类型
//将对应的的数据放到集合里面
lenList.add(detailList.size());
jsonList.add(detailList);
}else {
//此处是为了在维度出现空的时候占个位置
jsonList.add(null);
}
}
List<Map> maxLenDetailList = new ArrayList<>();
for (int i = 0; i < list.size(); i++) {//获取当前最大长度的条件风险集合信息
if(list.get(i).getDetailJson()!=null && !list.get(i).getDetailJson().equals("")){
//取当前集合里面最大的长度
Integer max = Collections.max(lenList);
//判断并获取最大长度的对应的数据
if(JSONArray.parseArray(list.get(i).getDetailJson(),Map.class).size()==max){
maxLenDetailList = JSONArray.parseArray(list.get(i).getDetailJson(),Map.class);
}
}else {
//没有就保持上面固定的标题
maxLenDetailList.add(null);
}
}
//将最长得字段信息打印出来
if(maxLenDetailList.size()>0){
for (Map map : maxLenDetailList) {
if(map!=null){
String conditionName = (String) map.get("conditionName");
//此处可自定义(赛塞到集合里面)
String titleName1 = "条件"+conditionName+"命中量";
String titleName2 = "条件"+conditionName+"风险率";
resultList.add(titleName1);
resultList.add(titleName2);
}
}
}
//excel元素
String content[][] = new String[list.size()][resultList.size()];
for (int i = 0; i < list.size(); i++) {
content[i] = new String[resultList.size()];
content[i][0] = list.get(i).getReportName()==null?"":list.get(i).getReportName();//报告名称
content[i][1] = list.get(i).getReportCreateTime()==null?"":list.get(i).getReportCreateTime();//报告生成时间
content[i][2] = list.get(i).getRuleName()==null?"":list.get(i).getRuleName();//规则名称
BigDecimal bigDecimal = list.get(i).getTaskType();
String taskType;
if(bigDecimal.toString().equals("0")){
taskType="常规质检";
}else if(bigDecimal.toString().equals("1")){
taskType="专项质检";
}else if(bigDecimal.toString().equals("2")){
taskType="常规关联";
}else if(bigDecimal.toString().equals("3")){
taskType="专项关联";
}else {
taskType="无";
}
content[i][3] = taskType;//任务类型
content[i][4] = list.get(i).getModelName()==null?"":list.get(i).getModelName();//任务名称
content[i][5] = list.get(i).getFirstCallTime()==null?"":list.get(i).getFirstCallTime();//任务开始时间
content[i][6] = list.get(i).getLastCallTime()==null?"":list.get(i).getLastCallTime();//任务结束时间
content[i][7] = list.get(i).getTaskCreateTime()==null?"":list.get(i).getTaskCreateTime();//任务创建时间
content[i][8] = list.get(i).getTaskCreator()==null?"":list.get(i).getTaskCreator();//任务创建人
BigDecimal totalNum = list.get(i).getTotalNum()==null?new BigDecimal(0):list.get(i).getTotalNum();
BigDecimal hitNum = list.get(i).getHitNum()==null?new BigDecimal(0):list.get(i).getHitNum();
content[i][9] = totalNum.toString();//录音总量
content[i][10] = hitNum.toString();//命中量
BigDecimal bfs = new BigDecimal(0);//百分比数
BigDecimal ys = new BigDecimal(0);//余数
if(totalNum.compareTo(BigDecimal.ZERO)>0){//判断总量是否大于0
if (hitNum.compareTo(BigDecimal.ZERO)>0){
ys= new BigDecimal(totalNum.intValue()).remainder(hitNum);//余数
}
if ( ys.compareTo(BigDecimal.ZERO)==0){//整除不保留小数
bfs = new BigDecimal(hitNum.intValue()*100).divide(totalNum, 0, BigDecimal.ROUND_HALF_UP);
}else {//没有整除保留2位小数
bfs = new BigDecimal(hitNum.intValue()*100).divide(totalNum, 2, BigDecimal.ROUND_HALF_UP);
}
}
content[i][11] = bfs+"%";//命中率
if(jsonList.size()>0){
int num=11;
List<Map> maps = jsonList.get(i);
if(maps!=null){
for (int i1 = 0; i1 < maps.size(); i1++) {
BigDecimal hitNumber = new BigDecimal((maps.get(i1).get("hitNumber")==null?0:maps.get(i1).get("hitNumber")).toString());
BigDecimal voiceTotalCount = new BigDecimal((maps.get(i1).get("voiceTotalCount")==null?0:maps.get(i1).get("voiceTotalCount")).toString());
BigDecimal hitRate = new BigDecimal(0);
BigDecimal decimal = new BigDecimal(0);
if (voiceTotalCount.compareTo(BigDecimal.ZERO)>0){//总数要大于0
if (hitNumber.compareTo(BigDecimal.ZERO)>0){
decimal= new BigDecimal(voiceTotalCount.intValue()).remainder(hitNumber);//余数
}
if ( decimal.compareTo(BigDecimal.ZERO)==0){//整除不保留小数
hitRate = new BigDecimal(hitNumber.intValue()*100).divide(voiceTotalCount, 0, BigDecimal.ROUND_HALF_UP);
}else {//没有整除保留2位小数
hitRate = new BigDecimal(hitNumber.intValue()*100).divide(voiceTotalCount, 2, BigDecimal.ROUND_HALF_UP);
}
}
content[i][++num]=hitNumber.toString();
content[i][++num]=hitRate+"%";
}
}
}
}
//再将上面的集合转换成数组
String[] array2 = resultList.toArray(new String[resultList.size()]);
//创建HSSFWorkbook
XSSFWorkbook wb = ExportExcelUtil.getHSSFWorkbook(title, array2, content);
//本地测试用
/*try {
//输出Excel文件1
FileOutputStream output=new FileOutputStream("D:\\mnt\\guangfa\\task\\batch"+fileName);
wb.write(output);//写入磁盘
output.close();
} catch (IOException e) {
e.printStackTrace();
}*/
//响应到客户端
try {
this.setResponseHeader(response, fileName);
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//发送响应流方法
public void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(),"ISO8859-1");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
response.setContentType("application/octet-stream;charset=ISO8859-1");
response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}
4,最终效果
这是最终的效果。