package org.insightech.er.editor.model.dbexport.excel.sheet_generator; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.regex.Matcher; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.util.CellRangeAddress; import org.insightech.er.editor.model.ERDiagram; import org.insightech.er.editor.model.ObjectModel; import org.insightech.er.editor.model.dbexport.excel.ExportToExcelManager.LoopDefinition; import org.insightech.er.editor.model.diagram_contents.element.connection.Relation; import org.insightech.er.editor.model.diagram_contents.element.node.table.TableView; import org.insightech.er.editor.model.diagram_contents.element.node.table.column.NormalColumn; import org.insightech.er.editor.model.progress_monitor.ProgressMonitor; import org.insightech.er.util.Format; import org.insightech.er.util.POIUtils; import org.insightech.er.util.POIUtils.CellLocation; public abstract class AbstractSheetGenerator { private static final int MAX_SHEET_NAME_LENGTH = 26; protected static final String KEYWORD_ORDER = "$ORD"; protected static final String KEYWORD_LOGICAL_TABLE_NAME = "$LTN"; protected static final String KEYWORD_PHYSICAL_TABLE_NAME = "$PTN"; protected static final String KEYWORD_LOGICAL_COLUMN_NAME = "$LCN"; protected static final String KEYWORD_PHYSICAL_COLUMN_NAME = "$PCN"; protected static final String KEYWORD_TYPE = "$TYP"; protected static final String KEYWORD_TYPE_EMBEDDED = "$TYE"; protected static final String KEYWORD_LENGTH = "$LEN"; protected static final String KEYWORD_DECIMAL = "$DEC"; protected static final String KEYWORD_PRIMARY_KEY = "$PK"; protected static final String KEYWORD_NOT_NULL = "$NN"; protected static final String KEYWORD_UNIQUE_KEY = "$UK"; protected static final String KEYWORD_FOREIGN_KEY = "$FK"; protected static final String KEYWORD_LOGICAL_REFERENCE_TABLE_KEY = "$LRFTC"; protected static final String KEYWORD_PHYSICAL_REFERENCE_TABLE_KEY = "$PRFTC"; protected static final String KEYWORD_LOGICAL_REFERENCE_TABLE = "$LRFT"; protected static final String KEYWORD_PHYSICAL_REFERENCE_TABLE = "$PRFT"; protected static final String KEYWORD_LOGICAL_REFERENCE_KEY = "$LRFC"; protected static final String KEYWORD_PHYSICAL_REFERENCE_KEY = "$PRFC"; protected static final String KEYWORD_AUTO_INCREMENT = "$INC"; protected static final String KEYWORD_DESCRIPTION = "$CDSC"; protected static final String KEYWORD_DEFAULT_VALUE = "$DEF"; protected static final String KEYWORD_LOGICAL_FOREIGN_KEY_NAME = "$LFKN"; protected static final String KEYWORD_PHYSICAL_FOREIGN_KEY_NAME = "$PFKN"; protected static final String KEYWORD_TABLE_DESCRIPTION = "$TDSC"; private static final String[] KEYWORDS_OF_COLUMN = {KEYWORD_ORDER, KEYWORD_LOGICAL_TABLE_NAME, KEYWORD_PHYSICAL_TABLE_NAME, KEYWORD_LOGICAL_COLUMN_NAME, KEYWORD_PHYSICAL_COLUMN_NAME, KEYWORD_TYPE, KEYWORD_TYPE_EMBEDDED, KEYWORD_LENGTH, KEYWORD_DECIMAL, KEYWORD_PRIMARY_KEY, KEYWORD_NOT_NULL, KEYWORD_UNIQUE_KEY, KEYWORD_FOREIGN_KEY, KEYWORD_LOGICAL_REFERENCE_TABLE_KEY, KEYWORD_PHYSICAL_REFERENCE_TABLE_KEY, KEYWORD_LOGICAL_REFERENCE_TABLE, KEYWORD_PHYSICAL_REFERENCE_TABLE, KEYWORD_LOGICAL_REFERENCE_KEY, KEYWORD_PHYSICAL_REFERENCE_KEY, KEYWORD_AUTO_INCREMENT, KEYWORD_DEFAULT_VALUE, KEYWORD_DESCRIPTION, KEYWORD_LOGICAL_FOREIGN_KEY_NAME, KEYWORD_PHYSICAL_FOREIGN_KEY_NAME}; protected static final String[] FIND_KEYWORDS_OF_COLUMN = {KEYWORD_LOGICAL_COLUMN_NAME, KEYWORD_PHYSICAL_COLUMN_NAME}; protected Map<String, String> keywordsValueMap; public static class ColumnTemplate { public Map<Integer, String> columnTemplateMap = new HashMap<Integer, String>(); public List<HSSFCellStyle> topRowCellStyleList; public List<HSSFCellStyle> middleRowCellStyleList; public List<HSSFCellStyle> bottomRowCellStyleList; } public static class MatrixCellStyle { public HSSFCellStyle headerTemplateCellStyle; public HSSFCellStyle style11; public HSSFCellStyle style12; public HSSFCellStyle style13; public HSSFCellStyle style21; public HSSFCellStyle style22; public HSSFCellStyle style23; public HSSFCellStyle style31; public HSSFCellStyle style32; public HSSFCellStyle style33; } protected Map<String, String> buildKeywordsValueMap(final HSSFSheet wordsSheet, final int columnNo, final String[] keywords) { final Map<String, String> keywordsValueMap = new HashMap<String, String>(); for (final String keyword : keywords) { final CellLocation location = POIUtils.findCell(wordsSheet, keyword, columnNo); if (location != null) { final HSSFRow row = wordsSheet.getRow(location.r); final HSSFCell cell = row.getCell(location.c + 2); final String value = cell.getRichStringCellValue().getString(); if (value != null) { keywordsValueMap.put(keyword, value); } } } return keywordsValueMap; } protected String getValue(final Map<String, String> keywordsValueMap, final String keyword, final Object obj) { if (obj instanceof Boolean) { if (Boolean.TRUE.equals(obj)) { final String value = keywordsValueMap.get(keyword); if (value != null && !"".equals(value)) { return value; } } else { return ""; } } if (obj == null) { return ""; } return obj.toString(); } protected void setColumnData(final Map<String, String> keywordsValueMap, final ColumnTemplate columnTemplate, final HSSFRow row, final NormalColumn normalColumn, final TableView tableView, final int order) { for (final int columnNum : columnTemplate.columnTemplateMap.keySet()) { final HSSFCell cell = row.createCell(columnNum); final String template = columnTemplate.columnTemplateMap.get(columnNum); String value = null; if (KEYWORD_ORDER.equals(template)) { value = String.valueOf(order); } else { value = getColumnValue(keywordsValueMap, normalColumn, tableView, template); } try { final double num = Double.parseDouble(value); cell.setCellValue(num); } catch (final NumberFormatException e) { final HSSFRichTextString text = new HSSFRichTextString(value); cell.setCellValue(text); } } } private String getColumnValue(final Map<String, String> keywordsValueMap, final NormalColumn normalColumn, final TableView tableView, final String template) { String str = template; for (final String keyword : KEYWORDS_OF_COLUMN) { str = str.replaceAll("\\" + keyword, Matcher.quoteReplacement(getKeywordValue(keywordsValueMap, normalColumn, tableView, keyword))); } return str; } private String getKeywordValue(final Map<String, String> keywordsValueMap, final NormalColumn normalColumn, final TableView tableView, final String keyword) { Object obj = null; if (KEYWORD_LOGICAL_TABLE_NAME.equals(keyword)) { obj = tableView.getLogicalName(); } else if (KEYWORD_PHYSICAL_TABLE_NAME.equals(keyword)) { obj = tableView.getPhysicalName(); } else if (KEYWORD_LOGICAL_COLUMN_NAME.equals(keyword)) { obj = normalColumn.getLogicalName(); } else if (KEYWORD_PHYSICAL_COLUMN_NAME.equals(keyword)) { obj = normalColumn.getPhysicalName(); } else if (KEYWORD_TYPE.equals(keyword)) { if (normalColumn.getType() == null) { obj = null; } else { obj = Format.formatType(normalColumn.getType(), normalColumn.getTypeData(), tableView.getDiagram().getDatabase(), false); } } else if (KEYWORD_TYPE_EMBEDDED.equals(keyword)) { if (normalColumn.getType() == null) { obj = null; } else { obj = Format.formatType(normalColumn.getType(), normalColumn.getTypeData(), tableView.getDiagram().getDatabase(), true); } } else if (KEYWORD_LENGTH.equals(keyword)) { obj = normalColumn.getTypeData().getLength(); } else if (KEYWORD_DECIMAL.equals(keyword)) { obj = normalColumn.getTypeData().getDecimal(); } else if (KEYWORD_PRIMARY_KEY.equals(keyword)) { obj = normalColumn.isPrimaryKey(); } else if (KEYWORD_NOT_NULL.equals(keyword)) { obj = normalColumn.isNotNull(); } else if (KEYWORD_FOREIGN_KEY.equals(keyword)) { final List<Relation> relationList = normalColumn.getRelationList(); if (relationList == null || relationList.isEmpty()) { obj = false; } else { obj = true; } } else if (KEYWORD_LOGICAL_REFERENCE_TABLE_KEY.equals(keyword)) { final List<Relation> relationList = normalColumn.getRelationList(); if (relationList == null || relationList.isEmpty()) { obj = null; } else { final Relation relation = relationList.get(0); final TableView referencedTable = relation.getSourceTableView(); obj = referencedTable.getLogicalName() + "." + normalColumn.getReferencedColumn(relation).getLogicalName(); } } else if (KEYWORD_PHYSICAL_REFERENCE_TABLE_KEY.equals(keyword)) { final List<Relation> relationList = normalColumn.getRelationList(); if (relationList == null || relationList.isEmpty()) { obj = null; } else { final Relation relation = relationList.get(0); final TableView referencedTable = relation.getSourceTableView(); obj = referencedTable.getPhysicalName() + "." + normalColumn.getReferencedColumn(relation).getPhysicalName(); } } else if (KEYWORD_LOGICAL_REFERENCE_TABLE.equals(keyword)) { final List<Relation> relationList = normalColumn.getRelationList(); if (relationList == null || relationList.isEmpty()) { obj = null; } else { final TableView referencedTable = relationList.get(0).getSourceTableView(); obj = referencedTable.getLogicalName(); } } else if (KEYWORD_PHYSICAL_REFERENCE_TABLE.equals(keyword)) { final List<Relation> relationList = normalColumn.getRelationList(); if (relationList == null || relationList.isEmpty()) { obj = null; } else { final TableView referencedTable = relationList.get(0).getSourceTableView(); obj = referencedTable.getPhysicalName(); } } else if (KEYWORD_LOGICAL_REFERENCE_KEY.equals(keyword)) { final List<Relation> relationList = normalColumn.getRelationList(); if (relationList == null || relationList.isEmpty()) { obj = null; } else { final Relation relation = relationList.get(0); obj = normalColumn.getReferencedColumn(relation).getLogicalName(); } } else if (KEYWORD_PHYSICAL_REFERENCE_KEY.equals(keyword)) { final List<Relation> relationList = normalColumn.getRelationList(); if (relationList == null || relationList.isEmpty()) { obj = null; } else { final Relation relation = relationList.get(0); obj = normalColumn.getReferencedColumn(relation).getPhysicalName(); } } else if (KEYWORD_LOGICAL_FOREIGN_KEY_NAME.equals(keyword)) { obj = normalColumn.getLogicalName(); } else if (KEYWORD_PHYSICAL_FOREIGN_KEY_NAME.equals(keyword)) { obj = normalColumn.getPhysicalName(); } else if (KEYWORD_UNIQUE_KEY.equals(keyword)) { obj = normalColumn.isUniqueKey(); } else if (KEYWORD_DESCRIPTION.equals(keyword)) { obj = normalColumn.getDescription(); } else if (KEYWORD_DEFAULT_VALUE.equals(keyword)) { obj = normalColumn.getDefaultValue(); } else if (KEYWORD_AUTO_INCREMENT.equals(keyword)) { obj = normalColumn.isAutoIncrement(); } return getValue(keywordsValueMap, keyword, obj); } protected ColumnTemplate loadColumnTemplate(final HSSFWorkbook workbook, final HSSFSheet templateSheet, final CellLocation location) { if (location == null) { return null; } final ColumnTemplate columnTemplate = new ColumnTemplate(); final HSSFRow row = templateSheet.getRow(location.r); final HSSFRow bottomRow = templateSheet.getRow(location.r + 1); for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) { final HSSFCell cell = row.getCell(colNum); if (cell != null) { columnTemplate.columnTemplateMap.put(colNum, cell.getRichStringCellValue().getString()); } } columnTemplate.topRowCellStyleList = POIUtils.copyCellStyle(workbook, row); columnTemplate.middleRowCellStyleList = POIUtils.copyCellStyle(workbook, row); columnTemplate.bottomRowCellStyleList = POIUtils.copyCellStyle(workbook, row); for (short i = 0; i < columnTemplate.middleRowCellStyleList.size(); i++) { final HSSFCellStyle middleRowCellStyle = columnTemplate.middleRowCellStyleList.get(i); if (middleRowCellStyle != null) { final HSSFCellStyle topRowCellStyle = columnTemplate.topRowCellStyleList.get(i); final HSSFCellStyle bottomRowCellStyle = columnTemplate.bottomRowCellStyleList.get(i); final HSSFCell bottomCell = bottomRow.getCell(row.getFirstCellNum() + i); topRowCellStyle.setBorderBottom(bottomCell.getCellStyle().getBorderTop()); middleRowCellStyle.setBorderTop(bottomCell.getCellStyle().getBorderTop()); middleRowCellStyle.setBorderBottom(bottomCell.getCellStyle().getBorderTop()); bottomRowCellStyle.setBorderTop(bottomCell.getCellStyle().getBorderTop()); bottomRowCellStyle.setBorderBottom(bottomCell.getCellStyle().getBorderBottom()); } } return columnTemplate; } protected void setCellStyle(final ColumnTemplate columnTemplate, final HSSFSheet sheet, final int firstRowNum, final int rowSize, final int firstColNum) { sheet.removeRow(sheet.getRow(firstRowNum + rowSize)); final HSSFRow bottomRowTemplate = sheet.getRow(firstRowNum + rowSize + 1); sheet.removeRow(bottomRowTemplate); for (int r = firstRowNum + 1; r < firstRowNum + rowSize; r++) { final HSSFRow row = sheet.getRow(r); for (int i = 0; i < columnTemplate.middleRowCellStyleList.size(); i++) { final HSSFCell cell = row.getCell(firstColNum + i); if (cell != null && columnTemplate.middleRowCellStyleList.get(i) != null) { cell.setCellStyle(columnTemplate.middleRowCellStyleList.get(i)); } } } if (rowSize > 0) { final HSSFRow topRow = sheet.getRow(firstRowNum); for (int i = 0; i < columnTemplate.topRowCellStyleList.size(); i++) { final HSSFCell cell = topRow.getCell(firstColNum + i); if (cell != null) { if (columnTemplate.topRowCellStyleList.get(i) != null) { cell.setCellStyle(columnTemplate.topRowCellStyleList.get(i)); } } } final HSSFRow bottomRow = sheet.getRow(firstRowNum + rowSize - 1); for (int i = 0; i < columnTemplate.bottomRowCellStyleList.size(); i++) { final HSSFCell bottomRowCell = bottomRow.getCell(firstColNum + i); if (bottomRowCell != null) { if (columnTemplate.bottomRowCellStyleList.get(i) != null) { bottomRowCell.setCellStyle(columnTemplate.bottomRowCellStyleList.get(i)); } } } } else { final HSSFRow bottomRow = sheet.getRow(firstRowNum - 1); if (bottomRow != null) { for (int i = 0; i < columnTemplate.bottomRowCellStyleList.size(); i++) { final HSSFCell bottomRowCell = bottomRow.getCell(firstColNum + i); if (bottomRowCell != null) { final HSSFCellStyle bottomRowCellStyle = bottomRowCell.getCellStyle(); if (columnTemplate.bottomRowCellStyleList.get(i) != null) { bottomRowCellStyle.setBorderBottom(columnTemplate.bottomRowCellStyleList.get(i).getBorderBottom()); } } } } } final List<CellRangeAddress> regionList = POIUtils.getMergedRegionList(sheet, firstRowNum); for (int r = firstRowNum + 1; r < firstRowNum + rowSize; r++) { POIUtils.copyMergedRegion(sheet, regionList, r); } } public static HSSFSheet createNewSheet(final HSSFWorkbook workbook, final int sheetNo, final String name, final Map<String, Integer> sheetNameMap) { final HSSFSheet sheet = workbook.cloneSheet(sheetNo); final int newSheetNo = workbook.getSheetIndex(sheet); workbook.setSheetName(newSheetNo, decideSheetName(name, sheetNameMap)); return sheet; } public static String decideSheetName(String name, final Map<String, Integer> sheetNameMap) { if (name.length() > MAX_SHEET_NAME_LENGTH) { name = name.substring(0, MAX_SHEET_NAME_LENGTH); } String sheetName = null; Integer sameNameNum = sheetNameMap.get(name.toUpperCase()); if (sameNameNum == null) { sameNameNum = 0; sheetName = name; } else { do { sameNameNum++; sheetName = name + "(" + sameNameNum + ")"; } while (sheetNameMap.containsKey(sheetName.toUpperCase())); } sheetNameMap.put(name.toUpperCase(), sameNameNum); return sheetName; } public void init(final HSSFSheet wordsSheet) { keywordsValueMap = buildKeywordsValueMap(wordsSheet, getKeywordsColumnNo(), getKeywords()); } public abstract void generate(ProgressMonitor monitor, HSSFWorkbook workbook, int sheetNo, boolean useLogicalNameAsSheetName, Map<String, Integer> sheetNameMap, Map<String, ObjectModel> sheetObjectMap, ERDiagram diagram, Map<String, LoopDefinition> loopDefinitionMap) throws InterruptedException; public abstract int count(ERDiagram diagram); public abstract String getTemplateSheetName(); public abstract int getKeywordsColumnNo(); public abstract String[] getKeywords(); }