java Excel 导入代码

最新推荐文章于 2025-04-25 16:42:18 发布

原创最新推荐文章于 2025-04-25 16:42:18 发布 · 221 阅读

0 ·

CC 4.0 BY-SA版权

Java 专栏收录该内容

4 篇文章

订阅专栏

该博客主要展示了如何从上传的Excel文件中读取数据，并利用@Autowired的mapper进行存储操作。首先，通过MultipartFile获取文件并创建Workbook对象，然后遍历工作表中的每一行，根据单元格类型转换数据类型，最后将组织代码插入数据库。

@Autowired
private Mapper mapper

@RequestMapping(value = "/demo/test", method = RequestMethod.POST)
public void test(@RequestParam("file") MultipartFile file, HttpServletRequest request) {
    //创建workbook
    try {
        Workbook workbook = getWorkBook(file);
        //获取第一个表格
        Sheet sheet = workbook.getSheetAt(1);
        //获取数据
        for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (null == row || rowNum < 2) {
                continue;
            }

            String name = row.getCell(0).getStringCellValue();
            Cell cell1 = row.getCell(1);
            Integer type = null;
            switch (cell1.getCellType()) {
                case 0:
                    type = new Double(row.getCell(1).getNumericCellValue()).intValue();
                    break;
                case 1:
                    type = Integer.valueOf(row.getCell(1).getStringCellValue());
                    break;
            }
            Cell cell2 = row.getCell(2);
            String pro = null;
            switch (cell2.getCellType()) {
                case 0:
                    pro = String.valueOf(new Double(row.getCell(2).getNumericCellValue()).intValue());
                    break;
                case 1:
                    pro = row.getCell(2).getStringCellValue();
                    break;
            }
            Cell cell3 = row.getCell(3);
            String city = null;
            switch (cell3.getCellType()) {
                case 0:
                    city = String.valueOf(new Double(row.getCell(3).getNumericCellValue()).intValue());
                    break;
                case 1:
                    city = row.getCell(3).getStringCellValue();
                    break;
            }

            Cell cell4 = row.getCell(4);
            String county = null;
            switch (cell4.getCellType()) {
                case 0:
                    county = String.valueOf(new Double(row.getCell(4).getNumericCellValue()).intValue());
                    break;
                case 1:
                    county = row.getCell(4).getStringCellValue();
                    break;
            }
            mapper.insert(new OrganizationCode(IdsUtil.getUUID32(), name, type, pro, city, county));
        }
    }  catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

/**
 * 获取workbook
 *
 * @param file
 * @return
 */
public Workbook getWorkBook(MultipartFile file) {
    String fileName = file.getOriginalFilename();

    InputStream in = null;
    try {
        in = file.getInputStream();

        Workbook workbook = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        // 判断是excel 03还是07
        if (Const.EXCEL2003L.equals(fileType)) {
            workbook = new HSSFWorkbook(in);
        } else if (Const.EXCEL2007U.equals(fileType)) {
            workbook = new XSSFWorkbook(in);
        } else {
            // 异常
        }
        return workbook;
    }catch (RmcpException re){
        re.printStackTrace();
    }catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (in != null) {
            try {
                in.close();
            } catch (IOException e) {
                e.printStackTrace();
                e.getMessage();
            }
        }
    }
}