Example usage for org.apache.poi.ss.usermodel Workbook write

List of usage examples for org.apache.poi.ss.usermodel Workbook write

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. write

Introduction

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

Prototype

void write(OutputStream stream) throws IOException;

Source Link

Document

Write out this workbook to an Outputstream.

Usage

From source file:de.iteratec.iteraplan.businesslogic.exchange.nettoExport.NettoExcelTransformer.java

License:Open Source License

/**{@inheritDoc}**/
@Override//  w  w  w.  ja  v a  2 s  . c o m
public void transform(List<?> sourceList, OutputStream out, TypeOfBuildingBlock typeOfBuildingBlock) {

    ColumnStructure[] columns = tableStructure.getColumns();

    try {
        Workbook workbook = createWorkbook();

        Sheet sheet = workbook.createSheet();
        configSheetName(sheet, typeOfBuildingBlock);

        Map<IteraExcelStyle, CellStyle> createdStyles = ExcelStylesCreator.createStyles(workbook);
        CellStyle headerStyle = createdStyles.get(IteraExcelStyle.HEADER);
        CellStyle dataStyle = createdStyles.get(IteraExcelStyle.DATA);
        CellStyle dataDateStyle = createdStyles.get(IteraExcelStyle.DATA_DATE);

        // Create cell style for numbers
        CellStyle numCellStyle = workbook.createCellStyle();
        numCellStyle.cloneStyleFrom(dataStyle);
        short numFormatIndex = workbook.createDataFormat().getFormat("0.00");
        numCellStyle.setDataFormat(numFormatIndex);

        Row headerRow = sheet.createRow(0);

        int nextCol = 0;
        for (ColumnStructure columnStructure : columns) {
            Cell headerCell = headerRow.createCell(nextCol);
            headerCell.setCellValue(columnStructure.getColumnHeader());
            headerCell.setCellStyle(headerStyle);
            nextCol++;
        }

        int nextRow = 1;
        for (Object obj : sourceList) {
            if (obj instanceof BuildingBlock) {
                BuildingBlock bb = (BuildingBlock) obj;

                // skip virutal root element
                if (bb instanceof AbstractHierarchicalEntity<?>) {
                    AbstractHierarchicalEntity<?> hierarchicalEntity = (AbstractHierarchicalEntity<?>) bb;
                    if (hierarchicalEntity.isTopLevelElement()) {
                        continue;
                    }
                }

                Row row = sheet.createRow(nextRow);

                nextCol = 0;
                for (ColumnStructure columnStructure : columns) {
                    Cell cell = row.createCell(nextCol);

                    Object resolvedValue = columnStructure.resolveValue(bb);

                    if (resolvedValue instanceof Date) {
                        cell.setCellStyle(dataDateStyle);
                        cell.setCellValue((Date) resolvedValue);
                    } else if (resolvedValue instanceof Number) {
                        cell.setCellStyle(numCellStyle);
                        double doubleValue = ((Number) resolvedValue).doubleValue();
                        cell.setCellValue(doubleValue);
                    } else {
                        cell.setCellStyle(dataStyle);
                        cell.setCellValue(String.valueOf(resolvedValue));
                    }

                    ++nextCol;
                }

                ++nextRow;
            }
        }

        // auto format
        nextCol = 0;
        for (int col = 0; col < columns.length; col++) {
            sheet.autoSizeColumn(col);
            int columnCharWidth = sheet.getColumnWidth(col) / 256;
            if (columnCharWidth > MAX_COLUM_CHAR_WIDTH) {
                sheet.setColumnWidth(col, MAX_COLUM_CHAR_WIDTH * 256);
            }
        }

        workbook.write(out);
        out.flush();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

From source file:de.iteratec.iteraplan.presentation.dialog.ExcelImport.ExcelImportController.java

License:Open Source License

private void experimentalExport(ExcelImportDialogMemory dialogMem, HttpServletResponse response) {
    //FIXME agu this is experimental feature
    if (dialogMem.getClickedButton().equals("button.excel.download_excel_data_experimental")) {

        //Note: Using NOOP message listener here
        Workbook wb = new XlsModelMapper(elasticMiService.getRMetamodel(), null, MessageListener.NOOP_LISTENER,
                ExcelFormat.XLS, IteraplanProperties.getProperties().getBuildVersion())
                        .write(elasticMiService.getModel());
        try {// w  ww. j a  va 2  s .  c  o  m
            response.setContentType(MIME_TYPE_MSEXCEL);
            response.setHeader("Content-Disposition", "attachment;filename=iteraplanExcelData.xls");
            OutputStream outputStream = response.getOutputStream();
            wb.write(outputStream);
        } catch (IOException e) {
            LOGGER.error("Excel Export error: ", e);
        }
    } else if (dialogMem.getClickedButton().equals("button.excel.download_excel_template_experimental")) {

        //Node: Using NOOP message listener here
        Workbook wb = new XlsModelMapper(elasticMiService.getRMetamodel(), null, MessageListener.NOOP_LISTENER,
                ExcelFormat.XLS, IteraplanProperties.getProperties().getBuildVersion()).write(new ModelImpl());
        try {
            response.setContentType(MIME_TYPE_MSEXCEL);
            response.setHeader("Content-Disposition", "attachment;filename=iteraplanExcelTemplate.xls");
            OutputStream outputStream = response.getOutputStream();
            wb.write(outputStream);
        } catch (IOException e) {
            LOGGER.error("Excel Export error: ", e);
        }
    }
}

From source file:de.iteratec.iteraplan.presentation.dialog.ExcelImport.ExportFrontendServiceImpl.java

License:Open Source License

private void writeExcelToOutputStream(HttpServletResponse response, Workbook wb, String filename,
        String mimeType) {// w  ww .  jav a  2s.co  m
    try {
        setContentTypeAndHeader(response, mimeType, filename);
        OutputStream outputStream = response.getOutputStream();
        wb.write(outputStream);
    } catch (IOException e) {
        LOGGER.error("Excel Export error: ", e);
    }
}

From source file:de.maklerpoint.office.Schnittstellen.Excel.ExportExcelXLSX.java

License:Open Source License

public void write() throws FileNotFoundException, IOException {
    FileOutputStream out = new FileOutputStream(new File(filename));
    Workbook wb;

    wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);
    Sheet sheet = wb.createSheet(sheetName);

    //turn off gridlines
    sheet.setDisplayGridlines(false);/*from   w  w  w.j  av a 2s .c  o  m*/
    sheet.setPrintGridlines(false);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);

    sheet.setAutobreaks(true);
    printSetup.setFitHeight((short) 1);
    printSetup.setFitWidth((short) 1);

    Row headerRow = sheet.createRow(0);
    headerRow.setHeightInPoints(12.75f);

    int[][] width = new int[titles.length][titles.length];

    for (int i = 0; i < titles.length; i++) {
        Cell cell = headerRow.createCell((short) i);
        cell.setCellValue(titles[i]);
        cell.setCellStyle(styles.get("header"));

        width[i][0] = titles[i].length();
    }

    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((short) j);
            if (data[i][j] == null)
                data[i][j] = "";

            cell.setCellValue(data[i][j].toString());

            if (data[i][j].toString().length() > width[j][0])
                width[j][0] = data[i][j].toString().length();
        }
    }

    for (short i = 0; i < titles.length; i++) {
        short widthShort = (short) (256 * (width[i][0] + 3));

        sheet.setColumnWidth(i, widthShort);
    }

    int position = (titles.length / 2) - 1;

    row = sheet.createRow(rownum + 3);
    cell = row.createCell((short) position);

    if (footName == null) {
        SimpleDateFormat df = new SimpleDateFormat("dd.MM.yyyy HH:mm");
        cell.setCellValue("Export MaklerPoint vom " + df.format(new Date(System.currentTimeMillis()))
                + " - www.maklerpoint.de");
    } else {
        cell.setCellValue(footName);
    }

    sheet.setZoom(3, 4);

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

From source file:de.quamoco.qm.editor.excelexport.HumanReadableExcelExporter.java

License:Apache License

public void export(String fileName, IProgressMonitor monitor) throws IOException {
    init();//from   w  w w.java  2 s .c  om

    monitor.beginTask("Export " + fileName,
            measureEvaluations.size() + 3 * factorAggregations.size() + 3 * multiMeasureEvaluations.size());

    Workbook workbook = fileName.endsWith("." + EExcelVersion.EXCEL_2007.getExtension()) ? new XSSFWorkbook()
            : new HSSFWorkbook();

    createAllInOneSheet(workbook);
    createMeasureSheet(workbook);
    createFactorSheet(workbook);
    createQASheet(workbook);

    // createMeasureEvaluationSheet(workbook, monitor);
    //
    // createFactorAggregationSheet(workbook,
    // QmPackage.eINSTANCE.getRanking_Weight(), "AggregationWeight",
    // monitor);
    //
    // createFactorAggregationSheet(workbook,
    // QmPackage.eINSTANCE.getRanking_Rank(), "AggregationRank",
    // monitor);
    //
    // createFactorImpactSheet(workbook, monitor);

    FileOutputStream fileOut = null;
    try {
        fileOut = new FileOutputStream(fileName);
        workbook.write(fileOut);
    } finally {
        if (fileOut != null) {
            fileOut.close();
        }
        monitor.done();
    }
}

From source file:de.quamoco.qm.editor.export.ResultCalibrationExporter.java

License:Apache License

/** Export the calibration data to an Excel file with a certain name. */
public void export(String fileName, IProgressMonitor monitor) throws IOException {

    init();//  w ww .  ja v  a  2 s. co  m

    monitor.beginTask("Export " + fileName,
            measureEvaluations.size() + 3 * factorAggregations.size() + 3 * multiMeasureEvaluations.size());

    Workbook workbook = fileName.endsWith("." + EExcelVersion.EXCEL_2007.getExtension()) ? new XSSFWorkbook()
            : new HSSFWorkbook();

    createMeasureEvaluationSheet(workbook, monitor);
    createFactorAggregationSheet(workbook, QmPackage.eINSTANCE.getRanking_Weight(), "AggregationWeight",
            monitor);
    createFactorAggregationSheet(workbook, QmPackage.eINSTANCE.getRanking_Rank(), "AggregationRank", monitor);
    createFactorImpactSheet(workbook, monitor);

    FileOutputStream fileOut = null;
    try {
        fileOut = new FileOutputStream(fileName);
        workbook.write(fileOut);
    } finally {
        if (fileOut != null) {
            fileOut.close();
        }
        monitor.done();
    }
}

From source file:de.thb.ue.backend.util.EvaluationExcelFileGenerator.java

License:Apache License

public void generateExcelFile() {

    Row row;/*from w  ww.  j ava 2s  .c o m*/
    Cell cell;
    int yOffset = 1;
    File workingDirectory = new File(
            (workingDirectoryPath.isEmpty() ? "" : (workingDirectoryPath + File.separatorChar))
                    + evaluationUID);
    if (!workingDirectory.exists()) {
        workingDirectory.mkdir();
    }
    File file = new File(workingDirectory, "auswertung.xls");

    try {
        FileOutputStream out = new FileOutputStream(file);
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet("Evaluation");

        // configure cell styles
        configureCellStyles(wb);

        /*
        * **********************************
        * begin formatting document
        * **********************************
        * */

        //construct first row of infopanel
        yOffset = constructInfoPanelRow("Lehrveranstaltung", subject, numberStudentsAll, yOffset, wb, sheet,
                InfoPanelBorderStyles.topLeftCorner, InfoPanelBorderStyles.top,
                InfoPanelBorderStyles.topRightCorner);

        //construct second row of infopanel
        yOffset = constructInfoPanelRow("Semester", semesterType == SemesterType.WINTER ? "Winter" : "Sommer",
                numberStudentsAll, yOffset, wb, sheet, InfoPanelBorderStyles.left, InfoPanelBorderStyles.none,
                InfoPanelBorderStyles.right);

        //construct third row of infopanel
        StringBuilder tutors = new StringBuilder();
        for (int i = 0; i < this.tutors.size(); i++) {
            if (i + 1 < this.tutors.size()) {
                tutors.append(this.tutors.get(i)).append(", ");
            } else {
                tutors.append(this.tutors.get(i));
            }
        }

        yOffset = constructInfoPanelRow("Lehrende(r)", tutors.toString(), numberStudentsAll, yOffset, wb, sheet,
                InfoPanelBorderStyles.left, InfoPanelBorderStyles.none, InfoPanelBorderStyles.right);

        //construct fourth row of infopanel
        yOffset = constructInfoPanelRow("Datum der Befragung", dateOfEvaluation.toString("dd.MM.yy HH:mm"),
                numberStudentsAll, yOffset, wb, sheet, InfoPanelBorderStyles.left, InfoPanelBorderStyles.none,
                InfoPanelBorderStyles.right);

        //construct fifth row of infopanel
        yOffset = constructInfoPanelRow("Anzahl der Teilnehmer", Integer.toString(numberStudentsAll),
                numberStudentsAll, yOffset, wb, sheet, InfoPanelBorderStyles.left, InfoPanelBorderStyles.none,
                InfoPanelBorderStyles.right);

        //construct sixth row of infopanel () last
        yOffset = constructInfoPanelRow("Anzahl der ausgefllten Fragebgen",
                Integer.toString(numberStudentsVoted), numberStudentsAll, yOffset, wb, sheet,
                InfoPanelBorderStyles.bottomLeftCorner, InfoPanelBorderStyles.bottom,
                InfoPanelBorderStyles.bottomRightCorner);

        //begin construction of evaluationPanel
        yOffset++;
        row = sheet.createRow(yOffset);
        cell = row.createCell(1, Cell.CELL_TYPE_STRING);
        cell.setCellValue("Frage");
        cell.setCellStyle(headerStyle);

        cell = row.createCell(2, Cell.CELL_TYPE_STRING);
        cell.setCellValue("MW");
        cell.setCellStyle(headerStyle);

        cell = row.createCell(3, Cell.CELL_TYPE_STRING);
        cell.setCellValue("Ifd NR.");
        cell.setCellStyle(headerStyle);

        cell = row.createCell(4);
        cell.setCellStyle(commonStyle);

        // add count of valid evaluations (how many students voted) (horizontal)
        for (int i = 0; i < numberStudentsVoted; i++) {
            cell = row.createCell(i + 5);
            cell.setCellValue(i + 1);
            sheet.setColumnWidth(cell.getColumnIndex(), 4 * 256);
            cell.setCellStyle(headerStyle);
        }

        // get letter of last student column
        CellReference cellReference = new CellReference(cell.getRowIndex(), cell.getColumnIndex());
        String endCellName = cellReference.getCellRefParts()[2];

        Row headRow = row;

        for (int i = 1; i < mcQuestionTexts.size() + 1; i++) {
            //add number of questions
            row = sheet.createRow(i + yOffset);
            cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC);
            cell.setCellValue(i);
            cell.setCellStyle(commonStyle);

            //add average formula
            cell = row.createCell(2, Cell.CELL_TYPE_FORMULA);
            //formlua works with blanks, empty strings and negative values
            String formula = "SUMPRODUCT(ABS(N(+F" + (i + yOffset + 1) + ":" + endCellName + (i + yOffset + 1)
                    + ")))/COUNT(F" + (i + yOffset + 1) + ":" + endCellName + (i + yOffset + 1) + ")";
            //String averageFormula = "AVERAGE(IF(F" + (i + yOffset + 1) + ":" + endCellName + (i + yOffset + 1) + "<>\"\", ABS(F" + (i + yOffset + 1) + ":" + endCellName + (i + yOffset + 1) + ")))";
            cell.setCellFormula(formula);
            cell.setCellStyle(commonStyle);

            //fill blank cells
            cell = row.createCell(3);
            cell.setCellStyle(commonStyle);

            //add question texts
            sheet.setColumnWidth(4, findLongestString(mcQuestionTexts) * 256
                    * (wb.getFontAt(questionStyle.getFontIndex()).getFontHeightInPoints()) / 10);
            cell = row.createCell(4, Cell.CELL_TYPE_STRING);
            cell.setCellValue(mcQuestionTexts.get(i - 1));
            cell.setCellStyle(questionStyle);
        }

        //add student votes
        for (int i = 0; i < studentVotes.size(); i++) {
            Vote vote = studentVotes.get(i);
            for (int k = 0; k < mcQuestionTexts.size(); k++) {
                row = sheet.getRow(headRow.getRowNum() + 1 + k);
                cell = row.createCell(5 + i);
                for (MCAnswer answer : vote.getMcAnswers()) {
                    //if question of inner loop equals question of outer loop we found
                    // the correct question for this cell
                    if (answer.getQuestion().getText().equals(mcQuestionTexts.get(k))) {
                        Choice choice = answer.getChoice();
                        if (choice != null && choice.getGrade() != 0) {
                            cell = colorizeCell(cell, wb, choice.getGrade());
                            cell.setCellValue(answer.getChoice().getGrade());
                        } else {
                            cell = colorizeCell(cell, wb, -1);
                            cell.setCellValue("");
                        }
                    }
                }
            }
        }

        // include textual answers
        createTextualAnswers(studentVotes, textualQuestionTexts, sheet, wb);
        wb.write(out);
        out.close();
    } catch (IOException e) {
        e.printStackTrace();
    }

}

From source file:de.tudarmstadt.ukp.csniper.webapp.statistics.page.export.ExportExcelTask.java

License:Apache License

@Override
protected void run() {
    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("Summary");

    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);// w  w  w  .j av a2 s .c  o m
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    contextProvider.setOutputPos(exportModel.includePos);

    outputFile = null;
    OutputStream os = null;
    try {
        List<AggregatedEvaluationResult> results = repository.listAggregatedResults(formModel.getCollections(),
                formModel.getTypes(), formModel.getUsers(), formModel.getUserThreshold(),
                formModel.getConfidenceThreshold());

        List<AdditionalColumn> ac = exportModel.additionalColumns;

        Collections.sort(results, new Comparator<AggregatedEvaluationResult>() {
            @Override
            public int compare(AggregatedEvaluationResult aO1, AggregatedEvaluationResult aO2) {
                String id1 = aO1.getItem().getCollectionId() + "|" + aO1.getItem().getDocumentId();
                String id2 = aO2.getItem().getCollectionId() + "|" + aO2.getItem().getDocumentId();
                return id1.compareTo(id2);
            }
        });

        // Write header row
        List<String> colIds = new ArrayList<String>(Arrays.asList("User", "Collection", "Document", "Begin",
                "End", "Left", "Unit", "Right", "Type", "Class", "Confidence", "Correct", "Wrong"));
        for (int i = 0; i < ac.size(); i++) {
            colIds.add(ac.get(i).getName());
        }
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < colIds.size(); i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(colIds.get(i));
        }

        // Write rest
        setTotal(results.size());
        int rowNum = 1;
        for (AggregatedEvaluationResult aer : results) {
            ResultFilter classification = aer.getClassification();
            if (formModel.getFilters().contains(classification)) {
                ItemContext context = contextProvider.getContext(aer.getItem(), exportModel.contextSize,
                        exportModel.contextSize);

                // only differentiate between users if additional columns are being exported
                Set<String> users;
                if (ac.isEmpty()) {
                    users = new HashSet<String>(Arrays.asList(""));
                } else {
                    users = aer.getUsers(false);
                }

                // output the AggregatedEvaluationResult for every user (because the additional
                // columns entries might differ)
                for (String user : users) {
                    Row row = sheet.createRow(rowNum);
                    row.createCell(0).setCellValue(user);
                    row.createCell(1).setCellValue(aer.getItem().getCollectionId());
                    row.createCell(2).setCellValue(aer.getItem().getDocumentId());
                    row.createCell(3).setCellValue(aer.getItem().getBeginOffset());
                    row.createCell(4).setCellValue(aer.getItem().getEndOffset());
                    row.createCell(5).setCellValue(context.getLeft());
                    row.createCell(6).setCellValue(context.getUnit());
                    row.createCell(7).setCellValue(context.getRight());
                    row.createCell(8).setCellValue(aer.getItem().getType());
                    row.createCell(9).setCellValue(classification.toString());
                    row.createCell(10).setCellValue(aer.getConfidence());
                    row.createCell(11).setCellValue(aer.getCorrect());
                    row.createCell(12).setCellValue(aer.getWrong());
                    for (int i = 0; i < ac.size(); i++) {
                        String cellValue = repository.getEvaluationResult(aer.getItem().getId(), user)
                                .getAdditionalColumns().get(ac.get(i));
                        if (cellValue == null) {
                            cellValue = "";
                        }
                        row.createCell(colIds.size() - ac.size() + i).setCellValue(cellValue);

                    }
                    rowNum++;
                }
            }

            // Make sure we do not get to 100% before we did the classification, because
            // otherwise ProgressBar.onFinish() will trigger!!!
            increment();
            if (isCancelled()) {
                break;
            }
        }

        outputFile = File.createTempFile("date", ".csv");
        os = new FileOutputStream(outputFile);
        wb.write(os);
    } catch (IOException e) {
        e.printStackTrace();
        error("Export failed: " + ExceptionUtils.getRootCauseMessage(e));
        cancel();
    } finally {
        IOUtils.closeQuietly(os);
        if (isCancelled()) {
            clean();
        }
    }
}

From source file:de.tudarmstadt.ukp.dkpro.lab.reporting.FlexTable.java

License:Apache License

public StreamWriter getExcelWriter() {
    return new StreamWriter() {
        @Override//from  w ww  .  ja  v a 2s.  co m
        public void write(OutputStream aStream) throws Exception {
            String[] colIds = compact ? getCompactColumnIds(false) : getColumnIds();

            Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet("Summary");

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

            // Header row
            {
                Row row = sheet.createRow(0);
                Cell rowIdCell = row.createCell(0);
                rowIdCell.setCellValue("ID");

                int colNum = 1;
                for (String colId : colIds) {
                    Cell cell = row.createCell(colNum);
                    cell.setCellValue(colId);
                    colNum++;
                }
            }

            //  Body rows
            {
                int rowNum = 1;
                for (String rowId : getRowIds()) {
                    Row row = sheet.createRow(rowNum);
                    Cell rowIdCell = row.createCell(0);
                    rowIdCell.setCellValue(rowId);

                    int colNum = 1;
                    for (String colId : colIds) {
                        Cell cell = row.createCell(colNum);
                        String value = getValueAsString(rowId, colId);
                        try {
                            cell.setCellValue(Double.valueOf(value));
                        } catch (NumberFormatException e) {
                            cell.setCellValue(value);
                        }
                        colNum++;
                    }
                    rowNum++;
                }
            }

            wb.write(aStream);
        }
    };
}

From source file:demo.poi.BusinessPlan.java

License:Apache License

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

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

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

    // turn off gridlines
    sheet.setDisplayGridlines(false);//w  ww.  j av  a2s.com
    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++) {
            String str = data[i][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 = "target/businessplan.xls";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}