Example usage for org.apache.poi.ss.usermodel Sheet setPrintGridlines

List of usage examples for org.apache.poi.ss.usermodel Sheet setPrintGridlines

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. setPrintGridlines

Introduction

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

Prototype

void setPrintGridlines(boolean show);

Source Link

Document

Sets the flag indicating whether this sheet should print the lines between rows and columns to make editing and reading easier.

Usage

From source file:br.com.techne.gluonsoft.eowexport.builder.ExcelBuilder.java

License:Apache License

/**
 * mtodo cria bytes de documento Excel//w w w . ja v a  2s . c  om
 * @param titles
 * @param columnIndex
 * @param dataRows
 * @param locale
 * @return
 * @throws Exception
 */
public static byte[] createExcelBytes(String[] titles, String[] columnIndex,
        List<HashMap<String, Object>> dataRows, Locale locale) throws Exception {

    //Workbook wb = new HSSFWorkbook();
    XSSFWorkbook wb = new XSSFWorkbook();
    byte[] outBytes;

    try {
        HashMap<String, CellStyle> styles = createStyles(wb);
        Sheet sheet = wb.createSheet("Tab 1");

        //turn off gridlines
        sheet.setDisplayGridlines(false);
        sheet.setPrintGridlines(false);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);
        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setLandscape(true);

        //the following three statements are required only for HSSF
        sheet.setAutobreaks(true);
        printSetup.setFitHeight((short) 1);
        printSetup.setFitWidth((short) 1);

        //the header row: centered text in 48pt font
        Row headerRow = sheet.createRow(0);
        headerRow.setHeightInPoints(12.75f);

        for (int indexColumn = 0; indexColumn < titles.length; indexColumn++) {
            Cell cell = headerRow.createCell(indexColumn);
            cell.setCellValue(titles[indexColumn]);

            if ((titles.length - 1) < indexColumn) {
                cell.setCellValue("");
            } else
                cell.setCellValue(titles[indexColumn]);

            cell.setCellStyle(styles.get("header"));
        }

        //freeze the first row
        sheet.createFreezePane(0, 1);

        Row row;
        Cell cell;
        int rownum = 1;//devido constar titulo, comea do indice 1

        ValueCellUtil vcutil = new ValueCellUtil(locale);

        for (int indexRow = 0; indexRow < dataRows.size(); indexRow++, rownum++) {

            row = sheet.createRow(rownum);
            HashMap<String, Object> dataRow = dataRows.get(indexRow);

            if (dataRow == null)
                continue;

            List<String> keysAttribs = null;

            if (columnIndex.length == 0) {
                keysAttribs = Arrays.asList(dataRow.keySet().toArray(new String[0]));
                Collections.reverse(keysAttribs);
            } else {
                keysAttribs = Arrays.asList(columnIndex);
            }

            int colCt = 0;

            for (String keyAttrib : keysAttribs) {

                cell = row.createCell(colCt);
                String styleName;
                cell.setCellValue(vcutil.parseValue(dataRow.get(keyAttrib)).toString());

                //zebrando tabela
                if (indexRow % 2 == 0) {
                    // even row
                    styleName = "cell_normal_even";
                } else {
                    // odd row
                    styleName = "cell_normal_odd";
                }

                if (indexRow == 0) {
                    //setando auto ajuste
                    sheet.autoSizeColumn(colCt);
                }

                cell.setCellStyle(styles.get(styleName));
                colCt++;
            }
        }

        sheet.setZoom(75); //75% scale

        // Write the output to a file
        // write for return byte[]
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try {
            wb.write(out);
            outBytes = out.toByteArray();
        } finally {
            out.close();
        }
    } finally {
        wb.close();
    }

    return outBytes;
}

From source file:com.actelion.research.spiritapp.report.StudyDesignReport.java

License:Open Source License

@Override
protected void populateWorkBook() throws Exception {
    CreationHelper helper = wb.getCreationHelper();

    //create sheet
    Sheet sheet = createSheet(wb, "Study Design");
    sheet.setPrintGridlines(false);
    createHeadersWithTitle(sheet, study, "Study Design");

    // Create the drawing patriarch.  This is the top level container for all shapes.
    Drawing drawing = sheet.createDrawingPatriarch();

    ByteArrayOutputStream os = new ByteArrayOutputStream();
    //10.5inches*600 = 6300 pixels, 7.5*600=4500 pixels, on 600dpi
    int size = FastFont.getDefaultFontSize();
    FastFont.setDefaultFontSize(36);//  ww w .j a v a 2 s.c o  m
    BufferedImage img = StudyDepictor.getImage(study, 6300, 4500, 1);
    ImageIO.write(img, "PNG", os);
    FastFont.setDefaultFontSize(size);

    //add a picture shape
    int pictureIdx = wb.addPicture(os.toByteArray(), Workbook.PICTURE_TYPE_PNG);

    ClientAnchor anchor = helper.createClientAnchor();
    anchor.setAnchorType(ClientAnchor.MOVE_DONT_RESIZE);
    anchor.setCol1(0);
    anchor.setRow1(3);
    Picture pict = drawing.createPicture(anchor, pictureIdx);
    final double scaleDown = .33;
    //Samplings
    int y = (int) (img.getHeight() * scaleDown) / 20 + 5;
    int nSamplings = 0;
    for (NamedSampling ns : study.getNamedSamplings()) {
        nSamplings = Math.max(nSamplings, ns.getAllSamplings().size());
    }
    if (study.getNamedSamplings().size() > 0) {
        set(sheet, y++, 0, "Sampling Templates", Style.S_TITLE14BLUE);
        int count = 0;
        for (NamedSampling ns : study.getNamedSamplings()) {
            set(sheet, y, count, ns.getName(), Style.S_TH_LEFT);
            int line = 0;
            for (Sampling s : ns.getAllSamplings()) {
                set(sheet, y + (++line), count, MiscUtils.removeHtml(s.getDetailsWithMeasurements()),
                        Style.S_TD_LEFT);
            }
            while (line < nSamplings) {
                set(sheet, y + (++line), count, "", Style.S_TD_LEFT);
            }
            count++;
        }
    }

    //Treatments
    y += nSamplings + 3;
    if (study.getNamedTreatments().size() > 0) {
        set(sheet, y++, 0, "Treatments", Style.S_TITLE14BLUE);
        int count = 0;
        for (NamedTreatment nt : study.getNamedTreatments()) {
            set(sheet, y, count, nt.getName(), Style.S_TH_LEFT);
            set(sheet, y + 1, count, nt.getCompoundAndUnit1(), Style.S_TD_LEFT);
            set(sheet, y + 2, count, nt.getCompoundAndUnit2(), Style.S_TD_LEFT);
            count++;
        }
    }

    POIUtils.autoSizeColumns(sheet, -1, true);
    pict.resize();
    pict.resize(scaleDown);

}

From source file:com.b510.excel.client.BusinessPlan.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;//from  w ww  . j a  v a  2  s .  c  o m

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else
        wb = new XSSFWorkbook();

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

    Sheet sheet = wb.createSheet("Business Plan");

    //turn off gridlines
    sheet.setDisplayGridlines(false);
    sheet.setPrintGridlines(false);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);

    //the following three statements are required only for HSSF
    sheet.setAutobreaks(true);
    printSetup.setFitHeight((short) 1);
    printSetup.setFitWidth((short) 1);

    //the header row: centered text in 48pt font
    Row headerRow = sheet.createRow(0);
    headerRow.setHeightInPoints(12.75f);
    for (int i = 0; i < titles.length; i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellValue(titles[i]);
        cell.setCellStyle(styles.get("header"));
    }
    //columns for 11 weeks starting from 9-Jul
    Calendar calendar = Calendar.getInstance();
    int year = calendar.get(Calendar.YEAR);

    calendar.setTime(fmt.parse("9-Jul"));
    calendar.set(Calendar.YEAR, year);
    for (int i = 0; i < 11; i++) {
        Cell cell = headerRow.createCell(titles.length + i);
        cell.setCellValue(calendar);
        cell.setCellStyle(styles.get("header_date"));
        calendar.roll(Calendar.WEEK_OF_YEAR, true);
    }
    //freeze the first row
    sheet.createFreezePane(0, 1);

    Row row;
    Cell cell;
    int rownum = 1;
    for (int i = 0; i < data.length; i++, rownum++) {
        row = sheet.createRow(rownum);
        if (data[i] == null)
            continue;

        for (int j = 0; j < data[i].length; j++) {
            cell = row.createCell(j);
            String styleName;
            boolean isHeader = i == 0 || data[i - 1] == null;
            switch (j) {
            case 0:
                if (isHeader) {
                    styleName = "cell_b";
                    cell.setCellValue(Double.parseDouble(data[i][j]));
                } else {
                    styleName = "cell_normal";
                    cell.setCellValue(data[i][j]);
                }
                break;
            case 1:
                if (isHeader) {
                    styleName = i == 0 ? "cell_h" : "cell_bb";
                } else {
                    styleName = "cell_indented";
                }
                cell.setCellValue(data[i][j]);
                break;
            case 2:
                styleName = isHeader ? "cell_b" : "cell_normal";
                cell.setCellValue(data[i][j]);
                break;
            case 3:
                styleName = isHeader ? "cell_b_centered" : "cell_normal_centered";
                cell.setCellValue(Integer.parseInt(data[i][j]));
                break;
            case 4: {
                calendar.setTime(fmt.parse(data[i][j]));
                calendar.set(Calendar.YEAR, year);
                cell.setCellValue(calendar);
                styleName = isHeader ? "cell_b_date" : "cell_normal_date";
                break;
            }
            case 5: {
                int r = rownum + 1;
                String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")";
                cell.setCellFormula(fmla);
                styleName = isHeader ? "cell_bg" : "cell_g";
                break;
            }
            default:
                styleName = data[i][j] != null ? "cell_blue" : "cell_normal";
            }

            cell.setCellStyle(styles.get(styleName));
        }
    }

    //group rows for each phase, row numbers are 0-based
    sheet.groupRow(4, 6);
    sheet.groupRow(9, 13);
    sheet.groupRow(16, 18);

    //set column widths, the width is measured in units of 1/256th of a character width
    sheet.setColumnWidth(0, 256 * 6);
    sheet.setColumnWidth(1, 256 * 33);
    sheet.setColumnWidth(2, 256 * 20);
    sheet.setZoom(3, 4);

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

From source file:com.b510.excel.client.CalendarDemo.java

License:Apache License

public static void main(String[] args) throws Exception {

    Calendar calendar = Calendar.getInstance();
    boolean xlsx = true;
    for (int i = 0; i < args.length; i++) {
        if (args[i].charAt(0) == '-') {
            xlsx = args[i].equals("-xlsx");
        } else {//from   w ww. jav a2 s  .c o  m
            calendar.set(Calendar.YEAR, Integer.parseInt(args[i]));
        }
    }
    int year = calendar.get(Calendar.YEAR);

    Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook();

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

    for (int month = 0; month < 12; month++) {
        calendar.set(Calendar.MONTH, month);
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        //create a sheet for each month
        Sheet sheet = wb.createSheet(months[month]);

        //turn off gridlines
        sheet.setDisplayGridlines(false);
        sheet.setPrintGridlines(false);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);
        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setLandscape(true);

        //the following three statements are required only for HSSF
        sheet.setAutobreaks(true);
        printSetup.setFitHeight((short) 1);
        printSetup.setFitWidth((short) 1);

        //the header row: centered text in 48pt font
        Row headerRow = sheet.createRow(0);
        headerRow.setHeightInPoints(80);
        Cell titleCell = headerRow.createCell(0);
        titleCell.setCellValue(months[month] + " " + year);
        titleCell.setCellStyle(styles.get("title"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1"));

        //header with month titles
        Row monthRow = sheet.createRow(1);
        for (int i = 0; i < days.length; i++) {
            //set column widths, the width is measured in units of 1/256th of a character width
            sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide
            sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide
            sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1));
            Cell monthCell = monthRow.createCell(i * 2);
            monthCell.setCellValue(days[i]);
            monthCell.setCellStyle(styles.get("month"));
        }

        int cnt = 1, day = 1;
        int rownum = 2;
        for (int j = 0; j < 6; j++) {
            Row row = sheet.createRow(rownum++);
            row.setHeightInPoints(100);
            for (int i = 0; i < days.length; i++) {
                Cell dayCell_1 = row.createCell(i * 2);
                Cell dayCell_2 = row.createCell(i * 2 + 1);

                int day_of_week = calendar.get(Calendar.DAY_OF_WEEK);
                if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) {
                    dayCell_1.setCellValue(day);
                    calendar.set(Calendar.DAY_OF_MONTH, ++day);

                    if (i == 0 || i == days.length - 1) {
                        dayCell_1.setCellStyle(styles.get("weekend_left"));
                        dayCell_2.setCellStyle(styles.get("weekend_right"));
                    } else {
                        dayCell_1.setCellStyle(styles.get("workday_left"));
                        dayCell_2.setCellStyle(styles.get("workday_right"));
                    }
                } else {
                    dayCell_1.setCellStyle(styles.get("grey_left"));
                    dayCell_2.setCellStyle(styles.get("grey_right"));
                }
                cnt++;
            }
            if (calendar.get(Calendar.MONTH) > month)
                break;
        }
    }

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

From source file:com.b510.excel.client.LoanCalculator.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;/*from www . ja va 2 s.  c o  m*/

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else
        wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);
    Sheet sheet = wb.createSheet("Loan Calculator");
    sheet.setPrintGridlines(false);
    sheet.setDisplayGridlines(false);

    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    sheet.setColumnWidth(0, 3 * 256);
    sheet.setColumnWidth(1, 3 * 256);
    sheet.setColumnWidth(2, 11 * 256);
    sheet.setColumnWidth(3, 14 * 256);
    sheet.setColumnWidth(4, 14 * 256);
    sheet.setColumnWidth(5, 14 * 256);
    sheet.setColumnWidth(6, 14 * 256);

    createNames(wb);

    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(35);
    for (int i = 1; i <= 7; i++) {
        titleRow.createCell(i).setCellStyle(styles.get("title"));
    }
    Cell titleCell = titleRow.getCell(2);
    titleCell.setCellValue("Simple Loan Calculator");
    sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1"));

    Row row = sheet.createRow(2);
    Cell cell = row.createCell(4);
    cell.setCellValue("Enter values");
    cell.setCellStyle(styles.get("item_right"));

    row = sheet.createRow(3);
    cell = row.createCell(2);
    cell.setCellValue("Loan amount");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_$"));
    cell.setAsActiveCell();

    row = sheet.createRow(4);
    cell = row.createCell(2);
    cell.setCellValue("Annual interest rate");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_%"));

    row = sheet.createRow(5);
    cell = row.createCell(2);
    cell.setCellValue("Loan period in years");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_i"));

    row = sheet.createRow(6);
    cell = row.createCell(2);
    cell.setCellValue("Start date of loan");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_d"));

    row = sheet.createRow(8);
    cell = row.createCell(2);
    cell.setCellValue("Monthly payment");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Monthly_Payment,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    row = sheet.createRow(9);
    cell = row.createCell(2);
    cell.setCellValue("Number of payments");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Loan_Years*12,\"\")");
    cell.setCellStyle(styles.get("formula_i"));

    row = sheet.createRow(10);
    cell = row.createCell(2);
    cell.setCellValue("Total interest");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Total_Cost-Loan_Amount,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    row = sheet.createRow(11);
    cell = row.createCell(2);
    cell.setCellValue("Total cost of loan");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Monthly_Payment*Number_of_Payments,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    // Write the output to a file
    String file = "loan-calculator.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:com.crm.webapp.util.ExcelCustomExporter.java

License:Apache License

@Override
public void export(ActionEvent event, String tableId, FacesContext context, String filename, String tableTitle,
        boolean pageOnly, boolean selectionOnly, String encodingType, MethodExpression preProcessor,
        MethodExpression postProcessor, boolean subTable) throws IOException {

    wb = new XSSFWorkbook();
    String safeName = WorkbookUtil.createSafeSheetName(filename);
    Sheet sheet = wb.createSheet(safeName);

    cellStyle = wb.createCellStyle();/*from w w w. j a va 2  s.  c  o m*/
    facetStyle = wb.createCellStyle();
    titleStyle = wb.createCellStyle();
    createCustomFonts();

    int maxColumns = 0;
    StringTokenizer st = new StringTokenizer(tableId, ",");
    while (st.hasMoreElements()) {
        String tableName = (String) st.nextElement();
        UIComponent component = SearchExpressionFacade.resolveComponent(context, event.getComponent(),
                tableName);
        if (component == null) {
            throw new FacesException("Cannot find component \"" + tableName + "\" in view.");
        }
        if (!(component instanceof DataTable || component instanceof DataList)) {
            throw new FacesException("Unsupported datasource target:\"" + component.getClass().getName()
                    + "\", exporter must target a PrimeFaces DataTable/DataList.");
        }

        DataList list = null;
        DataTable table = null;
        int cols = 0;
        if (preProcessor != null) {
            preProcessor.invoke(context.getELContext(), new Object[] { wb });
        }
        if (tableTitle != null && !tableTitle.isEmpty() && !tableId.contains("" + ",")) {
            Row titleRow = sheet.createRow(sheet.getLastRowNum());
            int cellIndex = titleRow.getLastCellNum() == -1 ? 0 : titleRow.getLastCellNum();
            Cell cell = titleRow.createCell(cellIndex);
            cell.setCellValue(new XSSFRichTextString(tableTitle));
            Font titleFont = wb.createFont();
            titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
            titleStyle.setFont(titleFont);
            cell.setCellStyle(titleStyle);
            sheet.createRow(sheet.getLastRowNum() + 3);

        }
        if (component instanceof DataList) {
            list = (DataList) component;

            if (list.getHeader() != null) {
                tableFacet(context, sheet, list, "header");
            }
            if (pageOnly) {
                exportPageOnly(context, list, sheet);
            } else {
                exportAll(context, list, sheet);
            }
            cols = list.getRowCount();
        } else {

            table = (DataTable) component;
            int columnsCount = getColumnsCount(table);

            if (table.getHeader() != null && !subTable) {
                tableFacet(context, sheet, table, columnsCount, "header");

            }
            if (!subTable) {
                tableColumnGroup(sheet, table, "header");
            }

            addColumnFacets(table, sheet, ColumnType.HEADER);

            if (pageOnly) {
                exportPageOnly(context, table, sheet);
            } else if (selectionOnly) {
                exportSelectionOnly(context, table, sheet);
            } else {
                exportAll(context, table, sheet, subTable);
            }

            if (table.hasFooterColumn() && !subTable) {
                addColumnFacets(table, sheet, ColumnType.FOOTER);
            }
            if (!subTable) {
                tableColumnGroup(sheet, table, "footer");
            }
            table.setRowIndex(-1);
            if (postProcessor != null) {
                postProcessor.invoke(context.getELContext(), new Object[] { wb });
            }
            cols = table.getColumnsCount();

            if (maxColumns < cols) {
                maxColumns = cols;
            }
        }
        sheet.createRow(sheet.getLastRowNum() + Integer.parseInt(datasetPadding));
    }

    if (!subTable)
        for (int i = 0; i < maxColumns; i++) {
            sheet.autoSizeColumn((short) i);
        }

    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);
    sheet.setPrintGridlines(true);

    writeExcelToResponse(context.getExternalContext(), wb, filename);

}

From source file:com.dufeng.core.BusinessPlan.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;//from w ww.  ja va2 s .c om

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else
        wb = new XSSFWorkbook();

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

    Sheet sheet = wb.createSheet("Business Plan");

    //turn off gridlines
    sheet.setDisplayGridlines(false);
    sheet.setPrintGridlines(false);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);

    //the following three statements are required only for HSSF
    sheet.setAutobreaks(true);
    printSetup.setFitHeight((short) 1);
    printSetup.setFitWidth((short) 1);

    //the header row: centered text in 48pt font
    Row headerRow = sheet.createRow(0);
    headerRow.setHeightInPoints(12.75f);
    for (int i = 0; i < titles.length; i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellValue(titles[i]);
        cell.setCellStyle(styles.get("header"));
    }
    //columns for 11 weeks starting from 9-Jul
    Calendar calendar = Calendar.getInstance();
    int year = calendar.get(Calendar.YEAR);

    //calendar.setTime(fmt.parse("9-Jul"));
    calendar.setTime(new Date());
    calendar.set(Calendar.YEAR, year);
    for (int i = 0; i < 11; i++) {
        Cell cell = headerRow.createCell(titles.length + i);
        cell.setCellValue(calendar);
        cell.setCellStyle(styles.get("header_date"));
        calendar.roll(Calendar.WEEK_OF_YEAR, true);
    }
    //freeze the first row
    sheet.createFreezePane(0, 1);

    Row row;
    Cell cell;
    int rownum = 1;
    for (int i = 0; i < data.length; i++, rownum++) {
        row = sheet.createRow(rownum);
        if (data[i] == null)
            continue;

        for (int j = 0; j < data[i].length; j++) {
            cell = row.createCell(j);
            String styleName;
            boolean isHeader = i == 0 || data[i - 1] == null;
            switch (j) {
            case 0:
                if (isHeader) {
                    styleName = "cell_b";
                    cell.setCellValue(Double.parseDouble(data[i][j]));
                } else {
                    styleName = "cell_normal";
                    cell.setCellValue(data[i][j]);
                }
                break;
            case 1:
                if (isHeader) {
                    styleName = i == 0 ? "cell_h" : "cell_bb";
                } else {
                    styleName = "cell_indented";
                }
                cell.setCellValue(data[i][j]);
                break;
            case 2:
                styleName = isHeader ? "cell_b" : "cell_normal";
                cell.setCellValue(data[i][j]);
                break;
            case 3:
                styleName = isHeader ? "cell_b_centered" : "cell_normal_centered";
                cell.setCellValue(Integer.parseInt(data[i][j]));
                break;
            case 4: {
                //calendar.setTime(fmt.parse(data[i][j]));
                calendar.setTime(new Date());
                calendar.set(Calendar.YEAR, year);
                cell.setCellValue(calendar);
                styleName = isHeader ? "cell_b_date" : "cell_normal_date";
                break;
            }
            case 5: {
                int r = rownum + 1;
                String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")";
                cell.setCellFormula(fmla);
                styleName = isHeader ? "cell_bg" : "cell_g";
                break;
            }
            default:
                styleName = data[i][j] != null ? "cell_blue" : "cell_normal";
            }

            cell.setCellStyle(styles.get(styleName));
        }
    }

    //group rows for each phase, row numbers are 0-based
    sheet.groupRow(4, 6);
    sheet.groupRow(9, 13);
    sheet.groupRow(16, 18);

    //set column widths, the width is measured in units of 1/256th of a character width
    sheet.setColumnWidth(0, 256 * 6);
    sheet.setColumnWidth(1, 256 * 33);
    sheet.setColumnWidth(2, 256 * 20);
    sheet.setZoom(3, 4);

    // Write the output to a file
    String file = "E:/businessplan.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:com.griffinslogistics.document.excel.BDLGenerator.java

private static void generate(Workbook workbook, List<BookBoxModel> bookBoxModels, Pulsiodetails pulsiodetails,
        String packageNumber) {/* w  ww  .  j  av a 2  s.  c o  m*/

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

    Sheet sheet = workbook.createSheet(packageNumber);

    // Generate header part
    insertPulsioImage(workbook, sheet, pulsiodetails);
    insertDate(sheet, styles.get("title"));
    insertContacts(sheet, styles.get("pulsioName"), styles.get("contacts"), pulsiodetails);
    insertTitle(sheet, styles.get("title"));
    // Generate table part
    insertTableHeaders(sheet, styles.get("tableHeadersLeft"), styles.get("tableHeadersMiddle"),
            styles.get("tableHeadersRight"));
    int index = insertTableBody(sheet, styles.get("tableBodyLeft"), styles.get("tableBodyMiddle"),
            styles.get("tableBodyRight"), styles.get("tableFooters"), bookBoxModels);

    String deliveryAddress = bookBoxModels.get(0).getDeliveryAddress();
    String client = bookBoxModels.get(0).getClient();

    insertFooter(sheet, styles.get("footer"), index, packageNumber, deliveryAddress, client);

    //        Default sheet styles
    sheet.setDisplayGridlines(false);
    sheet.setPrintGridlines(false);

    sheet.autoSizeColumn(1, true);
    sheet.autoSizeColumn(2, false);
    sheet.autoSizeColumn(5, false);
    sheet.autoSizeColumn(6, false);
    sheet.autoSizeColumn(7, false);
    sheet.autoSizeColumn(8, false);

    sheet.setFitToPage(true);
}

From source file:com.griffinslogistics.excel.BDLGenerator.java

public static void generateAll(OutputStream outputStream,
        Map<String, List<BookBoxModel>> bookBoxModelsForTransportation, Pulsiodetails pulsioDetails,
        String packageNumber) {/*  ww w.  j a  va2  s  .  c  o m*/
    try {
        Workbook workbook = new HSSFWorkbook();
        Map<String, CellStyle> styles = createStyles(workbook);

        for (String bookspackageNumber : bookBoxModelsForTransportation.keySet()) {

            Sheet sheet = workbook.createSheet(bookspackageNumber);

            //Default sheet styles
            sheet.setDisplayGridlines(false);
            sheet.setPrintGridlines(false);
            sheet.getPrintSetup().setLandscape(true);
            sheet.setFitToPage(true);
            sheet.setHorizontallyCenter(true);
            sheet.setDefaultRowHeight((short) 300);
            sheet.setDefaultColumnWidth(15);

            sheet.setColumnWidth(1, 6000);
            sheet.setColumnWidth(5, 3000);
            sheet.setColumnWidth(6, 3000);
            sheet.setColumnWidth(7, 3000);
            sheet.setColumnWidth(8, 3000);

            // Generate header part
            insertPulsioImage(workbook, sheet, pulsioDetails);
            insertDate(sheet, styles.get("title"));
            insertContacts(sheet, styles.get("pulsioName"), styles.get("contacts"), pulsioDetails);
            insertTitle(sheet, styles.get("title"));

            // Generate table part
            insertTableHeaders(sheet, styles.get("tableHeadersLeft"), styles.get("tableHeadersMiddle"),
                    styles.get("tableHeadersRight"));
            List<BookBoxModel> bookBoxModels = bookBoxModelsForTransportation.get(bookspackageNumber);
            int index = insertTableBody(sheet, styles.get("tableBodyLeft"), styles.get("tableBodyMiddle"),
                    styles.get("tableBodyRight"), styles.get("tableFooters"), bookBoxModels);

            String deliveryAddress = bookBoxModels.get(0).getDeliveryAddress();
            String client = bookBoxModels.get(0).getClient();
            insertFooter(sheet, styles.get("footer"), index, bookspackageNumber, deliveryAddress, client);
        }

        workbook.write(outputStream);
    } catch (FileNotFoundException ex) {
        logger.log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        logger.log(Level.SEVERE, null, ex);
    }
}

From source file:com.griffinslogistics.excel.BDLGenerator.java

public static OutputStream generateSingle(OutputStream outputStream, List<BookBoxModel> bookBoxModels,
        Pulsiodetails pulsiodetails, String packageNumber) {

    try {//from   w w  w  .  jav a 2s.com
        Workbook workbook = new HSSFWorkbook();
        Map<String, CellStyle> styles = createStyles(workbook);
        Sheet sheet = workbook.createSheet("Bon de livraison");

        //Default sheet styles
        sheet.setDisplayGridlines(false);
        sheet.setPrintGridlines(false);
        sheet.getPrintSetup().setLandscape(true);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);
        sheet.setDefaultRowHeight((short) 300);
        sheet.setDefaultColumnWidth(15);

        sheet.setColumnWidth(1, 6000);
        sheet.setColumnWidth(5, 3000);
        sheet.setColumnWidth(6, 3000);
        sheet.setColumnWidth(7, 3000);
        sheet.setColumnWidth(8, 3000);

        // Generate header part
        insertPulsioImage(workbook, sheet, pulsiodetails);
        insertDate(sheet, styles.get("title"));
        insertContacts(sheet, styles.get("pulsioName"), styles.get("contacts"), pulsiodetails);
        insertTitle(sheet, styles.get("title"));

        // Generate table part
        insertTableHeaders(sheet, styles.get("tableHeadersLeft"), styles.get("tableHeadersMiddle"),
                styles.get("tableHeadersRight"));
        int index = insertTableBody(sheet, styles.get("tableBodyLeft"), styles.get("tableBodyMiddle"),
                styles.get("tableBodyRight"), styles.get("tableFooters"), bookBoxModels);

        String deliveryAddress = bookBoxModels.get(0).getDeliveryAddress();
        String client = bookBoxModels.get(0).getClient();

        insertFooter(sheet, styles.get("footer"), index, packageNumber, deliveryAddress, client);

        workbook.write(outputStream);
    } catch (FileNotFoundException ex) {
        logger.log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        logger.log(Level.SEVERE, null, ex);
    }

    return outputStream;
}