@Override
  public String getPriceDate(String fileName) {
    try {
      HSSFSheet sheet = getSheet(fileName, 0);

      String marker = "Дата";

      int numOfRows = sheet.getPhysicalNumberOfRows() > 10 ? 10 : sheet.getPhysicalNumberOfRows();

      String dirtyDate = null;

      for (int i = priceConfig.getRowToStart(Brand.STELS); i < numOfRows; i++) {
        Row row = sheet.getRow(i);
        if (row.getPhysicalNumberOfCells() > 1) {
          Cell cell = row.getCell(0);
          if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            String stringCellValue = cell.getStringCellValue();
            if (stringCellValue.contains(marker)) {
              dirtyDate = stringCellValue;
              break;
            }
          }
        }
      }
      if (dirtyDate != null) {
        return dirtyDate.trim();
      }

    } catch (Exception ex) {
      LOGGER.error("FAIL to get price date - return today");
      return new Date().toString();
    }
    return new Date().toString();
  }
Ejemplo n.º 2
0
 /**
  * POI:解析Excel文件中的数据并把每行数据封装成一个实体
  *
  * @param fis 文件输入流
  * @return List<EmployeeInfo> Excel中数据封装实体的集合
  */
 public static List<String> importControlsByPoi(InputStream fis) {
   // 这里是解析出来的Excel的数据存放的List集合
   List<String> controls = new ArrayList<String>();
   try {
     // 创建Excel工作薄
     HSSFWorkbook hwb = new HSSFWorkbook(fis);
     // 得到第一个工作表
     HSSFSheet sheet = hwb.getSheetAt(0);
     HSSFRow row = null;
     // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数
     for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
       sheet = hwb.getSheetAt(i);
       // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
       for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
         row = sheet.getRow(j);
         // 此方法调用getCellValue(HSSFCell cell)对解析出来的数据进行判断,并做相应的处理
         if (ImportExcelByPoi.getCellValue(row.getCell(0)) != null
             && !"".equals(ImportExcelByPoi.getCellValue(row.getCell(0)))) {
           controls.add(String.valueOf(ImportExcelByPoi.getCellValue(row.getCell(0))));
         }
       }
     }
   } catch (IOException e) {
     e.printStackTrace();
   }
   return controls;
 }
Ejemplo n.º 3
0
  /**
   * 读取 Excel文件内容
   *
   * @param excel_name
   * @return
   * @throws Exception
   */
  public static List<List<Object>> readExcelByInputStream(InputStream inputstream)
      throws Exception {
    // 结果集
    List<List<Object>> list = new ArrayList<List<Object>>();

    HSSFWorkbook hssfworkbook = new HSSFWorkbook(inputstream);

    // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数
    HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);

    // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数

    // //System.out.println("excel行数: "+hssfsheet.getPhysicalNumberOfRows());
    for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
      HSSFRow hssfrow = hssfsheet.getRow(j);
      if (hssfrow != null) {
        int col = hssfrow.getPhysicalNumberOfCells();
        // 单行数据
        List<Object> arrayString = new ArrayList<Object>();
        for (int i = 0; i < col; i++) {
          HSSFCell cell = hssfrow.getCell(i);
          if (cell == null) {
            arrayString.add("");
          } else if (cell.getCellType() == 0) {
            arrayString.add(new Double(cell.getNumericCellValue()).toString());
          } else { // 如果EXCEL表格中的数据类型为字符串型
            arrayString.add(cell.getStringCellValue().trim());
          }
        }
        list.add(arrayString);
      }
    }
    return list;
  }
Ejemplo n.º 4
0
  /**
   * 判断报表是否可以输出
   *
   * @param sheet -- 报表对象
   * @return
   */
  public static boolean isAllowOut(HSSFSheet sheet) {
    boolean ret = true;

    if (sheet.getPhysicalNumberOfRows() > 50000) {
      _log.showWarn("报表输出行数超出了最大行数:50000!");
      ret = false;
    }

    return ret;
  }
Ejemplo n.º 5
0
  public void mapChampsdeExcel() {
    openFichierOrdreDB2();

    msg(nomTableDb2 + " mapChampsdeExcel dans la table memoire"); // $NON-NLS-1$

    if (wb != null) {
      HSSFSheet sheet = wb.getSheet(nomTableDb2);
      // HSSFSheet sheet = wb.getSheetAt(0);
      HSSFRow row;
      HSSFCell cell;

      int rows; // No of rows
      rows = sheet.getPhysicalNumberOfRows();

      int cols = xlsNombreDeColonne(sheet, rows);

      Excel2Plat = createArrayList(cols);
      Plat2Excel = createArrayList(cols);
      rowEnTete = -1;
      DB2debutOrdreDB2();
      for (int r = 0; r < rows; r++) {
        row = sheet.getRow(r);

        if (row != null) {

          if (rowEnTete == -1 && (r == 0 || r == 1)) {
            xlsRechercheEntete(row, cols, r);
          } else {
            if (rowEnTete >= 0) {
              msgProg(
                  nomTableDb2
                      + " ("
                      + r
                      + ")  rows d fichier excel lu "); //$NON-NLS-1$ //$NON-NLS-2$
              for (int c = 0; c < Excel2Plat.size(); c++) {
                if (Excel2Plat.get(c) > -1) {
                  cell = row.getCell(c);
                  if (cell != null) {
                    xlsPopulateValeur(cell, c);
                  }
                }
              }
              ecrireLigne2Plat();
              DB2addOrdreDB2();
              for (int i = 0; i < nbChamps; i++) {
                champs.get(i).valeurduchamp = "";
              }
            }
          }
        }
      }
      DB2ecrireOrdreDB2();
    }
    DB2fermetureOrdreDB2();
  }
Ejemplo n.º 6
0
  public void executeJob(Connection con) throws Exception {
    // input ����
    String fileName = "./기초자료/기초자료.xls";
    String sheetName = "GHOST_TABLES";
    FileInputStream fis = null;
    HSSFWorkbook wb = null;
    HSSFSheet sheet = null;

    // output ����
    String deleteSQL = "delete from ghost_tables";
    String insertSQL = "insert into ghost_tables values(?,?,?)";
    PreparedStatement pstmt = null;
    Statement stmt = null;

    try {
      // Input �ڷ� �غ�
      fis = new FileInputStream(fileName);
      wb = new HSSFWorkbook(fis);
      sheet = wb.getSheet(sheetName);

      // �����ڷ� All ����
      stmt = con.createStatement();
      stmt.execute(deleteSQL);

      // Insert�� SQL Statement ����
      pstmt = con.prepareStatement(insertSQL);

      HSSFRow row = null;
      Bean bean = null;

      for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
        row = sheet.getRow(i);
        readCount++;

        if (row == null) continue;

        bean = getBean(row);
        pstmt.setString(1, bean.tableName);
        pstmt.setString(2, bean.tableDesc);
        pstmt.setString(3, bean.shortName);
        pstmt.executeUpdate();
        writeCount++;

        System.out.printf("[%d][%s][%s][%s]\n", i, bean.tableName, bean.tableDesc, bean.shortName);
      }
    } finally {
      if (wb != null) wb.close();
      if (fis != null) fis.close();
      JDBCUtil.close(stmt);
      JDBCUtil.close(pstmt);
    }
  }
Ejemplo n.º 7
0
 static StringBuilder readSheet(HSSFSheet sheet) {
   StringBuilder json_sheet = new StringBuilder(1024 * 100);
   int row_length = sheet.getPhysicalNumberOfRows();
   Iterator<Row> rows = sheet.rowIterator();
   HSSFRow row;
   if (rows.hasNext()) row = (HSSFRow) rows.next();
   else return null;
   int col_length = row.getPhysicalNumberOfCells();
   Iterator<Cell> cells = row.cellIterator();
   HSSFCell cell;
   if (cells.hasNext()) cell = (HSSFCell) cells.next();
   else return null;
   int first_row = cell.getRowIndex();
   json_sheet.append("{'categories':[");
   a:
   for (int x = first_row; x < row_length; x++) {
     row = sheet.getRow(x);
     if (x > first_row) json_sheet.append("{");
     for (int y = 0; y < col_length; y++) {
       cell = row.getCell(y);
       String value = "空白";
       switch (cell.getCellType()) {
         case HSSFCell.CELL_TYPE_NUMERIC:
           value = "" + cell.getNumericCellValue();
           // Log.v("数字",value);
           break;
         case HSSFCell.CELL_TYPE_BOOLEAN:
           // Log.v("布尔",""+cell.getBooleanCellValue());
           break a;
         case HSSFCell.CELL_TYPE_STRING:
           value = "'" + cell.getStringCellValue() + "'";
           // Log.v("文本",value);
           break;
       }
       if (y == 0) {
         if (x == first_row) continue;
         else json_sheet.append("'name':" + value + ",'type':'line','data':[");
       } else json_sheet.append(value + ",");
     }
     json_sheet.replace(json_sheet.length() - 1, json_sheet.length(), "]");
     if (x == first_row) json_sheet.append("}\n[");
     else json_sheet.append("},");
   }
   json_sheet.replace(json_sheet.length() - 1, json_sheet.length(), "]");
   // Log.v("JSON",json_sheet.toString());
   return json_sheet;
 }
Ejemplo n.º 8
0
  /**
   * 读取 Excel文件内容
   *
   * @param excel_name
   * @return
   * @throws Exception
   */
  public static List<String[]> readExcel(String excel_name) throws Exception {
    // 结果集
    List<String[]> list = new ArrayList<String[]>();

    HSSFWorkbook hssfworkbook = new HSSFWorkbook(new FileInputStream(excel_name));

    // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数
    HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);

    // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
    for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
      HSSFRow hssfrow = hssfsheet.getRow(j);
      if (hssfrow != null) {
        int col = hssfrow.getPhysicalNumberOfCells();
        // 单行数据
        String[] arrayString = new String[col];
        for (int i = 0; i < col; i++) {
          HSSFCell cell = hssfrow.getCell(i);
          if (cell == null) {
            arrayString[i] = "";
          } else if (cell.getCellType() == 0) {
            // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
            if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
              if (HSSFDateUtil.isCellDateFormatted(cell)) {
                Date d = cell.getDateCellValue();
                //						    DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                arrayString[i] = formater.format(d);
              } else {
                arrayString[i] = new BigDecimal(cell.getNumericCellValue()).longValue() + "";
              }
            }
          } else { // 如果EXCEL表格中的数据类型为字符串型
            arrayString[i] = cell.getStringCellValue().trim();
          }
        }
        list.add(arrayString);
      }
    }
    return list;
  }
Ejemplo n.º 9
0
 public String saveRoomImp(HSSFSheet sheet) throws Exception {
   String result = "";
   try {
     int rowCount = sheet.getPhysicalNumberOfRows();
     if (rowCount > 0) {
       int savenum = 0;
       for (int i = 1; i < rowCount; i++) {
         HSSFRow row = sheet.getRow(i);
         try {
           String schoolArea = ""; // 校区
           String build = ""; // 楼宇
           String roomName = ""; // 教室
           schoolArea = row.getCell((short) 0).getStringCellValue().trim(); // 校区
           build = row.getCell((short) 1).getStringCellValue().trim(); // 楼宇
           roomName = row.getCell((short) 2).getStringCellValue().trim(); // 教室
           if (findOneRoom(schoolArea, build, roomName) != null) {
             result = result + " 第" + i + "行教室已存在,未导入。<br>";
             continue;
           } else {
             TArea area = areaService.findAreaByName(schoolArea);
             TRoom room = new TRoom();
             room.setSchoolArea(area);
             room.setBuild(build);
             room.setRoomName(roomName);
             // room.setStatus(status);
             roomDAO.save(room);
           }
           savenum++;
         } catch (Exception e) {
           result = result + " 第" + i + "行,数据导入失败。<br>";
         }
       }
       result = result + " 成功导入" + savenum + "行。<br>";
     } else {
       result = result + " 没有找到有效的记录。<br>";
     }
   } catch (Exception e) {
     result = result + "导入出现异常中断。<br>";
   }
   return result;
 }
Ejemplo n.º 10
0
 public String ifRoomCanImp(HSSFSheet sheet) throws Exception {
   String result = "";
   try {
     int rowCount = sheet.getPhysicalNumberOfRows();
     if (rowCount > 0) {
       for (int i = 1; i < rowCount; i++) {
         HSSFRow row = sheet.getRow(i);
         try {
           String schoolArea = ""; // 校区
           String build = ""; // 楼宇
           String roomName = ""; // 教室
           schoolArea = row.getCell((short) 0).getStringCellValue().trim(); // 校区
           build = row.getCell((short) 1).getStringCellValue().trim(); // 楼宇
           roomName = row.getCell((short) 2).getStringCellValue().trim(); // 教室
           TArea area = areaService.findAreaByName(schoolArea);
           if (area == null) {
             result = result + "第" + i + "行校区名称错误,请核对校区名称。<br>";
             break;
           }
         } catch (NumberFormatException ne) {
           result = result + "第" + i + "行数字数据非文本类型,修改文本类型后再导入。<br> ";
         } catch (NullPointerException n) {
           result = result + "第" + i + "行有空数据,请填充后再导入。<br>";
         }
       }
     } else {
       result = "没有找到有效的记录。<br>";
     }
   } catch (Exception e) {
     result = result + "Excel文件检查异常,无法导入。<br>";
   }
   if (!"".equals(result)) {
     result = result + "导入失败。";
   }
   return result;
 }
Ejemplo n.º 11
0
  public void batchUploadHolHoldays() throws IOException {
    if (file != null && file.length() > 0) {
      HSSFRow row;
      // HSSFCell cell;
      InputStream is = new FileInputStream(file);
      HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
      // 获得excel中的第一张表
      HSSFSheet sheet = hssfWorkbook.getSheetAt(0);
      List<HolHoliday> saveList = new ArrayList<HolHoliday>();
      String yearError = "";
      String repeatError = "";
      String dayError = "";
      String loginNameError = "";
      String deptError = "";
      String operatorId = servletRequest.getParameter("userId");

      for (int i = sheet.getFirstRowNum() + 1; i < sheet.getPhysicalNumberOfRows(); i++) {
        HolHoliday holHoliday = new HolHoliday();
        row = sheet.getRow(i);

        String year = getCellData(row, 0); // 年度
        String loginName = getCellData(row, 1); // 工号
        String dayCounts = getCellData(row, 2); // 天数
        String remark = getCellData(row, 3); // 备注

        String reg = "[0-9]{4}";
        Pattern p = Pattern.compile(reg);
        Matcher m = p.matcher(year);
        boolean yearFlag = m.matches();
        if (!yearFlag) {
          yearError += (i + 1) + ",";
          continue;
        }

        List<CsUser> userList = holHolidayService.findUsersByLoginName(loginName);
        if (userList == null || userList.size() < 1) {
          loginNameError += (i + 1) + ",";
          continue;
        }
        CsUser csUser = userList.get(0);
        List<HolHoliday> list =
            holHolidayService.findByYearAndHolPersonId(year, csUser.getId() + "");
        if (list != null && list.size() > 0) {
          repeatError += (i + 1) + ",";
          continue;
        }

        // holHoliday.setHolName(csUser.getName());
        // holHoliday.setHolLoginName(csUser.getLoginName());
        // holHoliday.setDeptName(csUser.getDept());
        holHoliday.setHolId(csUser.getId() + "");
        // holHoliday.setHolId(loginName);

        if (!StringUtils.isNumeric(dayCounts)) {
          dayCounts += (i + 1) + ",";
          continue;
        }

        holHoliday.setHolYear(year);
        holHoliday.setHolDays(Long.valueOf(dayCounts));
        holHoliday.setRemark(remark);

        HolHoliday last =
            this.holHolidayService.findLastHolidaysSetByholPersonId(loginName); // 同工号,上一次的数据
        if (last != null) {
          holHoliday.setHolDaysLeft(last.getHolDaysLeft() + holHoliday.getHolDays());
          holHoliday.setHolDaysWait(last.getHolDaysWait());
        } else {
          holHoliday.setHolDaysLeft(holHoliday.getHolDays());
          holHoliday.setHolDaysWait(0l);
        }
        holHoliday.setRemoved(0l);
        if (StringUtils.isNotEmpty(operatorId)) {
          holHoliday.setOperator(Long.valueOf(operatorId));
        }
        holHoliday.setOperateTime(sdf.format(new Date()));
        saveList.add(holHoliday);
      }

      servletResponse.setCharacterEncoding("utf-8"); // 务必,防止返回文件名是乱码
      servletResponse.setContentType("ajax");
      String msg = "";
      if (StringUtils.isEmpty(yearError)
          && StringUtils.isEmpty(dayError)
          && StringUtils.isEmpty(repeatError)
          && StringUtils.isEmpty(loginNameError)) {
        try {
          holHolidayService.saveAll(saveList);
          msg = "上传成功!共有" + saveList.size() + "条数据入库!";
          servletResponse.getWriter().write("{\"message\":\"success\",\"info\":\"" + msg + "\"}");
        } catch (Exception e) {
          e.printStackTrace();
        }

      } else {
        String error1 = "", error2 = "", error3 = "", error4 = "";
        if (StringUtils.isNotEmpty(yearError)) {
          error1 = "第" + yearError.substring(0, yearError.length() - 1) + "行,年份格式错误!";
        }
        if (StringUtils.isNotEmpty(loginNameError)) {
          error4 = "第" + loginNameError.substring(0, loginNameError.length() - 1) + "行,该用户不存在!";
        }
        if (StringUtils.isNotEmpty(repeatError)) {
          error2 =
              "第" + repeatError.substring(0, repeatError.length() - 1) + "行,数据重复!该用户在该年份下已设置过公休!";
        }
        if (StringUtils.isNotEmpty(dayError)) {
          error3 = "第" + dayError.substring(0, dayError.length() - 1) + "行,公休天数格式错误!";
        }
        servletResponse
            .getWriter()
            .write(
                "{\"message\":\"error\",\"error1\":\""
                    + error1
                    + "\",\"error2\":\""
                    + error2
                    + "\",\"error3\":\""
                    + error3
                    + "\",\"error4\":\""
                    + error4
                    + "\"}");
      }
    }
  }
Ejemplo n.º 12
0
  public List<T> importExcel(File file) {
    List<T> dist = new ArrayList<T>();
    // 文件输入流
    if (file != null) {
      try {
        // 得到目标目标类的所有的字段列表
        Field[] fields = clazz.getDeclaredFields();
        // 将所有标有Annotation的字段,也就是允许导入数据的字段,放入到一个map中
        Map<String, Method> fieldMap = new HashMap<String, Method>();
        // 循环读取所有字段
        for (Field field : fields) {
          // 得到单个字段上的Annotation
          ExcelAnnotation excelAnnotation = field.getAnnotation(ExcelAnnotation.class);
          // 如果标识了Annotationd
          if (excelAnnotation != null) {
            String fieldName = field.getName();
            // 构造设置了Annotation的字段的Setter方法
            String setMethodName =
                "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
            // 构造调用的method
            Method setMethod = clazz.getMethod(setMethodName, new Class[] {field.getType()});
            // 将这个method以Annotaion的名字为key来存入
            fieldMap.put(excelAnnotation.exportName(), setMethod);
          }
        }

        FileInputStream fileInputStream = new FileInputStream(file);
        // POIFSFileSystem这是POIFS类库的主要类,它管理文件系统的整个生命周期。
        POIFSFileSystem fs = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook wb = new HSSFWorkbook(fs);

        // 将标题的文字内容放入到一个map中
        Map<Integer, String> titleMap = new HashMap<Integer, String>();

        // for (int k = 0; k < wb.getNumberOfSheets(); k++) {
        // 获取第一个Sheet
        HSSFSheet sheet = wb.getSheetAt(0);
        int rows = sheet.getPhysicalNumberOfRows();
        for (int r = 0; r < rows; r++) {
          HSSFRow row = sheet.getRow(r);
          if (row != null) {
            int cells = 27;
            // 判断整行单元格是否全为空,true为全为空
            if (!isAllCellNULL(cells, row)) {
              // 得到传入类的实例
              T tObject = clazz.newInstance();
              // 序号
              Method method = clazz.getMethod("setSerialNumber", new Class[] {String.class});
              method.invoke(tObject, ((row.getRowNum() + 1) + ""));
              for (int c = 0; c < cells; c++) {
                HSSFCell cell = row.getCell(c);
                if (cell != null) {
                  // 获取列名
                  if (r == 0) {
                    titleMap.put(cell.getColumnIndex(), cell.getStringCellValue());
                  } else {
                    // 这里得到此列的对应的标题
                    String titleString = titleMap.get(c);
                    if (titleString != null) {
                      titleString = titleString.trim();
                    }
                    // 如果这一列的标题和类中的某一列的Annotation相同,那么则调用此类的的set方法,进行设值
                    if (fieldMap.containsKey(titleString)) {
                      Method setMethod = fieldMap.get(titleString);
                      // 得到setter方法的参数
                      Type[] types = setMethod.getGenericParameterTypes();
                      // 只要一个参数
                      String xclass = String.valueOf(types[0]);

                      // 判断参数类型
                      if ("class java.lang.String".equals(xclass)) {
                        // 设置所有的cell为文本形式
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                          setMethod.invoke(
                              tObject,
                              String.valueOf(cell.getNumericCellValue())
                                  .substring(
                                      0,
                                      String.valueOf(cell.getNumericCellValue()).lastIndexOf(".")));
                        } else {
                          setMethod.invoke(
                              tObject,
                              cell.getStringCellValue().equals("")
                                  ? ""
                                  : cell.getStringCellValue());
                        }
                      } else if ("class java.util.Date".equals(xclass)) {
                        if (titleString.equals("入学时间")
                            || titleString.equals("推送日期")
                            || titleString.equals("日期")) {
                          if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {

                            setMethod.invoke(tObject, cell.getDateCellValue());
                          } else {
                            if (cell.getStringCellValue() != null
                                && !"".equals(cell.getStringCellValue())) {
                              try {

                                if (cell.getStringCellValue().trim().length() == 10) {
                                  setMethod.invoke(
                                      tObject,
                                      DateUtil.StringToDate(
                                          cell.getStringCellValue(), "yyyy-MM-dd"));
                                } else if (cell.getStringCellValue().trim().length() == 8) {
                                  setMethod.invoke(
                                      tObject,
                                      DateUtil.StringToDate(cell.getStringCellValue(), "yyyyMMdd"));

                                } else {

                                }
                              } catch (Exception e) {
                                System.out.println(cell.getStringCellValue() + "日期错误");
                                setMethod.invoke(tObject, null);
                              }
                            }
                          }
                        }
                      } else if ("class java.lang.Boolean".equals(xclass)) {
                        Boolean boolName = true;
                        if ("否".equals(cell.getStringCellValue())) {
                          boolName = false;
                        }
                        setMethod.invoke(tObject, boolName);
                      } else if ("class java.lang.Integer".equals(xclass) || "int".equals(xclass)) {
                        // 设置所有的cell为文本形式
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        if (titleString.equals("民族")) {
                          setMethod.invoke(
                              tObject,
                              ConstantsPeopleMap.getCode(cell.getStringCellValue().trim()));
                        }
                        if (titleString.equals("入学方式")) {
                          setMethod.invoke(
                              tObject,
                              ConstantsEntranceWayMap.getCode(cell.getStringCellValue().trim()));
                        }

                        if (titleString.equals("政治面貌")) {
                          setMethod.invoke(
                              tObject,
                              ConstantsPoliticalMap.getCode(cell.getStringCellValue().trim()));
                        }
                        if (titleString.equals("证件类型")) {
                          setMethod.invoke(
                              tObject, ConstantsIDMap.getCode(cell.getStringCellValue().trim()));
                        }
                        if (titleString.equals("学习中心")) {
                          setMethod.invoke(tObject, cell.getStringCellValue().trim());
                        }
                        if (titleString.equals("报读院校")) {
                          setMethod.invoke(tObject, cell.getStringCellValue().trim());
                        }
                        if (titleString.equals("招生批次")) {
                          setMethod.invoke(tObject, cell.getStringCellValue().trim());
                        }
                        if (titleString.equals("学籍批次")) {
                          setMethod.invoke(tObject, cell.getStringCellValue().trim());
                        }
                        if (titleString.equals("层次")) {
                          setMethod.invoke(tObject, cell.getStringCellValue().trim());
                        }
                        if (titleString.equals("专业")) {
                          setMethod.invoke(tObject, cell.getStringCellValue().trim());
                        }
                        if (titleString.equals("是否免试生")) {
                          if ("否".equals(cell.getStringCellValue())) {
                            setMethod.invoke(tObject, 0);
                          }
                          if ("是".equals(cell.getStringCellValue())) {
                            setMethod.invoke(tObject, 1);
                          }
                        }
                        if (titleString.equals("性别")) {
                          if ("男".equals(cell.getStringCellValue())) {
                            setMethod.invoke(tObject, Constants.SEX_MALE);
                          }
                          if ("女".equals(cell.getStringCellValue())) {
                            setMethod.invoke(tObject, Constants.SEX_FAMALE);
                          }
                        }

                      } else if ("class java.lang.Long".equals(xclass)) {
                        setMethod.invoke(tObject, new Long(cell.getStringCellValue()));
                      } else {
                        //
                      }
                    }
                  }
                }
              }

              if (r != 0) {
                dist.add(tObject);
              }
            }
          }
        }
        // }
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
    return dist;
  }
Ejemplo n.º 13
0
  /**
   * 复制来源表格中第1个SHEET中的图片到目标表格中的第1个SHEET中。
   *
   * @param destBook -- 目标表格
   * @param srcBook -- 来源表格
   */
  private static void copySheetImage(HSSFWorkbook destBook, HSSFWorkbook srcBook) {
    // 来源表单
    HSSFSheet srcSheet = srcBook.getSheetAt(0);
    // 目标表单
    HSSFSheet destSheet = destBook.getSheetAt(0);

    // 需要偏移的行数
    int endRowNum = destSheet.getPhysicalNumberOfRows();

    // 取来源表单中的图片对象
    List<HSSFPicture> lsSrcPicture = getAllPicture(srcSheet);
    _log.showDebug("----------source picture size:" + lsSrcPicture.size());

    // 取所有子图形数据,如果是主从报表且明细数据占多页时,则会报空指针错误
    List<HSSFPictureData> lsPicData = null;
    try {
      lsPicData = srcBook.getAllPictures();
    } catch (Exception e) {
      // e.printStackTrace();
      _log.showWarn("由于表单明细有多页,造成临时表的图片数据取不到,只能采用原表第1个图替代!");

      // 原表中也没有图片,则不处理图片复制了
      lsPicData = destBook.getAllPictures();
      if (lsPicData == null || lsPicData.isEmpty()) return;

      // 只取原表中第1个图片信息
      List<HSSFPictureData> destData = FactoryUtil.newList();
      for (int i = 0, n = lsSrcPicture.size(); i < n; i++) {
        destData.add(lsPicData.get(0));
      }
      lsPicData = destData;
    }
    if (lsPicData == null || lsPicData.isEmpty()) return;
    _log.showDebug("----------source data size:" + lsPicData.size());

    // data数量可能大于图片数量
    if (lsSrcPicture.size() > lsPicData.size()) {
      _log.showWarn("图片数量与数据数量不符!");
      return;
    }

    // 取图片管理器
    HSSFPatriarch destDraw = destSheet.getDrawingPatriarch();
    if (destDraw == null) {
      destDraw = destSheet.createDrawingPatriarch();
    }

    // 取原目标表单中的图片对象
    List<HSSFPicture> lsDestPicture = getAllPicture(destSheet);
    int index = lsDestPicture.size();

    for (int i = 0, n = lsSrcPicture.size(); i < n; i++) {
      // 取图片对象
      HSSFPicture picture = lsSrcPicture.get(i);
      // 根据图片序号取图片数据
      HSSFPictureData picdata = lsPicData.get(i);
      // 取图片字节信息
      byte[] datas = picdata.getData();

      // 取图片位置信息
      HSSFClientAnchor anchor = (HSSFClientAnchor) picture.getAnchor();

      // 添加行偏移值
      anchor.setRow1(anchor.getRow1() + endRowNum);
      anchor.setRow2(anchor.getRow2() + endRowNum);

      // 插入新图片,返回的新图片序号无效
      destBook.addPicture(datas, picdata.getFormat());
      // 上面代码中新建图片的序号没有考虑原有图片数量,所以取原图片数量+1作为新图片的序号
      index++;
      _log.showDebug("---------copy new image index=" + index);

      destDraw.createPicture(anchor, index);
    }
  }
Ejemplo n.º 14
0
  /**
   * 添加临时表格内容
   *
   * @param mainSheet -- 原表单内容
   * @param subSheet -- 临时表单内容
   * @return
   */
  public static HSSFSheet appendSheet(HSSFSheet mainSheet, HSSFSheet subSheet) {
    if (mainSheet == null || subSheet == null) return null;
    // 判断报表是否允许输出
    if (!isAllowOut(mainSheet)) return mainSheet;
    // 原报表的最后一行
    int endRowNum = mainSheet.getPhysicalNumberOfRows();

    HSSFRow sourow = null, descrow = null;
    HSSFCell sourcell = null, descell = null, orgcell = null;
    int i = 0, offsetcnt = 0;

    // 复制表格中的图片
    copySheetImage(mainSheet.getWorkbook(), subSheet.getWorkbook());

    // 设置以合并的单元格
    CellRangeAddress range = null;
    int mergedNum = subSheet.getNumMergedRegions();
    for (i = 0; i < mergedNum; i++) {
      range = subSheet.getMergedRegion(i);
      range.setFirstRow(range.getFirstRow() + endRowNum);
      range.setLastRow(range.getLastRow() + endRowNum);
      mainSheet.addMergedRegion(range);
    }
    range = null;
    // int k = 0;

    // 设置相关参数
    mainSheet.setAlternativeExpression(subSheet.getAlternateExpression());
    mainSheet.setAlternativeFormula(subSheet.getAlternateFormula());
    mainSheet.setAutobreaks(subSheet.getAutobreaks());
    mainSheet.setDialog(subSheet.getDialog());
    mainSheet.setDisplayGuts(subSheet.getDisplayGuts());
    mainSheet.setFitToPage(subSheet.getFitToPage());

    for (java.util.Iterator<Row> iterow = subSheet.rowIterator(); iterow.hasNext(); ) {
      sourow = (HSSFRow) iterow.next();
      offsetcnt = sourow.getRowNum() + endRowNum;
      descrow = mainSheet.createRow(offsetcnt);
      descrow.setHeight(sourow.getHeight());
      descrow.setHeightInPoints(sourow.getHeightInPoints());

      java.util.Iterator<Cell> iter = sourow.cellIterator();
      while (iter.hasNext()) {
        sourcell = (HSSFCell) iter.next();
        int column = sourcell.getColumnIndex();
        descell = descrow.createCell(column);

        // 取模板中的单元格,与来源表单位置相同
        int row = sourcell.getRowIndex();
        orgcell = mainSheet.getRow(row).getCell(column);
        if (orgcell != null) {
          // 取模板中的类型赋值
          descell.setCellType(orgcell.getCellType());
          // 取模板中的样式赋值
          descell.setCellStyle(orgcell.getCellStyle());
        } else {
          _log.showWarn("module xls [{0}, {1}] cell is null!", row, column);
        }

        if (sourcell.getCellType() == HSSFCell.CELL_TYPE_STRING)
          descell.setCellValue(sourcell.getStringCellValue());
        else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
          descell.setCellValue(sourcell.getNumericCellValue());
        else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_BLANK) ;
      }
      sourow = null;
      sourcell = null;
      descrow = null;
      orgcell = null;
    }

    return mainSheet;
  }
Ejemplo n.º 15
0
    /**
     * 读取97-2003格式
     *
     * @param filePath 文件路径
     * @throws java.io.IOException
     */
    @SuppressWarnings("rawtypes")
    public static List<Map> readExcel2003(String filePath) throws IOException {
      // 返回结果集
      List<Map> valueList = new ArrayList<Map>();
      FileInputStream fis = null;
      try {
        fis = new FileInputStream(filePath);
        HSSFWorkbook wookbook = new HSSFWorkbook(fis); // 创建对Excel工作簿文件的引用
        HSSFSheet sheet = wookbook.getSheetAt(0); // 在Excel文档中,第一张工作表的缺省索引是0
        int rows = sheet.getPhysicalNumberOfRows(); // 获取到Excel文件中的所有行数­
        Map<Integer, String> keys = new HashMap<Integer, String>();
        int cells = 0;
        // 遍历行­(第1行  表头) 准备Map里的key
        HSSFRow firstRow = sheet.getRow(0);
        if (firstRow != null) {
          // 获取到Excel文件中的所有的列
          cells = firstRow.getPhysicalNumberOfCells();
          // 遍历列
          for (int j = 0; j < cells; j++) {
            // 获取到列的值­
            try {
              HSSFCell cell = firstRow.getCell(j);
              String cellValue = getCellValue(cell);
              keys.put(j, cellValue);
            } catch (Exception e) {
              e.printStackTrace();
            }
          }
        }
        // 遍历行­(从第二行开始)
        for (int i = 1; i < rows; i++) {
          // 读取左上端单元格(从第二行开始)
          HSSFRow row = sheet.getRow(i);
          // 行不为空
          if (row != null) {
            // 准备当前行 所储存值的map
            Map<String, Object> val = new HashMap<String, Object>();

            boolean isValidRow = false;

            // 遍历列
            for (int j = 0; j < cells; j++) {
              // 获取到列的值­
              try {
                HSSFCell cell = row.getCell(j);
                String cellValue = getCellValue(cell);
                val.put(keys.get(j), cellValue);
                if (!isValidRow && cellValue != null && cellValue.trim().length() > 0) {
                  isValidRow = true;
                }
              } catch (Exception e) {
                e.printStackTrace();
              }
            }
            // 第I行所有的列数据读取完毕,放入valuelist
            if (isValidRow) {
              valueList.add(val);
            }
          }
        }
      } catch (IOException e) {
        e.printStackTrace();
      } finally {
        fis.close();
      }
      return valueList;
    }
Ejemplo n.º 16
0
 /**
  * 从一行开始,到全部数据,列的范围指定
  *
  * @param sheet
  * @param startRowIndex
  * @param startCellIndex
  * @param endCellIndex
  * @return
  */
 public static List<String[]> getCellsStringValue(
     HSSFSheet sheet, int startRowIndex, int startCellIndex, int endCellIndex) {
   // 最后一行,就是全部行数-1
   int endRowIndex = sheet.getPhysicalNumberOfRows() - 1;
   return getCellsStringValue(sheet, startRowIndex, startCellIndex, endRowIndex, endCellIndex);
 }
  @Test
  public void testSheet() {
    try {
      truckService = ctx.getBean(TruckService.class);
      annualDataService = ctx.getBean(AnnualDataFleetFuelService.class);
      personService = ctx.getBean(PersonService.class);

      StringTokenizer stringTokenizer;
      SimpleDateFormat simpleFormat = (SimpleDateFormat) DateFormat.getDateInstance();
      simpleFormat.applyPattern("dd-MMM-yyyy");

      // Open the Excel File
      URL url = this.getClass().getResource("/setupfleet/Fleet_Data_Annual.xls");
      FileInputStream fileInputStream = new FileInputStream(url.getFile());
      HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); // Open Spreadsheet Work book

      // Process each Sheet at a time. API FAILS to move to next sheets
      for (int j = 16; j <= 16; j++) {
        Date datee = null;
        String truckId = null;
        String driverPersonId = null;
        BigDecimal amount;
        Integer closingMileage;

        // Get the first Sheet(0)
        HSSFSheet worksheet = workbook.getSheetAt(j); // First Sheet is 0 , second is 1 and so on

        vehicleNumber = worksheet.getRow(4).getCell(0).toString().trim();
        employeeNumber = worksheet.getRow(4).getCell(4).toString().trim();
        System.out.println(
            "\n\n"
                + "Begin processing Sheet: "
                + j
                + " i.e. "
                + vehicleNumber
                + ", Driver: "
                + employeeNumber);

        // Get the Truck based of the VEHICLEnUMBER
        truck = truckService.findByVehicleNumber(vehicleNumber);
        // Set this as Start Milleage for Truck
        if (truck != null) {
          truckId = truck.getId();
        }

        // Get personId
        driver = personService.findDriverWithEmployeeNumber(employeeNumber);
        if (driver != null) {
          driverPersonId = driver.getId();
        }

        if (truck != null && driver != null) {
          // iterated through all the rows
          for (int i = 4;
              i <= worksheet.getPhysicalNumberOfRows();
              i++) { // if Row 6,  index is (5)
            try {
              datee =
                  simpleFormat.parse(
                      worksheet.getRow(i).getCell(1).toString().trim()); // String to Date
            } catch (ParseException ex) {
              Logger.getLogger(Setup_DailyInputSheetOne.class.getName())
                  .log(Level.SEVERE, null, ex);
              List<AnnualDataFleetFuel> annualDataFleetFuelList = annualDataService.findAll();
              for (AnnualDataFleetFuel annualDataFuel : annualDataFleetFuelList) {
                System.out.println(
                    "READING DATABASE - TruckId: "
                        + annualDataFuel.getTruckId()
                        + ". Date: "
                        + annualDataFuel.getTransactionDate()
                        + ". Amount: "
                        + annualDataFuel.getMonthlyFuelCost()
                        + ". Closing Mileage: "
                        + annualDataFuel.getClosingMileage()
                        + ". Driver Person ID: "
                        + annualDataFuel.getDriverPersonId());
              }
            }
            //
            amount = new BigDecimal(worksheet.getRow(i).getCell(2).toString().trim());

            if (amount.compareTo(new BigDecimal("0.0")) == 0) {
              amount = new BigDecimal("0.00");
            }
            //
            String mileageToken = worksheet.getRow(i).getCell(3).toString().trim();
            stringTokenizer = new StringTokenizer(mileageToken, ".");
            closingMileage = Integer.parseInt(stringTokenizer.nextElement().toString());
            //
            System.out.println(
                "TruckId: "
                    + truckId
                    + ". Date: "
                    + datee
                    + ". Amount: "
                    + amount.setScale(2, BigDecimal.ROUND_HALF_UP)
                    + ". Closing Mileage: "
                    + closingMileage
                    + ". Driver Person ID: "
                    + driverPersonId);

            // Build and Persist the AnnualDataFleetFuel Object
            AnnualDataFleetFuel annualData =
                createAnnualDataEntity(
                    datee,
                    amount.setScale(2, BigDecimal.ROUND_HALF_UP),
                    closingMileage,
                    truckId,
                    driverPersonId);
            annualDataService.persist(annualData);
            //                    // Build and Update the Truck Object
            //                    addTruckAnnualData(annualData);

          }
        } else {
          if (truck != null) {
            System.out.println("Truck NOT FOUND");
          }
          if (driver != null) {
            System.out.println("DRIVER NOT FOUND");
          }
        }
      }
      fileInputStream.close();

    } catch (FileNotFoundException e) {
      System.out.println("\n\n" + "File Not Found  in function setupDailyInputs()");
      System.out.println("" + e);
    } catch (IOException e) {
      System.out.println("\n\n" + "Problem Reading File: /setupfleet/Fleet_Data_Annual.xls");
      System.out.println("" + e);
    }
  }