public void addSheet(String name) {
    if (name.length() > 30) {
      name = name.substring(0, 25);
    }

    boolean found = false;

    String tempName = name.replace("/", "-");
    int i = 0;
    while (true) {

      if (i > 1000) {
        break;
      }

      for (int j = 0; j < workbook.getNumberOfSheets(); j++) {
        found = workbook.getSheetName(j).equalsIgnoreCase(tempName);
        if (found) {
          break;
        }
      }

      if (!found) {
        break;
      } else {
        i++;
        tempName = name + " (" + i + ")";
      }
    }

    activeSheet = workbook.createSheet(tempName);

    nextRowId = 0;
  }
  @Test
  public void testExcel() throws Exception {
    AbstractExcelView excelView =
        new AbstractExcelView() {
          @Override
          protected void buildExcelDocument(
              Map<String, Object> model,
              HSSFWorkbook wb,
              HttpServletRequest request,
              HttpServletResponse response)
              throws Exception {
            HSSFSheet sheet = wb.createSheet("Test Sheet");
            // test all possible permutation of row or column not existing
            HSSFCell cell = getCell(sheet, 2, 4);
            cell.setCellValue("Test Value");
            cell = getCell(sheet, 2, 3);
            setText(cell, "Test Value");
            cell = getCell(sheet, 3, 4);
            setText(cell, "Test Value");
            cell = getCell(sheet, 2, 4);
            setText(cell, "Test Value");
          }
        };

    excelView.render(new HashMap<String, Object>(), request, response);

    POIFSFileSystem poiFs =
        new POIFSFileSystem(new ByteArrayInputStream(response.getContentAsByteArray()));
    HSSFWorkbook wb = new HSSFWorkbook(poiFs);
    assertEquals("Test Sheet", wb.getSheetName(0));
    HSSFSheet sheet = wb.getSheet("Test Sheet");
    HSSFRow row = sheet.getRow(2);
    HSSFCell cell = row.getCell(4);
    assertEquals("Test Value", cell.getStringCellValue());
  }
Example #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;
  }
Example #4
0
  /** Creates a new XlsDataSet object that loads the specified Excel document. */
  public XlsDataSet(InputStream in) throws IOException, DataSetException {
    _tables = super.createTableNameMap();

    HSSFWorkbook workbook = new HSSFWorkbook(in);
    int sheetCount = workbook.getNumberOfSheets();
    for (int i = 0; i < sheetCount; i++) {
      ITable table = new XlsTable(workbook.getSheetName(i), workbook.getSheetAt(i));
      _tables.add(table.getTableMetaData().getTableName(), table);
    }
  }
  /** @param xls */
  private static TreeSet<String> getSheetNameList(File xls) throws Exception {

    TreeSet<String> sheetNameList = new TreeSet<String>();

    if (!xls.exists()) {
      return sheetNameList;
    }

    POIFSFileSystem input = new POIFSFileSystem(new FileInputStream(xls));
    HSSFWorkbook wb = new HSSFWorkbook(input);

    int numberOfSheets = wb.getNumberOfSheets();

    for (int i = 0; i < numberOfSheets; i++) {
      String sheetName = wb.getSheetName(i);
      sheetNameList.add(sheetName);
    }

    return sheetNameList;
  }
  /** {@inheritDoc} */
  @Override
  public void generate(
      IProgressMonitor monitor,
      HSSFWorkbook workbook,
      int sheetNo,
      boolean useLogicalNameAsSheetName,
      Map<String, Integer> sheetNameMap,
      Map<String, ObjectModel> sheetObjectMap,
      ERDiagram diagram,
      Map<String, LoopDefinition> loopDefinitionMap) {
    this.clear();

    List<ERTable> nodeSet = null;

    if (diagram.getCurrentCategory() != null) {
      nodeSet = diagram.getCurrentCategory().getTableContents();
    } else {
      nodeSet = diagram.getDiagramContents().getContents().getTableSet().getList();
    }

    for (ERTable table : nodeSet) {
      String name = null;
      if (useLogicalNameAsSheetName) {
        name = table.getLogicalName();
      } else {
        name = table.getPhysicalName();
      }

      HSSFSheet newSheet = createNewSheet(workbook, sheetNo, name, sheetNameMap);

      sheetObjectMap.put(workbook.getSheetName(workbook.getSheetIndex(newSheet)), table);

      this.setTableData(workbook, newSheet, table);

      monitor.worked(1);
    }
  }
  /** Retrieves the text contents of the file */
  public String getText() {
    StringBuffer text = new StringBuffer();

    // We don't care about the difference between
    //  null (missing) and blank cells
    _wb.setMissingCellPolicy(HSSFRow.RETURN_BLANK_AS_NULL);

    // Process each sheet in turn
    for (int i = 0; i < _wb.getNumberOfSheets(); i++) {
      HSSFSheet sheet = _wb.getSheetAt(i);
      if (sheet == null) {
        continue;
      }

      if (_includeSheetNames) {
        String name = _wb.getSheetName(i);
        if (name != null) {
          text.append(name);
          text.append("\n");
        }
      }

      // Header text, if there is any
      if (_includeHeadersFooters) {
        text.append(_extractHeaderFooter(sheet.getHeader()));
      }

      int firstRow = sheet.getFirstRowNum();
      int lastRow = sheet.getLastRowNum();
      for (int j = firstRow; j <= lastRow; j++) {
        HSSFRow row = sheet.getRow(j);
        if (row == null) {
          continue;
        }

        // Check each cell in turn
        int firstCell = row.getFirstCellNum();
        int lastCell = row.getLastCellNum();
        if (_includeBlankCells) {
          firstCell = 0;
        }

        for (int k = firstCell; k < lastCell; k++) {
          HSSFCell cell = row.getCell(k);
          boolean outputContents = true;

          if (cell == null) {
            // Only output if requested
            outputContents = _includeBlankCells;
          } else {
            switch (cell.getCellType()) {
              case HSSFCell.CELL_TYPE_STRING:
                text.append(cell.getRichStringCellValue().getString());
                break;
              case HSSFCell.CELL_TYPE_NUMERIC:
                text.append(_formatter.formatCellValue(cell));
                break;
              case HSSFCell.CELL_TYPE_BOOLEAN:
                text.append(cell.getBooleanCellValue());
                break;
              case HSSFCell.CELL_TYPE_ERROR:
                text.append(ErrorEval.getText(cell.getErrorCellValue()));
                break;
              case HSSFCell.CELL_TYPE_FORMULA:
                if (!_shouldEvaluateFormulas) {
                  text.append(cell.getCellFormula());
                } else {
                  switch (cell.getCachedFormulaResultType()) {
                    case HSSFCell.CELL_TYPE_STRING:
                      HSSFRichTextString str = cell.getRichStringCellValue();
                      if (str != null && str.length() > 0) {
                        text.append(str.toString());
                      }
                      break;
                    case HSSFCell.CELL_TYPE_NUMERIC:
                      HSSFCellStyle style = cell.getCellStyle();
                      if (style == null) {
                        text.append(cell.getNumericCellValue());
                      } else {
                        text.append(
                            _formatter.formatRawCellContents(
                                cell.getNumericCellValue(),
                                style.getDataFormat(),
                                style.getDataFormatString()));
                      }
                      break;
                    case HSSFCell.CELL_TYPE_BOOLEAN:
                      text.append(cell.getBooleanCellValue());
                      break;
                    case HSSFCell.CELL_TYPE_ERROR:
                      text.append(ErrorEval.getText(cell.getErrorCellValue()));
                      break;
                  }
                }
                break;
              default:
                throw new RuntimeException("Unexpected cell type (" + cell.getCellType() + ")");
            }

            // Output the comment, if requested and exists
            HSSFComment comment = cell.getCellComment();
            if (_includeCellComments && comment != null) {
              // Replace any newlines with spaces, otherwise it
              //  breaks the output
              String commentText = comment.getString().getString().replace('\n', ' ');
              text.append(" Comment by " + comment.getAuthor() + ": " + commentText);
            }
          }

          // Output a tab if we're not on the last cell
          if (outputContents && k < (lastCell - 1)) {
            text.append("\t");
          }
        }

        // Finish off the row
        text.append("\n");
      }

      // Finally Footer text, if there is any
      if (_includeHeadersFooters) {
        text.append(_extractHeaderFooter(sheet.getFooter()));
      }
    }

    return text.toString();
  }
  public void testFormulas() {

    FormulaRecord[] fRecs = mockListen.getFormulaRecords();

    // Check our formula records
    assertEquals(6, fRecs.length);

    InternalWorkbook stubWB = listener.getStubWorkbook();
    assertNotNull(stubWB);
    HSSFWorkbook stubHSSF = listener.getStubHSSFWorkbook();
    assertNotNull(stubHSSF);

    // Check these stubs have the right stuff on them
    assertEquals("Sheet1", stubWB.getSheetName(0));
    assertEquals("Sheet1", stubHSSF.getSheetName(0));
    assertEquals("S2", stubWB.getSheetName(1));
    assertEquals("S2", stubHSSF.getSheetName(1));
    assertEquals("Sh3", stubWB.getSheetName(2));
    assertEquals("Sh3", stubHSSF.getSheetName(2));

    // Check we can get the formula without breaking
    for (int i = 0; i < fRecs.length; i++) {
      HSSFFormulaParser.toFormulaString(stubHSSF, fRecs[i].getParsedExpression());
    }

    // Peer into just one formula, and check that
    //  all the ptgs give back the right things
    Ptg[] ptgs = fRecs[0].getParsedExpression();
    assertEquals(1, ptgs.length);
    assertTrue(ptgs[0] instanceof Ref3DPtg);

    Ref3DPtg ptg = (Ref3DPtg) ptgs[0];
    HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.create(stubHSSF);
    assertEquals("Sheet1!A1", ptg.toFormulaString(book));

    // Now check we get the right formula back for
    //  a few sample ones
    FormulaRecord fr;

    // Sheet 1 A2 is on same sheet
    fr = fRecs[0];
    assertEquals(1, fr.getRow());
    assertEquals(0, fr.getColumn());
    assertEquals(
        "Sheet1!A1", HSSFFormulaParser.toFormulaString(stubHSSF, fr.getParsedExpression()));

    // Sheet 1 A5 is to another sheet
    fr = fRecs[3];
    assertEquals(4, fr.getRow());
    assertEquals(0, fr.getColumn());
    assertEquals("'S2'!A1", HSSFFormulaParser.toFormulaString(stubHSSF, fr.getParsedExpression()));

    // Sheet 1 A7 is to another sheet, range
    fr = fRecs[5];
    assertEquals(6, fr.getRow());
    assertEquals(0, fr.getColumn());
    assertEquals(
        "SUM(Sh3!A1:A4)", HSSFFormulaParser.toFormulaString(stubHSSF, fr.getParsedExpression()));

    // Now, load via Usermodel and re-check
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("3dFormulas.xls");
    assertEquals("Sheet1!A1", wb.getSheetAt(0).getRow(1).getCell(0).getCellFormula());
    assertEquals("SUM(Sh3!A1:A4)", wb.getSheetAt(0).getRow(6).getCell(0).getCellFormula());
  }
  /**
   * DOCUMENT ME!
   *
   * @throws Exception DOCUMENT ME!
   */
  public void testReadTable() throws Exception {

    InputStream is = null;
    POIFSFileSystem excelIn;

    try {
      is = getClass().getResource(NETWORK_FILE).openStream();
      excelIn = new POIFSFileSystem(is);
    } finally {
      if (is != null) {
        is.close();
      }
    }

    HSSFWorkbook wb = new HSSFWorkbook(excelIn);

    HSSFSheet sheet = wb.getSheetAt(0);

    List<String> delimiters = new ArrayList<String>();
    delimiters.add(TextFileDelimiters.TAB.toString());

    String[] galAttrName = {
      "Source", "Target", "Interaction", "edge bool attr", "edge string attr", "edge float attr"
    };
    Byte[] galAttrTypes = {
      CyAttributes.TYPE_STRING, CyAttributes.TYPE_STRING,
      CyAttributes.TYPE_STRING, CyAttributes.TYPE_BOOLEAN,
      CyAttributes.TYPE_STRING, CyAttributes.TYPE_FLOATING
    };
    NetworkTableMappingParameters mapping =
        new NetworkTableMappingParameters(
            delimiters,
            TextFileDelimiters.PIPE.toString(),
            galAttrName,
            galAttrTypes,
            null,
            null,
            0,
            1,
            2,
            null);

    reader = new ExcelNetworkSheetReader(wb.getSheetName(0), sheet, mapping);

    CyNetwork net = Cytoscape.createNetwork(reader, false, null);

    /*
     * test cases
     */
    assertEquals("Yeast Network Sheet 1", net.getTitle());
    assertEquals(331, net.getNodeCount());
    assertEquals(362, net.getEdgeCount());

    CyAttributes attr = Cytoscape.getEdgeAttributes();
    assertTrue(attr.getBooleanAttribute("YGL122C (pp) YOL123W", "edge bool attr"));
    assertFalse(attr.getBooleanAttribute("YKR026C (pp) YGL122C", "edge bool attr"));

    assertEquals(1.2344543, attr.getDoubleAttribute("YBL026W (pp) YOR167C", "edge float attr"));
    assertEquals("abcd12706", attr.getStringAttribute("YBL026W (pp) YOR167C", "edge string attr"));
    assertEquals("abcd12584", attr.getStringAttribute("YPL248C (pd) ?", "edge string attr"));

    Cytoscape.destroyNetwork(net);
  }
  public void testReadTableWithEmptyRows() throws Exception {
    String network = "/empty_attr_row.xls";

    InputStream is = null;
    POIFSFileSystem excelIn;

    try {
      is = getClass().getResource(network).openStream();
      excelIn = new POIFSFileSystem(is);
    } finally {
      if (is != null) {
        is.close();
      }
    }

    HSSFWorkbook wb = new HSSFWorkbook(excelIn);

    HSSFSheet sheet = wb.getSheetAt(0);

    List<String> delimiters = new ArrayList<String>();
    delimiters.add(TextFileDelimiters.TAB.toString());

    String[] galAttrName = {"Gene 1", "Gene 2", "Interaction Type", "Gene", "GO Group"};
    Byte[] galAttrTypes = {
      CyAttributes.TYPE_STRING,
      CyAttributes.TYPE_STRING,
      CyAttributes.TYPE_STRING,
      CyAttributes.TYPE_STRING,
      CyAttributes.TYPE_STRING
    };
    NetworkTableMappingParameters mapping =
        new NetworkTableMappingParameters(
            delimiters,
            TextFileDelimiters.PIPE.toString(),
            galAttrName,
            galAttrTypes,
            null,
            null,
            0,
            1,
            2,
            null);

    CyNetwork net = null;
    try {
      reader = new ExcelNetworkSheetReader(wb.getSheetName(0), sheet, mapping, 1);
      net = Cytoscape.createNetwork(reader, false, null);
    } catch (Exception ee) {
      ee.printStackTrace();
      fail("Caught exception");
    }

    assertEquals(222, net.getNodeCount());
    assertEquals(443, net.getEdgeCount());

    CyAttributes attr = Cytoscape.getEdgeAttributes();

    // test some random edges
    assertEquals("cc", attr.getStringAttribute("YDR459C (cc) YNL271C", "interaction"));
    assertEquals("Transport", attr.getStringAttribute("YDR459C (cc) YNL271C", "GO Group"));
    assertEquals("YPR011C", attr.getStringAttribute("YDR459C (cc) YNL271C", "Gene"));

    assertNull(attr.getStringAttribute("YEL040W (cc) YER016W", "GO Group"));
    assertNull(attr.getStringAttribute("YEL040W (cc) YER016W", "Gene"));

    Cytoscape.destroyNetwork(net);
  }