Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook createSheet

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. createSheet

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFWorkbook createSheet.

Prototype

@Override
public HSSFSheet createSheet(String sheetname)

Source Link

Document

Create a new sheet for this Workbook 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 ww w.  ja  v a2s .c om
    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.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 {/* ww w.ja  v a2  s .c  om*/

        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.efficio.fieldbook.web.nursery.service.impl.ExcelExportStudyServiceImpl.java

License:Open Source License

private void writeDescriptionSheet(HSSFWorkbook xlsBook, Workbook workbook) {
    Locale locale = LocaleContextHolder.getLocale();
    HSSFSheet xlsSheet = xlsBook
            .createSheet(messageSource.getMessage("export.study.sheet.description", null, locale));
    int currentRowNum = 0;

    currentRowNum = writeStudyDetails(currentRowNum, xlsBook, xlsSheet, workbook.getStudyDetails());
    xlsSheet.createRow(currentRowNum++);
    currentRowNum = writeConditions(currentRowNum, xlsBook, xlsSheet, workbook.getConditions());
    xlsSheet.createRow(currentRowNum++);
    currentRowNum = writeFactors(currentRowNum, xlsBook, xlsSheet, workbook.getFactors());
    xlsSheet.createRow(currentRowNum++);
    currentRowNum = writeConstants(currentRowNum, xlsBook, xlsSheet, workbook.getConstants());
    xlsSheet.createRow(currentRowNum++);
    currentRowNum = writeVariates(currentRowNum, xlsBook, xlsSheet, workbook.getVariates());

    for (int i = 0; i < 8; i++) {
        xlsSheet.autoSizeColumn(i);/*from   w ww . java 2s.c  o  m*/
    }
}

From source file:com.efficio.fieldbook.web.nursery.service.impl.ExcelExportStudyServiceImpl.java

License:Open Source License

private void writeObservationSheet(HSSFWorkbook xlsBook, Workbook workbook) {
    Locale locale = LocaleContextHolder.getLocale();
    HSSFSheet xlsSheet = xlsBook
            .createSheet(messageSource.getMessage("export.study.sheet.observation", null, locale));
    int currentRowNum = 0;

    writeObservationHeader(currentRowNum++, xlsBook, xlsSheet, workbook.getMeasurementDatasetVariables());
    for (MeasurementRow dataRow : workbook.getObservations()) {
        writeObservationRow(currentRowNum++, xlsSheet, dataRow, xlsBook);
    }/*from  www .java2  s .c om*/
}

From source file:com.eryansky.core.excelTools.JsGridReportBase.java

License:Apache License

/**
 * /* w ww. ja v a  2 s .c  om*/
 * @param wb Excel
 * @param title Sheet??
 * @param styles ?
 * @param creator 
 * @param tableData ?
 * @throws Exception
 */
public HSSFWorkbook writeSheet(HSSFWorkbook wb, String title, HashMap<String, HSSFCellStyle> styles,
        String creator, TableData tableData) throws Exception {

    SimpleDateFormat formater = new SimpleDateFormat("yyyyMMdd HHmm");
    String create_time = formater.format(new Date());

    HSSFSheet sheet = wb.createSheet(title);// Excel
    sheet.setDisplayGridlines(false);// ?

    HSSFRow row = sheet.createRow(0);// 
    HSSFCell cell = row.createCell(0);// 
    int rownum = 0;
    cell.setCellValue(new HSSFRichTextString(title));
    HSSFCellStyle style = styles.get("TITLE");
    if (style != null)
        cell.setCellStyle(style);

    TableHeaderMetaData headerMetaData = tableData.getTableHeader();// HTML
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headerMetaData.getColumnCount() - 1));
    row = sheet.createRow(1);

    cell = row.createCell(0);
    cell.setCellValue(new HSSFRichTextString(":"));
    style = styles.get("SUB_TITLE");
    if (style != null)
        cell.setCellStyle(style);

    cell = row.createCell(1);
    cell.setCellValue(new HSSFRichTextString(creator));
    style = styles.get("SUB_TITLE2");
    if (style != null)
        cell.setCellStyle(style);

    cell = row.createCell(2);
    cell.setCellValue(new HSSFRichTextString(":"));
    style = styles.get("SUB_TITLE");
    if (style != null)
        cell.setCellStyle(style);

    cell = row.createCell(3);
    style = styles.get("SUB_TITLE2");
    cell.setCellValue(new HSSFRichTextString(create_time));
    if (style != null)
        cell.setCellStyle(style);

    rownum = 3;// rownum = 1?

    HSSFCellStyle headerstyle = styles.get("TABLE_HEADER");

    System.out.println(JsonMapper.getInstance().toJson(headerMetaData));
    int colnum = 0;
    for (int i = 0; i < headerMetaData.getOriginColumns().size(); i++) {
        TableColumn tc = headerMetaData.getOriginColumns().get(i);
        if (i != 0) {
            colnum += headerMetaData.getOriginColumns().get(i - 1).getLength();
        }
        generateColumn(sheet, tc, headerMetaData.maxlevel, rownum, colnum, headerstyle);
    }
    rownum += headerMetaData.maxlevel;

    List<TableDataRow> dataRows = tableData.getRows();

    HashMap<Integer, Integer> counter = new HashMap<Integer, Integer>();
    HashMap<Integer, String> word = new HashMap<Integer, String>();
    int index = 0;
    for (TableDataRow dataRow : dataRows) {
        row = sheet.createRow(rownum);

        List<TableDataCell> dataCells = dataRow.getCells();
        int size = headerMetaData.getColumns().size();
        index = -1;
        for (int i = 0; i < size; i++) {
            TableColumn tc = headerMetaData.getColumns().get(i);
            if (!tc.isVisible())
                continue;
            index++;

            String value = dataCells.get(i).getValue();
            if (tc.isGrouped()) {
                String w = word.get(index);
                if (w == null) {
                    word.put(index, value);
                    counter.put(index, 1);
                    createCell(row, tc, dataCells, i, index, styles);
                } else {
                    if (w.equals(value)) {
                        counter.put(index, counter.get(index) + 1);
                    } else {
                        stopGrouping(sheet, word, counter, index, size, rownum, styles.get("STRING"));

                        word.put(index, value);
                        counter.put(index, 1);
                        createCell(row, tc, dataCells, i, index, styles);
                    }
                }
            } else {
                createCell(row, tc, dataCells, i, index, styles);
            }
        }
        rownum++;
    }

    stopGrouping(sheet, word, counter, 0, index, rownum, styles.get("STRING"));
    // ???
    for (int c = 0; c < headerMetaData.getColumns().size(); c++) {
        sheet.autoSizeColumn((short) c, true);
    }
    sheet.setGridsPrinted(true);

    return wb;
}

From source file:com.esd.cs.common.PoiCreateExcel.java

License:Open Source License

/**
 * ?/*  w  w w.j  ava  2  s. c  o  m*/
 * 
 * @param FilePath
 * @param workerList
 * @return
 */
public static boolean createExcel(String FilePath, List<WorkerTemp> workerList) {
    // Excel Workbook,excel
    HSSFWorkbook wb = new HSSFWorkbook();
    // Excelsheet,exceltab
    HSSFSheet sheet = wb.createSheet("sheet1");
    // excel?
    sheet.setColumnWidth(0, 4000);
    sheet.setColumnWidth(1, 3500);

    // Excel?
    HSSFRow headRow = sheet.createRow(0);
    HSSFCell headell = headRow.createCell(0);
    // ???
    headell = headRow.createCell(0);
    headell.setCellValue("??");
    headell = headRow.createCell(1);
    headell.setCellValue("??");
    sheet.setColumnWidth(1, 8000);
    headell = headRow.createCell(2);
    headell.setCellValue("");
    sheet.setColumnWidth(2, 13000);

    for (int i = 1; i <= workerList.size(); i++) {
        WorkerTemp worker = workerList.get(i - 1);
        // Excel?
        HSSFRow row = sheet.createRow(i);
        HSSFCell cell = row.createCell(0);
        // ???
        cell = row.createCell(0);
        cell.setCellValue(worker.getWorkerName());
        cell = row.createCell(1);
        cell.setCellValue(worker.getWorkerHandicapCode());
        cell = row.createCell(2);
        cell.setCellValue(worker.getRemark());
    }
    try {
        FileOutputStream os = new FileOutputStream(FilePath);
        wb.write(os);
        os.close();

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return true;
}

From source file:com.esd.cs.common.PoiCreateExcel.java

License:Open Source License

/**
 * ???//w w w  .ja  va 2  s .  co  m
 * 
 * @param FilePath
 * @param companyList
 * @return
 */
public static boolean createComapnyExcel(String FilePath, List<Company> companyList) {
    // Excel Workbook,excel
    HSSFWorkbook wb = new HSSFWorkbook();
    // Excelsheet,exceltab
    HSSFSheet sheet = wb.createSheet("sheet1");
    // excel?
    sheet.setColumnWidth(0, 4000);
    sheet.setColumnWidth(1, 3500);

    // Excel?
    HSSFRow headRow = sheet.createRow(0);
    HSSFCell headell = headRow.createCell(0);
    // ???
    headell = headRow.createCell(0);
    headell.setCellValue("?");
    headell = headRow.createCell(1);
    headell.setCellValue("?");
    headell = headRow.createCell(2);
    headell.setCellValue("????");
    sheet.setColumnWidth(2, 12000); // 

    headell = headRow.createCell(3);
    headell.setCellValue("");
    headell = headRow.createCell(4);
    headell.setCellValue("?");

    headell = headRow.createCell(5);
    headell.setCellValue("???");

    headell = headRow.createCell(6);
    headell.setCellValue("??");

    headell = headRow.createCell(7);
    headell.setCellValue("");
    headell = headRow.createCell(8);
    headell.setCellValue("???");
    sheet.setColumnWidth(8, 12000);

    for (int i = 1; i <= companyList.size(); i++) {
        Company company = companyList.get(i - 1);
        // Excel?
        HSSFRow row = sheet.createRow(i);
        HSSFCell cell = row.createCell(0);
        // ???
        // ?
        cell = row.createCell(0);
        cell.setCellValue(company.getCompanyCode());
        // ?
        cell = row.createCell(1);
        cell.setCellValue(company.getCompanyTaxCode());
        // ???
        cell = row.createCell(2);
        cell.setCellValue(company.getCompanyName());
        // 
        cell = row.createCell(3);
        cell.setCellValue(company.getCompanyLegal());
        // ?
        cell = row.createCell(4);
        cell.setCellValue(company.getCompanyContactPerson());
        // ???
        cell = row.createCell(5);
        cell.setCellValue(company.getCompanyPhone());
        // ??
        cell = row.createCell(6);
        cell.setCellValue(company.getCompanyMobile());
        // 
        cell = row.createCell(7);
        cell.setCellValue(company.getCompanyZipCode());
        // ???
        cell = row.createCell(8);
        cell.setCellValue(company.getCompanyAddress());
    }
    try {
        FileOutputStream os = new FileOutputStream(FilePath);
        wb.write(os);
        os.flush();
        os.close();
        companyList.clear();
        companyList = null;
        os = null;
        wb = null;
        System.gc();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return true;
}

From source file:com.esd.cs.common.PoiCreateExcel.java

License:Open Source License

/**
 * /*from w w w .j  a  v  a2s  .c o m*/
 * 
 * @param FilePath
 * @param companyList
 * @return
 */
public static boolean createRepeaExcel(String FilePath, List<ReportViewModel> companyList, ReportModel model) {
    // Excel Workbook,excel
    HSSFWorkbook wb = new HSSFWorkbook();
    // Excelsheet,exceltab
    HSSFSheet sheet = wb.createSheet("sheet1");
    // excel?
    sheet.setColumnWidth(0, 4000);
    sheet.setColumnWidth(1, 3500);

    // Excel?
    HSSFRow headRow0 = sheet.createRow(0);
    HSSFCell headCell = headRow0.createCell(0);
    // ??
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 13));// ???
    headCell = headRow0.createCell(0);
    // 
    headCell.setCellValue(model.getTitle());
    // ?
    HSSFCellStyle style = wb.createCellStyle();
    style.setFillBackgroundColor(HSSFColor.GREEN.index);
    style.setAlignment(CellStyle.ALIGN_CENTER);// 
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 
    // 
    HSSFFont font = wb.createFont();
    font.setFontHeightInPoints((short) 12);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 
    style.setFont(font);
    headCell.setCellStyle(style);

    // ? ??
    HSSFRow RowTow = sheet.createRow(1);
    HSSFCell CellTow = headRow0.createCell(1);
    // ??
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 5));// ???
    CellTow = RowTow.createCell(0);
    // 
    CellTow.setCellValue(model.getCreateCompany());

    // ? 
    // ??
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 6, 13));// ???
    CellTow = RowTow.createCell(6);
    HSSFCellStyle style1 = wb.createCellStyle();
    style1.setFillBackgroundColor(HSSFColor.GREEN.index);
    style1.setAlignment(CellStyle.ALIGN_RIGHT);// ?
    CellTow.setCellStyle(style1);
    // 
    CellTow.setCellValue(model.getCreateData());

    // ?
    HSSFRow headRow = sheet.createRow(2);
    HSSFCell headell = headRow.createCell(2);
    // ???
    headell = headRow.createCell(0);
    headell.setCellValue(model.getType());

    headell = headRow.createCell(1);
    headell.setCellValue("??");

    headell = headRow.createCell(2);
    headell.setCellValue("??");
    sheet.setColumnWidth(2, 3000); // 

    headell = headRow.createCell(3);
    headell.setCellValue("???");
    sheet.setColumnWidth(3, 3000); // 

    headell = headRow.createCell(4);
    headell.setCellValue("???");
    sheet.setColumnWidth(4, 4000); // 

    headell = headRow.createCell(5);
    headell.setCellValue("???");
    sheet.setColumnWidth(5, 4000); // 

    headell = headRow.createCell(6);
    headell.setCellValue("???");
    sheet.setColumnWidth(6, 4500); // 

    headell = headRow.createCell(7);
    headell.setCellValue("");
    sheet.setColumnWidth(8, 4000);

    headell = headRow.createCell(8);
    headell.setCellValue("");
    sheet.setColumnWidth(8, 4000);

    headell = headRow.createCell(9);
    headell.setCellValue("");
    sheet.setColumnWidth(9, 4000);

    headell = headRow.createCell(10);
    headell.setCellValue("?");
    sheet.setColumnWidth(10, 4000);

    headell = headRow.createCell(11);
    headell.setCellValue("???");

    headell = headRow.createCell(12);
    headell.setCellValue("?");

    headell = headRow.createCell(13);
    headell.setCellValue("?");

    for (int i = 0; i < companyList.size(); i++) {
        ReportViewModel company = companyList.get(i);
        // Excel?
        HSSFRow row = sheet.createRow(i + 3);
        HSSFCell cell = row.createCell(i + 3);
        // ???
        // ????
        cell = row.createCell(0);
        cell.setCellValue(company.getReportName());
        // ??
        cell = row.createCell(1);
        cell.setCellValue(company.getUnitNum());
        // ??
        cell = row.createCell(2);
        cell.setCellValue(company.getEmpTotal());

        // ???
        cell = row.createCell(3);
        cell.setCellValue(company.getUnAudit());

        // ?, ???
        cell = row.createCell(4);
        cell.setCellValue(company.getUnReAudit());

        // ?, ??
        cell = row.createCell(5);
        cell.setCellValue(company.getAuditOk());

        // ?, ??
        cell = row.createCell(6);
        cell.setCellValue(company.getUnauditOk());

        // 
        cell = row.createCell(7);
        cell.setCellValue(company.getShouldTotal().toString());

        // ?
        cell = row.createCell(8);
        cell.setCellValue(company.getAlreadyTotal().toString());

        // 
        cell = row.createCell(9);
        cell.setCellValue(company.getLessTotal().toString());
        // ?
        cell = row.createCell(10);
        cell.setCellValue(company.getAmountPayable().toString());
        // ???
        cell = row.createCell(11);
        cell.setCellValue(company.getReductionAmount().toString());
        // ?
        cell = row.createCell(12);
        cell.setCellValue(company.getActualAmount().toString());
        // ?
        cell = row.createCell(13);
        cell.setCellValue(company.getAlreadyAmount().toString());
    }

    // ? 
    HSSFRow row = sheet.createRow(companyList.size() + 3);
    HSSFCell cell = row.createCell(companyList.size() + 3);
    // ???
    // ????
    sheet.addMergedRegion(new CellRangeAddress(companyList.size() + 3, companyList.size() + 3, 0, 13));// ???
    cell = row.createCell(0);
    // ?
    HSSFCellStyle styleFoot = wb.createCellStyle();
    styleFoot.setAlignment(CellStyle.ALIGN_RIGHT);// ?
    cell.setCellStyle(styleFoot);
    // 
    cell.setCellValue(model.getCreatePeople());

    try {
        FileOutputStream os = new FileOutputStream(FilePath);
        wb.write(os);
        os.flush();
        os.close();
        companyList.clear();
        companyList = null;
        os = null;
        wb = null;
        System.gc();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return true;
}

From source file:com.esd.ps.excel.PoiCreateExcel.java

License:Open Source License

/**
 * ?/*from   w ww.  ja  v a 2s. c o m*/
 * 
 * @param FilePath
 * @param companyList
 * @return
 */
public static boolean createRegistrationExcel(String FilePath, List<Registration> list) {
    // Excel Workbook,excel
    HSSFWorkbook wb = new HSSFWorkbook();
    // Excelsheet,exceltab
    HSSFSheet sheet = wb.createSheet("sheet1");
    // excel?
    sheet.setColumnWidth(0, 4000);
    sheet.setColumnWidth(1, 3500);

    // Excel?
    HSSFRow headRow = sheet.createRow(0);
    HSSFCell headell = headRow.createCell(0);
    // ???
    headell = headRow.createCell(0);
    headell.setCellValue("??");
    headell = headRow.createCell(1);
    headell.setCellValue("??");
    headell = headRow.createCell(2);
    headell.setCellValue("??");
    // sheet.setColumnWidth(2, 12000); // 

    headell = headRow.createCell(3);
    headell.setCellValue("QQ");
    headell = headRow.createCell(4);
    headell.setCellValue("??");

    headell = headRow.createCell(5);
    headell.setCellValue("??");
    SimpleDateFormat sdf = new SimpleDateFormat(Constants.DATETIME_FORMAT);
    for (int i = 1; i <= list.size(); i++) {
        Registration r = list.get(i - 1);
        // Excel?
        HSSFRow row = sheet.createRow(i);
        HSSFCell cell = row.createCell(0);
        // ???
        // ??
        cell = row.createCell(0);
        cell.setCellValue(r.getName());
        // ??
        cell = row.createCell(1);
        cell.setCellValue(r.getCard());
        // ?
        cell = row.createCell(2);
        cell.setCellValue(r.getPhone());
        // QQ
        cell = row.createCell(3);
        cell.setCellValue(r.getQq());
        // ??
        cell = row.createCell(4);
        cell.setCellValue(r.getAddress());
        // ??
        cell = row.createCell(5);
        cell.setCellValue(sdf.format(r.getCreateTime()));
    }
    try {
        FileOutputStream os = new FileOutputStream(FilePath);
        wb.write(os);
        os.flush();
        os.close();
        list.clear();
        list = null;
        os = null;
        wb = null;
        System.gc();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return true;
}

From source file:com.excel.ExcelView.java

@Override
protected void buildExcelDocument(Map model, HSSFWorkbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {

    ExcelModel em = (ExcelModel) model.get("myModel");

    response.setHeader("Content-Disposition", "attachment; filename=\"" + em.getFileName() + ".xls\"");

    HSSFSheet excelSheet = workbook.createSheet(em.getFileName());

    setExcelHeader(excelSheet, em.getTitles());
    setExcelRows(excelSheet, em.getContents());
}