Excel导入导出到Mysql

	/*
	 * 导入Excel  （.xls）
	 */
	@RequestMapping("/save.do")
	@ResponseBody	
	public String importFile(HttpServletRequest request, @RequestParam("file")MultipartFile file) {
		String result = "";
		try {
			Workbook wb = WorkbookFactory.create(file.getInputStream());
		
			Sheet sheet = wb.getSheetAt(0);// 取得第一个sheets
			for (int i=3; i<=sheet.getLastRowNum(); i++) {
				Row row = sheet.getRow(i); // 获取行(row)对象
				if (row == null) {
                continue;
				} else {
					Customer user = new Customer();
					String jtbh = "";
					String xm = "";
					String hy = "";
					String bz = "";
					//获取每列内容
					for (int j=0; j<row.getLastCellNum(); j++) {
						Cell cell = row.getCell(j); // 获得单元格(cell)对象
						// 转换接收的单元格
						switch(j) {
                    		case 0:
                    			jtbh = FileUtils.ConvertCellStr(cell);
                    			break;
                    		case 1:
                    			xm = FileUtils.ConvertCellStr(cell);
                    			break;
                    		case 2:
                    			hy = FileUtils.ConvertCellStr(cell);
                    			break;
                    		case 3:
                    			bz = FileUtils.ConvertCellStr(cell);
                    			break;
									}
                }
            	if (!SysUtils.isEmpty(xm)) {
            		//根据姓名xm查询是否有这条数据，没有则添加
            		Customer existUser = CustomerService.findByUsername(xm);
        			if (SysUtils.isEmpty(existUser)){
        				user.setJtbh(jtbh);
        				user.setXm(xm);
        				user.setHy(hy);
        				user.setBz(bz);
                		try {
                			if(!SysUtils.isEmpty(user)){
                				CustomerService.save(user);
                			}
                		} catch (Exception e) {
                			continue;
                		}
                	}
            		}
        		}
            }
		
	} catch(Exception e) {
		e.printStackTrace();
	}
	
	result = ErrorInfo.getErrorCode_0();
	return result;
}
	
	
/**
 * 导出Excel （.xls）
 * @param request
 * @param response
 */

@RequestMapping("/export.do")
public void export(HttpServletRequest request, HttpServletResponse response) {
	String condition = getParameter(request, "condition");
	
	Map<String, Object> param = new HashMap<String, Object>();
	param.put("condition", condition);
			
	List<Customer> list = CustomerService.selectList(param);
	
	// 显示的字段
    String[] columnShows = {"编号" , "姓名", "年龄", "性别"};
    String[] columnNames = {"jtbh","xm", "hy", "bz"};
    String title = "信息";
    
	// 1.创建一个 workbook
    HSSFWorkbook workbook = new HSSFWorkbook();
    // 2.创建一个 worksheet
    HSSFSheet worksheet = workbook.createSheet("信息表");
    // 3.定义起始行和列
    int startRowIndex = 0;
    int startColIndex = 0;
    // 4.创建title,data,headers
    FileUtils.buildReport(worksheet, startRowIndex, startColIndex, columnShows, title);
    // 5.填充数据
    fillReport(worksheet, startRowIndex, startColIndex, list, columnNames, "已缴费");
    // 6.设置reponse参数
    String fileName;
	try {
		fileName = URLEncoder.encode("xin.xls", "UTF-8");
		response.setHeader("Content-Disposition", "inline; filename=" + fileName);
		// 确保发送的当前文本格式
		response.setContentType("application/vnd.ms-excel");
		// 7. 输出流
		FileUtils.write(response, worksheet);
	} catch (UnsupportedEncodingException e) {
		e.printStackTrace();
	}
}
private static void fillReport(HSSFSheet worksheet, int startRowIndex, int startColIndex, List<Customer> list, String[] columnNames, String jfzt) {
    // Row offset
    startRowIndex += 2;

    // Create cell style for the body
    HSSFCellStyle bodyCellStyle = worksheet.getWorkbook().createCellStyle();
    bodyCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    bodyCellStyle.setWrapText(false); //是否自动换行.

    // 显示的列
    // String columnNameStr = "loginName_nickName_code_sex_email_teleiphone";
    // Create body
    Customer user = new Customer();
    for (int i=startRowIndex; i+startRowIndex-2<list.size()+2; i++) {
        HSSFRow row = worksheet.createRow((short) i+1);
        
        user = list.get(i-2);
       
        String columnName = "";
        String columnValue = "";
        for (int j=0,len=columnNames.length; j<len; j++) {
        	columnName = columnNames[j];
        	
        	if ("jtbh".equals(columnName)) {
        		//columnValue = String.valueOf(i - 1);
        		columnValue = user.getJtbh();
        	} else if ("xm".equals(columnName)) {
        		columnValue = user.getXm();
        	} else if ("hy".equals(columnName)) {
        		columnValue = user.getHy();
        	} else if ("bz".equals(columnName)) {
        		columnValue = user.getBz();
        	}
        	HSSFCell cell = row.createCell(startColIndex + j);
        	cell.setCellValue(columnValue);
        	cell.setCellStyle(bodyCellStyle);
        }
    }
}