Import/Export Excel util, base on apache POI
- Include maven dependency
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.2</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.2</version> </dependency> <dependency> <groupId>com.github.tonyluo</groupId> <artifactId>excel-util</artifactId> <version>1.0.6</version> </dependency> - Define field/cell mapping in javabean:
@ExcelSheet(name="商品列表",colSplit = 4, rowSplit = 1, protectSheet = false) public class Goods { @ExcelCell(col="A",name="商品名") private String name; //商品名 @ExcelCell(col ="B",name="单位",width = 4, align = HorizontalAlignment.RIGHT,comment = "测试B1单元格备注功能") private String unit; //单位 @ExcelCell(col ="C",name="规格",align = HorizontalAlignment.CENTER) private String format; //规格 @ExcelCell(col ="D",name="生产厂家", wrapText= true, width = 4, comment = "测试单元格宽度、自动换行、备注功能") private String factory;//生产厂家 @ExcelCell(col ="E",name="生产时间", dateFormat = "yyyy-MM-dd HH:mm:ss") private Date manufactureTime; @ExcelCell(col="F", name="出厂日期",dateFormat = "MM/dd/yyyy") private Instant productionDate; @ExcelCell(col="G", name="数量", comment = "测试G1单元格备注功能") private int quantity; @ExcelCell(col="H", name="价格",hidden = true,comment = "test hide column") private double price; @ExcelCell(col="I", name="售价",format ="#,##0.00") private Float sellPrice; @Override public String toString() { return "Goods{" + "name='" + name + '\'' + ", unit='" + unit + '\'' + ", format='" + format + '\'' + ", factory='" + factory + '\'' + '}'; } } - Import/Export excel
public class ExcelUtilTest { @Test public void testImportExport() throws IOException, InstantiationException, IllegalAccessException { List<Goods> list = ExcelUtil.importFromPath("src/test/resources/goods.xlsx", Goods.class,1); for (Goods goods : list) { System.out.println(goods); } ExcelUtil.exportToFile("src/test/resources/export-goods.xlsx", list); } }Testing excel file sample: goods.xlsx.
Please refer to test package for more detail: Test package