java 对Excel 操作 例子(下)

结合项目理解

主要是封装在一个方法里面

实现逻辑代码：

ChangeProcessPlanDownloadUtils 实现类

@Component
public class ChangeProcessPlanDownloadUtils {
    @Autowired
    ProductDesignChangeService changeService;
    @Autowired
    ProductDesignChangePartService changePartService;
    @Autowired
    ISupplierService supplierService;
    @Autowired
    ProductDesignTechnologySampleDemandService demandService;
    @Autowired
    ProductDesignProcessPlanService planService;
    @Autowired
    ProductDesignTrialProcessService trialProcessService;
    @Autowired
    IAttachmentTemplateService attachmentTemplateService;


    /**
     * 获取 checkbox 框框字体
     *
     * @param workbook
     * @return
     */
    private static Font getBlockFont(Workbook workbook) {
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 7);
        font.setFontName("宋体");
        return font;
    }

    /**
     * 设置单元格颜色
     */
    private static CellStyle getColorFont(Workbook workbook) {
        CellStyle redStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setColor(Font.COLOR_RED);
        redStyle.setFont(font);
        return redStyle;
    }


    /**
     * 处理附件模板
     * @param planId    传入 实施计划 Id
     * @return
     */
    public void handle(File templateFile,Long planId){

        // 获取到这个文件
        Workbook wb = ExcelUtil.getWorkbook(templateFile);
        // 获取到第一个表单
        Sheet sheet = wb.getSheetAt(0);

        //根据参数实施计划的id查询实施计划
        ProductDesignProcessPlan plan = planService.getById(planId);
        //根据实施计划changeidid查询设变调查
        ProductDesignChange change = changeService.getById(plan.getChangeId());
        //根据设变id查询变更零件
        LambdaQueryWrapper<ProductDesignChangePart> partLambdaQueryWrapper = new LambdaQueryWrapper<>();
        partLambdaQueryWrapper.eq(ProductDesignChangePart::getChangeId,change.getId());
        List<ProductDesignChangePart> changeParts = changePartService.list(partLambdaQueryWrapper);
        //旧品番
        ProductDesignChangePart changeOldPart = changeParts.get(0);
        //新品番
        ProductDesignChangePart changeNewPart = changeParts.get(1);
        //根据 变更零件 供应商 id 查询 供应商
        Supplier supplier = supplierService.getById(changeNewPart.getSupplierId());


        /**
         * 填充
         */

        //设变编号
        ExcelUtil.getCell(sheet,10,0,getBlockFont(sheet.getWorkbook())).setCellValue(change.getCode());
        //切替形态
        ExcelUtil.getCell(sheet,16,0,getBlockFont(sheet.getWorkbook())).setCellValue(DictBizCache.getValue(DictBizEnum.PRODUCT_DESIGN_CHANGE_CUT_FORM.getName(),change.getCutForm()));
        //切替方式
        List<Integer> cutMethods = Func.toIntList(change.getCutMethod());
        int cutMethodsRow = 22;
        for (Integer cutMethod : cutMethods) {
            ExcelUtil.getCell(sheet,cutMethodsRow++,0, getBlockFont(sheet.getWorkbook())).setCellValue(DictBizCache.getValue(DictBizEnum.PRODUCT_DESIGN_CHANGE_CUT_METHOD.getName(),cutMethod) + "\n");
        }
        //切替目的
        List<Integer> cutTargets = Func.toIntList(change.getCutTarget());
        List<DictBiz> targetDicts = DictBizCache.getList(DictBizEnum.PRODUCT_DESIGN_CHANGE_CUT_TARGET.getName());
        int cutTargetsRow = 30;
        for (DictBiz dic : targetDicts) {
            if (cutTargets.contains(Func.toInt(dic.getDictKey()))) {
                ExcelUtil.getCell(sheet,cutTargetsRow++,0,getBlockFont(sheet.getWorkbook())).setCellValue(dic.getDictValue());
            }
        }

        //1.切替机型
        List<Integer> modelTypes = Func.toIntList(change.getModel());
        int modelTypesRow = 4;
        modelTypesRow = modelTypesRow + 2;
        for (Integer modelType : modelTypes) {
            ExcelUtil.getCell(sheet,10,modelTypesRow,getBlockFont(sheet.getWorkbook())).setCellValue(DictBizCache.getValue(DictBizEnum.PRODUCT_DESIGN_CHANGE_MODEL.getName(),modelType));
        }

        //2.变更零件
        ExcelUtil.getCell(sheet,18,7,getBlockFont(sheet.getWorkbook())).setCellValue(changeOldPart.getPartCode());
        ExcelUtil.getCell(sheet,19,10,getBlockFont(sheet.getWorkbook())).setCellValue(changeNewPart.getPartCode());
        ExcelUtil.getCell(sheet,18,13,getBlockFont(sheet.getWorkbook())).setCellValue(changeNewPart.getPartName());
        ExcelUtil.getCell(sheet,18,17,getBlockFont(sheet.getWorkbook())).setCellValue(changeOldPart.getVersion());
        ExcelUtil.getCell(sheet,19,17,getBlockFont(sheet.getWorkbook())).setCellValue(changeNewPart.getVersion());
        ExcelUtil.getCell(sheet,18,19,getBlockFont(sheet.getWorkbook())).setCellValue(changeNewPart.getProgramCode());
        ExcelUtil.getCell(sheet,18,21,getBlockFont(sheet.getWorkbook())).setCellValue(supplier.getShortName());
        ExcelUtil.getCell(sheet,19,21,getBlockFont(sheet.getWorkbook())).setCellValue(supplier.getShortName());
        ExcelUtil.getCell(sheet,18,23,getBlockFont(sheet.getWorkbook())).setCellValue(changeNewPart.getEngineVersion());
        ExcelUtil.getCell(sheet,18,26,getBlockFont(sheet.getWorkbook())).setCellValue(change.getChangeContent());

        //3.切替日程
        ExcelUtil.getCell(sheet,32,8,getBlockFont(sheet.getWorkbook())).setCellValue(DateUtil.format(changeNewPart.getInitialIncludeDate(), DatePattern.NORM_DATE_PATTERN));
        ExcelUtil.getCell(sheet,35,8,getBlockFont(sheet.getWorkbook())).setCellValue(DateUtil.format(changeNewPart.getInitialExpectCutDate(), DatePattern.NORM_DATE_PATTERN));
        //切替方法
        if (1 == plan.getIsCutAfterUse()){
            ExcelUtil.getCell(sheet,38,8,getBlockFont(sheet.getWorkbook())).setCellValue("旧品使用后切替（是）");
        } else if (0 == plan.getIsCutAfterUse()){
            ExcelUtil.getCell(sheet,38,8).setCellValue(" ");
        }
        if (1 == plan.getNeedInOrOut()){
            ExcelUtil.getCell(sheet,40,8,getBlockFont(sheet.getWorkbook())).setCellValue("E/G的先入先出（要）");
        } else if (0 == plan.getNeedInOrOut()){
            ExcelUtil.getCell(sheet,40,8).setCellValue(" ");
        }

        //4.生产管理室CHECK
        //设备变更
        if (1 == plan.getHasDesignChange()){
            ExcelUtil.getCell(sheet,30,27,getBlockFont(sheet.getWorkbook())).setCellValue("设备变更: 有");
        } else if (0 == plan.getHasDesignChange()){
            ExcelUtil.getCell(sheet,30,27).setCellValue(" ");
        }
        //到货区分
        if (1 == plan.getHasArrived()){
            ExcelUtil.getCell(sheet,32,27,getBlockFont(sheet.getWorkbook())).setCellValue("到货区分: 有");
        } else if (0 == plan.getHasArrived()){
            ExcelUtil.getCell(sheet,32,27).setCellValue(" ");
        }
        //系统变更
        if (1 == plan.getHasSystemChange()){
            ExcelUtil.getCell(sheet,34,27,getBlockFont(sheet.getWorkbook())).setCellValue("系统变更: 有");
        } else if (0 == plan.getHasSystemChange()){
            ExcelUtil.getCell(sheet,32,27).setCellValue(" ");
        }
        // *大变更时
        //补给原单位
        if (1 == plan.getHasSupplyOriginalDept()){
            ExcelUtil.getCell(sheet,38,27,getBlockFont(sheet.getWorkbook())).setCellValue("补给原单位: 有");
        } else if (0 == plan.getHasSupplyOriginalDept()){
            ExcelUtil.getCell(sheet,38,27).setCellValue(" ");
        }
        //原单位表：
        if (1 == plan.getHasOriginalDeptTable()){
            ExcelUtil.getCell(sheet,39,27,getBlockFont(sheet.getWorkbook())).setCellValue("原单位表: 有");
        } else if (0 == plan.getHasOriginalDeptTable()){
            ExcelUtil.getCell(sheet,38,27).setCellValue(" ");
        }
        //同时切替时：
        if (1 == plan.getHasTogetherCut()){
            ExcelUtil.getCell(sheet,40,27,getBlockFont(sheet.getWorkbook())).setCellValue("同时切替时: 要");
        } else if (0 == plan.getHasTogetherCut()){
            ExcelUtil.getCell(sheet,40,27).setCellValue(" ");
        }
        // 内示确认：
        if (1 == plan.getHasInnerView()){
            ExcelUtil.getCell(sheet,41,27,getBlockFont(sheet.getWorkbook())).setCellValue("内示确认: 要");
        } else if (0 == plan.getHasInnerView()){
            ExcelUtil.getCell(sheet,41,27).setCellValue(" ");
        }

        //写入 硬盘
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(templateFile);
            wb.write(fileOutputStream);
            fileOutputStream.flush();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if(fileOutputStream != null){
                try {
                    fileOutputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }


    }

}

调用方法        大致的逻辑实现

            /**
             * 主要调用 逻辑
             */
        if (!deptIdSet.isEmpty()) {
            List<Long> userIds = userDeptService.getUserIdByDeptId(new ArrayList<>(deptIdSet));
            ChangeProcessPlanDownloadUtils changeProcessPlanDownloadUtils = SpringUtil.getBean(ChangeProcessPlanDownloadUtils.class);
            try {
                //获取到附件模板
                AttachmentTemplate template = attachmentTemplateService.getAttachmentTemplateByCode("product_process_desige");
                File templateFile = null;
                //创建一个xlsx临时文件
                templateFile = File.createTempFile("toyota-product-design-detail-change", ".xlsx");
                //拷贝 把内容拷贝到临时文件中
                FileUtil.copy(attachmentTemplateService.getFile(template.getFileName()), templateFile, true);
                //调用修改后的方法
                changeProcessPlanDownloadUtils.handle(templateFile, plan.getId());
                /**
                 * 转成pdf 再发出
                 */
                //创建一个pdf临时文件
                File tmpFile = File.createTempFile("toyota-product-design-detail-change", ".pdf");
                //把xlsx文件转成pdf
                ExcelToPdfUtil.excelToPdf(templateFile.getAbsolutePath(), tmpFile.getAbsolutePath());
                List<File> files = new ArrayList<>();
                files.add(tmpFile);
                messageService.sendEmailMsg(EmailTemplateConstant.PRODUCT_DESIGN_CHANGE_PROCESS_PLAN_NOTICE, ProductDesignProcessPlanWrapper.build().emailParam(plan, change), userIds,files);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

excel 转 pdf 工具类        

ExcelToPdfUtil

public class ExcelToPdfUtil {

    public static void main(String[] args) throws Exception {
        excelToPdf("C:\\Users\\admin\\Desktop\\部品数据.xls", "C:\\Users\\admin\\Desktop\\部品数据.pdf");
    }

    /**
     * Excel文件转Pdf.
     * @throws Exception .
     */
    public static void excelToPdf(String path, OutputStream out) throws Exception {
        // 加载Excel文档.
        Workbook wb = new Workbook();
        wb.loadFromFile(path);
        wb.saveToStream(out, FileFormat.PDF);
    }

    /**
     * Excel文件转Pdf.
     * @throws Exception .
     */
    public static void excelToPdf(InputStream in, OutputStream out) throws Exception {
        // 加载Excel文档.
        Workbook wb = new Workbook();
        wb.loadFromStream(in);
        // 调用方法保存为PDF格式.
        wb.saveToStream(out, FileFormat.PDF);
    }

    /**
     * Excel文件转Pdf.
     * @param excelPath Excel文件路径.
     * @param pdfPath Pdf文件路径.
     * @throws Exception .
     */
    public static void excelToPdf(String excelPath, String pdfPath) throws Exception {
        // 加载Excel文档.
        Workbook wb = new Workbook();
        wb.loadFromFile(excelPath);
        // 调用方法保存为PDF格式.
        wb.saveToFile(pdfPath, FileFormat.PDF);
    }

    /**
     * Excel文件转Pdf.
     * @param excelPath Excel文件路径.
     * @param pdfPath Pdf文件路径.
     * @param sheetIndex sheet页序号.
     * @throws Exception .
     */
    public static void excelToPdf(String excelPath, String pdfPath, int sheetIndex) throws Exception {
        // 加载Excel文档.
        Workbook wb = new Workbook();
        wb.loadFromFile(excelPath);

        Worksheet sheet = wb.getWorksheets().get(sheetIndex);
        // 调用方法保存为PDF格式.
        wb.saveToFile(pdfPath, FileFormat.PDF);
    }

}