List of usage examples for org.apache.poi.ss.usermodel Sheet autoSizeColumn
void autoSizeColumn(int column);
From source file:com.asakusafw.testdata.generator.excel.SheetEditor.java
License:Apache License
private void adjustColumnWidth(Sheet sheet, int lastColumn) { assert sheet != null; for (int i = 0; i <= lastColumn; i++) { sheet.autoSizeColumn(i); int width = sheet.getColumnWidth(i); if (width < MINIMUM_COLUMN_WIDTH) { sheet.setColumnWidth(i, MINIMUM_COLUMN_WIDTH); }//from w w w . j av a 2 s . com } }
From source file:com.b2international.snowowl.datastore.server.importer.AbstractTerminologyExcelExporter.java
License:Apache License
/** * Creates the index sheet based on the given sheet names. * /* ww w . j av a 2 s.c o m*/ * @param sheetNames */ protected void createIndexSheet(final Collection<T> components) { final Sheet indexSheet = workbook.createSheet("INDEX"); final List<T> filteredComponents = Lists.newArrayList(Iterables.filter(components, new Predicate<T>() { @Override public boolean apply(T input) { return isToExport(getComponentId(input)); } })); final List<String> sheetNames = extractSheetNamesFromTerminologyComponents(filteredComponents); final Row firstRow = indexSheet.createRow(0); createCell(firstRow, getIndexSheetHeaderName(), BOLD_STYLE, 0); for (int i = 0; i < sheetNames.size(); i++) { final String sheetName = getFinalSheetName(i + 1, sheetNames.get(i)); final Hyperlink hyperlink = workbook.getCreationHelper().createHyperlink(XSSFHyperlink.LINK_DOCUMENT); hyperlink.setLabel(sheetName); hyperlink.setAddress(String.format("'%s'!A1", sheetName)); final Row row = indexSheet.createRow(i + 1); final Cell cell = row.createCell(0); cell.setCellValue(sheetName); cell.setCellStyle(hyperlinkStyle); cell.setHyperlink(hyperlink); } indexSheet.autoSizeColumn(0); }
From source file:com.b2international.snowowl.snomed.exporter.server.dsv.SnomedSimpleTypeRefSetExcelExporter.java
License:Apache License
@Override protected void exportTerminologyComponents(final OMMonitor monitor) { Async async = null;/*from w w w .jav a2 s . c o m*/ final OMMonitor componentsMonitor = monitor.fork(15); try { final String refSetLabel = formatSheetName( new SnomedConceptNameProvider(ApplicationContext.getServiceForClass(IEventBus.class), ApplicationContext.getServiceForClass(LanguageSetting.class)) .getComponentLabel(getBranchPath(), refSet.getId())); final Sheet sheet = workbook.createSheet(refSetLabel); async = monitor.forkAsync(70); final Collection<SnomedCoreComponent> components = getComponents(refSet); async.stop(); componentsMonitor.begin(components.size()); switch (refSet.getReferencedComponentType()) { case SnomedTerminologyComponentConstants.DESCRIPTION: cretaDescriptionHeader(sheet); exportDescriptions(components, sheet, componentsMonitor); break; case SnomedTerminologyComponentConstants.RELATIONSHIP: createRelationshipHeader(sheet); exportRelationships(components, sheet, componentsMonitor); break; default: throw new IllegalStateException(MessageFormat.format("Invalid referenced component type: ", refSet.getReferencedComponentType())); } for (int i = 0; i <= getColumnNumber(); i++) { sheet.autoSizeColumn(i); } } finally { if (null != async) { async.stop(); } if (null != componentsMonitor) { componentsMonitor.done(); } } }
From source file:com.ben12.reta.util.RETAAnalysis.java
License:Open Source License
public void writeExcel(Window parent) throws IOException, InvalidFormatException { logger.info("Start write excel output"); Path outputFile = Paths.get(output); if (!outputFile.isAbsolute()) { Path root = config.getAbsoluteFile().getParentFile().toPath(); outputFile = root.resolve(outputFile); }//from w w w . j av a2 s .c om // test using template InputStream is = getClass().getResourceAsStream("/com/ben12/reta/resources/template/template.xlsx"); ExcelTransformer transformer = new ExcelTransformer(); List<String> sheetNames = new ArrayList<>(); List<String> sheetTemplateNames = new ArrayList<>(); for (InputRequirementSource requirementSource : requirementSources.values()) { sheetTemplateNames.add("DOCUMENT"); sheetTemplateNames.add("COVERAGE"); sheetNames.add(requirementSource.getName()); sheetNames.add(requirementSource.getName() + " coverage"); } List<Map<String, Object>> sheetValues = new ArrayList<>(); for (InputRequirementSource source : requirementSources.values()) { Map<String, Object> values = new HashMap<>(); values.put("source", source); values.put("null", null); values.put("line", "\n"); Set<String> attributes = new LinkedHashSet<>(); attributes.add(Requirement.ATTRIBUTE_ID); if (source.getAttributesGroup().containsKey(Requirement.ATTRIBUTE_VERSION)) { attributes.add(Requirement.ATTRIBUTE_VERSION); } attributes.addAll(source.getAttributesGroup().keySet()); attributes.remove(Requirement.ATTRIBUTE_TEXT); values.put("attributes", attributes); Set<String> refAttributes = new LinkedHashSet<>(); refAttributes.add(Requirement.ATTRIBUTE_ID); if (source.getRefAttributesGroup().containsKey(Requirement.ATTRIBUTE_VERSION)) { refAttributes.add(Requirement.ATTRIBUTE_VERSION); } refAttributes.addAll(source.getRefAttributesGroup().keySet()); refAttributes.remove(Requirement.ATTRIBUTE_TEXT); values.put("refAttributes", refAttributes); sheetValues.add(values); sheetValues.add(values); } Workbook wb = transformer.transform(is, sheetTemplateNames, sheetNames, sheetValues); int sheetCount = wb.getNumberOfSheets(); for (int i = 0; i < sheetCount; i++) { Sheet sheet = wb.getSheetAt(i); int columns = 0; for (int j = 0; j <= sheet.getLastRowNum(); j++) { Row row = sheet.getRow(j); if (row != null) { row.setHeight((short) -1); columns = Math.max(columns, row.getLastCellNum() + 1); } } for (int j = 0; j < columns; j++) { sheet.autoSizeColumn(j); } } try (FileOutputStream fos = new FileOutputStream(outputFile.toFile())) { wb.write(fos); } catch (FileNotFoundException e) { int confirm = MessageDialog.showQuestionMessage(null, "Excel output file must be closed."); if (confirm == MessageDialog.OK_OPTION) { try (FileOutputStream fos = new FileOutputStream(outputFile.toFile())) { wb.write(fos); } catch (IOException e2) { throw e2; } } else { throw e; } } logger.info("End write excel output"); }
From source file:com.bonsoft.test.Report.java
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed ArrayList<ReportLine> lines = new ArrayList<>(); try {/* ww w . ja va 2 s. com*/ Orgs org = (Orgs) jComboBox1.getSelectedItem(); Stores store = (Stores) jComboBox2.getSelectedItem(); Calendar period = Calendar.getInstance(); try { period.setTime((new SimpleDateFormat("dd.MM.yyyy")).parse(jFormattedTextField1.getText())); } catch (ParseException ex) { Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex); } Connection connection = null; Statement statement = null; ResultSet result = null; try { connection = DriverManager.getConnection("jdbc:postgresql://88.201.248.46:5432/personal", "vitaly", "m127rqu4"); } catch (SQLException ex) { Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex); } try { statement = connection.createStatement(); } catch (SQLException ex) { Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex); } String sql = "select operations.descr, date_part('hour', mhr_period) as hr, sum(mhr_qty) as cnt from mhr, operations " + "where mhr.operation_id = operations.id and mhR_qty > 0 and mhr.org_id = " + org.getId() + " and store_id = " + store.getId() + " and " + "date_part('day', mhr_period) = " + period.get(Calendar.DAY_OF_MONTH) + " and date_part('month', mhr_period) = " + (period.get(Calendar.MONTH) + 1) + " and date_part('year', mhr_period) = " + period.get(Calendar.YEAR) + " and mhr.operation_id in (select id from operations) group by operations.descr, hr having count(mhr_qty) > 0 order by " + "operations.descr, hr"; try { result = statement.executeQuery(sql); } catch (SQLException ex) { Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex); } System.out.println("?: " + org.getName() + ", id = " + org.getId()); System.out.println(": " + store.getDescr() + ", id = " + store.getId()); System.out.println(": " + period.get(Calendar.DAY_OF_MONTH)); System.out.println("??: " + (period.get(Calendar.MONTH) + 1)); System.out.println(": " + period.get(Calendar.YEAR)); String oldDescr = ""; ReportLine line = null; while (result.next()) { String descr = result.getString("descr"); double hr = result.getDouble("hr"); double cnt = result.getDouble("cnt"); cnt = Math.ceil(cnt); if (oldDescr.equals(descr)) { line.add(hr, cnt); } else { oldDescr = descr; line = new ReportLine(); line.setName(descr); line.add(hr, cnt); lines.add(line); } } result.close(); statement.close(); connection.close(); } catch (SQLException ex) { Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex); } Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet(""); Row title = sheet.createRow(0); Cell cell = title.createCell(0); cell.setCellValue("?"); Row row = null; int x = 0, y = 0; for (ReportLine line : lines) { row = sheet.createRow(++y); cell = row.createCell(0); cell.setCellValue(line.getName()); for (int i = 0; i < line.getLen(); i++) { x = line.getHours().get(i) - ReportLine.getMinH() + 1; cell = title.createCell(x); cell.setCellValue(line.getHours().get(i) + ":00"); cell = row.createCell(x); cell.setCellValue(line.getCounts().get(i)); } } sheet.autoSizeColumn(0); try (FileOutputStream fileExcel = new FileOutputStream("Report.xls")) { workbook.write(fileExcel); } catch (IOException ex) { Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.centurylink.mdw.common.service.JsonExport.java
License:Apache License
private void setColumnLabels(Sheet sheet) { Row headerRow = sheet.createRow(0);// w w w. ja v a 2 s . c o m for (int i = 0; i < names.size(); i++) { Cell cell = headerRow.createCell(i); String label = names.get(i); if (labels != null && labels.containsKey(label)) label = labels.get(label); cell.setCellValue(label); if (!"message".equals(label)) sheet.autoSizeColumn(i); } }
From source file:com.crm.webapp.util.ExcelCustomExporter.java
License:Apache License
@Override public void export(ActionEvent event, String tableId, FacesContext context, String filename, String tableTitle, boolean pageOnly, boolean selectionOnly, String encodingType, MethodExpression preProcessor, MethodExpression postProcessor, boolean subTable) throws IOException { wb = new XSSFWorkbook(); String safeName = WorkbookUtil.createSafeSheetName(filename); Sheet sheet = wb.createSheet(safeName); cellStyle = wb.createCellStyle();/*w w w. j ava 2 s . c om*/ facetStyle = wb.createCellStyle(); titleStyle = wb.createCellStyle(); createCustomFonts(); int maxColumns = 0; StringTokenizer st = new StringTokenizer(tableId, ","); while (st.hasMoreElements()) { String tableName = (String) st.nextElement(); UIComponent component = SearchExpressionFacade.resolveComponent(context, event.getComponent(), tableName); if (component == null) { throw new FacesException("Cannot find component \"" + tableName + "\" in view."); } if (!(component instanceof DataTable || component instanceof DataList)) { throw new FacesException("Unsupported datasource target:\"" + component.getClass().getName() + "\", exporter must target a PrimeFaces DataTable/DataList."); } DataList list = null; DataTable table = null; int cols = 0; if (preProcessor != null) { preProcessor.invoke(context.getELContext(), new Object[] { wb }); } if (tableTitle != null && !tableTitle.isEmpty() && !tableId.contains("" + ",")) { Row titleRow = sheet.createRow(sheet.getLastRowNum()); int cellIndex = titleRow.getLastCellNum() == -1 ? 0 : titleRow.getLastCellNum(); Cell cell = titleRow.createCell(cellIndex); cell.setCellValue(new XSSFRichTextString(tableTitle)); Font titleFont = wb.createFont(); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); titleStyle.setFont(titleFont); cell.setCellStyle(titleStyle); sheet.createRow(sheet.getLastRowNum() + 3); } if (component instanceof DataList) { list = (DataList) component; if (list.getHeader() != null) { tableFacet(context, sheet, list, "header"); } if (pageOnly) { exportPageOnly(context, list, sheet); } else { exportAll(context, list, sheet); } cols = list.getRowCount(); } else { table = (DataTable) component; int columnsCount = getColumnsCount(table); if (table.getHeader() != null && !subTable) { tableFacet(context, sheet, table, columnsCount, "header"); } if (!subTable) { tableColumnGroup(sheet, table, "header"); } addColumnFacets(table, sheet, ColumnType.HEADER); if (pageOnly) { exportPageOnly(context, table, sheet); } else if (selectionOnly) { exportSelectionOnly(context, table, sheet); } else { exportAll(context, table, sheet, subTable); } if (table.hasFooterColumn() && !subTable) { addColumnFacets(table, sheet, ColumnType.FOOTER); } if (!subTable) { tableColumnGroup(sheet, table, "footer"); } table.setRowIndex(-1); if (postProcessor != null) { postProcessor.invoke(context.getELContext(), new Object[] { wb }); } cols = table.getColumnsCount(); if (maxColumns < cols) { maxColumns = cols; } } sheet.createRow(sheet.getLastRowNum() + Integer.parseInt(datasetPadding)); } if (!subTable) for (int i = 0; i < maxColumns; i++) { sheet.autoSizeColumn((short) i); } PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE); sheet.setPrintGridlines(true); writeExcelToResponse(context.getExternalContext(), wb, filename); }
From source file:com.efficio.fieldbook.service.ExportExcelServiceImpl.java
License:Open Source License
public FileOutputStream exportFieldMapToExcel(String fileName, UserFieldmap userFieldMap) throws FieldbookException { Locale locale = LocaleContextHolder.getLocale(); boolean isTrial = userFieldMap.isTrial(); // Summary of Trial/Nursery, Field and Planting Details String summaryOfFieldbookFieldPlantingDetailsLabel = messageSource .getMessage("fieldmap.header.summary.for.trial", null, locale); //SUMMARY OF TRIAL, FIELD AND PLANTING DETAILS String selectedFieldbookLabel = messageSource.getMessage("fieldmap.trial.selected.trial", null, locale); //Selected Trial: if (!isTrial) { summaryOfFieldbookFieldPlantingDetailsLabel = messageSource .getMessage("fieldmap.header.summary.for.nursery", null, locale); //SUMMARY OF NURSERY, FIELD AND PLANTING DETAILS selectedFieldbookLabel = messageSource.getMessage("fieldmap.nursery.selected.nursery", null, locale); //Selected Nursery: }// w w w.ja v a 2 s.c o m String selectedFieldbookValue = userFieldMap.getBlockName(); String orderHeader = messageSource.getMessage("fieldmap.trial.order", null, locale); String studyHeader = messageSource.getMessage((isTrial ? "fieldmap.trial" : "fieldmap.nursery"), null, locale); String instanceHeader = messageSource.getMessage("fieldmap.trial.instance", null, locale); String entriesCountHeader = messageSource.getMessage("fieldmap.trial.entry.count", null, locale); String repsCountHeader = messageSource.getMessage("fieldmap.trial.reps.count", null, locale); String plotsNeededHeader = messageSource.getMessage("fieldmap.trial.plots.needed", null, locale); String totalPlotsHeader = messageSource.getMessage("fieldmap.trial.total.number.of.plots", null, locale); String datasetNameHeader = messageSource.getMessage("fieldmap.nursery.dataset", null, locale); // Field And Block Details String fieldAndBlockDetailsLabel = messageSource.getMessage("fieldmap.trial.field.and.block.details", null, locale); //FIELD AND BLOCK DETAILS String fieldLocationLabel = messageSource.getMessage("fieldmap.label.field.location", null, locale); //Field Location String fieldLocationValue = userFieldMap.getLocationName(); String fieldNameLabel = messageSource.getMessage("fieldmap.label.field.name", null, locale); //Field Name String fieldNameValue = userFieldMap.getFieldName(); String blockNameLabel = messageSource.getMessage("fieldmap.label.block.name", null, locale); //Block Name String blockNameValue = userFieldMap.getBlockName(); // Row, Range & Plot Details String rowRangePlotDetailsLabel = messageSource.getMessage("fieldmap.trial.row.and.range.and.plot.details", null, locale); //ROW, RANGE AND PLOT DETAILS String blockCapacityLabel = messageSource.getMessage("fieldmap.label.block.capacity", null, locale); //Block Capacity String blockCapacityValue = userFieldMap.getBlockCapacityString(messageSource); //e.g. "10 Columns, 10 Ranges" String rowsPerPlotLabel = messageSource.getMessage("fieldmap.label.rows.per.plot", null, locale); //Rows per Plot int rowsPerPlotValue = userFieldMap.getNumberOfRowsPerPlot(); String columnsLabel = messageSource.getMessage("fieldmap.label.columns", null, locale); //Columns Integer columnsValue = userFieldMap.getNumberOfColumnsInBlock(); // 10 String machineCapacityLabel = messageSource.getMessage("fieldmap.label.row.capacity.machine", null, locale); //machine row capacity Integer machineCapacityValue = userFieldMap.getMachineRowCapacity(); //Planting Details String plantingDetailsLabel = messageSource.getMessage("fieldmap.header.planting.details", null, locale); //PLANTING DETAILS String startingCoordinatesLabel = messageSource.getMessage("fieldmap.label.starting.coordinates", null, locale); //Starting Coordinates String startingCoordinatesValue = userFieldMap.getStartingCoordinateString(messageSource); // Column 1, Range 1 String plantingOrderLabel = messageSource.getMessage("fieldmap.label.planting.order", null, locale); //Planting Order String plantingOrderValue = userFieldMap.getPlantingOrderString(messageSource); //"Row/Column" or "Serpentine" // FieldMap String fieldMapLabel = messageSource.getMessage("fieldmap.header.fieldmap", null, locale); //FIELD MAP String rowsLabel = messageSource.getMessage("fieldmap.label.rows", null, locale); //Rows String columnLabel = messageSource.getMessage("fieldmap.label.capitalized.column", null, locale); //Column String rangeLabel = messageSource.getMessage("fieldmap.label.capitalized.range", null, locale); //Range try { //Create workbook HSSFWorkbook workbook = new HSSFWorkbook(); String summaryLabelSheet = messageSource.getMessage("fieldmap.header.excel.summary", null, locale); Sheet summarySheet = workbook.createSheet(summaryLabelSheet); Sheet fieldMapSheet = workbook.createSheet(fieldMapLabel); CellStyle labelStyle = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); labelStyle.setFont(font); CellStyle wrapStyle = workbook.createCellStyle(); wrapStyle.setWrapText(true); wrapStyle.setAlignment(CellStyle.ALIGN_CENTER); CellStyle mainHeaderStyle = workbook.createCellStyle(); HSSFPalette palette = workbook.getCustomPalette(); // get the color which most closely matches the color you want to use HSSFColor myColor = palette.findSimilarColor(179, 165, 165); // get the palette index of that color short palIndex = myColor.getIndex(); // code to get the style for the cell goes here mainHeaderStyle.setFillForegroundColor(palIndex); mainHeaderStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); CellStyle mainSubHeaderStyle = workbook.createCellStyle(); HSSFPalette paletteSubHeader = workbook.getCustomPalette(); // get the color which most closely matches the color you want to use HSSFColor myColorSubHeader = paletteSubHeader.findSimilarColor(190, 190, 186); // get the palette index of that color short palIndexSubHeader = myColorSubHeader.getIndex(); // code to get the style for the cell goes here mainSubHeaderStyle.setFillForegroundColor(palIndexSubHeader); mainSubHeaderStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); mainSubHeaderStyle.setAlignment(CellStyle.ALIGN_CENTER); int rowIndex = 0; int columnIndex = 0; // Create Header Information // Row 1: SUMMARY OF TRIAL, FIELD AND PLANTING DETAILS CellStyle headerLabelStyle = workbook.createCellStyle(); font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerLabelStyle.setFont(font); headerLabelStyle.setAlignment(CellStyle.ALIGN_CENTER); Row row = summarySheet.createRow(rowIndex++); Cell summaryCell = row.createCell(columnIndex); summaryCell.setCellValue(summaryOfFieldbookFieldPlantingDetailsLabel); summaryCell.setCellStyle(headerLabelStyle); summarySheet.addMergedRegion(new CellRangeAddress(rowIndex - 1, //first row (0-based) rowIndex - 1, //last row (0-based) columnIndex, //first column (0-based) columnIndex + 5 //last column (0-based) )); // Row 2: Space row = summarySheet.createRow(rowIndex++); // Row 3: Fieldbook Name, Entries, Reps, Plots row = summarySheet.createRow(rowIndex++); // Selected Trial : [Fieldbook Name] TABLE SECTION Cell labelCell = row.createCell(columnIndex++); labelCell.setCellValue(selectedFieldbookLabel); row = summarySheet.createRow(rowIndex++); columnIndex = 0; Cell headerCell = row.createCell(columnIndex++); headerCell.setCellValue(orderHeader); headerCell.setCellStyle(labelStyle); headerCell = row.createCell(columnIndex++); headerCell.setCellValue(studyHeader); headerCell.setCellStyle(labelStyle); if (isTrial) { headerCell = row.createCell(columnIndex++); headerCell.setCellValue(instanceHeader); headerCell.setCellStyle(labelStyle); headerCell = row.createCell(columnIndex++); headerCell.setCellValue(entriesCountHeader); headerCell.setCellStyle(labelStyle); headerCell = row.createCell(columnIndex++); headerCell.setCellValue(repsCountHeader); headerCell.setCellStyle(labelStyle); } else { headerCell = row.createCell(columnIndex++); headerCell.setCellValue(datasetNameHeader); headerCell.setCellStyle(labelStyle); } headerCell = row.createCell(columnIndex++); headerCell.setCellValue(plotsNeededHeader); headerCell.setCellStyle(labelStyle); for (SelectedFieldmapRow rec : userFieldMap.getSelectedFieldmapList().getRows()) { row = summarySheet.createRow(rowIndex++); columnIndex = 0; row.createCell(columnIndex++).setCellValue(rec.getOrder()); row.createCell(columnIndex++).setCellValue(rec.getStudyName()); if (isTrial) { row.createCell(columnIndex++).setCellValue(rec.getTrialInstanceNo()); row.createCell(columnIndex++).setCellValue(String.valueOf(rec.getEntryCount())); row.createCell(columnIndex++).setCellValue(String.valueOf(rec.getRepCount())); row.createCell(columnIndex++).setCellValue(String.valueOf(rec.getPlotCount())); } else { row.createCell(columnIndex++).setCellValue(rec.getDatasetName()); row.createCell(columnIndex++).setCellValue(String.valueOf(rec.getEntryCount())); } } row = summarySheet.createRow(rowIndex++); columnIndex = 0; headerCell = row.createCell(columnIndex++); headerCell.setCellValue(totalPlotsHeader); headerCell.setCellStyle(labelStyle); row.createCell(columnIndex++) .setCellValue(String.valueOf(userFieldMap.getSelectedFieldmapList().getTotalNumberOfPlots())); // Row 4: Space row = summarySheet.createRow(rowIndex++); // Row 5: Header - Details Heading row = summarySheet.createRow(rowIndex++); columnIndex = 0; labelCell = row.createCell(columnIndex++); labelCell.setCellValue(fieldAndBlockDetailsLabel); labelCell.setCellStyle(headerLabelStyle); summarySheet.addMergedRegion(new CellRangeAddress(rowIndex - 1, //first row (0-based) rowIndex - 1, //last row (0-based) columnIndex - 1, //first column (0-based) columnIndex //last column (0-based) )); row.createCell(columnIndex++); labelCell = row.createCell(columnIndex++); labelCell.setCellValue(rowRangePlotDetailsLabel); labelCell.setCellStyle(headerLabelStyle); summarySheet.addMergedRegion(new CellRangeAddress(rowIndex - 1, //first row (0-based) rowIndex - 1, //last row (0-based) columnIndex - 1, //first column (0-based) columnIndex //last column (0-based) )); row.createCell(columnIndex++); labelCell = row.createCell(columnIndex++); labelCell.setCellValue(plantingDetailsLabel); labelCell.setCellStyle(headerLabelStyle); summarySheet.addMergedRegion(new CellRangeAddress(rowIndex - 1, //first row (0-based) rowIndex - 1, //last row (0-based) columnIndex - 1, //first column (0-based) columnIndex //last column (0-based) )); row.createCell(columnIndex++); //Row 6: Field Location, Block Capacity, Starting Coordinates row = summarySheet.createRow(rowIndex++); columnIndex = 0; labelCell = row.createCell(columnIndex++); labelCell.setCellValue(fieldLocationLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(fieldLocationValue); labelCell = row.createCell(columnIndex++); labelCell.setCellValue(blockCapacityLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(blockCapacityValue); labelCell = row.createCell(columnIndex++); labelCell.setCellValue(startingCoordinatesLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(startingCoordinatesValue); // Row 7: Field Name, Rows Per Plot, Planting Order row = summarySheet.createRow(rowIndex++); columnIndex = 0; labelCell = row.createCell(columnIndex++); labelCell.setCellValue(fieldNameLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(fieldNameValue); labelCell = row.createCell(columnIndex++); labelCell.setCellValue(rowsPerPlotLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(String.valueOf(rowsPerPlotValue)); labelCell = row.createCell(columnIndex++); labelCell.setCellValue(plantingOrderLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(plantingOrderValue); // Row 8: Block Name, Columns row = summarySheet.createRow(rowIndex++); columnIndex = 0; labelCell = row.createCell(columnIndex++); labelCell.setCellValue(blockNameLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(blockNameValue); labelCell = row.createCell(columnIndex++); labelCell.setCellValue(columnsLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(String.valueOf(columnsValue)); labelCell = row.createCell(columnIndex++); labelCell.setCellValue(machineCapacityLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(String.valueOf(machineCapacityValue)); // Row 9: Space row = summarySheet.createRow(rowIndex++); for (int columnsResize = 0; columnsResize < columnIndex; columnsResize++) { summarySheet.autoSizeColumn(columnsResize); } // Get FieldMap data //we reset the row index rowIndex = 0; // Row 10: FIELD MAP row = fieldMapSheet.createRow(rowIndex++); columnIndex = 0; labelCell = row.createCell(columnIndex++); labelCell.setCellValue(fieldMapLabel); labelCell.setCellStyle(labelStyle); // Row 11: Space row = fieldMapSheet.createRow(rowIndex++); Plot[][] plots = userFieldMap.getFieldmap(); int range = userFieldMap.getNumberOfRangesInBlock(); int col = userFieldMap.getNumberOfColumnsInBlock(); int rowsPerPlot = userFieldMap.getNumberOfRowsPerPlot(); int machineRowCapacity = userFieldMap.getMachineRowCapacity(); int rows = userFieldMap.getNumberOfRowsInBlock(); boolean isSerpentine = userFieldMap.getPlantingOrder() == 2; for (int j = range - 1; j >= 0; j--) { if (j == range - 1) { // TOP TABLE LABELS // Row 12: Rows Header rowIndex = printRowHeader(fieldMapSheet, userFieldMap.getNumberOfRowsInBlock(), rowIndex, rowsLabel, mainHeaderStyle, mainSubHeaderStyle); // Row 13: UP, DOWN Direction rowIndex = printDirectionHeader(fieldMapSheet, plots, j, rows, rowIndex, machineRowCapacity, mainHeaderStyle, mainSubHeaderStyle, isSerpentine); // Row 14: Column labels rowIndex = printColumnHeader(fieldMapSheet, col, rowIndex, columnLabel, rowsPerPlot, mainHeaderStyle, mainSubHeaderStyle); } // Rows 15 onwards: Ranges and Row Data row = fieldMapSheet.createRow(rowIndex); row.setHeightInPoints(45); columnIndex = 0; int rangeValue = j + 1; Cell cellRange = row.createCell(columnIndex++); cellRange.setCellValue(rangeLabel + " " + rangeValue); cellRange.setCellStyle(mainSubHeaderStyle); for (int i = 0; i < col; i++) { String displayString = plots[i][j].getDisplayString().replace("<br/>", "\n"); if (plots[i][j].isPlotDeleted()) { displayString = " X "; } Cell dataCell = row.createCell(columnIndex++); //dataCell.setCellValue(displayString); dataCell.setCellValue(new HSSFRichTextString(displayString)); dataCell.setCellStyle(wrapStyle); //row.createCell(columnIndex).setCellValue(""); for (int k = 0; k < rowsPerPlot - 1; k++) { row.createCell(columnIndex++).setCellValue(""); } fieldMapSheet.addMergedRegion(new CellRangeAddress(rowIndex, //first row (0-based) rowIndex, //last row (0-based) columnIndex - rowsPerPlot, //first column (0-based) columnIndex - 1 //last column (0-based) )); //columnIndex++; } rowIndex++; if (j == 0) { // BOTTOM TABLE LABELS rowIndex = printColumnHeader(fieldMapSheet, col, rowIndex, columnLabel, rowsPerPlot, mainHeaderStyle, mainSubHeaderStyle); rowIndex = printDirectionHeader(fieldMapSheet, plots, j, rows, rowIndex, machineRowCapacity, mainHeaderStyle, mainSubHeaderStyle, isSerpentine); rowIndex = printRowHeader(fieldMapSheet, userFieldMap.getNumberOfRowsInBlock(), rowIndex, rowsLabel, mainHeaderStyle, mainSubHeaderStyle); } } //Write the excel file FileOutputStream fileOutputStream = new FileOutputStream(fileName); workbook.write(fileOutputStream); fileOutputStream.close(); return fileOutputStream; } catch (FileNotFoundException e) { LOG.error(e.getMessage(), e); throw new FieldbookException("Error writing to file: " + fileName, e); } catch (IOException e) { LOG.error(e.getMessage(), e); throw new FieldbookException("Error writing to file: " + fileName, e); } }
From source file:com.efficio.fieldbook.service.LabelPrintingServiceImpl.java
License:Open Source License
@Override public String generateXlSLabels(List<StudyTrialInstanceInfo> trialInstances, UserLabelPrinting userLabelPrinting, ByteArrayOutputStream baos) throws MiddlewareQueryException { int pageSizeId = Integer.parseInt(userLabelPrinting.getSizeOfLabelSheet()); int numberOfLabelPerRow = Integer.parseInt(userLabelPrinting.getNumberOfLabelPerRow()); int numberofRowsPerPageOfLabel = Integer.parseInt(userLabelPrinting.getNumberOfRowsPerPageOfLabel()); int totalPerPage = numberOfLabelPerRow * numberofRowsPerPageOfLabel; String leftSelectedFields = userLabelPrinting.getLeftSelectedLabelFields(); String rightSelectedFields = userLabelPrinting.getRightSelectedLabelFields(); String barcodeNeeded = userLabelPrinting.getBarcodeNeeded(); String firstBarcodeField = userLabelPrinting.getFirstBarcodeField(); String secondBarcodeField = userLabelPrinting.getSecondBarcodeField(); String thirdBarcodeField = userLabelPrinting.getThirdBarcodeField(); String currentDate = DateUtil.getCurrentDate(); //String fileName = currentDate + ".xls"; String fileName = userLabelPrinting.getFilenameDLLocation(); try {/*from w w w. j av a 2 s .c o m*/ HSSFWorkbook workbook = new HSSFWorkbook(); String sheetName = cleanSheetName(userLabelPrinting.getName()); if (sheetName == null) sheetName = "Labels"; Sheet labelPrintingSheet = workbook.createSheet(sheetName); CellStyle labelStyle = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); labelStyle.setFont(font); CellStyle wrapStyle = workbook.createCellStyle(); wrapStyle.setWrapText(true); wrapStyle.setAlignment(CellStyle.ALIGN_CENTER); CellStyle mainHeaderStyle = workbook.createCellStyle(); HSSFPalette palette = workbook.getCustomPalette(); // get the color which most closely matches the color you want to use HSSFColor myColor = palette.findSimilarColor(179, 165, 165); // get the palette index of that color short palIndex = myColor.getIndex(); // code to get the style for the cell goes here mainHeaderStyle.setFillForegroundColor(palIndex); mainHeaderStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); CellStyle mainSubHeaderStyle = workbook.createCellStyle(); HSSFPalette paletteSubHeader = workbook.getCustomPalette(); // get the color which most closely matches the color you want to use HSSFColor myColorSubHeader = paletteSubHeader.findSimilarColor(190, 190, 186); // get the palette index of that color short palIndexSubHeader = myColorSubHeader.getIndex(); // code to get the style for the cell goes here mainSubHeaderStyle.setFillForegroundColor(palIndexSubHeader); mainSubHeaderStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); mainSubHeaderStyle.setAlignment(CellStyle.ALIGN_CENTER); int rowIndex = 0; int columnIndex = 0; // Create Header Information // Row 1: SUMMARY OF TRIAL, FIELD AND PLANTING DETAILS Row row = labelPrintingSheet.createRow(rowIndex++); //we add all the selected fields header StringTokenizer token = new StringTokenizer(leftSelectedFields, ","); while (token.hasMoreTokens()) { String headerId = token.nextToken(); String headerName = getHeader(headerId); Cell summaryCell = row.createCell(columnIndex++); summaryCell.setCellValue(headerName); summaryCell.setCellStyle(labelStyle); } token = new StringTokenizer(rightSelectedFields, ","); while (token.hasMoreTokens()) { String headerId = token.nextToken(); String headerName = getHeader(headerId); Cell summaryCell = row.createCell(columnIndex++); summaryCell.setCellValue(headerName); summaryCell.setCellStyle(labelStyle); } //we populate the info now int i = 0; for (StudyTrialInstanceInfo trialInstance : trialInstances) { FieldMapTrialInstanceInfo fieldMapTrialInstanceInfo = trialInstance.getTrialInstance(); Map<String, String> moreFieldInfo = new HashMap<String, String>(); moreFieldInfo.put("locationName", fieldMapTrialInstanceInfo.getLocationName()); moreFieldInfo.put("blockName", fieldMapTrialInstanceInfo.getBlockName()); moreFieldInfo.put("selectedName", trialInstance.getFieldbookName()); moreFieldInfo.put("trialInstanceNumber", fieldMapTrialInstanceInfo.getTrialInstanceNo()); for (FieldMapLabel fieldMapLabel : fieldMapTrialInstanceInfo.getFieldMapLabels()) { row = labelPrintingSheet.createRow(rowIndex++); columnIndex = 0; i++; token = new StringTokenizer(leftSelectedFields, ","); while (token.hasMoreTokens()) { String headerId = token.nextToken(); String leftText = getSpecificInfo(moreFieldInfo, fieldMapLabel, headerId); Cell summaryCell = row.createCell(columnIndex++); summaryCell.setCellValue(leftText); //summaryCell.setCellStyle(labelStyle); } token = new StringTokenizer(rightSelectedFields, ","); while (token.hasMoreTokens()) { String headerId = token.nextToken(); String rightText = getSpecificInfo(moreFieldInfo, fieldMapLabel, headerId); Cell summaryCell = row.createCell(columnIndex++); summaryCell.setCellValue(rightText); //summaryCell.setCellStyle(labelStyle); } } } for (int columnPosition = 0; columnPosition < columnIndex; columnPosition++) { labelPrintingSheet.autoSizeColumn((short) (columnPosition)); } //Write the excel file FileOutputStream fileOutputStream = new FileOutputStream(fileName); //workbook.write(baos); workbook.write(fileOutputStream); fileOutputStream.close(); //return fileOutputStream; } catch (Exception e) { LOG.error(e.getMessage(), e); } return fileName; }
From source file:com.esri.geoevent.test.performance.report.XLSXReportWriter.java
License:Apache License
@Override public void writeReport(String reportFile, List<String> testNames, List<String> columnNames, Map<String, List<FixtureStatistic>> stats) throws IOException { //create the parent directories - if needed createParentDirectoriesIfNeeded(reportFile); // rollover the file - keep backups rollOver(reportFile);/*from ww w.j a va 2 s . c o m*/ Workbook workbook = null; try { workbook = new XSSFWorkbook(); // header style CellStyle headerStyle = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); headerStyle.setFont(font); // copy the column names - add the test name as the first column List<String> columnNamesCopy = new ArrayList<String>(); columnNamesCopy.add("Test Name"); columnNamesCopy.addAll(columnNames); // create the sheet Sheet sheet = workbook.createSheet("Summary"); // create the header row int rowIndex = 0; Row headers = sheet.createRow(rowIndex); headers.setRowStyle(headerStyle); int cellIndex = 0; for (String columnName : columnNamesCopy) { Cell cell = headers.createCell(cellIndex); cell.setCellValue(columnName); cell.setCellStyle(headerStyle); cellIndex++; } for (String testName : testNames) { // get each test's fixture stats and sort them accordingly List<FixtureStatistic> fixtureStats = stats.get(testName); if (fixtureStats == null || fixtureStats.size() == 0) { continue; } Collections.sort(fixtureStats); rowIndex++; for (FixtureStatistic fixtureStat : fixtureStats) { Row data = sheet.createRow(rowIndex); cellIndex = 0; //write out the test name first Cell cell = data.createCell(cellIndex); cell.setCellValue(testName); cellIndex++; for (String columnName : columnNames) { cell = data.createCell(cellIndex); Object rawValue = fixtureStat.getStat(columnName); if (rawValue == null) { cell.setCellValue(""); } else { if (rawValue instanceof Integer) { cell.setCellValue((Integer) rawValue); } else if (rawValue instanceof Double) { cell.setCellValue((Double) rawValue); } else if (rawValue instanceof Long) { cell.setCellValue((Long) rawValue); } else if (rawValue instanceof Boolean) { cell.setCellValue((Boolean) rawValue); } else { cell.setCellValue(rawValue.toString()); } } // adjust column width to fit the content sheet.autoSizeColumn(cellIndex); cellIndex++; } //rowIndex++; } } //write out the total time if (getTotalTestingTime() != -1) { rowIndex = rowIndex + 2; Row data = sheet.createRow(rowIndex); Cell cell = data.createCell(0); cell.setCellValue("Total Testing Time:"); cell.setCellStyle(headerStyle); cell = data.createCell(1); cell.setCellValue(formatTime(getTotalTestingTime())); } } finally { // write out the file FileOutputStream out = null; try { String fullPath = FilenameUtils.getFullPathNoEndSeparator(reportFile); // create all non exists folders else you will hit FileNotFoundException for report file path new File(fullPath).mkdirs(); out = new FileOutputStream(reportFile); if (workbook != null) { workbook.write(out); } } finally { IOUtils.closeQuietly(out); } } }