Java 如何读取Excel格式xls、xlsx数据工具类

 ReadExcelTest excelTest = new ReadExcelTest(); excelTest.readExcel("D:\\data1.xlsx");

 package com.util; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.apache.xmlbeans.impl.piccolo.io.FileFormatException; public class ReadExcelTest { private static final String EXTENSION_XLS = "xls"; private static final String EXTENSION_XLSX = "xlsx"; /*** * 
 * 取得Workbook对象(xls和xlsx对象不同,不过都是Workbook的实现类) *   xls:HSSFWorkbook *   xlsx：XSSFWorkbook * @param filePath * @return * @throws IOException * 
 */ private Workbook getWorkbook(String filePath) throws IOException { Workbook workbook = null; InputStream is = new FileInputStream(filePath); if (filePath.endsWith(EXTENSION_XLS)) { workbook = new HSSFWorkbook(is); } else if (filePath.endsWith(EXTENSION_XLSX)) { workbook = new XSSFWorkbook(is); } return workbook; } /** * 文件检查 * @param filePath * @throws FileNotFoundException * @throws FileFormatException */ private void preReadCheck(String filePath) throws FileNotFoundException, FileFormatException { // 常规检查 File file = new File(filePath); if (!file.exists()) { throw new FileNotFoundException("传入的文件不存在：" + filePath); } if (!(filePath.endsWith(EXTENSION_XLS) || filePath.endsWith(EXTENSION_XLSX))) { throw new FileFormatException("传入的文件不是excel"); } } /** * 读取excel文件内容 * @param filePath * @throws FileNotFoundException * @throws FileFormatException */ public void readExcel(String filePath) throws FileNotFoundException, FileFormatException { // 检查 this.preReadCheck(filePath); // 获取workbook对象 Workbook workbook = null; try { workbook = this.getWorkbook(filePath); // 读文件 一个sheet一个sheet地读取 for (int numSheet = 0; numSheet 

 @RequestMapping("ReadFromExcel") @ResponseBody public Response ReadFromExcel(@RequestParam(value = "file") MultipartFile file, @RequestAttribute("userNo") String userNo) { try { //校验文件 checkFile(file); List> list =excelService.readExcel(file); if (CollectionUtils.isEmpty(list)) { return new Response(ERROR_CODE, "导入的文件没有数据",false); } }catch (Exception e){ logger.error("ReadFromExcel异常",e); } return new Response(ERROR_CODE, "导入失败", false); } private void checkFile(MultipartFile file) throws IOException { //判断文件是否存在 if(null == file){ logger.error("文件不存在！"); throw new FileNotFoundException("文件不存在！"); } //获得文件名 String fileName = file.getOriginalFilename(); logger.info("ReadFromExcel fileName",fileName); //判断文件是否是excel文件 if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){ logger.error(fileName + "不是excel文件"); throw new IOException(fileName + "不是excel文件"); } }

 @Override public List> readExcel(MultipartFile file) { List> list = new ArrayList>(); try { // 检查文件 logger.info("ExcelServiceImpl 获取文件名", file.getOriginalFilename()); // 获得Workbook工作薄对象 Workbook workbook = getWorkBook(file); // 创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回 logger.info("获得Workbook工作薄对象", file.getOriginalFilename()); if (workbook != null) { for (int sheetNum = 0; sheetNum  cells = new ArrayList<>(); // 循环当前行 for (int cellNum = firstCellNum; cellNum 

  org.apache.poipoi-ooxml3.9 org.apache.poipoi3.9

  xmlbeansxmlbeans2.3.0pom org.apache.poipoi-ooxml-schemas3.9 org.apache.poipoi-scratchpad3.9 org.apache.poipoi-examples3.9