投稿
  1. 首页
  2. 随笔

SpringBoot导出xlsx

南京大学cssci • 2022-12-16 • 随笔 • 阅读 13

SpringBoot导出xlsx,第1张

SpringBoot导出xlsx
SpringBoot导出xlsx

文章目录
  • SpringBoot导出xlsx
    • 1.需求
    • 2.架构选定
    • 2.Hutool 工具类
    • 3.POI 工具类
    • 4.前端响应方法

1.需求

​ 在Sprongboot项目中，导出数据为xlsx，然后放入HttpServletResponse中，响应给客户端；

2.架构选定

  1. Hutool

    优点：

    • hutool对poi *** 作进行大量的封装，可以简单调用工具类进行快速开发；

    缺点：

    • 不能进行细粒度开发，比如给每个单元格设置不同的的背景色，hutool没有提供相应的工具类；
    • API网址：https://www.hutool.cn/docs/#/poi/Excel%E7%94%9F%E6%88%90-ExcelWriter

  2. Apache POI

    优点：

    • 提供丰富的api *** 作，能细化到单元格进行 *** 作；

    缺点：

    • 增加开发时间成本；

    • API网址：https://poi.apache.org/components/spreadsheet/quick-guide.html

    • API使用博客推荐：https://blog.csdn.net/qq_42651904/article/details/88221392?utm_medium=distribute.pc_relevant.none-task-blog-2defaultbaidujs_baidulandingword~default-0.highlightwordscore&spm=1001.2101.3001.4242.1

    • 使用范例博客：https://www.cnblogs.com/jike1219/p/11182303.html

2.Hutool 工具类

1. 依赖：


    cn.hutool
    hutool-all
    5.7.17

2. 工具类：

    public static void export(List data, List list, HttpServletResponse response,Class cl,int passNum,String fileName){
        log.info("导出Excel  start。。。。。");
        log.info("导出数据：{}", JSONUtil.toJsonPrettyStr(data));
        
        if(ObjectUtil.isEmpty(data) || data.size() < 1){
            log.info("停止导出没有数据的操作。。");
            return ;
        }
        if(ObjectUtil.isEmpty(response)){
            log.info("响应参数不能为空！");
            return ;
        }
        
        if(StrUtil.isBlank(fileName)){
            fileName = "export.xlsx";
        }
        log.info("文件名称：{}",fileName);
        log.info("跳转行数：{}",passNum);

        ServletOutputStream resp = null;
        ExcelWriter writer = null;
        try {
            // 通过工具类创建writer，创建xlsx格式
            writer = ExcelUtil.getWriter(true);

            
            if(ObjectUtil.isNotEmpty(cl)){
                Field[] declaredFields = cl.getDeclaredFields();
                linkedHashMap collect = Arrays.stream(declaredFields)
                        // *** 需要有@ApiModelProperty注解的字段，没有注解的字段不要了 ***
                        .filter(e -> ObjectUtil.isNotEmpty(e.getAnnotation(ApiModelProperty.class)))
                        .collect(Collectors.toMap(Field::getName, e -> e.getAnnotation(ApiModelProperty.class).value(), (k1, k2) -> k2, linkedHashMap::new));
                writer.setHeaderAlias(collect);
            }

            
            if(ObjectUtil.isNotEmpty(list) && list.size() >0){
                log.info("合并策略：{}", JSONUtil.toJsonPrettyStr(list));
                for (MergeExport mergeExport : list) {
                    writer.merge(mergeExport.getFirstRow(),mergeExport.getLastRow(),mergeExport.getFirstColumn(),mergeExport.getLastColumn(),mergeExport.getContent(),true);
                }
                for (int i = 0; i < passNum; i++) {
                    
                    writer.passCurrentRow();
                }
            }

            // 一次性写出内容，使用默认样式，强制输出标题
            writer.write(data, true);

            
//            writer.autoSizeColumnAll();
            writer.setColumnWidth(-1,15);
            writer.setRowHeight(0,30);
            writer.setRowHeight(1,30);


            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition","attachment;filename="+fileName);
            resp = response.getOutputStream();
            writer.flush(resp, true);
            log.info("成功导出Excle：{}",fileName);
        } catch (IOException e) {
            log.info("导出Excle异常 error：{}",e.getMessage());
            e.printStackTrace();
        }finally {
            log.info("关闭流 。。。。。");
            // 关闭writer，释放内存
            writer.close();
            //此处记得关闭输出Servlet流
            IoUtil.close(resp);
            log.info("导出Excel  end。。。。。");
        }
    }
 

合并策略类
 

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.experimental.Accessors;


@AllArgsConstructor
@Data
@Accessors(chain = true)
@ApiModel("Excel 导出参数")
public class MergeExport {

    @ApiModelProperty("起始行，0开始")
    private int firstRow;

    @ApiModelProperty("结束行，0开始")
    private int lastRow;

    @ApiModelProperty("起始列，0开始")
    private int firstColumn;

    @ApiModelProperty("结束列，0开始")
    private int lastColumn;

    @ApiModelProperty("合并单元格后的内容")
    private Object content;

}


 

3. 导出Excel结果：
 
 
3.POI 工具类 

1. 依赖
 

		
			commons-fileupload
			commons-fileupload
			1.3.1
		
                
			commons-io
			commons-io
			2.5
		

		
			org.apache.poi
			poi
			3.17
		
		
			org.apache.poi
			poi-ooxml
			3.17
		

 

2. 工具类
 

 
    public static void createXSS(String sheetName, List> title,
                                               List> data,List merge, HttpServletResponse response
                                                ,String fileName,int distance){
        log.info("export start ...");
        log.info("导出标题：{}",JSONUtil.toJsonPrettyStr(title));
        log.info("导出数据：{}",JSONUtil.toJsonPrettyStr(data));
        log.info("合并策略：{}",JSONUtil.toJsonPrettyStr(merge));
        // 创建新HSSFWorkbook，对应一个Excel文件
//        HSSFWorkbook wb = new HSSFWorkbook();
        
        XSSFWorkbook wb = new XSSFWorkbook();

        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = wb.createSheet(sheetName);
        
        sheet.setDefaultColumnWidth(15);

        
        sheet.setVerticallyCenter(true);

        
        
        List colourStyle = getBackgroundColour(wb);

        
        for (int i = 0; i < merge.size(); i++) {
            MergeExport mergeExport = merge.get(i);
            CellRangeAddress rangeAddress = new CellRangeAddress(mergeExport.getFirstRow(), mergeExport.getLastRow(),
                    mergeExport.getFirstColumn(), mergeExport.getLastColumn());
            sheet.addMergedRegion(rangeAddress);
        }

        //声明列对象
        XSSFCell cell = null;

        
        // 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        XSSFRow row = sheet.createRow(0);
        row.setHeightInPoints(30);
        List firstTitle = title.get(0);
        
        cell = row.createCell(0);
        cell.setCellStyle(colourStyle.get(0));
        
        int firstCol = 1;
        for (int i = 0; i < firstTitle.size(); i++) {
            cell = row.createCell(firstCol);
            cell.setCellValue(firstTitle.get(i));
            cell.setCellStyle(colourStyle.get(i+1));
            firstCol+=distance;
        }
        
        cell = row.createCell(data.get(0).size()-1);
        cell.setCellStyle(colourStyle.get(colourStyle.size()-1));


        
        for (int i = 1; i < title.size(); i++) {
            XSSFRow tempRow = sheet.createRow(i);
            tempRow.setHeightInPoints(30);
            List otherTitle = title.get(i);

            for (int j = 0; j < otherTitle.size(); j++) {
                cell = tempRow.createCell(j);
                cell.setCellValue(otherTitle.get(j));
                
                
                setUpColor(cell,colourStyle,j,otherTitle.size());
            }
        }

        
        for (int i = 0; i < data.size(); i++) {
            XSSFRow tmpeRow = sheet.createRow(i+title.size());
            List values = new ArrayList();
            values.addAll(data.get(i).values());

            for (int j = 0; j < values.size(); j++) {
                cell = tmpeRow.createCell(j);
                cell.setCellValue(values.get(j));
                
                setUpColor(cell,colourStyle,j,values.size());
            }
        }

        
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition","attachment;filename="+fileName);
        ServletOutputStream resp = null;
        try {
            resp = response.getOutputStream();
            wb.write(resp);
            resp.flush();
            resp.close();
            log.info("export  sucess .....");
        } catch (Exception e) {
            log.info("export  erro:{}",e.getMessage());
            e.printStackTrace();
        }

    }

 

调用方法
 

 
    private static void setUpColor(XSSFCell cell,List color,int j,int distance){
        
        List> total = new ArrayList<>();
        
        total.add(Arrays.asList(0));
        
        int p = (distance - 1) / (color.size() - 1);
        
        int temp = 1;
        
        for (int i = 1; i < color.size(); i++) {
            
            total.add(Arrays.stream(NumberUtil.range(temp,temp + p -1)).boxed().collect(Collectors.toList()));
            temp = temp + p ;
        }

        
        for (int i = 0; i < total.size(); i++) {
            List list = total.get(i);
            if(list.contains(j)){
                cell.setCellStyle(color.get(i));
                return;
            }
        }

    }



    
    private static List getBackgroundColour(XSSFWorkbook wb){
        List list = new ArrayList<>();
        
        XSSFCellStyle greeyStyle = wb.createCellStyle();
//        greeyStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_80_PERCENT.getIndex());
        greeyStyle.setFillForegroundColor(new XSSFColor(new Color(217,217,217)));
        greeyStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置水平对齐的样式为居中对齐;
        greeyStyle.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        greeyStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        greeyStyle.setBorderBottom(BorderStyle.THIN);
        greeyStyle.setBorderLeft(BorderStyle.THIN);
        greeyStyle.setBorderTop(BorderStyle.THIN);
        greeyStyle.setBorderRight(BorderStyle.THIN);
        list.add(greeyStyle);

        
        XSSFCellStyle blueStyle = wb.createCellStyle();
//        blueStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.SKY_BLUE.getIndex());
        blueStyle.setFillForegroundColor(new XSSFColor(new Color(221,235,247)));
        blueStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置水平对齐的样式为居中对齐;
        blueStyle.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        blueStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        blueStyle.setBorderBottom(BorderStyle.THIN);
        blueStyle.setBorderLeft(BorderStyle.THIN);
        blueStyle.setBorderTop(BorderStyle.THIN);
        blueStyle.setBorderRight(BorderStyle.THIN);
        list.add(blueStyle);

        
        XSSFCellStyle yellowStyle = wb.createCellStyle();
//        yellowStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_YELLOW.getIndex());
        yellowStyle.setFillForegroundColor(new XSSFColor(new Color(255,242,204)));
        yellowStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置水平对齐的样式为居中对齐;
        yellowStyle.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        yellowStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        yellowStyle.setBorderBottom(BorderStyle.THIN);
        yellowStyle.setBorderLeft(BorderStyle.THIN);
        yellowStyle.setBorderTop(BorderStyle.THIN);
        yellowStyle.setBorderRight(BorderStyle.THIN);
        list.add(yellowStyle);

        
        XSSFCellStyle greenStyle = wb.createCellStyle();
//        greenStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_GREEN.getIndex());
        greenStyle.setFillForegroundColor(new XSSFColor(new Color(226,239,218)));
        greenStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置水平对齐的样式为居中对齐;
        greenStyle.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        greenStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        greenStyle.setBorderBottom(BorderStyle.THIN);
        greenStyle.setBorderLeft(BorderStyle.THIN);
        greenStyle.setBorderTop(BorderStyle.THIN);
        greenStyle.setBorderRight(BorderStyle.THIN);
        list.add(greenStyle);
        return list;
    }

 

3.导出结果
 存在两列标题，第一列进行了合并，并设置了不同的背景色：
 
 
4.前端响应方法 

async exportFile(val){
	  // 设置参数
      this[val] = true
      const param = {
        startTime:this.pickNowTopDate[0],
        endTime:this.pickNowTopDate[1]
      }
      // 调用后端方法
      const res = val == 'exportVisitsInfo' ? await exportVisitsInfo(param) : val == 'exportViewsInfo' ? await exportViewsInfo(param) : await exportApplyInfo(param)
      // 对响应Response的处理   接收xlsx文件
      if(res.data){
        this[val] = false
        let filename = res.headers['content-disposition']
        filename = filename.split('filename=')[1]
        let link = document.createElement("a");
        let blogw = new Blob([res.data],{type:"application/vnd.ms-excel;charset=utf-8"})
        let objectUrl = window.URL.createObjectURL(blogw); 
        link.href = objectUrl;
        link.download = filename;
        link.click();
        window.URL.revokeObjectURL(objectUrl)
      }
    }


					
										


					

						
欢迎分享，转载请注明来源：内存溢出
原文地址: https://outofmemory.cn/zaji/5671363.html

					

				
								

										
										
										
										
										
									

								

					
						
							
						 赞
						(0)
					
					

						
							 打赏
						
							
								微信扫一扫 微信扫一扫
							
							
								支付宝扫一扫 支付宝扫一扫
							
						
					

				

				

					

						

							
								南京大学cssci
								南京大学cssci
								一级用户组
							
						

						

							

								
									
										
									
									0
								
								
									
										
									
									0
								
							

							
							

								
									
										
									
								
							

						

					

				

			
			
            

            	            

            
			

								

					
					

         				
							
								
							 上一篇
						
						2022-12-16
					

				

												

					
					

         				
							下一篇 
           						
							
						
						2022-12-17
					

				

							


			
			

				

					

						发表评论
					

										

						
						

							

								登录后才能评论
							

							
						

					

									

								
 评论列表（0条）

				
    
									

				
											

		

	

	





	


	

		

			

			

				
				

					

						Copyright © 2022 内存溢出 版权所有
						
							湘ICP备2022025235号						
						Powered by
						
							outofmemory.cn
						
					
					

				

			

			

				

					
					
						
						
					
					
						
					
				

			

		

	




























    
    

    

        
            保存