Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. getSheetAt

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt.

Prototype

@Override
public XSSFSheet getSheetAt(int index)

Source Link

Document

Get the XSSFSheet object at the given index.

Usage

From source file:onboardsoftware.ReadXLSX.java

public ArrayList<Task> getTasks(String tabela) {
    ArrayList<Task> tasks = new ArrayList<Task>();
    FileInputStream spreadSheet = null;
    try {//from w  w w  .j a  v a2s.c o m
        File file = new File(tabela);
        spreadSheet = new FileInputStream(file);
        try {
            //planilha com todas as abas
            XSSFWorkbook workbook = new XSSFWorkbook(spreadSheet);
            //primeira planilha
            XSSFSheet sheet = workbook.getSheetAt(0);
            //todas as linhas da planilha[0]
            Iterator<Row> rowIterator = sheet.iterator();
            //varre todas as linhas da planilha[0]
            while (rowIterator.hasNext()) {
                //pegando cada linha
                Row row = rowIterator.next();
                //todas as celulas de cada linha
                Iterator<Cell> cellIterator = row.iterator();
                //varre todas as celulas
                System.out.println();
                ArrayList<Cell> list = new ArrayList<Cell>();
                while (cellIterator.hasNext()) {
                    //minha celula
                    Cell cell = cellIterator.next();
                    list.add(cell);
                }
                Task task = new Task(list);

                tasks.add(task);
            }
        } catch (IOException ex) {
            Logger.getLogger(ReadXLSX.class.getName()).log(Level.SEVERE, null, ex);
        }
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ReadXLSX.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            spreadSheet.close();
        } catch (IOException ex) {
            Logger.getLogger(ReadXLSX.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    return tasks;
}

From source file:Opm_Package.OpenFileName.java

public List<String> readReposNames(String file) throws Exception {
    int x = 0;/*from ww  w . j  a  va  2 s.c  o m*/
    OpenFileName fname = new OpenFileName();
    // array list to store the Repos names
    ArrayList<String> list = new ArrayList<String>();
    //calling the file name.....
    XSSFWorkbook workbook = readFileName(file);
    // setting the sheet number...
    XSSFSheet spreadsheet = workbook.getSheetAt(x);
    String sname = workbook.getSheetName(x);

    Row row;
    Cell cell = null;
    for (int j = 0; j < spreadsheet.getLastRowNum() + 1; ++j) {//To loop thru the rows in a sheet
        row = spreadsheet.getRow(j);
        cell = row.getCell(0); //forks are in the eighth column...
        switch (cell.getCellType()) {
        //Checking for strings values inthe cells..
        case Cell.CELL_TYPE_STRING:
            if (!cell.getStringCellValue().equals("")) {
                // adding the call value to the arraylist called forksList 
                list.add(cell.getStringCellValue());
            } //end of if statement...
            break;
        //Checking for numeric values inthe cells..
        case Cell.CELL_TYPE_NUMERIC:
            list.add(String.valueOf(cell.getNumericCellValue()));
            break;
        //Checking for bank in the cells..
        case Cell.CELL_TYPE_BLANK:
            break;
        }//end of switch statement

    } // end of  for loop for the rows..

    //returns the arraylist to the main class....
    return list;
}

From source file:org.addition.epanet.network.io.input.ExcelParser.java

License:Open Source License

@Override
public Network parse(Network net, File f) throws ENException {
    FileInputStream stream = null;
    try {//w  w  w  . j ava 2 s . c om
        stream = new FileInputStream(f);
        XSSFWorkbook workbook = new XSSFWorkbook(stream);

        findTimeStyle(workbook);

        Pattern tagPattern = Pattern.compile("\\[.*\\]");
        int errSum = 0;

        List<XSSFSheet> sheetPC = new ArrayList<XSSFSheet>();
        List<XSSFSheet> sheetOthers = new ArrayList<XSSFSheet>();
        List<XSSFSheet> sheetNodes = new ArrayList<XSSFSheet>();
        List<XSSFSheet> sheetTanks = new ArrayList<XSSFSheet>();

        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            XSSFSheet sh = workbook.getSheetAt(i);
            if (sh.getSheetName().equalsIgnoreCase("Patterns")
                    || sh.getSheetName().equalsIgnoreCase("Curves")) {
                sheetPC.add(sh);
            } else if (sh.getSheetName().equals("Junctions"))
                sheetNodes.add(sh);
            else if (sh.getSheetName().equals("Tanks") || sh.getSheetName().equals("Reservoirs"))
                sheetTanks.add(sh);
            else
                sheetOthers.add(sh);

        }
        errSum = parseWorksheet(net, sheetPC, tagPattern, errSum); // parse the patterns and curves
        errSum = parseWorksheet(net, sheetNodes, tagPattern, errSum); // parse the nodes
        errSum = parseWorksheet(net, sheetTanks, tagPattern, errSum); // parse the nodes
        errSum = parseWorksheet(net, sheetOthers, tagPattern, errSum); // parse other elements

        if (errSum != 0)
            throw new ENException(200);

        stream.close();

    } catch (IOException e) {
        throw new ENException(302);
    }

    adjust(net);
    net.getFieldsMap().prepare(net.getPropertiesMap().getUnitsflag(), net.getPropertiesMap().getFlowflag(),
            net.getPropertiesMap().getPressflag(), net.getPropertiesMap().getQualflag(),
            net.getPropertiesMap().getChemUnits(), net.getPropertiesMap().getSpGrav(),
            net.getPropertiesMap().getHstep());

    convert(net);
    return net;
}

From source file:org.apache.commons.g.QueryStorePicUI.java

/**
 * ??/*from   w w  w. ja v  a  2s .  c o  m*/
 * @param path
 * @throws Exception
 */
public void queryStorePic(String filePath, String outPath) throws Exception {

    final MyDialog dialog = new MyDialog(mContext, " ...");

    final List<String> noPics = new ArrayList<String>();
    final List<String> noStores = new ArrayList<String>();

    InputStream in = new FileInputStream(filePath);
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(in);
    final XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
    int rows = xssfSheet.getLastRowNum();
    if (rows > 100) {
        rows = 100;
    }

    System.out.println();
    System.out.println("  " + rows + "...");
    dialog.setWorkText("  " + rows + "...");

    HashMap<String, String> headers = new HashMap<String, String>();
    headers.put("Content-Type", "application/json");
    headers.put("Cookie", Config.JSESSIONID);
    headers.put("Referer", "https://homesis.homecredit.cn/hsis/index.html");

    final XSSFCell cell0 = xssfSheet.getRow(0).createCell(0);
    cell0.setCellValue("");

    System.out.println("?? |  | ? |    ??    | ??");
    for (int i = 1; i <= rows; i++) {
        final XSSFRow xssfRow = xssfSheet.getRow(i);

        if (xssfRow == null)
            continue;

        // ??
        String visitName = XssfUtil.getCellValue(xssfRow.getCell(0));
        if (TextUtils.isEmpty(visitName))
            continue;

        // ?
        final String storeCode = XssfUtil.getCellValue(xssfRow.getCell(3));
        final String data = "{\"code\":\"" + storeCode
                + "\",\"name\":null,\"licenseNumber\":null,\"salesDistrictId\":null,\"offset\":0,\"limit\":15}";

        // ??
        final String storeName = XssfUtil.getCellValue(xssfRow.getCell(10));

        final XSSFCell cell = xssfRow.createCell(0);

        String space2 = "     ";
        // ??2
        if (storeName.length() < 20)
            space2 += Util.getFormateSpace(20 - storeName.length());
        ;

        String space = "     ";
        // ??2
        if (visitName.length() == 3)
            space = space.substring(2);
        // ?????
        if (i < 10) {
            System.out.print(
                    "    " + i + "      " + visitName + space + storeCode + "        " + storeName + space2);
        } else {
            System.out.print(
                    "    " + i + "     " + visitName + space + storeCode + "        " + storeName + space2);
        }
        dialog.setWorkText(" " + storeCode + "-" + storeName);
        Thread.sleep(1234);

        String store = null;
        boolean run = true;
        while (run) {
            try {
                // ?
                HttpPost seachStore = mHttpUtil
                        .doPost("https://homesis.homecredit.cn/hsis/api/salesroom/search", headers, data);
                store = mHttpUtil.executeForBody(seachStore);
                //System.out.println(store);
                run = false;
            } catch (Exception e) {
                System.out.print("/");
            }
        }

        if (store.contains("HTTP/1.1 500 Internal Server Error")) {
            System.out.println("?(?)");
            break;
        }
        JSONObject json = null;
        try {
            json = JSONObject.parseObject(store);
        } catch (Exception e) {
            System.out.println(e.getMessage());
            System.out.println(store);
            continue;
        }
        if (json == null) {
            System.out.println("?");
            continue;
        }

        JSONArray array = json.getJSONArray("data");
        if (array.size() == 0) {
            noStores.add(storeCode);
            cell.setCellValue("?");
            System.out.println("?");
            continue;
        }
        JSONObject obj = (JSONObject) array.get(0);

        //  ID
        int id = obj.getIntValue("id");
        //System.out.println(id);

        String storePic = null;
        run = true;
        while (run) {
            try {
                // 
                HttpGet seachPic = mHttpUtil
                        .doGet("https://homesis.homecredit.cn/hsis/api/document/salesroom/all/" + id, headers);
                storePic = mHttpUtil.executeForBody(seachPic);
                //                    System.out.println(storePic);
                run = false;
            } catch (Exception e) {
                System.out.print("/");
            }
        }

        json = JSONObject.parseObject(storePic);
        array = json.getJSONArray("documents");
        if (array.size() == 0) {
            noPics.add(storeCode);
            cell.setCellValue("");
            System.out.println("");
            continue;
        }

        boolean print = true;
        for (int j = 0; j < array.size(); j++) {
            obj = (JSONObject) array.get(j);
            //  ID
            int picid = obj.getIntValue("id");
            String fileName = obj.getString("fileName");
            if (fileName.contains("POSRepID"))
                continue;
            //                fileName = fileName.substring(0, fileName.indexOf("-"));//+fileName.substring(fileName.indexOf("."));
            run = true;
            while (run) {
                try {
                    // 
                    HttpGet down = mHttpUtil.doGet(
                            "https://homesis.homecredit.cn/hsis/api/document/download/" + picid, headers);
                    String result = mHttpUtil.downPic(down, Util
                            .mkdirsIfNeed(outPath + File.separator + visitName, storeCode + "-" + fileName));
                    if (print) {
                        print = false;
                        System.out.println(fileName.substring(0, fileName.indexOf("-")));
                    }
                    run = false;
                } catch (Exception e) {
                    System.out.println();
                    e.printStackTrace();
                    System.out.println();
                    System.out.println("?/");
                    System.out.println();
                }
            }
        }
    }
    xssfWorkbook.write(new FileOutputStream(filePath.replace(".xlsx", "_pic.xlsx")));
    xssfWorkbook.close();
    xssfWorkbook = null;

    in.close();
    in = null;

    if (noStores.size() > 0) {
        System.out.println();
        System.out.println();
        System.out.println("??");
        System.out.println(
                "");
        for (int k = 0; k < noStores.size(); k++) {
            System.out.println(noStores.get(k));
        }
    }

    if (noPics.size() > 0) {
        System.out.println();
        System.out.println();
        System.out.println("?");
        System.out.println(
                "");
        for (int k = 0; k < noPics.size(); k++) {
            System.out.println(noPics.get(k));
        }
    }

    dialog.dispose(2);
}

From source file:org.apache.ofbiz.pricat.sample.SamplePricatParser.java

License:Apache License

/**
 * Parse pricat excel file in xlsx format.
 * /*from w ww  .  ja  v a2 s . c  o  m*/
 */
public void parsePricatExcel(boolean writeFile) {
    XSSFWorkbook workbook = null;
    try {
        // 1. read the pricat excel file
        FileInputStream is = new FileInputStream(pricatFile);

        // 2. use POI to load this bytes
        report.print(UtilProperties.getMessage(resource, "ParsePricatFileStatement",
                new Object[] { pricatFile.getName() }, locale), InterfaceReport.FORMAT_DEFAULT);
        try {
            workbook = new XSSFWorkbook(is);
            report.println(UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK);
        } catch (IOException e) {
            report.println(e);
            report.println(UtilProperties.getMessage(resource, "PricatSuggestion", locale),
                    InterfaceReport.FORMAT_ERROR);
            return;
        } catch (POIXMLException e) {
            report.println(e);
            report.println(UtilProperties.getMessage(resource, "PricatSuggestion", locale),
                    InterfaceReport.FORMAT_ERROR);
            return;
        }

        // 3. only first sheet will be parsed
        // 3.1 verify the file has a sheet at least
        formatter = new HSSFDataFormatter(locale);
        isNumOfSheetsOK(workbook);

        // 3.2 verify the version is supported
        XSSFSheet sheet = workbook.getSheetAt(0);
        if (!isVersionSupported(sheet)) {
            return;
        }

        // 3.3 get currencyId
        existsCurrencyId(sheet);

        // 3.4 verify the table header row is just the same as column names, if not, print error and return
        if (!isTableHeaderMatched(sheet)) {
            return;
        }

        // 3.5 verify the first table has 6 rows at least
        containsDataRows(sheet);

        if (UtilValidate.isNotEmpty(errorMessages)) {
            report.println(UtilProperties.getMessage(resource, "HeaderContainsError", locale),
                    InterfaceReport.FORMAT_ERROR);
            return;
        }

        // 4. parse data
        // 4.1 parse row by row and store the contents into database
        parseRowByRow(sheet);
        if (UtilValidate.isNotEmpty(errorMessages)) {
            report.println(UtilProperties.getMessage(resource, "DataContainsError", locale),
                    InterfaceReport.FORMAT_ERROR);
            if (writeFile) {
                sequenceNum = report.getSequenceNum();
                writeCommentsToFile(workbook, sheet);
            }
        }

        // 5. clean up the log files and commented Excel files
        cleanupLogAndCommentedExcel();
    } catch (IOException e) {
        report.println(e);
        Debug.logError(e, module);
    } finally {
        if (UtilValidate.isNotEmpty(fileItems)) {
            // remove tmp files
            FileItem fi = null;
            for (int i = 0; i < fileItems.size(); i++) {
                fi = fileItems.get(i);
                fi.delete();
            }
        }
        if (workbook != null) {
            try {
                workbook.close();
            } catch (IOException e) {
                Debug.logError(e, module);
            }
        }
    }
}

From source file:org.apache.solr.handler.extraction.TestXLSXResponseWriter.java

License:Apache License

private XSSFSheet getWSResultForQuery(SolrQueryRequest req, SolrQueryResponse rsp)
        throws IOException, Exception {
    ByteArrayOutputStream xmlBout = new ByteArrayOutputStream();
    writerXlsx.write(xmlBout, req, rsp);
    XSSFWorkbook output = new XSSFWorkbook(new ByteArrayInputStream(xmlBout.toByteArray()));
    XSSFSheet sheet = output.getSheetAt(0);
    req.close();/*from   w  ww . j  a  v  a 2  s . c o m*/
    output.close();
    return sheet;
}

From source file:org.azkfw.datasource.excel.ExcelDatasourceBuilder.java

License:Apache License

/**
 * ?/*from   ww w. j a va2 s.c  o m*/
 * 
 * @return 
 * @throws FileNotFoundException
 * @throws ParseException
 * @throws IOException
 */
@SuppressWarnings({ "unchecked", "rawtypes" })
public Datasource build() throws FileNotFoundException, ParseException, IOException {
    ExcelDatasource datasource = new ExcelDatasource();
    datasource.name = datasourceName;

    InputStream stream = null;
    try {
        List<Table> tables = new ArrayList<>();

        for (File file : excelFiles) {

            stream = new FileInputStream(file);
            XSSFWorkbook workbook = new XSSFWorkbook(stream);
            int cntSheet = workbook.getNumberOfSheets();
            for (int i = 0; i < cntSheet; i++) {
                String sheetName = workbook.getSheetName(i); // sheet name -> table name

                ExcelTable table = new ExcelTable();

                Matcher matcher = PTN_TABLE_NAME.matcher(sheetName);
                if (matcher.find()) {
                    table.label = matcher.group(3);
                    table.name = matcher.group(1);
                } else {
                    table.label = sheetName;
                    table.name = sheetName;
                }

                XSSFSheet sheet = workbook.getSheetAt(i);
                // Check row size
                int cntRow = sheet.getLastRowNum() + 1;
                if (3 > cntRow) {
                    System.out.println("Skip sheet[" + sheetName + "]. row size < 3");
                    continue;
                }

                // Read Field
                List<ExcelField> fields = new ArrayList<ExcelField>();
                XSSFRow rowLabel = sheet.getRow(0);
                XSSFRow rowName = sheet.getRow(1);
                XSSFRow rowType = sheet.getRow(2);
                for (int col = 0; col < rowLabel.getLastCellNum(); col++) {
                    ExcelField field = readField(col, rowLabel.getCell(col), rowName.getCell(col),
                            rowType.getCell(col));
                    fields.add(field);
                }

                // Read Data
                List<ExcelRecord> records = new ArrayList<ExcelRecord>();
                for (int row = 3; row < cntRow; row++) {
                    XSSFRow xssfrow = sheet.getRow(row);
                    if (!isEmptyRow(xssfrow)) {
                        ExcelRecord record = readData(row, xssfrow, fields);
                        records.add(record);
                    } else {
                        System.out
                                .println("Skip empty row.[table: " + table.getName() + "; row: " + row + ";]");
                    }
                }

                table.fields = (List) fields;
                table.records = (List) records;

                tables.add(table);
            }
        }

        datasource.tables = tables;

    } catch (FileNotFoundException ex) {
        throw ex;
    } catch (ParseException ex) {
        throw ex;
    } catch (IOException ex) {
        throw ex;
    } finally {
        if (null != stream) {
            try {
                stream.close();
            } catch (IOException ex) {
            } finally {
                stream = null;
            }
        }
    }

    return datasource;
}

From source file:org.azkfw.datasource.excel.ExcelDatasourceFactory.java

License:Apache License

/**
 * Excel???/*from  w  w w.j a va 2s  .  co  m*/
 * 
 * @param aName ??
 * @param aStream Excel
 * @return 
 */
@SuppressWarnings({ "unchecked", "rawtypes" })
public static Datasource generate(final String aName, final InputStream aStream)
        throws FileNotFoundException, ParseException, IOException {
    ExcelDatasource datasource = new ExcelDatasource();
    datasource.name = aName;

    try {
        List<Table> tables = new ArrayList<>();

        XSSFWorkbook workbook = new XSSFWorkbook(aStream);
        int cntSheet = workbook.getNumberOfSheets();
        for (int i = 0; i < cntSheet; i++) {
            String sheetName = workbook.getSheetName(i); // sheet name -> table name

            ExcelTable table = new ExcelTable();

            Matcher matcher = PTN_TABLE_NAME.matcher(sheetName);
            if (matcher.find()) {
                table.label = matcher.group(3);
                table.name = matcher.group(1);
            } else {
                table.label = sheetName;
                table.name = sheetName;
            }

            XSSFSheet sheet = workbook.getSheetAt(i);
            // Check row size
            int cntRow = sheet.getLastRowNum() + 1;
            if (3 > cntRow) {
                System.out.println("Skip sheet[" + sheetName + "]. row size < 3");
                continue;
            }

            // Read Field
            List<ExcelField> fields = new ArrayList<ExcelField>();
            XSSFRow rowLabel = sheet.getRow(0);
            XSSFRow rowName = sheet.getRow(1);
            XSSFRow rowType = sheet.getRow(2);
            for (int col = 0; col < rowLabel.getLastCellNum(); col++) {
                ExcelField field = readField(col, rowLabel.getCell(col), rowName.getCell(col),
                        rowType.getCell(col));
                fields.add(field);
            }

            // Read Data
            List<ExcelRecord> records = new ArrayList<ExcelRecord>();
            for (int row = 3; row < cntRow; row++) {
                XSSFRow xssfrow = sheet.getRow(row);
                if (!isEmptyRow(xssfrow)) {
                    ExcelRecord record = readData(row, xssfrow, fields);
                    records.add(record);
                } else {
                    System.out.println("Skip empty row.[table: " + table.getName() + "; row: " + row + ";]");
                }
            }

            table.fields = (List) fields;
            table.records = (List) records;

            tables.add(table);
        }

        datasource.tables = tables;

    } catch (FileNotFoundException ex) {
        throw ex;
    } catch (ParseException ex) {
        throw ex;
    } catch (IOException ex) {
        throw ex;
    } finally {
        if (null != aStream) {
            try {
                aStream.close();
            } catch (IOException ex) {
            }
        }
    }

    return datasource;
}

From source file:org.cgiar.ccafs.ap.summaries.projects.xlsx.BudgetByMOGSummaryXLS.java

License:Open Source License

/**
 * This method is used to generate the csv file for the ProjectLeading institutions.
 * //w w w .  j  av a  2 s. c  om
 * @param projectPartnerInstitutions is the list of institutions to be added
 * @param projectList is the list with the projects related to each institution
 */
public byte[] generateXLS(List<Map<String, Object>> informationBudgetReportByMOGDetail,
        List<Map<String, Object>> informationBudgetReportByMOG) {

    try {

        XSSFWorkbook workbook = xls.initializeWorkbook(true);

        /***************** Budget Summary By MOG Report ******************/
        // Writting headers
        String[] headersPOWB = new String[] { "Outcome 2019", "MOG", "Total Budget W1/W2 (USD)",
                "Gender W1/W2 (USD)", "Total Budget W3/Bilateral (USD)", "Gender W3/Bilateral (USD)" };

        // defining header types.
        int[] headerTypesPOWB = new int[] { BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG,
                BaseXLS.COLUMN_TYPE_BUDGET, BaseXLS.COLUMN_TYPE_BUDGET, BaseXLS.COLUMN_TYPE_BUDGET,
                BaseXLS.COLUMN_TYPE_BUDGET };

        // creating sheet
        Sheet[] sheets = new Sheet[2];
        sheets[0] = workbook.getSheetAt(0);
        sheets[1] = workbook.cloneSheet(0);

        workbook.setSheetName(0, "Level - 1 ");

        xls.initializeSheet(sheets[0], headerTypesPOWB);

        xls.writeHeaders(sheets[0], headersPOWB);
        this.addContent(informationBudgetReportByMOG, sheets[0], 0, workbook);

        // Set description
        xls.writeDescription(sheets[0], xls.getText("summaries.budget.summary.sheet.description"));

        // write text box
        xls.writeTitleBox(sheets[0], " \t\t Budget Summary");

        // write text box
        xls.createLogo(workbook, sheets[0]);

        /***************** Budget Summary By MOG Detail ******************/
        // Sheet cleanSheet =
        // Writting headers

        String[] headersPOWBDetail = new String[] { "Project Id", "Project title", "Project type", "MOG",
                "Expected annual contribution", "Expected plan of the gender and social inclusion",
                "Total Budget W1/W2 (USD)", " Gender W1/W2 (USD)", "Total Budget W3/Bilateral (USD)",
                "Gender W3/Bilateral (USD)" };

        // defining header types.
        int[] headerTypesPOWBDetail = new int[] { BaseXLS.COLUMN_TYPE_HYPERLINK, BaseXLS.COLUMN_TYPE_TEXT_LONG,
                BaseXLS.COLUMN_TYPE_TEXT_SHORT, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG,
                BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_BUDGET, BaseXLS.COLUMN_TYPE_BUDGET,
                BaseXLS.COLUMN_TYPE_BUDGET, BaseXLS.COLUMN_TYPE_BUDGET };

        workbook.setSheetName(1, "Level - 2");

        xls.initializeSheet(sheets[1], headerTypesPOWBDetail);

        xls.writeHeaders(sheets[1], headersPOWBDetail);
        this.addContent(informationBudgetReportByMOGDetail, sheets[1], 1, workbook);

        // Set description
        xls.writeDescription(sheets[1], xls.getText("summaries.budget.summary.sheet.description"));

        // write text box
        xls.writeTitleBox(sheets[1], "      Budget Summary Detail");

        // write text box
        xls.createLogo(workbook, sheets[1]);

        // this.flush();
        xls.writeWorkbook();

        byte[] byteArray = xls.getBytes();

        // Closing streams.
        xls.closeStreams();

        return byteArray;

    } catch (IOException e) {
        e.printStackTrace();
    }
    return null;
}

From source file:org.cgiar.ccafs.ap.summaries.projects.xlsx.DeliverablePlanningSummaryXLS.java

License:Open Source License

/**
 * This method is used to add an institution being a project leader
 * /*from w  w w  .j  ava 2 s . co  m*/
 * @param projectLeadingInstitutions is the list of institutions to be added
 * @param projectList is the list with the projects related to each institution
 */

private void addContent(List<Map<String, Object>> deliverableList, XSSFWorkbook workbook) {

    XSSFHyperlink link;
    Sheet sheet = workbook.getSheetAt(0);
    CreationHelper createHelper = workbook.getCreationHelper();
    Map<String, Object> deliverableMap;
    int projectID;

    // Main Type
    StringBuilder stringBuilder;

    // Iterating all the projects
    for (int a = 0; a < deliverableList.size(); a++) {
        deliverableMap = deliverableList.get(a);

        projectID = (int) deliverableMap.get("project_id");
        link = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL);
        link.setAddress(config.getBaseUrl() + "/planning/projects/description.do?projectID=" + projectID);

        // Project id
        xls.writeHyperlink(sheet, "P" + String.valueOf(projectID), link);
        xls.nextColumn();

        // Title
        xls.writeString(sheet, (String) deliverableMap.get("project_title"));
        xls.nextColumn();

        // Flashig
        xls.writeString(sheet, (String) deliverableMap.get("flagships"));
        xls.nextColumn();

        // Region
        xls.writeString(sheet, (String) deliverableMap.get("regions"));
        xls.nextColumn();

        // Title
        xls.writeString(sheet, this.messageReturn((String) deliverableMap.get("deliverable_title")));
        xls.nextColumn();

        // MOG
        xls.writeString(sheet, this.messageReturn((String) deliverableMap.get("mog")));
        xls.nextColumn();

        // Year
        xls.writeInteger(sheet, (int) deliverableMap.get("year"));
        xls.nextColumn();

        // Main type
        xls.writeString(sheet, this.messageReturn((String) deliverableMap.get("deliverable_type")));
        xls.nextColumn();

        // Sub Type
        int deliverableTypeId = (int) deliverableMap.get("deliverable_type_id");
        stringBuilder = new StringBuilder();
        if (deliverableTypeId == 38) {
            stringBuilder.append("Other: (");
            stringBuilder.append(this.messageReturn((String) deliverableMap.get("other_type")));
            stringBuilder.append(")");
        } else {
            stringBuilder.append(this.messageReturn((String) deliverableMap.get("deliverable_sub_type")));
        }
        xls.writeString(sheet, stringBuilder.toString());
        xls.nextColumn();

        // Partner Responsible
        xls.writeString(sheet, (String) deliverableMap.get("partner_responsible"));
        xls.nextColumn();

        // Other Partner
        xls.writeString(sheet, (String) deliverableMap.get("other_responsibles"));
        xls.nextColumn();

        xls.nextRow();

    }
}