-
Notifications
You must be signed in to change notification settings - Fork 0
Usage
Tony edited this page Feb 13, 2019
·
1 revision
Import/Export Excel util, base on apache POI
- Include maven dependency
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>com.github.tonyluo</groupId>
<artifactId>excel-util</artifactId>
<version>1.0.0</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