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

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

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

Introduction

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

Prototype

@Override
public void setBorderBottom(BorderStyle border)

Source Link

Document

Set the type of border to use for the bottom border of the cell

Usage

From source file:DSC.AccountantReport.java

private static void createExcelReport() {
    XSSFWorkbook workbook = new XSSFWorkbook();
    clients.sort(new Comparator<Client>() {
        @Override//w w  w . j  ava 2  s. c  o m
        public int compare(Client o1, Client o2) {
            return (o1.getSurname() + " " + o1.getName()).compareTo(o2.getSurname() + " " + o2.getName());
        }
    });

    int lastIndex = 0;
    for (int letter = 0; letter < 26; letter++) {
        XSSFSheet sheet = workbook.createSheet("AccountReport " + (char) (65 + letter));
        Map<String, Object[]> data = new TreeMap<>();
        data.put("1", new Object[] { "Doorstep Chef Accountant Sheet", "", "", "", "", "", "",
                "Week: " + DriverReport.returnWeekInt(), "", "" });
        data.put("2", new Object[] { "", "", "", "", "", "", "", "", "", "" });
        data.put("3", new Object[] { "Customer", "Contact", "Fam", "4Day", "Mthly", "EFT", "Cash", "Date Paid",
                "Stay", "Comments" });

        int reduction = 0;
        for (int i = 0; i < clients.size(); i++) {
            Client client = clients.get(i);
            if (client.getSurname().toUpperCase().charAt(0) == (char) (65 + letter)) {
                data.put((i + 4 - reduction) + "",
                        new Object[] { client.getName() + " " + client.getSurname(),
                                client.getContactNumber().substring(0, 3) + " "
                                        + client.getContactNumber().substring(3, 6) + " "
                                        + client.getContactNumber().substring(6, 10),
                                client.getAdditionalInfo(), "", "", "", "", "", "", "" });
            } else {
                reduction++;
            }
        }

        Set<String> keySet = data.keySet();
        int totalSize = 34900;
        int longestCustomer = 0;

        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 != 9) {
                            borderStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
                        } else {
                            borderStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
                        }

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

                    }
                } else {
                    if (i == 7) {
                        borderStyle.setAlignment(HorizontalAlignment.RIGHT);
                    }
                    XSSFFont font = workbook.createFont();
                    font.setFontName("Calibri");
                    font.setFontHeightInPoints((short) 13);
                    font.setBold(true);
                    borderStyle.setFont(font);
                }

                cell.setCellStyle(borderStyle);

            }
            if (key == 1) {
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 7, 9));
            }

        }
        sheet.setColumnWidth(0, (longestCustomer + 1) * 240);
        sheet.setColumnWidth(1, 11 * 240);
        sheet.setColumnWidth(2, 5 * 240);
        sheet.setColumnWidth(3, 5 * 240);
        sheet.setColumnWidth(4, 5 * 240);
        sheet.setColumnWidth(5, 5 * 240);
        sheet.setColumnWidth(6, 5 * 240);
        sheet.setColumnWidth(7, 10 * 240);
        sheet.setColumnWidth(8, 5 * 240);
        for (int i = 0; i < 9; i++) {
            totalSize -= sheet.getColumnWidth(i);
        }
        sheet.setColumnWidth(9, totalSize);

        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, 9));

    }

    try {
        workbook.write(excelOut);
        excelOut.close();
        System.out.println("Done - Accountant");
        if (!(DSC_Main.generateAllReports)) {
            accountLoadObj.setVisible(false);
            accountLoadObj.dispose();
            JOptionPane.showMessageDialog(null, "AccountReports Succesfully Generated", "Success",
                    JOptionPane.INFORMATION_MESSAGE);
        } else {
            DSC_Main.reportsDone++;
            if (DSC_Main.reportsDone == DSC_Main.TOTAL_REPORTS) {
                DSC_Main.reportsDone();
            }
        }

    } catch (IOException io) {
        accountLoadObj.setVisible(false);
        accountLoadObj.dispose();
        JOptionPane.showMessageDialog(null, "An error occured\nCould not create AccountReport", "Error",
                JOptionPane.ERROR_MESSAGE);
        System.err.println("Error - Could not create new AccountReport: ");
        io.printStackTrace();
    }
}

From source file:DSC.ChefReport.java

public static void processChefReport() throws IOException {

    boolean firstFamEntry = true;
    String list[] = { "Standard", "Low Carb", "Kiddies" };
    int excelNumber = 0;
    int sheetNumber = 0;

    for (mealCounter = 0; mealCounter < 3; mealCounter++) {
        workbook = new XSSFWorkbook();
        sheetNumber = 0;//from   w  w  w. j  av a  2s.  co m
        for (String currRoute : allRoutes) {

            Map<String, Object[]> data = new TreeMap<>();
            data.put(0 + "", new String[] { "Doorstep Chef - Chef Report " + DriverReport.returnWeekInt(), "",
                    "", "Meal Type : " + list[mealCounter] + " " + " " + "Route: " + sheetNumber });
            data.put(1 + "", new String[] { "", "", "", "", "", "", "" });
            data.put(2 + "", new String[] { "Family Size", "Quantity", "Allergies", "Exclusions" });
            int counter = 3;
            XSSFSheet sheet = workbook.createSheet("ChefReports Route - " + sheetNumber);
            int rowNum = 0;
            int cellNum = 0;
            String familysize = "";

            ArrayList<Meal> mealList = new ArrayList<>();
            boolean hasValue = false;

            for (Order order : orders) {
                for (Meal meal : order.getMeals()) {
                    if (meal.getMealType().equals(list[mealCounter]) && order.getRoute().equals(currRoute)) {
                        mealList.add(meal);
                        hasValue = true;
                    }
                }
            }

            mealList.sort(new Comparator<Meal>() {
                @Override
                public int compare(Meal o1, Meal o2) {
                    if (o1.getQuantity() < o2.getQuantity()) {
                        return -1;
                    } else if (o1.getQuantity() > o2.getQuantity()) {
                        return 1;
                    } else {
                        return 0;
                    }
                }
            });

            if (hasValue) {
                int currQuantity = 0;
                int bulk = 0;
                boolean firstIterate = true;

                for (Meal meal : mealList) {

                    if (meal.getQuantity() != currQuantity) {

                        if (!firstIterate && bulk != 0) {
                            data.put(counter + "",
                                    new String[] { familysize + " Normal *", bulk + "", "", "" });
                            bulk = 0;
                            counter++;
                        }
                        firstIterate = false;

                        switch (meal.getQuantity()) {
                        case 1:
                            familysize = "Single";
                            break;
                        case 2:
                            familysize = "Couple";
                            break;
                        case 3:
                            familysize = "Three";
                            break;
                        case 4:
                            familysize = "Four";
                            break;
                        case 5:
                            familysize = "Five";
                            break;
                        case 6:
                            familysize = "Six";
                            break;
                        default:
                            familysize = "Extra";
                        }
                        currQuantity = meal.getQuantity();
                    }

                    if (meal.getAllergies().equals("-") && meal.getExclusions().equals("-")) {
                        bulk++;
                    } else {
                        data.put(counter + "", new String[] { familysize + " Meal", meal.getQuantity() + "",
                                meal.getAllergies(), meal.getExclusions() });
                        counter++;
                    }

                }
            }

            Set<String> keySet = data.keySet();
            Object[] keys = data.keySet().toArray();
            Arrays.sort(keys);
            ArrayList<Object> keyList = new ArrayList();
            int longestCustomer = 5;
            int totalWidth = 50000;
            boolean isBulk = false;

            for (Object key : keys) {
                keyList.add(data.get(key));
            }

            for (int keyIterate = 0; keyIterate < keySet.size(); keyIterate++) {
                Row row = sheet.createRow(rowNum);
                Object[] arr = data.get(keyIterate + "");

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

                    if ((keyIterate + "").equals("0") || (keyIterate + "").equals("1")) {

                        font.setFontName("Calibri");
                        font.setFontHeightInPoints((short) 13);
                        font.setBold(true);
                        borderStyle.setFont(font);
                        borderStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
                        borderStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
                        borderStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
                        borderStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
                        borderStyle.setAlignment(HorizontalAlignment.LEFT);

                    } else {
                        borderStyle.setBorderBottom(BorderStyle.THIN);
                        borderStyle.setBorderTop(BorderStyle.THIN);
                        borderStyle.setBorderLeft(BorderStyle.THIN);
                        borderStyle.setBorderRight(BorderStyle.THIN);
                        if ((arr[0] + "").contains("*")) {
                            isBulk = true;
                            borderStyle.setBorderBottom(BorderStyle.MEDIUM);
                        }
                    }
                    if ((keyIterate + "").equals("2")) {
                        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 font2 = workbook.createFont();
                        font2.setColor(IndexedColors.WHITE.getIndex());
                        borderStyle.setFont(font2);

                    }
                    cell.setCellStyle(borderStyle);

                }

                rowNum++;
                cellNum++;

            }
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));

            for (int i = 0; i < 5; i++) {
                switch (i) {
                case 1:
                    sheet.setColumnWidth(i, 3000);
                    break;
                case 2:
                    sheet.setColumnWidth(i, 8000);
                    break;
                default:
                    sheet.setColumnWidth(i, 4000);
                    break;
                }

                if (i == 3) {
                    sheet.setColumnWidth(i, 8000);
                }

            }

            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, 3));

            sheetNumber++;

            creatSheet(list[mealCounter], workbook);

            excelNumber++;
            if (excelNumber == allRoutes.size()) {
                if (!(DSC_Main.generateAllReports)) {
                    chefLoadingObj.setVisible(false);
                    chefLoadingObj.dispose();
                    JOptionPane.showMessageDialog(null, "Chef Reports Successfully Generated.");
                }
            }
        }
    }
}

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";
                }// w  w  w  .  j a  v  a  2s .  c  o  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.NewClientReport.java

private static void createExcelReport() {

    XSSFWorkbook workbook = new XSSFWorkbook();
    clients.sort(new Comparator<Client>() {
        @Override//from   ww  w.ja va 2s .co  m
        public int compare(Client o1, Client o2) {
            return (o1.getSurname() + " " + o1.getName()).compareTo(o2.getSurname() + " " + o2.getName());
        }
    });

    XSSFSheet sheet = workbook.createSheet("NewClient Report");

    Map<String, Object[]> data = new TreeMap<>();
    data.put("1", new Object[] { "Doorstep Chef NewClient Sheet", "", "",
            "Week: " + DriverReport.returnWeekInt(), "" });
    data.put("2", new Object[] { "", "", "", "", "" });
    data.put("3", new Object[] { "Customer", "Contact", "DriverName", "R.ID", "Email", "Address" });

    int counter = 4;
    for (Client client : clients) {
        data.put(counter + "", new Object[] { client.getName() + " " + client.getSurname(),
                client.getContactNumber().substring(0, 3) + " " + client.getContactNumber().substring(3, 6)
                        + " " + client.getContactNumber().substring(6, 10),
                client.getAdditionalInfo(), client.getAlternativeNumber(), client.getEmail(),
                client.getAddress() });
        counter++;
    }

    Set<String> keySet = data.keySet();
    int totalSize = 34900;
    int longestCustomer = 0;

    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 != 4) {
                        borderStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
                    } else {
                        borderStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
                    }

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

                }
            } else {
                if (i == 3) {
                    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, 2));
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 5));

    sheet.setColumnWidth(0, (longestCustomer + 2) * 240);
    sheet.setColumnWidth(1, 13 * 240);
    sheet.setColumnWidth(2, 11 * 240);
    sheet.setColumnWidth(3, 5 * 240);

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

    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, 5));

    try {
        workbook.write(excelOut);
        excelOut.close();
        System.out.println("Done - New Client");
        if (!(DSC_Main.generateAllReports)) {
            newClientLoadObj.setVisible(false);
            newClientLoadObj.dispose();
            JOptionPane.showMessageDialog(null, "NewClientReports Succesfully Generated", "Success",
                    JOptionPane.INFORMATION_MESSAGE);
        } else {
            DSC_Main.reportsDone++;
            if (DSC_Main.reportsDone == 5) {
                DSC_Main.reportsDone();
            }
        }

    } catch (IOException io) {
        newClientLoadObj.setVisible(false);
        newClientLoadObj.dispose();
        JOptionPane.showMessageDialog(null, "An error occured\nCould not create NewClientReports", "Error",
                JOptionPane.ERROR_MESSAGE);
        System.err.println("Error - Could not create new NewClientReports: ");
        io.printStackTrace();
    }

}

From source file:DSC.PackerReport.java

private static void createSpreadsheets() {
    orderList.sort(new Comparator<Order>() {
        @Override/* www. j  a  va  2s. c o m*/
        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:DSC.QuantityReport.java

private static void processQuantityReport() {

    int excelNumber = 0;
    int sheetNumber = 0;
    workbook = new XSSFWorkbook();
    Map<String, String[]> data = new TreeMap<>();
    data.put(0 + "", new String[] { "Doorstep Chef - Quantity Report " + currentWeek(), "", "",
            "Week : " + returnWeekInt() + " " + " " });
    data.put(1 + "", new String[] { "", "", "", "", "", "", "" });
    data.put(2 + "", new String[] { "Total Of ", "Single", "Couple", "Three", "Four", "Five", "Six", "Extra" });

    XSSFSheet sheet = workbook.createSheet("Quantity Report " + sheetNumber);
    int rowNum = 0;
    int cellNum = 0;

    for (int i = 3; i < 19; i++) {
        if (i == 3) {
            data.put(i + "",
                    new String[] { "Orders", quantityObj.getCountFamilySize_1() + "",
                            quantityObj.getCountFamilySize_2() + "", quantityObj.getCountFamilySize_3() + "",
                            quantityObj.getCountFamilySize_4() + "", quantityObj.getCountFamilySize_5() + "",
                            quantityObj.getCountFamilySize_6() + "",
                            quantityObj.getCountFamilySizeMoreThanSix() + "" });
        } else if (i == 4) {
            data.put(i + "", new String[] { "", "", "", "", "", "", "", "" });
        } else if (i == 5) {
            data.put(i + "", new String[] { "Meals", quantityObj.getQuantityFamSize1() + "",
                    quantityObj.getQuantityFamSize2() + "", quantityObj.getQuantityFamSize3() + "",
                    quantityObj.getQuantityFamSize4() + "", quantityObj.getQuantityFamSize5() + "",
                    quantityObj.getQuantityFamSize6() + "", quantityObj.getQuantityFamSizeMoreThanSix() + "" });
        } else if (i == 6) {
            data.put(i + "", new String[] { "", "", "", "", "", "", "", "" });
        } else if (i == 7) {
            data.put(i + "", new String[] { "Standard Meals", quantityObj.getCountFamSize1_Standard() + "",
                    quantityObj.getCountFamSize2_Standard() + "", quantityObj.getCountFamSize3_Standard() + "",
                    quantityObj.getCountFamSize4_Standard() + "", quantityObj.getCountFamSize5_Standard() + "",
                    quantityObj.getCountFamSize6_Standard() + "",
                    quantityObj.getCountFamilySizeMoreThanSix_Standard() + "" });
        } else if (i == 8) {
            data.put(i + "",
                    new String[] { "Low Carb Meals", quantityObj.getCountFamSize1_LC() + "",
                            quantityObj.getCountFamSize2_LC() + "", quantityObj.getCountFamSize3_LC() + "",
                            quantityObj.getCountFamSize4_LC() + "", quantityObj.getCountFamSize5_LC() + "",
                            quantityObj.getCountFamSize6_LC() + "",
                            quantityObj.getCountFamilySizeMoreThanSix_LC() + "" });
        } else if (i == 9) {
            data.put(i + "",
                    new String[] { "Kiddies Meals", quantityObj.getCountFamSize1_KD() + "",
                            quantityObj.getCountFamSize2_KD() + "", quantityObj.getCountFamSize3_KD() + "",
                            quantityObj.getCountFamSize4_KD() + "", quantityObj.getCountFamSize5_KD() + "",
                            quantityObj.getCountFamSize6_KD() + "",
                            quantityObj.getCountFamilySizeMoreThanSix_KD() + "" });
        } else if (i == 10) {
            data.put(i + "", new String[] { "", "", "", "", "", "", "", "" });
        } else if (i == 11) {
            data.put(i + "",
                    new String[] { "Totals", quantityObj.totalSingleMeals() + "",
                            quantityObj.totalCoupleMeals() + "", quantityObj.totalThreeMeals() + "",
                            quantityObj.totalFourMeals() + "", quantityObj.totalFiveMeals() + "",
                            quantityObj.totalSixMeals() + "", quantityObj.totalExtraMeals() + "" });
        } else if (i == 12) {
            data.put(i + "", new String[] { "", "", "", "", "", "", "", "" });
        } else if (i == 13) {
            data.put(i + "", new String[] { "Standard Individuals", "", "", "", "", "", "",
                    quantityObj.returnTotalStandardMeals() + "" });
        } else if (i == 14) {
            data.put(i + "", new String[] { "Low Carb Individuals", "", "", "", "", "", "",
                    quantityObj.returnTotalLowCarbMeals() + "" });
        } else if (i == 15) {
            data.put(i + "", new String[] { "Kiddies Individuals", "", "", "", "", "", "",
                    quantityObj.returnTotalKiddiesMeals() + "" });
        } else if (i == 16) {
            data.put(i + "", new String[] { "", "", "", "", "", "", "", "" });
        } else if (i == 17) {
            data.put(i + "", new String[] { "Total Clients", "", "", "", "", "", "",
                    quantityObj.returnTotalClients() + "" });
        } else if (i == 18) {

            int totalIndividuals = quantityObj.returnTotalStandardMeals()
                    + quantityObj.returnTotalLowCarbMeals() + quantityObj.returnTotalKiddiesMeals();

            data.put(i + "",
                    new String[] { "Total Individuals", "", "", "", "", "", "", totalIndividuals + "" });
        }//  w ww.  jav a 2  s.c om
    }

    Set<String> keySet = data.keySet();

    for (int keyIterate = 0; keyIterate < keySet.size(); keyIterate++) {
        Row row = sheet.createRow(rowNum);
        Object[] arr = data.get(keyIterate + "");
        XSSFCellStyle borderStyle = workbook.createCellStyle();
        XSSFCellStyle borderStyle2 = workbook.createCellStyle();
        for (int i = 0; i < arr.length; i++) {
            XSSFFont font = workbook.createFont();
            Cell cell = row.createCell(i);
            cell.setCellValue((String) arr[i]);

            if ((keyIterate + "").equals("0") || (keyIterate + "").equals("1")) {

                font.setFontName("Calibri");
                font.setFontHeightInPoints((short) 13);
                font.setBold(true);
                borderStyle.setFont(font);
                borderStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
                borderStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
                borderStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
                borderStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
                borderStyle.setAlignment(HorizontalAlignment.LEFT);

            } else if (keyIterate > 1 && keyIterate < 12) {
                borderStyle.setBorderBottom(BorderStyle.THIN);
                borderStyle.setBorderTop(BorderStyle.THIN);
                borderStyle.setBorderLeft(BorderStyle.THIN);
                borderStyle.setBorderRight(BorderStyle.THIN);

            }

            if ((keyIterate + "").equals("2")) {
                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 font2 = workbook.createFont();
                font2.setColor(IndexedColors.WHITE.getIndex());
                borderStyle.setFont(font2);

            }

            cell.setCellStyle(borderStyle);
        }

        rowNum++;
        cellNum++;
        sheetNumber++;
    }

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

    for (int i = 0; i < 8; i++) {
        if (i == 0) {
            sheet.setColumnWidth(i, 5000);
        } else {
            sheet.setColumnWidth(i, 2000);

        }

    }

    try {
        creatSheet(sheetNumber + "", workbook);
    } catch (IOException ex) {
        JOptionPane.showMessageDialog(null, "File Could Not Be Found.");
    }

    excelNumber++;

}

From source file:in.expertsoftware.colorcheck.FormatvarificationErrorList.java

private static void CreaateHeaderOfErrorList(XSSFWorkbook ErrorWorkbook, Cell column, String text) {
    XSSFCellStyle headerStyleOfreference = ErrorWorkbook.createCellStyle();
    headerStyleOfreference.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    headerStyleOfreference.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    headerStyleOfreference.setFillForegroundColor(new XSSFColor(new java.awt.Color(217, 217, 217)));
    headerStyleOfreference.setBorderBottom((short) 1);
    headerStyleOfreference.setBorderTop((short) 1);
    headerStyleOfreference.setBorderLeft((short) 1);
    headerStyleOfreference.setBorderRight((short) 1);

    //create font
    XSSFFont fontOfCellFirst = ErrorWorkbook.createFont();
    fontOfCellFirst.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
    fontOfCellFirst.setFontHeightInPoints((short) 12);
    fontOfCellFirst.setFontName("Calibri");
    fontOfCellFirst.setColor(new XSSFColor(new java.awt.Color(0, 0, 0)));
    headerStyleOfreference.setFont(fontOfCellFirst);
    column.setCellValue(text);// w  ww . j  a v a 2  s  . c o  m
    column.setCellStyle(headerStyleOfreference);
}

From source file:in.expertsoftware.colorcheck.FormatvarificationErrorList.java

private static void CreaateStyleOfErrorList(XSSFWorkbook ErrorWorkbook, XSSFRow row, String cell_ref,
        String sheet_name, String error_desc, String error_level) {
    XSSFCellStyle StyleOfCell = ErrorWorkbook.createCellStyle();
    StyleOfCell.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    StyleOfCell.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    if (error_level.equalsIgnoreCase("Warning")) {
        StyleOfCell.setFillForegroundColor(new XSSFColor(new java.awt.Color(155, 194, 230)));
    } else {//from   w  w  w . ja v a 2s . c  o  m
        StyleOfCell.setFillForegroundColor(new XSSFColor(new java.awt.Color(225, 171, 171)));
    }
    StyleOfCell.setBorderLeft((short) 1);
    StyleOfCell.setBorderRight((short) 1);
    StyleOfCell.setBorderTop((short) 1);
    StyleOfCell.setBorderBottom((short) 1);
    StyleOfCell.setWrapText(true);

    //create font
    XSSFFont fontOfCell = ErrorWorkbook.createFont();
    fontOfCell.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
    fontOfCell.setFontHeightInPoints((short) 10);
    fontOfCell.setFontName("Calibri");
    fontOfCell.setColor(new XSSFColor(new java.awt.Color(0, 0, 0)));
    StyleOfCell.setFont(fontOfCell);
    Cell Rowcell_0 = row.createCell(0);
    Cell Rowcell_1 = row.createCell(1);
    Cell Rowcell_2 = row.createCell(2);
    Cell Rowcell_3 = row.createCell(3);
    Rowcell_0.setCellValue(cell_ref);
    Rowcell_1.setCellValue(sheet_name);
    Rowcell_2.setCellValue(error_desc);
    Rowcell_3.setCellValue(error_level);
    Rowcell_0.setCellStyle(StyleOfCell);
    Rowcell_1.setCellStyle(StyleOfCell);
    Rowcell_2.setCellStyle(StyleOfCell);
    Rowcell_3.setCellStyle(StyleOfCell);
}

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   w  w w . j  a va  2  s .  c om*/
    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;
}

From source file:nc.noumea.mairie.appock.util.StockSpreadsheetExporter.java

License:Open Source License

private static int generateHeader(XSSFSheet worksheet, XSSFWorkbook workbook, int rowNum) {
    // Now add// www.  j  av  a 2s .  c  o  m
    XSSFRow row = worksheet.createRow(rowNum);
    XSSFCell cell;

    XSSFCellStyle headerStyle = workbook.createCellStyle();
    headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.index);
    headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    headerStyle.setBorderBottom(BorderStyle.MEDIUM);
    headerStyle.setBorderLeft(BorderStyle.MEDIUM);
    headerStyle.setBorderRight(BorderStyle.MEDIUM);
    headerStyle.setBorderTop(BorderStyle.MEDIUM);
    headerStyle.setAlignment(HorizontalAlignment.CENTER);
    headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);

    XSSFFont txtFont = workbook.createFont();
    txtFont.setFontName("calibri");
    txtFont.setFontHeightInPoints((short) 9);
    txtFont.setBold(true);
    headerStyle.setFont(txtFont);

    cell = row.createCell(0);
    cell.setCellValue("Photo");
    cell.setCellStyle(headerStyle);
    worksheet.setColumnWidth(0, ConvertImageUnits.pixel2WidthUnits(COLUMN_WIDTH_PX));//4387

    cell = row.createCell(1);
    cell.setCellValue("Rfrence");
    cell.setCellStyle(headerStyle);

    cell = row.createCell(2);
    cell.setCellValue("Libell");
    cell.setCellStyle(headerStyle);

    cell = row.createCell(3);
    cell.setCellValue("Stock\n Appock");
    cell.setCellStyle(headerStyle);
    cell.getCellStyle().setWrapText(true);

    cell = row.createCell(4);
    cell.setCellValue("Stock\n rel");
    cell.setCellStyle(headerStyle);
    cell.getCellStyle().setWrapText(true);

    row.setHeight((short) 600);

    return rowNum + 1;
}