当前位置: 首页 > 工具软件 > XSSF > 使用案例 >

springBoot 生成excel (org.apache.poi.xssf.usermodel)

牛迪
2023-12-01

--------------------------------------------controller层--------------------------------------------------

@RequestMapping(value = "/excel/check_project_material_list", method

 = RequestMethod.GET)
@ApiOperation(value = "检测项目物料清单报表导出")
@ApiImplicitParam(value = "任务id", name = "taskId", required = true)
public void excelDownload(HttpServletRequest request, HttpServletResponse response, Integer taskId, String module) {
    new ExcelUtils().downloadExcelByTaskId(taskId, module, response);
}

----------------------------------------------工具类---------------------------------------------------------

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import javax.annotation.PostConstruct;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.*;


@Component
public class ExcelUtils {
    private static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);

private static ExcelUtils excelUtils;
    private static ReportMongoDao reportMongoDao;
    private static SysTaskDao sysTaskDao;
    @Autowired
    ReportMongoDao mongoDao;
    @Autowired
    SysTaskDao taskDao;

    @PostConstruct
    public void init() {
        excelUtils = this;
        reportMongoDao = mongoDao;
        sysTaskDao = taskDao;
    }


    /**
     * @return void
     * @Author ChengYuYun
     * @Description //TODO
通过taskid  type  获取数据填充excel
     * @Date 10:44 2020/12/2
     * @Param [taskId, type, response, logo]
     **/
   
public void downloadExcelByTaskId(Integer taskId, String type, HttpServletResponse response) {
        SysTaskEntity taskEntity = sysTaskDao.selectTaskByTaskId(taskId);
        String taskName = taskEntity.getProjectName();
        Map<String, Object> map = getTemplateAndFilenameByType(taskName, type);
        //SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd-HH:mm");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String filename = map.get("filename") + "_" + sdf.format(new Date()) + ".xlsx";
        FileInputStream fileInputStream = null;
        try {
            InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("templates/excel/" + map.get
                    ("template"));
            downloadExcel(is, response, filename, taskId, type);
        } catch (Exception ex) {
            logger.error("downloadExcelByTaskid发生异常", ex);
        } finally {
            if (fileInputStream != null) {
                try {
                    fileInputStream.close();
                } catch (IOException e) {
                    logger.error("downloadExcelByTaskid发生异常", e);
                }
            }
        }

    }
    /**
     * @Author ChengYuYun
     * @Description //TODO
下载excel
     * @Date 13:44 2020/12/2
     * @Param [fis, response, filename, taskId, type]
     * @return void
     **/
   
public void downloadExcel(InputStream fis, HttpServletResponse response, String filename, int taskId,
                              String type) {
        OutputStream out = null;
        try {
            XSSFWorkbook workBook = new XSSFWorkbook(fis);

            filename = new String((filename).getBytes(), "iso-8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + filename);
            out = response.getOutputStream();
            //  检测项目物料清单
            if (type.equals(ExeclTemplate.CHECK_PROJECT_MATERIAL_lIST)) {
                List<UseFile> useFiles = reportMongoDao.selectAllUseFiles(taskId);
                insertUseFilesInfoDataToExcel(workBook, useFiles, 0, taskId);           

}
            workBook.write(out);
            fis.close();
            out.flush();
            out.close();
        } catch (Exception ex) {
            logger.error("downloadexcel下载excel异常:", ex);
        } finally {
            try {
                if (fis != null) {
                    fis.close();
                }
                if (out != null) {
                    out.flush();
                    out.close();
                }
            } catch (Exception ex) {
                logger.error("downloadExcel下载excel异常:", ex);
            }
        }
    }
 /**
     * @return void
     * @Author ChengYuYun
     * @Description //TODO
检测项目物料清单--有效文件详情
     * @Date 10:11 2020/12/2
     * @Param [workBook, list, sheetId]
     **/
   
public void insertUseFilesInfoDataToExcel(XSSFWorkbook workBook, List<UseFile> list, int sheetId, int taskId) {
        XSSFSheet sheet = workBook.getSheetAt(sheetId);
        int rowNum = sheet.getLastRowNum();
        if (list != null && list.size() > 0) {
            // 填充数据
            for (int i = 0; i < list.size(); i++) {
                try {
                    UseFile useFile = list.get(i);
                    XSSFRow row = sheet.getRow(i + 3);
                    if (row == null) {
                        row = sheet.createRow(i + 3);
                    }
                    for (int j = 0; j < 6; j++) {
                        XSSFCell cell = row.getCell(j);
                        if (cell == null) {
                            cell = row.createCell(j);
                        }
                    }
                    row.getCell(0).setCellValue(i + 1);
                    row.getCell(1).setCellValue(useFile.getPath());
                    row.getCell(2).setCellValue(useFile.getSize());
                    row.getCell(3).setCellValue(useFile.getLine());
                    LicenseCopyright licenseCopyright = reportMongoDao.selectLicenseCopyrightByCheckpath(taskId, useFile.getPath());
                    String license = licenseCopyright.getLicense();
                    String copyRight = licenseCopyright.getCopyright();
                    row.getCell(4).setCellValue(license);
                    row.getCell(5).setCellValue(copyRight);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
    /**
     * @return java.util.Map
<java.lang.String   ,   java.lang.Object>
     * @Author ChengYuYun
     * @Description //TODO
通过taskName 和type获取模板名称和导出文件名
     * @Date 10:47 2020/12/2
     * @Param [taskName, type]
     **/
   
public static Map<String, Object> getTemplateAndFilenameByType(String taskName, String type) {
        Map<String, Object> map = new HashMap<>();
        String template = "";
        String filename = "";
        switch (type) {
            case ExeclTemplate.CHECK_PROJECT_MATERIAL_lIST:
                template = ExeclTemplate.CHECK_PROJECT_MATERIAL_lIST;
                filename = taskName + "_检测项目物料清单";
                break;
            /*case ExeclTemplate.CLONEDETAIL_TYPE:
                template = !logo ? ExeclTemplate.CLONEDETAIL_LOGO_XLSX : ExeclTemplate.CLONEDETAIL_XLSX;
                filename = taskName + "_开源代码匹配清单";
                break;

          ……

 */
        }
        map.put("template", template);
        map.put("filename", filename);
        return map;
    }
}

 类似资料: