List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet setPrintGridlines
@Override public void setPrintGridlines(boolean show)
From source file:com.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
public static void copySheetStyle(HSSFWorkbook destwb, HSSFSheet dest, HSSFWorkbook srcwb, HSSFSheet src) { if (src == null || dest == null) return;/*from www . jav a 2s . c o m*/ dest.setAlternativeExpression(src.getAlternateExpression()); dest.setAlternativeFormula(src.getAlternateFormula()); dest.setAutobreaks(src.getAutobreaks()); dest.setDialog(src.getDialog()); if (src.getColumnBreaks() != null) { for (int col : src.getColumnBreaks()) { dest.setColumnBreak(col); } } dest.setDefaultColumnWidth(src.getDefaultColumnWidth()); dest.setDefaultRowHeight(src.getDefaultRowHeight()); dest.setDefaultRowHeightInPoints(src.getDefaultRowHeightInPoints()); dest.setDisplayGuts(src.getDisplayGuts()); dest.setFitToPage(src.getFitToPage()); dest.setHorizontallyCenter(src.getHorizontallyCenter()); dest.setDisplayFormulas(src.isDisplayFormulas()); dest.setDisplayGridlines(src.isDisplayGridlines()); dest.setDisplayRowColHeadings(src.isDisplayRowColHeadings()); dest.setGridsPrinted(src.isGridsPrinted()); dest.setPrintGridlines(src.isPrintGridlines()); for (int i = 0; i < src.getNumMergedRegions(); i++) { CellRangeAddress r = src.getMergedRegion(i); dest.addMergedRegion(r); } if (src.getRowBreaks() != null) { for (int row : src.getRowBreaks()) { dest.setRowBreak(row); } } dest.setRowSumsBelow(src.getRowSumsBelow()); dest.setRowSumsRight(src.getRowSumsRight()); int maxcol = 0; for (int i = 0; i <= src.getLastRowNum(); i++) { HSSFRow row = src.getRow(i); if (row != null) { if (maxcol < row.getLastCellNum()) maxcol = row.getLastCellNum(); } } for (int col = 0; col <= maxcol; col++) { if (src.getColumnWidth(col) != src.getDefaultColumnWidth()) dest.setColumnWidth(col, src.getColumnWidth(col)); dest.setColumnHidden(col, src.isColumnHidden(col)); } }
From source file:de.maklerpoint.office.Schnittstellen.Excel.ExportExcelXLS.java
License:Open Source License
/** * /* ww w .ja v a 2s. c om*/ * @throws FileNotFoundException * @throws IOException */ public void write() throws FileNotFoundException, IOException { FileOutputStream out = new FileOutputStream(new File(filename)); HSSFWorkbook wb; wb = new HSSFWorkbook(); Map<String, HSSFCellStyle> styles = createStyles(wb); HSSFSheet sheet = wb.createSheet(sheetName); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); HSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); HSSFRow headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); int[][] width = new int[titles.length][titles.length]; for (int i = 0; i < titles.length; i++) { HSSFCell cell = headerRow.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(styles.get("header")); width[i][0] = titles[i].length(); } HSSFRow row; HSSFCell 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); if (data[i][j] == null) data[i][j] = ""; cell.setCellValue(data[i][j].toString()); if (data[i][j].toString().length() > width[j][0]) width[j][0] = data[i][j].toString().length(); } } for (int i = 0; i < titles.length; i++) { int widthShort = (256 * (width[i][0] + 3)); sheet.setColumnWidth(i, widthShort); } int position = (titles.length / 2) - 1; row = sheet.createRow(rownum + 3); cell = row.createCell(position); if (footName == null) { SimpleDateFormat df = new SimpleDateFormat("dd.MM.yyyy HH:mm"); cell.setCellValue("Export MaklerPoint vom " + df.format(new Date(System.currentTimeMillis())) + " - www.maklerpoint.de"); } else { cell.setCellValue(footName); } sheet.setZoom(3, 4); wb.write(out); out.close(); }
From source file:de.maklerpoint.office.Schnittstellen.Excel.ExportKalenderExcel.java
License:Open Source License
/** * /*from ww w . java2 s . c o m*/ * @throws FileNotFoundException * @throws IOException */ public void write() throws FileNotFoundException, IOException { Calendar calendar = Calendar.getInstance(); int year = calendar.get(Calendar.YEAR); HSSFWorkbook wb = new HSSFWorkbook(); Map<String, HSSFCellStyle> 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 HSSFSheet sheet = wb.createSheet(months[month]); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); HSSFPrintSetup 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 HSSFRow headerRow = sheet.createRow(0); headerRow.setHeightInPoints(80); HSSFCell titleCell = headerRow.createCell(0); titleCell.setCellValue(months[month] + " " + year); titleCell.setCellStyle(styles.get("title")); // sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); //header with month titles HSSFRow 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 Region(1, (short) 1, i*2, (short) (i * 2 + 1))); sheet.addMergedRegion(new CellRangeAddress(1, i * 2, 1, (i * 2 + 1))); // TODO Test HSSFCell 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++) { HSSFRow row = sheet.createRow(rownum++); row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { HSSFCell dayCell_1 = row.createCell((i * 2)); HSSFCell dayCell_2 = row.createCell((i * 2 + 1)); int day_of_week = calendar.get(Calendar.DAY_OF_WEEK); if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) { dayCell_1.setCellValue(day); calendar.set(Calendar.DAY_OF_MONTH, ++day); if (i == 0 || i == days.length - 1) { dayCell_1.setCellStyle(styles.get("weekend_left")); dayCell_2.setCellStyle(styles.get("weekend_right")); } else { dayCell_1.setCellStyle(styles.get("workday_left")); dayCell_2.setCellStyle(styles.get("workday_right")); } } else { dayCell_1.setCellStyle(styles.get("grey_left")); dayCell_2.setCellStyle(styles.get("grey_right")); } cnt++; } if (calendar.get(Calendar.MONTH) > month) break; } } // Write the output to a file FileOutputStream out = new FileOutputStream(this.filename); wb.write(out); out.close(); }
From source file:Sales.MainMenu.java
private void submitToPublishingPDFButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_submitToPublishingPDFButtonActionPerformed String validityFrom = validityFromDatePicker.getJFormattedTextField().getText(); String validityTo = validityToDatePicker.getJFormattedTextField().getText(); String kkluNumber = kkluNumberTextField.getText(); String pol = pPolTextField.getText(); String pod = pPodTextField.getText(); String commClass = pCommodityClassComboBox.getSelectedItem().toString(); String handlingInstructions = pHandlingInstructions.getSelectedItem().toString(); String commDesc = pCommodityDescriptionTextField.getText(); String oft = pOftTextField.getText(); String oftUnit = pOftComboBox.getSelectedItem().toString(); String baf = null;/* w w w .j a v a 2 s . com*/ String bafText = pBafTextField.getText(); Boolean bafIncluded = pBafIncludedCheckBox.isSelected(); String eca = null; String ecaText = pEcaTextField.getText(); String ecaUnit = pEcaComboBox.getSelectedItem().toString(); Boolean ecaIncluded = pEcaIncludedCheckBox.isSelected(); String thc = null; String thcText = "$" + pThcTextField.getText(); String thcUnit = pThcComboBox.getSelectedItem().toString(); Boolean thcIncluded = pThcIncludedCheckBox.isSelected(); Boolean thcSubjectToTariff = thcSubjectToTariffCheckBox.isSelected(); String wfg = null; String wfgText = pWfgTextField.getText(); String wfgUnit = pWfgComboBox.getSelectedItem().toString(); Boolean wfgIncluded = pWfgIncludedCheckBox.isSelected(); Boolean wfgSubjectToTariff = wfgSubjectToTariffCheckBox.isSelected(); String docFee = pDocFeeComboBox.getSelectedItem().toString(); Boolean docFeeIncluded = pDocFeeIncludedCheckBox.isSelected(); String comments = pCommentsTextArea.getText(); Boolean warRisk = pWarRiskCheckBox.isSelected(); String quoteID = pQuoteNumberTextField.getText(); String bookingNumber = pBookingNumberTextField.getText(); String storage = null; String storageText = storageTextField.getText(); String storageUnit = storageUnitComboBox.getSelectedItem().toString(); Boolean storageIncluded = storageIncludedCheckBox.isSelected(); Boolean storageSubjectToTariff = storageSubjectToTariffCheckBox.isSelected(); Boolean bafSubjectToTariff = bafSubjectToTariffCheckBox.isSelected(); Boolean ecaSubjectToTariff = ecaSubjectToTariffCheckBox.isSelected(); String TIME_STAMP = new SimpleDateFormat("MM/dd/yyyy HH:mm").format(Calendar.getInstance().getTime()); if (bafIncluded != true && bafSubjectToTariff != true) { baf = bafText; } else if (bafIncluded == true) { baf = "Included"; } else if (bafSubjectToTariff == true) { baf = "Subject to Tariff"; } if (ecaIncluded != true && ecaSubjectToTariff != true) { eca = ecaText; } else if (ecaIncluded == true) { eca = "Included"; } else if (ecaSubjectToTariff == true) { eca = "Subject to Tariff"; } else if (ecaSubjectToTariff == true) { eca = "Subject to Tariff"; } if (thcSubjectToTariff == true) { thc = "Subject to Tariff"; } else if (thcSubjectToTariff != true && thcIncluded != true) { thc = thcText; } else if (thcSubjectToTariff != true && thcIncluded == true) { thc = "Included"; } else if (thcIncluded == true && thcSubjectToTariff == true) { JOptionPane.showMessageDialog(null, "You can only select one"); } if (wfgSubjectToTariff == true) { wfg = "Subject to Tariff"; wfgUnit = "N/A"; } else if (wfgSubjectToTariff != true && wfgIncluded != true) { wfg = wfgText; } else if (wfgSubjectToTariff != true && wfgIncluded == true) { wfg = "Included"; } else if (wfgIncluded == true && wfgSubjectToTariff == true) { JOptionPane.showMessageDialog(null, "You can only select one"); } if (storageSubjectToTariff == true) { storage = "Subject to Tariff"; storageUnit = "N/A"; } else if (storageSubjectToTariff != true && storageIncluded != true) { storage = storageText; } else if (storageIncluded == true && storageSubjectToTariff != true) { storage = "Included"; } else if (storageIncluded == true && storageSubjectToTariff == true) { JOptionPane.showMessageDialog(null, "You can only select one"); } String sql = "INSERT INTO spotrates (validityFrom, validityTo, tariffNumber, pol, pod, bookingNumber, commClass, handlingInstructions, commDesc, oft, oftUnit, baf, bafIncluded, bafPerTariff, ecaBaf, ecaBafUnit, ecaIncluded, ecaPerTariff, thc, thcUnit,thcIncluded, thcPerTariff, wfg, wfgUnit, wfgIncluded, wfgPerTariff, storage, storageUnit, storageIncluded, storagePerTariff, docFee, docFeeIncluded, comments, quoteID, warRisk) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; try { PreparedStatement ps = CONN.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); ps.setString(1, validityFrom); ps.setString(2, validityTo); ps.setString(3, kkluNumber); ps.setString(4, pol); ps.setString(5, pod); ps.setString(6, bookingNumber); ps.setString(7, commClass); ps.setString(8, handlingInstructions); ps.setString(9, commDesc); ps.setString(10, oft); ps.setString(11, oftUnit); ps.setString(12, baf); ps.setBoolean(13, bafIncluded); ps.setBoolean(14, bafSubjectToTariff); ps.setString(15, eca); ps.setString(16, ecaUnit); ps.setBoolean(17, ecaIncluded); ps.setBoolean(18, ecaSubjectToTariff); ps.setString(19, thc); ps.setString(20, thcUnit); ps.setBoolean(21, thcIncluded); ps.setBoolean(22, thcSubjectToTariff); ps.setString(23, wfg); ps.setString(24, wfgUnit); ps.setBoolean(25, wfgIncluded); ps.setBoolean(26, wfgSubjectToTariff); ps.setString(27, storage); ps.setString(28, storageUnit); ps.setBoolean(29, storageIncluded); ps.setBoolean(30, storageSubjectToTariff); ps.setString(31, docFee); ps.setBoolean(32, docFeeIncluded); ps.setString(33, comments); ps.setString(34, quoteID); ps.setBoolean(35, warRisk); // Execute the update ps.executeUpdate(); //Return the auto-generated key ResultSet keys = ps.getGeneratedKeys(); int lastKey = 1; while (keys.next()) { lastKey = keys.getInt(1); } int pid = lastKey; Double oft1 = (Double) (Double.parseDouble(oft)); String eca1 = ""; switch (eca) { case "Included": eca1 = "Included"; break; case "Subject to Tariff": eca1 = "Subject to Tariff"; break; default: eca1 = eca; break; } String baf1; switch (baf) { case "Included": baf1 = "Included"; break; case "Subject To Tariff": baf1 = "Subject to Tariff"; break; default: baf1 = baf; break; } String thc1; switch (thc) { case "Included": thc1 = thc; break; case "Subject to Tariff": thc1 = "Subject to Tariff"; break; default: thc1 = "$" + thc + " per " + thcUnit; break; } switch (thcUnit) { case "FAS": thc1 = thcUnit; break; case "Subject to local charges": thc1 = thcUnit; default: break; } String wfg1; switch (wfg) { case "Included": wfg1 = wfg; break; case "Subject to Tariff": wfg1 = "Subject to Tariff"; break; default: wfg1 = "$" + wfg + " per " + wfgUnit; break; } switch (wfgUnit) { case "FAS": wfg1 = wfgUnit; break; case "Subject to local charges": wfg1 = wfgUnit; default: break; } /* *************************************** *************************************** *************************************** */ String filename = USER_HOME_FOLDER + "\\Desktop\\Publishing\\" + pol + " To " + pod + "; " + commDesc + " PID" + lastKey + ".xls"; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("KKLU" + kkluNumber); sheet.setColumnWidth(0, 650); sheet.setColumnWidth(1, 5742); sheet.setColumnWidth(2, 5920); sheet.setColumnWidth(3, 3668); sheet.setColumnWidth(4, 5711); //Bold Font HSSFFont font = workbook.createFont(); font.setBold(true); CellStyle style = workbook.createCellStyle(); style.setFont(font); //Currency cell type CellStyle currency = workbook.createCellStyle(); currency.setDataFormat((short) 7); //Percentage cell type CellStyle percentage = workbook.createCellStyle(); percentage.setDataFormat((short) 0xa); sheet.setDisplayGridlines(false); //Black medium sized border around cell CellStyle blackBorder = workbook.createCellStyle(); blackBorder.setBorderBottom(CellStyle.BORDER_MEDIUM); blackBorder.setBottomBorderColor(IndexedColors.BLACK.getIndex()); blackBorder.setBorderLeft(CellStyle.BORDER_MEDIUM); blackBorder.setLeftBorderColor(IndexedColors.BLACK.getIndex()); blackBorder.setBorderRight(CellStyle.BORDER_MEDIUM); blackBorder.setRightBorderColor(IndexedColors.BLACK.getIndex()); blackBorder.setBorderTop(CellStyle.BORDER_MEDIUM); blackBorder.setTopBorderColor(IndexedColors.BLACK.getIndex()); //Red font CellStyle redFontStyle = workbook.createCellStyle(); HSSFFont redFont = workbook.createFont(); redFont.setColor(HSSFColor.RED.index); redFontStyle.setFont(redFont); HSSFRow rowhead = sheet.createRow((short) 0); rowhead.createCell(3).setCellValue(validityFrom); HSSFRow row1 = sheet.createRow((short) 1); Cell cell = row1.createCell(1); cell.setCellValue("FILING REQUEST TO RICLFILE"); cell.setCellStyle(style); sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 3)); sheet.setPrintGridlines(false); HSSFRow row2 = sheet.createRow((short) 2); row2.createCell(0).setCellValue("A)"); row2.createCell(1).setCellValue("Tariff Number(KKLU):"); row2.createCell(2).setCellValue(kkluNumber); HSSFRow space0 = sheet.createRow((short) 3); HSSFRow row4 = sheet.createRow((short) 4); row4.createCell(0).setCellValue("B)"); row4.createCell(1).setCellValue("Commodity:"); row4.createCell(2).setCellValue(commDesc); HSSFRow space2 = sheet.createRow((short) 5); HSSFRow row5 = sheet.createRow((short) 6); row5.createCell(0).setCellValue("C)"); row5.createCell(1).setCellValue("POL:"); row5.createCell(2).setCellValue(pol); HSSFRow space3 = sheet.createRow((short) 7); HSSFRow row6 = sheet.createRow((short) 8); row6.createCell(0).setCellValue("D)"); row6.createCell(1).setCellValue("POD:"); row6.createCell(2).setCellValue(pod); HSSFRow space4 = sheet.createRow((short) 9); HSSFRow row7 = sheet.createRow((short) 10); row7.createCell(0).setCellValue("E)"); row7.createCell(1).setCellValue("Rate:"); Cell rate = row7.createCell(2); rate.setCellValue(oft1); rate.setCellStyle(currency); HSSFRow space5 = sheet.createRow((short) 11); HSSFRow row8 = sheet.createRow((short) 12); row8.createCell(0).setCellValue("F)"); row8.createCell(1).setCellValue("Rate Basis:"); row8.createCell(2).setCellValue(oftUnit); HSSFRow space6 = sheet.createRow((short) 13); HSSFRow row9 = sheet.createRow((short) 14); row9.createCell(0).setCellValue("G)"); row9.createCell(1).setCellValue("BAF:"); Cell Baf = row9.createCell(2); switch (baf1) { case "Included": Baf.setCellValue("Included"); break; case "Subject to Tariff": Baf.setCellValue("Subject to Tariff"); break; default: Baf.setCellValue(baf1); Baf.setCellStyle(percentage); break; } HSSFRow space7 = sheet.createRow((short) 15); HSSFRow row10 = sheet.createRow((short) 16); row10.createCell(0).setCellValue("H)"); row10.createCell(1).setCellValue("ECA BAF:"); Cell ecaBaf = row10.createCell(2); switch (eca) { case "Included": ecaBaf.setCellValue("Included"); break; case "Subject to Tariff": ecaBaf.setCellValue("Subject to Tariff"); break; default: ecaBaf.setCellValue(eca1 + " per " + ecaUnit); ecaBaf.setCellStyle(currency); break; } HSSFRow space8 = sheet.createRow((short) 17); HSSFRow row11 = sheet.createRow((short) 18); row11.createCell(0).setCellValue("I)"); row11.createCell(1).setCellValue("THC/WFG:"); row11.createCell(2).setCellValue(thc1 + " / " + wfg1); HSSFRow space = sheet.createRow((short) 19); HSSFRow row12 = sheet.createRow((short) 20); row12.createCell(0).setCellValue("J)"); row12.createCell(1).setCellValue("Storage:"); row12.createCell(2).setCellValue(storage); HSSFRow space10 = sheet.createRow((short) 21); HSSFRow row13 = sheet.createRow((short) 22); row13.createCell(0).setCellValue("K)"); row13.createCell(1).setCellValue("Doc Fee:"); row13.createCell(2).setCellValue(docFee); HSSFRow space11 = sheet.createRow((short) 23); HSSFRow row14 = sheet.createRow((short) 24); row14.createCell(0).setCellValue("L)"); row14.createCell(1).setCellValue("War Risk:"); HSSFRow space12 = sheet.createRow((short) 25); if (warRisk == true) { String warRiskPercentage = "3%"; row14.createCell(2).setCellValue(warRiskPercentage); } else if (warRisk != true) { String warRiskPercentage = "N/A"; row14.createCell(2).setCellValue(warRiskPercentage); } HSSFRow row15 = sheet.createRow((short) 26); row15.createCell(0).setCellValue("M)"); row15.createCell(1).setCellValue("Validity"); row15.createCell(2).setCellValue("Effective: " + validityFrom); HSSFRow row16 = sheet.createRow((short) 27); row16.createCell(2).setCellValue("Expiration: " + validityTo); HSSFRow space13 = sheet.createRow((short) 28); HSSFRow row17 = sheet.createRow((short) 29); row17.createCell(0).setCellValue("N)"); row17.createCell(1).setCellValue("Remarks"); row17.createCell(2).setCellValue(comments); HSSFRow space14 = sheet.createRow((short) 30); HSSFRow row18 = sheet.createRow((short) 31); row18.createCell(0).setCellValue("O)"); row18.createCell(1).setCellValue("Booking #:"); row18.createCell(2).setCellValue(bookingNumber); HSSFRow space15 = sheet.createRow((short) 32); HSSFRow row19 = sheet.createRow((short) 33); row19.createCell(0).setCellValue("P)"); row19.createCell(1).setCellValue("RQS #:"); row19.createCell(2).setCellValue(quoteID); HSSFRow space16 = sheet.createRow((short) 34); HSSFRow row20 = sheet.createRow((short) 35); row20.createCell(0).setCellValue("Q)"); row20.createCell(1).setCellValue("PID #:"); row20.createCell(2).setCellValue(pid); HSSFRow space17 = sheet.createRow((short) 36); HSSFRow space18 = sheet.createRow((short) 37); HSSFRow row21 = sheet.createRow((short) 38); row21.createCell(0).setCellValue(""); row21.createCell(1).setCellValue("For RICLFILE Use Only"); HSSFRow space19 = sheet.createRow((short) 39); HSSFRow row22 = sheet.createRow((short) 40); Cell comm = row22.createCell(1); comm.setCellValue("Commodity #:"); comm.setCellStyle(redFontStyle); row22.createCell(2).setCellValue(""); Cell desc = row22.createCell(3); desc.setCellValue("Description:"); desc.setCellStyle(redFontStyle); row22.createCell(4).setCellValue(""); HSSFRow space20 = sheet.createRow((short) 41); HSSFRow row24 = sheet.createRow((short) 42); Cell TLI = row24.createCell(1); TLI.setCellValue("TLI #:"); TLI.setCellStyle(redFontStyle); row24.createCell(2).setCellValue(""); HSSFRow space21 = sheet.createRow((short) 43); HSSFRow row26 = sheet.createRow((short) 44); Cell exp = row26.createCell(1); exp.setCellValue("Expiration: "); exp.setCellStyle(redFontStyle); row26.createCell(2).setCellValue(""); try (FileOutputStream fileOut = new FileOutputStream(filename)) { workbook.write(fileOut); } System.out.print("Your excel file has been generate"); String spotRateId = String.valueOf(lastKey); JOptionPane.showMessageDialog(null, "The spot filing (PID" + spotRateId + ") for " + quoteID + " has been succsefully generated."); pQuoteIDTextField.setText(""); validityFromDatePicker.getJFormattedTextField().setText(""); validityToDatePicker.getJFormattedTextField().setText(""); kkluNumberTextField.setText(""); pPolTextField.setText(""); pPodTextField.setText(""); pCommodityClassComboBox.setSelectedIndex(0); pHandlingInstructions.setSelectedIndex(0); pCommodityDescriptionTextField.setText(""); pOftTextField.setText(""); pOftComboBox.setSelectedItem(""); bafSubjectToTariffCheckBox.setSelected(false); pBafTextField.setText(""); pBafIncludedCheckBox.setSelected(false); pEcaTextField.setText(""); pEcaComboBox.setSelectedIndex(0); pEcaIncludedCheckBox.setSelected(false); ecaSubjectToTariffCheckBox.setSelected(false); pThcTextField.setText(""); pThcComboBox.setSelectedIndex(0); pThcIncludedCheckBox.setSelected(false); thcSubjectToTariffCheckBox.setSelected(false); pWfgTextField.setText(""); pWfgComboBox.setSelectedIndex(0); pWfgIncludedCheckBox.setSelected(false); wfgSubjectToTariffCheckBox.setSelected(false); pDocFeeComboBox.setSelectedIndex(0); pWarRiskCheckBox.setSelected(false); storageSubjectToTariffCheckBox.setSelected(false); pCommentsTextArea.setText(""); pBookingNumberTextField.setText(""); pQuoteNumberTextField.setText(""); pIDTextField.setText(""); } catch (SQLException | IOException e) { JOptionPane.showMessageDialog(null, "Error: " + e.getMessage()); System.out.println(e.getMessage()); } }
From source file:Sales.MainMenu.java
private void saveChangesPublishingPDFButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_saveChangesPublishingPDFButtonActionPerformed // Save spot rate changes String validityFrom = validityFromDatePicker.getJFormattedTextField().getText(); String validityTo = validityToDatePicker.getJFormattedTextField().getText(); String kkluNumber = kkluNumberTextField.getText(); String pol = pPolTextField.getText(); String pod = pPodTextField.getText(); String commClass = pCommodityClassComboBox.getSelectedItem().toString(); String handlingInstructions = pHandlingInstructions.getSelectedItem().toString(); String commDesc = pCommodityDescriptionTextField.getText(); String oft = pOftTextField.getText(); String oftUnit = pOftComboBox.getSelectedItem().toString(); String baf = null;/*from w w w. j a va2 s .co m*/ String bafText = pBafTextField.getText(); Boolean bafIncluded = pBafIncludedCheckBox.isSelected(); String eca = null; String ecaText = pEcaTextField.getText(); String ecaUnit = pEcaComboBox.getSelectedItem().toString(); Boolean ecaIncluded = pEcaIncludedCheckBox.isSelected(); String thc = null; String thcText = "$" + pThcTextField.getText(); String thcUnit = pThcComboBox.getSelectedItem().toString(); Boolean thcIncluded = pThcIncludedCheckBox.isSelected(); Boolean thcSubjectToTariff = thcSubjectToTariffCheckBox.isSelected(); String wfg = null; String wfgText = pWfgTextField.getText(); String wfgUnit = pWfgComboBox.getSelectedItem().toString(); Boolean wfgIncluded = pWfgIncludedCheckBox.isSelected(); Boolean wfgSubjectToTariff = wfgSubjectToTariffCheckBox.isSelected(); String docFee = pDocFeeComboBox.getSelectedItem().toString(); Boolean docFeeIncluded = pDocFeeIncludedCheckBox.isSelected(); String comments = pCommentsTextArea.getText(); Boolean warRisk = pWarRiskCheckBox.isSelected(); String quoteID = pQuoteNumberTextField.getText(); String bookingNumber = pBookingNumberTextField.getText(); String storage = null; String storageText = storageTextField.getText(); String storageUnit = storageUnitComboBox.getSelectedItem().toString(); Boolean storageIncluded = storageIncludedCheckBox.isSelected(); Boolean storageSubjectToTariff = storageSubjectToTariffCheckBox.isSelected(); Boolean bafSubjectToTariff = bafSubjectToTariffCheckBox.isSelected(); Boolean ecaSubjectToTariff = ecaSubjectToTariffCheckBox.isSelected(); String ID = pQuoteIDTextField.getText(); String timeStamp = new SimpleDateFormat("MM/dd/yyyy HH:mm").format(Calendar.getInstance().getTime()); if (bafIncluded != true && bafSubjectToTariff != true) { baf = bafText; } else if (bafIncluded == true) { baf = "Included"; } else if (bafSubjectToTariff == true) { baf = "Subject to Tariff"; } if (ecaIncluded != true && ecaSubjectToTariff != true) { eca = ecaText; } else if (ecaIncluded == true) { eca = "Included"; } else if (ecaSubjectToTariff == true) { eca = "Subject to Tariff"; } else if (ecaSubjectToTariff == true) { eca = "Subject to Tariff"; } if (thcSubjectToTariff == true) { thc = "Subject to Tariff"; } else if (thcSubjectToTariff != true && thcIncluded != true) { thc = thcText; } else if (thcSubjectToTariff != true && thcIncluded == true) { thc = "Included"; } else if (thcIncluded == true && thcSubjectToTariff == true) { JOptionPane.showMessageDialog(null, "You can only select one"); } if (wfgSubjectToTariff == true) { wfg = "Subject to Tariff"; wfgUnit = "N/A"; } else if (wfgSubjectToTariff != true && wfgIncluded != true) { wfg = wfgText; } else if (wfgSubjectToTariff != true && wfgIncluded == true) { wfg = "Included"; } else if (wfgIncluded == true && wfgSubjectToTariff == true) { JOptionPane.showMessageDialog(null, "You can only select one"); } if (storageSubjectToTariff == true) { storage = "Subject to Tariff"; storageUnit = "N/A"; } else if (storageSubjectToTariff != true && storageIncluded != true) { storage = storageText; } else if (storageIncluded == true && storageSubjectToTariff != true) { storage = "Included"; } else if (storageIncluded == true && storageSubjectToTariff == true) { JOptionPane.showMessageDialog(null, "You can only select one"); } String sql = "UPDATE spotrates SET validityFrom=?, validityTo=?, tariffNumber=?, pol=?, pod=?, bookingNumber=?, commClass=?, handlingInstructions=?, commDesc=?, oft=?, oftUnit=?, baf=?,bafIncluded=?, bafPerTariff=?, ecaBaf=?, ecaBafUnit=?, ecaIncluded=?, ecaPerTariff=?, thc=?, thcUnit=?, thcIncluded=?, thcPerTariff=?, wfg=?, wfgUnit=?, wfgIncluded=?, wfgPerTariff=?, storage=?, storageUnit=?, storageIncluded=?, storagePerTariff=?, docFee=?, docFeeIncluded=?, comments=?, quoteID=?, warRisk=? WHERE ID=?"; try { PreparedStatement ps = CONN.prepareStatement(sql); ps.setString(1, validityFrom); ps.setString(2, validityTo); ps.setString(3, kkluNumber); ps.setString(4, pol); ps.setString(5, pod); ps.setString(6, bookingNumber); ps.setString(7, commClass); ps.setString(8, handlingInstructions); ps.setString(9, commDesc); ps.setString(10, oft); ps.setString(11, oftUnit); ps.setString(12, baf); ps.setBoolean(13, bafIncluded); ps.setBoolean(14, bafSubjectToTariff); ps.setString(15, eca); ps.setString(16, ecaUnit); ps.setBoolean(17, ecaIncluded); ps.setBoolean(18, ecaSubjectToTariff); ps.setString(19, thc); ps.setString(20, thcUnit); ps.setBoolean(21, thcIncluded); ps.setBoolean(22, thcSubjectToTariff); ps.setString(23, wfg); ps.setString(24, wfgUnit); ps.setBoolean(25, wfgIncluded); ps.setBoolean(26, wfgSubjectToTariff); ps.setString(27, storage); ps.setString(28, storageUnit); ps.setBoolean(29, storageIncluded); ps.setBoolean(30, storageSubjectToTariff); ps.setString(31, docFee); ps.setBoolean(32, docFeeIncluded); ps.setString(33, comments); ps.setString(34, quoteID); ps.setBoolean(35, warRisk); ps.setString(40, ID); ps.executeUpdate(); String addBookingNumber = "UPDATE allquotes SET bookingNumber='" + bookingNumber + "', publishingID='" + ID + "' WHERE ID='" + quoteID + "';"; PreparedStatement psAddBookingNumber = CONN.prepareStatement(addBookingNumber); psAddBookingNumber.executeUpdate(addBookingNumber); Double oft1 = (Double) (Double.parseDouble(oft)); String eca1 = ""; switch (eca) { case "Included": eca1 = "Included"; break; case "Subject to Tariff": eca1 = "Subject to Tariff"; break; default: eca1 = "$" + eca + " " + ecaUnit; break; } String baf1; switch (baf) { case "Included": baf1 = "Included"; break; case "Subject To Tariff": baf1 = "Subject to Tariff"; break; default: baf1 = baf + "%"; break; } String thc1; switch (thc) { case "Included": thc1 = thc; break; case "Subject to Tariff": thc1 = "Subject to Tariff"; break; default: thc1 = "$" + thc + " per " + thcUnit; break; } switch (thcUnit) { case "FAS": thc1 = thcUnit; break; case "Subject to local charges": thc1 = thcUnit; default: break; } String wfg1; switch (wfg) { case "Included": wfg1 = wfg; break; case "Subject to Tariff": wfg1 = "Subject to Tariff"; break; default: wfg1 = "$" + wfg + " per " + wfgUnit; break; } switch (wfgUnit) { case "FAS": wfg1 = wfgUnit; break; case "Subject to local charges": wfg1 = wfgUnit; default: break; } /* *************************************** *************************************** *************************************** */ String filename = USER_HOME_FOLDER + "\\Desktop\\Publishing\\" + pol + " To " + pod + "; " + commDesc + " PID" + ID + ".xls"; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("KKLU" + kkluNumber); sheet.setColumnWidth(0, 650); sheet.setColumnWidth(1, 5742); sheet.setColumnWidth(2, 5920); sheet.setColumnWidth(3, 3668); sheet.setColumnWidth(4, 5711); //Bold Font HSSFFont font = workbook.createFont(); font.setBold(true); CellStyle style = workbook.createCellStyle(); style.setFont(font); //Currency cell type CellStyle currency = workbook.createCellStyle(); currency.setDataFormat((short) 7); //Percentage cell type CellStyle percentage = workbook.createCellStyle(); percentage.setDataFormat((short) 0xa); sheet.setDisplayGridlines(false); //Black medium sized border around cell CellStyle blackBorder = workbook.createCellStyle(); blackBorder.setBorderBottom(CellStyle.BORDER_MEDIUM); blackBorder.setBottomBorderColor(IndexedColors.BLACK.getIndex()); blackBorder.setBorderLeft(CellStyle.BORDER_MEDIUM); blackBorder.setLeftBorderColor(IndexedColors.BLACK.getIndex()); blackBorder.setBorderRight(CellStyle.BORDER_MEDIUM); blackBorder.setRightBorderColor(IndexedColors.BLACK.getIndex()); blackBorder.setBorderTop(CellStyle.BORDER_MEDIUM); blackBorder.setTopBorderColor(IndexedColors.BLACK.getIndex()); //Red font CellStyle redFontStyle = workbook.createCellStyle(); HSSFFont redFont = workbook.createFont(); redFont.setColor(HSSFColor.RED.index); redFontStyle.setFont(redFont); HSSFRow rowhead = sheet.createRow((short) 0); rowhead.createCell(3).setCellValue(validityFrom); HSSFRow row1 = sheet.createRow((short) 1); Cell cell = row1.createCell(1); cell.setCellValue("FILING REQUEST TO RICLFILE"); cell.setCellStyle(style); sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 3)); sheet.setPrintGridlines(false); HSSFRow row2 = sheet.createRow((short) 2); row2.createCell(0).setCellValue("A)"); row2.createCell(1).setCellValue("Tariff Number(KKLU):"); row2.createCell(2).setCellValue(kkluNumber); HSSFRow space0 = sheet.createRow((short) 3); HSSFRow row4 = sheet.createRow((short) 4); row4.createCell(0).setCellValue("B)"); row4.createCell(1).setCellValue("Commodity:"); row4.createCell(2).setCellValue(commDesc); HSSFRow space2 = sheet.createRow((short) 5); HSSFRow row5 = sheet.createRow((short) 6); row5.createCell(0).setCellValue("C)"); row5.createCell(1).setCellValue("POL:"); row5.createCell(2).setCellValue(pol); HSSFRow space3 = sheet.createRow((short) 7); HSSFRow row6 = sheet.createRow((short) 8); row6.createCell(0).setCellValue("D)"); row6.createCell(1).setCellValue("POD:"); row6.createCell(2).setCellValue(pod); HSSFRow space4 = sheet.createRow((short) 9); HSSFRow row7 = sheet.createRow((short) 10); row7.createCell(0).setCellValue("E)"); row7.createCell(1).setCellValue("Rate:"); Cell rate = row7.createCell(2); rate.setCellValue(oft1); rate.setCellStyle(currency); HSSFRow space5 = sheet.createRow((short) 11); HSSFRow row8 = sheet.createRow((short) 12); row8.createCell(0).setCellValue("F)"); row8.createCell(1).setCellValue("Rate Basis:"); row8.createCell(2).setCellValue(oftUnit); HSSFRow space6 = sheet.createRow((short) 13); HSSFRow row9 = sheet.createRow((short) 14); row9.createCell(0).setCellValue("G)"); row9.createCell(1).setCellValue("BAF:"); Cell Baf = row9.createCell(2); switch (baf1) { case "Included": Baf.setCellValue("Included"); break; case "Subject to Tariff": Baf.setCellValue("Subject to Tariff"); break; default: Baf.setCellValue(baf1); break; } HSSFRow space7 = sheet.createRow((short) 15); HSSFRow row10 = sheet.createRow((short) 16); row10.createCell(0).setCellValue("H)"); row10.createCell(1).setCellValue("ECA BAF:"); Cell ecaBaf = row10.createCell(2); switch (eca) { case "Included": ecaBaf.setCellValue("Included"); break; case "Subject to Tariff": ecaBaf.setCellValue("Subject to Tariff"); break; default: ecaBaf.setCellValue("$" + eca1 + " per " + ecaUnit); ecaBaf.setCellStyle(currency); break; } HSSFRow space8 = sheet.createRow((short) 17); HSSFRow row11 = sheet.createRow((short) 18); row11.createCell(0).setCellValue("I)"); row11.createCell(1).setCellValue("THC/WFG:"); row11.createCell(2).setCellValue(thc1 + " / " + wfg1); HSSFRow space = sheet.createRow((short) 19); HSSFRow row12 = sheet.createRow((short) 20); row12.createCell(0).setCellValue("J)"); row12.createCell(1).setCellValue("Storage:"); row12.createCell(2).setCellValue(storage); HSSFRow space10 = sheet.createRow((short) 21); HSSFRow row13 = sheet.createRow((short) 22); row13.createCell(0).setCellValue("K)"); row13.createCell(1).setCellValue("Doc Fee:"); row13.createCell(2).setCellValue(docFee); HSSFRow space11 = sheet.createRow((short) 23); HSSFRow row14 = sheet.createRow((short) 24); row14.createCell(0).setCellValue("L)"); row14.createCell(1).setCellValue("War Risk:"); HSSFRow space12 = sheet.createRow((short) 25); if (warRisk == true) { String warRiskPercentage = "3%"; row14.createCell(2).setCellValue(warRiskPercentage); } else if (warRisk != true) { String warRiskPercentage = "N/A"; row14.createCell(2).setCellValue(warRiskPercentage); } HSSFRow row15 = sheet.createRow((short) 26); row15.createCell(0).setCellValue("M)"); row15.createCell(1).setCellValue("Validity"); row15.createCell(2).setCellValue("Effective: " + validityFrom); HSSFRow row16 = sheet.createRow((short) 27); row16.createCell(2).setCellValue("Expiration: " + validityTo); HSSFRow space13 = sheet.createRow((short) 28); HSSFRow row17 = sheet.createRow((short) 29); row17.createCell(0).setCellValue("N)"); row17.createCell(1).setCellValue("Remarks"); row17.createCell(2).setCellValue(comments); HSSFRow space14 = sheet.createRow((short) 30); HSSFRow row18 = sheet.createRow((short) 31); row18.createCell(0).setCellValue("O)"); row18.createCell(1).setCellValue("Booking #:"); row18.createCell(2).setCellValue(bookingNumber); HSSFRow space15 = sheet.createRow((short) 32); HSSFRow row19 = sheet.createRow((short) 33); row19.createCell(0).setCellValue("P)"); row19.createCell(1).setCellValue("RQS #:"); row19.createCell(2).setCellValue(quoteID); HSSFRow space16 = sheet.createRow((short) 34); HSSFRow row20 = sheet.createRow((short) 35); row20.createCell(0).setCellValue("Q)"); row20.createCell(1).setCellValue("PID #:"); row20.createCell(2).setCellValue(ID); HSSFRow space17 = sheet.createRow((short) 36); HSSFRow space18 = sheet.createRow((short) 37); HSSFRow row21 = sheet.createRow((short) 38); row21.createCell(0).setCellValue(""); row21.createCell(1).setCellValue("For RICLFILE Use Only"); HSSFRow space19 = sheet.createRow((short) 39); HSSFRow row22 = sheet.createRow((short) 40); Cell comm = row22.createCell(1); comm.setCellValue("Commodity #:"); comm.setCellStyle(redFontStyle); row22.createCell(2).setCellValue(""); Cell desc = row22.createCell(3); desc.setCellValue("Description:"); desc.setCellStyle(redFontStyle); row22.createCell(4).setCellValue(""); HSSFRow space20 = sheet.createRow((short) 41); HSSFRow row24 = sheet.createRow((short) 42); Cell TLI = row24.createCell(1); TLI.setCellValue("TLI #:"); TLI.setCellStyle(redFontStyle); row24.createCell(2).setCellValue(""); HSSFRow space21 = sheet.createRow((short) 43); HSSFRow row26 = sheet.createRow((short) 44); Cell exp = row26.createCell(1); exp.setCellValue("Expiration: "); exp.setCellStyle(redFontStyle); row26.createCell(2).setCellValue(""); try (FileOutputStream fileOut = new FileOutputStream(filename)) { workbook.write(fileOut); } System.out.print("Your excel file has been generate"); String spotRateId = String.valueOf(ID); JOptionPane.showMessageDialog(null, "PID" + ID + " has been successfully update."); pQuoteIDTextField.setText(""); validityFromDatePicker.getJFormattedTextField().setText(""); validityToDatePicker.getJFormattedTextField().setText(""); kkluNumberTextField.setText(""); pPolTextField.setText(""); pPodTextField.setText(""); pCommodityClassComboBox.setSelectedIndex(0); pHandlingInstructions.setSelectedIndex(0); pCommodityDescriptionTextField.setText(""); pOftTextField.setText(""); pOftComboBox.setSelectedItem(""); bafSubjectToTariffCheckBox.setSelected(false); pBafTextField.setText(""); pBafIncludedCheckBox.setSelected(false); pEcaTextField.setText(""); pEcaComboBox.setSelectedItem(""); pEcaIncludedCheckBox.setSelected(false); ecaSubjectToTariffCheckBox.setSelected(false); pThcTextField.setText(""); pThcComboBox.setSelectedIndex(0); pThcIncludedCheckBox.setSelected(false); thcSubjectToTariffCheckBox.setSelected(false); pWfgTextField.setText(""); pWfgComboBox.setSelectedIndex(0); pWfgIncludedCheckBox.setSelected(false); wfgSubjectToTariffCheckBox.setSelected(false); pDocFeeComboBox.setSelectedIndex(0); pWarRiskCheckBox.setSelected(false); storageSubjectToTariffCheckBox.setSelected(false); pCommentsTextArea.setText(""); pBookingNumberTextField.setText(""); storageTextField.setText(""); storageUnitComboBox.setSelectedIndex(0); storageIncludedCheckBox.setSelected(false); pQuoteNumberTextField.setText(""); pIDTextField.setText(""); } catch (SQLException | IOException e) { JOptionPane.showMessageDialog(null, "Error: " + e.getMessage()); System.out.println(e.getMessage()); } }