List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet setColumnWidth
@Override public void setColumnWidth(int columnIndex, int width)
The maximum column width for an individual cell is 255 characters.
From source file:org.azkfw.document.database.xlsx.XLSXWriter.java
License:Apache License
private XSSFSheet createTableListSheet(final DatabaseModel datasource, final XSSFSheet sheet) { // /*from w ww . jav a 2s . c o m*/ sheet.setColumnWidth(0, 640 * 1); sheet.setColumnWidth(1, 640 * 2); // No sheet.setColumnWidth(2, 640 * 10); // ??? sheet.setColumnWidth(3, 640 * 10); // ??? sheet.setColumnWidth(4, 640 * 15); // sheet.setColumnWidth(5, 640 * 1); ///////////////////////////////////////////////////////////////////// XSSFCellStyle styleLabel = workbook.createCellStyle(); styleLabel.setFillPattern(CellStyle.SOLID_FOREGROUND); styleLabel.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex()); styleLabel.setFont(fontLabel); styleLabel.setBorderTop(CellStyle.BORDER_THIN); styleLabel.setBorderBottom(CellStyle.BORDER_THIN); styleLabel.setBorderLeft(CellStyle.BORDER_THIN); styleLabel.setBorderRight(CellStyle.BORDER_THIN); XSSFCellStyle styleValue1 = workbook.createCellStyle(); styleValue1.setFillPattern(CellStyle.SOLID_FOREGROUND); styleValue1.setFillForegroundColor(IndexedColors.WHITE.getIndex()); styleValue1.setFont(fontValue); styleValue1.setBorderTop(CellStyle.BORDER_DOTTED); styleValue1.setBorderBottom(CellStyle.BORDER_DOTTED); styleValue1.setBorderLeft(CellStyle.BORDER_THIN); styleValue1.setBorderRight(CellStyle.BORDER_THIN); XSSFCellStyle styleValue2 = workbook.createCellStyle(); styleValue2.setFillPattern(CellStyle.SOLID_FOREGROUND); styleValue2.setFillForegroundColor(IndexedColors.WHITE.getIndex()); styleValue2.setFont(fontLink); styleValue2.setBorderTop(CellStyle.BORDER_DOTTED); styleValue2.setBorderBottom(CellStyle.BORDER_DOTTED); styleValue2.setBorderLeft(CellStyle.BORDER_THIN); styleValue2.setBorderRight(CellStyle.BORDER_THIN); ///////////////////////////////////////////////////////////////////// XSSFRow row = null; @SuppressWarnings("unused") XSSFCell cell = null; int rowIndex = 1; //////////////////////////////////////////////////////////////////////////// // //////////////////////////////////////////////////////////////////////////// row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, Strings.get("doc.table_list"), styleTitle, row); List<TableModel> tables = datasource.getTables(); short top = BD_RECT; short bottom = CellStyle.BORDER_THIN; if (0 == tables.size()) { bottom = BD_RECT; } rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, Strings.get("doc.no"), styleManager.get(defStyleLabel, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(2, Strings.get("doc.logic_table_name"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(3, Strings.get("doc.physical_table_name"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(4, Strings.get("doc.memo"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); rowIndex++; for (int i = 0; i < tables.size(); i++) { int bufRowIndex = rowIndex + i; TableModel table = tables.get(i); Hyperlink link = createTableLink(table.getName()); top = CellStyle.BORDER_DOTTED; bottom = CellStyle.BORDER_DOTTED; if (i == 0) { top = CellStyle.BORDER_THIN; } if (i + 1 == tables.size()) { bottom = BD_RECT; } row = sheet.createRow(bufRowIndex); /////////////////////////////////////////// cell = createCell(1, String.format("%d", i + 1), styleManager.get(defStyleListValueNo, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(2, table.getLabel(), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(3, table.getName(), styleManager.get(defStyleListValueLink, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), link, row); cell = createCell(4, table.getComment(), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); } rowIndex += tables.size(); workbook.setPrintArea(workbook.getSheetIndex(getTableListSheetName()), 0, 5, 0, rowIndex); sheet.setAutobreaks(true); XSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setFitWidth((short) 1); printSetup.setScale((short) 95); return sheet; }
From source file:org.azkfw.document.database.xlsx.XLSXWriter.java
License:Apache License
private XSSFSheet createTableSheet(final DatabaseModel datasource, final TableModel table, final XSSFSheet sheet) { // /* w w w . j a v a 2 s. c o m*/ for (int i = 0; i < 39; i++) { sheet.setColumnWidth(i, 640); } ///////////////////////////////////////////////////////////////////// XSSFRow row = null; @SuppressWarnings("unused") XSSFCell cell = null; int rowIndex = 1; //////////////////////////////////////////////////////////////////////////// // //////////////////////////////////////////////////////////////////////////// { row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, Strings.get("doc.table_info"), styleTitle, row); rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 7, Strings.get("doc.system_name"), styleManager.get(defStyleLabel, BD_RECT, CellStyle.BORDER_THIN, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(8, 12, option.getSystemName(), styleManager.get(defStyleHeadValue, BD_RECT, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(20, 3, Strings.get("doc.creator"), styleManager.get(defStyleLabel, BD_RECT, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(23, 6, option.getCreateUser(), styleManager.get(defStyleHeadValue, BD_RECT, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(29, 3, Strings.get("doc.create_day"), styleManager.get(defStyleLabel, BD_RECT, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(32, 6, toString(option.getCreateDate()), styleManager.get(defStyleHeadValue, BD_RECT, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 7)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 8, 19)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 20, 22)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 23, 28)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 29, 31)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 32, 37)); rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 7, Strings.get("doc.sub_system_name"), styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(8, 12, option.getSubSystemName(), styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(20, 3, Strings.get("doc.updater"), styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(23, 6, option.getUpdateUser(), styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(29, 3, Strings.get("doc.update_day"), styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(32, 6, toString(option.getUpdateDate()), styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 7)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 8, 19)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 20, 22)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 23, 28)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 29, 31)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 32, 37)); rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 7, Strings.get("doc.schema_name"), styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(8, 12, table.getSchema().getName(), styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(20, 3, "", styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(23, 15, "", styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 7)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 8, 19)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 20, 22)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 23, 37)); rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 7, Strings.get("doc.logic_table_name"), styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(8, 12, table.getLabel(), styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(20, 3, "", styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(23, 15, "", styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 7)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 8, 19)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 20, 22)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 23, 37)); rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 7, Strings.get("doc.physical_table_name"), styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(8, 12, table.getName(), styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(20, 3, "", styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(23, 15, "", styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 7)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 8, 19)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 20, 22)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 23, 37)); rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 37, Strings.get("doc.comment"), styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_DOTTED, BD_RECT, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 37)); rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 37, "", styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT, BD_RECT), row); row = sheet.createRow(rowIndex + 1); /////////////////////////////////////////// cell = createCell(1, 37, "", styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT, BD_RECT), row); row = sheet.createRow(rowIndex + 2); /////////////////////////////////////////// cell = createCell(1, 37, "", styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, BD_RECT, BD_RECT, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex + 2, 1, 37)); rowIndex += 3; } //////////////////////////////////////////////////////////////////////////// // //////////////////////////////////////////////////////////////////////////// { rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, Strings.get("doc.column_info"), styleTitle, row); List<FieldModel> fields = table.getFields(); short top = BD_RECT; short bottom = CellStyle.BORDER_THIN; if (0 == fields.size()) { bottom = BD_RECT; } rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 2, Strings.get("doc.no"), styleManager.get(defStyleLabel, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(3, 6, Strings.get("doc.logic_name"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(9, 6, Strings.get("doc.physical_name"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(15, 6, Strings.get("doc.column_type"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(21, 3, Strings.get("doc.not_null"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(24, 3, Strings.get("doc.default_value"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(27, 11, Strings.get("doc.comment"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 3, 8)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 9, 14)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 15, 20)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 21, 23)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 24, 26)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 27, 37)); IndexModel primaryIndex = table.getPrimaryIndex(); rowIndex++; for (int i = 0; i < fields.size(); i++) { int bufRowIndex = rowIndex + i; FieldModel field = fields.get(i); top = CellStyle.BORDER_DOTTED; bottom = CellStyle.BORDER_DOTTED; if (i == 0) { top = CellStyle.BORDER_THIN; } if (i + 1 == fields.size()) { bottom = BD_RECT; } XSSFCellStyle defStylePK = defStyleListValue; if (null != primaryIndex) { if (null != primaryIndex.getField(field.getName())) { defStylePK = defStyleListValuePK; } } String type = field.getType().getLabel(); if (StringUtility.isNotEmpty(field.getExtra())) { type += " " + field.getExtra(); } row = sheet.createRow(bufRowIndex); /////////////////////////////////////////// cell = createCell(1, 2, String.format("%d", i + 1), styleManager.get(defStyleListValueNo, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(3, 6, field.getLabel(), styleManager.get(defStylePK, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(9, 6, field.getName(), styleManager.get(defStylePK, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(15, 6, type, styleManager.get(defStylePK, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(21, 3, toTrue(field.isNotNull()), styleManager.get(defStyleListValueCenter, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(24, 3, toDefault(field), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(27, 11, field.getComment(), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 3, 8)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 9, 14)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 15, 20)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 21, 23)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 24, 26)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 27, 37)); } rowIndex += fields.size(); } //////////////////////////////////////////////////////////////////////////// // //////////////////////////////////////////////////////////////////////////// { rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, Strings.get("doc.index_info"), styleTitle, row); List<IndexModel> indexs = table.getIndexs(); short top = BD_RECT; short bottom = CellStyle.BORDER_THIN; if (0 == indexs.size()) { bottom = BD_RECT; } rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 2, Strings.get("doc.no"), styleManager.get(defStyleLabel, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(3, 6, Strings.get("doc.index_name"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(9, 12, Strings.get("doc.column_list"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(21, 3, Strings.get("doc.primary_key"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(24, 3, Strings.get("doc.unique_key"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(27, 11, Strings.get("doc.comment"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 3, 8)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 9, 20)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 21, 23)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 24, 26)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 27, 37)); rowIndex++; for (int i = 0; i < indexs.size(); i++) { int bufRowIndex = rowIndex + i; IndexModel index = indexs.get(i); top = CellStyle.BORDER_DOTTED; bottom = CellStyle.BORDER_DOTTED; if (i == 0) { top = CellStyle.BORDER_THIN; } if (i + 1 == indexs.size()) { bottom = BD_RECT; } row = sheet.createRow(bufRowIndex); /////////////////////////////////////////// cell = createCell(1, 2, String.format("%d", i + 1), styleManager.get(defStyleListValueNo, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(3, 6, index.getName(), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(9, 12, toColumnList(index), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(21, 3, toTrue(index.isPrimaryKey()), styleManager.get(defStyleListValueCenter, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(24, 3, toTrue(index.isUnique()), styleManager.get(defStyleListValueCenter, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(27, 11, index.getComment(), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 3, 8)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 9, 20)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 21, 23)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 24, 26)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 27, 37)); } rowIndex += indexs.size(); } //////////////////////////////////////////////////////////////////////////// // //////////////////////////////////////////////////////////////////////////// { rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, Strings.get("doc.foreign_key_info"), styleTitle, row); List<ForeignKeyModel> foreignKeys = table.getForeignKeys(); short top = BD_RECT; short bottom = CellStyle.BORDER_THIN; if (0 == foreignKeys.size()) { bottom = BD_RECT; } rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 2, Strings.get("doc.no"), styleManager.get(defStyleLabel, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(3, 6, Strings.get("doc.foreign_key_name"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(9, 12, Strings.get("doc.column_list"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(21, 6, Strings.get("doc.ref_table"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(27, 11, Strings.get("doc.ref_column_list"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 3, 8)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 9, 20)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 21, 26)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 27, 37)); rowIndex++; for (int i = 0; i < foreignKeys.size(); i++) { int bufRowIndex = rowIndex + i; ForeignKeyModel foreignKey = foreignKeys.get(i); Hyperlink link = createTableLink(foreignKey.getReferenceTableName()); top = CellStyle.BORDER_DOTTED; bottom = CellStyle.BORDER_DOTTED; if (i == 0) { top = CellStyle.BORDER_THIN; } if (i + 1 == foreignKeys.size()) { bottom = BD_RECT; } row = sheet.createRow(bufRowIndex); /////////////////////////////////////////// cell = createCell(1, 2, String.format("%d", i + 1), styleManager.get(defStyleListValueNo, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(3, 6, foreignKey.getName(), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(9, 12, toForeignKeyColumnList(foreignKey), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(21, 6, foreignKey.getReferenceTableName(), styleManager.get(defStyleListValueLink, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), link, row); cell = createCell(27, 11, toForeignKeyRefColumnList(foreignKey), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 3, 8)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 9, 20)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 21, 26)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 27, 37)); } rowIndex += foreignKeys.size(); } //////////////////////////////////////////////////////////////////////////// // (Ref) //////////////////////////////////////////////////////////////////////////// { rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, Strings.get("doc.foreign_key_info_ref"), styleTitle, row); int size = 0; for (TableModel targetTable : datasource.getTables()) { if (targetTable.equals(table)) { continue; } List<ForeignKeyModel> foreignKeys = targetTable.getForeignKeys(); for (ForeignKeyModel foreignKey : foreignKeys) { if (!foreignKey.getReferenceTableName().equals(table.getName())) { continue; } size++; } } short top = BD_RECT; short bottom = CellStyle.BORDER_THIN; if (0 == size) { bottom = BD_RECT; } rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 2, Strings.get("doc.no"), styleManager.get(defStyleLabel, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(3, 6, Strings.get("doc.foreign_key_name"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(9, 12, Strings.get("doc.column_list"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(21, 6, Strings.get("doc.ref_former_table"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(27, 11, Strings.get("doc.ref_former_column_list"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 3, 8)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 9, 20)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 21, 26)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 27, 37)); int cnt = 0; rowIndex++; for (TableModel targetTable : datasource.getTables()) { if (targetTable.equals(table)) { continue; } List<ForeignKeyModel> foreignKeys = targetTable.getForeignKeys(); for (ForeignKeyModel foreignKey : foreignKeys) { if (!foreignKey.getReferenceTableName().equals(table.getName())) { continue; } int bufRowIndex = rowIndex + cnt; Hyperlink link = createTableLink(targetTable.getName()); top = CellStyle.BORDER_DOTTED; bottom = CellStyle.BORDER_DOTTED; if (cnt == 0) { top = CellStyle.BORDER_THIN; } if (cnt + 1 == size) { bottom = BD_RECT; } row = sheet.createRow(bufRowIndex); /////////////////////////////////////////// cell = createCell(1, 2, String.format("%d", cnt + 1), styleManager.get(defStyleListValueNo, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(3, 6, foreignKey.getName(), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(9, 12, toForeignKeyRefColumnList(foreignKey), styleManager.get( defStyleListValue, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(21, 6, targetTable.getName(), styleManager.get(defStyleListValueLink, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), link, row); cell = createCell(27, 11, toForeignKeyColumnList(foreignKey), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 3, 8)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 9, 20)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 21, 26)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 27, 37)); cnt++; } } rowIndex += cnt; } workbook.setPrintArea(workbook.getSheetIndex(getTableSheetName(table.getName())), 0, 38, 0, rowIndex); sheet.setAutobreaks(true); XSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setFitWidth((short) 1); printSetup.setScale((short) 95); return sheet; }
From source file:org.jboss.windup.reporting.spreadsheet.ScorecardReporter.java
License:Open Source License
@Override public void process(ArchiveMetadata archive, File reportDirectory) { Validate.notNull(archive, "Archive is required, but null."); Validate.notNull(reportDirectory, "Report directory is required, but null."); File output = generateScorecardName(archive, reportDirectory); List<ArchiveMetadata> results = unwind(archive); FileOutputStream out = null;//from ww w. j av a2 s .c o m try { out = new FileOutputStream(output); XSSFWorkbook workbook = new XSSFWorkbook(); // create a new sheet XSSFSheet s = workbook.createSheet("MigrationScoreCard"); s.setColumnWidth(0, 70 * 256); s.setColumnWidth(1, 20 * 256); s.setColumnWidth(2, 10 * 256); s.setColumnWidth(3, 255 * 256); appendTitleRow(workbook, s, 0); int rownum = 1; for (ArchiveMetadata result : results) { StringBuilder notes = new StringBuilder(); for (AbstractDecoration dr : result.getDecorations()) { if (dr instanceof Version) { notes.append(dr.toString()); } } Set<String> classifications = new HashSet<String>(); double estimate = 0; // calculate the nodes.. for (FileMetadata ir : result.getEntries()) { for (AbstractDecoration dr : ir.getDecorations()) { if (dr instanceof Classification) { String tempDesc = dr.getDescription(); tempDesc = StringUtils.removeStart(tempDesc, "Classification: "); classifications.add(tempDesc); } if (dr.getEffort() != null && dr.getEffort() instanceof StoryPointEffort) { estimate += ((StoryPointEffort) dr.getEffort()).getHours(); } } } if (classifications.size() > 0) { for (String classification : classifications) { if (LOG.isDebugEnabled()) { LOG.debug("Found: " + classification); } notes.append(", ").append(classification); } } String notesExtracted = StringUtils.removeStart(notes.toString(), ", "); appendNotesRow(workbook, s, rownum, result.getRelativePath(), estimate, notesExtracted); rownum++; } appendTotalRow(workbook, s, rownum++); // empty row. rownum++; appendMentoringTitleRow(workbook, s, rownum++); int start = rownum + 1; appendNotesRow(workbook, s, rownum++, "JBoss Configuration / Documentation / Mentoring", 80, ""); appendNotesRow(workbook, s, rownum++, "JBoss Server Setup for Apps", 80, ""); appendNotesRow(workbook, s, rownum++, "JBoss Operations Network Setup / Documentation / Mentoring", 120, ""); appendNotesRow(workbook, s, rownum++, "Deployment / Fail Over Plans", 80, ""); int end = rownum; appendMentoringTotalRow(workbook, s, rownum++, start, end); // write the workbook to the output stream // close our file (don't blow out our file handles workbook.write(out); } catch (IOException e) { LOG.error("Exception writing scorecard to: " + output.getAbsolutePath()); } finally { IOUtils.closeQuietly(out); } }
From source file:org.kuali.test.runner.output.PoiHelper.java
License:Educational Community License
private void copySheets(XSSFSheet newSheet, XSSFSheet sheet) { int maxColumnNum = 0; Map<Integer, XSSFCellStyle> styleMap = new HashMap<Integer, XSSFCellStyle>(); int mergedReqionsCount = sheet.getNumMergedRegions(); for (int i = 0; i < mergedReqionsCount; ++i) { newSheet.addMergedRegion(sheet.getMergedRegion(i)); }// www .j av a 2s .c om for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { XSSFRow srcRow = sheet.getRow(i); XSSFRow destRow = newSheet.createRow(i); if (srcRow != null) { copyRow(srcRow, destRow, styleMap); if (srcRow.getLastCellNum() > maxColumnNum) { maxColumnNum = srcRow.getLastCellNum(); } } } for (int i = 0; i <= maxColumnNum; i++) { newSheet.setColumnWidth(i, sheet.getColumnWidth(i)); } }
From source file:org.openelis.bean.DataViewReportBean.java
License:Open Source License
/** * Creates and returns a workbook that gets converted to an Excel file; each * row in the workbook shows fields from some part of a sample and analytes * and values/*from w w w .j a v a 2 s .com*/ * * @param results * the list of VOs containing result info to be shown * @param auxiliary * the list of VOs containing aux data info to be shown * @param noResAux * the list of VOs containing info to be shown when both results and * aux data are excluded * @param testAnaResMap * the map containing result analytes and values selected by the * user; if an analyte or value is not in the map, the result is not * shown * @param auxFieldValMap * the map containing aux data analytes and values selected by the * user; if an analyte or value is not in the map, the aux data is * not shown * @param moduleName * the name of a security module for the logged in user; the module's * clause is used to restrict the fetched data to specific records * e.g. organizations * @param showReportableColumnsOnly * if true, only reportable column analytes are shown * @param headers * the list of labels for the column headers * @param data * the VO containing the user's choices for the data shown e.g. the * meta keys for selected columns and "include" and "exclude" flags * @param smMap * the map that provides the data for the columns belonging to * various parts of a sample e.g. domain, organization, project etc. * @param status * the percent completion in this ReportStatus is updated every time * a new row is added to the workbook */ private XSSFWorkbook getWorkbook(List<DataViewResultVO> results, List<DataViewResultVO> auxiliary, List<DataViewResultVO> noResAux, HashMap<Integer, HashSet<String>> testAnaResMap, HashMap<Integer, HashSet<String>> auxFieldValMap, String moduleName, boolean showReportableColumnsOnly, ArrayList<String> headers, DataView1VO data, HashMap<Integer, SampleManager1> smMap, ReportStatus status) throws Exception { boolean excludeOverride, excludeRes, excludeAux, samOverridden, anaOverridden, addRow; int i, j, resIndex, auxIndex, noResAuxIndex, rowIndex, numRes, numAux, numNoResAux, lastCol, currCol; Integer samId, prevSamId, resAccNum, auxAccNum, itemId, anaId, prevAnaId, anaIndex; String value; SampleManager1 sm; XSSFWorkbook wb; XSSFSheet sheet; DataViewResultVO res; ResultViewDO rowRes, colRes; Row currRow, prevRow; RowData rd; Cell cell; ArrayList<Integer> maxChars; ArrayList<ResultViewDO> smResults; HashMap<String, Integer> colAnaMap; HashMap<Integer, HashSet<String>> anaValMap; numRes = results == null ? 0 : results.size(); numAux = auxiliary == null ? 0 : auxiliary.size(); numNoResAux = noResAux == null ? 0 : noResAux.size(); excludeOverride = "Y".equals(data.getExcludeResultOverride()); excludeRes = "Y".equals(data.getExcludeResults()); excludeAux = "Y".equals(data.getExcludeAuxData()); resIndex = 0; auxIndex = 0; noResAuxIndex = 0; lastCol = 0; currCol = 0; rowIndex = 1; prevSamId = null; prevAnaId = null; anaIndex = null; samOverridden = false; anaOverridden = false; currRow = null; prevRow = null; sm = null; wb = new XSSFWorkbook(); sheet = wb.createSheet(); colAnaMap = new HashMap<String, Integer>(); maxChars = new ArrayList<Integer>(); rd = new RowData(); status.setMessage(Messages.get().report_genDataView()); status.setPercentComplete(0); session.setAttribute("DataViewReportStatus", status); /* * the lists of results and aux data are iterated through until there * are no more elements left in each of them to read from */ while (resIndex < numRes || auxIndex < numAux || noResAuxIndex < numNoResAux) { /* * the user wants to stop the report */ if (ReportStatus.Status.CANCEL.equals(status.getStatus())) { status.setMessage(Messages.get().report_stopped()); return null; } status.setPercentComplete( 100 * (resIndex + auxIndex + noResAuxIndex) / (numRes + numAux + numNoResAux)); res = null; anaValMap = null; value = null; if (excludeRes && excludeAux) { res = noResAux.get(noResAuxIndex++); } else { if (resIndex < numRes && auxIndex < numAux) { resAccNum = results.get(resIndex).getSampleAccessionNumber(); auxAccNum = auxiliary.get(auxIndex).getSampleAccessionNumber(); /* * if this result's accession number is less than or equal * to this aux data's, add a row for this result, otherwise * add a row for the aux data; this makes sure that the * results for a sample are shown before the aux data; * accession numbers are compared instead of sample ids * because the former is the field shown in the report and * not the latter */ if (resAccNum <= auxAccNum) { res = results.get(resIndex++); anaValMap = testAnaResMap; } else { res = auxiliary.get(auxIndex++); anaValMap = auxFieldValMap; } } else if (resIndex < numRes) { /* * no more aux data left to show */ res = results.get(resIndex++); anaValMap = testAnaResMap; } else if (auxIndex < numAux) { /* * no more results left to show */ res = auxiliary.get(auxIndex++); anaValMap = auxFieldValMap; } } samId = res.getSampleId(); itemId = res.getSampleItemId(); anaId = res.getAnalysisId(); if (!samId.equals(prevSamId)) { /* * don't show any data for this sample if it's overridden and * such samples are excluded; whether the sample is overridden * is checked even if such samples are not excluded because * overridden result values are not shown in the report */ sm = smMap.get(samId); samOverridden = false; if ((getSampleQAs(sm) != null)) { for (SampleQaEventViewDO sqa : getSampleQAs(sm)) { if (Constants.dictionary().QAEVENT_OVERRIDE.equals(sqa.getTypeId())) { samOverridden = true; if (excludeOverride) prevSamId = samId; break; } } } } if (samOverridden && excludeOverride) { prevSamId = samId; continue; } /* * don't show any data for this analysis if it's overridden and such * analyses are excluded; whether the analysis is overridden is * checked even if such analyses are not excluded because overridden * values are not shown in the report */ if (anaId != null) { if (!anaId.equals(prevAnaId)) { anaOverridden = false; if ((getAnalysisQAs(sm) != null)) { for (AnalysisQaEventViewDO aqa : getAnalysisQAs(sm)) { if (aqa.getAnalysisId().equals(anaId) && Constants.dictionary().QAEVENT_OVERRIDE.equals(aqa.getTypeId())) { anaOverridden = true; if (excludeOverride) break; } } } } if (anaOverridden && excludeOverride) { prevSamId = samId; prevAnaId = anaId; continue; } } if (anaValMap != null) { /* * show this result or aux data only if its value was selected * by the user */ value = getValue(anaValMap, res.getAnalyteId(), res.getValue(), res.getTypeId()); if (value == null) { prevSamId = samId; prevAnaId = anaId; continue; } } currRow = sheet.createRow(rowIndex++); /* * fill the passed row's cells for all columns except the ones for * analytes and values */ setBaseCells(sm, itemId, anaId, rd, data.getColumns(), moduleName != null, wb, currRow, maxChars); if (value != null) { /* * this row is for either a result or aux data; show the analyte */ cell = currRow.createCell(currRow.getPhysicalNumberOfCells()); setCellValue(cell, res.getAnalyteName(), null); setMaxChars(cell.getColumnIndex(), res.getAnalyteName(), maxChars, null); cell = currRow.createCell(currRow.getPhysicalNumberOfCells()); if (anaId != null && !excludeRes) { /* * this row is for a result; show the value only if the * analysis and sample are not overridden */ if (!anaOverridden && !samOverridden) setCellValue(cell, value, null); setMaxChars(cell.getColumnIndex(), cell.getStringCellValue(), maxChars, null); /* * if this analyte has column analytes, show them in the * header and their values in the columns; results for a * sample can be null if it has no results with values but * has aux data with values and aux data is not excluded */ smResults = getResults(sm); if (smResults != null) { for (i = 0; i < smResults.size(); i++) { rowRes = smResults.get(i); if (!res.getId().equals(rowRes.getId())) continue; j = i + 1; if (j < smResults.size() && "Y".equals(smResults.get(j).getIsColumn())) { /* * this analyte has column analytes; "lastCol" * is the right-most column in the workbook; if * an analyte doesn't have a column yet, that * column will be added after "lastCol"; * "currCol" keeps track of the current column */ if (lastCol == 0) lastCol = currRow.getPhysicalNumberOfCells(); currCol = currRow.getPhysicalNumberOfCells(); while (j < smResults.size()) { colRes = smResults.get(j++); if ("N".equals(colRes.getIsColumn())) break; if (showReportableColumnsOnly && "N".equals(colRes.getIsReportable())) continue; anaIndex = colAnaMap.get(colRes.getAnalyte()); /* * if this column analyte's name is not * found in the map, create a new column and * start adding values in it; set the value * in this cell if the analyte is shown in * this column; if the analyte is not shown * in this column, find the column in which * it is shown and set the value */ if (anaIndex == null) { anaIndex = lastCol++; colAnaMap.put(colRes.getAnalyte(), anaIndex); headers.add(colRes.getAnalyte()); setMaxChars(cell.getColumnIndex(), colRes.getAnalyte(), maxChars, null); cell = currRow.createCell(anaIndex); } else if (anaIndex == currCol) { cell = currRow.createCell(currCol++); } else { cell = currRow.createCell(anaIndex); } /* * set the value if the analysis and sample * are not overridden */ if (!anaOverridden && !samOverridden) setCellValue(cell, getValue(colRes.getValue(), colRes.getTypeId()), null); setMaxChars(cell.getColumnIndex(), cell.getStringCellValue(), maxChars, null); } } } } } else { /* * this row is for an aux data; show the value */ setCellValue(cell, value, null); setMaxChars(cell.getColumnIndex(), value, maxChars, null); } } prevAnaId = anaId; prevSamId = samId; /* * an empty row can't be created and then added to the sheet, it has * to be obtained from the sheet; thus it has to be removed if it * shouldn't be shown because it has the same data as the previous * row in all cells; this can happen if e.g. a user selects only * container and sample type but all sample items in a sample have * the same values for these fields */ if (isSameDataInRows(currRow, prevRow)) { sheet.removeRow(currRow); rowIndex--; } else { prevRow = currRow; } } /* * add the header row and set the header labels for all columns */ setHeaderCells(sheet, wb, headers, maxChars); /* * make each column wide enough to show the longest string in it; the * width for each column is set as the maximum number of characters in * that column multiplied by 256; this is because the default width of * one character is 1/256 units in Excel */ for (i = 0; i < maxChars.size(); i++) sheet.setColumnWidth(i, maxChars.get(i) * 256); return wb; }
From source file:org.ramalapure.userinfoapp.UserInfoApp.java
@Override public void start(Stage primaryStage) { CheckConnection();/*ww w.ja v a 2s.c o m*/ fillComboBox(); // create transperant stage //primaryStage.initStyle(StageStyle.TRANSPARENT); primaryStage.setTitle("JavaFX 8 Tutorial 61 - Retrive Database Values Into CheckBox"); primaryStage.getIcons().add(new Image("file:user-icon.png")); BorderPane layout = new BorderPane(); Scene newscene = new Scene(layout, 1200, 700, Color.rgb(0, 0, 0, 0)); Group root = new Group(); Scene scene = new Scene(root, 320, 200, Color.rgb(0, 0, 0, 0)); scene.getStylesheets().add(getClass().getResource("Style.css").toExternalForm()); Color foreground = Color.rgb(255, 255, 255, 0.9); //Rectangila Background Rectangle background = new Rectangle(320, 250); background.setX(0); background.setY(0); background.setArcHeight(15); background.setArcWidth(15); background.setFill(Color.rgb(0, 0, 0, 0.55)); background.setStroke(foreground); background.setStrokeWidth(1.5); VBox vbox = new VBox(5); vbox.setPadding(new Insets(10, 0, 0, 10)); Label label = new Label("Label"); //label.setTextFill(Color.WHITESMOKE); label.setFont(new Font("SanSerif", 20)); TextField username = new TextField(); username.setFont(Font.font("SanSerif", 20)); username.setPromptText("Username"); username.getStyleClass().add("field-background"); PasswordField password = new PasswordField(); password.setFont(Font.font("SanSerif", 20)); password.setPromptText("Password"); password.getStyleClass().add("field-background"); password.setOnKeyPressed(e -> { if (e.getCode() == KeyCode.ENTER) { try { String query = "select * from UserDatabase where Username=? and Password=?"; pst = conn.prepareStatement(query); pst.setString(1, username.getText()); pst.setString(2, password.getText()); rs = pst.executeQuery(); if (rs.next()) { //label.setText("Login Successful"); primaryStage.setScene(newscene); primaryStage.show(); } else { label.setText("Login Failed"); } username.clear(); password.clear(); pst.close(); rs.close(); } catch (Exception e1) { label.setText("SQL Error"); System.err.println(e1); } } }); Button btn = new Button("Login"); btn.setFont(Font.font("SanSerif", 15)); btn.setOnAction(e -> { try { String query = "select * from UserDatabase where Username=? and Password=?"; pst = conn.prepareStatement(query); pst.setString(1, username.getText()); pst.setString(2, password.getText()); rs = pst.executeQuery(); if (rs.next()) { //label.setText("Login Successful"); primaryStage.setScene(newscene); primaryStage.show(); } else { label.setText("Login Failed"); } username.clear(); password.clear(); pst.close(); rs.close(); } catch (Exception e1) { label.setText("SQL Error"); System.err.println(e1); } }); vbox.getChildren().addAll(label, username, password, btn); root.getChildren().addAll(background, vbox); Button logout = new Button("Logout"); logout.setFont(Font.font("SanSerif", 15)); logout.setOnAction(e -> { primaryStage.setScene(scene); primaryStage.show(); }); layout.setTop(logout); BorderPane.setAlignment(logout, Pos.TOP_RIGHT); BorderPane.setMargin(logout, new Insets(10)); VBox fields = new VBox(5); searchField = new TextField(); searchField.setFont(Font.font("SanSerif", 15)); searchField.setPromptText("Search"); searchField.setMaxWidth(200); Label label1 = new Label("Create New User"); label1.setFont(new Font("Sanserif", 20)); id = new TextField(); id.setFont(Font.font("SanSerif", 15)); id.setPromptText("ID"); id.setMaxWidth(200); fn = new TextField(); fn.setFont(Font.font("SanSerif", 15)); fn.setPromptText("First Name"); fn.setMaxWidth(200); ln = new TextField(); ln.setFont(Font.font("SanSerif", 15)); ln.setPromptText("Last Name"); ln.setMaxWidth(200); em = new TextField(); em.setFont(Font.font("SanSerif", 15)); em.setPromptText("Email"); em.setMaxWidth(200); mobile = new TextField(); mobile.setFont(Font.font("SanSerif", 15)); mobile.setPromptText("Mobile No."); mobile.setMaxWidth(200); un = new TextField(); un.setFont(Font.font("SanSerif", 15)); un.setPromptText("Username"); un.setMaxWidth(200); pw = new PasswordField(); pw.setFont(Font.font("SanSerif", 15)); pw.setPromptText("Password"); pw.setMaxWidth(200); date = new DatePicker(); date.setPromptText("Date of Birth"); date.setMaxWidth(200); date.setStyle("-fx-font-size:15"); ToggleGroup gender = new ToggleGroup(); male = new RadioButton("Male"); male.setToggleGroup(gender); male.setOnAction(e -> { radioButtonLabel = male.getText(); }); female = new RadioButton("Female"); female.setToggleGroup(gender); female.setOnAction(e -> { radioButtonLabel = female.getText(); }); checkBox1 = new CheckBox("Playing"); checkBox1.setOnAction(e -> { checkBoxList.add(checkBox1.getText()); }); checkBox2 = new CheckBox("Singing"); checkBox2.setOnAction(e -> { checkBoxList.add(checkBox2.getText()); }); checkBox3 = new CheckBox("Dancing"); checkBox3.setOnAction(e -> { checkBoxList.add(checkBox3.getText()); }); date.requestFocus(); male.requestFocus(); female.requestFocus(); checkBox1.requestFocus(); checkBox2.requestFocus(); checkBox3.requestFocus(); textArea = new TextArea(); textArea.setFont(Font.font("SanSerif", 12)); textArea.setPromptText("Path Of Selected File Or Files"); textArea.setPrefSize(300, 50); textArea.setEditable(false); fileChooser = new FileChooser(); fileChooser.getExtensionFilters().addAll(new ExtensionFilter("Text Files", "*txt"), new ExtensionFilter("Image Files", "*.png", "*.jpg", "*.gif"), new ExtensionFilter("Audio Files", "*wav", "*.mp3", "*.aac"), new ExtensionFilter("All Files", "*.*")); browse = new Button("Browse"); browse.setFont(Font.font("SanSerif", 15)); browse.setOnAction(e -> { //Single File Selection file = fileChooser.showOpenDialog(primaryStage); if (file != null) { textArea.setText(file.getAbsolutePath()); image = new Image(file.toURI().toString(), 100, 150, true, true);//path, PrefWidth, PrefHeight, PreserveRatio, Smooth imageView = new ImageView(image); imageView.setFitWidth(100); imageView.setFitHeight(150); imageView.setPreserveRatio(true); layout.setCenter(imageView); BorderPane.setAlignment(imageView, Pos.TOP_LEFT); } //Multiple File Selection /*List<File> fileList = fileChooser.showOpenMultipleDialog(primaryStage); if(fileList != null){ fileList.stream().forEach(selectedFiles ->{ textArea.setText(fileList.toString()); }); }*/ }); Button button = new Button("Save"); button.setFont(Font.font("SanSerif", 15)); button.setOnAction(e -> { if (validateNumber() & validateFirstName() & validateLastName() & validateEmaill() & validateMobileNo() & validatePassword() & validateFields()) { try { String query = "INSERT INTO UserDatabase (ID, FirstName, LastName, Email, Username, Password, DOB, Gender, MobileNo, Hobbies, Image) VALUES(?,?,?,?,?,?,?,?,?,?,?)"; pst = conn.prepareStatement(query); pst.setString(1, id.getText()); pst.setString(2, fn.getText()); pst.setString(3, ln.getText()); pst.setString(4, em.getText()); pst.setString(5, un.getText()); pst.setString(6, pw.getText()); pst.setString(7, ((TextField) date.getEditor()).getText()); pst.setString(8, radioButtonLabel); pst.setString(9, mobile.getText()); pst.setString(10, checkBoxList.toString()); fis = new FileInputStream(file); pst.setBinaryStream(11, (InputStream) fis, (int) file.length()); Alert alert = new Alert(AlertType.INFORMATION); alert.setTitle("Information Dialog"); alert.setHeaderText(null); alert.setContentText("User has been created."); alert.showAndWait(); pst.execute(); pst.close(); clearFields(); } catch (SQLException | FileNotFoundException e1) { label.setText("SQL Error"); System.err.println(e1); } fillComboBox(); refreshTable(); } }); Button update = new Button("Update"); update.setFont(Font.font("SanSerif", 15)); update.setOnAction(e -> { if (validateNumber() & validateFirstName() & validateLastName() & validateEmaill() & validateMobileNo() & validatePassword() & validateFields()) { try { String query = "update UserDatabase set ID=?, FirstName=?, LastName=?, Email=?, Username=?, Password=?, DOB=?, Gender=?, MobileNo=?, Hobbies=?, Image=? where ID='" + id.getText() + "' "; pst = conn.prepareStatement(query); pst.setString(1, id.getText()); pst.setString(2, fn.getText()); pst.setString(3, ln.getText()); pst.setString(4, em.getText()); pst.setString(5, un.getText()); pst.setString(6, pw.getText()); pst.setString(7, ((TextField) date.getEditor()).getText()); pst.setString(8, radioButtonLabel); pst.setString(9, mobile.getText()); pst.setString(10, checkBoxList.toString()); fis = new FileInputStream(file); pst.setBinaryStream(11, (InputStream) fis, (int) file.length()); Alert alert = new Alert(AlertType.INFORMATION); alert.setTitle("Information Dialog"); alert.setHeaderText(null); alert.setContentText("User details has been updated."); alert.showAndWait(); pst.execute(); pst.close(); clearFields(); } catch (SQLException | FileNotFoundException e1) { label.setText("SQL Error"); System.err.println(e1); } fillComboBox(); refreshTable(); } }); fields.getChildren().addAll(searchField, label1, id, fn, ln, em, mobile, un, pw, date, male, female, checkBox1, checkBox2, checkBox3, browse, textArea, button); layout.setLeft(fields); BorderPane.setMargin(fields, new Insets(0, 10, 0, 10)); table = new TableView<>(); TableColumn column1 = new TableColumn("ID"); column1.setMaxWidth(50); column1.setCellValueFactory(new PropertyValueFactory<>("ID")); TableColumn column2 = new TableColumn("First Name"); column2.setMinWidth(80); column2.setCellValueFactory(new PropertyValueFactory<>("firstName")); TableColumn column3 = new TableColumn("Last Name"); column3.setMinWidth(80); column3.setCellValueFactory(new PropertyValueFactory<>("lastName")); TableColumn column4 = new TableColumn("Email"); column4.setMinWidth(150); column4.setCellValueFactory(new PropertyValueFactory<>("email")); TableColumn column5 = new TableColumn("Username"); column5.setMinWidth(80); column5.setCellValueFactory(new PropertyValueFactory<>("username")); TableColumn column6 = new TableColumn("Password"); column6.setMinWidth(80); column6.setCellValueFactory(new PropertyValueFactory<>("password")); TableColumn column7 = new TableColumn("DOB"); column7.setMinWidth(70); column7.setCellValueFactory(new PropertyValueFactory<>("DOB")); TableColumn column8 = new TableColumn("Gender"); column8.setMinWidth(50); column8.setCellValueFactory(new PropertyValueFactory<>("Gender")); TableColumn column9 = new TableColumn("Mobile No."); column9.setMinWidth(70); column9.setCellValueFactory(new PropertyValueFactory<>("MobileNo")); TableColumn column10 = new TableColumn("Hobbies"); column10.setMinWidth(100); column10.setCellValueFactory(new PropertyValueFactory<>("Hobbies")); table.getColumns().addAll(column1, column2, column3, column4, column5, column6, column7, column8, column9, column10); table.setTableMenuButtonVisible(true); ScrollPane sp = new ScrollPane(table); //sp.setContent(table); sp.setPrefSize(600, 200); sp.setHbarPolicy(ScrollPane.ScrollBarPolicy.AS_NEEDED); sp.setVbarPolicy(ScrollPane.ScrollBarPolicy.AS_NEEDED); sp.setFitToHeight(true); sp.setHmax(3); sp.setHvalue(0); sp.setDisable(false); layout.setRight(sp); BorderPane.setMargin(sp, new Insets(0, 10, 10, 10)); Button load = new Button("Load Table"); load.setFont(Font.font("SanSerif", 15)); load.setOnAction(e -> { refreshTable(); }); ComboBox comboBox = new ComboBox(options); comboBox.setMaxHeight(30); comboBox.setOnAction(e -> { try { String query = "select * from UserDatabase where FirstName = ?"; pst = conn.prepareStatement(query); pst.setString(1, (String) comboBox.getSelectionModel().getSelectedItem()); rs = pst.executeQuery(); while (rs.next()) { id.setText(rs.getString("ID")); fn.setText(rs.getString("FirstName")); ln.setText(rs.getString("LastName")); em.setText(rs.getString("Email")); mobile.setText(rs.getString("MobileNo")); un.setText(rs.getString("Username")); pw.setText(rs.getString("Password")); ((TextField) date.getEditor()).setText(rs.getString("DOB")); if ("Male".equals(rs.getString("Gender"))) { male.setSelected(true); } else if ("Female".equals(rs.getString("Gender"))) { female.setSelected(true); } else { male.setSelected(false); female.setSelected(false); } } pst.close(); rs.close(); } catch (SQLException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } }); Button delete = new Button("Delete User"); delete.setFont(Font.font("SanSerif", 15)); delete.setOnAction(e -> { Alert alert = new Alert(AlertType.CONFIRMATION); alert.setTitle("Confirmation Dialog"); alert.setHeaderText(null); alert.setContentText("Are you sure to delete?"); Optional<ButtonType> action = alert.showAndWait(); if (action.get() == ButtonType.OK) { try { String query = "delete from UserDatabase where id = ?"; pst = conn.prepareStatement(query); pst.setString(1, id.getText()); pst.executeUpdate(); pst.close(); } catch (SQLException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } } clearFields(); fillComboBox(); refreshTable(); }); exportToXL = new Button("Export To Excel"); exportToXL.setFont(Font.font("Sanserif", 15)); exportToXL.setOnAction(e -> { try { String query = "Select * from UserDatabase"; pst = conn.prepareStatement(query); rs = pst.executeQuery(); //Apache POI Jar Link- //http://a.mbbsindia.com/poi/release/bin/poi-bin-3.13-20150929.zip XSSFWorkbook wb = new XSSFWorkbook();//for earlier version use HSSF XSSFSheet sheet = wb.createSheet("User Details"); XSSFRow header = sheet.createRow(0); header.createCell(0).setCellValue("ID"); header.createCell(1).setCellValue("First Name"); header.createCell(2).setCellValue("Last Name"); header.createCell(3).setCellValue("Email"); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.setColumnWidth(3, 256 * 25);//256-character width sheet.setZoom(150);//scale-150% int index = 1; while (rs.next()) { XSSFRow row = sheet.createRow(index); row.createCell(0).setCellValue(rs.getString("ID")); row.createCell(1).setCellValue(rs.getString("FirstName")); row.createCell(2).setCellValue(rs.getString("LastName")); row.createCell(3).setCellValue(rs.getString("Email")); index++; } FileOutputStream fileOut = new FileOutputStream("UserDetails.xlsx");// befor 2007 version xls wb.write(fileOut); fileOut.close(); Alert alert = new Alert(AlertType.INFORMATION); alert.setTitle("Information Dialog"); alert.setHeaderText(null); alert.setContentText("User Details Exported in Excel Sheet."); alert.showAndWait(); pst.close(); rs.close(); } catch (SQLException | FileNotFoundException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } }); importXLToDB = new Button("Import XL TO DB"); importXLToDB.setFont(Font.font("Sanserif", 15)); importXLToDB.setOnAction(e -> { try { String query = "Insert into UserDatabase(ID, FirstName, LastName, Email) values (?,?,?,?)"; pst = conn.prepareStatement(query); FileInputStream fileIn = new FileInputStream(new File("UserInfo.xlsx")); XSSFWorkbook wb = new XSSFWorkbook(fileIn); XSSFSheet sheet = wb.getSheetAt(0); Row row; for (int i = 1; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); pst.setInt(1, (int) row.getCell(0).getNumericCellValue()); pst.setString(2, row.getCell(1).getStringCellValue()); pst.setString(3, row.getCell(2).getStringCellValue()); pst.setString(4, row.getCell(3).getStringCellValue()); pst.execute(); } Alert alert = new Alert(AlertType.INFORMATION); alert.setTitle("Information Dialog"); alert.setHeaderText(null); alert.setContentText("User Details Imported From Excel Sheet To Database."); alert.showAndWait(); wb.close(); fileIn.close(); pst.close(); rs.close(); } catch (SQLException | FileNotFoundException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } refreshTable(); }); HBox hbox = new HBox(5); hbox.getChildren().addAll(load, delete, update, comboBox, exportToXL, importXLToDB); layout.setBottom(hbox); BorderPane.setMargin(hbox, new Insets(10, 0, 10, 10)); ListView list = new ListView(options); list.setMaxSize(100, 250); //layout.setLeft(list); //BorderPane.setMargin(list, new Insets(10)); table.setOnMouseClicked(e -> { try { User user = (User) table.getSelectionModel().getSelectedItem(); String query = "select * from UserDatabase where ID = ?"; pst = conn.prepareStatement(query); pst.setString(1, user.getID()); rs = pst.executeQuery(); while (rs.next()) { id.setText(rs.getString("ID")); fn.setText(rs.getString("FirstName")); ln.setText(rs.getString("LastName")); em.setText(rs.getString("Email")); mobile.setText(rs.getString("MobileNo")); un.setText(rs.getString("Username")); pw.setText(rs.getString("Password")); ((TextField) date.getEditor()).setText(rs.getString("DOB")); if (null != rs.getString("Gender")) switch (rs.getString("Gender")) { case "Male": male.setSelected(true); break; case "Female": female.setSelected(true); break; default: male.setSelected(false); female.setSelected(false); break; } else { male.setSelected(false); female.setSelected(false); } // Retrive Hobbies Into CheckBox if (rs.getString("Hobbies") != null) { checkBox1.setSelected(false); checkBox2.setSelected(false); checkBox3.setSelected(false); //hobbies in the string formate - [Playing , Dancing] System.out.println(rs.getString("Hobbies")); String checkBoxString = rs.getString("Hobbies").replace("[", "").replace("]", ""); System.out.println(checkBoxString); //now can converert to a list, strip out commas and spaces List<String> hobbylist = Arrays.asList(checkBoxString.split("\\s*,\\s*")); System.out.println(hobbylist); for (String hobby : hobbylist) { switch (hobby) { case "Playing": checkBox1.setSelected(true); break; case "Singing": checkBox2.setSelected(true); break; case "Dancing": checkBox3.setSelected(true); break; default: checkBox1.setSelected(false); checkBox2.setSelected(false); checkBox3.setSelected(false); } } } else { checkBox1.setSelected(false); checkBox2.setSelected(false); checkBox3.setSelected(false); } InputStream is = rs.getBinaryStream("Image"); OutputStream os = new FileOutputStream(new File("photo.jpg")); byte[] content = new byte[1024]; int size = 0; while ((size = is.read(content)) != -1) { os.write(content, 0, size); } os.close(); is.close(); image = new Image("file:photo.jpg", 100, 150, true, true); ImageView imageView1 = new ImageView(image); imageView1.setFitWidth(100); imageView1.setFitHeight(150); imageView1.setPreserveRatio(true); layout.setCenter(imageView1); BorderPane.setAlignment(imageView1, Pos.TOP_LEFT); } pst.close(); rs.close(); } catch (SQLException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } catch (FileNotFoundException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } }); table.setOnKeyReleased(e -> { if (e.getCode() == KeyCode.UP || e.getCode() == KeyCode.DOWN) { try { User user = (User) table.getSelectionModel().getSelectedItem(); String query = "select * from UserDatabase where ID = ?"; pst = conn.prepareStatement(query); pst.setString(1, user.getID()); rs = pst.executeQuery(); while (rs.next()) { id.setText(rs.getString("ID")); fn.setText(rs.getString("FirstName")); ln.setText(rs.getString("LastName")); em.setText(rs.getString("Email")); mobile.setText(rs.getString("MobileNo")); un.setText(rs.getString("Username")); pw.setText(rs.getString("Password")); ((TextField) date.getEditor()).setText(rs.getString("DOB")); if (null != rs.getString("Gender")) switch (rs.getString("Gender")) { case "Male": male.setSelected(true); break; case "Female": female.setSelected(true); break; default: male.setSelected(false); female.setSelected(false); break; } else { male.setSelected(false); female.setSelected(false); } // Retrive Hobbies Into CheckBox if (rs.getString("Hobbies") != null) { checkBox1.setSelected(false); checkBox2.setSelected(false); checkBox3.setSelected(false); //hobbies in the string formate - [Playing , Dancing] System.out.println(rs.getString("Hobbies")); String checkBoxString = rs.getString("Hobbies").replace("[", "").replace("]", ""); System.out.println(checkBoxString); //now can converert to a list, strip out commas and spaces List<String> hobbylist = Arrays.asList(checkBoxString.split("\\s*,\\s*")); System.out.println(hobbylist); for (String hobby : hobbylist) { switch (hobby) { case "Playing": checkBox1.setSelected(true); break; case "Singing": checkBox2.setSelected(true); break; case "Dancing": checkBox3.setSelected(true); break; default: checkBox1.setSelected(false); checkBox2.setSelected(false); checkBox3.setSelected(false); } } } else { checkBox1.setSelected(false); checkBox2.setSelected(false); checkBox3.setSelected(false); } InputStream is = rs.getBinaryStream("Image"); OutputStream os = new FileOutputStream(new File("photo.jpg")); byte[] content = new byte[1024]; int size = 0; while ((size = is.read(content)) != -1) { os.write(content, 0, size); } os.close(); is.close(); image = new Image("file:photo.jpg", 100, 150, true, true); ImageView imageView1 = new ImageView(image); imageView1.setFitWidth(100); imageView1.setFitHeight(150); imageView1.setPreserveRatio(true); layout.setCenter(imageView1); BorderPane.setAlignment(imageView1, Pos.TOP_LEFT); } pst.close(); rs.close(); } catch (SQLException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } catch (FileNotFoundException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } } }); FilteredList<User> filteredData = new FilteredList<>(data, e -> true); searchField.setOnKeyReleased(e -> { searchField.textProperty().addListener((observableValue, oldValue, newValue) -> { filteredData.setPredicate((Predicate<? super User>) user -> { if (newValue == null || newValue.isEmpty()) { return true; } String lowerCaseFilter = newValue.toLowerCase(); if (user.getID().contains(newValue)) { return true; } else if (user.getFirstName().toLowerCase().contains(lowerCaseFilter)) { return true; } else if (user.getLastName().toLowerCase().contains(lowerCaseFilter)) { return true; } return false; }); }); SortedList<User> sortedData = new SortedList<>(filteredData); sortedData.comparatorProperty().bind(table.comparatorProperty()); table.setItems(sortedData); }); primaryStage.setScene(scene); primaryStage.show(); }
From source file:packtest.AligningCells.java
License:Apache License
public static void main(String[] args) throws IOException { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFRow row = sheet.createRow((short) 2); row.setHeightInPoints(30);// w w w . j a v a2s . co m for (int i = 0; i < 8; i++) { //column width is set in units of 1/256th of a character width sheet.setColumnWidth(i, 256 * 15); } createCell(wb, row, (short) 0, XSSFCellStyle.ALIGN_CENTER, XSSFCellStyle.VERTICAL_BOTTOM); createCell(wb, row, (short) 1, XSSFCellStyle.ALIGN_CENTER_SELECTION, XSSFCellStyle.VERTICAL_BOTTOM); createCell(wb, row, (short) 2, XSSFCellStyle.ALIGN_FILL, XSSFCellStyle.VERTICAL_CENTER); createCell(wb, row, (short) 3, XSSFCellStyle.ALIGN_GENERAL, XSSFCellStyle.VERTICAL_CENTER); createCell(wb, row, (short) 4, XSSFCellStyle.ALIGN_JUSTIFY, XSSFCellStyle.VERTICAL_JUSTIFY); createCell(wb, row, (short) 5, XSSFCellStyle.ALIGN_LEFT, XSSFCellStyle.VERTICAL_TOP); createCell(wb, row, (short) 6, XSSFCellStyle.ALIGN_RIGHT, XSSFCellStyle.VERTICAL_TOP); //center text over B4, C4, D4 row = sheet.createRow((short) 3); centerAcrossSelection(wb, row, (short) 1, (short) 3, XSSFCellStyle.VERTICAL_CENTER); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("xssf-align.xlsx"); wb.write(fileOut); fileOut.close(); wb.close(); }
From source file:packtest.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); if (args.length > 0) calendar.set(Calendar.YEAR, Integer.parseInt(args[0])); int year = calendar.get(Calendar.YEAR); XSSFWorkbook wb = new XSSFWorkbook(); Map<String, XSSFCellStyle> styles = createStyles(wb); for (int month = 0; month < 12; month++) { calendar.set(Calendar.MONTH, month); calendar.set(Calendar.DAY_OF_MONTH, 1); //create a sheet for each month XSSFSheet sheet = wb.createSheet(months[month]); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false);/*ww w. j a va 2s . co m*/ XSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setOrientation(PrintOrientation.LANDSCAPE); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //the header row: centered text in 48pt font XSSFRow headerRow = sheet.createRow(0); headerRow.setHeightInPoints(80); XSSFCell titleCell = headerRow.createCell(0); titleCell.setCellValue(months[month] + " " + year); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); //header with month titles XSSFRow monthRow = sheet.createRow(1); for (int i = 0; i < days.length; i++) { //for compatibility with HSSF we have to set column width in units of 1/256th of a character width sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1)); XSSFCell monthCell = monthRow.createCell(i * 2); monthCell.setCellValue(days[i]); monthCell.setCellStyle(styles.get("month")); } int cnt = 1, day = 1; int rownum = 2; for (int j = 0; j < 6; j++) { XSSFRow row = sheet.createRow(rownum++); row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { XSSFCell dayCell_1 = row.createCell(i * 2); XSSFCell dayCell_2 = row.createCell(i * 2 + 1); int day_of_week = calendar.get(Calendar.DAY_OF_WEEK); if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) { dayCell_1.setCellValue(day); calendar.set(Calendar.DAY_OF_MONTH, ++day); if (i == 0 || i == days.length - 1) { dayCell_1.setCellStyle(styles.get("weekend_left")); dayCell_2.setCellStyle(styles.get("weekend_right")); } else { dayCell_1.setCellStyle(styles.get("workday_left")); dayCell_2.setCellStyle(styles.get("workday_right")); } } else { dayCell_1.setCellStyle(styles.get("grey_left")); dayCell_2.setCellStyle(styles.get("grey_right")); } cnt++; } if (calendar.get(Calendar.MONTH) > month) break; } } // Write the output to a file FileOutputStream out = new FileOutputStream("calendar-" + year + ".xlsx"); wb.write(out); out.close(); wb.close(); }
From source file:poi.xssf.usermodel.examples.AligningCells.java
License:Apache License
public static void main(String[] args) throws IOException { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFRow row = sheet.createRow((short) 2); row.setHeightInPoints(30);//from w w w . j a v a 2 s . com for (int i = 0; i < 8; i++) { //column width is set in units of 1/256th of a character width sheet.setColumnWidth(i, 256 * 15); } createCell(wb, row, (short) 0, XSSFCellStyle.ALIGN_CENTER, XSSFCellStyle.VERTICAL_BOTTOM); createCell(wb, row, (short) 1, XSSFCellStyle.ALIGN_CENTER_SELECTION, XSSFCellStyle.VERTICAL_BOTTOM); createCell(wb, row, (short) 2, XSSFCellStyle.ALIGN_FILL, XSSFCellStyle.VERTICAL_CENTER); createCell(wb, row, (short) 3, XSSFCellStyle.ALIGN_GENERAL, XSSFCellStyle.VERTICAL_CENTER); createCell(wb, row, (short) 4, XSSFCellStyle.ALIGN_JUSTIFY, XSSFCellStyle.VERTICAL_JUSTIFY); createCell(wb, row, (short) 5, XSSFCellStyle.ALIGN_LEFT, XSSFCellStyle.VERTICAL_TOP); createCell(wb, row, (short) 6, XSSFCellStyle.ALIGN_RIGHT, XSSFCellStyle.VERTICAL_TOP); //center text over B4, C4, D4 row = sheet.createRow((short) 3); centerAcrossSelection(wb, row, (short) 1, (short) 3, XSSFCellStyle.VERTICAL_CENTER); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("xssf-align.xlsx"); wb.write(fileOut); fileOut.close(); }
From source file:poi.xssf.usermodel.examples.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); if (args.length > 0) calendar.set(Calendar.YEAR, Integer.parseInt(args[0])); int year = calendar.get(Calendar.YEAR); XSSFWorkbook wb = new XSSFWorkbook(); Map<String, XSSFCellStyle> styles = createStyles(wb); for (int month = 0; month < 12; month++) { calendar.set(Calendar.MONTH, month); calendar.set(Calendar.DAY_OF_MONTH, 1); //create a sheet for each month XSSFSheet sheet = wb.createSheet(months[month]); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false);/*from w ww . j av a2 s . co m*/ XSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setOrientation(PrintOrientation.LANDSCAPE); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //the header row: centered text in 48pt font XSSFRow headerRow = sheet.createRow(0); headerRow.setHeightInPoints(80); XSSFCell titleCell = headerRow.createCell(0); titleCell.setCellValue(months[month] + " " + year); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); //header with month titles XSSFRow monthRow = sheet.createRow(1); for (int i = 0; i < days.length; i++) { //for compatibility with HSSF we have to set column width in units of 1/256th of a character width sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1)); XSSFCell monthCell = monthRow.createCell(i * 2); monthCell.setCellValue(days[i]); monthCell.setCellStyle(styles.get("month")); } int cnt = 1, day = 1; int rownum = 2; for (int j = 0; j < 6; j++) { XSSFRow row = sheet.createRow(rownum++); row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { XSSFCell dayCell_1 = row.createCell(i * 2); XSSFCell dayCell_2 = row.createCell(i * 2 + 1); int day_of_week = calendar.get(Calendar.DAY_OF_WEEK); if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) { dayCell_1.setCellValue(day); calendar.set(Calendar.DAY_OF_MONTH, ++day); if (i == 0 || i == days.length - 1) { dayCell_1.setCellStyle(styles.get("weekend_left")); dayCell_2.setCellStyle(styles.get("weekend_right")); } else { dayCell_1.setCellStyle(styles.get("workday_left")); dayCell_2.setCellStyle(styles.get("workday_right")); } } else { dayCell_1.setCellStyle(styles.get("grey_left")); dayCell_2.setCellStyle(styles.get("grey_right")); } cnt++; } if (calendar.get(Calendar.MONTH) > month) break; } } // Write the output to a file FileOutputStream out = new FileOutputStream("calendar-" + year + ".xlsx"); wb.write(out); out.close(); }