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

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

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

Introduction

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

Prototype

CreationHelper getCreationHelper();

Source Link

Document

Returns an object that handles instantiating concrete classes of the various instances one needs for HSSF and XSSF.

Usage

From source file:fi.thl.pivot.export.XlsxExporter.java

private void createNumberStyle(Workbook wb) {
    this.numberStyle = wb.createCellStyle();
    numberStyle.setDataFormat(wb.getCreationHelper().createDataFormat().getFormat("#,##0"));
    numberStyle.setFont(valueFont);//from  w  w  w .j  a  v a2  s.  c  o  m
    numberStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
}

From source file:fi.thl.pivot.export.XlsxExporter.java

private CellStyle measureStyle(Workbook wb, int decimals) {
    if (decimals == 0) {
        return numberStyle;
    }/*from  w ww .j  a va 2s.c o  m*/
    if (decimalStyles.containsKey(decimals)) {
        return decimalStyles.get(decimals);
    }
    CellStyle style = wb.createCellStyle();
    String format = String.format("#,##0.%0" + decimals + "d", 0);
    style.setDataFormat(wb.getCreationHelper().createDataFormat().getFormat(format));
    style.setFont(valueFont);
    decimalStyles.put(decimals, style);
    return style;

}

From source file:ga_assignment.GeneticAlgorithm.java

public static void main(String[] args) throws FileNotFoundException, IOException {

    GeneticAlgorithm ga = new GeneticAlgorithm();

    // Reads in training set
    Rule[] rules;/* w  w  w  .  j  ava  2  s  .c o m*/
    rules = new Rule[32];
    Scanner inin = new Scanner(new FileReader("data1.txt"));
    for (int x = 0; x < 32; x++) {
        Rule rule = new Rule();
        rule.setCondition(inin.next());
        rule.setOutput(inin.nextInt());
        rules[x] = rule;
    }

    //set up export to excel
    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("sheet");
    Row row = sheet.createRow((short) 0);
    row.createCell(0).setCellValue(wb.getCreationHelper().createRichTextString("Generations"));
    row.createCell(1).setCellValue(wb.getCreationHelper().createRichTextString("Best Fitness"));
    row.createCell(2).setCellValue(wb.getCreationHelper().createRichTextString("Mean Fitness"));
    int rowNumber = 1;

    Random rand = new Random();
    Individual in = new Individual();
    FitnessCalculator fit = new FitnessCalculator();

    ArrayList<Individual> population = new ArrayList<>();
    ArrayList<Individual> offspring = new ArrayList<>();

    int i;
    int j;
    int[] gene;
    gene = new int[ga.N];
    int tempFitness = 0;
    int totalFitness = 0;

    int optimalFitness = (ga.N / 6) * ga.P;
    System.out.println("OPTIMAL FITNESS = " + optimalFitness);

    //
    //
    //
    //
    //
    //
    // ======================================= INITIALISATION =======================================
    for (i = 0; i < ga.P; i++) { // for i is less than the population limit
        tempFitness = 0;

        for (j = 0; j < ga.N; j++) { // for j is less than the number of genes in an individual
            gene[j] = rand.nextInt(2) % 2; // population[individual] gets genes (10010) set
        } // repeat until 5 individuals have 5 genes

        // converts gene to string
        String geno = "";
        for (int a : gene) {
            geno += Integer.toString(a);
        }

        // creates a new individual
        Individual ind = new Individual();
        ind.setGene(geno);
        ind.setFitness(tempFitness);
        population.add(ind);
    }

    // gets total fitness of original population
    for (Individual fitt : population) {
        fit.fitnessCalculator(fitt, rules);
        totalFitness = fitt.getFitness() + totalFitness;
        System.out.println("gene = " + fitt.getGene() + " fit = " + fitt.getFitness());
    }
    System.out.println("Initial Total Fitness = " + totalFitness + "\n");

    //
    //
    //
    //
    //
    //
    // ======================================= PARENT SELECTION =======================================
    // 
    for (j = 0; j < ga.G; j++) {
        if (totalFitness < 500000) {
            totalFitness = 0;

            // Loop through Population
            for (i = 0; i < ga.P; i++) {

                Individual winner1;

                // chooses X individuals at random     
                Individual parent1 = population.get(new Random().nextInt(population.size()));
                Individual parent2 = population.get(new Random().nextInt(population.size()));

                // compares parents based on fitness
                if (parent1.getFitness() >= parent2.getFitness()) {
                    winner1 = parent1;
                } else {
                    winner1 = parent2;
                }

                offspring.add(winner1);

            }

            //
            //
            //
            //
            //
            //
            // ======================================= CROSSOVER / MUTATION =======================================
            for (i = 0; i < ga.P; i++) {

                // get random bit from individual
                int pos1 = new Random().nextInt(offspring.size());
                Individual child1 = offspring.get(pos1);

                int pos2 = new Random().nextInt(offspring.size());
                Individual child2 = offspring.get(pos2);

                // Crossover between two individuals
                in.Crossover(child1, child2);

                // Mutation of individual 1
                String ds = child1.getGene();
                Individual c1 = new Individual();
                c1.setGene(ds);
                in.Mutator(c1, pos1);
                offspring.remove(pos1);
                offspring.add(c1);

                // Calculate fitness of both individuals
                fit.fitnessCalculator(c1, rules);

            }

            //
            //
            //
            //
            //
            //
            // ======================================= RESET POPULATION =======================================

            // calculates offspring's total fitness
            for (Individual fitt : offspring) {
                totalFitness = fitt.getFitness() + totalFitness;
            }

            // REMOVE WORST AND ADD BEST
            // finds best fitness from population
            Individual best = in;
            int bestFitness = 0;
            for (Individual ind : population) {
                if (ind.getFitness() > bestFitness) {
                    bestFitness = ind.getFitness();
                    best = ind;
                }
            }

            // finds the worst fitness
            String p = "d";
            int low = 0;
            int worstFitness = 33;
            // find lowest fitness in offspring 
            for (int lo = 0; lo < offspring.size(); lo++) {
                if (offspring.get(lo).getFitness() < worstFitness) {
                    worstFitness = offspring.get(lo).getFitness();
                    p = offspring.get(lo).getGene();
                    low = lo;
                }
            }
            System.out.println("Lowest Fitness " + worstFitness);

            // Replace worst fitness with best from parent population
            offspring.remove(low);
            offspring.add(best);

            // Find the best individual in the offspring
            bestFitness = 0;
            for (Individual ind : offspring) {
                if (ind.getFitness() > bestFitness) {
                    bestFitness = ind.getFitness();
                    p = ind.gene;
                }
            }

            // Calculate the mean fitness
            int meanFitness = 0;
            meanFitness = totalFitness / ga.P;

            System.out.println("Best Individual: " + p + " f: " + bestFitness);
            System.out.println("Total Offspring Fitness: " + totalFitness + " Mean: " + meanFitness + " (Run "
                    + (j + 1) + ")\n");

            //export to excel
            Row row1 = sheet.createRow((short) rowNumber);
            row1.createCell(0).setCellValue((j + 1));
            row1.createCell(1).setCellValue(bestFitness);
            row1.createCell(2).setCellValue(meanFitness);
            rowNumber++;

            population.clear();

            //Move offspring into population
            for (int a = 0; a < offspring.size(); a++) {
                Individual transfer = offspring.get(a);
                population.add(transfer);
            }

            //Clear offspring
            offspring.clear();

        } else {
            for (Individual fitt : population) {

                System.out.println("gene = " + fitt.getGene() + " fit = " + fitt.getFitness());
            }

            //export to excel
            FileOutputStream fileOut = new FileOutputStream("GA_Results.xls");
            wb.write(fileOut);
            fileOut.close();

            System.out.println("\nComplete");
            System.exit(0);
        }
    }

    //export to excel
    FileOutputStream fileOut = new FileOutputStream("GA_Results.xls");
    wb.write(fileOut);
    fileOut.close();

}

From source file:guru.qas.martini.report.DefaultState.java

License:Apache License

@Override
public void updateSuites(Sheet sheet) {
    int lastRowNum = sheet.getLastRowNum();
    Row row = sheet.createRow(0 == lastRowNum ? 0 : lastRowNum + 1);
    row.createCell(0, CellType.STRING).setCellValue("ID");
    row.createCell(1, CellType.STRING).setCellValue("Date");
    row.createCell(2, CellType.STRING).setCellValue("Name");
    row.createCell(3, CellType.STRING).setCellValue("Hostname");
    row.createCell(4, CellType.STRING).setCellValue("IP");
    row.createCell(5, CellType.STRING).setCellValue("Username");
    row.createCell(6, CellType.STRING).setCellValue("Profiles");
    row.createCell(7, CellType.STRING).setCellValue("Environment Variables");

    for (Map.Entry<String, JsonObject> mapEntry : suites.entrySet()) {
        row = sheet.createRow(sheet.getLastRowNum() + 1);

        String id = mapEntry.getKey();
        row.createCell(0, CellType.STRING).setCellValue(id);

        JsonObject suite = mapEntry.getValue();
        JsonPrimitive primitive = suite.getAsJsonPrimitive("startTimestamp");
        Long timestamp = null == primitive ? null : primitive.getAsLong();

        Cell cell = row.createCell(1);//from w w w .  j a v a  2 s.co m
        if (null != timestamp) {
            Workbook workbook = sheet.getWorkbook();
            CellStyle cellStyle = workbook.createCellStyle();
            CreationHelper creationHelper = workbook.getCreationHelper();
            cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy h:mm"));
            cellStyle.setVerticalAlignment(VerticalAlignment.TOP);
            cell.setCellValue(new Date(timestamp));
            cell.setCellStyle(cellStyle);
        }

        cell = row.createCell(2);
        primitive = suite.getAsJsonPrimitive("name");
        cell.setCellValue(null == primitive ? "" : primitive.getAsString());

        cell = row.createCell(3);
        JsonObject host = suite.getAsJsonObject("host");
        primitive = null == host ? null : host.getAsJsonPrimitive("name");
        cell.setCellValue(null == primitive ? "" : primitive.getAsString());

        cell = row.createCell(4);
        primitive = null == host ? null : host.getAsJsonPrimitive("ip");
        cell.setCellValue(null == primitive ? "" : primitive.getAsString());

        cell = row.createCell(5);
        primitive = null == host ? null : host.getAsJsonPrimitive("username");
        cell.setCellValue(null == primitive ? "" : primitive.getAsString());

        cell = row.createCell(6);
        JsonArray array = suite.getAsJsonArray("profiles");
        List<String> profiles = Lists.newArrayList();
        if (null != array) {
            int size = array.size();
            for (int i = 0; i < size; i++) {
                JsonElement element = array.get(i);
                String profile = null == element ? null : element.getAsString();
                profiles.add(profile);
            }
            String profilesValue = Joiner.on('\n').skipNulls().join(profiles);
            cell.setCellValue(profilesValue);
        }

        cell = row.createCell(7);
        JsonObject environmentVariables = suite.getAsJsonObject("environment");
        Map<String, String> index = new TreeMap<>();
        if (null != environmentVariables) {
            Set<Map.Entry<String, JsonElement>> entries = environmentVariables.entrySet();
            for (Map.Entry<String, JsonElement> environmentEntry : entries) {
                String key = environmentEntry.getKey();
                JsonElement element = environmentEntry.getValue();
                String value = null == element ? "" : element.getAsString();
                index.put(key, value);
            }

            String variablesValue = Joiner.on('\n').withKeyValueSeparator('=').useForNull("").join(index);
            cell.setCellValue(variablesValue);
        }
    }

    for (int i = 0; i < 8; i++) {
        sheet.autoSizeColumn(i, false);
    }
}

From source file:hjow.hgtable.util.XLSXUtil.java

License:Apache License

/**
 * <p>XLSX ? ?  ?? . ?  ?  ?? ?? , ? ? ?? ?  ?  ?? ?.</p>
 * /*w  w  w .  j  a v a 2 s  . c o m*/
 * @param file : XLSX ?
 * @return ?  ? 
 */
public static List<TableSet> toTableSets(File file) {
    List<TableSet> tableSets = new Vector<TableSet>();

    org.apache.poi.ss.usermodel.Workbook workbook = null;

    if (file == null)
        throw new NullPointerException(Manager.applyStringTable("Please select file !!"));
    if (!file.exists())
        throw new NullPointerException(Manager.applyStringTable("File") + " " + file.getAbsolutePath() + " "
                + Manager.applyStringTable("is not exist"));

    boolean isHead = true;
    int rowNum = 0;
    int cellNum = 0;

    int cellCount = 0;

    FileInputStream fileStream = null;
    try {
        if (file.getAbsolutePath().endsWith(".xlsx") || file.getAbsolutePath().endsWith(".XLSX")) {
            workbook = new org.apache.poi.xssf.usermodel.XSSFWorkbook(file);
        } else if (file.getAbsolutePath().endsWith(".xls") || file.getAbsolutePath().endsWith(".XLS")) {
            fileStream = new FileInputStream(file);
            workbook = new org.apache.poi.hssf.usermodel.HSSFWorkbook(fileStream);
        }

        org.apache.poi.ss.usermodel.FormulaEvaluator evals = workbook.getCreationHelper()
                .createFormulaEvaluator();

        org.apache.poi.ss.usermodel.Sheet sheet = null;

        for (int x = 0; x < workbook.getNumberOfSheets(); x++) {
            TableSet newTableSet = new DefaultTableSet();
            newTableSet.setColumns(new Vector<Column>());

            sheet = workbook.getSheetAt(x);
            newTableSet.setName(sheet.getSheetName());

            rowNum = 0;
            isHead = true;

            String targetData = null;

            for (org.apache.poi.ss.usermodel.Row row : sheet) {
                cellNum = 0;
                for (org.apache.poi.ss.usermodel.Cell cell : row) {
                    try {
                        if (cellNum >= cellCount) {
                            throw new IndexOutOfBoundsException(
                                    Manager.applyStringTable("There are some cells not have their heads") + ", "
                                            + Manager.applyStringTable("Head count") + " : " + cellCount + ", "
                                            + Manager.applyStringTable("Cell Number") + " : " + cellNum);
                        }

                        switch (cell.getCellType()) {
                        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
                            if (isHead) {
                                newTableSet.getColumns().add(new Column(
                                        cell.getRichStringCellValue().getString(), Column.TYPE_STRING));
                            } else {
                                targetData = cell.getRichStringCellValue().getString();
                                newTableSet.getColumns().get(cellNum).setType(cell.getCellType());
                            }
                            break;
                        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
                            if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                                if (isHead) {
                                    newTableSet.getColumns().add(new Column(
                                            String.valueOf(cell.getStringCellValue()), Column.TYPE_DATE));
                                } else {
                                    targetData = String.valueOf(cell.getDateCellValue());
                                    newTableSet.getColumns().get(cellNum).setType(cell.getCellType());
                                }
                            } else {
                                if (isHead) {
                                    newTableSet.getColumns().add(new Column(
                                            String.valueOf(cell.getStringCellValue()), Column.TYPE_NUMERIC));
                                } else {
                                    double values = cell.getNumericCellValue();
                                    double intPart = values - ((double) ((int) values));
                                    if (intPart == 0.0) {
                                        targetData = String.valueOf(((int) values));
                                        newTableSet.getColumns().get(cellNum).setType(Column.TYPE_INTEGER);
                                    } else {
                                        targetData = String.valueOf(values);
                                        newTableSet.getColumns().get(cellNum).setType(cell.getCellType());
                                    }
                                }
                            }
                            break;
                        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN:
                            if (isHead) {
                                newTableSet.getColumns().add(new Column(
                                        String.valueOf(cell.getStringCellValue()), Column.TYPE_BOOLEAN));
                            } else {
                                targetData = String.valueOf(cell.getBooleanCellValue());
                                newTableSet.getColumns().get(cellNum).setType(cell.getCellType());
                            }
                            break;
                        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA:
                            if (isHead) {
                                newTableSet.getColumns().add(new Column(
                                        String.valueOf(cell.getStringCellValue()), Column.TYPE_NUMERIC));
                            } else {
                                if (evals.evaluateFormulaCell(cell) == 0) {
                                    targetData = String.valueOf(cell.getNumericCellValue());
                                    newTableSet.getColumns().get(cellNum).setType(Column.TYPE_NUMERIC);
                                } else if (evals.evaluateFormulaCell(cell) == 1) {
                                    targetData = String.valueOf(cell.getStringCellValue());
                                    newTableSet.getColumns().get(cellNum).setType(Column.TYPE_STRING);
                                } else if (evals.evaluateFormulaCell(cell) == 4) {
                                    targetData = String.valueOf(cell.getBooleanCellValue());
                                    newTableSet.getColumns().get(cellNum).setType(Column.TYPE_BOOLEAN);
                                } else {
                                    targetData = String.valueOf(cell.getCellFormula());
                                    newTableSet.getColumns().get(cellNum).setType(cell.getCellType());
                                }
                            }
                            break;
                        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK:
                            if (isHead) {
                                newTableSet.getColumns().add(new Column("", Column.TYPE_STRING));
                            } else {
                                targetData = "";
                                newTableSet.getColumns().get(cellNum).setType(Column.TYPE_BLANK);
                            }
                            break;
                        default:
                            if (isHead) {
                                newTableSet.getColumns().add(new Column("", Column.TYPE_STRING));
                            } else {
                                try {
                                    targetData = cell.getStringCellValue();
                                } catch (Exception e1) {
                                    e1.printStackTrace();
                                }
                                newTableSet.getColumns().get(cellNum).setType(cell.getCellType());
                            }
                            break;
                        }

                        if (isHead) {
                            cellCount++;
                        } else {
                            while (rowNum > 0
                                    && newTableSet.getColumns().get(cellNum).getData().size() < rowNum) {
                                newTableSet.getColumns().get(cellNum).getData().add("");
                            }
                            if (targetData != null)
                                newTableSet.getColumns().get(cellNum).getData().add(targetData);
                            else {
                                newTableSet.getColumns().get(cellNum).getData().add("");
                            }
                        }
                    } catch (ArrayIndexOutOfBoundsException e1) {
                        StringBuffer err = new StringBuffer("");
                        for (StackTraceElement errEl : e1.getStackTrace()) {
                            err = err.append("\t " + errEl + "\n");
                        }

                        String cellObject = null;
                        try {
                            cellObject = cell.getStringCellValue();
                        } catch (Exception e2) {

                        }

                        throw new ArrayIndexOutOfBoundsException(
                                Manager.applyStringTable("Array index out of range") + " <- "
                                        + Manager.applyStringTable("Reading xlsx file") + " : " + file.getName()
                                        + ", " + sheet.getSheetName() + "\n" + Manager.applyStringTable("On")
                                        + " " + Manager.applyStringTable("Row") + " " + rowNum + ", "
                                        + Manager.applyStringTable("Cell") + " " + cellNum + ", "
                                        + Manager.applyStringTable("Value") + " : " + String.valueOf(cellObject)
                                        + "\n " + Manager.applyStringTable("<-\n") + err + "\n "
                                        + Manager.applyStringTable("Original Message") + "...\n"
                                        + e1.getMessage() + "\n" + Manager.applyStringTable("End"));
                    }

                    cellNum++;
                }

                isHead = false;
                rowNum++;
            }

            fillTableSet(newTableSet);
            newTableSet.removeEmptyColumn(true);

            tableSets.add(newTableSet);
        }

        return tableSets;
    } catch (Throwable e) {
        if (Main.MODE >= DebuggingUtil.DEBUG)
            e.printStackTrace();
        Main.logError(e,
                Manager.applyStringTable("On reading xlsx") + " : " + file + "\n"
                        + Manager.applyStringTable("At rownum") + " " + rowNum + ", "
                        + Manager.applyStringTable("cellnum") + " " + cellNum);

        return null;
    } finally {
        try {
            workbook.close();
        } catch (Throwable e) {

        }
        try {
            if (fileStream != null)
                fileStream.close();
        } catch (Throwable e) {

        }
    }
}

From source file:hu.webhejj.commons.io.table.excel.ExcelTableReader.java

License:Apache License

public ExcelTableReader(Workbook workbook) {
    this.workbook = workbook;
    rowValueConverter = new ExcelRowValueConverter(workbook.getCreationHelper().createFormulaEvaluator());
}

From source file:Import.Utils.XSSFConvert.java

public void convert() throws IOException {
    Workbook[] wbs = new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() };
    for (int i = 0; i < wbs.length; i++) {
        Workbook wb = wbs[i];
        CreationHelper createHelper = wb.getCreationHelper();

        // create a new sheet
        org.apache.poi.ss.usermodel.Sheet s = wb.createSheet();
        // declare a row object reference
        Row r = null;//from  w w w . ja  v a 2 s  .  c om
        // declare a cell object reference
        Cell c = null;
        // create 2 cell styles
        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();
        DataFormat df = wb.createDataFormat();

        // create 2 fonts objects
        Font f = wb.createFont();
        Font f2 = wb.createFont();

        // Set font 1 to 12 point type, blue and bold
        f.setFontHeightInPoints((short) 12);
        f.setColor(IndexedColors.RED.getIndex());
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);

        // Set font 2 to 10 point type, red and bold
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.RED.getIndex());
        f2.setBoldweight(Font.BOLDWEIGHT_BOLD);

        // Set cell style and formatting
        cs.setFont(f);
        cs.setDataFormat(df.getFormat("#,##0.0"));

        // Set the other cell style and formatting
        cs2.setBorderBottom(cs2.BORDER_THIN);
        cs2.setDataFormat(df.getFormat("text"));
        cs2.setFont(f2);

        // Define a few rows
        for (int rownum = 0; rownum < 30; rownum++) {
            r = s.createRow(rownum);
            for (int cellnum = 0; cellnum < 10; cellnum += 2) {
                c = r.createCell(cellnum);
                Cell c2 = r.createCell(cellnum + 1);

                c.setCellValue((double) rownum + (cellnum / 10));
                c2.setCellValue(createHelper.createRichTextString("Hello! " + cellnum));
            }
        }

        // Save
        String filename = "workbook.xls";
        if (wb instanceof XSSFWorkbook) {
            filename = filename + "x";
        }

        FileOutputStream out = null;
        try {
            out = new FileOutputStream(filename);
        } catch (FileNotFoundException ex) {
            Logger.getLogger(XSSFConvert.class.getName()).log(Level.SEVERE, null, ex);
        }
        wb.write(out);
        out.close();
    }
}

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);//ww  w.j  a  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);// ww w  . ja  va  2  s. c  om
    }
    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 exportToXLS(WorkSheetXLSExporter exporter, JSONObject worksheetJSON,
        JSONArray metadataPropertiesJSON, JSONArray parametersJSON, OutputStream stream) throws Exception {

    int sheetsNumber = worksheetJSON.getInt(SHEETS_NUM);

    Workbook wb = exporter.createNewWorkbook();

    CreationHelper createHelper = wb.getCreationHelper();

    if (metadataPropertiesJSON != null && metadataPropertiesJSON.length() > 0) {
        exportMetadataToXLS(wb, exporter, createHelper, metadataPropertiesJSON, parametersJSON);
    }//from  www. ja v  a 2s . c o m

    JSONArray exportedSheets = worksheetJSON.getJSONArray(EXPORTED_SHEETS);

    JSONArray fieldOptions = WorkSheetSerializationUtils.getFieldOptions(worksheetJSON);

    for (int i = 0; i < sheetsNumber; i++) {
        // sheets could be null if containing charts in massive export
        if (!exportedSheets.isNull(i)) {
            logger.debug("found non empty sheet at index " + i);
            JSONObject sheetJ = exportedSheets.getJSONObject(i);
            JSONObject optionalFilters = sheetJ.optJSONObject(QbeEngineStaticVariables.FILTERS);
            String sheetName = sheetJ.getString(SHEET);
            List<WhereField> splittingWF = getSplittingFieldValues(optionalFilters, sheetName);
            WhereField splittingWhereField = null;
            if (splittingWF == null || splittingWF.size() == 0) {
                exportSheetToXLS(wb, sheetJ, fieldOptions, exporter, createHelper, splittingWhereField);
            } else {
                for (int y = 0; y < splittingWF.size(); y++) {
                    splittingWhereField = splittingWF.get(y);
                    exportSheetToXLS(wb, sheetJ, fieldOptions, exporter, createHelper, splittingWhereField);
                }
            }
        } else {
            logger.debug("found empty sheet at index " + i);

        }
    }

    wb.write(stream);
    stream.flush();
}