List of usage examples for org.apache.poi.xssf.usermodel XSSFRow createCell
@Override public XSSFCell createCell(int columnIndex)
From source file:org.azkfw.document.tools.DirectoryTreeDocument.java
License:Apache License
private void onFindFile(final DirectoryParserEvent event) { DirectoryParserFileInfo info = event.getInfo(); String prefix = info.getPrefix(); File file = info.getFile();/*from w ww .ja v a2s . co m*/ XSSFRow row = sheet.createRow(offsetRow + countFile); int col = 0; for (int i = 0; i < prefix.length(); i++) { Character c = prefix.charAt(i); XSSFCell cell = row.createCell(offsetCol + col); cell.setCellValue(c.toString()); col++; } XSSFDrawing patriarch = sheet.createDrawingPatriarch(); XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 18, 18, (short) (offsetCol + col), (offsetRow + countFile), (short) (offsetCol + col + 1), (offsetRow + countFile + 1)); anchor.setAnchorType(XSSFClientAnchor.MOVE_DONT_RESIZE); if (file.isFile()) { patriarch.createPicture(anchor, sheet.getWorkbook().addPicture(imgFile.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG)); } else { patriarch.createPicture(anchor, sheet.getWorkbook().addPicture(imgDirectory.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG)); } XSSFCell cell = row.createCell(offsetCol + col + 1); cell.setCellValue(file.getName()); maxCol = Math.max(maxCol, col + 1); countFile++; }
From source file:org.centralperf.helper.view.ExcelOOXMLView.java
License:Open Source License
/** * @see AbstractPOIExcelView#buildExcelDocument(Map, Workbook, HttpServletRequest, HttpServletResponse) *///from ww w . ja va 2 s .co m @Override protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { log.debug("Generating Excel report from run samples"); // Set the headers response.setHeader("Content-Type", "application/octet-stream"); response.setHeader("Content-Disposition", "attachment; filename=central_perf_result.xlsx"); // get data model which is passed by the Spring container Run run = (Run) model.get("run"); // Set run summary informations setCellValueByName(PROJECT_NAME_CELL_NAME, run.getProject().getName(), workbook); setCellValueByName(RUN_LABEL_CELL_NAME, run.getLabel(), workbook); setCellValueByName(RUN_DESCRIPTION_CELL_NAME, run.getComment(), workbook); setCellValueByName(START_DATE_CELL_NAME, run.getStartDate().toString(), workbook); setCellValueByName(START_DATE_CELL_NAME, run.getStartDate().toString(), workbook); setCellValueByName(GENERATED_ON_CELL_NAME, "" + unixTimestamp2ExcelTimestampconvert(new Date().getTime()), workbook); // Populate data sheet XSSFSheet dataSheet = (XSSFSheet) workbook.getSheet(DATA_SHEET_NAME); // Set date style for first column CellStyle dateStyle = workbook.createCellStyle(); CreationHelper createHelper = workbook.getCreationHelper(); dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/mm/dd")); dataSheet.setDefaultColumnStyle(0, dateStyle); // Add samples for (int i = 0; i < run.getSamples().size(); i++) { Sample sample = run.getSamples().get(i); XSSFRow dataRow = dataSheet.createRow(i + 1); if (sample.getTimestamp() != null) { dataRow.createCell(0) .setCellValue(unixTimestamp2ExcelTimestampconvert(sample.getTimestamp().getTime())); dataRow.createCell(1).setCellValue(sample.getElapsed()); dataRow.createCell(2).setCellValue(sample.getSampleName()); dataRow.createCell(3).setCellValue(sample.getStatus()); dataRow.createCell(4).setCellValue(sample.getReturnCode()); dataRow.createCell(5).setCellValue(sample.getSizeInOctet()); dataRow.createCell(6).setCellValue(sample.getGrpThreads()); dataRow.createCell(7).setCellValue(sample.getAllThreads()); dataRow.createCell(8).setCellValue(sample.getLatency()); } } // Return generated sheet OutputStream outStream = null; try { outStream = response.getOutputStream(); workbook.write(outStream); outStream.flush(); } finally { outStream.close(); } }
From source file:org.cvrgrid.hl7.fileparse.PicuDataLoader.java
License:Apache License
public static void main(String[] args) throws Exception { PicuDataLoader picuDataLoader = new PicuDataLoader(); SimpleDateFormat fromUser = new SimpleDateFormat("yyyyMMddHHmmss"); SimpleDateFormat myFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); OpenTSDBConfiguration openTSDBConfiguration = picuDataLoader.getOpenTSDBConfiguration(); String urlString = openTSDBConfiguration.getOpenTSDBUrl(); HL7Measurements hl7Measurements = new HL7Measurements(); HashMap<String, String> measurementNames = hl7Measurements.getMeasurementNames(); XSSFWorkbook wb = readFile(openTSDBConfiguration.getAwareSupportedParams()); XSSFSheet sheet = wb.getSheetAt(0);//ww w . j a v a 2 s .c o m for (int r = 1; r < 280; r++) { XSSFRow row = sheet.getRow(r); if (row == null) { continue; } String key = row.getCell(2).getStringCellValue(); String value = row.getCell(1).getStringCellValue(); value = value.replaceAll(":", "/"); measurementNames.put(key, value); } HashMap<String, PatientInfo> idMatch = new HashMap<String, PatientInfo>(); File f = new File(openTSDBConfiguration.getIdMatch()); if (f.exists()) { wb = readFile(openTSDBConfiguration.getIdMatch()); sheet = wb.getSheetAt(0); for (int r = 1; r < sheet.getLastRowNum() + 1; r++) { XSSFRow row = sheet.getRow(r); PatientInfo patInfo = new PatientInfo(); patInfo.setPicuSubject(row.getCell(1).getBooleanCellValue()); patInfo.setFirstName(row.getCell(3).getStringCellValue()); patInfo.setLastName(row.getCell(4).getStringCellValue()); patInfo.setBirthDateTime(row.getCell(5).getStringCellValue()); patInfo.setGender(row.getCell(6).getStringCellValue()); patInfo.setBirthplace(row.getCell(7).getStringCellValue()); patInfo.setEarliestDataPoint(row.getCell(8).getStringCellValue()); LinkedList<String> locations = new LinkedList<String>(); String lSet = row.getCell(10).getStringCellValue(); lSet = lSet.replaceAll("\\[", ""); lSet = lSet.replaceAll("\\]", ""); String[] locationSet = lSet.split(","); for (String location : locationSet) { locations.add(location.trim()); } patInfo.setLocations(locations); LinkedList<String> variables = new LinkedList<String>(); String vSet = row.getCell(12).getStringCellValue(); vSet = vSet.replaceAll("\\[", ""); vSet = vSet.replaceAll("\\]", ""); String[] variableSet = vSet.split(","); for (String variable : variableSet) { variables.add(variable.trim()); } patInfo.setVariables(variables); idMatch.put(patInfo.getHash(), patInfo); } } System.out.println("Existing Subject Count: " + idMatch.size()); String processedFile = openTSDBConfiguration.getProcessedFile(); String rootDir = openTSDBConfiguration.getRootDir(); ArrayList<String> processedFiles = new ArrayList<String>(); File processedFileContents = new File(processedFile); getProcessedFiles(processedFileContents, processedFiles); ArrayList<String> messageFiles = new ArrayList<String>(); File rootDirContents = new File(rootDir); getDirectoryContents(rootDirContents, processedFiles, messageFiles); XSSFWorkbook workbook; XSSFSheet sheetOut, sheetOut2; if (processedFiles.size() > 1) { workbook = readFile(openTSDBConfiguration.getIdMatch()); sheetOut = workbook.getSheetAt(0); sheetOut2 = workbook.getSheetAt(1); } else { workbook = new XSSFWorkbook(); sheetOut = workbook.createSheet("idMatch"); sheetOut2 = workbook.createSheet(openTSDBConfiguration.getIdMatchSheet()); } for (String filePath : messageFiles) { System.out.println(" File: " + filePath); FileReader reader = new FileReader(filePath); Hl7InputStreamMessageIterator iter = new Hl7InputStreamMessageIterator(reader); while (iter.hasNext()) { HashMap<String, String> tags = new HashMap<String, String>(); Message next = iter.next(); ORU_R01 oru = new ORU_R01(); oru.parse(next.encode()); PatientInfo patInfo = new PatientInfo(); if (Terser.get(oru.getRESPONSE().getPATIENT().getPID(), 5, 0, 2, 1) != null) patInfo.setFirstName(Terser.get(oru.getRESPONSE().getPATIENT().getPID(), 5, 0, 2, 1).trim()); if (Terser.get(oru.getRESPONSE().getPATIENT().getPID(), 5, 0, 1, 1) != null) patInfo.setLastName(Terser.get(oru.getRESPONSE().getPATIENT().getPID(), 5, 0, 1, 1).trim()); if (Terser.get(oru.getRESPONSE().getPATIENT().getPID(), 7, 0, 1, 1) != null) patInfo.setBirthDateTime( Terser.get(oru.getRESPONSE().getPATIENT().getPID(), 7, 0, 1, 1).trim()); if (Terser.get(oru.getRESPONSE().getPATIENT().getPID(), 8, 0, 1, 1) != null) patInfo.setGender(Terser.get(oru.getRESPONSE().getPATIENT().getPID(), 8, 0, 1, 1).trim()); if (Terser.get(oru.getRESPONSE().getPATIENT().getPID(), 23, 0, 1, 1) != null) patInfo.setBirthplace(Terser.get(oru.getRESPONSE().getPATIENT().getPID(), 23, 0, 1, 1).trim()); LinkedList<String> locations = new LinkedList<String>(); LinkedList<String> variables = new LinkedList<String>(); if (idMatch.get(patInfo.getHash()) != null) { patInfo = idMatch.get(patInfo.getHash()); locations = patInfo.getLocations(); variables = patInfo.getVariables(); } if (!locations .contains(Terser.get(oru.getRESPONSE().getPATIENT().getVISIT().getPV1(), 3, 0, 1, 1))) { locations.add(Terser.get(oru.getRESPONSE().getPATIENT().getVISIT().getPV1(), 3, 0, 1, 1)); if (locations.peekLast().startsWith("ZB04")) patInfo.setPicuSubject(true); } tags.put("subjectId", patInfo.getHash()); String time = Terser.get(oru.getRESPONSE().getORDER_OBSERVATION().getOBR(), 7, 0, 1, 1); Date timepoint = fromUser.parse(time); String reformattedTime = myFormat.format(timepoint); if (patInfo.getEarliestDataPoint().equalsIgnoreCase("")) { patInfo.setEarliestDataPoint(reformattedTime); } List<ORU_R01_OBSERVATION> observations = oru.getRESPONSE().getORDER_OBSERVATION() .getOBSERVATIONAll(); for (ORU_R01_OBSERVATION observation : observations) { String seriesName = Terser.get(observation.getOBX(), 3, 0, 1, 1); if (measurementNames.get(seriesName) != null) { seriesName = measurementNames.get(seriesName); } else { seriesName = seriesName.replaceFirst("\\d", "#"); seriesName = measurementNames.get(seriesName); } StringBuffer buff = new StringBuffer(); String[] tokens = seriesName.split(" "); for (String i : tokens) { i = i.replaceAll("\\(", ""); i = i.replaceAll("\\)", ""); buff.append(StringUtils.capitalize(i)); } String measurementValue = Terser.get(observation.getOBX(), 5, 0, 1, 1); String units = Terser.get(observation.getOBX(), 6, 0, 1, 1); if (units != null) { units = units.replaceAll(":", ""); units = units.replaceAll("cm_h2o", "cmH2O"); units = units.replaceAll("\\(min/m2\\)", "MinPerMeterSquared"); units = units.replaceAll("l", "liters"); units = units.replaceAll("mliters", "milliliters"); units = units.replaceAll("g.m", "gramMeters"); units = units.replaceAll("dyn.sec.cm-5", "dyneSecondsPerQuinticCentimeter"); units = units.replaceAll("dyneSecondsPerQuinticCentimeter.m2", "dyneSecondsPerQuinticCentimeterPerMeterSquared"); units = units.replaceAll("m2", "MeterSquared"); units = units.replaceAll("min", "Min"); units = units.replaceAll("/", "Per"); units = units.replaceAll("%", "percent"); units = units.replaceAll("#", "Count"); units = units.replaceAll("celiters", "Celsius"); units = units.replaceAll("mm\\(hg\\)", "mmHg"); } else { units = "percent"; } seriesName = "vitals." + StringUtils.uncapitalize(units); seriesName += "." + StringUtils.uncapitalize(buff.toString()); seriesName = seriesName.trim(); if (!variables.contains(StringUtils.uncapitalize(buff.toString()))) variables.add(StringUtils.uncapitalize(buff.toString())); IncomingDataPoint dataPoint = new IncomingDataPoint(seriesName, timepoint.getTime(), measurementValue, tags); TimeSeriesStorer.storeTimePoint(urlString, dataPoint); } patInfo.setLocations(locations); patInfo.setVariables(variables); idMatch.put(patInfo.getHash(), patInfo); } System.out.println(" Subject Count: " + idMatch.size()); int rowNum = 0; Set<String> keys = idMatch.keySet(); TreeSet<String> sortedKeys = new TreeSet<String>(keys); for (String key : sortedKeys) { XSSFRow row = sheetOut.createRow(rowNum); XSSFRow row2 = sheetOut2.createRow(rowNum); XSSFCell cell, cell2; if (rowNum == 0) { cell = row.createCell(0); cell.setCellValue("Count"); cell = row.createCell(1); cell.setCellValue("PICU Subject?"); cell = row.createCell(2); cell.setCellValue("Hash"); cell = row.createCell(3); cell.setCellValue("First Name"); cell = row.createCell(4); cell.setCellValue("Last Name"); cell = row.createCell(5); cell.setCellValue("Birth Date/Time"); cell = row.createCell(6); cell.setCellValue("Gender"); cell = row.createCell(7); cell.setCellValue("Birthplace"); cell = row.createCell(8); cell.setCellValue("First Time Point"); cell = row.createCell(9); cell.setCellValue("Location Count"); cell = row.createCell(10); cell.setCellValue("Locations"); cell = row.createCell(11); cell.setCellValue("Variable Count"); cell = row.createCell(12); cell.setCellValue("Variables"); cell2 = row2.createCell(0); cell2.setCellValue("Count"); cell2 = row2.createCell(1); cell2.setCellValue("PICU Subject?"); cell2 = row2.createCell(2); cell2.setCellValue("Hash"); cell2 = row2.createCell(3); cell2.setCellValue("First Name"); cell2 = row2.createCell(4); cell2.setCellValue("Last Name"); cell2 = row2.createCell(5); cell2.setCellValue("Birth Date/Time"); cell2 = row2.createCell(6); cell2.setCellValue("Gender"); cell2 = row2.createCell(7); cell2.setCellValue("Birthplace"); cell2 = row2.createCell(8); cell2.setCellValue("First Time Point"); cell2 = row2.createCell(9); cell2.setCellValue("Location Count"); cell2 = row2.createCell(10); cell2.setCellValue("Locations"); cell2 = row2.createCell(11); cell2.setCellValue("Variable Count"); cell2 = row2.createCell(12); cell2.setCellValue("Variables"); } else { cell = row.createCell(0); cell.setCellValue(rowNum); cell = row.createCell(1); cell.setCellValue(idMatch.get(key).isPicuSubject()); cell = row.createCell(2); cell.setCellValue(key); cell = row.createCell(3); cell.setCellValue(idMatch.get(key).getFirstName()); cell = row.createCell(4); cell.setCellValue(idMatch.get(key).getLastName()); cell = row.createCell(5); cell.setCellValue(idMatch.get(key).getBirthDateTime()); cell = row.createCell(6); cell.setCellValue(idMatch.get(key).getGender()); cell = row.createCell(7); cell.setCellValue(idMatch.get(key).getBirthplace()); cell = row.createCell(8); cell.setCellValue(idMatch.get(key).getEarliestDataPoint()); cell = row.createCell(9); cell.setCellValue(idMatch.get(key).getLocations().size()); cell = row.createCell(10); cell.setCellValue(idMatch.get(key).getLocations().toString()); cell = row.createCell(11); cell.setCellValue(idMatch.get(key).getVariables().size()); cell = row.createCell(12); cell.setCellValue(idMatch.get(key).getVariables().toString()); if (idMatch.get(key).isPicuSubject()) { cell2 = row2.createCell(0); cell2.setCellValue(rowNum); cell2 = row2.createCell(1); cell2.setCellValue(idMatch.get(key).isPicuSubject()); cell2 = row2.createCell(2); cell2.setCellValue(key); cell2 = row2.createCell(3); cell2.setCellValue(idMatch.get(key).getFirstName()); cell2 = row2.createCell(4); cell2.setCellValue(idMatch.get(key).getLastName()); cell2 = row2.createCell(5); cell2.setCellValue(idMatch.get(key).getBirthDateTime()); cell2 = row2.createCell(6); cell2.setCellValue(idMatch.get(key).getGender()); cell2 = row2.createCell(7); cell2.setCellValue(idMatch.get(key).getBirthplace()); cell2 = row2.createCell(8); cell2.setCellValue(idMatch.get(key).getEarliestDataPoint()); cell2 = row2.createCell(9); cell2.setCellValue(idMatch.get(key).getLocations().size()); cell2 = row2.createCell(10); cell2.setCellValue(idMatch.get(key).getLocations().toString()); cell2 = row2.createCell(11); cell2.setCellValue(idMatch.get(key).getVariables().size()); cell2 = row2.createCell(12); cell2.setCellValue(idMatch.get(key).getVariables().toString()); } } rowNum++; } } if (messageFiles.size() > 0) { try { FileOutputStream out = new FileOutputStream(new File(openTSDBConfiguration.getIdMatch())); workbook.write(out); out.close(); System.out.println("Excel written successfully..."); PrintWriter writer = new PrintWriter(rootDir + "done.txt", "UTF-8"); for (String filePath : processedFiles) { writer.println(filePath); } for (String filePath : messageFiles) { writer.println(filePath); } writer.close(); System.out.println("done.txt written successfully..."); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } else { System.out.println("Nothing new to process..."); } }
From source file:org.dash.valid.report.DetectedFindingsWriter.java
License:Open Source License
/** * @param linkagesFound//from ww w . ja va2s. co m * @throws IOException * @throws SecurityException */ public synchronized void reportDetectedFindings(DetectedLinkageFindings findings) { XSSFRow row = spreadsheet.createRow(rowId++); int cellId = 0; XSSFCell cell = row.createCell(cellId++); cell.setCellValue(findings.getGenotypeList().getId()); cell = row.createCell(cellId++); cell.setCellValue(findings.getGenotypeList().getAlleleCount(Locus.HLA_A)); cell = row.createCell(cellId++); cell.setCellValue(findings.getGenotypeList().getAlleleCount(Locus.HLA_B)); cell = row.createCell(cellId++); cell.setCellValue(findings.getGenotypeList().getAlleleCount(Locus.HLA_C)); cell = row.createCell(cellId++); cell.setCellValue(findings.getGenotypeList().getAlleleCount(Locus.HLA_DRB1)); cell = row.createCell(cellId++); cell.setCellValue(findings.getGenotypeList().getAlleleCount(Locus.HLA_DRB345)); cell = row.createCell(cellId++); cell.setCellValue(findings.getGenotypeList().getAlleleCount(Locus.HLA_DQB1)); for (Linkages linkage : LinkagesLoader.getInstance().getLinkages()) { cell = row.createCell(cellId++); cell.setCellValue(findings.getLinkageCount(linkage.getLoci())); cell = row.createCell(cellId++); cell.setCellValue(findings.getMinimumDifference(linkage.getLoci()) + ""); } printWriter.write(findings.getGenotypeList().getId() + GLStringConstants.COMMA); printWriter.write(findings.getGenotypeList().getAlleleCount(Locus.HLA_A) + GLStringConstants.COMMA); printWriter.write(findings.getGenotypeList().getAlleleCount(Locus.HLA_B) + GLStringConstants.COMMA); printWriter.write(findings.getGenotypeList().getAlleleCount(Locus.HLA_C) + GLStringConstants.COMMA); printWriter.write(findings.getGenotypeList().getAlleleCount(Locus.HLA_DRB1) + GLStringConstants.COMMA); printWriter.write(findings.getGenotypeList().getAlleleCount(Locus.HLA_DRB345) + GLStringConstants.COMMA); printWriter.write(findings.getGenotypeList().getAlleleCount(Locus.HLA_DQB1) + GLStringConstants.COMMA); for (Linkages linkage : LinkagesLoader.getInstance().getLinkages()) { printWriter.write(findings.getLinkageCount(linkage.getLoci()) + GLStringConstants.COMMA); printWriter.write(findings.getMinimumDifference(linkage.getLoci()) + GLStringConstants.COMMA); } printWriter.write(GLStringConstants.NEWLINE); }
From source file:org.displaytag.render.XssfTableWriter.java
License:Artistic License
/** * Writes a table header or a footer./* w w w . jav a 2 s . c om*/ * @param value Header or footer value to be rendered. * @param row The row in which to write the header or footer. * @param style Style used to render the header or footer. */ private void writeHeaderFooter(String value, XSSFRow row, XSSFCellStyle style) { this.currentCell = row.createCell(this.colNum++); this.currentCell.setCellValue(new XSSFRichTextString(value)); this.currentCell.setCellStyle(style); }
From source file:org.easybatch.extensions.msexcel.MsExcelRecordWriter.java
License:Open Source License
@Override public void writeRecords(Batch batch) throws Exception { for (Record record : batch) { XSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1); int i = 0; int lastCellNum = ((Row) record.getPayload()).getLastCellNum(); for (int index = 0; index < lastCellNum; index++) { Cell nextCell = ((Row) record.getPayload()).getCell(index); XSSFCell cell = row.createCell(i++); setValue(cell, nextCell);/*from www . j a v a 2 s.co m*/ } } FileOutputStream fileOutputStream = new FileOutputStream(file); workbook.write(fileOutputStream); }
From source file:org.gaia.gui.reports.ExcelReportExporter.java
License:Open Source License
/** * generate file EXCEL/*w w w .ja v a2s . co m*/ * * @param table * @param template */ public static void generateExcel(SortableTreeTable table, ReportTemplate template) { FileOutputStream fileOut = null; XSSFWorkbook wb = new XSSFWorkbook(); try { String excelFilename = generateFileName(template); fileOut = new FileOutputStream(excelFilename); List<TemplateColumnItem> items = ReportBuilder.orderColumns(template.getTemplateColumnItems()); TemplateColumnItem item; int colMax = table.getColumnModel().getColumnCount(); if (items.size() < colMax) { colMax = items.size(); } XSSFSheet bomSheet = (XSSFSheet) wb.createSheet(template.getTemplateName()); XSSFRow headerRow = (XSSFRow) bomSheet.createRow(0); XSSFCellStyle headerStyle = (XSSFCellStyle) wb.createCellStyle(); Font font = wb.createFont(); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); font.setFontName("Tahoma"); headerStyle.setFont(font); headerStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); headerStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); headerStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); headerStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); headerStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); headerStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < colMax; i++) { XSSFCell cell = (XSSFCell) headerRow.createCell(i); cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(table.getColumnName(i)); cell.setCellStyle(headerStyle); } XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle(); font = wb.createFont(); font.setFontName("Tahoma"); cellStyle.setFont(font); cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); for (int j = 0; j < table.getRowCount(); j++) { XSSFRow row = (XSSFRow) bomSheet.createRow(j + 1); for (int i = 0; i < colMax; i++) { XSSFCell cell = (XSSFCell) row.createCell(i); cell.setCellStyle(cellStyle); Object value = table.getValueAt(j, i); item = (TemplateColumnItem) items.get(i); if (value != null && !value.equals(StringUtils.EMPTY_STRING)) { Class<?> clazz = Class.forName(item.getReturnType()); //used for Snapshot Export if (clazz == String.class && NumberUtils.isInteger(value.toString())) { clazz = Integer.class; } else if (clazz == String.class && NumberUtils.isNumber(value.toString())) { clazz = BigDecimal.class; } if (short.class.isAssignableFrom(clazz) || Short.class.isAssignableFrom(clazz)) { cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((Short) value).intValue()); } else if (int.class.isAssignableFrom(clazz) || Integer.class.isAssignableFrom(clazz)) { cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(Integer.parseInt(value.toString())); } else if (long.class.isAssignableFrom(clazz) || Long.class.isAssignableFrom(clazz)) { cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((Long) value).intValue()); } else if (float.class.isAssignableFrom(clazz) || Float.class.isAssignableFrom(clazz)) { cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((Float) value).doubleValue()); } else if (BigDecimal.class.isAssignableFrom(clazz)) { cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(Double.parseDouble(value.toString())); } else if (double.class.isAssignableFrom(clazz) || Double.class.isAssignableFrom(clazz)) { cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue((Double) value); } else if (Date.class.isAssignableFrom(clazz)) { cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(HSSFDateUtil.getExcelDate((java.sql.Date) value)); } else { cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(value.toString()); } } } } for (int i = 0; i < table.getColumnModel().getColumnCount(); i++) { bomSheet.autoSizeColumn(i); } SortableTreeTableModel model = (SortableTreeTableModel) table.getTreeTableModel(); AbstractSortableTreeTableNode root = (AbstractSortableTreeTableNode) model.getRoot(); groupNode(root, bomSheet); wb.write(fileOut); fileOut.flush(); fileOut.close(); nodeList.clear(); groupList.clear(); allNodeList.clear(); openExcel(excelFilename); } catch (ClassNotFoundException | IOException ex) { Exceptions.printStackTrace(ex); } finally { try { fileOut.close(); } catch (IOException ex) { logger.error(ex); } } }
From source file:org.isatools.isacreatorconfigurator.configui.io.Utils.java
License:Open Source License
public static String createTableConfigurationEXL(String outputDir, Map<MappingObject, List<Display>> tableFields) throws DataNotCompleteException, InvalidFieldOrderException, IOException { String excelFileName = "ISA-config-template.xlsx"; FileOutputStream fos = new FileOutputStream(outputDir + File.separator + excelFileName); String tableName = ""; XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet hiddenSheet = workbook.createSheet("hiddenCV"); Map<String, List<String>> nodups = new HashMap<String, List<String>>(); XSSFSheet ontologyRestriction = workbook.createSheet("Restrictions"); XSSFRow ontorow0 = ontologyRestriction.createRow((short) 0); ontorow0.createCell(0).setCellValue("Column Name"); ontorow0.createCell(1).setCellValue("Ontology"); ontorow0.createCell(2).setCellValue("Branch"); ontorow0.createCell(3).setCellValue("Version"); CreationHelper factory = workbook.getCreationHelper(); // int counting=0; // int ontocounter=0; int lastposition = 0; for (MappingObject mo : tableFields.keySet()) { tableName = mo.getAssayName().replace("\\s", ""); List<Display> elements = tableFields.get(mo); System.out.println("creating worksheet: " + tableName); //we create a table with 50 records by default for anything that is not an investigation file if (!tableName.contains("investigation")) { XSSFSheet tableSheet = workbook.createSheet(tableName); Drawing drawing = tableSheet.createDrawingPatriarch(); CellStyle style = workbook.createCellStyle(); XSSFRow rowAtIndex;/* ww w .j av a 2 s .co m*/ //we create 51 rows by default for each table for (int index = 0; index <= 50; index++) { rowAtIndex = tableSheet.createRow((short) index); } //the first row is the header we need to build from the configuration declaration XSSFRow header = tableSheet.getRow(0); //we now iterated through the element found in the xml table configuration for (int fieldIndex = 0; fieldIndex < elements.size(); fieldIndex++) { if (elements.get(fieldIndex).getFieldDetails() != null) { if (elements.get(fieldIndex).getFieldDetails().isRequired() == true) { XSSFCell cell = header.createCell(fieldIndex); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setColor(IndexedColors.RED.index); cell.setCellStyle(style); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); System.out.println("REQUIRED field number " + fieldIndex + " is: " + elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory.createRichTextString( elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); tableSheet.autoSizeColumn(fieldIndex); } else { XSSFCell cell = header.createCell(fieldIndex); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setColor(IndexedColors.BLACK.index); cell.setCellStyle(style); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory.createRichTextString( elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); tableSheet.autoSizeColumn(fieldIndex); } //checking if the field requires controled values, i.e ISA datatype is List if (elements.get(fieldIndex).getFieldDetails().getDatatype() == DataTypes.LIST) { //create a hidden spreadsheet and named range with the list of val //counting++; //incrementing the counter defining the position where to start the new namedrange in the hidden spreadsheet //obtain the name of the ISA fields and extracting key information needed to create a unique name for the Named Range to be String rangeName = elements.get(fieldIndex).getFieldDetails().getFieldName() .replace("'", "").replace(" ", "").replace("Comment[", "") .replace("ParameterValue[", "").replace("Characteristics[", "").replace("]", "") .replace("(", "").replace(")", ""); //getting all the values allowed by the List Field String[] fieldValues = elements.get(fieldIndex).getFieldDetails().getFieldList(); //System.out.println("CV : "+elements.get(fieldIndex).getFieldDetails().getFieldName()+ " values: " + Arrays.asList(fieldValues).toString()+ "size :" +fieldValues.length); //iterating through the values and creating a cell for each for (int j = 0; j < fieldValues.length; j++) { hiddenSheet.createRow(lastposition + j).createCell(0).setCellValue(fieldValues[j]); } Name namedCell = workbook.createName(); workbook.getNumberOfNames(); int k = 0; int position = 0; //this is to handle ISA Fields sharing the same name (in different assays) //namedRanges in Excel must be unique while (k < workbook.getNumberOfNames()) { //we can the total number of field to type list we have found so far. //something already exists... if (workbook.getNameAt(k).equals(rangeName)) { // namedCell.setNameName(workbook.getNameAt(k).toString()); //no need to go further, we exit here and set the parameter position to use the value position = k; k = -1; } else { k++; } } if (k > 0) { //this means this field already existed list of that type //we name the new cell after it namedCell.setNameName(rangeName + k); System.out.println("Name Name: " + namedCell.getNameName()); } else { //there is already one, so we just point back to it using the position parameter namedCell.setNameName(workbook.getNameAt(k).toString()); //workbook.getNameAt(position).toString() System.out.println("Name Name: " + namedCell.getNameName()); } int start = 0; int end = 0; start = lastposition + 1; System.out.println("start: + " + start); end = lastposition + fieldValues.length; System.out.println("end: + " + end); // String reference ="hiddenCV"+"!"+convertNumToColString(0)+start+":"+ convertNumToColString(0)+end; String reference = "hiddenCV" + "!$" + convertNumToColString(0) + "$" + start + ":$" + convertNumToColString(0) + "$" + end; namedCell.setRefersToFormula(reference); start = 0; end = 0; DataValidationHelper validationHelper = new XSSFDataValidationHelper(tableSheet); DataValidationConstraint constraint = validationHelper .createFormulaListConstraint(reference); CellRangeAddressList addressList = new CellRangeAddressList(1, 50, fieldIndex, fieldIndex); System.out.println("field index: " + fieldIndex); DataValidation dataValidation = validationHelper.createValidation(constraint, addressList); tableSheet.addValidationData(dataValidation); lastposition = lastposition + fieldValues.length; System.out.println("lastposition: + " + lastposition); System.out.println("reference: " + reference); } // //TODO: reformat date but this is pain in Excel // if (elements.get(fieldIndex).getFieldDetails().getDatatype()== DataTypes.DATE) { // //do something // } // If a default value has been specified in the ISAconfiguration, we set it in the Excel spreadsheet if (elements.get(fieldIndex).getFieldDetails().getDefaultVal() != null) { for (int i = 1; i < 51; i++) { rowAtIndex = tableSheet.getRow(i); XSSFCell cellThere = rowAtIndex.createCell(fieldIndex); cellThere.setCellValue(elements.get(fieldIndex).getFieldDetails().getDefaultVal()); } } if (elements.get(fieldIndex).getFieldDetails().getDatatype() == DataTypes.ONTOLOGY_TERM) { int count = elements.get(fieldIndex).getFieldDetails().getRecommmendedOntologySource() .values().size(); Collection<RecommendedOntology> myList = elements.get(fieldIndex).getFieldDetails() .getRecommmendedOntologySource().values(); for (RecommendedOntology recommendedOntology : myList) { System.out.println("ONTOLOGY :" + recommendedOntology.getOntology()); try { if (recommendedOntology.getOntology() != null) { ArrayList<String> ontoAttributes = new ArrayList<String>(); ontoAttributes.add(recommendedOntology.getOntology().getOntologyID()); ontoAttributes.add(recommendedOntology.getOntology().getOntologyVersion()); // ontocounter++; // XSSFRow ontoRowj = ontologyRestriction.createRow(ontocounter); // ontoRowj.createCell(0).setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); // ontoRowj.createCell(1).setCellValue(recommendedOntology.getOntology().getOntologyID()); // ontoRowj.createCell(3).setCellValue(recommendedOntology.getOntology().getOntologyVersion()); if (recommendedOntology.getBranchToSearchUnder() != null) { System.out.println("ONTOLOGY BRANCH :" + recommendedOntology.getBranchToSearchUnder()); // ontoRowj.createCell(2).setCellValue(recommendedOntology.getBranchToSearchUnder().toString()); ontoAttributes .add(recommendedOntology.getBranchToSearchUnder().toString()); } else { ontoAttributes.add(""); } nodups.put(elements.get(fieldIndex).getFieldDetails().getFieldName(), ontoAttributes); } } catch (NullPointerException npe) { System.out.println(npe); } } } } } } else { //we now create with the Investigation Sheet XSSFSheet tableSheet = workbook.createSheet(tableName); Drawing drawing = tableSheet.createDrawingPatriarch(); CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); for (int fieldIndex = 0; fieldIndex < elements.size(); fieldIndex++) { XSSFRow row = tableSheet.createRow((short) fieldIndex); if (elements.get(fieldIndex).getFieldDetails() != null) { XSSFCell cell = row.createCell(0); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory .createRichTextString(elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); cell.setCellStyle(style); tableSheet.autoSizeColumn(fieldIndex); SheetConditionalFormatting sheetCF = tableSheet.getSheetConditionalFormatting(); //condition: if the output of the FIND function is equal to 1, then, set cell to a blue font ConditionalFormattingRule rule = sheetCF .createConditionalFormattingRule("FIND(Investigation,$A$1:$A$21)>1"); //ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule(ComparisonOperator.) ; FontFormatting font1 = rule.createFontFormatting(); font1.setFontStyle(false, true); font1.setFontColorIndex(IndexedColors.BLUE.index); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A21") }; sheetCF.addConditionalFormatting(regions, rule); } } tableSheet.setSelected(true); workbook.setSheetOrder(tableName, 0); } } //writes the values of ontology resources used to restrict selection in ISA fields int compteur = 1; for (Map.Entry<String, List<String>> entry : nodups.entrySet()) { String key = entry.getKey(); // Object value = entry.getValue(); System.out.println("UNIQUE RESOURCE: " + key); XSSFRow ontoRowj = ontologyRestriction.createRow(compteur); ontoRowj.createCell(0).setCellValue(key); ontoRowj.createCell(1).setCellValue(entry.getValue().get(0)); ontoRowj.createCell(2).setCellValue(entry.getValue().get(2)); ontoRowj.createCell(3).setCellValue(entry.getValue().get(1)); compteur++; } //moving support worksheet to be the rightmost sheets in the workbook. //if the table corresponds to the study sample table, we move it to first position if (tableName.toLowerCase().contains("studysample")) { workbook.setSheetOrder(tableName, 1); } workbook.setSheetOrder("hiddenCV", tableFields.keySet().size() + 1); workbook.setSheetOrder("Restrictions", tableFields.keySet().size() + 1); workbook.write(fos); fos.close(); String message = "Files have been saved in "; if (outputDir.equals("")) { message += "this programs directory"; } else { message += outputDir; } return message; }
From source file:org.jboss.windup.reporting.spreadsheet.ScorecardReporter.java
License:Open Source License
private static void appendTotalRow(XSSFWorkbook wb, XSSFSheet sheet, int rowNum) { Font boldFont = wb.createFont(); boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD); boldFont.setColor((short) 0x0); XSSFCellStyle commentCell = wb.createCellStyle(); commentCell.setBorderTop(CellStyle.BORDER_THIN); XSSFCellStyle totalCell = wb.createCellStyle(); totalCell.setBorderTop(CellStyle.BORDER_THIN); totalCell.setFont(boldFont);//from ww w .j ava 2 s . co m XSSFCellStyle totalCellRight = wb.createCellStyle(); totalCellRight.setBorderTop(CellStyle.BORDER_THIN); totalCellRight.setAlignment(HorizontalAlignment.RIGHT); totalCellRight.setFont(boldFont); XSSFRow row = sheet.createRow(rowNum); XSSFCell t1 = row.createCell(0); t1.setCellValue("Total:"); t1.setCellStyle(totalCellRight); XSSFCell t2 = row.createCell(1); t2.setCellFormula("SUM(B1:B" + rowNum + ")*" + TEST_PADDING); t2.setCellStyle(totalCell); XSSFCell t3 = row.createCell(2); t3.setCellStyle(totalCell); XSSFCell t4 = row.createCell(3); t4.setCellValue("Total with Testing & App Migration Factors"); t4.setCellStyle(commentCell); }
From source file:org.jboss.windup.reporting.spreadsheet.ScorecardReporter.java
License:Open Source License
private static void appendTitleRow(XSSFWorkbook wb, XSSFSheet sheet, int rowNum) { XSSFCellStyle titleCell = wb.createCellStyle(); Color titleCellGrey = new Color(0xECECEC); XSSFColor color = new XSSFColor(titleCellGrey); titleCell.setFillForegroundColor(color); titleCell.setBorderBottom(CellStyle.BORDER_MEDIUM); titleCell.setFillPattern(CellStyle.SOLID_FOREGROUND); Font titleFormat = wb.createFont(); titleFormat.setBoldweight(Font.BOLDWEIGHT_BOLD); titleFormat.setColor((short) 0x0); titleCell.setFont(titleFormat);/* w w w . jav a2 s . c o m*/ XSSFRow row = sheet.createRow(rowNum); XSSFCell t1 = row.createCell(0); t1.setCellValue("Application Migration Estimate"); t1.setCellStyle(titleCell); XSSFCell t2 = row.createCell(1); t2.setCellValue("Effort (Points)"); t2.setCellStyle(titleCell); XSSFCell t3 = row.createCell(2); t3.setCellStyle(titleCell); XSSFCell t4 = row.createCell(3); t4.setCellValue("Notes"); t4.setCellStyle(titleCell); }