Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook createCellStyle

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createCellStyle

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. createCellStyle

Introduction

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

Prototype

@Override
public HSSFCellStyle createCellStyle()

Source Link

Document

Create a new Cell style and add it to the workbook's style table.

Usage

From source file:com.itn.excelDao.ExcelView.java

@Override
protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest requesr,
        HttpServletResponse response) throws Exception {

    // get data model which is passed by the Spring container
    List<Users> allUsers = (List<Users>) model.get("allUsers");

    //Create new excel sheet
    HSSFSheet sheet = workbook.createSheet("Java Books");
    sheet.setDefaultColumnWidth(30);/*from  ww w.ja  va  2  s .  c om*/

    //create style for header cells
    CellStyle style = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setFontName("Arial");
    style.setFillForegroundColor(HSSFColor.BLUE.index);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.WHITE.index);
    style.setFont(font);

    // create header row
    HSSFRow header = sheet.createRow(0);

    header.createCell(0).setCellValue("id");
    header.getCell(0).setCellStyle(style);

    header.createCell(1).setCellValue("First Name");
    header.getCell(1).setCellStyle(style);

    header.createCell(2).setCellValue("Last Name");
    header.getCell(2).setCellStyle(style);

    header.createCell(3).setCellValue("Email Address");
    header.getCell(3).setCellStyle(style);

    // create data rows
    int rowCount = 1;

    for (Users aUsers : allUsers) {
        HSSFRow aRow = sheet.createRow(rowCount++);
        aRow.createCell(0).setCellValue(aUsers.getId());
        aRow.createCell(1).setCellValue(aUsers.getFirstName());
        aRow.createCell(2).setCellValue(aUsers.getLastName());
        aRow.createCell(3).setCellValue(aUsers.getEmail());

    }

}

From source file:com.kahlon.guard.controller.DocumentManager.java

public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);/*from   w  w  w  .ja  v  a2s. c om*/
    HSSFRow header = sheet.getRow(0);

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.BLUE.index);

    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    HSSFFont font = wb.createFont();
    font.setColor(HSSFColor.WHITE.index);
    cellStyle.setFont(font);
    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);
        cell.setCellStyle(cellStyle);
    }
}

From source file:com.kcs.action.ForwardContractAction.java

@Override
@SuppressWarnings("empty-statement")
public String export() throws Exception {
    list = getService().findByCriteria(DateUtil.convertDateFromJsp(getDataSetDate()));

    getLogger().debug("exportExcel : begin...");
    setFileName("Forward contract Report.xls");
    DateFormat dateFormat = new SimpleDateFormat("dd MMM yyyy");
    HSSFWorkbook myWorkBook = new HSSFWorkbook();
    HSSFCellStyle style = myWorkBook.createCellStyle();
    HSSFCellStyle styleFont = myWorkBook.createCellStyle();
    HSSFFont fontB = myWorkBook.createFont();
    fontB.setBoldweight(Font.BOLDWEIGHT_BOLD);
    styleFont.setFont(fontB);/* w  ww .j  a va2  s . c  om*/

    list = sortSheet(list);
    List<String> listSheet = countSheet(list);

    for (int iListSheet = 0; iListSheet < listSheet.size(); iListSheet++) {
        List<ForwardContract> sheetObject = getSheetByAtSheet(list, listSheet.get(iListSheet));
        HSSFSheet FW1 = myWorkBook.createSheet(listSheet.get(iListSheet));
        List<String> currencyList = getCurrency(sheetObject);

        //------------------------- ROW 1 -----------------------------//
        Row FW1_row_0 = FW1.createRow(0);

        FW1_row_0.createCell(0).setCellValue(" FORWARD CONTRACT");
        CellUtil.setAlignment(FW1_row_0.getCell(0), myWorkBook, CellStyle.ALIGN_CENTER);

        HSSFCellStyle styleRow1 = myWorkBook.createCellStyle();

        styleRow1.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleRow1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleRow1.setFont(fontB);

        FW1_row_0.getCell(0).setCellStyle(styleRow1);

        FW1_row_0.createCell(1)
                .setCellValue(" ? ?()");
        FW1_row_0.getCell(1).setCellStyle(styleRow1);

        // FW1_row_0.getCell(8).setCellStyle(styleFont);

        //------------------------- ROW 2 -----------------------------//
        Row FW1_row_1 = FW1.createRow(1);

        FW1_row_1.createCell(0).setCellValue("FW");
        //        FW1_row_1.getCell(0).setCellStyle(styleBR);

        //     FW1.addMergedRegion(new CellRangeAddress(1,1,7,8));

        FW1.autoSizeColumn(0);
        FW1.autoSizeColumn(7);
        FW1.autoSizeColumn(8);

        Row FW1_row_2 = FW1.createRow(2);
        FW1_row_2.createCell(0).setCellValue("?");
        //CellUtil.setAlignment(FW1_row_2.getCell(0), myWorkBook, CellStyle.ALIGN_RIGHT);
        HSSFCellStyle style2 = myWorkBook.createCellStyle();

        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

        FW1_row_2.getCell(0).setCellStyle(style2);

        Row FW1_row_3 = FW1.createRow(3);
        FW1_row_3.createCell(0).setCellValue("?");

        HSSFCellStyle style3 = myWorkBook.createCellStyle();
        style3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style3.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style3.setAlignment(HSSFCellStyle.ALIGN_LEFT);

        FW1_row_3.getCell(0).setCellStyle(style3);

        FW1.createRow(4).createCell(0).setCellValue("?");
        FW1.createRow(5).createCell(0).setCellValue("? (129027)");
        FW1.createRow(6).createCell(0).setCellValue(
                "???");
        FW1.createRow(7).createCell(0).setCellValue("  ?");
        FW1.createRow(8).createCell(0).setCellValue("  - ?? (129030)");
        FW1.createRow(9).createCell(0)
                .setCellValue("  - ??? (129031)");
        FW1.createRow(10).createCell(0).setCellValue("  ??");
        FW1.createRow(11).createCell(0).setCellValue("  ");
        FW1.createRow(12).createCell(0).setCellValue("  - ?? (129034)");
        FW1.createRow(13).createCell(0)
                .setCellValue("  - ??? (129035)");
        FW1.createRow(14).createCell(0).setCellValue("  ???");
        FW1.createRow(15).createCell(0).setCellValue("  - ?? (129037)");
        FW1.createRow(16).createCell(0)
                .setCellValue("  - ??? (129038) ");
        FW1.createRow(17).createCell(0)
                .setCellValue("  ?");
        FW1.createRow(18).createCell(0).setCellValue("  - ?? (129040)");
        FW1.createRow(19).createCell(0)
                .setCellValue("  - ??? (129041) ");
        FW1.createRow(20).createCell(0)
                .setCellValue("  ??");
        FW1.createRow(21).createCell(0).setCellValue("  - ?? (129043)");
        FW1.createRow(22).createCell(0)
                .setCellValue("  - ??? (129044)");
        FW1.createRow(23).createCell(0)
                .setCellValue("  ?");
        FW1.createRow(24).createCell(0).setCellValue("  - ?? (129046)");
        FW1.createRow(25).createCell(0)
                .setCellValue("  - ??? (129047) ");
        FW1.createRow(26).createCell(0).setCellValue("????");
        FW1.createRow(27).createCell(0).setCellValue("  ??");
        FW1.createRow(28).createCell(0).setCellValue("  - ?? (129050)");
        FW1.createRow(29).createCell(0)
                .setCellValue("  - ??? (129051)");
        FW1.createRow(30).createCell(0)
                .setCellValue("  ");
        FW1.createRow(31).createCell(0).setCellValue("  - ?? (129053)");
        FW1.createRow(32).createCell(0)
                .setCellValue("  - ??? (129054)");
        FW1.createRow(33).createCell(0).setCellValue("  ?  ");
        FW1.createRow(34).createCell(0).setCellValue("  - ?? (129056) ");
        FW1.createRow(35).createCell(0)
                .setCellValue("  - ??? (129057)  ");
        FW1.createRow(36).createCell(0).setCellValue("   ");
        FW1.createRow(37).createCell(0).setCellValue("  - ?? (129059) ");
        FW1.createRow(38).createCell(0)
                .setCellValue("  - ??? (129060)  ");
        FW1.createRow(39).createCell(0).setCellValue("? (129061) ");
        FW1.createRow(40).createCell(0)
                .setCellValue("? (129062) ");
        //    FW1.getRow(40).getCell(0).setCellStyle(styleBR);

        FW1.getRow(4).getCell(0).setCellStyle(styleFont);
        FW1.getRow(5).getCell(0).setCellStyle(styleFont);
        FW1.getRow(6).getCell(0).setCellStyle(styleFont);
        FW1.getRow(7).getCell(0).setCellStyle(styleFont);
        FW1.getRow(11).getCell(0).setCellStyle(styleFont);
        FW1.getRow(14).getCell(0).setCellStyle(styleFont);
        FW1.getRow(17).getCell(0).setCellStyle(styleFont);
        FW1.getRow(20).getCell(0).setCellStyle(styleFont);
        FW1.getRow(23).getCell(0).setCellStyle(styleFont);
        FW1.getRow(26).getCell(0).setCellStyle(styleFont);
        FW1.getRow(30).getCell(0).setCellStyle(styleFont);
        FW1.getRow(33).getCell(0).setCellStyle(styleFont);
        FW1.getRow(36).getCell(0).setCellStyle(styleFont);
        FW1.getRow(39).getCell(0).setCellStyle(styleFont);
        FW1.getRow(40).getCell(0).setCellStyle(styleFont);

        int cellCurrency = 1;

        for (int iCurrencyList = 0; iCurrencyList < currencyList.size(); iCurrencyList++) {

            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);

            String currencyName = currencyList.get(iCurrencyList);
            FW1_row_2.createCell(cellCurrency).setCellValue(currencyName);
            FW1_row_3.createCell(cellCurrency).setCellValue("");
            FW1_row_2.getCell(cellCurrency).setCellStyle(style);
            FW1_row_2.createCell(cellCurrency + 1).setCellStyle(style);

            FW1_row_3.getCell(cellCurrency).setCellStyle(style);

            List<ForwardContract> tmpGroupCCY = getGroupByCCY(sheetObject, currencyName);

            for (ForwardContract objCCY : tmpGroupCCY) {
                FW1.getRow(5).createCell(cellCurrency).setCellValue(objCCY.getSELL_AMT_129027() + "");

                if ("????"
                        .equals(objCCY.getTRANS_TYPE())) {
                    if ("New Forward".equals(objCCY.getLIST_ITEM().trim())) {
                        FW1.getRow(8).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129029(), true));
                        FW1.getRow(12).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129033(), true));
                        FW1.getRow(15).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129036(), true));
                        FW1.getRow(18).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129039(), true));
                        FW1.getRow(21).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129042(), true));
                        FW1.getRow(24).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129045(), true));

                        FW1.getRow(8).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129029(), true));
                        FW1.getRow(12).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129033(), true));
                        FW1.getRow(15).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129036(), true));
                        FW1.getRow(18).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129039(), true));
                        FW1.getRow(21).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129042(), true));
                        FW1.getRow(24).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129045(), true));
                    } else if ("Utilize Forward".equals(objCCY.getLIST_ITEM().trim())) {

                        FW1.getRow(9).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129029(), false));
                        FW1.getRow(13).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129033(), false));
                        FW1.getRow(16).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129036(), false));
                        FW1.getRow(19).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129039(), false));
                        FW1.getRow(22).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129042(), false));
                        FW1.getRow(25).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129045(), false));

                        FW1.getRow(9).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129029(), false));
                        FW1.getRow(13).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129033(), false));
                        FW1.getRow(16).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129036(), false));
                        FW1.getRow(19).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129039(), false));
                        FW1.getRow(22).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129042(), false));
                        FW1.getRow(25).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129045(), false));

                    }
                } else if ("????"
                        .equals(objCCY.getTRANS_TYPE().trim())) {
                    if ("New Forward".equals(objCCY.getLIST_ITEM())) {
                        FW1.getRow(28).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129049(), true));
                        FW1.getRow(31).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129052(), true));
                        FW1.getRow(34).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129055(), true));
                        FW1.getRow(37).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129058(), true));

                        FW1.getRow(28).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129049(), true));
                        FW1.getRow(31).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129052(), true));
                        FW1.getRow(34).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129055(), true));
                        FW1.getRow(37).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129058(), true));

                    } else if ("Utilize Forward".equals(objCCY.getLIST_ITEM().trim())) {

                        FW1.getRow(29).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129049(), false));
                        FW1.getRow(32).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129052(), false));
                        FW1.getRow(35).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129055(), false));
                        FW1.getRow(38).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129058(), false));

                        FW1.getRow(29).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129049(), false));
                        FW1.getRow(32).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129052(), false));
                        FW1.getRow(35).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129055(), false));
                        FW1.getRow(38).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129058(), false));

                    }
                } else if ("?".equals(objCCY.getTRANS_TYPE())) {

                }

                FW1.getRow(39).createCell(cellCurrency)
                        .setCellValue(convertNewForward(objCCY.getSELL_AMT_129061(), false));
                FW1.getRow(40).createCell(cellCurrency)
                        .setCellValue(convertNewForward(objCCY.getSELL_AMT_129062(), false));

                FW1.getRow(39).createCell(cellCurrency + 1)
                        .setCellValue(convertNewForward(objCCY.getBUY_AMT_129061(), false));
                FW1.getRow(40).createCell(cellCurrency + 1)
                        .setCellValue(convertNewForward(objCCY.getBUY_AMT_129062(), false));

            }
            //   
            // FW1.getRow(createRow++).createCell(cellCurrency).setCellValue(tmpGroup.getSELL_AMT_129033()+"");

            CellUtil.setAlignment(FW1_row_2.getCell(cellCurrency), myWorkBook, CellStyle.ALIGN_CENTER);
            FW1_row_2.getCell(cellCurrency).setCellStyle(style);
            FW1.addMergedRegion(new CellRangeAddress(2, 2, cellCurrency, ++cellCurrency));

            FW1_row_3.createCell(cellCurrency).setCellValue("");
            HSSFCellStyle styleTop = style;
            styleTop.setBorderTop(HSSFCellStyle.BORDER_THIN);
            FW1_row_3.getCell(cellCurrency).setCellStyle(styleTop);

            cellCurrency++;
        }
        System.out.println("cellCurrency ++++ " + cellCurrency);
        int finishM = cellCurrency - 3;

        int noOfColumns = FW1.getRow(2).getLastCellNum();
        int noOfRow = FW1.getLastRowNum();

        //          HSSFPalette palette = myWorkBook.getCustomPalette();
        //          HSSFColor hssfColor = null;
        //
        //
        //    palette.setColorAtIndex(HSSFColor.WHITE.index, (byte) 255, (byte) 255, (byte) 255);
        //    hssfColor = palette.getColor(HSSFColor.WHITE.index);
        //    
        //    
        //
        //    HSSFCellStyle styleBG = myWorkBook.createCellStyle();
        //    styleBG.setFillForegroundColor(hssfColor.getIndex());
        //    styleBG.setFillPattern(CellStyle.SOLID_FOREGROUND);  

        for (int i = 0; i < noOfRow; i++) {

            for (int j = 0; j < noOfColumns; j++) {
                FW1.autoSizeColumn(i);
                //  row.getCell(j).setCellStyle(styleBG);
            }
        }

        for (int i = 4; i < noOfRow + 1; i++) {

            for (int j = 1; j < noOfColumns; j++) {
                HSSFCellStyle style6 = myWorkBook.createCellStyle();
                try {

                    if (j == 1) {
                        style6.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                        style6.setBorderRight(HSSFCellStyle.BORDER_THIN);
                        style6.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
                    } else {
                        style6.setBorderRight(HSSFCellStyle.BORDER_THIN);
                        style6.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
                    }

                    if (i == noOfRow) {
                        style6.setBorderBottom(HSSFCellStyle.BORDER_THIN);
                    }

                    FW1.getRow(i).getCell(j).setCellStyle(style6);
                } catch (Exception e) {

                    FW1.getRow(i).createCell(j).setCellStyle(style6);
                }
            }
        }

        HSSFFont fontBold = myWorkBook.createFont();
        fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD);

        HSSFCellStyle borderRightCenterFontBold = myWorkBook.createCellStyle();

        borderRightCenterFontBold.setBorderRight(HSSFCellStyle.BORDER_THIN);
        borderRightCenterFontBold.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        borderRightCenterFontBold.setFont(fontBold);

        HSSFCellStyle borderRightBottomCenterFontBold = borderRightCenterFontBold;
        borderRightBottomCenterFontBold.setBorderBottom(HSSFCellStyle.BORDER_THIN);

        HSSFCellStyle styleR = myWorkBook.createCellStyle();
        styleR.setBorderRight(HSSFCellStyle.BORDER_THIN);

        //  Forward contract
        // FW1.getRow(0).getCell(0).setCellStyle(borderRightCenterFontBold);
        // FW1.getRow(0).getCell(1).setCellStyle(borderRightCenterFontBold);
        FW1.addMergedRegion(new CellRangeAddress(0, 1, 1, finishM));

        FW1_row_0.createCell(finishM + 1)
                .setCellValue(" " + dateFormat.format(new Date()) + "");

        HSSFCellStyle style4 = myWorkBook.createCellStyle();

        style4.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style4.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style4.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        FW1_row_0.getCell(finishM + 1).setCellStyle(style4);
        FW1_row_0.getCell(finishM + 1).setCellStyle(styleFont);

        FW1.addMergedRegion(new CellRangeAddress(0, 0, finishM + 1, finishM + 2));
        FW1_row_1.createCell(finishM + 1).setCellValue(
                ":");

        FW1.addMergedRegion(new CellRangeAddress(1, 1, finishM + 1, finishM + 2));

        FW1_row_1.getCell(finishM + 1).setCellStyle(style4);
        FW1_row_1.getCell(finishM + 1).setCellStyle(styleFont);

        FW1_row_0.createCell(finishM + 2).setCellStyle(styleR);
        FW1_row_1.createCell(finishM + 2).setCellStyle(styleR);

        FW1.getRow(1).getCell(0).setCellStyle(borderRightCenterFontBold);
        //        FW1.getRow(1).getCell(1).setCellStyle(borderRightBottomCenterFontBold);
        HSSFCellStyle borderBottom = myWorkBook.createCellStyle();
        borderBottom.setBorderRight(HSSFCellStyle.BORDER_THIN);
        borderBottom.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        borderBottom.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        borderBottom.setFont(fontBold);

        FW1.getRow(40).getCell(0).setCellStyle(borderBottom);

        HSSFPatriarch patriarch = (HSSFPatriarch) FW1.createDrawingPatriarch();

        /* Here is the thing: the line will go from top left in cell (0,0) to down left 
        of cell (0,1) */
        //  int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2)
        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 255, (short) 0, 2, (short) 1, 3);

        HSSFSimpleShape shape = patriarch.createSimpleShape(anchor);
        shape.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
        shape.setLineStyleColor(10, 10, 10);
        shape.setFillColor(90, 10, 200);
        shape.setLineWidth(HSSFShape.LINEWIDTH_ONE_PT);
        shape.setLineStyle(HSSFShape.LINESTYLE_SOLID);

        //  createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow);    
        //  FW1.createFreezePane(0,4);

    }

    ByteArrayOutputStream boas = new ByteArrayOutputStream();
    myWorkBook.write(boas);
    setExcelStream(new ByteArrayInputStream(boas.toByteArray()));

    getLogger().debug("exportExcel : end...");
    return "excel";
}

From source file:com.kiwisoft.db.export.ExcelExporter.java

License:Open Source License

public void exportTable(JTable table, SQLStatement statement, File file, ExportConfiguration configuration)
        throws Exception {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("Query");
    TableColumnModel columnModel = table.getColumnModel();
    ResultSetTableModel tableModel = (ResultSetTableModel) table.getModel();
    HSSFFont headerFont = workbook.createFont();
    headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    HSSFCellStyle headerStyle = workbook.createCellStyle();
    headerStyle.setFont(headerFont);/*  www.  j av  a  2s.  com*/
    headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    int rowNum = 0;
    HSSFRow row = sheet.createRow(rowNum++);
    sheet.createFreezePane(0, 1);
    int columnCount = columnModel.getColumnCount();
    for (short i = 0; i < columnCount; i++) {
        TableColumn column = columnModel.getColumn(i);
        int columnIndex = column.getModelIndex();
        HSSFCell cell = row.createCell(i);
        cell.setCellStyle(headerStyle);
        String columnName = tableModel.getColumnName(columnIndex);
        cell.setCellValue(columnName);
    }
    for (int j = 0; j < tableModel.getRowCount(); j++) {
        row = sheet.createRow(rowNum++);
        for (short i = 0; i < columnCount; i++) {
            TableColumn column = columnModel.getColumn(i);
            int columnIndex = column.getModelIndex();
            Object cellValue = tableModel.getValueAt(j, columnIndex);
            HSSFCell cell = row.createCell(i);
            if (cellValue instanceof Number)
                cell.setCellValue(((Number) cellValue).doubleValue());
            else if (cellValue instanceof Date) {

                HSSFCellStyle style = workbook.createCellStyle();
                style.setDataFormat((short) 14);
                cell.setCellValue((Date) cellValue);
                cell.setCellStyle(style);
            } else if (cellValue instanceof Boolean)
                cell.setCellValue(((Boolean) cellValue).booleanValue());
            else if (cellValue != null)
                cell.setCellValue(cellValue.toString());
        }
    }
    FileOutputStream out = new FileOutputStream(file);
    workbook.write(out);
    Field field = ClassLoader.class.getDeclaredField("classes");
    field.setAccessible(true);
    Vector classes = (Vector) field.get(HSSFWorkbook.class.getClassLoader());
    field.setAccessible(false);
    System.out.println("classes = " + StringUtils.enumerate(classes, "\n"));
    out.close();
}

From source file:com.leosys.core.utils.ExcelUtil.java

public void exportExcel(List<?> dataList, OutputStream out) throws Exception {
    HSSFWorkbook workbook = null;
    HSSFSheet sheet = null;/*  w  ww .  j  a va  2s . c o  m*/
    HSSFRow row = null;
    HSSFCell cell = null;
    HSSFCellStyle titleStyle = null;
    int rowIndex = 0;
    try {
        workbook = new HSSFWorkbook();// 
        sheet = workbook.createSheet("?");// ?      
        sheet.setDefaultColumnWidth((short) 30);// 15   
        titleStyle = workbook.createCellStyle();//?
        titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        titleStyle.setFillForegroundColor(HSSFColor.WHITE.index);
        titleStyle.setFillBackgroundColor(HSSFColor.WHITE.index);
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        HSSFFont font = workbook.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints((short) 17);
        titleStyle.setFont(font);

        row = sheet.createRow(rowIndex++);
        row.setHeight((short) 600);
        for (short i = 0; i < headArr.length; i++) {
            cell = row.createCell(i); //?
            if (i == 0)
                cell.setCellValue(new HSSFRichTextString(title));
            cell.setCellStyle(titleStyle);
        }
        // ???    
        sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) (headArr.length - 1)));

        titleStyle = workbook.createCellStyle();
        titleStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        font = workbook.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        font.setFontHeightInPoints((short) 13);
        titleStyle.setFont(font);// ??   
        row = sheet.createRow(rowIndex++);// 
        for (short i = 0; i < headArr.length; i++) {
            cell = row.createCell(i);
            cell.setCellStyle(titleStyle);
            cell.setCellValue(new HSSFRichTextString(headArr[i]));
        }
        //?
        titleStyle = workbook.createCellStyle();
        titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        if (dataList == null || dataList.isEmpty())
            return;
        short dataType = 0;//
        if (dataList.get(0) instanceof Map<?, ?>)
            dataType = 1;
        else if (dataList.get(0) instanceof List<?>)
            dataType = 2;
        if (dataType == 0) {
            String[] dataArr = null;
            for (Object data : dataList) {
                dataArr = (String[]) data;
                if (dataArr == null)
                    continue;
                row = sheet.createRow(rowIndex++);
                for (short i = 0; i < headArr.length; i++) {
                    if (i < dataArr.length) {
                        Object val = dataArr[i];
                        if (rendererArr != null && rendererArr[i] != null)
                            val = rendererArr[i].renderer(dataArr[i], i, dataArr);
                        fillCell(row, titleStyle, font, i, val);
                    }
                }
            }
        } else if (dataType == 1) {
            Map<?, ?> map = null;
            for (Object data : dataList) {
                map = (Map<?, ?>) data;
                if (map == null)
                    continue;
                Object[] dataArr = map.values().toArray();
                if (dataArr == null)
                    continue;
                row = sheet.createRow(rowIndex++);
                for (short i = 0; i < headArr.length; i++) {
                    if (i < dataArr.length) {
                        Object val = dataArr[i];
                        if (rendererArr != null && rendererArr[i] != null)
                            val = rendererArr[i].renderer(dataArr[i], i, dataArr);
                        fillCell(row, titleStyle, font, i, val);
                    }
                }
            }
        } else if (dataType == 2) {
            List<?> list = null;
            for (Object data : dataList) {
                list = (List<?>) data;
                if (list == null || list.isEmpty())
                    continue;
                row = sheet.createRow(rowIndex++);
                for (short i = 0; i < headArr.length; i++) {
                    if (i < list.size()) {
                        Object val = list.get(i);
                        if (rendererArr != null && rendererArr[i] != null)
                            val = rendererArr[i].renderer(list.get(i), i, list);
                        fillCell(row, titleStyle, font, i, val);
                    }
                }
            }
        } else
            throw new Exception("excel???");
        workbook.write(out);
    } catch (Exception e) {
        throw new Exception("excel" + e.getMessage());
    } finally {
        //         if(out != null){
        //            try {
        //               out.close();
        //            } catch (IOException e) {}
        //         }
    }
}

From source file:com.liferay.portlet.documentlibrary.action.EditEntryAction.java

License:Open Source License

public static void exportDocumentData(ResourceRequest resourceRequest, ResourceResponse resourceResponse)
        throws NumberFormatException, PortalException, SystemException {

    long file_id = 0;

    String fileEntryIds = ParamUtil.getString(resourceRequest, "fileEntryIds");
    String[] fileentires = fileEntryIds.split(",");
    List<Long> tempResults = new ArrayList<Long>();

    if (!fileEntryIds.isEmpty()) {
        if (fileentires[0].equals("true")) {

            for (int i = 1; i < fileentires.length; i++) {
                DLFileEntry FileEntry = DLFileEntryLocalServiceUtil
                        .getDLFileEntry(Long.parseLong(fileentires[i]));
                file_id = FileEntry.getFileEntryId();
                tempResults.add(file_id);

            }//from  ww w . j  a v  a2s  . co m
        } else {
            for (int i = 0; i < fileentires.length; i++) {
                DLFileEntry FileEntry = DLFileEntryLocalServiceUtil
                        .getDLFileEntry(Long.parseLong(fileentires[i]));
                file_id = FileEntry.getFileEntryId();
                tempResults.add(file_id);

            }
        }
    }

    try {

        HSSFWorkbook hwb = new HSSFWorkbook();
        HSSFSheet sheet = hwb.createSheet("Site Information");
        org.apache.poi.ss.usermodel.Font font = hwb.createFont();
        font.setFontHeightInPoints((short) 11);
        font.setFontName("Arial");
        font.setItalic(false);
        font.setStrikeout(false);
        font.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);
        CellStyle style = hwb.createCellStyle();

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setBorderRight(CellStyle.BORDER_THIN);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setFont(font);

        CellStyle filterStyle = hwb.createCellStyle();

        org.apache.poi.ss.usermodel.Font filterfont = hwb.createFont();
        filterfont.setFontHeightInPoints((short) 9);
        filterfont.setFontName("Courier New");
        filterfont.setItalic(false);
        filterfont.setStrikeout(false);
        filterfont.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);
        filterStyle.setFont(filterfont);

        filterStyle.setBorderBottom(CellStyle.BORDER_THIN);
        filterStyle.setBorderLeft(CellStyle.BORDER_THIN);
        filterStyle.setBorderTop(CellStyle.BORDER_THIN);
        filterStyle.setBorderRight(CellStyle.BORDER_THIN);
        HSSFRow rowhead = sheet.createRow((short) 2);
        rowhead.createCell((short) 0).setCellValue("S.No.");
        rowhead.createCell((short) 1).setCellValue("Title ");
        rowhead.createCell((short) 2).setCellValue("Site Name ");
        rowhead.createCell((short) 3).setCellValue("Category Type");
        rowhead.createCell((short) 4).setCellValue("Com");
        rowhead.createCell((short) 5).setCellValue("Upload Date");
        rowhead.createCell((short) 6).setCellValue("File Type");

        int index = 3;
        int sno = 0;

        for (int i = 0; i < tempResults.size(); i++)

        {

            sno++;

            DLFileEntry objdlfileentry = DLFileEntryLocalServiceUtil.getDLFileEntry(tempResults.get(i));
            docs_customData objdocs_customData = null;
            try {
                objdocs_customData = docs_customDataLocalServiceUtil.getfileEntry(tempResults.get(i));
            } catch (Exception e) {
                // TODO Auto-generated catch block
                //e.printStackTrace();
            }

            String com = "";
            if (objdocs_customData != null) {
                com = objdocs_customData.getCom();
            }

            String siteName = "";
            if (objdocs_customData != null) {
                siteName = objdocs_customData.getSite();
            }

            String doccategory = "";
            if (objdocs_customData != null) {
                doccategory = objdocs_customData.getCategory();
            }

            HSSFRow row = sheet.createRow((short) index);
            row.createCell((short) 0).setCellValue(sno);
            row.createCell((short) 1).setCellValue(objdlfileentry.getTitle());
            row.createCell((short) 2).setCellValue(siteName);
            row.createCell((short) 3).setCellValue(doccategory);
            row.createCell((short) 4).setCellValue(com);
            row.createCell((short) 5).setCellValue(objdlfileentry.getModifiedDate());
            row.createCell((short) 6).setCellValue(objdlfileentry.getMimeType());

            index++;

        }

        resourceResponse.setContentType("application/vnd.ms-excel");
        resourceResponse.addProperty("Content-Disposition", "attachment;filename=Document_Listing.xls");
        OutputStream fileOut = resourceResponse.getPortletOutputStream();
        hwb.write(fileOut);
        fileOut.close();

    } catch (Exception ex) {
        ex.printStackTrace();
    }

}

From source file:com.lingxiang2014.ExcelView.java

License:Open Source License

public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    Assert.notEmpty(properties);/* w  w  w.jav  a  2 s.  co  m*/
    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" + " " + "S" + "H" + "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.lition.service.impl.OwnedServiceImpl.java

/**
 * POI??/*from ww  w  .  j a  va  2s .  co m*/
 */
@Override
public InputStream getOutExcelDate() {
    //1.?
    String headTitle[] = { "id", "?", "??", "", "??" };

    //2.Dao??
    List<OwnedVehicle> list = dao.queryAll();

    //3.?HSSFWorkbook
    HSSFWorkbook wb = new HSSFWorkbook();

    //4.?sheet
    HSSFSheet sheet = wb.createSheet("?");

    //5.?
    HSSFCellStyle style = wb.createCellStyle();
    // ??
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);

    //6.
    HSSFRow row0 = sheet.createRow(0);
    //7.??
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));

    HSSFCell row0cell0 = row0.createCell(0);
    row0cell0.setCellValue("?");
    row0cell0.setCellStyle(style);

    HSSFRow row1 = sheet.createRow(1);

    for (int i = 0; i < headTitle.length; i++) {
        HSSFCell row0cell = row1.createCell(i);
        row0cell.setCellValue(headTitle[i]);
        row0cell.setCellStyle(style);
    }

    for (int i = 0; i < list.size(); i++) {
        HSSFRow row = sheet.createRow(i + 2);

        //ID
        HSSFCell cell0 = row.createCell(0);
        cell0.setCellValue(list.get(i).getId());
        cell0.setCellStyle(style);

        //?
        HSSFCell cell1 = row.createCell(1);
        cell1.setCellValue(list.get(i).getVehicleId());
        cell1.setCellStyle(style);

        //??
        HSSFCell cell2 = row.createCell(2);
        cell2.setCellValue(list.get(i).getDepid());
        cell2.setCellStyle(style);

        //
        HSSFCell cell3 = row.createCell(3);
        cell3.setCellValue(list.get(i).getModel());
        cell3.setCellStyle(style);

        //??
        HSSFCell cell4 = row.createCell(4);
        cell4.setCellValue(list.get(i).getVehicleUsageId());
        cell4.setCellStyle(style);
    }

    //?inputstream;
    try {
        OutputStream out = new FileOutputStream("abc.xls");
        wb.write(out);
        out.close();
        InputStream in = new FileInputStream("abc.xls");
        return in;
    } catch (FileNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    return null;
}

From source file:com.lushapp.common.excel.ExportExcel.java

License:Apache License

/**
 * excel?/*  w ww.j  a v a2  s.  c  om*/
 * @param workbook
 * @return
 */
public static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
    // Excel
    HSSFCellStyle titleStyle = workbook.createCellStyle();
    titleStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE); //?
    titleStyle.setBorderLeft((short) 2); //
    titleStyle.setBorderRight((short) 2); //?
    titleStyle.setBorderTop((short) 2); //
    titleStyle.setBorderBottom((short) 2); //?
    titleStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE); //
    titleStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index); //
    titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //

    return titleStyle;
}

From source file:com.lushapp.common.excel.ExportExcel.java

License:Apache License

public static HSSFCellStyle getTwoStyle(HSSFWorkbook workbook) {
    // Excel//  w  ww.j av  a 2s  . c  o m
    HSSFCellStyle style = workbook.createCellStyle();
    style.setBorderLeft((short) 1); //
    style.setBorderRight((short) 1); //?
    style.setBorderBottom((short) 1);
    style.setBorderTop((short) 1);
    style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index); //
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //
    return style;
}