导入EXCEL
导入EXCEL
1.传入对应的表格，转为list工具类
import java.io.File; import java.io.FileInputStream; import java.util.ArrayList; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; /** * 从EXCEL导入到数据库 * 创建人：FH 创建时间：2014年12月23日 * @version */ public class ObjectExcelRead { /** * @param filepath //文件路径 * @param filename //文件名 * @param startrow //开始行号 * @param startcol //开始列号 * @param sheetnum //sheet * @return list */ public static List<Object> readExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) { List<Object> varList = new ArrayList<Object>(); try { File target = new File(filepath, filename); FileInputStream fi = new FileInputStream(target); HSSFWorkbook wb = new HSSFWorkbook(fi); HSSFSheet sheet = wb.getSheetAt(sheetnum); //sheet 从0开始 int rowNum = sheet.getLastRowNum() + 1; //取得最后一行的行号 for (int i = startrow; i < rowNum; i++) { //行循环开始 PageData varpd = new PageData(); HSSFRow row = sheet.getRow(i); //行 int cellNum = row.getLastCellNum(); //每行的最后一个单元格位置 for (int j = startcol; j < cellNum; j++) { //列循环开始 HSSFCell cell = row.getCell(Short.parseShort(j + "")); String cellValue = null; if (null != cell) { switch (cell.getCellType()) { // 判断excel单元格内容的格式，并对其进行转换，以便插入数据库 case 0: cellValue = String.valueOf((int) cell.getNumericCellValue()); break; case 1: cellValue = cell.getStringCellValue(); break; case 2: cellValue = cell.getNumericCellValue() + ""; // cellValue = String.valueOf(cell.getDateCellValue()); break; case 3: cellValue = ""; break; case 4: cellValue = String.valueOf(cell.getBooleanCellValue()); break; case 5: cellValue = String.valueOf(cell.getErrorCellValue()); break; } } else { cellValue = ""; } varpd.put("var"+j, cellValue); } varList.add(varpd); } } catch (Exception e) { System.out.println(e); } return varList; } }
导入案例
/** * 从EXCEL导入到数据库 */ @RequestMapping(value="/readExcel") @ResponseBody public String readExcel( @RequestParam(value="excel",required=false) MultipartFile file ) throws Exception{ PageData pd = new PageData(); if(!Jurisdiction.buttonJurisdiction(menuUrl, "add")){return null;} if (null != file && !file.isEmpty()) { String filePath = PathUtil.getClasspath() + Const.FILEpathFILE; //文件上传路径 String fileName = FileUpload.fileUp(file, filePath, "userexcel"); //执行上传 List<PageData> listPd = (List)ObjectExcelRead.readExcel(filePath, fileName, 1, 0, 0); //执行读EXCEL操作,读出的数据导入List 1:从第2行开始；0:从第A列开始；0:第0个sheet /*存入数据库操作======================================*/ pd.put("rights", ""); //权限 pd.put("last_login", ""); //最后登录时间 pd.put("ip", ""); //ip pd.put("status", "0"); //状态 pd.put("skin", "default"); //默认皮肤 //得到数据；保存到数据库 for(int i=0;i<listPd.size();i++){ //查询身份证信息，如果已经存在，则跳过 if(driverService.getByIdcode(listPd.get(i).getString("var10"))>0){ continue; } PageData pd2 = new PageData(); /* [{var9=是, var8=一车队, var7=在职, var6=2018-04-03 16:29:36.0, var5=qewe, var4=加盟, var10=21, var3=21, var2=1, var1=男, var0=wqw}]*/ pd2.put("name", listPd.get(i).getString("var0")); if(listPd.get(i).get("var1")!=null&&listPd.get(i).get("var1").toString().equals("男")){ pd2.put("sex",1 ); }else{ pd2.put("sex",2 ); } pd2.put("phone", listPd.get(i).getString("var2")); pd2.put("drive_number", listPd.get(i).getString("var3")); pd2.put("signing_company", listPd.get(i).getString("var4")); pd2.put("entry_time", listPd.get(i).getString("var6")); if(listPd.get(i).get("var7")!=null&&listPd.get(i).get("var7").toString().equals("在职")){ pd2.put("state",1); }else if(listPd.get(i).get("var7")!=null&&listPd.get(i).get("var7").toString().equals("离职")){ pd2.put("state",2); }else{ pd2.put("state",3); } pd2.put("idcode", listPd.get(i).getString("var10")); pd2.put("address", listPd.get(i).getString("var11")); //批量导入时的默认密码为123456 String md5DigestAsHex = DigestUtils.md5DigestAsHex("123456".getBytes()); pd2.put("password", md5DigestAsHex); pd2.put("id", this.get32UUID()); driverService.save(pd2); } } //删除所有司机缓存 driverCacheUtil.deleteAllDriver(); return "0"; }
路径工具类
package com.skjd.util; import java.io.File; import javax.servlet.http.HttpServletRequest; import org.springframework.web.context.request.RequestContextHolder; import org.springframework.web.context.request.ServletRequestAttributes; /** * 路径工具类 * * @author * */ public class PathUtil { /** * 图片访问路径 * * @param pathType * 图片类型 visit-访问；save-保存 * @param pathCategory * 图片类别，如：话题图片-topic、话题回复图片-reply、商家图片 * @return */ public static String getPicturePath(String pathType, String pathCategory) { String strResult = ""; HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder .getRequestAttributes()).getRequest(); StringBuffer strBuf = new StringBuffer(); if ("visit".equals(pathType)) { } else if ("save".equals(pathType)) { String projectPath = PublicUtil.getPorjectPath().replaceAll("\\\\", "/"); projectPath = splitString(projectPath, "bin/"); strBuf.append(projectPath); strBuf.append("webapps/ROOT/"); } strResult = strBuf.toString(); return strResult; } private static String splitString(String str, String param) { String result = str; if (str.contains(param)) { int start = str.indexOf(param); result = str.substring(0, start); } return result; } /* * 获取classpath1 */ public static String getClasspath(){ String path = (String.valueOf(Thread.currentThread().getContextClassLoader().getResource(""))+"../../").replaceAll("file:/", "").replaceAll("%20", " ").trim(); if(path.indexOf(":") != 1){ path = File.separator + path; } return path; } /* * 获取classpath2 */ public static String getClassResources(){ String path = (String.valueOf(Thread.currentThread().getContextClassLoader().getResource(""))).replaceAll("file:/", "").replaceAll("%20", " ").trim(); if(path.indexOf(":") != 1){ path = File.separator + path; } return path; } public static String PathAddress() { String strResult = ""; HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder .getRequestAttributes()).getRequest(); StringBuffer strBuf = new StringBuffer(); strBuf.append(request.getScheme() + "://"); strBuf.append(request.getServerName() + ":"); strBuf.append(request.getServerPort() + ""); strBuf.append(request.getContextPath() + "/"); strResult = strBuf.toString();// +"ss/";//加入项目的名称 return strResult; } }
上传文件
package com.skjd.util; import java.io.File; import java.io.IOException; import java.io.InputStream; import org.apache.commons.io.FileUtils; import org.springframework.web.multipart.MultipartFile; /** * 上传文件 * 创建人：FH 创建时间：2014年12月23日 * @version */ public class FileUpload { /** * @param file //文件对象 * @param filePath //上传路径 * @param fileName //文件名 * @return 文件名 */ public static String fileUp(MultipartFile file, String filePath, String fileName){ String extName = ""; // 扩展名格式： try { if (file.getOriginalFilename().lastIndexOf(".") >= 0){ extName = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".")); } copyFile(file.getInputStream(), filePath, fileName+extName).replaceAll("-", ""); } catch (IOException e) { System.out.println(e); } return fileName+extName; } /** * 写文件到当前目录的upload目录中 * * @param in * @param fileName * @throws IOException */ private static String copyFile(InputStream in, String dir, String realName) throws IOException { File file = new File(dir, realName); if (!file.exists()) { if (!file.getParentFile().exists()) { file.getParentFile().mkdirs(); } file.createNewFile(); } FileUtils.copyInputStreamToFile(in, file); return realName; } }
