Example usage for org.apache.poi.ss.usermodel CellStyle setDataFormat

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel CellStyle setDataFormat.

Prototype

void setDataFormat(short fmt);

Source Link

Document

set the data format (must be a valid format).

Usage

From source file:ro.ldir.report.formatter.UserExcelFormatter.java

License:Open Source License

public final Workbook convert(Workbook wb) {
    Sheet sheet = wb.createSheet("Utilizatori");
    CreationHelper createHelper = wb.getCreationHelper();
    teams = new ArrayList<Team>();

    Row row = sheet.createRow(0);/*from w  w w . j  ava  2s.co m*/
    row.createCell(0).setCellValue("Prenume");
    row.createCell(1).setCellValue("Nume");
    row.createCell(2).setCellValue("Email");
    row.createCell(3).setCellValue("Telefon");
    row.createCell(4).setCellValue("Rol");
    row.createCell(5).setCellValue("Jude\u0163");
    row.createCell(6).setCellValue("Oras");
    row.createCell(7).setCellValue("Data \u00eenregistr\u04d1rii");
    row.createCell(8).setCellValue("ID");
    row.createCell(9).setCellValue("Nr. mormane");
    row.createCell(10).setCellValue("Nr. zone");
    row.createCell(11).setCellValue("Activitate");

    for (int i = 0; i < users.size(); i++) {
        row = sheet.createRow(i + 1);
        User user = users.get(i);
        if (user == null)
            continue;

        row.createCell(0).setCellValue(user.getFirstName());
        row.createCell(1).setCellValue(user.getLastName());
        row.createCell(2).setCellValue(user.getEmail());
        row.createCell(3).setCellValue(user.getPhone());
        row.createCell(4).setCellValue(user.getRole());
        row.createCell(5).setCellValue(user.getCounty());
        row.createCell(6).setCellValue(user.getTown());

        if (user.getRecordDate() != null) {
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
            Cell dateCell = row.createCell(7);
            dateCell.setCellValue(user.getRecordDate());
            dateCell.setCellStyle(cellStyle);
        }

        row.createCell(8, Cell.CELL_TYPE_NUMERIC).setCellValue(user.getUserId());

        if (user.getGarbages() == null)
            row.createCell(9, Cell.CELL_TYPE_NUMERIC).setCellValue(0);
        else
            row.createCell(9, Cell.CELL_TYPE_NUMERIC).setCellValue(user.getGarbages().size());

        if (user.getMemberOf() == null || user.getMemberOf().getChartedAreas() == null)
            row.createCell(10, Cell.CELL_TYPE_NUMERIC).setCellValue(0);
        else
            row.createCell(10, Cell.CELL_TYPE_NUMERIC)
                    .setCellValue(user.getMemberOf().getChartedAreas().size());

        StringBuffer ab = new StringBuffer();
        List<User.Activity> activities = user.getActivities();
        if (activities != null && activities.size() > 0) {
            for (User.Activity activity : activities)

                if (activity != null)
                    if (activity.getReportName() != null)
                        ab.append(activity.getReportName() + ", ");
                    else
                        ab.append("  " + ", ");

            if (ab.length() > 1)
                row.createCell(11).setCellValue(ab.substring(0, ab.length() - 2));
            else
                row.createCell(11).setCellValue(" ");

        }

        List<Team> managedTeams = user.getManagedTeams();
        if (managedTeams != null && managedTeams.size() > 0) {
            for (Team team : managedTeams)
                teams.add(team);
        }

    }

    TeamExcelFormatter teamWb = new TeamExcelFormatter(teams);
    wb = teamWb.convert(wb);

    return wb;
}

---

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;/*from  w  ww.  j ava  2 s.  c  o  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 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.  jav  a2 s. c  o 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());
    }
}

---

From source file:savio_estadisticas.clases.Control.Estadisticas.Table_DataBase.java

public void GenerateStatistis(Workbook libro, int total, int Ninguno) {
    Sheet estadisticas = libro.createSheet("Estadisticas");

    for (int i = 0; i < 22; i++) {
        Row fila_esta = estadisticas.createRow(i);
        for (int j = 0; j < 4; j++) {
            Cell celda_esta = fila_esta.createCell(j);
            switch (i) {
            case 0:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Recurso");
                    break;
                case 1:
                    celda_esta.setCellValue("Cursos");
                    break;
                case 2:
                    celda_esta.setCellValue("Promedio (%)");
                    break;
                case 3:
                    celda_esta.setCellValue("Total Cursos");
                    break;
                }/*from   w w  w.j a v a2s .c o m*/
                break;
            case 1:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Tareas");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!D:D,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 2:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Consultas");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!E:E,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 3:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Etiquetas");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!F:F,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 4:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Foros");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!G:G,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 5:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Chats");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!H:H,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 6:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Lecciones");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!I:I,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 7:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Wikis");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!J:J,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 8:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Bases de Datos");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!K:K,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 9:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Paquetes SCORM");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!L:L,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 10:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Archivos");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!M:M,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 11:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("URLs");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!N:N,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 12:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Paginas");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!O:O,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 13:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Cuestionarios");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!P:P,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 14:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Talleres");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!Q:Q,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 15:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("VPL");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!R:R,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 16:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Libros");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!S:S,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 17:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Glosario");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!T:T,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 18:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Portafolio");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!U:U,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 19:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Innovadores");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!V:V,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 20:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Ninguno");
                    break;
                case 1:
                    celda_esta.setCellValue(Ninguno);
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            }
        }
    }

    //celda_esta.setCellFormula("COUNTIF(Tabla!S:S,\">0\")");
}

---

From source file:se.mithlond.services.content.impl.ejb.report.ExcelReportServiceBean.java

License:Apache License

/**
 * {@inheritDoc}/*from w ww  . ja va 2s.c o  m*/
 */
@Override
@SuppressWarnings("all")
public CellStyle getCellStyle(final ExcelElement el, final Workbook workbook) {

    // Check sanity
    Validate.notNull(workbook, "workbook");
    Validate.notNull(el, "el");

    // Acquire the el and Font as expected
    final CellStyle toReturn = workbook.createCellStyle();
    final Font theFont = workbook.createFont();

    switch (el) {

    case TITLE:
        theFont.setFontHeightInPoints((short) 18);
        theFont.setBold(true);
        theFont.setColor(IndexedColors.BLUE_GREY.getIndex());

        toReturn.setAlignment(HorizontalAlignment.CENTER);
        toReturn.setVerticalAlignment(VerticalAlignment.CENTER);
        break;

    case HEADER:
        theFont.setFontHeightInPoints((short) 11);
        theFont.setColor(IndexedColors.WHITE.getIndex());

        toReturn.setAlignment(HorizontalAlignment.CENTER);
        toReturn.setVerticalAlignment(VerticalAlignment.CENTER);
        toReturn.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
        toReturn.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        toReturn.setWrapText(true);
        break;

    case CELL:

        toReturn.setAlignment(HorizontalAlignment.LEFT);
        toReturn.setWrapText(true);
        toReturn.setBorderRight(BORDER_THIN);
        toReturn.setRightBorderColor(GREY_25_PERCENT);
        toReturn.setBorderLeft(BORDER_THIN);
        toReturn.setLeftBorderColor(GREY_25_PERCENT);
        toReturn.setBorderTop(BORDER_THIN);
        toReturn.setTopBorderColor(GREY_25_PERCENT);
        toReturn.setBorderBottom(BORDER_THIN);
        toReturn.setBottomBorderColor(GREY_25_PERCENT);
        break;

    case NON_WRAPPING:
        toReturn.setAlignment(HorizontalAlignment.LEFT);
        toReturn.setWrapText(false);
        toReturn.setBorderRight(BORDER_THIN);
        toReturn.setRightBorderColor(GREY_25_PERCENT);
        toReturn.setBorderLeft(BORDER_THIN);
        toReturn.setLeftBorderColor(GREY_25_PERCENT);
        toReturn.setBorderTop(BORDER_THIN);
        toReturn.setTopBorderColor(GREY_25_PERCENT);
        toReturn.setBorderBottom(BORDER_THIN);
        toReturn.setBottomBorderColor(GREY_25_PERCENT);
        break;

    case FORMULA:
        toReturn.setAlignment(HorizontalAlignment.CENTER);
        toReturn.setVerticalAlignment(VerticalAlignment.CENTER);
        toReturn.setFillForegroundColor(GREY_25_PERCENT);
        toReturn.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        toReturn.setDataFormat(workbook.createDataFormat().getFormat("0.00"));
        break;

    case ALT_FORMULA:
        toReturn.setAlignment(HorizontalAlignment.CENTER);
        toReturn.setVerticalAlignment(VerticalAlignment.CENTER);
        toReturn.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
        toReturn.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        toReturn.setDataFormat(workbook.createDataFormat().getFormat("0.00"));
        break;

    default:
        throw new IllegalArgumentException(
                "Style [" + el.name() + "] was not defined. " + "Blame the programmer.");
    }

    // All done.
    toReturn.setFont(theFont);
    return toReturn;
}

---

From source file:servlet.exportScoreSheet.java

private static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle style;
    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 18);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();//from  w  w w  . j  a  v  a 2 s  .  c o m
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFont(titleFont);
    styles.put("title", style);

    Font monthFont = wb.createFont();
    monthFont.setFontHeightInPoints((short) 11);
    monthFont.setColor(IndexedColors.WHITE.getIndex());
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(monthFont);
    style.setWrapText(true);
    styles.put("header", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setWrapText(true);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    styles.put("cell", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("formula", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("formula_2", style);

    return styles;
}

---

From source file:sql.fredy.sqltools.XLSExport.java

License:Open Source License

/**
 * Create the XLS-File named fileName//from  www  . j  av  a  2  s  .c om
 *
 * @param fileName is the Name (incl. Path) of the XLS-file to create
 *
 *
 */
public int createXLS(String fileName) {

    // I need to have a query to process
    if ((getQuery() == null) && (getPstmt() == null)) {
        logger.log(Level.WARNING, "Need to have a query to process");
        return 0;
    }

    // I also need to have a file to write into
    if (fileName == null) {
        logger.log(Level.WARNING, "Need to know where to write into");
        return 0;
    }
    fileName = fixFileName(fileName);
    checkXlsx(fileName);

    // I need to have a connection to the RDBMS
    if (getCon() == null) {
        logger.log(Level.WARNING, "Need to have a connection to process");
        return 0;
    }

    //Statement stmt = null;
    ResultSet resultSet = null;
    ResultSetMetaData rsmd = null;
    try {

        // first we have to create the Statement
        if (getPstmt() == null) {
            pstmt = getCon().prepareStatement(getQuery());
        }

        //stmt = getCon().createStatement();
    } catch (SQLException sqle1) {
        setException(sqle1);
        logger.log(Level.WARNING, "Can not create Statement. Message: " + sqle1.getMessage().toString());
        return 0;
    }

    logger.log(Level.FINE, "FileName: " + fileName);
    logger.log(Level.FINE, "Query   : " + getQuery());

    logger.log(Level.FINE, "Starting export...");

    // create an empty sheet
    Workbook wb;
    Sheet sheet;
    Sheet sqlsheet;
    CreationHelper createHelper = null;
    //XSSFSheet xsheet; 
    //HSSFSheet sheet;

    if (isXlsx()) {
        wb = new SXSSFWorkbook();
        createHelper = wb.getCreationHelper();
    } else {
        wb = new HSSFWorkbook();
        createHelper = wb.getCreationHelper();
    }
    sheet = wb.createSheet("Data Export");

    // create a second sheet just containing the SQL Statement
    sqlsheet = wb.createSheet("SQL Statement");
    Row sqlrow = sqlsheet.createRow(0);
    Cell sqltext = sqlrow.createCell(0);
    try {
        if (getQuery() != null) {
            sqltext.setCellValue(getQuery());
        } else {
            sqltext.setCellValue(pstmt.toString());
        }
    } catch (Exception lex) {

    }
    CellStyle style = wb.createCellStyle();
    style.setWrapText(true);

    sqltext.setCellStyle(style);

    Row r = null;

    int row = 0; // row    number
    int col = 0; // column number
    int columnCount = 0;

    try {
        //resultSet = stmt.executeQuery(getQuery());
        resultSet = pstmt.executeQuery();
        logger.log(Level.FINE, "query executed");
    } catch (SQLException sqle2) {
        setException(sqle2);
        logger.log(Level.WARNING, "Can not execute query. Message: " + sqle2.getMessage().toString());
        return 0;
    }

    // create Header in XLS-file
    ArrayList<String> head = new ArrayList();
    try {
        rsmd = resultSet.getMetaData();
        logger.log(Level.FINE, "Got MetaData of the resultset");

        columnCount = rsmd.getColumnCount();
        logger.log(Level.FINE, Integer.toString(columnCount) + " Columns in this resultset");

        r = sheet.createRow(row); // titlerow

        if ((!isXlsx()) && (columnCount > 255)) {
            columnCount = 255;
        }

        for (int i = 0; i < columnCount; i++) {

            // we create the cell
            Cell cell = r.createCell(col);

            // set the value of the cell
            cell.setCellValue(rsmd.getColumnName(i + 1));
            head.add(rsmd.getColumnName(i + 1));

            // then we align center
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

            // now we make it bold
            //HSSFFont f = wb.createFont();
            Font headerFont = wb.createFont();
            headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
            cellStyle.setFont(headerFont);

            //cellStyle.setFont(f);
            // adapt this font to the cell
            cell.setCellStyle(cellStyle);

            col++;
        }
    } catch (SQLException sqle3) {
        setException(sqle3);
        logger.log(Level.WARNING, "Can not create XLS-Header. Message: " + sqle3.getMessage().toString());
        return 0;
    }

    // looping the resultSet
    int wbCounter = 0;
    try {
        while (resultSet.next()) {

            // this is the next row
            col = 0; // put column counter back to 0 to start at the next row
            row++; // next row

            // create a new sheet if more then 60'000 Rows and xls file
            if ((!isXlsx()) && (row % 65530 == 0)) {
                wbCounter++;
                row = 0;

                sheet = wb.createSheet("Data Export " + Integer.toString(wbCounter));
                logger.log(Level.INFO, "created a further page because of a huge amount of data");

                // create the head
                r = sheet.createRow(row); // titlerow
                for (int i = 0; i < head.size(); i++) {

                    // we create the cell
                    Cell cell = r.createCell(col);

                    // set the value of the cell
                    cell.setCellValue((String) head.get(i));

                    // then we align center
                    CellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

                    // now we make it bold
                    //HSSFFont f = wb.createFont();
                    Font headerFont = wb.createFont();
                    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
                    cellStyle.setFont(headerFont);

                    //cellStyle.setFont(f);
                    // adapt this font to the cell
                    cell.setCellStyle(cellStyle);

                    col++;
                }

                row++;
            }

            try {
                r = sheet.createRow(row);
            } catch (Exception e) {
                logger.log(Level.WARNING, "Error while creating row number " + row + " " + e.getMessage());

                wbCounter++;
                row = 0;

                sheet = wb.createSheet("Data Export " + Integer.toString(wbCounter));
                logger.log(Level.WARNING, "created a further page in the hope it helps...");

                // create the head
                r = sheet.createRow(row); // titlerow
                for (int i = 0; i < head.size(); i++) {

                    // we create the cell
                    Cell cell = r.createCell(col);

                    // set the value of the cell
                    cell.setCellValue((String) head.get(i));

                    // then we align center
                    CellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

                    // now we make it bold
                    //HSSFFont f = wb.createFont();
                    Font headerFont = wb.createFont();
                    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
                    cellStyle.setFont(headerFont);

                    //cellStyle.setFont(f);
                    // adapt this font to the cell
                    cell.setCellStyle(cellStyle);

                    col++;
                }

                row++;

            }

            col = 0; // put column counter back to 0 to start at the next row
            String previousMessage = "";
            for (int i = 0; i < columnCount; i++) {
                try {
                    // depending on the type, create the cell
                    switch (rsmd.getColumnType(i + 1)) {
                    case java.sql.Types.INTEGER:
                        r.createCell(col).setCellValue(resultSet.getInt(i + 1));
                        break;
                    case java.sql.Types.FLOAT:
                        r.createCell(col).setCellValue(resultSet.getFloat(i + 1));
                        break;
                    case java.sql.Types.DOUBLE:
                        r.createCell(col).setCellValue(resultSet.getDouble(i + 1));
                        break;
                    case java.sql.Types.DECIMAL:
                        r.createCell(col).setCellValue(resultSet.getFloat(i + 1));
                        break;
                    case java.sql.Types.NUMERIC:
                        r.createCell(col).setCellValue(resultSet.getFloat(i + 1));
                        break;
                    case java.sql.Types.BIGINT:
                        r.createCell(col).setCellValue(resultSet.getInt(i + 1));
                        break;
                    case java.sql.Types.TINYINT:
                        r.createCell(col).setCellValue(resultSet.getInt(i + 1));
                        break;
                    case java.sql.Types.SMALLINT:
                        r.createCell(col).setCellValue(resultSet.getInt(i + 1));
                        break;

                    case java.sql.Types.DATE:
                        // first we get the date
                        java.sql.Date dat = resultSet.getDate(i + 1);
                        java.util.Date date = new java.util.Date(dat.getTime());
                        r.createCell(col).setCellValue(date);
                        break;

                    case java.sql.Types.TIMESTAMP:
                        // first we get the date
                        java.sql.Timestamp ts = resultSet.getTimestamp(i + 1);

                        Cell c = r.createCell(col);
                        try {
                            c.setCellValue(ts);
                            // r.createCell(col).setCellValue(ts);

                            // Date Format
                            CellStyle cellStyle = wb.createCellStyle();
                            cellStyle.setDataFormat(
                                    createHelper.createDataFormat().getFormat("yyyy/mm/dd hh:mm:ss"));
                            c.setCellStyle(cellStyle);
                        } catch (Exception e) {
                            c.setCellValue(" ");
                        }
                        break;

                    case java.sql.Types.TIME:
                        // first we get the date
                        java.sql.Time time = resultSet.getTime(i + 1);
                        r.createCell(col).setCellValue(time);
                        break;

                    case java.sql.Types.BIT:
                        boolean b1 = resultSet.getBoolean(i + 1);
                        r.createCell(col).setCellValue(b1);
                        break;
                    case java.sql.Types.BOOLEAN:
                        boolean b2 = resultSet.getBoolean(i + 1);
                        r.createCell(col).setCellValue(b2);
                        break;
                    case java.sql.Types.CHAR:
                        r.createCell(col).setCellValue(resultSet.getString(i + 1));
                        break;
                    case java.sql.Types.NVARCHAR:
                        r.createCell(col).setCellValue(resultSet.getString(i + 1));
                        break;

                    case java.sql.Types.VARCHAR:
                        try {
                            r.createCell(col).setCellValue(resultSet.getString(i + 1));
                        } catch (Exception e) {
                            r.createCell(col).setCellValue(" ");
                            logger.log(Level.WARNING,
                                    "Exception while writing column {0} row {3} type: {1} Message: {2}",
                                    new Object[] { col, rsmd.getColumnType(i + 1), e.getMessage(), row });
                        }
                        break;
                    default:
                        r.createCell(col).setCellValue(resultSet.getString(i + 1));
                        break;
                    }
                } catch (Exception e) {
                    //e.printStackTrace();
                    if (resultSet.wasNull()) {
                        r.createCell(col).setCellValue(" ");
                    } else {
                        logger.log(Level.WARNING,
                                "Unhandled type at column {0}, row {3} type: {1}. Filling up with blank {2}",
                                new Object[] { col, rsmd.getColumnType(i + 1), e.getMessage(), row });
                        r.createCell(col).setCellValue(" ");
                    }
                }
                col++;
            }
        }
        //pstmt.close();
    } catch (SQLException sqle3) {
        setException(sqle3);
        logger.log(Level.WARNING,
                "Exception while writing data into sheet. Message: " + sqle3.getMessage().toString());
    }

    try {

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream(fileName);
        wb.write(fileOut);
        fileOut.close();

        logger.log(Level.INFO, "File created");
        logger.log(Level.INFO, "Wrote: {0} lines into XLS-File", Integer.toString(row));

    } catch (Exception e) {
        logger.log(Level.WARNING, "Exception while writing xls-File: " + e.getMessage().toString());
    }
    return row;

}

---

From source file:stroom.dashboard.server.download.ExcelTarget.java

License:Apache License

private void dateTime(final SXSSFWorkbook wb, final Cell cell, final Object value,
        final FormatSettings settings) {
    if (value instanceof Double) {
        final long ms = ((Double) value).longValue();

        final Date date = new Date(ms);
        cell.setCellValue(date);/*from   www.ja  va2s  . c  om*/
        cell.setCellType(Cell.CELL_TYPE_NUMERIC);

        String pattern = "dd/mm/yyyy hh:mm:ss";

        if (settings != null && settings instanceof DateTimeFormatSettings) {
            final DateTimeFormatSettings dateTimeFormatSettings = (DateTimeFormatSettings) settings;
            if (dateTimeFormatSettings.getPattern() != null
                    && dateTimeFormatSettings.getPattern().trim().length() > 0) {
                pattern = dateTimeFormatSettings.getPattern();
                pattern = pattern.replaceAll("'", "");
                pattern = pattern.replaceAll("\\.SSS", "");
            }
        }

        final DataFormat df = wb.createDataFormat();
        final CellStyle cs = wb.createCellStyle();
        cs.setDataFormat(df.getFormat(pattern));
        cell.setCellStyle(cs);

    } else {
        cell.setCellValue(getText(value));
    }
}

---

From source file:stroom.dashboard.server.download.ExcelTarget.java

License:Apache License

private void number(final SXSSFWorkbook wb, final Cell cell, final Object value,
        final FormatSettings settings) {
    if (value instanceof Double) {
        final double dbl = ((Double) value).doubleValue();

        cell.setCellValue(dbl);/*from   w  w w.  ja  v  a  2s  . c o m*/
        cell.setCellType(Cell.CELL_TYPE_NUMERIC);

        if (settings != null && settings instanceof NumberFormatSettings) {
            final NumberFormatSettings numberFormatSettings = (NumberFormatSettings) settings;
            final StringBuilder sb = new StringBuilder();

            if (Boolean.TRUE.equals(numberFormatSettings.getUseSeparator())) {
                sb.append("#,##0");
            } else {
                sb.append("#");
            }
            if (numberFormatSettings.getDecimalPlaces() != null
                    && numberFormatSettings.getDecimalPlaces() > 0) {
                sb.append(".");
                for (int i = 0; i < numberFormatSettings.getDecimalPlaces(); i++) {
                    sb.append("0");
                }
            }

            final String pattern = sb.toString();

            final DataFormat df = wb.createDataFormat();
            final CellStyle cs = wb.createCellStyle();
            cs.setDataFormat(df.getFormat(pattern));
            cell.setCellStyle(cs);
        }
    } else {
        cell.setCellValue(getText(value));
    }
}

---

From source file:summary.GenotypeSummary.java

License:LGPL

public void writeToWorkbook(Workbook wb) {
    Sheet sheet = getSheet(wb);//from w w w.  ja  v a  2  s .  co  m

    Row header = sheet.createRow(0);
    header.createCell(0).setCellValue("Metabolizer Group based on Genotype Only");
    header.createCell(1).setCellValue("Weak");
    header.createCell(2).setCellValue("Potent");
    header.createCell(3).setCellValue("Count");

    int rowNum = 1;
    for (String key : countMap.keySet()) {
        String[] fields = key.split("\\|");
        Row data = sheet.createRow(rowNum);
        data.createCell(0).setCellValue(fields[0]);
        data.createCell(1).setCellValue(fields[1]);
        data.createCell(2).setCellValue(fields[2]);
        data.createCell(3).setCellValue(countMap.get(key));

        rowNum++;
    }

    // Tumor source table
    Row row = sheet.createRow(++rowNum);
    row.createCell(0).setCellValue("*4 Status by Sample Source");
    row = sheet.createRow(++rowNum);
    row.createCell(0).setCellValue("Source");
    row.createCell(1).setCellValue("Count");
    row.createCell(2).setCellValue("*4 Homozygous");
    row.createCell(3).setCellValue("*4 Heterozygous");
    row.createCell(4).setCellValue("Non-*4");

    for (Subject.SampleSource source : Subject.SampleSource.values()) {
        row = sheet.createRow(++rowNum);
        row.createCell(0).setCellValue(source.toString());
        row.createCell(1).setCellValue(sourceMap.get(source)[fourTotal]);
        row.createCell(2).setCellValue(sourceMap.get(source)[fourHomo]);
        row.createCell(3).setCellValue(sourceMap.get(source)[fourHeto]);
        row.createCell(4).setCellValue(sourceMap.get(source)[fourNon]);
    }

    rowNum++;
    row = sheet.createRow(++rowNum);
    row.createCell(0).setCellValue("Sample Source by Site");
    row = sheet.createRow(++rowNum);
    row.createCell(0).setCellValue("Site");

    int colMarker = 0;
    for (Subject.SampleSource source : Subject.SampleSource.values()) {
        row.createCell(colMarker * 2 + 1).setCellValue(source.name() + " N");
        row.createCell(colMarker * 2 + 2).setCellValue(source.name() + " %");
        colMarker++;
    }

    int[] totals = new int[] { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
    CellStyle pctStyle = sheet.getWorkbook().createCellStyle();
    DataFormat format = sheet.getWorkbook().createDataFormat();
    pctStyle.setDataFormat(format.getFormat("0.0%"));

    for (Integer i : tumorFreqMap.keySet()) {
        row = sheet.createRow(++rowNum);
        Integer siteTotal = tumorFreqMap.get(i)[Subject.SampleSource.TUMOR_FFP.ordinal()]
                + tumorFreqMap.get(i)[Subject.SampleSource.TUMOR_FROZEN.ordinal()]
                + tumorFreqMap.get(i)[Subject.SampleSource.BLOOD.ordinal()]
                + tumorFreqMap.get(i)[Subject.SampleSource.BUCCAL.ordinal()]
                + tumorFreqMap.get(i)[Subject.SampleSource.NORMAL_PARAFFIN.ordinal()]
                + tumorFreqMap.get(i)[Subject.SampleSource.UNKNOWN.ordinal()];

        Cell cell;
        row.createCell(0).setCellValue(i + 1);

        colMarker = 0;
        for (Subject.SampleSource source : Subject.SampleSource.values()) {
            Integer total = tumorFreqMap.get(i)[source.ordinal()];
            Float pct = (float) tumorFreqMap.get(i)[source.ordinal()] / (float) siteTotal;

            row.createCell(colMarker * 2 + 1).setCellValue(total);

            cell = row.createCell(colMarker * 2 + 2);
            cell.setCellValue(pct);
            cell.setCellStyle(pctStyle);

            totals[source.ordinal()] += total;
            colMarker++;
        }
    }
    row = sheet.createRow(++rowNum);
    int projectTotal = totals[Subject.SampleSource.TUMOR_FFP.ordinal()]
            + totals[Subject.SampleSource.TUMOR_FROZEN.ordinal()]
            + totals[Subject.SampleSource.NORMAL_PARAFFIN.ordinal()]
            + totals[Subject.SampleSource.BLOOD.ordinal()] + totals[Subject.SampleSource.BUCCAL.ordinal()]
            + totals[Subject.SampleSource.UNKNOWN.ordinal()];

    colMarker = 0;
    for (Subject.SampleSource source : Subject.SampleSource.values()) {
        row.createCell(colMarker * 2 + 1).setCellValue(totals[source.ordinal()]);

        Cell cell = row.createCell(colMarker * 2 + 2);
        cell.setCellValue((float) totals[source.ordinal()] / (float) projectTotal);
        cell.setCellStyle(pctStyle);
        colMarker++;
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• »