<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.2.1</version>
</dependency>
public class EasyExcelExportDemo {
public static void main(String[] args) throws IOException {
EasyExcelExportDemo test = new EasyExcelExportDemo();
String fileName = new File("").getCanonicalPath() + "/report-single.xlsx";
test.write(fileName);
System.out.println("========");
test.read(fileName);
fileName = new File("").getCanonicalPath() + "/report-multi.xlsx";
test.repeatedWrite(fileName);
System.out.println("========");
test.repeatedRead(fileName);
}
public void write(String fileName) {
EasyExcel.write(fileName, ExcelDemoModel.class)
.sheet("模板")
.doWrite(data());
}
public void repeatedWrite(String fileName) {
ExcelWriter excelWriter = null;
WriteSheet writeSheet = null;
excelWriter = EasyExcel.write(fileName).build();
for (int i = 0; i < 5; i++) {
writeSheet = EasyExcel.writerSheet(i, "模板" + i).head(ExcelDemoModel.class).build();
List<ExcelDemoModel> data = data();
excelWriter.write(data, writeSheet);
}
excelWriter.finish();
}
public void read(String fileName) {
EasyExcel.read(fileName, ExcelDemoModel.class, new DemoDataListener()).doReadAll();
}
public void repeatedRead(String fileName) {
try {
ExcelReader excelReader = EasyExcel.read(fileName).build();
ReadSheet readSheet1 =
EasyExcel.readSheet(0).head(ExcelDemoModel.class).registerReadListener(new DemoDataListener()).build();
ReadSheet readSheet2 =
EasyExcel.readSheet(1).head(ExcelDemoModel.class).registerReadListener(new DemoDataListener()).build();
excelReader.read(readSheet1, readSheet2);
} catch (Exception e) {
e.printStackTrace();
}
}
public void download(HttpServletResponse response) throws IOException {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), ExcelDemoModel.class).sheet("模板").doWrite(data());
}
public void upload(MultipartFile file) throws IOException{
EasyExcel.read(file.getInputStream(), ExcelDemoModel.class, new DemoDataListener()).sheet().doRead();
}
private List<ExcelDemoModel> data() {
List<ExcelDemoModel> list = new ArrayList<ExcelDemoModel>();
for (int i = 0; i < 10; i++) {
ExcelDemoModel data = new ExcelDemoModel();
data.setName("字符串" + i);
data.setDate(new Date());
data.setRate(0.56);
data.setFlag(true);
list.add(data);
}
return list;
}
}
public class ConverterCust implements Converter<String> {
public Class<?> supportJavaTypeKey() {
return String.class;
}
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
public String convertToJavaData(ReadConverterContext<?> context) {
return context.getReadCellData().getStringValue();
}
public WriteCellData<?> convertToExcelData(WriteConverterContext<String> context) {
return new WriteCellData<String>("自定义:" + context.getValue());
}
}
public class DemoDataListener implements ReadListener<ExcelDemoModel> {
private List<ExcelDemoModel> list = Lists.newArrayList();
public void invoke(ExcelDemoModel excelDemoModel, AnalysisContext analysisContext) {
list.add(excelDemoModel);
System.out.println("----invoke--" + excelDemoModel.toString());
}
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("----doAfterAllAnalysed--" + list.size());
}
void save(){
}
}
public class ExcelDemoModel {
@ExcelProperty(value = "数字")
@NumberFormat("#.##%")
private Double rate;
@ExcelProperty(value = "字符串", converter = ConverterCust.class)
private String name;
@DateTimeFormat("yyyy-MM-dd HH:mm:ss")
@ExcelProperty(value = "日期")
private Date date;
@ExcelProperty(value = "是否")
private boolean flag;
@Override
public String toString() {
return "ExcelDemoModel{" +
"rate=" + rate +
", name='" + name + '\'' +
", date=" + date +
", flag=" + flag +
'}';
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
public Double getRate() {
return rate;
}
public void setRate(Double rate) {
this.rate = rate;
}
public boolean isFlag() {
return flag;
}
public void setFlag(boolean flag) {
this.flag = flag;
}
}