@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();
  }
  /**
   * get the specified excel sheet and put its value string to list.
   *
   * @param sheetName excel sheet name
   * @param ignoreRows first several rows not to read.
   * @param ignoreCols first several cols not to read.
   * @param readRows specified row count to read.
   * @param readColumns specified column count to read.
   * @throws RuntimeException
   */
  public List<String> excelToList(
      String sheetName, int ignoreRows, int ignoreCols, int readRows, int readColumns) {
    FileInputStream fso = null;
    List<String> paraList = new ArrayList<String>();

    try {
      fso = new FileInputStream(fileName);
      Workbook workBook = getWorkBook(fso, true);
      xlSheet = workBook.getSheet(sheetName);
      if (xlSheet == null) {
        LOG.error("sheet [" + sheetName + "] does not exist!");
        throw new RuntimeException("sheet [" + sheetName + "] does not exist!");
      }
      readRows = (readRows == 0) ? xlSheet.getPhysicalNumberOfRows() : readRows;
      for (int i = ignoreRows; i < ignoreRows + readRows; i++) {
        xlRow = xlSheet.getRow(i);
        readColumns = (readColumns == 0) ? xlRow.getPhysicalNumberOfCells() : readColumns;
        if (xlRow != null) {
          for (int j = ignoreCols; j < ignoreCols + readColumns; j++) {
            xlCell = xlRow.getCell(j);
            if (xlCell == null) {
              paraList.add("");
            } else {
              paraList.add(xlCell.toString());
            }
          }
        }
      }
      fso.close();
    } catch (Exception e) {
      LOG.error(e);
      throw new RuntimeException("read excel failed:" + e.getMessage());
    }
    return paraList;
  }
  public static void generateTrainingDataFromFile(
      String
          fileLocation) // Requires that the original file had the metadata and requires that this
        // file is formated the same in first sheet
      {
    testDataLL = (LinkedList<String[]>) dataLL.clone();
    actualClassifications = (LinkedList<String>) classificationsLL.clone();

    FileInputStream file;
    try {
      file = new FileInputStream(new File(fileLocation));
      Workbook excelFile = new HSSFWorkbook(file);
      Sheet sheet1 = excelFile.getSheetAt(0); // Data sheet
      for (Row row : sheet1) {
        String data[] = new String[row.getPhysicalNumberOfCells() - 1];
        String classification = "";

        int offset =
            0; // Used so that we can declare an array of the size of the attributes without the
        // classification
        for (Cell cell : row) {
          int index = cell.getColumnIndex();
          if (classificationLocation != index) {
            data[index - offset] = cell.toString();
          } else {
            classification = cell.toString();
            offset++;
          }
        }

        // Even though data and classifications are not really used add it onto the end so it is
        // still complete for in the event they end up being used in a later version
        dataLL.add(data);
        classificationsLL.add(classification);

        trainingDataLL.add(data);
        knownClassifications.add(classification);

        // Check to see if we have seen that classification yet
        int occurrences = 0;
        for (int i = 0; i < classificationTypes.size() && occurrences == 0; i++) {
          if (classificationTypes.get(i).compareTo(classification) == 0) {
            occurrences = 1;
          }
        }
        if (occurrences == 0) {
          classificationTypes.add(classification);
        }
      }
      excelFile.close();
    } catch (FileNotFoundException e) {
      System.out.println("Error file not found");
      System.exit(0);
    } catch (IOException e) {
      System.out.println("Unable to read file, disk drive may be failing");
      e.printStackTrace();
      System.exit(0);
    }
  }
  /**
   * This method checks the row to see if it is a title row. It returns true if it is.
   *
   * @param oRow The row to be checked.
   * @return TRUE if this is a title row.
   */
  private static boolean isTitleRow(Row oRow) {
    boolean bReturnValue = false;

    if ((oRow != null) && (oRow.getPhysicalNumberOfCells() > 0)) {
      Cell oCell = oRow.getCell(oRow.getFirstCellNum());
      if ((oCell != null) && (oCell.getCellType() == Cell.CELL_TYPE_STRING)) {
        if (TITLE_ROW_CELL_1_TEXT.equals(oCell.getStringCellValue())) {
          bReturnValue = true;
        }
      }
    }

    return bReturnValue;
  }
  public static List<List<List<String>>> readExcel(File file, Rule rule) {
    int start = rule.getStart();
    int end = rule.getEnd();
    List<List<List<String>>> result = Lists.newArrayList();
    Workbook wb;
    try {
      wb = WorkbookFactory.create(file);
    } catch (Exception e) {
      throw new ExcelException(e);
    }
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
      Sheet sheet = wb.getSheetAt(i);
      List<List<String>> sheetList = Lists.newArrayList();
      int rows = sheet.getLastRowNum();
      if (start <= sheet.getFirstRowNum()) {
        start = sheet.getFirstRowNum();
      }
      if (end >= rows) {
        end = rows;
      } else if (end <= 0) {
        end = rows + end;
      }
      for (int rowIndex = start; rowIndex <= end; rowIndex++) {
        Row row = sheet.getRow(rowIndex);
        List<String> columns = Lists.newArrayList();
        int cellNum = row.getLastCellNum();
        System.out.println(row.getLastCellNum());
        System.out.println(row.getPhysicalNumberOfCells());
        for (int cellIndex = row.getFirstCellNum(); cellIndex < cellNum; cellIndex++) {
          Cell cell = row.getCell(cellIndex);
          int cellType = cell.getCellType();
          String column = "";
          switch (cellType) {
            case Cell.CELL_TYPE_NUMERIC:
              //                            DecimalFormat format = new DecimalFormat();
              //                            format.setGroupingUsed(false);
              column = String.valueOf(cell.getDateCellValue());
              break;
            case Cell.CELL_TYPE_STRING:
              column = cell.getStringCellValue();
              break;
            case Cell.CELL_TYPE_BOOLEAN:
              column = cell.getBooleanCellValue() + "";
              break;
            case Cell.CELL_TYPE_FORMULA:
              column = cell.getCellFormula();
              break;
            case Cell.CELL_TYPE_ERROR:

            case Cell.CELL_TYPE_BLANK:
              column = " ";
              break;
            default:
          }
          columns.add(column.trim());
        }

        List<Boolean> rowFilterFlagList = Lists.newArrayList();
        List<RowFilter> rowFilterList = Lists.newArrayList();
        for (int k = 0; k < rowFilterList.size(); k++) {
          RowFilter rowFilter = rowFilterList.get(k);
          rowFilterFlagList.add(rowFilter.doFilter(rowIndex, columns));
        }
        if (!rowFilterFlagList.contains(false)) {
          sheetList.add(columns);
        }
      }
      result.add(sheetList);
    }
    return result;
  }
Exemple #6
0
 /**
  * 将excel内的内容读取到xml文件中,并添加dtd验证
  *
  * @param xmlFile
  * @param sheetNum
  * @return 1代表成功,0失败,-1超过最大sheet,2跳过当前失败的xml
  */
 public int excelToXml(String xmlFile, int sheetNum) {
   if (sheetNum >= workBook.getNumberOfSheets()) return -1;
   else sheet = workBook.getSheetAt(sheetNum);
   xmlFile = xmlFile + ".xml";
   try {
     Document document = DocumentHelper.createDocument();
     // 使用sheet名称命名跟节点
     String rootName = sheet.getSheetName().replaceAll(" ", "");
     Element root = document.addElement(rootName);
     // 添加dtd文件说明
     DocumentType documentType = new DOMDocumentType();
     documentType.setElementName(rootName);
     List<ElementDecl> declList = new ArrayList<>();
     declList.add(new ElementDecl(rootName, "(row*)"));
     // 判断sheet是否为空,为空则不执行任何操作
     if (sheet.getRow(0) == null) return 1;
     // 遍历sheet第一行,获取元素名称
     row = sheet.getRow(0);
     String rowString = null;
     List<String> pcdataList = new ArrayList<>();
     for (int y = 0; y < row.getPhysicalNumberOfCells(); y++) {
       Object object = this.getCellValueObject(0, y);
       // 判断是否有合并单元格,有的话跳过
       if (object == null) return 2;
       // 去除表头字符串中的空格
       String objectStr = object.toString().replaceAll(" ", "");
       if (rowString != null) rowString += "|" + objectStr;
       else rowString = objectStr;
       pcdataList.add(objectStr);
     }
     // 设置行节点
     declList.add(new ElementDecl("row", "(" + rowString + ")*"));
     // 遍历list设置行的下级节点
     for (String tmp : pcdataList) {
       declList.add(new ElementDecl(tmp, "(#PCDATA)"));
     }
     documentType.setInternalDeclarations(declList);
     // 遍历读写excel数据到xml中
     for (int x = 1; x < sheet.getLastRowNum(); x++) {
       row = sheet.getRow(x);
       Element rowElement = root.addElement("row");
       for (int y = 0; y < row.getPhysicalNumberOfCells(); y++) {
         // cell = row.getCell(y);
         Object object = this.getCellValueObject(x, y);
         if (object != null) {
           // 将sheet第一行的行首元素当作元素名称
           String pcdataString = pcdataList.get(y);
           Element element = rowElement.addElement(pcdataString);
           // Element element = rowElement.addElement("name");
           element.setText(object.toString());
         }
       }
     }
     // 写入文件和dtd
     document.setDocType(documentType);
     this.docToXmlFile(document, xmlFile);
   } catch (Exception e) {
     e.printStackTrace();
   }
   return 1;
 }
  public static void readExcelFile(String fileName) {
    FileInputStream file;
    try {
      file = new FileInputStream(new File(fileName));
      Workbook excelFile = new HSSFWorkbook(file);

      Sheet sheet1 = excelFile.getSheetAt(0); // Data sheet
      // Set just in case metadata is incomplete or malformed
      classificationLocation =
          sheet1.getRow(0).getPhysicalNumberOfCells()
              - 1; // Minus one since classificationLocation includes 0 and getPhysicalNumberOfCells
      // does not

      Sheet sheet2 = excelFile.getSheetAt(1); // Metadata sheet
      // Loop based on number of attribute names
      for (int i = 0; i < sheet2.getRow(0).getPhysicalNumberOfCells(); i++) {
        String[] metadata = new String[METADATASIZE];

        // Construct metadata
        Row currRow = sheet2.getRow(0); // This should be a row of names
        metadata[0] = currRow.getCell(i).toString();
        currRow = sheet2.getRow(1); // This should be a row of data types (discrete or continuous)
        metadata[1] = currRow.getCell(i).toString();
        currRow = sheet2.getRow(2); // This should say which one is the classifier
        if (currRow.getCell(i) == null
            || currRow.getCell(i).getCellType() == Cell.CELL_TYPE_BLANK) {
          metadata[2] = "attribute";
        } else {
          metadata[2] = "classifier";
          classificationLocation = i;
        }

        metadataLL.add(metadata);
      }

      for (Row row : sheet1) {
        String data[] = new String[row.getPhysicalNumberOfCells() - 1];
        int offset =
            0; // Used so that we can declare an array of the size of the attributes without the
        // classification
        for (Cell cell : row) {
          int index = cell.getColumnIndex();
          if (classificationLocation != index) {
            data[index - offset] = cell.toString();
          } else {
            classificationsLL.add(cell.toString());

            // Moved to generate training data so that we do not get possible classifications from
            // unknown data since some denote unknown by saying ?

            //						//Check to see if we have seen it yet
            //
            //						int occurrences = 0;
            //						for(int i = 0; i < classificationTypes.size(); i++)
            //						{
            //							if(classificationTypes.get(i).compareTo(cell.toString()) == 0)
            //							{
            //								occurrences++;
            //							}
            //						}
            //						if(occurrences == 0)
            //						{
            //							classificationTypes.add(cell.toString());
            //						}
            offset++;
          }
        }
        dataLL.add(data);
        // classCount = temp.length;
      }

      excelFile.close();
    } catch (FileNotFoundException e) {
      System.out.println("Error file not found");
      System.exit(0);
    } catch (IOException e) {
      System.out.println("Unable to read file, disk drive may be failing");
      e.printStackTrace();
      System.exit(0);
    }
  }
  /** Adds in a Row to the given Sheet */
  public Row addRow(
      Workbook wb,
      SheetToAdd sheetToAdd,
      RowToAdd rowToAdd,
      int rowIndex,
      ReportData reportData,
      ReportDesign design,
      Map<String, String> repeatSections) {

    // Create a new row and copy over style attributes from the row to add
    Row newRow = sheetToAdd.getSheet().createRow(rowIndex);
    Row rowToClone = rowToAdd.getRowToClone();
    try {
      CellStyle rowStyle = rowToClone.getRowStyle();
      if (rowStyle != null) {
        newRow.setRowStyle(rowStyle);
      }
    } catch (Exception e) {
      // No idea why this is necessary, but this has thrown IndexOutOfBounds errors getting the
      // rowStyle.  Mysteries of POI
    }
    newRow.setHeight(rowToClone.getHeight());

    // Iterate across all of the cells in the row, and configure all those that need to be
    // added/cloned
    List<CellToAdd> cellsToAdd = new ArrayList<CellToAdd>();

    int totalCells = rowToClone.getPhysicalNumberOfCells();
    int cellsFound = 0;
    for (int cellNum = 0; cellsFound < totalCells; cellNum++) {
      Cell currentCell = rowToClone.getCell(cellNum);
      log.debug("Handling cell: " + currentCell);
      if (currentCell != null) {
        cellsFound++;
      }
      // If we find that the cell that we are on is a repeating cell, then add the appropriate
      // number of cells to clone
      String repeatingColumnProperty =
          getRepeatingColumnProperty(sheetToAdd.getOriginalSheetNum(), cellNum, repeatSections);
      if (repeatingColumnProperty != null) {
        String[] dataSetSpanSplit = repeatingColumnProperty.split(",");
        String dataSetName = dataSetSpanSplit[0];
        DataSet dataSet = getDataSet(reportData, dataSetName, rowToAdd.getReplacementData());
        int numCellsToRepeat = 1;
        if (dataSetSpanSplit.length == 2) {
          numCellsToRepeat = Integer.parseInt(dataSetSpanSplit[1]);
        }
        log.debug(
            "Repeating this cell with dataset: " + dataSet + " and repeat of " + numCellsToRepeat);
        int repeatNum = 0;
        for (DataSetRow dataSetRow : dataSet) {
          repeatNum++;
          for (int i = 0; i < numCellsToRepeat; i++) {
            Cell cell = (i == 0 ? currentCell : rowToClone.getCell(cellNum + i));
            if (repeatNum == 1 && cell != null && cell != currentCell) {
              cellsFound++;
            }
            Map<String, Object> newReplacements =
                getReplacementData(
                    rowToAdd.getReplacementData(),
                    reportData,
                    design,
                    dataSetName,
                    dataSetRow,
                    repeatNum);
            cellsToAdd.add(new CellToAdd(cell, newReplacements));
            log.debug("Adding " + cell + " with dataSetRow: " + dataSetRow);
          }
        }
        cellNum += numCellsToRepeat;
      } else {
        cellsToAdd.add(new CellToAdd(currentCell, rowToAdd.getReplacementData()));
        log.debug("Adding " + currentCell);
      }
    }

    // Now, go through all of the collected cells, and add them back in

    ExcelStyleHelper styleHelper = new ExcelStyleHelper(wb);
    String prefix = getExpressionPrefix(design);
    String suffix = getExpressionSuffix(design);

    for (int i = 0; i < cellsToAdd.size(); i++) {
      CellToAdd cellToAdd = cellsToAdd.get(i);
      Cell newCell = newRow.createCell(i);
      Cell cellToClone = cellToAdd.getCellToClone();
      if (cellToClone != null) {
        String contents = ExcelUtil.getCellContentsAsString(cellToClone);
        newCell.setCellStyle(cellToClone.getCellStyle());
        try {
          newCell.setCellFormula(cellToClone.getCellFormula());
        } catch (Exception e) {
          // Do nothing here.  I don't know why POI throw exceptions here when the cell is not a
          // formula, but this suppresses them...
        }

        int numFormattings =
            sheetToAdd.getSheet().getSheetConditionalFormatting().getNumConditionalFormattings();
        for (int n = 0; n < numFormattings; n++) {
          ConditionalFormatting f =
              sheetToAdd.getSheet().getSheetConditionalFormatting().getConditionalFormattingAt(n);
          for (CellRangeAddress add : f.getFormattingRanges()) {

            if (add.getFirstRow() == rowToAdd.getRowToClone().getRowNum()
                && add.getLastRow() == rowToClone.getRowNum()) {
              if (add.getFirstColumn() == cellToClone.getColumnIndex()
                  && add.getLastColumn() == cellToClone.getColumnIndex()) {
                ConditionalFormattingRule[] rules =
                    new ConditionalFormattingRule[f.getNumberOfRules()];
                for (int j = 0; j < f.getNumberOfRules(); j++) {
                  rules[j] = f.getRule(j);
                }
                CellRangeAddress[] cellRange = new CellRangeAddress[1];
                cellRange[0] = new CellRangeAddress(rowIndex, rowIndex, i, i);
                sheetToAdd
                    .getSheet()
                    .getSheetConditionalFormatting()
                    .addConditionalFormatting(cellRange, rules);
              }
            }
          }
        }

        if (ObjectUtil.notNull(contents)) {
          Object newContents =
              EvaluationUtil.evaluateExpression(
                  contents, cellToAdd.getReplacementData(), prefix, suffix);
          ExcelUtil.setCellContents(styleHelper, newCell, newContents);
        }
      }
    }

    return newRow;
  }