导入Excel文件
package com.huawei.iop.servlet.upload; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.servlet.ServletException; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.commons.fileupload.FileItem; import org.apache.commons.fileupload.FileUploadBase; import org.apache.commons.fileupload.disk.DiskFileItemFactory; import org.apache.commons.fileupload.servlet.ServletFileUpload; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.json.JSONObject; import org.springframework.web.context.WebApplicationContext; import org.springframework.web.context.support.WebApplicationContextUtils; import com.huawei.iop.bean.DateInfo; import com.huawei.iop.common.Developer; import com.huawei.iop.common.IopConst; import com.huawei.iop.manager.system.holiday.QueryDateManager; import com.huawei.iop.util.JSONUtils; import com.huawei.iop.util.POIUtil; public class ImpHolidayServlet extends HttpServlet { private static final long serialVersionUID = -3187434089884407741L; /** * 日期格式正则 */ private final static String pattern1 = "(([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})-" + "(((0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)-(0[1-9]|[12][0-9]|30))|(02-(0[1-9]|[1][0-9]|2[0-8]))))|" + "((([0-9]{2})(0[48]|[2468][048]|[13579][26])|((0[48]|[2468][048]|[3579][26])00))-02-29)"; /** * 日期名称正则校验 */ // private final static String pattern2 = "^[A-Za-z\u4e00-\u9fa5]+$"; private final static String pattern2 = "^[\u4e00-\u9fa5a-zA-Z0-9]+$"; @Override public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { Developer.info("enter ImpHolidayServlet.doPost()", "the request is" + request); String status = IopConst.IOP_RUN_SUCCESS; POIUtil poiUtil = POIUtil.getInstance(); List<DateInfo> dateList = new ArrayList<DateInfo>(IopConst.DEFAULTNUM); Map<String, Object> respMap = new HashMap<String, Object>(IopConst.DEFAULTNUM); WebApplicationContext wac = WebApplicationContextUtils.getRequiredWebApplicationContext(getServletContext()); InputStream in = null; XSSFWorkbook excel = null; try { // 使用Apache文件上传组件处理文件上传步骤: // 1、创建一个DiskFileItemFactory工厂 DiskFileItemFactory factory = new DiskFileItemFactory(); // 设置工厂的缓冲区的大小,当上传的文件大小超过缓冲区的大小时,就会生成一个临时文件存放到指定的临时目录当中。 factory.setSizeThreshold(1024 * 100);// 设置缓冲区的大小为100KB,如果不指定,那么缓冲区的大小默认是10KB // 设置上传时生成的临时文件的保存目录 // factory.setRepository(tempFile); // 2、创建一个文件上传解析器 ServletFileUpload upload = new ServletFileUpload(factory); // 解决上传文件名的中文乱码 upload.setHeaderEncoding("UTF-8"); // 4、使用ServletFileUpload解析器解析上传数据,解析结果返回的是一个List<FileItem>集合,每一个FileItem对应一个Form表单的输入项 List<FileItem> flist = upload.parseRequest(request); FileItem item = null; String filename = IopConst.STRING_EMPTY; String prefix = IopConst.STRING_EMPTY; XSSFSheet sheet = null; int LastRowNum = 0; Row validationRow = null; XSSFRow row = null; DateInfo dateInfo = null; QueryDateManager queryDateManager = null; List<DateInfo> checkDate = new ArrayList<DateInfo>(IopConst.DEFAULTNUM); List<DateInfo> checkHoliday = new ArrayList<DateInfo>(IopConst.DEFAULTNUM); String checkExcel = IopConst.STRING_EMPTY; String checkExcelData = IopConst.STRING_EMPTY; int length = flist.size(); for (int i = 0; i < length; i++) { item = flist.get(i); // 如果fileitem中封装的是普通输入项的数据 if (!item.isFormField() && item.getSize() != 0) { // 得到上传的文件名称, filename = item.getName(); // 处理获取到的上传文件的文件名的路径部分,只保留文件名部分 filename = filename.substring(filename.lastIndexOf("\\") + 1); // 获取文件后缀名 prefix = item.getName().substring(item.getName().lastIndexOf(".") + 1); // 获取item中的上传文件的输入流 in = item.getInputStream(); if (StringUtils.equals(prefix, "xlsm") || StringUtils.equals(prefix, "xlsx")) { // 读入excel excel = new XSSFWorkbook(in); // 读取第一个sheet页 sheet = excel.getSheetAt(0); // 获取excel的最后一行行数 LastRowNum = sheet.getLastRowNum(); // 校验excel是否为模板 validationRow = sheet.getRow(0); checkExcel = checkExcel(validationRow, status); if (checkExcel != null) { status = checkExcel; return; } // 循环行,当某一行为空时,获取到该行的行数,作为遍历行 for (int rowNum = 2; rowNum <= LastRowNum; rowNum++) { row = sheet.getRow(rowNum); if (row == null) { LastRowNum = rowNum - 1; break; } for (int j = 0; j < 2; j++) { if (IopConst.STRING_EMPTY.equals(poiUtil.getCellValue(row.getCell(0))) && IopConst.STRING_EMPTY.equals(poiUtil.getCellValue(row.getCell(1)))) { LastRowNum = rowNum - 1; break; } } } // 循环行数据 for (int rowNum = 2; rowNum <= LastRowNum; rowNum++) { row = sheet.getRow(rowNum); // 校验模板数据 checkExcelData = checkExcelData(poiUtil, row, status); if (checkExcelData != null) { status = checkExcelData; return; } dateInfo = new DateInfo(); // 遍历excel单元格,获取单元格数据,塞入DateInfo对象中 getExcelData(poiUtil, row, dateInfo); dateList.add(dateInfo); } if (dateList.isEmpty()) { status = IopConst.SpecialStatusCode.IOP_RUN_NODATA; return; } queryDateManager = (QueryDateManager) wac.getBean("queryDateManager"); checkDate = queryDateManager.checkDate(dateList); checkHoliday = queryDateManager.checkHoliday(dateList); if (checkDate.size() != 0 || checkHoliday.size() != 0) { status = IopConst.SpecialStatusCode.IOP_RUN_ERRORDATE; return; } status = queryDateManager.generDate(dateList, status); if (status != null) { return; } } else { status = IopConst.SpecialStatusCode.IOP_RUN_ERRORFORMART; } } } // 返回JSON JSONUtils jsonUtils = JSONUtils.getInstance(); respMap.put("dateList", jsonUtils.beanListToArray(dateList, true)); } catch (FileUploadBase.FileSizeLimitExceededException e) { Developer.error("ImpHolidayServlet:", e); status = IopConst.IOP_RUN_FAILSE; } catch (FileUploadBase.SizeLimitExceededException e) { Developer.error("ImpHolidayServlet:", e); status = IopConst.IOP_RUN_FAILSE; } catch (FileNotFoundException e) { Developer.error("ImpHolidayServlet:", e); status = IopConst.IOP_RUN_FAILSE; } catch (Exception e) { Developer.error("ImpHolidayServlet:", e); status = IopConst.IOP_RUN_FAILSE; } finally { if (excel != null) { excel.close(); } if (in != null) { in.close(); } respMap.put("statusCode", status); ServletOutputStream writer = response.getOutputStream(); JSONObject respObject = new JSONObject(respMap); response.setContentType("text/html;charset=UTF-8"); writer.write(respObject.toString().getBytes("UTF-8")); writer.flush(); writer.close(); } Developer.info("out ImpHolidayServlet.doPost()", " response is" + respMap); } @Override public void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { this.doPost(req, resp); } /** * 校验excel模板 * * @param validationRow * @param status */ private String checkExcel(Row validationRow, String status) { if (validationRow == null || validationRow.getCell(0) == null || validationRow.getCell(1) == null || validationRow.getCell(2) == null || validationRow.getCell(3) == null || validationRow.getCell(4) == null || !"日期".equals(validationRow.getCell(0).getStringCellValue()) || !"假日名称".equals(validationRow.getCell(1).getStringCellValue()) || !"预留1".equals(validationRow.getCell(2).getStringCellValue()) || !"预留2".equals(validationRow.getCell(3).getStringCellValue()) || !"预留3".equals(validationRow.getCell(4).getStringCellValue())) { status = IopConst.SpecialStatusCode.IOP_RUN_ERRORFORMART; return status; } return null; } /** * 遍历excel单元格 * * @param poiUtil * @param row * @param dateInfo */ private void getExcelData(POIUtil poiUtil, XSSFRow row, DateInfo dateInfo) { // 每行列数 short lastCellNum = row.getLastCellNum(); String cellValue1 = null; String cellValue2 = null; // 循环列,获取每个单元格数值赋予对应的实体bean属性 for (int cellNum = 0; cellNum < lastCellNum; cellNum++) { switch (cellNum) { case 0: cellValue1 = poiUtil.getCellValue(row.getCell(cellNum)); dateInfo.setHdate(cellValue1.substring(0, 10)); break; case 1: cellValue2 = poiUtil.getCellValue(row.getCell(cellNum)); dateInfo.setRemark(cellValue2); break; default: break; } } } /** * 校验模板中是否有空数据 * * @param poiUtil * @param row * @param status * @return */ private String checkExcelData(POIUtil poiUtil, XSSFRow row, String status) { String dataCell = IopConst.STRING_EMPTY; // 当前行存在空数据时返回错误 for (int j = 0; j < 2; j++) { dataCell = poiUtil.getCellValue(row.getCell(j)); if (IopConst.STRING_EMPTY.equals(dataCell)) { status = IopConst.SpecialStatusCode.IOP_RUN_NODATA; return status; } } // 得到起始时间 String holidayTime = poiUtil.getCellValue(row.getCell(0)); if (!holidayTime.substring(0, 10).matches(pattern1)) { // 判断日期格式是否正确 status = IopConst.SpecialStatusCode.IOP_RUN_ERRORDATEFORMAT; return status; } // 节假日名称 String holidayName = poiUtil.getCellValue(row.getCell(1)); if (!holidayName.matches(pattern2)) { // 判断日期格式是否正确 status = IopConst.SpecialStatusCode.IOP_RUN_NONAME; return status; } return null; } }