excel批量导入数据库

excel导入数据库数据的结构如下：

Controller:

@RequestMapping(value="/formExcel",method = RequestMethod.POST)@ResponseBodypublic String formExcel(MultipartFile upExcel){        try {String originalFilename = upExcel.getOriginalFilename();String uploadPath = "upload/Excel";String rootPath = SpringMVCUtil.getRequest().getSession().getServletContext().getRealPath("/");String imagePath = uploadPath + "/" + UUID.getUUID() + originalFilename;try {FileStorageHelper.transferFile(upExcel.getInputStream(), imagePath);} catch (IOException e) {e.printStackTrace();return "Excel存储失败";}//String rootPath = SpringMVCUtil.getRequest().getSession().getServletContext().getRealPath("/");String paths = rootPath+imagePath;unitsService.Exceldata(paths);return "Excel导入数据成功";} catch (Exception e) {e.printStackTrace();return "Excel导入数据失败";}}

Service:

public void Exceldata(String originalFilename){List<Map<String, Integer>> list = new ArrayList<Map<String, Integer>>();// 判断是否为excel类型文件if (!originalFilename.endsWith(".xls") && !originalFilename.endsWith(".xlsx")) {System.out.println("文件不是excel类型");}FileInputStream fis = null;Workbook wookbook = null;int flag = 0;try {// 获取一个绝对地址的流fis = new FileInputStream(originalFilename);} catch (Exception e) {e.printStackTrace();}try {// 2003版本的excel，用.xls结尾wookbook = new HSSFWorkbook(fis);// 得到工作簿} catch (Exception ex) {// ex.printStackTrace();try {// 这里需要重新获取流对象，因为前面的异常导致了流的关闭—————————————————————————————加了这一行fis = new FileInputStream(originalFilename);// 2007版本的excel，用.xlsx结尾wookbook = new XSSFWorkbook(originalFilename);// 得到工作簿} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}// 得到一个工作表Sheet sheet = wookbook.getSheetAt(0);// 获得表头Row rowHead = sheet.getRow(0);// 根据不同的data放置不同的表头Map<Object, Integer> headMap = new HashMap<Object, Integer>();// 判断表头是否合格 ------------------------这里看你有多少列if (rowHead.getPhysicalNumberOfCells() != 8) {System.out.println("表头列数与要导入的数据库不对应");}try {// ----------------这里根据你的表格有多少列while (flag < 8) {Cell cell = rowHead.getCell(flag);if (getRightTypeCell(cell).toString().trim().equals("单位名称")) {headMap.put("dwmc", flag);}if (getRightTypeCell(cell).toString().trim().equals("单位属性")) {headMap.put("dwsx", flag);}if (getRightTypeCell(cell).toString().trim().equals("单位性质")) {headMap.put("dwxz", flag);}if (getRightTypeCell(cell).toString().trim().equals("单位负责人")) {headMap.put("dwfzr", flag);}if (getRightTypeCell(cell).toString().trim().equals("联系电话")) {headMap.put("lxdh", flag);}if (getRightTypeCell(cell).toString().trim().equals("单位地址")) {headMap.put("dwdz", flag);}if (getRightTypeCell(cell).toString().trim().equals("节点信息")) {headMap.put("jdxx", flag);}if (getRightTypeCell(cell).toString().trim().equals("组织机构编码")) {headMap.put("zzjgbm", flag);}flag++;}} catch (Exception e) {e.printStackTrace();System.out.println("表头不合规范，请修改后重新导入");}// 获得数据的总行数int totalRowNum = sheet.getLastRowNum();// 要获得属性String unitsName = "";  //单位名称String unitsProperty = "";  //单位属性String unitsCharacter = ""; //单位性质String agent = "";  //单位负责人String contactTel = "";  //联系电话String unitsAddress = "";  //单位地址String nodeData = "";  //节点信息String organizationCode = ""; //组织机构编码if (0 == totalRowNum) {System.out.println("Excel内没有数据！");}Cell cell_1 = null, cell_2 = null, cell_3 = null, cell_4 = null, cell_5 = null, cell_6 = null, cell_7 = null, cell_8 = null;// 获得所有数据for (int i = 1; i <= totalRowNum; i++) {// 获得第i行对象Row row = sheet.getRow(i);try {cell_1 = row.getCell(headMap.get("dwmc"));cell_2 = row.getCell(headMap.get("dwsx"));cell_3 = row.getCell(headMap.get("dwxz"));cell_4 = row.getCell(headMap.get("dwfzr"));cell_5 = row.getCell(headMap.get("lxdh"));cell_6 = row.getCell(headMap.get("dwdz"));cell_7 = row.getCell(headMap.get("jdxx"));cell_8 = row.getCell(headMap.get("zzjgbm"));if (cell_2 == null || cell_2.getCellType() == HSSFCell.CELL_TYPE_BLANK) {cell_2 = cell_1;}if (cell_3 == null || cell_3.getCellType() == HSSFCell.CELL_TYPE_BLANK) {cell_3 = cell_1;}if (cell_4 == null || cell_4.getCellType() == HSSFCell.CELL_TYPE_BLANK) {cell_4 = cell_1;}if (cell_5 == null || cell_5.getCellType() == HSSFCell.CELL_TYPE_BLANK) {cell_5 = cell_1;}if (cell_6 == null || cell_6.getCellType() == HSSFCell.CELL_TYPE_BLANK) {cell_6 = cell_1;}if (cell_7 == null || cell_7.getCellType() == HSSFCell.CELL_TYPE_BLANK) {cell_7 = cell_1;}if (cell_8 == null || cell_8.getCellType() == HSSFCell.CELL_TYPE_BLANK) {cell_8 = cell_1;}} catch (Exception e) {e.printStackTrace();System.out.println("获取单元格错误");}try {if (cell_1 == null || cell_1.getCellType() == HSSFCell.CELL_TYPE_BLANK) {break;}// 单位名称unitsName = (String) getRightTypeCell(cell_1);// 单位属性unitsProperty = (String) getRightTypeCell(cell_2);// 单位性质unitsCharacter = (String) getRightTypeCell(cell_3);// 单位负责人agent = (String) getRightTypeCell(cell_4);// 联系电话contactTel = (String) getRightTypeCell(cell_5);// 单位地址unitsAddress = (String) getRightTypeCell(cell_6);// 节点信息nodeData = (String) getRightTypeCell(cell_7);// 组织机构编码organizationCode = (String) getRightTypeCell(cell_8);} catch (ClassCastException e) {e.printStackTrace();System.out.println("数据不全是数字或全部是文字!");}System.out.println("单位名称：" + unitsName + ",单位属性：" + unitsProperty + ",单位性质:" + unitsCharacter + ",单位负责人：" + agent+ ",联系电话:" +contactTel+ ",单位地址：" + unitsAddress + ",节点信息：" + nodeData + ",组织机构编码：" +organizationCode);//里面处理逻辑代码     }}

页面这样写：

<div class="form-group"><label class="col-sm-3 control-label">上传文件</label><div class="col-sm-7"><input type="file" name="upExcel" id="ben" /></div></div>

<button class="btn btn-primary m-r-xs" id="submitBtn">提交</button>

//上传$("#submitBtn").click(function(){var diag = new Dialog();diag.Width = 200;diag.Height = 80;diag.InvokeElementId="shifougongxiang";var formData = new FormData($( "#toForm" )[0]);//     diag.OKEvent = function(){//     $( "#toForm" ).attr("method","post").attr("action","/Document/formExcel").submit();    $.ajax({      type:"POST",      url:"/Document/formExcel",      data:formData,       async: false,                cache: false,                contentType: false,                processData: false,        success:function(data){//      Dialog.alert(data);                     },      error:function(data){     alert("ERROR");     Dialog.alert(data);      }      });

这就是基本代码了，欢迎指导交流