Example usage for org.apache.poi.hssf.usermodel HSSFCellStyle setFillPattern

List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setFillPattern

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFCellStyle
  6. setFillPattern

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFCellStyle setFillPattern.

Prototype

@Override
public void setFillPattern(FillPatternType fp)

Source Link

Document

setting to one fills the cell with the foreground color...

Usage

From source file:com.nokia.s60tools.swmtanalyser.model.ExcelCreator.java

License:Open Source License

/**
  * create set of cell styles//from   w w  w.  j  a  v a 2  s .  c o m
  */
private Map<String, HSSFCellStyle> createStyles(HSSFWorkbook wb) {
    Map<String, HSSFCellStyle> styles = new HashMap<String, HSSFCellStyle>();

    HSSFCellStyle style;
    HSSFFont headerFont = wb.createFont();
    headerFont.setColor(HSSFColor.WHITE.index);
    headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
    headerFont.setFontHeightInPoints((short) 18);
    style = createBorderedStyle(wb);
    style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    style.setFillForegroundColor(HSSFColor.BLUE.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style.setFont(headerFont);
    styles.put("header", style);

    HSSFFont font1 = wb.createFont();
    font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font1.setFontHeightInPoints((short) 12);
    font1.setFontName(HSSFFont.FONT_ARIAL);
    font1.setColor(HSSFColor.WHITE.index);
    style = createBorderedStyle(wb);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(HSSFColor.BLUE.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style.setFont(font1);
    style.setWrapText(true);
    styles.put("header2", style);

    style = createBorderedStyle(wb);
    style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    style.setFillForegroundColor(HSSFColor.BLUE.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style.setFont(font1);
    style.setWrapText(true);
    styles.put("header1", style);

    HSSFFont font3 = wb.createFont();
    font3.setColor(HSSFColor.BLACK.index);
    font3.setFontHeightInPoints((short) 10);
    font3.setFontName(HSSFFont.FONT_ARIAL);
    font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    style = createBorderedStyle(wb);
    style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    style.setFont(font3);
    styles.put("cell_bold", style);

    HSSFFont font5 = wb.createFont();
    font5.setColor(HSSFColor.BLACK.index);
    font5.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
    style = createBorderedStyle(wb);
    style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    style.setFont(font5);
    styles.put("cell_normal", style);

    HSSFFont font4 = wb.createFont();
    font4.setFontHeightInPoints((short) 10);
    font4.setColor(HSSFColor.WHITE.index);
    style = createBorderedStyle(wb);
    style.setFillForegroundColor(HSSFColor.BLUE.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style.setFont(font4);
    styles.put("cell_blue_font_white", style);

    style = createBorderedStyle(wb);
    style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    styles.put("cell_number", style);

    HSSFFont blue_font = wb.createFont();
    blue_font.setFontHeightInPoints((short) 10);
    blue_font.setColor(HSSFColor.BLUE.index);
    style = createBorderedStyle(wb);
    style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    style.setFont(blue_font);
    styles.put("blue_font", style);

    return styles;
}

From source file:com.objectfrontier.insta.neft.reports.bean.InstaNEFTReportBean.java

License:Open Source License

/**
  * Method used to export the bank wise summary report in to Excel for both Inward and Outward.
  */*from  w  w  w  .  j  a  v a  2  s. c o m*/
  * @param out ServletOutputStream
  *
  */
public void generateNEFTInwBankSummaryReportToExcel(ServletOutputStream out) throws Exception {

    try {

        if (returnedList.contains(" ")) {
            throw new Exception("No Data Found! Cannot Export as Excel Sheet!!");
        }

        HSSFWorkbook wb = new HSSFWorkbook();

        HSSFSheet sheet = wb.createSheet(reportTitle);

        HSSFCellStyle titleStyle = wb.createCellStyle();
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        HSSFCellStyle fontStyle = wb.createCellStyle();
        HSSFFont fontSize = wb.createFont();
        fontSize.setFontHeightInPoints((short) 10);
        fontSize.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        fontStyle.setFont(fontSize);
        fontStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        sheet.setColumnWidth((short) 0, (short) 2000);
        sheet.setColumnWidth((short) 1, (short) 4000);
        sheet.setColumnWidth((short) 2, (short) 6000);
        sheet.setColumnWidth((short) 3, (short) 5000);
        sheet.setColumnWidth((short) 4, (short) 7000);
        sheet.setColumnWidth((short) 5, (short) 7000);
        sheet.setColumnWidth((short) 6, (short) 7000);
        sheet.setColumnWidth((short) 7, (short) 7000);

        HSSFCellStyle contentStyle = wb.createCellStyle();
        contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

        HSSFCellStyle stringStyle = wb.createCellStyle();
        stringStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        stringStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        stringStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        stringStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        stringStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

        HSSFCellStyle stringRightStyle = wb.createCellStyle();
        stringRightStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        stringRightStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        stringRightStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        stringRightStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        stringRightStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

        HSSFFont headingFont = wb.createFont();
        headingFont.setFontHeightInPoints((short) 9);
        headingFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        HSSFCellStyle headingStyle = wb.createCellStyle();

        headingStyle.setFont(headingFont);
        headingStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headingStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
        headingStyle.setFillPattern(HSSFCellStyle.BIG_SPOTS);
        headingStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        headingStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        headingStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headingStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headingStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headingStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

        HSSFCellStyle titleRightStyle = wb.createCellStyle();
        titleRightStyle.setFont(headingFont);
        titleRightStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        titleRightStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        titleRightStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        titleRightStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        titleRightStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

        HSSFCellStyle titleLeftStyle = wb.createCellStyle();
        titleLeftStyle.setFont(headingFont);
        titleLeftStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        titleLeftStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        titleLeftStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        titleLeftStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        titleLeftStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

        int rowCount = 2;

        String dateForm = currentReportPrintTime.substring(0, 11);
        String time = currentReportPrintTime.substring(11);
        HSSFRow reportTimeRow = sheet.createRow((short) rowCount);
        HSSFCell reportTimeRowCell = reportTimeRow.createCell((short) 0);
        reportTimeRowCell.setCellStyle(titleRightStyle);
        reportTimeRowCell.setCellValue(
                "Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time);
        sheet.addMergedRegion(new Region(rowCount, (short) 0, rowCount, (short) 7));

        rowCount++;
        HSSFRow stTitle = sheet.createRow((short) rowCount);
        HSSFCell title = stTitle.createCell((short) 0);
        title.setCellStyle(fontStyle);
        title.setCellValue(reportTitle);
        sheet.addMergedRegion(new Region(rowCount, (short) 0, rowCount, (short) 7));

        rowCount++;
        HSSFRow stTitleTwo = sheet.createRow((short) rowCount);
        HSSFCell stTitleCellOne = stTitleTwo.createCell((short) 0);
        stTitleCellOne.setCellStyle(fontStyle);
        stTitleCellOne.setCellValue("from "
                + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to "
                + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate()));
        sheet.addMergedRegion(new Region(rowCount, (short) 0, rowCount, (short) 7));

        rowCount = rowCount + 2;

        HSSFRow rowhead = sheet.createRow((short) rowCount);

        HSSFCell snoHead = rowhead.createCell((short) 0);
        snoHead.setCellStyle(headingStyle);
        snoHead.setCellValue("S. NO");

        HSSFCell ifscHead = rowhead.createCell((short) 1);
        ifscHead.setCellStyle(headingStyle);
        ifscHead.setCellValue("Bank IFSC");

        HSSFCell txnCountHead = rowhead.createCell((short) 2);
        txnCountHead.setCellStyle(headingStyle);
        txnCountHead.setCellValue("Txn Count");

        HSSFCell amountHead = rowhead.createCell((short) 3);
        amountHead.setCellStyle(headingStyle);
        amountHead.setCellValue("Sum of Txn AMT (Rs)");

        if (reportTitle.equals(inwSummaryReport)) {

            HSSFCell txnCompletedCountHead = rowhead.createCell((short) 4);
            txnCompletedCountHead.setCellStyle(headingStyle);
            txnCompletedCountHead.setCellValue("TXN count Completed/Credited ");

            HSSFCell txnCompletedCountAmtHead = rowhead.createCell((short) 5);
            txnCompletedCountAmtHead.setCellStyle(headingStyle);
            txnCompletedCountAmtHead.setCellValue("Sum of Completed/Credited AMT");

            HSSFCell txnRtnCountHead = rowhead.createCell((short) 6);
            txnRtnCountHead.setCellStyle(headingStyle);
            txnRtnCountHead.setCellValue("TXN count Inward Returned");

            HSSFCell txnRtnCountAmtHead = rowhead.createCell((short) 7);
            txnRtnCountAmtHead.setCellStyle(headingStyle);
            txnRtnCountAmtHead.setCellValue("Sum of Inward Returned AMT");
        } else {

            HSSFCell txnCompletedCountHead = rowhead.createCell((short) 4);
            txnCompletedCountHead.setCellStyle(headingStyle);
            txnCompletedCountHead.setCellValue("TXN count Settled/Credited ");

            HSSFCell txnCompletedCountAmtHead = rowhead.createCell((short) 5);
            txnCompletedCountAmtHead.setCellStyle(headingStyle);
            txnCompletedCountAmtHead.setCellValue("Sum of Settled/Credited AMT");

            HSSFCell txnRtnCountHead = rowhead.createCell((short) 6);
            txnRtnCountHead.setCellStyle(headingStyle);
            txnRtnCountHead.setCellValue("TXN count Outward Returned");

            HSSFCell txnRtnCountAmtHead = rowhead.createCell((short) 7);
            txnRtnCountAmtHead.setCellStyle(headingStyle);
            txnRtnCountAmtHead.setCellValue("Sum of Outward Returned AMT");
        }
        int rowIndex = 1;
        for (Iterator itr = returnedList.iterator(); itr.hasNext();) {

            ReportDTO reportDTO = (ReportDTO) itr.next();
            HSSFRow row = null;

            rowCount++;

            row = sheet.createRow(rowCount);

            HSSFCell snoCell = row.createCell((short) 0);
            snoCell.setCellStyle(contentStyle);
            snoCell.setCellValue(rowIndex);
            rowIndex++;

            HSSFCell ifscCell = row.createCell((short) 1);
            ifscCell.setCellStyle(stringStyle);
            if (reportDTO.getSenderAddress() != null) {
                ifscCell.setCellValue(reportDTO.getSenderAddress());
            } else {
                ifscCell.setCellValue("");
            }

            HSSFCell txnCountCell = row.createCell((short) 2);
            txnCountCell.setCellStyle(stringRightStyle);
            if (reportDTO.getCount() != 0) {
                txnCountCell.setCellValue(reportDTO.getCount());
            } else {
                txnCountCell.setCellValue("");
            }

            HSSFCell txnAmountCell = row.createCell((short) 3);
            txnAmountCell.setCellStyle(stringRightStyle);
            if (reportDTO.getAmt() != null && !(reportDTO.getAmt().equals("0"))) {
                txnAmountCell.setCellValue(reportDTO.getAmt());
            } else {
                txnAmountCell.setCellValue("");
            }

            HSSFCell cmpTxnCountCell = row.createCell((short) 4);
            cmpTxnCountCell.setCellStyle(stringRightStyle);
            if (reportDTO.getCompletedTxnCount() != 0) {
                cmpTxnCountCell.setCellValue(reportDTO.getCompletedTxnCount());
            } else {
                cmpTxnCountCell.setCellValue("");
            }

            HSSFCell cmpTxnAmtCell = row.createCell((short) 5);
            cmpTxnAmtCell.setCellStyle(stringRightStyle);
            if (reportDTO.getCompletedTxnAmount() != null && !(reportDTO.getCompletedTxnAmount().equals("0"))) {
                cmpTxnAmtCell.setCellValue(reportDTO.getCompletedTxnAmount());
            } else {
                cmpTxnAmtCell.setCellValue("");
            }

            HSSFCell rtnTxnCountCell = row.createCell((short) 6);
            rtnTxnCountCell.setCellStyle(stringRightStyle);
            if (reportDTO.getRtnTxnCount() != 0) {
                rtnTxnCountCell.setCellValue(reportDTO.getRtnTxnCount());
            } else {
                rtnTxnCountCell.setCellValue("");
            }

            HSSFCell rtnTxnAmtCell = row.createCell((short) 7);
            rtnTxnAmtCell.setCellStyle(stringRightStyle);
            if (reportDTO.getRtnTxnAmount() != null && !(reportDTO.getRtnTxnAmount().equals("0"))) {
                rtnTxnAmtCell.setCellValue(reportDTO.getRtnTxnAmount());
            } else {
                rtnTxnAmtCell.setCellValue("");
            }
        }

        wb.write(out);
        out.flush();
        out.close();

    } catch (Exception e) {
        logger.error("Exception while creating Excel sheet file" + e.getMessage());
        throw new Exception("Exception while creating Excel sheet file" + e);
    }
}

From source file:com.objectfrontier.insta.neft.reports.bean.InstaNEFTReportBean.java

License:Open Source License

/**
  * Method used to export the bank wise detailed report in to Excel for both Inward and Outward.
  *//from   w  w w . j  a  va  2s  .c  om
  * @param out ServletOutputStream
  *
  */
public void generateNEFTInwBankDetailedReportToExcel(ServletOutputStream out) throws Exception {

    try {

        HSSFWorkbook wb = new HSSFWorkbook();

        HSSFSheet sheet = wb.createSheet(reportTitle);

        HSSFCellStyle titleStyle = wb.createCellStyle();
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        HSSFCellStyle fontStyle = wb.createCellStyle();
        HSSFFont fontSize = wb.createFont();
        fontSize.setFontHeightInPoints((short) 10);
        fontSize.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        fontStyle.setFont(fontSize);
        fontStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        sheet.setColumnWidth((short) 0, (short) 2000);
        sheet.setColumnWidth((short) 1, (short) 4000);
        sheet.setColumnWidth((short) 2, (short) 6000);
        sheet.setColumnWidth((short) 3, (short) 5000);
        sheet.setColumnWidth((short) 4, (short) 3000);
        sheet.setColumnWidth((short) 5, (short) 6000);
        sheet.setColumnWidth((short) 6, (short) 3000);
        sheet.setColumnWidth((short) 7, (short) 6000);
        sheet.setColumnWidth((short) 8, (short) 2500);
        sheet.setColumnWidth((short) 9, (short) 6000);
        sheet.setColumnWidth((short) 10, (short) 3000);
        sheet.setColumnWidth((short) 11, (short) 6000);
        sheet.setColumnWidth((short) 12, (short) 4500);

        HSSFCellStyle contentStyle = wb.createCellStyle();
        contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

        HSSFCellStyle stringStyle = wb.createCellStyle();
        stringStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        stringStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        stringStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        stringStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        stringStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

        HSSFFont headingFont = wb.createFont();
        headingFont.setFontHeightInPoints((short) 9);
        headingFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        HSSFCellStyle headingStyle = wb.createCellStyle();

        headingStyle.setFont(headingFont);
        headingStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headingStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
        headingStyle.setFillPattern(HSSFCellStyle.BIG_SPOTS);
        headingStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        headingStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        headingStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headingStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headingStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headingStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

        HSSFCellStyle titleRightStyle = wb.createCellStyle();
        titleRightStyle.setFont(headingFont);
        titleRightStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        titleRightStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        titleRightStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        titleRightStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        titleRightStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

        HSSFCellStyle titleLeftStyle = wb.createCellStyle();
        titleLeftStyle.setFont(headingFont);
        titleLeftStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        titleLeftStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        titleLeftStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        titleLeftStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        titleLeftStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

        int rowCount = 2;

        String dateForm = currentReportPrintTime.substring(0, 11);
        String time = currentReportPrintTime.substring(11);
        HSSFRow reportTimeRow = sheet.createRow((short) rowCount);
        HSSFCell reportTimeRowCell = reportTimeRow.createCell((short) 0);
        reportTimeRowCell.setCellStyle(titleRightStyle);
        reportTimeRowCell.setCellValue(
                "Report Printed on " + InstaReportUtil.getDateInSpecificFormat(dateFormat, dateForm) + time);
        sheet.addMergedRegion(new Region(rowCount, (short) 0, rowCount, (short) 12));

        rowCount++;
        HSSFRow stTitle = sheet.createRow((short) rowCount);
        HSSFCell title = stTitle.createCell((short) 0);
        title.setCellStyle(fontStyle);
        title.setCellValue(reportTitle);
        sheet.addMergedRegion(new Region(rowCount, (short) 0, rowCount, (short) 12));

        rowCount++;
        HSSFRow stTitleTwo = sheet.createRow((short) rowCount);
        HSSFCell stTitleCellOne = stTitleTwo.createCell((short) 0);
        stTitleCellOne.setCellStyle(fontStyle);
        stTitleCellOne.setCellValue("from "
                + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getValueDate()) + " to "
                + InstaReportUtil.getDateInSpecificFormat(dateFormat, getReportDto().getToDate()));
        sheet.addMergedRegion(new Region(rowCount, (short) 0, rowCount, (short) 12));

        rowCount = rowCount + 2;
        if (getReportMap().size() != 0) {

            Set set = getReportMap().entrySet();
            for (Iterator z = set.iterator(); z.hasNext();) {

                Map.Entry<String, List<TransactionInfo>> entry = (Map.Entry<String, List<TransactionInfo>>) z
                        .next();
                List exportXLS = new ArrayList(1);
                exportXLS.addAll(entry.getValue());

                String bankName = entry.getKey().substring(0, 4);
                String sumTxnAmount = entry.getKey().substring(5);

                HSSFRow titleRowOne = sheet.createRow((short) rowCount);

                HSSFCell bankNameCell = titleRowOne.createCell((short) 0);
                bankNameCell.setCellStyle(titleRightStyle);
                if (getTranType().equals("inward")) {
                    bankNameCell.setCellValue("Sender Bank : ");
                } else {
                    bankNameCell.setCellValue(" Receiver Bank : ");
                }
                sheet.addMergedRegion(new Region(rowCount, (short) 0, rowCount, (short) 6));

                HSSFCell bankNameValCell = titleRowOne.createCell((short) 7);
                bankNameValCell.setCellStyle(titleLeftStyle);
                bankNameValCell.setCellValue(bankName);
                sheet.addMergedRegion(new Region(rowCount, (short) 7, rowCount, (short) 12));

                rowCount++;
                HSSFRow titleRowTwo = sheet.createRow((short) rowCount);
                HSSFCell txnCountCell = titleRowTwo.createCell((short) 0);
                txnCountCell.setCellStyle(titleRightStyle);
                txnCountCell.setCellValue("Txn Count : ");
                sheet.addMergedRegion(new Region(rowCount, (short) 0, rowCount, (short) 6));

                HSSFCell txnCountValCell = titleRowTwo.createCell((short) 7);
                txnCountValCell.setCellStyle(titleLeftStyle);
                txnCountValCell.setCellValue(exportXLS.size());
                sheet.addMergedRegion(new Region(rowCount, (short) 7, rowCount, (short) 12));

                rowCount++;
                HSSFRow titleRowThree = sheet.createRow((short) rowCount);
                HSSFCell sumTxnAmountCell = titleRowThree.createCell((short) 0);
                sumTxnAmountCell.setCellStyle(titleRightStyle);
                sumTxnAmountCell.setCellValue("Sum of Txn Amount : ");
                sheet.addMergedRegion(new Region(rowCount, (short) 0, rowCount, (short) 6));

                HSSFCell sumTxnAmountValCell = titleRowThree.createCell((short) 7);
                sumTxnAmountValCell.setCellStyle(titleLeftStyle);
                sumTxnAmountValCell.setCellValue(sumTxnAmount);
                sheet.addMergedRegion(new Region(rowCount, (short) 7, rowCount, (short) 12));

                rowCount++;

                HSSFRow titleRow = sheet.createRow((short) rowCount);
                HSSFCell beneficiaryTitle = titleRow.createCell((short) 4);
                beneficiaryTitle.setCellStyle(headingStyle);
                beneficiaryTitle.setCellValue("Beneficiary Details");

                sheet.addMergedRegion(new Region(rowCount, (short) 4, rowCount, (short) 7));

                HSSFCell senderTitle = titleRow.createCell((short) 8);
                senderTitle.setCellStyle(headingStyle);
                senderTitle.setCellValue("Sender's Details");

                sheet.addMergedRegion(new Region(rowCount, (short) 8, rowCount, (short) 11));

                rowCount++;
                HSSFRow rowhead = sheet.createRow((short) rowCount);

                HSSFCell snoHead = rowhead.createCell((short) 0);
                snoHead.setCellStyle(headingStyle);
                snoHead.setCellValue("S .NO");

                HSSFCell valueDateHead = rowhead.createCell((short) 1);
                valueDateHead.setCellStyle(headingStyle);
                valueDateHead.setCellValue("Value Date");

                HSSFCell tranRefHead = rowhead.createCell((short) 2);
                tranRefHead.setCellStyle(headingStyle);
                tranRefHead.setCellValue("Transaction Ref. No");

                HSSFCell amountHead = rowhead.createCell((short) 3);
                amountHead.setCellStyle(headingStyle);
                amountHead.setCellValue("Amount(Rs)");

                HSSFCell benIfscHead = rowhead.createCell((short) 4);
                benIfscHead.setCellStyle(headingStyle);
                benIfscHead.setCellValue("IFSC");

                HSSFCell benACNameHead = rowhead.createCell((short) 5);
                benACNameHead.setCellStyle(headingStyle);
                benACNameHead.setCellValue("A/c Name");

                HSSFCell benACTypeHead = rowhead.createCell((short) 6);
                benACTypeHead.setCellStyle(headingStyle);
                benACTypeHead.setCellValue("A/c Type");

                HSSFCell benACNoHead = rowhead.createCell((short) 7);
                benACNoHead.setCellStyle(headingStyle);
                benACNoHead.setCellValue("A/c No");

                HSSFCell senIfscHead = rowhead.createCell((short) 8);
                senIfscHead.setCellStyle(headingStyle);
                senIfscHead.setCellValue("IFSC");

                HSSFCell senACNameHead = rowhead.createCell((short) 9);
                senACNameHead.setCellStyle(headingStyle);
                senACNameHead.setCellValue("A/c Name");

                HSSFCell senACTypeHead = rowhead.createCell((short) 10);
                senACTypeHead.setCellStyle(headingStyle);
                senACTypeHead.setCellValue("A/c Type");

                HSSFCell senACNoHead = rowhead.createCell((short) 11);
                senACNoHead.setCellStyle(headingStyle);
                senACNoHead.setCellValue("A/c No");

                HSSFCell tranStatusHead = rowhead.createCell((short) 12);
                tranStatusHead.setCellStyle(headingStyle);
                tranStatusHead.setCellValue("Transaction Status");

                HSSFRow row = null;

                for (int i = exportXLS.size(), rowIndex = 0; rowIndex < i; rowIndex++) {

                    TransactionInfo ti = (TransactionInfo) exportXLS.get(rowIndex);

                    rowCount++;

                    row = sheet.createRow(rowCount);

                    HSSFCell snoCell = row.createCell((short) 0);
                    snoCell.setCellStyle(contentStyle);
                    snoCell.setCellValue(rowIndex + 1);

                    HSSFCell valueDateCell = row.createCell((short) 1);
                    valueDateCell.setCellStyle(stringStyle);
                    if (ti.getValueDate() != null) {
                        valueDateCell.setCellValue(InstaReportUtil.getDateInSpecificFormat(dateFormat,
                                ti.getValueDate().toString()));
                    } else {
                        valueDateCell.setCellValue("");
                    }

                    HSSFCell tranRefCell = row.createCell((short) 2);
                    tranRefCell.setCellStyle(stringStyle);
                    if (ti.getUtrNo() != null) {
                        tranRefCell.setCellValue(ti.getUtrNo());
                    } else {
                        tranRefCell.setCellValue("");
                    }

                    HSSFCell amountCell = row.createCell((short) 3);
                    amountCell.setCellStyle(stringStyle);
                    if (ti.getAmount() != null) {
                        amountCell.setCellValue(ti.getAmount().toString());
                    } else {
                        amountCell.setCellValue("");
                    }

                    HSSFCell benIfscCell = row.createCell((short) 4);
                    benIfscCell.setCellStyle(stringStyle);
                    if (ti.getBeneficiaryInfo().getAccIfsc() != null) {
                        benIfscCell.setCellValue(ti.getBeneficiaryInfo().getAccIfsc());
                    } else {
                        benIfscCell.setCellValue("");
                    }

                    HSSFCell benACNameCell = row.createCell((short) 5);
                    benACNameCell.setCellStyle(stringStyle);
                    if (ti.getBeneficiaryInfo().getAccName() != null) {
                        benACNameCell.setCellValue(ti.getBeneficiaryInfo().getAccName());
                    } else {
                        benACNameCell.setCellValue("");
                    }

                    HSSFCell benACTypeCell = row.createCell((short) 6);
                    benACTypeCell.setCellStyle(stringStyle);
                    if (ti.getBeneficiaryInfo().getAccType() != null) {
                        benACTypeCell.setCellValue(ti.getBeneficiaryInfo().getAccType());
                    } else {
                        benACTypeCell.setCellValue("");
                    }

                    HSSFCell benACNoCell = row.createCell((short) 7);
                    benACNoCell.setCellStyle(stringStyle);
                    if (ti.getBeneficiaryInfo().getAccNo() != null) {
                        benACNoCell.setCellValue(ti.getBeneficiaryInfo().getAccNo());
                    } else {
                        benACNoCell.setCellValue("");
                    }

                    HSSFCell senIfscCell = row.createCell((short) 8);
                    senIfscCell.setCellStyle(stringStyle);
                    if (ti.getBeneficiaryInfo().getAccIfsc() != null) {
                        senIfscCell.setCellValue(ti.getSenderInfo().getAccIfsc());
                    } else {
                        senIfscCell.setCellValue("");
                    }

                    HSSFCell senACNameCell = row.createCell((short) 9);
                    senACNameCell.setCellStyle(stringStyle);
                    if (ti.getBeneficiaryInfo().getAccName() != null) {
                        senACNameCell.setCellValue(ti.getSenderInfo().getAccName());
                    } else {
                        senACNameCell.setCellValue("");
                    }

                    HSSFCell senACTypeCell = row.createCell((short) 10);
                    senACTypeCell.setCellStyle(stringStyle);
                    if (ti.getBeneficiaryInfo().getAccType() != null) {
                        senACTypeCell.setCellValue(ti.getSenderInfo().getAccType());
                    } else {
                        senACTypeCell.setCellValue("");
                    }

                    HSSFCell senACNoCell = row.createCell((short) 11);
                    senACNoCell.setCellStyle(stringStyle);
                    if (ti.getBeneficiaryInfo().getAccNo() != null) {
                        senACNoCell.setCellValue(ti.getSenderInfo().getAccNo());
                    } else {
                        senACNoCell.setCellValue("");
                    }

                    HSSFCell tranStatusCell = row.createCell((short) 12);
                    tranStatusCell.setCellStyle(stringStyle);
                    if (ti != null) {
                        tranStatusCell.setCellValue(ti.getStatusShortDesc());
                    } else {
                        tranStatusCell.setCellValue("");
                    }

                }

                rowCount = rowCount + 3;
            }
        }

        wb.write(out);
        out.flush();
        out.close();

    } catch (Exception e) {
        logger.error("Exception while creating Excel sheet file" + e.getMessage());
        throw new Exception("Exception while creating Excel sheet file" + e);
    }
}

From source file:com.openitech.util.HSSFWrapper.java

License:Apache License

public static final HSSFWorkbook getWorkbook(JTable source, boolean countRows) {
    HSSFWorkbook xls_workbook = new HSSFWorkbook();
    HSSFSheet xls_sheet = xls_workbook.createSheet("Pregled podatkov");
    HSSFPrintSetup xls_sheet_printsetup = xls_sheet.getPrintSetup();
    xls_sheet_printsetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
    xls_sheet_printsetup.setFitWidth((short) 1);

    TableColumnModel columnModel = source.getColumnModel();
    Enumeration<TableColumn> columns = columnModel.getColumns();

    HSSFRow xls_row = xls_sheet.createRow(0);
    short cell = 1;
    HSSFCellStyle xls_header_cell_style = xls_workbook.createCellStyle();
    HSSFFont xls_header_font = xls_workbook.createFont();

    xls_header_font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    xls_header_cell_style.setFont(xls_header_font);
    xls_header_cell_style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    xls_header_cell_style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    xls_header_cell_style.setFillForegroundColor(new HSSFColor.GREY_25_PERCENT().getIndex());
    //xls_header_cell_style.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);

    java.util.Map<String, HSSFCellStyle> cellStyles = new java.util.HashMap<String, HSSFCellStyle>();

    HSSFDataFormat xls_data_format = xls_workbook.createDataFormat();

    HSSFCellStyle xls_date_cell_style = xls_workbook.createCellStyle();
    xls_date_cell_style.setDataFormat(xls_data_format.getFormat("d.m.yyyy"));
    cellStyles.put("d.m.yyyy", xls_date_cell_style);

    HSSFCellStyle xls_double_cell_style = xls_workbook.createCellStyle();
    xls_double_cell_style.setDataFormat(xls_data_format.getFormat("#,##0.00"));
    cellStyles.put("#,##0.00", xls_double_cell_style);

    while (columns.hasMoreElements()) {
        TableColumn column = columns.nextElement();

        HSSFCell xls_cell = xls_row.createCell(cell++);
        xls_cell.setCellValue(new HSSFRichTextString(column.getHeaderValue().toString()));
        xls_cell.setCellStyle(xls_header_cell_style);
    }/*from   w  ww  . j a  va2s .  c  om*/

    TableModel tableModel = source.getModel();
    DbTableModel dbTableModel = (tableModel instanceof DbTableModel) ? (DbTableModel) tableModel : null;
    Integer fetchSize = null;

    if (dbTableModel != null) {
        try {
            fetchSize = dbTableModel.getDataSource().getFetchSize();
            dbTableModel.getDataSource().setFetchSize(2000);
        } catch (SQLException ex) {
            Logger.getLogger(HSSFWrapper.class.getName()).log(Level.WARNING, null, ex);
            fetchSize = null;
        }
    }

    short row = 1;

    JWProgressMonitor progress = new JWProgressMonitor((java.awt.Frame) null);

    progress.setTitle("Izvoz podatkov v Excel");
    progress.setMax(tableModel.getRowCount());

    progress.setVisible(true);

    try {
        while (row <= tableModel.getRowCount()) {
            xls_row = xls_sheet.createRow(row);
            cell = 0;

            HSSFCell xls_cell = xls_row.createCell(cell++);
            if (countRows) {
                xls_cell.setCellValue(new HSSFRichTextString(Short.toString(row)));
            }

            while (cell <= columnModel.getColumnCount()) {
                Object value = tableModel.getValueAt(source.convertRowIndexToModel(row - 1),
                        source.convertColumnIndexToModel(cell - 1));
                if (value != null) {
                    if (value instanceof DbTableModel.ColumnDescriptor.ValueMethod) {
                        DbTableModel.ColumnDescriptor.ValueMethod vm = (DbTableModel.ColumnDescriptor.ValueMethod) value;

                        if (vm.getColumnNames().size() == 1) {
                            java.util.List<Object> values = vm.getValues();
                            java.util.List<String> cellFormats = vm.getCellFormats();

                            for (String cellFormat : cellFormats) {
                                if (cellFormat != null) {
                                    if (!cellStyles.containsKey(cellFormat)) {
                                        HSSFCellStyle xls_cell_style = xls_workbook.createCellStyle();
                                        xls_cell_style.setDataFormat(xls_data_format.getFormat(cellFormat));
                                        cellStyles.put(cellFormat, xls_cell_style);
                                    }
                                }
                            }

                            Object vm_value = values.get(0);
                            HSSFCellStyle xls_cell_style = cellFormats.get(0) == null ? null
                                    : cellStyles.get(cellFormats.get(0));

                            if (vm_value != null) {
                                xls_cell = xls_row.createCell(cell);

                                if (vm_value instanceof java.util.Date) {
                                    xls_cell.setCellValue((java.util.Date) vm_value);
                                    xls_cell.setCellStyle(
                                            xls_cell_style == null ? xls_date_cell_style : xls_cell_style);
                                } else if (vm_value instanceof java.lang.Number) {
                                    xls_cell.setCellValue(((java.lang.Number) vm_value).doubleValue());
                                    if ((vm_value instanceof java.math.BigDecimal)
                                            || (vm_value instanceof java.lang.Double)
                                            || (vm_value instanceof java.lang.Float)) {
                                        xls_cell.setCellStyle(xls_cell_style == null ? xls_double_cell_style
                                                : xls_cell_style);
                                    }
                                } else if (vm_value instanceof java.lang.Boolean) {
                                    xls_cell.setCellValue(((java.lang.Boolean) vm_value).booleanValue());
                                } else {
                                    xls_cell.setCellValue(new HSSFRichTextString(value.toString()));
                                }
                            }
                        } else {
                            xls_cell = xls_row.createCell(cell);
                            xls_cell.setCellValue(new HSSFRichTextString(value.toString()));
                        }
                    } else {
                        xls_cell = xls_row.createCell(cell);
                        xls_cell.setCellValue(new HSSFRichTextString(value.toString()));
                    }
                }
                cell++;
            }

            row++;
            progress.next();
        }

        for (cell = 0; cell <= columnModel.getColumnCount(); cell++) {
            xls_sheet.autoSizeColumn(cell);
        }

        xls_sheet.createFreezePane(1, 1);
    } finally {
        progress.setVisible(false);

        if (fetchSize != null) {
            try {
                dbTableModel.getDataSource().setFetchSize(fetchSize);
            } catch (SQLException ex) {
                Logger.getLogger(HSSFWrapper.class.getName()).log(Level.WARNING, null, ex);
            }
        }
    }

    return xls_workbook;
}

From source file:com.pe.nisira.movil.view.action.RegistroPaleta.java

public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);/*from  w ww .  j a v  a 2 s  .  co  m*/
    HSSFRow header = sheet.getRow(0);
    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.GREEN.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        header.getCell(i).setCellStyle(cellStyle);
    }
}

From source file:com.qcadoo.mes.assignmentToShift.print.xls.AssignmentToShiftXlsStyleHelper.java

License:Open Source License

private HSSFCellStyle getHeaderStyle(final HSSFWorkbook workbook, final short borderLeft,
        final short borderRight, final short alignment, final short boldweight) {
    HSSFCellStyle style = workbook.createCellStyle();

    style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
    style.setBorderLeft(borderLeft);/*  w w  w  . ja va 2 s . c om*/
    style.setBorderRight(borderRight);
    style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);

    style.setAlignment(alignment);
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    style.setIndention((short) 3);
    style.setWrapText(true);

    style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    Font font = workbook.createFont();
    font.setFontName(HSSFFont.FONT_ARIAL);
    font.setFontHeightInPoints((short) 12);
    font.setBoldweight(boldweight);

    style.setFont(font);

    return style;
}

From source file:com.sammyun.ExcelView.java

License:Open Source License

/**
 * ?Excel//from w  w  w .jav  a2  s.  c  o  m
 * 
 * @param model ?
 * @param workbook workbook
 * @param request request
 * @param response response
 */
public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    Assert.notEmpty(properties);
    HSSFSheet sheet;
    if (StringUtils.isNotEmpty(sheetName)) {
        sheet = workbook.createSheet(sheetName);
    } else {
        sheet = workbook.createSheet();
    }
    int rowNumber = 0;
    if (titles != null && titles.length > 0) {
        HSSFRow header = sheet.createRow(rowNumber);
        header.setHeight((short) 400);
        for (int i = 0; i < properties.length; i++) {
            HSSFCell cell = header.createCell(i);
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
            cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            HSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short) 11);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            if (i == 0) {
                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
                HSSFComment comment = patriarch
                        .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4));
                comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B"
                        + "y" + " " + "M" + "o" + "S" + "ho" + "o" + "p"));
                cell.setCellComment(comment);
            }
            if (titles.length > i && titles[i] != null) {
                cell.setCellValue(titles[i]);
            } else {
                cell.setCellValue(properties[i]);
            }
            if (widths != null && widths.length > i && widths[i] != null) {
                sheet.setColumnWidth(i, widths[i]);
            } else {
                sheet.autoSizeColumn(i);
            }
        }
        rowNumber++;
    }
    if (data != null) {
        for (Object item : data) {
            HSSFRow row = sheet.createRow(rowNumber);
            for (int i = 0; i < properties.length; i++) {
                HSSFCell cell = row.createCell(i);
                if (converters != null && converters.length > i && converters[i] != null) {
                    Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]);
                    ConvertUtils.register(converters[i], clazz);
                    cell.setCellValue(BeanUtils.getProperty(item, properties[i]));
                    ConvertUtils.deregister(clazz);
                    if (clazz.equals(Date.class)) {
                        DateConverter dateConverter = new DateConverter();
                        dateConverter.setPattern(DEFAULT_DATE_PATTERN);
                        ConvertUtils.register(dateConverter, Date.class);
                    }
                } else {
                    cell.setCellValue(BeanUtils.getProperty(item, properties[i]));
                }
                if (rowNumber == 0 || rowNumber == 1) {
                    if (widths != null && widths.length > i && widths[i] != null) {
                        sheet.setColumnWidth(i, widths[i]);
                    } else {
                        sheet.autoSizeColumn(i);
                    }
                }
            }
            rowNumber++;
        }
    }
    if (contents != null && contents.length > 0) {
        rowNumber++;
        for (String content : contents) {
            HSSFRow row = sheet.createRow(rowNumber);
            HSSFCell cell = row.createCell(0);
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setColor(HSSFColor.GREY_50_PERCENT.index);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(content);
            rowNumber++;
        }
    }
    response.setContentType("application/force-download");
    if (StringUtils.isNotEmpty(filename)) {
        response.setHeader("Content-disposition",
                "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));
    } else {
        response.setHeader("Content-disposition", "attachment");
    }
}

From source file:com.seer.datacruncher.factories.streams.SchemaStreamsExcel.java

License:Open Source License

@Override
public byte[] getDownloadableStreams() {
    if (maxVertical == 0)
        return null;
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet("Sheet 1");
    HSSFRow headerRow = s.createRow(0);//from ww  w  .j  a  v a2  s.  co  m
    HSSFCellStyle style = wb.createCellStyle();
    style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    for (int i = 0; i < maxVertical; i++) {
        Document doc = getNewDomDocument();
        plainListChilds(doc, i, schemaEnt.getIdSchema(), linkedFieldsPaths);
        NodeList childList = DomToOtherFormat.getRootNodeOfDocument(doc).getChildNodes();
        HSSFRow row = s.createRow(i + 1);
        for (int j = 0; j < childList.getLength(); j++) {
            Node child = childList.item(j);
            if (i == 0) {
                HSSFCell cell = headerRow.createCell(j);
                cell.setCellValue(child.getNodeName());
                cell.setCellStyle(style);
            }
            HSSFCell cell = row.createCell(j);
            cell.setCellValue(child.getTextContent());
        }
    }
    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    try {
        wb.write(bos);
    } catch (IOException e) {
        log.error("IO Exception, excel generation", e);
    } finally {
        try {
            bos.close();
        } catch (IOException e) {
            log.error("IO stream closure exception, excel generation", e);
        }
    }
    return bos.toByteArray();
}

From source file:com.sevenorcas.openstyle.app.service.spreadsheet.SpreadSheet.java

/**
 * Set a cell style as header/*  w  w  w .java2s  .  c o  m*/
 * 
 * Thanks to http://www.experts-exchange.com/Programming/Languages/Java/Q_24242777.html
 * 
 * @param workbook
 * @param style id
 * @param rowStyle
 * @param column
 * @param wb
 * @return
 */
protected void setStyleHeader(HSSFWorkbook wb, HSSFCellStyle style, int styleId) { //EX1, int row, int column, SpreadsheetCell cell){
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    style.setFont(font);
    style.setLocked(true);
    style.setBottomBorderColor(HSSFColor.WHITE.index);
    style.setLeftBorderColor(HSSFColor.WHITE.index);
    style.setRightBorderColor(HSSFColor.WHITE.index);
    style.setTopBorderColor(HSSFColor.WHITE.index);
    style.setFillForegroundColor(getHeaderBGColorIndex());
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
}

From source file:com.sevenorcas.openstyle.app.service.spreadsheet.SpreadSheet.java

/**
 * Format a style//from   www.ja  va  2  s.  c  o  m
 * @param wb
 * @return
 */
public void setCellStyle(HSSFWorkbook wb, HSSFCellStyle style, SpreadsheetCell cell, int styleId) {

    int row = cell != null ? cell.getRow() : -1;
    int col = cell != null ? cell.getColumn() : -1;

    //Check row or column formats
    int formatTyp = cell != null ? cell.getFormatTyp() : FORMAT_NORMAL;
    formatTyp = formatTyp == FORMAT_NORMAL ? testRowFormat(row, FORMAT_HEADER, formatTyp) : formatTyp;
    formatTyp = formatTyp == FORMAT_NORMAL ? testRowFormat(row, FORMAT_BOLD, formatTyp) : formatTyp;
    formatTyp = formatTyp == FORMAT_NORMAL ? testColFormat(col, FORMAT_HEADER, formatTyp) : formatTyp;
    formatTyp = formatTyp == FORMAT_NORMAL ? testColFormat(col, FORMAT_BOLD, formatTyp) : formatTyp;

    switch (formatTyp) {
    case FORMAT_HEADER:
        if (cell != null) {
            cell.setFormatTyp(FORMAT_HEADER);
        }
        setStyleHeader(wb, style, cell.getStyleId());
        break;
    case FORMAT_BOLD:
        if (cell != null) {
            cell.setFormatTyp(FORMAT_BOLD);
        }
        setStyleBold(wb, style, cell.getStyleId());
        break;
    }

    //Check row or column justifications
    int justification = cell != null ? cell.getJustification() : ALIGN_UNDEFINED;
    justification = justification == ALIGN_UNDEFINED ? testRowFormat(row, ALIGN_LEFT, justification)
            : justification;
    justification = justification == ALIGN_UNDEFINED ? testRowFormat(row, ALIGN_CENTER, justification)
            : justification;
    justification = justification == ALIGN_UNDEFINED ? testRowFormat(row, ALIGN_RIGHT, justification)
            : justification;
    justification = justification == ALIGN_UNDEFINED ? testColFormat(col, ALIGN_LEFT, justification)
            : justification;
    justification = justification == ALIGN_UNDEFINED ? testColFormat(col, ALIGN_CENTER, justification)
            : justification;
    justification = justification == ALIGN_UNDEFINED ? testColFormat(col, ALIGN_RIGHT, justification)
            : justification;

    switch (justification) {
    case ALIGN_LEFT:
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        break;
    case ALIGN_RIGHT:
        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        break;
    case ALIGN_CENTER:
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        break;
    default: //none
    }

    //Check row or column vertical align
    int valign = cell != null ? cell.getValign() : VALIGN_UNDEFINED;
    valign = valign == VALIGN_UNDEFINED ? testRowFormat(row, VALIGN_TOP, valign) : valign;
    valign = valign == VALIGN_UNDEFINED ? testRowFormat(row, VALIGN_CENTER, valign) : valign;
    valign = valign == VALIGN_UNDEFINED ? testRowFormat(row, VALIGN_BOTTOM, valign) : valign;
    valign = valign == VALIGN_UNDEFINED ? testColFormat(col, VALIGN_TOP, valign) : valign;
    valign = valign == VALIGN_UNDEFINED ? testColFormat(col, VALIGN_CENTER, valign) : valign;
    valign = valign == VALIGN_UNDEFINED ? testColFormat(col, VALIGN_BOTTOM, valign) : valign;

    switch (valign) {
    case VALIGN_TOP:
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
        break;
    case VALIGN_CENTER:
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        break;
    case VALIGN_BOTTOM:
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_BOTTOM);
        break;
    default:
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    }

    //Text wrap
    int wrap = cell != null ? cell.getWrap() : -1;
    wrap = wrap != -1 ? testRowFormat(row, TEXT_WARP_ON, wrap) : wrap;
    wrap = wrap != -1 ? testColFormat(col, TEXT_WARP_ON, wrap) : wrap;
    if (wrap == TEXT_WARP_ON) {
        style.setWrapText(true);
    }

    if (cell != null && cell.getBgColor() != null) {
        SpreadsheetColor c = cell.getBgColor();
        style.setFillForegroundColor(setColor(wb, c).getIndex());
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    } else if (getRowColor(row, true) != null) {
        SpreadsheetColor c = getRowColor(row, true);
        style.setFillForegroundColor(setColor(wb, c).getIndex());
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    } else if (getColColor(col, true) != null) {
        SpreadsheetColor c = getColColor(col, true);
        style.setFillForegroundColor(setColor(wb, c).getIndex());
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    }

    int border = cell != null ? cell.getBorder() : -1;
    border = border != -1 ? testRowFormat(row, BORDER_ON, border) : border;
    border = border != -1 ? testColFormat(col, BORDER_ON, border) : border;
    if (border == BORDER_ON) {
        style.setBorderTop((short) 1);
        style.setBorderBottom((short) 1);
        style.setBorderRight((short) 1);
        style.setBorderLeft((short) 1);
    }

    styles.put(styleId, style);
}