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

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

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

Introduction

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

Prototype

Sheet getSheetAt(int index);

Source Link

Document

Get the Sheet object at the given index.

Usage

From source file:info.informationsea.tableio.excel.test.ExcelSheetReaderTest.java

License:Open Source License

@Test
public void testRead() throws IOException {
    Workbook workbook = new XSSFWorkbook(getClass().getResourceAsStream("iris.xlsx"));
    ExcelSheetReader excelSheetReader = new ExcelSheetReader(workbook.getSheetAt(0));
    testContents(excelSheetReader);/*from  w ww  .  java 2 s .  com*/
}

From source file:info.informationsea.tableio.excel.test.ExcelSheetReaderTest.java

License:Open Source License

@Test
public void testRead2() throws IOException {
    Workbook workbook = new HSSFWorkbook(getClass().getResourceAsStream("iris.xls"));
    ExcelSheetReader excelSheetReader = new ExcelSheetReader(workbook.getSheetAt(0));
    testContents(excelSheetReader);/*  ww  w .  ja v  a  2 s . c  o  m*/
}

From source file:info.informationsea.venn.CombinationImporterExporter.java

License:Open Source License

public static CombinationSolver<String, String> importCombination(File file) throws Exception {
    if (file.getName().endsWith(".csv")) {
        try (Reader reader = new FileReader(file); TableCSVReader csvReader = new TableCSVReader(reader)) {
            csvReader.setUseHeader(true);
            return importCombination(csvReader);
        }/*from   w w w  .  ja  v  a 2  s  . com*/
    } else if (file.getName().endsWith(".xls") || file.getName().endsWith(".xlsx")) {
        Workbook workbook = WorkbookFactory.create(file);
        Sheet sheet = workbook.getSheetAt(0);
        try (ExcelSheetReader sheetReader = new ExcelSheetReader(sheet)) {
            sheetReader.setUseHeader(true);
            return importCombination(sheetReader);
        }
    } else {
        throw new IllegalArgumentException("Unsupported file");
    }
}

From source file:info.informationsea.venn.CombinationImporterExporterTest.java

License:Open Source License

public void testExcel(String filename) throws Exception {
    CombinationImporterExporter.export(new File(DIST_DIR, filename), combinationSolver,
            new VennFigureParameters<String>(combinationSolver).getKeys());

    Workbook workbook = WorkbookFactory.create(new File(DIST_DIR, filename));
    Sheet sheet = workbook.getSheetAt(0);

    ExcelSheetReader reader = new ExcelSheetReader(sheet);
    reader.setUseHeader(true);/*ww  w.  ja va 2s  . co  m*/

    CombinationSolver<String, String> solver = CombinationImporterExporter.importCombination(reader);
    Assert.assertEquals(combinationSolver.getValues(), solver.getValues());

    sheet = workbook.getSheetAt(1);
    reader = new ExcelSheetReader(sheet);
    reader.setUseHeader(true);

    Map<Set<String>, Set<String>> combinations = new HashMap<>();
    for (TableRecord record : reader) {
        String[] key = record.get(0).toString().split(", ");
        Set<String> values = new HashSet<>();
        for (int i = 2; i < record.size(); i++) {
            values.add(record.get(i).toString());
        }

        Assert.assertEquals(values.size(), Integer.parseInt(record.get(1).toString()));

        combinations.put(asSet(key), values);
    }

    Assert.assertEquals(combinations, combinationSolver.getCombinationResult());
    Assert.assertEquals(solver.getCombinationResult(), combinationSolver.getCombinationResult());
}

From source file:instancegenerator.ExcelTranslator.java

private static void Translate(String input, String output) {
    try {//from www .  j a  v a  2s.  c om
        FileWriter fw = new FileWriter(output);// Objeto para que establece origen de los datos
        BufferedWriter bw = new BufferedWriter(fw);// buffer para el manejo de los datos
        PrintWriter title = new PrintWriter(bw);
        FileInputStream ficheroXlsx = new FileInputStream(new File(input));
        Workbook ficheroWb = new HSSFWorkbook(ficheroXlsx);
        Sheet sheet = ficheroWb.getSheetAt(0);
        //for the items
        Row row = sheet.getRow(1);
        int i = 1;
        String itemsSet = "set J := ";
        int reqCapacity = 0;
        List<String> varItem = new ArrayList<>();
        while (row != null && row.getCell(2) != null) {
            itemsSet = itemsSet + i + " ";
            int weight = (int) row.getCell(2).getNumericCellValue();
            reqCapacity += weight;
            String tmp = "";
            if (i == 1) {
                tmp = "param w:=     " + i + "   " + weight;
            } else {
                tmp = "              " + i + "   " + weight;
            }
            varItem.add(tmp);
            i++;
            row = sheet.getRow(i);
        }
        varItem.add(";");
        itemsSet = itemsSet + ";";

        //for the bins
        row = sheet.getRow(1);
        int j = 1;
        String binSet = "set I := ";
        List<String> binsCost = new ArrayList<>();
        List<String> binsCap = new ArrayList<>();
        while (row != null && row.getCell(0) != null) {
            String tmpCost = "";
            String tmpCap = "";
            binSet = binSet + j + " ";
            if (j == 1) {
                tmpCost = "param f:=     " + j + "   " + (int) row.getCell(1).getNumericCellValue();
                tmpCap = "param b:=     " + j + "   " + (int) row.getCell(0).getNumericCellValue();
            } else {
                tmpCost = "              " + j + "   " + (int) row.getCell(1).getNumericCellValue();
                tmpCap = "              " + j + "   " + (int) row.getCell(0).getNumericCellValue();
            }
            binsCost.add(tmpCost);
            binsCap.add(tmpCap);
            j++;
            row = sheet.getRow(j);
        }
        binsCap.add(";");
        binsCost.add(";");
        binSet = binSet + ";";

        title.print(binSet);
        title.println();
        title.println();
        title.print(itemsSet);
        title.println();
        title.println();

        for (int x = 0; x < binsCap.size(); x++) {
            title.print(binsCap.get(x));
            title.println();
        }
        title.println();
        title.println();
        for (int y = 0; y < binsCost.size(); y++) {
            title.print(binsCost.get(y));
            title.println();
        }
        title.println();
        title.println();
        for (int z = 0; z < varItem.size(); z++) {
            title.print(varItem.get(z));
            title.println();
        }
        title.println();
        title.close();
    } catch (IOException ex) {
        System.out.println(ex.getMessage());
    }
}

From source file:IO.FILES.java

public void overWrite(Persona p, String texto) throws Exception {
    if (p == null)
        return;//from w  w  w .  ja  va 2s.  co m
    Workbook wb = WorkbookFactory.create(new FileInputStream(new File(ruta)));
    Sheet hoja = wb.getSheetAt(0);
    boolean encontrado = false;
    Row row = null;
    Iterator it = hoja.rowIterator();
    while (it.hasNext()) {
        row = (Row) it.next();
        if (row.getCell(0).getStringCellValue().equalsIgnoreCase(p.getId())) {
            encontrado = true;
            break;
        }
    }

    if (!encontrado)
        return;
    int aux = row.getRowNum();
    hoja.removeRow(row);
    row = hoja.createRow(aux);
    CentroEducativo centro = p.getCentro();
    Redaccion R = p.getRedaccion();
    for (int i = 0; i < cols.length; i++) {
        Cell cell = row.createCell(i);
        switch (i) {
        case 0:
            cell.setCellValue(p.getId());
            break;
        case 1:
            cell.setCellValue(p.getName());
            break;
        case 2:
            cell.setCellValue(p.getLastName());
            break;
        case 3:
            cell.setCellValue(p.getLastName2());
            break;
        case 4:
            cell.setCellValue(p.getNivel());
            break;
        case 5:
            cell.setCellValue(p.getEdad());
            break;
        case 6:
            cell.setCellValue(p.isMale() ? "M" : "F");
            break;
        case 7:
            cell.setCellValue(centro.getName());
            break;
        case 8:
            cell.setCellValue(centro.getSiglas());
            break;
        case 9:
            cell.setCellValue(centro.isPublic() ? "PUBLICO" : "PRIVADO");
            break;
        case 10:
            cell.setCellValue(p.isCCA() ? "CCA" : "SCA");
            break;
        case 11:
            cell.setCellValue(p.isCCA() ? p.getCCA_DETALLE() : "-");
            break;
        case 12:
            cell.setCellValue(p.isAdecuacion() ? "SI" : "NO");
            break;
        case 13:
            cell.setCellValue(p.isAdecuacion() ? modelo.Info.tipos[p.getTipo()] : "-");
            break;
        case 14:
            cell.setCellValue(R.getUT());
            break;
        case 15:
            cell.setCellValue(R.getCL());
            break;
        case 16:
            cell.setCellValue(R.getPAL());
            break;
        case 17:
            cell.setCellValue(R.getLPUT());
            break;
        case 18:
            cell.setCellValue(R.getLPCL());
            break;
        case 19:
            cell.setCellValue(R.getINSUB());
        }
    }
    REDACCIONES.overWrite(wb, p, texto);
    save(wb);
}

From source file:IO.FILES.java

public void write(Persona p, String texto) throws Exception {
    if (p == null)
        return;//from   ww w . jav a2s.  c  o  m
    Workbook wb = WorkbookFactory.create(new FileInputStream(new File(ruta)));
    Sheet hoja = wb.getSheetAt(0);
    int inx = 0;
    Iterator it = hoja.rowIterator();
    while (it.hasNext()) {
        inx++;
        it.next();
    }
    Row row = hoja.createRow(inx);
    CentroEducativo centro = p.getCentro();
    Redaccion R = p.getRedaccion();
    for (int i = 0; i < cols.length; i++) {
        Cell cell = row.createCell(i);
        switch (i) {
        case 0:
            cell.setCellValue(p.getId());
            break;
        case 1:
            cell.setCellValue(p.getName());
            break;
        case 2:
            cell.setCellValue(p.getLastName());
            break;
        case 3:
            cell.setCellValue(p.getLastName2());
            break;
        case 4:
            cell.setCellValue(p.getNivel());
            break;
        case 5:
            cell.setCellValue(p.getEdad());
            break;
        case 6:
            cell.setCellValue(p.isMale() ? "M" : "F");
            break;
        case 7:
            cell.setCellValue(centro.getName());
            break;
        case 8:
            cell.setCellValue(centro.getSiglas());
            break;
        case 9:
            cell.setCellValue(centro.isPublic() ? "PUBLICO" : "PRIVADO");
            break;
        case 10:
            cell.setCellValue(p.isCCA() ? "CCA" : "SCA");
            break;
        case 11:
            cell.setCellValue(p.isCCA() ? p.getCCA_DETALLE() : "-");
            break;
        case 12:
            cell.setCellValue(p.isAdecuacion() ? "SI" : "NO");
            break;
        case 13:
            cell.setCellValue(p.isAdecuacion() ? modelo.Info.tipos[p.getTipo()] : "-");
            break;
        case 14:
            cell.setCellValue(R.getUT());
            break;
        case 15:
            cell.setCellValue(R.getCL());
            break;
        case 16:
            cell.setCellValue(R.getPAL());
            break;
        case 17:
            cell.setCellValue(R.getLPUT());
            break;
        case 18:
            cell.setCellValue(R.getLPCL());
            break;
        case 19:
            cell.setCellValue(R.getINSUB());
        }
    }
    REDACCIONES.write(wb, p, texto);
    save(wb);
}

From source file:IO.FILES.java

public void remove(String id) throws Exception {
    try {//ww  w  .  ja v  a  2s  .  c om
        Workbook wb = WorkbookFactory.create(new FileInputStream(new File(ruta)));
        Sheet hoja = wb.getSheetAt(0);
        Iterator rows = hoja.rowIterator();

        int i = 0;
        Persona p = null;
        while (rows.hasNext()) {
            Row row = (Row) rows.next();
            if (row.getCell(0).getStringCellValue().equalsIgnoreCase(id)) {
                removeRow(hoja, row);
                REDACCIONES.remove(id, wb);
                save(wb);
                break;
            }
            i++;
        }
    } catch (IOException | InvalidFormatException | EncryptedDocumentException ex) {
        Logger.getLogger(FILES.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:IO.FILES.java

final void header() {
    try {/*w ww  . ja  v a2 s . c  o  m*/
        Workbook wb = WorkbookFactory.create(new FileInputStream(new File(ruta)));
        Sheet hoja = wb.getSheetAt(0);
        Row row = hoja.createRow(0);
        int h = 0;
        for (String s : cols) {
            Cell cell = row.createCell(h++);
            cell.setCellValue(s);
        }
        save(wb);
    } catch (FileNotFoundException ex) {
        Logger.getLogger(FILES.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException | InvalidFormatException | EncryptedDocumentException ex) {
        Logger.getLogger(FILES.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:IO.FILES.java

public Persona getById(String id) {
    if (id.equals(""))
        return null;
    Persona p = null;/*  ww w.  j  av a 2s . c om*/
    try {
        Workbook wb = WorkbookFactory.create(new FileInputStream(new File(ruta)));
        Sheet hoja = wb.getSheetAt(0);
        Iterator rows = hoja.rowIterator();

        while (rows.hasNext()) {
            Row row = (Row) rows.next();
            if (row.getCell(0).getStringCellValue().equalsIgnoreCase(id)) {
                p = toPersona(row);
            }
        }
    } catch (IOException | InvalidFormatException | EncryptedDocumentException ex) {
        Logger.getLogger(FILES.class.getName()).log(Level.SEVERE, null, ex);
    }
    return p;
}