Java中Excel导出

小菜鸡0626

已于 2022-05-20 16:50:26 修改

阅读量819

点赞数

分类专栏：辅助技术 SpringBoot java 文章标签： java poi excel

于 2021-03-12 10:09:01 首次发布

本文链接：https://blog.csdn.net/WaY0626/article/details/114686912

版权

SpringBoot 同时被 3 个专栏收录

14 篇文章 0 订阅

订阅专栏

java

10 篇文章 0 订阅

订阅专栏

辅助技术

6 篇文章 1 订阅

订阅专栏

1.Poi

POI是Apache软件基金会的，POI为“Poor Obfuscation Implementation”的首字母缩写，意为“简洁版的模糊实现”。
所以POI的主要功能是可以用Java操作Microsoft Office的相关文件

1 .导入依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        //下面是07(xlsx)版本的，上面是03(xls)
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>12345678910

2 .开启读写操作，代码走起

无非就是对api的充分认识，接下来我们先去了解他的api

 Workbook wordkbook =new HSSFWorkbook();//创建一个Workbook对象
 wordkbook.createSheet();//创建表名，如果不写参数，会有默认值
 Row row1=sheet.createRow(0);//根据里面的数字拿到对应的行，0默认为第一行
 Cell cell = row1.createCell(0);//根据行对象创建单元格，这里0为第一个
 cell.setCellValue("");//可以给单元格赋值

写入Excel

package com.example.test;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWriter {
    //先要有个路劲
    static String path="F:\\demo\\javapoi\\demopoi\\";
    public static void main(String[] args) throws IOException {
        //1，创建一个工作薄
        Workbook wordkbook =new HSSFWorkbook();
        //表名
        Sheet sheet=wordkbook.createSheet("灰灰统计表");
        //创建行
        Row row1=sheet.createRow(0);
        //4.创建一个单元格
        Cell cell = row1.createCell(0);
        cell.setCellValue("今日新增观众");
        Cell cell2 = row1.createCell(1);
        cell2.setCellValue("卢本伟");

        //创建行
        Row row2=sheet.createRow(1);
        //4.创建一个单元格
        Cell cell3 = row2.createCell(0);
        cell3.setCellValue("统计时间");
        Cell cell24= row2.createCell(1);
        String time=new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell24.setCellValue(time);

        //生成一张表 03是xls 07是xlsx
        FileOutputStream fileOutputStream = new FileOutputStream(path + "lele统计表07.xlsx");
       wordkbook.write(fileOutputStream);

       fileOutputStream.close();
        System.out.println("灰灰统计表03已生成");
    }

}

上面写完后会在项目目录下生成一个表格

读取Excel

这个操作跟上述的写并没有什么不同，不同就是方法是get而不是set

 static String path="F:\\demo\\javapoi\\demopoi";
    @Test
    public void testread03() throws IOException {


        //Sheet sheet=workbook.createSheet("统计表");
        //sheet操作表中元素
        FileInputStream fileInputStream = new FileInputStream(path + "灰灰统计表03.xls");
        Workbook workbook=new HSSFWorkbook(fileInputStream);
        Sheet sheet = workbook.getSheetAt(0);
        Sheet sheet2 = workbook.getSheet("灰灰统计表");
        Row row = sheet.getRow(1);
        Cell cell = row.getCell(0);
        Cell cell2 = row.getCell(1);
        System.out.println(cell.getStringCellValue());
        System.out.println(cell2.getNumericCellValue());
        fileInputStream.close();
    }1234567891011121314151617

这里值得注意的是，使用表格对象要注意三种创建方式

POI-HSSF
POI-XSSF
SXSSF

**HSSF：*Excel97-2003版本，扩展名为.xls。一个sheet最大行数*65536，最大列数256。

**XSSF：*Excel2007版本开始，扩展名为.xlsx。一个sheet最大行数*1048576，最大列数16384。

SXSSF：**是在XSSF基础上，POI3.8版本开始提供的**支持低内存占用的操作方式，扩展名为.xlsx。

Excel版本兼容性是向下兼容。

在读取数据的时候我们需要先判断值类型，才能用对应API

下面这个是先拿到表头那一行，相当于数据库的字段

        FileInputStream fileInputStream = new FileInputStream(path + "数据表07.xlsx");
        Workbook workbook=new XSSFWorkbook(fileInputStream);
        Sheet sheet = workbook.getSheetAt(0);
        Row rowTitle = sheet.getRow(0);
        if(rowTitle!=null){
            int cellCount=rowTitle.getPhysicalNumberOfCells();    //拿到第row行的那一行的总个数
            for (int i = 0; i <cellCount ; i++) {  //循环个数取出
                Cell cell = rowTitle.getCell(i);
                if(cell!=null){          //如果不等于空取出值
                    int cellType = cell.getCellType();   //这里是知道我们标题是String，考虑不确定的时候怎么取
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue+"|");
                }
            }
            System.out.println();
        }

下面接着读取对应的数据，这里就需要我们刚刚讲的类型判断

int cellType=cell.getCellType();利用这个，然后判断它的XSSFCell类型再具体输出

  //获取表中内容
        int rowCount=sheet.getPhysicalNumberOfRows();
        for(int rowNum=1;rowNum<rowCount;rowNum++){
            Row rowData=sheet.getRow(rowNum); //取出对应的行
            if(rowData!=null){
                int cellCount=rowTitle.getPhysicalNumberOfCells();
                for(int cellNum=0;cellNum<cellCount;cellNum++){
                    System.out.print("["+(rowNum+1+"-"+(cellNum+1)+"]"));
                    Cell cell = rowData.getCell(cellNum);
                    //匹配数据类型
                    if(cell!=null){
                        int cellType=cell.getCellType();
                        switch (cellType){
                            case XSSFCell.CELL_TYPE_STRING: System.out.print("字符串："+cell.getStringCellValue());break;
                            case XSSFCell.CELL_TYPE_BOOLEAN: System.out.print("布尔："+cell.getBooleanCellValue());break;
                            case XSSFCell.CELL_TYPE_NUMERIC:
                                if(HSSFDateUtil.isCellDateFormatted(cell)){
                                    System.out.println("日期格式："+new DateTime(cell.getDateCellValue()).toString("yyyy-MM-dd HH:mm:ss"));break;
                                }else
                                    cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                                System.out.print("整形："+cell.toString());break;
                            case XSSFCell.CELL_TYPE_BLANK: System.out.print("空");break;
                            case XSSFCell.CELL_TYPE_ERROR: System.out.print("数据类型错误");break;
                            case Cell.CELL_TYPE_FORMULA:
                String formula=cell.getCellFormula();
                System.out.println("公式:"+formula);

                //
                CellValue evaluate = formulaEvaluator.evaluate(cell);
                String cellValue=evaluate.formatAsString();
                System.out.println(cellValue);
                break;
                            default:break;
                        }
                    }
                }
            }
        }


        fileInputStream.close();

计算属性

//获取计算公式eval
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
//输出单元格的内容
int cellType=cell.getCellType();
switch(cellType){
    case Cell.CELL_TYPE_FORMULA://公式
        String fomula=cell.getCellFormula();
        System.out.println(fomula);
        
        //计算
        CellValue value =evaluator.evaluate(cell);
}

紧接着我们去讲EasyExcel

EasyExcel

导入依赖

<dependency>
	<groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.6</version>
</dependency>

实体类

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import java.util.Date;

@Data   //lombok
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore   //注意这个注解是高版本的easyexcel依赖才有
    private String ignore;
}

工具类

public class utilList {
    public static List<DemoData> data() {
        List<DemoData> list = new ArrayList<DemoData>();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + i);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }
}

测试类

/**
     * 最简单的写
     * <p>1. 创建excel对应的实体对象 参照{@link com.alibaba.easyexcel.test.demo.write.DemoData}
     * <p>2. 直接写即可
     */
    @Test
    public void simpleWrite() {
        String fileName = TestFileUtil.getPath() + "write" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去读，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        //sheet(表名)
        //data(数据)
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
    }

监听类

// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class DemoDataListener extends AnalysisEventListener<DemoData> {
    private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<DemoData> list = new ArrayList<DemoData>();
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private DemoDAO demoDAO;
    public DemoDataListener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        demoDAO = new DemoDAO();
    }
    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param demoDAO
     */
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }
    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data
     *            one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }
    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
    }
    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        demoDAO.save(list);
        LOGGER.info("存储数据库成功！");
    }
}

简单读

 /**
     * 读多个或者全部sheet,这里注意一个sheet不能读取多次，多次读取需要重新读取文件
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link DemoData}
     * <p>
     * 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link DemoDataListener}
     * <p>
     * 3. 直接读即可
     */
    @Test
    public void repeatedRead() {
        String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
        // 读取全部sheet
        // 这里需要注意 DemoDataListener的doAfterAllAnalysed 会在每个sheet读取完毕后调用一次。然后所有sheet都会往同一个DemoDataListener里面写
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).doReadAll();

        // 读取部分sheet
        fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
        ExcelReader excelReader = null;
        try {
            excelReader = EasyExcel.read(fileName).build();

            // 这里为了简单 所以注册了 同样的head 和Listener 自己使用功能必须不同的Listener
            ReadSheet readSheet1 =
                EasyExcel.readSheet(0).head(DemoData.class).registerReadListener(new DemoDataListener()).build();
            ReadSheet readSheet2 =
                EasyExcel.readSheet(1).head(DemoData.class).registerReadListener(new DemoDataListener()).build();
            // 这里注意 一定要把sheet1 sheet2 一起传进去，不然有个问题就是03版的excel 会读取多次，浪费性能
            excelReader.read(readSheet1, readSheet2);
        } finally {
            if (excelReader != null) {
                // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
                excelReader.finish();
            }
        }
    }

自定义转化器

@Data
public class ConverterData {
    /**
     * 我自定义 转换器，不管数据库传过来什么 。我给他加上“自定义：”
     */
    @ExcelProperty(converter = CustomStringStringConverter.class)
    private String string;
    /**
     * 这里用string 去接日期才能格式化。我想接收年月日格式
     */
    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    private String date;
    /**
     * 我想接收百分比的数字
     */
    @NumberFormat("#.##%")
    private String doubleData;
}