List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getCell
@Override public HSSFCell getCell(int cellnum)
From source file:kr.co.blackducksoftware.rg.displayexcel.Ex.java
License:Open Source License
/** * "OSS Check List sheet" /*from w w w. j a v a 2 s .co m*/ * */ public static void OSSCLineHeader(HSSFSheet sheet) { LogMaker.makelog("Creating OSS Header"); HSSFRow row = sheet.createRow(0); row.setHeight((short) 500); int i = 0; row.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue("Analysis Description "); row.getCell(i).setCellStyle(Style.anaDesCellStyle); sheet.autoSizeColumn((short) i, true); i++; row.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue("Analysis vv Description"); row.getCell(i).setCellStyle(Style.anaDesCellStyle); sheet.autoSizeColumn((short) i, true); i++; row.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue("Analysis Description"); row.getCell(i).setCellStyle(Style.anaDesCellStyle); sheet.autoSizeColumn((short) i, true); i++; row.createCell(3, HSSFCell.CELL_TYPE_STRING).setCellValue("1st Review (Development Team) "); row.getCell(3).setCellStyle(Style.fReviewCellStyle); sheet.autoSizeColumn((short) i, true); i++; row.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue("Analysis Description"); row.getCell(i).setCellStyle(Style.anaDesCellStyle); sheet.autoSizeColumn((short) i, true); i++; row.createCell(5, HSSFCell.CELL_TYPE_STRING).setCellValue(" Final Review "); row.getCell(5).setCellStyle(Style.finalReviewCellStyle); sheet.autoSizeColumn((short) i, true); i++; row.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue("Analysis Description"); row.getCell(i).setCellStyle(Style.anaDesCellStyle); sheet.autoSizeColumn((short) i, true); i++; }
From source file:kr.co.blackducksoftware.rg.displayexcel.Ex.java
License:Open Source License
/** * "OSS Check List sheet" /*from w w w. j av a2 s .com*/ * */ public static void OSSC(HSSFWorkbook wb) { LogMaker.makelog("Making OSS Sheet"); HSSFSheet sheet = wb.createSheet("OSS Checklist");// ??????? ArrayList<String> Header = new ArrayList<String>(); OSSCLineHeader(sheet);// 0?? ???? ??????? //Header.clear(); Header.add("3rd Party Name"); Header.add("Checklist File"); Header.add("Analyzer's Comments");//drop down HSSFRow row = sheet.createRow(1); row.setHeight((short) 500); //src2ndrow(HSSFSheet sheet, ArrayList<String> al,int i,HSSFRow row, int a, int b) src2ndrow(sheet, Header, 0, row, 3, 5); Header.clear(); Header.add("Person In Charge"); Header.add("Reviewer's Ccomments"); src2ndrow(sheet, Header, 3, row, 3, 5); Header.clear(); Header.add("Review Date"); Header.add("Reviewer's Comments"); src2ndrow(sheet, Header, 5, row, 3, 5); Header.clear(); sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 2)); sheet.addMergedRegion(new Region(0, (short) 3, 0, (short) 4)); sheet.addMergedRegion(new Region(0, (short) 5, 0, (short) 6)); //?? ???? for (int i = 2; i < 200; i++) { HSSFRow row2 = sheet.createRow(i); row2.setHeight((short) 800); for (int j = 0; j < 7; j++) { row2.createCell(j, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row2.getCell(j).setCellStyle(Style.componentCellStyle); } } }
From source file:kr.co.blackducksoftware.rg.displayexcel.Ex.java
License:Open Source License
/** * "Final List" /* ww w . j av a 2s. c o m*/ * */ public static void FL(HSSFWorkbook wb) { LogMaker.makelog("Making Final sheet"); HSSFSheet sheet = wb.createSheet("Final List");// ??????? ArrayList<String> Header = new ArrayList<String>(); Header.add(" Component Name "); Header.add(" A or B "); //Drop down Header.add(" License "); //Drop Down Header.add(" Linkage Type "); //Drop down Header.add(" License Copy "); Header.add(" Remark "); // Macro ? ??? setOneLineHeader(sheet, 0, Header);// 0?? ???? ??????? Header.clear(); //?? ???? for (int i = 1; i < 200; i++) { HSSFRow row2 = sheet.createRow(i); row2.setHeight((short) 500); for (int j = 0; j < 6; j++) { row2.createCell(j, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row2.getCell(j).setCellStyle(Style.componentCellStyle); if (j == 1) { //CellRangeAddressList(startRow, endRow, startCol, endCol) CellRangeAddressList addressList = new CellRangeAddressList(i, i, 1, 1); DVConstraint dvConstraint = DVConstraint .createExplicitListConstraint(new String[] { "A", "B" }); DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); dataValidation.setSuppressDropDownArrow(false); sheet.addValidationData(dataValidation); } if (j == 2) { //CellRangeAddressList(startRow, endRow, startCol, endCol) CellRangeAddressList addressList = new CellRangeAddressList(i, i, j, j); DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint( new String[] { "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12" }); DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); dataValidation.setSuppressDropDownArrow(false); sheet.addValidationData(dataValidation); } if (j == 3) { //CellRangeAddressList(startRow, endRow, startCol, endCol) CellRangeAddressList addressList = new CellRangeAddressList(i, i, j, j); DVConstraint dvConstraint = DVConstraint .createExplicitListConstraint(new String[] { "A", "B", "C", "D", "E" }); DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); dataValidation.setSuppressDropDownArrow(false); sheet.addValidationData(dataValidation); } } } }
From source file:kr.co.blackducksoftware.rg.displayexcel.Style.java
@SuppressWarnings("deprecation") public static void createSheetHeaders(HSSFSheet currentSheet) { HSSFRow mainHeader = currentSheet.createRow(1); // HSSFRow topMergedHeaders = currentSheet.createRow(1); /**// ww w. j a va2 s. co m * ???? ??? ???? */ HSSFRichTextString sourceCodeIdentificationReportHeader = new HSSFRichTextString( "?? ???? SW ????? ???? ???"); mainHeader.createCell(0).setCellValue(sourceCodeIdentificationReportHeader); currentSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));// 7->6?? // ???? // for // comment mainHeader.getCell(0).setCellStyle(mainHeaderStyle); /* * Menu 1 */ HSSFRow menu1 = currentSheet.createRow(3); HSSFRichTextString menuString = new HSSFRichTextString( "1. ?????? ????"); menu1.createCell(0).setCellValue(menuString); currentSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));// 7->6?? // ???? // for // comment menu1.getCell(0).setCellStyle(menuLineStyle); /* * BOM (menu2) */ HSSFRow BOMHead = currentSheet.createRow(11);// 12?? ???? BOMHead.createCell(0).setCellValue( "2. ?????? ??????? ???(Bill of Materials)"); currentSheet.autoSizeColumn((short) 0, true); BOMHead.getCell(0).setCellStyle(menuLineStyle); /** * ?? ??? ???? ???? */ HSSFRichTextString homepageHeader = new HSSFRichTextString("??????"); HSSFRichTextString matchedObHeader = new HSSFRichTextString("??? ??"); HSSFRichTextString matchedFilesCountHeader = new HSSFRichTextString("File / Folder"); HSSFRichTextString componentHeader = new HSSFRichTextString("Component"); HSSFRichTextString versionHeader = new HSSFRichTextString("Version"); // HSSFRichTextString licenseHeader = new // HSSFRichTextString("Total Lines"); HSSFRichTextString identifierHeader = new HSSFRichTextString("Component"); HSSFRichTextString identifiedDateHeader = new HSSFRichTextString("Version"); HSSFRichTextString commentHeader = new HSSFRichTextString("????????"); HSSFRichTextString firstLineHeader = new HSSFRichTextString("Matched First Line");// firstline HSSFRichTextString matchedRatioHeader = new HSSFRichTextString("Matched Ratio");// MachedRatioPercent HSSFRichTextString realCommentHeader = new HSSFRichTextString("Comment");// add // for // comment HSSFRichTextString licenseHeader = new HSSFRichTextString("?????");// add // for // license HSSFRichTextString jointHeader = new HSSFRichTextString("????????");// add // for // license HSSFRichTextString fileCountHeader = new HSSFRichTextString("?? ???? ??");// add // for // license HSSFRichTextString percentHeader = new HSSFRichTextString("%");// add // for // license // currentSheet.addMergedRegion(new CellRangeAddress(1, 1, 9, 13)); /* * 2?? ?? ??????? ???. */ HSSFRow BOM = currentSheet.createRow(12);// 13?? ???? // topMergedHeaders.createCell(0).setCellValue(categoryHeader); BOM.createCell(0).setCellValue(matchedObHeader); BOM.createCell(1).setCellValue(componentHeader); BOM.createCell(2).setCellValue(versionHeader); BOM.createCell(3).setCellValue(homepageHeader); BOM.createCell(4).setCellValue(licenseHeader); BOM.createCell(5).setCellValue(jointHeader); BOM.createCell(6).setCellValue(fileCountHeader); // BOM.createCell(7).setCellValue(commentHeader); /* * 2?? ?? ?????? ? ?? */ for (int a = 0; a < BOM.getLastCellNum(); a++) { BOM.getCell(a).setCellStyle(firstMergedCellStyle); currentSheet.autoSizeColumn((short) a, true); } /* * ????sw????? ???? (menu3) */ int BOMCount = 0;// ?? HSSFRow OSSSMenu = currentSheet.createRow(15 + BOMCount);// 3?? ?? ???? ???? OSSSMenu.createCell(0) .setCellValue("3. ????SW????? ????"); currentSheet.autoSizeColumn((short) 0, true); OSSSMenu.getCell(0).setCellStyle(menuLineStyle); /* * 3?? ?? ??????? ???. */ HSSFRow OSSS = currentSheet.createRow(16 + BOMCount);// 3?? ???? // topMergedHeaders.createCell(0).setCellValue(categoryHeader); OSSS.createCell(0).setCellValue(licenseHeader); OSSS.createCell(1).setCellValue(jointHeader); OSSS.createCell(2).setCellValue(fileCountHeader); OSSS.createCell(3).setCellValue(percentHeader); // OSSS.createCell(7).setCellValue(commentHeader); for (int a = 0; a < OSSS.getLastCellNum(); a++) { OSSS.getCell(a).setCellStyle(firstMergedCellStyle); currentSheet.autoSizeColumn((short) a, true); } /* * ????? ????? (menu4) */ int OSSSCount = 0;// ?? HSSFRow analMenu = currentSheet.createRow(19 + BOMCount + OSSSCount);// 4?? // ?? // ???? // ???? analMenu.createCell(0).setCellValue("4. ????? ?????"); currentSheet.autoSizeColumn((short) 0, true); analMenu.getCell(0).setCellStyle(menuLineStyle); /* * 4?? ?? ??????? ???. */ HSSFRow anal = currentSheet.createRow(20 + BOMCount + OSSSCount);// 3?? // ???? anal.createCell(0).setCellValue("????? ?????"); anal.getCell(0).setCellStyle(analCellStyle); currentSheet.autoSizeColumn((short) 0, true); }
From source file:kupkb_experiments.ExperimentSpreadSheetParser.java
License:Open Source License
public ExperimentSpreadSheetParser(File file) { keyValue = new HashMap<String, List<Cell>>(); compoundAttributeToColumn = new HashMap<String, Integer>(); String expId = "exp_" + String.valueOf(System.currentTimeMillis()); String analysisId = "analysis_" + String.valueOf(System.currentTimeMillis()); roleCell = new ArrayList<Cell>(); workbookManager = new WorkbookManager(); InputStream inputStream = null; try {//from w w w .j a v a2 s.c o m workbookManager.loadWorkbook(file); workbook = workbookManager.getWorkbook(); validationManager = workbookManager.getOntologyTermValidationManager(); inputStream = file.toURI().toURL().openStream(); HSSFWorkbook workbook = new HSSFWorkbook(new BufferedInputStream(inputStream)); this.sheet = workbook.getSheetAt(0); int lastRow = sheet.getLastRowNum(); for (int x = 0; x <= lastRow; x++) { HSSFRow row = sheet.getRow(x); if (row != null) { firstPass(row); } } } catch (IOException e) { e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates. } if (!keyValue.containsKey(SpreadhseetVocabulary.EXPERIMENT_ID.getKeyName())) { System.err.println("Didn't find an experiment id in the spreadsheet"); System.exit(0); } else { Cell c = keyValue.get(SpreadhseetVocabulary.EXPERIMENT_ID.getKeyName()).get(0); String texpId = getValueForKey(c); if (!texpId.equals("")) { expId = texpId; } experiment = new KUPExperiment(expId); if (experiment == null) { System.err.println("Can't create experiment"); System.exit(0); } else { if (keyValue.get(SpreadhseetVocabulary.COMPOUND_LIST.getKeyName()) != null) { String desc = getValueForKey( keyValue.get(SpreadhseetVocabulary.COMPOUND_LIST.getKeyName()).get(0)); if (!desc.equals("")) { experiment.setListType(desc); } } if (keyValue.get(SpreadhseetVocabulary.EXPERIMENT_ASSAY.getKeyName()) != null) { String desc = getValueForKey( keyValue.get(SpreadhseetVocabulary.EXPERIMENT_ASSAY.getKeyName()).get(0)); if (!desc.equals("")) { experiment.setAssayType(desc); } } if (keyValue.get(SpreadhseetVocabulary.PRE_ANALYTICAL.getKeyName()) != null) { String desc = getValueForKey( keyValue.get(SpreadhseetVocabulary.PRE_ANALYTICAL.getKeyName()).get(0)); if (!desc.equals("")) { experiment.setPreAnalyticalTechnuique(desc); } } if (keyValue.get(SpreadhseetVocabulary.ANALYSIS_TYPE.getKeyName()) != null) { String desc = getValueForKey( keyValue.get(SpreadhseetVocabulary.ANALYSIS_TYPE.getKeyName()).get(0)); if (!desc.equals("")) { experiment.setAnalysisType(desc); } } } } // now go through the analysis roles Set<KUPAnnotation> annotations = new HashSet<KUPAnnotation>(); String uniqueString = ""; int rolecounter = 0; for (Cell currentRole : roleCell) { // search from current cell down until you get to the next role int rowIndex = currentRole.getRowIndex(); // first is the Role uniqueString = "_" + String.valueOf(System.currentTimeMillis() + rolecounter); analysisId = expId + uniqueString; KUPAnnotation annotation = new KUPAnnotation(analysisId); String roleValue = getValueForKey(currentRole); if (roleValue.equals("")) { rolecounter++; continue; } annotation.setRole(roleValue); rowIndex++; // now keep going until you find the next role Set<String> qualities = new HashSet<String>(); Set<String> bioMaterials = new HashSet<String>(); while (keepGettingRole(rowIndex)) { HSSFRow currentRow = sheet.getRow(rowIndex); // get the first cell Cell cell = currentRow.getCell(0); System.err.println(cell.getStringCellValue()); if (cell.getStringCellValue().toLowerCase() .equals(SpreadhseetVocabulary.EXPERIMENT_CONDITION.getKeyName())) { String t = getValueForKey(cell); IRI iri = lookupId(cell, t); if (iri != null) { t = iri.toString(); } annotation.setCondition(t); System.out.println("Setting exp condition: " + t); } else if (cell.getStringCellValue().toLowerCase() .equals(SpreadhseetVocabulary.SPECIES.getKeyName())) { String t = getValueForKey(cell); IRI iri = lookupId(cell, t); if (iri != null) { t = iri.toString(); } annotation.setTaxonomy(t); System.out.println("Setting taxonomy: " + t); } else if (cell.getStringCellValue().toLowerCase() .equals(SpreadhseetVocabulary.DISEASE.getKeyName())) { String t = getValueForKey(cell); String[] diseaseValues = t.split("\\s*\\|\\s*"); Set<String> diseasesSet = new HashSet<String>(); for (String s : diseaseValues) { s = s.trim(); IRI iri = lookupId(cell, s); if (iri != null) { s = iri.toString(); } System.out.println("Setting disease: " + s); diseasesSet.add(s); } annotation.getHasDisease().addAll(diseasesSet); } else if (cell.getStringCellValue().toLowerCase() .equals(SpreadhseetVocabulary.BIOMATERIAL.getKeyName())) { String desc = getValueForKey( keyValue.get(SpreadhseetVocabulary.BIOMATERIAL.getKeyName()).get(rolecounter)); String[] values = desc.split("\\s*\\|\\s*"); for (String s : values) { s = s.trim(); IRI iri = lookupId(cell, s); if (iri != null) { s = iri.toString(); } System.out.println("Setting biomaterial: " + s); bioMaterials.add(s); } } else if (cell.getStringCellValue().toLowerCase() .equals(SpreadhseetVocabulary.EXPERIMENT_DESCRIPTION.getKeyName())) { String description = getValueForKey( keyValue.get(SpreadhseetVocabulary.EXPERIMENT_DESCRIPTION.getKeyName()).get(0)); if (!description.equals("")) { System.out.println("Setting description: " + description); experiment.setAssayDescription(description); } } else { String t = getValueForKey(cell); if (!t.equals("")) { IRI iri = lookupId(cell, t); if (iri != null) { t = iri.toString(); } System.out.println("Setting quality: " + t); qualities.add(t); } // the rest are qualities } rowIndex++; } annotation.getBioMaterial().addAll(bioMaterials); annotation.getQualities().addAll(qualities); annotations.add(annotation); rolecounter++; } // finally parse the compound lists CompoundList comList = new CompoundList(expId + uniqueString); for (int r = compoundListStart; r <= sheet.getLastRowNum(); r++) { if (sheet.getRow(r) == null) { continue; } CompoundList.ListMember listMember = comList.newListMember(); // Cell firstCell = sheet.getRow(r).getCell(1); // if (firstCell != null) { // if (!firstCell.getStringCellValue().equals("")) { for (String key : compoundAttributeToColumn.keySet()) { if (key.equals(SpreadhseetVocabulary.GENE_SYMBOL.getKeyName())) { // get the value in the cell int col = compoundAttributeToColumn.get(key); Cell cell = sheet.getRow(r).getCell(col); if (cell != null) listMember.setGeneSymbol(cell.getStringCellValue()); } if (key.equals(SpreadhseetVocabulary.GENE_ID.getKeyName()) || key.equals(SpreadhseetVocabulary.ENTREZ_GENE_ID.getKeyName())) { // get the value in the cell int col = compoundAttributeToColumn.get(key); Cell cell = sheet.getRow(r).getCell(col); if (cell != null) { String s = cell.getStringCellValue(); if (s.contains("E")) { String tmps = s.substring(s.indexOf("E")); s = s.replace(tmps, ""); s = s.replace(".", ""); } else if (s.endsWith(".0")) { s = s.replace(".0", ""); } listMember.setGeneId(s); } } if (key.equals(SpreadhseetVocabulary.UNIPROT_ID.getKeyName()) || key.equals(SpreadhseetVocabulary.UNIPROT_ACC.getKeyName())) { // get the value in the cell int col = compoundAttributeToColumn.get(key); Cell cell = sheet.getRow(r).getCell(col); if (cell != null) listMember.setUniprotID(cell.getStringCellValue()); } if (key.equals(SpreadhseetVocabulary.HMDB_ID.getKeyName())) { // get the value in the cell int col = compoundAttributeToColumn.get(key); Cell cell = sheet.getRow(r).getCell(col); if (cell != null) listMember.setHmdbid(cell.getStringCellValue()); } if (key.equals(SpreadhseetVocabulary.MICROCOSM.getKeyName())) { // get the value in the cell int col = compoundAttributeToColumn.get(key); Cell cell = sheet.getRow(r).getCell(col); if (cell != null) listMember.setMicrocosmid(cell.getStringCellValue()); } if (key.equals(SpreadhseetVocabulary.EXPRESSION_STRENGTH.getKeyName())) { // get the value in the cell int col = compoundAttributeToColumn.get(key); Cell cell = sheet.getRow(r).getCell(col); if (cell != null) listMember.setExpressionStrength(cell.getStringCellValue()); } if (key.equals(SpreadhseetVocabulary.DIFFERENTIAL.getKeyName())) { // get the value in the cell int col = compoundAttributeToColumn.get(key); Cell cell = sheet.getRow(r).getCell(col); if (cell != null) listMember.setDifferential(cell.getStringCellValue()); } if (key.equals(SpreadhseetVocabulary.RATIO.getKeyName())) { // get the value in the cell int col = compoundAttributeToColumn.get(key); Cell cell = sheet.getRow(r).getCell(col); if (cell != null) listMember.setRatio(cell.getStringCellValue()); } if (key.equals(SpreadhseetVocabulary.P_VALUE.getKeyName())) { // get the value in the cell int col = compoundAttributeToColumn.get(key); Cell cell = sheet.getRow(r).getCell(col); if (cell != null) listMember.setPValue(cell.getStringCellValue()); } if (key.equals(SpreadhseetVocabulary.FDR.getKeyName())) { // get the value in the cell int col = compoundAttributeToColumn.get(key); Cell cell = sheet.getRow(r).getCell(col); if (cell != null) listMember.setFdrValue(cell.getStringCellValue()); } } comList.getMembers().add(listMember); // } // } } KUPAnalysis analysis = new KUPAnalysis(analysisId); analysis.getCompoundList().add(comList); analysis.getAnnotations().addAll(annotations); experiment.getAnalysis().add(analysis); }
From source file:kupkb_experiments.ExperimentSpreadSheetParser.java
License:Open Source License
private boolean keepGettingRole(int rowIndex) { HSSFRow currentRow = sheet.getRow(rowIndex); if (currentRow == null) { return false; }/* www . ja va 2s .c o m*/ Cell cell = currentRow.getCell(0); if (cell == null) { return false; } else if (cell.getStringCellValue().equals("")) { return false; } return true; //To change body of created methods use File | Settings | File Templates. }