Example usage for org.apache.poi.xssf.usermodel XSSFCellStyle setVerticalAlignment

List of usage examples for org.apache.poi.xssf.usermodel XSSFCellStyle setVerticalAlignment

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFCellStyle
  6. setVerticalAlignment

Introduction

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

Prototype

public void setVerticalAlignment(VerticalAlignment align)

Source Link

Document

Set the type of vertical alignment for the cell

Usage

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

License:Apache License

private int createMainBody(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision, Context context)
        throws Exception {
    int mrRow = row;

    XSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#ffffff")));
    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    XSSFFont cellFont = wb.createFont();
    cellFont.setBold(false);//www .  j a  v a2s  .c o  m
    cellFont.setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#000000")));
    cellStyle.setFont(cellFont);
    cellStyle.setWrapText(true);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setWrapText(true);

    for (PerspectiveVO perspective : vision.getPerspectives()) {
        for (ObjectiveVO objective : perspective.getObjectives()) {
            for (KpiVO kpi : objective.getKpis()) {
                int kCol = 0;
                Row contentRow = sh.createRow(row++);
                contentRow.setHeight((short) 1000);

                Cell contentCell1 = contentRow.createCell(kCol++);
                contentCell1.setCellValue(objective.getName());
                contentCell1.setCellStyle(cellStyle);

                Cell contentCell2 = contentRow.createCell(kCol++);
                contentCell2.setCellValue(kpi.getName());
                contentCell2.setCellStyle(cellStyle);

                Cell contentCell3 = contentRow.createCell(kCol++);
                contentCell3.setCellValue("max: " + kpi.getMax() + "\n" + "target: " + kpi.getTarget() + "\n"
                        + "min: " + kpi.getMin() + "\n" + "unit: " + kpi.getUnit());
                contentCell3.setCellStyle(cellStyle);

                Cell contentCell4 = contentRow.createCell(kCol++);
                contentCell4.setCellValue(kpi.getWeight() + "%");
                contentCell4.setCellStyle(cellStyle);

                Cell contentCell5 = contentRow.createCell(kCol++);
                contentCell5.setCellValue(kpi.getFormula().getName());
                contentCell5.setCellStyle(cellStyle);

                DateRangeScoreVO dateRangeScore = kpi.getDateRangeScores().get(0); // ?

                XSSFCellStyle cellStyleScore = wb.createCellStyle();
                cellStyleScore.setFillForegroundColor(
                        new XSSFColor(SimpleUtils.getColorRGB4POIColor(dateRangeScore.getBgColor())));
                cellStyleScore.setFillPattern(FillPatternType.SOLID_FOREGROUND);

                XSSFFont cellScoreFont = wb.createFont();
                cellScoreFont.setBold(false);
                cellScoreFont.setColor(
                        new XSSFColor(SimpleUtils.getColorRGB4POIColor(dateRangeScore.getFontColor())));
                cellStyleScore.setFont(cellScoreFont);
                cellStyleScore.setWrapText(true);
                cellStyleScore.setVerticalAlignment(VerticalAlignment.CENTER);
                cellStyleScore.setBorderBottom(BorderStyle.THIN);
                cellStyleScore.setBorderTop(BorderStyle.THIN);
                cellStyleScore.setBorderRight(BorderStyle.THIN);
                cellStyleScore.setBorderLeft(BorderStyle.THIN);

                Cell contentCell6 = contentRow.createCell(kCol++);
                contentCell6.setCellValue(BscReportSupportUtils.parse2(dateRangeScore.getScore()));
                contentCell6.setCellStyle(cellStyleScore);

            }
        }
    }

    for (PerspectiveVO perspective : vision.getPerspectives()) {
        for (ObjectiveVO objective : perspective.getObjectives()) {
            int rowspan = objective.getRow();
            if (objective.getRow() > 1) {
                // 2016-12-13 old work with POI 3.12
                //sh.addMergedRegion(new CellRangeAddress(mrRow, mrRow+rowspan-1, 0, 0));

                // 2016-12-13 new work with POI 3.15
                int mrRow1 = mrRow + rowspan - 1;
                if (mrRow1 > mrRow) {
                    sh.addMergedRegion(new CellRangeAddress(mrRow, mrRow1, 0, 0));
                }

            }
            mrRow += rowspan;
        }
    }

    return row;
}

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

License:Apache License

private void createFoot(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision, Context context)
        throws Exception {

    Row footRow = sh.createRow(row);//  ww w  .  ja v  a  2s.c o m
    Row footRowB = sh.createRow(row + 1);
    XSSFCellStyle cellStyle = wb.createCellStyle();

    cellStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#FFFFFF")));
    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    XSSFFont cellFont = wb.createFont();
    cellFont.setBold(true);
    cellStyle.setFont(cellFont);
    cellStyle.setWrapText(true);

    Cell footCell1 = footRow.createCell(0);
    footCell1.setCellValue("assess:");
    footCell1.setCellStyle(cellStyle);
    Cell footCell1B = footRowB.createCell(0);
    footCell1B.setCellValue("assess:");
    footCell1B.setCellStyle(cellStyle);
    sh.addMergedRegion(new CellRangeAddress(row, row + 1, 0, 0));

    Cell footCell2 = footRow.createCell(1);
    footCell2.setCellValue(BscReportPropertyUtils.getPersonalReportClassLevel());
    footCell2.setCellStyle(cellStyle);
    Cell footCell3 = footRow.createCell(2);
    footCell3.setCellValue(BscReportPropertyUtils.getPersonalReportClassLevel());
    footCell3.setCellStyle(cellStyle);
    Cell footCell4 = footRow.createCell(3);
    footCell4.setCellValue(BscReportPropertyUtils.getPersonalReportClassLevel());
    footCell4.setCellStyle(cellStyle);
    Cell footCell2B = footRowB.createCell(1);
    footCell2B.setCellValue(BscReportPropertyUtils.getPersonalReportClassLevel());
    footCell2B.setCellStyle(cellStyle);
    Cell footCell3B = footRowB.createCell(2);
    footCell3B.setCellValue(BscReportPropertyUtils.getPersonalReportClassLevel());
    footCell3B.setCellStyle(cellStyle);
    Cell footCell4B = footRowB.createCell(3);
    footCell4B.setCellValue(BscReportPropertyUtils.getPersonalReportClassLevel());
    footCell4B.setCellStyle(cellStyle);
    sh.addMergedRegion(new CellRangeAddress(row, row + 1, 1, 3));

    Cell footCell5 = footRow.createCell(4);
    footCell5.setCellValue("Total");
    footCell5.setCellStyle(cellStyle);

    float total = 0.0f;
    if (context.get("total") != null && context.get("total") instanceof Float) {
        total = (Float) context.get("total");
    }

    Cell footCell6 = footRow.createCell(5);
    footCell6.setCellValue(BscReportSupportUtils.parse2(total));
    footCell6.setCellStyle(cellStyle);

    Cell footCell5b = footRowB.createCell(4);
    footCell5b.setCellValue("Class");
    footCell5b.setCellStyle(cellStyle);

    Cell footCell6b = footRowB.createCell(5);
    footCell6b.setCellValue("");
    footCell6b.setCellStyle(cellStyle);

}

From source file:com.respam.comniq.models.POIexcelExporter.java

License:Open Source License

public void excelWriter(JSONObject parsedObj, int rownum) throws IOException {
    String path = System.getProperty("user.home") + File.separator + "comniq" + File.separator + "output";
    File file = new File(path + File.separator + "POImovieInfo.xlsx");

    String thumbnailPath = System.getProperty("user.home") + File.separator + "comniq" + File.separator
            + "output" + File.separator + "thumbnails";
    File posterFile = new File(thumbnailPath + File.separator + parsedObj.get("Title") + ".jpg");

    if (!file.exists()) {
        createFile();/*from  w ww . j  a v a  2 s .  com*/
    }

    if (file.exists() && checked.equals(false)) {
        findLastRow();
    }

    try {
        FileInputStream fis = new FileInputStream(file);
        XSSFWorkbook workbook = new XSSFWorkbook(fis);

        XSSFSheet sheet = workbook.getSheet("Movies");

        Map<String, Object[]> label = new TreeMap<>();
        label.put("1",
                new Object[] { "", parsedObj.get("Title"), parsedObj.get("Released"),
                        parsedObj.get("Metascore"), parsedObj.get("imdbRating"), parsedObj.get("Plot"),
                        parsedObj.get("imdbID"), parsedObj.get("Genre"), parsedObj.get("Director"),
                        parsedObj.get("Actors"), parsedObj.get("Rated"), parsedObj.get("Runtime") });

        Set<String> keyset = label.keySet();

        // Setting Style for the Label Row

        XSSFCellStyle contentStyle = workbook.createCellStyle();
        contentStyle.setWrapText(true);
        contentStyle.setVerticalAlignment(VerticalAlignment.TOP);

        rownum = rownum + lastRow;

        if (posterFile.exists()) {
            InputStream imageStream = new FileInputStream(
                    thumbnailPath + File.separator + parsedObj.get("Title") + ".jpg");
            byte[] imageBytes = IOUtils.toByteArray(imageStream);
            pictureureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_PNG);
            imageStream.close();

            CreationHelper helper = workbook.getCreationHelper();
            drawing = sheet.createDrawingPatriarch();
            anchor = helper.createClientAnchor();

        }

        for (String key : keyset) {

            Row row = sheet.createRow(rownum++);
            row.setHeight((short) 2000);
            Object[] objArr = label.get(key);
            int cellnum = 0;
            for (Object obj : objArr) {
                Cell cell = row.createCell(cellnum++);
                cell.setCellStyle(contentStyle);
                cell.setCellValue((String) obj);
            }
            if (posterFile.exists()) {
                anchor.setCol1(0);
                anchor.setRow1(rownum - 1);
                anchor.setCol2(0);
                anchor.setRow2(rownum - 1);
                Picture pict = drawing.createPicture(anchor, pictureureIdx);
                pict.resize(1, 1);
            }
        }
        FileOutputStream out = new FileOutputStream(file);
        workbook.write(out);
        out.close();
    } catch (Exception e) {
        e.printStackTrace();
    }

}

From source file:com.sec.ose.osi.report.standard.CoverSheetTemplate.java

License:Open Source License

protected void createTitle() {
    short lineThickness = (short) (6 * BASE_HEIGHT);

    // Top Line/*from w  w w. j  a v  a2  s. c om*/
    Row row = sheet.createRow(ROW_4);
    row.setHeight(lineThickness);

    XSSFCellStyle style = wb.createCellStyle();
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    setDummyTitleStyle(row, style);

    // Title
    row = sheet.createRow(ROW_5);
    row.setHeightInPoints(100);
    sheet.addMergedRegion(CellRangeAddress.valueOf("B5:G5"));

    Font font = wb.createFont();
    font.setFontHeightInPoints((short) 28);
    font.setFontName("Trebuchet MS");
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setFont(font);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

    setDummyTitleStyle(row, style);
    row.getCell(COL_B).setCellValue("Open Source License Verification Report");

    // Bottom Line
    row = sheet.createRow(ROW_6);
    row.setHeight(lineThickness);

    style = wb.createCellStyle();
    style.setFillForegroundColor(DARK_BLUE);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    setDummyTitleStyle(row, style);
}

From source file:com.sec.ose.osi.report.standard.CoverSheetTemplate.java

License:Open Source License

protected XSSFCellStyle getCellStyle(int border, Font font) {
    XSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setWrapText(true); // new line

    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());

    if ((border & BORDER_BOTTOM) > 0)
        style.setBorderBottom(CellStyle.BORDER_DOUBLE);
    else//from ww  w.  ja v  a 2 s  .  co m
        style.setBorderBottom(CellStyle.BORDER_THIN);

    if ((border & BORDER_LEFT) > 0)
        style.setBorderLeft(CellStyle.BORDER_DOUBLE);
    else
        style.setBorderLeft(CellStyle.BORDER_THIN);

    if ((border & BORDER_RIGHT) > 0)
        style.setBorderRight(CellStyle.BORDER_DOUBLE);
    else
        style.setBorderRight(CellStyle.BORDER_THIN);

    if ((border & BORDER_TOP) > 0)
        style.setBorderTop(CellStyle.BORDER_DOUBLE);
    else
        style.setBorderTop(CellStyle.BORDER_THIN);

    if (font != null)
        style.setFont(font);

    return style;
}

From source file:com.sec.ose.osi.report.standard.ISheetTemplate.java

License:Open Source License

/**
 * @param color/*from  w  w  w.j a  va2 s. c  o m*/
 * @param font
 * @return CellStyle
 */
protected XSSFCellStyle getCellStyle(XSSFColor color, Font font) {
    XSSFCellStyle style = wb.createCellStyle();
    style.setFillForegroundColor(color);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setWrapText(true); // new line

    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());

    if (font != null)
        style.setFont(font);

    return style;
}

From source file:DSC.DriverReport.java

private static void createSpreadsheets() {
    XSSFWorkbook workbook = new XSSFWorkbook();
    for (Route route : routeList) {

        XSSFSheet sheet = workbook.createSheet("DriverReports Route - " + route.getID());

        Map<String, Object[]> data = new TreeMap<>();
        data.put("1",
                new Object[] {
                        "Doorstep Chef Driver Sheet  Week: " + returnWeekInt() + " - " + returnWeekString(), "",
                        "", "", "", "", "", "", "",
                        "Driver: " + route.getDrivers().get(0).getDriver().getDriverName().split(" ")[0] + " - "
                                + route.getDrivers().get(0).getDriver().getContactNumber(),
                        "Route: " + route.getID() });
        data.put("2", new Object[] { "", "", "", "", "", "", "", "", "" });
        data.put("3", new Object[] { "Customer", "Contact", "Cash", "DatePaid", "Mon", "Tue", "Wed", "Thu",
                "Fri", "Address", "AdditionalInfo" });

        int counter = 4;
        for (Order order : orderList) {
            if (order.getRoute().equals(route.getID())) {
                Client client = order.getClient();
                String durationMarker = "";
                if (order.getDuration().equals("Monday - Thursday")) {
                    durationMarker = "X";
                }/*from   w ww .j  a  v a  2  s  .co  m*/

                data.put("" + counter,
                        new Object[] { client.getName() + " " + client.getSurname(),
                                client.getContactNumber().substring(0, 3) + " "
                                        + client.getContactNumber().substring(3, 6) + " "
                                        + client.getContactNumber().substring(6, 10),
                                "", "", "", "", "", "", durationMarker,
                                client.getAddress().replaceAll("\n", ", "), client.getAdditionalInfo() });
                counter++;
            }
        }

        Set<String> keySet = data.keySet();
        int longestCustomer = 0;
        int totalWidth = 34900;
        for (int key = 1; key < keySet.size() + 1; key++) {

            Row row = sheet.createRow(key - 1);
            Object[] arr = data.get(key + "");

            for (int i = 0; i < arr.length; i++) {
                Cell cell = row.createCell(i);
                cell.setCellValue((String) arr[i]);
                if (i == 0 && !(key + "").equals("1") && longestCustomer < ((String) arr[i]).length()) {
                    longestCustomer = ((String) arr[i]).length();
                }
                XSSFCellStyle borderStyle = workbook.createCellStyle();

                if (!((key + "").equals("1") || (key + "").equals("2"))) {
                    borderStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
                    borderStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
                    borderStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
                    borderStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
                    if ((key + "").equals("3")) {
                        borderStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
                        borderStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
                        borderStyle.setBorderTop(XSSFCellStyle.BORDER_MEDIUM);
                        borderStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
                        borderStyle.setAlignment(HorizontalAlignment.CENTER);
                        borderStyle.setFillPattern(XSSFCellStyle.LESS_DOTS);
                        borderStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
                        XSSFFont font = workbook.createFont();
                        font.setColor(IndexedColors.WHITE.getIndex());
                        font.setBold(true);
                        borderStyle.setFont(font);

                    } else {
                        if (i != 0) {
                            borderStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
                        } else {
                            borderStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
                        }
                        if (i != 10) {
                            borderStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
                        } else {
                            borderStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
                        }

                        if (i == 8 && ((String) arr[i]).contains("X")) {
                            cell.setCellValue("");
                            borderStyle.setFillPattern(XSSFCellStyle.FINE_DOTS);
                            borderStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
                            borderStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
                        }

                        if ((Integer.parseInt((key + ""))) != keySet.size()) {
                            borderStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
                        } else {
                            borderStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
                        }
                        borderStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);

                    }
                    if ((i == 9 || i == 10) && !(key + "").equals("3")) {
                        borderStyle.setAlignment(XSSFCellStyle.ALIGN_JUSTIFY);
                        borderStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_JUSTIFY);
                    }
                } else {
                    if (i == 9) {
                        borderStyle.setAlignment(HorizontalAlignment.CENTER);
                    } else if (i == 10) {
                        borderStyle.setAlignment(HorizontalAlignment.RIGHT);
                    }
                    XSSFFont font = workbook.createFont();
                    font.setFontName("Calibri");
                    font.setFontHeightInPoints((short) 13);
                    font.setBold(true);
                    borderStyle.setFont(font);
                }

                cell.setCellStyle(borderStyle);
            }
        }

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));

        if ((longestCustomer) < 14) {
            longestCustomer = 14;
        }

        sheet.setColumnWidth(0, (longestCustomer + 1) * 240);
        sheet.setColumnWidth(1, 12 * 240);
        for (int i = 0; i < 6; i++) {
            sheet.setColumnWidth(i + 4, 1000);
        }
        sheet.setColumnWidth(2, 1000);
        sheet.setColumnWidth(3, 10 * 240);
        for (int i = 0; i < 9; i++) {
            totalWidth -= sheet.getColumnWidth(i);
        }
        sheet.setColumnWidth(9, (totalWidth / 3) * 2);
        sheet.setColumnWidth(10, totalWidth / 3);

        Row rowDate = sheet.createRow(keySet.size() + 1);
        Cell cell = rowDate.createCell(0);
        SimpleDateFormat sf = new SimpleDateFormat("EEE MMM yyyy HH:mm:ss");

        cell.setCellValue(sf.format(Calendar.getInstance().getTime()));
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(keySet.size() + 1, keySet.size() + 1, 0, 10));

    }

    try {
        excelOut = new FileOutputStream(file);
        workbook.write(excelOut);
        excelOut.close();
        if (!(DSC_Main.generateAllReports)) {
            driverLoadingObj.setVisible(false);
            driverLoadingObj.dispose();
            JOptionPane.showMessageDialog(null, "DriverReports Succesfully Generated", "Success",
                    JOptionPane.INFORMATION_MESSAGE);
        } else {
            DSC_Main.reportsDone++;
            if (DSC_Main.reportsDone == DSC_Main.TOTAL_REPORTS) {
                DSC_Main.reportsDone();
            }
        }

    } catch (FileNotFoundException ex) {
        JOptionPane.showMessageDialog(null, "Please close the excel file before using generating.", "Error",
                JOptionPane.ERROR_MESSAGE);
        System.err.println("Error - Could not create new DriverReport: File currently in use.");
        ex.printStackTrace();
    } catch (IOException io) {
        JOptionPane.showMessageDialog(null, "An error occured\nCould not create Driver Report", "Error",
                JOptionPane.ERROR_MESSAGE);
        System.err.println("Error - Could not create new Driver Report: ");
        io.printStackTrace();
    }
    System.out.println("Done - Driver");
}

From source file:DSC.PackerReport.java

private static void createSpreadsheets() {
    orderList.sort(new Comparator<Order>() {
        @Override/*from w  w  w .  j  a  va 2s .  c  om*/
        public int compare(Order o1, Order o2) {
            int result;
            if (o1.getFamilySize() < o2.getFamilySize()) {
                result = -1;
            } else if (o1.getFamilySize() == o2.getFamilySize()) {
                result = 0;
            } else {
                result = 1;
            }
            return result;
        }
    });

    XSSFWorkbook workbook = new XSSFWorkbook();
    for (Route route : routeList) {
        XSSFSheet sheet = workbook.createSheet("PackerReports Route - " + route.getID());

        Map<String, String[]> data = new TreeMap<>();
        data.put("1",
                new String[] { "Doorstep Chef Packer Sheet", "", "",
                        route.getDrivers().get(0).getDriver().getDriverName().split(" ")[0] + " - "
                                + route.getDrivers().get(0).getDriver().getContactNumber(),
                        "", " Week: " + DriverReport.returnWeekInt() + " Route: " + route.getID() });
        data.put("2", new String[] { "", "", "", "", "", "" });
        data.put("3", new String[] { "Customer", "FamSize", "MealType", "Qty", "Allergies", "Exclutions" });

        int[] totals = new int[11];

        int counter = 4;
        for (Order order : orderList) {
            if (order.getRoute().equals(route.getID())) {

                Client client = order.getClient();
                String customer = client.getName() + " " + client.getSurname();
                String famSize = order.getFamilySize() + "";
                for (Meal meal : order.getMeals()) {
                    data.put(counter + "", new String[] { customer, famSize, meal.getMealType(),
                            meal.getQuantity() + "", meal.getAllergies(), meal.getExclusions() });
                    customer = "";
                    famSize = "";
                    counter++;
                    if (meal.getMealType().equals("Standard")) {
                        totals[1] += meal.getQuantity();
                    } else if (meal.getMealType().equals("Low Carb")) {
                        totals[2] += meal.getQuantity();
                    } else if (meal.getMealType().equals("Kiddies")) {
                        totals[3] += meal.getQuantity();
                    }

                    switch (meal.getQuantity()) {
                    case 1:
                        totals[4]++;
                        break;
                    case 2:
                        totals[5]++;
                        break;
                    case 3:
                        totals[6]++;
                        break;
                    case 4:
                        totals[7]++;
                        break;
                    case 5:
                        totals[8]++;
                        break;
                    case 6:
                        totals[9]++;
                        break;
                    default:
                        if (meal.getQuantity() > 6) {
                            totals[10]++;
                        }
                    }
                }
                totals[0]++;
            }
        }

        Set<String> keySet = data.keySet();
        int totalSize = 22000;
        int longestCustomer = 0;
        for (int key = 1; key < keySet.size() + 1; key++) {
            Row row = sheet.createRow(key - 1);
            String[] arr = data.get(key + "");

            for (int i = 0; i < arr.length; i++) {
                Cell cell = row.createCell(i);
                cell.setCellValue(arr[i]);
                XSSFCellStyle borderStyle = workbook.createCellStyle();

                if (i == 0 && !(key + "").equals("1") && longestCustomer < ((String) arr[i]).length()) {
                    longestCustomer = ((String) arr[i]).length();
                }

                if (!((key + "").equals("1") || (key + "").equals("2"))) {
                    borderStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
                    borderStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
                    borderStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
                    borderStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
                    if ((key + "").equals("3")) {
                        borderStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
                        borderStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
                        borderStyle.setBorderTop(XSSFCellStyle.BORDER_MEDIUM);
                        borderStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
                        borderStyle.setAlignment(HorizontalAlignment.CENTER);
                        borderStyle.setFillPattern(XSSFCellStyle.LESS_DOTS);
                        borderStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
                        XSSFFont font = workbook.createFont();
                        font.setColor(IndexedColors.WHITE.getIndex());
                        font.setBold(true);
                        borderStyle.setFont(font);

                    } else {
                        if (i != 0) {
                            borderStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
                        } else {
                            borderStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
                        }
                        if (i != 5) {
                            borderStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
                        } else {
                            borderStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
                        }

                        if (i == 5 || i == 4) {
                            borderStyle.setAlignment(XSSFCellStyle.ALIGN_JUSTIFY);
                            borderStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_JUSTIFY);
                        }

                        if ((Integer.parseInt((key + ""))) != keySet.size()) {
                            borderStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
                        } else {
                            borderStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
                        }
                        borderStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);

                    }
                    if (i == 3 || i == 1) {
                        borderStyle.setAlignment(HorizontalAlignment.CENTER);
                    }
                } else {
                    if (key != 3 && (i == 4 || i == 5)) {
                        borderStyle.setAlignment(XSSFCellStyle.ALIGN_JUSTIFY);
                        borderStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_JUSTIFY);
                    }
                    if (i == 3) {
                        borderStyle.setAlignment(HorizontalAlignment.CENTER);
                    } else if (i == 5) {
                        borderStyle.setAlignment(HorizontalAlignment.RIGHT);
                    }
                    XSSFFont font = workbook.createFont();
                    font.setFontName("Calibri");
                    font.setFontHeightInPoints((short) 13);
                    font.setBold(true);
                    borderStyle.setFont(font);
                }

                cell.setCellStyle(borderStyle);
            }
        }

        //<editor-fold defaultstate="collapsed" desc="Add Totals">
        Row row = sheet.createRow(keySet.size());
        Cell cell1 = row.createCell(0);
        cell1.setCellValue("Clients: " + totals[0]);
        XSSFCellStyle cellStyle1 = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setBold(true);
        cellStyle1.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
        cellStyle1.setFont(font);
        cell1.setCellStyle(cellStyle1);

        Cell cell2 = row.createCell(1);
        cell2.setCellValue("Standard: " + totals[1]);
        XSSFCellStyle cellStyle2 = workbook.createCellStyle();
        font.setBold(true);
        cellStyle2.setFont(font);
        cell2.setCellStyle(cellStyle2);

        Cell cell3 = row.createCell(4);
        cell3.setCellValue("Low Carb:  " + totals[2]);
        XSSFCellStyle cellStyle3 = workbook.createCellStyle();
        font.setBold(true);
        cellStyle3.setFont(font);
        cell3.setCellStyle(cellStyle3);

        Cell cell4 = row.createCell(5);
        cell4.setCellValue("Kiddies: " + totals[3]);
        XSSFCellStyle cellStyle4 = workbook.createCellStyle();
        font.setBold(true);
        cellStyle4.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
        cellStyle4.setFont(font);
        cell4.setCellStyle(cellStyle4);

        row = sheet.createRow(keySet.size() + 1);

        Cell holder = row.createCell(0);
        XSSFCellStyle border1 = workbook.createCellStyle();
        border1.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
        holder.setCellStyle(border1);

        cell2 = row.createCell(1);
        cell2.setCellValue("Single: " + totals[4]);
        XSSFCellStyle cellStyle6 = workbook.createCellStyle();
        font.setBold(true);
        cellStyle6.setFont(font);
        cell2.setCellStyle(cellStyle6);

        cell3 = row.createCell(4);
        cell3.setCellValue("Couple:  " + totals[5]);
        XSSFCellStyle cellStyle7 = workbook.createCellStyle();
        font.setBold(true);
        cellStyle7.setFont(font);
        cell3.setCellStyle(cellStyle7);

        cell4 = row.createCell(5);
        cell4.setCellValue("Small(3): " + totals[6]);
        XSSFCellStyle cellStyle8 = workbook.createCellStyle();
        font.setBold(true);
        cellStyle8.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
        cellStyle8.setFont(font);
        cell4.setCellStyle(cellStyle8);

        row = sheet.createRow(keySet.size() + 2);

        Cell holder2 = row.createCell(0);
        XSSFCellStyle border2 = workbook.createCellStyle();
        border2.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
        border2.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
        holder2.setCellStyle(border2);

        cell2 = row.createCell(1);
        cell2.setCellValue("Medium(4): " + totals[7]);
        XSSFCellStyle cellStyle9 = workbook.createCellStyle();
        font.setBold(true);
        cellStyle9.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
        cellStyle9.setFont(font);
        cell2.setCellStyle(cellStyle9);

        Cell holder3 = row.createCell(2);
        XSSFCellStyle border3 = workbook.createCellStyle();
        border3.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
        holder3.setCellStyle(border3);

        Cell holder4 = row.createCell(3);
        XSSFCellStyle border4 = workbook.createCellStyle();
        border4.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
        holder4.setCellStyle(border4);

        cell3 = row.createCell(4);
        cell3.setCellValue("Large(5):  " + totals[8]);
        XSSFCellStyle cellStyle10 = workbook.createCellStyle();
        font.setBold(true);
        cellStyle10.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
        cellStyle10.setFont(font);
        cell3.setCellStyle(cellStyle10);

        cell4 = row.createCell(5);
        cell4.setCellValue("XLarge(6): " + totals[9]);
        XSSFCellStyle cellStyle11 = workbook.createCellStyle();
        font.setBold(true);
        cellStyle11.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
        cellStyle11.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
        cellStyle11.setFont(font);
        cell4.setCellStyle(cellStyle11);

        //</editor-fold>
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 4));
        sheet.addMergedRegion(new CellRangeAddress(keySet.size(), keySet.size(), 1, 3));
        sheet.addMergedRegion(new CellRangeAddress(keySet.size() + 1, keySet.size() + 1, 1, 3));
        sheet.addMergedRegion(new CellRangeAddress(keySet.size() + 2, keySet.size() + 2, 1, 3));

        sheet.setColumnWidth(0, (longestCustomer + 1) * 240);
        sheet.setColumnWidth(1, 8 * 240);
        sheet.setColumnWidth(2, 10 * 240);
        sheet.setColumnWidth(3, 4 * 240);

        int usedSize = 0;
        for (int i = 0; i <= 3; i++) {
            usedSize += sheet.getColumnWidth(i);
        }
        sheet.setColumnWidth(4, (totalSize - usedSize) / 2);
        sheet.setColumnWidth(5, (totalSize - usedSize) / 2);

        Row rowDate = sheet.createRow(keySet.size() + 4);
        Cell cell = rowDate.createCell(0);
        SimpleDateFormat sf = new SimpleDateFormat("EEE MMM yyyy HH:mm:ss");

        cell.setCellValue(sf.format(Calendar.getInstance().getTime()));
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(keySet.size() + 4, keySet.size() + 4, 0, 5));

    }

    try {
        workbook.write(excelOut);
        excelOut.close();
        System.out.println("Done - Packer");
        if (DSC_Main.generateAllReports) {
            DSC_Main.reportsDone++;
            if (DSC_Main.reportsDone == DSC_Main.TOTAL_REPORTS) {
                DSC_Main.reportsDone();
            }
        } else {
            packerLoadingObj.setVisible(false);
            packerLoadingObj.dispose();
            JOptionPane.showMessageDialog(null, "PackerReport Succesfully Generated", "Success",
                    JOptionPane.INFORMATION_MESSAGE);
        }
    } catch (IOException io) {
        packerLoadingObj.setVisible(false);
        packerLoadingObj.dispose();
        JOptionPane.showMessageDialog(null, "An error occured\nCould not create PackerReport", "Error",
                JOptionPane.ERROR_MESSAGE);
        System.err.println("Error - Could not create new PackerReport: ");
        io.printStackTrace();
    }

}

From source file:edu.vt.vbi.patric.common.ExcelHelper.java

License:Apache License

/**
 * This method creates a map of Cellstyle objects for page building. Note: this method used for XSSF pages
 * /*  w w  w .j  av a  2 s .co m*/
 * @return hashmap of styles
 */
private Map<String, XSSFCellStyle> createXStyles() {

    XSSFCellStyle style = (XSSFCellStyle) createBorderStyle();

    // create style for cells in header row
    Font headerFont = xwb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setColor(IndexedColors.WHITE.getIndex());

    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(new XSSFColor(new java.awt.Color(0, 52, 94)));
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setWrapText(true);
    style.setFont(headerFont);
    xstyles.put("header", style);

    // create styles for alternating background color cells
    Font bodyFont = xwb.createFont();
    bodyFont.setFontHeightInPoints((short) 8);

    style = (XSSFCellStyle) createBorderStyle();
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(new XSSFColor(new java.awt.Color(230, 240, 248)));
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(bodyFont);
    style.setWrapText(true);
    xstyles.put("bg2", style);

    style = (XSSFCellStyle) createBorderStyle();
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setWrapText(true);
    style.setFont(bodyFont);
    xstyles.put("bg1", style);

    style = (XSSFCellStyle) createBorderStyle();
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 193, 193)));
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(bodyFont);
    style.setWrapText(true);
    xstyles.put("empty", style);

    return xstyles;
}

From source file:jdbreport.model.io.xls.poi.Excel2007Writer.java

License:Apache License

protected CellStyle createStyle(jdbreport.model.CellStyle style, Workbook wb) {

    XSSFCellStyle newStyle = (XSSFCellStyle) wb.createCellStyle();
    newStyle.setAlignment(convertHorizontalAlign(style.getHorizontalAlignment()));
    newStyle.setVerticalAlignment(convertVerticalAlign(style.getVerticalAlignment()));

    Border border = style.getBorders(Border.LINE_BOTTOM);
    if (border != null) {
        newStyle.setBorderBottom(getBorder(border));
        newStyle.setBottomBorderColor(new XSSFColor(border.getColor()));
    }//from   ww w .j  a  v a2s  .co  m
    border = style.getBorders(Border.LINE_TOP);
    if (border != null) {
        newStyle.setBorderTop(getBorder(border));
        newStyle.setTopBorderColor(new XSSFColor(border.getColor()));
    }
    border = style.getBorders(Border.LINE_LEFT);
    if (border != null) {
        newStyle.setBorderLeft(getBorder(border));
        newStyle.setLeftBorderColor(new XSSFColor(border.getColor()));
    }
    border = style.getBorders(Border.LINE_RIGHT);
    if (border != null) {
        newStyle.setBorderRight(getBorder(border));
        newStyle.setRightBorderColor(new XSSFColor(border.getColor()));
    }

    Font font = wb.createFont();
    font.setFontName(style.getFamily());
    if (style.isBold()) {
        font.setBold(true);
    }
    font.setItalic(style.isItalic());
    if (style.isUnderline()) {
        font.setUnderline(Font.U_SINGLE);
    }
    if (style.isStrikethrough()) {
        font.setStrikeout(true);
    }
    font.setFontHeightInPoints((short) style.getSize());
    if (style.getForegroundColor() != null && !style.getForegroundColor().equals(Color.black)) {
        font.setColor(colorToIndex(wb, style.getForegroundColor()));
    }

    newStyle.setFont(font);

    if (style.getBackground() != null && !style.getBackground().equals(Color.white)) {
        newStyle.setFillForegroundColor(new XSSFColor(style.getBackground()));
        newStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    }

    if (style.getAngle() != 0) {
        int angle = style.getAngle();
        if (angle > 90 && angle <= 180) {
            angle = 90;
        } else if (angle > 180 && angle <= 270) {
            angle = -90;
        } else if (angle > 270) {
            angle = -(360 - angle);
        }
        newStyle.setRotation((short) angle);
    }

    newStyle.setWrapText(style.isWrapLine());

    return newStyle;
}