コード例 #1
0
 @Override
 public void writeRow(List<?> row) {
   Row record = sheet.createRow(sheet.getLastRowNum() + 1);
   for (int i = 0; i < row.size(); i++) {
     Cell cell = record.createCell(i);
     Object value = row.get(i);
     if (value == null) {
       cell.setCellValue("");
     } else if (value instanceof String) {
       cell.setCellType(Cell.CELL_TYPE_STRING);
       cell.setCellValue((String) value);
     } else if (value instanceof Number) {
       cell.setCellType(Cell.CELL_TYPE_NUMERIC);
       cell.setCellValue(converters.convert(value, Double.class));
     } else if (value instanceof Date || value instanceof DateTime || value instanceof Calendar) {
       cell.setCellType(Cell.CELL_TYPE_NUMERIC);
       cell.setCellStyle(dateCellStyle);
       cell.setCellValue(converters.convert(value, Date.class));
     } else if (value instanceof Boolean) {
       cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
       cell.setCellValue((Boolean) value);
     } else {
       cell.setCellType(Cell.CELL_TYPE_STRING);
       cell.setCellValue(converters.convert(value, String.class));
     }
   }
 }
コード例 #2
0
 @Override
 public void writeHeader() {
   if (header != null) {
     Row record = sheet.createRow(0);
     for (int i = 0; i < header.size(); i++) {
       Cell cell = record.createCell(i);
       cell.setCellValue(header.get(i));
     }
   }
 }
コード例 #3
0
  @Override
  public List<String> getAnchors() {
    List<String> anchors = new ArrayList<>();

    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
      anchors.add(workbook.getSheetName(i));
    }

    return anchors;
  }
コード例 #4
0
ファイル: ExcelUtil.java プロジェクト: sdgas/arrearage
 private List<ExcelHeader> getHeaderList(Class clz) {
   List<ExcelHeader> headers = new ArrayList<ExcelHeader>();
   // 获取全部get/is方法
   Method[] ms = clz.getDeclaredMethods();
   for (Method m : ms) {
     String mn = m.getName();
     if (mn.startsWith("get") || mn.startsWith("is")) {
       if (m.isAnnotationPresent(ExcelResources.class)) {
         ExcelResources er = m.getAnnotation(ExcelResources.class);
         headers.add(new ExcelHeader(er.title(), er.order(), mn));
       }
     }
   }
   return headers;
 }
コード例 #5
0
  @Override
  public void writeItem(Map<String, ?> item) {
    List<Object> row = new ArrayList<>();

    for (String key : getHeader()) {
      row.add(item.get(key));
    }

    if (this.type != null) {
      for (int i = 0; i < row.size(); i++) {
        Class clazz = (i < this.type.size() ? this.type.get(i) : null);
        if (clazz != null) row.set(i, converters.convert(row.get(i), clazz));
      }
    }

    writeRow(row);
  }
コード例 #6
0
ファイル: ExcelUtil.java プロジェクト: sdgas/arrearage
 public List<Object> readExcel(Workbook wb, Class clz, int readLine, int tailLine) {
   Sheet sheet = wb.getSheetAt(0); // 取第一张表
   List<Object> objs = null;
   try {
     Row row = sheet.getRow(readLine); // 开始行,主题栏
     objs = new ArrayList<Object>();
     Map<Integer, String> maps = getHeaderMap(row, clz); // 设定对应的字段顺序与方法名
     if (maps == null || maps.size() <= 0)
       throw new RuntimeException("要读取的Excel的格式不正确,检查是否设定了合适的行"); // 与order顺序不符
     for (int i = readLine + 1; i <= sheet.getLastRowNum() - tailLine; i++) { // 取数据
       row = sheet.getRow(i);
       Object obj = clz.newInstance(); //   调用无参结构
       for (Cell c : row) {
         int ci = c.getColumnIndex();
         String mn = maps.get(ci).substring(3); // 消除get
         mn = mn.substring(0, 1).toLowerCase() + mn.substring(1);
         Map<String, Object> params = new HashMap<String, Object>();
         if (!"enterDate".equals(mn)) c.setCellType(Cell.CELL_TYPE_STRING); // 设置单元格格式
         else c.setCellType(Cell.CELL_TYPE_NUMERIC);
         if (this.getCellValue(c).trim().equals("是")) {
           BeanUtils.copyProperty(obj, mn, 1);
         } else if (this.getCellValue(c).trim().equals("否")) {
           BeanUtils.copyProperty(obj, mn, 0);
         } else BeanUtils.copyProperty(obj, mn, this.getCellValue(c));
       }
       objs.add(obj);
     }
   } catch (InstantiationException e) {
     e.printStackTrace();
     logger.error(e);
   } catch (IllegalAccessException e) {
     e.printStackTrace();
     logger.error(e);
   } catch (InvocationTargetException e) {
     e.printStackTrace();
     logger.error(e);
   } catch (NumberFormatException e) {
     e.printStackTrace();
     logger.error(e);
   }
   return objs;
 }
コード例 #7
0
  private Collection<Object[]> loadFromSpreadsheet(final InputStream excelFile) throws IOException {
    HSSFWorkbook workbook = new HSSFWorkbook(excelFile);

    data = new ArrayList<Object[]>();
    Sheet sheet = workbook.getSheetAt(0);

    int numberOfColumns = countNonEmptyColumns(sheet);
    List<Object[]> rows = new ArrayList<Object[]>();
    List<Object> rowData = new ArrayList<Object>();

    for (Row row : sheet) {
      if (isEmpty(row)) {
        break;
      } else {
        rowData.clear();
        for (int column = 0; column < numberOfColumns; column++) {
          Cell cell = row.getCell(column);
          rowData.add(objectFrom(workbook, cell));
        }
        rows.add(rowData.toArray());
      }
    }
    return rows;
  }
コード例 #8
0
  public void readProcessSpreadSheet() {
    processSpreadSheet = System.getProperty("processSpreadSheet");
    System.out.println("processSpreadSheet: " + processSpreadSheet);
    if (processCodesList.size() == 0) {
      if (processSpreadSheet != null && !processSpreadSheet.equals("")) {
        Workbook wb1 = null;
        try {
          wb1 = new XSSFWorkbook(processSpreadSheet);
        } catch (IOException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
        Sheet sheet = wb1.getSheetAt(0);
        Row row;
        Cell cell;

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

        int cols = 0; // No of columns
        int tmp = 0;

        // This trick ensures that we get the data properly even if it
        // doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
          row = sheet.getRow(i);
          if (row != null) {
            tmp = sheet.getRow(i).getPhysicalNumberOfCells();
            // out.println("tmp value"+tmp);
            if (tmp > cols) {
              cols = tmp;
            }
          }
        }

        ProcessDefinition tempProcessCode;
        for (int r1 = 0; r1 < rows; r1++) {
          tempProcessCode = new ProcessDefinition();

          row = sheet.getRow(r1);
          if (row != null) {
            if (row.getCell(0) != null) {
              for (int counter = 0; counter < cols; counter++) {
                cell = row.getCell((short) counter);
                // cell = row.getCell(1);
                if (counter == 0) {
                  if (cell != null) {
                    tempProcessCode.setProcessName(cell.getStringCellValue());
                  } else {
                    tempProcessCode.setProcessName("");
                  }
                } else if (counter == 1) {
                  if (cell != null) {
                    tempProcessCode.setProcessCode(cell.getStringCellValue());
                  } else {
                    tempProcessCode.setProcessCode("");
                  }
                } else if (counter == 2) {
                  if (cell != null) {
                    tempProcessCode.setIaeaCode(cell.getStringCellValue());
                  } else {
                    tempProcessCode.setIaeaCode("");
                  }
                } else if (counter == 3) {
                  if (cell != null) {
                    tempProcessCode.setProcessDescription(cell.getStringCellValue());
                    // System.out.println(tempProcessCode.getProcessDescription());
                  } else {
                    tempProcessCode.setProcessDescription("");
                    // System.out.println("Process Description EMpty");
                  }
                }
              }
            }

          } else {
            rows++;
          }
          processCodesList.add(tempProcessCode);
        }
      }
    }
    System.out.println(processCodesList.size());
  }
コード例 #9
0
ファイル: ExcelUtil.java プロジェクト: sdgas/arrearage
 private Workbook handleExcel(List objs, Class clz, boolean isXssf, String message) {
   XSSFWorkbook wb = null;
   try {
     if (isXssf) {
       XSSFWorkbook w = new XSSFWorkbook();
     } else {
       HSSFWorkbook w = new HSSFWorkbook();
     }
     wb = new XSSFWorkbook();
     XSSFDataFormat format = wb.createDataFormat();
     XSSFSheet sheet = wb.createSheet(message + "备份记录"); // 取excel工作表对象
     XSSFCellStyle cellStyle = wb.createCellStyle(); // 设置excel单元格样式
     XSSFCellStyle passwordCellStyle = wb.createCellStyle(); // 设置密码单元格样式
     XSSFDataFormat passwordFormat = wb.createDataFormat();
     passwordCellStyle.setDataFormat(passwordFormat.getFormat(";;;"));
     List<ExcelHeader> headers = getHeaderList(clz);
     Collections.sort(headers);
     sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) (headers.size() - 1)));
     Row r0 = sheet.createRow(0);
     Cell cell = r0.createCell(0);
     r0.setHeightInPoints(28);
     cell.setCellValue(message + "备份记录");
     Row r = sheet.createRow(1);
     r.setHeightInPoints(25);
     cell.setCellStyle(cellStyle);
     // 输出标题
     for (int i = 0; i < headers.size(); i++) {
       Cell cell1 = r.createCell(i);
       if (headers.get(i).getTitle().equals("密码")) cell1.setCellStyle(passwordCellStyle);
       else cell1.setCellStyle(cellStyle);
       cell1.setCellValue(headers.get(i).getTitle());
     }
     Object obj = null;
     // 输出用户资料信息
     if (message.indexOf("用户资料 ") > 0) {
       sheet.setColumnWidth(3, 32 * 150);
       sheet.setColumnWidth(4, 32 * 110);
       sheet.setColumnWidth(7, 32 * 120);
       for (int i = 0; i < objs.size(); i++) {
         r = sheet.createRow(i + 2);
         obj = objs.get(i);
         for (int j = 0; j < headers.size(); j++) {
           Cell cell2 = r.createCell(j);
           copyDefaultCellStyle(null, cell2, cellStyle, 0);
           if (getMethodName(headers.get(j)).equals("nabled"))
             cell2.setCellValue(BeanUtils.getProperty(obj, "enabled"));
           else if (getMethodName(headers.get(j)).equals("password")) {
             cell2.setCellStyle(passwordCellStyle);
             cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
           } else cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
         }
       }
     }
     // 输出房间使用信息数据
     else {
       sheet.setColumnWidth(0, 32 * 80);
       sheet.setColumnWidth(2, 32 * 100);
       sheet.setColumnWidth(3, 32 * 190);
       sheet.setColumnWidth(4, 32 * 190);
       sheet.setColumnWidth(5, 32 * 190);
       sheet.setColumnWidth(10, 32 * 130);
       for (int i = 0; i < objs.size(); i++) {
         r = sheet.createRow(i + 2);
         obj = objs.get(i);
         for (int j = 0; j < headers.size(); j++) {
           Cell cell2 = r.createCell(j);
           if (j == 3 || j == 4 || j == 5) {
             XSSFCellStyle cs3 = wb.createCellStyle();
             cell2.setCellValue(new Date());
             copyDefaultCellStyle(format, cell2, cs3, 1);
           }
           if (j == 10) {
             XSSFCellStyle cs2 = wb.createCellStyle();
             copyDefaultCellStyle(format, cell2, cs2, 2);
           }
           copyDefaultCellStyle(null, cell2, cellStyle, 0);
           cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
         }
       }
     }
     // 设置行列的默认宽度和高度
   } catch (IllegalAccessException e) {
     e.printStackTrace();
     logger.error(e);
   } catch (InvocationTargetException e) {
     e.printStackTrace();
     logger.error(e);
   } catch (NoSuchMethodException e) {
     e.printStackTrace();
     logger.error(e);
   }
   return wb;
 }
コード例 #10
0
  private String[] createXLSRowComments(List<String> selects, QueryResultRow row) {
    List<String> commentsData = new ArrayList<>();
    PartRevision part = row.getPartRevision();
    PartIteration lastIteration = part.getLastIteration();

    for (String select : selects) {

      if (select.equals(QueryField.CTX_SERIAL_NUMBER)) {
        String path = row.getPath();
        if (path != null && !path.isEmpty()) {
          commentsData.add(path);
        }

      } else if (select.startsWith(QueryField.PART_REVISION_ATTRIBUTES_PREFIX)) {
        String attributeSelectType =
            select
                .substring(0, select.indexOf("."))
                .substring(QueryField.PART_REVISION_ATTRIBUTES_PREFIX.length());
        String attributeSelectName = select.substring(select.indexOf(".") + 1);
        StringBuilder commentsSbattr = new StringBuilder();

        if (lastIteration != null) {
          List<InstanceAttribute> attributes = lastIteration.getInstanceAttributes();
          if (attributes != null) {
            for (InstanceAttribute attribute : attributes) {
              InstanceAttributeDescriptor attributeDescriptor =
                  new InstanceAttributeDescriptor(attribute);

              if (attributeDescriptor.getName().equals(attributeSelectName)
                  && attributeDescriptor.getStringType().equals(attributeSelectType)) {
                commentsSbattr.append(attribute.getId() + "|");
              }
            }
          }
        }

        String commentsContent = commentsSbattr.toString().trim();
        if (commentsContent.length() > 0) {
          commentsContent = commentsContent.substring(0, commentsContent.lastIndexOf("|"));
        }
        commentsData.add(commentsContent);

      } else if (select.startsWith(QueryField.PATH_DATA_ATTRIBUTES_PREFIX)) {
        String attributeSelectType =
            select
                .substring(0, select.indexOf("."))
                .substring(QueryField.PATH_DATA_ATTRIBUTES_PREFIX.length());
        String attributeSelectName = select.substring(select.indexOf(".") + 1);
        PathDataIteration pdi = row.getPathDataIteration();
        StringBuilder commentsSbpattr = new StringBuilder();

        if (pdi != null) {
          List<InstanceAttribute> attributes = pdi.getInstanceAttributes();
          if (attributes != null) {
            for (InstanceAttribute attribute : attributes) {
              InstanceAttributeDescriptor attributeDescriptor =
                  new InstanceAttributeDescriptor(attribute);

              if (attributeDescriptor.getName().equals(attributeSelectName)
                  && attributeDescriptor.getStringType().equals(attributeSelectType)) {
                commentsSbpattr.append(attribute.getId() + "|");
              }
            }
          }
        }

        String commentsContent = commentsSbpattr.toString().trim();
        if (commentsContent.length() > 0) {
          commentsContent = commentsContent.substring(0, commentsContent.lastIndexOf("|"));
        }
        commentsData.add(commentsContent);

      } else {
        commentsData.add("");
      }
    }

    String commentsRowData = StringUtils.join(commentsData, ";");
    return commentsRowData.split(";");
  }
コード例 #11
0
  private String[] createXLSRow(List<String> selects, QueryResultRow row, String baseURL) {
    List<String> data = new ArrayList<>();
    PartRevision part = row.getPartRevision();
    PartIteration lastCheckedInIteration = part.getLastCheckedInIteration();
    PartIteration lastIteration = part.getLastIteration();
    QueryContext context = row.getContext();

    for (String select : selects) {

      switch (select) {
        case QueryField.CTX_PRODUCT_ID:
          String productId = context != null ? context.getConfigurationItemId() : "";
          data.add(productId);
          break;
        case QueryField.CTX_SERIAL_NUMBER:
          String serialNumber = context != null ? context.getSerialNumber() : "";
          data.add(serialNumber != null ? serialNumber : "");
          break;
        case QueryField.PART_MASTER_NUMBER:
          data.add(part.getPartNumber());
          break;
        case QueryField.PART_MASTER_NAME:
          String sName = part.getPartName();
          data.add(sName != null ? sName : "");
          break;
        case QueryField.PART_MASTER_TYPE:
          String sType = part.getType();
          data.add(sType != null ? sType : "");
          break;
        case QueryField.PART_REVISION_MODIFICATION_DATE:
          data.add(
              (lastIteration != null && lastIteration.getModificationDate() != null)
                  ? simpleDateFormat.format(lastIteration.getModificationDate())
                  : "");
          break;
        case QueryField.PART_REVISION_CREATION_DATE:
          data.add(
              (part.getCreationDate() != null)
                  ? simpleDateFormat.format(part.getCreationDate())
                  : "");
          break;
        case QueryField.PART_REVISION_CHECKOUT_DATE:
          data.add(
              (part.getCheckOutDate() != null)
                  ? simpleDateFormat.format(part.getCheckOutDate())
                  : "");
          break;
        case QueryField.PART_REVISION_CHECKIN_DATE:
          data.add(
              (lastCheckedInIteration != null && lastCheckedInIteration.getCheckInDate() != null)
                  ? simpleDateFormat.format(lastCheckedInIteration.getCheckInDate())
                  : "");
          break;
        case QueryField.PART_REVISION_VERSION:
          data.add(part.getVersion() != null ? part.getVersion() : "");
          break;
        case QueryField.PART_REVISION_LIFECYCLE_STATE:
          data.add(part.getLifeCycleState() != null ? part.getLifeCycleState() : "");
          break;
        case QueryField.PART_REVISION_STATUS:
          data.add(part.getStatus().toString());
          break;
        case QueryField.AUTHOR_LOGIN:
          User user = part.getAuthor();
          data.add(user.getLogin());
          break;
        case QueryField.AUTHOR_NAME:
          User userAuthor = part.getAuthor();
          data.add(userAuthor.getName());
          break;
        case QueryField.CTX_DEPTH:
          data.add(row.getDepth() + "");
          break;
        case QueryField.CTX_AMOUNT:
          data.add(row.getAmount() + "");
          break;
        case QueryField.PART_ITERATION_LINKED_DOCUMENTS:
          StringBuilder sb = new StringBuilder();
          if (lastCheckedInIteration != null) {
            Set<DocumentLink> linkedDocuments = lastCheckedInIteration.getLinkedDocuments();
            for (DocumentLink documentLink : linkedDocuments) {
              DocumentRevision targetDocument = documentLink.getTargetDocument();
              sb.append(
                  baseURL
                      + "/documents/"
                      + targetDocument.getWorkspaceId()
                      + "/"
                      + targetDocument.getId()
                      + "/"
                      + targetDocument.getVersion()
                      + " ");
            }
          }
          data.add(sb.toString());
          break;

        case QueryField.CTX_P2P_SOURCE:
          Map<String, List<PartLinkList>> sources = row.getSources();
          String sourcePartLinksAsString = Tools.getPartLinksAsExcelString(sources);
          data.add(sourcePartLinksAsString);
          break;

        case QueryField.CTX_P2P_TARGET:
          Map<String, List<PartLinkList>> targets = row.getTargets();
          String targetPartLinksAsString = Tools.getPartLinksAsExcelString(targets);
          data.add(targetPartLinksAsString);
          break;

        default:
          if (select.startsWith(QueryField.PART_REVISION_ATTRIBUTES_PREFIX)) {
            String attributeSelectType =
                select
                    .substring(0, select.indexOf("."))
                    .substring(QueryField.PART_REVISION_ATTRIBUTES_PREFIX.length());
            String attributeSelectName = select.substring(select.indexOf(".") + 1);
            String attributeValue = "";
            StringBuilder sbattr = new StringBuilder();

            if (lastIteration != null) {
              List<InstanceAttribute> attributes = lastIteration.getInstanceAttributes();
              if (attributes != null) {
                for (InstanceAttribute attribute : attributes) {
                  InstanceAttributeDescriptor attributeDescriptor =
                      new InstanceAttributeDescriptor(attribute);
                  if (attributeDescriptor.getName().equals(attributeSelectName)
                      && attributeDescriptor.getStringType().equals(attributeSelectType)) {

                    attributeValue = attribute.getValue() + "";
                    if (attributeDescriptor.getType() == InstanceAttributeDescriptor.Type.DATE) {
                      attributeValue =
                          attribute.getValue() != null
                              ? attributeDateFormat.format(attribute.getValue())
                              : "";
                    } else if (attribute instanceof InstanceListOfValuesAttribute) {
                      attributeValue =
                          ((InstanceListOfValuesAttribute) attribute).getSelectedName();
                    }
                    sbattr.append(attributeValue + "|");
                  }
                }
              }
            }
            String content = sbattr.toString().trim();
            if (content.length() > 0) {
              content = content.substring(0, content.lastIndexOf("|"));
            }
            data.add(content);
          }
          if (select.startsWith(QueryField.PATH_DATA_ATTRIBUTES_PREFIX)) {
            String attributeSelectType =
                select
                    .substring(0, select.indexOf("."))
                    .substring(QueryField.PATH_DATA_ATTRIBUTES_PREFIX.length());
            String attributeSelectName = select.substring(select.indexOf(".") + 1);
            String attributeValue = "";
            PathDataIteration pdi = row.getPathDataIteration();
            StringBuilder sbpdattr = new StringBuilder();

            if (pdi != null) {
              List<InstanceAttribute> attributes = pdi.getInstanceAttributes();
              if (attributes != null) {
                for (InstanceAttribute attribute : attributes) {
                  InstanceAttributeDescriptor attributeDescriptor =
                      new InstanceAttributeDescriptor(attribute);
                  if (attributeDescriptor.getName().equals(attributeSelectName)
                      && attributeDescriptor.getStringType().equals(attributeSelectType)) {

                    attributeValue = attribute.getValue() + "";
                    if (attributeDescriptor.getType() == InstanceAttributeDescriptor.Type.DATE) {
                      attributeValue =
                          attribute.getValue() != null
                              ? attributeDateFormat.format(attribute.getValue())
                              : "";
                    } else if (attribute instanceof InstanceListOfValuesAttribute) {
                      attributeValue =
                          ((InstanceListOfValuesAttribute) attribute).getSelectedName();
                    }
                    sbpdattr.append(attributeValue + "|");
                  }
                }
              }
            }
            String content = sbpdattr.toString().trim();
            if (content.length() > 0) {
              content = content.substring(0, content.lastIndexOf("|"));
            }
            data.add(content);
          }
      }
    }

    String rowData = StringUtils.join(data, ";");
    return rowData.split(";");
  }