POIEasyExcel

POI与EasyExcel

• 数据导出excel
• 数据导入程序

POI

• 数据加入内存再操作
• 消耗内存

// 工作簿 2003 65535行
Workbook workbook = new HSSFWorkbook();
// 工作簿 2007 无限制，但耗时较长
Workbook workbook = new XSSFWorkbook();
// 无限制，大量数据速度也快，但会产生临时文件
// 工作表
Sheet sheet = workbook.createSheet("sheet1");
// 行
Row row1 = sheet.createRow(0);
// 列 (1,1)
Cell cell1 = row1.createCell(0);
cell1.setCellValue("1,1");
Workbook workbook = new SXSSFWorkbook();
// 清除临时文件
((SXSSFWorkbook)workbook).dispose(); 

读取

• 注意类型问题，程序中获取时按照类型获取

        <!--03-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>
        <!--07-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</version>
        </dependency>

EasyExcel

• 逐行操作

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.6</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.12</version>
</dependency>

@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
	@ExcelProperty("id")
	private Integer id;
	@ExcelProperty("用户名")
	private String name;
	//@ExcelIgnore 忽略属性
	@ExcelProperty("用户密码")
	private String password;

	public static List<User> create(){
		List<User> list = new ArrayList<User>();
		User u = new User(1,"dzf", "123");
		User u2 = new User(1,"dzf","123456");
		User u3 = new User(2,"gyg","1234");
		list.add(u);
		list.add(u);
		list.add(u3);
		return list;
	}
	public static class UserListener extends AnalysisEventListener<User> {
		List<User> list = new ArrayList<User>();
		@Override
		public void invoke(User data, AnalysisContext context) {
			System.out.println(data);
		}

		@Override
		public void doAfterAllAnalysed(AnalysisContext analysisContext) {
			System.out.println("读入完成");
		}

	}
}

// 写
List<User> list = User.create();
EasyExcel.write("D:/desktop/xxx.xlsx",User.class).sheet("sheetname").doWrite(list);

// 读
EasyExcel.read("D:/desktop/xxx.xlsx",User.class,new User.UserListener()).sheet("sheetname").doRead();

https://alibaba-easyexcel.github.io/index.html