java操作Excel
java操作excel一般采用apach的poi或者阿里的EasyExcel
EasyExcel 是阿里巴巴开源的一个excel处理框架,以使用简单、节省内存著称。
EasyExcel 能大大减少占用内存的主要原因是在解析 Excel 时没有将文件数据一次性全部加载到内存中,而是从磁盘上一行行读取数据,逐个解析。
1. POI操作Excel
1.引入相关的依赖
<!--导入poi的包 这个是操作xsl03版的-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!--导入poi-ooxml的包,这个是操作-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<!--导入日期格式化工具-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.2</version>
</dependency>
<!--03版与07版的区别就是03版只能支持65535行数据的读写,07版的则没有上限
导入pom文件的时候,必须注意版本
-->
2. 03版写入excel的代码
public class WriteExcelTest {
String PATH = "F:\\";
@Test
public void excel03Test() throws Exception {
// 创建一个工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 在工作簿下创建表
HSSFSheet sheet = workbook.createSheet("小王的工作表");
// 创建Excel的行和列
/**
* 这个方法里面传入一个int类型参数,表示的是excel的第几行
* 0表示第一行,1表示第二行。。。。以此类推
*/
HSSFRow row1 = sheet.createRow(0);
// 在excel的第一行中创建列
/**
* 同创建行相同,这里的0表示第一列,1表示第二列.....
*/
// 下面这个单元格就表示excel第一行的第一列,坐标(1,1)
HSSFCell cell11 = row1.createCell(0);
// 给(1,1)这个单元格中写入数据
cell11.setCellValue("编号");
// 创建第一行的第二列(1,2),并写入数据
HSSFCell cell12 = row1.createCell(1);
cell12.setCellValue("写入日期");
// 创建工作表的第二行
HSSFRow row2 = sheet.createRow(1);
// 创建第二行的第一列并写入数据
HSSFCell cell21 = row2.createCell(0);
cell21.setCellValue(1);
// 创建工作表的第二行的第二列,并写入数据
HSSFCell cell22 = row2.createCell(1);
cell22.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
// 获取输出流,将工作簿输出
FileOutputStream outputStream = new FileOutputStream(PATH+"chaochao.xls");
workbook.write(outputStream);
// 关闭流
outputStream.close();
}
3.07版写入excel的代码
public class WriteExcelTest07 {
String PATH = "F:\\";
@Test
// 07版的xls测试 07版的03版api相同,只是使用的对象不同
public void write07test() throws Exception{
// 创建一个工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
// 在工作簿下创建表
XSSFSheet sheet = workbook.createSheet("小王的工作表");
// 创建Excel的行和列
/**
* 这个方法里面传入一个int类型参数,表示的是excel的第几行
* 0表示第一行,1表示第二行。。。。以此类推
*/
XSSFRow row1 = sheet.createRow(0);
// 在excel的第一行中创建列
/**
* 同创建行相同,这里的0表示第一列,1表示第二列.....
*/
// 下面这个单元格就表示excel第一行的第一列,坐标(1,1)
XSSFCell cell11 = row1.createCell(0);
// 给(1,1)这个单元格中写入数据
cell11.setCellValue("编号");
// 创建第一行的第二列(1,2),并写入数据
XSSFCell cell12 = row1.createCell(1);
cell12.setCellValue("写入日期");
// 创建工作表的第二行
XSSFRow row2 = sheet.createRow(1);
// 创建第二行的第一列并写入数据
XSSFCell cell21 = row2.createCell(0);
cell21.setCellValue(1);
// 创建工作表的第二行的第二列,并写入数据
XSSFCell cell22 = row2.createCell(1);
cell22.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
// 获取输出流,将工作簿输出
FileOutputStream outputStream = new FileOutputStream(PATH+"chaochao2.xlsx");
workbook.write(outputStream);
// 关闭流
outputStream.close();
}
}
4. 大文件的测试
注意:HSSF最多只能写65535行,否则报错
java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0…65535)
public class WriteBigTest {
String PATH = "F:\\";
@Test
// 使用HSSF测试,这个最多只能生成65535行数据
// 优点:过程中写入缓存,不操作磁盘,最后一次性写入磁盘,速度快
public void Big03Test() throws Exception{
long begin = System.currentTimeMillis();
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("测试HSSF表");
for (int rowNum = 0; rowNum <65536 ; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int colNum = 0; colNum < 10; colNum++) {
Cell cell = row.createCell(colNum);
cell.setCellValue(colNum);
}
}
FileOutputStream outputStream = new FileOutputStream(PATH+"HSSF.xls");
workbook.write(outputStream);
outputStream.close();
long end = System.currentTimeMillis();
System.out.println(end-begin); //2105ms
}
// 使用XFFS测试
// 缺点:写数据时速度非常慢,非常耗内存,也会发生内存溢出,如100万条
// 优点:可以写较大的数据量,如20万条
@Test
public void bigXSSFTest() throws Exception{
long begin = System.currentTimeMillis();
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("测试HSSF表");
for (int rowNum = 0; rowNum <65536 ; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int colNum = 0; colNum < 10; colNum++) {
Cell cell = row.createCell(colNum);
cell.setCellValue(colNum);
}
}
FileOutputStream outputStream = new FileOutputStream(PATH+"XSSF.xlsx");
workbook.write(outputStream);
outputStream.close();
long end = System.currentTimeMillis();
System.out.println(end-begin); //8172ms
}
//使用SXFFS测试
// 可以写非常大的数据量,如100万条甚至更多条,写数据速度快,占用更少的内存
@Test
public void bigSXFFSTest() throws Exception{
long begin = System.currentTimeMillis();
Workbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet("测试HSSF表");
for (int rowNum = 0; rowNum <65536 ; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int colNum = 0; colNum < 10; colNum++) {
Cell cell = row.createCell(colNum);
cell.setCellValue(colNum);
}
}
FileOutputStream outputStream = new FileOutputStream(PATH+"SXSSF.xlsx");
workbook.write(outputStream);
outputStream.close();
// 清理使用SXFFS产生的临时文件
((SXSSFWorkbook) workbook).dispose();
long end = System.currentTimeMillis();
System.out.println(end-begin); //1352ms
}
}
2. EasyExcel操作excel
1.导包
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
2.简单的写的操作
实体类,写出文件时的类型
/**
* @Author xiaoqi
* @Date 2020/6/12 17:26
*/
@Data
public class Student {
@ExcelProperty(value = "姓名")
@ColumnWidth(20) //指定列宽
private String name;
@ExcelProperty(value = "性别")
@ColumnWidth(16)
private String gender;
@ExcelProperty(value = "出生日期")
@ColumnWidth(20)
private Date birthday;
@ExcelIgnore
private String id;
}
准备数据和写出xls文件
/**
* @Author xiaoqi
* @Date 2020/6/12 18:42
*/
public class StudentWriteTest {
// 准备写出的数据源
public List<Student> data(){
ArrayList list = new ArrayList();
for (int i = 0; i <10 ; i++) {
Student student = new Student();
student.setName("nihao"+i);
student.setBirthday(new Date());
student.setGender("男");
list.add(student);
}
return list;
}
// 写的操作
@Test
public void studentWriteTest(){
// 获取工作簿
/**
* 第一个参数:写入的路径
* 第二个参数:写入的类型
*/
ExcelWriterBuilder writerBuilder = EasyExcel.write("xieruceshi.xls", Student.class);
// 获取表
ExcelWriterSheetBuilder sheet = writerBuilder.sheet();
// 准备数据
List<Student> data = data();
// 写入excel
sheet.doWrite(data);
}
}
3.最简单的读的操作
定义监听器
/**
* @Author xiaoqi
* @Date 2020/6/12 17:42
*/
public class StudentListener extends AnalysisEventListener<Student>{
/**
* 每次读取都会执行的方法
* @param student
* @param analysisContext
*/
@Override
public void invoke ( Student student, AnalysisContext analysisContext ) {
System.out.println("student"+student);
}
/**
* 读取完整个文档以后执行的方法
* @param analysisContext
*/
@Override
public void doAfterAllAnalysed ( AnalysisContext analysisContext ) {
}
}
读的操作
/**
* @Author xiaoqi
* @Date 2020/6/12 17:37
*/
public class ReadTest01 {
@Test
public void readTest001(){
/**
* 第一个参数:excel的路径
* 第二个参数:读取的每一行数据要存储到的实体类的额class
* 监听器对象
*/
// 获取工作簿
ExcelReaderBuilder readerBuilder = EasyExcel.read("学生表.xlsx", Student.class, new StudentListener());
// 获取表 这个方法默认读取第一个工作表
ExcelReaderSheetBuilder sheet = readerBuilder.sheet();
// 读取内容
sheet.doRead();
}
}