List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getPrintSetup
@Override
public XSSFPrintSetup getPrintSetup()
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) { // // ww w .j av a2 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: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);//from w w w .j a va2s . c o 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.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 www . j a v a 2s.c om 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(); }
From source file:rpt.GUI.ProgramStrategist.CyclePlans.CompareDialogController.java
public void okButtonClicked() throws IOException { // create two new maps, one for currentCycleplan and one for the comparison plan Map<String, TableVariant> currentCyclePlan = new HashMap<String, TableVariant>(); Map<String, TableVariant> oldCyclePlan = new HashMap<String, TableVariant>(); Map<String, TableVariant> movedVariants = new HashMap<String, TableVariant>(); Map<String, TableVariant> changedVariants = new HashMap<String, TableVariant>(); Map<String, Map<String, String>> changedInfo = new HashMap<String, Map<String, String>>(); Map<String, String> diffValues = new HashMap<String, String>(); Statement statement;// www . j a v a 2 s . c om try { // Set current YYwWW and use to ignore variants that are no longer in production Calendar cal = Calendar.getInstance(); String currentWeek = cal.get(Calendar.YEAR) % 100 + "w" + cal.get(Calendar.WEEK_OF_YEAR); // Extract all variants in the current cycleplan and put them in an map System.out.println("Extracting current variants"); statement = RPT.conn.createStatement(); statement.setQueryTimeout(30); String query = "SELECT * FROM VARIANTS, VariantBelongsToCyclePlan WHERE " + "VariantBelongsToCyclePlan.CyclePlanID= \'" + CyclePlansController.selectedCyclePlan + "\' " + "AND VARIANTS.VariantID = VariantBelongsToCyclePlan.VariantID " + "AND EndOfProd > '" + currentWeek + "'"; ResultSet rs = statement.executeQuery(query); while (rs.next()) { TableVariant entry = new TableVariant(rs.getString("Plant"), rs.getString("Platform"), rs.getString("Vehicle"), rs.getString("Propulsion"), rs.getString("Denomination"), rs.getString("Fuel"), rs.getString("EngineFamily"), rs.getString("Generation"), "EngineName not used", rs.getString("EngineCode"), rs.getString("Displacement"), rs.getString("EnginePower"), rs.getString("ElMotorPower"), rs.getString("Torque"), rs.getString("TorqueOverBoost"), rs.getString("GearboxType"), rs.getString("Gears"), rs.getString("Gearbox"), rs.getString("Driveline"), rs.getString("TransmissionCode"), rs.getString("CertGroup"), rs.getString("EmissionClass"), rs.getString("StartOfProd"), rs.getString("EndOfProd")); currentCyclePlan.put(entry.getVariantID(), entry); } //Now extract all variants in the cycleplan to compare with System.out.println("Extracting comparison variants"); query = "SELECT * FROM VARIANTS, VariantBelongsToCyclePlan WHERE " + "VariantBelongsToCyclePlan.CyclePlanID= \'" + cyclePlanSelector.getSelectionModel().getSelectedItem().toString() + "\' " + "AND VARIANTS.VariantID = VariantBelongsToCyclePlan.VariantID " + "AND EndOfProd > '" + currentWeek + "'"; rs = statement.executeQuery(query); while (rs.next()) { TableVariant entry = new TableVariant(rs.getString("Plant"), rs.getString("Platform"), rs.getString("Vehicle"), rs.getString("Propulsion"), rs.getString("Denomination"), rs.getString("Fuel"), rs.getString("EngineFamily"), rs.getString("Generation"), "EngineName not used", rs.getString("EngineCode"), rs.getString("Displacement"), rs.getString("EnginePower"), rs.getString("ElMotorPower"), rs.getString("Torque"), rs.getString("TorqueOverBoost"), rs.getString("GearboxType"), rs.getString("Gears"), rs.getString("Gearbox"), rs.getString("Driveline"), rs.getString("TransmissionCode"), rs.getString("CertGroup"), rs.getString("EmissionClass"), rs.getString("StartOfProd"), rs.getString("EndOfProd")); oldCyclePlan.put(entry.getVariantID(), entry); } } catch (Exception e) { System.err.println(e.getMessage()); } //for each variant in current plan, remove from both if it exists in old for (Iterator<Map.Entry<String, TableVariant>> entries = currentCyclePlan.entrySet().iterator(); entries .hasNext();) { Map.Entry<String, TableVariant> entry = entries.next(); if (oldCyclePlan.containsKey(entry.getKey())) { entries.remove(); // remove from currentCyclePlan oldCyclePlan.remove(entry.getKey()); } } // Now need to check if some entries were only moved in time for (Iterator<Map.Entry<String, TableVariant>> entries = currentCyclePlan.entrySet().iterator(); entries .hasNext();) { Map.Entry<String, TableVariant> entry = entries.next(); try { statement = RPT.conn.createStatement(); statement.setQueryTimeout(30); //TODO //Add all columns except Start of production, as all will be important to find it correctly String query = "SELECT VARIANTS.VariantID, VARIANTS.StartOfProd, VARIANTS.EndOfProd FROM VARIANTS, VariantBelongsToCyclePlan WHERE " + "VARIANTS.VariantID = VariantBelongsToCyclePlan.VariantID AND " + "VariantBelongsToCyclePlan.CyclePlanID= \'" + cyclePlanSelector.getSelectionModel().getSelectedItem().toString() + "\' AND " + "VARIANTS.Plant = \'" + entry.getValue().getPlant() + "\' AND " + "VARIANTS.Platform = \'" + entry.getValue().getPlatform() + "\' AND " + "VARIANTS.Vehicle = \'" + entry.getValue().getVehicle() + "\' AND " + "VARIANTS.Propulsion = \'" + entry.getValue().getPropulsion() + "\' AND " + "VARIANTS.Denomination = \'" + entry.getValue().getDenomination() + "\' AND " + "VARIANTS.Fuel = \'" + entry.getValue().getFuel() + "\' AND " + "VARIANTS.EngineFamily = \'" + entry.getValue().getEngineFamily() + "\' AND " + "VARIANTS.Generation = \'" + entry.getValue().getGeneration() + "\' AND " + "VARIANTS.EngineCode = \'" + entry.getValue().getEngineCode() + "\' AND " + "VARIANTS.Displacement = \'" + entry.getValue().getDisplacement() + "\' AND " + "VARIANTS.EnginePower = \'" + entry.getValue().getEnginePower() + "\' AND " + "VARIANTS.ElMotorPower = \'" + entry.getValue().getElMotorPower() + "\' AND " + "VARIANTS.TorqueOverBoost = \'" + entry.getValue().getTorqueOverBoost() + "\' AND " + "VARIANTS.GearboxType = \'" + entry.getValue().getGearboxType() + "\' AND " + "VARIANTS.Gears = \'" + entry.getValue().getGears() + "\' AND " + "VARIANTS.Gearbox = \'" + entry.getValue().getGearbox() + "\' AND " + "VARIANTS.Driveline = \'" + entry.getValue().getDriveline() + "\' AND " + "VARIANTS.TransmissionCode = \'" + entry.getValue().getTransmissionCode() + "\' AND " + "VARIANTS.CertGroup = \'" + entry.getValue().getCertGroup() + "\' AND " // may remove once + "VARIANTS.EmissionClass = \'" + entry.getValue().getEmissionClass() + "\'"; ResultSet rs = statement.executeQuery(query); if (rs.next()) { entry.getValue().setOldSOP(rs.getString("StartOfProd")); entry.getValue().setOldEOP(rs.getString("EndOfProd")); movedVariants.put(entry.getKey(), entry.getValue()); //Save variant to moved map entries.remove(); //remove variant from current map oldCyclePlan.remove(rs.getString("VariantID")); //remove variant from old map } } catch (Exception e) { System.err.println(e.getMessage()); } } // Now check for variants that have been slightly changed only. // Show a dialog window allowing the user to define what a minor change is majorChanges = new ArrayList(); Stage stage; Parent root; stage = new Stage(); root = FXMLLoader .load(getClass().getResource("/rpt/GUI/ProgramStrategist/CyclePlans/dialogDefineChanged.fxml")); stage.setScene(new Scene(root)); stage.setTitle("Set change definition"); stage.initModality(Modality.APPLICATION_MODAL); stage.showAndWait(); // pause until the user has selected minor changes // Now loop through the remaining Added items and check if they are to be moved to "Modified" //for (String s : majorChanges) { // System.out.println(s); //} // Create string for extracting data which has been judged as minor //String dataString = ""; // Data which will be used for difference check //for (String s : majorChanges) { // dataString = dataString + ", VARIANTS." + s; //} // Build list of parameters to extract and compare with the new variant ArrayList<String> infoArray = new ArrayList(); String query = "PRAGMA table_info(VARIANTS)"; //Get all column names String extractionData = ""; try { statement = RPT.conn.createStatement(); statement.setQueryTimeout(30); ResultSet rsColumns = statement.executeQuery(query); //traverser through list of columns and add those not pointed out as MAJOR boolean first = true; while (rsColumns.next()) { if (!(majorChanges.contains(rsColumns.getString("name")))) { infoArray.add(rsColumns.getString("name")); if (first) { extractionData = extractionData + "VARIANTS." + rsColumns.getString("name"); first = false; } else { extractionData = extractionData + ", VARIANTS." + rsColumns.getString("name"); } } } } catch (Exception e) { System.err.println("CompareDialogController error when building extraction data: " + e.getMessage()); } for (Iterator<Map.Entry<String, TableVariant>> entries = currentCyclePlan.entrySet().iterator(); entries .hasNext();) { Map.Entry<String, TableVariant> entry = entries.next(); try { statement = RPT.conn.createStatement(); statement.setQueryTimeout(30); query = "SELECT "; query = query + extractionData; query = query + " FROM VARIANTS, VariantBelongsToCyclePlan WHERE " + "VARIANTS.VariantID = VariantBelongsToCyclePlan.VariantID AND " + "VariantBelongsToCyclePlan.CyclePlanID= \'" + cyclePlanSelector.getSelectionModel().getSelectedItem().toString() + "\'"; for (String s : majorChanges) { query = query + " AND VARIANTS." + s + " = \'" + entry.getValue().getValue(s) + "\'"; } //System.out.println(query); ResultSet rs = statement.executeQuery(query); if (rs.next()) { // Found "similar enough" changedVariants.put(entry.getKey(), entry.getValue()); //Save variant to moved map entries.remove(); //remove variant from current map oldCyclePlan.remove(rs.getString("VariantID")); //remove variant from old map // now loop through all non major columns and check for difference between variant in new and old cycle plan diffValues = new HashMap<String, String>(); for (String s : infoArray) { if (!rs.getString(s).equals(entry.getValue().getValue(s))) { diffValues.put(s, rs.getString(s)); } } changedInfo.put(entry.getKey(), diffValues); //Add information about differences between new and old variant } } catch (Exception e) { System.err.println(e.getMessage()); } } // Open file selector and let user specify report file XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Information"); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); // print out information about baseline cycle plan Row row = sheet.createRow(0); Cell cell = row.createCell(0); Font headerFont = workbook.createFont(); headerFont.setBold(true); CellStyle style = workbook.createCellStyle(); style.setFont(headerFont); cell.setCellStyle(style); cell.setCellValue("Cycle plan:"); cell = row.createCell(1); cell.setCellValue(CyclePlansController.selectedCyclePlan); // print out information about comaparison cycle plan row = sheet.createRow(1); cell = row.createCell(0); headerFont = workbook.createFont(); headerFont.setBold(true); style.setFont(headerFont); cell.setCellStyle(style); cell.setCellValue("Compared to:"); cell = row.createCell(1); cell.setCellValue(cyclePlanSelector.getSelectionModel().getSelectedItem().toString()); sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); // Write Added variant information sheet = workbook.createSheet("Added"); //freeze the first row sheet.createFreezePane(0, 1); row = sheet.createRow(0); writeHeaders(workbook, row, false); int rowNum = 1; int amountOfColumns = 0; // loop through added for (Iterator<Map.Entry<String, TableVariant>> entries = currentCyclePlan.entrySet().iterator(); entries .hasNext();) { Map.Entry<String, TableVariant> entry = entries.next(); row = sheet.createRow(rowNum); amountOfColumns = writeRow(workbook, sheet, row, entry.getValue(), null, false, false); rowNum++; } //autosize all columns for (int i = 0; i < amountOfColumns; i++) { sheet.autoSizeColumn(i); } amountOfColumns = 0; // Write Removed variant information sheet = workbook.createSheet("Removed"); //freeze the first row sheet.createFreezePane(0, 1); row = sheet.createRow(0); writeHeaders(workbook, row, false); rowNum = 1; // loop through removed for (Iterator<Map.Entry<String, TableVariant>> entries = oldCyclePlan.entrySet().iterator(); entries .hasNext();) { Map.Entry<String, TableVariant> entry = entries.next(); row = sheet.createRow(rowNum); amountOfColumns = writeRow(workbook, sheet, row, entry.getValue(), null, false, false); rowNum++; } //autosize all columns for (int i = 0; i < amountOfColumns; i++) { sheet.autoSizeColumn(i); } amountOfColumns = 0; // Write Changed variant information sheet = workbook.createSheet("Changed"); //freeze the first row sheet.createFreezePane(0, 1); row = sheet.createRow(0); writeHeaders(workbook, row, false); rowNum = 1; // loop through changed for (Iterator<Map.Entry<String, TableVariant>> entries = changedVariants.entrySet().iterator(); entries .hasNext();) { Map.Entry<String, TableVariant> entry = entries.next(); row = sheet.createRow(rowNum); amountOfColumns = writeRow(workbook, sheet, row, entry.getValue(), changedInfo, true, false); rowNum++; } //autosize all columns for (int i = 0; i < amountOfColumns; i++) { sheet.autoSizeColumn(i); } amountOfColumns = 0; // Write Moved variant information sheet = workbook.createSheet("Moved"); //freeze the first row sheet.createFreezePane(0, 1); row = sheet.createRow(0); writeHeaders(workbook, row, true); rowNum = 1; for (Iterator<Map.Entry<String, TableVariant>> entries = movedVariants.entrySet().iterator(); entries .hasNext();) { Map.Entry<String, TableVariant> entry = entries.next(); row = sheet.createRow(rowNum); amountOfColumns = writeRow(workbook, sheet, row, entry.getValue(), null, false, true); rowNum++; } //autosize all columns for (int i = 0; i < amountOfColumns; i++) { sheet.autoSizeColumn(i); } amountOfColumns = 0; FileChooser fileChooser = new FileChooser(); fileChooser.setTitle("Save Comparison Result File"); File selectedFile = fileChooser.showSaveDialog(null); if (selectedFile != null) { try { FileOutputStream out = new FileOutputStream(selectedFile); workbook.write(out); out.close(); System.out.println("Excel written successfully.."); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } closeDialog(); }
From source file:uk.ac.ebi.generic.util.ExcelWorkBook.java
License:Apache License
public ExcelWorkBook(String[] titles, Object[][] tableData, String sheetTitle) throws Exception { this.wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); // create a new sheet XSSFSheet sheet = wb.createSheet(sheetTitle); XSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true);//from ww w . j a v a 2 s . c o m sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //header row XSSFRow headerRow = sheet.createRow(0); //headerRow.setHeightInPoints(40); XSSFCell headerCell; for (int j = 0; j < titles.length; j++) { headerCell = headerRow.createCell(j); headerCell.setCellValue(titles[j]); //headerCell.setCellStyle(styles.get("header")); } // data rows // Create a row and put some cells in it. Rows are 0 based. // Then set value for that created cell for (int k = 0; k < tableData.length; k++) { XSSFRow row = sheet.createRow(k + 1); // data starts from row 1 for (int l = 0; l < tableData[k].length; l++) { XSSFCell cell = row.createCell(l); String cellStr = null; try { cellStr = tableData[k][l].toString(); } catch (Exception e) { cellStr = ""; } //System.out.println("cell " + l + ": " + cellStr); // make hyperlink in cell if ((cellStr.startsWith("http://") || cellStr.startsWith("https://")) && !cellStr.contains("|")) { //need to encode URI for this version of ExcelWorkBook cellStr = URIUtil.encodePath(cellStr, "UTF-8"); cellStr = cellStr.replace("%3F", "?"); // so that url link would work //System.out.println("cellStr: " + cellStr); XSSFHyperlink url_link = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL); url_link.setAddress(cellStr); cell.setCellValue(cellStr); cell.setHyperlink(url_link); } else { cell.setCellValue(cellStr); } //System.out.println((String)tableData[k][l]); } } }
From source file:uk.co.spudsoft.birt.emitters.excel.tests.PageLayoutTest.java
License:Open Source License
@Test public void testRunReportXlsx() throws BirtException, IOException { InputStream inputStream = runAndRenderReport("PageLayout.rptdesign", "xlsx"); assertNotNull(inputStream);/*from ww w .jav a 2 s.co m*/ try { XSSFWorkbook workbook = new XSSFWorkbook(inputStream); assertNotNull(workbook); assertEquals(1, workbook.getNumberOfSheets()); assertEquals("Page Layout Test", workbook.getSheetAt(0).getSheetName()); XSSFSheet sheet0 = workbook.getSheetAt(0); XSSFPrintSetup printSetup = sheet0.getPrintSetup(); assertEquals(PaperSize.A4_PAPER, printSetup.getPaperSizeEnum()); assertEquals(PrintOrientation.LANDSCAPE, printSetup.getOrientation()); assertEquals(1.0 / 2.54, printSetup.getHeaderMargin(), 0.01); assertEquals(1.0 / 2.54, printSetup.getFooterMargin(), 0.01); assertEquals(0.7 / 2.54, sheet0.getMargin(Sheet.LeftMargin), 0.01); assertEquals(0.7 / 2.54, sheet0.getMargin(Sheet.RightMargin), 0.01); assertEquals(1.7 / 2.54, sheet0.getMargin(Sheet.TopMargin), 0.01); assertEquals(1.7 / 2.54, sheet0.getMargin(Sheet.BottomMargin), 0.01); } finally { inputStream.close(); } }
From source file:uk.co.spudsoft.birt.emitters.excel.tests.PageLayoutTest.java
License:Open Source License
@Test public void testRunReportPixelsXlsx() throws BirtException, IOException { InputStream inputStream = runAndRenderReport("PageLayoutPixels.rptdesign", "xlsx"); assertNotNull(inputStream);//from w ww.ja v a 2 s. c om try { XSSFWorkbook workbook = new XSSFWorkbook(inputStream); assertNotNull(workbook); assertEquals(1, workbook.getNumberOfSheets()); assertEquals("Page Layout Test", workbook.getSheetAt(0).getSheetName()); XSSFSheet sheet0 = workbook.getSheetAt(0); XSSFPrintSetup printSetup = sheet0.getPrintSetup(); assertEquals(PaperSize.A4_PAPER, printSetup.getPaperSizeEnum()); assertEquals(PrintOrientation.LANDSCAPE, printSetup.getOrientation()); assertEquals(0.3, printSetup.getHeaderMargin(), 0.01); assertEquals(0.3, printSetup.getFooterMargin(), 0.01); assertEquals(0.7, sheet0.getMargin(Sheet.LeftMargin), 0.01); assertEquals(0.7, sheet0.getMargin(Sheet.RightMargin), 0.01); assertEquals(0.75, sheet0.getMargin(Sheet.TopMargin), 0.01); assertEquals(0.75, sheet0.getMargin(Sheet.BottomMargin), 0.01); } finally { inputStream.close(); } }