List of usage examples for org.apache.poi.ss.usermodel PrintSetup setFitWidth
void setFitWidth(short width);
From source file:org.alfresco.bm.report.XLSXReporter.java
License:Open Source License
private void createPropertiesSheet(XSSFWorkbook workbook) throws IOException, NotFoundException { DBObject testRunObj;//from w w w .ja va2s . 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.bbreak.excella.reports.util.ReportsUtil.java
License:Open Source License
/** * fromIdx??toIdx?????//from ww w . ja v a 2 s. c o m * @param workbook fromIdx?toIdx??workbook * @param fromIdx ? * @param sheet */ public static void copyPrintSetup(Workbook workbook, int fromIdx, Sheet toSheet) { Sheet fromSheet = workbook.getSheetAt(fromIdx); // ? PrintSetup fromPrintSetup = fromSheet.getPrintSetup(); PrintSetup printSetup = toSheet.getPrintSetup(); printSetup.setCopies(fromPrintSetup.getCopies()); printSetup.setDraft(fromPrintSetup.getDraft()); printSetup.setFitHeight(fromPrintSetup.getFitHeight()); printSetup.setFitWidth(fromPrintSetup.getFitWidth()); printSetup.setFooterMargin(fromPrintSetup.getFooterMargin()); printSetup.setHeaderMargin(fromPrintSetup.getHeaderMargin()); printSetup.setHResolution(fromPrintSetup.getHResolution()); printSetup.setLandscape(fromPrintSetup.getLandscape()); printSetup.setLeftToRight(fromPrintSetup.getLeftToRight()); printSetup.setNoColor(fromPrintSetup.getNoColor()); printSetup.setNoOrientation(fromPrintSetup.getNoOrientation()); printSetup.setPageStart(fromPrintSetup.getPageStart()); printSetup.setPaperSize(fromPrintSetup.getPaperSize()); printSetup.setScale(fromPrintSetup.getScale()); printSetup.setUsePage(fromPrintSetup.getUsePage()); printSetup.setValidSettings(fromPrintSetup.getValidSettings()); printSetup.setVResolution(fromPrintSetup.getVResolution()); // ? String printArea = workbook.getPrintArea(fromIdx); if (printArea != null) { if (printArea.contains("!")) { printArea = printArea.substring(printArea.indexOf("!") + 1); } int toIdx = workbook.getSheetIndex(toSheet); workbook.setPrintArea(toIdx, printArea); } // ? toSheet.setRepeatingColumns(fromSheet.getRepeatingColumns()); toSheet.setRepeatingRows(fromSheet.getRepeatingRows()); }
From source file:org.cerberus.service.export.ExportServiceFactory.java
License:Open Source License
private void createReportByTagExport(Workbook workbook) { //handles the export of the execution by tag data HashMap<String, SummaryStatisticsDTO> summaryMap = new HashMap<String, SummaryStatisticsDTO>(); HashMap<String, HashMap<String, List<TestCaseExecution>>> mapList = new HashMap<String, HashMap<String, List<TestCaseExecution>>>(); List<String> mapCountries = new ArrayList<String>(); List<CellStyle> stylesList = new LinkedList<CellStyle>(); if (exportOptions.contains("chart") || exportOptions.contains("list")) { //then we need to create the default colors for each cell HSSFWorkbook hwb = new HSSFWorkbook(); HSSFPalette palette = hwb.getCustomPalette(); CellStyle okStyle = workbook.createCellStyle(); // get the color which most closely matches the color you want to use // code to get the style for the cell goes here okStyle.setFillForegroundColor(palette.findSimilarColor(92, 184, 0).getIndex()); okStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); //okStyle.setFont(); stylesList.add(okStyle);/*from w w w . ja v a 2 s . c o m*/ } for (TestCaseExecution execution : (List<TestCaseExecution>) list) { //check if the country and application shows if (exportOptions.contains("chart") || exportOptions.contains("summary")) { String keySummaryTable = execution.getApplication() + " " + execution.getCountry() + " " + execution.getEnvironment(); SummaryStatisticsDTO stats; String status = execution.getControlStatus(); if (summaryMap.containsKey(keySummaryTable)) { stats = summaryMap.get(keySummaryTable); } else { stats = new SummaryStatisticsDTO(); stats.setApplication(execution.getApplication()); stats.setCountry(execution.getCountry()); stats.setEnvironment(execution.getEnvironment()); } stats.updateStatisticByStatus(status); summaryMap.put(keySummaryTable, stats); //updates the map } if (exportOptions.contains("list")) { if (exportOptions.contains("filter")) { //filter active } else { //all data is saved } HashMap<String, List<TestCaseExecution>> listExecution; List<TestCaseExecution> testCaseList; String testKey = execution.getTest(); String testCaseKey = execution.getTestCase(); if (mapList.containsKey(testKey)) { listExecution = mapList.get(testKey); } else { listExecution = new HashMap<String, List<TestCaseExecution>>(); } if (listExecution.containsKey(testCaseKey)) { testCaseList = listExecution.get(testCaseKey); } else { testCaseList = new ArrayList<TestCaseExecution>(); } testCaseList.add(execution); listExecution.put(testCaseKey, testCaseList); mapList.put(testKey, listExecution); if (mapCountries.indexOf(execution.getCountry()) == -1) { mapCountries.add(execution.getCountry()); } } } int rowCount = -1; //Create a blank sheet Sheet sheet = workbook.createSheet("Report by Tag"); sheet.getPrintSetup().setLandscape(true); PrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true); //ps.setFitHeight((short) 1); ps.setFitWidth((short) 1); sheet.setFitToPage(true); sheet.setColumnWidth(0, 9000); if (exportOptions.contains("chart")) { SummaryStatisticsDTO sumsTotal = calculateTotalValues(summaryMap); Row row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("Report By Status"); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("Status"); row.createCell(1).setCellValue("Total"); row.createCell(2).setCellValue("Percentage"); row = sheet.createRow(++rowCount); CellStyle okStyle = stylesList.get(0); Cell cellOk = row.createCell(0); cellOk.setCellValue("OK"); cellOk.setCellStyle(okStyle); row.createCell(1).setCellValue(sumsTotal.getOk()); row.createCell(2).setCellValue(sumsTotal.getPercOk()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("KO"); row.createCell(1).setCellValue(sumsTotal.getKo()); row.createCell(2).setCellValue(sumsTotal.getPercKo()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("FA"); row.createCell(1).setCellValue(sumsTotal.getFa()); row.createCell(2).setCellValue(sumsTotal.getPercFa()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("NA"); row.createCell(1).setCellValue(sumsTotal.getNa()); row.createCell(2).setCellValue(sumsTotal.getPercNa()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("NE"); row.createCell(1).setCellValue(sumsTotal.getNe()); row.createCell(2).setCellValue(sumsTotal.getPercNe()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("PE"); row.createCell(1).setCellValue(sumsTotal.getPe()); row.createCell(2).setCellValue(sumsTotal.getPercPe()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("CA"); row.createCell(1).setCellValue(sumsTotal.getCa()); row.createCell(2).setCellValue(sumsTotal.getPercCa()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("Total"); row.createCell(1).setCellValue(sumsTotal.getTotal()); sheet.createRow(++rowCount).createCell(0).setCellValue(""); sheet.createRow(++rowCount).createCell(0).setCellValue(""); sheet.createRow(++rowCount).createCell(0).setCellValue(""); sheet.createRow(++rowCount).createCell(0).setCellValue(""); } if (exportOptions.contains("summary")) { //draw the table with data Row row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("Summary Table"); //start creating data row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("Application"); row.createCell(1).setCellValue("Country"); row.createCell(2).setCellValue("Environment"); row.createCell(3).setCellValue("OK"); row.createCell(4).setCellValue("KO"); row.createCell(5).setCellValue("FA"); row.createCell(6).setCellValue("NA"); row.createCell(7).setCellValue("NE"); row.createCell(8).setCellValue("PE"); row.createCell(9).setCellValue("CA"); row.createCell(10).setCellValue("NOT OK"); row.createCell(11).setCellValue("Total"); /*temporary styles*/ CellStyle styleBlue = workbook.createCellStyle(); CellStyle styleGreen = workbook.createCellStyle(); HSSFWorkbook hwb = new HSSFWorkbook(); HSSFPalette palette = hwb.getCustomPalette(); // get the color which most closely matches the color you want to use HSSFColor myColor = palette.findSimilarColor(66, 139, 202); // get the palette index of that color short palIndex = myColor.getIndex(); // code to get the style for the cell goes here styleBlue.setFillForegroundColor(palIndex); styleBlue.setFillPattern(CellStyle.SPARSE_DOTS); HSSFColor myColorGreen = palette.findSimilarColor(92, 184, 0); styleGreen.setFillForegroundColor(myColorGreen.getIndex()); styleGreen.setFillPattern(CellStyle.SPARSE_DOTS); int startRow = (rowCount + 2); TreeMap<String, SummaryStatisticsDTO> sortedSummaryMap = new TreeMap<String, SummaryStatisticsDTO>( summaryMap); for (String key : sortedSummaryMap.keySet()) { row = sheet.createRow(++rowCount); SummaryStatisticsDTO sumStats = summaryMap.get(key); //application row.createCell(0).setCellValue((String) sumStats.getApplication()); //country row.createCell(1).setCellValue((String) sumStats.getCountry()); //environment row.createCell(2).setCellValue((String) sumStats.getEnvironment()); //OK row.createCell(3).setCellValue(sumStats.getOk()); //KO row.createCell(4).setCellValue(sumStats.getKo()); //FA row.createCell(5).setCellValue(sumStats.getFa()); //NA row.createCell(6).setCellValue(sumStats.getNa()); //NE row.createCell(7).setCellValue(sumStats.getNe()); //PE row.createCell(8).setCellValue(sumStats.getPe()); //CA row.createCell(9).setCellValue(sumStats.getCa()); int rowNumber = row.getRowNum() + 1; //NOT OK //row.createCell(11).setCellValue(sumStats.getNotOkTotal()); row.createCell(10).setCellFormula("SUM(E" + rowNumber + ":J" + rowNumber + ")"); //Total row.createCell(11).setCellFormula("SUM(D" + rowNumber + ",K" + rowNumber + ")"); //row.createCell(12).setCellValue(sumStats.getTotal()); if (sumStats.getOk() == sumStats.getTotal()) { for (int i = 0; i < 12; i++) { row.getCell(i).setCellStyle(styleGreen); } } } //TODO:FN percentages missing //Total row row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("Total"); row.createCell(1).setCellValue(""); row.createCell(2).setCellValue(""); //OK row.createCell(3).setCellFormula("SUM(D" + startRow + ":D" + rowCount + ")"); //KO row.createCell(4).setCellFormula("SUM(E" + startRow + ":E" + rowCount + ")"); //FA row.createCell(5).setCellFormula("SUM(F" + startRow + ":F" + rowCount + ")"); //NA row.createCell(6).setCellFormula("SUM(G" + startRow + ":G" + rowCount + ")"); //NE row.createCell(7).setCellFormula("SUM(H" + startRow + ":H" + rowCount + ")"); //PE row.createCell(8).setCellFormula("SUM(I" + startRow + ":I" + rowCount + ")"); //CA row.createCell(9).setCellFormula("SUM(J" + startRow + ":J" + rowCount + ")"); int rowNumberTotal = row.getRowNum() + 1; //NOT OK row.createCell(10).setCellFormula("SUM(E" + rowNumberTotal + ":J" + rowNumberTotal + ")"); //Total row.createCell(11).setCellFormula("SUM(D" + rowNumberTotal + ",K" + rowNumberTotal + ")"); for (int i = 0; i < 12; i++) { row.getCell(i).setCellStyle(styleBlue); } //add some empty rows sheet.createRow(++rowCount).createCell(0).setCellValue(""); sheet.createRow(++rowCount).createCell(0).setCellValue(""); sheet.createRow(++rowCount).createCell(0).setCellValue(""); sheet.createRow(++rowCount).createCell(0).setCellValue(""); } if (exportOptions.contains("list")) { //exports the data from test cases' executions Row r = sheet.createRow(++rowCount); r.createCell(0).setCellValue("Test"); r.createCell(1).setCellValue("Test Case"); r.createCell(2).setCellValue("Description"); r.createCell(3).setCellValue("Application"); r.createCell(4).setCellValue("Environment"); r.createCell(5).setCellValue("Browser"); //creates the country list Collections.sort(mapCountries);//sorts the list of countries int startIndexForCountries = 6; for (String country : mapCountries) { r.createCell(startIndexForCountries).setCellValue(country); startIndexForCountries++; } TreeMap<String, HashMap<String, List<TestCaseExecution>>> sortedKeys = new TreeMap<String, HashMap<String, List<TestCaseExecution>>>( mapList); rowCount++; for (String keyMapList : sortedKeys.keySet()) { rowCount = createRow(keyMapList, mapList.get(keyMapList), sheet, rowCount, mapCountries); } } }
From source file:org.unitime.timetable.export.XLSPrinter.java
License:Apache License
public XLSPrinter(OutputStream output, boolean checkLast) { iOutput = output;// ww w .j ava 2 s. c o m iCheckLast = checkLast; iWorkbook = new HSSFWorkbook(); iSheet = iWorkbook.createSheet(); iSheet.setDisplayGridlines(false); iSheet.setPrintGridlines(false); iSheet.setFitToPage(true); iSheet.setHorizontallyCenter(true); PrintSetup printSetup = iSheet.getPrintSetup(); printSetup.setLandscape(true); iSheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); iStyles = new HashMap<String, CellStyle>(); CellStyle style; style = iWorkbook.createCellStyle(); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(HorizontalAlignment.LEFT); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFont(getFont(true, false, false, Color.BLACK)); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setWrapText(true); iStyles.put("header", style); style = iWorkbook.createCellStyle(); style.setAlignment(HorizontalAlignment.LEFT); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFont(getFont(false, false, false, Color.BLACK)); style.setWrapText(true); iStyles.put("plain", style); style = iWorkbook.createCellStyle(); style.setAlignment(HorizontalAlignment.RIGHT); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFont(getFont(false, false, false, Color.BLACK)); iStyles.put("number", style); }
From source file:packtest.FitSheetToOnePage.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); Sheet sheet = wb.createSheet("format sheet"); PrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true);//from w w w . j a va 2s . co m ps.setFitHeight((short) 1); ps.setFitWidth((short) 1); // Create various cells and rows for spreadsheet. FileOutputStream fileOut = new FileOutputStream("fitSheetToOnePage.xlsx"); wb.write(fileOut); fileOut.close(); }
From source file:ro.dabuno.office.integration.BusinessPlan.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;//from w w w . j a va 2s . co m if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Business Plan"); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); //the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); for (int i = 0; i < titles.length; i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(styles.get("header")); } //columns for 11 weeks starting from 9-Jul Calendar calendar = Calendar.getInstance(); int year = calendar.get(Calendar.YEAR); calendar.setTime(fmt.parse("9-Jul")); calendar.set(Calendar.YEAR, year); for (int i = 0; i < 11; i++) { Cell cell = headerRow.createCell(titles.length + i); cell.setCellValue(calendar); cell.setCellStyle(styles.get("header_date")); calendar.roll(Calendar.WEEK_OF_YEAR, true); } //freeze the first row sheet.createFreezePane(0, 1); Row row; Cell cell; int rownum = 1; for (int i = 0; i < data.length; i++, rownum++) { row = sheet.createRow(rownum); if (data[i] == null) continue; for (int j = 0; j < data[i].length; j++) { cell = row.createCell(j); String styleName; boolean isHeader = i == 0 || data[i - 1] == null; switch (j) { case 0: if (isHeader) { styleName = "cell_b"; cell.setCellValue(Double.parseDouble(data[i][j])); } else { styleName = "cell_normal"; cell.setCellValue(data[i][j]); } break; case 1: if (isHeader) { styleName = i == 0 ? "cell_h" : "cell_bb"; } else { styleName = "cell_indented"; } cell.setCellValue(data[i][j]); break; case 2: styleName = isHeader ? "cell_b" : "cell_normal"; cell.setCellValue(data[i][j]); break; case 3: styleName = isHeader ? "cell_b_centered" : "cell_normal_centered"; cell.setCellValue(Integer.parseInt(data[i][j])); break; case 4: { calendar.setTime(fmt.parse(data[i][j])); calendar.set(Calendar.YEAR, year); cell.setCellValue(calendar); styleName = isHeader ? "cell_b_date" : "cell_normal_date"; break; } case 5: { int r = rownum + 1; String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")"; cell.setCellFormula(fmla); styleName = isHeader ? "cell_bg" : "cell_g"; break; } default: styleName = data[i][j] != null ? "cell_blue" : "cell_normal"; } cell.setCellStyle(styles.get(styleName)); } } //group rows for each phase, row numbers are 0-based sheet.groupRow(4, 6); sheet.groupRow(9, 13); sheet.groupRow(16, 18); //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 256 * 6); sheet.setColumnWidth(1, 256 * 33); sheet.setColumnWidth(2, 256 * 20); sheet.setZoom(75); //75% scale // Write the output to a file String file = "businessplan.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); wb.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;/*from www. j ava2 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(); }
From source file:sample.poi.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); SimpleDateFormat formatFile = new SimpleDateFormat("dd-MM-yyyy-hh-mm-ss"); HSSFWorkbook wb = new HSSFWorkbook(); Map<String, CellStyle> 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 Sheet sheet = wb.createSheet(months[month]); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false);//from ww w . j a v a2 s. c o m sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); //the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(80); Cell titleCell = headerRow.createCell(0); titleCell.setCellValue(months[month] + " " + "2013"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); //header with month titles Row monthRow = sheet.createRow(1); for (int i = 0; i < days.length; i++) { //set column widths, the width is measured 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)); Cell 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++) { Row row = sheet.createRow(rownum++); row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { Cell dayCell_1 = row.createCell(i * 2); Cell 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 String file = "calendar.xls"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }