1，maven配置
12 org.apache.poi 3poi-ooxml 4${poi.version} 5
2，Controller层
1 /** 2 * Excel导入数据 3 * @return 4 */ 5 @ResponseBody 6 @RequestMapping(value="importExcel", method={RequestMethod.GET, RequestMethod.POST}) 7 public String importExcel(HttpServletRequest request, String name) { 8 JSONObject result = new JSONObject(); 9 result.put("code", 0);10 result.put("msg", "导入成功！");11 //初始化解析器12 CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(request.getServletContext());13 //解析form中是否有enctype="multipart/form-data"14 try {15 if (multipartResolver.isMultipart(request)) {16 MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;17 Iterator iterator = multipartRequest.getFileNames();18 while (iterator.hasNext()) {19 MultipartFile multipartFile = multipartRequest.getFile(iterator.next().toString());20 List data = GhPOIUtils.readExcel(multipartFile);21 Method method = IExportExcelService.class.getDeclaredMethod(name + "Import", new Class[]{List.class});22 method.invoke(exportExcelService, data);23 }24 }25 } catch(Exception e) {26 result.put("code", -1);27 result.put("msg", e.getMessage());28 }29 return result.toString();30 } 3，Util
1 package com.smart.produce.core.utils.comm; 2 3 import java.io.FileNotFoundException; 4 import java.io.IOException; 5 import java.io.InputStream; 6 import java.util.ArrayList; 7 import java.util.List; 8 9 import org.apache.log4j.Logger; 10 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 11 import org.apache.poi.ss.usermodel.Cell; 12 import org.apache.poi.ss.usermodel.Row; 13 import org.apache.poi.ss.usermodel.Sheet; 14 import org.apache.poi.ss.usermodel.Workbook; 15 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 16 import org.springframework.web.multipart.MultipartFile; 17 /** 18 * excel读写工具类 */ 19 public class GhPOIUtils { 20 private static Logger logger = Logger.getLogger(GhPOIUtils.class); 21 private final static String xls = "xls"; 22 private final static String xlsx = "xlsx"; 23 24 public static void checkFile(MultipartFile file) throws IOException{ 25 //判断文件是否存在 26 if(null == file){ 27 logger.error("文件不存在！"); 28 throw new FileNotFoundException("文件不存在！"); 29 } 30 //获得文件名 31 String fileName = file.getOriginalFilename(); 32 //判断文件是否是excel文件 33 if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){ 34 logger.error(fileName + "不是excel文件"); 35 throw new IOException(fileName + "不是excel文件"); 36 } 37 } 38 39 public static Workbook getWorkBook(MultipartFile file) { 40 //获得文件名 41 String fileName = file.getOriginalFilename(); 42 //创建Workbook工作薄对象，表示整个excel 43 Workbook workbook = null; 44 try { 45 //获取excel文件的io流 46 InputStream is = file.getInputStream(); 47 //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象 48 if(fileName.endsWith(xls)){ 49 //2003 50 workbook = new HSSFWorkbook(is); 51 }else if(fileName.endsWith(xlsx)){ 52 //2007 53 workbook = new XSSFWorkbook(is); 54 } 55 } catch (IOException e) { 56 logger.info(e.getMessage()); 57 } 58 return workbook; 59 } 60 61 public static String getCellValue(Cell cell){ 62 String cellValue = ""; 63 if(cell == null){ 64 return cellValue; 65 } 66 //把数字当成String来读，避免出现1读成1.0的情况 67 if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){ 68 cell.setCellType(Cell.CELL_TYPE_STRING); 69 } 70 //判断数据的类型 71 switch (cell.getCellType()){ 72 case Cell.CELL_TYPE_NUMERIC: //数字 73 cellValue = String.valueOf(cell.getNumericCellValue()); 74 break; 75 case Cell.CELL_TYPE_STRING: //字符串 76 cellValue = String.valueOf(cell.getStringCellValue()); 77 break; 78 case Cell.CELL_TYPE_BOOLEAN: //Boolean 79 cellValue = String.valueOf(cell.getBooleanCellValue()); 80 break; 81 case Cell.CELL_TYPE_FORMULA: //公式 82 cellValue = String.valueOf(cell.getCellFormula()); 83 break; 84 case Cell.CELL_TYPE_BLANK: //空值 85 cellValue = ""; 86 break; 87 case Cell.CELL_TYPE_ERROR: //故障 88 cellValue = "非法字符"; 89 break; 90 default: 91 cellValue = "未知类型"; 92 break; 93 } 94 return cellValue; 95 } 96 97 /** 98 * 读入excel文件，解析后返回 99 * @param file100 */101 public static List readExcel(MultipartFile file) throws IOException{102 //检查文件103 checkFile(file);104 //获得Workbook工作薄对象105 Workbook workbook = getWorkBook(file);106 //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回107 List list = new ArrayList ();108 if(workbook != null){109 for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){110 //获得当前sheet工作表111 Sheet sheet = workbook.getSheetAt(sheetNum);112 if(sheet == null){113 continue;114 }115 //获得当前sheet的开始行116 int firstRowNum = sheet.getFirstRowNum();117 //获得当前sheet的结束行118 int lastRowNum = sheet.getLastRowNum();119 //循环所有行120 for(int rowNum = firstRowNum;rowNum <= lastRowNum;rowNum++){121 //获得当前行122 Row row = sheet.getRow(rowNum);123 if(row == null){124 continue;125 }126 //获得当前行的开始列127 int firstCellNum = row.getFirstCellNum();128 //获得当前行的列数129 int lastCellNum = row.getPhysicalNumberOfCells();130 String[] cells = new String[row.getPhysicalNumberOfCells()];131 //循环当前行132 for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){133 Cell cell = row.getCell(cellNum);134 cells[cellNum] = getCellValue(cell);135 }136 list.add(cells);137 }138 }139 workbook.close();140 }141 return list;142 }143 }