java自定义导出excel

最新推荐文章于 2024-07-12 15:18:42 发布

love_燕子

最新推荐文章于 2024-07-12 15:18:42 发布

阅读量337

点赞数

分类专栏： java 文章标签： excel

本文链接：https://blog.csdn.net/u010833154/article/details/131979946

版权

java 专栏收录该内容

38 篇文章 0 订阅

订阅专栏

导出Excel时候，表头需要自定义，只能自己重新写导出代码了
1、引入包

<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>easyexcel</artifactId>
  <version>2.1.6</version>
</dependency>

2、工具类

public class ExportExcel {
	
	//设置表格数据
    public static void export(SXSSFSheet sheetOne,List<String> heads,List<QuestionSubjectAnswer> answerList,String title,String status){
        setHeaderData(sheetOne,heads);
        if(!CollectionUtils.isEmpty(answerList)){
            Row dataRow =null;
            int num=1;
            for(QuestionSubjectAnswer answer:answerList){
                dataRow = sheetOne.createRow(num);
                Cell cell = dataRow.createCell(0);
                cell.setCellValue(answer.getPhone());
                cell = dataRow.createCell(1);
                cell.setCellValue(title);
                cell = dataRow.createCell(2);
                cell.setCellValue(answer.getNickName());
                cell = dataRow.createCell(3);
                cell.setCellValue(status);
                cell = dataRow.createCell(4);
                cell.setCellValue(answer.getCreateTime().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
                JSONObject jsonObject=JSONObject.parseObject(answer.getAnswers());
                for(int questionNum=0;questionNum<(heads.size()-5);questionNum++){
                    cell = dataRow.createCell(5+questionNum);
                    cell.setCellValue(jsonObject.getString(String.valueOf(questionNum+1)));
                }
                num++;
            }
        }
    }
	//设置表头
    private static void setHeaderData(SXSSFSheet sheetOne,List<String> heads) {
        Row row = sheetOne.createRow(0);
        int num=0;
        for (String head:heads) {
            Cell cell = row.createCell(num);
            cell.setCellValue(head);
            num++;
        }
    }
}

3、编写controller

//固定表头
private final static String[] headStr=new String[]{"手机号","问卷标题","昵称","问卷状态","问卷提交时间"};

    @PostMapping("/v1/export")
    public void export(@RequestBody final QuestionSubjectAnswerRequestBean questionSubjectAnswerRequestBean, HttpServletResponse response) {
        OutputStream out =null;
        try{
            List<QuestionSubjects> questionSubjects=iQuestionSubjectsService.findByNaireId(questionSubjectAnswerRequestBean.getId());
            List<String> heads=new ArrayList<>();
            heads.addAll(Arrays.asList(headStr));
            if(!CollectionUtils.isEmpty(questionSubjects)){
            	//表头数据列表
                for(QuestionSubjects subject:questionSubjects){
                    heads.add(subject.getSubjectName());
                }
            }

            QuestionNaire questionNaire=iQuestionNaireService.findById(questionSubjectAnswerRequestBean.getId());
            SXSSFWorkbook wb = new SXSSFWorkbook(100);
            //sheet创建
            SXSSFSheet sheetOne = wb.createSheet();
            wb.setSheetName(0, questionNaire.getTitle());

            List<QuestionSubjectAnswer> answerList=iQuestionSubjectAnswerService.findByCondition(questionSubjectAnswerRequestBean);
            ExportExcel.export(sheetOne,heads,answerList,questionNaire.getTitle(), QuestionNaireStatusEnum.getDescByCode(questionNaire.getStatus()));
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename="+new String("问卷结果导出.xlsx".getBytes(), "UTF-8"));
            out = response.getOutputStream();
            wb.write(out);
        }catch (Exception ex){
            log.error("导出问卷调查文件失败{}",ex);
        }finally {
            try {
                out.close();
            } catch (IOException e) {
                log.error("导出问卷调查文件流关闭失败{}",e);
            }
        }
    }