Example usage for org.apache.poi.ss.usermodel Workbook createSheet

List of usage examples for org.apache.poi.ss.usermodel Workbook createSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. createSheet

Introduction

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

Prototype

Sheet createSheet(String sheetname);

Source Link

Document

Create a new sheet for this Workbook and return the high level representation.

Usage

From source file:instancegenerator.ExcelGenerator.java

public static void makeInstancesN1000S2() {
    int weight = 0;
    int capacity = 0;
    int cost = 0;
    int[] items = new int[1000];
    try {/*  ww  w  .j a  va  2 s . c  o m*/
        //**************** Class B ***************************************************
        //instance B1
        FileOutputStream fileout = new FileOutputStream(new File("src/instances/Set2/N1000/N1000B1.xls"));
        Workbook ficheroWb = new HSSFWorkbook();
        Sheet sheet = ficheroWb.createSheet("Instance B1 for VSBPP");
        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue("Capacity");
        row.createCell(1).setCellValue("Cost");
        row.createCell(2).setCellValue("Weight");
        //for the items
        int reqCapacity = 0;
        for (int i = 1; i <= 1000; i++) {
            weight = (int) randVal(1, 20);
            items[i - 1] = weight;
            reqCapacity += weight;
            row = sheet.createRow(i);
            row.createCell(2).setCellValue(weight);
        }
        // for the bins
        int cap = 100;
        int j = 1;
        for (int i = 1; i <= 3; i++) {
            int availableCap = 0;
            capacity = cap * i;
            while (availableCap < reqCapacity) {
                row = sheet.getRow(j);
                double y = randValueReal(0.05, 0.3);
                cost = (int) ((100 * Math.sqrt(capacity)) * (1 + y));
                if (row != null) {
                    row.createCell(0).setCellValue(capacity);
                    row.createCell(1).setCellValue(cost);
                } else {
                    row = sheet.createRow(j);
                    row.createCell(0).setCellValue(capacity);
                    row.createCell(1).setCellValue(cost);
                }
                j++;
                availableCap = availableCap + capacity;
            }
        }
        ficheroWb.write(fileout);
        fileout.flush();
        capacity = 0;

        //instance B2
        fileout = new FileOutputStream(new File("src/instances/Set2/N1000/N1000B2.xls"));
        ficheroWb = new HSSFWorkbook();
        sheet = ficheroWb.createSheet("Instance B2 for VSBPP");
        row = sheet.createRow(0);
        row.createCell(0).setCellValue("Capacity");
        row.createCell(1).setCellValue("Cost");
        row.createCell(2).setCellValue("Weight");
        // for the items
        for (int i = 1; i <= 1000; i++) {
            row = sheet.createRow(i);
            row.createCell(2).setCellValue(items[i - 1]);
        }
        //for the bins
        cap = 50;
        j = 1;
        for (int i = 1; i <= 6; i++) {
            int availableCap = 0;
            capacity = cap * i;
            while (availableCap < reqCapacity) {
                row = sheet.getRow(j);
                double y = randValueReal(0.05, 0.3);
                cost = (int) ((100 * Math.sqrt(capacity)) * (1 + y));
                if (row != null) {
                    row.createCell(0).setCellValue(capacity);
                    row.createCell(1).setCellValue(cost);
                } else {
                    row = sheet.createRow(j);
                    row.createCell(0).setCellValue(capacity);
                    row.createCell(1).setCellValue(cost);
                }
                j++;
                availableCap = availableCap + capacity;
            }
        }
        ficheroWb.write(fileout);
        fileout.flush();
        capacity = 0;

        //instance B3
        fileout = new FileOutputStream(new File("src/instances/Set2/N1000/N1000B3.xls"));
        ficheroWb = new HSSFWorkbook();
        sheet = ficheroWb.createSheet("Instance A3 for VSBPP");
        row = sheet.createRow(0);
        row.createCell(0).setCellValue("Capacity");
        row.createCell(1).setCellValue("Cost");
        row.createCell(2).setCellValue("Weight");
        // for the items
        for (int i = 1; i <= 1000; i++) {
            row = sheet.createRow(i);
            row.createCell(2).setCellValue(items[i - 1]);
        }
        //for the bins
        cap = 25;
        j = 1;
        for (int i = 1; i <= 12; i++) {
            int availableCap = 0;
            capacity = cap * i;
            while (availableCap < reqCapacity) {
                row = sheet.getRow(j);
                double y = randValueReal(0.05, 0.3);
                cost = (int) ((100 * Math.sqrt(capacity)) * (1 + y));
                if (row != null) {
                    row.createCell(0).setCellValue(capacity);
                    row.createCell(1).setCellValue(cost);
                } else {
                    row = sheet.createRow(j);
                    row.createCell(0).setCellValue(capacity);
                    row.createCell(1).setCellValue(cost);
                }
                j++;
                availableCap = availableCap + capacity;
            }
        }
        ficheroWb.write(fileout);
        fileout.flush();
        capacity = 0;

        //instance B4
        fileout = new FileOutputStream(new File("src/instances/Set2/N1000/N1000B4.xls"));
        ficheroWb = new HSSFWorkbook();
        sheet = ficheroWb.createSheet("Instance B4 for VSBPP");
        row = sheet.createRow(0);
        row.createCell(0).setCellValue("Capacity");
        row.createCell(1).setCellValue("Cost");
        row.createCell(2).setCellValue("Weight");
        // for the items
        for (int i = 1; i <= 1000; i++) {
            row = sheet.createRow(i);
            row.createCell(2).setCellValue(items[i - 1]);
        }
        //for the bins
        cap = 60;
        j = 1;
        for (int i = 1; i <= 55; i++) {
            capacity = cap;
            double y = randValueReal(0.05, 0.3);
            cost = (int) ((100 * Math.sqrt(capacity)) * (1 + y));
            row = sheet.getRow(i);
            row.createCell(0).setCellValue(capacity);
            row.createCell(1).setCellValue(cost);
            cap = cap + 5;
        }
        ficheroWb.write(fileout);
        fileout.flush();
        capacity = 0;

    }

    catch (IOException ex) {
        System.out.println(ex.getMessage());
    }
}

From source file:io.vulpine.lib.kalo.Kalo.java

License:Apache License

public <T> Workbook poi(final Collection<T> items, final Class<T> type, final String sheetName,
        final Workbook workbook) {
    final Sheet sheet = workbook.createSheet(sheetName);
    final PropertyAggregator map = ClassUtils.parse(type);
    final Collection<Imu> sorted = sortColumns(map.columns());

    int row = 1;//from  w  w  w  .j a va  2 s  . co m

    WorkbookUtil.parseHeaders(sheet, map, sorted, new PoiConfig().getHeaderStyle(workbook, null));

    for (final T item : items) {
        final Row sheetRow = sheet.createRow(row++);
        int col = 0;
        for (final Imu imu : sorted) {
            final Cell cell = sheetRow.createCell(col++);
            final CellType cType = PoiUtil.translateType(imu);

            cell.setCellType(cType);
            switch (cType) {
            case BOOLEAN:
                cell.setCellValue((Boolean) imu.getValue(item));
                break;
            case NUMERIC:
                cell.setCellValue(((Number) imu.getValue(item)).doubleValue());
                break;
            default:
                cell.setCellValue(String.valueOf(imu.getValue(item)));
            }
        }
    }

    for (int i = 0; i < sorted.size(); i++)
        sheet.autoSizeColumn(i);

    return workbook;
}

From source file:it.drwolf.ridire.util.async.ExcelGenerator.java

License:Apache License

@Asynchronous
public void generateExcelTable(ExcelDataGenerator excelDataGenerator) {
    excelDataGenerator.setProgress(0);//from  www  .jav  a  2 s.c o m
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    this.cwbConcordancer.setForma(excelDataGenerator.getForma());
    this.cwbConcordancer.setContextGroupingLength(excelDataGenerator.getContextGroupingLength());
    this.cwbConcordancer.setContextLength(excelDataGenerator.getContextLength());
    this.cwbConcordancer.setLemma(excelDataGenerator.getLemma());
    this.cwbConcordancer.setPhrase(excelDataGenerator.getPhrase());
    this.cwbConcordancer.setPos(excelDataGenerator.getPos());
    this.cwbConcordancer.setSortBy(excelDataGenerator.getSortBy());
    this.cwbConcordancer.setSortOrder(excelDataGenerator.getSortOrder());
    this.cwbConcordancer.setToBeVisualized(excelDataGenerator.getToBeVisualized());
    this.cwbConcordancer.setFunctionalMetadatum(excelDataGenerator.getFunctionalMetadatum());
    this.cwbConcordancer.setSemanticMetadatum(excelDataGenerator.getSemanticMetadatum());
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet(DocumentDownloader.CONCORDANZE);
    int rowNumber = 0;
    int results4DownloadSize = this.cwbConcordancer.getResults4DownloadSize();
    // System.out.println("Query size: " + results4DownloadSize);
    int i = 0;
    for (int start = 0; start < results4DownloadSize; start += ExcelGenerator.PAGE_SIZE) {
        List<CWBResult> results4Download = this.cwbConcordancer.getResults4Download(start,
                ExcelGenerator.PAGE_SIZE);
        for (CWBResult itemWithContext : results4Download) {
            Row row = sheet.createRow(rowNumber);
            ++rowNumber;
            row.createCell(0).setCellValue(itemWithContext.getLeftContext().toString());
            row.createCell(1).setCellValue(itemWithContext.getSearchedText());
            row.createCell(2).setCellValue(itemWithContext.getRightContext().toString());
            excelDataGenerator.setProgress(Math.round(++i / (results4DownloadSize * 1.0f) * 100));
        }
    }
    try {
        workbook.write(baos);
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    excelDataGenerator.setFileReady(true);
    excelDataGenerator.setBaos(baos);
    excelDataGenerator.setProgress(100);
    excelDataGenerator.setInProgress(false);
}

From source file:it.drwolf.ridire.util.async.ExcelGenerator.java

License:Apache License

@Asynchronous
public void generateFLTable(ExcelDataGenerator excelDataGenerator) {
    excelDataGenerator.setProgress(0);//ww w . j a v a  2s  .  c o m
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    this.cwbFrequencyList.setFunctionalMetadatum(excelDataGenerator.getFunctionalMetadatum());
    this.cwbFrequencyList.setSemanticMetadatum(excelDataGenerator.getSemanticMetadatum());
    this.cwbFrequencyList.setFrequencyBy(excelDataGenerator.getFrequencyBy());
    this.cwbFrequencyList.setQuantity(excelDataGenerator.getQuantity());
    this.cwbFrequencyList.setThreshold(excelDataGenerator.getThreshold());
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet(DocumentDownloader.CONCORDANZE);
    int rowNumber = 0;
    this.cwbFrequencyList.calculateFrequencyList();
    int results4DownloadSize = this.cwbFrequencyList.getFrequencyList().size();
    // System.out.println("Query size: " + results4DownloadSize);
    int i = 0;
    for (int start = 0; start < results4DownloadSize; start += ExcelGenerator.PAGE_SIZE) {
        List<FrequencyItem> results4Download = this.cwbFrequencyList.getFrequencyList().subList(start,
                Math.min(start + ExcelGenerator.PAGE_SIZE, results4DownloadSize));
        for (FrequencyItem frequencyItem : results4Download) {
            Row row = sheet.createRow(rowNumber);
            ++rowNumber;
            row.createCell(0).setCellValue(frequencyItem.getFormaPosLemma());
            if (excelDataGenerator.getFrequencyBy().equals("PoS-forma")
                    || excelDataGenerator.getFrequencyBy().equals("PoS-lemma")) {
                row.createCell(1).setCellValue(frequencyItem.getPos());
                row.createCell(2).setCellValue(frequencyItem.getFrequency());
            } else {
                row.createCell(1).setCellValue(frequencyItem.getFrequency());
            }
            excelDataGenerator.setProgress(Math.round(++i / (results4DownloadSize * 1.0f) * 100));
        }
    }
    try {
        workbook.write(baos);
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    excelDataGenerator.setFileReady(true);
    excelDataGenerator.setBaos(baos);
    excelDataGenerator.setProgress(100);
    excelDataGenerator.setInProgress(false);
}

From source file:it.eng.spagobi.engines.console.exporter.types.ExporterExcel.java

License:Mozilla Public License

public Workbook export() {
    Workbook wb = new HSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("new sheet");
    for (int j = 0; j < 50; j++) {
        sheet.createRow(j);//w ww . ja  va2s. c o  m
    }
    fillSheet(sheet, wb, createHelper);

    return wb;
}

From source file:it.eng.spagobi.engines.qbe.exporter.QbeXLSExporter.java

License:Mozilla Public License

public Workbook export() {
    Workbook workbook = this.instantiateWorkbook();
    CreationHelper createHelper = workbook.getCreationHelper();
    Sheet sheet = workbook.createSheet("new sheet");
    for (int j = 0; j < 50; j++) {
        sheet.createRow(j);/*from  w w w  .j  ava  2s . co  m*/
    }
    fillSheet(sheet, workbook, createHelper, 0);
    return workbook;
}

From source file:it.eng.spagobi.engines.worksheet.services.export.ExportWorksheetAction.java

License:Mozilla Public License

public void exportMetadataToXLS(Workbook wb, WorkSheetXLSExporter exporter, CreationHelper createHelper,
        JSONArray metadataPropertiesJSON, JSONArray parametersJSON) throws Exception {

    int FIRST_ROW = 0;
    int FIRST_COLUMN = 0;
    int rowCount = 0;

    JSONArray technicalMetadataProperty;
    JSONArray shortBusinessMetadataProperty;
    JSONArray longBusinessMetadataProperty;

    org.apache.poi.ss.usermodel.Sheet sheet = wb
            .createSheet(EngineMessageBundle.getMessage("worksheet.export.metadata.title", this.getLocale()));

    sheet.setColumnWidth(FIRST_COLUMN, 256 * 25);
    sheet.setColumnWidth(FIRST_COLUMN + 1, 256 * 90);

    CellStyle headerCellStyle = exporter.buildMetadataTitleCellStyle(sheet);
    CellStyle metaNameCellStyle = exporter.buildMetadataNameCellStyle(sheet);
    CellStyle metaValueCellStyle = exporter.buildMetadataValueCellStyle(sheet);

    Row row;/*ww  w .j  av a2 s .c  o  m*/
    Cell nameCell;
    Cell valueCell;
    Cell headerCell;
    String text;

    technicalMetadataProperty = new JSONArray();
    shortBusinessMetadataProperty = new JSONArray();
    longBusinessMetadataProperty = new JSONArray();

    if (metadataPropertiesJSON != null) {
        for (int i = 0; i < metadataPropertiesJSON.length(); i++) {
            JSONObject metadataProperty = metadataPropertiesJSON.getJSONObject(i);
            String metadataPropertyType = metadataProperty.getString("meta_type");
            if ("SHORT_TEXT".equalsIgnoreCase(metadataPropertyType)) {
                shortBusinessMetadataProperty.put(metadataProperty);
                continue;
            } else if ("LONG_TEXT".equalsIgnoreCase(metadataPropertyType)) {
                longBusinessMetadataProperty.put(metadataProperty);
                continue;
            } else {
                technicalMetadataProperty.put(metadataProperty);
            }

        }

    }

    if (technicalMetadataProperty.length() > 0) {

        row = sheet.createRow((FIRST_ROW) + rowCount);
        headerCell = row.createCell(FIRST_COLUMN + 1);
        headerCell = row.createCell(FIRST_COLUMN + 1);
        text = EngineMessageBundle.getMessage("worksheet.export.metadata.technicalMetadata", this.getLocale());
        headerCell.setCellValue(createHelper.createRichTextString(text));
        headerCell.setCellType(exporter.getCellTypeString());
        headerCell.setCellStyle(headerCellStyle);

        rowCount++;

        for (int i = 0; i < technicalMetadataProperty.length(); i++) {
            JSONObject metadataProperty = technicalMetadataProperty.getJSONObject(i);

            String metadataPropertyName = metadataProperty.getString("meta_name");
            String metadataPropertyValue = metadataProperty.getString("meta_content");
            row = sheet.createRow((FIRST_ROW) + rowCount);

            nameCell = row.createCell(FIRST_COLUMN);
            nameCell.setCellValue(createHelper.createRichTextString(metadataPropertyName));
            nameCell.setCellType(exporter.getCellTypeString());
            nameCell.setCellStyle(metaNameCellStyle);

            valueCell = row.createCell(FIRST_COLUMN + 1);
            valueCell.setCellValue(createHelper.createRichTextString(metadataPropertyValue));
            valueCell.setCellType(exporter.getCellTypeString());
            valueCell.setCellStyle(metaValueCellStyle);
            rowCount++;
        }

        rowCount = rowCount + 2;

    }

    if (shortBusinessMetadataProperty.length() + longBusinessMetadataProperty.length() > 0) {

        row = sheet.createRow((FIRST_ROW) + rowCount);
        headerCell = row.createCell(FIRST_COLUMN + 1);
        headerCell = row.createCell(FIRST_COLUMN + 1);
        text = EngineMessageBundle.getMessage("worksheet.export.metadata.businessMetadata", this.getLocale());
        headerCell.setCellValue(createHelper.createRichTextString(text));
        headerCell.setCellType(exporter.getCellTypeString());
        headerCell.setCellStyle(headerCellStyle);
        rowCount++;

        for (int i = 0; i < shortBusinessMetadataProperty.length(); i++, rowCount++) {

            JSONObject metadataProperty = shortBusinessMetadataProperty.getJSONObject(i);

            String metadataPropertyName = metadataProperty.getString("meta_name");
            String metadataPropertyValue = metadataProperty.getString("meta_content");
            row = sheet.createRow((FIRST_ROW) + rowCount);

            nameCell = row.createCell(FIRST_COLUMN);
            nameCell.setCellValue(createHelper.createRichTextString(metadataPropertyName));
            nameCell.setCellType(exporter.getCellTypeString());
            nameCell.setCellStyle(metaNameCellStyle);

            valueCell = row.createCell(FIRST_COLUMN + 1);
            valueCell.setCellValue(createHelper.createRichTextString(metadataPropertyValue));
            valueCell.setCellType(exporter.getCellTypeString());
            valueCell.setCellStyle(metaValueCellStyle);
        }

        for (int i = 0; i < longBusinessMetadataProperty.length(); i++, rowCount++) {

            JSONObject metadataProperty = longBusinessMetadataProperty.getJSONObject(i);

            String metadataPropertyName = metadataProperty.getString("meta_name");
            String metadataPropertyValue = metadataProperty.getString("meta_content");

            row = sheet.createRow((FIRST_ROW) + rowCount);

            nameCell = row.createCell(FIRST_COLUMN);
            nameCell.setCellValue(createHelper.createRichTextString(metadataPropertyName));
            nameCell.setCellType(exporter.getCellTypeString());
            nameCell.setCellStyle(metaNameCellStyle);

            valueCell = row.createCell(FIRST_COLUMN + 1);
            valueCell.setCellValue(createHelper.createRichTextString(metadataPropertyValue));
            valueCell.setCellType(exporter.getCellTypeString());
            valueCell.setCellStyle(metaValueCellStyle);
        }

        rowCount = rowCount + 2;

    }

    if (parametersJSON.length() > 0) {

        row = sheet.createRow((FIRST_ROW) + rowCount);
        headerCell = row.createCell(FIRST_COLUMN + 1);
        headerCell = row.createCell(FIRST_COLUMN + 1);
        text = EngineMessageBundle.getMessage("worksheet.export.metadata.analyticalDrivers", this.getLocale());
        headerCell.setCellValue(createHelper.createRichTextString(text));
        headerCell.setCellType(exporter.getCellTypeString());
        headerCell.setCellStyle(headerCellStyle);

        rowCount++;

        Drawing drawing = sheet.createDrawingPatriarch();

        for (int i = 0; i < parametersJSON.length(); i++) {
            JSONObject parameterJSON = parametersJSON.getJSONObject(i);
            String name = parameterJSON.getString("name");
            String value = parameterJSON.getString("value");
            String description = parameterJSON.optString("description");

            row = sheet.createRow((FIRST_ROW) + rowCount);

            nameCell = row.createCell(FIRST_COLUMN);
            nameCell.setCellValue(createHelper.createRichTextString(name));
            nameCell.setCellType(exporter.getCellTypeString());
            nameCell.setCellStyle(metaNameCellStyle);

            valueCell = row.createCell(FIRST_COLUMN + 1);

            if (StringUtilities.isNotEmpty(description)) {

                valueCell.setCellValue(createHelper.createRichTextString(description));

                ClientAnchor anchor = createHelper.createClientAnchor();
                anchor.setCol1(valueCell.getColumnIndex());
                anchor.setCol2(valueCell.getColumnIndex() + 1);
                anchor.setRow1(row.getRowNum());
                anchor.setRow2(row.getRowNum() + 3);

                Comment comment = drawing.createCellComment(anchor);
                RichTextString str = createHelper.createRichTextString(value);
                comment.setString(str);
                comment.setAuthor("SpagoBI");

                valueCell.setCellComment(comment);
            } else {
                valueCell.setCellValue(createHelper.createRichTextString(value));
            }
            valueCell.setCellType(exporter.getCellTypeString());
            valueCell.setCellStyle(metaValueCellStyle);
            rowCount++;
        }

    }

}

From source file:it.eng.spagobi.engines.worksheet.services.export.ExportWorksheetAction.java

License:Mozilla Public License

public void exportSheetToXLS(Workbook wb, JSONObject sheetJ, JSONArray fieldOptions,
        WorkSheetXLSExporter exporter, CreationHelper createHelper, WhereField splittingWhereField)
        throws Exception {

    //The number of row of the sheet
    int sheetRow = 0;

    String sheetName = sheetJ.getString(SHEET);
    String finalSheetName = sheetName;
    if (splittingWhereField != null) {
        finalSheetName = sheetName + " (" + splittingWhereField.getRightOperand().values[0] + ")";
    }/* ww w  .j  a va 2 s.  c  o m*/

    org.apache.poi.ss.usermodel.Sheet sheet = wb.createSheet(finalSheetName);
    sheet.setDefaultColumnWidth(DEFAULT_COLUMN_WIDTH);

    Drawing patriarch = sheet.createDrawingPatriarch();

    if (sheetJ.has(WorkSheetXLSExporter.HEADER)) {
        JSONObject header = sheetJ.getJSONObject(WorkSheetXLSExporter.HEADER);
        if (header != null) {
            sheetRow = exporter.setHeader(sheet, header, createHelper, wb, patriarch, sheetRow);
        }
        sheet.createRow(sheetRow);
        sheetRow++;
    }

    JSONObject optionalFiltersJSON = sheetJ.optJSONObject(QbeEngineStaticVariables.FILTERS);
    List<WhereField> optionalFilters = getOptionalFilters(optionalFiltersJSON);
    if (splittingWhereField != null) {
        optionalFilters.add(splittingWhereField);
    }
    Map<String, List<String>> filters = getSheetFiltersInfo(sheetJ.getString(SHEET), optionalFilters);
    if (filters != null && !filters.isEmpty()) {
        sheetRow = fillFiltersInfo(filters, wb, sheet, exporter, splittingWhereField, createHelper, sheetRow,
                0);
        sheet.createRow(sheetRow);
        sheetRow++;
    }

    if (sheetJ.has(WorkSheetXLSExporter.CONTENT)) {
        // if there are mandatory (non-splitting) filters with no values, we cannot get the content of the sheet
        boolean filtersOk = this.checkMandatoryFilters(optionalFilters, sheetName);
        if (!filtersOk) {
            Row row = sheet.createRow(sheetRow++);
            Cell cell = row.createCell(0);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(EngineMessageBundle.getMessage("worksheet.export.missingMandatoryFilter",
                    this.getLocale()));
        } else {
            sheetRow = fillSheetContent(wb, sheet, sheetJ, fieldOptions, splittingWhereField, createHelper,
                    exporter, patriarch, sheetRow);
        }
    }

    sheet.createRow(sheetRow);
    sheetRow++;

    if (sheetJ.has(WorkSheetXLSExporter.FOOTER)) {
        JSONObject footer = sheetJ.getJSONObject(WorkSheetXLSExporter.FOOTER);
        if (footer != null) {
            exporter.setFooter(sheet, footer, createHelper, wb, patriarch, sheetRow);
        }
    }
}

From source file:it.unitn.elisco.utils.Utilities.java

public static Workbook getExcelFromQuestionList(String workbookName, List<Question> questions) {

    // Create EXCEL File (Workbook with sheets)
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet(workbookName);

    // Create styles for cells
    CellStyle questionStyle = workbook.createCellStyle();
    questionStyle.setWrapText(true);/*ww  w  .  j  av  a 2 s  . c  o  m*/
    questionStyle.setVerticalAlignment(VerticalAlignment.CENTER);

    CellStyle othersStyle = workbook.createCellStyle();
    othersStyle.setAlignment(HorizontalAlignment.CENTER);
    othersStyle.setVerticalAlignment(VerticalAlignment.CENTER);

    // Create header row 
    Row headerRow = sheet.createRow(0);
    headerRow.createCell(0).setCellValue("ID");
    headerRow.createCell(1).setCellValue("TAG");
    headerRow.createCell(2).setCellValue("DOMANDA");
    headerRow.createCell(3).setCellValue("APRROVATA (SI/NO)");
    headerRow.getCell(0).setCellStyle(othersStyle);
    headerRow.getCell(1).setCellStyle(othersStyle);
    headerRow.getCell(2).setCellStyle(othersStyle);
    headerRow.getCell(3).setCellStyle(othersStyle);

    int rownum = 1;
    for (Question question : questions) {
        // Create a row
        Row row = sheet.createRow(rownum++);

        // Create cells for id and question and set their values
        row.createCell(0).setCellValue(question.getId());
        row.createCell(1).setCellValue(question.getTag());
        row.createCell(2).setCellValue(question.getBody());
        // Create empty cell for admin input
        row.createCell(3);

        // Set cell styles
        row.getCell(0).setCellStyle(othersStyle);
        row.getCell(1).setCellStyle(othersStyle);
        row.getCell(2).setCellStyle(questionStyle);
        row.getCell(3).setCellStyle(othersStyle);
    }

    sheet.autoSizeColumn(0);
    sheet.autoSizeColumn(1);
    sheet.setColumnWidth(2, 100 * 256); // Set questionStyle column width to 100 characters
    sheet.autoSizeColumn(3);

    return workbook;
}

From source file:itpreneurs.itp.report.archive.BusinessPlan.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;

    // if(args.length > 0 && args[0].equals("-xls")) wb = new
    // HSSFWorkbook();
    // else wb = new XSSFWorkbook();

    wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    Sheet sheet = wb.createSheet("Business Plan");

    // turn off gridlines
    sheet.setDisplayGridlines(false);/* w  ww .  j  ava  2 s . co  m*/
    sheet.setPrintGridlines(false);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);

    // the following three statements are required only for HSSF
    sheet.setAutobreaks(true);
    printSetup.setFitHeight((short) 1);
    printSetup.setFitWidth((short) 1);

    // the header row: centered text in 48pt font
    Row headerRow = sheet.createRow(0);
    headerRow.setHeightInPoints(12.75f);
    for (int i = 0; i < titles.length; i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellValue(titles[i]);
        cell.setCellStyle(styles.get("header"));
    }
    // columns for 11 weeks starting from 9-Jul
    Calendar calendar = Calendar.getInstance();
    int year = calendar.get(Calendar.YEAR);

    calendar.setTime(fmt.parse("9-Jul"));
    calendar.set(Calendar.YEAR, year);
    for (int i = 0; i < 11; i++) {
        Cell cell = headerRow.createCell(titles.length + i);
        cell.setCellValue(calendar);
        cell.setCellStyle(styles.get("header_date"));
        calendar.roll(Calendar.WEEK_OF_YEAR, true);
    }
    // freeze the first row
    sheet.createFreezePane(0, 1);

    Row row;
    Cell cell;
    int rownum = 1;
    for (int i = 0; i < data.length; i++, rownum++) {
        row = sheet.createRow(rownum);
        if (data[i] == null)
            continue;

        for (int j = 0; j < data[i].length; j++) {
            cell = row.createCell(j);
            String styleName;
            boolean isHeader = i == 0 || data[i - 1] == null;
            switch (j) {
            case 0:
                if (isHeader) {
                    styleName = "cell_b";
                    cell.setCellValue(Double.parseDouble(data[i][j]));
                } else {
                    styleName = "cell_normal";
                    cell.setCellValue(data[i][j]);
                }
                break;
            case 1:
                if (isHeader) {
                    styleName = i == 0 ? "cell_h" : "cell_bb";
                } else {
                    styleName = "cell_indented";
                }
                cell.setCellValue(data[i][j]);
                break;
            case 2:
                styleName = isHeader ? "cell_b" : "cell_normal";
                cell.setCellValue(data[i][j]);
                break;
            case 3:
                styleName = isHeader ? "cell_b_centered" : "cell_normal_centered";
                cell.setCellValue(Integer.parseInt(data[i][j]));
                break;
            case 4: {
                calendar.setTime(fmt.parse(data[i][j]));
                calendar.set(Calendar.YEAR, year);
                cell.setCellValue(calendar);
                styleName = isHeader ? "cell_b_date" : "cell_normal_date";
                break;
            }
            case 5: {
                int r = rownum + 1;
                String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")";
                cell.setCellFormula(fmla);
                styleName = isHeader ? "cell_bg" : "cell_g";
                break;
            }
            default:
                styleName = data[i][j] != null ? "cell_blue" : "cell_normal";
            }

            cell.setCellStyle(styles.get(styleName));
        }
    }

    // group rows for each phase, row numbers are 0-based
    sheet.groupRow(4, 6);
    sheet.groupRow(9, 13);
    sheet.groupRow(16, 18);

    // set column widths, the width is measured in units of 1/256th of a
    // character width
    sheet.setColumnWidth(0, 256 * 6);
    sheet.setColumnWidth(1, 256 * 33);
    sheet.setColumnWidth(2, 256 * 20);
    sheet.setZoom(3, 4);

    // Write the output to a file
    String file = "/Users/vincentgong/Documents/workspaces/Resource/itpreneurs/report/businessplan.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}