csv以及excel文件解析

<!-- csv文件解析依赖 -->
<dependency>
   <groupId>com.opencsv</groupId>
   <artifactId>opencsv</artifactId>
   <version>4.3.2</version>
</dependency>
<dependency>
   <groupId>org.projectlombok</groupId>
   <artifactId>lombok</artifactId>
   <scope>compile</scope>
</dependency>

 

@RequestMapping(value = "/file/upload", method = RequestMethod.POST)
@Response
public BaseResult batchInsert(@RequestPart("file") MultipartFile file, String charsetName) {
    //excel默认转换成csv文件编码方式是gbk,文件限制了大小，太多文件分多次上传
    if (StringUtil.isEmpty(charsetName)) {
        charsetName = CHARSET_NAME_GBK;
    }

    BaseResult baseResult = new BaseResult();
    CsvUtil csvUtil = new CsvUtil();
    // 将csv文件内容转成bean
    List<DataBarrageFillersCsv> csvData = csvUtil.getCsvData(file, DataBarrageFillersCsv.class, charsetName);

    if (CollectionUtils.isEmpty(csvData)) {
        return baseResult;
    }

.....

}

public class CsvUtil {
    /**
     * 日志对象
     */
    private static final Logger LOGGER = LoggerFactory.getLogger(CsvUtil.class);

    /**
     * 解析csv文件并转成bean
     *
     * @param file  csv文件
     * @param clazz 类
     * @param <T>   泛型
     * @return 泛型bean集合
     */
    public <T> List<T> getCsvData(MultipartFile file, Class<T> clazz, String charsetName) {
        BOMInputStream bomInputStream = null;
        try {
            bomInputStream = new BOMInputStream(file.getInputStream());
        } catch (Exception e) {
            LOGGER.error("getCsvData transfer BOMInputStream error,", e);
        }

        HeaderColumnNameMappingStrategy<T> strategy = new HeaderColumnNameMappingStrategy<>();
        strategy.setType(clazz);

        CsvToBean<T> csvToBean = null;
        try {
            csvToBean = new CsvToBeanBuilder<T>(new InputStreamReader(bomInputStream, charsetName))
                    .withSeparator(',')
                    .withQuoteChar('\'')
                    .withQuoteChar('\"')
                    .withMappingStrategy(strategy).build();
        } catch (UnsupportedEncodingException e) {
            LOGGER.error("getCsvData charsetName is  UnsupportedEncodingException error,", e);
        }
        return csvToBean.parse();
    }

@Data
public class DataBarrageFillersCsv {

    @CsvBindByName(column = "content")
    private String content;

    @CsvBindByName(column = "fill_pos")
    private Integer fillPos;

    @CsvBindByName(column = "type")
    private Integer type;

    @CsvBindByName(column = "barrage_color")
    private Integer barrageColor;

}

id,content,fill_pos,type,barrage_color,created_at,updated_at,is_deleted
1,"ce测试1,!!",1,3,0,07/29/2020 11:08:49,07/29/2020 11:08:49,0

https://blog.csdn.net/qq_31289187/article/details/86104522

excel解析：

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.5</version>
</dependency>

@Override
public void processInvoke() {
    log.info("start to do task...");
    try {
        File file = ResourceUtils.getFile("classpath:cXX.xlsx");
        EasyExcel.read(file, pictureUploadModel.class, new PictureExcelReadListener(PictureUploadService)).sheet().doRead();
    } catch (Exception e) {
        log.error("do task error.", e);
    }
}

/**
 * @Author chong.wang@ximalaya.com
 * @Date 2020/12/15
 */
public class PictureExcelReadListener extends AnalysisEventListener<ChannelPictureUploadModel> {

    private final Logger logger = LoggerFactory.getLogger(getClass());

    private ChannelPictureUploadService channelPictureUploadService;

    public PictureExcelReadListener(ChannelPictureUploadService channelPictureUploadService) {
        this.channelPictureUploadService = channelPictureUploadService;
    }

    @Override
    public void invoke(PictureUploadModel data, AnalysisContext context) {
        logger.info("读取第{}条数据", context.readRowHolder().getRowIndex());
        channelPictureUploadService.processData(data);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        logger.info("数据处理完成......");
    }

}

@Data
public class PictureUploadModel {

    @ExcelProperty(index = 0)
    private String groupName;

    @ExcelProperty(index = 1)
    private Long groupPosition;
   
}

 

public void processData(ChannelPictureUploadModel data) {
    try {
        String filePath =".png";
      
        ChannelGroupModel param = assembleParam(data, coverUploadRes);
        doHttpProcess(param);
    } catch (Exception e) {
        logger.error("upload channelImage error. data:{}", data.toString(), e);
    }
}