List of usage examples for org.apache.poi.ss.usermodel Workbook setSheetName
void setSheetName(int sheet, String name);
From source file:org.cgiar.ccafs.ap.summaries.projects.xlsx.ProjectsNotModifiedSummaryXLS.java
License:Open Source License
/** * This method is used to generate the xls file for the ProjectLeading institutions. * // w w w . j a va 2 s.co m * @param projectList is the list with the projects partner leaders * @return a byte array with the information provided for the xls file. */ public byte[] generateXLS(List<Map<String, Object>> projectList) { Workbook workbook = xls.initializeWorkbook(true); /***************** Submmited project level ******************/ // Defining headers String[] headersProject = new String[] { "ID", "Title", "Summary", "Project type" }; // Defining header types int[] headerTypesProject = { BaseXLS.COLUMN_TYPE_HYPERLINK, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_SHORT }; // creating sheet Sheet sheet = workbook.getSheetAt(0); workbook.setSheetName(0, "Projects Not Modified"); try { xls.initializeSheet(sheet, headerTypesProject); // write text box xls.writeTitleBox(sheet, "Projects Not Modified Summary"); // write text box xls.createLogo(workbook, sheet); xls.writeHeaders(sheet, headersProject); this.addContent(projectList, sheet); // Set description xls.writeDescription(sheet, xls.getText("summaries.project.not.modified.description")); xls.writeWorkbook(); byte[] byteArray = xls.getBytes(); // Closing streams. xls.closeStreams(); return byteArray; } catch (IOException e) { e.printStackTrace(); } return null; }
From source file:org.cgiar.ccafs.ap.summaries.projects.xlsx.SearchTermsSummaryXLS.java
License:Open Source License
/** * This method is used to generate the xls file for the ProjectLeading institutions. * //from w w w. j av a 2 s .c om * @param projectList is the list with the projects partner leaders * @return a byte array with the information provided for the xls file. */ public byte[] generateXLS(List<Map<String, Object>> projectList, List<Map<String, Object>> activityList, List<Map<String, Object>> deliverableList, String[] termsToSearch) { Workbook workbook = xls.initializeWorkbook(true); /***************** Gender Contribution Report Project Level ******************/ // Defining headers String[] headersProject = new String[] { "Project Id", "Title", "Summary", "Outcome statement", "Start date", "End date", "Flagship(s)", "Region(s)", "Lead institution", "Leader", "Coordinator", "Total budget W1/W2", "Total budget W3/Bilateral", "Total gender W1/W2", "Total gender W3/Bilateral" }; // Defining header types int[] headerTypesProject = { BaseXLS.COLUMN_TYPE_HYPERLINK, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_DATE, BaseXLS.COLUMN_TYPE_DATE, BaseXLS.COLUMN_TYPE_TEXT_SHORT, BaseXLS.COLUMN_TYPE_TEXT_SHORT, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_BUDGET, BaseXLS.COLUMN_TYPE_BUDGET, BaseXLS.COLUMN_TYPE_BUDGET, BaseXLS.COLUMN_TYPE_BUDGET }; // creating sheet Sheet[] sheets = new Sheet[3]; sheets[0] = workbook.getSheetAt(0); sheets[1] = workbook.cloneSheet(0); sheets[2] = workbook.cloneSheet(0); workbook.setSheetName(0, "Projects"); workbook.setSheetName(1, "Activities "); workbook.setSheetName(2, "Deliverables "); try { xls.initializeSheet(sheets[0], headerTypesProject); xls.writeHeaders(sheets[0], headersProject); this.addContent(projectList, sheets[0], 0, termsToSearch); // Set description xls.writeDescription(sheets[0], xls.getText("summaries.gender.summary.sheetone.description", new String[] { StringUtils.join(termsToSearch, ", ") })); // write text box xls.writeTitleBox(sheets[0], "Search Terms Summary Project Level Summary"); // write text box xls.createLogo(workbook, sheets[0]); /***************** Gender Contribution Report Activity Level ******************/ // Defining headers String[] headersActivity = new String[] { "Project Id", "Project Title", "Activity Id", "Activity Title", "Description", "Start date", "End date", "Leader Institution", "Leader Person" }; // Defining header types int[] headerTypesActivity = { BaseXLS.COLUMN_TYPE_HYPERLINK, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_HYPERLINK, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_DATE, BaseXLS.COLUMN_TYPE_DATE, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG }; xls.initializeSheet(sheets[1], headerTypesActivity); xls.writeHeaders(sheets[1], headersActivity); this.addContent(activityList, sheets[1], 1, termsToSearch); // Set description xls.writeDescription(sheets[1], xls.getText("summaries.gender.summary.sheettwo.description", new String[] { StringUtils.join(termsToSearch, ", ") })); // write text box xls.writeTitleBox(sheets[1], "Search Terms Summary Project Level Summary"); // write text box xls.createLogo(workbook, sheets[1]); /***************** Gender Contribution Report Deliverable Level ******************/ // Defining headers String[] headersDeliverable = new String[] { "Project Id", "Project Title", "Deliverable Id", "Deliverable Title", "Deliverable Type", "Deliverable Sub-Type", "Next User", "Knowledge, attitude, skills and practice changes ", " Strategies", "Leader Institution", "Responsible Person" }; // Defining header types int[] headerTypesDeliverable = { BaseXLS.COLUMN_TYPE_HYPERLINK, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_HYPERLINK, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_SHORT, BaseXLS.COLUMN_TYPE_TEXT_SHORT, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG }; xls.initializeSheet(sheets[2], headerTypesDeliverable); xls.writeHeaders(sheets[2], headersDeliverable); this.addContent(deliverableList, sheets[2], 2, termsToSearch); // Set description xls.writeDescription(sheets[2], xls.getText("summaries.gender.summary.sheetthree.description", new String[] { StringUtils.join(termsToSearch, ", ") })); // write text box xls.writeTitleBox(sheets[2], "Search Terms Summary Project Level Summary"); // write text box xls.createLogo(workbook, sheets[2]); xls.writeWorkbook(); byte[] byteArray = xls.getBytes(); // Closing streams. xls.closeStreams(); return byteArray; } catch (IOException e) { e.printStackTrace(); } return null; }
From source file:org.cgiar.ccafs.ap.summaries.projects.xlsx.SubmissionProjectSummaryXLS.java
License:Open Source License
/** * This method is used to generate the xls file for the ProjectLeading institutions. * /*from w ww .j av a2 s . c o m*/ * @param projectList is the list with the projects partner leaders * @return a byte array with the information provided for the xls file. */ public byte[] generateXLS(List<Map<String, Object>> projectList) { Workbook workbook = xls.initializeWorkbook(true); /***************** Submmited project level ******************/ // Defining headers String[] headersProject = new String[] { "ID", "Title", "Summary", "Type", "Submitted by", "Submitted Date" }; // Defining header types int[] headerTypesProject = { BaseXLS.COLUMN_TYPE_HYPERLINK, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_SHORT, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_DATE_TIME }; // creating sheet Sheet sheet = workbook.getSheetAt(0); workbook.setSheetName(0, "Submitted Projects"); try { xls.initializeSheet(sheet, headerTypesProject); xls.writeHeaders(sheet, headersProject); this.addContent(projectList, sheet); // Set description xls.writeDescription(sheet, xls.getText("summaries.project.submitted.description")); // write text box xls.writeTitleBox(sheet, "Submitted Projects Summary"); // write text box xls.createLogo(workbook, sheet); xls.writeWorkbook(); byte[] byteArray = xls.getBytes(); // Closing streams. xls.closeStreams(); return byteArray; } catch (IOException e) { e.printStackTrace(); } return null; }
From source file:org.dbunit.dataset.excel.XlsDataSetWriter.java
License:Open Source License
/** * Write the specified dataset to the specified Excel document. *//*from w ww . ja v a2 s . co m*/ public void write(IDataSet dataSet, OutputStream out) throws IOException, DataSetException { logger.debug("write(dataSet={}, out={}) - start", dataSet, out); Workbook workbook = createWorkbook(); this.dateCellStyle = createDateCellStyle(workbook); int index = 0; ITableIterator iterator = dataSet.iterator(); while (iterator.next()) { // create the table i.e. sheet ITable table = iterator.getTable(); ITableMetaData metaData = table.getTableMetaData(); Sheet sheet = workbook.createSheet(metaData.getTableName()); // write table metadata i.e. first row in sheet workbook.setSheetName(index, metaData.getTableName()); Row headerRow = sheet.createRow(0); Column[] columns = metaData.getColumns(); for (int j = 0; j < columns.length; j++) { Column column = columns[j]; Cell cell = headerRow.createCell(j); cell.setCellValue(column.getColumnName()); } // write table data for (int j = 0; j < table.getRowCount(); j++) { Row row = sheet.createRow(j + 1); for (int k = 0; k < columns.length; k++) { Column column = columns[k]; Object value = table.getValue(j, column.getColumnName()); if (value != null) { Cell cell = row.createCell(k); if (value instanceof Date) { setDateCell(cell, (Date) value, workbook); } else if (value instanceof BigDecimal) { setNumericCell(cell, (BigDecimal) value, workbook); } else if (value instanceof Long) { setDateCell(cell, new Date(((Long) value).longValue()), workbook); } else { cell.setCellValue(DataType.asString(value)); } } } } index++; } // write xls document workbook.write(out); out.flush(); }
From source file:org.forzaframework.util.ExcelUtils.java
License:Apache License
public static Sheet getSheet(Workbook wb, String sheetName, Boolean createNewSheet, Integer indexSheet) { Sheet sheet = null;//Revisamos si vamos a crear una hoja nueva o con una ya existente. if ((createNewSheet != null && createNewSheet) || wb.getNumberOfSheets() == 0) { //Creamos una hoja nueva if (sheetName != null) { sheet = wb.createSheet(sheetName); } else {/*from w w w . ja v a 2 s . c o m*/ sheet = wb.createSheet(); } } else { //Revisamos si existe la hoja con el nombre especificado if (indexSheet == null && sheetName != null) { sheet = wb.getSheet(sheetName); } if (sheet == null) { //Trabajamos con una hoja ya existente if (indexSheet == null) { indexSheet = 0; } if (sheetName != null) { wb.setSheetName(indexSheet, sheetName); } sheet = wb.getSheetAt(indexSheet); } } return sheet; }
From source file:org.openmrs.module.mksreports.renderer.PatientHistoryExcelTemplateRenderer.java
License:Open Source License
/** * Clone the sheet at the passed index and replace values as needed *///from w w w .j a v a 2 s . c o m public Sheet addSheet(Workbook wb, SheetToAdd sheetToAdd, Set<String> usedSheetNames, ReportData reportData, ReportDesign design, Map<String, String> repeatSections) { String prefix = getExpressionPrefix(design); String suffix = getExpressionSuffix(design); Sheet sheet = sheetToAdd.getSheet(); sheet.setForceFormulaRecalculation(true); int sheetIndex = wb.getSheetIndex(sheet); // Configure the sheet name, replacing any values as needed, and ensuring it is unique for the workbook String sheetName = EvaluationUtil.evaluateExpression(sheetToAdd.getOriginalSheetName(), sheetToAdd.getReplacementData(), prefix, suffix).toString(); sheetName = ExcelUtil.formatSheetTitle(sheetName, usedSheetNames); wb.setSheetName(sheetIndex, sheetName); usedSheetNames.add(sheetName); log.debug("Handling sheet: " + sheetName + " at index " + sheetIndex); // Iterate across all of the rows in the sheet, and configure all those that need to be added/cloned List<RowToAdd> rowsToAdd = new ArrayList<RowToAdd>(); int totalRows = sheet.getPhysicalNumberOfRows(); int rowsFound = 0; for (int rowNum = 0; rowsFound < totalRows && rowNum < 50000; rowNum++) { // check for < 50000 is a hack to prevent infinite loops in edge cases Row currentRow = sheet.getRow(rowNum); if (log.isDebugEnabled()) { log.debug("Handling row: " + ExcelUtil.formatRow(currentRow)); } if (currentRow != null) { rowsFound++; } // If we find that the row that we are on is a repeating row, then add the appropriate number of rows to clone String repeatingRowProperty = getRepeatingRowProperty(sheetToAdd.getOriginalSheetNum(), rowNum, repeatSections); if (repeatingRowProperty != null) { String[] dataSetSpanSplit = repeatingRowProperty.split(","); String dataSetName = dataSetSpanSplit[0]; DataSet dataSet = getDataSet(reportData, dataSetName, sheetToAdd.getReplacementData()); int numRowsToRepeat = 1; if (dataSetSpanSplit.length == 2) { numRowsToRepeat = Integer.parseInt(dataSetSpanSplit[1]); } log.debug("Repeating this row with dataset: " + dataSet + " and repeat of " + numRowsToRepeat); int repeatNum = 0; for (DataSetRow dataSetRow : dataSet) { repeatNum++; for (int i = 0; i < numRowsToRepeat; i++) { Row row = (i == 0 ? currentRow : sheet.getRow(rowNum + i)); if (repeatNum == 1 && row != null && row != currentRow) { rowsFound++; } Map<String, Object> newReplacements = getReplacementData(sheetToAdd.getReplacementData(), reportData, design, dataSetName, dataSetRow, repeatNum); rowsToAdd.add(new RowToAdd(row, newReplacements)); if (log.isDebugEnabled()) { log.debug("Adding " + ExcelUtil.formatRow(row) + " with dataSetRow: " + dataSetRow); } } } if (numRowsToRepeat > 1) { rowNum += numRowsToRepeat - 1; } } else { rowsToAdd.add(new RowToAdd(currentRow, sheetToAdd.getReplacementData())); if (log.isDebugEnabled()) { log.debug("Adding row: " + ExcelUtil.formatRow(currentRow)); } } } // Now, go through all of the collected rows, and add them back in for (int i = 0; i < rowsToAdd.size(); i++) { RowToAdd rowToAdd = rowsToAdd.get(i); if (rowToAdd.getRowToClone() != null && rowToAdd.getRowToClone().cellIterator() != null) { Row addedRow = addRow(wb, sheetToAdd, rowToAdd, i, reportData, design, repeatSections); if (log.isDebugEnabled()) { log.debug("Wrote row " + i + ": " + ExcelUtil.formatRow(addedRow)); } } } return sheet; }
From source file:org.riflemansd.businessprofit.excel.ExcelExampleFont.java
License:Open Source License
public static void main(String[] args) { // create a new file FileOutputStream out = null;//from ww w.j a va2 s . c om try { out = new FileOutputStream("workbook.xls"); } catch (FileNotFoundException ex) { Logger.getLogger(ExcelExampleFont.class.getName()).log(Level.SEVERE, null, ex); } // create a new workbook Workbook wb = new HSSFWorkbook(); // create a new sheet Sheet s = wb.createSheet(); // declare a row object reference Row r = null; // declare a cell object reference Cell c = null; // create 3 cell styles CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); CellStyle cs3 = wb.createCellStyle(); DataFormat df = wb.createDataFormat(); // create 2 fonts objects Font f = wb.createFont(); Font f2 = wb.createFont(); //set font 1 to 12 point type f.setFontHeightInPoints((short) 12); //make it blue f.setColor((short) 0xc); // make it bold //arial is the default font f.setBoldweight(Font.BOLDWEIGHT_BOLD); //set font 2 to 10 point type f2.setFontHeightInPoints((short) 10); //make it red f2.setColor((short) Font.COLOR_RED); //make it bold f2.setBoldweight(Font.BOLDWEIGHT_BOLD); f2.setStrikeout(true); //set cell stlye cs.setFont(f); //set the cell format cs.setDataFormat(df.getFormat("#,##0.0")); //set a thin border cs2.setBorderBottom(cs2.BORDER_THIN); //fill w fg fill color cs2.setFillPattern((short) CellStyle.SOLID_FOREGROUND); //set the cell format to text see DataFormat for a full list cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); // set the font cs2.setFont(f2); // set the sheet name in Unicode wb.setSheetName(0, "\u0422\u0435\u0441\u0442\u043E\u0432\u0430\u044F " + "\u0421\u0442\u0440\u0430\u043D\u0438\u0447\u043A\u0430"); // in case of plain ascii // wb.setSheetName(0, "HSSF Test"); // create a sheet with 30 rows (0-29) int rownum; for (rownum = (short) 0; rownum < 30; rownum++) { // create a row r = s.createRow(rownum); // on every other row if ((rownum % 2) == 0) { // make the row height bigger (in twips - 1/20 of a point) r.setHeight((short) 0x249); } //r.setRowNum(( short ) rownum); // create 10 cells (0-9) (the += 2 becomes apparent later for (short cellnum = (short) 0; cellnum < 10; cellnum += 2) { // create a numeric cell c = r.createCell(cellnum); // do some goofy math to demonstrate decimals c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); String cellValue; // create a string cell (see why += 2 in the c = r.createCell((short) (cellnum + 1)); // on every other row if ((rownum % 2) == 0) { // set this cell to the first cell style we defined c.setCellStyle(cs); // set the cell's string value to "Test" c.setCellValue("Test"); } else { c.setCellStyle(cs2); // set the cell's string value to "\u0422\u0435\u0441\u0442" c.setCellValue("\u0422\u0435\u0441\u0442"); } // make this column a bit wider s.setColumnWidth((short) (cellnum + 1), (short) ((50 * 8) / ((double) 1 / 20))); } } //draw a thick black border on the row at the bottom using BLANKS // advance 2 rows rownum++; rownum++; r = s.createRow(rownum); // define the third style to be the default // except with a thick black border at the bottom cs3.setBorderBottom(cs3.BORDER_THICK); //create 50 cells for (short cellnum = (short) 0; cellnum < 50; cellnum++) { //create a blank type cell (no value) c = r.createCell(cellnum); // set it to the thick black border style c.setCellStyle(cs3); } //end draw thick black border // demonstrate adding/naming and deleting a sheet // create a sheet, set its title then delete it s = wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); //end deleted sheet try { // write the workbook to the output stream // close our file (don't blow out our file handles wb.write(out); } catch (IOException ex) { Logger.getLogger(ExcelExampleFont.class.getName()).log(Level.SEVERE, null, ex); } try { out.close(); } catch (IOException ex) { Logger.getLogger(ExcelExampleFont.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:org.tiefaces.components.websheet.configuration.ConfigurationHandler.java
License:MIT License
/** * Copy the each command area to seperated sheet. As it will be used for * iteration./* ww w .j av a 2s . co m*/ * * @param sheet * sheet. */ private void copyTemplateForTieCommands(final Sheet sheet) { // if skip configuration. then return. if (parent.isSkipConfiguration()) { return; } Workbook wb = sheet.getWorkbook(); String copyName = TieConstants.COPY_SHEET_PREFIX + sheet.getSheetName(); if (wb.getSheet(copyName) == null) { Sheet newSheet = wb.cloneSheet(wb.getSheetIndex(sheet)); int sheetIndex = wb.getSheetIndex(newSheet); wb.setSheetName(sheetIndex, copyName); wb.setSheetHidden(sheetIndex, Workbook.SHEET_STATE_VERY_HIDDEN); } }
From source file:snpviewer.SnpViewer.java
License:Open Source License
public void writeSavedRegionsToFile() { if (savedRegions.size() < 1) { Dialogs.showErrorDialog(null, "No Saved Regions exist to write!", "No Saved Regions", "SnpViewer"); return;/*from w w w.j a v a 2 s .co m*/ } final int flanks = 10; FileChooser fileChooser = new FileChooser(); FileChooser.ExtensionFilter extFilter = new FileChooser.ExtensionFilter("Excel (*.xlsx)", "*.xlsx"); fileChooser.getExtensionFilters().add(extFilter); fileChooser.setTitle("Write regions to Excel file (.xlsx)..."); File rFile = fileChooser.showSaveDialog(mainWindow); if (rFile == null) { return; } else if (!rFile.getName().endsWith(".xlsx")) { rFile = new File(rFile.getAbsolutePath() + ".xlsx"); } final File regionFile = rFile; final Task<Boolean> writeTask = new Task() { @Override protected Boolean call() throws Exception { try { updateProgress(-1, -1); BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(regionFile)); Workbook wb = new XSSFWorkbook(); //first create a summary sheet of all regions Sheet sheet = wb.createSheet(); Row row = null; int rowNo = 0; int sheetNo = 0; wb.setSheetName(sheetNo++, "Summary"); row = sheet.createRow(rowNo++); String header[] = { "Coordinates", "rsIDs", "Size (Mb)" }; for (int col = 0; col < header.length; col++) { Cell cell = row.createCell(col); cell.setCellValue(header[col]); } for (int i = 0; i < savedRegions.size(); i++) { row = sheet.createRow(rowNo++); int col = 0; Cell cell = row.createCell(col++); cell.setCellValue("chr" + savedRegions.get(i).getCoordinateString()); cell = row.createCell(col++); cell.setCellValue(savedRegions.get(i).getIdLine()); cell = row.createCell(col++); double mB = (double) savedRegions.get(i).getLength() / 1000000; cell.setCellValue(mB); } ArrayList<SnpFile> bothFiles = new ArrayList<>(); bothFiles.addAll(affFiles); bothFiles.addAll(unFiles); String prevChrom = new String(); double prog = 0; double total = savedRegions.size() * bothFiles.size() * 2; updateProgress(prog, total); int regCounter = 0; for (RegionSummary reg : savedRegions) { updateMessage("Writing region " + ++regCounter + " of " + savedRegions.size()); //create a sheet for each chromosome if (!reg.getChromosome().equalsIgnoreCase(prevChrom)) { if (!prevChrom.isEmpty()) { CellRangeAddress[] regions = { new CellRangeAddress(0, rowNo, 2, 2 + bothFiles.size()) }; SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); ConditionalFormattingRule rule1 = sheetCF .createConditionalFormattingRule(ComparisonOperator.EQUAL, "\"AA\""); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); ConditionalFormattingRule rule2 = sheetCF .createConditionalFormattingRule(ComparisonOperator.EQUAL, "\"BB\""); PatternFormatting fill2 = rule2.createPatternFormatting(); fill2.setFillBackgroundColor(IndexedColors.PALE_BLUE.index); fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND); ConditionalFormattingRule rule3 = sheetCF .createConditionalFormattingRule(ComparisonOperator.EQUAL, "\"AB\""); PatternFormatting fill3 = rule3.createPatternFormatting(); fill3.setFillBackgroundColor(IndexedColors.ROSE.index); fill3.setFillPattern(PatternFormatting.SOLID_FOREGROUND); sheetCF.addConditionalFormatting(regions, rule3, rule2); sheetCF.addConditionalFormatting(regions, rule1); } rowNo = 0; sheet = wb.createSheet(); wb.setSheetName(sheetNo++, reg.getChromosome()); prevChrom = reg.getChromosome(); } else {//pad regions with an empty line rowNo++; } TreeMap<Integer, HashMap<String, String>> coordMap = new TreeMap(); /*coordmap - key is position, key of hashmap * is input filename and value call */ HashMap<Integer, String> coordToId = new HashMap<>(); //coordinate to rs ID try { for (SnpFile f : bothFiles) { updateProgress(prog++, total); if (isCancelled()) { return false; } List<SnpFile.SnpLine> lines = f.getSnpsInRegion(reg.getChromosome(), reg.getStartPos(), reg.getEndPos(), flanks); for (SnpFile.SnpLine snpLine : lines) { if (isCancelled()) { return false; } Integer coord = snpLine.getPosition(); if (!coordMap.containsKey(coord)) { coordMap.put(coord, new HashMap<String, String>()); } String filename = f.inputFile.getName(); String rsId = snpLine.getId(); String call = snpLine.getCall(); coordMap.get(coord).put(filename, call); coordToId.put(coord, rsId); } } row = sheet.createRow(rowNo++); Cell cell = row.createCell(0); cell.setCellValue(reg.getCoordinateString()); row = sheet.createRow(rowNo++); cell = row.createCell(0); cell.setCellValue(reg.getIdLine()); int col = 0; row = sheet.createRow(rowNo++); cell = row.createCell(col++); cell.setCellValue("Position"); cell = row.createCell(col++); cell.setCellValue("rsID"); for (SnpFile f : bothFiles) { updateProgress(prog++, total); cell = row.createCell(col++); if (f.getSampleName() != null && !f.getSampleName().isEmpty()) { cell.setCellValue(f.getSampleName()); } else { cell.setCellValue(f.inputFile.getName()); } } for (Entry current : coordMap.entrySet()) { if (isCancelled()) { return false; } col = 0; Integer coord = (Integer) current.getKey(); row = sheet.createRow(rowNo++); cell = row.createCell(col++); cell.setCellValue(coord); cell = row.createCell(col++); cell.setCellValue(coordToId.get(coord)); HashMap<String, String> fileToCall = (HashMap<String, String>) current.getValue(); for (SnpFile f : bothFiles) { cell = row.createCell(col++); if (fileToCall.containsKey(f.inputFile.getName())) { cell.setCellValue(fileToCall.get(f.inputFile.getName())); } else { cell.setCellValue("-"); } } } } catch (Exception ex) { return false; } } CellRangeAddress[] regions = { new CellRangeAddress(0, rowNo, 2, 2 + bothFiles.size()) }; SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); ConditionalFormattingRule rule1 = sheetCF .createConditionalFormattingRule(ComparisonOperator.EQUAL, "\"AA\""); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); ConditionalFormattingRule rule2 = sheetCF .createConditionalFormattingRule(ComparisonOperator.EQUAL, "\"BB\""); PatternFormatting fill2 = rule2.createPatternFormatting(); fill2.setFillBackgroundColor(IndexedColors.PALE_BLUE.index); fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND); ConditionalFormattingRule rule3 = sheetCF .createConditionalFormattingRule(ComparisonOperator.EQUAL, "\"AB\""); PatternFormatting fill3 = rule3.createPatternFormatting(); fill3.setFillBackgroundColor(IndexedColors.ROSE.index); fill3.setFillPattern(PatternFormatting.SOLID_FOREGROUND); sheetCF.addConditionalFormatting(regions, rule3, rule2); sheetCF.addConditionalFormatting(regions, rule1); wb.write(out); updateProgress(total, total); out.close(); } catch (IOException | NumberFormatException ex) { ex.printStackTrace(); return false; } return true; } };//end of task setProgressMode(true); progressBar.progressProperty().bind(writeTask.progressProperty()); progressMessage.textProperty().bind(writeTask.messageProperty()); writeTask.setOnSucceeded(new EventHandler<WorkerStateEvent>() { @Override public void handle(WorkerStateEvent e) { if (e.getSource().getValue() == true) { Dialogs.showInformationDialog(null, "Saved regions written " + "to file " + "(" + regionFile.getName() + ")successfully", "Regions Written", "SNP Viewer"); } else { Dialogs.showErrorDialog(null, "Region write failed.", "Write Failed", "SNP Viewer"); } setProgressMode(false); progressBar.progressProperty().unbind(); progressBar.progressProperty().set(0); progressMessage.textProperty().unbind(); progressMessage.setText(""); progressTitle.setText(""); } }); writeTask.setOnFailed(new EventHandler<WorkerStateEvent>() { @Override public void handle(WorkerStateEvent e) { setProgressMode(false); progressBar.progressProperty().unbind(); progressBar.progressProperty().set(0); progressMessage.textProperty().unbind(); progressMessage.setText(""); progressTitle.setText("Region write failed!"); Dialogs.showErrorDialog(null, "Error writing region to file\n", "Region write error", "SNP Viewer", e.getSource().getException()); } }); writeTask.setOnCancelled(new EventHandler<WorkerStateEvent>() { @Override public void handle(WorkerStateEvent e) { progressMessage.setText("Region write cancelled"); progressTitle.setText("Cancelled"); setProgressMode(false); progressBar.progressProperty().unbind(); progressBar.progressProperty().set(0); Dialogs.showErrorDialog(null, "Error writing region to file\n", "Region write error", "SNP Viewer"); } }); cancelButton.setOnAction(new EventHandler<ActionEvent>() { @Override public void handle(ActionEvent actionEvent) { writeTask.cancel(); } }); progressTitle.setText("Writing regions to .xlsx file"); new Thread(writeTask).start(); }
From source file:weka.core.converters.ExcelSaver.java
License:Open Source License
/** * Writes a Batch of instances./* w w w .j a v a 2 s . co m*/ * * @throws IOException throws IOException if saving in batch mode is not * possible */ @Override public void writeBatch() throws IOException { if (getInstances() == null) { throw new IOException("No instances to save"); } if (getRetrieval() == INCREMENTAL) { throw new IOException("Batch and incremental saving cannot be mixed."); } setRetrieval(BATCH); setWriteMode(WRITE); try { Instances data = getInstances(); Workbook workbook; if (retrieveUseOOXML()) { workbook = new XSSFWorkbook(); } else { workbook = new HSSFWorkbook(); } Sheet sheet = workbook.createSheet(); workbook.setSheetName(0, data.relationName()); Row row; Cell cell; Instance inst; // header row = sheet.createRow(0); for (int i = 0; i < data.numAttributes(); i++) { cell = row.createCell(i); cell.setCellValue(data.attribute(i).name()); } // data for (int n = 0; n < data.numInstances(); n++) { row = sheet.createRow(n + 1); inst = data.instance(n); for (int i = 0; i < data.numAttributes(); i++) { cell = row.createCell(i); if (inst.isMissing(i)) { if (m_MissingValue.length() > 0) { cell.setCellValue(m_MissingValue); } else { cell.setCellType(Cell.CELL_TYPE_BLANK); } continue; } switch (data.attribute(i).type()) { case Attribute.NUMERIC: cell.setCellValue(inst.value(i)); break; case Attribute.NOMINAL: case Attribute.STRING: cell.setCellValue(inst.stringValue(i)); break; default: throw new IllegalStateException("Unhandled attribute type: " + data.attribute(i).type()); } } } // save if (retrieveFile() == null) { workbook.write(System.out); } else { OutputStream out = new FileOutputStream(retrieveFile()); workbook.write(out); out.close(); } } catch (Exception e) { throw new IOException(e); } setWriteMode(WAIT); resetWriter(); setWriteMode(CANCEL); }