导出:
package com.example.demo.excel.demo0; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.format.NumberFormat; import lombok.Builder; import lombok.Data; import java.math.BigDecimal; @Data @Builder public class RespCustomerDailyImport { @ExcelProperty("客户编码") private String customerName; @ExcelProperty("MIS编码") private String misCode; @ExcelProperty("月度滚动额") private BigDecimal monthlyQuota; @ExcelProperty("最新应收账款余额") private BigDecimal accountReceivableQuota; @NumberFormat("#.##%") @ExcelProperty("本月利率(年化)") private BigDecimal dailyInterestRate; }
package com.example.demo.excel.demo0; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy; import com.beust.jcommander.internal.Lists; import com.example.demo.anoationselect.loginannoation.AuthLogin; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.math.BigDecimal; import java.net.URLEncoder; import java.util.List; @Controller @RequestMapping public class Export { @GetMapping("/export0") @AuthLogin public void export(HttpServletResponse response) throws IOException { // 生成数据 List<RespCustomerDailyImport> respCustomerDailyImports = Lists.newArrayList(); for (int i = 0; i < 50; i++) { RespCustomerDailyImport respCustomerDailyImport = RespCustomerDailyImport.builder() .misCode(String.valueOf(i)) .customerName("customerName" + i) .monthlyQuota(new BigDecimal(String.valueOf(i))) .accountReceivableQuota(new BigDecimal(String.valueOf(i))) .dailyInterestRate(new BigDecimal(String.valueOf(i))).build(); respCustomerDailyImports.add(respCustomerDailyImport); } response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系 String fileName = URLEncoder.encode("导出", "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); EasyExcel.write(response.getOutputStream(), RespCustomerDailyImport.class) .sheet("sheet0") // 设置字段宽度为自动调整,不太精确 .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .doWrite(respCustomerDailyImports); } }
导入:
package com.example.demo.excel.demo; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.excel.exception.ExcelDataConvertException; import com.alibaba.excel.util.StringUtils; import com.beust.jcommander.internal.Lists; import java.util.List; /** * 导入监听器 */ public class CustomerDailyImportListener extends AnalysisEventListener { List misCodes= Lists.newArrayList(); @Override public void invoke(Object data, AnalysisContext context) { String misCode = ((ReqCustomerDailyImport) data).getMisCode(); if (StringUtils.isEmpty(misCode)) { throw new RuntimeException(String.format("第%s行MIS编码为空,请核实", context.readRowHolder().getRowIndex() + 1)); } if (misCodes.contains(misCodes)) { throw new RuntimeException(String.format("第%s行MIS编码已重复,请核实", context.readRowHolder().getRowIndex() + 1)); } else { misCodes.add(misCode); } } @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { misCodes.clear(); } @Override public void onException(Exception exception, AnalysisContext context) throws Exception { // ExcelDataConvertException:当数据转换异常的时候,会抛出该异常,此处可以得知第几行,第几列的数据 if (exception instanceof ExcelDataConvertException) { Integer columnIndex = ((ExcelDataConvertException) exception).getColumnIndex() + 1; Integer rowIndex = ((ExcelDataConvertException) exception).getRowIndex() + 1; String message = "第" + rowIndex + "行,第" + columnIndex + "列" + "数据格式有误,请核实"; throw new RuntimeException(message); } else if (exception instanceof RuntimeException) { throw exception; } else { super.onException(exception, context); } } }
package com.example.demo.excel.demo; import com.alibaba.excel.annotation.ExcelProperty; import lombok.Data; import java.math.BigDecimal; /** *导入测试实体 */ @Data public class ReqCustomerDailyImport { @ExcelProperty(index = 0) private String customerName; /** * MIS编码 */ @ExcelProperty(index = 1) private String misCode; /** * 月度滚动额 */ @ExcelProperty(index = 3) private BigDecimal monthlyQuota; /** * 最新应收账款余额 */ @ExcelProperty(index = 4) private BigDecimal accountReceivableQuota; /** * 本月利率(年化) */ @ExcelProperty(index = 5) private BigDecimal dailyInterestRate; }
package com.example.demo.excel.demo; import com.alibaba.excel.converters.Converter; import com.alibaba.excel.enums.CellDataTypeEnum; import com.alibaba.excel.metadata.CellData; import com.alibaba.excel.metadata.GlobalConfiguration; import com.alibaba.excel.metadata.property.ExcelContentProperty; /** * 类型转换器 */ public class StringConverter implements Converter<String> { @Override public Class supportJavaTypeKey() { return String.class; } @Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; } /** * 将excel对象转成Java对象,这里读的时候会调用 * * @param cellData NotNull * @param contentProperty Nullable * @param globalConfiguration NotNull * @return */ @Override public String convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) { return "自定义:" + cellData.getStringValue(); } /** * 将Java对象转成String对象,写出的时候调用 * @param value * @param contentProperty * @param globalConfiguration * @return */ @Override public CellData convertToExcelData(String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) { return new CellData(value); } }
package com.example.demo.excel.demo; import com.alibaba.excel.EasyExcel; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.multipart.MultipartFile; import java.io.IOException; import java.io.InputStream; import java.util.List; /** * 导入测试 */ @Controller @RequestMapping public class Qwert { @PostMapping("/import") public void importCustomerDaily(@RequestParam MultipartFile file) throws IOException { InputStream inputStream = file.getInputStream(); List<ReqCustomerDailyImport> reqCustomerDailyImports = EasyExcel.read(inputStream) .head(ReqCustomerDailyImport.class) // 设置sheet,默认读取第一个 .sheet() // 设置标题所在行数 .headRowNumber(2) .doReadSync(); } @PostMapping("/import0") public void get(@RequestParam MultipartFile file) throws IOException { InputStream inputStream = file.getInputStream(); List<ReqCustomerDailyImport> reqCustomerDailyImports = EasyExcel.read(inputStream) // 这个转换是成全局的, 所有java为string,excel为string的都会用这个转换器。 // 如果就想单个字段使用请使用@ExcelProperty 指定converter .registerConverter(new StringConverter()) // 注册监听器,可以在这里校验字段 .registerReadListener(new CustomerDailyImportListener()) .head(ReqCustomerDailyImport.class) .sheet() .headRowNumber(2) .doReadSync(); } }