WEB下的excel批量导入功能

新手学习中,记录一下excel导出功能实现的整个流程。使用框架ssm。

control层 importExcel+parseDataItem:

  @RequestMapping("importExcel.do")
    public String importExcel(@RequestParam MultipartFile uploadfile, 
            HttpServletRequest request, HttpServletResponse response) {
        String fileName = uploadfile.getOriginalFilename();
        if (StringUtils.isEmpty(fileName)) {
            System.out.println("未上传文件名为空");
        }
        String tempPath = request.getSession().getServletContext().getRealPath("/")
                + "upload";
        File dir = new File(tempPath);    
        if (!tempPath.endsWith(File.separator)) {
            tempPath = tempPath + File.separator;
        }
        if (!dir.exists()) {
            dir.mkdirs();
        }
        System.out.println(tempPath);
        //制作路径
        String newFile = tempPath + fileName;
        File file = new File(newFile);        
        try {
            FileCopyUtils.copy(uploadfile.getBytes(), file);
            //导入excel中的内容
            this.parseDataItem(newFile);
            System.out.println("成功导入");
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                FileUtils.forceDelete(file);
            } catch (IOException e) {
                e.printStackTrace();
                return "uploadfail";
            }
        }
        return "uploadsuccess";
    }
	
    public final boolean parseDataItem(String file){
        
       return ysdwAnimalService.importExcel(file);
    }
	
ysdwAnimalService层 import 调用底层通用方法readExcel:

@Transactional
    public boolean importExcel(String file) {
        final int rowStart = 2;
        final int cellStrart = 0;      
        List<YsdwAnimal> list = new LinkedList<YsdwAnimal>();    
        YsdwAnimal ysdwAnimal = null;
        try {
            List<Object> dataList = ReadExcleUtils.readExcel(file, new YsdwAnimalExcel(), rowStart, cellStrart);
            for (Object object : dataList) {
               YsdwAnimalExcel excel = (YsdwAnimalExcel) object;
                ysdwAnimal = new YsdwAnimal();               
                String uuid = CommonUtils.getUuid();
                ysdwAnimal.setId(uuid);
                ysdwAnimal.setAnimalname(excel.getAnimalname());
                ysdwAnimal.setAnimalnumber(excel.getAnimalnumber());
                ysdwAnimal.setAnimaltypes(excel.getAnimaltypes());
                ysdwAnimal.setVegetationtypes(excel.getVegetationtypes());
                ysdwAnimal.setJingdu(excel.getJingdu());
                ysdwAnimal.setWeidu(excel.getWeidu());
                ysdwAnimal.setPodu(excel.getPodu());
                ysdwAnimal.setPowei(excel.getPowei());
                ysdwAnimal.setPoxiang(excel.getPoxiang());
                ysdwAnimal.setZuobiao(excel.getZuobiao());
                ysdwAnimal.setHaiba(excel.getHaiba());
                ysdwAnimal.setTianqi(excel.getTianqi());
                ysdwAnimal.setJilusj(excel.getJilusj());
                ysdwAnimal.setQita(excel.getQita());      
                ysdwAnimal.setDid(excel.getDid());
                ysdwAnimal.setBaohdj(excel.getBaohdj());
                list.add(ysdwAnimal);              
            }           
            //存库
            for (YsdwAnimal YsdwAnimal : list) {
            	YsdwAnimalMapper.saveOrUpdate(YsdwAnimal);
                System.out.println("存库");
            }
        } catch (IOException e) {
            e.printStackTrace();
            return false;
        }
        return true;
        
    }
底层通用方法readExcel

/**
	 * 读取EXCLE2007的方法
	 * @param filePath 文件名
	 * @param obj 对象名
	 * @param rowStart 起点行(从0开始)
	 * @param cellStrart 起点列(从0开始)
	 * @return 对象集合
	 * @throws IOException
	 */
	@SuppressWarnings("rawtypes")
	public static List<Object> readExcel(final String filePath, final Object obj, final int rowStart, final int cellStrart) throws IOException {
		List<Object> aList = null;
		final String extension = filePath.substring(filePath.lastIndexOf(".")+1, filePath.length());//截取文件扩展名
		final Class c = obj.getClass();//获得类对象
		final Field[] filds = c.getDeclaredFields();//获得该类中所有的属性
		final File excelFile = new File(filePath); 
		final InputStream is = new FileInputStream(excelFile);// 获取文件输入流
		if ("xlsx".equals(extension)) {//解析2007
			aList = ReadExcleUtils.analyExcle2007(c, is, filds, rowStart, cellStrart);
		} else if ("xls".equals(extension)) {
			aList = ReadExcleUtils.analyExcle2003(c, is, filds, rowStart, cellStrart);
		} else {
			System.out.println("上传文件不正确");
		}
		return aList;
	}
	
	@SuppressWarnings("rawtypes")
	private static List<Object> analyExcle2007(final Class c, final InputStream is, final Field[] filds, final int rowStart, final int cellStrart) throws IOException {
		Object objs = null;
		Map<String,Object> mapAtt = null;
		final List<Object> aList = new ArrayList<Object>();
		final XSSFWorkbook workbook2007 = new XSSFWorkbook(is);// 创建Excel2007文件对象  
		final XSSFSheet sheet = workbook2007.getSheetAt(0);// 取出第一个工作表,索引是0
		final XSSFFormulaEvaluator evaluator=new XSSFFormulaEvaluator(workbook2007);
		for (int i = rowStart; i <= sheet.getLastRowNum(); i++) {
			String cellStr = null;// 单元格,最终按字符串处理
			final XSSFRow row = sheet.getRow(i);// 获取行对象  
			if (row == null) {// 如果为空,不处理  
                continue;  
            }else {
            	mapAtt = new HashMap<String,Object>();
            	for (int j = 0; j < filds.length; j++) {
    				final XSSFCell cell = row.getCell(j+cellStrart);
    				//判断单元格的数据类型   
    				if (cell != null) {
    					//对时间的特殊处理
    					int dataFormat = cell.getCellStyle().getDataFormat();
    					if (dataFormat == 14 || dataFormat == 176 || dataFormat == 178 || dataFormat == 180 || dataFormat == 181 || dataFormat == 182) {
    						cellStr = ReadExcleUtils.getDateValue2007(cell);
    					} else{
    						switch (cell.getCellType()) {
        					case HSSFCell.CELL_TYPE_NUMERIC://数值
        						BigDecimal db = new BigDecimal(cell.getNumericCellValue());
        						if (db.toString().indexOf(".") != -1) {
        							java.text.DecimalFormat dfomat = new java.text.DecimalFormat("0.000000");
        							cellStr = dfomat.format(db);
        						}else {
        							cellStr = db.toPlainString();
        						}
//        						cellStr = db.toPlainString();
//        						cellStr = String.valueOf(cell.getNumericCellValue());
        						break;
        					case HSSFCell.CELL_TYPE_STRING://字符串
        						cellStr = cell.getStringCellValue();
        						break;
        					case HSSFCell.CELL_TYPE_BOOLEAN://布尔
        						cellStr = String.valueOf(cell.getBooleanCellValue());
        						break;
        					case HSSFCell.CELL_TYPE_FORMULA://公式
        						cellStr = String.valueOf(evaluator.evaluate(cell).getNumberValue());
        						break;
        					case HSSFCell.CELL_TYPE_BLANK://空值
        						cellStr =  "";
        						break;
        					default:
        						cellStr = cell.getStringCellValue();
        						break;
        					}
    					}
    				} else {
    				    cellStr = null;
    				}
    				//讲单元格中的数据放入集合中
    				mapAtt.put(filds[j].getName(), cellStr);
    			}
            }
			try {
				objs = c.newInstance();
				ReadExcleUtils.invokeMethod(c, objs, mapAtt);
				aList.add(objs);
			} catch (InstantiationException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return aList;
	}
	
	@SuppressWarnings("rawtypes")
	private static List<Object> analyExcle2003(final Class c, final InputStream is, final Field[] filds, final int rowStart, final int cellStrart) throws IOException {
		Object objs = null;
		Map<String,Object> mapAtt = null;
		final List<Object> aList = new ArrayList<Object>();
		final HSSFWorkbook workbook2003 = new HSSFWorkbook(is);// 创建Excel2003文件对象 
		final HSSFSheet sheet = workbook2003.getSheetAt(0);// 取出第一个工作表,索引是0
		final HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook2003);
		for (int i = rowStart; i <= sheet.getLastRowNum(); i++) {
			final HSSFRow row = sheet.getRow(i);// 获取行对象
			if (row == null) {// 如果为空,不处理  
                continue;  
            }else {
            	mapAtt = new HashMap<String,Object>();
            	for (int j = 0; j < filds.length; j++) {
            		String cellStr = null;// 单元格,最终按字符串处理
    				final HSSFCell cell = row.getCell(j+cellStrart);
    				//判断单元格的数据类型   
    				if (cell != null) {
    					//对时间的特殊处理
    					int dataFormat = cell.getCellStyle().getDataFormat();
//    					if (dataFormat == 14 || dataFormat == 178 || dataFormat == 180 || dataFormat == 181 || dataFormat == 182) {
    					if (dataFormat == 14 || dataFormat == 31 || dataFormat == 57 || dataFormat == 58) {
    						cellStr = ReadExcleUtils.getDateValue2003(cell);
    					} else{
    						switch (cell.getCellType()) {
        					case HSSFCell.CELL_TYPE_NUMERIC://数值
        						BigDecimal db = new BigDecimal(cell.getNumericCellValue());
        						if (db.toString().indexOf(".") != -1) {
        							java.text.DecimalFormat dfomat = new java.text.DecimalFormat("0.000000");
        							cellStr = dfomat.format(db);
        						}else {
        							cellStr = db.toPlainString();
        						}
        						break;
        					case HSSFCell.CELL_TYPE_STRING://字符串
        						cellStr = cell.getStringCellValue();
        						break;
        					case HSSFCell.CELL_TYPE_BOOLEAN://布尔
        						cellStr = String.valueOf(cell.getBooleanCellValue());
        						break;
        					case HSSFCell.CELL_TYPE_FORMULA://公式
        						cellStr = String.valueOf(evaluator.evaluate(cell).getNumberValue());
        						break;
        					case HSSFCell.CELL_TYPE_BLANK://空值
        						cellStr =  "";
        						break;
        					default:
        						cellStr = cell.getStringCellValue();
        						break;
        					}
    					}
    				} else {
                        cellStr = null;
                    }
    				//讲单元格中的数据放入集合中
    				mapAtt.put(filds[j].getName(), cellStr);
    			}
            }
			try {
				objs = c.newInstance();
				ReadExcleUtils.invokeMethod(c, objs, mapAtt);
				aList.add(objs);
			} catch (InstantiationException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return aList;
	}
注意!!在底层方法种完成对excel表格字段顺序的控制,具体控制语句为:final Field[] filds = c.getDeclaredFields();//获得该类中所有的属性

这就是从底层开始导入excel的完整过程


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值