List of usage examples for org.apache.poi.ss.usermodel HorizontalAlignment LEFT
HorizontalAlignment LEFT
To view the source code for org.apache.poi.ss.usermodel HorizontalAlignment LEFT.
Click Source Link
From source file:com.commander4j.util.JExcel.java
License:Open Source License
public void exportToExcel(String filename, ResultSet rs) { try {/*w ww . j a v a 2 s . c o 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.funtl.framework.smoke.core.commons.excel.ExportExcel.java
License:Apache License
/** * ?// w w w. j a v a 2s . c o m * * @param wb * @return ? */ private Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); // style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); // style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); Font titleFont = wb.createFont(); titleFont.setFontName("Arial"); titleFont.setFontHeightInPoints((short) 16); titleFont.setBold(true); // titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(titleFont); styles.put("title", style); style = wb.createCellStyle(); style.setVerticalAlignment(VerticalAlignment.CENTER); // style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setBorderRight(BorderStyle.THIN); // style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderLeft(BorderStyle.THIN); // style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderTop(BorderStyle.THIN); // style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderBottom(BorderStyle.THIN); // style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); Font dataFont = wb.createFont(); dataFont.setFontName("Arial"); dataFont.setFontHeightInPoints((short) 10); style.setFont(dataFont); styles.put("data", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(HorizontalAlignment.LEFT); // style.setAlignment(CellStyle.ALIGN_LEFT); styles.put("data1", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(HorizontalAlignment.CENTER); // style.setAlignment(CellStyle.ALIGN_CENTER); styles.put("data2", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(HorizontalAlignment.RIGHT); // style.setAlignment(CellStyle.ALIGN_RIGHT); styles.put("data3", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); // style.setWrapText(true); style.setAlignment(HorizontalAlignment.CENTER); // style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); // style.setFillPattern(CellStyle.SOLID_FOREGROUND); Font headerFont = wb.createFont(); headerFont.setFontName("Arial"); headerFont.setFontHeightInPoints((short) 10); headerFont.setBold(true); // headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setColor(IndexedColors.WHITE.getIndex()); style.setFont(headerFont); styles.put("header", style); return styles; }
From source file:com.github.ukase.toolkit.xlsx.XlsxUtil.java
License:Open Source License
static HorizontalAlignment prepareAlignment(IdentValue ident) { if (XlsxUtil.isLeft(ident)) { return HorizontalAlignment.LEFT; } else if (XlsxUtil.isRight(ident)) { return HorizontalAlignment.RIGHT; } else if (XlsxUtil.isJustify(ident)) { return HorizontalAlignment.JUSTIFY; }/* w w w . j a v a 2s . c o m*/ return HorizontalAlignment.CENTER; }
From source file:com.guardias.excel.CalendarToExcel.java
License:Apache License
/** * cell styles used for formatting calendar sheets *///from ww w . ja v a2 s . c o m private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); short borderColor = IndexedColors.GREY_50_PERCENT.getIndex(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 48); titleFont.setColor(IndexedColors.DARK_BLUE.getIndex()); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setFont(titleFont); styles.put("title", style); Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short) 12); monthFont.setColor(IndexedColors.WHITE.getIndex()); monthFont.setBold(true); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFont(monthFont); styles.put("month", style); Font dayFont = wb.createFont(); dayFont.setFontHeightInPoints((short) 14); dayFont.setBold(true); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.LEFT); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderLeft(BorderStyle.THIN); style.setLeftBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); style.setFont(dayFont); styles.put("weekend_left", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderRight(BorderStyle.THIN); style.setRightBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); styles.put("weekend_right", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.LEFT); style.setVerticalAlignment(VerticalAlignment.TOP); style.setBorderLeft(BorderStyle.THIN); style.setFillForegroundColor(IndexedColors.WHITE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setLeftBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); style.setFont(dayFont); styles.put("workday_left", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFillForegroundColor(IndexedColors.WHITE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderRight(BorderStyle.THIN); style.setRightBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); styles.put("workday_right", style); style = wb.createCellStyle(); style.setBorderLeft(BorderStyle.THIN); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); styles.put("grey_left", style); style = wb.createCellStyle(); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderRight(BorderStyle.THIN); style.setRightBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); styles.put("grey_right", style); return styles; }
From source file:com.hauldata.dbpa.file.book.XlsxTargetSheet.java
License:Apache License
public static HorizontalAlignment getTextAlign(String textProperty) { switch (textProperty) { case "center": return HorizontalAlignment.CENTER; case "left": return HorizontalAlignment.LEFT; case "right": return HorizontalAlignment.RIGHT; default:// w w w. j a va 2s. c o m return null; } }
From source file:com.lufs.java.apache.poi.example.CalendarDemo.java
License:Apache License
/** * cell styles used for formatting calendar sheets *//*from w ww . ja v a 2 s. co m*/ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<>(); short borderColor = IndexedColors.GREY_50_PERCENT.getIndex(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 48); titleFont.setColor(IndexedColors.DARK_BLUE.getIndex()); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setFont(titleFont); styles.put("title", style); Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short) 12); monthFont.setColor(IndexedColors.WHITE.getIndex()); monthFont.setBold(true); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFont(monthFont); styles.put("month", style); Font dayFont = wb.createFont(); dayFont.setFontHeightInPoints((short) 14); dayFont.setBold(true); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.LEFT); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderLeft(BorderStyle.THIN); style.setLeftBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); style.setFont(dayFont); styles.put("weekend_left", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderRight(BorderStyle.THIN); style.setRightBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); styles.put("weekend_right", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.LEFT); style.setVerticalAlignment(VerticalAlignment.TOP); style.setBorderLeft(BorderStyle.THIN); style.setFillForegroundColor(IndexedColors.WHITE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setLeftBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); style.setFont(dayFont); styles.put("workday_left", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFillForegroundColor(IndexedColors.WHITE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderRight(BorderStyle.THIN); style.setRightBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); styles.put("workday_right", style); style = wb.createCellStyle(); style.setBorderLeft(BorderStyle.THIN); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); styles.put("grey_left", style); style = wb.createCellStyle(); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderRight(BorderStyle.THIN); style.setRightBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); styles.put("grey_right", style); return styles; }
From source file:com.netsteadfast.greenstep.bsc.command.PdcaReportExcelCommand.java
License:Apache License
private int createPdca(XSSFWorkbook wb, XSSFSheet sh, int row, Context context) throws Exception { PdcaVO pdca = (PdcaVO) this.getResult(context); Row headRow = sh.createRow(row);/* w ww . ja v a 2 s. co m*/ headRow.setHeight((short) 700); // -------------------------------------------------------------------------------------- XSSFColor bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#d8d8d8")); XSSFColor fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#000000")); XSSFCellStyle cellHeadStyle = wb.createCellStyle(); cellHeadStyle.setFillForegroundColor(bgColor); cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellHeadFont = wb.createFont(); cellHeadFont.setBold(true); cellHeadFont.setColor(fnColor); cellHeadStyle.setFont(cellHeadFont); cellHeadStyle.setBorderBottom(BorderStyle.THIN); cellHeadStyle.setBorderTop(BorderStyle.THIN); cellHeadStyle.setBorderRight(BorderStyle.THIN); cellHeadStyle.setBorderLeft(BorderStyle.THIN); cellHeadStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellHeadStyle.setAlignment(HorizontalAlignment.CENTER); cellHeadStyle.setWrapText(true); // -------------------------------------------------------------------------------------- int cols = 6; for (int i = 0; i < cols; i++) { sh.setColumnWidth(i, 6000); Cell headCell1 = headRow.createCell(i); headCell1.setCellValue(pdca.getTitle()); headCell1.setCellStyle(cellHeadStyle); } sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1)); // -------------------------------------------------------------------------------------- XSSFColor bgLabelColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#F2F2F2")); XSSFCellStyle cellLabelStyle = wb.createCellStyle(); cellLabelStyle.setFillForegroundColor(bgLabelColor); cellLabelStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellLabelFont = wb.createFont(); cellLabelFont.setBold(true); cellLabelFont.setColor(fnColor); cellLabelStyle.setFont(cellLabelFont); cellLabelStyle.setBorderBottom(BorderStyle.THIN); cellLabelStyle.setBorderTop(BorderStyle.THIN); cellLabelStyle.setBorderRight(BorderStyle.THIN); cellLabelStyle.setBorderLeft(BorderStyle.THIN); cellLabelStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellLabelStyle.setAlignment(HorizontalAlignment.LEFT); cellLabelStyle.setWrapText(true); // -------------------------------------------------------------------------------------- XSSFColor bgNormalColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#ffffff")); XSSFCellStyle cellNormalStyle = wb.createCellStyle(); cellNormalStyle.setFillForegroundColor(bgNormalColor); cellNormalStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellNormalFont = wb.createFont(); cellNormalFont.setBold(false); cellNormalFont.setColor(fnColor); cellNormalStyle.setFont(cellNormalFont); cellNormalStyle.setBorderBottom(BorderStyle.THIN); cellNormalStyle.setBorderTop(BorderStyle.THIN); cellNormalStyle.setBorderRight(BorderStyle.THIN); cellNormalStyle.setBorderLeft(BorderStyle.THIN); cellNormalStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellNormalStyle.setAlignment(HorizontalAlignment.LEFT); cellNormalStyle.setWrapText(true); // -------------------------------------------------------------------------------------- row++; Row labelRow = sh.createRow(row); Cell labelCell_0_1 = labelRow.createCell(0); labelCell_0_1.setCellValue("Responsibility"); labelCell_0_1.setCellStyle(cellLabelStyle); Cell labelCell_0_2 = labelRow.createCell(1); labelCell_0_2.setCellValue(pdca.getResponsibilityAppendNames()); labelCell_0_2.setCellStyle(cellNormalStyle); Cell labelCell_0_3 = labelRow.createCell(2); labelCell_0_3.setCellValue(pdca.getResponsibilityAppendNames()); labelCell_0_3.setCellStyle(cellNormalStyle); sh.addMergedRegion(new CellRangeAddress(row, row, 1, 2)); Cell labelCell_0_4 = labelRow.createCell(3); labelCell_0_4.setCellValue("Date range"); labelCell_0_4.setCellStyle(cellLabelStyle); Cell labelCell_0_5 = labelRow.createCell(4); labelCell_0_5.setCellValue(pdca.getStartDateDisplayValue() + " ~ " + pdca.getEndDateDisplayValue()); labelCell_0_5.setCellStyle(cellNormalStyle); Cell labelCell_0_6 = labelRow.createCell(5); labelCell_0_6.setCellValue(pdca.getStartDateDisplayValue() + " ~ " + pdca.getEndDateDisplayValue()); labelCell_0_6.setCellStyle(cellNormalStyle); sh.addMergedRegion(new CellRangeAddress(row, row, 4, 5)); // -------------------------------------------------------------------------------------- row++; labelRow = sh.createRow(row); Cell labelCell_1_1 = labelRow.createCell(0); labelCell_1_1.setCellValue("Confirm"); labelCell_1_1.setCellStyle(cellLabelStyle); Cell labelCell_1_2 = labelRow.createCell(1); labelCell_1_2.setCellValue(pdca.getConfirmEmployeeName()); labelCell_1_2.setCellStyle(cellNormalStyle); Cell labelCell_1_3 = labelRow.createCell(2); labelCell_1_3.setCellValue(pdca.getConfirmEmployeeName()); labelCell_1_3.setCellStyle(cellNormalStyle); sh.addMergedRegion(new CellRangeAddress(row, row, 1, 2)); Cell labelCell_1_4 = labelRow.createCell(3); labelCell_1_4.setCellValue("Confirm date"); labelCell_1_4.setCellStyle(cellLabelStyle); Cell labelCell_1_5 = labelRow.createCell(4); labelCell_1_5.setCellValue(pdca.getConfirmDateDisplayValue()); labelCell_1_5.setCellStyle(cellNormalStyle); Cell labelCell_1_6 = labelRow.createCell(5); labelCell_1_6.setCellValue(pdca.getConfirmDateDisplayValue()); labelCell_1_6.setCellStyle(cellNormalStyle); sh.addMergedRegion(new CellRangeAddress(row, row, 4, 5)); // -------------------------------------------------------------------------------------- row++; labelRow = sh.createRow(row); Cell labelCell_2_1 = labelRow.createCell(0); labelCell_2_1.setCellValue("Organization\nDepartment"); labelCell_2_1.setCellStyle(cellLabelStyle); Cell labelCell_2_2 = labelRow.createCell(1); labelCell_2_2.setCellValue(pdca.getOrganizationAppendNames()); labelCell_2_2.setCellStyle(cellNormalStyle); Cell labelCell_2_3 = labelRow.createCell(2); labelCell_2_3.setCellValue(pdca.getOrganizationAppendNames()); labelCell_2_3.setCellStyle(cellNormalStyle); Cell labelCell_2_4 = labelRow.createCell(3); labelCell_2_4.setCellValue(pdca.getOrganizationAppendNames()); labelCell_2_4.setCellStyle(cellNormalStyle); Cell labelCell_2_5 = labelRow.createCell(4); labelCell_2_5.setCellValue(pdca.getOrganizationAppendNames()); labelCell_2_5.setCellStyle(cellNormalStyle); Cell labelCell_2_6 = labelRow.createCell(5); labelCell_2_6.setCellValue(pdca.getOrganizationAppendNames()); labelCell_2_6.setCellStyle(cellNormalStyle); sh.addMergedRegion(new CellRangeAddress(row, row, 1, 5)); // -------------------------------------------------------------------------------------- row++; labelRow = sh.createRow(row); Cell labelCell_3_1 = labelRow.createCell(0); labelCell_3_1.setCellValue("KPIs"); labelCell_3_1.setCellStyle(cellLabelStyle); Cell labelCell_3_2 = labelRow.createCell(1); labelCell_3_2.setCellValue(pdca.getKpisAppendNames()); labelCell_3_2.setCellStyle(cellNormalStyle); Cell labelCell_3_3 = labelRow.createCell(2); labelCell_3_3.setCellValue(pdca.getKpisAppendNames()); labelCell_3_3.setCellStyle(cellNormalStyle); Cell labelCell_3_4 = labelRow.createCell(3); labelCell_3_4.setCellValue(pdca.getKpisAppendNames()); labelCell_3_4.setCellStyle(cellNormalStyle); Cell labelCell_3_5 = labelRow.createCell(4); labelCell_3_5.setCellValue(pdca.getKpisAppendNames()); labelCell_3_5.setCellStyle(cellNormalStyle); Cell labelCell_3_6 = labelRow.createCell(5); labelCell_3_6.setCellValue(pdca.getKpisAppendNames()); labelCell_3_6.setCellStyle(cellNormalStyle); sh.addMergedRegion(new CellRangeAddress(row, row, 1, 5)); // -------------------------------------------------------------------------------------- row++; labelRow = sh.createRow(row); Cell labelCell_4_1 = labelRow.createCell(0); labelCell_4_1.setCellValue("Parent PDCA"); labelCell_4_1.setCellStyle(cellLabelStyle); Cell labelCell_4_2 = labelRow.createCell(1); labelCell_4_2.setCellValue(pdca.getParentName()); labelCell_4_2.setCellStyle(cellNormalStyle); Cell labelCell_4_3 = labelRow.createCell(2); labelCell_4_3.setCellValue(pdca.getParentName()); labelCell_4_3.setCellStyle(cellNormalStyle); Cell labelCell_4_4 = labelRow.createCell(3); labelCell_4_4.setCellValue(pdca.getParentName()); labelCell_4_4.setCellStyle(cellNormalStyle); Cell labelCell_4_5 = labelRow.createCell(4); labelCell_4_5.setCellValue(pdca.getParentName()); labelCell_4_5.setCellStyle(cellNormalStyle); Cell labelCell_4_6 = labelRow.createCell(5); labelCell_4_6.setCellValue(pdca.getParentName()); labelCell_4_6.setCellStyle(cellNormalStyle); sh.addMergedRegion(new CellRangeAddress(row, row, 1, 5)); // -------------------------------------------------------------------------------------- row++; labelRow = sh.createRow(row); Cell labelCell_6_1 = labelRow.createCell(0); labelCell_6_1.setCellValue("TYPE"); labelCell_6_1.setCellStyle(cellLabelStyle); Cell labelCell_6_2 = labelRow.createCell(1); labelCell_6_2.setCellValue("Title"); labelCell_6_2.setCellStyle(cellLabelStyle); Cell labelCell_6_3 = labelRow.createCell(2); labelCell_6_3.setCellValue("Responsibility"); labelCell_6_3.setCellStyle(cellLabelStyle); Cell labelCell_6_4 = labelRow.createCell(3); labelCell_6_4.setCellValue("Date range"); labelCell_6_4.setCellStyle(cellLabelStyle); Cell labelCell_6_5 = labelRow.createCell(4); labelCell_6_5.setCellValue("Audit"); labelCell_6_5.setCellStyle(cellLabelStyle); Cell labelCell_6_6 = labelRow.createCell(5); labelCell_6_6.setCellValue("Audit date"); labelCell_6_6.setCellStyle(cellLabelStyle); // -------------------------------------------------------------------------------------- row++; int nRow = row; row = this.createPdcaItem(wb, sh, row, cellNormalStyle, pdca.getItemPlan(), pdca.getAuditPlan()); row = this.createPdcaItem(wb, sh, row, cellNormalStyle, pdca.getItemDo(), pdca.getAuditDo()); row = this.createPdcaItem(wb, sh, row, cellNormalStyle, pdca.getItemCheck(), pdca.getAuditCheck()); row = this.createPdcaItem(wb, sh, row, cellNormalStyle, pdca.getItemAction(), pdca.getAuditAction()); nRow = this.mergedRegionForItemsRow(wb, sh, nRow, pdca.getItemPlan()); nRow = this.mergedRegionForItemsRow(wb, sh, nRow, pdca.getItemDo()); nRow = this.mergedRegionForItemsRow(wb, sh, nRow, pdca.getItemCheck()); nRow = this.mergedRegionForItemsRow(wb, sh, nRow, pdca.getItemAction()); return row; }
From source file:Controller.Sonstiges.ExcelController.java
private void makeTitleOfPage() throws IOException { this.sheet.addMergedRegion(new CellRangeAddress(0, 3, 0, 13)); this.row = this.sheet.createRow(0); XSSFCell cell2 = this.row.createCell(0); String title = "Inprotuc Datenbank | Informationen zur Personen \nSuchkriterien: "; ArrayList<String> array2 = this.model.getQueryInfo(); ArrayList<String> array = this.deleteEmptyValueOArray(array2); String info = ""; if (array.size() == 2) { info = array.get(0) + " / " + array.get(1) + "."; }//from w w w.jav a2 s . c om if (array.size() == 4) { info = array.get(0) + "/" + array.get(1) + ", "; info = info + array.get(2) + "/" + array.get(3) + "."; } if (array.size() == 6) { info = array.get(0) + "/" + array.get(1) + ", "; info = info + array.get(2) + "/" + array.get(3) + ", "; info = info + array.get(4) + "/" + array.get(5) + "."; } cell2.setCellValue(title + info); CellStyle cellStyle = this.wb.createCellStyle(); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setAlignment(HorizontalAlignment.LEFT); // font Font font = this.wb.createFont(); font.setFontHeightInPoints((short) 14); font.setFontName(HSSFFont.FONT_ARIAL); font.setBold(true); font.setColor(HSSFColor.BLACK.index); cellStyle.setFont(font); cell2.setCellStyle(cellStyle); }
From source file:de.jlo.talendcomp.excel.SpreadsheetFile.java
License:Apache License
/** * adds a font to the workbook//from w w w . j av a2 s . c om * @param family like Arial * @param height like 8,9,10,12,14... * @param bui with "b"=bold, "u"=underlined, "i"=italic and all combinations as String * @param color color index */ public void addStyle(String styleName, String fontFamily, String fontHeight, String fontDecoration, String fontColor, String bgColor, String textAlign, boolean buttomBorder) { if (styleName != null && styleName.isEmpty() == false) { Font f = workbook.createFont(); if (fontFamily != null && fontFamily.isEmpty() == false) { f.setFontName(fontFamily); } if (fontHeight != null && fontHeight.isEmpty() == false) { short height = Short.parseShort(fontHeight); if (height > 0) { f.setFontHeightInPoints(height); } } if (fontDecoration != null && fontDecoration.isEmpty() == false) { if (fontDecoration.contains("b")) { f.setBold(true); } if (fontDecoration.contains("i")) { f.setItalic(true); } if (fontDecoration.contains("u")) { f.setUnderline(Font.U_SINGLE); } } if (fontColor != null && fontColor.isEmpty() == false) { short color = Short.parseShort(fontColor); f.setColor(color); } CellStyle style = workbook.createCellStyle(); style.setFont(f); if (bgColor != null && bgColor.isEmpty() == false) { short color = Short.parseShort(bgColor); style.setFillForegroundColor(color); //style.setFillBackgroundColor(color); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); } if (textAlign != null && textAlign.isEmpty() == false) { if ("center".equalsIgnoreCase(textAlign)) { style.setAlignment(HorizontalAlignment.CENTER); } else if ("left".equalsIgnoreCase(textAlign)) { style.setAlignment(HorizontalAlignment.LEFT); } else if ("right".equals(textAlign)) { style.setAlignment(HorizontalAlignment.RIGHT); } } if (buttomBorder) { style.setBorderBottom(BorderStyle.MEDIUM); style.setBottomBorderColor((short) 9); } namedStyles.put(styleName, style); } }
From source file:DSC.ChefReport.java
public static void processChefReport() throws IOException { boolean firstFamEntry = true; String list[] = { "Standard", "Low Carb", "Kiddies" }; int excelNumber = 0; int sheetNumber = 0; for (mealCounter = 0; mealCounter < 3; mealCounter++) { workbook = new XSSFWorkbook(); sheetNumber = 0;//w w w . j av a 2s .c o m for (String currRoute : allRoutes) { Map<String, Object[]> data = new TreeMap<>(); data.put(0 + "", new String[] { "Doorstep Chef - Chef Report " + DriverReport.returnWeekInt(), "", "", "Meal Type : " + list[mealCounter] + " " + " " + "Route: " + sheetNumber }); data.put(1 + "", new String[] { "", "", "", "", "", "", "" }); data.put(2 + "", new String[] { "Family Size", "Quantity", "Allergies", "Exclusions" }); int counter = 3; XSSFSheet sheet = workbook.createSheet("ChefReports Route - " + sheetNumber); int rowNum = 0; int cellNum = 0; String familysize = ""; ArrayList<Meal> mealList = new ArrayList<>(); boolean hasValue = false; for (Order order : orders) { for (Meal meal : order.getMeals()) { if (meal.getMealType().equals(list[mealCounter]) && order.getRoute().equals(currRoute)) { mealList.add(meal); hasValue = true; } } } mealList.sort(new Comparator<Meal>() { @Override public int compare(Meal o1, Meal o2) { if (o1.getQuantity() < o2.getQuantity()) { return -1; } else if (o1.getQuantity() > o2.getQuantity()) { return 1; } else { return 0; } } }); if (hasValue) { int currQuantity = 0; int bulk = 0; boolean firstIterate = true; for (Meal meal : mealList) { if (meal.getQuantity() != currQuantity) { if (!firstIterate && bulk != 0) { data.put(counter + "", new String[] { familysize + " Normal *", bulk + "", "", "" }); bulk = 0; counter++; } firstIterate = false; switch (meal.getQuantity()) { case 1: familysize = "Single"; break; case 2: familysize = "Couple"; break; case 3: familysize = "Three"; break; case 4: familysize = "Four"; break; case 5: familysize = "Five"; break; case 6: familysize = "Six"; break; default: familysize = "Extra"; } currQuantity = meal.getQuantity(); } if (meal.getAllergies().equals("-") && meal.getExclusions().equals("-")) { bulk++; } else { data.put(counter + "", new String[] { familysize + " Meal", meal.getQuantity() + "", meal.getAllergies(), meal.getExclusions() }); counter++; } } } Set<String> keySet = data.keySet(); Object[] keys = data.keySet().toArray(); Arrays.sort(keys); ArrayList<Object> keyList = new ArrayList(); int longestCustomer = 5; int totalWidth = 50000; boolean isBulk = false; for (Object key : keys) { keyList.add(data.get(key)); } for (int keyIterate = 0; keyIterate < keySet.size(); keyIterate++) { Row row = sheet.createRow(rowNum); Object[] arr = data.get(keyIterate + ""); for (int i = 0; i < arr.length; i++) { XSSFFont font = workbook.createFont(); Cell cell = row.createCell(i); cell.setCellValue((String) arr[i]); XSSFCellStyle borderStyle = workbook.createCellStyle(); if ((keyIterate + "").equals("0") || (keyIterate + "").equals("1")) { font.setFontName("Calibri"); font.setFontHeightInPoints((short) 13); font.setBold(true); borderStyle.setFont(font); borderStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); borderStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); borderStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); borderStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); borderStyle.setAlignment(HorizontalAlignment.LEFT); } else { borderStyle.setBorderBottom(BorderStyle.THIN); borderStyle.setBorderTop(BorderStyle.THIN); borderStyle.setBorderLeft(BorderStyle.THIN); borderStyle.setBorderRight(BorderStyle.THIN); if ((arr[0] + "").contains("*")) { isBulk = true; borderStyle.setBorderBottom(BorderStyle.MEDIUM); } } if ((keyIterate + "").equals("2")) { borderStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setBorderTop(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setAlignment(HorizontalAlignment.CENTER); borderStyle.setFillPattern(XSSFCellStyle.LESS_DOTS); borderStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); XSSFFont font2 = workbook.createFont(); font2.setColor(IndexedColors.WHITE.getIndex()); borderStyle.setFont(font2); } cell.setCellStyle(borderStyle); } rowNum++; cellNum++; } sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2)); for (int i = 0; i < 5; i++) { switch (i) { case 1: sheet.setColumnWidth(i, 3000); break; case 2: sheet.setColumnWidth(i, 8000); break; default: sheet.setColumnWidth(i, 4000); break; } if (i == 3) { sheet.setColumnWidth(i, 8000); } } Row rowDate = sheet.createRow(keySet.size() + 1); Cell cell = rowDate.createCell(0); SimpleDateFormat sf = new SimpleDateFormat("EEE MMM yyyy HH:mm:ss"); cell.setCellValue(sf.format(Calendar.getInstance().getTime())); XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(XSSFCellStyle.ALIGN_RIGHT); cell.setCellStyle(cellStyle); sheet.addMergedRegion(new CellRangeAddress(keySet.size() + 1, keySet.size() + 1, 0, 3)); sheetNumber++; creatSheet(list[mealCounter], workbook); excelNumber++; if (excelNumber == allRoutes.size()) { if (!(DSC_Main.generateAllReports)) { chefLoadingObj.setVisible(false); chefLoadingObj.dispose(); JOptionPane.showMessageDialog(null, "Chef Reports Successfully Generated."); } } } } }