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

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

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

Introduction

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

Prototype

Row createRow(int rownum);

Source Link

Document

Create a new row within the sheet and return the high level representation

Usage

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  a v  a 2s  . 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);
    }

}

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

License:Open Source License

private int printRowHeader(Sheet fieldMapSheet, int numOfRows, int rowIndex, String rowsLabel,
        CellStyle mainHeader, CellStyle subHeaderStyle) {
    Row row = fieldMapSheet.createRow(rowIndex++);
    int columnIndex = 0;
    Cell cell = row.createCell(columnIndex++);
    cell.setCellValue(rowsLabel);//  ww  w . ja v  a2s  . c o  m
    cell.setCellStyle(mainHeader);
    for (int i = 0; i < numOfRows; i++) {
        Cell tableCell = row.createCell(columnIndex++);
        tableCell.setCellValue(i + 1);
        tableCell.setCellStyle(subHeaderStyle);
    }
    return rowIndex;

}

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

License:Open Source License

private int printColumnHeader(Sheet fieldMapSheet, int numberOfColumns, int rowIndex, String columnLabel,
        int rowsPerPlot, CellStyle mainHeader, CellStyle subHeaderStyle) {
    Row row = fieldMapSheet.createRow(rowIndex);
    int columnIndex = 0;
    Cell mainCell = row.createCell(columnIndex++);
    mainCell.setCellValue("");
    mainCell.setCellStyle(mainHeader);/*  www.  j a v a2 s  .c o  m*/
    for (int i = 0; i < numberOfColumns; i++) {
        int columnValue = i + 1;
        Cell cell = row.createCell(columnIndex++);
        cell.setCellValue(columnLabel + " " + columnValue);
        cell.setCellStyle(subHeaderStyle);
        for (int j = 0; j < rowsPerPlot - 1; j++) {
            Cell cell1 = row.createCell(columnIndex++);
            cell1.setCellValue("");
            cell.setCellStyle(subHeaderStyle);
        }

        fieldMapSheet.addMergedRegion(
                new CellRangeAddress(rowIndex, rowIndex, columnIndex - rowsPerPlot, columnIndex - 1));
        //columnIndex++;
    }
    rowIndex++;
    return rowIndex;

}

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

License:Open Source License

private int printDirectionHeader(Sheet fieldMapSheet, Plot[][] plots, int range, int numberOfRows, int rowIndex,
        int machineRowCapacity, CellStyle mainHeader, CellStyle subHeaderStyle, boolean isSerpentine) {

    Row row = fieldMapSheet.createRow(rowIndex);
    int columnIndex = 0;
    Cell cell1 = row.createCell(columnIndex++);
    cell1.setCellValue("");
    cell1.setCellStyle(mainHeader);/*from  w  w  w.j  a  v  a 2  s.  c om*/

    int numberOfDirections = numberOfRows / machineRowCapacity;
    int remainingRows = numberOfRows % machineRowCapacity;
    if (remainingRows > 0) {
        numberOfDirections++;
    }

    for (int i = 0; i < numberOfDirections; i++) {
        int startCol = machineRowCapacity * i + 1;
        if (i % 2 == 1) {
            Cell cell = row.createCell(startCol);
            cell.setCellValue(DOWN);
            cell.setCellStyle(subHeaderStyle);
        } else {
            Cell cell = row.createCell(startCol);
            cell.setCellValue(UP);
            cell.setCellStyle(subHeaderStyle);
        }
        /*
        if (isSerpentine) {
        if (i % 2 == 1) {
            Cell cell = row.createCell(startCol);
            cell.setCellValue(DOWN);
            cell.setCellStyle(subHeaderStyle);
        }
        else {
            Cell cell = row.createCell(startCol);
            cell.setCellValue(UP);
            cell.setCellStyle(subHeaderStyle);
        }
        }
        else {
        Cell cell = row.createCell(startCol);
        cell.setCellValue(UP);
        cell.setCellStyle(subHeaderStyle);
        }
        */
        if (i == numberOfDirections - 1 && remainingRows > 0) { //last item
            fieldMapSheet.addMergedRegion(
                    new CellRangeAddress(rowIndex, rowIndex, startCol, machineRowCapacity * i + remainingRows));
        } else {
            fieldMapSheet.addMergedRegion(
                    new CellRangeAddress(rowIndex, rowIndex, startCol, machineRowCapacity * (i + 1)));
        }
    }
    rowIndex++;
    return rowIndex;
}

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

License:Open Source License

@Override
public String generateXlSLabels(List<StudyTrialInstanceInfo> trialInstances,
        UserLabelPrinting userLabelPrinting, ByteArrayOutputStream baos) throws MiddlewareQueryException {
    int pageSizeId = Integer.parseInt(userLabelPrinting.getSizeOfLabelSheet());
    int numberOfLabelPerRow = Integer.parseInt(userLabelPrinting.getNumberOfLabelPerRow());
    int numberofRowsPerPageOfLabel = Integer.parseInt(userLabelPrinting.getNumberOfRowsPerPageOfLabel());
    int totalPerPage = numberOfLabelPerRow * numberofRowsPerPageOfLabel;
    String leftSelectedFields = userLabelPrinting.getLeftSelectedLabelFields();
    String rightSelectedFields = userLabelPrinting.getRightSelectedLabelFields();
    String barcodeNeeded = userLabelPrinting.getBarcodeNeeded();

    String firstBarcodeField = userLabelPrinting.getFirstBarcodeField();
    String secondBarcodeField = userLabelPrinting.getSecondBarcodeField();
    String thirdBarcodeField = userLabelPrinting.getThirdBarcodeField();

    String currentDate = DateUtil.getCurrentDate();
    //String fileName = currentDate + ".xls";
    String fileName = userLabelPrinting.getFilenameDLLocation();
    try {/*from   w w  w .j ava 2 s.com*/

        HSSFWorkbook workbook = new HSSFWorkbook();
        String sheetName = cleanSheetName(userLabelPrinting.getName());
        if (sheetName == null)
            sheetName = "Labels";
        Sheet labelPrintingSheet = workbook.createSheet(sheetName);

        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 
        Row row = labelPrintingSheet.createRow(rowIndex++);

        //we add all the selected fields header
        StringTokenizer token = new StringTokenizer(leftSelectedFields, ",");
        while (token.hasMoreTokens()) {
            String headerId = token.nextToken();
            String headerName = getHeader(headerId);
            Cell summaryCell = row.createCell(columnIndex++);
            summaryCell.setCellValue(headerName);
            summaryCell.setCellStyle(labelStyle);
        }
        token = new StringTokenizer(rightSelectedFields, ",");
        while (token.hasMoreTokens()) {
            String headerId = token.nextToken();
            String headerName = getHeader(headerId);
            Cell summaryCell = row.createCell(columnIndex++);
            summaryCell.setCellValue(headerName);
            summaryCell.setCellStyle(labelStyle);
        }

        //we populate the info now
        int i = 0;
        for (StudyTrialInstanceInfo trialInstance : trialInstances) {
            FieldMapTrialInstanceInfo fieldMapTrialInstanceInfo = trialInstance.getTrialInstance();

            Map<String, String> moreFieldInfo = new HashMap<String, String>();
            moreFieldInfo.put("locationName", fieldMapTrialInstanceInfo.getLocationName());
            moreFieldInfo.put("blockName", fieldMapTrialInstanceInfo.getBlockName());
            moreFieldInfo.put("selectedName", trialInstance.getFieldbookName());
            moreFieldInfo.put("trialInstanceNumber", fieldMapTrialInstanceInfo.getTrialInstanceNo());

            for (FieldMapLabel fieldMapLabel : fieldMapTrialInstanceInfo.getFieldMapLabels()) {
                row = labelPrintingSheet.createRow(rowIndex++);
                columnIndex = 0;
                i++;

                token = new StringTokenizer(leftSelectedFields, ",");
                while (token.hasMoreTokens()) {
                    String headerId = token.nextToken();
                    String leftText = getSpecificInfo(moreFieldInfo, fieldMapLabel, headerId);
                    Cell summaryCell = row.createCell(columnIndex++);
                    summaryCell.setCellValue(leftText);
                    //summaryCell.setCellStyle(labelStyle);
                }
                token = new StringTokenizer(rightSelectedFields, ",");
                while (token.hasMoreTokens()) {
                    String headerId = token.nextToken();
                    String rightText = getSpecificInfo(moreFieldInfo, fieldMapLabel, headerId);
                    Cell summaryCell = row.createCell(columnIndex++);
                    summaryCell.setCellValue(rightText);
                    //summaryCell.setCellStyle(labelStyle);
                }

            }
        }

        for (int columnPosition = 0; columnPosition < columnIndex; columnPosition++) {
            labelPrintingSheet.autoSizeColumn((short) (columnPosition));
        }

        //Write the excel file

        FileOutputStream fileOutputStream = new FileOutputStream(fileName);
        //workbook.write(baos);
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        //return fileOutputStream;

    } catch (Exception e) {
        LOG.error(e.getMessage(), e);
    }
    return fileName;
}

From source file:com.emi.loan.test.ExportToExcel.java

public static void main(String[] args) {
    try {/*from   ww w . ja  v  a 2 s .  c o  m*/

        //Populate DefaultTableModel data
        DefaultTableModel dtm = new DefaultTableModel();
        Vector<String> cols = new Vector<String>();
        dtm.addColumn("Col 1");
        dtm.addColumn("Col 2");
        dtm.addColumn("Col 3");

        Vector<String> dtmrow = null;
        for (int i = 1; i <= 10; i++) {
            dtmrow = new Vector<String>();
            for (int j = 1; j <= 3; j++) {
                dtmrow.add("Cell " + j + "." + i);
            }
            dtm.addRow(dtmrow);
        }

        //Exporting to Excel           
        Workbook wb = new HSSFWorkbook();
        CreationHelper createhelper = wb.getCreationHelper();
        Sheet sheet = wb.createSheet("new sheet");
        Row row = null;
        Cell cell = null;
        for (int i = 0; i < dtm.getRowCount(); i++) {
            row = sheet.createRow(i);
            for (int j = 0; j < dtm.getColumnCount(); j++) {

                cell = row.createCell(j);
                cell.setCellValue((String) dtm.getValueAt(i, j));
            }
        }

        FileOutputStream out = new FileOutputStream(new File("C:\\26276\\workbook.xls"));
        wb.write(out);
        out.close();
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ExportToExcel.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(ExportToExcel.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:com.emi.loan.util.Utilities.java

public static void exportTOExcel(DefaultTableModel dtm, Map<String, String> ln_info) {
    FileOutputStream out = null;/*ww w.j  a  v a  2  s . c  om*/
    try {
        Workbook wb = new HSSFWorkbook();
        //            CreationHelper createhelper = wb.getCreationHelper();
        Sheet sheet = wb.createSheet("EMI TABLE");
        Row row;
        Cell cell;
        File file = chooseFile();
        out = new FileOutputStream(file);

        HSSFFont headerFont = (HSSFFont) wb.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setFontName("CENTURY GOTHIC");
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerFont.setColor(HSSFColor.WHITE.index);

        HSSFFont infoFont = (HSSFFont) wb.createFont();
        infoFont.setFontHeightInPoints((short) 14);
        infoFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        //            HSSFFont font = (HSSFFont) wb.createFont();
        //            font.setFontHeightInPoints((short) 10);
        //            font.setFontName("CENTURY GOTHIC");
        //            font.setColor(HSSFColor.BLACK.index);
        CellStyle defaultStyle = wb.createCellStyle();
        defaultStyle.setFillForegroundColor(HSSFColor.AQUA.index);
        defaultStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        defaultStyle.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY);
        defaultStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_JUSTIFY);
        defaultStyle.setFont(headerFont);

        CellStyle borderStyle = wb.createCellStyle();
        borderStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        borderStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        borderStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        borderStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        borderStyle.setFont(infoFont);

        row = sheet.createRow(1);
        cell = row.createCell(0);
        cell.setCellStyle(defaultStyle);
        cell.setCellValue("Loan Amount(Rs.)");

        cell = row.createCell(1);
        cell.setCellStyle(borderStyle);
        cell.setCellValue(Double.parseDouble(ln_info.get("Loan Amount")));

        row = sheet.createRow(3);
        cell = row.createCell(0);
        cell.setCellStyle(defaultStyle);
        cell.setCellValue("Interest %");

        cell = row.createCell(1);
        cell.setCellStyle(borderStyle);
        cell.setCellValue(Double.parseDouble(ln_info.get("Interest")));

        row = sheet.createRow(5);
        cell = row.createCell(0);
        cell.setCellStyle(defaultStyle);
        cell.setCellValue("Period (months)");

        cell = row.createCell(1);
        cell.setCellStyle(borderStyle);
        cell.setCellValue(Integer.parseInt(ln_info.get("Period")));

        for (int i = 0; i <= dtm.getRowCount(); i++) {

            row = sheet.createRow(i + 8);
            for (int j = 0; j < dtm.getColumnCount(); j++) {
                cell = row.createCell(j);

                if (i == 0) { // writing the column headers 

                    cell.setCellStyle(defaultStyle);
                    cell.setCellValue(dtm.getColumnName(j));

                } else if (j == 0 || j == 5) {
                    cell.setCellValue(Integer.parseInt(dtm.getValueAt(i - 1, j).toString()));
                } else {
                    cell.setCellValue(Double.parseDouble(dtm.getValueAt(i - 1, j).toString()));
                }

            }
        }

        row = sheet.createRow(dtm.getRowCount() + 12);
        cell = row.createCell(0);
        cell.setCellValue("-- END OF REPORT --");

        for (int j = 0; j < dtm.getColumnCount(); j++) {
            sheet.autoSizeColumn(j, true);
        }
        wb.write(out);
    } catch (FileNotFoundException ex) {
        System.out.println("File not Found");
        Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        System.out.println("IOException");
        Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            out.close();
        } catch (IOException ex) {
            Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

From source file:com.esri.geoevent.test.performance.report.XLSXReportWriter.java

License:Apache License

@Override
public void writeReport(String reportFile, List<String> testNames, List<String> columnNames,
        Map<String, List<FixtureStatistic>> stats) throws IOException {
    //create the parent directories - if needed
    createParentDirectoriesIfNeeded(reportFile);

    // rollover the file - keep backups
    rollOver(reportFile);//from w ww.ja  va  2  s.c o m

    Workbook workbook = null;
    try {
        workbook = new XSSFWorkbook();

        // header style
        CellStyle headerStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerStyle.setFont(font);

        // copy the column names - add the test name as the first column
        List<String> columnNamesCopy = new ArrayList<String>();
        columnNamesCopy.add("Test Name");
        columnNamesCopy.addAll(columnNames);

        // create the sheet
        Sheet sheet = workbook.createSheet("Summary");

        // create the header row
        int rowIndex = 0;
        Row headers = sheet.createRow(rowIndex);
        headers.setRowStyle(headerStyle);
        int cellIndex = 0;
        for (String columnName : columnNamesCopy) {
            Cell cell = headers.createCell(cellIndex);
            cell.setCellValue(columnName);
            cell.setCellStyle(headerStyle);
            cellIndex++;
        }
        for (String testName : testNames) {
            // get each test's fixture stats and sort them accordingly
            List<FixtureStatistic> fixtureStats = stats.get(testName);
            if (fixtureStats == null || fixtureStats.size() == 0) {
                continue;
            }
            Collections.sort(fixtureStats);
            rowIndex++;

            for (FixtureStatistic fixtureStat : fixtureStats) {
                Row data = sheet.createRow(rowIndex);
                cellIndex = 0;

                //write out the test name first
                Cell cell = data.createCell(cellIndex);
                cell.setCellValue(testName);
                cellIndex++;

                for (String columnName : columnNames) {
                    cell = data.createCell(cellIndex);
                    Object rawValue = fixtureStat.getStat(columnName);
                    if (rawValue == null) {
                        cell.setCellValue("");
                    } else {
                        if (rawValue instanceof Integer) {
                            cell.setCellValue((Integer) rawValue);
                        } else if (rawValue instanceof Double) {
                            cell.setCellValue((Double) rawValue);
                        } else if (rawValue instanceof Long) {
                            cell.setCellValue((Long) rawValue);
                        } else if (rawValue instanceof Boolean) {
                            cell.setCellValue((Boolean) rawValue);
                        } else {
                            cell.setCellValue(rawValue.toString());
                        }
                    }
                    // adjust column width to fit the content
                    sheet.autoSizeColumn(cellIndex);
                    cellIndex++;
                }
                //rowIndex++;
            }
        }

        //write out the total time
        if (getTotalTestingTime() != -1) {
            rowIndex = rowIndex + 2;
            Row data = sheet.createRow(rowIndex);
            Cell cell = data.createCell(0);
            cell.setCellValue("Total Testing Time:");
            cell.setCellStyle(headerStyle);
            cell = data.createCell(1);
            cell.setCellValue(formatTime(getTotalTestingTime()));
        }
    } finally {
        // write out the file
        FileOutputStream out = null;
        try {
            String fullPath = FilenameUtils.getFullPathNoEndSeparator(reportFile);
            // create all non exists folders else you will hit FileNotFoundException for report file path
            new File(fullPath).mkdirs();

            out = new FileOutputStream(reportFile);
            if (workbook != null) {
                workbook.write(out);
            }
        } finally {
            IOUtils.closeQuietly(out);
        }
    }
}

From source file:com.eurodyn.qlack2.fuse.lexicon.impl.LanguageServiceImpl.java

License:EUPL

@Override
@Transactional(TxType.REQUIRED)/* w ww  . ja  v  a  2  s. c  o  m*/
public byte[] downloadLanguage(String languageID) {
    byte[] retVal = null;

    // Check that the language exists and get its translations
    Language language = Language.find(languageID, em);

    // Create an Excel workbook. The workbook will contain a sheet for each
    // group.
    Workbook wb = new HSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();

    // Iterate over all existing groups and create a sheet for each one.
    // Creating a new list below and not using the one retrieved from
    // Group.getAllGroups since result lists are read only and
    // we need to add the empty group below to the list.
    List<Group> groups = new ArrayList<>(Group.getAllGroups(em));
    // Add an dummy entry to the list to also check for translations without
    // a group.
    Group emptyGroup = new Group();
    emptyGroup.setId(null);
    emptyGroup.setTitle("<No group>");
    groups.add(0, emptyGroup);
    for (Group group : groups) {
        Map<String, String> translations;
        translations = keyService.getTranslationsForGroupAndLocale(group.getId(), language.getLocale());
        if (!translations.isEmpty()) {
            Sheet sheet = wb.createSheet(group.getTitle());

            // Add the header.
            Row headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue(createHelper.createRichTextString("Key"));
            headerRow.createCell(1).setCellValue(createHelper.createRichTextString("Translation"));

            // Add the data.
            int rowCounter = 1;
            for (String key : translations.keySet()) {
                Row row = sheet.createRow(rowCounter++);
                row.createCell(0).setCellValue(createHelper.createRichTextString(key));
                row.createCell(1).setCellValue(createHelper.createRichTextString(translations.get(key)));
            }
        }
    }

    // Create the byte[] holding the Excel data.
    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    try {
        wb.write(bos);
        retVal = bos.toByteArray();
    } catch (IOException ex) {
        // Convert to a runtime exception in order to roll back transaction
        LOGGER.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
        throw new QLanguageProcessingException("Error creating Excel file for language " + languageID);
    }

    return retVal;
}

From source file:com.evidon.areweprivateyet.Aggregator.java

License:Open Source License

private void createContent(Workbook wb, Sheet s, String map) {
    Map<String, String> out = new HashMap<String, String>();

    int rownum = 2;
    int cellnum = 0;

    // create a merged list of domains.
    domains.clear();/* w w  w.j av  a 2s  . c  om*/
    for (String database : results.keySet()) {
        if (database.equals("baseline")) {
            Analyzer ra = results.get(database);
            Map<String, Integer> mapToUse = this.getMap(map, ra);

            for (String domain : mapToUse.keySet()) {
                if ((!domains.contains(domain)) && !exclusions.contains(domain)) {
                    domains.add(domain);
                    out.put(domain, "");
                }
            }
        }
    }

    CellStyle numberStyle = wb.createCellStyle();
    numberStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("number"));
    s.setColumnWidth(0, 5000);

    for (String domain : domains) {
        cellnum = 0;

        Row r = s.createRow(rownum);
        Cell c = r.createCell(cellnum);
        c.setCellValue(domain);
        cellnum++;

        for (String database : results.keySet()) {
            Analyzer ra = results.get(database);

            Map<String, Integer> mapToUse = this.getMap(map, ra);

            c = r.createCell(cellnum);
            try {
                if (mapToUse.containsKey(domain)) {
                    c.setCellValue(mapToUse.get(domain));
                } else {
                    c.setCellValue(0);
                }
            } catch (Exception e) {
                c.setCellValue(0);
            }

            c.setCellStyle(numberStyle);

            cellnum++;
        }
        rownum++;
    }

    // Totals.
    rownum++;
    cellnum = 1;
    Row r = s.createRow(rownum);

    Cell c = r.createCell(0);
    c.setCellValue("Totals:");

    for (int i = 0; i < results.keySet().size(); i++) {
        c = r.createCell(cellnum);
        c.setCellType(Cell.CELL_TYPE_FORMULA);
        c.setCellFormula("SUM(" + getCellLetter(i) + "3:" + getCellLetter(i) + (domains.size() + 2) + ")");

        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        evaluator.evaluateFormulaCell(c);

        if (!totals.containsKey(s.getRow(1).getCell(i + 1).getStringCellValue())) {
            Map<String, String> contents = new LinkedHashMap<String, String>();
            contents.put(s.getSheetName(), c.getNumericCellValue() + "");

            totals.put(s.getRow(1).getCell(i + 1).getStringCellValue(), contents);
        } else {
            Map<String, String> contents = totals.get(s.getRow(1).getCell(i + 1).getStringCellValue());
            contents.put(s.getSheetName(), c.getNumericCellValue() + "");

            totals.put(s.getRow(1).getCell(i + 1).getStringCellValue(), contents);
        }

        cellnum++;
    }

    // Delta/Reduction
    rownum++;
    cellnum = 1;
    r = s.createRow(rownum);

    c = r.createCell(0);
    c.setCellValue("Tracking Decrease:");

    for (int i = 0; i < results.keySet().size(); i++) {
        c = r.createCell(cellnum);
        c.setCellType(Cell.CELL_TYPE_FORMULA);
        c.setCellFormula("ROUND((100-(" + getCellLetter(i) + (rownum) + "*100/B" + (rownum) + ")),0)");

        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        evaluator.evaluateFormulaCell(c);

        if (!decrease.containsKey(s.getRow(1).getCell(i + 1).getStringCellValue())) {
            Map<String, String> contents = new LinkedHashMap<String, String>();
            contents.put(s.getSheetName(), c.getNumericCellValue() + "");

            decrease.put(s.getRow(1).getCell(i + 1).getStringCellValue(), contents);
        } else {
            Map<String, String> contents = decrease.get(s.getRow(1).getCell(i + 1).getStringCellValue());
            contents.put(s.getSheetName(), c.getNumericCellValue() + "");

            decrease.put(s.getRow(1).getCell(i + 1).getStringCellValue(), contents);
        }

        cellnum++;
    }
}