List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook createSheet
@Override
public XSSFSheet createSheet(String sheetname)
From source file:org.codelabor.example.poi.xssf.usermodel.XSSFWorkbookTest.java
License:Apache License
@Test public void testCreateCell() { String path = "C:/temp/workbook3.xlsx"; OutputStream outputStream = null; try {//from w w w . jav a 2s. c o m XSSFWorkbook workbook = new XSSFWorkbook(); Sheet sheet1 = workbook.createSheet("Sheet1"); CreationHelper creationHelper = workbook.getCreationHelper(); Row row = sheet1.createRow(0); Cell cell = row.createCell(0); cell.setCellValue(1); cell = row.createCell(1); cell.setCellValue(1.2); cell = row.createCell(2); cell.setCellValue(creationHelper.createRichTextString("This is a string")); cell = row.createCell(3); cell.setCellValue(true); outputStream = new FileOutputStream(path); workbook.write(outputStream); } catch (IOException e) { e.printStackTrace(); fail(); } finally { try { outputStream.close(); } catch (IOException e) { e.printStackTrace(); } } }
From source file:org.codelabor.example.poi.xssf.usermodel.XSSFWorkbookTest.java
License:Apache License
@Test public void testSetCellStyle() { String path = "C:/temp/workbook4.xlsx"; OutputStream outputStream = null; try {//from ww w . j a va 2 s . c o m XSSFWorkbook workbook = new XSSFWorkbook(); Sheet sheet1 = workbook.createSheet("Sheet1"); CreationHelper creationHelper = workbook.getCreationHelper(); CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-mm-dd hh:mm")); Row row = sheet1.createRow(0); Cell cell = row.createCell(0); cell.setCellValue(new Date()); cell = row.createCell(1); cell.setCellValue(new Date()); cell.setCellStyle(cellStyle); cell = row.createCell(2); cell.setCellValue(Calendar.getInstance()); cell.setCellStyle(cellStyle); outputStream = new FileOutputStream(path); workbook.write(outputStream); } catch (IOException e) { e.printStackTrace(); fail(); } finally { try { outputStream.close(); } catch (IOException e) { e.printStackTrace(); } } }
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);/* w ww . jav a 2s .com*/ 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.gaia.gui.reports.ExcelReportExporter.java
License:Open Source License
/** * generate file EXCEL//from w w w. j a va2 s. com * * @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;/*from w w w . ja va 2s.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.javasoft.ciclope.servlets.ExportMaterialePratica.java
private static synchronized File createExportFile(String fileName, List<Object[]> materialeRows, List<Object[]> oreLavorateRows, List<Object[]> lavoriEseguitiRows) { //Blank workbook XSSFWorkbook workbook = new XSSFWorkbook(); //Add general style for title headers to the workbook XSSFCellStyle titleStyle = createTitleFont(workbook); //Create a blank sheet XSSFSheet sheet = workbook.createSheet("Dati Pratica"); int nextRow = 0; Row r = sheet.createRow(nextRow++);// www . j a v a 2s . c o m Cell c = r.createCell(0); c.setCellStyle(titleStyle); c.setCellValue(EXPORTED_ELEMENT.MATERIALE.getTitle()); nextRow = AddElements(nextRow, titleStyle, EXPORTED_ELEMENT.MATERIALE, sheet, materialeRows); r = sheet.createRow(nextRow++); c = r.createCell(0); c.setCellStyle(titleStyle); c.setCellValue(EXPORTED_ELEMENT.LAVORI_ESEGUITI.getTitle()); nextRow = AddElements(nextRow, titleStyle, EXPORTED_ELEMENT.LAVORI_ESEGUITI, sheet, lavoriEseguitiRows); r = sheet.createRow(nextRow++); c = r.createCell(0); c.setCellStyle(titleStyle); c.setCellValue(EXPORTED_ELEMENT.ORE_LAVORATE.getTitle()); AddElements(nextRow, titleStyle, EXPORTED_ELEMENT.ORE_LAVORATE, sheet, oreLavorateRows); //adjust some known column size sheet.autoSizeColumn(1); try { //Write the workbook in file system FileOutputStream out = new FileOutputStream(new File(fileName)); workbook.write(out); out.close(); File f = new File(fileName); if (f.exists()) { return f; } } catch (Exception e) { e.printStackTrace(); } return null; }
From source file:org.jboss.windup.reporting.spreadsheet.ScorecardReporter.java
License:Open Source License
@Override public void process(ArchiveMetadata archive, File reportDirectory) { Validate.notNull(archive, "Archive is required, but null."); Validate.notNull(reportDirectory, "Report directory is required, but null."); File output = generateScorecardName(archive, reportDirectory); List<ArchiveMetadata> results = unwind(archive); FileOutputStream out = null;/*w w w . ja v a 2 s . c o m*/ try { out = new FileOutputStream(output); XSSFWorkbook workbook = new XSSFWorkbook(); // create a new sheet XSSFSheet s = workbook.createSheet("MigrationScoreCard"); s.setColumnWidth(0, 70 * 256); s.setColumnWidth(1, 20 * 256); s.setColumnWidth(2, 10 * 256); s.setColumnWidth(3, 255 * 256); appendTitleRow(workbook, s, 0); int rownum = 1; for (ArchiveMetadata result : results) { StringBuilder notes = new StringBuilder(); for (AbstractDecoration dr : result.getDecorations()) { if (dr instanceof Version) { notes.append(dr.toString()); } } Set<String> classifications = new HashSet<String>(); double estimate = 0; // calculate the nodes.. for (FileMetadata ir : result.getEntries()) { for (AbstractDecoration dr : ir.getDecorations()) { if (dr instanceof Classification) { String tempDesc = dr.getDescription(); tempDesc = StringUtils.removeStart(tempDesc, "Classification: "); classifications.add(tempDesc); } if (dr.getEffort() != null && dr.getEffort() instanceof StoryPointEffort) { estimate += ((StoryPointEffort) dr.getEffort()).getHours(); } } } if (classifications.size() > 0) { for (String classification : classifications) { if (LOG.isDebugEnabled()) { LOG.debug("Found: " + classification); } notes.append(", ").append(classification); } } String notesExtracted = StringUtils.removeStart(notes.toString(), ", "); appendNotesRow(workbook, s, rownum, result.getRelativePath(), estimate, notesExtracted); rownum++; } appendTotalRow(workbook, s, rownum++); // empty row. rownum++; appendMentoringTitleRow(workbook, s, rownum++); int start = rownum + 1; appendNotesRow(workbook, s, rownum++, "JBoss Configuration / Documentation / Mentoring", 80, ""); appendNotesRow(workbook, s, rownum++, "JBoss Server Setup for Apps", 80, ""); appendNotesRow(workbook, s, rownum++, "JBoss Operations Network Setup / Documentation / Mentoring", 120, ""); appendNotesRow(workbook, s, rownum++, "Deployment / Fail Over Plans", 80, ""); int end = rownum; appendMentoringTotalRow(workbook, s, rownum++, start, end); // write the workbook to the output stream // close our file (don't blow out our file handles workbook.write(out); } catch (IOException e) { LOG.error("Exception writing scorecard to: " + output.getAbsolutePath()); } finally { IOUtils.closeQuietly(out); } }
From source file:org.jmesa.view.excel.Excel2007View.java
License:Apache License
@Override public Object render() { XSSFWorkbook workbook = new XSSFWorkbook(); Table table = this.getTable(); String caption = table.getCaption(); if (!StringUtils.hasText(caption)) { caption = "JMesa Export"; }// w w w . ja v a2s . co m XSSFSheet sheet = workbook.createSheet(caption); Row row = table.getRow(); row.getRowRenderer(); List<Column> columns = table.getRow().getColumns(); // renderer header XSSFRow hssfRow = sheet.createRow(0); int columncount = 0; for (Column col : columns) { XSSFCell cell = hssfRow.createCell(columncount++); cell.setCellValue(new XSSFRichTextString(col.getTitle())); } // renderer body Collection<?> items = getCoreContext().getPageItems(); int rowcount = 1; for (Object item : items) { XSSFRow r = sheet.createRow(rowcount++); columncount = 0; for (Column col : columns) { XSSFCell cell = r.createCell(columncount++); Object value = col.getCellRenderer().render(item, rowcount); if (value == null) { value = ""; } if (value instanceof Number) { Double number = Double.valueOf(value.toString()); cell.setCellValue(number); } else { cell.setCellValue(new XSSFRichTextString(value.toString())); } } } return workbook; }
From source file:org.kuali.test.runner.output.PoiHelper.java
License:Educational Community License
/** * // www. j ava2 s . c o m * @param fileName * @param inputFiles * @param errorRuns * @param deleteExistingFiles * @return */ public File mergeWorkbookFiles(String fileName, List<File> inputFiles, boolean[] errorRuns, boolean deleteExistingFiles) { File retval = new File(fileName); XSSFWorkbook wbmerged = new XSSFWorkbook(); createPoiCellStyles(wbmerged); int indx = 0; for (File f : inputFiles) { InputStream fs = null; try { fs = new FileInputStream(f); String nm = ""; if (errorRuns[indx]) { nm = "run(" + (++indx) + ") - errors"; } else { nm = "run(" + (++indx) + ")"; } XSSFSheet newSheet = wbmerged.createSheet(nm); copySheets(newSheet, new XSSFWorkbook(fs).getSheetAt(0)); } catch (Exception ex) { LOG.error(ex.toString(), ex); } finally { if (fs != null) { try { fs.close(); } catch (Exception ex) { } ; } } } writeFile(retval, wbmerged); if (deleteExistingFiles) { for (File f : inputFiles) { try { FileUtils.forceDelete(f); } catch (IOException ex) { } } } return retval; }
From source file:org.nekorp.workflow.desktop.servicio.reporte.cliente.GeneradorReporteImp.java
License:Apache License
@Override public void generaReporte(ParametrosReporte param) { FileOutputStream fileOut = null; ReporteCliente datos = datosReporteClienteFactory.getData(); try {/*www . j a va 2 s . c o m*/ GeneradorReporteImp.LOGGER.info("file:" + param.getDestination()); //Workbook wb = WorkbookFactory.create(template); XSSFWorkbook wb = new XSSFWorkbook(); int maxColumn = 0; //CreationHelper createHelper = wb.getCreationHelper(); //se crea la primera hoja Sheet sheet = wb.createSheet("Hoja1"); BordeSeccion borde = new BordeSeccion(); ContextoSeccion contexto = new ContextoSeccion(); contexto.setWb(wb); contexto.setSheet(sheet); //encabezado borde.setLeftColumn(1); borde.setUpperRow(1); BordeSeccion bordeEncabezado = encabezadoReporteCliente.generar(borde, contexto, datos); if (bordeEncabezado.getRightColumn() > maxColumn) { maxColumn = bordeEncabezado.getRightColumn(); } //datos auto borde.setLeftColumn(1); borde.setUpperRow(bordeEncabezado.getLowerRow() + 2); BordeSeccion bordeAuto = datosAutoReporteCliente.generar(borde, contexto, datos); if (bordeAuto.getRightColumn() > maxColumn) { maxColumn = bordeAuto.getRightColumn(); } //mecanica borde.setLeftColumn(1); borde.setUpperRow(bordeAuto.getLowerRow() + 2); BordeSeccion bordeMecanica = costoMecanicaReporteCliente.generar(borde, contexto, datos); if (bordeMecanica.getRightColumn() > maxColumn) { maxColumn = bordeMecanica.getRightColumn(); } //hojalateria borde.setLeftColumn(bordeMecanica.getRightColumn() + 1); BordeSeccion bordeHojalateria = costoHojalateriaReporteCliente.generar(borde, contexto, datos); if (bordeHojalateria.getRightColumn() > maxColumn) { maxColumn = bordeHojalateria.getRightColumn(); } //total servicio if (bordeMecanica.getLowerRow() < bordeHojalateria.getLowerRow()) { borde.setUpperRow(bordeHojalateria.getLowerRow() + 2); } else { borde.setUpperRow(bordeMecanica.getLowerRow() + 2); } borde.setLeftColumn(1); BordeSeccion bordeTotalServicio = totalServicioReporteCliente.generar(borde, contexto, datos); if (bordeTotalServicio.getRightColumn() > maxColumn) { maxColumn = bordeTotalServicio.getRightColumn(); } //bitacora borde.setLeftColumn(1); borde.setUpperRow(bordeTotalServicio.getLowerRow() + 3); BordeSeccion bordeBitacora = bitacoraReporteCliente.generar(borde, contexto, datos); if (bordeBitacora.getRightColumn() > maxColumn) { maxColumn = bordeBitacora.getRightColumn(); } for (int i = 1; i <= maxColumn; i++) { sheet.autoSizeColumn(i); } fileOut = new FileOutputStream(param.getDestination()); wb.write(fileOut); fileOut.close(); } catch (Exception ex) { GeneradorReporteImp.LOGGER.error("error al generar reporte", ex); } finally { try { fileOut.close(); } catch (IOException | NullPointerException ex) { GeneradorReporteImp.LOGGER.error("error al cerrar archivo de reporte", ex); } } }