struts2+pol实现Exl导入
package com.xinghuo.jqfx.web.jqlr;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;/** * Exl导入 */public class JqfxJqlrDtAction extends FileUploadAction{ //省去一些不要的东西，只保留导入功能代码 private File excelFile;//前台传过来的文件private String excelFileFileName;//前台传过来的文件名/** * Excel导入 * @time 2013-3-9 * @author xiongmin */@SuppressWarnings("deprecation")public String daoRu(){dicChange();//字典项键值对集合HSSFWorkbook workbook = findWorkbook(excelFile);//创建文件对象if(workbook != null){List<JqfxJqlrDt> jqfxJqlrDtlList = new ArrayList<JqfxJqlrDt>();//对象集合int beginRowIndex = 4; //从excel中第几行读取int totalRows = 0; HSSFSheet sheet = workbook.getSheetAt(1);//读取第一个seet页的内容totalRows = sheet.getLastRowNum();//得到seet的总行数for (int i = beginRowIndex; i <= totalRows; i++) {JqfxJqlrDt jqfxJqlrDt = new JqfxJqlrDt();HSSFRow row = sheet.getRow(i);//得到行if (row == null) {continue;}/* 读取表格数据 */HSSFCell cell_xzqy = row.getCell((short) 1);HSSFCell cell_jjdwName = row.getCell((short) 2);HSSFCell cell_jqxz = row.getCell((short) 3);HSSFCell cell_jqlb = row.getCell((short) 4);//一下两列和成一个时间HSSFCell cell_afTime = row.getCell((short) 5);HSSFCell cell_afDate = row.getCell((short) 6);HSSFCell cell_sacw = row.getCell((short) 7);HSSFCell cell_xl = row.getCell((short) 8);HSSFCell cell_ddmc = row.getCell((short) 9);HSSFCell cell_remark = row.getCell((short) 10);HSSFCell cell_jcjId = row.getCell((short) 11);/* 向对象中填充数据 */String xzqhName = initHSSFCellToString(cell_xzqy);String xzqhId = map_xzqh.get(xzqhName);//根据Map集合键取值if(xzqhId == null){continue;}jqfxJqlrDt.setXzqhName(xzqhName);jqfxJqlrDt.setXzqhId(xzqhId);jqfxJqlrDt.setJjdwName(initHSSFCellToString(cell_jjdwName));//警情性质String jqxz = initHSSFCellToString(cell_jqxz);String jqxzId = map_jqxz.get(jqxz);if(jqxzId == null){continue;}jqfxJqlrDt.setJqxz(jqxz);jqfxJqlrDt.setJqxzId(jqxzId);//警情类别String jqlb = initHSSFCellToString(cell_jqlb);String jqlbId = map_jqlb.get(jqlb);if(jqlbId == null){continue;}jqfxJqlrDt.setJqlb(jqlb);jqfxJqlrDt.setJqlbId(jqlbId);//案发时间//如果案发日期格式正确则添加，不正确似数据无效，跳过该条数据Date faDate = initHSSFCellToDate(cell_afTime,cell_afDate);if(faDate == null){continue;}else{jqfxJqlrDt.setAfTime(faDate);}jqfxJqlrDt.setSacw(initHSSFCellToString(cell_sacw));jqfxJqlrDt.setXl(initHSSFCellToString(cell_xl));//地铁线路String xl = this.initHSSFCellToString(cell_xl);try {xl=xl.substring(0,xl.lastIndexOf("."));} catch (StringIndexOutOfBoundsException e) {}String xlId = map_xl.get(xl);if(xlId == null){continue;}jqfxJqlrDt.setXl(xl);jqfxJqlrDt.setXlId(xlId);jqfxJqlrDt.setDdmc(initHSSFCellToString(cell_ddmc));jqfxJqlrDt.setRemark(initHSSFCellToString(cell_remark));jqfxJqlrDt.setJcjId(initHSSFCellToString(cell_jcjId));//导入时流程状态为1jqfxJqlrDt.setDataFlowStatus("1");jqfxJqlrDtlList.add(jqfxJqlrDt);}//保存警情信息for (JqfxJqlrDt jqfxJqlrDt : jqfxJqlrDtlList) {//查询数据库中存不存在接警编号相同的数据boolean boole = manager.getJqfxJqlrDtjjcbh(jqfxJqlrDt.getJcjId());if(boole){//存在接警编号相同的数据，则不插入数据}else{manager.saveJqfxJqlrDt(jqfxJqlrDt);}}message.setUrl("/jqlr/jqfxJqlrDt/jqfxJqlrDt!list.action");message.setMessInfo("导入成功。");}message.setType(Message.LOCATION_URL);return "message";}/** * 得到excel文件 * @return */public HSSFWorkbook findWorkbook(File file) {if(excelFileFileName.toLowerCase().endsWith(".xls")){try {FileInputStream fileInputStream = new FileInputStream(file);//得到文件HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fileInputStream);//将文件转换为excel格式return hssfWorkbook;} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}}else{message.setUrl("/jqlr/jqfxJqlrDt/jqfxJqlrDt!excelDaoRuXuanZe.action");message.setMessInfo("导入失败，文件格式不对，请选择.xls文件。"); return null;}return null;}/** * 将表格格式转化为字符串 * @param cell * @author xiongmin */private String initHSSFCellToString(HSSFCell cell) {String initCell = "";if (cell != null) {initCell = cell.toString();}return initCell;}/** * 将表格格式转化为时间类型 */private Date initHSSFCellToDate(HSSFCell cell,HSSFCell cell2) {Date time = new Date();SimpleDateFormat formatDate = new SimpleDateFormat("yyyy-MM-dd HH:mm");String strTime = cell.toString() + " " + cell2.toString();if (cell != null) {try {time = formatDate.parse(strTime);return time;} catch (ParseException e) {e.printStackTrace();return null;}}return null;}/** * 得到格式化的系统时间 * @param date * @return */public Date findDate(){Date date = new Date();SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");try {date = dateFormat.parse(dateFormat.format(date));} catch (ParseException e) {e.printStackTrace();}return date;} //下面省去get/set方法 ......}
?
?二、页面代码
?
<%@ page contentType="text/html;charset=UTF-8"%><%@ include file="/commons/taglibs.jsp"%><html xmlns="http://www.w3.org/1999/xhtml"><head><title>地铁-警情录入</title><%@ include file="/commons/meta.jsp"%><link rel="stylesheet" href="${ctx}/public/css/text.css" type="text/css"><script language=javascript>//根据单选按钮选择导入情况function daoRu(){var obj = document.getElementsByName("excel");for(var i = 0; i < obj.length; i++){ if(obj[i].checked){ document.form1.action= obj[i].value; } } document.form1.submit();} </script></head><body><form id="form1" name="form1" method="post" enctype="multipart/form-data"><table width="98%" align="left" border="0" cellpadding="0" cellspacing="1" align="right" width="8%" height="28">请选择导入类型：</td><td width="40%" bgcolor="#eaf7ff" align="left"><input type="radio" name="excel" value="${ctx}/jqlr/jqfxJqlrDt/jqfxJqlrDt!daoRu.action" /> 地铁<input type="radio" name="excel" value="${ctx}/jqlr/jqfxJqlrGj/jqfxJqlrGj!daoRu.action" /> 公交</td></tr><tr><td width="8%" height="31" bgcolor="#eaf7ff" align="right">请选择Execl文件：</td><td width="40%" bgcolor="#eaf7ff" align="left"><input type="file" name="excelFile" /></td></tr><tr><td bgcolor="#eaf7ff" align="left" colspan="2"><input type="button" name="dr" value="导入" onclick="daoRu()" /></td></tr></table></form></body></html>?
?
?