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:FormatConvert.exceloperation.ExcelOperation.java

public static void MergeExcel(String sourceDir, String targetexcel) {
    try {//from w  ww .ja  va2 s. c  om
        File[] filelist = FilelistReader.getFileList(sourceDir);
        FileOutputStream fileOut = new FileOutputStream(targetexcel);
        Workbook wb = new XSSFWorkbook();
        for (int i = 0; i < filelist.length; i++) {
            File f = filelist[i];
            if (f.getName().endsWith(".xlsx")) {
                FileInputStream is = (new FileInputStream(f));
                XSSFWorkbook wb2 = new XSSFWorkbook(is);
                XSSFSheet sheet = wb2.getSheetAt(0);
                Sheet sheetnew = wb.createSheet(f.getName());
                //System.out.println(sheet.get);
                Util.copySheets((XSSFSheet) sheetnew, sheet);

            } else if (f.getName().endsWith(".xls")) {
                FileInputStream is = (new FileInputStream(f));
                HSSFWorkbook wb2 = new HSSFWorkbook(is);
                HSSFSheet sheet = wb2.getSheetAt(0);
                Sheet sheetnew = wb.createSheet(f.getName());
                Util.copySheets((HSSFSheet) sheetnew, sheet);
            }
        }
        wb.write(fileOut);

    } catch (Exception ioe) {
        ioe.printStackTrace();
    }
}

From source file:Funcionalidad.LeerExcel.java

public boolean leer(File archivo, Contenedor almacenamiento) {
    boolean ok = true;
    try {//  w ww  .  j  a  v  a 2 s .co m
        FileInputStream fs = new FileInputStream(archivo);
        XSSFWorkbook workbook = new XSSFWorkbook(fs);
        for (int i = 0; i < 5; i++) {
            XSSFSheet sheet = null;
            sheet = workbook.getSheetAt(i);
            Iterator<Row> rowIterator = sheet.iterator();
            Row row;
            while (rowIterator.hasNext()) {
                row = rowIterator.next();
                Iterator<Cell> cellIterator = row.cellIterator();
                Cell celda;
                ArrayList<String> nombres = new ArrayList<>();
                ArrayList<Integer> numeros = new ArrayList<>();
                while (cellIterator.hasNext()) {
                    celda = cellIterator.next();
                    switch (celda.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        nombres.add(celda.getStringCellValue());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        Double d = celda.getNumericCellValue();
                        numeros.add(d.intValue());
                        break;
                    }
                }
                switch (i) {
                case 0:
                    almacenamiento.anadirProfesor(
                            new Profesor(nombres.get(0), nombres.get(1), nombres.get(2), numeros.get(0)));
                    break;
                case 1:
                    almacenamiento
                            .anadidTitulacion(new Titulacion(numeros.get(0), nombres.get(0), numeros.get(1)));
                    break;
                case 2:
                    almacenamiento.anadirAsignatura(new Asignatura(nombres.get(0),
                            almacenamiento.getProfesorPorId(numeros.get(0)), numeros.get(1),
                            almacenamiento.getTitulacionPorId(numeros.get(2)), numeros.get(3), numeros.get(4)));
                    break;
                case 3:
                    almacenamiento.anadirAula(new Aula(numeros.get(0), nombres.get(0)));
                    break;
                case 4:
                    almacenamiento.anadirGrupo(new Grupo(numeros.get(0),
                            almacenamiento.getAulaPorId(numeros.get(1)),
                            almacenamiento.getTitulacionPorId(numeros.get(2)), numeros.get(3), nombres.get(0)));
                    break;
                }
            }
        }

    } catch (Exception ex) {
        //Logger.getLogger(LeerExcel.class.getName()).log(Level.SEVERE, null, ex);
        ok = false;
    }
    return ok;
}

From source file:fyp.POI.POI.java

public static void main(String[] args) throws Exception {
    FileInputStream fis = new FileInputStream(new File("FA_AAX.xlsx"));
    XSSFWorkbook wb = new XSSFWorkbook(fis);
    XSSFSheet sheet = wb.getSheetAt(0);
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

    CellReference cr = new CellReference("C4");
    Row row = sheet.getRow(cr.getRow());
    Cell cell = row.getCell(cr.getCol());
    CellValue cellValue = evaluator.evaluate(cell);

    switch (cellValue.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        boolean truefalse = cellValue.getBooleanValue();
        System.out.println("Boolean" + truefalse);
        break;//from   w  w w. j a  v  a  2s. com
    case Cell.CELL_TYPE_NUMERIC:
        double NumericCheck = cellValue.getNumberValue();
        if (NumericCheck % 1 == 0) {
            int Integer = (int) NumericCheck;
            System.out.println("Integer" + Integer);
        } else {
            double Dbl = NumericCheck;
            System.out.println("Double" + Dbl);
        }
        break;

    case Cell.CELL_TYPE_STRING:
        String str = cellValue.getStringValue();
        System.out.println("String" + str.substring(3));
        break;

    case Cell.CELL_TYPE_BLANK:
        System.out.println("Blank");
        break;

    case Cell.CELL_TYPE_ERROR:
        break;
    }
    fis.close();
}

From source file:fyp.POI.POIFunction.java

public double poiToGetDbl(String FileAddress, String CellDetails, int SheetNum) throws NullPointerException {
    double result = 0.0;
    try {//from w ww  .j a  va  2s  .  c  om
        FileInputStream fis = new FileInputStream(new File(FileAddress));
        XSSFWorkbook wb = null;
        try {
            wb = new XSSFWorkbook(fis);
        } catch (IOException e) {
            System.out.print("Error detected: " + e);
        }
        XSSFSheet sheet = wb.getSheetAt(SheetNum);
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        try {
            CellReference cr = new CellReference(CellDetails);
            Row row = sheet.getRow(cr.getRow());
            Cell cell = row.getCell(cr.getCol());
            //System.out.println("CELL "+ cell);
            checkNullNumeric(cell);
            CellValue cellValue = evaluator.evaluate(cell);

            switch (cellValue.getCellType()) {
            case Cell.CELL_TYPE_BLANK:
                result = 0.0;
                break;

            case Cell.CELL_TYPE_ERROR:
                result = 0.0;
                break;

            case Cell.CELL_TYPE_NUMERIC:
                result = cellValue.getNumberValue();
                break;

            }
        } catch (NullPointerException n) {
            result = 0.0;
        }
        fis.close();
    } catch (IOException e) {
        System.out.print("Error detected: " + e);
    }
    return result;
}

From source file:fyp.POI.POIFunction.java

public double poiToGetInt(String FileAddress, String CellDetails, int SheetNum) throws FileNotFoundException {
    double result = 0.0;

    try {//from   www .  j ava2  s. c o m
        FileInputStream fis = new FileInputStream(new File(FileAddress));
        XSSFWorkbook wb = null;
        try {
            wb = new XSSFWorkbook(fis);
        } catch (IOException e) {
            System.out.print("Error detected: " + e);
        }
        XSSFSheet sheet = wb.getSheetAt(SheetNum);
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

        try {
            CellReference cr = new CellReference(CellDetails);
            Row row = sheet.getRow(cr.getRow());
            Cell cell = row.getCell(cr.getCol());
            checkNullNumeric(cell);
            CellValue cellValue = evaluator.evaluate(cell);
            switch (cellValue.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                result = cellValue.getNumberValue();
                break;

            case Cell.CELL_TYPE_BLANK:
                result = 0;
                break;

            case Cell.CELL_TYPE_ERROR:
                result = 0;
                break;
            }
        } catch (NullPointerException n) {
            result = 0;
        }

        fis.close();
    } catch (IOException e) {
        System.out.print("Error detected: " + e);
    }
    return (int) result;
}

From source file:fyp.POI.POIFunction.java

public String poiToGetString(String FileAddress, String CellDetails, int SheetNum)
        throws FileNotFoundException {
    String result = "NOTFOUND";
    try {/*w w w.  j a v  a  2 s. co  m*/
        FileInputStream fis = new FileInputStream(new File(FileAddress));
        XSSFWorkbook wb = null;
        try {
            wb = new XSSFWorkbook(fis);
        } catch (IOException e) {
            System.out.print("Error detected: " + e);
        }
        XSSFSheet sheet = wb.getSheetAt(SheetNum);
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        try {
            CellReference cr = new CellReference(CellDetails);
            Row row = sheet.getRow(cr.getRow());
            Cell cell = row.getCell(cr.getCol());
            checkNullString(cell);
            CellValue cellValue = evaluator.evaluate(cell);

            switch (cellValue.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                result = cellValue.getStringValue();
                break;

            case Cell.CELL_TYPE_BLANK:
                result = " ";
                break;

            case Cell.CELL_TYPE_ERROR:
                result = " ";
                break;
            }
        } catch (NullPointerException n) {
            result = " ";
        }
        fis.close();
    } catch (IOException e) {
        System.out.print("Error detected: " + e);
    }
    return result;
}

From source file:gov.va.isaac.isaacDbProcessingRules.spreadsheet.SpreadsheetReader.java

License:Apache License

public List<RuleDefinition> readSpreadSheet(InputStream is) throws IOException {
    XSSFWorkbook ss = new XSSFWorkbook(is);

    Sheet sheet = ss.getSheetAt(1);

    int i = readHeaders(sheet);

    for (; i <= sheet.getLastRowNum(); i++) {
        Row r = sheet.getRow(i);//from w  w w . j  a  v  a 2s.c o m

        if (r != null) {
            ArrayList<Cell> values = new ArrayList<>();

            for (int col = 0; col < columnHeaders.size(); col++) {
                values.add(r.getCell(col));
            }
            data.add(values);
        }
    }

    //Have read the entire spreadsheet - now process into our 'rule' format

    ArrayList<RuleDefinition> result = new ArrayList<>();

    for (int rowNum = 0; rowNum <= data.size(); rowNum++) {
        RuleDefinition rd = new RuleDefinition();
        Integer id = readIntColumn(rowNum, "ID");
        if (id == null) {
            //blank row?
            continue;
        }
        rd.id = id;
        rd.date = readDateColumn(rowNum, version == 1 ? "Date" : "Timestamp");

        rd.action = Action.parse(readStringColumn(rowNum, "Action"));
        rd.sctFSN = readStringColumn(rowNum, version == 1 ? "SCT FSN" : "FSN");
        try {
            rd.sctID = readLongColumn(rowNum, version == 1 ? "SCT ID" : "SCT_ID");
        } catch (IllegalStateException e) {
            String temp = readStringColumn(rowNum, version == 1 ? "SCT ID" : "SCT_ID");
            if (temp != null) {
                rd.sctID = Long.parseLong(temp);
            }
        }
        rd.author = readStringColumn(rowNum, "Author");
        if (version == 1) {
            rd.comments = readStringColumn(rowNum, "Comments");
        }

        ArrayList<SelectionCriteria> criteria = new ArrayList<>();

        while (true) {
            SelectionCriteria sc = new SelectionCriteria();
            if (version == 1) {
                sc.operand = readOperand(rowNum);
                sc.type = SelectionCriteriaType.parse(readStringColumn(rowNum, "Type"));
            } else {
                sc.type = SelectionCriteriaType.RXCUI;
            }
            try {
                //If we read a long, as a string, we get an extra .0 on the end - so read as a long first, if it is one.
                sc.value = readLongColumn(rowNum, version == 1 ? "Value" : "RXCUI").toString();
            } catch (IllegalStateException e) {
                sc.value = readStringColumn(rowNum, version == 1 ? "Value" : "RXCUI");
            }
            if (version == 1) {
                sc.valueId = readStringColumn(rowNum, "Value ID");
            }

            criteria.add(sc);
            //peak at the next row, see if it is an additional criteria, or a new rule
            Integer nextId = readIntColumn(rowNum + 1, "ID"); //if the next row has an id, its a new rule
            String nextType = readStringColumn(rowNum + 1, "Type"); //check to see if we hit the end of the rows
            if (nextId != null || nextType == null) {
                break;
            } else //more criteria for this rule
            {
                rowNum++;
            }
        }

        rd.criteria = criteria;
        result.add(rd);
    }
    ss.close();
    return result;
}

From source file:graphbuilder.ExcelParser.java

public static List<Map<String, Object>> loadExcelFile(File file) {
    List<Map<String, Object>> records = new LinkedList();
    OPCPackage pkg = null;/* w w w  .  j  a va2  s.c  om*/
    try {
        pkg = OPCPackage.open(file);
        XSSFWorkbook wb = new XSSFWorkbook(pkg);
        Sheet sheet = wb.getSheetAt(0);
        List<String> schema = null;
        for (Row row : sheet) {
            if (schema == null) {
                schema = new LinkedList();
                for (Cell cell : row) {
                    schema.add("" + loadCellData(cell));
                }
            } else {
                Map<String, Object> record = new HashMap();
                int index = 0;
                for (Cell cell : row) {
                    String name = schema.get(index++);
                    Object value = loadCellData(cell);
                    if (value == null) {
                        continue;
                    }
                    record.put(name, value);
                }
                if (record.size() > 0) {
                    records.add(record);
                }
            }
        }
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {

    }
    return records;
}

From source file:graphene.hts.file.ExcelXSSFToJSONConverter.java

License:Apache License

public Map<String, List> convert(final File f) throws Exception, IOException {
    FileInputStream fis = null;//from www  .j a v  a2  s . co  m
    final Map<String, List> workbookConversion = new TreeMap<String, List>();
    try {

        final String ext = FileUtils.getFileExtension(f.getName());
        boolean success = false;
        logger.debug("Ext " + ext);
        /**
         * For modern excel files
         */
        if (".xls".equalsIgnoreCase(ext) || ".xlsx".equalsIgnoreCase(ext)) {
            fis = new FileInputStream(f);
            try {
                logger.debug("Trying to open file " + f.getAbsolutePath() + " as an XSSF document.");
                final XSSFWorkbook workbook = new XSSFWorkbook(fis);
                for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                    workbookConversion.put(workbook.getSheetAt(i).getSheetName(),
                            internalConvert(workbook.getSheetAt(i).iterator()));

                }
                success = true;
            } catch (final Exception e) {
                logger.error("Could not open file " + f.getAbsolutePath() + " as an XSSF document.", e);
            } finally {
                if (ValidationUtils.isValid(fis)) {
                    fis.close();
                }
            }
        }
        /**
         * For older excel files
         */
        if (!success && ".xls".equalsIgnoreCase(FileUtils.getFileExtension(f.getName()))) {
            fis = new FileInputStream(f);
            try {
                logger.debug("Trying to open file " + f.getAbsolutePath() + " as an HSSF document.");
                final HSSFWorkbook workbook = new HSSFWorkbook(fis);
                for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                    workbookConversion.put(workbook.getSheetAt(i).getSheetName(),
                            internalConvert(workbook.getSheetAt(i).iterator()));
                }
            } catch (final Exception e) {
                logger.error("Could not open file " + f.getAbsolutePath() + " as an HSSF document.", e);

            } finally {
                if (ValidationUtils.isValid(fis)) {
                    fis.close();
                }
            }
        }
    } finally {
        if (ValidationUtils.isValid(fis)) {
            fis.close();
        }
    }
    return workbookConversion;
}

From source file:gtu._work.ui.SqlCreaterUI.java

License:Open Source License

private void executeBtnPreformed() {
    try {/*from   w ww.j  ava 2s  .  co m*/
        logArea.setText("");
        File srcFile = JCommonUtil.filePathCheck(excelFilePathText.getText(), "?", false);
        if (srcFile == null) {
            return;
        }
        if (!srcFile.getName().endsWith(".xlsx")) {
            JCommonUtil._jOptionPane_showMessageDialog_error("excel");
            return;
        }
        if (StringUtils.isBlank(sqlArea.getText())) {
            return;
        }
        File saveFile = JCommonUtil._jFileChooser_selectFileOnly_saveFile();
        if (saveFile == null) {
            JCommonUtil._jOptionPane_showMessageDialog_error("?");
            return;
        }

        String sqlText = sqlArea.getText();

        StringBuffer sb = new StringBuffer();
        Map<Integer, String> refMap = new HashMap<Integer, String>();
        Pattern sqlPattern = Pattern.compile("\\$\\{(\\w+)\\}", Pattern.MULTILINE);
        Matcher matcher = sqlPattern.matcher(sqlText);
        while (matcher.find()) {
            String val = StringUtils.trim(matcher.group(1)).toUpperCase();
            refMap.put(ExcelUtil.cellEnglishToPos(val), val);
            matcher.appendReplacement(sb, "\\$\\{" + val + "\\}");
        }
        matcher.appendTail(sb);
        appendLog(refMap.toString());

        sqlText = sb.toString();
        sqlArea.setText(sqlText);

        Configuration cfg = new Configuration();
        StringTemplateLoader stringTemplatge = new StringTemplateLoader();
        stringTemplatge.putTemplate("aaa", sqlText);
        cfg.setTemplateLoader(stringTemplatge);
        cfg.setObjectWrapper(new DefaultObjectWrapper());
        Template temp = cfg.getTemplate("aaa");

        BufferedWriter writer = new BufferedWriter(
                new OutputStreamWriter(new FileOutputStream(saveFile), "utf8"));

        BufferedInputStream bis = new BufferedInputStream(new FileInputStream(srcFile));
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(bis);
        Sheet sheet = xssfWorkbook.getSheetAt(0);
        for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {
            Row row = sheet.getRow(j);
            if (row == null) {
                continue;
            }
            Map<String, Object> root = new HashMap<String, Object>();
            for (int index : refMap.keySet()) {
                root.put(refMap.get(index), formatCellType(row.getCell(index)));
            }
            appendLog(root.toString());

            StringWriter out = new StringWriter();
            temp.process(root, out);
            out.flush();
            String writeStr = out.getBuffer().toString();
            appendLog(writeStr);

            writer.write(writeStr);
            writer.newLine();
        }
        bis.close();

        writer.flush();
        writer.close();

        JCommonUtil._jOptionPane_showMessageDialog_info("? : \n" + saveFile);
    } catch (Exception ex) {
        JCommonUtil.handleException(ex);
    }
}