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

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

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

Introduction

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

Prototype

@Override
public XSSFSheet getSheet(String name)

Source Link

Document

Get sheet with the given name (case insensitive match)

Usage

From source file:uk.ac.bbsrc.tgac.miso.spring.util.FormUtils.java

License:Open Source License

public static void createSampleExportForm(File outpath, JSONArray jsonArray) throws Exception {
    InputStream in = null;/*w  ww.j  a v a  2s. c  om*/
    in = FormUtils.class.getResourceAsStream("/forms/ods/export_samples.xlsx");
    if (in != null) {
        XSSFWorkbook oDoc = new XSSFWorkbook(in);

        XSSFSheet sheet = oDoc.getSheet("samples_export");
        FileOutputStream fileOut = new FileOutputStream(outpath);
        int i = 5;
        for (JSONObject jsonObject : (Iterable<JSONObject>) jsonArray) {
            if ("sampleinwell".equals(jsonObject.getString("name"))) {
                String sampleinwell = jsonObject.getString("value");
                // "sampleid:wellid:samplealias:projectname:projectalias:dnaOrRNA"
                String sampleId = sampleinwell.split(":")[0];
                String wellId = sampleinwell.split(":")[1];
                String sampleAlias = sampleinwell.split(":")[2];
                String projectName = sampleinwell.split(":")[3];
                String projectAlias = sampleinwell.split(":")[4];
                String dnaOrRNA = sampleinwell.split(":")[5];
                XSSFRow row = sheet.createRow(i);
                XSSFCell cellA = row.createCell(0);
                cellA.setCellValue(projectName);
                XSSFCell cellB = row.createCell(1);
                cellB.setCellValue(projectAlias);
                XSSFCell cellC = row.createCell(2);
                cellC.setCellValue(sampleId);
                XSSFCell cellD = row.createCell(3);
                cellD.setCellValue(sampleAlias);
                XSSFCell cellE = row.createCell(4);
                cellE.setCellValue(wellId);
                XSSFCell cellG = row.createCell(6);
                XSSFCell cellH = row.createCell(7);
                XSSFCell cellI = row.createCell(8);
                XSSFCell cellL = row.createCell(11);
                if ("R".equals(dnaOrRNA)) {
                    cellG.setCellValue("NA");
                    cellL.setCellFormula("1000/H" + (i + 1));
                } else if ("D".equals(dnaOrRNA)) {
                    cellH.setCellValue("NA");
                    cellI.setCellValue("NA");
                    cellL.setCellFormula("1000/G" + (i + 1));
                }
                XSSFCell cellM = row.createCell(12);
                cellM.setCellFormula("50-L" + (i + 1));
                i++;
            }
        }
        oDoc.write(fileOut);
        fileOut.close();
    } else {
        throw new IOException("Could not read from resource.");
    }

}

From source file:uk.ac.bbsrc.tgac.miso.spring.util.FormUtils.java

License:Open Source License

public static void createLibraryPoolExportFormFromWeb(File outpath, JSONArray jsonArray, String indexFamily)
        throws Exception {
    InputStream in = null;//from  w w  w.  j av a  2s. c  om
    in = FormUtils.class.getResourceAsStream("/forms/ods/export_libraries_pools.xlsx");
    if (in != null) {
        XSSFWorkbook oDoc = new XSSFWorkbook(in);

        XSSFSheet sheet = oDoc.getSheet("library_pool_export");
        FileOutputStream fileOut = new FileOutputStream(outpath);
        XSSFRow row2 = sheet.getRow(1);

        int i = 6;
        for (JSONObject jsonObject : (Iterable<JSONObject>) jsonArray) {
            if ("paired".equals(jsonObject.getString("name"))) {
                XSSFCell row2cellA = row2.createCell(0);
                row2cellA.setCellValue(jsonObject.getString("value"));
            } else if ("platform".equals(jsonObject.getString("name"))) {
                XSSFCell row2cellB = row2.createCell(1);
                row2cellB.setCellValue(jsonObject.getString("value"));
            } else if ("type".equals(jsonObject.getString("name"))) {
                XSSFCell row2cellC = row2.createCell(2);
                row2cellC.setCellValue(jsonObject.getString("value"));
            } else if ("selection".equals(jsonObject.getString("name"))) {
                XSSFCell row2cellD = row2.createCell(3);
                row2cellD.setCellValue(jsonObject.getString("value"));
            } else if ("strategy".equals(jsonObject.getString("name"))) {
                XSSFCell row2cellE = row2.createCell(4);
                row2cellE.setCellValue(jsonObject.getString("value"));
            }
            if ("sampleinwell".equals(jsonObject.getString("name"))) {
                String sampleinwell = jsonObject.getString("value");
                // "sampleid:wellid:samplealias:projectname:projectalias:dnaOrRNA"
                String sampleId = sampleinwell.split(":")[0];
                String wellId = sampleinwell.split(":")[1];
                String sampleAlias = sampleinwell.split(":")[2];
                String projectName = sampleinwell.split(":")[3];
                String projectAlias = sampleinwell.split(":")[4];
                XSSFRow row = sheet.createRow(i);
                XSSFCell cellA = row.createCell(0);
                cellA.setCellValue(projectName);
                XSSFCell cellB = row.createCell(1);
                cellB.setCellValue(projectAlias);
                XSSFCell cellC = row.createCell(2);
                cellC.setCellValue(sampleId);
                XSSFCell cellD = row.createCell(3);
                cellD.setCellValue(sampleAlias);
                XSSFCell cellE = row.createCell(4);
                cellE.setCellValue(wellId);
                if (indexFamily != null) {
                    XSSFCell cellJ = row.createCell(9);
                    cellJ.setCellValue(indexFamily);
                }
                i++;
            }
        }
        oDoc.write(fileOut);
        fileOut.close();
    } else {
        throw new IOException("Could not read from resource.");
    }

}

From source file:uk.ac.bbsrc.tgac.miso.spring.util.FormUtils.java

License:Open Source License

public static void createBoxContentsSpreadsheet(File outpath, ArrayList<String> array) throws IOException {
    InputStream in = null;//from w  w  w.j ava  2  s.c  om
    in = FormUtils.class.getResourceAsStream("/forms/ods/box_input.xlsx");
    if (in != null) {
        XSSFWorkbook oDoc = new XSSFWorkbook(in);

        XSSFSheet sheet = oDoc.getSheet("Input");
        FileOutputStream fileOut = new FileOutputStream(outpath);

        String boxInfo = array.remove(0);
        String boxName = boxInfo.split(":")[0];
        String boxAlias = boxInfo.split(":")[1];
        XSSFRow row1 = sheet.createRow(1);
        XSSFCell cellA = row1.createCell(0);
        cellA.setCellValue(boxName);
        XSSFCell cellB = row1.createCell(1);
        cellB.setCellValue(boxAlias);

        int i = 4; // start on row 4 of the sheet
        for (String item : array) {
            String position = item.split(":")[0];
            String name = item.split(":")[1];
            String alias = item.split(":")[2];

            XSSFRow row = sheet.createRow(i);
            cellA = row.createCell(0);
            cellA.setCellValue(position);
            cellB = row.createCell(1);
            cellB.setCellValue(name);
            XSSFCell cellC = row.createCell(2);
            cellC.setCellValue(alias);
            i++;
        }
        oDoc.write(fileOut);
        fileOut.close();
    } else {
        throw new IOException("Could not read from resource.");
    }
}

From source file:vd10_workbook.AbilityManagement.java

public void loadWorkSheet(XSSFWorkbook workbook) {
    this.list = new ArrayList<>();
    //Get the sheet at index 0 (fist sheet)
    XSSFSheet spreadsheet = workbook.getSheet("kha_nang");
    //declare row
    XSSFRow row;//from   ww w.  j  a  v a 2s .  c  om
    //declare Iterator<Row> to browse row by row
    Iterator<Row> rowIterator = spreadsheet.iterator();
    row = (XSSFRow) rowIterator.next();
    row = (XSSFRow) rowIterator.next();
    while (rowIterator.hasNext()) {
        row = (XSSFRow) rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        Cell cell = cellIterator.next();

        cell = cellIterator.next();
        int employeeID = (int) cell.getNumericCellValue();
        cell = cellIterator.next();
        int languageID = (int) cell.getNumericCellValue();
        this.list.add(new Ability(employeeID, languageID));
    }
}

From source file:vd10_workbook.AssignedVoteManagement.java

public void loadWorkSheet(XSSFWorkbook workbook) throws ParseException {
    this.list = new ArrayList<>();
    //Get the sheet at index 0 (fist sheet)
    XSSFSheet spreadsheet = workbook.getSheet("phieu_phan_cong");
    //declare row
    XSSFRow row;// w ww  . java 2  s .  co  m
    //declare Iterator<Row> to browse row by row
    Iterator<Row> rowIterator = spreadsheet.iterator();
    row = (XSSFRow) rowIterator.next();
    row = (XSSFRow) rowIterator.next();
    int count = 1;
    while (rowIterator.hasNext()) {
        row = (XSSFRow) rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        Cell cell = cellIterator.next();
        cell = cellIterator.next();
        String date_s = cell.getStringCellValue();
        SimpleDateFormat dt = new SimpleDateFormat("yyyy-MM-dd");
        Date date = dt.parse(date_s);
        cell = cellIterator.next();
        int numberOfday = (int) cell.getNumericCellValue();
        cell = cellIterator.next();
        int employeeID = (int) cell.getNumericCellValue();
        cell = cellIterator.next();
        int workTypeID = (int) cell.getNumericCellValue();
        this.list.add(new AssignedVote(date, numberOfday, employeeID, workTypeID));
        if (count == NUMBEROFROWS) {
            break;
        } else {
            count++;
        }
    }
}

From source file:vd10_workbook.DepartmentManagement.java

public void loadWorkSheet(XSSFWorkbook workbook) throws ParseException {
    this.list = new ArrayList<>();
    //Get the sheet at index 0 (fist sheet)
    XSSFSheet spreadsheet = workbook.getSheet("don_vi");
    //declare row
    XSSFRow row;/*from  w  w  w  .ja v a 2s .co m*/
    //declare Iterator<Row> to browse row by row
    Iterator<Row> rowIterator = spreadsheet.iterator();
    row = (XSSFRow) rowIterator.next();
    row = (XSSFRow) rowIterator.next();
    while (rowIterator.hasNext()) {
        row = (XSSFRow) rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        Cell cell = cellIterator.next();
        cell = cellIterator.next();
        this.list.add(new Department(cell.getStringCellValue()));
    }
}

From source file:vd10_workbook.EmployeeManagement.java

public void loadWorkSheet(XSSFWorkbook workbook) throws ParseException {
    this.list = new ArrayList<>();
    //Get the sheet at index 0 (fist sheet)
    XSSFSheet spreadsheet = workbook.getSheet("ds_nhan_vien");
    //declare row
    XSSFRow row;//from   w  ww.ja  va 2s.co  m
    //declare Iterator<Row> to browse row by row
    Iterator<Row> rowIterator = spreadsheet.iterator();
    row = (XSSFRow) rowIterator.next();
    row = (XSSFRow) rowIterator.next();
    int count = 1;
    while (rowIterator.hasNext()) {
        row = (XSSFRow) rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        Cell cell = cellIterator.next();
        cell = cellIterator.next();
        String name = cell.getStringCellValue();
        int sex = -1;
        cell = cellIterator.next();
        if (cell.getStringCellValue().equalsIgnoreCase("nam")) {
            sex = 1;
        } else {
            sex = 0;
        }
        cell = cellIterator.next();
        String date_s = cell.getStringCellValue();
        SimpleDateFormat dt = new SimpleDateFormat("yyyy-MM-dd");
        Date date = dt.parse(date_s);
        cell = cellIterator.next();
        String cmnd = cell.getStringCellValue();
        cell = cellIterator.next();
        double salary = cell.getNumericCellValue();
        cell = cellIterator.next();
        String address = cell.getStringCellValue();
        cell = cellIterator.next();
        int department = (int) cell.getNumericCellValue();
        this.list.add(new Employee(name, sex, date, cmnd, salary, address, department));
        if (count == NUMBEROFROWS) {
            break;
        } else {
            count++;
        }
    }
}

From source file:vd10_workbook.ForeignLanguageManagement.java

public void loadWorkSheet(XSSFWorkbook workbook) throws ParseException {
    this.list = new ArrayList<>();
    //Get the sheet at index 0 (fist sheet)
    XSSFSheet spreadsheet = workbook.getSheet("ngoai_ngu");
    //declare row
    XSSFRow row;//  w  ww .j a v  a 2 s  .co m
    //declare Iterator<Row> to browse row by row
    Iterator<Row> rowIterator = spreadsheet.iterator();
    row = (XSSFRow) rowIterator.next();
    row = (XSSFRow) rowIterator.next();
    while (rowIterator.hasNext()) {
        row = (XSSFRow) rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        Cell cell = cellIterator.next();
        cell = cellIterator.next();
        this.list.add(new ForeignLanguage(cell.getStringCellValue()));
    }
}

From source file:vd10_workbook.RequirementManagement.java

public void loadWorkSheet(XSSFWorkbook workbook) throws ParseException {
    this.list = new ArrayList<>();
    //Get the sheet at index 0 (fist sheet)
    XSSFSheet spreadsheet = workbook.getSheet("yeu_cau");
    //declare row
    XSSFRow row;/*  w ww . j  a va  2 s.  c  o  m*/
    //declare Iterator<Row> to browse row by row
    Iterator<Row> rowIterator = spreadsheet.iterator();
    row = (XSSFRow) rowIterator.next();
    row = (XSSFRow) rowIterator.next();
    while (rowIterator.hasNext()) {
        row = (XSSFRow) rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        Cell cell = cellIterator.next();
        cell = cellIterator.next();
        int workTypeID = (int) cell.getNumericCellValue();
        cell = cellIterator.next();
        int languageID = (int) cell.getNumericCellValue();
        this.list.add(new Requirement(workTypeID, languageID));
    }
}

From source file:vd10_workbook.UserGroupManagement.java

public void loadWorkSheet(XSSFWorkbook workbook) throws ParseException {
    this.list = new ArrayList<>();
    //Get the sheet at index 0 (fist sheet)
    XSSFSheet spreadsheet = workbook.getSheet("nhom_nguoi_dung");
    //declare row
    XSSFRow row;/*  ww  w. j av  a2  s .  co m*/
    //declare Iterator<Row> to browse row by row
    Iterator<Row> rowIterator = spreadsheet.iterator();
    row = (XSSFRow) rowIterator.next();
    row = (XSSFRow) rowIterator.next();
    while (rowIterator.hasNext()) {
        row = (XSSFRow) rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        Cell cell = cellIterator.next();
        cell = cellIterator.next();
        String name = cell.getStringCellValue();
        cell = cellIterator.next();
        String id = cell.getStringCellValue();
        this.list.add(new UserGroup(id, name));
    }
}