POI 导出Excel

最新推荐文章于 2022-06-24 18:08:08 发布

原创最新推荐文章于 2022-06-24 18:08:08 发布 · 354 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#POI Excel导出

文件操作专栏收录该内容

6 篇文章

订阅专栏

一、POI介绍

Java POI有很多组件组成，其中有用于操作Excel格式文件的HSSF和用于操作Word的HWPF,其中常用的的包有：

二、xml引入依赖

<!--读取excel文件-->
<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
</dependency>
<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
</dependency>

三、使用Excel导出实例

导出需要提供要导出的数据，和定义Excel的格式（sheel名，表头，表格数据，宽度，及一些样式等）

controller 层获取输出流，设置文件类型，头信息，文件名等

@PostMapping("bookExcel")
    @ApiOperation("武侠小说列表导出")
    public void getBookExcel(
            HttpServletResponse response,String bookIds
    ) {
        //从response中获取输出流
        try (OutputStream outputStream = response.getOutputStream()) {
            List<Book> bookList =bookService .selectAllExcel(bookIds);
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode("武侠小说汇总导出.xlsx", "UTF-8"));
            boolean success = excelExportService.bookExcel(outputStream, bookList);
            outputStream.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

定义：Excel的表头，填充每个单元格数据，列宽度，sheel名

public boolean bookExcel(OutputStream outputStream, List<Book> bookList){
        List<String[]> content = new ArrayList<>();
        for (Book book : bookList) {
            String[] str=new String[7];
            str[0] = String.valueOf(book.getBookId());
            str[1] = String.valueOf(isNull(book.getBookname()));
            str[2] = String.valueOf(isNull(book.getAuthorname()));
            str[3] = String.valueOf(isNull(book.getMainperson()));
            str[4] = String.valueOf(isNull(book.getDepartment()));
            str[5] = String.valueOf(isNull(book.getEsoterica()));
            str[6] = String.valueOf(isNull(book.getCreatetime()));
            content.add(str);
        }
        int[] widths = {3000,6000,4000,8000,8000,8000,4000};
        List<String> title = new ArrayList<>();
        title.add("编号ID");
        title.add("书名");
        title.add("作者");
        title.add("主要人物");
        title.add("主要门派");
        title.add("出现武功高");
        title.add("出版日期");
        Workbook sxssfWorkbook=excelExportUtils.export(title,"书籍导出", widths, content);
        // 数据写入
        try {
            sxssfWorkbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

通用的Excel导出方法：

public SXSSFWorkbook export(List<String> title, String name, int[] widths, List<String[]> content) {
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();
        CellStyle titleStyle = titleStyle(sxssfWorkbook);
        CellStyle tableStyle = tableStyle(sxssfWorkbook);
        //单元名：
        SXSSFSheet sheet = sxssfWorkbook.createSheet(name);
        //每一列的宽度
        for (int i = 0; i < widths.length; i++) {
            sheet.setColumnWidth(i, widths[i]);
        }
        //设置标题
        SXSSFRow row =sheet.createRow(0);
        Cell cell = null;
        for (int i = 0; i < title.size(); i++) {
            cell = row.createCell(i);
            cell.setCellStyle(titleStyle);
            cell.setCellValue(title.get(i));
        }
        //设置内容
        if (content != null) {
            int num = 1;
            for (String[] contents : content) {
                row = sheet.createRow(num++);
                for (int i = 0; i < contents.length; i++) {
                    cell = row.createCell(i);
                    cell.setCellStyle(tableStyle);
                    cell.setCellValue(contents[i]);
                }
            }
        }
        return sxssfWorkbook;
    }

设置表格内容样式：

public CellStyle titleStyle(SXSSFWorkbook workbook) {
       CellStyle titleStyle = workbook.createCellStyle();
       //设置字体
       Font font =workbook.createFont();
       font.setFontName("宋体");//设置字体
       font.setBold(true);//字体加粗
       font.setItalic(false);//字体是否倾斜
       font.setFontHeightInPoints((short)22);//设置字体大小
       font.setColor(IndexedColors.BLACK.index);//设置字体颜色
       titleStyle.setFont(font);
       //设置颜色：
                    //设置前景颜色
       titleStyle.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.index);
                    //设置颜色填充规则
       titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
       //设置对齐方式：
       titleStyle.setAlignment(HorizontalAlignment.CENTER);
       titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
       //设置边框样式：
       titleStyle.setBorderTop(BorderStyle.THIN);
       titleStyle.setBorderBottom(BorderStyle.THIN);
       titleStyle.setBorderLeft(BorderStyle.THIN);
       titleStyle.setBorderLeft(BorderStyle.THIN);
       //设置边框颜色：
       titleStyle.setBottomBorderColor(IndexedColors.BLACK.index);
       titleStyle.setTopBorderColor(IndexedColors.BLACK.index);
       titleStyle.setLeftBorderColor(IndexedColors.BLACK.index);
       titleStyle.setRightBorderColor(IndexedColors.BLACK.index);
       return titleStyle;
   }

导出带分组的Excel：

@Data
public class ExcelVo {
    //每一行数据
    private List<String> content = new ArrayList<>();
    //该行对应的子集数据
    private List<ExcelVo> excelVos = new ArrayList<>();
}

设置数据：

public boolean ExcelGroup(OutputStream outputStream, List<Book> bookList){
    // 设置数据
    List<ExcelVo> excelVoList = new ArrayList<>();
    if (bookList != null) {
        for (Book book : bookList) {
            ExcelVo excelVo = new ExcelVo();
            excelVo.getContent().add(String.valueOf(book.getBookId()));
            excelVo.getContent().add(book.getBookname());
            excelVo.getContent().add(book.getAuthorname());
            excelVo.getContent().add(book.getMainperson());
            excelVo.getContent().add(book.getDepartment());
            excelVo.getContent().add(book.getEsoterica());
            excelVoList.add(excelVo);
            List<Characters> list1 = characterMapper.selectBySource(book.getBookname());
            if (list1 != null) {
                //获取二级
                for (Characters ch : list1) {
                    ExcelVo secondExcelVo = new ExcelVo();
                    secondExcelVo.getContent().add(String.valueOf(ch.getCharacterId()));
                    secondExcelVo.getContent().add(ch.getName());
                    secondExcelVo.getContent().add(ch.getEsoterica());
                    secondExcelVo.getContent().add(ch.getDepartment());
                    secondExcelVo.getContent().add("-");
                    secondExcelVo.getContent().add("-");
                    excelVo.getExcelVos().add(secondExcelVo);
                }
            }
        }
    }
    int[] widths = {3000,6000,4000,8000,8000,8000,4000};
    List<String> title = new ArrayList<>();
    title.add("编号ID");
    title.add("书名");
    title.add("作者");
    title.add("主要人物");
    title.add("主要门派");
    title.add("出现武功");
    Workbook sxssfWorkbook=excelExportUtils.exportGroup(title,"书籍导出", widths, excelVoList);
    // 数据写入
    try {
        sxssfWorkbook.write(outputStream);
    } catch (IOException e) {
        e.printStackTrace();
        return false;
    }
    return true;
}

调用Excel生成方法：

public Workbook exportGroup(List<String> title, String name, int[] widths,
                            List<ExcelVo> excelVoList) {
    SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(-1);
    Sheet sheet = sxssfWorkbook.createSheet(name);
    CellStyle tableStyle = tableStyle(sxssfWorkbook);
    CellStyle titleStyle = titleStyle(sxssfWorkbook);
    CellStyle leftStyle = titleStyle(sxssfWorkbook);
    //设置每一列单元格宽度
    for (int i = 0; i < widths.length; i++) {
        sheet.setColumnWidth(i, widths[i]);
    }
    Row row = sheet.createRow(0);
    Cell cell = null;
    for (int i = 0; i < title.size(); i++) {
        cell = row.createCell(i);
        cell.setCellStyle(titleStyle);
        cell.setCellValue(title.get(i));
    }
    if (excelVoList != null && excelVoList.size() > 0) {
        exportGroup(sheet, excelVoList, 1, tableStyle, leftStyle);
    }
    //将组合的"-"号显示在上方
    sheet.setRowSumsBelow(false);
    return sxssfWorkbook;
}

public int exportGroup(Sheet sheet, List<ExcelVo> excelVoList, int num, 
                       CellStyle tableStyle, CellStyle leftStyle) {
    Row row = null; Cell cell = null;
    for (ExcelVo excelVo : excelVoList) {
        row = sheet.createRow(num++);
        //记录分组开始行号
        int start = num;
        for (int i = 0; i < excelVo.getContent().size(); i++) {
            cell = row.createCell(i);
            if (i == 0) {     
                cell.setCellStyle(leftStyle); //第一列数据左对齐
            } else {
                cell.setCellStyle(tableStyle);
            }
            cell.setCellValue(Optional.ofNullable(excelVo.getContent().get(i)).orElse(""));
        }
        //如果改行有子集数据，填充子集数据，获取分组结束行号
        if (excelVo.getExcelVos() != null && excelVo.getExcelVos().size() > 0) {
            num = exportGroup(sheet, excelVo.getExcelVos(), num, tableStyle, leftStyle);
            //行分组
            sheet.groupRow(start, num - 1);
            //设置默认收缩
            sheet.setRowGroupCollapsed(start,true);
        }
    }
    return num;
}