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

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

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

Introduction

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

Prototype

public HSSFWorkbook()

Source Link

Document

Creates new HSSFWorkbook from scratch (start here!)

Usage

From source file:com.dv.util.DVExcelIO.java

License:Open Source License

public static boolean exportIntoExcel(String fullExcelFileName, String sheetName, Vector cols, Vector rows) {
    boolean isExportFine = true;

    HSSFWorkbook hsswb = null;//  w ww .  ja  v a 2  s  .  co m
    HSSFSheet hsssh = null;
    HSSFRow row = null;

    try {
        File excel = new File(fullExcelFileName);
        if (!excel.exists()) {
            hsswb = new HSSFWorkbook();
            hsssh = hsswb.createSheet(sheetName);
            hsssh.setDefaultRowHeight((short) 10);
            hsssh.setDefaultColumnWidth(20);

        } else {
            hsswb = new HSSFWorkbook(new FileInputStream(excel));
            hsssh = hsswb.createSheet(sheetName);
            hsssh.setDefaultRowHeight((short) 10);
            hsssh.setDefaultColumnWidth(20);
        }

        row = hsssh.createRow((short) 2);
        HSSFCellStyle style = hsswb.createCellStyle();
        style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);

        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        style.setTopBorderColor(HSSFColor.BLACK.index);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        for (int i = 0; i < cols.size(); i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(cols.get(i).toString());
            cell.setCellStyle(style);
        }

        HSSFCellStyle style1 = hsswb.createCellStyle();

        style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style1.setBorderTop(HSSFCellStyle.BORDER_THIN);

        style1.setBottomBorderColor(HSSFColor.BLACK.index);
        style1.setLeftBorderColor(HSSFColor.BLACK.index);
        style1.setTopBorderColor(HSSFColor.BLACK.index);
        style1.setRightBorderColor(HSSFColor.BLACK.index);

        for (int i = 3; i <= rows.size() + 2; i++) {
            row = hsssh.createRow((short) i);

            for (int j = 0; j < cols.size(); j++) {
                HSSFCell cell = row.createCell(j);
                cell.setCellValue(((Vector) rows.elementAt(i - 3)).get(j).toString());
                cell.setCellStyle(style1);
            }
        }

        FileOutputStream fOut = new FileOutputStream(excel);
        hsswb.write(fOut);
        fOut.flush();
        fOut.close();

    } catch (IOException e) {
        DVLOG.setErrorLog(DVExcelIO.class.getName(), e);
        return false;
    } catch (IllegalArgumentException e) {
        DVLOG.setErrorLog(DVExcelIO.class.getName(), e);
        return false;
    } catch (Exception e) {
        DVLOG.setErrorLog(DVExcelIO.class.getName(), e);
        return false;
    }

    return isExportFine;
}

From source file:com.dv.util.DVExcelIO.java

License:Open Source License

public static boolean exportBatchResultIntoExcel(String fullExcelFileName, String sheetName,
        HashMap<String, Vector> colsMap, HashMap<String, Vector> rowsMap) {
    HSSFWorkbook hsswb = null;// w w  w .  j  a va 2s .  co  m
    HSSFSheet hsssh = null;
    HSSFRow row = null;
    Vector cols = new Vector();
    Vector rows = new Vector();

    try {
        File excel = new File(fullExcelFileName);
        if (!excel.exists()) {
            hsswb = new HSSFWorkbook();
            hsssh = hsswb.createSheet(sheetName);
            hsssh.setDefaultRowHeight((short) 10);
            hsssh.setDefaultColumnWidth(20);

        } else {
            hsswb = new HSSFWorkbook(new FileInputStream(excel));
            hsssh = hsswb.createSheet(sheetName);
            hsssh.setDefaultRowHeight((short) 10);
            hsssh.setDefaultColumnWidth(20);
        }

        int rowCount = 1;

        for (int k = 0; k < colsMap.size(); k++) {

            cols = colsMap.get(String.valueOf(k));
            rows = rowsMap.get(String.valueOf(k));

            rowCount = rowCount + 1;

            row = hsssh.createRow((short) (rowCount));
            HSSFCellStyle style = hsswb.createCellStyle();
            style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);

            style.setBottomBorderColor(HSSFColor.BLACK.index);
            style.setLeftBorderColor(HSSFColor.BLACK.index);
            style.setTopBorderColor(HSSFColor.BLACK.index);
            style.setRightBorderColor(HSSFColor.BLACK.index);

            for (int i = 0; i < cols.size(); i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(cols.get(i).toString());
                cell.setCellStyle(style);
            }

            HSSFCellStyle style1 = hsswb.createCellStyle();

            style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style1.setBorderTop(HSSFCellStyle.BORDER_THIN);

            style1.setBottomBorderColor(HSSFColor.BLACK.index);
            style1.setLeftBorderColor(HSSFColor.BLACK.index);
            style1.setTopBorderColor(HSSFColor.BLACK.index);
            style1.setRightBorderColor(HSSFColor.BLACK.index);

            int loop = rowCount;

            for (int i = 1 + loop; i <= rows.size() + loop; i++) {
                row = hsssh.createRow((short) i);
                for (int j = 0; j < cols.size(); j++) {
                    HSSFCell cell = row.createCell(j);
                    cell.setCellValue(((Vector) rows.elementAt(i - (1 + loop))).get(j).toString());
                    cell.setCellStyle(style1);
                }
                rowCount++;
            }
        }
        FileOutputStream fOut = new FileOutputStream(excel);
        hsswb.write(fOut);
        fOut.flush();
        fOut.close();

    } catch (IOException e) {
        DVLOG.setErrorLog(DVExcelIO.class.getName(), e);
        return false;
    } catch (IllegalArgumentException e) {
        DVLOG.setErrorLog(DVExcelIO.class.getName(), e);
        return false;
    } catch (Exception e) {
        DVLOG.setErrorLog(DVExcelIO.class.getName(), e);
        return false;
    }
    return true;
}

From source file:com.eastsoft.ui.MainUI.java

License:Open Source License

void savePrintInfoToExcel(recordInfo recordinfor, int printFormat) {
    if (printFormat != 0) {
        HSSFWorkbook wb = null;/*  w  w w  . j  a  v  a  2 s .co m*/
        File printRecord = new File("?.xls");
        if (!printRecord.exists()) {

            wb = new HSSFWorkbook();
            HSSFSheet sheet1 = wb.createSheet("sheet1");
            HSSFRow row = sheet1.createRow(0);
            row.createCell((short) 0).setCellValue("?           ");
            row.createCell((short) 1).setCellValue("           ");
            row.createCell((short) 2).setCellValue("??           ");
            row.createCell((short) 3).setCellValue("??           ");
            row.createCell((short) 4).setCellValue("Aid     ");
            row.createCell((short) 5).setCellValue("?            ");

            HSSFRow row1 = sheet1.createRow(sheet1.getLastRowNum() + 1);
            row1.createCell((short) 0).setCellValue(recordinfor.getDate());
            row1.createCell((short) 1).setCellValue(recordinfor.getProductSerialNO());
            row1.createCell((short) 2).setCellValue(recordinfor.getDevice());
            row1.createCell((short) 3).setCellValue(recordinfor.getProduct());
            row1.createCell((short) 4).setCellValue(recordinfor.getAid());
            row1.createCell((short) 5).setCellValue(recordinfor.getPasswd());

        } else {
            FileInputStream fs = null;
            try {
                fs = new FileInputStream(printRecord);
            } catch (FileNotFoundException e2) {
                // TODO Auto-generated catch block
                e2.printStackTrace();
            }
            POIFSFileSystem ps = null;
            try {
                ps = new POIFSFileSystem(fs);
            } catch (IOException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }

            try {
                wb = new HSSFWorkbook(ps);
            } catch (IOException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }

            HSSFSheet sheet = wb.getSheetAt(0);
            // ?cell,Rows 0(Create a row and put some cells
            // in
            // it. Rows are 0 based.)
            HSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);
            row.createCell((short) 0).setCellValue(recordinfor.getDate());
            row.createCell((short) 1).setCellValue(recordinfor.getProductSerialNO());
            row.createCell((short) 2).setCellValue(recordinfor.getDevice());
            row.createCell((short) 3).setCellValue(recordinfor.getProduct());
            row.createCell((short) 4).setCellValue(recordinfor.getAid());
            row.createCell((short) 5).setCellValue(recordinfor.getPasswd());

        }

        FileOutputStream fileOut = null;
        try {
            fileOut = new FileOutputStream(printRecord);
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            appendTextareaText(jTextArea_status,
                    "\n?.xls ??");
            JOptionPane.showMessageDialog(getParent(),
                    "?.xls ??",
                    "?.xls", JOptionPane.WARNING_MESSAGE);
            e.printStackTrace();
        }
        try {
            wb.write(fileOut);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            fileOut.close();

        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

From source file:com.ecannetwork.core.ectable.view.XlsView.java

License:Apache License

public void beforeBody(TableModel model) {
    logger.debug("XlsView.init()");

    moneyFormat = model.getPreferences().getPreference(PreferencesConstants.TABLE_EXPORTABLE + "format.money");
    if (StringUtils.isEmpty(moneyFormat)) {
        moneyFormat = DEFAULT_MONEY_FORMAT;
    }//from  w ww .j av a 2  s  .c  o m
    percentFormat = model.getPreferences()
            .getPreference(PreferencesConstants.TABLE_EXPORTABLE + "format.percent");
    if (StringUtils.isEmpty(percentFormat)) {
        percentFormat = DEFAULT_PERCENT_FORMAT;
    }

    encoding = model.getExportHandler().getCurrentExport().getEncoding();

    wb = new HSSFWorkbook();
    sheet = wb.createSheet();

    //      if (encoding.equalsIgnoreCase("UTF"))
    //      {
    //         wb.setSheetName(0, "Export Workbook", HSSFWorkbook.ENCODING_UTF_16);
    //      } else if (encoding.equalsIgnoreCase("UNICODE"))
    //      {
    //         wb.setSheetName(0, "Export Workbook",
    //               HSSFWorkbook.ENCODING_COMPRESSED_UNICODE);
    //      }

    styles = initStyles(wb);
    ps = sheet.getPrintSetup();

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

    createHeader(model);
}

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

License:Open Source License

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

    boolean isTrial = userFieldMap.isTrial();

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

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

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

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

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

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

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

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

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

        CellStyle mainHeaderStyle = workbook.createCellStyle();

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

        CellStyle mainSubHeaderStyle = workbook.createCellStyle();

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

        int rowIndex = 0;
        int columnIndex = 0;

        // Create Header Information

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

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

        summaryCell.setCellStyle(headerLabelStyle);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

        row.createCell(columnIndex++);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

        }

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

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

}

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 www . j av  a2 s  . co m*/

        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

@Override
public void export(Workbook workbook, String filename) {
    FileOutputStream fos = null;// ww w  .j a  va2  s.  c  om

    try {
        HSSFWorkbook xlsBook = new HSSFWorkbook();

        writeDescriptionSheet(xlsBook, workbook);
        writeObservationSheet(xlsBook, workbook);

        fos = new FileOutputStream(new File(filename));
        xlsBook.write(fos);

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (fos != null) {
            try {
                fos.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

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

public static void main(String[] args) {
    try {/*from w  w  w  .j av  a2s.c om*/

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

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

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

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

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

}

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

public static void exportTOExcel(DefaultTableModel dtm, Map<String, String> ln_info) {
    FileOutputStream out = null;/* www  .  j  ava2s .  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.eryansky.core.excelTools.JsGridReportBase.java

License:Apache License

/**
 * Excel(?)//ww  w .  ja v  a 2  s. co  m
 * 
 * @param title
 *            ??
 * @param creator
 *            
 * @param tableData
 *            ?
 * @return void <style name="dataset"> case SYSROWNUM%2==0?#row0:#row1;
 *         fontsize:9px; </style> <style name="row0"> import(parent);
 *         bgcolor:#FFFFFF; </style> <style name="row1"> import(parent);
 *         bgcolor:#CAEAFE; </style>
 */
public void exportToExcel(String title, String creator, TableData tableData) throws Exception {

    HSSFWorkbook wb = new HSSFWorkbook();// Excel 
    HashMap<String, HSSFCellStyle> styles = initStyles(wb);// ??

    wb = writeSheet(wb, title, styles, creator, tableData);//

    String sFileName = title + ".xls";
    WebUtils.setDownloadableHeader(request, response, sFileName);
    response.setHeader("Connection", "close");
    response.setHeader("Content-Type", WebUtils.EXCEL_TYPE);

    wb.write(response.getOutputStream());
}