org.apache.poi 3.9依赖普通导出execl

org.apache.poi 3.9依赖 普通导出execl

<!-- POI导入导出3.9版本 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.9</version>
        </dependency>

  @PostMapping("/exportExcel")
    @ApiOperation("导出监控数据")
    public AjaxResult exportExcel(HttpServletResponse response, @RequestBody MonitorVO vo) throws Exception {
        try {
            cudWbProcessInfoTService.exportExcel(response, vo);
        } catch (Exception e) {
            e.getStackTrace();
            //log.info("HeartMailQuestionController->exportExcel  导出失败");
            return error("导出失败");
        }
        return success("导出成功");
    }

service 依赖

import com.cgnpc.cqs.constant.enumeration.EnumEnterpriseCategory;
import com.cgnpc.cqs.constant.enumeration.EnumTransferOperationType;
import com.cgnpc.cqs.monitor.domain.CudWbProcessInfoT;
import com.cgnpc.cqs.monitor.mapper.CudWbProcessInfoTMapper;
import com.cgnpc.cqs.monitor.service.ICudWbProcessInfoTService;
import com.cgnpc.cqs.monitor.vo.MonitorVO;
import com.cgnpc.cqs.utils.EasyExcelUtil;
import com.cgnpc.cqs.wchz.model.SubEnterpriseAllInfoVO;
import com.cgnpc.cud.core.service.BaseServiceImpl;
import com.cgnpc.cud.utils.text.StringUtil;
import com.cgnpc.cuddemo.httpclient.application.exception.BusinessException;
import com.cgnpc.framework.userinfo.CurrentUser;
import com.cgnpc.framework.utils.HttpServlet;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

 @Override
    public void exportExcel(HttpServletResponse response, MonitorVO vo) throws Exception {

        List<MonitorVO> list = baseMapper.list(vo);

        //List<Map<String, Object>> dataList = new ArrayList<>();
        //if(!CollectionUtils.isEmpty(list)) {
        //    for (MonitorVO pojo : list) {
        //        Map<String, Object> dataExport = new HashMap<>();
        //        if (!StringUtils.isEmpty(pojo.getProcTitle())) {
        //            dataExport.put("procTitle", pojo.getProcTitle()); // 任务标题
        //        }
        //        if (!StringUtils.isEmpty(pojo.getProcName())) {
        //            dataExport.put("procName", pojo.getProcName());//流程类型
        //        }
        //        if (!StringUtils.isEmpty(pojo.getStartUser())) {
        //            dataExport.put("startUser", pojo.getStartUser()); // 发起人
        //        }
        //        if (!StringUtils.isEmpty(pojo.getActName())) {
        //            dataExport.put("actName", pojo.getActName()); // 当前环节
        //        }
        //        if (!StringUtils.isEmpty(pojo.getStartTime())) {
        //            dataExport.put("startTime", pojo.getStartTime()); // 发起时间
        //        }
        //        if (!StringUtils.isEmpty(pojo.getEndTime())) {
        //            dataExport.put("endTime", pojo.getEndTime()); //完成时间
        //        }
        //
        //        dataList.add(dataExport);
        //    }
        //}
        //String fileName = "MonitorExport";
        //String template = this.getClass().getClassLoader().getResource("").getPath() + "upfileTemplate/" + fileName + ".xlsx";
        //EasyExcelUtil.exportSingleSheetExcel(response, dataList, dataList.getClass(), fileName, "", template);


        // 文件名
        String fileTitle = "业务监控数据";
        // 设置表头内容
        String[] headers = {"任务标题", "流程类型", "发起人", "当前环节", "发起时间", "完成时间"};

        // 创建一个Workbook对象
        Workbook workbook = initExcelWorkbook(fileTitle, fileTitle, headers);
        Sheet sheet = workbook.getSheetAt(0);

        // 遍历列表，创建行并填充数据
        CellStyle rightStyle = workbook.createCellStyle();
        rightStyle.setAlignment(CellStyle.ALIGN_RIGHT);
        CellStyle leftStyle = workbook.createCellStyle();
        leftStyle.setAlignment(CellStyle.ALIGN_LEFT);
        CellStyle centerStyle = workbook.createCellStyle();
        centerStyle.setAlignment(CellStyle.ALIGN_CENTER);
        for (int i = 0; i < list.size(); i++) {
            MonitorVO exportVo = list.get(i);
            Row row = sheet.createRow(i + 1);

            Cell babmCell = row.createCell(0);
            babmCell.setCellValue(exportVo.getProcTitle() == null ? "" : exportVo.getProcTitle());
            babmCell.setCellStyle(centerStyle);
            Cell pgdxCell = row.createCell(1);
            pgdxCell.setCellValue(exportVo.getProcName() == null ? "" : exportVo.getProcName());
            pgdxCell.setCellStyle(centerStyle);
            Cell barqCell = row.createCell(2);
            barqCell.setCellValue(exportVo.getStartUser() == null ? "" : exportVo.getStartUser());
            barqCell.setCellStyle(centerStyle);
            Cell jjxwlxCell = row.createCell(3);
            jjxwlxCell.setCellValue(exportVo.getActName() == null ? "" : exportVo.getActName());
            jjxwlxCell.setCellStyle(centerStyle);
            Cell zcpgwtfCell = row.createCell(4);
            zcpgwtfCell.setCellValue(exportVo.getStartTime() == null ? "" : exportVo.getStartTime());
            zcpgwtfCell.setCellStyle(centerStyle);
            Cell pgjzrCell = row.createCell(5);
            pgjzrCell.setCellValue(exportVo.getEndTime() == null ? "" : exportVo.getEndTime());
            pgjzrCell.setCellStyle(centerStyle);
        }

        exportWorkbook(workbook, fileTitle);
    }


    /**
     * 初始化excel的workbook
     *
     * @param sheetName 第一个sheet名称
     * @param tableTitle 表格主题
     * @param tableHeaders 表头数组
     * @return
     */
    private Workbook initExcelWorkbook(String sheetName, String tableTitle, String[] tableHeaders){
        // 创建一个Workbook对象
        Workbook workbook = new XSSFWorkbook();
        // 创建一个Sheet对象
        Sheet sheet = workbook.createSheet(sheetName);
        for (int i = 0; i < tableHeaders.length; i++) {
            if(i == 0){
                sheet.setColumnWidth(i, 20*500);
            }else {
                sheet.setColumnWidth(i, 20*300);
            }
        }

        //创建字体
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 16);         //设置字体大小
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);   //粗体显示
        //创建单元格样式
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(font);

        // 第一行是表头
        Row headerRow = sheet.createRow(0);
        Font font2 = workbook.createFont();
        font2.setFontName("宋体");
        font2.setColor(IndexedColors.WHITE.getIndex());
        font2.setFontHeightInPoints((short) 12);         //设置字体大小
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);   //粗体显示
        CellStyle style2 = workbook.createCellStyle();
        style2.setAlignment(CellStyle.ALIGN_CENTER);
        style2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style2.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
        style2.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style2.setFont(font2);
        for (int i = 0; i < tableHeaders.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(tableHeaders[i]);
            cell.setCellStyle(style2);
        }

        return workbook;
    }


    /**
     * 导出excel
     *
     * @param workbook excel对象
     * @param fileName 文件名
     */
    private void exportWorkbook(Workbook workbook, String fileName){
        // 将Workbook对象写入文件
        OutputStream os = null;
        try {
            String date = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
            String exportName = fileName + date + ".xlsx";
            HttpServletResponse response = HttpServlet.getResponse();
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            //前端好像获取不到filename
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(exportName, "UTF-8"));
            os = response.getOutputStream();
            workbook.write(os);
            System.out.println("导出成功！");
        } catch (IOException e) {
            log.error("导出失败");
            e.printStackTrace();
        } finally {
            if (os != null){
                try {
                    os.flush();
                    os.close();
                } catch (IOException e) {
                    log.error("输出流异常！");
                    throw new RuntimeException(e);
                }
            }
        }
    }