利用poi从excel中导入数据到数据库

最近遇到了从excel向数据库导入数据的情况，利用POI技术实现数据的导入：

前台页面代码如下：

<input type="file" name="" id="file" style="width: 165px"><button type="button" onclick="improtMessage()" style="width: 48px">导入</button><button type="button" onclick="exportMessage()" style="width: 48px">导出</button><button type="button" onclick="dowloadMessage()" style="width: 70px">模板下载</button>

前台js代码：

/** * 导入配置 */function improtMessage() {$.messager.confirm('提示','确定您要导入配置？',function(r){if (r){var fileName=$('#file').val();var file=fileName.split(".")[0];$('#file').attr("name",$('#file').val());$.ajaxFileUpload({url: path+'/systemConfigureController/importSysConfig.do',secureuri: false,//异步fileElementId: 'file',//上传控件IDdataType: 'text',//返回的数据信息格式success: function(data){$.messager.alert('提示','导入配置成功！');if(file == "小区"){selectVillageMessage();}if(file == "国家地区码"){selectCountryMessage();}if(file == "位置区"){selectPositionMessage();}if(file == "终端信息"){selectIMEIMessage();}}})}});}

控制层代码如下：

/** * 导入配置 * @param request * @param response * @throws IOException */@RequestMapping("importSysConfig")public void importData(HttpServletRequest request, HttpServletResponse response) throws IOException{logger.info("**********into method importData*************");// 设置上下文    CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(        request.getSession().getServletContext());    // 检查form是否有enctype="multipart/form-data"    if (multipartResolver.isMultipart(request)) {    MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;    Iterator<String> iter = multiRequest.getFileNames();    while (iter.hasNext()) {    List objList = new ArrayList();    MultipartFile file = multiRequest.getFile(iter.next());    logger.info("print out file:  "+file+"  fileName:  "+file.getName()+" fileSize: "+file.getSize());    version = (file.getName().endsWith(".xls") ? version2003 : version2007);    if (version == 2003) {     POIFSFileSystem fs = new POIFSFileSystem(file.getInputStream());                       HSSFWorkbook wb = new HSSFWorkbook(fs);                       HSSFSheet sheet = wb.getSheetAt(0);                      readObject(file.getName(), sheet);                     logger.info("print out readExcel: " + objList);    }else if (version == 2007){    XSSFWorkbook hwk = new XSSFWorkbook(file.getInputStream());// 将is流实例到 一个excel流里    XSSFSheet sheet = hwk.getSheetAt(0);// 得到book第一个工作薄sheet    readObject(file.getName(), sheet);    logger.info("print out readExcel: " + objList);    }    }    }    response.getWriter().print("1");}/** * 根据不同的excle表名，调用不同的读取方法 * @param name * @param sheet * @return */public void readObject(String name, Sheet sheet){List objList = new ArrayList();String _name = name.substring(0, name.indexOf("."));if (_name.equals("国家地区码")){objList = readCountryExcel(sheet);systemConfigureService.addCountryMEessage(objList);}else if (_name.equals("位置区")){objList = readPositionExcel(sheet);systemConfigureService.addPositionMessage(objList);}else if(_name.equals("小区")){objList = readVillageExcel(sheet);systemConfigureService.addVillageMessage(objList);}else if(_name.equals("终端信息")){objList = readIMEIExcel(sheet);systemConfigureService.addIMEIMessage(objList);}}

/** * 1读取国家地区码excel表格 * @param sheet * @return */public List<Country> readCountryExcel(Sheet sheet){List<Country> countryList = new ArrayList<Country>(); int rowNum = sheet.getPhysicalNumberOfRows();  //获得总共的行数 Country country = null; for (int i =1; i<rowNum; i++){ country = new Country(); Row row = sheet.getRow(i);   //得到第i行数据 Cell c = null; if (null != row){ c = row.getCell(0); if (null != c){ if (c.getCellType() == c.CELL_TYPE_NUMERIC) { c.setCellType(c.CELL_TYPE_STRING); country.setCountry(c.getStringCellValue()); }else{ country.setCountry(c.getStringCellValue()); } } c = row.getCell(1); if (null != c){ if (c.getCellType() == c.CELL_TYPE_NUMERIC) { c.setCellType(c.CELL_TYPE_STRING); country.setProvince(c.getStringCellValue()); }else { country.setProvince(c.getStringCellValue()); } } c = row.getCell(2); if (null != c){ if (c.getCellType() == c.CELL_TYPE_NUMERIC) { c.setCellType(c.CELL_TYPE_STRING); country.setZone(c.getStringCellValue()); }else { country.setZone(c.getStringCellValue()); } } c = row.getCell(3); if (null != c){ if (c.getCellType() == c.CELL_TYPE_NUMERIC) { c.setCellType(c.CELL_TYPE_STRING); country.setCode(c.getStringCellValue()); }else { country.setCode(c.getStringCellValue()); } } } countryList.add(country); logger.info("print out read Excel row" + i + "Country: " + country); }return countryList;}/** * 2.读取位置区excel表格 * @param sheet * @return */public List<Position> readPositionExcel(Sheet sheet){List<Position> positionList = new ArrayList<Position>(); int rowNum = sheet.getPhysicalNumberOfRows();  //获得总共的行数 Position position = null; for (int i =1; i<rowNum; i++){ position = new Position(); Row row = sheet.getRow(i);   //得到第i行数据 Cell c = null; if (null != row){ c = row.getCell(0); if (null != c){ if (c.getCellType() == c.CELL_TYPE_NUMERIC) { c.setCellType(c.CELL_TYPE_STRING); position.setArea(c.getStringCellValue()); }else { position.setArea(c.getStringCellValue()); } } c = row.getCell(1); if (null != c){ if (c.getCellType() == c.CELL_TYPE_NUMERIC) { c.setCellType(c.CELL_TYPE_STRING); position.setPosition_code(c.getStringCellValue()); }else { position.setPosition_code(c.getStringCellValue()); } } c = row.getCell(2); if (null != c){ if (c.getCellType() == c.CELL_TYPE_NUMERIC) { c.setCellType(c.CELL_TYPE_STRING); position.setPosition_name(c.getStringCellValue()); }else { position.setPosition_name(c.getStringCellValue()); }  } } positionList.add(position); logger.info("print out read Excel row" + i + "position: " + position); }return positionList;}/** * 3.读取小区excel表格 * @param sheet * @return */public List<Village> readVillageExcel(Sheet sheet){StringBuffer sss=null;List<Village> villageList = new ArrayList<Village>();int rowNum = sheet.getPhysicalNumberOfRows();  //获得总共的行数   for(int i = 1; i<rowNum; i++){               Row row = sheet.getRow(i);  //得到第i行数据            int cols = row.getLastCellNum();  // 该行的总列数         String[] str = new String[cols];   // 用来存放该行每一列的值       for (int j = 0; j < cols; j++) {                  Object col = row.getCell((short)j);      if (null != col){   str[j] = col.toString();             }else{   str[j] = "";   }      }     Village v = new Village(str);    logger.info("print out read String[] str" +  "str: " + Arrays.toString(str));   villageList.add(v);   }return villageList;}/** * 4.读取终端信息excel表格 * @param sheet * @return */public List<IMEI> readIMEIExcel(Sheet sheet){List<IMEI> IMEIList = new ArrayList<IMEI>(); int rowNum = sheet.getPhysicalNumberOfRows();  //获得总共的行数 IMEI _imei = null; for (int i =1; i<rowNum; i++){ _imei = new IMEI(); Row row = sheet.getRow(i);   //得到第i行数据 Cell c = null; if (null != row){ c = row.getCell(0); if (null != c){ if (c.getCellType() == c.CELL_TYPE_NUMERIC){ c.setCellType(c.CELL_TYPE_STRING); _imei.setIMEI(c.getStringCellValue()); }else{ _imei.setIMEI(c.getStringCellValue()); } } c = row.getCell(1); if (null != c){ if (c.getCellType() == c.CELL_TYPE_NUMERIC){ c.setCellType(c.CELL_TYPE_STRING); _imei.setImei_version(c.getStringCellValue()); }else{ _imei.setImei_version(c.getStringCellValue()); } } c = row.getCell(2); if (null != c){ if (c.getCellType() == c.CELL_TYPE_NUMERIC){ c.setCellType(c.CELL_TYPE_STRING); _imei.setImei_name(c.getStringCellValue()); }else{ _imei.setImei_name(c.getStringCellValue()); } } c = row.getCell(3); if (null != c){ if (c.getCellType() == c.CELL_TYPE_NUMERIC){ c.setCellType(c.CELL_TYPE_STRING); _imei.setImei_factory_name(c.getStringCellValue()); }else{ _imei.setImei_factory_name(c.getStringCellValue()); }  } } IMEIList.add(_imei); logger.info("print out read Excel row" + i + "_imei: " + _imei); }return IMEIList;}}

servicedao的代码：

public void addCountryMEessage(List<Country> list);public void addIMEIMessage(List<IMEI> list);public void addPositionMessage(List<Position> list);public void addVillageMessage(List<Village> list);

servicedaoimpl的代码：

@Overridepublic void addCountryMEessage(List<Country> list) {countryDao.addCountryMessage(list);}@Overridepublic void addIMEIMessage(List<IMEI> list) {imeiDao.addIMEIMessage(list);}@Overridepublic void addPositionMessage(List<Position> list) {positionDao.addPositionMessage(list);}@Overridepublic void addVillageMessage(List<Village> list) {villageDao.addVillageMessage(list);}

xml中sql的代码如下：

<!-- 向数据库imei中插入数据 --><insert id="addIMEIMessage" parameterType="java.util.List">insert into imei (IMEI, imei_version, imei_name, imei_factory_name)values<foreach collection="list" item="item" index="index" separator="," >(#{item.IMEI}, #{item.imei_version}, #{item.imei_name}, #{item.imei_factory_name})</foreach></insert>