List of usage examples for org.apache.poi.ss.usermodel CellStyle setWrapText
void setWrapText(boolean wrapped);
From source file:com.haulmont.cuba.gui.export.ExcelExporter.java
License:Apache License
public void exportTable(Table<Entity> table, List<Table.Column> columns, Boolean exportExpanded, ExportDisplay display, List<String> filterDescription, String fileName, ExportMode exportMode) { if (display == null) { throw new IllegalArgumentException("ExportDisplay is null"); }/* w ww .j a va 2s .co m*/ createWorkbookWithSheet(); createFonts(); createFormats(); int r = 0; if (filterDescription != null) { for (r = 0; r < filterDescription.size(); r++) { String line = filterDescription.get(r); HSSFRow row = sheet.createRow(r); if (r == 0) { HSSFRichTextString richTextFilterName = new HSSFRichTextString(line); richTextFilterName.applyFont(boldFont); row.createCell(0).setCellValue(richTextFilterName); } else { row.createCell(0).setCellValue(line); } } r++; } HSSFRow row = sheet.createRow(r); createAutoColumnSizers(columns.size()); float maxHeight = sheet.getDefaultRowHeightInPoints(); CellStyle headerCellStyle = wb.createCellStyle(); headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); for (Table.Column column : columns) { String caption = column.getCaption(); int countOfReturnSymbols = StringUtils.countMatches(caption, "\n"); if (countOfReturnSymbols > 0) { maxHeight = Math.max(maxHeight, (countOfReturnSymbols + 1) * sheet.getDefaultRowHeightInPoints()); headerCellStyle.setWrapText(true); } } row.setHeightInPoints(maxHeight); for (int c = 0; c < columns.size(); c++) { Table.Column column = columns.get(c); String caption = column.getCaption(); HSSFCell cell = row.createCell(c); HSSFRichTextString richTextString = new HSSFRichTextString(caption); richTextString.applyFont(boldFont); cell.setCellValue(richTextString); ExcelAutoColumnSizer sizer = new ExcelAutoColumnSizer(); sizer.notifyCellValue(caption, boldFont); sizers[c] = sizer; cell.setCellStyle(headerCellStyle); } CollectionDatasource datasource = table.getDatasource(); if (exportMode == ExportMode.SELECTED_ROWS && table.getSelected().size() > 0) { Set<Entity> selected = table.getSelected(); List<Entity> ordered = ((Collection<Entity>) datasource.getItems()).stream().filter(selected::contains) .collect(Collectors.toList()); for (Entity item : ordered) { createRow(table, columns, 0, ++r, item.getId()); } } else { if (table instanceof TreeTable) { TreeTable treeTable = (TreeTable) table; HierarchicalDatasource ds = treeTable.getDatasource(); if (table.isAggregatable()) { r = createAggregatableRow(table, columns, ++r, 1, datasource); } for (Object itemId : ds.getRootItemIds()) { r = createHierarhicalRow(treeTable, columns, exportExpanded, r, itemId); } } else if (table instanceof GroupTable && datasource instanceof GroupDatasource && ((GroupDatasource) datasource).hasGroups()) { GroupDatasource ds = (GroupDatasource) datasource; if (table.isAggregatable()) { r = createAggregatableRow(table, columns, ++r, 1, datasource); } for (Object item : ds.rootGroups()) { r = createGroupRow((GroupTable) table, columns, ++r, (GroupInfo) item, 0); } } else { if (table.isAggregatable()) { r = createAggregatableRow(table, columns, ++r, 1, datasource); } for (Object itemId : datasource.getItemIds()) { createRow(table, columns, 0, ++r, itemId); } } } for (int c = 0; c < columns.size(); c++) { sheet.setColumnWidth(c, sizers[c].getWidth() * COL_WIDTH_MAGIC); } ByteArrayOutputStream out = new ByteArrayOutputStream(); try { wb.write(out); } catch (IOException e) { throw new RuntimeException("Unable to write document", e); } if (fileName == null) { fileName = messages.getTools().getEntityCaption(datasource.getMetaClass()); } display.show(new ByteArrayDataProvider(out.toByteArray()), fileName + ".xls", ExportFormat.XLS); }
From source file:com.haulmont.cuba.gui.export.ExcelExporter.java
License:Apache License
public void exportDataGrid(DataGrid<Entity> dataGrid, List<DataGrid.Column> columns, ExportDisplay display, List<String> filterDescription, String fileName, ExportMode exportMode) { if (display == null) { throw new IllegalArgumentException("ExportDisplay is null"); }// w ww. j a v a2s . c o m createWorkbookWithSheet(); createFonts(); createFormats(); int r = 0; if (filterDescription != null) { for (r = 0; r < filterDescription.size(); r++) { String line = filterDescription.get(r); HSSFRow row = sheet.createRow(r); if (r == 0) { HSSFRichTextString richTextFilterName = new HSSFRichTextString(line); richTextFilterName.applyFont(boldFont); row.createCell(0).setCellValue(richTextFilterName); } else { row.createCell(0).setCellValue(line); } } r++; } HSSFRow row = sheet.createRow(r); createAutoColumnSizers(columns.size()); float maxHeight = sheet.getDefaultRowHeightInPoints(); CellStyle headerCellStyle = wb.createCellStyle(); headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); for (DataGrid.Column column : columns) { String caption = column.getCaption(); int countOfReturnSymbols = StringUtils.countMatches(caption, "\n"); if (countOfReturnSymbols > 0) { maxHeight = Math.max(maxHeight, (countOfReturnSymbols + 1) * sheet.getDefaultRowHeightInPoints()); headerCellStyle.setWrapText(true); } } row.setHeightInPoints(maxHeight); for (int c = 0; c < columns.size(); c++) { DataGrid.Column column = columns.get(c); String caption = column.getCaption(); HSSFCell cell = row.createCell(c); HSSFRichTextString richTextString = new HSSFRichTextString(caption); richTextString.applyFont(boldFont); cell.setCellValue(richTextString); ExcelAutoColumnSizer sizer = new ExcelAutoColumnSizer(); sizer.notifyCellValue(caption, boldFont); sizers[c] = sizer; cell.setCellStyle(headerCellStyle); } CollectionDatasource datasource = dataGrid.getDatasource(); if (exportMode == ExportMode.SELECTED_ROWS && dataGrid.getSelected().size() > 0) { Set<Entity> selected = dataGrid.getSelected(); List<Entity> ordered = ((Collection<Entity>) datasource.getItems()).stream().filter(selected::contains) .collect(Collectors.toList()); for (Entity item : ordered) { createDataGridRow(dataGrid, columns, 0, ++r, item.getId()); } } else { for (Object itemId : datasource.getItemIds()) { createDataGridRow(dataGrid, columns, 0, ++r, itemId); } } for (int c = 0; c < columns.size(); c++) { sheet.setColumnWidth(c, sizers[c].getWidth() * COL_WIDTH_MAGIC); } ByteArrayOutputStream out = new ByteArrayOutputStream(); try { wb.write(out); } catch (IOException e) { throw new RuntimeException("Unable to write document", e); } if (fileName == null) { fileName = messages.getTools().getEntityCaption(datasource.getMetaClass()); } display.show(new ByteArrayDataProvider(out.toByteArray()), fileName + ".xls", ExportFormat.XLS); }
From source file:com.helger.poi.excel.style.ExcelStyle.java
License:Apache License
public void fillCellStyle(@Nonnull final Workbook aWB, @Nonnull final CellStyle aCS, @Nonnull final CreationHelper aCreationHelper) { if (m_eAlign != null) aCS.setAlignment(m_eAlign.getValue()); if (m_eVAlign != null) aCS.setVerticalAlignment(m_eVAlign.getValue()); aCS.setWrapText(m_bWrapText); if (m_sDataFormat != null) aCS.setDataFormat(aCreationHelper.createDataFormat().getFormat(m_sDataFormat)); if (m_eFillBackgroundColor != null) aCS.setFillBackgroundColor(m_eFillBackgroundColor.getIndex()); if (m_eFillForegroundColor != null) aCS.setFillForegroundColor(m_eFillForegroundColor.getIndex()); if (m_eFillPattern != null) aCS.setFillPattern(m_eFillPattern.getValue()); if (m_eBorderTop != null) aCS.setBorderTop(m_eBorderTop.getValue()); if (m_eBorderRight != null) aCS.setBorderRight(m_eBorderRight.getValue()); if (m_eBorderBottom != null) aCS.setBorderBottom(m_eBorderBottom.getValue()); if (m_eBorderLeft != null) aCS.setBorderLeft(m_eBorderLeft.getValue()); if (m_nFontIndex >= 0) aCS.setFont(aWB.getFontAt(m_nFontIndex)); }
From source file:com.ideaspymes.proyecttemplate.stock.web.ProductoConsultaBean.java
@Override public Workbook getWorkBook() { Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("My Sample Excel"); List<CatalogoProductos> lista = (List<CatalogoProductos>) getDetalles(); sheet.setDefaultRowHeight((short) (sheet.getDefaultRowHeight() * new Short("6"))); org.apache.poi.ss.usermodel.Font fontTitulo = wb.createFont(); fontTitulo.setFontHeightInPoints((short) 12); fontTitulo.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); org.apache.poi.ss.usermodel.Font fontTituloPricipal = wb.createFont(); fontTituloPricipal.setFontHeightInPoints((short) 22); fontTituloPricipal.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); DataFormat format = wb.createDataFormat(); CellStyle styleTituloPrincipal = wb.createCellStyle(); styleTituloPrincipal.setFont(fontTituloPricipal); styleTituloPrincipal.setVerticalAlignment(CellStyle.VERTICAL_CENTER); styleTituloPrincipal.setAlignment(CellStyle.ALIGN_CENTER); CellStyle styleTitulo = wb.createCellStyle(); styleTitulo.setFont(fontTitulo);/*from ww w . ja v a2 s .c om*/ styleTitulo.setVerticalAlignment(CellStyle.VERTICAL_CENTER); styleTitulo.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex()); styleTitulo.setFillPattern(CellStyle.SOLID_FOREGROUND); styleTitulo.setWrapText(true); CellStyle styleNumero = wb.createCellStyle(); styleNumero.setDataFormat(format.getFormat("#,##0")); styleNumero.setWrapText(true); styleNumero.setVerticalAlignment(CellStyle.VERTICAL_CENTER); styleNumero.setAlignment(CellStyle.ALIGN_CENTER); CellStyle styleFecha = wb.createCellStyle(); styleFecha.setDataFormat(format.getFormat("dd/MM/yyyy")); styleFecha.setVerticalAlignment(CellStyle.VERTICAL_CENTER); styleFecha.setAlignment(CellStyle.ALIGN_CENTER); CellStyle style = wb.createCellStyle(); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setWrapText(true); CellStyle styleCenter = wb.createCellStyle(); styleCenter.setVerticalAlignment(CellStyle.VERTICAL_CENTER); styleCenter.setAlignment(CellStyle.ALIGN_CENTER); styleCenter.setWrapText(true); Row rowTitle = sheet.createRow(0); Cell cellTitle = rowTitle.createCell(1); cellTitle.setCellStyle(styleTituloPrincipal); sheet.addMergedRegion(new CellRangeAddress(0, //first row (0-based) 1, //last row (0-based) 1, //first column (0-based) 11 //last column (0-based) )); cellTitle.setCellValue("Listado de Activos"); int i = 2; Row row0 = sheet.createRow(i); row0.setHeight((short) 500); Cell cell1 = row0.createCell(1); cell1.setCellValue("Foto"); cell1.setCellStyle(styleTitulo); Cell cellFecha = row0.createCell(3); cellFecha.setCellValue("Fecha Ingreso"); cellFecha.setCellStyle(styleTitulo); Cell cellFechaCarga = row0.createCell(4); cellFechaCarga.setCellValue("Fecha Carga"); cellFechaCarga.setCellStyle(styleTitulo); Cell cell3 = row0.createCell(5); cell3.setCellValue("Nombre"); cell3.setCellStyle(styleTitulo); Cell cell4 = row0.createCell(6); cell4.setCellValue("Cdigo"); cell4.setCellStyle(styleTitulo); Cell cell5 = row0.createCell(7); cell5.setCellValue("Descripcin"); cell5.setCellStyle(styleTitulo); Cell cell6 = row0.createCell(8); cell6.setCellValue("Es Regalo?"); cell6.setCellStyle(styleTitulo); Cell cell7 = row0.createCell(9); cell7.setCellValue("Familia"); cell7.setCellStyle(styleTitulo); Cell cell8 = row0.createCell(10); cell8.setCellValue("Ubicaciones"); cell8.setCellStyle(styleTitulo); Cell cell9 = row0.createCell(11); cell9.setCellValue("Stock"); cell9.setCellStyle(styleTitulo); for (CatalogoProductos cp : lista) { int indexFila = i + 1; if (cp.getImagen() != null) { int pictureIdx = wb.addPicture(cp.getImagen(), Workbook.PICTURE_TYPE_PNG); CreationHelper helper = wb.getCreationHelper(); //Creates the top-level drawing patriarch. Drawing drawing = sheet.createDrawingPatriarch(); //Create an anchor that is attached to the worksheet ClientAnchor anchor = helper.createClientAnchor(); //set top-left corner for the image anchor.setCol1(1); anchor.setRow1(indexFila); //Creates a picture Picture pict = drawing.createPicture(anchor, pictureIdx); //Reset the image to the original size pict.resize(0.4); } Row row1 = sheet.createRow(indexFila); row1.setHeightInPoints(80f); Cell cellColFecha = row1.createCell(3); if (cp.getFecha() != null) { cellColFecha.setCellValue(cp.getFecha()); cellColFecha.setCellStyle(styleFecha); } else { cellColFecha.setCellValue(""); cellColFecha.setCellStyle(styleFecha); } Cell cellColFechaCarga = row1.createCell(4); if (cp.getFechaCarga() != null) { cellColFechaCarga.setCellValue(cp.getFechaCarga()); cellColFechaCarga.setCellStyle(styleFecha); } else { cellColFechaCarga.setCellValue(""); cellColFechaCarga.setCellStyle(styleFecha); } Cell cellCol1 = row1.createCell(5); cellCol1.setCellValue(cp.getProducto()); cellCol1.setCellStyle(style); Cell cellCol2 = row1.createCell(6); cellCol2.setCellValue(cp.getCodigo()); cellCol2.setCellStyle(styleNumero); Cell cellCol3 = row1.createCell(7); cellCol3.setCellValue(cp.getDescripcion()); cellCol3.setCellStyle(style); Cell cellCol4 = row1.createCell(8); cellCol4.setCellValue(cp.isEsRegalo() ? "SI" : "NO"); cellCol4.setCellStyle(styleCenter); Cell cellCol5 = row1.createCell(9); cellCol5.setCellValue(cp.getFamilia()); cellCol5.setCellStyle(style); Cell cellCol6 = row1.createCell(10); cellCol6.setCellValue(cp.getUbicaciones()); cellCol6.setCellStyle(style); Cell cellCol7 = row1.createCell(11); cellCol7.setCellValue(cp.getStock()); cellCol7.setCellStyle(styleNumero); i++; } sheet.setColumnWidth(1, 4000); sheet.setColumnWidth(2, 0); sheet.setColumnWidth(3, 4000); sheet.setColumnWidth(4, 4000); sheet.setColumnWidth(5, 10000); sheet.setColumnWidth(6, 3000); sheet.setColumnWidth(7, 10000); sheet.setColumnWidth(8, 3500); sheet.setColumnWidth(9, 6000); sheet.setColumnWidth(10, 10000); sheet.setColumnWidth(11, 2000); return wb; }
From source file:com.ipcglobal.fredimport.xls.BaseXls.java
License:Apache License
/** * Find cell style./*w ww .j a v a 2 s. c om*/ * * @param fontName the font name * @param fontColor the font color * @param fontHeight the font height * @param fontWeight the font weight * @param alignHorz the align horz * @param alignVert the align vert * @param bgColor the bg color * @param cellBorder the cell border * @param dataFormat the data format * @return the cell style * @throws Exception the exception */ public CellStyle findCellStyle(String fontName, short fontColor, short fontHeight, short fontWeight, short alignHorz, short alignVert, short bgColor, CellBorder cellBorder, short dataFormat) throws Exception { String keyStyle = new StringBuffer().append(fontName).append("|").append(fontColor).append("|") .append(fontHeight).append("|").append(fontWeight).append("|").append(alignHorz).append("|") .append(alignVert).append("|").append(bgColor).append("|").append(cellBorder).append("|") .append(dataFormat).append("|").toString(); CellStyle cellStyle = cellStyles.get(keyStyle); if (cellStyle == null) { String keyFont = new StringBuffer().append(fontName).append("|").append(fontColor).append("|") .append(fontHeight).append("|").append(fontWeight).append("|").toString(); Font font = fonts.get(keyFont); if (font == null) { font = wb.createFont(); fonts.put(keyFont, font); font.setFontName(fontName); font.setFontHeightInPoints(fontHeight); font.setBoldweight(fontWeight); font.setColor(fontColor); } cellStyle = wb.createCellStyle(); cellStyles.put(keyStyle, cellStyle); cellStyle.setWrapText(true); cellStyle.setFont(font); if (bgColor != BG_COLOR_NONE) { cellStyle.setFillForegroundColor(bgColor); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); } if (alignHorz != -1) cellStyle.setAlignment(alignHorz); if (alignVert != -1) cellStyle.setVerticalAlignment(alignVert); if (dataFormat != -1) { cellStyle.setDataFormat(dataFormat); } if (cellBorder != null) addBorderToStyle(cellStyle, cellBorder); } return cellStyle; }
From source file:com.ipcglobal.fredimport.xls.DistinctCategoriesSpreadsheet.java
License:Apache License
/** * Creates the sheet./* w w w. ja va 2 s .c o m*/ * * @param distinctCategoryItems the distinct category items * @throws Exception the exception */ public void createSheet(Collection<DistinctCategoryItem> distinctCategoryItems) throws Exception { List<XlsDefItem> xlsDefItems = initHhdrWidthItems(); String sheetName = "DistinctCategoryItems"; Sheet sheet = wb.createSheet(sheetName); processColumnWidths(sheet, xlsDefItems); sheet.createFreezePane(0, 1, 0, 1); // freeze top row sheet.setAutoFilter(CellRangeAddress.valueOf("A1:AB1")); // hack - i know the number of columns sheet.getPrintSetup().setLandscape(true); sheet.setAutobreaks(true); sheet.getPrintSetup().setFitWidth((short) 1); sheet.getPrintSetup().setFitHeight((short) 1); int rowCnt = 0; // Header int colCnt = 0; Row rowHdr = sheet.createRow(rowCnt); for (XlsDefItem xlsDefItem : xlsDefItems) { Cell cellHdr = rowHdr.createCell(colCnt, Cell.CELL_TYPE_STRING); CellStyle style = findCellStyle("Arial", HSSFColor.WHITE.index, (short) 11, XSSFFont.BOLDWEIGHT_BOLD, cellStyleFromHdrAlign(HdrAlign.Left), XSSFCellStyle.VERTICAL_TOP, HSSFColor.LIGHT_BLUE.index, CellBorder.All_Thin, formatGeneral); style.setWrapText(true); cellHdr.setCellStyle(style); cellHdr.setCellValue(xlsDefItem.getName()); colCnt++; } rowCnt++; // Data for (DistinctCategoryItem distinctCategoryItem : distinctCategoryItems) { Row rowData = sheet.createRow(rowCnt); int colNum = 0; for (XlsDefItem xlsDefItem : xlsDefItems) populateCell(rowData, colNum++, xlsDefItem.getDataType(), getByNameAsString(distinctCategoryItem, xlsDefItem.getName())); rowCnt++; } }
From source file:com.jeans.iservlet.action.asset.AssetExportAction.java
private void appendRow(Sheet sheet, Asset asset, int rowNumber) { // /* w ww. j a v a 2 s. co m*/ DataFormat df = sheet.getWorkbook().createDataFormat(); // ?10? Font font = sheet.getWorkbook().createFont(); font.setFontName(""); font.setFontHeightInPoints((short) 10); // ?1??????? CellStyle cellStyleString = sheet.getWorkbook().createCellStyle(); cellStyleString.setAlignment(CellStyle.ALIGN_CENTER); cellStyleString.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleString.setFont(font); cellStyleString.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); cellStyleString.setWrapText(false); // ?2????(yyyyMM)??? CellStyle cellStyleDate = sheet.getWorkbook().createCellStyle(); cellStyleDate.setAlignment(CellStyle.ALIGN_CENTER); cellStyleDate.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleDate.setFont(font); cellStyleDate.setDataFormat(df.getFormat("yyyyMM")); cellStyleDate.setWrapText(false); // ?3??????(#)??? CellStyle cellStyleQuantity = sheet.getWorkbook().createCellStyle(); cellStyleQuantity.setAlignment(CellStyle.ALIGN_RIGHT); cellStyleQuantity.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleQuantity.setFont(font); cellStyleQuantity.setDataFormat(df.getFormat("#")); cellStyleQuantity.setWrapText(false); // ?4?????(#,##0.00_ )??? CellStyle cellStyleCost = sheet.getWorkbook().createCellStyle(); cellStyleCost.setAlignment(CellStyle.ALIGN_RIGHT); cellStyleCost.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleCost.setFont(font); cellStyleCost.setDataFormat(df.getFormat("#,##0.00_ ")); cellStyleCost.setWrapText(false); // 20 Row row = sheet.createRow(rowNumber); row.setHeightInPoints(20); Cell cell = null; if (asset instanceof Hardware) { cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getCode()); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getFinancialCode()); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); Company company = asset.getCompany(); if (null != company) { cell.setCellValue(company.getAlias()); } cell = row.createCell(3, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getAssetCatalogName(asset.getCatalog())); cell = row.createCell(4, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getName()); cell = row.createCell(5, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getVendor()); cell = row.createCell(6, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getModelOrVersion()); cell = row.createCell(7, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getAssetUsage()); cell = row.createCell(8, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getSn()); cell = row.createCell(9, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getConfiguration()); cell = row.createCell(10, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleDate); Date pt = asset.getPurchaseTime(); if (null != pt) { cell.setCellValue(pt); } cell = row.createCell(11, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleQuantity); cell.setCellValue(asset.getQuantity()); cell = row.createCell(12, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleCost); cell.setCellValue(asset.getCost().doubleValue()); cell = row.createCell(13, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getAssetStateName(asset.getState())); cell = row.createCell(14, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getHardwareWarrantyName(((Hardware) asset).getWarranty())); cell = row.createCell(15, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getLocation()); cell = row.createCell(16, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getIp()); cell = row.createCell(17, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getHardwareImportanceName(((Hardware) asset).getImportance())); cell = row.createCell(18, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); Employee owner = ((Hardware) asset).getOwner(); if (null != owner) { cell.setCellValue(owner.getName()); } cell = row.createCell(19, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getComment()); } else if (asset instanceof Software) { cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); Company company = asset.getCompany(); if (null != company) { cell.setCellValue(company.getAlias()); } cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getAssetCatalogName(asset.getCatalog())); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getName()); cell = row.createCell(3, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getVendor()); cell = row.createCell(4, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getModelOrVersion()); cell = row.createCell(5, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getAssetUsage()); cell = row.createCell(6, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleDate); Date pt = asset.getPurchaseTime(); if (null != pt) { cell.setCellValue(pt); } cell = row.createCell(7, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleQuantity); cell.setCellValue(asset.getQuantity()); cell = row.createCell(8, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleCost); cell.setCellValue(asset.getCost().doubleValue()); cell = row.createCell(9, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getAssetStateName(asset.getState())); cell = row.createCell(10, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getSoftwareTypeName(((Software) asset).getSoftwareType())); cell = row.createCell(11, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Software) asset).getLicense()); cell = row.createCell(12, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleDate); Date et = ((Software) asset).getExpiredTime(); if (null != et) { cell.setCellValue(et); } cell = row.createCell(13, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getComment()); } }
From source file:com.jeans.iservlet.action.asset.AssetExportAction.java
private void generateSheetHeader(Sheet sheet, boolean hardware) { // //from ww w . j a v a2 s. co m // ?10?? Font font = sheet.getWorkbook().createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontName(""); font.setFontHeightInPoints((short) 10); // ????????? CellStyle cellStyle = sheet.getWorkbook().createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyle.setFont(font); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); cellStyle.setWrapText(false); // 20 Row row = sheet.createRow(0); row.setHeightInPoints(20); Cell cell = null; if (hardware) { for (int i = 0; i < 20; i++) { cell = row.createCell(i, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyle); cell.setCellValue(HARDWARE_HEADERS[i]); sheet.setColumnWidth(i, HARDWARE_HEADERS_WIDTH[i] * 256); } } else { for (int i = 0; i < 14; i++) { cell = row.createCell(i, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyle); cell.setCellValue(SOFTWARE_HEADERS[i]); sheet.setColumnWidth(i, SOFTWARE_HEADERS_WIDTH[i] * 256); } } }
From source file:com.jeans.iservlet.controller.impl.ExportController.java
private void generateSheet(Sheet sheet, byte type, Company company) { // sheet//from w w w .ja v a 2s . c om if (type == AssetConstants.HARDWARE_ASSET) { // generateSheetHeader(sheet, true); } else if (type == AssetConstants.SOFTWARE_ASSET) { // generateSheetHeader(sheet, false); } else if (type >= AssetConstants.NETWORK_EQUIPMENT && type <= AssetConstants.OTHER_EQUIPMENT) { // ? generateSheetHeader(sheet, true); } else if (type >= AssetConstants.OPERATING_SYSTEM_SOFTWARE && type <= AssetConstants.OTHER_SOFTWARE) { // ? generateSheetHeader(sheet, false); } List<Asset> assets = astService.listAssets(company, type); DataFormat df = sheet.getWorkbook().createDataFormat(); // ?10? Font font = sheet.getWorkbook().createFont(); font.setFontName(""); font.setFontHeightInPoints((short) 10); // ?1??????? CellStyle cellStyleString = sheet.getWorkbook().createCellStyle(); cellStyleString.setAlignment(CellStyle.ALIGN_CENTER); cellStyleString.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleString.setFont(font); cellStyleString.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); cellStyleString.setWrapText(false); // ?2????(yyyyMM)??? CellStyle cellStyleDate = sheet.getWorkbook().createCellStyle(); cellStyleDate.setAlignment(CellStyle.ALIGN_CENTER); cellStyleDate.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleDate.setFont(font); cellStyleDate.setDataFormat(df.getFormat("yyyyMM")); cellStyleDate.setWrapText(false); // ?3??????(#)??? CellStyle cellStyleQuantity = sheet.getWorkbook().createCellStyle(); cellStyleQuantity.setAlignment(CellStyle.ALIGN_RIGHT); cellStyleQuantity.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleQuantity.setFont(font); cellStyleQuantity.setDataFormat(df.getFormat("#")); cellStyleQuantity.setWrapText(false); // ?4?????(#,##0.00_ )??? CellStyle cellStyleCost = sheet.getWorkbook().createCellStyle(); cellStyleCost.setAlignment(CellStyle.ALIGN_RIGHT); cellStyleCost.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleCost.setFont(font); cellStyleCost.setDataFormat(df.getFormat("#,##0.00_ ")); cellStyleCost.setWrapText(false); int rowNumber = 1; for (Asset asset : assets) { appendRow(sheet, asset, rowNumber++, cellStyleString, cellStyleDate, cellStyleQuantity, cellStyleCost); } }
From source file:com.jeans.iservlet.controller.impl.ExportController.java
/** * ?????//from ww w. ja va2 s . c o m * * @param storedOnly * ??? * @return * @throws IOException */ @RequestMapping(method = RequestMethod.POST, value = "/accessories") public ResponseEntity<byte[]> exportAccessories(@RequestParam boolean storedOnly) throws IOException { StringBuilder fn = new StringBuilder(getCurrentCompany().getName()); Date n = new Date(); String today = (new SimpleDateFormat("yyyyMMdd")).format(n); String now = (new SimpleDateFormat("yyyy-MM-dd HHmmss")).format(n); Workbook wb = new XSSFWorkbook(); fn.append(" - ???(").append(today).append(").xlsx"); Sheet sheet = wb.createSheet(now); // // ?10?? Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontName(""); font.setFontHeightInPoints((short) 10); // ????????? CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyle.setFont(font); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); cellStyle.setWrapText(false); // 20 Row row = sheet.createRow(0); row.setHeightInPoints(20); Cell cell = null; for (int i = 0; i < 7; i++) { cell = row.createCell(i, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyle); cell.setCellValue(ACS_HEADERS[i]); sheet.setColumnWidth(i, ACS_HEADERS_WIDTH[i] * 256); } List<Accessory> acs = acsService.listAccessories(getCurrentCompany(), storedOnly); Collections.sort(acs, new Comparator<Accessory>() { @Override public int compare(Accessory o1, Accessory o2) { int ret = o1.getType().compareTo(o2.getType()); if (ret == 0) { ret = Collator.getInstance(java.util.Locale.CHINA).compare(o1.getName(), o2.getName()); if (ret == 0) { ret = Collator.getInstance(java.util.Locale.CHINA).compare(o1.getBrand(), o2.getBrand()); if (ret == 0) { ret = Collator.getInstance(java.util.Locale.CHINA).compare(o1.getModel(), o2.getModel()); } } } return ret; } }); // DataFormat df = wb.createDataFormat(); // ?10? Font dFont = wb.createFont(); dFont.setFontName(""); dFont.setFontHeightInPoints((short) 10); // ?1??????? CellStyle cellStyleString = wb.createCellStyle(); cellStyleString.setAlignment(CellStyle.ALIGN_CENTER); cellStyleString.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleString.setFont(dFont); cellStyleString.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); cellStyleString.setWrapText(false); // ?2??????(#)??? CellStyle cellStyleQuantity = sheet.getWorkbook().createCellStyle(); cellStyleQuantity.setAlignment(CellStyle.ALIGN_RIGHT); cellStyleQuantity.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleQuantity.setFont(dFont); cellStyleQuantity.setDataFormat(df.getFormat("#")); cellStyleQuantity.setWrapText(false); int rowNumber = 1; for (Accessory ac : acs) { // 20 Row dRow = sheet.createRow(rowNumber); dRow.setHeightInPoints(20); Cell dCell = null; dCell = dRow.createCell(0, Cell.CELL_TYPE_STRING); dCell.setCellStyle(cellStyleString); dCell.setCellValue(ac.getType().getTitle()); dCell = dRow.createCell(1, Cell.CELL_TYPE_STRING); dCell.setCellStyle(cellStyleString); dCell.setCellValue(ac.getName()); dCell = dRow.createCell(2, Cell.CELL_TYPE_STRING); dCell.setCellStyle(cellStyleString); dCell.setCellValue(ac.getBrand()); dCell = dRow.createCell(3, Cell.CELL_TYPE_STRING); dCell.setCellStyle(cellStyleString); dCell.setCellValue(ac.getModel()); dCell = dRow.createCell(4, Cell.CELL_TYPE_NUMERIC); dCell.setCellStyle(cellStyleQuantity); dCell.setCellValue(null == ac.getStorage() ? 0 : ac.getStorage().getQuantity()); dCell = dRow.createCell(5, Cell.CELL_TYPE_STRING); dCell.setCellStyle(cellStyleString); dCell.setCellValue(ac.getUnit()); dCell = dRow.createCell(6, Cell.CELL_TYPE_STRING); dCell.setCellStyle(cellStyleString); dCell.setCellValue(ac.getDescription()); rowNumber++; } String filename = null; if (isIE()) { filename = URLEncoder.encode(fn.toString(), "UTF-8").replaceAll("\\+", "%20"); } else { filename = new String(fn.toString().getBytes("UTF-8"), "iso8859-1"); } response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setHeader("Content-disposition", "attachment; filename=" + filename); BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream(), 4096); wb.write(out); wb.close(); out.close(); return null; }