List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet setAutobreaks
@Override public void setAutobreaks(boolean value)
From source file:nl.detoren.ijsco.io.ExcelExport.java
License:Open Source License
public void exportGroepen(Groepen groepen) { String password = "abcd"; try {/*from ww w . j a v a 2 s . c o m*/ if (groepen == null) return; // sheetindx geeft index in Excel template op basis van groepsgrootte. -1: geen sheet voor groepsgrootte int[] sheetindx = new int[] { -1, -1, -1, -1, 5, -1, 4, -1, 3, -1, 2, -1, 1, -1, 0, -1, -1, -1 }; // columnsize geeft lengte in Excel template op basis van groepsgrootte. -1: geen sheet voor groepsgrootte int[] columnsize = new int[] { -1, -1, -1, -1, 20, -1, 35, -1, 54, -1, 77, -1, 100, -1, 127, -1, -1, -1 }; // pagelngth geeft lengte in Excel template op basis van groepsgrootte. -1: geen sheet voor groepsgrootte int[] pagelngth = new int[] { -1, -1, -1, -1, 20, -1, 35, -1, 54, -1, 77, -1, 100, -1, 127, -1, -1, -1 }; int sheet2row = 2; int sheet3row = 2; FileInputStream file = new FileInputStream("Indeling.xlsm"); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFCellStyle style1 = workbook.createCellStyle(); style1.setFillPattern(FillPatternType.SOLID_FOREGROUND); style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(180, 180, 180))); XSSFCellStyle my_style = workbook.createCellStyle(); XSSFColor my_foreground = new XSSFColor(Color.ORANGE); XSSFColor my_background = new XSSFColor(Color.RED); my_style.setFillPattern(FillPatternType.SOLID_FOREGROUND); my_style.setFillForegroundColor(my_foreground); my_style.setFillBackgroundColor(my_background); XSSFSheet sheet2 = workbook.getSheet("Groepsindeling"); XSSFSheet sheet3 = workbook.getSheet("Deelnemerslijst"); updateCell(sheet3, sheet3row, 0, "Naam", style1); updateCell(sheet3, sheet3row, 1, "KNSB nr", style1); updateCell(sheet3, sheet3row, 2, "rating", style1); updateCell(sheet3, sheet3row, 3, "groep", style1); sheet3row++; for (Groep groep : groepen) { logger.log(Level.INFO, "Exporteer groep : " + groep.getNaam()); XSSFSheet sheet = workbook.cloneSheet(sheetindx[groep.getGrootte()], groep.getNaam()); updateCell(sheet, 0, 6, groep.getNaam()); updateCell(sheet2, sheet2row, 1, groep.getNaam()); sheet2row++; updateCell(sheet2, sheet2row, 0, "nr", style1); updateCell(sheet2, sheet2row, 1, "Naam", style1); updateCell(sheet2, sheet2row, 2, "KNSB nr", style1); updateCell(sheet2, sheet2row, 3, "rating", style1); sheet2row++; for (int i = 0; i < groep.getGrootte(); i++) { updateCell(sheet, 3 + i, 2, groep.getSpeler(i).getNaam()); updateCell(sheet, 3 + i, 3, groep.getSpeler(i).getKnsbnummer()); updateCell(sheet, 3 + i, 5, groep.getSpeler(i).getRating()); updateCell(sheet2, sheet2row, 0, i + 1); updateCell(sheet2, sheet2row, 1, "'" + sheet.getSheetName() + "'!" + org.apache.poi.ss.util.CellReference.convertNumToColString(2) + (4 + i), true); updateCell(sheet2, sheet2row, 2, "'" + sheet.getSheetName() + "'!" + org.apache.poi.ss.util.CellReference.convertNumToColString(3) + (4 + i), true); updateCell(sheet2, sheet2row, 3, "'" + sheet.getSheetName() + "'!" + org.apache.poi.ss.util.CellReference.convertNumToColString(5) + (4 + i), true); if (groep.getSpeler(i).getNaam() != "Bye") { updateCell(sheet3, sheet3row, 0, "'" + sheet.getSheetName() + "'!" + org.apache.poi.ss.util.CellReference.convertNumToColString(2) + (4 + i), true); updateCell(sheet3, sheet3row, 1, "'" + sheet.getSheetName() + "'!" + org.apache.poi.ss.util.CellReference.convertNumToColString(3) + (4 + i), true); updateCell(sheet3, sheet3row, 2, "'" + sheet.getSheetName() + "'!" + org.apache.poi.ss.util.CellReference.convertNumToColString(5) + (4 + i), true); updateCell(sheet3, sheet3row, 3, groep.getNaam()); } sheet2row++; sheet3row++; } sheet2row++; sheet.setForceFormulaRecalculation(true); // Set print margins XSSFPrintSetup ps = sheet.getPrintSetup(); ps.setLandscape(true); ps.setFitWidth((short) 1); sheet.setFitToPage(true); sheet.setAutobreaks(false); workbook.setPrintArea(workbook.getSheetIndex(sheet), 1, 26, 0, pagelngth[groep.getGrootte()]); sheet.setColumnBreak(18); sheet.protectSheet(password); sheet.enableLocking(); } XSSFSheet sheet4 = workbook.cloneSheet(workbook.getSheetIndex(sheet3), "Deelnemerslijst (naam)"); sortSheet(sheet4, 1, 3, 62); //XSSFSheet sheet5 = workbook.cloneSheet(workbook.getSheetIndex(sheet3), "Deelnemerslijst (rating)"); //sortSheet(sheet5, 1,4); sheet2.protectSheet(password); sheet3.protectSheet(password); sheet4.protectSheet(password); //sheet5.protectSheet(password); // Remove template sheets for (int i = 0; i < 6; i++) { workbook.removeSheetAt(0); } // Close input file file.close(); // Store Excel to new file String filename = "Indeling resultaat.xlsm"; File outputFile = new File(filename); FileOutputStream outFile = new FileOutputStream(outputFile); workbook.write(outFile); // Close output file workbook.close(); outFile.close(); // And open it in the system editor Desktop.getDesktop().open(outputFile); } catch (IOException e) { logger.log(Level.SEVERE, "Fout bij maken indeling excel : " + e.getMessage()); } }
From source file:org.alfresco.bm.report.XLSXReporter.java
License:Open Source License
/** * Create a 'Summary' sheet containing the table of averages *//*from w w w. j a v a 2 s . c om*/ private void createSummarySheet(XSSFWorkbook workbook) throws IOException, NotFoundException { DBObject testRunObj = getTestService().getTestRunMetadata(test, run); // Create the sheet XSSFSheet sheet = workbook.createSheet("Summary"); // Create the fonts we need Font fontBold = workbook.createFont(); fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD); // Create the styles we need XSSFCellStyle summaryDataStyle = sheet.getWorkbook().createCellStyle(); summaryDataStyle.setAlignment(HorizontalAlignment.RIGHT); XSSFCellStyle headerStyle = sheet.getWorkbook().createCellStyle(); headerStyle.setAlignment(HorizontalAlignment.RIGHT); headerStyle.setFont(fontBold); XSSFRow row = null; int rowCount = 0; row = sheet.createRow(rowCount++); { row.getCell(0).setCellValue("Name:"); row.getCell(0).setCellStyle(headerStyle); row.getCell(1).setCellValue(title); row.getCell(1).setCellStyle(summaryDataStyle); } row = sheet.createRow(rowCount++); { String description = (String) testRunObj.get(FIELD_DESCRIPTION); description = description == null ? "" : description; row.getCell(0).setCellValue("Description:"); row.getCell(0).setCellStyle(headerStyle); row.getCell(1).setCellValue(description); row.getCell(1).setCellStyle(summaryDataStyle); } row = sheet.createRow(rowCount++); { row.getCell(0).setCellValue("Progress (%):"); row.getCell(0).setCellStyle(headerStyle); Double progress = (Double) testRunObj.get(FIELD_PROGRESS); progress = progress == null ? 0.0 : progress; row.getCell(1).setCellValue(progress * 100); row.getCell(1).setCellType(XSSFCell.CELL_TYPE_NUMERIC); row.getCell(1).setCellStyle(summaryDataStyle); } row = sheet.createRow(rowCount++); { row.getCell(0).setCellValue("State:"); row.getCell(0).setCellStyle(headerStyle); String state = (String) testRunObj.get(FIELD_STATE); if (state != null) { row.getCell(1).setCellValue(state); row.getCell(1).setCellStyle(summaryDataStyle); } } row = sheet.createRow(rowCount++); { row.getCell(0).setCellValue("Started:"); row.getCell(0).setCellStyle(headerStyle); Long time = (Long) testRunObj.get(FIELD_STARTED); if (time > 0) { row.getCell(1).setCellValue(FastDateFormat .getDateTimeInstance(FastDateFormat.MEDIUM, FastDateFormat.MEDIUM).format(time)); row.getCell(1).setCellStyle(summaryDataStyle); } } row = sheet.createRow(rowCount++); { row.getCell(0).setCellValue("Finished:"); row.getCell(0).setCellStyle(headerStyle); Long time = (Long) testRunObj.get(FIELD_COMPLETED); if (time > 0) { row.getCell(1).setCellValue(FastDateFormat .getDateTimeInstance(FastDateFormat.MEDIUM, FastDateFormat.MEDIUM).format(time)); row.getCell(1).setCellStyle(summaryDataStyle); } } row = sheet.createRow(rowCount++); { row.getCell(0).setCellValue("Duration:"); row.getCell(0).setCellStyle(headerStyle); Long time = (Long) testRunObj.get(FIELD_DURATION); if (time > 0) { row.getCell(1).setCellValue(DurationFormatUtils.formatDurationHMS(time)); row.getCell(1).setCellStyle(summaryDataStyle); } } rowCount++; rowCount++; // Create a header row row = sheet.createRow(rowCount++); // Header row String[] headers = new String[] { "Event Name", "Total Count", "Success Count", "Failure Count", "Success Rate (%)", "Min (ms)", "Max (ms)", "Arithmetic Mean (ms)", "Standard Deviation (ms)" }; int columnCount = 0; for (String header : headers) { XSSFCell cell = row.getCell(columnCount++); cell.setCellStyle(headerStyle); cell.setCellValue(header); } // Grab results and output them columnCount = 0; TreeMap<String, ResultSummary> summaries = collateResults(true); for (Map.Entry<String, ResultSummary> entry : summaries.entrySet()) { // Reset column count columnCount = 0; row = sheet.createRow(rowCount++); String eventName = entry.getKey(); ResultSummary summary = entry.getValue(); SummaryStatistics statsSuccess = summary.getStats(true); SummaryStatistics statsFail = summary.getStats(false); // Event Name row.getCell(columnCount++).setCellValue(eventName); // Total Count row.getCell(columnCount++).setCellValue(summary.getTotalResults()); // Success Count row.getCell(columnCount++).setCellValue(statsSuccess.getN()); // Failure Count row.getCell(columnCount++).setCellValue(statsFail.getN()); // Success Rate (%) row.getCell(columnCount++).setCellValue(summary.getSuccessPercentage()); // Min (ms) row.getCell(columnCount++).setCellValue((long) statsSuccess.getMin()); // Max (ms) row.getCell(columnCount++).setCellValue((long) statsSuccess.getMax()); // Arithmetic Mean (ms) row.getCell(columnCount++).setCellValue((long) statsSuccess.getMean()); // Standard Deviation (ms) row.getCell(columnCount++).setCellValue((long) statsSuccess.getStandardDeviation()); } // Auto-size the columns for (int i = 0; i < 10; i++) { sheet.autoSizeColumn(i); } sheet.setColumnWidth(1, 5120); // Printing PrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true); ps.setFitWidth((short) 1); ps.setLandscape(true); // Header and footer sheet.getHeader().setCenter(title); }
From source file:org.alfresco.bm.report.XLSXReporter.java
License:Open Source License
private void createPropertiesSheet(XSSFWorkbook workbook) throws IOException, NotFoundException { DBObject testRunObj;/* w w w .j a va 2 s .c o m*/ try { testRunObj = services.getTestDAO().getTestRun(test, run, true); } catch (ObjectNotFoundException e) { logger.error("Test run not found!", e); return; } // Ensure we don't leak passwords testRunObj = AbstractRestResource.maskValues(testRunObj); BasicDBList propertiesList = (BasicDBList) testRunObj.get(FIELD_PROPERTIES); if (propertiesList == null) { logger.error("Properties not found!"); return; } // Order the properties, nicely TreeMap<String, DBObject> properties = new TreeMap<String, DBObject>(); for (Object propertyObj : propertiesList) { DBObject property = (DBObject) propertyObj; String key = (String) property.get(FIELD_NAME); properties.put(key, property); } XSSFSheet sheet = workbook.createSheet("Properties"); // Create the fonts we need Font fontBold = workbook.createFont(); fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD); // Create the styles we need XSSFCellStyle propertyStyle = sheet.getWorkbook().createCellStyle(); propertyStyle.setAlignment(HorizontalAlignment.RIGHT); propertyStyle.setWrapText(true); XSSFCellStyle headerStyle = sheet.getWorkbook().createCellStyle(); headerStyle.setAlignment(HorizontalAlignment.RIGHT); headerStyle.setFont(fontBold); XSSFRow row = null; int rowCount = 0; XSSFCell cell = null; int cellCount = 0; row = sheet.createRow(rowCount++); cell = row.createCell(cellCount++); { cell.setCellValue("Property"); cell.setCellStyle(headerStyle); } cell = row.createCell(cellCount++); { cell.setCellValue("Value"); cell.setCellStyle(headerStyle); } cell = row.createCell(cellCount++); { cell.setCellValue("Origin"); cell.setCellStyle(headerStyle); } cellCount = 0; // Iterate all the properties for the test run for (Map.Entry<String, DBObject> entry : properties.entrySet()) { DBObject property = entry.getValue(); String key = (String) property.get(FIELD_NAME); String value = (String) property.get(FIELD_VALUE); String origin = (String) property.get(FIELD_ORIGIN); row = sheet.createRow(rowCount++); cell = row.createCell(cellCount++); { cell.setCellValue(key); cell.setCellStyle(propertyStyle); } cell = row.createCell(cellCount++); { cell.setCellValue(value); cell.setCellStyle(propertyStyle); } cell = row.createCell(cellCount++); { cell.setCellValue(origin); cell.setCellStyle(propertyStyle); } // Back to first column cellCount = 0; } // Size the columns sheet.autoSizeColumn(0); sheet.setColumnWidth(1, 15360); sheet.autoSizeColumn(2); // Printing PrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true); ps.setFitWidth((short) 1); ps.setLandscape(true); // Header and footer sheet.getHeader().setCenter(title); }
From source file:org.azkfw.document.database.xlsx.XLSXWriter.java
License:Apache License
private XSSFSheet createTableListSheet(final DatabaseModel datasource, final XSSFSheet sheet) { // //from w w w.ja v a2s . 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) { // /*ww w. j ava 2 s . c om*/ 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: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;// w w w . j a v a 2 s. c o m 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(); }