List of usage examples for org.apache.poi.ss.usermodel Sheet autoSizeColumn
void autoSizeColumn(int column);
From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java
License:Open Source License
/** * Sets the column to automatically adjust the column width to fit the * largest cell content within the column. This is a POI feature, and is * meant to be called after all the data for the target column has been * written. See {@link Sheet#autoSizeColumn(int)}. * <p>/*from w w w.jav a 2 s .co m*/ * This does not take into account cells that have custom Vaadin components * inside them. * * @param columnIndex * Index of the target column, 0-based */ public void autofitColumn(int columnIndex) { final Sheet activeSheet = getActiveSheet(); activeSheet.autoSizeColumn(columnIndex); getState().colW[columnIndex] = AbstractExcelUtils .getColumnWidthInPx(activeSheet.getColumnWidth(columnIndex)); getCellValueManager().clearCacheForColumn(columnIndex + 1); getCellValueManager().loadCellData(firstRow, columnIndex + 1, lastRow, columnIndex + 1); if (hasSheetOverlays()) { reloadImageSizesFromPOI = true; loadOrUpdateOverlays(); } }
From source file:controller.VisitasController.java
public void autoSizeColumns(Workbook workbook) { int numberOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numberOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); if (sheet.getPhysicalNumberOfRows() > 0) { Row row = sheet.getRow(8);/*from w ww .ja v a 2s . c o m*/ Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); int columnIndex = cell.getColumnIndex(); sheet.autoSizeColumn(columnIndex); } } } }
From source file:csv.impl.DefaultExcelFormatter.java
License:Open Source License
/** * Finalizes the workbook.//w ww. j av a 2 s .com * This method is called immediately before the {@link ExcelWriter} writes the * complete workbook to the underlying output stream. * This implementation just sets all columns to auto fit. * @param writer the calling writer * @param rowCount the number of rows in the selected sheet * @param columnCount the number of columns modified in the selected sheet */ @Override public void finalize(ExcelWriter writer, int rowCount, int columnCount) { Sheet sheet = writer.getSheet(); for (int i = 0; i <= columnCount; i++) sheet.autoSizeColumn(i); }
From source file:das.pf.io.IOExcel.java
License:Open Source License
public boolean processFile(Path input, boolean openFile) { boolean result = false; int endRow = 0; try {//from w w w . j a va 2 s. co m updateMessages(String.format("Inicializando el documento: %s", input.toString())); Path copy = createCopy(input); if (copy != null && Files.exists(copy, LinkOption.NOFOLLOW_LINKS)) { Workbook workbook = WorkbookFactory.create(copy.toFile()); Sheet sheet = workbook.getSheetAt(0); Sheet newSheet = workbook.createSheet("Procesado"); workbook.setSheetName(0, "Crudo"); endRow = getLasRow(sheet); // seccion para la creacion de los encabezados updateMessages("Creando la cabecera de los datos"); createHeaderData(newSheet, getCellStyleHeaderData(workbook)); // seccion para los values USD updateMessages( String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.MTH.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.MTH), 11, 35, 14); updateMessages( String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.QRT.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.QRT), 35, 49, 38); updateMessages( String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.YTD.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.YTD), 49, 54, 52); updateMessages( String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.MAT.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.MAT), 54, 59, 57); // seccion para los values units updateMessages( String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.MTH.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.MTH), 59, 83, 63); updateMessages( String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.QRT.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.QRT), 83, 97, 87); updateMessages( String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.YTD.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.YTD), 97, 102, 101); updateMessages( String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.MAT.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.MAT), 102, 107, 106); // // // seccion para los values units standars updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s", TypeUnits.MTH.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.MTH), 107, 131, 112); updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s", TypeUnits.QRT.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.QRT), 131, 145, 136); updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s", TypeUnits.YTD.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.YTD), 145, 150, 150); updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s", TypeUnits.MAT.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.MAT), 150, 155, 155); // fin de la seccion para la creacion de los encabezados // seccion para escribir los CT updateMessages("Escribiendo las clases terampeuticas..."); writeCT(newSheet, sheet, 13, endRow); // seccion para escribir los productos updateMessages("Escribiendo los productos..."); writeProducts(newSheet, sheet, 14); // seccion para escribir los otros valores updateMessages("Escribiendo datos en general..."); writerOthersValues(newSheet, sheet, 15); // seccion para escribir los key competitors updateMessages("Escribiendo los Key Competitors..."); writeKeyCompetitors(newSheet, 3, endRow, 9, 5); // seccion para escribir el pais XmlContry contry = writeContries(newSheet, 3, 0, input); // seccion para escribir la region writeRegions(contry, newSheet, 3, 1); for (int i = 0; i < 155; i++) newSheet.autoSizeColumn(i); newSheet.setAutoFilter(CellRangeAddress.valueOf(String.format("A3:K%d", newSheet.getLastRowNum()))); String pathOutput = "DAS PF - " + input.getFileName().toString(); try (FileOutputStream fos = new FileOutputStream( Paths.get(this.out.toString(), pathOutput).toFile())) { updateMessages(String.format("Guadando el trabajo en la ruta: '%s'", Paths.get(this.out.toString(), pathOutput))); workbook.write(fos); } catch (IOException ex) { Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex); } finally { workbook.close(); } if (openFile && Desktop.isDesktopSupported() && Desktop.getDesktop().isSupported(Desktop.Action.OPEN)) Desktop.getDesktop().open(Paths.get(this.out.toString(), pathOutput).toFile()); result = true; newSheet = null; sheet = null; workbook = null; Files.delete(copy); } } catch (IOException | InvalidFormatException ex) { Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex); Util.showException("No se pudo guardar el archivo", ex); } return result; }
From source file:das.pf.io.IOExcel.java
License:Open Source License
public boolean consolidateFiles() { boolean result = false; AtomicInteger rowIndex = new AtomicInteger(3); String outputName = "DAS - " + this.out.getFileName().toString() + " consolidate.xlsx"; Workbook consolidateWb = new XSSFWorkbook(); try {/*from w w w. j a v a 2 s . c o m*/ Sheet sheetConsolidate = consolidateWb.createSheet("Consolidado"); Files.list(this.out).filter((p) -> { String name = p.toString(); return (name.endsWith(".xlsx") || name.endsWith(".xls")) && !p.getFileName().toString().equals(outputName); }).sorted((p1, p2) -> { String acronym = getAcromynName(p1); String acronym2 = getAcromynName(p2); return acronym.compareToIgnoreCase(acronym2); }).forEach(p -> { try { Workbook wb = WorkbookFactory.create(p.toFile()); Sheet sheet = wb.getSheet("Procesado"); updateMessages(String.format("Copiando los datos del archivo: %s dentro del archivo: %s", p.toString(), outputName)); for (int index = 3; index < sheet.getLastRowNum(); index++) { Row row = sheet.getRow(index); Row r = sheetConsolidate.createRow(rowIndex.getAndIncrement()); row.forEach(c -> { if (c != null && c.getCellType() != Cell.CELL_TYPE_BLANK) { final Cell cell = r.createCell(c.getColumnIndex(), c.getCellType()); updateMessages( String.format("Copiando los datos de la fila: #%d", c.getRowIndex())); switch (c.getCellType()) { case Cell.CELL_TYPE_NUMERIC: cell.setCellValue(c.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: cell.setCellValue(c.getRichStringCellValue()); break; } } }); row = null; } sheet = null; wb.close(); wb = null; } catch (IOException | InvalidFormatException | EncryptedDocumentException ex) { Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex); } }); Path path = Files.list(this.out).filter((p) -> { String name = p.toString(); return (name.endsWith(".xlsx") || name.endsWith(".xls")) && !p.getFileName().toString().equals(outputName); }).findFirst().get(); createHeadersConsolidateFile(consolidateWb, path); for (int i = 0; i < 155; i++) sheetConsolidate.autoSizeColumn(i); sheetConsolidate.setAutoFilter( CellRangeAddress.valueOf(String.format("A3:K%d", sheetConsolidate.getLastRowNum()))); try (FileOutputStream fos = new FileOutputStream(Paths.get(out.toString(), outputName).toFile())) { updateMessages(String.format("Guadando el trabajo en la ruta: '%s'", Paths.get(this.out.toString(), outputName))); consolidateWb.write(fos); result = true; } catch (IOException ex) { Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, "Ocurrio un error al intenatr guardar el archivo consolidado", ex); } finally { consolidateWb.close(); } } catch (IOException ex) { Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex); } consolidateWb = null; return result; }
From source file:ddf.metrics.reporting.internal.rrd4j.RrdMetricsRetriever.java
License:Open Source License
private void createSummary(Workbook wb, List<String> metricNames, String metricsDir, long startTime, long endTime, SUMMARY_INTERVALS summaryInterval) throws IOException, MetricsGraphException { // convert seconds to milliseconds startTime = TimeUnit.SECONDS.toMillis(startTime); endTime = TimeUnit.SECONDS.toMillis(endTime); DateTime reportStart = new DateTime(startTime, DateTimeZone.UTC); DateTime reportEnd = new DateTime(endTime, DateTimeZone.UTC); Sheet sheet = wb.createSheet(); wb.setSheetName(0,// w w w . jav a 2 s .c o m reportStart.toString(SUMMARY_TIMESTAMP) + " to " + reportEnd.toString(SUMMARY_TIMESTAMP)); Row headingRow = sheet.createRow(0); int columnMax = 1; for (String metricName : metricNames) { MutableDateTime chunkStart = new MutableDateTime(reportStart); MutableDateTime chunkEnd = new MutableDateTime(chunkStart); Row row = sheet.createRow(metricNames.indexOf(metricName) + 1); int columnCounter = 1; Boolean isSum = null; while (reportEnd.compareTo(chunkEnd) > 0 && columnCounter < EXCEL_MAX_COLUMNS) { increment(chunkEnd, summaryInterval); if (chunkEnd.isAfter(reportEnd)) { chunkEnd.setMillis(reportEnd); } // offset range by one millisecond so rrd will calculate granularity correctly chunkEnd.addMillis(-1); MetricData metricData = getMetricData(getRrdFilename(metricsDir, metricName), TimeUnit.MILLISECONDS.toSeconds(chunkStart.getMillis()), TimeUnit.MILLISECONDS.toSeconds(chunkEnd.getMillis())); isSum = metricData.hasTotalCount(); chunkEnd.addMillis(1); if (headingRow.getCell(columnCounter) == null) { Cell headingRowCell = headingRow.createCell(columnCounter); headingRowCell.getCellStyle().setWrapText(true); headingRowCell.setCellValue(getTimestamp(chunkStart, chunkEnd, columnCounter, summaryInterval)); } Cell sumOrAvg = row.createCell(columnCounter); if (isSum) { sumOrAvg.setCellValue((double) metricData.getTotalCount()); } else { sumOrAvg.setCellValue(cumulativeRunningAverage(metricData.getValues())); } chunkStart.setMillis(chunkEnd); columnCounter++; } columnMax = columnCounter; if (isSum != null) { row.createCell(0).setCellValue(convertCamelCase(metricName) + " (" + (isSum ? "sum" : "avg") + ")"); } } for (int i = 0; i < columnMax; i++) { sheet.autoSizeColumn(i); } }
From source file:ddf.metrics.reporting.internal.rrd4j.RrdMetricsRetriever.java
License:Open Source License
/** * Creates an Excel worksheet containing the metric's data (timestamps and values) for the * specified time range. This worksheet is titled with the trhe metric's name and added to the * specified Workbook.//from w w w . j a v a 2s . com * * @param wb the workbook to add this worksheet to * @param metricName the name of the metric whose data is being rendered in this worksheet * @param rrdFilename the name of the RRD file to retrieve the metric's data from * @param startTime start time, in seconds since Unix epoch, to fetch metric's data * @param endTime end time, in seconds since Unix epoch, to fetch metric's data * @throws IOException * @throws MetricsGraphException */ private void createSheet(Workbook wb, String metricName, String rrdFilename, long startTime, long endTime) throws IOException, MetricsGraphException { LOGGER.trace("ENTERING: createSheet"); MetricData metricData = getMetricData(rrdFilename, startTime, endTime); String displayableMetricName = convertCamelCase(metricName); String title = displayableMetricName + " for " + getCalendarTime(startTime) + " to " + getCalendarTime(endTime); Sheet sheet = wb.createSheet(displayableMetricName); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle columnHeadingsStyle = wb.createCellStyle(); columnHeadingsStyle.setFont(headerFont); CellStyle bannerStyle = wb.createCellStyle(); bannerStyle.setFont(headerFont); bannerStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index); bannerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); int rowCount = 0; Row row = sheet.createRow((short) rowCount); Cell cell = row.createCell(0); cell.setCellValue(title); cell.setCellStyle(bannerStyle); rowCount++; // Blank row for spacing/readability row = sheet.createRow((short) rowCount); cell = row.createCell(0); cell.setCellValue(""); rowCount++; row = sheet.createRow((short) rowCount); cell = row.createCell(0); cell.setCellValue("Timestamp"); cell.setCellStyle(columnHeadingsStyle); cell = row.createCell(1); cell.setCellValue("Value"); cell.setCellStyle(columnHeadingsStyle); rowCount++; List<Long> timestamps = metricData.getTimestamps(); List<Double> values = metricData.getValues(); for (int i = 0; i < timestamps.size(); i++) { String timestamp = getCalendarTime(timestamps.get(i)); row = sheet.createRow((short) rowCount); row.createCell(0).setCellValue(timestamp); row.createCell(1).setCellValue(values.get(i)); rowCount++; } if (metricData.hasTotalCount()) { // Blank row for spacing/readability row = sheet.createRow((short) rowCount); cell = row.createCell(0); cell.setCellValue(""); rowCount++; row = sheet.createRow((short) rowCount); cell = row.createCell(0); cell.setCellValue("Total Count: "); cell.setCellStyle(columnHeadingsStyle); row.createCell(1).setCellValue(metricData.getTotalCount()); } sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); LOGGER.trace("EXITING: createSheet"); }
From source file:de.fme.alfresco.repo.web.scripts.datalist.DataListDownloadWebScript.java
License:Open Source License
@SuppressWarnings("deprecation") @Override/*from w w w. ja v a 2 s . c o m*/ protected void populateBody(Object resource, Workbook workbook, Sheet sheet, List<QName> properties) throws IOException { NodeRef list = (NodeRef) resource; List<NodeRef> items = getItems(list); // Our various formats DataFormat formatter = workbook.createDataFormat(); CreationHelper createHelper = workbook.getCreationHelper(); CellStyle styleInt = workbook.createCellStyle(); styleInt.setDataFormat(formatter.getFormat("0")); CellStyle styleDate = workbook.createCellStyle(); styleDate.setDataFormat(formatter.getFormat("yyyy-mm-dd")); CellStyle styleDouble = workbook.createCellStyle(); styleDouble.setDataFormat(formatter.getFormat("General")); CellStyle styleNewLines = workbook.createCellStyle(); styleNewLines.setWrapText(true); CellStyle hlink_style = workbook.createCellStyle(); Font hlink_font = workbook.createFont(); hlink_font.setUnderline(Font.U_SINGLE); hlink_font.setColor(IndexedColors.BLUE.getIndex()); hlink_style.setFont(hlink_font); // Export the items int rowNum = 1, colNum = 0; for (NodeRef item : items) { Row r = sheet.createRow(rowNum); colNum = 0; for (QName prop : properties) { Cell c = r.createCell(colNum); Serializable val = nodeService.getProperty(item, prop); if (val == null) { // Is it an association, or just missing? List<AssociationRef> assocs = nodeService.getTargetAssocs(item, prop); Set<QName> qnames = new HashSet<QName>(1, 1.0f); qnames.add(prop); List<ChildAssociationRef> childAssocs = nodeService.getChildAssocs(item, qnames); if (assocs.size() > 0) { StringBuffer text = new StringBuffer(); int lines = 1; for (AssociationRef ref : assocs) { NodeRef child = ref.getTargetRef(); QName type = nodeService.getType(child); if (ContentModel.TYPE_PERSON.equals(type)) { if (text.length() > 0) { text.append('\n'); lines++; } text.append(nodeService.getProperty(child, ContentModel.PROP_FIRSTNAME)); text.append(" "); text.append(nodeService.getProperty(child, ContentModel.PROP_LASTNAME)); } else if (ContentModel.TYPE_CONTENT.equals(type)) { // TODO Link to the content if (text.length() > 0) { text.append('\n'); lines++; } text.append(nodeService.getProperty(child, ContentModel.PROP_NAME)); text.append(" ("); text.append(nodeService.getProperty(child, ContentModel.PROP_TITLE)); text.append(") "); /*MessageFormat.format(CONTENT_DOWNLOAD_PROP_URL, new Object[] { child.getStoreRef().getProtocol(), child.getStoreRef().getIdentifier(), child.getId(), URLEncoder.encode((String)nodeService.getProperty(child, ContentModel.PROP_TITLE)), URLEncoder.encode(ContentModel.PROP_CONTENT.toString()) }); */ /*currently only one link per cell possible * Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL); *link.setAddress("http://poi.apache.org/"); *c.setHyperlink(link); *c.setCellStyle(hlink_style);*/ } else if (ApplicationModel.TYPE_FILELINK.equals(type)) { NodeRef linkRef = (NodeRef) nodeService.getProperty(child, ContentModel.PROP_LINK_DESTINATION); if (linkRef != null) { if (text.length() > 0) { text.append('\n'); lines++; } text.append("link to: "); try { text.append(nodeService.getProperty(linkRef, ContentModel.PROP_NAME)); text.append(" ("); text.append(nodeService.getProperty(linkRef, ContentModel.PROP_TITLE)); text.append(") "); } catch (Exception e) { text.append(nodeService.getProperty(child, ContentModel.PROP_NAME)); text.append(" ("); text.append(nodeService.getProperty(child, ContentModel.PROP_TITLE)); text.append(") "); } } } else { System.err.println("TODO: handle " + type + " for " + child); } } String v = text.toString(); c.setCellValue(v); if (lines > 1) { c.setCellStyle(styleNewLines); r.setHeightInPoints(lines * sheet.getDefaultRowHeightInPoints()); } } else if (childAssocs.size() > 0) { StringBuffer text = new StringBuffer(); for (ChildAssociationRef childAssociationRef : childAssocs) { NodeRef child = childAssociationRef.getChildRef(); QName type = nodeService.getType(child); if (type.equals(ForumModel.TYPE_FORUM)) { List<ChildAssociationRef> topics = nodeService.getChildAssocs(child); if (topics.size() > 0) { ChildAssociationRef topicRef = topics.get(0); List<ChildAssociationRef> comments = nodeService .getChildAssocs(topicRef.getChildRef()); for (ChildAssociationRef commentChildRef : comments) { NodeRef commentRef = commentChildRef.getChildRef(); ContentData data = (ContentData) nodeService.getProperty(commentRef, ContentModel.PROP_CONTENT); TemplateContentData contentData = new TemplateContentData(data, ContentModel.PROP_CONTENT); String commentString = ""; try { commentString = contentData.getContentAsText(commentRef, -1); } catch (Exception e) { logger.warn("failed to extract content for nodeRef " + commentRef, e); } String creator = (String) nodeService.getProperty(commentRef, ContentModel.PROP_CREATOR); NodeRef person = personService.getPerson(creator, false); if (person != null) { creator = nodeService.getProperty(person, ContentModel.PROP_FIRSTNAME) + " " + nodeService.getProperty(person, ContentModel.PROP_LASTNAME); } Date created = (Date) nodeService.getProperty(commentRef, ContentModel.PROP_CREATED); text.append(creator).append(" (") .append(DateFormatUtils.format(created, "yyyy-MM-dd")) .append("):\n "); text.append(commentString).append("\n"); } } } } String v = text.toString(); c.setCellValue(v); c.setCellStyle(styleNewLines); } else { // This property isn't set c.setCellType(Cell.CELL_TYPE_BLANK); } } else { // Regular property, set if (val instanceof String) { c.setCellValue((String) val); c.setCellStyle(styleNewLines); } else if (val instanceof Date) { c.setCellValue((Date) val); c.setCellStyle(styleDate); } else if (val instanceof Integer || val instanceof Long) { double v = 0.0; if (val instanceof Long) v = (double) (Long) val; if (val instanceof Integer) v = (double) (Integer) val; c.setCellValue(v); c.setCellStyle(styleInt); } else if (val instanceof Float || val instanceof Double) { double v = 0.0; if (val instanceof Float) v = (double) (Float) val; if (val instanceof Double) v = (double) (Double) val; c.setCellValue(v); c.setCellStyle(styleDouble); } else { // TODO System.err.println("TODO: handle " + val.getClass().getName() + " - " + val); } } colNum++; } rowNum++; } // Sensible column widths please! colNum = 0; for (QName prop : properties) { try { sheet.autoSizeColumn(colNum); } catch (IllegalArgumentException e) { sheet.setColumnWidth(colNum, 40 * 256); } colNum++; } }
From source file:de.iteratec.iteraplan.businesslogic.exchange.nettoExport.NettoExcelTransformer.java
License:Open Source License
/**{@inheritDoc}**/ @Override//from ww w.j a v a 2 s .c o m public void transform(List<?> sourceList, OutputStream out, TypeOfBuildingBlock typeOfBuildingBlock) { ColumnStructure[] columns = tableStructure.getColumns(); try { Workbook workbook = createWorkbook(); Sheet sheet = workbook.createSheet(); configSheetName(sheet, typeOfBuildingBlock); Map<IteraExcelStyle, CellStyle> createdStyles = ExcelStylesCreator.createStyles(workbook); CellStyle headerStyle = createdStyles.get(IteraExcelStyle.HEADER); CellStyle dataStyle = createdStyles.get(IteraExcelStyle.DATA); CellStyle dataDateStyle = createdStyles.get(IteraExcelStyle.DATA_DATE); // Create cell style for numbers CellStyle numCellStyle = workbook.createCellStyle(); numCellStyle.cloneStyleFrom(dataStyle); short numFormatIndex = workbook.createDataFormat().getFormat("0.00"); numCellStyle.setDataFormat(numFormatIndex); Row headerRow = sheet.createRow(0); int nextCol = 0; for (ColumnStructure columnStructure : columns) { Cell headerCell = headerRow.createCell(nextCol); headerCell.setCellValue(columnStructure.getColumnHeader()); headerCell.setCellStyle(headerStyle); nextCol++; } int nextRow = 1; for (Object obj : sourceList) { if (obj instanceof BuildingBlock) { BuildingBlock bb = (BuildingBlock) obj; // skip virutal root element if (bb instanceof AbstractHierarchicalEntity<?>) { AbstractHierarchicalEntity<?> hierarchicalEntity = (AbstractHierarchicalEntity<?>) bb; if (hierarchicalEntity.isTopLevelElement()) { continue; } } Row row = sheet.createRow(nextRow); nextCol = 0; for (ColumnStructure columnStructure : columns) { Cell cell = row.createCell(nextCol); Object resolvedValue = columnStructure.resolveValue(bb); if (resolvedValue instanceof Date) { cell.setCellStyle(dataDateStyle); cell.setCellValue((Date) resolvedValue); } else if (resolvedValue instanceof Number) { cell.setCellStyle(numCellStyle); double doubleValue = ((Number) resolvedValue).doubleValue(); cell.setCellValue(doubleValue); } else { cell.setCellStyle(dataStyle); cell.setCellValue(String.valueOf(resolvedValue)); } ++nextCol; } ++nextRow; } } // auto format nextCol = 0; for (int col = 0; col < columns.length; col++) { sheet.autoSizeColumn(col); int columnCharWidth = sheet.getColumnWidth(col) / 256; if (columnCharWidth > MAX_COLUM_CHAR_WIDTH) { sheet.setColumnWidth(col, MAX_COLUM_CHAR_WIDTH * 256); } } workbook.write(out); out.flush(); } catch (IOException e) { e.printStackTrace(); } }
From source file:de.ks.idnadrev.expimp.xls.XlsxExporter.java
License:Apache License
protected void exportSource(Sheet sheet, EntityExportSource<?> source) { List<XlsxColumn> columns = getColumnDefinitions(source); createTitle(sheet, columns);//from w w w . ja va 2 s .co m int rowId = 1; for (AbstractPersistentObject object : source) { Row row = sheet.createRow(rowId); for (int columnId = 0; columnId < columns.size(); columnId++) { XlsxColumn column = columns.get(columnId); Object value = column.getValue(object); if (value == null) { row.createCell(columnId, Cell.CELL_TYPE_BLANK); } else { Cell cell = row.createCell(columnId, column.getCellType()); cell.setCellStyle(column.getCellStyle(workbook)); setCellValue(sheet.getWorkbook().getCreationHelper(), cell, value); } } rowId++; } for (int columnId = 0; columnId < columns.size(); columnId++) { try { sheet.autoSizeColumn(columnId); } catch (NullPointerException e) { // } } }