Example usage for org.apache.poi.ss.usermodel CellStyle setFont

List of usage examples for org.apache.poi.ss.usermodel CellStyle setFont

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. CellStyle
  6. setFont

Introduction

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

Prototype

void setFont(Font font);

Source Link

Document

set the font for this style

Usage

From source file:com.diversityarrays.kdxplore.exportdata.CellStyleProvider.java

License:Open Source License

public CellStyle getCellStyle(BmsCellColor bmsCellColor) {

    CellStyle result = styleByBmsCellColor.get(bmsCellColor);

    if (result == null) {

        result = workbook.createCellStyle();

        result.setFillPattern(CellStyle.SOLID_FOREGROUND);
        result.setFillForegroundColor(bmsCellColor.indexedColor.getIndex());

        Font font = workbook.createFont();
        font.setColor(IndexedColors.WHITE.getIndex());
        result.setFont(font);

        styleByBmsCellColor.put(bmsCellColor, result);
    }// ww w.  ja  v a2  s .c om

    return result;
}

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

License:Open Source License

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

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

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

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

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

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

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

    for (Integer key : keyset) {

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

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

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

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

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

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

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

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

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

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

}

From source file:com.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   www  .ja  v a  2  s  . co  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.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  ww .j  a  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 CellStyle getHeaderStyle(HSSFWorkbook xlsBook, int c1, int c2, int c3) {
    HSSFPalette palette = xlsBook.getCustomPalette();
    HSSFColor color = palette.findSimilarColor(c1, c2, c3);
    short colorIndex = color.getIndex();

    HSSFFont whiteFont = xlsBook.createFont();
    whiteFont.setColor(new HSSFColor.WHITE().getIndex());

    CellStyle cellStyle = xlsBook.createCellStyle();
    cellStyle.setFillForegroundColor(colorIndex);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    cellStyle.setFont(whiteFont);

    return cellStyle;
}

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

public static void exportTOExcel(DefaultTableModel dtm, Map<String, String> ln_info) {
    FileOutputStream out = null;// w w w. j a va  2s  .  c  o  m
    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.endro.belajar.controller.InvoiceProdukController.java

private void clickedbuttonExportDialog() {
    dialogExport.getButtonExport().addActionListener(new ActionListener() {
        @Override/*  w ww.j a  v  a2  s .co m*/
        public void actionPerformed(ActionEvent e) {
            try {
                LocalDate tanggalAwal = dialogExport.getTanggalAwalChooser().getDate().toInstant()
                        .atZone(ZoneId.systemDefault()).toLocalDate();
                LocalDate tanggalAkhir = dialogExport.getTanggalAkhirChooser().getDate().toInstant()
                        .atZone(ZoneId.systemDefault()).toLocalDate();

                List<InvoiceOrder> daftar = invoiceDao.findAllByTanggal(tanggalAwal, tanggalAkhir);
                processConvertExcel(daftar);
            } catch (SQLException | IOException ex) {
                Logger.getLogger(InvoiceProdukController.class.getName()).log(Level.SEVERE, null, ex);
            } catch (NullPointerException ex) {
                JOptionPane.showMessageDialog(dialogExport, "Form tanggal diisi dengan lengkap!");
            } finally {
                dialogExport.dispose();
                dialogExport = null;
            }
        }

        private void processConvertExcel(List<InvoiceOrder> daftarInvoice)
                throws FileNotFoundException, IOException {
            Integer returnVal = dialogExport.getChooserSaveFile().showOpenDialog(dialogExport);

            if (returnVal == dialogExport.getChooserSaveFile().APPROVE_OPTION) {
                XSSFWorkbook workbook = new XSSFWorkbook();
                XSSFSheet sheet = workbook.createSheet("Just Example");

                List<InvoiceOrder> list = daftarInvoice;

                Integer rowTable = 0;
                Integer cellTable = 0;
                CellStyle cellStyleTanggal = workbook.createCellStyle();
                CellStyle cellStyleHeader = workbook.createCellStyle();
                CellStyle cellStyleDouble = workbook.createCellStyle();

                CreationHelper createHelper = workbook.getCreationHelper();
                XSSFFont font = workbook.createFont();

                cellStyleTanggal.setDataFormat(createHelper.createDataFormat().getFormat("dd/mm/yyyy"));
                cellStyleDouble.setDataFormat(
                        createHelper.createDataFormat().getFormat("[$Rp-421]#,##0.0000;-[$Rp-421]#,##0.0000"));
                font.setBold(true);
                cellStyleHeader.setFont(font);
                cellStyleHeader.setWrapText(true);
                //cellStyleHeader.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
                cellStyleHeader.setFillPattern(FillPatternType.DIAMONDS);
                for (InvoiceOrder order : list) {
                    Row row = sheet.createRow(rowTable);

                    if (rowTable == 0) {
                        sheet.setColumnWidth(0, 2000);
                        Cell cellHeader = row.createCell(0);
                        cellHeader.setCellValue("ID");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(1, 5000);
                        cellHeader = row.createCell(1);
                        cellHeader.setCellValue("Nomor Transaksi");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(2, 4000);
                        cellHeader = row.createCell(2);
                        cellHeader.setCellValue("Tanggal");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(3, 6000 * 3);
                        cellHeader = row.createCell(3);
                        cellHeader.setCellValue("Informasi Posting");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(4, 4850);
                        cellHeader = row.createCell(4);
                        cellHeader.setCellValue("Total Sebelum Diskon");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(5, 5000);
                        cellHeader = row.createCell(5);
                        cellHeader.setCellValue("Diskon");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(6, 4500);
                        cellHeader = row.createCell(6);
                        cellHeader.setCellValue("Total Setelah Diskon");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(7, 5000 * 2);
                        cellHeader = row.createCell(7);
                        cellHeader.setCellValue("Alamat Pengiriman");
                        cellHeader.setCellStyle(cellStyleHeader);
                    } else {
                        row.createCell(0).setCellValue((Integer) order.getPk());
                        row.createCell(1).setCellValue((String) order.getNomortransaksi());

                        Cell cellTanggal = row.createCell(2);
                        cellTanggal.setCellValue((Date) order.getTanggal());
                        cellTanggal.setCellStyle(cellStyleTanggal);

                        row.createCell(3).setCellValue((String) order.getInformasiposting());

                        Cell cellDouble = row.createCell(4);
                        cellDouble.setCellValue(order.getTotalbelumdiskon());
                        cellDouble.setCellStyle(cellStyleDouble);

                        cellDouble = row.createCell(5);
                        cellDouble.setCellValue(order.getDiskonfaktur());
                        cellDouble.setCellStyle(cellStyleDouble);

                        cellDouble = row.createCell(6);
                        cellDouble.setCellValue(order.getTotalsetelahdiskon());
                        cellDouble.setCellStyle(cellStyleDouble);

                        row.createCell(7).setCellValue((String) order.getAlamatpengiriman() == null ? "Null"
                                : order.getAlamatpengiriman());
                    }
                    rowTable++;
                }

                File file = dialogExport.getChooserSaveFile().getSelectedFile();

                FileOutputStream outputStream = new FileOutputStream(file + File.separator + "Penjualan.xlsx");
                workbook.write(outputStream);

                int pesan = JOptionPane.showConfirmDialog(dialogExport,
                        "Telah tersimpan di " + file + File.separator
                                + "Penjualan.xlsx \n Apakah anda ingin membuka file tersebut?",
                        "Notification", JOptionPane.OK_CANCEL_OPTION);
                if (pesan == JOptionPane.YES_OPTION) {
                    if ("Linux".equals(System.getProperty("os.name"))) {
                        String runPenjualan = "xdg-open " + file + File.separator + "Penjualan.xlsx";
                        Runtime.getRuntime().exec(runPenjualan);
                    } else if ("Windows".equals(System.getProperty("os.name"))) {
                        String runPenjualan = "excel.exe /r" + file + File.separator + "Penjualan.xlsx";
                        Runtime.getRuntime().exec(runPenjualan);
                    }
                }
            } else {
                dialogExport.getChooserSaveFile().cancelSelection();
            }
        }
    });
}

From source file:com.endro.belajar.controller.MainController.java

private void clickedExport() {
    exportPenjualan.getButtonExport().addActionListener(new ActionListener() {
        @Override//from   ww w  .  j  a v a  2 s . c o m
        public void actionPerformed(ActionEvent e) {
            try {
                LocalDate tanggalAwal = exportPenjualan.getTanggalAwalChooser().getDate().toInstant()
                        .atZone(ZoneId.systemDefault()).toLocalDate();
                LocalDate tanggalAkhir = exportPenjualan.getTanggalAkhirChooser().getDate().toInstant()
                        .atZone(ZoneId.systemDefault()).toLocalDate();

                List<InvoiceOrder> daftar = invoiceDao.findAllByTanggal(tanggalAwal, tanggalAkhir);
                processConvertExcel(daftar);
            } catch (SQLException ex) {
                Logger.getLogger(MainController.class.getName()).log(Level.SEVERE, null, ex);
            } catch (NullPointerException ex) {
                JOptionPane.showMessageDialog(exportPenjualan, "Form tanggal diisi dengan lengkap!");
            } catch (IOException ex) {
                Logger.getLogger(MainController.class.getName()).log(Level.SEVERE, null, ex);
            } finally {
                exportPenjualan.dispose();
                exportPenjualan = null;
            }
        }

        private void processConvertExcel(List<InvoiceOrder> daftarInvoice)
                throws FileNotFoundException, IOException {
            Integer returnVal = exportPenjualan.getChooserSaveFile().showOpenDialog(exportPenjualan);

            if (returnVal == exportPenjualan.getChooserSaveFile().APPROVE_OPTION) {
                XSSFWorkbook workbook = new XSSFWorkbook();
                XSSFSheet sheet = workbook.createSheet("Just Example");

                List<InvoiceOrder> list = daftarInvoice;

                Integer rowTable = 0;
                Integer cellTable = 0;
                CellStyle cellStyleTanggal = workbook.createCellStyle();
                CellStyle cellStyleHeader = workbook.createCellStyle();
                CellStyle cellStyleDouble = workbook.createCellStyle();

                CreationHelper createHelper = workbook.getCreationHelper();
                XSSFFont font = workbook.createFont();

                cellStyleTanggal.setDataFormat(createHelper.createDataFormat().getFormat("dd/mm/yyyy"));
                cellStyleDouble.setDataFormat(
                        createHelper.createDataFormat().getFormat("[$Rp-421]#,##0.0000;-[$Rp-421]#,##0.0000"));
                font.setBold(true);
                cellStyleHeader.setFont(font);
                cellStyleHeader.setWrapText(true);
                //cellStyleHeader.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
                cellStyleHeader.setFillPattern(FillPatternType.DIAMONDS);
                for (InvoiceOrder order : list) {
                    Row row = sheet.createRow(rowTable);

                    if (rowTable == 0) {
                        sheet.setColumnWidth(0, 2000);
                        Cell cellHeader = row.createCell(0);
                        cellHeader.setCellValue("ID");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(1, 5000);
                        cellHeader = row.createCell(1);
                        cellHeader.setCellValue("Nomor Transaksi");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(2, 4000);
                        cellHeader = row.createCell(2);
                        cellHeader.setCellValue("Tanggal");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(3, 6000 * 3);
                        cellHeader = row.createCell(3);
                        cellHeader.setCellValue("Informasi Posting");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(4, 4850);
                        cellHeader = row.createCell(4);
                        cellHeader.setCellValue("Total Sebelum Diskon");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(5, 5000);
                        cellHeader = row.createCell(5);
                        cellHeader.setCellValue("Diskon");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(6, 4500);
                        cellHeader = row.createCell(6);
                        cellHeader.setCellValue("Total Setelah Diskon");
                        cellHeader.setCellStyle(cellStyleHeader);

                        sheet.setColumnWidth(7, 5000 * 2);
                        cellHeader = row.createCell(7);
                        cellHeader.setCellValue("Alamat Pengiriman");
                        cellHeader.setCellStyle(cellStyleHeader);
                    } else {
                        row.createCell(0).setCellValue((Integer) order.getPk());
                        row.createCell(1).setCellValue((String) order.getNomortransaksi());

                        Cell cellTanggal = row.createCell(2);
                        cellTanggal.setCellValue((Date) order.getTanggal());
                        cellTanggal.setCellStyle(cellStyleTanggal);

                        row.createCell(3).setCellValue((String) order.getInformasiposting());

                        Cell cellDouble = row.createCell(4);
                        cellDouble.setCellValue(order.getTotalbelumdiskon());
                        cellDouble.setCellStyle(cellStyleDouble);

                        cellDouble = row.createCell(5);
                        cellDouble.setCellValue(order.getDiskonfaktur());
                        cellDouble.setCellStyle(cellStyleDouble);

                        cellDouble = row.createCell(6);
                        cellDouble.setCellValue(order.getTotalsetelahdiskon());
                        cellDouble.setCellStyle(cellStyleDouble);

                        row.createCell(7).setCellValue((String) order.getAlamatpengiriman() == null ? "Null"
                                : order.getAlamatpengiriman());
                    }
                    rowTable++;
                }

                File file = exportPenjualan.getChooserSaveFile().getSelectedFile();

                FileOutputStream outputStream = new FileOutputStream(file + File.separator + "Penjualan.xlsx");
                workbook.write(outputStream);

                int pesan = JOptionPane.showConfirmDialog(exportPenjualan,
                        "Telah tersimpan di " + file + File.separator
                                + "Penjualan.xlsx \n Apakah anda ingin membuka file tersebut?",
                        "Notification", JOptionPane.OK_CANCEL_OPTION);
                if (pesan == JOptionPane.YES_OPTION) {
                    if ("Linux".equals(System.getProperty("os.name"))) {
                        String runPenjualan = "xdg-open " + file + File.separator + "Penjualan.xlsx";
                        Runtime.getRuntime().exec(runPenjualan);
                    } else if ("Windows".equals(System.getProperty("os.name"))) {
                        String runPenjualan = "excel.exe /r" + file + File.separator + "Penjualan.xlsx";
                        Runtime.getRuntime().exec(runPenjualan);
                    }
                }
            } else {
                exportPenjualan.getChooserSaveFile().cancelSelection();
            }
        }
    });
}

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);/*ww  w. j  a  va  2  s. co  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.evidon.areweprivateyet.Aggregator.java

License:Open Source License

private void createHeader(Workbook wb, Sheet s, String sheetTitle, int skipCell) {
    int rownum = 0, cellnum = 0;
    Row r = null;/*from  w w  w .j a v a 2  s  . co  m*/
    Cell c = null;

    // Header
    r = s.createRow(rownum);
    c = r.createCell(0);
    c.setCellValue(sheetTitle);

    rownum++;
    r = s.createRow(rownum);

    if (skipCell > 0) {
        c = r.createCell(cellnum);
        c.setCellValue("");
        cellnum++;
    }

    for (String database : results.keySet()) {
        c = r.createCell(cellnum);
        c.setCellValue(database);

        CellStyle cs = wb.createCellStyle();
        Font f = wb.createFont();
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);
        cs.setFont(f);

        c.setCellStyle(cs);
        cellnum++;
    }
}