excel导入数据到mysql中

package com.ycq.base.controller; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.math.BigDecimal; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletRequest; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.util.StringUtils; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.ResponseBody; import com.ycq.base.service.IExcelToDBService; import com.ycq.util.UniqueIdGenerator; import net.sf.json.JSONArray; import net.sf.json.JSONObject; @Controller @RequestMapping("excelToDB") public class ExcelToDBController { @Autowired private IExcelToDBService excelToDB; String string = "{allcolumn:[{ecname:'Excel里的列名a',tcname:'a',tctype:'1'},{ecname:'Excel里的列名b',tcname:'b',tctype:'1'},{ecname:'Excel里的列名c',tcname:'c',tctype:'2'},{ecname:'Excel里的列名d',tcname:'d',tctype:'1'},{ecname:'Excel里的列名e',tcname:'e',tctype:'1'}]," + "main:{tablename:'test1',column:['a','b','e'],increasecolumn:[{tcname:'id',tctype:'1'},{tcname:'createTime',tctype:'3'}]}," + "onetoone:[{tablename:'test2',column:['b','c'],relationcolumn:[{pcname:'id',ccname:'pid'}],increasecolumn:[{tcname:'id',tctype:'1'},{tcname:'createTime',tctype:'3'}]}]," + "onetomany:[{tablename:'test3',column:'d',relationcolumn:[{pcname:'id',ccname:'pid'}],increasecolumn:[{tcname:'id',tctype:'1'},{tcname:'createTime',tctype:'3'}]}]}"; String path1 = "C:/Users/Administrator/Desktop/12345.xlsx"; @RequestMapping("add") public String toAdd(){ return "system/add"; } @RequestMapping("exceltodb") @ResponseBody public Map<String, Object> exceltodb(HttpServletRequest request, String json, String path) { importDB(request.getServletContext().getRealPath("").replace("\\\\", "/") + "/" + path, json); return null; } /** * 导入数据库 * * @param excelPath * Excel的路径 * @param string * Excel的解析json串 * @return */ private List<Map<String, Object>> importDB(String excelPath, String string) { // Excel对应的解析json JSONObject json = JSONObject.fromObject(string); if (null == json) { System.err.println("不是个json串"); return null; } // 获取文件 File file = new File(excelPath); if (!file.exists() && !file.isFile()) { System.err.println("不是个文件"); return null; } List<Map<String, Object>> dataList = null; InputStream is = null; Workbook wb = null; try { if ((file != null) && (file.exists())) { String fileName = file.getCanonicalPath(); if ((fileName != null) && (!"".equals(fileName))) { int dot = fileName.lastIndexOf("."); String fileType = fileName.substring(dot); if (".xlsx".equalsIgnoreCase(fileType)) { is = new FileInputStream(file); wb = new XSSFWorkbook(is); } else if (".xls".equalsIgnoreCase(fileType)) { is = new FileInputStream(file); wb = new HSSFWorkbook(is); } } } if (wb != null) { Sheet sheet = wb.getSheetAt(0); dataList = getSheetData(sheet, json); excelToDB.insertSQLLi(dataList); } } catch (Exception e) { e.printStackTrace(); } finally { if (is != null) { try { is.close(); } catch (IOException e) { e.printStackTrace(); } } } return dataList; } /** * 拿到Excel中的数据，并最终形成往数据库表中添加的数据 * * @param sheet * @param json * @return * @throws Exception */ private List<Map<String, Object>> getSheetData(Sheet sheet, JSONObject json) throws Exception { List<Map<String, Object>> li = new ArrayList<Map<String, Object>>(); // 主表json JSONObject main = json.getJSONObject("main"); if (null == main) { return null; } // 最终返回的map Map<String, Object> map = new HashMap<String, Object>(); // 主表数据 List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>(); // 从表数据123 List<List<Map<String, Object>>> onetomanyli = new ArrayList<List<Map<String, Object>>>(); // 从表数据121 List<List<Map<String, Object>>> onetooneli = new ArrayList<List<Map<String, Object>>>(); // 全列名 Map<String, String> allColumnMap = new HashMap<String, String>(); // 列类型 Map<String, String> allColumnTppeMap = new HashMap<String, String>(); JSONArray allcolumn = json.getJSONArray("allcolumn"); for (int i = 0; i < allcolumn.size(); i++) { JSONObject jSONObject = allcolumn.getJSONObject(i); allColumnMap.put(jSONObject.optString("ecname"), jSONObject.optString("tcname")); allColumnTppeMap.put(jSONObject.optString("tcname"), jSONObject.optString("tctype")); } // 一对一关系json JSONArray onetoone = json.getJSONArray("onetoone"); // 拼接一对一关系的数据集合 if (null != onetoone && onetoone.size() > 0) { for (int k = 0; k < onetoone.size(); k++) { // 从表数据 List<Map<String, Object>> onetoonelimap = new ArrayList<Map<String, Object>>(); onetooneli.add(onetoonelimap); Map<String, Object> mapd = new HashMap<String, Object>(); mapd.put("tablename", onetoone.getJSONObject(k).optString("tablename")); mapd.put("datali", onetoonelimap); li.add(mapd); } } // 一对多关系json JSONArray onetomany = json.getJSONArray("onetomany"); // 拼接一对多关系的数据集合 if (null != onetomany && onetomany.size() > 0) { for (int k = 0; k < onetomany.size(); k++) { // 从表数据 List<Map<String, Object>> onetomanylimap = new ArrayList<Map<String, Object>>(); onetomanyli.add(onetomanylimap); Map<String, Object> mapd = new HashMap<String, Object>(); mapd.put("tablename", onetomany.getJSONObject(k).optString("tablename")); mapd.put("datali", onetomanylimap); li.add(mapd); } } // 第一行数据 Row headRow = sheet.getRow(sheet.getFirstRowNum()); // 遍历所有行 for (int i = sheet.getFirstRowNum() + 1; i < sheet.getPhysicalNumberOfRows(); i++) { // 添加一对一数据 if (null != onetoone && onetoone.size() > 0) { for (int k = 0; k < onetoone.size(); k++) { // 从表数据 onetooneli.get(k).add(new HashMap<String, Object>()); } } Row dataRow = sheet.getRow(i); // 主表数据 Map<String, Object> mainMap = new HashMap<String, Object>(); // 把要新增加的列数据填上 // 主表所有列 JSONArray increasecolumn = main.getJSONArray("increasecolumn"); if (null != increasecolumn) { for (int j = 0; j < increasecolumn.size(); j++) { switch (increasecolumn.getJSONObject(j).optString("tctype")) { case "1":// 字符串32位 mainMap.put(increasecolumn.getJSONObject(j).optString("tcname"), UniqueIdGenerator.getInstance().generate()); break; case "3":// 时间 mainMap.put(increasecolumn.getJSONObject(j).optString("tcname"), new Date()); break; default: break; } } } // 从表主表关联数据及自己新增数据 if (null != onetoone && onetoone.size() > 0) { for (int k = 0; k < onetoone.size(); k++) { JSONObject jSONObject = onetoone.getJSONObject(k); JSONArray