poi读取excel

最近学习了poi的用法，在项目中遇到的问题就是如果用InputStream is = new FileInputStream(path);获取不了流，之后进行了修改，代码如下：

controller层:

package com.controller;import java.io.IOException;import java.util.HashMap;import java.util.Map;import javax.annotation.Resource;import javax.servlet.ServletException;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.commons.lang.StringUtils;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.context.annotation.Scope;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RequestMethod;import org.springframework.web.bind.annotation.RequestParam;import org.springframework.web.bind.annotation.ResponseBody;import org.springframework.web.multipart.MultipartFile;import com.service.AuditOvertimeService;import com.service.ImportAttendanceService;import com.service.returncodeService;import tool.JsonTool;/** * 数据导入 * @param request * @param response * @return */@Controller@RequestMapping("")@Scope("prototype")public class ImportAttendanceController {@AutowiredImportAttendanceService importAttendanceService;@AutowiredreturncodeService returncodeService;@Resourceprivate AuditOvertimeService auditOvertimeService;@RequestMapping(value = "/importAttendance", method = RequestMethod.POST, produces = "text/html;charset=UTF-8")@ResponseBodypublic String ImportAttendance(HttpServletRequest request, HttpServletResponse response,@RequestParam(value = "upFile1", required = false) MultipartFile place1,@RequestParam(value = "upFile2", required = false) MultipartFile place2,String month)throws ServletException, IOException {String token = request.getHeader("token");int code;String msg;if (StringUtils.isNotBlank(token)) {code = importAttendanceService.isoktoken(token);msg = returncodeService.returnmsg(code); if (code == 9999) {Map<String, Object> resultMap = new HashMap<String, Object>();resultMap.put("code", code);resultMap.put("data", "");resultMap.put("msg", msg);String jsondata = JsonTool.toJson(resultMap);response.setCharacterEncoding("utf-8");return jsondata;}}int importmonth = importAttendanceService.importmonth(month);Map<String, Object> resultMap = new HashMap<String, Object>();int a = importAttendanceService.setattendance(place1.getInputStream(),month);int b =importAttendanceService.setattendance1(place2.getInputStream(),month);if(a==0&&b==0){if (importmonth==0){importAttendanceService.setattendance(place1.getInputStream(),month);importAttendanceService.setattendance1(place2.getInputStream(),month);auditOvertimeService.countEmpOverTime();resultMap.put("code", 0);resultMap.put("data", "");resultMap.put("msg", "上传成功");String jsondata = JsonTool.toJson(resultMap);response.setCharacterEncoding("utf-8");return jsondata;}resultMap.put("code", 1030);resultMap.put("data", "");resultMap.put("msg", "有重复的数据请检查后再提交");String jsondata = JsonTool.toJson(resultMap);response.setCharacterEncoding("utf-8");return jsondata;}resultMap.put("code", 1040);resultMap.put("data", "");resultMap.put("msg", "请检查选择的月份与表格是否正确！");String jsondata = JsonTool.toJson(resultMap);response.setCharacterEncoding("utf-8");return jsondata;}}

service层:

public interface ImportAttendanceService {        public int setattendance(InputStream inputStream,String month);        public int setattendance1(InputStream inputStream,String month);public int isoktoken(String token);}

考虑到excel2003跟2007兼容的问题，修改如下：

package com.service.impl;import java.io.InputStream;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;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;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import org.springframework.transaction.annotation.Propagation;import org.springframework.transaction.annotation.Transactional;import tool.ComptimeCalculate;import tool.ExcelChangeAccount;import tool.TokenInspection;import com.dao.BadgeNumberdao;import com.dao.ImportAttendancedao;import com.entity.returncode;import com.entity.ImportAttendance.Attendance;import com.service.ImportAttendanceService;@Service("ImportAttendanceService")public class ImportAttendanceServiceImpl implements ImportAttendanceService{@AutowiredBadgeNumberdao dao1;@AutowiredImportAttendancedao dao;@Overridepublic int isoktoken(String token) {// TODO Auto-generated method stubint account = TokenInspection.getAccount(token);String token1 = dao1.selecttoken(account);int code = TokenInspection.isok(token1, token);if (code == 9999) {return returncode.token_ERRO;}return returncode.SUCCESS;}/**@SuppressWarnings("unused") *  * @param s * @param t * @return true：secondTime大于firstTime */private boolean compareTime(String firstTime,String secondTime){Boolean res = false; SimpleDateFormat format = new SimpleDateFormat("HH:mm"); try { Date s = format.parse(firstTime); Date e = format.parse(secondTime); res = s.getTime() <= e.getTime(); } catch (ParseException e) { throw new RuntimeException(e); } return res;}@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)@Overridepublic int setattendance(InputStream inputStream,String month) {// TODO Auto-generated method stubtry {Workbook hssfWorkbook = WorkbookFactory.create(inputStream);  Attendance attendance = null;List<Attendance> list = new ArrayList<Attendance>();// 循环工作表Sheetfor (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);if (hssfSheet == null) {continue;}// 循环行Rowfor (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {Row hssfRow = hssfSheet.getRow(rowNum);if (hssfRow != null) {attendance = new Attendance();String account = hssfRow.getCell(1).toString();Cell name = hssfRow.getCell(2);String year = hssfRow.getCell(3).toString();year = year.substring(0, 10);String Month = year.substring(0, 7);if (!(month.equals(Month))){return 1;}Cell strTime = hssfRow.getCell(4);attendance.setStrTime(attendance.getStrTime());if (strTime!=null){  String strTime1 = hssfRow.getCell(4).toString();  attendance.setStrTime(strTime1.toString());}Cell endTime = hssfRow.getCell(6);attendance.setEndTime(attendance.getEndTime());if (endTime!=null){  String endTime1 = hssfRow.getCell(6).toString();  attendance.setEndTime(endTime1.toString());}attendance.setAccount(account.toString());attendance.setName(name.toString());attendance.setYear(year.toString());attendance.setMonth(Month);list.add(attendance);int Repetitions = dao.repetition(account,year);if (Repetitions==0) {dao.attandance(attendance);}}}}} catch (Exception e) {// TODO: handle exceptionthrow new RuntimeException(e);}return 0;}@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)@Overridepublic int setattendance1(InputStream inputStream,String month) {// TODO Auto-generated method stubtry {Workbook hssfWorkbook = WorkbookFactory.create(inputStream);  Attendance attendance = null;int num = 0;List<Attendance> list = new ArrayList<Attendance>();// 循环工作表Sheetfor (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {Sheet hssfSheet = hssfWorkbook.getSheetAt(2);if (hssfSheet == null || num > 0) {continue;} num ++;// 循环行Rowfor (int rowNum = 4; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {Row hssfRow = hssfSheet.getRow(rowNum);Row hssfRow1 = hssfSheet.getRow(2);if (hssfRow != null) {Cell gonghao = hssfRow.getCell(0);String year = hssfRow1.getCell(2).toString();year = year.substring(0, 10);year = year.replace('/','-');String Month = year.substring(0, 7);if (!(month.equals(Month))){return 1;}int startyear = Integer.parseInt(year.substring(0, 4));int startmonth = Integer.parseInt(year.substring(5, 7));int day = ComptimeCalculate.selectMax(startyear, startmonth);if(("工号：").equals(gonghao.toString().replace(" ",""))){attendance = new Attendance();Cell name = hssfRow.getCell(10);String account = hssfRow.getCell(2).toString();account = ExcelChangeAccount.ChangeAccount(account);attendance.setName(name.toString());attendance.setAccount(account.toString());}else{for(int i=0;i<day;i++){Attendance attendancetemp = new Attendance();String strTime = hssfRow.getCell(i).toString();String [] arry = strTime.split("\n");attendancetemp.setAccount(attendance.getAccount());attendancetemp.setName(attendance.getName());attendancetemp.setYear(Month+"-"+(i+1)+"");attendancetemp.setMonth(Month);if(arry.length>0 && !("").equals(strTime)){attendancetemp.setStrTime(arry[0]);if(arry.length-1>0){ attendancetemp.setEndTime(arry[arry.length-1]);}}list.add(attendancetemp);strTime = attendancetemp.getStrTime();if (strTime!=null) {boolean rs = false;rs = compareTime("18:00",strTime);if(rs==true){String endTime = strTime;strTime = null;attendancetemp.setStrTime(strTime);attendancetemp.setEndTime(endTime);}}String account = attendancetemp.account;year = attendancetemp.getYear();int Repetitions = dao.repetition(account,year);if (Repetitions==0) {dao.attandance(attendancetemp);}else{strTime = attendancetemp.getStrTime();StringendTime = attendancetemp.getEndTime();String startTime = dao.selectStrTime(account, year);String overTime = dao.selectEndTime(account, year);if (strTime!=null) {if (startTime!=null) {boolean rs = false;rs = compareTime(strTime,startTime);if(rs==true){dao.modifyStrTime(attendancetemp);}}else {dao.modifyStrTime(attendancetemp);}}if (endTime!=null){if(overTime!=null){boolean rs = false;rs = compareTime(overTime,endTime);if(rs==true){dao.modifyEndTime(attendancetemp);}}else{dao.modifyEndTime(attendancetemp);}}}}}}}}} catch (Exception e) {// TODO: handle exceptionthrow new RuntimeException(e);}return 0;}@Overridepublic int importmonth(String month) {// TODO Auto-generated method stubint importmonth = dao.importmonth(month);return importmonth;}}

dao层:

public interface ImportAttendancedao {//添加考勤信息public void attandance(Attendance attendance);//查询数据库里的数据是否重复public int repetition(@Param("account")String account,@Param("year")String year);//查询数据库里的月份是否重复public int importmonth(String month);//查询当天的上班打卡时间public String selectStrTime(@Param("account")String account,@Param("year")String year);//查询当天的下班打卡时间public String selectEndTime(@Param("account")String account,@Param("year")String year);//修改同一天的上班打卡考勤数据public void modifyStrTime(Attendance attendance);//修改同一天的下班打卡考勤数据public void modifyEndTime(Attendance attendance);}

mapper:

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.dao.ImportAttendancedao"><!-- 解决表名与字段不匹配 --><!--select表示查询,它的id名称必须与DAO层接口的方法名相同,否则无法绑定 --><select id="repetition" parameterType="int" resultType="int">select  count(*) from clock where account=#{account} and year=#{year} </select><select id="importmonth" parameterType="int" resultType="int">select  count(*) from clock where month=#{month}   </select><select id="selectStrTime" parameterType="int" resultType="String">select  strTime from clock where account=#{account} and year=#{year}   </select><select id="selectEndTime" parameterType="int" resultType="String">select  endTime  from clock where account=#{account} and year=#{year}   </select><!-- 添加数据 --><insert id="attandance" parameterType="com.entity.ImportAttendance.Attendance"useGeneratedKeys="true" keyProperty="id">insert into clock(account,name,strTime,endTime,year,month)values(#{account},#{name},#{strTime},#{endTime},#{year},#{month})</insert><!-- 更新数据--><update id="modifyStrTime" parameterType="com.entity.ImportAttendance.Attendance">update clock set strTime=#{strTime}where account=#{account} and year=#{year} </update><update id="modifyEndTime" parameterType="com.entity.ImportAttendance.Attendance">update clock set endTime=#{endTime} where account=#{account} and year=#{year} </update></mapper>

前端js代码:

 <form method="post" enctype="multipart/form-data" id="file_form" acction="<%=basePath%>/test/importAttendanceController/importAttendance.do" > <laber for="file_label1" > 选择文件<input id="file_label1" type="file" name="upFile1" style="display:none;"/></label> <laber for="file_label2" > 选择文件<input id="file_label2" type="file" name="upFile2" style="display:none;"/></label> <input type = "submit"/> </form>