当前位置：首页 > news >正文

POI与EasyExcel--写Excel

news 2025/9/8 20:36:41

简单写入

03和07版的简单写入注意事项：

1. 对象不同：03对应HSSFWorkbook，07对应XSSFWorkbook

2. 文件后缀不同：03对应xls，07对应xlsx

package com.zrf;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.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;import java.io.FileOutputStream;public class ExcelWriteTest {String PATH = "D:\\ideaprojection\\excel_test\\poi";@Testpublic void testWrite03() throws Exception {// 创建一个工作簿Workbook workbook = new HSSFWorkbook();// 创建一张工作表Sheet sheet = workbook.createSheet("统计表");// 创建第一行Row row1 = sheet.createRow(0);// 创建一个单元格Cell cell11 = row1.createCell(0);cell11.setCellValue("今日新增实习生");Cell cell12 = row1.createCell(1);cell12.setCellValue(1);// 创建第二行Row row2 = sheet.createRow(1);Cell cell21 = row2.createCell(0);cell21.setCellValue("统计时间");Cell cell22 = row2.createCell(1);String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");cell22.setCellValue(time);// 生成一张表（IO流）// 03版本就是使用xls结尾FileOutputStream fileOutputStream = new FileOutputStream(PATH + "统计表03.xls");// 输出workbook.write(fileOutputStream);// 关闭流fileOutputStream.close();System.out.println("统计表03版本生成完毕");}@Testpublic void testWrite07() throws Exception {// 创建一个工作簿Workbook workbook = new XSSFWorkbook();// 创建一张工作表Sheet sheet = workbook.createSheet("统计表");// 创建第一行Row row1 = sheet.createRow(0);// 创建一个单元格Cell cell11 = row1.createCell(0);cell11.setCellValue("今日新增实习生");Cell cell12 = row1.createCell(1);cell12.setCellValue(1);// 创建第二行Row row2 = sheet.createRow(1);Cell cell21 = row2.createCell(0);cell21.setCellValue("统计时间");Cell cell22 = row2.createCell(1);String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");cell22.setCellValue(time);// 生成一张表（IO流）// 03版本就是使用xls结尾FileOutputStream fileOutputStream = new FileOutputStream(PATH + "统计表07.xlsx");// 输出workbook.write(fileOutputStream);// 关闭流fileOutputStream.close();System.out.println("统计表07版本生成完毕");}}

数据批量导入

大文件写HSSF

缺点：最多处理65536行，再多会抛出异常
优点：过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快

大文件写XSSF

缺点：写数据时速度非常慢，非常耗内存，也会发生内存溢出，如100万条
优点：可以写较大的数据量

SXSSF优化版本

            优点：可以写非常大的数据量，如100万甚至更多条，写数据速度更快，占用更少的内存
            注意：过程中会产生临时文件，需要清理临时文件
     默认100条数据被保存到内存中，如果超过这数量，则最前面的数据被写入临时文件，如果想自定义内存中数据的数量，可以使用new SXSSFWorkbook(数量)

package com.zrf;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.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;import java.io.FileOutputStream;public class ExcelWriteTest {String PATH = "D:\\ideaprojection\\excel_test\\poi";@Testpublic void testWrite03BigData() throws Exception {// 时间long begin = System.currentTimeMillis();// 创建一个工作簿Workbook workbook = new HSSFWorkbook();// 创建一张工作表Sheet sheet = workbook.createSheet("统计表");// 写入数据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);}}System.out.println("over");FileOutputStream fileOutputStream = new FileOutputStream(PATH + "testWrite03BigData.xls");workbook.write(fileOutputStream);fileOutputStream.close();long end = System.currentTimeMillis();System.out.println((double) (end - begin)/1000);}// 耗时较长 优化 缓存@Testpublic void testWrite07BigData() throws Exception {// 时间long begin = System.currentTimeMillis();// 创建一个工作簿Workbook workbook = new XSSFWorkbook();// 创建一张工作表Sheet sheet = workbook.createSheet("统计表");// 写入数据for (int rowNum = 0; rowNum < 65537; rowNum++){Row row = sheet.createRow(rowNum);for (int cellNum = 0; cellNum < 10; cellNum++){Cell cell = row.createCell(cellNum);cell.setCellValue(cellNum);}}System.out.println("over");FileOutputStream fileOutputStream = new FileOutputStream(PATH + "testWrite07BigData.xlsx");workbook.write(fileOutputStream);fileOutputStream.close();long end = System.currentTimeMillis();System.out.println((double) (end - begin)/1000);}@Testpublic void testWrite07BigDataS() throws Exception {// 时间long begin = System.currentTimeMillis();// 创建一个工作簿Workbook workbook = new SXSSFWorkbook();// 创建一张工作表Sheet sheet = workbook.createSheet("统计表");// 写入数据for (int rowNum = 0; rowNum < 65537; rowNum++){Row row = sheet.createRow(rowNum);for (int cellNum = 0; cellNum < 10; cellNum++){Cell cell = row.createCell(cellNum);cell.setCellValue(cellNum);}}System.out.println("over");FileOutputStream fileOutputStream = new FileOutputStream(PATH + "testWrite07BigDataS.xlsx");workbook.write(fileOutputStream);fileOutputStream.close();// 清除临时文件((SXSSFWorkbook) workbook).dispose();long end = System.currentTimeMillis();System.out.println((double) (end - begin)/1000);}}

查看全文

http://www.lryc.cn/news/133324.html