easyexcel

导出：

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();
   }
}