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

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

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

Introduction

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

Prototype

@Override
public void setBorderLeft(BorderStyle border)

Source Link

Document

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

Usage

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

License:Open Source License

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

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

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

    return style;
}

From source file:com.tikal.tallerWeb.servicio.reporte.cliente.AbstractSeccionXLS.java

License:Apache License

public void addBorders(XSSFWorkbook wb, XSSFCellStyle cellStyle, short borderType) {
    cellStyle.setBorderBottom(borderType);
    cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    cellStyle.setBorderLeft(borderType);
    cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    cellStyle.setBorderRight(borderType);
    cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
    cellStyle.setBorderTop(borderType);/*from  w ww .  ja v  a  2 s. c  o m*/
    cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
}

From source file:com.tikal.tallerWeb.servicio.reporte.cliente.AbstractSeccionXLS.java

License:Apache License

public void paintBorder(XSSFWorkbook wb, Sheet sheet, short borderType, BordeSeccion borde) {
    for (int i = borde.getUpperRow(); i <= borde.getLowerRow(); i++) {
        Row row = sheet.getRow(i);//from w w w .  ja va2  s . com
        for (int j = borde.getLeftColumn(); j <= borde.getRightColumn(); j++) {
            if (i == borde.getUpperRow() || i == borde.getLowerRow() || j == borde.getLeftColumn()
                    || j == borde.getRightColumn()) {
                Cell cell = row.getCell(j);
                XSSFCellStyle actual = (XSSFCellStyle) cell.getCellStyle();
                XSSFCellStyle nuevo = wb.createCellStyle();
                nuevo.cloneStyleFrom(actual);
                if (i == borde.getUpperRow()) {
                    nuevo.setBorderTop(borderType);
                    nuevo.setTopBorderColor(IndexedColors.BLACK.getIndex());
                }
                if (i == borde.getLowerRow()) {
                    nuevo.setBorderBottom(borderType);
                    nuevo.setBottomBorderColor(IndexedColors.BLACK.getIndex());
                }
                if (j == borde.getLeftColumn()) {
                    nuevo.setBorderLeft(borderType);
                    nuevo.setLeftBorderColor(IndexedColors.BLACK.getIndex());
                }
                if (j == borde.getRightColumn()) {
                    nuevo.setBorderRight(borderType);
                    nuevo.setRightBorderColor(IndexedColors.BLACK.getIndex());
                }
                cell.setCellStyle(nuevo);
            }
        }
    }
}

From source file:contestTabulation.Setup.java

License:Open Source License

@Override
public void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOException {
    HttpTransport httpTransport = new NetHttpTransport();
    JacksonFactory jsonFactory = new JacksonFactory();
    DatastoreService datastore = DatastoreServiceFactory.getDatastoreService();

    Entity contestInfo = Retrieve.contestInfo();

    GoogleCredential credential = new GoogleCredential.Builder().setJsonFactory(jsonFactory)
            .setTransport(httpTransport)
            .setClientSecrets((String) contestInfo.getProperty("OAuth2ClientId"),
                    (String) contestInfo.getProperty("OAuth2ClientSecret"))
            .build().setFromTokenResponse(new JacksonFactory().fromString(
                    ((Text) contestInfo.getProperty("OAuth2Token")).getValue(), GoogleTokenResponse.class));

    String docName = null, docLevel = null;
    for (Level level : Level.values()) {
        docName = req.getParameter("doc" + level.getName());
        if (docName != null) {
            docLevel = level.toString();
            break;
        }/*  w  ww . j a  va 2s  .c  o  m*/
    }

    if (docLevel == null) {
        resp.sendError(HttpServletResponse.SC_BAD_REQUEST,
                "Spreadsheet creation request must have paramater document name parameter set");
        return;
    }

    Query query = new Query("registration")
            .setFilter(new FilterPredicate("schoolLevel", FilterOperator.EQUAL, docLevel))
            .addSort("schoolName", SortDirection.ASCENDING);
    List<Entity> registrations = datastore.prepare(query).asList(FetchOptions.Builder.withDefaults());

    Map<String, List<JSONObject>> studentData = new HashMap<String, List<JSONObject>>();
    for (Entity registration : registrations) {
        String regSchoolName = ((String) registration.getProperty("schoolName")).trim();
        String regStudentDataJSON = unescapeHtml4(((Text) registration.getProperty("studentData")).getValue());

        JSONArray regStudentData = null;
        try {
            regStudentData = new JSONArray(regStudentDataJSON);
        } catch (JSONException e) {
            e.printStackTrace();
            resp.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, e.toString());
            return;
        }

        for (int i = 0; i < regStudentData.length(); i++) {
            if (!studentData.containsKey(regSchoolName)) {
                studentData.put(regSchoolName, new ArrayList<JSONObject>());
            }
            try {
                studentData.get(regSchoolName).add(regStudentData.getJSONObject(i));
            } catch (JSONException e) {
                resp.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
                e.printStackTrace();
                return;
            }
        }
    }

    for (List<JSONObject> students : studentData.values()) {
        Collections.sort(students, new Comparator<JSONObject>() {
            @Override
            public int compare(JSONObject a, JSONObject b) {
                try {
                    return a.getString("name").compareTo(b.getString("name"));
                } catch (JSONException e) {
                    e.printStackTrace();
                    return 0;
                }
            }
        });
    }

    Workbook workbook = new XSSFWorkbook();

    XSSFCellStyle boldStyle = (XSSFCellStyle) workbook.createCellStyle();
    Font boldFont = workbook.createFont();
    boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    boldStyle.setFont(boldFont);

    Map<Subject, XSSFCellStyle> subjectCellStyles = new HashMap<Subject, XSSFCellStyle>();
    for (Subject subject : Subject.values()) {
        final double ALPHA = .144;
        String colorStr = (String) contestInfo.getProperty("color" + subject.getName());
        byte[] backgroundColor = new byte[] { Integer.valueOf(colorStr.substring(1, 3), 16).byteValue(),
                Integer.valueOf(colorStr.substring(3, 5), 16).byteValue(),
                Integer.valueOf(colorStr.substring(5, 7), 16).byteValue() };
        // http://en.wikipedia.org/wiki/Alpha_compositing#Alpha_blending
        byte[] borderColor = new byte[] { (byte) ((backgroundColor[0] & 0xff) * (1 - ALPHA)),
                (byte) ((backgroundColor[1] & 0xff) * (1 - ALPHA)),
                (byte) ((backgroundColor[2] & 0xff) * (1 - ALPHA)) };

        XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();
        style.setFillBackgroundColor(new XSSFColor(backgroundColor));
        style.setFillPattern(CellStyle.ALIGN_FILL);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(new XSSFColor(borderColor));
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(new XSSFColor(borderColor));
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(new XSSFColor(borderColor));
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(new XSSFColor(borderColor));
        subjectCellStyles.put(subject, style);
    }

    Entry<String, List<JSONObject>>[] studentDataEntries = studentData.entrySet().toArray(new Entry[] {});
    Arrays.sort(studentDataEntries, Collections.reverseOrder(new Comparator<Entry<String, List<JSONObject>>>() {
        @Override
        public int compare(Entry<String, List<JSONObject>> arg0, Entry<String, List<JSONObject>> arg1) {
            return Integer.compare(arg0.getValue().size(), arg1.getValue().size());
        }
    }));

    for (Entry<String, List<JSONObject>> studentDataEntry : studentDataEntries) {
        Sheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(studentDataEntry.getKey()));
        Row row = sheet.createRow((short) 0);

        String[] columnNames = { "Name", "Grade", "N", "C", "M", "S" };
        for (int i = 0; i < columnNames.length; i++) {
            String columnName = columnNames[i];
            Cell cell = row.createCell(i);
            cell.setCellValue(columnName);
            cell.setCellStyle(boldStyle);
            CellUtil.setAlignment(cell, workbook, CellStyle.ALIGN_CENTER);
        }

        int longestNameLength = 7;
        int rowNum = 1;
        for (JSONObject student : studentDataEntry.getValue()) {
            try {
                row = sheet.createRow((short) rowNum);
                row.createCell(0).setCellValue(student.getString("name"));
                row.createCell(1).setCellValue(student.getInt("grade"));

                for (Subject subject : Subject.values()) {
                    String value = student.getBoolean(subject.toString()) ? "" : "X";
                    Cell cell = row.createCell(Arrays.asList(columnNames).indexOf(subject.toString()));
                    cell.setCellValue(value);
                    cell.setCellStyle(subjectCellStyles.get(subject));
                }

                if (student.getString("name").length() > longestNameLength) {
                    longestNameLength = student.getString("name").length();
                }

                rowNum++;
            } catch (JSONException e) {
                e.printStackTrace();
                resp.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, e.toString());
                return;
            }
        }

        sheet.createFreezePane(0, 1, 0, 1);
        // sheet.autoSizeColumn((short) 0); Not supported by App Engine
        sheet.setColumnWidth((short) 0, (int) (256 * longestNameLength * 1.1));
    }

    Drive drive = new Drive.Builder(httpTransport, jsonFactory, credential)
            .setApplicationName("contestTabulation").build();

    File body = new File();
    body.setTitle(docName);
    body.setMimeType("application/vnd.google-apps.spreadsheet");

    ByteArrayOutputStream outStream = new ByteArrayOutputStream();
    workbook.write(outStream);
    ByteArrayInputStream inStream = new ByteArrayInputStream(outStream.toByteArray());
    InputStreamContent content = new InputStreamContent(
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", inStream);

    drive.files().insert(body, content).execute();
    workbook.close();
}

From source file:coverageqc.functions.MyExcelEditor.java

private static XSSFCellStyle getDefaultCellStyle(XSSFRow currentRow) {

    XSSFCellStyle cellStyle = currentRow.getSheet().getWorkbook().createCellStyle();

    cellStyle.setWrapText(true);/*  w  ww  .  java  2  s.c o  m*/
    cellStyle.setBorderBottom(cellStyle.BORDER_THIN);
    cellStyle.setBorderLeft(cellStyle.BORDER_THIN);
    cellStyle.setBorderRight(cellStyle.BORDER_THIN);
    cellStyle.setBorderTop(cellStyle.BORDER_THIN);
    XSSFFont myFont = currentRow.getSheet().getWorkbook().createFont();
    myFont.setFontHeight(9);
    cellStyle.setFont(myFont);
    cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
    cellStyle.setFillForegroundColor(new XSSFColor(Color.white));
    return cellStyle;
}

From source file:coverageqc.functions.MyExcelEditor.java

private static XSSFCellStyle getGrayCellStyle(XSSFRow currentRow) {

    XSSFCellStyle cellStyle = currentRow.getSheet().getWorkbook().createCellStyle();

    cellStyle.setWrapText(true);//from   w ww.  j av a 2 s  .c  o m
    cellStyle.setBorderBottom(cellStyle.BORDER_THIN);
    cellStyle.setBorderLeft(cellStyle.BORDER_THIN);
    cellStyle.setBorderRight(cellStyle.BORDER_THIN);
    cellStyle.setBorderTop(cellStyle.BORDER_THIN);
    XSSFFont myFont = currentRow.getSheet().getWorkbook().createFont();
    myFont.setFontHeight(9);
    cellStyle.setFont(myFont);
    cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
    cellStyle.setFillForegroundColor(new XSSFColor(Color.gray));
    return cellStyle;
}

From source file:coverageqc.functions.MyExcelGenerator.java

private XSSFCellStyle getDefaultCellStyle(XSSFRow currentRow, Color specifiedColor) {

    XSSFCellStyle cellStyle = currentRow.getSheet().getWorkbook().createCellStyle();

    cellStyle.setWrapText(true);//from w  w  w .  j a  v  a  2s  .  c  o  m
    cellStyle.setBorderBottom(cellStyle.BORDER_THIN);
    cellStyle.setBorderLeft(cellStyle.BORDER_THIN);
    cellStyle.setBorderRight(cellStyle.BORDER_THIN);
    cellStyle.setBorderTop(cellStyle.BORDER_THIN);
    XSSFFont myFont = currentRow.getSheet().getWorkbook().createFont();
    myFont.setFontHeight(9);
    cellStyle.setFont(myFont);
    cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
    cellStyle.setFillForegroundColor(new XSSFColor(specifiedColor));
    return cellStyle;
}

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);//w  w w.ja  va2 s .  c  om
    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:document.ExcelDocumentStyles.java

private void withBorder(XSSFCellStyle style) {
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(borderColor);
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(borderColor);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(borderColor);
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(borderColor);
}

From source file:DSC.AccountantReport.java

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

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

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

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

        for (int key = 1; key < keySet.size() + 1; key++) {
            Row row = sheet.createRow(key - 1);
            Object[] arr = data.get(key + "");
            for (int i = 0; i < arr.length; i++) {
                Cell cell = row.createCell(i);
                cell.setCellValue((String) arr[i]);

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

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

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

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

                cell.setCellStyle(borderStyle);

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

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

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

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

    }

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

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