如何在spring mvc中使用java导入xls和xlsx文件 [英] How to import both xls and xlsx files using java in spring mvc

问题描述

在这种方法中，我使用了用于读取 xlsx 文件的 xssf 类，但我们不能为 xls 文件执行此操作.对于 xls，我们需要有 Hssf 类.用户可以在那里导入任何格式.我的要求，是否有任何类可以用来代替 xssf 和 hssf 来读取这两种文件.在我的示例中，我使用了 xssf.

In this method i used xssf class which is used to read xlsx file but we cant do it for xls file.for xls we need to have Hssf class .User can import any format there .My requirement,Is there any Class that can be used instead of xssf and hssf to read both kind of file. in my example i used xssf.

  @RequestMapping(value="/import",method = RequestMethod.POST)
     public ModelAndView imports(Model model, @RequestParam("excelFile") MultipartFile excelfile){
        try {
            List<DepartmentModel> lstUser = new ArrayList<>();
            int i = 0;

            XSSFWorkbook workbook = new XSSFWorkbook(excelfile.getInputStream());

            XSSFSheet worksheet = workbook.getSheetAt(0);
            while (i <= worksheet.getLastRowNum()) {

                DepartmentModel user = new DepartmentModel();

                XSSFRow row = worksheet.getRow(i++);
user.setHrName(row.getCell(0).getStringCellValue());
user.setDepartmentName(row.getCell(1).getStringCellValue());
user.setParentDepartment(row.getCell(2).getStringCellValue());


                lstUser.add(user);

            }

            departmentService.updateList(lstUser);

            model.addAttribute("lstUser", lstUser);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return new ModelAndView("redirect:/listOfDepartment");
    }

我有另一种方法，我使用 Hssf 读取 xls 文件.但是我只有一个导入按钮，用户可以上传任何类型的文件 xls、xlsx，但是对于导入按钮，我可以执行一个操作，即转到 xssf 或 hssf 方法.所以我想知道是否有任何可能的方法在单一方法中同时拥有这两种方法.或者任何其他超类都具有 Xssf 和 Hssf 类的属性.

Im having another method which i used Hssf to read xls file.But iam having only one import button user can upload any type of file xls,xlsx but for import button i can have one action eigther go to xssf or hssf method.So i like to know is there any possible way to have botth in single method.Or any other super class to having property of both Xssf and Hssf Class.

推荐答案

同时支持 HSSF 和 XSSF 读取和重写 *.xls 和 *.xlsx，您将使用 WorkbookFactory 用于创建 工作簿.这可以从 *.xls 的 InputStream 以及 *.xlsx 文件创建 Workbook.>

For supporting both HSSF as well as XSSF for reading and rewriting *.xls and *.xlsx, you will using WorkbookFactory for creating Workbook. This is able creating Workbook from InputStream of *.xls as well as of *.xlsx files.

 FileInputStream fileinputstream = new FileInputStream("pathToExcelFile.xls_or_.xlsx");
 Workbook workbook = WorkbookFactory.create(fileinputstream);

然后，只要可能，您将使用 包 org.apache.poi.ss.usermodel 而不是特殊的 HSSF 或 XSSF 类.

Then, as long as possible, you will working with the interfaces of Package org.apache.poi.ss.usermodel instead of the special HSSF or XSSF classes.

这并不总是可行的，因为 apache poi 直到现在才处于开发阶段.但如果不可能，您可以通过 instanceof 检测您真正在使用什么对象(HSSF 或 XSSF).

This is not always possible since apache poi is in development until now. But if not possible you can detect via instanceof what object (HSSF or XSSF) you really are working with.

对于编写，您将使用依赖于 instanceof 和 Workbook 的适当方法.

And for writing you will using the appropriate methods dependent of the instanceof the Workbook.

  if (workbook instanceof XSSFWorkbook) {
   workbook.write(new FileOutputStream("pathToExcelFile.xlsx"));
  } else if (workbook instanceof HSSFWorkbook) {
   workbook.write(new FileOutputStream("pathToExcelFile.xls"));
  }
  workbook.close();

直到 apache poi 3.17 Workbook.write 已经关闭了 OutputStream.现在在 apache poi 4.0.* 版本中，它不再关闭 OutputStream.所以我们需要使用

Up to apache poi 3.17 Workbook.write has closed the OutputStream. Now in apache poi 4.0.* versions it not more closes the OutputStream. So we need using

  FileOutputStream out = null;
  if (workbook instanceof XSSFWorkbook) out = new FileOutputStream("pathToExcelFile.xlsx");
  else if (workbook instanceof HSSFWorkbook) out = new FileOutputStream("pathToExcelFile.xls");
  if (out != null) {
   workbook.write(out);
   out.close();
  }
  workbook.close();

这篇关于如何在spring mvc中使用java导入xls和xlsx文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！