Example usage for org.apache.poi.ss.usermodel Cell setCellStyle

List of usage examples for org.apache.poi.ss.usermodel Cell setCellStyle

Introduction

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

Prototype

void setCellStyle(CellStyle style);

Source Link

Document

Set the style for the cell.

Usage

From source file:com.dituiba.excel.DefaultOutputAdapter.java

License:Apache License

public void outputIntAdapter(DataBean dataBean, Object fieldValue, String fieldName, Cell cell)
        throws AdapterException {
    log.debug("in DefaultOutputAdapter:outputIntAdapter fieldName:{} fieldValue:{}", fieldName, fieldValue);
    if (ObjectHelper.isNullOrEmptyString(fieldValue))
        return;/*from  ww w  .  j a  v a2 s .c  om*/
    Workbook workbook = cell.getSheet().getWorkbook();
    CellStyle cellStyle = workbook.createCellStyle();
    CreationHelper createHelper = workbook.getCreationHelper();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"));
    cell.setCellValue(NumberUtils.format(fieldValue, 0));
    cell.setCellStyle(cellStyle);
}

From source file:com.dituiba.excel.ExcelUtility.java

License:Apache License

public static void copyCell(Cell srcCell, Cell distCell) {
    distCell.setCellStyle(srcCell.getCellStyle());
    if (srcCell.getCellComment() != null) {
        distCell.setCellComment(srcCell.getCellComment());
    }//  www . j ava 2 s .  c o m
    int srcCellType = srcCell.getCellType();
    distCell.setCellType(srcCellType);

    if (srcCellType == Cell.CELL_TYPE_NUMERIC) {
        if (DateUtil.isCellDateFormatted(srcCell)) {
            distCell.setCellValue(srcCell.getDateCellValue());
        } else {
            distCell.setCellValue(srcCell.getNumericCellValue());
        }
    } else if (srcCellType == Cell.CELL_TYPE_STRING) {
        distCell.setCellValue(srcCell.getRichStringCellValue());
    } else if (srcCellType == Cell.CELL_TYPE_BLANK) {
        //nothing
    } else if (srcCellType == Cell.CELL_TYPE_BOOLEAN) {
        distCell.setCellValue(srcCell.getBooleanCellValue());
    } else if (srcCellType == Cell.CELL_TYPE_ERROR) {
        distCell.setCellErrorValue(srcCell.getErrorCellValue());
    } else if (srcCellType == Cell.CELL_TYPE_FORMULA) {
        distCell.setCellFormula(srcCell.getCellFormula());
    } else {
        //nothing
    }
}

From source file:com.dituiba.excel.ExportTableService.java

License:Apache License

public void doExport() {
    Collection<CellBean> cellBeans = tableBean.getCellBeans();
    if (ObjectHelper.isNotEmpty(cellBeans)) {
        for (CellBean cellBean : cellBeans) {
            if (cellBean.getXSize() > 1 || cellBean.getYSize() > 1) {
                log.debug("??{}", JsonUtil.toJSON(cellBean));
                CellRangeAddress range = new CellRangeAddress(cellBean.getRowIndex(),
                        cellBean.getRowIndex() + cellBean.getYSize() - 1, cellBean.getColumnIndex(),
                        cellBean.getColumnIndex() + cellBean.getXSize() - 1);
                sheet.addMergedRegion(range);
            }//from  ww  w .ja va  2  s . c om
            log.debug("set row:{},column:{},content:{}", cellBean.getRowIndex(), cellBean.getColumnIndex(),
                    cellBean.getContent());
            Cell cell = sheet.getRow(cellBean.getRowIndex()).getCell(cellBean.getColumnIndex());
            cell.setCellValue(cellBean.getContent());
            CellStyle cellStyle = cell.getCellStyle();
            if (cellStyle == null) {
                cellStyle = sheet.getWorkbook().createCellStyle();
            }
            if (cellBean.isAlignCenter()) {
                cellStyle.setAlignment(CellStyle.ALIGN_CENTER);//
            }
            if (cellBean.isVerticalCenter()) {
                cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//
            }
            cellStyle.setWrapText(cellBean.isWrapText());
            cell.setCellStyle(cellStyle);
        }
    }
}

From source file:com.docdoku.server.export.ExcelGenerator.java

License:Open Source License

public File generateXLSResponse(QueryResult queryResult, Locale locale, String baseURL) {
    File excelFile = new File("export_parts.xls");
    //Blank workbook
    XSSFWorkbook workbook = new XSSFWorkbook();

    //Create a blank sheet
    XSSFSheet sheet = workbook.createSheet("Parts Data");

    String header = StringUtils.join(queryResult.getQuery().getSelects(), ";");
    String[] columns = header.split(";");

    Map<Integer, String[]> data = new HashMap<>();
    String[] headerFormatted = createXLSHeaderRow(header, columns, locale);
    data.put(1, headerFormatted);//from w w w .j a  v  a  2s. c  o  m

    Map<Integer, String[]> commentsData = new HashMap<>();
    String[] headerComments = createXLSHeaderRowComments(header, columns);
    commentsData.put(1, headerComments);

    List<String> selects = queryResult.getQuery().getSelects();
    int i = 1;
    for (QueryResultRow row : queryResult.getRows()) {
        i++;
        data.put(i, createXLSRow(selects, row, baseURL));
        commentsData.put(i, createXLSRowComments(selects, row));
    }

    //Iterate over data and write to sheet
    Set<Integer> keyset = data.keySet();
    int rownum = 0;

    for (Integer key : keyset) {

        Row row = sheet.createRow(rownum++);
        String[] objArr = data.get(key);
        int cellnum = 0;
        for (String obj : objArr) {
            Cell cell = row.createCell(cellnum++);
            cell.setCellValue(obj);
        }

        CreationHelper factory = workbook.getCreationHelper();
        Drawing drawing = sheet.createDrawingPatriarch();
        String[] commentsObjArr = commentsData.get(key);
        cellnum = 0;
        for (String commentsObj : commentsObjArr) {
            if (commentsObj.length() > 0) {
                Cell cell = row.getCell(cellnum) != null ? row.getCell(cellnum) : row.createCell(cellnum);

                // When the comment box is visible, have it show in a 1x3 space
                ClientAnchor anchor = factory.createClientAnchor();
                anchor.setCol1(cell.getColumnIndex());
                anchor.setCol2(cell.getColumnIndex() + 1);
                anchor.setRow1(row.getRowNum());
                anchor.setRow2(row.getRowNum() + 1);

                Comment comment = drawing.createCellComment(anchor);
                RichTextString str = factory.createRichTextString(commentsObj);
                comment.setString(str);

                // Assign the comment to the cell
                cell.setCellComment(comment);
            }
            cellnum++;
        }
    }

    // Define header style
    Font headerFont = workbook.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontHeightInPoints((short) 10);
    headerFont.setFontName("Courier New");
    headerFont.setItalic(true);
    headerFont.setColor(IndexedColors.WHITE.getIndex());
    CellStyle headerStyle = workbook.createCellStyle();
    headerStyle.setFont(headerFont);
    headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    // Set header style
    for (int j = 0; j < columns.length; j++) {
        Cell cell = sheet.getRow(0).getCell(j);
        cell.setCellStyle(headerStyle);

        if (cell.getCellComment() != null) {
            String comment = cell.getCellComment().getString().toString();

            if (comment.equals(QueryField.CTX_PRODUCT_ID) || comment.equals(QueryField.CTX_SERIAL_NUMBER)
                    || comment.equals(QueryField.PART_MASTER_NUMBER)) {
                for (int k = 0; k < queryResult.getRows().size(); k++) {
                    Cell grayCell = sheet.getRow(k + 1).getCell(j) != null ? sheet.getRow(k + 1).getCell(j)
                            : sheet.getRow(k + 1).createCell(j);
                    grayCell.setCellStyle(headerStyle);
                }
            }
        }
    }

    try {
        //Write the workbook in file system
        FileOutputStream out = new FileOutputStream(excelFile);
        workbook.write(out);
        out.close();
    } catch (Exception e) {
        LOGGER.log(Level.FINEST, null, e);
    }
    return excelFile;

}

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

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;/*from   w  w w. j  a  va 2s.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.ebay.xcelite.writer.BeanSheetWriter.java

License:Apache License

@SuppressWarnings("unchecked")
private void writeToCell(Cell cell, Col col, Object fieldValueObj) {
    if (fieldValueObj == null) {
        cell.setCellValue((String) null);
        return;//from   w ww .j  av a  2  s . co m
    }
    if (col.getConverter() != null) {
        try {
            ColumnValueConverter<?, Object> converter = (ColumnValueConverter<?, Object>) col.getConverter()
                    .newInstance();
            fieldValueObj = converter.serialize(fieldValueObj);
        } catch (InstantiationException e) {
            throw new RuntimeException(e);
        } catch (IllegalAccessException e) {
            throw new RuntimeException(e);
        }
    }
    if (col.getDataFormat() != null) {
        cell.setCellStyle(CellStylesBank.get(sheet.getNativeSheet().getWorkbook())
                .getCustomDataFormatStyle(col.getDataFormat()));
    }

    if (col.getType() == Date.class) {
        if (col.getDataFormat() == null) {
            cell.setCellStyle(CellStylesBank.get(sheet.getNativeSheet().getWorkbook()).getDateStyle());
        }
    }

    writeToCell(cell, fieldValueObj, col.getType());
}

From source file:com.ebay.xcelite.writer.BeanSheetWriter.java

License:Apache License

private void addColumns(Set<Col> columnsToAdd, boolean append) {
    int i = (headerRow == null || headerRow.getLastCellNum() == -1) ? 0 : headerRow.getLastCellNum();
    for (Col column : columnsToAdd) {
        if (append && columns.contains(column))
            continue;
        if (writeHeader) {
            Cell cell = headerRow.createCell(i);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellStyle(CellStylesBank.get(sheet.getNativeSheet().getWorkbook()).getBoldStyle());
            cell.setCellValue(column.getName());
            i++;//from w  w w .  j a  v a  2  s .c o m
        }
        columns.add(column);
    }
}

From source file:com.ebay.xcelite.writer.SheetWriterAbs.java

License:Apache License

protected void writeToCell(Cell cell, Object fieldValueObj, Class<?> dataType) {
    cell.setCellStyle(CellStylesBank.get(sheet.getNativeSheet().getWorkbook()).getBoldStyle());
    if (fieldValueObj == null) {
        cell.setCellType(CellType.BLANK);
        cell.setCellValue("");
    } else {/*from   ww w  .j  a  v  a2  s .  c  o  m*/
        Class<?> type = fieldValueObj.getClass();
        if (dataType != null) {
            type = dataType;
        }
        if (type == Date.class) {
            cell.setCellValue((Date) fieldValueObj);
        } else if (type == Boolean.class) {
            cell.setCellValue((Boolean) fieldValueObj);
        } else if (type == Double.class || type == double.class || type == Integer.class || type == int.class
                || type == Long.class || type == long.class || type == Float.class || type == float.class
                || type == Short.class || type == short.class) {
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(Double.valueOf(fieldValueObj.toString()));
        } else {
            cell.setCellType(CellType.STRING);
            cell.setCellValue(fieldValueObj.toString());
        }
    }
}

From source file:com.ebay.xcelite.writer.SimpleSheetWriter.java

License:Apache License

@Override
public void write(Collection<Collection<Object>> data) {
    int i = 0;//from  www  . j  a v a  2  s.  co m
    for (Collection<Object> row : data) {
        Row excelRow = sheet.getNativeSheet().createRow(i);
        int j = 0;
        for (Object column : row) {
            Cell cell = excelRow.createCell(j);
            if (writeHeader && i == 0) {
                cell.setCellStyle(CellStylesBank.get(sheet.getNativeSheet().getWorkbook()).getBoldStyle());
            }
            writeToCell(cell, column, null);
            ++j;
        }
        ++i;
    }
}

From source file:com.efficio.fieldbook.service.ExportExcelServiceImpl.java

License:Open Source License

public FileOutputStream exportFieldMapToExcel(String fileName, UserFieldmap userFieldMap)
        throws FieldbookException {
    Locale locale = LocaleContextHolder.getLocale();

    boolean isTrial = userFieldMap.isTrial();

    // Summary of Trial/Nursery, Field and Planting Details
    String summaryOfFieldbookFieldPlantingDetailsLabel = messageSource
            .getMessage("fieldmap.header.summary.for.trial", null, locale);
    //SUMMARY OF TRIAL, FIELD AND PLANTING DETAILS
    String selectedFieldbookLabel = messageSource.getMessage("fieldmap.trial.selected.trial", null, locale); //Selected Trial:
    if (!isTrial) {
        summaryOfFieldbookFieldPlantingDetailsLabel = messageSource
                .getMessage("fieldmap.header.summary.for.nursery", null, locale);
        //SUMMARY OF NURSERY, FIELD AND PLANTING DETAILS
        selectedFieldbookLabel = messageSource.getMessage("fieldmap.nursery.selected.nursery", null, locale); //Selected Nursery:
    }/*from   w  w  w .  j  ava  2  s .c  o m*/
    String selectedFieldbookValue = userFieldMap.getBlockName();

    String orderHeader = messageSource.getMessage("fieldmap.trial.order", null, locale);
    String studyHeader = messageSource.getMessage((isTrial ? "fieldmap.trial" : "fieldmap.nursery"), null,
            locale);
    String instanceHeader = messageSource.getMessage("fieldmap.trial.instance", null, locale);
    String entriesCountHeader = messageSource.getMessage("fieldmap.trial.entry.count", null, locale);
    String repsCountHeader = messageSource.getMessage("fieldmap.trial.reps.count", null, locale);
    String plotsNeededHeader = messageSource.getMessage("fieldmap.trial.plots.needed", null, locale);
    String totalPlotsHeader = messageSource.getMessage("fieldmap.trial.total.number.of.plots", null, locale);
    String datasetNameHeader = messageSource.getMessage("fieldmap.nursery.dataset", null, locale);

    //  Field And Block Details
    String fieldAndBlockDetailsLabel = messageSource.getMessage("fieldmap.trial.field.and.block.details", null,
            locale);
    //FIELD AND BLOCK DETAILS
    String fieldLocationLabel = messageSource.getMessage("fieldmap.label.field.location", null, locale); //Field Location
    String fieldLocationValue = userFieldMap.getLocationName();
    String fieldNameLabel = messageSource.getMessage("fieldmap.label.field.name", null, locale); //Field Name
    String fieldNameValue = userFieldMap.getFieldName();
    String blockNameLabel = messageSource.getMessage("fieldmap.label.block.name", null, locale); //Block Name
    String blockNameValue = userFieldMap.getBlockName();

    // Row, Range & Plot Details
    String rowRangePlotDetailsLabel = messageSource.getMessage("fieldmap.trial.row.and.range.and.plot.details",
            null, locale);
    //ROW, RANGE AND PLOT DETAILS
    String blockCapacityLabel = messageSource.getMessage("fieldmap.label.block.capacity", null, locale);
    //Block Capacity
    String blockCapacityValue = userFieldMap.getBlockCapacityString(messageSource); //e.g. "10 Columns, 10 Ranges"
    String rowsPerPlotLabel = messageSource.getMessage("fieldmap.label.rows.per.plot", null, locale); //Rows per Plot
    int rowsPerPlotValue = userFieldMap.getNumberOfRowsPerPlot();
    String columnsLabel = messageSource.getMessage("fieldmap.label.columns", null, locale); //Columns     
    Integer columnsValue = userFieldMap.getNumberOfColumnsInBlock(); // 10
    String machineCapacityLabel = messageSource.getMessage("fieldmap.label.row.capacity.machine", null, locale);
    //machine row capacity
    Integer machineCapacityValue = userFieldMap.getMachineRowCapacity();

    //Planting Details
    String plantingDetailsLabel = messageSource.getMessage("fieldmap.header.planting.details", null, locale);
    //PLANTING DETAILS
    String startingCoordinatesLabel = messageSource.getMessage("fieldmap.label.starting.coordinates", null,
            locale);
    //Starting Coordinates     
    String startingCoordinatesValue = userFieldMap.getStartingCoordinateString(messageSource); // Column 1, Range 1
    String plantingOrderLabel = messageSource.getMessage("fieldmap.label.planting.order", null, locale); //Planting Order     
    String plantingOrderValue = userFieldMap.getPlantingOrderString(messageSource); //"Row/Column" or "Serpentine"

    // FieldMap
    String fieldMapLabel = messageSource.getMessage("fieldmap.header.fieldmap", null, locale); //FIELD MAP
    String rowsLabel = messageSource.getMessage("fieldmap.label.rows", null, locale); //Rows
    String columnLabel = messageSource.getMessage("fieldmap.label.capitalized.column", null, locale); //Column
    String rangeLabel = messageSource.getMessage("fieldmap.label.capitalized.range", null, locale); //Range

    try {
        //Create workbook
        HSSFWorkbook workbook = new HSSFWorkbook();
        String summaryLabelSheet = messageSource.getMessage("fieldmap.header.excel.summary", null, locale);
        Sheet summarySheet = workbook.createSheet(summaryLabelSheet);
        Sheet fieldMapSheet = workbook.createSheet(fieldMapLabel);

        CellStyle labelStyle = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        labelStyle.setFont(font);

        CellStyle wrapStyle = workbook.createCellStyle();
        wrapStyle.setWrapText(true);
        wrapStyle.setAlignment(CellStyle.ALIGN_CENTER);

        CellStyle mainHeaderStyle = workbook.createCellStyle();

        HSSFPalette palette = workbook.getCustomPalette();
        // get the color which most closely matches the color you want to use
        HSSFColor myColor = palette.findSimilarColor(179, 165, 165);
        // get the palette index of that color 
        short palIndex = myColor.getIndex();
        // code to get the style for the cell goes here
        mainHeaderStyle.setFillForegroundColor(palIndex);
        mainHeaderStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        CellStyle mainSubHeaderStyle = workbook.createCellStyle();

        HSSFPalette paletteSubHeader = workbook.getCustomPalette();
        // get the color which most closely matches the color you want to use
        HSSFColor myColorSubHeader = paletteSubHeader.findSimilarColor(190, 190, 186);
        // get the palette index of that color 
        short palIndexSubHeader = myColorSubHeader.getIndex();
        // code to get the style for the cell goes here
        mainSubHeaderStyle.setFillForegroundColor(palIndexSubHeader);
        mainSubHeaderStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        mainSubHeaderStyle.setAlignment(CellStyle.ALIGN_CENTER);

        int rowIndex = 0;
        int columnIndex = 0;

        // Create Header Information

        // Row 1: SUMMARY OF TRIAL, FIELD AND PLANTING DETAILS 
        CellStyle headerLabelStyle = workbook.createCellStyle();
        font = workbook.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerLabelStyle.setFont(font);
        headerLabelStyle.setAlignment(CellStyle.ALIGN_CENTER);

        Row row = summarySheet.createRow(rowIndex++);
        Cell summaryCell = row.createCell(columnIndex);
        summaryCell.setCellValue(summaryOfFieldbookFieldPlantingDetailsLabel);

        summaryCell.setCellStyle(headerLabelStyle);

        summarySheet.addMergedRegion(new CellRangeAddress(rowIndex - 1, //first row (0-based)
                rowIndex - 1, //last row  (0-based)
                columnIndex, //first column (0-based)
                columnIndex + 5 //last column  (0-based)
        ));

        // Row 2: Space
        row = summarySheet.createRow(rowIndex++);

        // Row 3: Fieldbook Name, Entries, Reps, Plots
        row = summarySheet.createRow(rowIndex++);

        // Selected Trial : [Fieldbook Name]  TABLE SECTION
        Cell labelCell = row.createCell(columnIndex++);
        labelCell.setCellValue(selectedFieldbookLabel);

        row = summarySheet.createRow(rowIndex++);
        columnIndex = 0;
        Cell headerCell = row.createCell(columnIndex++);
        headerCell.setCellValue(orderHeader);
        headerCell.setCellStyle(labelStyle);
        headerCell = row.createCell(columnIndex++);
        headerCell.setCellValue(studyHeader);
        headerCell.setCellStyle(labelStyle);
        if (isTrial) {
            headerCell = row.createCell(columnIndex++);
            headerCell.setCellValue(instanceHeader);
            headerCell.setCellStyle(labelStyle);
            headerCell = row.createCell(columnIndex++);
            headerCell.setCellValue(entriesCountHeader);
            headerCell.setCellStyle(labelStyle);
            headerCell = row.createCell(columnIndex++);
            headerCell.setCellValue(repsCountHeader);
            headerCell.setCellStyle(labelStyle);
        } else {
            headerCell = row.createCell(columnIndex++);
            headerCell.setCellValue(datasetNameHeader);
            headerCell.setCellStyle(labelStyle);
        }
        headerCell = row.createCell(columnIndex++);
        headerCell.setCellValue(plotsNeededHeader);
        headerCell.setCellStyle(labelStyle);

        for (SelectedFieldmapRow rec : userFieldMap.getSelectedFieldmapList().getRows()) {
            row = summarySheet.createRow(rowIndex++);
            columnIndex = 0;
            row.createCell(columnIndex++).setCellValue(rec.getOrder());
            row.createCell(columnIndex++).setCellValue(rec.getStudyName());
            if (isTrial) {
                row.createCell(columnIndex++).setCellValue(rec.getTrialInstanceNo());
                row.createCell(columnIndex++).setCellValue(String.valueOf(rec.getEntryCount()));
                row.createCell(columnIndex++).setCellValue(String.valueOf(rec.getRepCount()));
                row.createCell(columnIndex++).setCellValue(String.valueOf(rec.getPlotCount()));
            } else {
                row.createCell(columnIndex++).setCellValue(rec.getDatasetName());
                row.createCell(columnIndex++).setCellValue(String.valueOf(rec.getEntryCount()));
            }
        }

        row = summarySheet.createRow(rowIndex++);
        columnIndex = 0;
        headerCell = row.createCell(columnIndex++);
        headerCell.setCellValue(totalPlotsHeader);
        headerCell.setCellStyle(labelStyle);
        row.createCell(columnIndex++)
                .setCellValue(String.valueOf(userFieldMap.getSelectedFieldmapList().getTotalNumberOfPlots()));

        // Row 4: Space
        row = summarySheet.createRow(rowIndex++);

        // Row 5: Header - Details Heading
        row = summarySheet.createRow(rowIndex++);
        columnIndex = 0;
        labelCell = row.createCell(columnIndex++);
        labelCell.setCellValue(fieldAndBlockDetailsLabel);
        labelCell.setCellStyle(headerLabelStyle);

        summarySheet.addMergedRegion(new CellRangeAddress(rowIndex - 1, //first row (0-based)
                rowIndex - 1, //last row  (0-based)
                columnIndex - 1, //first column (0-based)
                columnIndex //last column  (0-based)
        ));

        row.createCell(columnIndex++);
        labelCell = row.createCell(columnIndex++);
        labelCell.setCellValue(rowRangePlotDetailsLabel);
        labelCell.setCellStyle(headerLabelStyle);

        summarySheet.addMergedRegion(new CellRangeAddress(rowIndex - 1, //first row (0-based)
                rowIndex - 1, //last row  (0-based)
                columnIndex - 1, //first column (0-based)
                columnIndex //last column  (0-based)
        ));

        row.createCell(columnIndex++);
        labelCell = row.createCell(columnIndex++);
        labelCell.setCellValue(plantingDetailsLabel);
        labelCell.setCellStyle(headerLabelStyle);

        summarySheet.addMergedRegion(new CellRangeAddress(rowIndex - 1, //first row (0-based)
                rowIndex - 1, //last row  (0-based)
                columnIndex - 1, //first column (0-based)
                columnIndex //last column  (0-based)
        ));

        row.createCell(columnIndex++);

        //Row 6: Field Location, Block Capacity, Starting Coordinates
        row = summarySheet.createRow(rowIndex++);
        columnIndex = 0;
        labelCell = row.createCell(columnIndex++);
        labelCell.setCellValue(fieldLocationLabel);
        labelCell.setCellStyle(labelStyle);

        row.createCell(columnIndex++).setCellValue(fieldLocationValue);
        labelCell = row.createCell(columnIndex++);
        labelCell.setCellValue(blockCapacityLabel);
        labelCell.setCellStyle(labelStyle);

        row.createCell(columnIndex++).setCellValue(blockCapacityValue);

        labelCell = row.createCell(columnIndex++);
        labelCell.setCellValue(startingCoordinatesLabel);
        labelCell.setCellStyle(labelStyle);

        row.createCell(columnIndex++).setCellValue(startingCoordinatesValue);

        // Row 7: Field Name, Rows Per Plot, Planting Order
        row = summarySheet.createRow(rowIndex++);
        columnIndex = 0;
        labelCell = row.createCell(columnIndex++);
        labelCell.setCellValue(fieldNameLabel);
        labelCell.setCellStyle(labelStyle);

        row.createCell(columnIndex++).setCellValue(fieldNameValue);
        labelCell = row.createCell(columnIndex++);
        labelCell.setCellValue(rowsPerPlotLabel);
        labelCell.setCellStyle(labelStyle);

        row.createCell(columnIndex++).setCellValue(String.valueOf(rowsPerPlotValue));
        labelCell = row.createCell(columnIndex++);
        labelCell.setCellValue(plantingOrderLabel);
        labelCell.setCellStyle(labelStyle);

        row.createCell(columnIndex++).setCellValue(plantingOrderValue);

        // Row 8: Block Name, Columns
        row = summarySheet.createRow(rowIndex++);
        columnIndex = 0;
        labelCell = row.createCell(columnIndex++);
        labelCell.setCellValue(blockNameLabel);
        labelCell.setCellStyle(labelStyle);

        row.createCell(columnIndex++).setCellValue(blockNameValue);
        labelCell = row.createCell(columnIndex++);
        labelCell.setCellValue(columnsLabel);
        labelCell.setCellStyle(labelStyle);

        row.createCell(columnIndex++).setCellValue(String.valueOf(columnsValue));

        labelCell = row.createCell(columnIndex++);
        labelCell.setCellValue(machineCapacityLabel);
        labelCell.setCellStyle(labelStyle);

        row.createCell(columnIndex++).setCellValue(String.valueOf(machineCapacityValue));

        // Row 9: Space
        row = summarySheet.createRow(rowIndex++);

        for (int columnsResize = 0; columnsResize < columnIndex; columnsResize++) {
            summarySheet.autoSizeColumn(columnsResize);
        }

        // Get FieldMap data
        //we reset the row index
        rowIndex = 0;

        // Row 10: FIELD MAP
        row = fieldMapSheet.createRow(rowIndex++);
        columnIndex = 0;
        labelCell = row.createCell(columnIndex++);
        labelCell.setCellValue(fieldMapLabel);
        labelCell.setCellStyle(labelStyle);

        // Row 11: Space
        row = fieldMapSheet.createRow(rowIndex++);

        Plot[][] plots = userFieldMap.getFieldmap();
        int range = userFieldMap.getNumberOfRangesInBlock();
        int col = userFieldMap.getNumberOfColumnsInBlock();
        int rowsPerPlot = userFieldMap.getNumberOfRowsPerPlot();
        int machineRowCapacity = userFieldMap.getMachineRowCapacity();
        int rows = userFieldMap.getNumberOfRowsInBlock();
        boolean isSerpentine = userFieldMap.getPlantingOrder() == 2;

        for (int j = range - 1; j >= 0; j--) {

            if (j == range - 1) { // TOP TABLE LABELS

                // Row 12: Rows Header
                rowIndex = printRowHeader(fieldMapSheet, userFieldMap.getNumberOfRowsInBlock(), rowIndex,
                        rowsLabel, mainHeaderStyle, mainSubHeaderStyle);

                // Row 13: UP, DOWN Direction
                rowIndex = printDirectionHeader(fieldMapSheet, plots, j, rows, rowIndex, machineRowCapacity,
                        mainHeaderStyle, mainSubHeaderStyle, isSerpentine);

                // Row 14: Column labels
                rowIndex = printColumnHeader(fieldMapSheet, col, rowIndex, columnLabel, rowsPerPlot,
                        mainHeaderStyle, mainSubHeaderStyle);
            }

            // Rows 15 onwards: Ranges and Row Data
            row = fieldMapSheet.createRow(rowIndex);
            row.setHeightInPoints(45);
            columnIndex = 0;
            int rangeValue = j + 1;
            Cell cellRange = row.createCell(columnIndex++);
            cellRange.setCellValue(rangeLabel + " " + rangeValue);
            cellRange.setCellStyle(mainSubHeaderStyle);
            for (int i = 0; i < col; i++) {
                String displayString = plots[i][j].getDisplayString().replace("<br/>", "\n");
                if (plots[i][j].isPlotDeleted()) {
                    displayString = "  X  ";
                }
                Cell dataCell = row.createCell(columnIndex++);
                //dataCell.setCellValue(displayString);
                dataCell.setCellValue(new HSSFRichTextString(displayString));
                dataCell.setCellStyle(wrapStyle);
                //row.createCell(columnIndex).setCellValue("");

                for (int k = 0; k < rowsPerPlot - 1; k++) {
                    row.createCell(columnIndex++).setCellValue("");
                }

                fieldMapSheet.addMergedRegion(new CellRangeAddress(rowIndex, //first row (0-based)
                        rowIndex, //last row  (0-based)
                        columnIndex - rowsPerPlot, //first column (0-based)
                        columnIndex - 1 //last column  (0-based)
                ));
                //columnIndex++;
            }
            rowIndex++;

            if (j == 0) {
                // BOTTOM TABLE LABELS
                rowIndex = printColumnHeader(fieldMapSheet, col, rowIndex, columnLabel, rowsPerPlot,
                        mainHeaderStyle, mainSubHeaderStyle);
                rowIndex = printDirectionHeader(fieldMapSheet, plots, j, rows, rowIndex, machineRowCapacity,
                        mainHeaderStyle, mainSubHeaderStyle, isSerpentine);
                rowIndex = printRowHeader(fieldMapSheet, userFieldMap.getNumberOfRowsInBlock(), rowIndex,
                        rowsLabel, mainHeaderStyle, mainSubHeaderStyle);
            }

        }

        //Write the excel file
        FileOutputStream fileOutputStream = new FileOutputStream(fileName);
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        return fileOutputStream;

    } catch (FileNotFoundException e) {
        LOG.error(e.getMessage(), e);
        throw new FieldbookException("Error writing to file: " + fileName, e);
    } catch (IOException e) {
        LOG.error(e.getMessage(), e);
        throw new FieldbookException("Error writing to file: " + fileName, e);
    }

}