导入Excle数据

1.添加依赖

　　使用poi包完成Excle数据导入：

<!-- POI Excel操作 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.3.1</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</version>
        </dependency>        

 

2.使用MultipartHttpServletRequest、MultipartFile获取文件对象

MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
　　MultipartFile file = entity.getValue();// 获取上传文件对象
　　String fileName = file.getOriginalFilename();
　　try { 
　　　　ExcelResult excelResult = archiveService.docExcelCheck(fileName, file);
　　}catch(Exception e){
　　　　log.error(e.getMessage(),e);
　　}finally { 
　　　　try { 
　　　　　　file.getInputStream().close(); 
　　} catch (IOException e) {
　　　　　　e.printStackTrace(); 
　　} 
}

 

3.验证文件格式

　　.xlsx文件使用HSSFWorkbook类处理，.xls文件使用XSSFWorkbook类处理

public ExcelResult docExcelCheck(String fileName, MultipartFile file) throws Exception {
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            System.out.println("上传文件格式不正确");
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream excelFile = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(excelFile);
        } else {
            wb = new XSSFWorkbook(excelFile);
        }
        return getArchiveExcelResult(wb);
    }

4.获取Excle中数据，保存数据库

private ExcelResult getArchiveExcelResult(Workbook wb) {
        ExcelResult excelResult = new ExcelResult(false, new ArrayList<String>(), new ArrayList<Object>());
        ArrayList<Archive> recordList = new ArrayList<>();
        Sheet sheet = wb.getSheetAt(0);
        for (int row_index = 1; row_index <= sheet.getLastRowNum(); row_index++) {
            Archive archive = new Archive();
            Row row = sheet.getRow(row_index);
            if (row == null) {
                excelResult.setErrStatus(true);
                excelResult.getErrList().add("第" + row_index + "行数据行为空");
            }

            if (row.getCell(0) != null) {
                row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                String title = row.getCell(0).getStringCellValue();
                if (title.length() != 0) {
                    archive.setTitle(title);
                } else {
                    excelResult.setErrStatus(true);
                    excelResult.getErrList().add("第" + row_index + "行第1列案卷题名为空");
                }
            } else {
                excelResult.setErrStatus(true);
                excelResult.getErrList().add("第" + row_index + "行第1列案卷题名为空");
            }


            if (row.getCell(1) != null) {
                row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                String archivalCode = row.getCell(1).getStringCellValue();
                if (archivalCode.length() != 0) {
                    archive.setArchivalCode(archivalCode);
                    //获取第二次出现"-"的位置，截取左侧字符串作为案卷目录
                    String archiveCatalog = archivalCode.substring(0, archivalCode.indexOf("-", archivalCode.indexOf("-") + 1));
                    archive.setArchiveCatalog(archiveCatalog);
                } else {
                    excelResult.setErrStatus(true);
                    excelResult.getErrList().add("第" + row_index + "行第2列档号为空");
                }
            } else {
                excelResult.setErrStatus(true);
                excelResult.getErrList().add("第" + row_index + "行第2列档号为空");
            }
　　　　　　  recordList.add(archive);
            if(recordList.size()==3000){
                this.saveBatch(recordList);
                recordList.clear();
            }
        }
        excelResult.setResult(recordList);
        this.saveBatch(recordList);
        return excelResult;
    }