Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook createSheet

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook createSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. createSheet

Introduction

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

Prototype

@Override
public XSSFSheet createSheet(String sheetname)

Source Link

Document

Create a new sheet for this Workbook and return the high level representation.

Usage

From source file:de.symeda.sormas.api.doc.DataDictionaryGenerator.java

License:Open Source License

@SuppressWarnings("unchecked")
private XSSFSheet createEntitySheet(XSSFWorkbook workbook, Class<? extends EntityDto> entityClass,
        String i18nPrefix) {//from ww w  .  jav  a  2s  .c  o  m
    String name = I18nProperties.getCaption(i18nPrefix);
    String safeName = WorkbookUtil.createSafeSheetName(name);
    XSSFSheet sheet = workbook.createSheet(safeName);

    // Create
    XSSFTable table = sheet.createTable();
    String safeTableName = safeName.replaceAll("\\s", "_");
    table.setName(safeTableName);
    table.setDisplayName(safeTableName);

    XssfHelper.styleTable(table, 1);

    int columnCount = EntityColumn.values().length;
    int rowNumber = 0;
    // header
    XSSFRow headerRow = sheet.createRow(rowNumber++);
    for (EntityColumn column : EntityColumn.values()) {
        table.addColumn();
        String columnCaption = column.toString();
        columnCaption = columnCaption.substring(0, 1) + columnCaption.substring(1).toLowerCase();
        headerRow.createCell(column.ordinal()).setCellValue(columnCaption);
    }

    // column width
    sheet.setColumnWidth(EntityColumn.FIELD.ordinal(), 256 * 30);
    sheet.setColumnWidth(EntityColumn.TYPE.ordinal(), 256 * 30);
    sheet.setColumnWidth(EntityColumn.CAPTION.ordinal(), 256 * 30);
    sheet.setColumnWidth(EntityColumn.DESCRIPTION.ordinal(), 256 * 60);
    sheet.setColumnWidth(EntityColumn.REQUIRED.ordinal(), 256 * 10);
    sheet.setColumnWidth(EntityColumn.DISEASES.ordinal(), 256 * 45);
    sheet.setColumnWidth(EntityColumn.OUTBREAKS.ordinal(), 256 * 10);

    CellStyle defaultCellStyle = workbook.createCellStyle();
    defaultCellStyle.setWrapText(true);

    List<Class<Enum<?>>> usedEnums = new ArrayList<Class<Enum<?>>>();

    for (Field field : entityClass.getDeclaredFields()) {
        if (java.lang.reflect.Modifier.isStatic(field.getModifiers()))
            continue;
        XSSFRow row = sheet.createRow(rowNumber++);

        // field name
        XSSFCell fieldNameCell = row.createCell(EntityColumn.FIELD.ordinal());
        fieldNameCell.setCellValue(field.getName());

        // value range
        XSSFCell fieldValueCell = row.createCell(EntityColumn.TYPE.ordinal());
        fieldValueCell.setCellStyle(defaultCellStyle);
        Class<?> fieldType = field.getType();
        if (fieldType.isEnum()) {
            // use enum type name - values are added below
            //            Object[] enumValues = fieldType.getEnumConstants();
            //            StringBuilder valuesString = new StringBuilder();
            //            for (Object enumValue : enumValues) {
            //               if (valuesString.length() > 0)
            //                  valuesString.append(", ");
            //               valuesString.append(((Enum) enumValue).name());
            //            }
            //            fieldValueCell.setCellValue(valuesString.toString());
            fieldValueCell.setCellValue(fieldType.getSimpleName());
            if (!usedEnums.contains(fieldType)) {
                usedEnums.add((Class<Enum<?>>) fieldType);
            }
        } else if (EntityDto.class.isAssignableFrom(fieldType)) {
            fieldValueCell.setCellValue(fieldType.getSimpleName().replaceAll("Dto", ""));
        } else if (ReferenceDto.class.isAssignableFrom(fieldType)) {
            fieldValueCell.setCellValue(fieldType.getSimpleName().replaceAll("Dto", ""));
        } else if (String.class.isAssignableFrom(fieldType)) {
            fieldValueCell.setCellValue(I18nProperties.getCaption("text"));
        } else if (Date.class.isAssignableFrom(fieldType)) {
            fieldValueCell.setCellValue(I18nProperties.getCaption("date"));
        } else if (Number.class.isAssignableFrom(fieldType)) {
            fieldValueCell.setCellValue(I18nProperties.getCaption("number"));
        } else if (Boolean.class.isAssignableFrom(fieldType) || boolean.class.isAssignableFrom(fieldType)) {
            fieldValueCell.setCellValue(Boolean.TRUE.toString() + ", " + Boolean.FALSE.toString());
        }

        // caption
        XSSFCell captionCell = row.createCell(EntityColumn.CAPTION.ordinal());
        captionCell.setCellValue(I18nProperties.getPrefixCaption(i18nPrefix, field.getName(), ""));

        // description
        XSSFCell descriptionCell = row.createCell(EntityColumn.DESCRIPTION.ordinal());
        descriptionCell.setCellStyle(defaultCellStyle);
        descriptionCell.setCellValue(I18nProperties.getPrefixDescription(i18nPrefix, field.getName(), ""));

        // required
        XSSFCell requiredCell = row.createCell(EntityColumn.REQUIRED.ordinal());
        if (field.getAnnotation(Required.class) != null)
            requiredCell.setCellValue(true);

        // diseases
        XSSFCell diseasesCell = row.createCell(EntityColumn.DISEASES.ordinal());
        diseasesCell.setCellStyle(defaultCellStyle);
        Diseases diseases = field.getAnnotation(Diseases.class);
        if (diseases != null) {
            StringBuilder diseasesString = new StringBuilder();
            for (Disease disease : diseases.value()) {
                if (diseasesString.length() > 0)
                    diseasesString.append(", ");
                diseasesString.append(disease.toShortString());
            }
            diseasesCell.setCellValue(diseasesString.toString());
        } else {
            diseasesCell.setCellValue("All");
        }

        // outbreak
        XSSFCell outbreakCell = row.createCell(EntityColumn.OUTBREAKS.ordinal());
        if (field.getAnnotation(Outbreaks.class) != null)
            outbreakCell.setCellValue(true);
    }

    AreaReference reference = workbook.getCreationHelper().createAreaReference(new CellReference(0, 0),
            new CellReference(rowNumber - 1, columnCount - 1));
    table.setCellReferences(reference);
    table.getCTTable().addNewAutoFilter();

    for (Class<Enum<?>> usedEnum : usedEnums) {
        rowNumber = createEnumTable(sheet, rowNumber + 1, usedEnum);
    }

    return sheet;
}

From source file:de.symeda.sormas.api.doc.UserRightsGenerator.java

License:Open Source License

@Test
public void generateUserRights() throws FileNotFoundException, IOException {
    XSSFWorkbook workbook = new XSSFWorkbook();

    // Create User Rights sheet
    String safeName = WorkbookUtil.createSafeSheetName("User Rights");
    XSSFSheet sheet = workbook.createSheet(safeName);

    // Initialize cell styles
    // Authorized style
    XSSFCellStyle authorizedStyle = workbook.createCellStyle();
    authorizedStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    authorizedStyle.setFillForegroundColor(new XSSFColor(new Color(0, 153, 0)));
    authorizedStyle.setBorderBottom(BorderStyle.THIN);
    authorizedStyle.setBorderLeft(BorderStyle.THIN);
    authorizedStyle.setBorderTop(BorderStyle.THIN);
    authorizedStyle.setBorderRight(BorderStyle.THIN);
    authorizedStyle.setBorderColor(BorderSide.BOTTOM, new XSSFColor(Color.BLACK));
    authorizedStyle.setBorderColor(BorderSide.LEFT, new XSSFColor(Color.BLACK));
    authorizedStyle.setBorderColor(BorderSide.TOP, new XSSFColor(Color.BLACK));
    authorizedStyle.setBorderColor(BorderSide.RIGHT, new XSSFColor(Color.BLACK));
    // Unauthorized style
    XSSFCellStyle unauthorizedStyle = workbook.createCellStyle();
    unauthorizedStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    unauthorizedStyle.setFillForegroundColor(new XSSFColor(Color.RED));
    unauthorizedStyle.setBorderBottom(BorderStyle.THIN);
    unauthorizedStyle.setBorderLeft(BorderStyle.THIN);
    unauthorizedStyle.setBorderTop(BorderStyle.THIN);
    unauthorizedStyle.setBorderRight(BorderStyle.THIN);
    unauthorizedStyle.setBorderColor(BorderSide.BOTTOM, new XSSFColor(Color.BLACK));
    unauthorizedStyle.setBorderColor(BorderSide.LEFT, new XSSFColor(Color.BLACK));
    unauthorizedStyle.setBorderColor(BorderSide.TOP, new XSSFColor(Color.BLACK));
    unauthorizedStyle.setBorderColor(BorderSide.RIGHT, new XSSFColor(Color.BLACK));
    // Bold style
    XSSFFont boldFont = workbook.createFont();
    boldFont.setBold(true);/*from   w  ww .j a  v a2s  .com*/
    XSSFCellStyle boldStyle = workbook.createCellStyle();
    boldStyle.setFont(boldFont);

    int rowCounter = 0;

    // Header
    Row headerRow = sheet.createRow(rowCounter++);
    Cell userRightHeadlineCell = headerRow.createCell(0);
    userRightHeadlineCell.setCellValue("User Right");
    userRightHeadlineCell.setCellStyle(boldStyle);
    Cell descHeadlineCell = headerRow.createCell(1);
    descHeadlineCell.setCellValue("Description");
    descHeadlineCell.setCellStyle(boldStyle);
    sheet.setColumnWidth(0, 256 * 35);
    sheet.setColumnWidth(1, 256 * 50);
    for (UserRole userRole : UserRole.values()) {
        String columnCaption = userRole.toString();
        Cell headerCell = headerRow.createCell(userRole.ordinal() + 2);
        headerCell.setCellValue(columnCaption);
        headerCell.setCellStyle(boldStyle);
        sheet.setColumnWidth(userRole.ordinal() + 2, 256 * 14);
    }

    // User right rows
    for (UserRight userRight : UserRight.values()) {
        Row row = sheet.createRow(rowCounter++);

        // User right name
        Cell nameCell = row.createCell(0);
        nameCell.setCellValue(userRight.name());
        nameCell.setCellStyle(boldStyle);

        // User right description
        Cell descCell = row.createCell(1);
        descCell.setCellValue(userRight.toString());

        // Add styled cells for all user roles
        for (UserRole userRole : UserRole.values()) {
            Cell roleRightCell = row.createCell(userRole.ordinal() + 2);
            if (userRole.hasDefaultRight(userRight)) {
                roleRightCell.setCellStyle(authorizedStyle);
                roleRightCell.setCellValue("Yes");
            } else {
                roleRightCell.setCellStyle(unauthorizedStyle);
                roleRightCell.setCellValue("No");
            }
        }
    }

    XssfHelper.addAboutSheet(workbook);

    String filePath = "src/main/resources/doc/SORMAS_User_Rights.xlsx";
    try (OutputStream fileOut = new FileOutputStream(filePath)) {
        workbook.write(fileOut);
    }
    workbook.close();

    //      Desktop.getDesktop().open(new File(filePath));
}

From source file:de.symeda.sormas.api.doc.XssfHelper.java

License:Open Source License

public static void addAboutSheet(XSSFWorkbook workbook) {
    XSSFSheet sheet = workbook.createSheet("About");
    XSSFRow row = sheet.createRow(0);/*  w w w  .  jav  a2s. co  m*/
    XSSFCell cell = row.createCell(0);
    cell.setCellValue("SORMAS Version");

    row = sheet.createRow(1);
    cell = row.createCell(0);
    cell.setCellValue(InfoProvider.get().getVersion());
}

From source file:de.tuttas.util.ExcelUtil.java

public static XSSFWorkbook readExcel(String name, String[] sheetNames, int rows, int cols) throws IOException {
    FileInputStream file = null;/* www  . j  a  v  a  2s.com*/
    try {
        file = new FileInputStream(new File(name));
        Log.d("Template " + name + " gefunden!");
        XSSFWorkbook wb = new XSSFWorkbook(file);
        return wb;
    } catch (FileNotFoundException ex) {
        Log.d("Template " + name + " nicht gefunden erzeuge leeres Workbook");
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet[] sheets = new XSSFSheet[sheetNames.length];
        for (int k = 0; k < sheetNames.length; k++) {
            sheets[k] = wb.createSheet(sheetNames[k]);
            for (int i = 0; i < rows; i++) {
                XSSFRow hr = sheets[k].createRow(i);
                for (int j = 0; j < cols; j++) {
                    hr.createCell(j);
                }
            }
        }
        return wb;
    } finally {
        try {
            if (file != null) {
                file.close();
            }
        } catch (IOException ex) {
            Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

From source file:dias.Save.java

public void save(Matrix matrice, String filename) throws FileNotFoundException, IOException { ///It is working
    String dirPath = DIAS.excelFilePath + File.separator + filepath;
    setupDirectory(dirPath);//from  w  w w. jav  a  2  s .  c om
    String fileName = dirPath + File.separator + filename + ".xlsx";
    System.out.println("Using filepath " + filepath + ", saving to address: " + fileName);
    try {
        FileOutputStream fileOut = new FileOutputStream(fileName);
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet worksheet = workbook.createSheet("POI Worksheet");

        int lastvaluex = matrice.getRowDimension();
        int lastvaluey = matrice.getColumnDimension();
        int ih = 0;
        int jh = 0;

        while (ih < lastvaluex) {

            XSSFRow row = worksheet.createRow(ih);
            ih++;
            while (jh < lastvaluey) {
                XSSFCell cell = row.createCell(jh);
                jh++;
                cell.setCellValue(matrice.get(ih - 1, jh - 1));
            }
            jh = 0;
        }
        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

From source file:dias.Save.java

public void savedouble(double matrice, String filename) throws FileNotFoundException, IOException { ///It is working
    String dirPath = DIAS.excelFilePath + File.separator + filepath;
    setupDirectory(dirPath);//  ww w . java 2s.co  m
    String fileName = dirPath + File.separator + filename + ".xlsx";
    try {
        FileOutputStream fileOut = new FileOutputStream(fileName);
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet worksheet = workbook.createSheet("POI Worksheet");

        int lastvaluex = 1;
        int lastvaluey = 1;
        int ih = 0;
        int jh = 0;

        while (ih < lastvaluex) {

            XSSFRow row = worksheet.createRow(ih);
            ih++;
            while (jh < lastvaluey) {
                XSSFCell cell = row.createCell(jh);
                jh++;
                cell.setCellValue(matrice);
            }
            jh = 0;
        }
        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

From source file:dias.Save.java

public void save3D(Matrix matrice, String filename, int kj) throws FileNotFoundException, IOException { ///It is working
    String dirPath = DIAS.excelFilePath + File.separator + filepath;
    setupDirectory(dirPath);/*from   w  w  w  . ja va 2s  .  co  m*/
    String fileName = dirPath + File.separator + filename + kj + ".xlsx";
    try {
        FileOutputStream fileOut = new FileOutputStream(fileName);
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet worksheet = workbook.createSheet("POI Worksheet");

        int lastvaluex = matrice.getRowDimension();
        int lastvaluey = matrice.getColumnDimension();
        int ih = 0;
        int jh = 0;

        while (ih < lastvaluex) {
            XSSFRow row = worksheet.createRow(ih);
            while (jh < lastvaluey) {
                XSSFCell cell = row.createCell(jh);
                cell.setCellValue(matrice.get(ih, jh));
                jh++;
            }
            ih++;
            jh = 0;
        }
        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

From source file:dias.Save.java

public void saveString(String[] matrice, String filename) throws FileNotFoundException, IOException { ///It is working
    String dirPath = DIAS.excelFilePath + File.separator + filepath;
    setupDirectory(dirPath);//from  w w  w .  j a  v a2  s  .  c o  m
    String fileName = dirPath + File.separator + filename + ".xlsx";
    try {
        FileOutputStream fileOut = new FileOutputStream(fileName);
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet worksheet = workbook.createSheet("POI Worksheet");

        int lastvaluex = matrice.length;
        int lastvaluey = 1;
        int ih = 0;
        int jh = 0;

        while (ih < lastvaluex) {
            XSSFRow row = worksheet.createRow(ih);

            while (jh < lastvaluey) {
                XSSFCell cell = row.createCell(jh);
                cell.setCellValue(matrice[ih]);
                jh++;
            }
            ih++;
            jh = 0;
        }
        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

From source file:DSC.AccountantReport.java

private static void createExcelReport() {
    XSSFWorkbook workbook = new XSSFWorkbook();
    clients.sort(new Comparator<Client>() {
        @Override/* w w w .  ja v a  2  s  . c om*/
        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.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");
}