实现自定义表头之导出功能（使用 SXSSFWorkbook ）

深藏阁楼爱情的钟 2024-03-22 18:34 77阅读 0赞

### 导出 不使用模板 ###

**效果展示**  
![在这里插入图片描述][fbaca1c9630e4a41aa39a2863b0ec8e1.png]

**代码展示：**

public AjaxResult exportTableData(UserColumn userColumn) {
        
    //        获取总列数
            List<UserColumn> userColumns = userColumnMapper.getCloumn(userColumn);
    
    
            try {
        
                //开始创建excel
                SXSSFWorkbook workBook = new SXSSFWorkbook();
                //加粗、垂直
                CellStyle boldStyle = workBook.createCellStyle();
                Font font = workBook.createFont();
                font.setBold(true);
                boldStyle.setFont(font);
    
                //加边框
                CellStyle boldLeftStyle = workBook.createCellStyle();
                boldLeftStyle.setBorderBottom(BorderStyle.MEDIUM); //下边框
                boldLeftStyle.setBorderLeft(BorderStyle.MEDIUM);//左边框
                boldLeftStyle.setBorderTop(BorderStyle.MEDIUM);//上边框
                boldLeftStyle.setBorderRight(BorderStyle.MEDIUM);//右边框
    
    
                //头部颜色
                CellStyle headStyle = workBook.createCellStyle();
                headStyle.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());//设置单元格的背景颜色
                headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//填充单元格
                headStyle.setWrapText(true);// 指定当单元格内容显示不下时自动换行
    
                Font fontTxt = workBook.createFont();
                fontTxt.setColor(HSSFColor.WHITE.index);
                fontTxt.setFontHeightInPoints((short) 18);
                fontTxt.setFontName("微软雅黑");
    
                //垂直、加粗、居中
                CellStyle centerStyle = workBook.createCellStyle();
                Font center_font = workBook.createFont();
                center_font.setBold(true);
                centerStyle.setFont(center_font);
                //centerStyle.setAlignment(CellStyle.ALIGN_CENTER);
                centerStyle.setAlignment(HorizontalAlignment.CENTER);
                //centerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
                centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //        设置sheet
                Sheet sheet = workBook.createSheet("个性化报表");
        //设置总列
                for (int z = 0; z < userColumns.size(); z++) {
        
                    sheet.setColumnWidth(z, 5500);
                }
        //设置第一行
                Row row0 = sheet.createRow(0);
                row0.setHeight((short) 1000);
        //第一行第一列
                row0.createCell(0).setCellStyle(headStyle);
                //第一行第二列
                Cell cell = row0.createCell(1);
                String head = "上海XXX股份有限公司上海分公司" + "\r\n" + "China Telecom Corporation Limited Shanghai Branch";
                cell.setCellValue(head);
                headStyle.setFont(fontTxt);
                cell.setCellStyle(headStyle);
                //合并单元格CellRangeAddress有4个参数：起始行号，终止行号， 起始列号，终止列号
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, userColumns.size()-1));
                //向excel中插入logo图片
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                String picPaht = "C:\\Users\\Bt_202003\\Desktop\\202210\\fsdownloadtempFile\\heng.png";
                File imageFile = new File(picPaht);
                BufferedImage bi = ImageIO.read(imageFile);
                ImageIO.write(bi, "png", baos);
                Drawing drawing = sheet.createDrawingPatriarch();
                XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 1, 1);
                drawing.createPicture(anchor, workBook.addPicture(baos.toByteArray(), SXSSFWorkbook.PICTURE_TYPE_JPEG));
                //设置第二行
                Row row1 = sheet.createRow(1);
    //            设置列
                for (int i = 0; i < userColumns.size(); i++) {
        
                    cell = row1.createCell(i);
                    cell.setCellValue(userColumns.get(i).getFieldName());
                    cell.setCellStyle(boldLeftStyle);
                }
    
    //            填充表数据
                List<KcExcelTable> tables = mapper.getData(userColumns.get(0).getTaskId());
    
                for (int i = 0; i < tables.size(); i++) {
        
                    KcExcelTable kcExcelTable1 = tables.get(i);
    //                创建行
                    Row row = sheet.createRow((i + 2));
    
                    for (int j = 0; j < userColumns.size(); j++) {
        
                        String entityName = userColumns.get(j).getEntityName();
                        //通过传入实体类字段名，获取字段值方法
                        if (null == (kcExcelTable1.get(entityName))) {
        
                            cell = row.createCell(j);
                            cell.setCellValue("");
                            if (!(null == kcExcelTable1.get(entityName))) {
        
                                cell.setCellStyle(boldLeftStyle);
                            }
                        } else {
        
                            cell = row.createCell(j);
                            cell.setCellValue(kcExcelTable1.get(entityName));
                            cell.setCellStyle(boldLeftStyle);
    //                        cell.setCellStyle(boldStyle);
                        }
                    }
    
                }
    
    //            创建写入文件流
                OutputStream os = null;
    
    //        // 创建这个文件夹
    //        try {
        
    //            File execlSaveFile = new File(exportUrl);
    //            if (!execlSaveFile.exists()) {
        
    //                execlSaveFile.mkdirs();
    //            }
    //        } catch (Exception e) {
        
    //            e.printStackTrace();
    //        }
    
    //            导出地址
                String exportUrl = "C:/Users/Bt_202003/Desktop/202210/fsdownload" + FileUtils.getTempPath() + "excel导出" + UUID.randomUUID().toString() + ".xlsx";
                FileUtils.createFile(exportUrl);//创建文件
                //写入文件
                try {
        
                    os = new FileOutputStream(exportUrl);
                    workBook.write(os);
                } catch (Exception e) {
        
                    e.printStackTrace();
                } finally {
        
                    if (null != os) {
        
                        os.close();
                    }
                }
    
            } catch (IOException e) {
        
                e.printStackTrace();
            }
    
            return AjaxResult.success("导出成功");
        }

```java
    //数据表实体类，通过传入实体类字段名，获取字段值方法
        public String get(String name){
        
            String str = "";
            //如果传入的实体类名字与case里面的字符串相等，则调用相应的get方法获取值。
            switch (name){
        
                case "deviceNo":
                    str =  getDeviceNo();
                    break;
                case "billingCycle":
                    str =  getBillingCycle();
                break;
            }
            return str;
        }

[fbaca1c9630e4a41aa39a2863b0ec8e1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/c19d7992bc924fed9479def1bc629564.png