EasyExcel+Web 读取数据保存到数据库完整代码

wuchangjian2021-11-05 15:23:29编程学习

1.功能描述

这里我的需求是从选择本地文件读取excel里面的固定格式的数据保存到数据库

2.技术

Spring boot+angular(primeNg)

3.代码展示

页面组件
<input type="file" accept=".csv, application/vnd.ms-excel, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" value="" id="input_file"
                               (change)="fileChange($event, [(titleEnum)])"/>
点击事件方法
fileChange(event: any, fileType: any) {
        this.progress = true;
        const files = event.target.files;
        this.commonService.uploadFile(files, `aaa/${this.id}/`, fileType).pipe(
            finalize(() => {
                this.progress = false;
                const inputFile = document.getElementById('input_file');
                inputFile['value'] = '';
            })
        ).subscribe(data => {
                this.files.push(data);
                this.callFileChangeEmit();
            }
            , error => {
                if (typeof error === 'string') {
                    this.messageService.add({key: 'upload-file-c', severity: 'error', detail: error});
                }
            });
    }
调用接口方法
uploadFile(files: any[], directory?: string, fileType?: string) {
        const formData = new FormData();
        for (const file of files) {
            formData.append('file', file);
        }
        formData.append('directory', directory);
        formData.append('sourceFileName', fileType);
        return this.http.post<any>(`/common/upload/file`, formData);
    }
Controller
@PostMapping("/upload/file")
public ResponseEntity<Map<String, String>> uploadFile(@RequestPart("file") MultipartFile file, @RequestParam String directory,@RequestParam(required = false) String sourceFileName) {
        try {
            orderInitService.importOrderInfoOfExcel(file);
        } catch (IOException e) {
            log.info("Error importing order with message: {}", e.getMessage());
        }
        Map<String, String> result = new HashMap<>();
        result.put("result", "OK");
        return new ResponseEntity(result, HttpStatus.OK);
    }
Service
void easyExcelRead(MultipartFile file) throws IOException;
ServiceImpl
 @Override
    public void easyExcelRead(MultipartFile file)  {
        try{
            log.info("Importing excel info of : {}", file);
            EasyExcel.read(file.getInputStream(), ExcelDataEntity.class, new ExcelDataInitImportListener(excelDataEntityRepo)).sheet().doRead();
        }catch (IOException e){
            e.printStackTrace();
            log.info("excel init file IOException");
        };
    }
Listener
@Slf4j
public class OrderInfoInitImportListener extends AnalysisEventListener<OrderInfoEntity> {
    /**
     * 新增列表
     */
    List<OrderInfoEntity> addList = new ArrayList<OrderInfoEntity>();

    /**
     * Jpa repository
     */
    private final ExcelDataEntityRepoexcelDataEntityRepo;


    /**
     * 每隔10条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
     */
    private static final int BATCH_COUNT = 100;

    /**
     * Constructor
     * @param excelDataEntityRepo base repository
     */
    public OrderInfoInitImportListener(ExcelDataEntityRepo excelDataEntityRepo) {
         this.excelDataEntityRepo= excelDataEntityRepo;
    }

    /**
     * 这个每一条数据解析都会来调用
     * @param data row
     * @param context all data
     */
    @Override
    public void invoke(OrderInfoEntity data, AnalysisContext context) {
        log.info("One Excel Data: {}", data);
        addList.add(data);
        if (addList.size() >= BATCH_COUNT) {
            saveData();
            addList.clear();
        }
    }

    /**
     * 保存数据
     */
    private void saveData() {
        log.info("{}条Excel Data,开始存储数据库...", addList.size());
        if (addList.isEmpty()) {
            return;
        }
        addList.stream().map(item -> {
            item.setCTime(DateTimeUtils.getDateTimeStr(new Date()));
            item.setUTime(DateTimeUtils.getDateTimeStr(new Date()));
            return item;
        }).forEach(excelDataEntityRepo::save);
        log.info("存储数据库成功!");
    }

    /**
     * 所有数据解析完成了 都会来调用
     * @param context all data
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
    }
}

相关文章

JAVA(2021-11-2)leetcode每日一题----删除链表中的节点

力扣https://leetcode-cn.com/problems/delete-...

结尾看开始

结尾看开始

结尾看开始 2021-12-06 股市理性投资 价值投资看人性 大环...

胃经常不舒服?教你如何养成健康胃

  现代人生活节奏快、工作压力大,再加上无节制的饮食,导致许...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。