Java操作Excel的两种方式（详细）

不念不忘少年蓝@ 2023-01-04 13:29 163阅读 0赞

**目录**

POI操作

03版excel操作

07版操作

公式操作

poi读操作

EasyExcel操作

写操作

读操作

--------------------

# POI操作 #

poi操作excel分为03版和07版，03版的excel是有长度限制的

最多可以写入65535条数据

## 03版excel操作 ##

1，导入依赖

<dependency>
 <groupId>org.apache.poi</groupId>
 <artifactId>poi</artifactId>
 <version>3.9</version>
 </dependency>
 
 <dependency>
 <groupId>joda-time</groupId>
 <artifactId>joda-time</artifactId>
 <version>2.10.1</version>
 </dependency>

2，最简单的写入

@Test
        //03版excel写操作
        public void test01() throws Exception {
            //创建一个新的工作簿
            Workbook workbook = new HSSFWorkbook();
            //新建一个工作表
            Sheet sheet = workbook.createSheet("导出excel");
            //创建行
            Row row = sheet.createRow(0);
            //创建单元格（列和行交错）
            Cell cell = row.createCell(0);
            //给第一个单元格设置值
            cell.setCellValue("天下第一");
            //创建第二个单元格
            Cell cell1 = row.createCell(1);
            //给第二个单元格设置值
            cell1.setCellValue("1");
            //创建第二行的两列数据
            Row row1 = sheet.createRow(1);
            Cell cell2 = row1.createCell(0);
            cell2.setCellValue("天下第二");
            Cell cell3 = row1.createCell(1);
            cell3.setCellValue("2");
            //创建输出流
            FileOutputStream fs = new FileOutputStream(path+"03版排名.xls");
            //将相应的excel工作簿存盘
            workbook.write(fs);
            fs.close();
            System.out.println("文件生成成功");
        }

3，大数据写入

@Test
 //03版写操作大数据
 public void test03() throws Exception{
 long start = System.currentTimeMillis();
 Workbook workbook = new HSSFWorkbook();
 Sheet sheet = workbook.createSheet("03版操作大数据");
 for (int rowNum = 0 ; rowNum < 65536 ; rowNum++){
 Row row = sheet.createRow(rowNum);
 for (int cellNum = 0 ; cellNum < 10 ;cellNum++){
 Cell cell = row.createCell(cellNum);
 cell.setCellValue(cellNum);
 }
 }
 FileOutputStream fs = new FileOutputStream(path+"03版大数据操作.xls");
 workbook.write(fs);
 System.out.println("文件导出成功");
 long end = System.currentTimeMillis();
 System.out.println((double) (end-start) / 1000 +"秒");
 }

## 07版操作 ##

导入依赖

<dependency>
 <groupId>org.apache.poi</groupId>
 <artifactId>poi-ooxml</artifactId>
 <version>3.9</version>
 </dependency>

1，最简单的写

@Test
        //07版excel写操作
        public void  test02() throws Exception {
            //与03版实现类不同
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("07版excel");
            //第一行数据
            Row row1 = sheet.createRow(0);
            Cell cell1 = row1.createCell(0);
            cell1.setCellValue("字段一");
            Cell cell2 = row1.createCell(1);
            cell2.setCellValue("1");
            //第二行数据
            Row row2 = sheet.createRow(1);
            Cell cell3 = row2.createCell(0);
            cell3.setCellValue("字段二");
            Cell cell4 = row2.createCell(1);
            cell4.setCellValue("2");
            //创建输出流
            FileOutputStream fs = new FileOutputStream(path+"07版.xlsx");
            workbook.write(fs);
            fs.close();
            System.out.println("07文件生成成功");
        }

2，大数据写

@Test
 //07版写操作大数据
 public void test04() throws Exception{
 long start = System.currentTimeMillis();
 Workbook workbook = new SXSSFWorkbook();
 Sheet sheet = workbook.createSheet("07版操作大数据");
 for (int rowNum = 0 ; rowNum < 100000;rowNum++){
 Row row = sheet.createRow(rowNum);
 for (int cellNum = 0 ;cellNum < 5 ;cellNum++){
 Cell cell = row.createCell(cellNum);
 cell.setCellValue(cellNum+1);
 }
 }
 FileOutputStream fs = new FileOutputStream(path+"07版大数据.xlsx");
 workbook.write(fs);
 fs.close();
 long end = System.currentTimeMillis();
 System.out.println((double)(end-start) / 1000 +"秒");
 }

### 公式操作 ###

@Test
        //poi操作读excel中的公式
        public void test05() throws Exception{
            InputStream is = new FileInputStream(path+"计算公式.xls");
            //获取工作簿
            Workbook workbook = new HSSFWorkbook(is);
            //获取第一张工作表
            Sheet sheet = workbook.getSheetAt(0);
            System.out.println(sheet);
            //读取第五行的第一列
            Row row = sheet.getRow(4);
            Cell cell = row.getCell(0);
            //公式计算器
            FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
            //根据单元格的类型输出单元格的内容
            int cellType = cell.getCellType();
            if (cellType== Cell.CELL_TYPE_FORMULA){//2
                //得到公式
                String cellFormula = cell.getCellFormula();
                System.out.println(cellFormula);
                //根据公式计算器计算单元格的值
                CellValue evaluate = formulaEvaluator.evaluate(cell);
                System.out.println(evaluate.formatAsString());
            }
            is.close();
        }

## poi读操作 ##

1，读取不同数据类型

@Test
 //读取excel中不同类型的数据
 public void readExcel() throws Exception{
 InputStream is = new FileInputStream(path+"计算公式.xls");
 Workbook workbook = new HSSFWorkbook(is);
 //获得第一张表的数据
 Sheet sheet = workbook.getSheetAt(0);
 int rows = sheet.getPhysicalNumberOfRows();
 for (int rowNum = 0 ; rowNum < rows;rowNum++){
 Row row = sheet.getRow(rowNum);
 if (row!=null){
 int cells = row.getPhysicalNumberOfCells();
 for (int cellNum = 0 ;cellNum < cells ;cellNum++){
 Cell cell = row.getCell(cellNum);
 if (cell!=null){
 switch (cell.getCellType()){
 case Cell.CELL_TYPE_STRING://字符串
 System.out.print(cell.getStringCellValue());
 break;
 case Cell.CELL_TYPE_NUMERIC://数字
 if (HSSFDateUtil.isCellDateFormatted(cell)){//如果是日期
 Date date = cell.getDateCellValue();
 System.out.print(new DateTime(date).toString("yyyy-MM-dd"));
 }else {
 //防止数字过长将数字转换为字符串输出
 cell.setCellType(Cell.CELL_TYPE_STRING);
 System.out.print(cell.getStringCellValue());
 }
 break;
 case Cell.CELL_TYPE_BOOLEAN://boolean
 System.out.print(String.valueOf(cell.getBooleanCellValue()));
 break;
 case Cell.CELL_TYPE_BLANK://null
 System.out.print("空");
 break;
 case Cell.CELL_TYPE_ERROR:
 System.out.print("数据类型错误");
 break;
 case Cell.CELL_TYPE_FORMULA://公式
 System.out.println(String.valueOf(cell.getNumericCellValue()));
 break;
 }
 }
 }
 }
 System.out.println();
 is.close();
 }
 }

# EasyExcel操作 #

导入依赖

<dependency>
 <groupId>com.alibaba</groupId>
 <artifactId>easyexcel</artifactId>
 <version>2.1.7</version>
 </dependency>
 
 <dependency>
 <groupId>org.projectlombok</groupId>
 <artifactId>lombok</artifactId>
 <version>1.18.12</version>
 </dependency>
 
 <dependency>
 <groupId>com.alibaba</groupId>
 <artifactId>fastjson</artifactId>
 <version>1.2.62</version>
 </dependency>
 
 <dependency>
 <groupId>joda-time</groupId>
 <artifactId>joda-time</artifactId>
 <version>2.10.1</version>
 </dependency>
 
 
 <dependency>
 <groupId>junit</groupId>
 <artifactId>junit</artifactId>
 <version>4.12</version>
 </dependency>

## 写操作 ##

1，Java万物皆对象，创建一个需要写出的对象，这里为了方便用了lombok，value是导出的标题，index是导出的位置，@DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")是格式化导出的日期

@Data
    public class DemoData {
        @ExcelProperty(value = "字符串标题",index = 0)
        private String title;
        @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
        @ExcelProperty(value = "日期标题",index = 1)
        private Date date;
        @ExcelProperty(value = "数字标题",index = 2)
        private Double numberTitle;
        /**
         * 忽略这个字段
         */
        @ExcelIgnore
        private String ignore;
    }

2，测试写入

package com.gong.easyexcel;
 
 import com.alibaba.excel.EasyExcel;
 
 import org.junit.Test;
 
 import java.util.*;
 
 public class WriteTest {
 String path = "C:\\Users\\92036\\Desktop\\测试excel\\";
 //模拟测试数据
 private List<DemoData> data() {
 List<DemoData> list = new ArrayList<DemoData>();
 for (int i = 0; i < 10; i++) {
 DemoData data = new DemoData();
 data.setTitle("字符串" + i);
 data.setDate(new Date());
 data.setNumberTitle(0.56);
 list.add(data);
 System.out.println(data.getTitle()+"-"+data.getDate()+"-"+data.getNumberTitle());
 }
 return list;
 }
 /**
 * 最简单的写
 * 1. 创建excel对应的实体对象 参照{@link DemoData}
 * 2. 直接写即可
 */
 @Test
 public void simpleWrite() {
 // 写法1
 String fileName = path+"easyExcel.xls";
 // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
 // 如果这里想使用03 则 传入excelType参数即可
 //EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
 //只想写出指定的列
 // 根据用户传入字段 假设我们要忽略 date
 /*Set<String> excludeColumnFiledNames = new HashSet<String>();
 excludeColumnFiledNames.add("date");
 EasyExcel.write(fileName, DemoData.class).excludeColumnFiledNames(excludeColumnFiledNames).
 sheet("模板").doWrite(data());*/
 EasyExcel.write(fileName, DemoData.class).
 sheet("模板").doWrite(data());
 System.out.println("写出成功");
 // 写法2
 /*fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
 // 这里 需要指定写用哪个class去写
 ExcelWriter excelWriter = null;
 try {
 excelWriter = EasyExcel.write(fileName, DemoData.class).build();
 WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
 excelWriter.write(data(), writeSheet);
 } finally {
 // 千万别忘记finish 会帮忙关闭流
 if (excelWriter != null) {
 excelWriter.finish();
 }
 }*/
 }
 }

## 读操作 ##

1，创建一个监听器

package com.gong.easyexcel;
 
 import com.alibaba.excel.context.AnalysisContext;
 import com.alibaba.excel.event.AnalysisEventListener;
 import com.alibaba.fastjson.JSON;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 
 import java.util.ArrayList;
 import java.util.List;
 
 // 有个很重要的点 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) {
 System.out.println(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();
 System.out.println("所有数据解析完成");
 }
 
 /**
 * 加上存储数据库
 */
 
 private void saveData() {
 System.out.println(list.size()+"条数据，开始存储数据库！");
 demoDAO.save(list);
 System.out.println("存储数据库成功！");
 }
 }

2，测试读

package com.gong.easyexcel;
 
 import com.alibaba.excel.EasyExcel;
 import com.alibaba.excel.ExcelReader;
 import com.alibaba.excel.read.metadata.ReadSheet;
 import org.junit.Test;
 
 import java.io.File;
 import java.util.ArrayList;
 import java.util.Date;
 import java.util.List;
 
 public class ReadTest {
 String path = "C:\\Users\\92036\\Desktop\\测试excel\\";
 
 /**
 * 最简单的读
 * 1. 创建excel对应的实体对象 参照{@link DemoData}
 * 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link DemoDataListener}
 * 3. 直接读即可
 */
 
 @Test
 public void simpleRead() {
 // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
 // 写法1：
 String fileName = path + "EasyExcel.xls";
 // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
 EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
 
 // 写法2：
 /*fileName = path + "easyExcel.xls";
 ExcelReader excelReader = null;
 try {
 excelReader = EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).build();
 ReadSheet readSheet = EasyExcel.readSheet(0).build();
 excelReader.read(readSheet);
 } finally {
 if (excelReader != null) {
 // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
 excelReader.finish();
 }
 }*/
 }
 }

关于EasyExcel的操作可以参考

[https://www.yuque.com/easyexcel/doc/quickstart][https_www.yuque.com_easyexcel_doc_quickstart]

[https_www.yuque.com_easyexcel_doc_quickstart]: https://www.yuque.com/easyexcel/doc/quickstart