List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet autoSizeColumn
@Override public void autoSizeColumn(int column)
This process can be relatively slow on large sheets, so this should normally only be called once per column, at the end of your processing.
From source file:com.accounting.reportMBean.ReportCommonBean.java
public void simplePostProcessXLS(Object document) { String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate4"); String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate1"); HSSFWorkbook wb = (HSSFWorkbook) document; HSSFCellStyle headerCellStyle = wb.createCellStyle(); HSSFCellStyle headerCellStyle1 = wb.createCellStyle(); HSSFCellStyle headerCellStyle2 = wb.createCellStyle(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle.setFont(headerFont); headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER); Font headerFont1 = wb.createFont(); headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING); headerFont1.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle1.setFont(headerFont); headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT); Font headerFont3 = wb.createFont(); headerFont3.setBoldweight(Font.U_SINGLE); headerFont3.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle2.setFont(headerFont1); headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT); HSSFSheet sheet = wb.getSheetAt(0); int noOfColumns = sheet.getRow(0).getLastCellNum(); for (int i = 0; i < noOfColumns; i++) { sheet.autoSizeColumn(i); }/* ww w . j av a2 s . com*/ sheet.shiftRows(0, sheet.getLastRowNum(), 4); HSSFRow firstRow = sheet.createRow(1); firstRow.createCell(0).setCellValue(pageName); firstRow.getCell(0).setCellStyle(headerCellStyle); HSSFRow secondRow = sheet.createRow(0); secondRow.createCell(0).setCellValue(getLoggedInOffice().getName()); secondRow.getCell(0).setCellStyle(headerCellStyle); HSSFRow thirdRow = sheet.createRow(3); String date = ndc.convertToNepaliDate(new Date()); SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a"); String time = sdf.format(new Date()); thirdRow.createCell(0) .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName()); thirdRow.getCell(0).setCellStyle(headerCellStyle2); HSSFRow fourthRow = sheet.createRow(2); fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString); fourthRow.getCell(0).setCellStyle(headerCellStyle); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7)); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 7)); sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 7)); HSSFRow lastRow; for (Row row : sheet) { if (row.getRowNum() > 4) { for (int i = 3; i < 8; i++) { String cost = row.getCell(i).getStringCellValue(); if (cost != null && !cost.isEmpty()) { row.getCell(i).setCellType(HSSFCell.CELL_TYPE_BLANK); row.getCell(i).setCellType(HSSFCell.CELL_TYPE_NUMERIC); row.getCell(i).setCellValue(Double.parseDouble(cost.replace(",", ""))); } } } } }
From source file:com.accounting.reportMBean.StockLedgerMBeans.java
public void postProcessXLS(Object document) { String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate4"); String endDateReading = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate1"); HSSFWorkbook wb = (HSSFWorkbook) document; HSSFCellStyle headerCellStyle = wb.createCellStyle(); HSSFCellStyle headerCellStyle1 = wb.createCellStyle(); HSSFCellStyle headerCellStyle2 = wb.createCellStyle(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle.setFont(headerFont); headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER); Font headerFont1 = wb.createFont(); headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING); headerFont1.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle1.setFont(headerFont); headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT); HSSFSheet sheet = wb.getSheetAt(0); int noOfColumns = sheet.getRow(0).getLastCellNum(); Font headerFont3 = wb.createFont(); headerFont3.setBoldweight(Font.U_SINGLE); headerFont3.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle2.setFont(headerFont1); headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT); for (int i = 0; i < noOfColumns; i++) { sheet.autoSizeColumn(i); }//from w w w.ja v a2s . c om sheet.shiftRows(0, sheet.getLastRowNum(), 4); HSSFRow firstRow = sheet.createRow(0); firstRow.createCell(0).setCellValue(reportHeader); firstRow.getCell(0).setCellStyle(headerCellStyle); HSSFRow secondRow = sheet.createRow(1); secondRow.createCell(0).setCellValue(getLoggedInOffice().getName()); secondRow.getCell(0).setCellStyle(headerCellStyle); HSSFRow thirdRow = sheet.createRow(3); String generatedDate = ndc.convertToNepaliDate(new Date()); SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a"); String time = sdf.format(new Date()); thirdRow.createCell(0) .setCellValue("Generated on:" + generatedDate + " " + time + " by:" + getLoggedInUser().getName()); thirdRow.getCell(0).setCellStyle(headerCellStyle2); HSSFRow fourthRow = sheet.createRow(2); fourthRow.createCell(0).setCellValue("From: " + startDateString + " To: " + endDateReading); fourthRow.getCell(0).setCellStyle(headerCellStyle); System.out.println("The cell count " + cellCount); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, cellCount)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, cellCount)); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, cellCount)); sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, cellCount)); for (Row row : sheet) { if (row.getRowNum() > 4) { for (int i = 3; i < count; i++) { String cost = row.getCell(i).getStringCellValue(); if (cost != null && !cost.isEmpty()) { row.getCell(i).setCellType(HSSFCell.CELL_TYPE_BLANK); row.getCell(i).setCellType(HSSFCell.CELL_TYPE_NUMERIC); row.getCell(i).setCellValue(Double.parseDouble(cost.replace(",", ""))); } } } } }
From source file:com.app.ExcelView.java
License:Open Source License
/** * ?Excel//from w ww .j a v a 2 s . c o m * * @param model * ? * @param workbook * workbook * @param request * request * @param response * response */ public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { Assert.notEmpty(properties); HSSFSheet sheet; if (StringUtils.isNotEmpty(sheetName)) { sheet = workbook.createSheet(sheetName); } else { sheet = workbook.createSheet(); } int rowNumber = 0; if (titles != null && titles.length > 0) { HSSFRow header = sheet.createRow(rowNumber); header.setHeight((short) 400); for (int i = 0; i < properties.length; i++) { HSSFCell cell = header.createCell(i); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 11); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(font); cell.setCellStyle(cellStyle); if (i == 0) { HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFComment comment = patriarch .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4)); comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B" + "y" + " " + "A" + "P" + "P" + "T" + "E" + "A" + "M")); cell.setCellComment(comment); } if (titles.length > i && titles[i] != null) { cell.setCellValue(titles[i]); } else { cell.setCellValue(properties[i]); } if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } rowNumber++; } if (data != null) { for (Object item : data) { HSSFRow row = sheet.createRow(rowNumber); for (int i = 0; i < properties.length; i++) { HSSFCell cell = row.createCell(i); if (converters != null && converters.length > i && converters[i] != null) { Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]); ConvertUtils.register(converters[i], clazz); cell.setCellValue(BeanUtils.getProperty(item, properties[i])); ConvertUtils.deregister(clazz); if (clazz.equals(Date.class)) { DateConverter dateConverter = new DateConverter(); dateConverter.setPattern(DEFAULT_DATE_PATTERN); ConvertUtils.register(dateConverter, Date.class); } } else { cell.setCellValue(BeanUtils.getProperty(item, properties[i])); } if (rowNumber == 0 || rowNumber == 1) { if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } } rowNumber++; } } if (contents != null && contents.length > 0) { rowNumber++; for (String content : contents) { HSSFRow row = sheet.createRow(rowNumber); HSSFCell cell = row.createCell(0); HSSFCellStyle cellStyle = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.GREY_50_PERCENT.index); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cell.setCellValue(content); rowNumber++; } } response.setContentType("application/force-download"); if (StringUtils.isNotEmpty(filename)) { response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8")); } else { response.setHeader("Content-disposition", "attachment"); } }
From source file:com.asakusafw.testtools.templategen.ExcelBookBuilder.java
License:Apache License
private HSSFSheet createTestConditionSheet(String sheetName) { // ??, ?????//from w w w. j av a2s. co m int maxColumn = 0; HSSFSheet sheet = workbook.createSheet(sheetName); for (ConditionSheetItem item : ConditionSheetItem.values()) { HSSFCell cell = getCell(sheet, item.getRow(), item.getCol()); cell.setCellValue(item.getName()); cell.setCellStyle(titleStyle); if (maxColumn < item.getCol()) { maxColumn = item.getCol(); } } // ?????? HSSFCell tableNameCell = getCell(sheet, ConditionSheetItem.TABLE_NAME.getRow(), ConditionSheetItem.TABLE_NAME.getCol() + 1); tableNameCell.setCellStyle(fixedValueStyle); tableNameCell.setCellValue(tableName); HSSFCell rowMatichingConditionCell = getCell(sheet, ConditionSheetItem.ROW_MATCHING_CONDITION.getRow(), ConditionSheetItem.ROW_MATCHING_CONDITION.getCol() + 1); rowMatichingConditionCell.setCellValue(RowMatchingCondition.NONE.getJapaneseName()); // ?? int startRow = ConditionSheetItem.NO.getRow(); int endRow = configureColumns(sheet, startRow); // ? setExplicitListConstraint(sheet, RowMatchingCondition.getJapaneseNames(), ConditionSheetItem.ROW_MATCHING_CONDITION.getRow(), ConditionSheetItem.ROW_MATCHING_CONDITION.getRow(), ConditionSheetItem.ROW_MATCHING_CONDITION.getCol() + 1, ConditionSheetItem.ROW_MATCHING_CONDITION.getCol() + 1); setExplicitListConstraint(sheet, ColumnMatchingCondition.getJapaneseNames(), startRow + 1, endRow, ConditionSheetItem.MATCHING_CONDITION.getCol(), ConditionSheetItem.MATCHING_CONDITION.getCol()); setExplicitListConstraint(sheet, NullValueCondition.getJapaneseNames(), startRow + 1, endRow, ConditionSheetItem.NULL_VALUE_CONDITION.getCol(), ConditionSheetItem.NULL_VALUE_CONDITION.getCol()); // ? for (int i = 0; i <= maxColumn + 1; i++) { sheet.autoSizeColumn(i); } return sheet; }
From source file:com.asakusafw.testtools.templategen.ExcelBookBuilder.java
License:Apache License
/** * ???//from w w w . j a va 2s . c om * @param sheetName ??? * @return ??? * @throws SQLException ???????? */ private HSSFSheet createInputDataSheet(String sheetName) throws SQLException { HSSFSheet sheet = workbook.createSheet(sheetName); // ?? HSSFRow row = sheet.createRow(0); for (int i = 0; i < columnInfos.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellValue(columnInfos[i].getColumnName()); cell.setCellStyle(titleStyle); } // DB? PreparedStatement ps = null; ResultSet rs = null; String sql = "SELECT * FROM " + databaseName + "." + tableName + " limit 0, " + Constants.MAX_ROWS; try { ps = conn.prepareStatement(sql); rs = ps.executeQuery(); while (rs.next()) { row = sheet.createRow(row.getRowNum() + 1); for (int i = 0; i < columnInfos.length; i++) { ColumnInfo info = columnInfos[i]; HSSFCell cell = row.createCell(i); cell.setCellStyle(commonStyle); switch (info.getDataType()) { case CHAR: case VARCHAR: String str = rs.getString(info.getColumnName()); if (!rs.wasNull()) { cell.setCellValue(str); } break; case DATE: Date date = rs.getDate(info.getColumnName()); if (!rs.wasNull()) { cell.setCellValue(new java.util.Date(date.getTime())); cell.setCellStyle(dateStyle); } break; case DATETIME: case TIMESTAMP: Timestamp ts = rs.getTimestamp(info.getColumnName()); if (!rs.wasNull()) { cell.setCellValue(new java.util.Date(ts.getTime())); cell.setCellStyle(dateTimeStyle); } break; case DECIMAL: BigDecimal decimal = rs.getBigDecimal(info.getColumnName()); if (!rs.wasNull()) { cell.setCellValue(decimal.toPlainString()); } break; case TINY_INT: case SMALL_INT: case INT: case LONG: long value = rs.getLong(info.getColumnName()); if (!rs.wasNull()) { cell.setCellValue(Long.toString(value)); } break; default: assert false; break; } } } } finally { if (rs != null) { try { rs.close(); } catch (Exception e) { e.printStackTrace(); } } if (ps != null) { try { ps.close(); } catch (Exception e) { e.printStackTrace(); } } } // ? for (int i = 0; i < columnInfos.length; i++) { sheet.autoSizeColumn(i); } return sheet; }
From source file:com.astrocytes.core.statistics.LayerStatistics.java
License:Open Source License
public boolean saveLayerStatisticsToXls(Map<Integer, List<Point>> layers, List<Point> astrocyteCenters, List<Point> neuronsCenters, File fileToSave) { try {// w w w. j a v a2 s . c o m FileOutputStream outputStream = new FileOutputStream(fileToSave); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(CoreConstants.XLS_SPREADSHEET_TITLE); HSSFRow headerRow = sheet.createRow(0); headerRow.createCell(0).setCellValue(CoreConstants.XLS_LAYERS_TITLE); for (int i = 0; i < layers.size() - 1; i++) { HSSFRow row = sheet.createRow(i + 1); HSSFCell cell = row.createCell(0); cell.setCellType(CellType.STRING); cell.setCellValue(String.valueOf(i < 1 ? i + 1 : (i == 1 ? "2 - 3" : i + 2))); } if (astrocyteCenters != null) { headerRow.createCell(1).setCellValue(CoreConstants.XLS_ASTROCYTES_TITLE); for (Map.Entry<Integer, Integer> count : count(astrocyteCenters, layers).entrySet()) { HSSFRow row = sheet.getRow(count.getKey() + 1); HSSFCell cell = row.createCell(1); cell.setCellType(CellType.NUMERIC); cell.setCellValue(count.getValue()); } } if (neuronsCenters != null) { int colIdx = astrocyteCenters != null ? 2 : 1; headerRow.createCell(colIdx).setCellValue(CoreConstants.XLS_NEURONS_TITLE); for (Map.Entry<Integer, Integer> count : count(neuronsCenters, layers).entrySet()) { HSSFRow row = sheet.getRow(count.getKey() + 1); HSSFCell cell = row.createCell(astrocyteCenters != null ? 2 : 1); cell.setCellType(CellType.NUMERIC); cell.setCellValue(count.getValue()); } } sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); workbook.write(outputStream); outputStream.close(); } catch (IOException e) { e.printStackTrace(); return false; } return true; }
From source file:com.commander4j.util.JExcel.java
License:Open Source License
public void exportToExcel(String filename, ResultSet rs) { try {/* w w w . j a v a2 s .co m*/ ResultSetMetaData rsmd = rs.getMetaData(); int numberOfColumns = rsmd.getColumnCount(); int columnType = 0; String columnTypeName = ""; int recordNumber = 0; int passwordCol = -1; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); HSSFCellStyle cellStyle_varchar = workbook.createCellStyle(); cellStyle_varchar.setAlignment(HorizontalAlignment.LEFT); HSSFCellStyle cellStyle_nvarchar = workbook.createCellStyle(); cellStyle_nvarchar.setAlignment(HorizontalAlignment.LEFT); HSSFCellStyle cellStyle_varchar2 = workbook.createCellStyle(); cellStyle_varchar2.setAlignment(HorizontalAlignment.LEFT); HSSFCellStyle cellStyle_title = workbook.createCellStyle(); cellStyle_title.setAlignment(HorizontalAlignment.CENTER); HSSFCellStyle cellStyle_char = workbook.createCellStyle(); cellStyle_char.setAlignment(HorizontalAlignment.LEFT); HSSFCellStyle cellStyle_date = workbook.createCellStyle(); cellStyle_date.setAlignment(HorizontalAlignment.CENTER); cellStyle_date.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); HSSFCellStyle cellStyle_timestamp = workbook.createCellStyle(); cellStyle_timestamp.setAlignment(HorizontalAlignment.CENTER); cellStyle_timestamp.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); HSSFCellStyle cellStyle_decimal = workbook.createCellStyle(); cellStyle_decimal.setAlignment(HorizontalAlignment.RIGHT); HSSFFont font_title = workbook.createFont(); font_title.setColor((short) 0xc); font_title.setBold(true); ; font_title.setItalic(true); font_title.setUnderline(HSSFFont.U_DOUBLE); cellStyle_title.setFont(font_title); HSSFCell cell; HSSFRow row; // rs.beforeFirst(); while (rs.next()) { recordNumber++; if (recordNumber == 1) { row = sheet.createRow((int) 0); for (int column = 1; column <= numberOfColumns; column++) { cell = row.createCell((int) (column - 1)); String columnName = rsmd.getColumnLabel(column); columnName = columnName.replace("_", " "); columnName = JUtility.capitalize(columnName); cell.setCellStyle(cellStyle_title); cell.setCellValue(columnName); if (columnName.equals("Password")) { passwordCol = column; } } } row = sheet.createRow((int) recordNumber); for (int column = 1; column <= numberOfColumns; column++) { columnType = rsmd.getColumnType(column); columnTypeName = rsmd.getColumnTypeName(column); cell = row.createCell((int) (column - 1)); try { switch (columnType) { case java.sql.Types.NVARCHAR: HSSFRichTextString rtf_nvarchar; if (column == passwordCol) { rtf_nvarchar = new HSSFRichTextString("*****"); } else { rtf_nvarchar = new HSSFRichTextString(rs.getString(column)); } cell.setCellStyle(cellStyle_nvarchar); cell.setCellValue(rtf_nvarchar); break; case java.sql.Types.VARCHAR: HSSFRichTextString rtf_varchar; if (column == passwordCol) { rtf_varchar = new HSSFRichTextString("*****"); } else { rtf_varchar = new HSSFRichTextString(rs.getString(column)); } cell.setCellStyle(cellStyle_varchar); cell.setCellValue(rtf_varchar); break; case java.sql.Types.CHAR: HSSFRichTextString rtf_char = new HSSFRichTextString(rs.getString(column)); cell.setCellStyle(cellStyle_char); cell.setCellValue(rtf_char); break; case java.sql.Types.DATE: try { cell.setCellValue(rs.getTimestamp(column)); cell.setCellStyle(cellStyle_date); } catch (Exception ex) { } break; case java.sql.Types.TIMESTAMP: try { cell.setCellValue(rs.getTimestamp(column)); cell.setCellStyle(cellStyle_timestamp); } catch (Exception ex) { } break; case java.sql.Types.DECIMAL: HSSFRichTextString rtf_decimal = new HSSFRichTextString( rs.getBigDecimal(column).toString()); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_decimal); break; case java.sql.Types.NUMERIC: HSSFRichTextString rtf_decimaln = new HSSFRichTextString( rs.getBigDecimal(column).toString()); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_decimaln); break; case java.sql.Types.BIGINT: HSSFRichTextString rtf_bigint = new HSSFRichTextString( rs.getBigDecimal(column).toString()); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_bigint); break; case java.sql.Types.INTEGER: HSSFRichTextString rtf_int = new HSSFRichTextString(String.valueOf(rs.getInt(column))); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_int); break; case java.sql.Types.FLOAT: HSSFRichTextString rtf_float = new HSSFRichTextString( String.valueOf(rs.getFloat(column))); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_float); break; case java.sql.Types.DOUBLE: HSSFRichTextString rtf_double = new HSSFRichTextString( String.valueOf(rs.getDouble(column))); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_double); break; default: cell.setCellValue(new HSSFRichTextString(columnTypeName)); break; } } catch (Exception ex) { String errormessage = ex.getLocalizedMessage(); HSSFRichTextString rtf_exception = new HSSFRichTextString(errormessage); cell.setCellStyle(cellStyle_varchar); cell.setCellValue(rtf_exception); break; } } if (recordNumber == 65535) { break; } } for (int column = 1; column <= numberOfColumns; column++) { sheet.autoSizeColumn((int) (column - 1)); } if (recordNumber > 0) { try { FileOutputStream fileOut = new FileOutputStream(filename.toLowerCase()); workbook.write(fileOut); fileOut.close(); } catch (Exception ex) { setErrorMessage(ex.getMessage()); } } try { workbook.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } catch (SQLException e) { setErrorMessage(e.getMessage()); } }
From source file:com.dp2345.ExcelView.java
License:Open Source License
/** * ?Excel/*from w w w. j a v a2 s .c o m*/ * * @param model * ? * @param workbook * workbook * @param request * request * @param response * response */ public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { Assert.notEmpty(properties); HSSFSheet sheet; if (StringUtils.isNotEmpty(sheetName)) { sheet = workbook.createSheet(sheetName); } else { sheet = workbook.createSheet(); } int rowNumber = 0; if (titles != null && titles.length > 0) { HSSFRow header = sheet.createRow(rowNumber); header.setHeight((short) 400); for (int i = 0; i < properties.length; i++) { HSSFCell cell = header.createCell(i); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 11); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(font); cell.setCellStyle(cellStyle); if (i == 0) { HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFComment comment = patriarch .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4)); comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B" + "y" + " " + "D" + "P" + "2" + "3" + "4" + "5")); cell.setCellComment(comment); } if (titles.length > i && titles[i] != null) { cell.setCellValue(titles[i]); } else { cell.setCellValue(properties[i]); } if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } rowNumber++; } if (data != null) { for (Object item : data) { HSSFRow row = sheet.createRow(rowNumber); for (int i = 0; i < properties.length; i++) { HSSFCell cell = row.createCell(i); if (converters != null && converters.length > i && converters[i] != null) { Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]); ConvertUtils.register(converters[i], clazz); cell.setCellValue(BeanUtils.getProperty(item, properties[i])); ConvertUtils.deregister(clazz); if (clazz.equals(Date.class)) { DateConverter dateConverter = new DateConverter(); dateConverter.setPattern(DEFAULT_DATE_PATTERN); ConvertUtils.register(dateConverter, Date.class); } } else { cell.setCellValue(BeanUtils.getProperty(item, properties[i])); } if (rowNumber == 0 || rowNumber == 1) { if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } } rowNumber++; } } if (contents != null && contents.length > 0) { rowNumber++; for (String content : contents) { HSSFRow row = sheet.createRow(rowNumber); HSSFCell cell = row.createCell(0); HSSFCellStyle cellStyle = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.GREY_50_PERCENT.index); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cell.setCellValue(content); rowNumber++; } } response.setContentType("application/force-download"); if (StringUtils.isNotEmpty(filename)) { response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8")); } else { response.setHeader("Content-disposition", "attachment"); } }
From source file:com.efficio.fieldbook.web.nursery.service.impl.ExcelExportStudyServiceImpl.java
License:Open Source License
private void writeDescriptionSheet(HSSFWorkbook xlsBook, Workbook workbook) { Locale locale = LocaleContextHolder.getLocale(); HSSFSheet xlsSheet = xlsBook .createSheet(messageSource.getMessage("export.study.sheet.description", null, locale)); int currentRowNum = 0; currentRowNum = writeStudyDetails(currentRowNum, xlsBook, xlsSheet, workbook.getStudyDetails()); xlsSheet.createRow(currentRowNum++); currentRowNum = writeConditions(currentRowNum, xlsBook, xlsSheet, workbook.getConditions()); xlsSheet.createRow(currentRowNum++); currentRowNum = writeFactors(currentRowNum, xlsBook, xlsSheet, workbook.getFactors()); xlsSheet.createRow(currentRowNum++); currentRowNum = writeConstants(currentRowNum, xlsBook, xlsSheet, workbook.getConstants()); xlsSheet.createRow(currentRowNum++); currentRowNum = writeVariates(currentRowNum, xlsBook, xlsSheet, workbook.getVariates()); for (int i = 0; i < 8; i++) { xlsSheet.autoSizeColumn(i); }/*w w w . j a v a 2 s.co m*/ }
From source file:com.github.gujou.deerbelling.sonarqube.service.XlsTasksGenerator.java
License:Open Source License
public static File generateFile(Project sonarProject, FileSystem sonarFileSystem, String sonarUrl, String sonarLogin, String sonarPassword) { short formatIndex; HSSFDataFormat dataFormat = null;//w ww .j a v a2 s. co m FileOutputStream out = null; HSSFWorkbook workbook = null; String filePath = sonarFileSystem.workDir().getAbsolutePath() + File.separator + "tasks_report_" + sonarProject.getEffectiveKey().replace(':', '-') + "." + ReportsKeys.TASKS_REPORT_TYPE_XLS_EXTENSION; File resultFile = new File(filePath); try { out = new FileOutputStream(resultFile); workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Tasks list"); // Date format. dataFormat = workbook.createDataFormat(); formatIndex = dataFormat.getFormat("yyyy-MM-ddTHH:mm:ss"); HSSFCellStyle dateStyle = workbook.createCellStyle(); dateStyle.setDataFormat(formatIndex); Issues rootIssue = IssueGateway.getOpenIssues(sonarProject.getEffectiveKey(), sonarUrl, sonarLogin, sonarPassword); if (rootIssue == null) { return null; } DataValidationHelper validationHelper = new HSSFDataValidationHelper(sheet); DataValidationConstraint constraint = validationHelper.createExplicitListConstraint( new String[] { "OPENED", "CONFIRMED", "REOPENED", "RESOLVED", "CLOSE" }); CellRangeAddressList addressList = new CellRangeAddressList(1, rootIssue.getIssues().size() + 1, STATUS_COLUMN_INDEX, STATUS_COLUMN_INDEX); DataValidation dataValidation = validationHelper.createValidation(constraint, addressList); dataValidation.setSuppressDropDownArrow(false); sheet.addValidationData(dataValidation); int rownum = 0; Row row = sheet.createRow(rownum++); row.createCell(STATUS_COLUMN_INDEX).setCellValue("Status"); row.createCell(SEVERITY_COLUMN_INDEX).setCellValue("Severity"); row.createCell(COMPONENT_COLUMN_INDEX).setCellValue("Component"); row.createCell(LINE_COLUMN_INDEX).setCellValue("Line"); row.createCell(MESSAGE_COLUMN_INDEX).setCellValue("Message"); row.createCell(AUTHOR_COLUMN_INDEX).setCellValue("Author"); row.createCell(ASSIGNED_COLUMN_INDEX).setCellValue("Assigned"); row.createCell(CREATION_DATE_COLUMN_INDEX).setCellValue("CreationDate"); row.createCell(UPDATE_DATE_COLUMN_INDEX).setCellValue("UpdateDate"); row.createCell(COMPONENT_PATH_COLUMN_INDEX).setCellValue("Path"); for (Issue issue : rootIssue.getIssues()) { if (issue != null) { row = sheet.createRow(rownum++); int componentIndex = 0; if (issue.getComponent() != null) { componentIndex = issue.getComponent().lastIndexOf('/'); } String component; String path; if (componentIndex > 0) { component = issue.getComponent().substring(componentIndex + 1); path = issue.getComponent().substring(0, componentIndex); } else { component = issue.getComponent(); path = ""; } // Set values. row.createCell(STATUS_COLUMN_INDEX).setCellValue(issue.getStatus()); row.createCell(SEVERITY_COLUMN_INDEX).setCellValue(issue.getSeverity()); row.createCell(COMPONENT_COLUMN_INDEX).setCellValue(component); row.createCell(LINE_COLUMN_INDEX).setCellValue(issue.getLine()); row.createCell(MESSAGE_COLUMN_INDEX).setCellValue(issue.getMessage()); row.createCell(AUTHOR_COLUMN_INDEX).setCellValue(issue.getAuthor()); row.createCell(ASSIGNED_COLUMN_INDEX).setCellValue(issue.getAssignee()); row.createCell(CREATION_DATE_COLUMN_INDEX).setCellValue(issue.getCreationDate()); row.createCell(UPDATE_DATE_COLUMN_INDEX).setCellValue(issue.getUpdateDate()); row.createCell(COMPONENT_PATH_COLUMN_INDEX).setCellValue(path); // Set date style to date column. row.getCell(CREATION_DATE_COLUMN_INDEX).setCellStyle(dateStyle); row.getCell(UPDATE_DATE_COLUMN_INDEX).setCellStyle(dateStyle); } } // Auto-size sheet columns. sheet.autoSizeColumn(STATUS_COLUMN_INDEX); sheet.autoSizeColumn(STATUS_COLUMN_INDEX); sheet.autoSizeColumn(COMPONENT_COLUMN_INDEX); sheet.autoSizeColumn(LINE_COLUMN_INDEX); sheet.autoSizeColumn(MESSAGE_COLUMN_INDEX); sheet.autoSizeColumn(AUTHOR_COLUMN_INDEX); sheet.autoSizeColumn(ASSIGNED_COLUMN_INDEX); sheet.autoSizeColumn(CREATION_DATE_COLUMN_INDEX); sheet.autoSizeColumn(UPDATE_DATE_COLUMN_INDEX); sheet.autoSizeColumn(COMPONENT_PATH_COLUMN_INDEX); workbook.write(out); } catch (FileNotFoundException e) { // TODO manage error. e.printStackTrace(); } catch (IOException e) { // TODO manage error. e.printStackTrace(); } finally { IOUtils.closeQuietly(workbook); IOUtils.closeQuietly(out); } return resultFile; }