Example usage for org.apache.poi.ss.usermodel HorizontalAlignment RIGHT

List of usage examples for org.apache.poi.ss.usermodel HorizontalAlignment RIGHT

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. HorizontalAlignment
  6. RIGHT

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel HorizontalAlignment RIGHT.

Prototype

HorizontalAlignment RIGHT

To view the source code for org.apache.poi.ss.usermodel HorizontalAlignment RIGHT.

Click Source Link

Document

The horizontal alignment is right-aligned, meaning that cell contents are aligned at the right edge of the cell, even in Right-to-Left mode.

Usage

From source file:cn.afterturn.easypoi.excel.export.ExcelExportService.java

License:Apache License

/**
 *  ?// w w  w.  j  a v a  2  s . c  om
 */
public int createTitle2Row(ExportParams entity, Sheet sheet, Workbook workbook, int fieldWidth) {

    Row row = sheet.createRow(0);
    row.setHeight(entity.getTitleHeight());
    createStringCell(row, 0, entity.getTitle(), getExcelExportStyler().getHeaderStyle(entity.getHeaderColor()),
            null);
    for (int i = 1; i <= fieldWidth; i++) {
        createStringCell(row, i, "", getExcelExportStyler().getHeaderStyle(entity.getHeaderColor()), null);
    }
    PoiMergeCellUtil.addMergedRegion(sheet, 0, 0, 0, fieldWidth);
    if (entity.getSecondTitle() != null) {
        row = sheet.createRow(1);
        row.setHeight(entity.getSecondTitleHeight());
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.RIGHT);
        createStringCell(row, 0, entity.getSecondTitle(), style, null);
        for (int i = 1; i <= fieldWidth; i++) {
            createStringCell(row, i, "", getExcelExportStyler().getHeaderStyle(entity.getHeaderColor()), null);
        }
        PoiMergeCellUtil.addMergedRegion(sheet, 1, 1, 0, fieldWidth);
        return 2;
    }
    return 1;
}

From source file:com.commander4j.util.JExcel.java

License:Open Source License

public void exportToExcel(String filename, ResultSet rs) {
    try {//from  w w w  . jav a2  s  . c  o  m

        ResultSetMetaData rsmd = rs.getMetaData();
        int numberOfColumns = rsmd.getColumnCount();
        int columnType = 0;
        String columnTypeName = "";
        int recordNumber = 0;
        int passwordCol = -1;

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

        HSSFCellStyle cellStyle_varchar = workbook.createCellStyle();
        cellStyle_varchar.setAlignment(HorizontalAlignment.LEFT);

        HSSFCellStyle cellStyle_nvarchar = workbook.createCellStyle();
        cellStyle_nvarchar.setAlignment(HorizontalAlignment.LEFT);

        HSSFCellStyle cellStyle_varchar2 = workbook.createCellStyle();
        cellStyle_varchar2.setAlignment(HorizontalAlignment.LEFT);

        HSSFCellStyle cellStyle_title = workbook.createCellStyle();
        cellStyle_title.setAlignment(HorizontalAlignment.CENTER);

        HSSFCellStyle cellStyle_char = workbook.createCellStyle();
        cellStyle_char.setAlignment(HorizontalAlignment.LEFT);

        HSSFCellStyle cellStyle_date = workbook.createCellStyle();
        cellStyle_date.setAlignment(HorizontalAlignment.CENTER);
        cellStyle_date.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

        HSSFCellStyle cellStyle_timestamp = workbook.createCellStyle();
        cellStyle_timestamp.setAlignment(HorizontalAlignment.CENTER);
        cellStyle_timestamp.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

        HSSFCellStyle cellStyle_decimal = workbook.createCellStyle();
        cellStyle_decimal.setAlignment(HorizontalAlignment.RIGHT);

        HSSFFont font_title = workbook.createFont();
        font_title.setColor((short) 0xc);
        font_title.setBold(true);
        ;
        font_title.setItalic(true);
        font_title.setUnderline(HSSFFont.U_DOUBLE);
        cellStyle_title.setFont(font_title);

        HSSFCell cell;
        HSSFRow row;

        // rs.beforeFirst();

        while (rs.next()) {
            recordNumber++;

            if (recordNumber == 1) {
                row = sheet.createRow((int) 0);
                for (int column = 1; column <= numberOfColumns; column++) {
                    cell = row.createCell((int) (column - 1));
                    String columnName = rsmd.getColumnLabel(column);
                    columnName = columnName.replace("_", " ");
                    columnName = JUtility.capitalize(columnName);
                    cell.setCellStyle(cellStyle_title);
                    cell.setCellValue(columnName);
                    if (columnName.equals("Password")) {
                        passwordCol = column;
                    }
                }
            }

            row = sheet.createRow((int) recordNumber);

            for (int column = 1; column <= numberOfColumns; column++) {

                columnType = rsmd.getColumnType(column);
                columnTypeName = rsmd.getColumnTypeName(column);

                cell = row.createCell((int) (column - 1));

                try {
                    switch (columnType) {
                    case java.sql.Types.NVARCHAR:
                        HSSFRichTextString rtf_nvarchar;
                        if (column == passwordCol) {
                            rtf_nvarchar = new HSSFRichTextString("*****");
                        } else {
                            rtf_nvarchar = new HSSFRichTextString(rs.getString(column));
                        }

                        cell.setCellStyle(cellStyle_nvarchar);
                        cell.setCellValue(rtf_nvarchar);
                        break;
                    case java.sql.Types.VARCHAR:
                        HSSFRichTextString rtf_varchar;
                        if (column == passwordCol) {
                            rtf_varchar = new HSSFRichTextString("*****");
                        } else {
                            rtf_varchar = new HSSFRichTextString(rs.getString(column));
                        }

                        cell.setCellStyle(cellStyle_varchar);
                        cell.setCellValue(rtf_varchar);
                        break;
                    case java.sql.Types.CHAR:
                        HSSFRichTextString rtf_char = new HSSFRichTextString(rs.getString(column));
                        cell.setCellStyle(cellStyle_char);
                        cell.setCellValue(rtf_char);
                        break;
                    case java.sql.Types.DATE:
                        try {
                            cell.setCellValue(rs.getTimestamp(column));
                            cell.setCellStyle(cellStyle_date);
                        } catch (Exception ex) {

                        }
                        break;
                    case java.sql.Types.TIMESTAMP:
                        try {
                            cell.setCellValue(rs.getTimestamp(column));
                            cell.setCellStyle(cellStyle_timestamp);
                        } catch (Exception ex) {

                        }
                        break;
                    case java.sql.Types.DECIMAL:
                        HSSFRichTextString rtf_decimal = new HSSFRichTextString(
                                rs.getBigDecimal(column).toString());
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_decimal);
                        break;
                    case java.sql.Types.NUMERIC:
                        HSSFRichTextString rtf_decimaln = new HSSFRichTextString(
                                rs.getBigDecimal(column).toString());
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_decimaln);
                        break;
                    case java.sql.Types.BIGINT:
                        HSSFRichTextString rtf_bigint = new HSSFRichTextString(
                                rs.getBigDecimal(column).toString());
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_bigint);
                        break;
                    case java.sql.Types.INTEGER:
                        HSSFRichTextString rtf_int = new HSSFRichTextString(String.valueOf(rs.getInt(column)));
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_int);
                        break;
                    case java.sql.Types.FLOAT:
                        HSSFRichTextString rtf_float = new HSSFRichTextString(
                                String.valueOf(rs.getFloat(column)));
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_float);
                        break;
                    case java.sql.Types.DOUBLE:
                        HSSFRichTextString rtf_double = new HSSFRichTextString(
                                String.valueOf(rs.getDouble(column)));
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_double);
                        break;
                    default:
                        cell.setCellValue(new HSSFRichTextString(columnTypeName));
                        break;
                    }
                } catch (Exception ex) {
                    String errormessage = ex.getLocalizedMessage();
                    HSSFRichTextString rtf_exception = new HSSFRichTextString(errormessage);
                    cell.setCellStyle(cellStyle_varchar);
                    cell.setCellValue(rtf_exception);
                    break;
                }
            }

            if (recordNumber == 65535) {
                break;
            }
        }

        for (int column = 1; column <= numberOfColumns; column++) {
            sheet.autoSizeColumn((int) (column - 1));
        }

        if (recordNumber > 0) {
            try {
                FileOutputStream fileOut = new FileOutputStream(filename.toLowerCase());
                workbook.write(fileOut);
                fileOut.close();
            } catch (Exception ex) {
                setErrorMessage(ex.getMessage());
            }
        }

        try {
            workbook.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    } catch (SQLException e) {
        setErrorMessage(e.getMessage());
    }
}

From source file:com.funtl.framework.smoke.core.commons.excel.ExportExcel.java

License:Apache License

/**
 * ?//from  w w w .  j  a  va 2 s .  c o m
 *
 * @param wb 
 * @return ?
 */
private Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

    CellStyle style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    //      style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    //      style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    Font titleFont = wb.createFont();
    titleFont.setFontName("Arial");
    titleFont.setFontHeightInPoints((short) 16);
    titleFont.setBold(true);
    //      titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style.setFont(titleFont);
    styles.put("title", style);

    style = wb.createCellStyle();
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    //      style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setBorderRight(BorderStyle.THIN);
    //      style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderLeft(BorderStyle.THIN);
    //      style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderTop(BorderStyle.THIN);
    //      style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderBottom(BorderStyle.THIN);
    //      style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    Font dataFont = wb.createFont();
    dataFont.setFontName("Arial");
    dataFont.setFontHeightInPoints((short) 10);
    style.setFont(dataFont);
    styles.put("data", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    style.setAlignment(HorizontalAlignment.LEFT);
    //      style.setAlignment(CellStyle.ALIGN_LEFT);
    styles.put("data1", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    style.setAlignment(HorizontalAlignment.CENTER);
    //      style.setAlignment(CellStyle.ALIGN_CENTER);
    styles.put("data2", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    style.setAlignment(HorizontalAlignment.RIGHT);
    //      style.setAlignment(CellStyle.ALIGN_RIGHT);
    styles.put("data3", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    //      style.setWrapText(true);
    style.setAlignment(HorizontalAlignment.CENTER);
    //      style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    //      style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    Font headerFont = wb.createFont();
    headerFont.setFontName("Arial");
    headerFont.setFontHeightInPoints((short) 10);
    headerFont.setBold(true);
    //      headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setColor(IndexedColors.WHITE.getIndex());
    style.setFont(headerFont);
    styles.put("header", style);

    return styles;
}

From source file:com.github.ukase.toolkit.xlsx.XlsxUtil.java

License:Open Source License

static HorizontalAlignment prepareAlignment(IdentValue ident) {
    if (XlsxUtil.isLeft(ident)) {
        return HorizontalAlignment.LEFT;
    } else if (XlsxUtil.isRight(ident)) {
        return HorizontalAlignment.RIGHT;
    } else if (XlsxUtil.isJustify(ident)) {
        return HorizontalAlignment.JUSTIFY;
    }// www .ja v  a  2  s.  c  o m
    return HorizontalAlignment.CENTER;
}

From source file:com.hauldata.dbpa.file.book.XlsxTargetSheet.java

License:Apache License

public static HorizontalAlignment getTextAlign(String textProperty) {
    switch (textProperty) {
    case "center":
        return HorizontalAlignment.CENTER;
    case "left":
        return HorizontalAlignment.LEFT;
    case "right":
        return HorizontalAlignment.RIGHT;
    default:/*from w w w.j  a  v  a2  s.com*/
        return null;
    }
}

From source file:com.philips.his.pixiu.cdr.poi.BigGridDemo.java

License:Apache License

/**
 * Create a library of cell styles./*w  ww.  ja  v a  2s .co  m*/
 */
private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb) {
    Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
    XSSFDataFormat fmt = wb.createDataFormat();

    XSSFCellStyle style1 = wb.createCellStyle();
    style1.setAlignment(HorizontalAlignment.RIGHT);
    style1.setDataFormat(fmt.getFormat("0.0%"));
    styles.put("percent", style1);

    XSSFCellStyle style2 = wb.createCellStyle();
    style2.setAlignment(HorizontalAlignment.CENTER);
    style2.setDataFormat(fmt.getFormat("0.0X"));
    styles.put("coeff", style2);

    XSSFCellStyle style3 = wb.createCellStyle();
    style3.setAlignment(HorizontalAlignment.RIGHT);
    style3.setDataFormat(fmt.getFormat("$#,##0.00"));
    styles.put("currency", style3);

    XSSFCellStyle style4 = wb.createCellStyle();
    style4.setAlignment(HorizontalAlignment.RIGHT);
    style4.setDataFormat(fmt.getFormat("mmm dd"));
    styles.put("date", style4);

    XSSFCellStyle style5 = wb.createCellStyle();
    XSSFFont headerFont = wb.createFont();
    headerFont.setBold(true);
    style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style5.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style5.setFont(headerFont);
    styles.put("header", style5);

    return styles;
}

From source file:de.jlo.talendcomp.excel.SpreadsheetFile.java

License:Apache License

/**
 * adds a font to the workbook/*w w  w  .  j  a va 2 s  . c om*/
 * @param family like Arial
 * @param height like 8,9,10,12,14...
 * @param bui with "b"=bold, "u"=underlined, "i"=italic and all combinations as String
 * @param color color index
 */
public void addStyle(String styleName, String fontFamily, String fontHeight, String fontDecoration,
        String fontColor, String bgColor, String textAlign, boolean buttomBorder) {
    if (styleName != null && styleName.isEmpty() == false) {
        Font f = workbook.createFont();
        if (fontFamily != null && fontFamily.isEmpty() == false) {
            f.setFontName(fontFamily);
        }
        if (fontHeight != null && fontHeight.isEmpty() == false) {
            short height = Short.parseShort(fontHeight);
            if (height > 0) {
                f.setFontHeightInPoints(height);
            }
        }
        if (fontDecoration != null && fontDecoration.isEmpty() == false) {
            if (fontDecoration.contains("b")) {
                f.setBold(true);
            }
            if (fontDecoration.contains("i")) {
                f.setItalic(true);
            }
            if (fontDecoration.contains("u")) {
                f.setUnderline(Font.U_SINGLE);
            }
        }
        if (fontColor != null && fontColor.isEmpty() == false) {
            short color = Short.parseShort(fontColor);
            f.setColor(color);
        }
        CellStyle style = workbook.createCellStyle();
        style.setFont(f);
        if (bgColor != null && bgColor.isEmpty() == false) {
            short color = Short.parseShort(bgColor);
            style.setFillForegroundColor(color);
            //style.setFillBackgroundColor(color);
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }
        if (textAlign != null && textAlign.isEmpty() == false) {
            if ("center".equalsIgnoreCase(textAlign)) {
                style.setAlignment(HorizontalAlignment.CENTER);
            } else if ("left".equalsIgnoreCase(textAlign)) {
                style.setAlignment(HorizontalAlignment.LEFT);
            } else if ("right".equals(textAlign)) {
                style.setAlignment(HorizontalAlignment.RIGHT);
            }
        }
        if (buttomBorder) {
            style.setBorderBottom(BorderStyle.MEDIUM);
            style.setBottomBorderColor((short) 9);
        }
        namedStyles.put(styleName, style);
    }
}

From source file:DSC.AccountantReport.java

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

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

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

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

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

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

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

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

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

                cell.setCellStyle(borderStyle);

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

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

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

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

    }

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

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

From source file:DSC.DriverReport.java

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

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

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

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

                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/*  w w w.  jav 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();
    }

}