Example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress

List of usage examples for org.apache.poi.ss.util CellRangeAddress CellRangeAddress

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.util.*
  5. CellRangeAddress
  6. CellRangeAddress

Introduction

In this page you can find the example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress.

Prototype

public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)

Source Link

Document

Creates new cell range.

Usage

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  av  a2s  . 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  w w w  .  j a v a 2 s. c om
        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//from  w  w w .ja  v  a2s.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: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  w  w. j  a  v 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:es.upm.oeg.tools.rdfshapes.utils.CadinalityResultGenerator.java

License:Apache License

public static void main(String[] args) throws Exception {

    String endpoint = "http://3cixty.eurecom.fr/sparql";

    List<String> classList = Files.readAllLines(Paths.get(classListPath), Charset.defaultCharset());

    String classPropertyQueryString = readFile(classPropertyQueryPath, Charset.defaultCharset());
    String propertyCardinalityQueryString = readFile(propertyCardinalityQueryPath, Charset.defaultCharset());
    String individualCountQueryString = readFile(individualCountQueryPath, Charset.defaultCharset());

    DecimalFormat df = new DecimalFormat("0.0000");

    //Create the Excel workbook and sheet
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet("Cardinality");

    int currentExcelRow = 0;
    int classStartRow = 0;

    for (String clazz : classList) {

        Map<String, String> litMap = new HashMap<>();
        Map<String, String> iriMap = ImmutableMap.of("class", clazz);

        String queryString = bindQueryString(individualCountQueryString,
                ImmutableMap.of(IRI_BINDINGS, iriMap, LITERAL_BINDINGS, litMap));

        int individualCount;
        List<RDFNode> c = executeQueryForList(queryString, endpoint, "c");
        if (c.size() == 1) {
            individualCount = c.get(0).asLiteral().getInt();
        } else {//from  w w  w  .  j a v a  2 s  . c  o  m
            continue;
        }

        // If there are zero individuals, continue
        if (individualCount == 0) {
            throw new IllegalStateException("Check whether " + classListPath + " and " + endpoint + " match.");
        }

        //            System.out.println("***");
        //            System.out.println("### **" + clazz + "** (" + individualCount + ")");
        //            System.out.println("***");
        //            System.out.println();

        classStartRow = currentExcelRow;
        XSSFRow row = sheet.createRow(currentExcelRow);
        XSSFCell cell = row.createCell(0);
        cell.setCellValue(clazz);
        cell.getCellStyle().setAlignment(CellStyle.ALIGN_CENTER);

        queryString = bindQueryString(classPropertyQueryString,
                ImmutableMap.of(IRI_BINDINGS, iriMap, LITERAL_BINDINGS, litMap));

        List<RDFNode> nodeList = executeQueryForList(queryString, endpoint, "p");

        for (RDFNode property : nodeList) {
            if (property.isURIResource()) {

                DescriptiveStatistics stats = new DescriptiveStatistics();

                String propertyURI = property.asResource().getURI();
                //                    System.out.println("* " + propertyURI);
                //                    System.out.println();

                XSSFRow propertyRow = sheet.getRow(currentExcelRow);
                if (propertyRow == null) {
                    propertyRow = sheet.createRow(currentExcelRow);
                }
                currentExcelRow++;

                XSSFCell propertyCell = propertyRow.createCell(1);
                propertyCell.setCellValue(propertyURI);

                Map<String, String> litMap2 = new HashMap<>();
                Map<String, String> iriMap2 = ImmutableMap.of("class", clazz, "p", propertyURI);

                queryString = bindQueryString(propertyCardinalityQueryString,
                        ImmutableMap.of(IRI_BINDINGS, iriMap2, LITERAL_BINDINGS, litMap2));

                List<Map<String, RDFNode>> solnMaps = executeQueryForList(queryString, endpoint,
                        ImmutableSet.of("card", "count"));

                int sum = 0;
                List<CardinalityCount> cardinalityList = new ArrayList<>();
                if (solnMaps.size() > 0) {

                    for (Map<String, RDFNode> soln : solnMaps) {
                        int count = soln.get("count").asLiteral().getInt();
                        int card = soln.get("card").asLiteral().getInt();

                        for (int i = 0; i < count; i++) {
                            stats.addValue(card);
                        }

                        CardinalityCount cardinalityCount = new CardinalityCount(card, count,
                                (((double) count) / individualCount) * 100);
                        cardinalityList.add(cardinalityCount);
                        sum += count;
                    }

                    // Check for zero cardinality instances
                    int count = individualCount - sum;
                    if (count > 0) {
                        for (int i = 0; i < count; i++) {
                            stats.addValue(0);
                        }
                        CardinalityCount cardinalityCount = new CardinalityCount(0, count,
                                (((double) count) / individualCount) * 100);
                        cardinalityList.add(cardinalityCount);
                    }
                }

                Map<Integer, Double> cardMap = new HashMap<>();
                for (CardinalityCount count : cardinalityList) {
                    cardMap.put(count.getCardinality(), count.getPrecentage());
                }

                XSSFCell instanceCountCell = propertyRow.createCell(2);
                instanceCountCell.setCellValue(individualCount);

                XSSFCell minCell = propertyRow.createCell(3);
                minCell.setCellValue(stats.getMin());

                XSSFCell maxCell = propertyRow.createCell(4);
                maxCell.setCellValue(stats.getMax());

                XSSFCell p1 = propertyRow.createCell(5);
                p1.setCellValue(stats.getPercentile(1));

                XSSFCell p99 = propertyRow.createCell(6);
                p99.setCellValue(stats.getPercentile(99));

                XSSFCell mean = propertyRow.createCell(7);
                mean.setCellValue(df.format(stats.getMean()));

                for (int i = 0; i < 21; i++) {
                    XSSFCell dataCell = propertyRow.createCell(8 + i);
                    Double percentage = cardMap.get(i);
                    if (percentage != null) {
                        dataCell.setCellValue(df.format(percentage));
                    } else {
                        dataCell.setCellValue(0);
                    }
                }

                //                    System.out.println("| Min Card. |Max Card. |");
                //                    System.out.println("|---|---|");
                //                    System.out.println("| ? | ? |");
                //                    System.out.println();

            }
        }

        //System.out.println("class start: " + classStartRow + ", class end: " + (currentExcelRow -1));
        //We have finished writting properties of one class, now it's time to merge the cells
        int classEndRow = currentExcelRow - 1;
        if (classStartRow < classEndRow) {
            sheet.addMergedRegion(new CellRangeAddress(classStartRow, classEndRow, 0, 0));
        }

    }

    String filename = "3cixty.xls";
    FileOutputStream fileOut = new FileOutputStream(filename);
    wb.write(fileOut);
    fileOut.close();
}

From source file:es.upm.oeg.tools.rdfshapes.utils.CardinalityTemplateGenerator.java

License:Apache License

public static void main(String[] args) throws Exception {

    OntModel model = ModelFactory.createOntologyModel(OntModelSpec.OWL_MEM_RULE_INF,
            ModelFactory.createDefaultModel());
    model.read("http://dublincore.org/2012/06/14/dcelements.ttl");

    String endpoint = "http://infra2.dia.fi.upm.es:8899/sparql";

    List<String> classList = Files.readAllLines(Paths.get(classListPath), Charset.defaultCharset());

    String classPropertyQueryString = readFile(classPropertyQueryPath, Charset.defaultCharset());
    String propertyCardinalityQueryString = readFile(propertyCardinalityQueryPath, Charset.defaultCharset());
    String individualCountQueryString = readFile(individualCountQueryPath, Charset.defaultCharset());

    //Create the Excel workbook and sheet
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet("Cardinality");

    int currentExcelRow = 0;
    int classStartRow = 0;

    for (String clazz : classList) {

        Map<String, String> litMap = new HashMap<>();
        Map<String, String> iriMap = ImmutableMap.of("class", clazz);

        String queryString = bindQueryString(individualCountQueryString,
                ImmutableMap.of(IRI_BINDINGS, iriMap, LITERAL_BINDINGS, litMap));

        int individualCount;
        List<RDFNode> c = executeQueryForList(queryString, endpoint, "c");
        if (c.size() == 1) {
            individualCount = c.get(0).asLiteral().getInt();
        } else {//from   w ww .ja va 2 s  .co m
            continue;
        }

        // If there are zero individuals, continue
        if (individualCount == 0) {
            throw new IllegalStateException("Check whether " + classListPath + " and " + endpoint + " match.");
        }

        //            System.out.println("***");
        //            System.out.println("### **" + clazz + "** (" + individualCount + ")");
        //            System.out.println("***");
        //            System.out.println();

        classStartRow = currentExcelRow;
        XSSFRow row = sheet.createRow(currentExcelRow);
        XSSFCell cell = row.createCell(0);
        cell.setCellValue(clazz);
        cell.getCellStyle().setAlignment(CellStyle.ALIGN_CENTER);

        queryString = bindQueryString(classPropertyQueryString,
                ImmutableMap.of(IRI_BINDINGS, iriMap, LITERAL_BINDINGS, litMap));

        List<RDFNode> nodeList = executeQueryForList(queryString, endpoint, "p");

        for (RDFNode property : nodeList) {
            if (property.isURIResource()) {

                String propertyURI = property.asResource().getURI();
                //                    System.out.println("* " + propertyURI);
                //                    System.out.println();

                XSSFRow propertyRow = sheet.getRow(currentExcelRow);
                if (propertyRow == null) {
                    propertyRow = sheet.createRow(currentExcelRow);
                }
                currentExcelRow++;

                XSSFCell propertyCell = propertyRow.createCell(1);
                propertyCell.setCellValue(propertyURI);

                //                    System.out.println("| Min Card. |Max Card. |");
                //                    System.out.println("|---|---|");
                //                    System.out.println("| ? | ? |");
                //                    System.out.println();

            }
        }

        //System.out.println("class start: " + classStartRow + ", class end: " + (currentExcelRow -1));
        //We have finished writting properties of one class, now it's time to merge the cells
        int classEndRow = currentExcelRow - 1;
        if (classStartRow < classEndRow) {
            sheet.addMergedRegion(new CellRangeAddress(classStartRow, classEndRow, 0, 0));
        }

    }

    String filename = "test.xls";
    FileOutputStream fileOut = new FileOutputStream(filename);
    wb.write(fileOut);
    fileOut.close();

}

From source file:excel.FileExcel.java

public File excel_create_katalog_update(ArrayList<UpdKatalog> newKatalogUpdate) {
    if (newKatalogUpdate.size() != 0) {
        DateFormat time = new SimpleDateFormat("hhmm");
        String fileName = "KatalogDiff_" + fmt.format(newKatalogUpdate.get(0).update_date_new) + "_"
                + time.format(newKatalogUpdate.get(0).update_date_new) + ".xls";
        File ExcelKatalogDiff = new File(fileName);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

        // set page
        sheet.getPrintSetup().setLandscape(true);

        //Set Header Information 
        Header headerPage = sheet.getHeader();
        headerPage.setCenter(HeaderFooter.page());
        headerPage.setRight(fileName);/*w  ww . j  a  va 2s  .  c  om*/

        //Set Footer Information with Page Numbers
        Footer footerPage = sheet.getFooter();
        footerPage.setCenter("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages());

        // prepare variable to edit the xls
        HSSFRow header;
        HSSFCell cell;
        HSSFCellStyle titlestyle = workbook.createCellStyle();
        HSSFCellStyle headerstyle = workbook.createCellStyle();
        HSSFCellStyle datastyle = workbook.createCellStyle();
        HSSFFont boldfont = workbook.createFont();
        HSSFFont normalfont = workbook.createFont();

        // create the title 
        header = sheet.createRow(1);
        cell = header.createCell(1);
        boldfont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titlestyle.setFont(boldfont);
        titlestyle.setAlignment(CellStyle.ALIGN_CENTER);
        titlestyle.setBorderTop(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderBottom(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderLeft(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderRight(HSSFCellStyle.BORDER_NONE);
        cell.setCellStyle(titlestyle);
        cell.setCellValue("TABEL PERUBAHAN HARGA");
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 5));

        // create file info
        header = sheet.createRow(3);
        cell = header.createCell(2);
        cell.setCellValue("Tanggal Update : ");
        cell = header.createCell(3);
        cell.setCellValue(fmt.format(newKatalogUpdate.get(0).update_date_new));

        // create the header
        datastyle.setFont(boldfont);
        headerstyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        header = sheet.createRow(6);
        cell = header.createCell(1);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Kode Barang");
        cell = header.createCell(2);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Harga TPG Lama");
        cell = header.createCell(3);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Tanggal Update Lama");
        cell = header.createCell(4);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Harga TPG Baru");
        cell = header.createCell(5);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Tanggal Update Baru");
        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);
        sheet.autoSizeColumn(3);
        sheet.autoSizeColumn(4);
        sheet.autoSizeColumn(5);

        normalfont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
        datastyle.setFont(normalfont);
        datastyle.setAlignment(CellStyle.ALIGN_RIGHT);
        datastyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

        for (int i = 0; i < newKatalogUpdate.size(); i++) {
            header = sheet.createRow(i + 7);
            cell = header.createCell(1);
            cell.setCellStyle(datastyle);
            cell.setCellValue(newKatalogUpdate.get(i).kode_barang);
            cell = header.createCell(2);
            cell.setCellStyle(datastyle);
            cell.setCellValue(newKatalogUpdate.get(i).harga_tpg_old);
            cell = header.createCell(3);
            cell.setCellStyle(datastyle);
            cell.setCellValue(fmt.format(newKatalogUpdate.get(i).update_date_old));
            cell = header.createCell(4);
            cell.setCellStyle(datastyle);
            cell.setCellValue(newKatalogUpdate.get(i).harga_tpg_new);
            cell = header.createCell(5);
            cell.setCellStyle(datastyle);
            cell.setCellValue(fmt.format(newKatalogUpdate.get(i).update_date_new));
        }
        try {
            // String pathname = "D:\\Document\\Dropbox\\sophie\\DB\\update_" + fmt.format(newKatalogUpdate.get(0).update_date_new) + ".xls";
            FileOutputStream out = new FileOutputStream(ExcelKatalogDiff);
            workbook.write(out);
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            ExcelKatalogDiff = null;
        } catch (IOException e) {
            e.printStackTrace();
            ExcelKatalogDiff = null;
        }
        return ExcelKatalogDiff;
    } else
        return null;
}

From source file:excel.FileExcel.java

public File excel_create_diff_upd_stock(ArrayList<DiffUpdStock> diffinput, Date currtime) {
    if (diffinput.size() != 0) {
        DateFormat time = new SimpleDateFormat("hhmm");
        String fileName = "DailyDiff_" + fmt.format(currtime) + "_" + time.format(currtime) + ".xls";
        File ExcelDailyDiff = new File(fileName);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

        // set page
        sheet.getPrintSetup().setLandscape(false);

        //Set Header Information 
        Header headerPage = sheet.getHeader();
        headerPage.setCenter(HeaderFooter.page());
        headerPage.setRight(fileName);/*  ww  w. j  a  v  a 2s. c o m*/

        //Set Footer Information with Page Numbers
        Footer footerPage = sheet.getFooter();
        footerPage.setCenter("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages());

        // prepare variable to edit the xls
        HSSFRow header;
        HSSFCell cell;
        HSSFCellStyle titlestyle = workbook.createCellStyle();
        HSSFCellStyle headerstyle = workbook.createCellStyle();
        HSSFCellStyle datastyle = workbook.createCellStyle();
        HSSFFont boldfont = workbook.createFont();
        HSSFFont normalfont = workbook.createFont();

        // create the title 
        header = sheet.createRow(1);
        cell = header.createCell(1);
        boldfont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titlestyle.setFont(boldfont);
        titlestyle.setAlignment(CellStyle.ALIGN_CENTER);
        titlestyle.setBorderTop(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderBottom(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderLeft(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderRight(HSSFCellStyle.BORDER_NONE);
        cell.setCellStyle(titlestyle);
        cell.setCellValue("TABEL UPDATE DAILY STOK");
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 4));

        // create file info
        header = sheet.createRow(3);
        cell = header.createCell(2);
        cell.setCellValue("Tanggal Stock : ");
        cell = header.createCell(3);
        cell.setCellValue(currtime);

        // create the header
        headerstyle.setFont(boldfont);
        headerstyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        header = sheet.createRow(6);
        cell = header.createCell(1);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Unedited Kode Barang");
        cell = header.createCell(2);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Unedited Jumlah Barang");
        cell = header.createCell(3);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Edited Kode Barang");
        cell = header.createCell(4);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Edited Jumlah Barang");
        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);
        sheet.autoSizeColumn(3);
        sheet.autoSizeColumn(4);

        normalfont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
        datastyle.setFont(normalfont);
        datastyle.setAlignment(CellStyle.ALIGN_RIGHT);
        datastyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

        for (int i = 0; i < diffinput.size(); i++) {
            header = sheet.createRow(i + 7);
            cell = header.createCell(1);
            cell.setCellStyle(datastyle);
            cell.setCellValue(diffinput.get(i).oriKodeBarang);
            cell = header.createCell(2);
            cell.setCellStyle(datastyle);
            cell.setCellValue(diffinput.get(i).oriJumlahBarang);
            cell = header.createCell(3);
            cell.setCellStyle(datastyle);
            cell.setCellValue(diffinput.get(i).edtKodeBarang);
            cell = header.createCell(4);
            cell.setCellStyle(datastyle);
            cell.setCellValue(diffinput.get(i).oriJumlahBarang);

        }
        try {
            // String pathname = "D:\\Document\\Dropbox\\sophie\\DB\\update_" + fmt.format(newKatalogUpdate.get(0).update_date_new) + ".xls";
            FileOutputStream out = new FileOutputStream(ExcelDailyDiff);
            workbook.write(out);
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            ExcelDailyDiff = null;
        } catch (IOException e) {
            e.printStackTrace();
            ExcelDailyDiff = null;
        }
        return ExcelDailyDiff;
    }
    return null;
}

From source file:excel.FileExcel.java

public File excel_create_order_pusat_coming(ArrayList<ComingOrderReportData> comingorder,
        Date comingorderdate) {/*from w  ww.j  a  va  2s.  co  m*/
    if (comingorder.size() != 0) {
        DateFormat time = new SimpleDateFormat("hhmm");
        String fileName = "ComingOrder_" + fmt.format(comingorderdate) + "_" + time.format(comingorderdate)
                + ".xls";
        File ExcelComingOrder = new File(fileName);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

        // set page
        sheet.getPrintSetup().setLandscape(false);

        //Set Header Information 
        Header headerPage = sheet.getHeader();
        headerPage.setCenter(HeaderFooter.page());
        headerPage.setRight(fileName);

        //Set Footer Information with Page Numbers
        Footer footerPage = sheet.getFooter();
        footerPage.setCenter("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages());

        // prepare variable to edit the xls
        HSSFRow header;
        HSSFCell cell;
        HSSFCellStyle titlestyle = workbook.createCellStyle();
        HSSFCellStyle headerstyle = workbook.createCellStyle();
        HSSFCellStyle datastyle = workbook.createCellStyle();
        HSSFFont boldfont = workbook.createFont();
        HSSFFont normalfont = workbook.createFont();

        // create the title 
        header = sheet.createRow(1);
        cell = header.createCell(1);
        boldfont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titlestyle.setFont(boldfont);
        titlestyle.setAlignment(CellStyle.ALIGN_CENTER);
        titlestyle.setBorderTop(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderBottom(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderLeft(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderRight(HSSFCellStyle.BORDER_NONE);
        cell.setCellStyle(titlestyle);
        cell.setCellValue("TABEL COMING ORDER");
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 5));

        // create file info
        header = sheet.createRow(3);
        cell = header.createCell(2);
        cell.setCellValue("Tanggal Stock : ");
        cell = header.createCell(3);
        cell.setCellValue(fmt.format(comingorderdate));

        // create the header
        headerstyle.setFont(boldfont);
        headerstyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        header = sheet.createRow(6);
        cell = header.createCell(1);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Kode Konter");
        cell = header.createCell(2);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Kode Barang");
        cell = header.createCell(3);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Jumlah");
        cell = header.createCell(4);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Nama Barang");
        cell = header.createCell(5);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Kategori");

        normalfont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
        datastyle.setFont(normalfont);
        datastyle.setAlignment(CellStyle.ALIGN_RIGHT);
        datastyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

        // Sorting
        Collections.sort(comingorder, new Comparator<ComingOrderReportData>() {
            @Override
            public int compare(ComingOrderReportData data2, ComingOrderReportData data1) {
                return data1.nama_barang.compareTo(data2.nama_barang);
            }
        });

        for (int i = 0; i < comingorder.size(); i++) {
            header = sheet.createRow(i + 7);
            cell = header.createCell(1);
            cell.setCellStyle(datastyle);
            cell.setCellValue(comingorder.get(i).kode_konter);
            cell = header.createCell(2);
            cell.setCellStyle(datastyle);
            cell.setCellValue(comingorder.get(i).kode_barang);
            cell = header.createCell(3);
            cell.setCellStyle(datastyle);
            cell.setCellValue(comingorder.get(i).jumlah_barang);
            cell = header.createCell(4);
            cell.setCellStyle(datastyle);
            cell.setCellValue(comingorder.get(i).nama_barang);
            cell = header.createCell(5);
            cell.setCellStyle(datastyle);
            cell.setCellValue(comingorder.get(i).kategori);

        }

        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);
        sheet.autoSizeColumn(3);
        sheet.autoSizeColumn(4);
        sheet.autoSizeColumn(5);

        try {
            // String pathname = "D:\\Document\\Dropbox\\sophie\\DB\\update_" + fmt.format(newKatalogUpdate.get(0).update_date_new) + ".xls";
            FileOutputStream out = new FileOutputStream(ExcelComingOrder);
            workbook.write(out);
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            ExcelComingOrder = null;
        } catch (IOException e) {
            e.printStackTrace();
            ExcelComingOrder = null;
        }
        return ExcelComingOrder;
    } else {
        return null;
    }
}

From source file:excel.FileExcel.java

public File excel_create_popular(ArrayList<PopulerData> PopulerItem, Date startdate, Date enddate) {
    if (PopulerItem.size() != 0) {
        String fileName = "PopularItem_" + fmt.format(startdate) + "-" + fmt.format(enddate) + ".xls";
        File ExcelPopular = new File(fileName);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

        // set page
        sheet.getPrintSetup().setLandscape(false);

        //Set Header Information 
        Header headerPage = sheet.getHeader();
        headerPage.setCenter(HeaderFooter.page());
        headerPage.setRight(fileName);/*from  w w  w.ja  v  a  2s .co  m*/

        //Set Footer Information with Page Numbers
        Footer footerPage = sheet.getFooter();
        footerPage.setCenter("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages());

        // prepare variable to edit the xls
        HSSFRow header;
        HSSFCell cell;
        HSSFCellStyle titlestyle = workbook.createCellStyle();
        HSSFCellStyle headerstyle = workbook.createCellStyle();
        HSSFCellStyle datastyle = workbook.createCellStyle();
        HSSFFont boldfont = workbook.createFont();
        HSSFFont normalfont = workbook.createFont();

        // create the title 
        header = sheet.createRow(1);
        cell = header.createCell(1);
        boldfont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titlestyle.setFont(boldfont);
        titlestyle.setAlignment(CellStyle.ALIGN_CENTER);
        titlestyle.setBorderTop(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderBottom(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderLeft(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderRight(HSSFCellStyle.BORDER_NONE);
        cell.setCellStyle(titlestyle);
        cell.setCellValue("TABEL ORDER POPULAR");
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 4));

        // create file info
        header = sheet.createRow(3);
        cell = header.createCell(1);
        cell.setCellValue("Tanggal : ");
        cell = header.createCell(2);
        cell.setCellValue(fmt.format(startdate));
        cell = header.createCell(3);
        cell.setCellValue(" - ");
        cell = header.createCell(4);
        cell.setCellValue(fmt.format(enddate));

        // create the header
        headerstyle.setFont(boldfont);
        headerstyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        header = sheet.createRow(6);
        cell = header.createCell(1);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Kode Barang");
        cell = header.createCell(2);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Jumlah Order");
        cell = header.createCell(3);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Jumlah Konter");
        cell = header.createCell(4);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Stok");
        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);
        sheet.autoSizeColumn(3);
        sheet.autoSizeColumn(4);

        normalfont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
        datastyle.setFont(normalfont);
        datastyle.setAlignment(CellStyle.ALIGN_RIGHT);
        datastyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

        for (int i = 0; i < PopulerItem.size(); i++) {
            header = sheet.createRow(i + 7);
            cell = header.createCell(1);
            cell.setCellStyle(datastyle);
            cell.setCellValue(PopulerItem.get(i).kode_barang);
            cell = header.createCell(2);
            cell.setCellStyle(datastyle);
            cell.setCellValue(PopulerItem.get(i).jumlah_order);
            cell = header.createCell(3);
            cell.setCellStyle(datastyle);
            cell.setCellValue(PopulerItem.get(i).jumlah_konter);
            cell = header.createCell(4);
            cell.setCellStyle(datastyle);
            cell.setCellValue(PopulerItem.get(i).stock);
        }

        try {
            FileOutputStream out = new FileOutputStream(ExcelPopular);
            workbook.write(out);
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            ExcelPopular = null;
        } catch (IOException e) {
            e.printStackTrace();
            ExcelPopular = null;
        }
        return ExcelPopular;
    } else {
        return null;
    }
}