Java POI 导入导出Excel（二）

最新推荐文章于 2023-11-06 20:48:23 发布

tea-Sir

最新推荐文章于 2023-11-06 20:48:23 发布

阅读量266

点赞数

分类专栏： Java基础

本文链接：https://blog.csdn.net/qq_36109477/article/details/105867630

版权

Java基础专栏收录该内容

24 篇文章 0 订阅

订阅专栏

用JavaPOI导出Excel时，我们需要考虑到Excel版本及数据量的问题。针对不同的Excel版本，要采用不同的工具类，如果使用错了，会出现错误信息。

HSSFWorkbook:

poi导出excel最常用的方式
操作Excel2003以前（包括2003）的版本，扩展名是.xls
导出的行数至多为65535行，超出65536条后系统就会报错。此方式因为行数不足七万行所以一般不会发生内存不足的情况（OOM）

XSSFWorkbook:

操作Excel2007的版本，扩展名是.xlsx
这种形式的出现是为了突破HSSFWorkbook的65535行局限。其对应的是excel2007(1048576行，16384列)扩展名为“.xlsx”，最多可以导出104万行，不过这样就伴随着一个问题—OOM内存溢出，原因是你所创建的book
sheet row cell等此时是存在内存的并没有持久化。

SXSSFWorkbook

从POI3.8版本开始，提供了一种基于XSSF的低内存占用的SXSSF方式。对于大型excel文件的创建，一个关键问题就是，要确保不会内存溢出。其实，就算生成很小的excel（比如几Mb），它用掉的内存是远大于excel文件实际的size的。如果单元格还有各种格式（比如，加粗，背景标红之类的），那它占用的内存就更多了。对于大型excel的创建且不会内存溢出的，就只有SXSSFWorkbook了。它的原理很简单，用硬盘空间换内存（就像hashmap用空间换时间一样）。
SXSSFWorkbook是streaming版本的XSSFWorkbook,它只会保存最新的excelrows在内存里供查看，在此之前的excelrows都会被写入到硬盘里（Windows电脑的话，是写入到C盘根目录下的temp文件夹）。被写入到硬盘里的rows是不可见的/不可访问的。只有还保存在内存里的才可以被访问到。

当数据量超出65536条后，在使用HSSFWorkbook或XSSFWorkbook，程序会报OutOfMemoryError：Javaheap space;内存溢出错误。这时应该用SXSSFworkbook。
本文使用了XSSFWorkbook和SXSSFWorkbook两种方式进行Excel导出。
思路：先创建Excel工作簿、显示样式等，再设置表头，接着往表里面插入数据，最后返回输出流。（XSSFWorkbook和SXSSFWorkbook最大的区别就是创建的工作簿不同，其余大同小异）

一、引入poi依赖。

 <!--poi依赖-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

二、定义商品实体类。

package org.teasir.excel.poi.bean;

import java.math.BigDecimal;


public class Goods {
    //商品编码
    private String code;
    //商品名字
    private String name;
    //商品数量
    private BigDecimal amount;
    //商品生产日期
    private String day;

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }


    public BigDecimal getAmount() {
        return amount;
    }

    public void setAmount(BigDecimal amount) {
        this.amount = amount;
    }

    public String getDay() {
        return day;
    }

    public void setDay(String day) {
        this.day = day;
    }
}

三、Excel导出工具类。

package org.teasir.excel.poi;


import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.usermodel.HeaderFooter;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.teasir.excel.poi.bean.Goods;

import java.io.*;
import java.nio.charset.StandardCharsets;
import java.util.*;


public class PoiWrite {

    //导出时设置表单，并生成表单
    private static XSSFSheet genSheet(XSSFWorkbook workbook, String sheetName) {
        //生成表单
        XSSFSheet sheet = workbook.createSheet(sheetName);
        //设置表单文本居中
        sheet.setHorizontallyCenter(true);
        sheet.setFitToPage(false);
        //打印时在底部右边显示文本页信息
        Footer footer = sheet.getFooter();
        footer.setRight("Page " + HeaderFooter.numPages() + " Of " + HeaderFooter.page());
        //打印时在头部右边显示Excel创建日期信息
        Header header = sheet.getHeader();
        header.setRight("Create Date " + HeaderFooter.date() + " " + HeaderFooter.time());
        //设置打印方式
        XSSFPrintSetup ps = sheet.getPrintSetup();
        ps.setLandscape(true); // true：横向打印，false：竖向打印 ，因为列数较多，推荐在打印时横向打印
        ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); //打印尺寸大小设置为A4纸大小
        return sheet;
    }

    //导出时创建文本样式
    private static XSSFCellStyle genContextStyle(XSSFWorkbook workbook) {
        XSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);//文本水平居中显示
        style.setVerticalAlignment(VerticalAlignment.CENTER);//文本竖直居中显示
        style.setWrapText(true);//文本自动换行
        style.setBorderBottom(BorderStyle.THIN);//设置文本边框
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(new XSSFColor(java.awt.Color.BLACK));//设置文本边框颜色
        style.setBottomBorderColor(new XSSFColor(java.awt.Color.BLACK));
        style.setLeftBorderColor(new XSSFColor(java.awt.Color.BLACK));
        style.setRightBorderColor(new XSSFColor(java.awt.Color.BLACK));
        return style;
    }

    //导出时生成标题样式
    private static XSSFCellStyle genTitleStyle(XSSFWorkbook workbook) {

        XSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(true);

        //标题居中，没有边框，所以这里没有设置边框，设置标题文字样式
        XSSFFont titleFont = workbook.createFont();
        titleFont.setBold(true);//加粗
        titleFont.setFontHeight((short) 10);//文字尺寸
        titleFont.setFontHeightInPoints((short) 10);
        style.setFont(titleFont);

        return style;
    }
/*
* 以XSSFWorkbook的格式导出.xlsx文件，适合于数据量不大的情况
* */
    public static ResponseEntity<byte[]> exportGoods2Excel(List<Goods> lgs) throws Exception {
        HttpHeaders headers;
        ByteArrayOutputStream baos;
       try {
            //1.创建Excel文档
            XSSFWorkbook workbook = new XSSFWorkbook();

            //创建Excel表单
            XSSFSheet sheet = genSheet(workbook, "处置信息表");
            //创建标题的显示样式
            XSSFCellStyle titleStyle = genTitleStyle(workbook);//创建标题样式
            titleStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
            titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            // 冻结最左边的两列、冻结最上面的一行
            // 即：滚动横向滚动条时，左边的第一、二列固定不动;滚动纵向滚动条时，上面的第一行固定不动。
            sheet.createFreezePane(0, 1);
            // 创建第一行,作为header表头
            Row headerRow = sheet.createRow(0);
            //根据Excel列名长度，指定列名宽度
            for (int i = 0; i < 4; i++) {
                if (i == 1||i==3) {
                    sheet.setColumnWidth(i, 5000);
                } else {
                    sheet.setColumnWidth(i, 2500);
                }
            }

            //5.设置表头
            Cell cell0 = headerRow.createCell(0);
            cell0.setCellValue("商品编码");
            cell0.setCellStyle(titleStyle);
            Cell cell1 = headerRow.createCell(1);
            cell1.setCellValue("商品名字");
            cell1.setCellStyle(titleStyle);
            Cell cell2 = headerRow.createCell(2);
            cell2.setCellValue("商品数量");
            cell2.setCellStyle(titleStyle);
            Cell cell3 = headerRow.createCell(3);
            cell3.setCellValue("商品生产日期");
            cell3.setCellStyle(titleStyle);


            //6.装数据
            for (int i = 0; i < lgs.size(); i++) {
                Row row = sheet.createRow(i + 1);
                Goods goods = lgs.get(i);
                row.createCell(0).setCellValue(goods.getCode());
                row.createCell(1).setCellValue(goods.getName());
                row.createCell(2).setCellValue(goods.getAmount()==null?"":goods.getAmount().toString());
                row.createCell(3).setCellValue(goods.getDay());

            }
            headers = new HttpHeaders();
            headers.setContentDispositionFormData("attachment",
                    new String("商品信息表.xlsx".getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            baos = new ByteArrayOutputStream();
            workbook.write(baos);
        } catch (IOException e) {
            throw new Exception("导出异常！");
        }
        return new ResponseEntity<byte[]>(baos.toByteArray(), headers, HttpStatus.CREATED);
    }
    private static XSSFCellStyle getCellStyleHeader(SXSSFWorkbook sxssfWorkbook) {

        XSSFCellStyle style = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(true);

        //标题居中，没有边框，所以这里没有设置边框，设置标题文字样式
        XSSFFont titleFont = (XSSFFont) sxssfWorkbook.createFont();
        titleFont.setBold(true);//加粗
        titleFont.setFontHeight((short) 10);//文字尺寸
        titleFont.setFontHeightInPoints((short) 10);
        style.setFont(titleFont);

        return style;

    }
    /*
     * 以SXSSFWorkbook的格式导出.xlsx文件，适合于数据量大的情况
     * */
    public static ResponseEntity<byte[]> exportGoods1Excel(List<Goods> lgs) throws Exception {
        HttpHeaders headers;
        ByteArrayOutputStream baos;
        SXSSFWorkbook workbook;
        try {
            //1.创建Excel文档
            workbook = new SXSSFWorkbook(5000);

            //创建Excel表单
            Sheet sheet = workbook.createSheet("商品信息表");
            // 冻结最左边的两列、冻结最上面的一行
            // 即：滚动横向滚动条时，左边的第一、二列固定不动;滚动纵向滚动条时，上面的第一行固定不动。
            sheet.createFreezePane(0, 1);

            // 创建第一行,作为header表头
            Row headerRow = sheet.createRow(0);

            //创建标题的显示样式
            XSSFCellStyle titleStyle = getCellStyleHeader(workbook);
            //创建标题样式
            titleStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
            titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            //根据Excel列名长度，指定列名宽度
            for (int i = 0; i < 4; i++) {
                if (i == 1||i==3) {
                    sheet.setColumnWidth(i, 5000);
                } else {
                    sheet.setColumnWidth(i, 2500);
                }
            }

            //5.设置表头
            Cell cell0 = headerRow.createCell(0);
            cell0.setCellValue("商品编码");
            cell0.setCellStyle(titleStyle);
            Cell cell1 = headerRow.createCell(1);
            cell1.setCellValue("商品名字");
            cell1.setCellStyle(titleStyle);
            Cell cell2 = headerRow.createCell(2);
            cell2.setCellValue("商品数量");
            cell2.setCellStyle(titleStyle);
            Cell cell3 = headerRow.createCell(3);
            cell3.setCellValue("商品生产日期");
            cell3.setCellStyle(titleStyle);


            //6.装数据
            for (int i = 0; i < lgs.size(); i++) {
                Row row = sheet.createRow(i + 1);
                Goods goods = lgs.get(i);
                row.createCell(0).setCellValue(goods.getCode());
                row.createCell(1).setCellValue(goods.getName());
                row.createCell(2).setCellValue(goods.getAmount()==null?"":goods.getAmount().toString());
                row.createCell(3).setCellValue(goods.getDay());

            }
            headers = new HttpHeaders();
            headers.setContentDispositionFormData("attachment",
                    new String("商品信息表.xlsx".getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            baos = new ByteArrayOutputStream();
            workbook.write(baos);
        } catch (IOException e) {
            throw new Exception("导出异常！");
        }
        return new ResponseEntity<byte[]>(baos.toByteArray(), headers, HttpStatus.CREATED);
    }

}

四、main方法，调用工具类接收返回输出流。

package org.teasir.excel.poi;

import org.springframework.http.ResponseEntity;
import org.teasir.excel.poi.bean.Goods;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class PoiMain {
    public static void main(String[] args) throws Exception {
        /*
        * 2.传入商品列表，返回字节数组输出流，前台导出Excel
        * */
        Goods goods=new Goods();
        goods.setCode("1");
        goods.setName("红烧牛肉面");
        goods.setAmount(new BigDecimal(120));
        goods.setDay("2020/4/30");
        List<Goods> goodsList=new ArrayList<Goods>();
        goodsList.add(goods);
        ResponseEntity<byte[]> responseEntity= PoiWrite.exportGoods1Excel(goodsList);
        ResponseEntity<byte[]> responseEntity2= PoiWrite.exportGoods2Excel(goodsList);
    }
}

Java POI 导入导出Excel（一）

Java POI 导入导出Excel（二）

Java POI 导入导出Excel（三）

源码我已经上传到GitHub，有需要的小伙伴可自行下载。
https://github.com/tea-Sir/poi-excel