List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setDataFormat
@Override public void setDataFormat(short fmt)
From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java
License:Apache License
@Override public void apply() { for (DataObject dataObject : data) { HSSFCell templateCell = dataObject.templateCell; HSSFCell resultCell = dataObject.resultCell; BandData bandData = dataObject.bandData; HSSFWorkbook resultWorkbook = resultCell.getSheet().getWorkbook(); HSSFWorkbook templateWorkbook = templateCell.getSheet().getWorkbook(); String templateCellValue = templateCell.getStringCellValue(); Matcher matcher = pattern.matcher(templateCellValue); if (matcher.find()) { String paramName = matcher.group(1); String styleName = (String) bandData.getParameterValue(paramName); if (styleName == null) continue; HSSFCellStyle cellStyle = styleCache.getStyleByName(styleName); if (cellStyle == null) continue; HSSFCellStyle resultStyle = styleCache.getNamedCachedStyle(cellStyle); if (resultStyle == null) { HSSFCellStyle newStyle = resultWorkbook.createCellStyle(); // color newStyle.setFillBackgroundColor(cellStyle.getFillBackgroundColor()); newStyle.setFillForegroundColor(cellStyle.getFillForegroundColor()); newStyle.setFillPattern(cellStyle.getFillPattern()); // borders newStyle.setBorderLeft(cellStyle.getBorderLeft()); newStyle.setBorderRight(cellStyle.getBorderRight()); newStyle.setBorderTop(cellStyle.getBorderTop()); newStyle.setBorderBottom(cellStyle.getBorderBottom()); // border colors newStyle.setLeftBorderColor(cellStyle.getLeftBorderColor()); newStyle.setRightBorderColor(cellStyle.getRightBorderColor()); newStyle.setBottomBorderColor(cellStyle.getBottomBorderColor()); newStyle.setTopBorderColor(cellStyle.getTopBorderColor()); // alignment newStyle.setAlignment(cellStyle.getAlignment()); newStyle.setVerticalAlignment(cellStyle.getVerticalAlignment()); // misc DataFormat dataFormat = resultWorkbook.getCreationHelper().createDataFormat(); newStyle.setDataFormat(dataFormat.getFormat(cellStyle.getDataFormatString())); newStyle.setHidden(cellStyle.getHidden()); newStyle.setLocked(cellStyle.getLocked()); newStyle.setIndention(cellStyle.getIndention()); newStyle.setRotation(cellStyle.getRotation()); newStyle.setWrapText(cellStyle.getWrapText()); // font HSSFFont cellFont = cellStyle.getFont(templateWorkbook); HSSFFont newFont = fontCache.getFontByTemplate(cellFont); if (newFont == null) { newFont = resultWorkbook.createFont(); newFont.setFontName(cellFont.getFontName()); newFont.setItalic(cellFont.getItalic()); newFont.setStrikeout(cellFont.getStrikeout()); newFont.setTypeOffset(cellFont.getTypeOffset()); newFont.setBoldweight(cellFont.getBoldweight()); newFont.setCharSet(cellFont.getCharSet()); newFont.setColor(cellFont.getColor()); newFont.setUnderline(cellFont.getUnderline()); newFont.setFontHeight(cellFont.getFontHeight()); newFont.setFontHeightInPoints(cellFont.getFontHeightInPoints()); fontCache.addCachedFont(cellFont, newFont); }// ww w. ja v a 2 s . com newStyle.setFont(newFont); resultStyle = newStyle; styleCache.addCachedNamedStyle(cellStyle, resultStyle); } fixNeighbourCellBorders(cellStyle, resultCell); resultCell.setCellStyle(resultStyle); Sheet sheet = resultCell.getSheet(); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) { int firstRow = mergedRegion.getFirstRow(); int lastRow = mergedRegion.getLastRow(); int firstCol = mergedRegion.getFirstColumn(); int lastCol = mergedRegion.getLastColumn(); for (int row = firstRow; row <= lastRow; row++) for (int col = firstCol; col <= lastCol; col++) sheet.getRow(row).getCell(col).setCellStyle(resultStyle); // cell includes only in one merged region break; } } } } }
From source file:com.kiwisoft.db.export.ExcelExporter.java
License:Open Source License
public void exportTable(JTable table, SQLStatement statement, File file, ExportConfiguration configuration) throws Exception { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Query"); TableColumnModel columnModel = table.getColumnModel(); ResultSetTableModel tableModel = (ResultSetTableModel) table.getModel(); HSSFFont headerFont = workbook.createFont(); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle headerStyle = workbook.createCellStyle(); headerStyle.setFont(headerFont);// ww w .j av a 2 s . com headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); int rowNum = 0; HSSFRow row = sheet.createRow(rowNum++); sheet.createFreezePane(0, 1); int columnCount = columnModel.getColumnCount(); for (short i = 0; i < columnCount; i++) { TableColumn column = columnModel.getColumn(i); int columnIndex = column.getModelIndex(); HSSFCell cell = row.createCell(i); cell.setCellStyle(headerStyle); String columnName = tableModel.getColumnName(columnIndex); cell.setCellValue(columnName); } for (int j = 0; j < tableModel.getRowCount(); j++) { row = sheet.createRow(rowNum++); for (short i = 0; i < columnCount; i++) { TableColumn column = columnModel.getColumn(i); int columnIndex = column.getModelIndex(); Object cellValue = tableModel.getValueAt(j, columnIndex); HSSFCell cell = row.createCell(i); if (cellValue instanceof Number) cell.setCellValue(((Number) cellValue).doubleValue()); else if (cellValue instanceof Date) { HSSFCellStyle style = workbook.createCellStyle(); style.setDataFormat((short) 14); cell.setCellValue((Date) cellValue); cell.setCellStyle(style); } else if (cellValue instanceof Boolean) cell.setCellValue(((Boolean) cellValue).booleanValue()); else if (cellValue != null) cell.setCellValue(cellValue.toString()); } } FileOutputStream out = new FileOutputStream(file); workbook.write(out); Field field = ClassLoader.class.getDeclaredField("classes"); field.setAccessible(true); Vector classes = (Vector) field.get(HSSFWorkbook.class.getClassLoader()); field.setAccessible(false); System.out.println("classes = " + StringUtils.enumerate(classes, "\n")); out.close(); }
From source file:com.learn.core.utils.HSSFReadWrite.java
License:Apache License
/** * given a filename this outputs a sample sheet with just a set of * rows/cells.//from w w w .j a va2 s . com */ private static void testCreateSampleSheet(String outputFilename) throws IOException { try (HSSFWorkbook wb = new HSSFWorkbook()) { HSSFSheet s = wb.createSheet(); HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); f.setFontHeightInPoints((short) 12); f.setColor((short) 0xA); f.setBold(true); f2.setFontHeightInPoints((short) 10); f2.setColor((short) 0xf); f2.setBold(true); cs.setFont(f); cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); cs2.setBorderBottom(BorderStyle.THIN); cs2.setFillPattern(FillPatternType.SOLID_FOREGROUND); cs2.setFillForegroundColor((short) 0xA); cs2.setFont(f2); wb.setSheetName(0, "HSSF Test"); int rownum; for (rownum = 0; rownum < 300; rownum++) { HSSFRow r = s.createRow(rownum); if ((rownum % 2) == 0) { r.setHeight((short) 0x249); } for (int cellnum = 0; cellnum < 50; cellnum += 2) { HSSFCell c = r.createCell(cellnum); c.setCellValue( rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); if ((rownum % 2) == 0) { c.setCellStyle(cs); } c = r.createCell(cellnum + 1); c.setCellValue(new HSSFRichTextString("TEST")); // 50 characters divided by 1/20th of a point s.setColumnWidth(cellnum + 1, (int) (50 * 8 / 0.05)); if ((rownum % 2) == 0) { c.setCellStyle(cs2); } } } // draw a thick black border on the row at the bottom using BLANKS rownum++; rownum++; HSSFRow r = s.createRow(rownum); cs3.setBorderBottom(BorderStyle.THICK); for (int cellnum = 0; cellnum < 50; cellnum++) { HSSFCell c = r.createCell(cellnum); c.setCellStyle(cs3); } s.addMergedRegion(new CellRangeAddress(0, 3, 0, 3)); s.addMergedRegion(new CellRangeAddress(100, 110, 100, 110)); // end draw thick black border // create a sheet, set its title then delete it wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); // end deleted sheet try (FileOutputStream out = new FileOutputStream(outputFilename)) { wb.write(out); } } }
From source file:com.mss.mirage.marketing.MarketingServiceImpl.java
License:Open Source License
public String generateInvestmentXls(String queryString) throws ServiceLocatorException { String filePath = ""; StringBuffer sb = null;//from w w w. jav a 2s . c o m Connection connection = null; /** * preStmt,preStmtTemp are reference variable for PreparedStatement . */ PreparedStatement preStmt = null; /** * The queryString is useful to get queryString result to the particular * jsp page */ /** * The statement is useful to execute the above queryString */ ResultSet resultSet = null; HashMap map = null; double totalAmount = 0.0; double totalOpprtunity = 0.0; double floortotalsum = 0.0; String generatedPath = ""; List finalList = new ArrayList(); try { generatedPath = com.mss.mirage.util.Properties.getProperty("Marketing.Investment.Path"); File file = new File(generatedPath); if (!file.exists()) { file.mkdirs(); } FileOutputStream fileOut = new FileOutputStream(file.getAbsolutePath() + "/Investment.xls"); connection = ConnectionProvider.getInstance().getConnection(); String query = null; if (!"".equals(queryString)) { query = queryString; } else { query = "SELECT * from vwInvestments WHERE STATUS='Active' ORDER BY createdDate DESC"; } String reportToName = ""; List teamList = null; int j = 1; // System.out.println("query...."+query); preStmt = connection.prepareStatement(query); resultSet = preStmt.executeQuery(); while (resultSet.next()) { String InvestmentName = resultSet.getString("Inv_Name"); String TotalExpenses = resultSet.getString("TotalExpenses"); String StartDate = resultSet.getString("StartDate"); String EndDate = resultSet.getString("EndDate"); String Location = resultSet.getString("Location"); String InvestmentType = resultSet.getString("InvestmentType"); String TotalOpprtunity = resultSet.getString("TotalOpprtunity"); totalAmount = totalAmount + resultSet.getDouble("TotalExpenses"); totalOpprtunity = totalOpprtunity + resultSet.getDouble("TotalOpprtunity"); map = new HashMap(); map.put("SNO", String.valueOf(j)); map.put("InvestmentName", InvestmentName); map.put("TotalExpenses", TotalExpenses); map.put("StartDate", StartDate); map.put("EndDate", EndDate); map.put("Location", Location); if ("S".equalsIgnoreCase(InvestmentType)) { map.put("InvestmentType", "Lead Source"); } else if ("P".equalsIgnoreCase(InvestmentType)) { map.put("InvestmentType", "Lead Pass"); } map.put("TotalOpprtunity", TotalOpprtunity); map.put("Sum", totalAmount); map.put("SumOpp", totalOpprtunity); finalList.add(map); j++; } if (finalList.size() > 0) { filePath = file.getAbsolutePath() + "/Investment.xls"; HSSFWorkbook hssfworkbook = new HSSFWorkbook(); HSSFSheet sheet = hssfworkbook.createSheet("Investment Sheet"); HSSFFont timesBoldFont1 = hssfworkbook.createFont(); timesBoldFont1.setFontHeightInPoints((short) 13); timesBoldFont1.setColor(HSSFColor.BLACK.index); timesBoldFont1.setFontName("Arial"); HSSFCellStyle cellColor = hssfworkbook.createCellStyle(); cellColor.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); cellColor.setAlignment(HSSFCellStyle.ALIGN_LEFT); cellColor.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellColor.setBorderTop((short) 1); // single line border cellColor.setBorderBottom((short) 1); // single line border cellColor.setFont(timesBoldFont1); HSSFCellStyle cellColor1 = hssfworkbook.createCellStyle(); cellColor1.setFillForegroundColor(HSSFColor.WHITE.index); cellColor1.setAlignment(HSSFCellStyle.ALIGN_LEFT); cellColor1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellColor1.setBorderTop((short) 1); // single line border cellColor1.setBorderBottom((short) 1); // single line border cellColor1.setFont(timesBoldFont1); HSSFCellStyle cs = hssfworkbook.createCellStyle(); HSSFCellStyle headercs = hssfworkbook.createCellStyle(); headercs.setFillForegroundColor(HSSFColor.BLUE.index); headercs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headercs.setBorderTop((short) 1); // single line border headercs.setBorderBottom((short) 1); // single line border // cs.setFont(timesBoldFont1); HSSFFont timesBoldFont = hssfworkbook.createFont(); timesBoldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); timesBoldFont.setFontHeightInPoints((short) 13); timesBoldFont.setColor(HSSFColor.WHITE.index); timesBoldFont.setFontName("Calibri"); headercs.setFont(timesBoldFont); // cs.setFont(timesBoldFont); HSSFFont footerFont = hssfworkbook.createFont(); footerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); timesBoldFont.setFontHeightInPoints((short) 13); footerFont.setFontName("Calibri"); HSSFCellStyle footercs = hssfworkbook.createCellStyle(); footercs.setFont(footerFont); HSSFDataFormat df = hssfworkbook.createDataFormat(); HSSFRow row = sheet.createRow((short) 0); HSSFCell cell = row.createCell((short) 0); HSSFCell cell1 = row.createCell((short) 1); HSSFCell cell2 = row.createCell((short) 2); HSSFCell cell3 = row.createCell((short) 3); HSSFCell cell4 = row.createCell((short) 4); HSSFCell cell5 = row.createCell((short) 5); HSSFCell cell6 = row.createCell((short) 6); HSSFCell cell7 = row.createCell((short) 7); cell.setCellValue("SNO"); cell1.setCellValue("InvestmentName"); cell2.setCellValue("TotalExpenses"); cell3.setCellValue("StartDate"); cell4.setCellValue("EndDate"); cell5.setCellValue("Location"); cell6.setCellValue("InvestmentType"); cell7.setCellValue("TotalOpprtunity"); cell.setCellStyle(headercs); cell1.setCellStyle(headercs); cell2.setCellStyle(headercs); cell3.setCellStyle(headercs); cell4.setCellStyle(headercs); cell5.setCellStyle(headercs); cell6.setCellStyle(headercs); cell7.setCellStyle(headercs); int count = 1; if (finalList.size() > 0) { Map stateHistorylMap = null; for (int i = 0; i < finalList.size(); i++) { stateHistorylMap = (Map) finalList.get(i); row = sheet.createRow((short) count++); cell = row.createCell((short) 0); cell1 = row.createCell((short) 1); cell2 = row.createCell((short) 2); cell3 = row.createCell((short) 3); cell4 = row.createCell((short) 4); cell5 = row.createCell((short) 5); cell6 = row.createCell((short) 6); cell7 = row.createCell((short) 7); cell.setCellValue((String) stateHistorylMap.get("SNO")); cell1.setCellValue((String) stateHistorylMap.get("InvestmentName")); HSSFCellStyle css1 = hssfworkbook.createCellStyle(); HSSFCellStyle css2 = hssfworkbook.createCellStyle(); css1.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); css1.setAlignment(HSSFCellStyle.ALIGN_LEFT); css1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); css1.setBorderTop((short) 1); // single line border css1.setBorderBottom((short) 1); // single line border css1.setFont(timesBoldFont1); HSSFDataFormat df1 = hssfworkbook.createDataFormat(); css1.setDataFormat(df1.getFormat("#,##0.0")); css2.setDataFormat(df1.getFormat("#,##0.0")); css2.setAlignment(HSSFCellStyle.ALIGN_LEFT); css2.setFont(timesBoldFont1); cell2.setCellValue(Convert.toDouble(stateHistorylMap.get("TotalExpenses"))); cell3.setCellValue((String) stateHistorylMap.get("StartDate")); cell4.setCellValue((String) stateHistorylMap.get("EndDate")); cell5.setCellValue((String) stateHistorylMap.get("Location")); cell6.setCellValue((String) stateHistorylMap.get("InvestmentType")); cell7.setCellValue((String) stateHistorylMap.get("TotalOpprtunity")); if (count % 2 == 0) { cell.setCellStyle(cellColor1); cell1.setCellStyle(cellColor1); cell2.setCellStyle(css2); cell3.setCellStyle(cellColor1); cell4.setCellStyle(cellColor1); cell5.setCellStyle(cellColor1); cell6.setCellStyle(cellColor1); cell7.setCellStyle(cellColor1); } else { cell.setCellStyle(cellColor); cell1.setCellStyle(cellColor); cell2.setCellStyle(css1); cell3.setCellStyle(cellColor); cell4.setCellStyle(cellColor); cell5.setCellStyle(cellColor); cell6.setCellStyle(cellColor); cell7.setCellStyle(cellColor); } } row = sheet.createRow((short) count++); cell = row.createCell((short) 0); cell1 = row.createCell((short) 1); cell2 = row.createCell((short) 2); cell3 = row.createCell((short) 3); cell4 = row.createCell((short) 4); cell5 = row.createCell((short) 5); cell6 = row.createCell((short) 6); cell7 = row.createCell((short) 7); cell.setCellValue(""); cell7.setCellValue(""); cell.setCellStyle(footercs); cell1.setCellStyle(footercs); cell2.setCellStyle(footercs); cell3.setCellStyle(footercs); cell4.setCellStyle(footercs); cell5.setCellStyle(footercs); cell6.setCellStyle(footercs); cell7.setCellStyle(footercs); } HSSFCellStyle totalSum = hssfworkbook.createCellStyle(); totalSum.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); totalSum.setAlignment(HSSFCellStyle.ALIGN_LEFT); totalSum.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); totalSum.setBorderTop((short) 1); // single line border totalSum.setBorderBottom((short) 1); // single line border totalSum.setFont(timesBoldFont1); HSSFCellStyle totalSum1 = hssfworkbook.createCellStyle(); totalSum1.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); totalSum1.setAlignment(HSSFCellStyle.ALIGN_RIGHT); totalSum1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); totalSum1.setBorderTop((short) 1); // single line border totalSum1.setBorderBottom((short) 1); // single line border totalSum1.setFont(timesBoldFont1); HSSFDataFormat totalSumdf1 = hssfworkbook.createDataFormat(); totalSum.setDataFormat((short) 7); HSSFCellStyle test = hssfworkbook.createCellStyle(); HSSFDataFormat testdf = hssfworkbook.createDataFormat(); sheet.autoSizeColumn((int) 0); sheet.autoSizeColumn((int) 3); sheet.autoSizeColumn((int) 4); sheet.setColumnWidth(1, 50 * 256); sheet.setColumnWidth(2, 35 * 256); sheet.setColumnWidth(5, 25 * 256); sheet.setColumnWidth(6, 25 * 256); sheet.setColumnWidth(7, 25 * 256); BigDecimal bb, bc, cc, cd; bb = new BigDecimal(totalAmount); bc = bb.setScale(2, RoundingMode.CEILING); cc = new BigDecimal(totalOpprtunity); cd = cc.setScale(2, RoundingMode.CEILING); totalSum.setDataFormat(testdf.getFormat("#,##0.0")); cell.setCellStyle(totalSum); cell1.setCellValue("Sum "); cell1.setCellStyle(totalSum1); cell2.setCellValue(bc.longValue()); cell2.setCellStyle(totalSum); cell3.setCellStyle(totalSum); cell4.setCellStyle(totalSum); cell5.setCellStyle(totalSum); cell6.setCellStyle(totalSum); cell7.setCellValue(cd.longValue()); cell7.setCellStyle(totalSum); hssfworkbook.write(fileOut); fileOut.flush(); fileOut.close(); } } catch (FileNotFoundException fne) { fne.printStackTrace(); } catch (IOException ioe) { ioe.printStackTrace(); } catch (Exception ex) { ex.printStackTrace(); } finally { try { if (resultSet != null) { resultSet.close(); resultSet = null; } if (preStmt != null) { preStmt.close(); preStmt = null; } if (connection != null) { connection.close(); connection = null; } } catch (Exception se) { se.printStackTrace(); } } return filePath; }
From source file:com.openitech.util.HSSFWrapper.java
License:Apache License
public static final HSSFWorkbook getWorkbook(JTable source, boolean countRows) { HSSFWorkbook xls_workbook = new HSSFWorkbook(); HSSFSheet xls_sheet = xls_workbook.createSheet("Pregled podatkov"); HSSFPrintSetup xls_sheet_printsetup = xls_sheet.getPrintSetup(); xls_sheet_printsetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); xls_sheet_printsetup.setFitWidth((short) 1); TableColumnModel columnModel = source.getColumnModel(); Enumeration<TableColumn> columns = columnModel.getColumns(); HSSFRow xls_row = xls_sheet.createRow(0); short cell = 1; HSSFCellStyle xls_header_cell_style = xls_workbook.createCellStyle(); HSSFFont xls_header_font = xls_workbook.createFont(); xls_header_font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); xls_header_cell_style.setFont(xls_header_font); xls_header_cell_style.setAlignment(HSSFCellStyle.ALIGN_CENTER); xls_header_cell_style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); xls_header_cell_style.setFillForegroundColor(new HSSFColor.GREY_25_PERCENT().getIndex()); //xls_header_cell_style.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE); java.util.Map<String, HSSFCellStyle> cellStyles = new java.util.HashMap<String, HSSFCellStyle>(); HSSFDataFormat xls_data_format = xls_workbook.createDataFormat(); HSSFCellStyle xls_date_cell_style = xls_workbook.createCellStyle(); xls_date_cell_style.setDataFormat(xls_data_format.getFormat("d.m.yyyy")); cellStyles.put("d.m.yyyy", xls_date_cell_style); HSSFCellStyle xls_double_cell_style = xls_workbook.createCellStyle(); xls_double_cell_style.setDataFormat(xls_data_format.getFormat("#,##0.00")); cellStyles.put("#,##0.00", xls_double_cell_style); while (columns.hasMoreElements()) { TableColumn column = columns.nextElement(); HSSFCell xls_cell = xls_row.createCell(cell++); xls_cell.setCellValue(new HSSFRichTextString(column.getHeaderValue().toString())); xls_cell.setCellStyle(xls_header_cell_style); }/*from w w w.ja v a 2s . co m*/ TableModel tableModel = source.getModel(); DbTableModel dbTableModel = (tableModel instanceof DbTableModel) ? (DbTableModel) tableModel : null; Integer fetchSize = null; if (dbTableModel != null) { try { fetchSize = dbTableModel.getDataSource().getFetchSize(); dbTableModel.getDataSource().setFetchSize(2000); } catch (SQLException ex) { Logger.getLogger(HSSFWrapper.class.getName()).log(Level.WARNING, null, ex); fetchSize = null; } } short row = 1; JWProgressMonitor progress = new JWProgressMonitor((java.awt.Frame) null); progress.setTitle("Izvoz podatkov v Excel"); progress.setMax(tableModel.getRowCount()); progress.setVisible(true); try { while (row <= tableModel.getRowCount()) { xls_row = xls_sheet.createRow(row); cell = 0; HSSFCell xls_cell = xls_row.createCell(cell++); if (countRows) { xls_cell.setCellValue(new HSSFRichTextString(Short.toString(row))); } while (cell <= columnModel.getColumnCount()) { Object value = tableModel.getValueAt(source.convertRowIndexToModel(row - 1), source.convertColumnIndexToModel(cell - 1)); if (value != null) { if (value instanceof DbTableModel.ColumnDescriptor.ValueMethod) { DbTableModel.ColumnDescriptor.ValueMethod vm = (DbTableModel.ColumnDescriptor.ValueMethod) value; if (vm.getColumnNames().size() == 1) { java.util.List<Object> values = vm.getValues(); java.util.List<String> cellFormats = vm.getCellFormats(); for (String cellFormat : cellFormats) { if (cellFormat != null) { if (!cellStyles.containsKey(cellFormat)) { HSSFCellStyle xls_cell_style = xls_workbook.createCellStyle(); xls_cell_style.setDataFormat(xls_data_format.getFormat(cellFormat)); cellStyles.put(cellFormat, xls_cell_style); } } } Object vm_value = values.get(0); HSSFCellStyle xls_cell_style = cellFormats.get(0) == null ? null : cellStyles.get(cellFormats.get(0)); if (vm_value != null) { xls_cell = xls_row.createCell(cell); if (vm_value instanceof java.util.Date) { xls_cell.setCellValue((java.util.Date) vm_value); xls_cell.setCellStyle( xls_cell_style == null ? xls_date_cell_style : xls_cell_style); } else if (vm_value instanceof java.lang.Number) { xls_cell.setCellValue(((java.lang.Number) vm_value).doubleValue()); if ((vm_value instanceof java.math.BigDecimal) || (vm_value instanceof java.lang.Double) || (vm_value instanceof java.lang.Float)) { xls_cell.setCellStyle(xls_cell_style == null ? xls_double_cell_style : xls_cell_style); } } else if (vm_value instanceof java.lang.Boolean) { xls_cell.setCellValue(((java.lang.Boolean) vm_value).booleanValue()); } else { xls_cell.setCellValue(new HSSFRichTextString(value.toString())); } } } else { xls_cell = xls_row.createCell(cell); xls_cell.setCellValue(new HSSFRichTextString(value.toString())); } } else { xls_cell = xls_row.createCell(cell); xls_cell.setCellValue(new HSSFRichTextString(value.toString())); } } cell++; } row++; progress.next(); } for (cell = 0; cell <= columnModel.getColumnCount(); cell++) { xls_sheet.autoSizeColumn(cell); } xls_sheet.createFreezePane(1, 1); } finally { progress.setVisible(false); if (fetchSize != null) { try { dbTableModel.getDataSource().setFetchSize(fetchSize); } catch (SQLException ex) { Logger.getLogger(HSSFWrapper.class.getName()).log(Level.WARNING, null, ex); } } } return xls_workbook; }
From source file:com.project.jsica.cdi.ReporteBean.java
public void reporte2(List<ReportePermisoBean> reporte) { LOG.info("TAMAO reporte: " + reporte.size()); FacesContext fc = FacesContext.getCurrentInstance(); HttpServletResponse response = (HttpServletResponse) fc.getExternalContext().getResponse(); HSSFWorkbook libro = new HSSFWorkbook(); HSSFFont fuente = libro.createFont(); fuente.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle estiloCeldaCabecera = libro.createCellStyle(); estiloCeldaCabecera.setFont(fuente); estiloCeldaCabecera.setAlignment(HSSFCellStyle.ALIGN_CENTER); DataFormat format = libro.createDataFormat(); HSSFCellStyle style = libro.createCellStyle(); style.setDataFormat(format.getFormat("hh:mm:ss")); HSSFCellStyle fechas = libro.createCellStyle(); fechas.setDataFormat(format.getFormat("dd.MM.yyyy")); HSSFSheet hoja = libro.createSheet("hoja 1"); //CREAR LAS CABECERAS String[] cabeceras = { "CODIGO", "NOMBRE", "HORA INICIO", "HORA FIN", "HORAS", "MINUTOS", "FECHA", "MOTIVO" }; HSSFRow filaCabecera = hoja.createRow(0); for (int x = 0; x < cabeceras.length; x++) { HSSFCell cabecera = filaCabecera.createCell(x); cabecera.setCellValue(cabeceras[x]); cabecera.setCellStyle(estiloCeldaCabecera); }// w ww.j ava 2s .c o m //FIN DE CABECERAS for (int i = 0; i < reporte.size(); i++) { HSSFRow fila = hoja.createRow(i + 1); HSSFCell columna1 = fila.createCell(0); columna1.setCellValue(reporte.get(i).getCodigo()); HSSFCell columna2 = fila.createCell(1); columna2.setCellValue(reporte.get(i).getNombre()); HSSFCell columna3 = fila.createCell(2); columna3.setCellValue(reporte.get(i).getHoraInicio()); columna3.setCellStyle(style); HSSFCell columna4 = fila.createCell(3); columna4.setCellValue(reporte.get(i).getHoraFin()); columna4.setCellStyle(style); HSSFCell columna5 = fila.createCell(4); columna5.setCellValue(reporte.get(i).getHoras()); HSSFCell columna6 = fila.createCell(5); columna6.setCellValue(reporte.get(i).getMinutos()); HSSFCell columna7 = fila.createCell(6); columna7.setCellValue(reporte.get(i).getFechaReal()); columna7.setCellStyle(fechas); HSSFCell columna8 = fila.createCell(7); columna8.setCellValue(reporte.get(i).getMotivo()); } try { OutputStream output = response.getOutputStream(); libro.write(output); output.close(); fc.responseComplete(); } catch (IOException ex) { LOG.info("ERROR: " + ex); } }
From source file:com.project.jsica.cdi.ReporteBean.java
public void reporte3() { if (nuevoReporte) { LOG.info("OPCION: " + opcionReporte); String nombreReporte = ""; int filas = 0; if (opcionReporte == 2) { reporte = registroAsistenciaController.buscarXArea(areaSeleccionada, desde, hasta); LOG.info("TAMAO reporte: " + reporte.size()); nombreReporte = "Reporte de asistencia por area"; filas = 1;//from ww w . j a va 2 s . c om } else if (opcionReporte == 1) { reporte = registroAsistenciaController.buscarXEmpleado(empleado, desde, hasta); LOG.info("TAMAO reporte: " + reporte.size()); nombreReporte = "Reporte de asistencia por empleado"; filas = 0; } FacesContext fc = FacesContext.getCurrentInstance(); HttpServletResponse response = (HttpServletResponse) fc.getExternalContext().getResponse(); response.reset(); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=" + nombreReporte + ".xls"); HSSFWorkbook libro = new HSSFWorkbook(); HSSFFont fuente = libro.createFont(); fuente.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle estiloCeldaCabecera = libro.createCellStyle(); estiloCeldaCabecera.setFont(fuente); estiloCeldaCabecera.setAlignment(HSSFCellStyle.ALIGN_CENTER); DataFormat format = libro.createDataFormat(); HSSFCellStyle style = libro.createCellStyle(); style.setDataFormat(format.getFormat("hh:mm:ss")); HSSFCellStyle fechas = libro.createCellStyle(); fechas.setDataFormat(format.getFormat("dd.MM.yyyy")); HSSFSheet hoja = libro.createSheet("Reporte de Asistencias"); //CREAR LAS CABECERAS String[] cabeceras = { "CODIGO", "APELLIDOS Y NOMBRES", "FECHA", "TIPO", "HORA DE INGRESO", "HORA DE SALIDA", "MARCACION DE ENTRADA", "MARCACION DE SALIDA", "TARDANZA(Minutos)", "SALIDA REFRIGERIO", "ENTRADA REFRIGERIO", "MARCACION SALIDA R", "MARCACION DE ENTRADA R", "TARDANZA(refrigerio)", "TARDANZA TOTAL" }; if (filas == 1) { HSSFRow filaArea = hoja.createRow(0); HSSFCell Area = filaArea.createCell(0); Area.setCellValue("AREA"); Area.setCellStyle(estiloCeldaCabecera); HSSFCell nombre = filaArea.createCell(1); nombre.setCellValue(areaSeleccionada.getNombre() + ""); } HSSFRow filaCabecera = hoja.createRow(filas); for (int x = 0; x < cabeceras.length; x++) { HSSFCell cabecera = filaCabecera.createCell(x); cabecera.setCellValue(cabeceras[x]); cabecera.setCellStyle(estiloCeldaCabecera); } //FIN DE CABECERAS for (int i = filas; i < reporte.size(); i++) { HSSFRow fila = hoja.createRow(i + 1); HSSFCell columna1 = fila.createCell(0); columna1.setCellValue(reporte.get(i).getEmpleado().getCodigo()); HSSFCell columna2 = fila.createCell(1); columna2.setCellValue(reporte.get(i).getEmpleado().getNombreCompleto()); HSSFCell columna3 = fila.createCell(2); columna3.setCellValue(reporte.get(i).getFecha()); columna3.setCellStyle(fechas); HSSFCell columna4 = fila.createCell(3); columna4.setCellValue(reporte.get(i).getTipo() + ""); HSSFCell columna5 = fila.createCell(4); columna5.setCellValue(reporte.get(i).getHoraEntrada()); columna5.setCellStyle(style); HSSFCell columna6 = fila.createCell(5); columna6.setCellValue(reporte.get(i).getHoraSalida()); columna6.setCellStyle(style); HSSFCell columna7 = fila.createCell(6); if (reporte.get(i).getMarcacionInicio() != null) { columna7.setCellValue(reporte.get(i).getMarcacionInicio()); columna7.setCellStyle(style); } else { columna7.setCellValue("No marco."); } HSSFCell columna8 = fila.createCell(7); if (reporte.get(i).getMarcacionFin() != null) { columna8.setCellValue(reporte.get(i).getMarcacionFin()); columna8.setCellStyle(style); } else { columna8.setCellValue("No marco."); } HSSFCell columna9 = fila.createCell(8); int minutos = (int) ((reporte.get(i).getMilisTardanzaTotal() / (1000 * 60)) % 60); columna9.setCellValue(minutos); HSSFCell columna10 = fila.createCell(9); columna10.setCellValue(reporte.get(i).getHoraSalidaRefrigerio()); columna10.setCellStyle(style); HSSFCell columna11 = fila.createCell(10); columna11.setCellValue(reporte.get(i).getHoraEntradaRefrigerio()); columna11.setCellStyle(style); HSSFCell columna12 = fila.createCell(11); if (reporte.get(i).getMarcacionInicioRefrigerio() != null) { columna12.setCellValue(reporte.get(i).getMarcacionInicioRefrigerio()); columna12.setCellStyle(style); } else { columna12.setCellValue("No marco."); } HSSFCell columna13 = fila.createCell(12); if (reporte.get(i).getMarcacionFinRefrigerio() != null) { columna13.setCellValue(reporte.get(i).getMarcacionFinRefrigerio()); columna13.setCellStyle(style); } else { columna13.setCellValue("No marco."); } HSSFCell columna14 = fila.createCell(13); columna14.setCellValue((int) ((reporte.get(i).getMilisTardanzaRefrigerio() / (1000 * 60)) % 60)); HSSFCell columna15 = fila.createCell(14); columna15.setCellValue((int) ((reporte.get(i).getMilisTardanzaTotalFinal() / (1000 * 60)) % 60)); } try { OutputStream output = response.getOutputStream(); libro.write(output); output.close(); fc.responseComplete(); } catch (IOException ex) { LOG.info("ERROR: " + ex); } nuevoReporte = false; } }
From source file:com.pureinfo.dolphin.export.impl.ExcelExporterImpl.java
License:Open Source License
/** * Returns the date style in excel./*w w w . j ava 2s . c o m*/ * * @param _workbook * excell work book * @return the date style in excel. */ protected HSSFCellStyle getDateStyle(HSSFWorkbook _workbook) { HSSFCellStyle dateStyle = _workbook.createCellStyle(); HSSFDataFormat fmt = _workbook.createDataFormat(); dateStyle.setDataFormat(fmt.getFormat(ForceConstants.DATE_FORMAT_STR)); return dateStyle; }
From source file:com.pureinfo.srm.project.model.compile.helper.CompileExcelExportHelper.java
License:Open Source License
/** * Returns the date style in excel./*from ww w .j a v a 2 s . c o m*/ * * @param _workbook * excell work book * @return the date style in excel. */ protected HSSFCellStyle getDateStyle(HSSFWorkbook _workbook) { HSSFCellStyle dateStyle = _workbook.createCellStyle(); HSSFDataFormat fmt = _workbook.createDataFormat(); dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("yy-mm")); HSSFFont font = _workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); dateStyle.setWrapText(true); dateStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); dateStyle.setFont(font); return dateStyle; }
From source file:com.pureinfo.srm.project.model.compile.helper.CompileExcelExportHelper.java
License:Open Source License
protected HSSFCellStyle getDoubleStyle(HSSFWorkbook _workbook) { HSSFCellStyle doubleStyle = _workbook.createCellStyle(); HSSFFont font = _workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); doubleStyle.setWrapText(true);/*from w w w .j a v a 2 s. c o m*/ HSSFDataFormat fmt = _workbook.createDataFormat(); doubleStyle.setDataFormat(fmt.getFormat("0.00")); doubleStyle.setFont(font); return doubleStyle; }