利用poi，jxl将Excel数据导入数据库
需求：‘需要将本地的Excel中的数据经过验证之后导入数据库，在导入数据库之前在页面上展示出来
思路：将Excel导入存到session里面 去判断有没有不合法数据 如果有阻止提交
工具类：
import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.text.SimpleDateFormat; import java.util.*; import java.util.regex.Matcher; import java.util.regex.Pattern; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; 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.ss.usermodel.WorkbookFactory; public class PoiUtils { private static int titleCellCount = 0; /** * 解析Excel * * @throws IOException * * @throws IOException * @throws InvalidFormatException * fileName: 文件名称 firstRow: 从第几行开始解析 firstColum: 从第几列开始解析 RowTotal:解析工作表的索引 * firstSheet : 从第几个sheet页开始解析 */ public static List<String[][]> excelToMap(File file, int firstRow, int firstColum, int RowTotal) throws IOException { FileInputStream is = null;// 创建文件流 Workbook workbook = null;// 创建Excel对象 // List<Map<String, String>> rowMapList = null; List<String[][]> list = null; Map<String, String> map = null; int rowCount = 0; int cellCount = 0; String[][] arr = null; try { is = new FileInputStream(file); workbook = WorkbookFactory.create(is); int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量 list = getArrsList(workbook, RowTotal); for (int s = 0; s < list.size(); s++) { Sheet sheet = workbook.getSheetAt(s);// 获取当前sheet页 rowCount = sheet.getPhysicalNumberOfRows(); // 获取当前页的总行数 for (int r = firstRow; r < rowCount; r++) { Row row2 = sheet.getRow(r); if (row2 == null) { continue; } arr = list.get(s); cellCount = row2.getPhysicalNumberOfCells(); // 获取总列数 for (int c = firstColum; c < cellCount; c++) { Cell cell = row2.getCell(c); if (cell == null) { continue; } try { String cellValue = getCellValue(cell).trim();// 获取某一个单元格里面的数据 arr[r][c] = cellValue; } catch (Exception e) { e.printStackTrace(); } } } } } catch (IOException e) { e.printStackTrace(); } catch (InvalidFormatException e) { e.printStackTrace(); } return list; } /** * 返回list里面装的二维数组 */ private static List<String[][]> getArrsList(Workbook workbook, Integer RowTotal) { // 获取总的sheet页数 List<String[][]> list = new ArrayList<String[][]>(); int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量 for (int i = 0; i < sheetCount; i++) {// i就是有多少页 // 获取每个sheet页的行数和列数 Sheet sheetAt = workbook.getSheetAt(i); int rowCount1 = sheetAt.getPhysicalNumberOfRows();// 获取出总行数 // 取某一行创建行对象来获取总列数(取开始行就行) Row row3 = sheetAt.getRow(RowTotal); if (row3 == null) { continue; } int cells = row3.getPhysicalNumberOfCells();// 总列数 // 根据每个sheet页的行数和列数创建二维数组并且装入list中返回 String[][] arr = new String[rowCount1][cells]; list.add(arr); } return list; } /** * 获取单元格信息 */ private static String getCellValue(Cell cell) { String cellValue = null; switch (cell.getCellType()) { // Excel存储日期、时间均以数值类型进行存储，读取时POI先判断是是否是数值类型，再进行判断转化 case Cell.CELL_TYPE_NUMERIC: /* * POI对Excel自定义日期格式的读取的万能处理方案： 所有日期格式都可以通过getDataFormat()值来判断 * yyyy-MM-dd----- 14 yyyy年m月d日--- 31 yyyy年m月------- 57 m月d日 * ---------- 58 HH:mm----------- 20 h时mm分 ------- 32 yyyy-m */ // 1、判断是否是日期格式 boolean isDateFormat = false; short format = cell.getCellStyle().getDataFormat(); SimpleDateFormat sdf = null; if (format == 14 || format == 31 || format == 57 || format == 58 || format == 20 || format == 32) { isDateFormat = true; // 时间 sdf = new SimpleDateFormat("yyyyMMddHHmm"); } // 2.1若是日期格式，则调用日期单元格值获取方法得到日期单元格的值 if (isDateFormat) { double value = cell.getNumericCellValue(); Date date = org.apache.poi.ss.usermodel.DateUtil .getJavaDate(value); cellValue = sdf.format(date); // 2.2若不是日期格式，则将单元格格式设置为字符串格式，调用字符串单元格值获取方法得到字符串单元格的值 } else { cell.setCellType(Cell.CELL_TYPE_STRING); cellValue = cell.getStringCellValue(); } break; default: // 将单元格格式设置为字符串格式，调用字符串单元格值获取方法得到字符串单元格的值 cell.setCellType(Cell.CELL_TYPE_STRING); cellValue = cell.getStringCellValue(); } return cellValue; } /** * 根据情况解析 * @param * @return * @throws IOException */ public Map<String, Object> packMath2(File file) throws IOException { Map<String, Object> allMap = new HashMap<String, Object>(); List<Map<String, String>> rowMapList = new ArrayList<Map<String, String>>(); List<Map<String, String>> rowpList = new ArrayList<Map<String, String>>(); List<String[][]> list = PoiUtils.excelToMap(file, 0, 0, 0); //获取sheet页 /*for (int x = 0; x < list.size(); x++) {*/ String[][] arr = list.get(0);// 获取EXCEL第一页的数据(可改为X遍历获取) int rowLength = arr.length;// 总行数 int lieLength = arr[0].length;// 总列数 for (int i = 1; i < rowLength; i++) { Map<String, String> map = new HashMap<String, String>(); for (int j = 0; j < lieLength; j++) { //第几行为表头 以表头为key 获取下面的数据 map.put(arr[0][j], arr[i][j]); } Pattern pattern1 = Pattern.compile("^(([0-9]+\\.[0-9]*[1-9][0-9]*)|([0-9]*[1-9][0-9]*\\.[0-9]+)|([0-9]*[1-9][0-9]*))$"); // 身份证号规则 Pattern pattern = Pattern.compile("^[1-9]\\d{7}((0\\d)|(1[0-2]))(([0|1|2]\\d)|3[0-1])\\d{3}$|^[1-9]\\d{5}[1-9]\\d{3}((0\\d)|(1[0-2]))(([0|1|2]\\d)|3[0-1])\\d{3}([0-9]|X)$"); // 手机号规则 //Pattern pattern1 = Pattern.compile("^[1][3,4,5,7,8][0-9]{9}$"); // 默认不满足规格 Matcher isNum1 = pattern.matcher("a"); Matcher isNum = null; //判断标识位(数字位),满足规则保存,不满足不保存 if (arr[i][1] != null && !"".equals(arr[i][1])) { if (arr[i][1] != null && !"".equals(arr[i][1])) { // 匹配序号是否满足 isNum = pattern.matcher(arr[i][1].trim()); isNum1 = pattern1.matcher(arr[i][1].trim()); } } if(!isNum1.matches()){ continue;// 不满足退出 }else { if (!isNum.matches()) { rowpList.add(map); continue;// 不满足退出 } else { rowMapList.add(map);// 满足添加 } } } /* }*/ allMap.put("success",rowMapList); allMap.put("fail",rowpList); return allMap; } }
jxl的工具类
package com.lf.common.utils; import java.io.File; import java.io.IOException; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.HashSet; import java.util.List; import java.util.Map; import java.util.Set; import org.springframework.stereotype.Service; import com.lf.utils.DateUtils; import jxl.CellView; import jxl.Workbook; import jxl.format.Alignment; import jxl.format.UnderlineStyle; import jxl.read.biff.BiffException; import jxl.write.Border; import jxl.write.BorderLineStyle; import jxl.write.Colour; import jxl.write.Label; import jxl.write.NumberFormats; import jxl.write.WritableCellFormat; import jxl.write.WritableFont; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import jxl.write.WriteException; import jxl.write.biff.RowsExceededException; @Service public class JxlWriteExcel { public boolean createTable(String header, String[] body, String filePath) { boolean createFlag = true; WritableWorkbook book; try { // 根据路径生成excel文件 book = Workbook.createWorkbook(new File(filePath)); // 创建一个sheet名为"表格" WritableSheet sheet = book.createSheet("表格", 0); // 设置NO列宽度 // 去掉整个sheet中的网格线 sheet.getSettings().setShowGridLines(false); Label tempLabel = null; // 表头输出 String[] headerArr = header.split(","); int headerLen = headerArr.length; // 循环写入表头内容 for (int i = 0; i < headerLen; i++) { tempLabel = new Label( i, 0, headerArr[i], getHeaderCellStyle()); sheet.addCell(tempLabel); sheet.setColumnView(i, 30); } // 表体输出 int bodyLen = body.length; // 循环写入表体内容 for (int j = 0; j < bodyLen; j++) { String[] bodyTempArr = body[j].split(","); for (int k = 0; k < bodyTempArr.length; k++) { WritableCellFormat tempCellFormat = null; tempCellFormat = getBodyCellStyle(); if (tempCellFormat != null) { if (k == 0 || k == (bodyTempArr.length - 1)) { tempCellFormat.setAlignment(Alignment.CENTRE); } } tempLabel = new Label( k, 1 + j, bodyTempArr[k], tempCellFormat); sheet.addCell(tempLabel); } } book.write(); book.close(); } catch (IOException e) { createFlag = false; System.out.println("EXCEL创建失败！"); e.printStackTrace(); } catch (RowsExceededException e) { createFlag = false; System.out.println("EXCEL单元设置创建失败！"); e.printStackTrace(); } catch (WriteException e) { createFlag = false; System.out.println("EXCEL写入失败！"); e.printStackTrace(); } return createFlag; } public boolean createErroeTable(String header, String[] body, String filePath ,String sheetName,int sheetNum) { boolean createFlag = true; WritableWorkbook book ; WritableSheet sheet = null ; File errorFile = new File(filePath); try { if (errorFile.exists()) { Workbook workbook = Workbook.getWorkbook(errorFile); book = workbook.createWorkbook(errorFile,workbook); sheet = book.createSheet(sheetName, 1); }else { // 根据路径生成excel文件 book = Workbook.createWorkbook(errorFile); // 创建一个sheet名为"表格" sheet = book.createSheet(sheetName, sheetNum); } // 设置NO列宽度 // 去掉整个sheet中的网格线 sheet.getSettings().setShowGridLines(false); Label tempLabel = null; // 表头输出 String[] headerArr = header.split(","); int headerLen = headerArr.length; // 循环写入表头内容 for (int i = 0; i < headerLen; i++) { tempLabel = new Label( i, 0, headerArr[i], getHeaderCellStyle()); sheet.addCell(tempLabel); sheet.setColumnView(i, 30); } // 表体输出 int bodyLen = body.length; // 循环写入表体内容 for (int j = 0; j < bodyLen; j++) { String[] bodyTempArr = body[j].split(","); for (int k = 0; k < bodyTempArr.length; k++) { WritableCellFormat tempCellFormat = null; tempCellFormat = getBodyCellStyle(); if (tempCellFormat != null) { if (k == 0 || k == (bodyTempArr.length - 1)) { tempCellFormat.setAlignment(Alignment.CENTRE); } } tempLabel = new Label( k, 1 + j, bodyTempArr[k], tempCellFormat); sheet.addCell(tempLabel); } } book.write(); book.close(); } catch (IOException e) { createFlag = false; System.out.println("EXCEL创建失败！"); e.printStackTrace(); } catch (RowsExceededException e) { createFlag = false; System.out.println("EXCEL单元设置创建失败！"); e.printStackTrace(); } catch (WriteException e) { createFlag = false; System.out.println("EXCEL写入失败！"); e.printStackTrace(); } catch (BiffException e) { System.out.println("读取EXCEL对象失败！"); e.printStackTrace(); } return createFlag; } public WritableCellFormat getHeaderCellStyle() { WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE); WritableCellFormat headerFormat = new WritableCellFormat( NumberFormats.TEXT); try { // 添加字体设置 headerFormat.setFont(font); // 设置单元格背景色：表头为黄色 headerFormat.setBackground(Colour.YELLOW); // 设置表头表格边框样式 // 整个表格线为粗线、黑色 headerFormat.setBorder(Border.ALL, BorderLineStyle.THICK, Colour.BLACK); // 表头内容水平居中显示 headerFormat.setAlignment(Alignment.CENTRE); } catch (WriteException e) { System.out.println("表头单元格样式设置失败！"); } return headerFormat; } public WritableCellFormat getBodyCellStyle() { WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE); WritableCellFormat bodyFormat = new WritableCellFormat(font); try { // 设置单元格背景色：表体为白色 bodyFormat.setBackground(Colour.WHITE); // 设置表头表格边框样式 // 整个表格线为细线、黑色 bodyFormat .setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK); } catch (WriteException e) { System.out.println("表体单元格样式设置失败！"); } return bodyFormat; } public void deleteFile(String fileName){ File file = new File(fileName); if(file.exists()){ file.delete(); System.out.println("删除文件成功"); }else{ System.out.println("文件不存在"); } } /** * 创建表格 * @module： * @author: ZhangK * @date： Aug 1, 2016 */ public String createTableByZk(String header, String[] body, String filePath) { WritableWorkbook book; String date = DateUtils.convertDateToString(new Date(), "yyyyMMddHHmmssSSS"); String fileName=date+".xls"; File file = null; if (fileName !=null|| "".equals(fileName)) { file = new File(fileName); }else{ return null; } try { // 根据路径生成excel文件 book = Workbook.createWorkbook(file); // 创建一个sheet名为"表格" WritableSheet sheet = book.createSheet("发货情况", 0); // 去掉整个sheet中的网格线 sheet.getSettings().setShowGridLines(false); Label tempLabel = null; // 表头输出 String[] headerArr = header.split(","); int headerLen = headerArr.length; // 循环写入表头内容 CellView cv = null; for (int i = 0; i < headerLen; i++) { tempLabel = new Label(i, 0, headerArr[i], getHeaderCellStyle()); sheet.addCell(tempLabel); // 设置NO列宽度 sheet.setColumnView(i, 15); } // 表体输出 int bodyLen = body.length; // 循环写入表体内容 for (int j = 0; j < bodyLen; j++) { String[] bodyTempArr = body[j].split(","); for (int k = 0; k < bodyTempArr.length; k++) { WritableCellFormat tempCellFormat = null; tempCellFormat = getBodyCellStyle(); if (tempCellFormat != null) { if (k == 0 || k == (bodyTempArr.length - 1)) { tempCellFormat.setAlignment(Alignment.CENTRE); } } tempLabel = new Label( k, 1 + j, bodyTempArr[k], tempCellFormat); sheet.addCell(tempLabel); } } book.write(); book.close(); } catch (IOException e) { fileName = null; System.out.println("EXCEL创建失败！"); e.printStackTrace(); } catch (RowsExceededException e) { fileName = null; System.out.println("EXCEL单元设置创建失败！"); e.printStackTrace(); } catch (WriteException e) { fileName = null; System.out.println("EXCEL写入失败！"); e.printStackTrace(); } return fileName; } /** * list<map>去重 * @param list * @param param_list * @return */ public String[] RemoveDuplicate(List<Map<String, String>> list,List<String> param_list) { List<Map<String, String>> listMap = new ArrayList<Map<String, String>>(); Set<Map> setMap = new HashSet<Map>(); for (Map<String, String> map1 : list) { if (setMap.add(map1)) { listMap.add(map1); } } String[] rowData_retail = new String[listMap.size()]; for (int i = 0; i < listMap.size(); i++) { Map<String, String> map = listMap.get(i); for (int j = 0; j < param_list.size(); j++) { String param_i = map.get(param_list.get(j)); param_i = map.get(param_list.get(j)) == null ? "" : map .get(param_list.get(j)); if (j == param_list.size() -1) { rowData_retail[i] = rowData_retail[i] + param_i ; }else if (j==0) { rowData_retail[i] = param_i + ","; }else { rowData_retail[i] = rowData_retail[i] + param_i + ","; } } } return rowData_retail; } public static void main(String[] args) { JxlWriteExcel jxl = new JxlWriteExcel(); String goodsHeader = "缺少商品对照编码,缺少商品对照名称"; List<Map<String, String>> error_goods_list = new ArrayList<Map<String, String>>(); List<String> list = new ArrayList<String>(); Map<String, String> error_map = new HashMap<String, String>(); error_map.put("测试", "测试"); error_map.put("测试1", "测试"); error_goods_list.add(error_map); int sheetNum = 0; list.add("测试"); list.add("测试1"); String[] rowData_goods = jxl.RemoveDuplicate(error_goods_list, list); if (rowData_goods.length != 0) { jxl.createErroeTable(goodsHeader, rowData_goods, "C:\\Users\\Administrator\\Desktop\\ceshi(1).xls","商品",sheetNum); } } }
上传的controller
@RequestMapping("/saveExcel") public ModelAndView saveExcel(HttpServletRequest request,@RequestParam(value = "file") MultipartFile file) throws IOException { ModelAndView mav = new ModelAndView(); if (!file.isEmpty()) { File excelFil = new File(file.getOriginalFilename()); FileUtils.copyInputStreamToFile(file.getInputStream(),excelFil); PoiUtils utils = new PoiUtils(); try { Map<String, Object> excellist = utils.packMath(excelFil); List<Map<String, String>> rowMapList = new ArrayList<Map<String, String>>(); List<Map<String, String>> rowpList = new ArrayList<Map<String, String>>(); rowMapList= (List<Map<String, String>>) excellist.get("success"); rowpList= (List<Map<String, String>>) excellist.get("fail"); request.getSession().setAttribute("successList",rowMapList); request.getSession().setAttribute("failList",rowpList); mav.setViewName("redirect:lookExcel"); } catch (IOException e) { e.printStackTrace(); } } else { mav.setViewName("error"); } return mav; }
保存的controller
@RequestMapping("/saveAllExcel") @ResponseBody public Object saveAllExcel(HttpServletRequest request){ JSONObject jo = new JSONObject(); List<Map<String,String>> mapList = (List<Map<String, String>>) request.getSession().getAttribute("successList"); List<Map<String,String>> failList = (List<Map<String, String>>) request.getSession().getAttribute("failList"); try{ if(failList.size()>0){ jo.put("msg","存在失败数据"); jo.put("data","001"); }else{ Integer sex = 0; Integer i = 0; for(int count = 0;count<mapList.size();count++){ EmployeeTemp employeeTemp = new EmployeeTemp()；
//解析和封装到实体类里面 employeeTemp.setName(mapList.get(count).get("姓名")); i = employeeTempService.insertExcel(employeeTemp); } if(i>0){ jo.put("msg","成功"); jo.put("msgcode","000"); }else{ jo.put("msg","修改错误"); jo.put("msgcode","002"); } jo.put("msg","成功"); jo.put("msgcode","000"); } }catch (Exception e){ LoggerUtils.error(getClass(),"系统错误",e); jo.put("msg","系统错误"); jo.put("msgcode","003"); } return jo; }
另外一种方法
@RequestMapping(value = "planImport",method = RequestMethod.POST) public ModelAndView planImport(HttpServletRequest request, @RequestParam("file") MultipartFile file){ ModelAndView mav = new ModelAndView(); try { File excelFil = new File(file.getOriginalFilename()); FileUtils.copyInputStreamToFile(file.getInputStream(),excelFil); List<WorkPlan> workPlanList = workPlanService.importPlan(excelFil); if(workPlanList.size()>=0){ request.getSession().setAttribute("wpList", workPlanList); mav.addObject("wpList", workPlanList); mav.setViewName("plan/plan_import_result"); }else{ mav.addObject("url", "planImportInit.do"); mav.addObject("info","导入的Excel文件有误,请确认格式和数据!"); mav.setViewName("error"); } } catch (Exception e) { mav.addObject("url", "planImportInit.do"); mav.addObject("info","导入的Excel文件有误,请确认格式和数据!"); mav.setViewName("error"); } return mav; }
service
@Override public List<WorkPlan> importPlan(File file) throws Exception{ List<WorkPlan> workPlanList = new ArrayList<WorkPlan>(); /*使用jxl导入*/ // Workbook wb = Workbook.getWorkbook(file); // Sheet sheet = wb.getSheet(0); // int rows = sheet.getRows(); // WorkPlan workPlan = null; // Cell cell1 = null; // DateCell dc1 = null; // Cell cell2 = null; // DateCell dc2 = null; // for(int i=4;i<rows;i++){ // workPlan = new WorkPlan(); // workPlan.setEquip(sheet.getCell(0, i).getContents()); // workPlan.setPlanContent(sheet.getCell(1,i).getContents()); // workPlan.setArea(sheet.getCell(2,i).getContents()); // workPlan.setRiskContent(sheet.getCell(3,i).getContents()); // workPlan.setWorkType(sheet.getCell(4,i).getContents()); // workPlan.setInspect(sheet.getCell(5,i).getContents()); // cell1 = sheet.getCell(6, i); // if((cell1.getContents()).equals("")){ // workPlan.setPlanTime1(null); // }else if(cell1.getType() == CellType.DATE){ // dc1 = (DateCell)cell1; // System.out.println(dc1.getDate()+"-------------"); // System.out.println(cell1.getContents()+"---------------"); // workPlan.setPlanTime1(dc1.getDate()); // }else{ // workPlan.setPlanTime1(CommonUtil.convertStringToDate(cell1.getContents(), "yyyy-MM-dd HH:mm")); // } // cell2 = sheet.getCell(7, i); // if((cell2.getContents()).equals("")){ // workPlan.setPlanTime2(null); // }else if(cell2.getType() == CellType.DATE){ // dc2 = (DateCell)cell2; // System.out.println(dc2.getDate()+"==========="); // workPlan.setPlanTime2(dc2.getDate()); // }else{ // workPlan.setPlanTime2(CommonUtil.convertStringToDate(cell2.getContents(), "yyyy-MM-dd HH:mm")); // } // workPlan.setDutyDept(sheet.getCell(8, i).getContents()); // workPlan.setDutyMan(sheet.getCell(9, i).getContents()); // workPlan.setCooperate(sheet.getCell(10, i).getContents()); // workPlan.setIntendPlan(sheet.getCell(11, i).getContents()); // if(!workPlan.getEquip().equals("")|| // !workPlan.getPlanContent().equals("")|| // !workPlan.getArea().equals("")|| // !workPlan.getPlanContent().equals("")|| // workPlan.getPlanTime1()!=null|| // workPlan.getPlanTime2()!=null|| // !workPlan.getDutyDept().equals("")|| // !workPlan.getDutyMan().equals("")|| // !workPlan.getWorkType().equals("")) // { // workPlanList.add(workPlan); // } // } /*使用poi导入*/ try { InputStream is = new FileInputStream(file); POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); int rowNum = sheet.getLastRowNum(); HSSFRow row = null; HSSFCell cell = null; WorkPlan workPlan = null; for(int i=4;i<=rowNum;i++){ row = sheet.getRow(i); workPlan = new WorkPlan(); workPlan.setEquip(row.getCell(0).getStringCellValue()); workPlan.setPlanContent(row.getCell(1).getStringCellValue()); workPlan.setArea(row.getCell(2).getStringCellValue()); workPlan.setRiskContent(row.getCell(3).getStringCellValue()); workPlan.setWorkType(row.getCell(4).getStringCellValue()); workPlan.setInspect(row.getCell(5).getStringCellValue()); workPlan.setDutyDept(row.getCell(8).getStringCellValue()); workPlan.setDutyMan(row.getCell(9).getStringCellValue()); workPlan.setCooperate(row.getCell(10).getStringCellValue()); workPlan.setIntendPlan(row.getCell(11).getStringCellValue()); cell = row.getCell(6); if(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK){ workPlan.setPlanTime1(null); }else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC&& HSSFDateUtil.isCellDateFormatted(cell)){ workPlan.setPlanTime1(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())); }else{ workPlan.setPlanTime1(CommonUtil.convertStringToDate(cell.getStringCellValue(), "yyyy-MM-dd HH:mm")); } cell = row.getCell(7); if(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK){ workPlan.setPlanTime2(null); }else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC&& HSSFDateUtil.isCellDateFormatted(cell)){ workPlan.setPlanTime2(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())); }else{ workPlan.setPlanTime2(CommonUtil.convertStringToDate(cell.getStringCellValue(), "yyyy-MM-dd HH:mm")); } if(!workPlan.getEquip().equals("")|| !workPlan.getPlanContent().equals("")|| !workPlan.getArea().equals("")|| !workPlan.getPlanContent().equals("")|| workPlan.getPlanTime1()!=null|| workPlan.getPlanTime2()!=null|| !workPlan.getDutyDept().equals("")|| !workPlan.getDutyMan().equals("")|| !workPlan.getWorkType().equals("")) { workPlanList.add(workPlan); } } return workPlanList; } catch (Exception e) { return null; } }
[@t.head][/@t.head] [@t.menu][/@t.menu] [@t.body] <section class="wrapper"> <div class="row"> <div class="col-lg-12"> <ul class="breadcrumb"> <li><a href="index.html"><i class="icon-home"></i>主页</a></li> <li>增员</li> <li class="active">增员</li> </ul> </div> </div> <div class="row"> <div class="col-md-12"> <section class="panel"> <header class="panel-heading"> <label>增员</label> </header> <div class="panel-body"> <form id ="pageForm" class="form-inline" role="form"> [#if failList ?size==0] <button type="button" class="btn btn-danger" onclick="addCo();">保存</button> [#elseif failList ?size>0] <button type="button" class="btn btn-danger" onclick="again();">重新上传</button> [/#if] <table class="table table-striped table-advance table-hover"> <thead> <tr> <th>序号</th> <th>社会保障号码</th> <th>姓名</th> <th>身份证号码</th> <th>性别</th> <th>出生日期</th> <th>缴费基数</th> <th>用工形式</th> <th>增员年月</th> <th>增员原因</th> <th>参加工作日期</th> <th>联系人</th> <th>联系电话</th> <th>家庭住址</th> <th>通讯地址省级</th> <th>通讯地址市级</th> <th>通讯地址区县级</th> <th>通讯地址补充信息</th> <th>户口所在地省级</th> <th>户口所在地市级</th> <th>户口所在地区县级</th><th>户口所在地补充信息</th> <th>报名费</th> <th>查体费</th> </tr> </thead> <tbody> [#if (failList) ??] [#list failList as em] <tr style="color:red" > <td>${em_index + 1}</td> <td>${(em.社会保障号码)!'' }</td> <td>${(em.姓名)!''}</td> <td>${(em.身份证号码)!'' }</td> <td>${(em.性别)!'' }</td> <td>${(em.出生日期)!'' }</td> <td>${(em.缴费基数)!'' }</td> <td>${(em.用工形式)!'' }</td> <td>${(em.增员年月)!'' }</td> <td>${(em.增员原因)!'' }</td> <td>${(em.参加工作日期)!'' }</td> <td>${(em.联系人)!'' }</td> <td>${(em.联系电话)!'' }</td> <td>${(em.家庭住址)!'' }</td> <td>${(em.通讯地址省级)!'' }</td> <td>${(em.通讯地址市级)!'' }</td> <td>${(em.通讯地址区县级)!'' }</td> <td>${(em.通讯地址补充信息)!'' }</td> <td>${(em.户口所在地省级)!'' }</td> <td>${(em.户口所在地市级)!'' }</td> <td>${(em.户口所在地区县级)!'' }</td> <td>${(em.户口所在地补充信息)!'' }</td> <td>${(em.报名费)!'' }</td> <td>${(em.查体费)!'' }</td> </tr> [/#list] [/#if] [#if (Session.successList) ??] [#list Session.successList as em] <tr> <td>${em_index + 1}</td> <td>${(em.社会保障号码)!'' }</td> <td>${(em.姓名)!''}</td> <td>${(em.身份证号码)!'' }</td> <td>${(em.性别)!'' }</td> <td>${(em.出生日期)!'' }</td> <td>${(em.缴费基数)!'' }</td> <td>${(em.用工形式)!'' }</td> <td>${(em.增员年月)!'' }</td> <td>${(em.增员原因)!'' }</td> <td>${(em.参加工作日期)!'' }</td> <td>${(em.联系人)!'' }</td> <td>${(em.联系电话)!'' }</td> <td>${(em.家庭住址)!'' }</td> <td>${(em.通讯地址省级)!'' }</td> <td>${(em.通讯地址市级)!'' }</td> <td>${(em.通讯地址区县级)!'' }</td> <td>${(em.通讯地址补充信息)!'' }</td> <td>${(em.户口所在地省级)!'' }</td> <td>${(em.户口所在地市级)!'' }</td> <td>${(em.户口所在地区县级)!'' }</td> <td>${(em.户口所在地补充信息)!'' }</td> <td>${(em.报名费)!'' }</td> <td>${(em.查体费)!'' }</td> </tr> [/#list] [/#if] </tbody> </table> </form> </div> </section> </div> </div> </section> <script> function addCo(){ $.ajax({ type: "post", url: "emp_temp/saveAllExcel", dataType: "json", success: function(data){ if(data.msgcode=="000"){ layer.alert(data.msg,function(index){ layer.close(index); }); }else{ layer.alert(data.msg,function(index){ layer.close(index); }); } loadPage("emp_temp/getEmpTemp"); } }); } </script> [/@t.body] [@t.foot] [/@t.foot]
