有一个小功能,要求是按照指定时间,计算出规定的工作日,排除国家法定节假日,周日,周六...
网上找了一些案例,但都算不准,但是它们的思路和方法可以借鉴下...于是一边想,一边改...就出来了....
国家法定假日,Java 不可能有专门的API去做调用..
所以,我的思路是,做一个页面,将当年的所有法定假日输入到Excel中,用Excel去导入至数据库,然后从数据库取出数据,去和当前时间进行比较....
Struts2版本 完整项目下载
SpringMVC版本 完整项目下载(更新于-6-15 )
其实就是Excel上传 不一样,判断工作日还是一样的.....
这个是按照Struts2 上传Excel的....
先看Demo结构图
Demo所需要的所有Jar包
web.xml
<?xml version="1.0" encoding="UTF-8"?><web-app version="2.5" xmlns="/xml/ns/javaee" xmlns:xsi="/2001/XMLSchema-instance" xsi:schemaLocation="/xml/ns/javaee /xml/ns/javaee/web-app_2_5.xsd"><!-- Struts2 过滤器 --><filter><filter-name>struts2</filter-name><filter-class>org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter</filter-class></filter><filter-mapping><filter-name>struts2</filter-name><url-pattern>/*</url-pattern></filter-mapping><welcome-file-list><welcome-file>index.jsp</welcome-file></welcome-file-list></web-app>
Struts.xml
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE struts PUBLIC"-//Apache Software Foundation//DTD Struts Configuration 2.3//EN""/dtds/struts-2.3.dtd"><struts><package name="default" namespace="" extends="struts-default"><action name="startAction" method="" class="com.hav.action.MainAction"><result name="success">/result.jsp</result></action><action name="loadRoleFile" method="loadRoleFile" class="com.hav.action.MainAction"><result name="success">/index.jsp</result></action></package><constant name="struts.multipart.saveDir" value="/tmp"></constant></struts>
mybatis_Config.xml
<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE configuration PUBLIC "-////DTD Config 3.0//EN" "/dtd/mybatis-3-config.dtd"><configuration><environments default="development"><environment id="development"><transactionManager type="JDBC" /><!-- 配置数据库连接信息 --><dataSource type="POOLED"><property name="driver" value="com.mysql.jdbc.Driver"/><property name="url" value="jdbc:mysql://localhost:3306/testmysql"/><property name="username" value="root"/><property name="password" value="1234"/></dataSource></environment></environments><!-- sql语句的配置文件 --><mappers><mapper resource="com/hav/sqlConfig/Holiday.xml"/></mappers></configuration>
mysql 建表语句
create table holiday_table(id int PRIMARY key AUTO_INCREMENT,holiday varchar(50),year varchar(50));
sqlConfig 中的 Holiday.xml
<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN""/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.hav.dao.HolidayDaoImpl"><select id="getHolidays" resultType="com.hav.model.Holiday">select * from holiday_table order by id</select><insert id="insertHolidays" parameterType="map">insert into holiday_table (holiday,year)values(#{holiday},#{year})</insert><select id="getHolidayByHoliday" resultType="com.hav.model.Holiday" parameterType="map">select * from holiday_table where holiday = #{holiday}</select><select id="getHolidayByYear" resultType="com.hav.model.Holiday" parameterType="map">select * from holiday_table where year = #{year}</select><!-- <select id="getStudentById" resultType="map" parameterType="map">select * from students where id = #{userId}</select><delete id="deleteStuentById" parameterType="map">delete from students where id = #{userId}</delete>--></mapper>
实体类
package com.hav.model;public class Holiday {private String holiday;//日期private String year;//年份public String getYear() {return year;}public void setYear(String year) {this.year = year;}public String getHoliday() {return holiday;}public void setHoliday(String holiday) {this.holiday = holiday;}public Holiday(String holiday, String year) {//super();this.holiday = holiday;this.year = year;}@Overridepublic String toString() {return "Holiday [holiday=" + holiday + ", year=" + year + "]";}public Holiday() {//super();}}
DAO层
package com.hav.dao;import java.util.List;import java.util.Map;import com.hav.model.Holiday;public interface HolidayDao {public List<Holiday> getHolidays();public int insertDataBase(Map map);public List<Holiday> getHolidayByHoliday(Map map);public List<Holiday> getHolidayByYear(Map map);}
DAO层实现
package com.hav.dao;import java.util.List;import java.util.Map;import org.apache.ibatis.session.SqlSession;import com.hav.model.Holiday;import com.hav.utils.SqlSessionUtil;public class HolidayDaoImpl implements HolidayDao {private static SqlSessionUtil sqlSession = new SqlSessionUtil();private static SqlSession session = sqlSession.getSqlSession();private static String className = HolidayDaoImpl.class.getName() + ".";public List<Holiday> getHolidays() {return session.selectList(className + "getHolidays");}public int insertDataBase(Map map) {int result = 0;try{result = session.insert(className + "insertHolidays", map);mit();}catch(Exception e){e.printStackTrace();session.rollback();}return result;}public List<Holiday> getHolidayByHoliday(Map map) {return session.selectList(className+"getHolidayByHoliday",map);}public List<Holiday> getHolidayByYear(Map map) {return session.selectList(className+"getHolidayByYear",map);}}
Service层
package com.hav.service;import java.util.List;import java.util.Map;import com.hav.model.Holiday;public interface HolidayService {public List<Holiday> getHolidays();public int insertDataBase(Map map);public List<Holiday> getHolidayByHoliday(Map map);public List<Holiday> getHolidayByYear(Map map);}
Service实现
package com.hav.service;import java.text.SimpleDateFormat;import java.util.Date;import java.util.Iterator;import java.util.List;import java.util.Map;import com.hav.dao.HolidayDao;import com.hav.dao.HolidayDaoImpl;import com.hav.model.Holiday;public class HolidayServiceImpl implements HolidayService {private static HolidayDao holidayDao = new HolidayDaoImpl();@SuppressWarnings({ "rawtypes", "unchecked" })public List<Holiday> getHolidays() {List<Holiday> holidayList = holidayDao.getHolidays();Iterator it = holidayList.iterator();return holidayList;}public int insertDataBase(Map map) {return holidayDao.insertDataBase(map);}public List<Holiday> getHolidayByHoliday(Map map) {return holidayDao.getHolidayByHoliday(map);}@SuppressWarnings({ "rawtypes", "static-access" })public List<Holiday> getHolidayByYear(Map map) {List<Holiday> holiList = holidayDao.getHolidayByYear(map);return holiList;}}
调用ExcelUtils 对上传的Excel文件进行解析
package com.hav.utils;import java.io.File;import java.io.FileInputStream;import java.text.NumberFormat;import java.util.ArrayList;import java.util.Arrays;import java.util.HashMap;import java.util.LinkedHashMap;import java.util.Map;import jxl.Sheet;import jxl.Workbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.DateUtil;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;/*** 读取Excel* @author CYX* @since -6-4 上午1:03:23*/public class ReaderExcelUtils {/*** 输入Excel文件,解析后返回ArrayList* * @param file* 输入的Excel文件* * @return ArrayList<Map>,其中的map以第一行的内容为键值*/@SuppressWarnings({ "unchecked", "rawtypes" })public static ArrayList<Map> ReaderExcel(File file){/** workbook : 工作簿,就是整个Excel文档* sheet : 工作表* cell : 一个单元格* row : 一行*/if(checkExcel(file)){return importToExcel(file);}//初始化返回值和字段名数组ArrayList<Map> arr = new ArrayList<Map>();String[] title;Workbook workbook = null;try{//读取Excel文件workbook = Workbook.getWorkbook(file);//总Sheet数int sheetNumber = workbook.getNumberOfSheets();System.out.println("Sheet总数: "+sheetNumber);for (int i = 0; i < sheetNumber; i++) {Sheet sheet = workbook.getSheet(i);//当前页 总记录行数和列数int rowCount = sheet.getRows();//获取行数int columeCount = sheet.getColumns();//获取列数System.out.println("总记录数 : "+rowCount);System.out.println("总列数 : "+columeCount);//第一行为字段名,所以行数大于一才执行if(rowCount > 1 && columeCount >0){//取第一列 为 字段名title = new String[columeCount];for (int j = 0; j < columeCount; j++) {title[j] = sheet.getCell(j,0).getContents().trim();}//取当前页所有值放入list中for (int h = 1; h < rowCount; h++) {//行数//LinkedHashMap dataMap = new LinkedHashMap();Map dataMap = new HashMap();for (int k = 0; k < columeCount; k++) {//列数dataMap.put(title[k], sheet.getCell(k,h).getContents());//getContents() 获取单元格的值}arr.add(dataMap);}}}}catch(Exception e){e.printStackTrace();}finally{if(workbook != null){workbook.close();workbook = null;}}return arr;}/*** 输入版以上excel文件,解析后返回ArrayList* @param file* @return*/@SuppressWarnings("rawtypes")public static ArrayList<Map> importToExcel(File file){ArrayList<Map> arr = new ArrayList<Map>();String[] title;//初始化FileInputStream readFile = null;XSSFWorkbook workbook = null;XSSFRow row = null;XSSFSheet sheet = null;XSSFCell cell = null;try{//读取文件readFile = new FileInputStream(file);workbook = new XSSFWorkbook(readFile);//文档页数int numOfSheets = workbook.getNumberOfSheets();System.out.println("文档页数 : "+numOfSheets);for (int i = 0; i < numOfSheets; i++) {//获取当前的sheet(工作表)sheet = workbook.getSheetAt(i);//获取当前页的行数int sheetRows = sheet.getLastRowNum();System.out.println("当前页总行数 : "+sheetRows);//如果当前页行数大于0,则先取第一行为字段名if(sheetRows > 0){row = sheet.getRow(0);//当前页 第一行int cells = row.getLastCellNum();//第一行 单元格数量title = new String[cells];for (int j = 0; j < cells; j++) {//列为空,则输入空字符串if(row.getCell(j) == null){title[j] = "";continue;}cell = row.getCell(j);switch (cell.getCellType()) {case Cell.CELL_TYPE_NUMERIC:{Integer num = new Integer((int) cell.getNumericCellValue());title[j] = String.valueOf(num);break;}case Cell.CELL_TYPE_STRING:{title[j] = cell.getRichStringCellValue().toString();break;}default:title[j] = "";}}//分行解析for (int j = 0; j < sheetRows; j++) {//如果是空行,则继续下一条if(sheet.getRow(j) == null){continue;}//将每行数据放入map中row = sheet.getRow(j);arr.add(getCellMap(row,cells,title));}}}}catch(Exception e){e.printStackTrace();}finally{try {readFile.close();} catch (Exception e) {e.printStackTrace();}}return arr;}/*** 根据文件扩展名判断是否是Excel 以上* @param file* @return*/private static boolean checkExcel(File file){String extendName = file.getName().substring(file.getName().lastIndexOf("."));if(extendName.equals(".xlsx")){return true;}return false;}/***根据传入的Excel行数据,得到Map数据 * @param row* @param cells* @param title* @return*/@SuppressWarnings({ "unchecked", "rawtypes" })private static Map getCellMap(XSSFRow row , int cells , String[] title){//初始化Map data = new HashMap();XSSFCell cell = null;//分列for (int i = 0; i < cells; i++) {//列为空,则输入空字符串if(row.getCell(i) == null){data.put(title[i], "");continue;}cell = row.getCell(i);switch (cell.getCellType()) {case Cell.CELL_TYPE_NUMERIC:{if(DateUtil.isCellDateFormatted(cell)){data.put(title[i], cell.getDateCellValue());}else{NumberFormat nf = NumberFormat.getInstance();nf.setGroupingUsed(false);data.put(title[i], nf.format(cell.getNumericCellValue()));}break;}case Cell.CELL_TYPE_STRING:{data.put(title[i], cell.getRichStringCellValue());break;}case Cell.CELL_TYPE_BOOLEAN:{data.put(title[i], cell.getBooleanCellValue());break;}default:data.put(title[i], "");}}return data;}}
SqlSessionUtil
package com.hav.utils;import java.io.InputStream;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;public class SqlSessionUtil {public SqlSession getSqlSession() {String configFile = "mybatis_Config.xml";// 使用类加载器 加载mybatis的配置文件,mybatis配置文件中配置了关联映射文件InputStream inputStream = SqlSessionUtil.class.getClassLoader().getResourceAsStream(configFile);// 构建sqlSession工厂SqlSessionFactory sqlsessionFactory = new SqlSessionFactoryBuilder().build(inputStream);SqlSession sqlSession = sqlsessionFactory.openSession();return sqlSession;}}
Action
package com.hav.action;import java.io.File;import java.io.IOException;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.HashMap;import java.util.Iterator;import java.util.List;import java.util.Map;import mons.io.FileUtils;import org.apache.struts2.ServletActionContext;import com.hav.model.Holiday;import com.hav.model.Students;import com.hav.service.HolidayService;import com.hav.service.HolidayServiceImpl;import com.hav.utils.ReaderExcelUtils;import com.opensymphony.xwork2.ActionSupport;import freemarker.template.SimpleDate;@SuppressWarnings("serial")public class MainAction extends ActionSupport {private static String SUCCESS = "success";private static String ERROR = "error";private List<Students> stuList;private List<Holiday> holidayList;private static HolidayService holidayService = new HolidayServiceImpl();private static ReaderExcelUtils reu = new ReaderExcelUtils();private File uploadFile;private String uploadFileFileName;@Overridepublic String execute() throws Exception {List<Holiday> holiList = holidayService.getHolidays();System.out.println(holiList);holidayList = holiList;return SUCCESS;}/*** 保存上传的文件* * @return* @throws Exception */@SuppressWarnings("static-access")public String loadRoleFile() throws Exception {String directory = "/upload/role";String targetDirectory = ServletActionContext.getServletContext().getRealPath(directory);// 生成上传的文件对象File target = new File(targetDirectory, uploadFileFileName);// 如果文件已经存在,则删除原有文件if (target.exists()) {target.delete();}// 复制file对象,实现上传try {// FileUtils.copyFile(file1 , file2) 将file1 拷贝到file2FileUtils.copyFile(uploadFile, target);System.out.println("文件导入成功");} catch (IOException e) {e.printStackTrace();}System.out.println(uploadFileFileName);List<Map> listMap = reu.ReaderExcel(target);System.out.println(listMap);insertDB(listMap);return SUCCESS;}/*** 将数据保存至数据库* * @param listMap* @throws Exception */@SuppressWarnings({ "unchecked", "rawtypes" })public static void insertDB(List<Map> listMap) throws Exception {Iterator it = listMap.iterator();while (it.hasNext()) {Map itMap = (Map) it.next();Map holidayMap = new HashMap();//时间格式转换SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd");String strHolidayDate = sdf2.format(sdf.parse(itMap.get("时间").toString()));holidayMap.put("year", itMap.get("年份"));holidayMap.put("holiday", strHolidayDate);//判断数据库中是否存在List<Holiday> HolidayList = holidayService.getHolidayByHoliday(holidayMap);if (HolidayList.size() > 0) {System.out.println(itMap.get("时间") + "数据库中已存在");continue;} else {int result = holidayService.insertDataBase(holidayMap);System.out.println("插入" + result + "条数据");}}}public List<Students> getStuList() {return stuList;}public void setStuList(List<Students> stuList) {this.stuList = stuList;}public List<Holiday> getHolidayList() {return holidayList;}public void setHolidayList(List<Holiday> holidayList) {this.holidayList = holidayList;}public File getUploadFile() {return uploadFile;}public void setUploadFile(File uploadFile) {this.uploadFile = uploadFile;}public String getUploadFileFileName() {return uploadFileFileName;}public void setUploadFileFileName(String uploadFileFileName) {this.uploadFileFileName = uploadFileFileName;}}
index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%><html><head><title>导入界面</title><script src="jQuery2.1/jquery-2.1.0.js" type="text/javascript"></script><script type="text/javascript">$(function(){submit();});function submit(){$("#form").submit();}</script></head><body><form action="startAction" method="post" id="form"></form></body></html>
result.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%><%@ taglib uri="/struts-tags" prefix="s"%><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>Insert title here</title><script src="jQuery2.1/jquery-2.1.0.js" type="text/javascript"></script><style type="text/css">#table th {background-color: gray;border-bottom: #ddd solid 1px;border-right: #ddd solid 1px;overflow: hidden;white-space: nowrap;}td {overflow: hidden;white-space: nowrap;border-bottom: #ddd solid 1px;border-right: #ddd solid 1px;}</style><script type="text/javascript">function checkForm(flag) {if ($("input[name='fileUpUrl']").val() == '') {$.showWarn('请选择需要上传的文件');return false;}var fileUrl = $("input[name='fileUpUrl']").val();$("form[name='frm']").submit();}</script></head><body><!-- --><s:form id="form1" name="form1" action="loadRoleFile"method="post" enctype="multipart/form-data" theme="simple"><div align="center" id="div1" style="width: 80%"><table width="80%" border="1" align="center" class="DB_table"><tr><td colspan="99" align="left">文件上传</td></tr><tr><td colspan="99" id="more"><s:file name="uploadFile" label="文件位置" size="80" /></td></tr><tr><td colspan="99" align="right"><s:submit value="导入"></s:submit><s:reset value="重置"></s:reset></td></tr></table></div></s:form><table id="table"><th id="th1">日期</th><th id="th2">年份</th><s:iterator value="holidayList" id="holi"><tr><td><s:property value="#holi.holiday" /></td><td><s:property value="#holi.year" /></td></tr></s:iterator></table></body></html>
以上,是利用Struts2 上传Excel 至数据库 部署运行即可
************************************************************************************************************************************************************************
然后是计算工作日,排除法定节假日和周六周日的代码
这里先说明下,当天是不计算在内的,也就是说,10月1号开始工作,1号是不计算在内的,从2号开始计算
package com.hav.action;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Calendar;import java.util.Date;import java.util.HashMap;import java.util.Iterator;import java.util.List;import java.util.Map;import com.hav.model.Holiday;import com.hav.service.HolidayService;import com.hav.service.HolidayServiceImpl;public class TestHolidayMain {private static HolidayService holidayService = new HolidayServiceImpl();private static List<Calendar> holidayList = new ArrayList<Calendar>();// 节假日列表public static void main(String[] args) throws Exception {Date date = new Date();Integer numDate = 2;workDate(date, numDate);}public static void workDate(Date date, Integer numDate) throws Exception {if (numDate != 0) {// 取出数据库中的法定节假日initHolidayList();SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");Calendar ca = Calendar.getInstance();ca.setTime(date); // 设置当前时间Calendar calen = addDateByWorkDay(ca, numDate); // 传入当前时间 以及工作日天数System.out.println("截止工作日 : " + sdf.format(calen.getTime()));} else {System.out.println("工作日天数不能为 零");}}/*** 设置起始日期 和 工作日天数* * @param calendar* 起始日期* @param day* 工作日天数* @return*/public static Calendar addDateByWorkDay(Calendar calendar, int day) {try {for (int i = 0; i < day; i++) {calendar.add(Calendar.DAY_OF_MONTH, 1);boolean result = checkHoliday(calendar);if (result) {i--;}}return calendar;} catch (Exception e) {e.printStackTrace();}return calendar;}/*** 判断日期是否是节假日或周末* * @param calendar* 传入要校验的日期* @return*/public static boolean checkHoliday(Calendar calendar) {// 判断是否是节假日for (Calendar cal : holidayList) { // 节假日列表中的日期 cal// 判断是否是节假日if (cal.get(Calendar.MONTH) == calendar.get(Calendar.MONTH)&& cal.get(Calendar.DAY_OF_MONTH) == calendar.get(Calendar.DAY_OF_MONTH)&& cal.get(Calendar.YEAR) == calendar.get(Calendar.YEAR)) {System.out.println("是节假日");return true;} else {System.out.println("不是节假日");}}// 判断是否是周六周日if (calendar.get(Calendar.DAY_OF_WEEK) == Calendar.SATURDAY|| calendar.get(Calendar.DAY_OF_WEEK) == Calendar.SUNDAY) {System.out.println("是周末");return true;} else {System.out.println("不是周末");}return false;}// 将数据库中的法定假日取出来,放的List@SuppressWarnings({ "rawtypes", "unchecked" })public static void initHolidayList() throws Exception {Calendar ca = Calendar.getInstance();Map yearMap = new HashMap();yearMap.put("year", ca.get(ca.YEAR));List<Holiday> holiList = holidayService.getHolidayByYear(yearMap);Iterator it = holiList.iterator();while (it.hasNext()) {Holiday holiday = (Holiday) it.next();// 存入法定假日集合String date = holiday.getHoliday();SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");Date fmtDate = sdf.parse(date);Calendar calendar = Calendar.getInstance();calendar.setTime(fmtDate);holidayList.add(calendar);}}}
上面这段代码 是单独运行的,通过Main方法直接调用 测试
最后,Struts2 文件上传,参考
/fishineye/article/details/9179021
/unable-to-find-struts-multipart-savedir-struts2-upload-file-error-solution.html