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 {
	@Autowired
	ImportAttendanceService importAttendanceService;
	@Autowired
	returncodeService returncodeService;
	@Resource
	private AuditOvertimeService auditOvertimeService;
	
	@RequestMapping(value = "/importAttendance", method = RequestMethod.POST, produces = "text/html;charset=UTF-8")
	@ResponseBody
	public 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{
@Autowired
BadgeNumberdao dao1;
@Autowired
ImportAttendancedao dao;


	@Override
	public int isoktoken(String token) {
		// TODO Auto-generated method stub
		int 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)
	@Override
	public int setattendance(InputStream inputStream,String month) {
		// TODO Auto-generated method stub


		try {
			Workbook hssfWorkbook = WorkbookFactory.create(inputStream);  
			Attendance attendance = null;
			List<Attendance> list = new ArrayList<Attendance>();
			// 循环工作表Sheet
			for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
				Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
				if (hssfSheet == null) {
					continue;
				}
				// 循环行Row
				for (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 exception
			throw new RuntimeException(e);
		}
		return 0;
	}






	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	@Override
	public int setattendance1(InputStream inputStream,String month) {
		// TODO Auto-generated method stub		
		try {
			Workbook hssfWorkbook = WorkbookFactory.create(inputStream);  
			Attendance attendance = null;
			int num = 0;
			List<Attendance> list = new ArrayList<Attendance>();
			// 循环工作表Sheet
			for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
				Sheet hssfSheet = hssfWorkbook.getSheetAt(2);
				if (hssfSheet == null || num > 0) {
					continue;
					}					
					 num ++;			
						// 循环行Row					
					for (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();	
											String	endTime = 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 exception
			throw new RuntimeException(e);
		}
		return 0;				
	}


	@Override
	public int importmonth(String month) {
		// TODO Auto-generated method stub
		int 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>