导入Excel java
2016-01-18 16:34 qqForever 阅读(134) 评论(0) 编辑 收藏 举报1.方法调用
public void importExcel(HttpServletRequest request, HttpServletResponse response) throws IOException { ItemController xlsMain = new ItemController(); ItemEntity xls = null; List<ItemEntity> list = xlsMain.readXls(request, response); for (int i = 0; i < list.size(); i++) { xls = (ItemEntity) list.get(i); itemService.save(xls); } }
2.读取xls文件内容
1 private List<ItemEntity> readXls(HttpServletRequest request, HttpServletResponse response) throws IOException { 2 MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request; 3 Map<String, MultipartFile> fileMap = multipartRequest.getFileMap(); 4 List<ItemEntity> list = new ArrayList<ItemEntity>(); 5 for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) { 6 MultipartFile file = entity.getValue();// 获取上传文件对象 7 InputStream is =file.getInputStream(); 8 HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is); 9 ItemEntity item = null; 10 11 // 循环工作表Sheet 12 for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { 13 HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); 14 if (hssfSheet == null) { 15 continue; 16 } 17 // 循环行Row 18 for (int rowNum = 3; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { 19 HSSFRow hssfRow = hssfSheet.getRow(rowNum); 20 if (hssfRow == null) { 21 continue; 22 } 23 item = new ItemEntity(); 24 // 循环列Cell 25 // 0编号 1名称 2学院 3类型 4 26 HSSFCell no = hssfRow.getCell(0); 27 item.setNo(ExcelTempletService.getCellValueString(no)); 28 HSSFCell name = hssfRow.getCell(1); 29 item.setName(ExcelTempletService.getCellValueString(name)); 30 HSSFCell type = hssfRow.getCell(2); 31 item.setType(ExcelTempletService.getCellValueString(type)); 32 HSSFCell price = hssfRow.getCell(3); 33 if (ExcelTempletService.getCellValueString(price)!=null) { 34 item.setPrice(Double.parseDouble(ExcelTempletService.getCellValueString(price))); 35 } 36 HSSFCell memberPrice = hssfRow.getCell(4); 37 if (ExcelTempletService.getCellValueString(memberPrice)!=null) { 38 item.setMemberPrice(Double.parseDouble(ExcelTempletService.getCellValueString(memberPrice))); 39 } 40 HSSFCell periodPrice = hssfRow.getCell(5); 41 if (ExcelTempletService.getCellValueString(periodPrice)!=null) { 42 item.setPeriodPrice(Double.parseDouble(ExcelTempletService.getCellValueString(periodPrice))); 43 } 44 HSSFCell periodNumber = hssfRow.getCell(6); 45 if (ExcelTempletService.getCellValueString(periodNumber)!=null) { 46 item.setPeriodNumber(Integer.parseInt(ExcelTempletService.getCellValueString(periodNumber))); 47 } 48 HSSFCell itemTime = hssfRow.getCell(7); 49 if (ExcelTempletService.getCellValueString(itemTime)!=null) { 50 item.setItemTime(Integer.parseInt(ExcelTempletService.getCellValueString(itemTime))); 51 } 52 HSSFCell brand = hssfRow.getCell(8); 53 item.setBrand(ExcelTempletService.getCellValueString(brand)); 54 HSSFCell feature = hssfRow.getCell(9); 55 item.setFeature(ExcelTempletService.getCellValueString(feature)); 56 HSSFCell effect = hssfRow.getCell(10); 57 item.setEffect(ExcelTempletService.getCellValueString(effect)); 58 HSSFCell fitPeople = hssfRow.getCell(11); 59 item.setFitPeople(ExcelTempletService.getCellValueString(fitPeople)); 60 HSSFCell flow = hssfRow.getCell(12); 61 item.setFlow(ExcelTempletService.getCellValueString(flow)); 62 HSSFCell caution = hssfRow.getCell(13); 63 item.setCaution(ExcelTempletService.getCellValueString(caution)); 64 HSSFCell suggest = hssfRow.getCell(14); 65 item.setSuggest(ExcelTempletService.getCellValueString(suggest)); 66 HSSFCell description = hssfRow.getCell(15); 67 item.setDescription(ExcelTempletService.getCellValueString(description)); 68 HSSFCell canOptional = hssfRow.getCell(16); 69 item.setCanOptional(ExcelTempletService.getCellValueString(canOptional)); 70 HSSFCell canGift = hssfRow.getCell(17); 71 item.setCanGift(ExcelTempletService.getCellValueString(canGift)); 72 HSSFCell isHomepage = hssfRow.getCell(18); 73 item.setIsHomepage(ExcelTempletService.getCellValueString(isHomepage)); 74 HSSFCell workload = hssfRow.getCell(19); 75 item.setWorkload(ExcelTempletService.getCellValueString(workload)); 76 HSSFCell status = hssfRow.getCell(20); 77 item.setStatus(ExcelTempletService.getCellValueString(status)); 78 HSSFCell createBy = hssfRow.getCell(21); 79 item.setCreateBy(ExcelTempletService.getCellValueString(createBy)); 80 HSSFCell createName = hssfRow.getCell(22); 81 item.setCreateName(ExcelTempletService.getCellValueString(createName)); 82 HSSFCell createDate = hssfRow.getCell(23); 83 //将String类型转成Date类型 84 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 85 if (ExcelTempletService.getCellValueString(createDate)!=null) { 86 try { 87 Date date=sdf.parse(ExcelTempletService.getCellValueString(createDate)); 88 item.setCreateDate(date); 89 } catch (ParseException e) { 90 e.printStackTrace(); 91 } 92 } 93 HSSFCell updateBy = hssfRow.getCell(24); 94 item.setUpdateBy(ExcelTempletService.getCellValueString(updateBy)); 95 HSSFCell updateName = hssfRow.getCell(25); 96 item.setUpdateName(ExcelTempletService.getCellValueString(updateName)); 97 HSSFCell updateDate = hssfRow.getCell(26); 98 if (ExcelTempletService.getCellValueString(updateDate)!=null) { 99 try { 100 Date date=sdf.parse(ExcelTempletService.getCellValueString(updateDate)); 101 item.setUpdateDate(date); 102 } catch (ParseException e) { 103 e.printStackTrace(); 104 } 105 } 106 HSSFCell delflag = hssfRow.getCell(27); 107 if(ExcelTempletService.getCellValueString(delflag)!=null){ 108 item.setDelflag(Integer.parseInt(ExcelTempletService.getCellValueString(delflag))); 109 } 110 111 HSSFCell delDate = hssfRow.getCell(28); 112 if (ExcelTempletService.getCellValueString(delDate)!=null) { 113 try { 114 Date date=sdf.parse(ExcelTempletService.getCellValueString(delDate)); 115 item.setDelDate(date); 116 } catch (ParseException e) { 117 e.printStackTrace(); 118 } 119 } 120 121 list.add(item); 122 } 123 124 } 125 } 126 return list; 127 }
