Example usage for org.apache.poi.xssf.usermodel XSSFSheet setColumnWidth

List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet setColumnWidth

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFSheet
  6. setColumnWidth

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFSheet setColumnWidth.

Prototype

@Override
public void setColumnWidth(int columnIndex, int width)

Source Link

Document

Set the width (in units of 1/256th of a character width)

The maximum column width for an individual cell is 255 characters.

Usage

From source file:CE.CyberedgeInterface.java

public void toExcel() {

    try {/*from   w w w . j ava2s.  c om*/
        con = DbConnection.getConnection();
        String query = "Select * from Candidates_record";
        ps = con.prepareStatement(query);
        rs = ps.executeQuery();

        XSSFWorkbook w = new XSSFWorkbook();
        XSSFSheet ws = w.createSheet("Candidates Record");
        XSSFRow header = ws.createRow(0);
        header.createCell(0).setCellValue("ID");
        header.createCell(1).setCellValue("Name");
        header.createCell(2).setCellValue("Position");
        header.createCell(3).setCellValue("Client");
        header.createCell(4).setCellValue("Location");
        header.createCell(5).setCellValue("Contact");
        header.createCell(6).setCellValue("Email");
        header.createCell(7).setCellValue("Experience");
        header.createCell(8).setCellValue("Remark");

        ws.setColumnWidth(1, 256 * 25);
        ws.setColumnWidth(2, 256 * 25);
        ws.setColumnWidth(3, 256 * 25);
        ws.setColumnWidth(4, 256 * 25);
        ws.setColumnWidth(5, 256 * 25);
        ws.setColumnWidth(6, 256 * 25);
        ws.setColumnWidth(7, 256 * 25);
        ws.setColumnWidth(8, 256 * 25);
        ws.setColumnWidth(9, 256 * 25);
        int index = 1;
        while (rs.next()) {
            XSSFRow row = ws.createRow(index);
            row.createCell(0).setCellValue(rs.getInt("Candidate_id"));
            row.createCell(1).setCellValue(rs.getString("Name"));
            row.createCell(2).setCellValue(rs.getString("Position"));
            row.createCell(3).setCellValue(rs.getString("Client"));
            row.createCell(4).setCellValue(rs.getString("Location"));
            row.createCell(5).setCellValue(rs.getString("Contact"));
            row.createCell(6).setCellValue(rs.getString("Email"));
            row.createCell(7).setCellValue(rs.getInt("Experience"));
            row.createCell(8).setCellValue(rs.getString("Remark"));
            index++;

        }
        String file = "C:\\..\\..\\..\\..\\..\\Cyberedge\\CandidateDetails.xlsx";
        FileOutputStream fileout = new FileOutputStream(file);
        w.write(fileout);

        fileout.close();

        JOptionPane.showMessageDialog(null, "File Saved");

        ps.close();
        rs.close();
    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, e);
    }

}

From source file:com.bc.util.XlsxExporter.java

public static void WriteInvoiceToFile(File output, CustomerOrder order, ArrayList<CustomerOrderItem> items) {
    try {/*from  w ww  .  j a  v a 2  s  .  c o  m*/
        if (output.exists()) {
            log.info(output.getName() + " exists. Deleting");
            output.delete();
            log.info("Deleted " + output.getName());
        }

        log.info("Creating xlsx file...");

        FileOutputStream fos = new FileOutputStream(output);
        XSSFWorkbook workBook = new XSSFWorkbook();
        XSSFSheet sheet = workBook.createSheet("Order");
        CellStyle style = workBook.createCellStyle();
        style.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(CellStyle.ALIGN_FILL);
        Font font = workBook.createFont();
        font.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(font);

        String[] columnHeaders = { "Invoice", "Salesman", "Customer Name", "Customer Code", "PO", "Ship Date",
                "Post Date", "ISBN", "ISBN13", "Title", "List Price", "Price", "Quantity", "Shipped",
                "Discount", "Extended Price" };

        log.info("Creating header row & columns");

        Row row = sheet.createRow(0);

        for (int i = 0; i < columnHeaders.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(columnHeaders[i]);
            cell.setCellStyle(style);
            sheet.setColumnWidth(i, 4500);
        }

        sheet.setColumnWidth(9, 13500);

        log.info("Writing " + items.size() + " records");

        XSSFDataFormat decimalFormat = workBook.createDataFormat();
        CellStyle dstyle = workBook.createCellStyle();
        dstyle.setDataFormat(decimalFormat.getFormat("0.00"));

        int i = 1;
        for (CustomerOrderItem orderItem : items) {
            Row drow = sheet.createRow(i++);
            Hibernate.initialize(order.getCustomerOrderItems());

            String strValue;
            Float floatValue;
            Integer intVal;

            Cell cInvoice = drow.createCell(0);
            strValue = order.getInvoiceNumber();
            if (strValue == null)
                strValue = "";
            cInvoice.setCellValue(order.getInvoiceNumber());

            Cell cSalesman = drow.createCell(1);
            strValue = order.getSalesman();
            if (strValue == null)
                strValue = "";
            cSalesman.setCellValue(strValue);

            Cell cCustomerName = drow.createCell(2);
            strValue = order.getCustomer().getCompanyName();
            if (strValue == null)
                strValue = "";
            cCustomerName.setCellValue(strValue);

            Cell cCustomerCode = drow.createCell(3);
            strValue = order.getCustomerCode();
            if (strValue == null)
                strValue = "";
            cCustomerCode.setCellValue(strValue);

            Cell cPo = drow.createCell(4);
            strValue = order.getPoNumber();
            if (strValue == null)
                strValue = "";
            cPo.setCellValue(strValue);

            Cell cShipDate = drow.createCell(5);
            Date d = order.getShipDate();
            if (d == null)
                cShipDate.setCellValue("");
            else
                cShipDate.setCellValue("" + d.getMonth() + "/" + d.getDay() + "/" + (1900 + d.getYear()));

            Cell cPostDate = drow.createCell(6);
            d = order.getPostDate();
            if (d == null)
                cPostDate.setCellValue("");
            else
                cPostDate.setCellValue("" + d.getMonth() + "/" + d.getDay() + "/" + (1900 + d.getYear()));
            Hibernate.initialize(orderItem.getInventoryItem());
            InventoryItem item = orderItem.getInventoryItem(); //orderItem.getInventoryItem();
            if (item != null) {
                Cell cIsbn = drow.createCell(7);
                strValue = item.getIsbn();
                if (strValue == null)
                    strValue = "";
                cIsbn.setCellValue(strValue);

                Cell cIsbn13 = drow.createCell(8);
                strValue = item.getIsbn13();
                if (strValue == null)
                    strValue = "";
                cIsbn13.setCellValue(strValue);

                Cell cTitle = drow.createCell(9);
                strValue = item.getTitle();
                if (strValue == null)
                    strValue = "";
                cTitle.setCellValue(strValue);

                Cell cListPrice = drow.createCell(10);
                floatValue = item.getListPrice();
                cListPrice.setCellStyle(dstyle);
                if (floatValue == null)
                    floatValue = 0.0f;
                cListPrice.setCellValue(floatValue);

                Cell cPrice = drow.createCell(11);
                floatValue = item.getSellingPrice();
                cPrice.setCellStyle(dstyle);
                if (floatValue == null)
                    floatValue = 0.0f;
                cPrice.setCellValue(floatValue);
            }
            Cell cQuantity = drow.createCell(12);
            intVal = orderItem.getQuantity();
            log.info("Quantity : " + intVal);
            if (intVal == null)
                intVal = 0;
            cQuantity.setCellValue(intVal);

            Cell cShipped = drow.createCell(13);
            intVal = orderItem.getFilled();
            log.info("Shipped QTY : " + intVal);
            if (intVal == null)
                intVal = 0;
            cShipped.setCellValue(intVal);

            Cell cDiscount = drow.createCell(14);
            cDiscount.setCellStyle(dstyle);
            floatValue = orderItem.getDiscount();
            if (floatValue == null)
                floatValue = 0.0f;
            cDiscount.setCellValue(floatValue);

            Cell cExtendedPrice = drow.createCell(15);
            cExtendedPrice.setCellStyle(dstyle);
            BigDecimal dValue = orderItem.getTotalPrice();
            if (dValue == null)
                dValue = BigDecimal.ZERO;
            cExtendedPrice.setCellValue(dValue.doubleValue());

        }

        workBook.write(fos);
        log.info("Finished writing data, closing...");

        fos.close();
        log.info("Completed exporting data to " + output.getAbsolutePath());
    } catch (Exception ex) {
        Logger.getLogger(XlsxExporter.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:com.endro.belajar.controller.InvoiceProdukController.java

private void clickedbuttonExportDialog() {
    dialogExport.getButtonExport().addActionListener(new ActionListener() {
        @Override//from   w w w  . ja v  a  2  s . com
        public void actionPerformed(ActionEvent e) {
            try {
                LocalDate tanggalAwal = dialogExport.getTanggalAwalChooser().getDate().toInstant()
                        .atZone(ZoneId.systemDefault()).toLocalDate();
                LocalDate tanggalAkhir = dialogExport.getTanggalAkhirChooser().getDate().toInstant()
                        .atZone(ZoneId.systemDefault()).toLocalDate();

                List<InvoiceOrder> daftar = invoiceDao.findAllByTanggal(tanggalAwal, tanggalAkhir);
                processConvertExcel(daftar);
            } catch (SQLException | IOException ex) {
                Logger.getLogger(InvoiceProdukController.class.getName()).log(Level.SEVERE, null, ex);
            } catch (NullPointerException ex) {
                JOptionPane.showMessageDialog(dialogExport, "Form tanggal diisi dengan lengkap!");
            } finally {
                dialogExport.dispose();
                dialogExport = null;
            }
        }

        private void processConvertExcel(List<InvoiceOrder> daftarInvoice)
                throws FileNotFoundException, IOException {
            Integer returnVal = dialogExport.getChooserSaveFile().showOpenDialog(dialogExport);

            if (returnVal == dialogExport.getChooserSaveFile().APPROVE_OPTION) {
                XSSFWorkbook workbook = new XSSFWorkbook();
                XSSFSheet sheet = workbook.createSheet("Just Example");

                List<InvoiceOrder> list = daftarInvoice;

                Integer rowTable = 0;
                Integer cellTable = 0;
                CellStyle cellStyleTanggal = workbook.createCellStyle();
                CellStyle cellStyleHeader = workbook.createCellStyle();
                CellStyle cellStyleDouble = workbook.createCellStyle();

                CreationHelper createHelper = workbook.getCreationHelper();
                XSSFFont font = workbook.createFont();

                cellStyleTanggal.setDataFormat(createHelper.createDataFormat().getFormat("dd/mm/yyyy"));
                cellStyleDouble.setDataFormat(
                        createHelper.createDataFormat().getFormat("[$Rp-421]#,##0.0000;-[$Rp-421]#,##0.0000"));
                font.setBold(true);
                cellStyleHeader.setFont(font);
                cellStyleHeader.setWrapText(true);
                //cellStyleHeader.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
                cellStyleHeader.setFillPattern(FillPatternType.DIAMONDS);
                for (InvoiceOrder order : list) {
                    Row row = sheet.createRow(rowTable);

                    if (rowTable == 0) {
                        sheet.setColumnWidth(0, 2000);
                        Cell cellHeader = row.createCell(0);
                        cellHeader.setCellValue("ID");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(1, 5000);
                        cellHeader = row.createCell(1);
                        cellHeader.setCellValue("Nomor Transaksi");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(2, 4000);
                        cellHeader = row.createCell(2);
                        cellHeader.setCellValue("Tanggal");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(3, 6000 * 3);
                        cellHeader = row.createCell(3);
                        cellHeader.setCellValue("Informasi Posting");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(4, 4850);
                        cellHeader = row.createCell(4);
                        cellHeader.setCellValue("Total Sebelum Diskon");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(5, 5000);
                        cellHeader = row.createCell(5);
                        cellHeader.setCellValue("Diskon");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(6, 4500);
                        cellHeader = row.createCell(6);
                        cellHeader.setCellValue("Total Setelah Diskon");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(7, 5000 * 2);
                        cellHeader = row.createCell(7);
                        cellHeader.setCellValue("Alamat Pengiriman");
                        cellHeader.setCellStyle(cellStyleHeader);
                    } else {
                        row.createCell(0).setCellValue((Integer) order.getPk());
                        row.createCell(1).setCellValue((String) order.getNomortransaksi());

                        Cell cellTanggal = row.createCell(2);
                        cellTanggal.setCellValue((Date) order.getTanggal());
                        cellTanggal.setCellStyle(cellStyleTanggal);

                        row.createCell(3).setCellValue((String) order.getInformasiposting());

                        Cell cellDouble = row.createCell(4);
                        cellDouble.setCellValue(order.getTotalbelumdiskon());
                        cellDouble.setCellStyle(cellStyleDouble);

                        cellDouble = row.createCell(5);
                        cellDouble.setCellValue(order.getDiskonfaktur());
                        cellDouble.setCellStyle(cellStyleDouble);

                        cellDouble = row.createCell(6);
                        cellDouble.setCellValue(order.getTotalsetelahdiskon());
                        cellDouble.setCellStyle(cellStyleDouble);

                        row.createCell(7).setCellValue((String) order.getAlamatpengiriman() == null ? "Null"
                                : order.getAlamatpengiriman());
                    }
                    rowTable++;
                }

                File file = dialogExport.getChooserSaveFile().getSelectedFile();

                FileOutputStream outputStream = new FileOutputStream(file + File.separator + "Penjualan.xlsx");
                workbook.write(outputStream);

                int pesan = JOptionPane.showConfirmDialog(dialogExport,
                        "Telah tersimpan di " + file + File.separator
                                + "Penjualan.xlsx \n Apakah anda ingin membuka file tersebut?",
                        "Notification", JOptionPane.OK_CANCEL_OPTION);
                if (pesan == JOptionPane.YES_OPTION) {
                    if ("Linux".equals(System.getProperty("os.name"))) {
                        String runPenjualan = "xdg-open " + file + File.separator + "Penjualan.xlsx";
                        Runtime.getRuntime().exec(runPenjualan);
                    } else if ("Windows".equals(System.getProperty("os.name"))) {
                        String runPenjualan = "excel.exe /r" + file + File.separator + "Penjualan.xlsx";
                        Runtime.getRuntime().exec(runPenjualan);
                    }
                }
            } else {
                dialogExport.getChooserSaveFile().cancelSelection();
            }
        }
    });
}

From source file:com.endro.belajar.controller.MainController.java

private void clickedExport() {
    exportPenjualan.getButtonExport().addActionListener(new ActionListener() {
        @Override//www . jav a2  s  .c o  m
        public void actionPerformed(ActionEvent e) {
            try {
                LocalDate tanggalAwal = exportPenjualan.getTanggalAwalChooser().getDate().toInstant()
                        .atZone(ZoneId.systemDefault()).toLocalDate();
                LocalDate tanggalAkhir = exportPenjualan.getTanggalAkhirChooser().getDate().toInstant()
                        .atZone(ZoneId.systemDefault()).toLocalDate();

                List<InvoiceOrder> daftar = invoiceDao.findAllByTanggal(tanggalAwal, tanggalAkhir);
                processConvertExcel(daftar);
            } catch (SQLException ex) {
                Logger.getLogger(MainController.class.getName()).log(Level.SEVERE, null, ex);
            } catch (NullPointerException ex) {
                JOptionPane.showMessageDialog(exportPenjualan, "Form tanggal diisi dengan lengkap!");
            } catch (IOException ex) {
                Logger.getLogger(MainController.class.getName()).log(Level.SEVERE, null, ex);
            } finally {
                exportPenjualan.dispose();
                exportPenjualan = null;
            }
        }

        private void processConvertExcel(List<InvoiceOrder> daftarInvoice)
                throws FileNotFoundException, IOException {
            Integer returnVal = exportPenjualan.getChooserSaveFile().showOpenDialog(exportPenjualan);

            if (returnVal == exportPenjualan.getChooserSaveFile().APPROVE_OPTION) {
                XSSFWorkbook workbook = new XSSFWorkbook();
                XSSFSheet sheet = workbook.createSheet("Just Example");

                List<InvoiceOrder> list = daftarInvoice;

                Integer rowTable = 0;
                Integer cellTable = 0;
                CellStyle cellStyleTanggal = workbook.createCellStyle();
                CellStyle cellStyleHeader = workbook.createCellStyle();
                CellStyle cellStyleDouble = workbook.createCellStyle();

                CreationHelper createHelper = workbook.getCreationHelper();
                XSSFFont font = workbook.createFont();

                cellStyleTanggal.setDataFormat(createHelper.createDataFormat().getFormat("dd/mm/yyyy"));
                cellStyleDouble.setDataFormat(
                        createHelper.createDataFormat().getFormat("[$Rp-421]#,##0.0000;-[$Rp-421]#,##0.0000"));
                font.setBold(true);
                cellStyleHeader.setFont(font);
                cellStyleHeader.setWrapText(true);
                //cellStyleHeader.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
                cellStyleHeader.setFillPattern(FillPatternType.DIAMONDS);
                for (InvoiceOrder order : list) {
                    Row row = sheet.createRow(rowTable);

                    if (rowTable == 0) {
                        sheet.setColumnWidth(0, 2000);
                        Cell cellHeader = row.createCell(0);
                        cellHeader.setCellValue("ID");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(1, 5000);
                        cellHeader = row.createCell(1);
                        cellHeader.setCellValue("Nomor Transaksi");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(2, 4000);
                        cellHeader = row.createCell(2);
                        cellHeader.setCellValue("Tanggal");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(3, 6000 * 3);
                        cellHeader = row.createCell(3);
                        cellHeader.setCellValue("Informasi Posting");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(4, 4850);
                        cellHeader = row.createCell(4);
                        cellHeader.setCellValue("Total Sebelum Diskon");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(5, 5000);
                        cellHeader = row.createCell(5);
                        cellHeader.setCellValue("Diskon");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(6, 4500);
                        cellHeader = row.createCell(6);
                        cellHeader.setCellValue("Total Setelah Diskon");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(7, 5000 * 2);
                        cellHeader = row.createCell(7);
                        cellHeader.setCellValue("Alamat Pengiriman");
                        cellHeader.setCellStyle(cellStyleHeader);
                    } else {
                        row.createCell(0).setCellValue((Integer) order.getPk());
                        row.createCell(1).setCellValue((String) order.getNomortransaksi());

                        Cell cellTanggal = row.createCell(2);
                        cellTanggal.setCellValue((Date) order.getTanggal());
                        cellTanggal.setCellStyle(cellStyleTanggal);

                        row.createCell(3).setCellValue((String) order.getInformasiposting());

                        Cell cellDouble = row.createCell(4);
                        cellDouble.setCellValue(order.getTotalbelumdiskon());
                        cellDouble.setCellStyle(cellStyleDouble);

                        cellDouble = row.createCell(5);
                        cellDouble.setCellValue(order.getDiskonfaktur());
                        cellDouble.setCellStyle(cellStyleDouble);

                        cellDouble = row.createCell(6);
                        cellDouble.setCellValue(order.getTotalsetelahdiskon());
                        cellDouble.setCellStyle(cellStyleDouble);

                        row.createCell(7).setCellValue((String) order.getAlamatpengiriman() == null ? "Null"
                                : order.getAlamatpengiriman());
                    }
                    rowTable++;
                }

                File file = exportPenjualan.getChooserSaveFile().getSelectedFile();

                FileOutputStream outputStream = new FileOutputStream(file + File.separator + "Penjualan.xlsx");
                workbook.write(outputStream);

                int pesan = JOptionPane.showConfirmDialog(exportPenjualan,
                        "Telah tersimpan di " + file + File.separator
                                + "Penjualan.xlsx \n Apakah anda ingin membuka file tersebut?",
                        "Notification", JOptionPane.OK_CANCEL_OPTION);
                if (pesan == JOptionPane.YES_OPTION) {
                    if ("Linux".equals(System.getProperty("os.name"))) {
                        String runPenjualan = "xdg-open " + file + File.separator + "Penjualan.xlsx";
                        Runtime.getRuntime().exec(runPenjualan);
                    } else if ("Windows".equals(System.getProperty("os.name"))) {
                        String runPenjualan = "excel.exe /r" + file + File.separator + "Penjualan.xlsx";
                        Runtime.getRuntime().exec(runPenjualan);
                    }
                }
            } else {
                exportPenjualan.getChooserSaveFile().cancelSelection();
            }
        }
    });
}

From source file:com.netsteadfast.greenstep.bsc.command.KpiPeriodTrendsExcelCommand.java

License:Apache License

@SuppressWarnings("unchecked")
private void putTables(XSSFWorkbook wb, XSSFSheet sh, Context context) throws Exception {

    XSSFCellStyle cellHeadStyle = wb.createCellStyle();
    cellHeadStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#f5f5f5")));
    cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    cellHeadStyle.setBorderBottom(BorderStyle.THIN);
    cellHeadStyle.setBorderTop(BorderStyle.THIN);
    cellHeadStyle.setBorderRight(BorderStyle.THIN);
    cellHeadStyle.setBorderLeft(BorderStyle.THIN);
    XSSFFont cellHeadFont = wb.createFont();
    cellHeadFont.setBold(true);//from  ww  w. ja  v a 2s  .  c o m
    cellHeadStyle.setFont(cellHeadFont);

    sh.setColumnWidth(0, 12000);

    int row = 0;

    Row nowRow = sh.createRow(row);
    Cell cell1 = nowRow.createCell(0);
    cell1.setCellStyle(cellHeadStyle);
    cell1.setCellValue("KPI");
    Cell cell2 = nowRow.createCell(1);
    cell2.setCellStyle(cellHeadStyle);
    cell2.setCellValue("Maximum");
    Cell cell3 = nowRow.createCell(2);
    cell3.setCellStyle(cellHeadStyle);
    cell3.setCellValue("Target");
    Cell cell4 = nowRow.createCell(3);
    cell4.setCellStyle(cellHeadStyle);
    cell4.setCellValue("Minimum");
    Cell cell5 = nowRow.createCell(4);
    cell5.setCellStyle(cellHeadStyle);
    cell5.setCellValue("Current score");
    Cell cell6 = nowRow.createCell(5);
    cell6.setCellStyle(cellHeadStyle);
    cell6.setCellValue("Previous score");
    Cell cell7 = nowRow.createCell(6);
    cell7.setCellStyle(cellHeadStyle);
    cell7.setCellValue("Change(%)");

    row++;

    List<PeriodTrendsData<KpiVO>> periodDatas = (List<PeriodTrendsData<KpiVO>>) context.get("periodDatas");
    for (PeriodTrendsData<KpiVO> periodData : periodDatas) {
        nowRow = sh.createRow(row);

        cell1 = nowRow.createCell(0);
        cell1.setCellValue(periodData.getCurrent().getName());
        cell2 = nowRow.createCell(1);
        cell2.setCellValue(periodData.getCurrent().getMax());
        cell3 = nowRow.createCell(2);
        cell3.setCellValue(periodData.getCurrent().getTarget());
        cell4 = nowRow.createCell(3);
        cell4.setCellValue(periodData.getCurrent().getMin());
        cell5 = nowRow.createCell(4);
        cell5.setCellValue(BscReportSupportUtils.parse2(periodData.getCurrent().getScore()));
        cell6 = nowRow.createCell(5);
        cell6.setCellValue(BscReportSupportUtils.parse2(periodData.getPrevious().getScore()));
        cell7 = nowRow.createCell(6);
        cell7.setCellValue(BscReportSupportUtils.parse2(periodData.getChange()));

        row++;
    }

    nowRow = sh.createRow(row);

    cell1 = nowRow.createCell(0);
    cell1.setCellValue("Current period: " + (String) context.get("currentPeriodDateRange")
            + " , Previous period: " + (String) context.get("previousPeriodDateRange"));

}

From source file:com.netsteadfast.greenstep.bsc.command.KpiReportExcelCommand.java

License:Apache License

private int createHead(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision) throws Exception {
    Row headRow = sh.createRow(row);// w ww .  java 2  s.  c om
    headRow.setHeight((short) 700);

    int cell = 0;

    XSSFColor bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor(vision.getBgColor()));
    XSSFColor fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor(vision.getFontColor()));

    XSSFCellStyle cellHeadStyle = wb.createCellStyle();
    cellHeadStyle.setFillForegroundColor(bgColor);
    cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    XSSFFont cellHeadFont = wb.createFont();
    cellHeadFont.setBold(true);
    cellHeadFont.setColor(fnColor);
    cellHeadStyle.setFont(cellHeadFont);
    cellHeadStyle.setBorderBottom(BorderStyle.THIN);
    cellHeadStyle.setBorderTop(BorderStyle.THIN);
    cellHeadStyle.setBorderRight(BorderStyle.THIN);
    cellHeadStyle.setBorderLeft(BorderStyle.THIN);
    cellHeadStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellHeadStyle.setAlignment(HorizontalAlignment.CENTER);
    cellHeadStyle.setWrapText(true);

    int cols = 12;
    for (int i = 0; i < cols; i++) {
        sh.setColumnWidth(i, 4000);
        Cell headCell1 = headRow.createCell(cell++);
        headCell1.setCellValue(
                vision.getTitle() + "\nscore: " + BscReportSupportUtils.parse2(vision.getScore()));
        headCell1.setCellStyle(cellHeadStyle);
    }

    sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1));

    // ------------------------------------------------------------------------
    bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor(BscReportPropertyUtils.getBackgroundColor()));
    fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor(BscReportPropertyUtils.getFontColor()));

    cellHeadStyle = wb.createCellStyle();
    cellHeadStyle.setFillForegroundColor(bgColor);
    cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    cellHeadFont = wb.createFont();
    cellHeadFont.setBold(true);
    cellHeadFont.setColor(fnColor);
    cellHeadStyle.setFont(cellHeadFont);
    cellHeadStyle.setBorderBottom(BorderStyle.THIN);
    cellHeadStyle.setBorderTop(BorderStyle.THIN);
    cellHeadStyle.setBorderRight(BorderStyle.THIN);
    cellHeadStyle.setBorderLeft(BorderStyle.THIN);
    cellHeadStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellHeadStyle.setAlignment(HorizontalAlignment.CENTER);
    cellHeadStyle.setWrapText(true);

    row++;
    headRow = sh.createRow(row);
    cell = 0;
    int titleCols = 4;
    for (int i = 0; i < titleCols; i++) {
        Cell headCell1 = headRow.createCell(cell++);
        headCell1.setCellValue(BscReportPropertyUtils.getPerspectiveTitle());
        headCell1.setCellStyle(cellHeadStyle);
    }
    for (int i = 0; i < titleCols; i++) {
        Cell headCell1 = headRow.createCell(cell++);
        headCell1.setCellValue(BscReportPropertyUtils.getObjectiveTitle());
        headCell1.setCellStyle(cellHeadStyle);
    }
    for (int i = 0; i < titleCols; i++) {
        Cell headCell1 = headRow.createCell(cell++);
        headCell1.setCellValue(BscReportPropertyUtils.getKpiTitle());
        headCell1.setCellStyle(cellHeadStyle);
    }

    sh.addMergedRegion(new CellRangeAddress(row, row, 0, 3));
    sh.addMergedRegion(new CellRangeAddress(row, row, 4, 7));
    sh.addMergedRegion(new CellRangeAddress(row, row, 8, 11));

    // ------------------------------------------------------------------------

    return 2;
}

From source file:com.netsteadfast.greenstep.bsc.command.KpisDashboardExcelCommand.java

License:Apache License

@SuppressWarnings("unchecked")
private int putTables(XSSFWorkbook wb, XSSFSheet sh, Context context) throws Exception {

    XSSFCellStyle cellHeadStyle = wb.createCellStyle();
    cellHeadStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#f5f5f5")));
    cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    cellHeadStyle.setBorderBottom(BorderStyle.THIN);
    cellHeadStyle.setBorderTop(BorderStyle.THIN);
    cellHeadStyle.setBorderRight(BorderStyle.THIN);
    cellHeadStyle.setBorderLeft(BorderStyle.THIN);
    XSSFFont cellHeadFont = wb.createFont();
    cellHeadFont.setBold(true);/*w w  w . j  a  v  a 2s  .  c o m*/
    cellHeadStyle.setFont(cellHeadFont);

    sh.setColumnWidth(0, 12000);

    int left = 0;
    int row = 0;
    List<Map<String, Object>> chartDatas = (List<Map<String, Object>>) context.get("chartDatas");
    for (Map<String, Object> data : chartDatas) {
        Row nowRow = sh.createRow(row);
        Map<String, Object> nodeData = (Map<String, Object>) ((List<Object>) data.get("datas")).get(0);

        if (row == 0) {
            Cell cell1 = nowRow.createCell(0);
            cell1.setCellStyle(cellHeadStyle);
            cell1.setCellValue("KPI");
            Cell cell2 = nowRow.createCell(1);
            cell2.setCellStyle(cellHeadStyle);
            cell2.setCellValue("Maximum");
            Cell cell3 = nowRow.createCell(2);
            cell3.setCellStyle(cellHeadStyle);
            cell3.setCellValue("Target");
            Cell cell4 = nowRow.createCell(3);
            cell4.setCellStyle(cellHeadStyle);
            cell4.setCellValue("Minimum");
            Cell cell5 = nowRow.createCell(4);
            cell5.setCellStyle(cellHeadStyle);
            cell5.setCellValue("Score");

            List<Map<String, Object>> dateRangeScores = (List<Map<String, Object>>) nodeData
                    .get("dateRangeScores");
            for (Map<String, Object> rangeScore : dateRangeScores) {
                Cell cell = nowRow.createCell(5 + left);
                cell.setCellStyle(cellHeadStyle);
                cell.setCellValue(String.valueOf(rangeScore.get("date")));
                left++;
            }

            row++;
        }

        left = 0;
        nowRow = sh.createRow(row);

        XSSFColor bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor((String) nodeData.get("bgColor")));
        XSSFColor fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor((String) nodeData.get("fontColor")));
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFillForegroundColor(bgColor);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        XSSFFont cellFont = wb.createFont();
        cellFont.setBold(false);
        cellFont.setColor(fnColor);
        cellStyle.setFont(cellFont);
        cellStyle.setWrapText(true);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);

        Cell cell1 = nowRow.createCell(0);
        cell1.setCellValue(String.valueOf(nodeData.get("name")));
        Cell cell2 = nowRow.createCell(1);
        cell2.setCellValue(String.valueOf(nodeData.get("max")));
        Cell cell3 = nowRow.createCell(2);
        cell3.setCellValue(String.valueOf(nodeData.get("target")));
        Cell cell4 = nowRow.createCell(3);
        cell4.setCellValue(String.valueOf(nodeData.get("min")));
        Cell cell5 = nowRow.createCell(4);
        cell5.setCellValue(String.valueOf(nodeData.get("score")));
        cell5.setCellStyle(cellStyle);

        List<Map<String, Object>> dateRangeScores = (List<Map<String, Object>>) nodeData.get("dateRangeScores");
        for (Map<String, Object> rangeScore : dateRangeScores) {
            bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor((String) rangeScore.get("bgColor")));
            fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor((String) rangeScore.get("fontColor")));
            cellStyle = wb.createCellStyle();
            cellStyle.setFillForegroundColor(bgColor);
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cellFont = wb.createFont();
            cellFont.setBold(false);
            cellFont.setColor(fnColor);
            cellStyle.setFont(cellFont);
            cellStyle.setWrapText(true);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);

            Cell cell = nowRow.createCell(5 + left);
            cell.setCellStyle(cellHeadStyle);
            cell.setCellValue(String.valueOf(rangeScore.get("score")));
            cell.setCellStyle(cellStyle);

            left++;
        }

        row++;
    }
    return row + 1;
}

From source file:com.netsteadfast.greenstep.bsc.command.OrganizationReportExcelCommand.java

License:Apache License

private int createHead(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision, Context context)
        throws Exception {
    String dateType = (String) context.get("dateType");
    String year = (String) context.get("startYearDate");
    String orgId = (String) context.get("orgId");
    String departmentName = "";
    String dateTypeName = "Year";
    if ("1".equals(dateType)) {
        dateTypeName = "In the first half";
    }/* w w w  . ja  v a2s.c o m*/
    if ("2".equals(dateType)) {
        dateTypeName = "In the second half";
    }
    OrganizationVO organization = new OrganizationVO();
    organization.setOrgId(orgId);
    DefaultResult<OrganizationVO> result = this.organizationService.findByUK(organization);
    if (result.getValue() != null) {
        organization = result.getValue();
        departmentName = organization.getName();
    }

    Row headRow = sh.createRow(row);
    headRow.setHeight((short) 700);

    XSSFColor bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#F2F2F2"));
    XSSFColor fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#000000"));

    XSSFCellStyle cellHeadStyle = wb.createCellStyle();
    cellHeadStyle.setFillForegroundColor(bgColor);
    cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    XSSFFont cellHeadFont = wb.createFont();
    cellHeadFont.setBold(true);
    cellHeadFont.setColor(fnColor);
    cellHeadStyle.setFont(cellHeadFont);
    cellHeadStyle.setBorderBottom(BorderStyle.THIN);
    cellHeadStyle.setBorderTop(BorderStyle.THIN);
    cellHeadStyle.setBorderRight(BorderStyle.THIN);
    cellHeadStyle.setBorderLeft(BorderStyle.THIN);
    cellHeadStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellHeadStyle.setAlignment(HorizontalAlignment.CENTER);
    cellHeadStyle.setWrapText(true);

    int cols = 6;
    for (int i = 0; i < cols; i++) {
        sh.setColumnWidth(i, 6000);
        Cell headCell1 = headRow.createCell(i);
        headCell1.setCellValue("Personal Balance SourceCard");
        headCell1.setCellStyle(cellHeadStyle);
    }
    sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1));

    row++;
    headRow = sh.createRow(row);
    for (int i = 0; i < cols; i++) {
        sh.setColumnWidth(i, 6000);
        Cell headCell1 = headRow.createCell(i);
        headCell1.setCellValue(vision.getTitle());
        headCell1.setCellStyle(cellHeadStyle);
    }
    sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1));

    row++;
    headRow = sh.createRow(row);

    Cell titleCell1 = headRow.createCell(0);
    titleCell1.setCellValue("Department");
    titleCell1.setCellStyle(cellHeadStyle);

    Cell titleCell2 = headRow.createCell(1);
    titleCell2.setCellValue(departmentName);
    titleCell2.setCellStyle(cellHeadStyle);

    Cell titleCell3 = headRow.createCell(2);
    titleCell3.setCellValue(departmentName);
    titleCell3.setCellStyle(cellHeadStyle);

    Cell titleCell4 = headRow.createCell(3);
    titleCell4.setCellValue(departmentName);
    titleCell4.setCellStyle(cellHeadStyle);

    Cell titleCell5 = headRow.createCell(4);
    titleCell5.setCellValue(departmentName);
    titleCell5.setCellStyle(cellHeadStyle);

    Cell titleCell6 = headRow.createCell(5);
    titleCell6.setCellValue(year + " " + dateTypeName);
    titleCell6.setCellStyle(cellHeadStyle);

    sh.addMergedRegion(new CellRangeAddress(row, row, 1, cols - 2));

    row++;
    headRow = sh.createRow(row);

    titleCell1 = headRow.createCell(0);
    titleCell1.setCellValue(BscReportPropertyUtils.getPerspectiveTitle());
    titleCell1.setCellStyle(cellHeadStyle);

    titleCell2 = headRow.createCell(1);
    titleCell2.setCellValue(BscReportPropertyUtils.getObjectiveTitle());
    titleCell2.setCellStyle(cellHeadStyle);

    titleCell3 = headRow.createCell(2);
    titleCell3.setCellValue(BscReportPropertyUtils.getKpiTitle());
    titleCell3.setCellStyle(cellHeadStyle);

    titleCell4 = headRow.createCell(3);
    titleCell4.setCellValue("Weight");
    titleCell4.setCellStyle(cellHeadStyle);

    titleCell5 = headRow.createCell(4);
    titleCell5.setCellValue("Maximum\nTarget\nMinimum");
    titleCell5.setCellStyle(cellHeadStyle);

    titleCell6 = headRow.createCell(5);
    titleCell6.setCellValue("Score");
    titleCell6.setCellStyle(cellHeadStyle);

    row = row + 1;
    return row;
}

From source file:com.netsteadfast.greenstep.bsc.command.PdcaReportExcelCommand.java

License:Apache License

private int createPdca(XSSFWorkbook wb, XSSFSheet sh, int row, Context context) throws Exception {

    PdcaVO pdca = (PdcaVO) this.getResult(context);

    Row headRow = sh.createRow(row);//from  w w w  .j a  va 2s  .  c o  m
    headRow.setHeight((short) 700);

    // --------------------------------------------------------------------------------------

    XSSFColor bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#d8d8d8"));
    XSSFColor fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#000000"));

    XSSFCellStyle cellHeadStyle = wb.createCellStyle();
    cellHeadStyle.setFillForegroundColor(bgColor);
    cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    XSSFFont cellHeadFont = wb.createFont();
    cellHeadFont.setBold(true);
    cellHeadFont.setColor(fnColor);
    cellHeadStyle.setFont(cellHeadFont);
    cellHeadStyle.setBorderBottom(BorderStyle.THIN);
    cellHeadStyle.setBorderTop(BorderStyle.THIN);
    cellHeadStyle.setBorderRight(BorderStyle.THIN);
    cellHeadStyle.setBorderLeft(BorderStyle.THIN);
    cellHeadStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellHeadStyle.setAlignment(HorizontalAlignment.CENTER);
    cellHeadStyle.setWrapText(true);

    // --------------------------------------------------------------------------------------

    int cols = 6;
    for (int i = 0; i < cols; i++) {
        sh.setColumnWidth(i, 6000);
        Cell headCell1 = headRow.createCell(i);
        headCell1.setCellValue(pdca.getTitle());
        headCell1.setCellStyle(cellHeadStyle);
    }
    sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1));

    // --------------------------------------------------------------------------------------

    XSSFColor bgLabelColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#F2F2F2"));

    XSSFCellStyle cellLabelStyle = wb.createCellStyle();
    cellLabelStyle.setFillForegroundColor(bgLabelColor);
    cellLabelStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    XSSFFont cellLabelFont = wb.createFont();
    cellLabelFont.setBold(true);
    cellLabelFont.setColor(fnColor);
    cellLabelStyle.setFont(cellLabelFont);
    cellLabelStyle.setBorderBottom(BorderStyle.THIN);
    cellLabelStyle.setBorderTop(BorderStyle.THIN);
    cellLabelStyle.setBorderRight(BorderStyle.THIN);
    cellLabelStyle.setBorderLeft(BorderStyle.THIN);
    cellLabelStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellLabelStyle.setAlignment(HorizontalAlignment.LEFT);
    cellLabelStyle.setWrapText(true);

    // --------------------------------------------------------------------------------------

    XSSFColor bgNormalColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#ffffff"));

    XSSFCellStyle cellNormalStyle = wb.createCellStyle();
    cellNormalStyle.setFillForegroundColor(bgNormalColor);
    cellNormalStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    XSSFFont cellNormalFont = wb.createFont();
    cellNormalFont.setBold(false);
    cellNormalFont.setColor(fnColor);
    cellNormalStyle.setFont(cellNormalFont);
    cellNormalStyle.setBorderBottom(BorderStyle.THIN);
    cellNormalStyle.setBorderTop(BorderStyle.THIN);
    cellNormalStyle.setBorderRight(BorderStyle.THIN);
    cellNormalStyle.setBorderLeft(BorderStyle.THIN);
    cellNormalStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellNormalStyle.setAlignment(HorizontalAlignment.LEFT);
    cellNormalStyle.setWrapText(true);

    // --------------------------------------------------------------------------------------

    row++;

    Row labelRow = sh.createRow(row);
    Cell labelCell_0_1 = labelRow.createCell(0);
    labelCell_0_1.setCellValue("Responsibility");
    labelCell_0_1.setCellStyle(cellLabelStyle);

    Cell labelCell_0_2 = labelRow.createCell(1);
    labelCell_0_2.setCellValue(pdca.getResponsibilityAppendNames());
    labelCell_0_2.setCellStyle(cellNormalStyle);

    Cell labelCell_0_3 = labelRow.createCell(2);
    labelCell_0_3.setCellValue(pdca.getResponsibilityAppendNames());
    labelCell_0_3.setCellStyle(cellNormalStyle);

    sh.addMergedRegion(new CellRangeAddress(row, row, 1, 2));

    Cell labelCell_0_4 = labelRow.createCell(3);
    labelCell_0_4.setCellValue("Date range");
    labelCell_0_4.setCellStyle(cellLabelStyle);

    Cell labelCell_0_5 = labelRow.createCell(4);
    labelCell_0_5.setCellValue(pdca.getStartDateDisplayValue() + " ~ " + pdca.getEndDateDisplayValue());
    labelCell_0_5.setCellStyle(cellNormalStyle);

    Cell labelCell_0_6 = labelRow.createCell(5);
    labelCell_0_6.setCellValue(pdca.getStartDateDisplayValue() + " ~ " + pdca.getEndDateDisplayValue());
    labelCell_0_6.setCellStyle(cellNormalStyle);

    sh.addMergedRegion(new CellRangeAddress(row, row, 4, 5));

    // --------------------------------------------------------------------------------------

    row++;

    labelRow = sh.createRow(row);
    Cell labelCell_1_1 = labelRow.createCell(0);
    labelCell_1_1.setCellValue("Confirm");
    labelCell_1_1.setCellStyle(cellLabelStyle);

    Cell labelCell_1_2 = labelRow.createCell(1);
    labelCell_1_2.setCellValue(pdca.getConfirmEmployeeName());
    labelCell_1_2.setCellStyle(cellNormalStyle);

    Cell labelCell_1_3 = labelRow.createCell(2);
    labelCell_1_3.setCellValue(pdca.getConfirmEmployeeName());
    labelCell_1_3.setCellStyle(cellNormalStyle);

    sh.addMergedRegion(new CellRangeAddress(row, row, 1, 2));

    Cell labelCell_1_4 = labelRow.createCell(3);
    labelCell_1_4.setCellValue("Confirm date");
    labelCell_1_4.setCellStyle(cellLabelStyle);

    Cell labelCell_1_5 = labelRow.createCell(4);
    labelCell_1_5.setCellValue(pdca.getConfirmDateDisplayValue());
    labelCell_1_5.setCellStyle(cellNormalStyle);

    Cell labelCell_1_6 = labelRow.createCell(5);
    labelCell_1_6.setCellValue(pdca.getConfirmDateDisplayValue());
    labelCell_1_6.setCellStyle(cellNormalStyle);

    sh.addMergedRegion(new CellRangeAddress(row, row, 4, 5));

    // --------------------------------------------------------------------------------------

    row++;

    labelRow = sh.createRow(row);
    Cell labelCell_2_1 = labelRow.createCell(0);
    labelCell_2_1.setCellValue("Organization\nDepartment");
    labelCell_2_1.setCellStyle(cellLabelStyle);

    Cell labelCell_2_2 = labelRow.createCell(1);
    labelCell_2_2.setCellValue(pdca.getOrganizationAppendNames());
    labelCell_2_2.setCellStyle(cellNormalStyle);

    Cell labelCell_2_3 = labelRow.createCell(2);
    labelCell_2_3.setCellValue(pdca.getOrganizationAppendNames());
    labelCell_2_3.setCellStyle(cellNormalStyle);

    Cell labelCell_2_4 = labelRow.createCell(3);
    labelCell_2_4.setCellValue(pdca.getOrganizationAppendNames());
    labelCell_2_4.setCellStyle(cellNormalStyle);

    Cell labelCell_2_5 = labelRow.createCell(4);
    labelCell_2_5.setCellValue(pdca.getOrganizationAppendNames());
    labelCell_2_5.setCellStyle(cellNormalStyle);

    Cell labelCell_2_6 = labelRow.createCell(5);
    labelCell_2_6.setCellValue(pdca.getOrganizationAppendNames());
    labelCell_2_6.setCellStyle(cellNormalStyle);

    sh.addMergedRegion(new CellRangeAddress(row, row, 1, 5));

    // --------------------------------------------------------------------------------------

    row++;

    labelRow = sh.createRow(row);
    Cell labelCell_3_1 = labelRow.createCell(0);
    labelCell_3_1.setCellValue("KPIs");
    labelCell_3_1.setCellStyle(cellLabelStyle);

    Cell labelCell_3_2 = labelRow.createCell(1);
    labelCell_3_2.setCellValue(pdca.getKpisAppendNames());
    labelCell_3_2.setCellStyle(cellNormalStyle);

    Cell labelCell_3_3 = labelRow.createCell(2);
    labelCell_3_3.setCellValue(pdca.getKpisAppendNames());
    labelCell_3_3.setCellStyle(cellNormalStyle);

    Cell labelCell_3_4 = labelRow.createCell(3);
    labelCell_3_4.setCellValue(pdca.getKpisAppendNames());
    labelCell_3_4.setCellStyle(cellNormalStyle);

    Cell labelCell_3_5 = labelRow.createCell(4);
    labelCell_3_5.setCellValue(pdca.getKpisAppendNames());
    labelCell_3_5.setCellStyle(cellNormalStyle);

    Cell labelCell_3_6 = labelRow.createCell(5);
    labelCell_3_6.setCellValue(pdca.getKpisAppendNames());
    labelCell_3_6.setCellStyle(cellNormalStyle);

    sh.addMergedRegion(new CellRangeAddress(row, row, 1, 5));

    // --------------------------------------------------------------------------------------

    row++;

    labelRow = sh.createRow(row);
    Cell labelCell_4_1 = labelRow.createCell(0);
    labelCell_4_1.setCellValue("Parent PDCA");
    labelCell_4_1.setCellStyle(cellLabelStyle);

    Cell labelCell_4_2 = labelRow.createCell(1);
    labelCell_4_2.setCellValue(pdca.getParentName());
    labelCell_4_2.setCellStyle(cellNormalStyle);

    Cell labelCell_4_3 = labelRow.createCell(2);
    labelCell_4_3.setCellValue(pdca.getParentName());
    labelCell_4_3.setCellStyle(cellNormalStyle);

    Cell labelCell_4_4 = labelRow.createCell(3);
    labelCell_4_4.setCellValue(pdca.getParentName());
    labelCell_4_4.setCellStyle(cellNormalStyle);

    Cell labelCell_4_5 = labelRow.createCell(4);
    labelCell_4_5.setCellValue(pdca.getParentName());
    labelCell_4_5.setCellStyle(cellNormalStyle);

    Cell labelCell_4_6 = labelRow.createCell(5);
    labelCell_4_6.setCellValue(pdca.getParentName());
    labelCell_4_6.setCellStyle(cellNormalStyle);

    sh.addMergedRegion(new CellRangeAddress(row, row, 1, 5));

    // --------------------------------------------------------------------------------------

    row++;

    labelRow = sh.createRow(row);
    Cell labelCell_6_1 = labelRow.createCell(0);
    labelCell_6_1.setCellValue("TYPE");
    labelCell_6_1.setCellStyle(cellLabelStyle);

    Cell labelCell_6_2 = labelRow.createCell(1);
    labelCell_6_2.setCellValue("Title");
    labelCell_6_2.setCellStyle(cellLabelStyle);

    Cell labelCell_6_3 = labelRow.createCell(2);
    labelCell_6_3.setCellValue("Responsibility");
    labelCell_6_3.setCellStyle(cellLabelStyle);

    Cell labelCell_6_4 = labelRow.createCell(3);
    labelCell_6_4.setCellValue("Date range");
    labelCell_6_4.setCellStyle(cellLabelStyle);

    Cell labelCell_6_5 = labelRow.createCell(4);
    labelCell_6_5.setCellValue("Audit");
    labelCell_6_5.setCellStyle(cellLabelStyle);

    Cell labelCell_6_6 = labelRow.createCell(5);
    labelCell_6_6.setCellValue("Audit date");
    labelCell_6_6.setCellStyle(cellLabelStyle);

    // --------------------------------------------------------------------------------------

    row++;

    int nRow = row;

    row = this.createPdcaItem(wb, sh, row, cellNormalStyle, pdca.getItemPlan(), pdca.getAuditPlan());
    row = this.createPdcaItem(wb, sh, row, cellNormalStyle, pdca.getItemDo(), pdca.getAuditDo());
    row = this.createPdcaItem(wb, sh, row, cellNormalStyle, pdca.getItemCheck(), pdca.getAuditCheck());
    row = this.createPdcaItem(wb, sh, row, cellNormalStyle, pdca.getItemAction(), pdca.getAuditAction());

    nRow = this.mergedRegionForItemsRow(wb, sh, nRow, pdca.getItemPlan());
    nRow = this.mergedRegionForItemsRow(wb, sh, nRow, pdca.getItemDo());
    nRow = this.mergedRegionForItemsRow(wb, sh, nRow, pdca.getItemCheck());
    nRow = this.mergedRegionForItemsRow(wb, sh, nRow, pdca.getItemAction());

    return row;
}

From source file:com.netsteadfast.greenstep.bsc.command.PersonalReportExcelCommand.java

License:Apache License

private int createHead(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision, Context context)
        throws Exception {
    String dateType = (String) context.get("dateType");
    String year = (String) context.get("startYearDate");
    String empId = (String) context.get("empId");
    String account = (String) context.get("account");
    String fullName = "";
    String jobTitle = "";
    String departmentName = "";
    String dateTypeName = "Year";
    if ("1".equals(dateType)) {
        dateTypeName = "In the first half";
    }//from ww w . j a  va2s .  com
    if ("2".equals(dateType)) {
        dateTypeName = "In the second half";
    }
    EmployeeVO employee = new EmployeeVO();
    employee.setEmpId(empId);
    employee.setAccount(account);
    DefaultResult<EmployeeVO> result = this.employeeService.findByUK(employee);
    if (result.getValue() != null) {
        fullName = result.getValue().getEmpId() + " - " + result.getValue().getFullName();
        jobTitle = result.getValue().getEmpId() + " - " + result.getValue().getFullName();
        List<String> appendIds = this.organizationService
                .findForAppendOrganizationOids(result.getValue().getEmpId());
        List<String> appendNames = this.organizationService.findForAppendNames(appendIds);
        StringBuilder sb = new StringBuilder();
        for (int i = 0; appendNames != null && i < appendNames.size(); i++) {
            sb.append(appendNames.get(i)).append(Constants.ID_DELIMITER);
        }
        departmentName = sb.toString();
    }

    Row headRow = sh.createRow(row);
    headRow.setHeight((short) 700);

    XSSFColor bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#F2F2F2"));
    XSSFColor fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#000000"));

    XSSFCellStyle cellHeadStyle = wb.createCellStyle();
    cellHeadStyle.setFillForegroundColor(bgColor);
    cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    XSSFFont cellHeadFont = wb.createFont();
    cellHeadFont.setBold(true);
    cellHeadFont.setColor(fnColor);
    cellHeadStyle.setFont(cellHeadFont);
    cellHeadStyle.setBorderBottom(BorderStyle.THIN);
    cellHeadStyle.setBorderTop(BorderStyle.THIN);
    cellHeadStyle.setBorderRight(BorderStyle.THIN);
    cellHeadStyle.setBorderLeft(BorderStyle.THIN);
    cellHeadStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellHeadStyle.setAlignment(HorizontalAlignment.CENTER);
    cellHeadStyle.setWrapText(true);

    int cols = 6;
    for (int i = 0; i < cols; i++) {
        sh.setColumnWidth(i, 6000);
        Cell headCell1 = headRow.createCell(i);
        headCell1.setCellValue("Personal Balance SourceCard");
        headCell1.setCellStyle(cellHeadStyle);
    }
    sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1));

    row++;
    headRow = sh.createRow(row);
    for (int i = 0; i < cols; i++) {
        sh.setColumnWidth(i, 6000);
        Cell headCell1 = headRow.createCell(i);
        headCell1.setCellValue(vision.getTitle());
        headCell1.setCellStyle(cellHeadStyle);
    }
    sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1));

    row++;
    headRow = sh.createRow(row);
    headRow.setHeight((short) 700);

    Cell titleCell1 = headRow.createCell(0);
    titleCell1.setCellValue("Job Title");
    titleCell1.setCellStyle(cellHeadStyle);

    Cell titleCell2 = headRow.createCell(1);
    titleCell2.setCellValue(jobTitle);
    titleCell2.setCellStyle(cellHeadStyle);

    Cell titleCell3 = headRow.createCell(2);
    titleCell3.setCellValue("Department");
    titleCell3.setCellStyle(cellHeadStyle);

    Cell titleCell4 = headRow.createCell(3);
    titleCell4.setCellValue(departmentName);
    titleCell4.setCellStyle(cellHeadStyle);

    Cell titleCell5 = headRow.createCell(4);
    titleCell5.setCellValue("name: " + fullName);
    titleCell5.setCellStyle(cellHeadStyle);

    Cell titleCell6 = headRow.createCell(5);
    titleCell6.setCellValue("Annual assessment: " + year);
    titleCell6.setCellStyle(cellHeadStyle);

    row++;
    headRow = sh.createRow(row);

    titleCell1 = headRow.createCell(0);
    titleCell1.setCellValue(BscReportPropertyUtils.getObjectiveTitle());
    titleCell1.setCellStyle(cellHeadStyle);

    titleCell2 = headRow.createCell(1);
    titleCell2.setCellValue(BscReportPropertyUtils.getKpiTitle());
    titleCell2.setCellStyle(cellHeadStyle);

    titleCell3 = headRow.createCell(2);
    titleCell3.setCellValue("Maximum\nTarget\nMinimum");
    titleCell3.setCellStyle(cellHeadStyle);

    titleCell4 = headRow.createCell(3);
    titleCell4.setCellValue("Weight");
    titleCell4.setCellStyle(cellHeadStyle);

    titleCell5 = headRow.createCell(4);
    titleCell5.setCellValue("Formula");
    titleCell5.setCellStyle(cellHeadStyle);

    titleCell6 = headRow.createCell(5);
    titleCell6.setCellValue("Score");
    titleCell6.setCellStyle(cellHeadStyle);

    row++;
    headRow = sh.createRow(row);
    headRow.setHeight((short) 1000);

    titleCell1 = headRow.createCell(0);
    titleCell1.setCellValue("Objective of Strategy");
    titleCell1.setCellStyle(cellHeadStyle);

    titleCell2 = headRow.createCell(1);
    titleCell2.setCellValue("KPI");
    titleCell2.setCellStyle(cellHeadStyle);

    titleCell3 = headRow.createCell(2);
    titleCell3.setCellValue("Target");
    titleCell3.setCellStyle(cellHeadStyle);

    titleCell4 = headRow.createCell(3);
    titleCell4.setCellValue("Weight");
    titleCell4.setCellStyle(cellHeadStyle);

    titleCell5 = headRow.createCell(4);
    titleCell5.setCellValue("Formula");
    titleCell5.setCellStyle(cellHeadStyle);

    XSSFCellStyle titleStyle = wb.createCellStyle();
    titleStyle.setFillForegroundColor(bgColor);
    titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    titleStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#F5F4F4")));
    titleStyle.setFont(cellHeadFont);
    titleStyle.setBorderBottom(BorderStyle.THIN);
    titleStyle.setBorderTop(BorderStyle.THIN);
    titleStyle.setBorderRight(BorderStyle.THIN);
    titleStyle.setBorderLeft(BorderStyle.THIN);
    titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    titleStyle.setAlignment(HorizontalAlignment.CENTER);
    titleStyle.setWrapText(true);

    titleCell6 = headRow.createCell(5);
    titleCell6.setCellValue(dateTypeName);
    titleCell6.setCellStyle(titleStyle);

    for (int i = 0; i < 5; i++) {
        sh.addMergedRegion(new CellRangeAddress(row - 1, row, i, i));
    }

    return 5;
}