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());
}
@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;
}
/** 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);
}