Example usage for org.apache.poi.ss.util CellRangeAddress valueOf

List of usage examples for org.apache.poi.ss.util CellRangeAddress valueOf

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.util.*
  5. CellRangeAddress
  6. valueOf

Introduction

In this page you can find the example usage for org.apache.poi.ss.util CellRangeAddress valueOf.

Prototype

public static CellRangeAddress valueOf(String ref)

Source Link

Document

Creates a CellRangeAddress from a cell range reference string.

Usage

From source file:com.saba.CalendarDemo.java

License:Apache License

private static void prepareProductDetailsTable(XSSFWorkbook workbook, XSSFSheet sheet,
        Map<String, CellStyle> styles, int awardDetailsRow) {
    XSSFRow titleRow;/*  w  w w .j av a 2  s  .c o  m*/
    XSSFCell titleCell;
    titleRow = sheet.createRow(awardDetailsRow);
    titleRow.setHeightInPoints(16);
    for (int i = 0; i < productDetailsHeaders.length; i++) {
        titleRow.createCell(i).setCellStyle(styles.get("title"));
    }
    titleCell = titleRow.getCell(0);
    titleCell.setCellValue(awardHeaders[2].toString());
    String columId = productDetailsHeaders.length > 0 && productDetailsHeaders.length < 27
            ? String.valueOf((char) (productDetailsHeaders.length + 'A' - 1))
            : null;
    String cellMergeRange = "$A$" + (awardDetailsRow + 1) + ":$" + columId + "$" + (awardDetailsRow + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(cellMergeRange));
    XSSFRow row = sheet.createRow(awardDetailsRow + 1);
    for (int i = 0; i < productDetailsHeaders.length; i++) {
        XSSFCell cell = row.createCell(i);
        cell.setCellValue(productDetailsHeaders[i]);
        //create header style for product Details table
        CellStyle headerStyle = createHeaderStyleForAward(workbook);
        cell.setCellStyle(headerStyle);
    }
}

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

License:Open Source License

protected void createTitle() {
    // Title/*  w  w  w.java 2  s  .  co m*/
    Row row = sheet.createRow(ROW_1);
    row.setHeight((short) (43 * BASE_HEIGHT));

    sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1"));

    Cell cell = row.createCell(COL_A);
    cell.setCellValue(sheet.getSheetName());
    cell.setCellStyle(titleStyle);
}

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

License:Open Source License

protected void createTable() {

    short mainItemThickness = (short) (24 * BASE_HEIGHT);
    short subItemThickness = (short) (18 * BASE_HEIGHT);
    CellStyle styleMainItem = getCellStyle(ROYAL_BLUE, getFont(FONT_WHITE, (short) 12, true));

    //  ROW_2/*  w  w  w.  java2s .  com*/
    Row row = sheet.createRow(ROW_2);
    row.setHeight(mainItemThickness);

    sheet.addMergedRegion(CellRangeAddress.valueOf("A2:F2"));

    Cell cell = row.createCell(COL_A);
    cell.setCellValue("Open source License Verification Result ");
    cell.setCellStyle(styleMainItem);

    row.createCell(COL_B).setCellStyle(styleMainItem);
    row.createCell(COL_C).setCellStyle(styleMainItem);
    row.createCell(COL_D).setCellStyle(styleMainItem);
    row.createCell(COL_E).setCellStyle(styleMainItem);
    row.createCell(COL_F).setCellStyle(styleMainItem);

    //  ROW_3
    row = sheet.createRow(ROW_3);
    row.setHeight(subItemThickness);

    CellStyle style = getCellStyle(PALE_BLUE, getFont(FONT_BLACK, (short) 10, false));

    cell = row.createCell(COL_A);
    cell.setCellValue("Category");
    cell.setCellStyle(style);

    cell = row.createCell(COL_B);
    cell.setCellValue("Files");
    cell.setCellStyle(style);

    cell = row.createCell(COL_C);
    cell.setCellValue("Files #");
    cell.setCellStyle(style);

    cell = row.createCell(COL_D);
    cell.setCellValue("Component");
    cell.setCellStyle(style);

    cell = row.createCell(COL_E);
    cell.setCellValue("License");
    cell.setCellStyle(style);

    cell = row.createCell(COL_F);
    cell.setCellValue("Remark");
    cell.setCellStyle(style);

}

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

License:Open Source License

protected void createTitle() {
    short lineThickness = (short) (6 * BASE_HEIGHT);

    // Top Line// www  .  j av  a  2s.  com
    Row row = sheet.createRow(ROW_4);
    row.setHeight(lineThickness);

    XSSFCellStyle style = wb.createCellStyle();
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    setDummyTitleStyle(row, style);

    // Title
    row = sheet.createRow(ROW_5);
    row.setHeightInPoints(100);
    sheet.addMergedRegion(CellRangeAddress.valueOf("B5:G5"));

    Font font = wb.createFont();
    font.setFontHeightInPoints((short) 28);
    font.setFontName("Trebuchet MS");
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setFont(font);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

    setDummyTitleStyle(row, style);
    row.getCell(COL_B).setCellValue("Open Source License Verification Report");

    // Bottom Line
    row = sheet.createRow(ROW_6);
    row.setHeight(lineThickness);

    style = wb.createCellStyle();
    style.setFillForegroundColor(DARK_BLUE);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    setDummyTitleStyle(row, style);
}

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

License:Open Source License

protected void createTable() {

    Font fontBold = getFont(FONT_BLACK, (short) 11, true);
    Font fontNormal = getFont(FONT_BLACK, (short) 11, false);

    short tableThickness = (short) (37 * BASE_HEIGHT);
    short toolThickness = (short) (55 * BASE_HEIGHT);

    //  ROW_10//from w ww .j  a v  a 2  s . c om
    Row row = sheet.createRow(ROW_10);
    row.setHeight(tableThickness);

    XSSFCellStyle style = getCellStyle(BORDER_LEFT | BORDER_TOP, fontBold);
    Cell cell = row.createCell(COL_C);
    cell.setCellValue("Project Name");
    cell.setCellStyle(style);

    style = getCellStyle(BORDER_TOP, fontNormal);
    row.createCell(COL_D).setCellStyle(style);

    style = getCellStyle(BORDER_TOP, fontBold);
    cell = row.createCell(COL_E);
    cell.setCellValue("Date");
    cell.setCellStyle(style);

    style = getCellStyle(BORDER_RIGHT | BORDER_TOP, fontNormal);
    row.createCell(COL_F).setCellStyle(style);

    //  ROW_11
    row = sheet.createRow(ROW_11);
    row.setHeight(tableThickness);

    sheet.addMergedRegion(CellRangeAddress.valueOf("D11:F11"));

    style = getCellStyle(BORDER_LEFT, fontBold);
    cell = row.createCell(COL_C);
    cell.setCellValue("Author");
    cell.setCellStyle(style);

    style = getCellStyle(0, fontNormal);
    row.createCell(COL_D).setCellStyle(style);
    row.createCell(COL_E).setCellStyle(style);

    style = getCellStyle(BORDER_RIGHT, fontNormal);
    row.createCell(COL_F).setCellStyle(style);

    //  ROW_12
    row = sheet.createRow(ROW_12);
    row.setHeight(tableThickness);

    sheet.addMergedRegion(CellRangeAddress.valueOf("D12:F12"));

    style = getCellStyle(BORDER_LEFT, fontBold);
    cell = row.createCell(COL_C);
    cell.setCellValue("Team");
    cell.setCellStyle(style);

    style = getCellStyle(0, fontNormal);
    row.createCell(COL_D).setCellStyle(style);
    row.createCell(COL_E).setCellStyle(style);

    style = getCellStyle(BORDER_RIGHT, fontNormal);
    row.createCell(COL_F).setCellStyle(style);

    //  ROW_13
    row = sheet.createRow(ROW_13);
    row.setHeight(toolThickness);

    sheet.addMergedRegion(CellRangeAddress.valueOf("D13:F13"));

    style = getCellStyle(BORDER_BOTTOM | BORDER_LEFT, fontBold);
    cell = row.createCell(COL_C);
    cell.setCellValue("Tool");
    cell.setCellStyle(style);

    style = getCellStyle(BORDER_BOTTOM, fontNormal);
    row.createCell(COL_D).setCellStyle(style);
    row.createCell(COL_E).setCellStyle(style);

    style = getCellStyle(BORDER_BOTTOM | BORDER_RIGHT, fontNormal);
    row.createCell(COL_F).setCellStyle(style);

}

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

License:Open Source License

protected void createTitle() {
    // Title/*w  ww.jav a2  s  . c o  m*/
    Row row = sheet.createRow(ROW_1);
    row.setHeight((short) (43 * BASE_HEIGHT));

    sheet.addMergedRegion(CellRangeAddress.valueOf("A1:G1"));

    Cell cell = row.createCell(COL_A);
    cell.setCellValue("Identified Files");
    cell.setCellStyle(titleStyle);
}

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

License:Open Source License

protected void createTitle() {
    // Title/*from  w  w  w.j  a  va  2  s .c om*/
    Row row = sheet.createRow(ROW_1);
    row.setHeight((short) (43 * BASE_HEIGHT));

    sheet.addMergedRegion(CellRangeAddress.valueOf("A1:K1"));

    Cell cell = row.createCell(COL_A);
    cell.setCellValue("Summary");
    cell.setCellStyle(titleStyle);
}

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

License:Open Source License

protected void createTable() {

    short mainItemBorderThickness = (short) (24 * BASE_HEIGHT);
    short subItemBorderThickness = (short) (45 * BASE_HEIGHT);
    CellStyle styleMainItem = getCellStyle(ROYAL_BLUE, getFont(FONT_WHITE, (short) 12, true));
    CellStyle styleSubItem = getCellStyle(ROYAL_BLUE, getFont(FONT_WHITE, (short) 10, false));

    //  ROW_2/*from  w w  w .j  a  va 2 s  .c o m*/
    Row row = sheet.createRow(ROW_2);
    row.setHeight(mainItemBorderThickness);

    sheet.addMergedRegion(CellRangeAddress.valueOf("A2:A3"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("B2:B3"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("C2:I2"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("J2:K2"));

    Cell cell = row.createCell(COL_A);
    cell.setCellValue("Classification");
    cell.setCellStyle(styleMainItem);

    cell = row.createCell(COL_B);
    cell.setCellValue("Project Name");
    cell.setCellStyle(styleMainItem);

    cell = row.createCell(COL_C);
    cell.setCellValue("Source Code Scan Result");
    cell.setCellStyle(styleMainItem);

    row.createCell(COL_D).setCellStyle(styleMainItem);
    row.createCell(COL_E).setCellStyle(styleMainItem);
    row.createCell(COL_F).setCellStyle(styleMainItem);
    row.createCell(COL_G).setCellStyle(styleMainItem);
    row.createCell(COL_H).setCellStyle(styleMainItem);
    row.createCell(COL_I).setCellStyle(styleMainItem);

    // Identify Result

    CellStyle style = getCellStyle(LIGHT_RED, getFont(DARK_RED, (short) 12, true));

    cell = row.createCell(COL_J);
    cell.setCellValue("Identify Result");
    cell.setCellStyle(style);

    row.createCell(COL_K).setCellStyle(style);

    //  ROW_3
    row = sheet.createRow(ROW_3);
    row.setHeight(subItemBorderThickness);

    cell = row.createCell(COL_A);
    cell.setCellStyle(styleSubItem);

    cell = row.createCell(COL_B);
    cell.setCellStyle(styleSubItem);

    cell = row.createCell(COL_C);
    cell.setCellValue("Scan Date");
    cell.setCellStyle(styleSubItem);

    cell = row.createCell(COL_D);
    cell.setCellValue("Scan time\n(HH:MM:SS)");
    cell.setCellStyle(styleSubItem);

    cell = row.createCell(COL_E);
    cell.setCellValue("Pending Files");
    cell.setCellStyle(styleSubItem);

    cell = row.createCell(COL_F);
    cell.setCellValue("Pending\n(%)");
    cell.setCellStyle(styleSubItem);

    cell = row.createCell(COL_G);
    cell.setCellValue("Total Files");
    cell.setCellStyle(styleSubItem);

    cell = row.createCell(COL_H);
    cell.setCellValue("Exceptional Files");
    cell.setCellStyle(styleSubItem);

    cell = row.createCell(COL_I);
    cell.setCellValue("Bytes");
    cell.setCellStyle(styleSubItem);

    // Identify Result

    style = getCellStyle(LIGHT_RED, getFont(DARK_RED, (short) 10, true));

    cell = row.createCell(COL_J);
    cell.setCellValue("Total Identified Files");
    cell.setCellStyle(style);

    cell = row.createCell(COL_K);
    cell.setCellValue("Current Pending Files");
    cell.setCellStyle(style);

}

From source file:com.smi.travel.controller.excel.booking.OutboundHotelSummary.java

private void genOutboundHotelSummaryReport(HSSFWorkbook wb, List list) {
    UtilityExcelFunction excelFunction = new UtilityExcelFunction();
    String sheetName = "Sheet1";// name of sheet
    HSSFSheet sheet = wb.createSheet(sheetName);

    OutboundHotelSummaryView dataheader = new OutboundHotelSummaryView();

    HSSFDataFormat currency = wb.createDataFormat();
    // Set align Text
    HSSFCellStyle styleC21 = wb.createCellStyle();
    styleC21.setAlignment(styleC21.ALIGN_RIGHT);
    styleC21.setDataFormat(currency.getFormat("#,##0.00"));
    HSSFCellStyle styleC22 = wb.createCellStyle();
    styleC22.setAlignment(styleC22.ALIGN_LEFT);

    if (!list.isEmpty()) {
        dataheader = (OutboundHotelSummaryView) list.get(0);

        // set Header Report (Row 1)
        HSSFCellStyle styleC1 = wb.createCellStyle();
        HSSFRow row1 = sheet.createRow(0);
        HSSFCell cell1 = row1.createCell(0);
        cell1.setCellValue("Hotel Summary");
        styleC1.setFont(excelFunction.getHeaderFont(wb.createFont()));
        cell1.setCellStyle(styleC1);//from   w  w  w. ja v  a 2  s  .com
        sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1"));

        // Row 2
        HSSFRow row2 = sheet.createRow(1);
        HSSFCell cell21 = row2.createCell(0);
        cell21.setCellValue("Country : ");
        cell21.setCellStyle(styleC21);
        HSSFCell cell22 = row2.createCell(1);
        cell22.setCellValue(dataheader.getHeadcountry());
        cell22.setCellStyle(styleC22);
        sheet.addMergedRegion(CellRangeAddress.valueOf("B2:D2"));
        HSSFCell cell23 = row2.createCell(4);
        cell23.setCellValue("Pay By : ");
        cell23.setCellStyle(styleC21);
        HSSFCell cell24 = row2.createCell(5);
        cell24.setCellValue(dataheader.getHeadpayby());
        cell24.setCellStyle(styleC22);

        // Row 3
        HSSFRow row3 = sheet.createRow(2);
        HSSFCell cell31 = row3.createCell(0);
        cell31.setCellValue("City : ");
        cell31.setCellStyle(styleC21);
        HSSFCell cell32 = row3.createCell(1);
        cell32.setCellValue(dataheader.getHeadcity());
        cell32.setCellStyle(styleC22);
        sheet.addMergedRegion(CellRangeAddress.valueOf("B3:D3"));
        HSSFCell cell33 = row3.createCell(4);
        cell33.setCellValue("Bank Transfer : ");
        cell33.setCellStyle(styleC21);
        HSSFCell cell34 = row3.createCell(5);
        cell34.setCellValue(dataheader.getHeadbanktransfer());
        cell34.setCellStyle(styleC22);

        // Row 4
        HSSFRow row4 = sheet.createRow(3);
        HSSFCell cell41 = row4.createCell(0);
        cell41.setCellValue("Hotel : ");
        cell41.setCellStyle(styleC21);
        HSSFCell cell42 = row4.createCell(1);
        cell42.setCellValue(dataheader.getHeadhotel());
        cell42.setCellStyle(styleC22);
        sheet.addMergedRegion(CellRangeAddress.valueOf("B4:D4"));
        HSSFCell cell43 = row4.createCell(4);
        cell43.setCellValue("Status : ");
        cell43.setCellStyle(styleC21);
        HSSFCell cell44 = row4.createCell(5);
        cell44.setCellValue(dataheader.getHeadstatus());
        cell44.setCellStyle(styleC22);

        // Row 5
        HSSFRow row5 = sheet.createRow(4);
        HSSFCell cell51 = row5.createCell(0);
        cell51.setCellValue("Date : ");
        cell51.setCellStyle(styleC21);
        HSSFCell cell52 = row5.createCell(1);
        cell52.setCellValue(dataheader.getHeaddate());
        cell52.setCellStyle(styleC22);
        sheet.addMergedRegion(CellRangeAddress.valueOf("B5:D5"));

    }

    // Header Table
    HSSFCellStyle styleC3 = wb.createCellStyle();
    styleC3.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styleC3.setAlignment(styleC3.ALIGN_CENTER);
    styleC3.setVerticalAlignment(styleC3.VERTICAL_CENTER);
    styleC3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC3.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC3.setBorderTop(HSSFCellStyle.BORDER_THIN);

    HSSFCellStyle styletop = wb.createCellStyle();
    styletop.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styletop.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styletop.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styletop.setAlignment(styletop.ALIGN_CENTER);
    styletop.setFont(excelFunction.getHeaderTable(wb.createFont()));
    styletop.setVerticalAlignment(styletop.VERTICAL_CENTER);

    HSSFCellStyle stylebottom = wb.createCellStyle();
    stylebottom.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    stylebottom.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    stylebottom.setBorderRight(HSSFCellStyle.BORDER_THIN);
    stylebottom.setAlignment(styletop.ALIGN_CENTER);
    stylebottom.setFont(excelFunction.getHeaderTable(wb.createFont()));
    stylebottom.setVerticalAlignment(styletop.VERTICAL_CENTER);
    //        
    //        for(int i = 0 ; i < 20 ; i++){
    //            HSSFRow row4 = sheet.createRow(4);
    //            HSSFCell cell = row4.createCell(i);
    //            cell.setCellStyle(stylebottom);
    //        }

    HSSFRow row6 = sheet.createRow(6);
    HSSFCell cell61 = row6.createCell(0);
    cell61.setCellValue("SALE DATE");
    cell61.setCellStyle(styletop);
    HSSFCell cell62 = row6.createCell(1);
    cell62.setCellValue("RECORD NO");
    cell62.setCellStyle(styletop);
    HSSFCell cell63 = row6.createCell(2);
    cell63.setCellValue("REF NO");
    cell63.setCellStyle(styletop);
    HSSFCell cell64 = row6.createCell(3);
    cell64.setCellValue("HOTEL NAME");
    cell64.setCellStyle(styletop);
    HSSFCell cell65 = row6.createCell(4);
    cell65.setCellValue("CUSTOMER NAME");
    cell65.setCellStyle(styletop);
    HSSFCell cell66 = row6.createCell(5);
    cell66.setCellValue("PERIOD");
    cell66.setCellStyle(styletop);
    sheet.autoSizeColumn(5);
    HSSFCell cell67 = row6.createCell(6);
    cell67.setCellValue("PAX");
    cell67.setCellStyle(styletop);
    HSSFCell cell68 = row6.createCell(7);
    cell68.setCellValue("NETT");
    cell68.setCellStyle(styletop);
    HSSFCell cell69 = row6.createCell(8);
    cell69.setCellValue("SELLING");
    cell69.setCellStyle(styletop);
    HSSFCell cell610 = row6.createCell(9);
    cell610.setCellValue("TOTAL");
    cell610.setCellStyle(styletop);
    HSSFCell cell611 = row6.createCell(10);
    cell611.setCellValue("SALE");
    cell611.setCellStyle(styletop);
    HSSFCell cell612 = row6.createCell(11);
    cell612.setCellValue("PROFIT");
    cell612.setCellStyle(styletop);
    HSSFCell cell613 = row6.createCell(12);
    cell613.setCellValue("BANK");
    cell613.setCellStyle(styletop);
    HSSFCell cell614 = row6.createCell(13);
    cell614.setCellValue("DATE");
    cell614.setCellStyle(styletop);
    HSSFCell cell615 = row6.createCell(14);
    cell615.setCellValue("SUPPLIER");
    cell615.setCellStyle(styletop);
    HSSFCell cell616 = row6.createCell(15);
    cell616.setCellValue("STATUS");
    cell616.setCellStyle(styletop);
    HSSFCell cell617 = row6.createCell(16);
    cell617.setCellValue("REMARK");
    cell617.setCellStyle(styletop);
    HSSFCell cell618 = row6.createCell(17);
    cell618.setCellValue("SALLER");
    cell618.setCellStyle(styletop);

    sheet.addMergedRegion(CellRangeAddress.valueOf("A7:A8"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("B7:B8"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("C7:C8"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("D7:D8"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("E7:E8"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("F7:F8"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("G7:G8"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("O7:O8"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("P7:P8"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("Q7:Q8"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("R7:R8"));

    HSSFRow row7 = sheet.createRow(7);
    HSSFCell cell71 = row7.createCell(0);
    cell71.setCellStyle(stylebottom);
    HSSFCell cell72 = row7.createCell(1);
    cell72.setCellStyle(stylebottom);
    HSSFCell cell73 = row7.createCell(2);
    cell73.setCellStyle(stylebottom);
    HSSFCell cell74 = row7.createCell(3);
    cell74.setCellStyle(stylebottom);
    HSSFCell cell75 = row7.createCell(4);
    cell75.setCellStyle(stylebottom);
    HSSFCell cell76 = row7.createCell(5);
    cell76.setCellStyle(stylebottom);
    //        HSSFCell cell77 = row7.createCell(6);
    //        cell77.setCellValue("OF PAX");
    //        cell77.setCellStyle(stylebottom);
    HSSFCell cell78 = row7.createCell(7);
    cell78.setCellValue("ROOM/NIGHT");
    cell78.setCellStyle(stylebottom);
    HSSFCell cell79 = row7.createCell(8);
    cell79.setCellValue("ROOM/NIGHT");
    cell79.setCellStyle(stylebottom);
    HSSFCell cell710 = row7.createCell(9);
    cell710.setCellValue("NETT");
    cell710.setCellStyle(stylebottom);
    HSSFCell cell711 = row7.createCell(10);
    cell711.setCellValue("TOTAL");
    cell711.setCellStyle(stylebottom);
    HSSFCell cell712 = row7.createCell(11);
    cell712.setCellValue("TOTAL");
    cell712.setCellStyle(stylebottom);
    HSSFCell cell713 = row7.createCell(12);
    cell713.setCellValue("TRFS");
    cell713.setCellStyle(stylebottom);
    HSSFCell cell714 = row7.createCell(13);
    cell714.setCellValue("TRFS");
    cell714.setCellStyle(stylebottom);
    HSSFCell cell715 = row7.createCell(14);
    cell715.setCellStyle(stylebottom);
    HSSFCell cell716 = row7.createCell(15);
    cell716.setCellStyle(stylebottom);
    HSSFCell cell717 = row7.createCell(16);
    cell717.setCellStyle(stylebottom);
    HSSFCell cell718 = row7.createCell(17);
    cell718.setCellStyle(stylebottom);

    //Detail of Table
    int count = 8;
    HSSFCellStyle styleC23 = wb.createCellStyle();
    styleC23.setAlignment(styleC23.ALIGN_CENTER);
    styleC23.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC23.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC23.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC23.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC23.setDataFormat(currency.getFormat("#,##0"));

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

    HSSFCellStyle styleC25 = wb.createCellStyle();
    styleC25.setAlignment(styleC25.ALIGN_RIGHT);
    styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleC25.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleC25.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleC25.setDataFormat(currency.getFormat("#,##0.00"));

    for (int i = 0; i < list.size(); i++) {
        OutboundHotelSummaryView data = (OutboundHotelSummaryView) list.get(i);
        HSSFRow row = sheet.createRow(count + i);

        HSSFCell celldata0 = row.createCell(0);
        celldata0.setCellValue(data.getHoteldate());
        celldata0.setCellStyle(styleC23);

        HSSFCell celldata1 = row.createCell(1);
        celldata1.setCellValue(data.getRecordno());
        celldata1.setCellStyle(styleC24);

        HSSFCell celldata2 = row.createCell(2);
        celldata2.setCellValue(data.getRefno());
        celldata2.setCellStyle(styleC24);

        HSSFCell celldata3 = row.createCell(3);
        celldata3.setCellValue(data.getHotel());
        celldata3.setCellStyle(styleC24);

        HSSFCell celldata4 = row.createCell(4);
        celldata4.setCellValue(data.getLeader());
        celldata4.setCellStyle(styleC24);

        HSSFCell celldata5 = row.createCell(5);
        celldata5.setCellValue(data.getPeriod());
        celldata5.setCellStyle(styleC23);

        HSSFCell celldata6 = row.createCell(6);
        celldata6.setCellValue("".equalsIgnoreCase(String.valueOf(data.getPax())) ? 0
                : new BigDecimal(data.getPax()).doubleValue());
        celldata6.setCellStyle(styleC23);

        HSSFCell celldata7 = row.createCell(7);
        celldata7.setCellValue("".equalsIgnoreCase(String.valueOf(data.getNet())) ? 0
                : new BigDecimal(data.getNet()).doubleValue());
        celldata7.setCellStyle(styleC25);

        HSSFCell celldata14 = row.createCell(8);
        celldata14.setCellValue("".equalsIgnoreCase(String.valueOf(data.getSale())) ? 0
                : new BigDecimal(data.getSale()).doubleValue());
        celldata14.setCellStyle(styleC25);

        //set data 
        HSSFCell celldata8 = row.createCell(9);
        celldata8.setCellValue("".equalsIgnoreCase(String.valueOf(data.getTotelnet())) ? 0
                : new BigDecimal(data.getTotelnet()).doubleValue());
        celldata8.setCellStyle(styleC25);

        HSSFCell celldata9 = row.createCell(10);
        celldata9.setCellValue("".equalsIgnoreCase(String.valueOf(data.getTotalsell())) ? 0
                : new BigDecimal(data.getTotalsell()).doubleValue());
        celldata9.setCellStyle(styleC25);

        HSSFCell celldata10 = row.createCell(11);
        celldata10.setCellValue("".equalsIgnoreCase(String.valueOf(data.getTotalprofit())) ? 0
                : new BigDecimal(data.getTotalprofit()).doubleValue());
        celldata10.setCellStyle(styleC25);

        HSSFCell celldata11 = row.createCell(12);
        celldata11.setCellValue(data.getBank());
        celldata11.setCellStyle(styleC24);

        HSSFCell celldata12 = row.createCell(13);
        celldata12.setCellValue(data.getTransferdate());
        celldata12.setCellStyle(styleC24);

        HSSFCell celldata15 = row.createCell(14);
        celldata15.setCellValue(data.getSupplier());
        celldata15.setCellStyle(styleC24);

        HSSFCell celldata13 = row.createCell(15);
        celldata13.setCellValue(data.getStatus());
        celldata13.setCellStyle(styleC23);

        HSSFCell celldata16 = row.createCell(16);
        celldata16.setCellValue(data.getRemark());
        celldata16.setCellStyle(styleC24);

        HSSFCell celldata17 = row.createCell(17);
        celldata17.setCellValue(data.getSeller());
        celldata17.setCellStyle(styleC24);

    }

    for (int j = 0; j < 21; j++) {
        sheet.autoSizeColumn(j);
    }

    sheet.setColumnWidth(0, 256 * 15);
    sheet.setColumnWidth(1, 256 * 15);
    sheet.setColumnWidth(2, 256 * 15);
    sheet.setColumnWidth(3, 256 * 15);
    sheet.setColumnWidth(4, 256 * 25);
    sheet.setColumnWidth(5, 256 * 25);
    sheet.setColumnWidth(6, 256 * 15);
    sheet.setColumnWidth(10, 256 * 15);
    sheet.setColumnWidth(11, 256 * 15);
    sheet.setColumnWidth(12, 256 * 15);
    sheet.setColumnWidth(13, 256 * 15);
    sheet.setColumnWidth(14, 256 * 15);
    sheet.setColumnWidth(15, 256 * 15);
    sheet.setColumnWidth(16, 256 * 15);
    sheet.setColumnWidth(17, 256 * 15);
    sheet.setColumnWidth(18, 256 * 15);
    sheet.setColumnWidth(19, 256 * 15);
    sheet.setColumnWidth(20, 256 * 15);

}

From source file:com.solidmaps.webapp.report.TimesheetDemo.java

License:Apache License

public void main(String[] args) throws Exception {
    Workbook wb = new HSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    Sheet sheet = wb.createSheet("Timesheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);/*from   w ww.  ja va 2s  . c  om*/
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    //title row
    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Weekly Timesheet");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));

    //header row
    Row headerRow = sheet.createRow(1);
    headerRow.setHeightInPoints(40);
    Cell headerCell;
    for (int i = 0; i < titles.length; i++) {
        headerCell = headerRow.createCell(i);
        headerCell.setCellValue(titles[i]);
        headerCell.setCellStyle(styles.get("header"));
    }

    int rownum = 2;
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(rownum++);
        for (int j = 0; j < titles.length; j++) {
            Cell cell = row.createCell(j);
            if (j == 9) {
                //the 10th cell contains sum over week days, e.g. SUM(C3:I3)
                String ref = "C" + rownum + ":I" + rownum;
                cell.setCellFormula("SUM(" + ref + ")");
                cell.setCellStyle(styles.get("formula"));
            } else if (j == 11) {
                cell.setCellFormula("J" + rownum + "-K" + rownum);
                cell.setCellStyle(styles.get("formula"));
            } else {
                cell.setCellStyle(styles.get("cell"));
            }
        }
    }

    //row with totals below
    Row sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(35);
    Cell cell;
    cell = sumRow.createCell(0);
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellValue("Total Hrs:");
    cell.setCellStyle(styles.get("formula"));

    for (int j = 2; j < 12; j++) {
        cell = sumRow.createCell(j);
        String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
        cell.setCellFormula("SUM(" + ref + ")");
        if (j >= 9)
            cell.setCellStyle(styles.get("formula_2"));
        else
            cell.setCellStyle(styles.get("formula"));
    }
    rownum++;
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Regular Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("L13");
    cell.setCellStyle(styles.get("formula_2"));
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Overtime Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("K13");
    cell.setCellStyle(styles.get("formula_2"));

    //set sample data
    for (int i = 0; i < sample_data.length; i++) {
        Row row = sheet.getRow(2 + i);
        for (int j = 0; j < sample_data[i].length; j++) {
            if (sample_data[i][j] == null)
                continue;

            if (sample_data[i][j] instanceof String) {
                row.getCell(j).setCellValue((String) sample_data[i][j]);
            } else {
                row.getCell(j).setCellValue((Double) sample_data[i][j]);
            }
        }
    }

    //finally set column widths, the width is measured in units of 1/256th of a character width
    sheet.setColumnWidth(0, 30 * 256); //30 characters wide
    for (int i = 2; i < 9; i++) {
        sheet.setColumnWidth(i, 6 * 256); //6 characters wide
    }
    sheet.setColumnWidth(10, 10 * 256); //10 characters wide

    // Write the output to a file
    String file = "/home/brunorocca/Desktop/mapa_teste.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}