导入导出Excel

导出Excel设置某一列下拉框

     /** 
     * 工具类
     * 只针对 .xlsx设置下拉列表
     * @param subContent 下拉选择内容
     * @param sheet 表格
     * @param firstRow 开始行
     * @param lastRow 结束行
     * @param firstCol 开始列
     * @param lastCol 结束列
     */
    public static void setSubListExcel(String[] subContent,XSSFSheet sheet,Integer firstRow,Integer lastRow,Integer firstCol,Integer lastCol) {
        // 设置第一列为下拉框
        XSSFDataValidationHelper helper = new XSSFDataValidationHelper(sheet);
        XSSFDataValidationConstraint constraint = (XSSFDataValidationConstraint)helper.createExplicitListConstraint(subContent);
        //参数顺序：开始行、结束行、开始列、结束列
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow,lastRow,firstCol,lastCol);
        XSSFDataValidation validation = (XSSFDataValidation)helper.createValidation(constraint, addressList);
        validation.setSuppressDropDownArrow(true);
        validation.setShowErrorBox(true);
        sheet.addValidationData(validation);
    }

导出Excel添加图片

    @Override
    public void exportListExcelImages(HttpServletResponse response, String ids, MultipartFile file) {
        ExcelWriter writer = ExcelUtil.getWriter(true);
        // 图片高度 /行
        int imagesHeight = 15;
        int imageswidth = 6;
        try {
            LambdaQueryWrapper<StartRun> wrapper = new LambdaQueryWrapper<>();
            List<Long> collect = Arrays.stream(ids.split(",")).map(a -> Long.parseLong(a)).collect(Collectors.toList());
            wrapper.in(StartRun::getId,collect);
            wrapper.orderBy(true,false,StartRun::getStartDate);
            List<StartRun> startRuns = startRunMapper.selectList(wrapper);
            // 导出List
            List<StartRunExportVO> startRunExportVOS = new ArrayList<>();
            int seq = 1;
            StartRunExportVO startRunExportVO;
            for(StartRun startRun : startRuns) {
                startRunExportVO = new StartRunExportVO();
                BeanUtils.copyProperties(startRun,startRunExportVO);
                startRunExportVO.setSeq(seq++);
                startRunExportVO.setReconnectionStr(startRun.getReconnectionRate().multiply(new BigDecimal("100"))+"%");
                startRunExportVOS.add(startRunExportVO);
            }
            // 表头
            List<String> headList = CollUtil.newArrayList("序号", "日期", "单租", "重联", "合计", "重联比");
            byte[] bytes = file.getBytes();
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition","attachment;filename=passengerFlow.xlsx");
            response.setCharacterEncoding("utf-8");
            // 获取一个表格
            XSSFSheet sheet = (XSSFSheet)writer.getSheet();
            // 获取工作簿
            XSSFWorkbook workbook = sheet.getWorkbook();
            // 创建画图管理器
            XSSFDrawing drawingPatriarch = sheet.createDrawingPatriarch();
            // 设置图片范围大小
            XSSFClientAnchor anchor = new XSSFClientAnchor(0,0,255,255,(short)0,0,(short)imageswidth,imagesHeight);
            anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
            drawingPatriarch.createPicture(anchor,workbook.addPicture(bytes,XSSFWorkbook.PICTURE_TYPE_JPEG));
            CellStyle headCellStyle = StyleUtil.createHeadCellStyle(workbook);
            CellStyle cellStyle = StyleUtil.createCellStyle(workbook);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 表头赋值
            for (int i = 0; i < headList.size(); i++) {
                Cell cell = writer.getCell(i , imagesHeight+1, true);
                cell.setCellStyle(headCellStyle);
                cell.setCellValue(headList.get(i));
            }
            // 数据赋值
            for(int i = 0; i<startRunExportVOS.size(); i++) {
                StartRunExportVO param1 = startRunExportVOS.get(i);
                Field[] fields = param1.getClass().getDeclaredFields();
                for(int j = 0; j<fields.length; j++) {
                    fields[j].setAccessible(true);
                    DateTimeFormat annotation = fields[j].getAnnotation(DateTimeFormat.class);
                    if(annotation != null) {
                        Cell cell = writer.getCell(j , i+imagesHeight+1+1, true);
                        cell.setCellStyle(cellStyle);
                        cell.setCellValue(DateUtils.formatDate((Date) fields[j].get(param1),"yyyy-MM-dd"));
                        continue;
                    }
                    // 从第7行开始
                    Cell cell = writer.getCell(j , i+imagesHeight+1+1, true);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(fields[j].get(param1).toString());
                }
            }
            // 单独设置列宽、行高
            for(int i=0; i<11; i++) {
                writer.setColumnWidth(i,18);
            }
            // 高度
            int height = imagesHeight + startRunExportVOS.size()+1;
            for(int i=0; i<height; i++) {
                writer.setRowHeight(i,35);
            }
        }catch (Exception e) {
            log.error("开行分析导出列表excecl异常：{}",e.getMessage(),e);
            throw new BusinessException("开行分析导出列表excecl异常");
        }finally {
            ServletOutputStream out= null;
            try {
                out = response.getOutputStream();
            } catch (IOException e) {
                e.printStackTrace();
            }
            writer.flush(out, true);
            // 关闭writer，释放内存
            writer.close();
            //此处记得关闭输出Servlet流
            IoUtil.close(out);
        }
    }