某些情况下,我们的需求可能需要把数据库某些字段或者某些数据来写入到Excel并生成文件,而恰好最近我就收到了这样的需求,把签到数据写入Excel并生成,提供接口给前端下载使用.

操作微软的办公套件工具我看了有JXL和POI,最后还是选用POI.

需求

将MySQL字段内存的JSON数据写入到Excel表格中,title标题与content数量为用户自定义,前端传入当前签到活动的ID到数据库查询到对应ID的数据转为list, 转list需要先生成一个实体类映射对象.

对象

@Data
@AllArgsConstructor
@NoArgsConstructor
public class BasicSelcInfo {
    private String title;
    private String content;
}

坐标

<!--操作Excel的依赖坐标-->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
 <groupId>org.apache.poi</groupId>
 <artifactId>poi</artifactId>
 <version>3.17</version>
</dependency>

代码

@Service
public class ExcelServiceImpl implements ExcelService {

    @Autowired
    private AttendanceMapper attendanceMapper;

    //生成Excel方法
    public HSSFWorkbook getHSSFWorkbook(Long id) {
        //创建对象
        HSSFWorkbook workbook = null;
        //查询数据库
        List<Attendance> attendances = attendanceMapper.listAttendanceByAid(id);
        System.err.println(attendances.size());
        System.out.println(attendances);

        //非空判断
        if (workbook == null) {
            workbook = new HSSFWorkbook();
        }
        //在workbook中添加一个sheet
        HSSFSheet sheet = workbook.createSheet(new Date().getTime() + ""); //设置当前时间为sheet名称
        //sheet添加标头第0行
        HSSFRow row = sheet.createRow(0);
        //创建单元格设置,设置标头居中
        HSSFCellStyle style = workbook.createCellStyle();
        //声明列对象
        HSSFCell cell = null;
        sheet.setDefaultRowHeightInPoints(10);

        //声明list,存放数据库总结果
        List<List<BasicSelcInfo>> all = new ArrayList<>();
        //声明list,提升作用域
        List<BasicSelcInfo> list = null;
        //获取字段JSON
        for (Attendance attendance : attendances) {
            String jsonText = attendance.getBasicSelcInfo();
            ObjectMapper objectMapper = new ObjectMapper();
            //JSON转集合
            try {
                list = objectMapper.readValue(jsonText, new TypeReference<List<BasicSelcInfo>>() {
                });
            } catch (IOException e) {
                e.printStackTrace();
            }
            all.add(list);
            System.err.println("数据" + list);
        }
        //创建标题
        for (int i1 = 0; i1 < list.size(); i1++) {
            cell = row.createCell(i1);
            cell.setCellValue(list.get(i1).getTitle());
            cell.setCellStyle(style);
        }
        //创建内容
        int index = 0;
        for (List<BasicSelcInfo> basicSelcInfos : all) {
            row = sheet.createRow(index + 1);
            for (int j = 0; j < basicSelcInfos.size(); j++) {
                row.createCell(j).setCellValue(basicSelcInfos.get(j).getContent());
            }
            index++;
        }
        
       /* try {
            //文件输出流
            OutputStream os = new FileOutputStream(new File("/Users/zhangchanggeng/Desktop/test.xls"));
            //写入文件
            workbook.write(os);
            //切换状态
            os.flush();
        }catch (Exception e){
            e.printStackTrace();
        }*/
        return workbook;
    }
}

Controller

@RestController
public class DownLoadExcelController {

    @Autowired
    private ExcelService excelService;

    //处理Excel
    @GetMapping("/exportExcel")
    public void exportExcel(@RequestParam("aid") Long aid , HttpServletResponse response){
        HSSFWorkbook workbook = excelService.getHSSFWorkbook(aid);


        try {
            //响应到客户端
            this.setResponseHeader(response,new Date().getYear()+"Y"+aid+".xls");
            ServletOutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    //发送响应流
    public void setResponseHeader(HttpServletResponse response,String fileName){
        fileName = new String(fileName.getBytes(), StandardCharsets.UTF_8);
        response.setContentType("application/vnd.ms-excel");
        try {
            response.setHeader("Content-disposition","attachment:filename="+ URLEncoder.encode(fileName,"UTF-8"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
    }
}