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:Servelt.ExcelWriter.java

private void autoStyle(XSSFWorkbook workbook) {

    int snum = workbook.getNumberOfSheets();
    int cnum;//from  w  ww. j av a2 s .c o  m

    for (int i = 0; i < snum; i++) {
        int rnum = workbook.getSheetAt(i).getLastRowNum();
        cnum = 0;
        for (int j = 0; j < rnum; j++) {
            if (workbook.getSheetAt(i).getRow(j) != null)
                if (cnum < workbook.getSheetAt(i).getRow(j).getLastCellNum())
                    cnum = workbook.getSheetAt(i).getRow(j).getLastCellNum();
        }
        for (int j = 0; j < cnum; j++)
            workbook.getSheetAt(i).autoSizeColumn(j);

    }

}

From source file:service.Read_Write_File.java

public static List<Consomation> Read_Fil_XLSX(File myFile) throws FileNotFoundException, IOException {
    List<CorpDetat> corpDetats = new ArrayList<CorpDetat>();
    List<Consomation> consomations = new ArrayList<Consomation>();

    FileInputStream fis = new FileInputStream(myFile);

    XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);

    XSSFSheet mySheet = myWorkBook.getSheetAt(0);

    Iterator<Row> rowIterator = mySheet.iterator();

    Consomation consomation = null;//from   w w  w. j  a  v  a2s .  c o  m
    CorpDetat corpDetat = null;
    ConsomationItem consomationItem = null;
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        if (row.getRowNum() == 0) {// pour ne pas lire les  titres
            row = rowIterator.next();
        }

        int lascellNum = row.getLastCellNum();
        int firstcellNum = row.getFirstCellNum();
        System.out.println("first cell num" + firstcellNum + "  last cell num" + lascellNum);
        if (lascellNum < 3) {
            if (row.getRowNum() > 1) {
                //corpDetats.add(corpDetat);

                consomations.add(consomation);
                corpDetat = null;
                System.out.println("if dyal row celll");
            }
            consomation = new Consomation();
            consomation.setId((int) row.getCell(firstcellNum).getNumericCellValue());
            corpDetat = new CorpDetat();
            corpDetat.setTitre(row.getCell(firstcellNum + 1).getStringCellValue());

        } else {
            Post post = new Post();
            consomationItem = new ConsomationItem();

            for (int i = firstcellNum; i < lascellNum; i++) {
                Cell cell = row.getCell(i);

                switch (i) {
                case 0:
                    System.out.println("num de consomation item" + cell.getStringCellValue());
                    consomationItem.setId(cell.getStringCellValue());
                    break;
                case 1:
                    System.out.println("cell " + i + ":" + cell.getStringCellValue());
                    post.setTitre(cell.getStringCellValue());
                    break;
                case 2:
                    consomationItem.setUnite(cell.getStringCellValue());
                    break;
                case 3:
                    System.out.println("cell " + i + ":" + cell.getCellType());
                    consomationItem.setQuanite((int) cell.getNumericCellValue());
                    break;

                }
            }
            if (post != null) {
                post.setCorpdetat(corpDetat);
                consomationItem.setPost(post);
                post.setCorpdetat(corpDetat);
                corpDetat.getPosts().add(post);
                consomation.getConsomationItems().add(consomationItem);
                consomation.setCorpDetat(corpDetat);

            }
        }

    }

    consomations.add(consomation);
    // pour le dernier corp makydkholch l row li tab30

    return consomations;
}

From source file:Servlets.UploadList.java

@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    PrintWriter out = response.getWriter();

    String file_name = "";
    String extension = "";
    String cellData = "";
    InputStream input = null;//  w  w w .j  av  a  2  s .  c o m
    ArrayList<String[]> list = new ArrayList<String[]>();
    String[] arr;

    FileItemFactory factory = new DiskFileItemFactory();
    ServletFileUpload upload = new ServletFileUpload(factory);
    List items = null;
    try {
        items = upload.parseRequest(request);
    } catch (Exception e) {
        e.printStackTrace();
    }

    try {
        Iterator itr = items.iterator();
        while (itr.hasNext()) {
            FileItem item = (FileItem) itr.next();
            file_name = item.getName();
            input = item.getInputStream();
        }
        if (!file_name.equals("")) {
            extension = file_name.substring(file_name.indexOf("."));
            if (extension.equalsIgnoreCase(".xlsx")) {
                XSSFWorkbook wb = new XSSFWorkbook(input);
                // Get first sheet from the workbook
                XSSFSheet sheet = wb.getSheetAt(0);
                Row row, header;
                int r = sheet.getLastRowNum();
                int headerCnt = 0;
                header = sheet.getRow(3);
                String[] headerText = new String[10];
                for (int i = 0; i < 10; i++) {
                    try {
                        headerText[i] = header.getCell(i).getStringCellValue();
                        headerCnt++;
                    } catch (Exception e) {
                    }
                }
                arr = new String[headerCnt];
                System.arraycopy(headerText, 0, arr, 0, headerCnt);
                list.add(arr);
                for (int i = 4; i <= r; i++) {
                    arr = new String[headerCnt];
                    row = sheet.getRow(i);
                    for (int j = 0; j < headerCnt; j++) {
                        try {
                            cellData = row.getCell(j).getStringCellValue();
                            arr[j] = cellData;
                        } catch (Exception e) {
                        }
                    }
                    list.add(arr);
                }
                InfosDao dao = new InfosDao();
                ArrayList<CollegeBean> collegeList = dao.getAllCollegeInfo();
                request.setAttribute("collegeList", collegeList);
                request.setAttribute("fileName", file_name.substring(0, file_name.lastIndexOf('.')));
                request.setAttribute("itemList", list);
                RequestDispatcher dispatcher = request.getRequestDispatcher("/admin/uploadInTable.jsp");
                dispatcher.forward(request, response);
            } else if (extension.equalsIgnoreCase(".xls")) {
                HSSFWorkbook workbook = new HSSFWorkbook(input);
                // Get first sheet from the workbook
                HSSFSheet sheet = workbook.getSheetAt(0);
                Row row, header;
                int r = sheet.getLastRowNum();
                int headerCnt = 0;
                header = sheet.getRow(3);
                String[] headerText = new String[10];
                for (int i = 0; i < 10; i++) {
                    try {
                        headerText[i] = header.getCell(i).getStringCellValue();
                        headerCnt++;
                    } catch (Exception e) {
                    }

                }
                arr = new String[headerCnt];
                System.arraycopy(headerText, 0, arr, 0, headerCnt);
                list.add(arr);
                for (int i = 4; i <= r; i++) {
                    arr = new String[headerCnt];
                    row = sheet.getRow(i);
                    for (int j = 0; j < headerCnt; j++) {
                        try {
                            cellData = row.getCell(j).getStringCellValue();
                            arr[j] = cellData;
                        } catch (Exception e) {
                        }
                    }
                    list.add(arr);
                }
                InfosDao dao = new InfosDao();
                ArrayList<CollegeBean> collegeList = dao.getAllCollegeInfo();
                request.setAttribute("collegeList", collegeList);
                request.setAttribute("fileName", file_name.substring(0, file_name.lastIndexOf('.')));
                request.setAttribute("itemList", list);
                RequestDispatcher dispatcher = request.getRequestDispatcher("/admin/uploadInTable.jsp");
                dispatcher.forward(request, response);
            } else {
                request.setAttribute("temp", "File Is Not In Proper Format !!!");
                RequestDispatcher dispatcher = request.getRequestDispatcher("/admin/upload.jsp");
                dispatcher.forward(request, response);
            }

        } else {
            request.setAttribute("temp", "Please Select File !!!");
            RequestDispatcher dispatcher = request.getRequestDispatcher("/admin/upload.jsp");
            dispatcher.forward(request, response);
        }
    } catch (Exception e) {
        request.setAttribute("temp", "Error Loading File !!! Check contents and format of file.");
        RequestDispatcher dispatcher = request.getRequestDispatcher("/admin/upload.jsp");
        dispatcher.forward(request, response);
    }

}

From source file:singleton.StaticClass.java

public synchronized void copyFromExcel(String fileName) throws IOException {
    //System.out.println("start");
    Data temp;/*from   w  ww. j a  v a  2 s .c  o m*/
    String value = "";

    File inFile = new File(fileName);
    FileInputStream file = new FileInputStream(inFile);

    XSSFWorkbook wb = new XSSFWorkbook(file);
    XSSFSheet sheet = wb.getSheetAt(4); // Build Image Analysis page is at
    // the 4th sheet of Open source
    // license excel file.

    int rows = sheet.getPhysicalNumberOfRows();

    for (int i = 2; i < rows; ++i) { // start index should be 2 since the
        // 1st row is used for titles.
        XSSFRow row = sheet.getRow(i);
        if (row != null) {

            int cells = row.getPhysicalNumberOfCells(); // Number of cells
            // at each row.

            temp = new Data();
            for (int colIndex = 1; colIndex <= cells; colIndex++) {
                XSSFCell cell = row.getCell(colIndex);

                if (colIndex == 1) {
                    switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_BLANK:
                        temp.setBinary("");
                        break;
                    case XSSFCell.CELL_TYPE_STRING:
                        temp.setBinary(cell.getStringCellValue());
                        break;
                    }
                } else if (colIndex == 2) {
                    switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_BLANK:
                        temp.setPath("");
                        break;
                    case XSSFCell.CELL_TYPE_STRING:
                        temp.setPath(cell.getStringCellValue());
                        break;
                    }
                } else if (colIndex == 3) {
                    switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_BLANK:
                        temp.setOnok("");
                        break;
                    case XSSFCell.CELL_TYPE_STRING:
                        value = cell.getStringCellValue();
                        temp.setOnok(value);
                        break;
                    }
                } else if (colIndex == 4) {
                    switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_BLANK:
                        temp.setOssComponent("");
                        break;
                    case XSSFCell.CELL_TYPE_STRING:
                        temp.setOssComponent(cell.getStringCellValue());
                        break;
                    }
                } else if (colIndex == 6) {
                    switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_BLANK:
                        temp.setLicense("");
                        break;
                    case XSSFCell.CELL_TYPE_STRING:
                        temp.setLicense(cell.getStringCellValue());
                        break;
                    }
                } else {
                    continue;
                }
            }
            if (temp != null) {
                if (value.equalsIgnoreCase("nok")) {
                    nokList.add(temp);
                    //System.out.println("nok count : " + nokList.size());
                } else if (value.equalsIgnoreCase("ok")) {
                    okList.add(temp);
                    //System.out.println("ok count : " + okList.size());
                } else if (value.equalsIgnoreCase("nok(na)")) {
                    nokNaList.add(temp);
                    //System.out.println("nok(na) count : " + nokNaList.size());
                } else {
                    blankList.add(temp);
                    //System.out.println("blank count : " + blankList.size());
                }
                System.out.println(temp.getBinary() + "\t" + temp.getPath() + "\t\t" + temp.getOnok() + "\t\t"
                        + temp.getLicense());
            }
        }
    }
}

From source file:si_piket_smkn3.frm_tambah_guru.java

public static Vector read(String fileName) {
    Vector cellVectorHolder = new Vector();
    try {/*w w w. j  av a  2 s .  c om*/
        FileInputStream myInput = new FileInputStream(fileName);
        //POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
        XSSFWorkbook myWorkBook = new XSSFWorkbook(myInput);
        XSSFSheet mySheet = myWorkBook.getSheetAt(0);
        Iterator rowIter = mySheet.rowIterator();
        while (rowIter.hasNext()) {
            XSSFRow myRow = (XSSFRow) rowIter.next();
            Iterator cellIter = myRow.cellIterator();
            //Vector cellStoreVector=new Vector();
            List list = new ArrayList();
            while (cellIter.hasNext()) {
                XSSFCell myCell = (XSSFCell) cellIter.next();
                list.add(myCell);
            }
            cellVectorHolder.addElement(list);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return cellVectorHolder;
}

From source file:som.file.StemmedDataFileReader.java

License:Open Source License

public void writeIntoInputDataSheet(List<String> wordsList) {
    XSSFWorkbook workbook = null;
    try {/*w  w  w.jav a  2 s .  c o  m*/
        System.out.println("Writing into Input File");
        FileInputStream file = new FileInputStream(
                new File(GenericHelper.getAbsolutePath() + REVISED_INPUT_SHEET_NAME));
        //FileInputStream file = new FileInputStream(new File(REVISED_INPUT_SHEET_NAME));
        //File file = new File(REVISED_INPUT_SHEET_NAME);

        //Get the workbook instance for XLS file 
        workbook = new XSSFWorkbook(file);

        //Get first sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            if (row.getRowNum() == 0) {
                continue;
            }

            Cell cell = row.getCell(IGenericConstants.STEMMED_DATA_COLUMN_NUMBER);
            if (wordsList.size() > row.getRowNum()) {
                cell.setCellValue(wordsList.get(row.getRowNum()));
            }
        }

        file.close();

        System.out.println("Writing to the file on the server");
        FileOutputStream outFile = new FileOutputStream(
                new File(GenericHelper.getAbsolutePath() + REVISED_INPUT_SHEET_NAME));
        workbook.write(outFile);
        System.out.println("File Writing Complete");
        outFile.close();

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

From source file:standarapp.algorithm.CodeAssign.java

public CodeAssign(String nameExcel) throws IOException {
    //Logica de la aplicacion
    diccionario_UbicacionLocalidad = new Hashtable<>();
    codigo_Dpto = new Hashtable<>();
    codigo_Municipio = new Hashtable<>();
    dpto_Municipio = new Hashtable<>();
    codigo_localidad = new Hashtable<>();
    localidad_X = new Hashtable<>();
    localidad_Y = new Hashtable<>();
    codigo_municipioLocalidad = new Hashtable<>();

    XSSFWorkbook xwb = Lecture.lectureXLSX(nameExcel);
    XSSFSheet xsheet = xwb.getSheetAt(0);
    double codigoTemporal = 0;

    for (Row row : xsheet) {
        if (row.getRowNum() > 0) {
            String departamento = "", municipio = "", localidad = "";
            int cod_departamento = 0, cod_municipio = 0;
            double cod_localidad = 0, x = 0, y = 0;
            for (Cell cell : row) {
                if (cell.getColumnIndex() == 0) {
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        codigoTemporal = Double.valueOf(cell.getStringCellValue());
                        cod_departamento = (int) codigoTemporal;
                        continue;
                    case Cell.CELL_TYPE_NUMERIC:
                        cod_departamento = (int) cell.getNumericCellValue();
                        continue;
                    }//www  .j  av a 2s .c  o  m
                }

                if (cell.getColumnIndex() == 1) {
                    departamento = cell.getStringCellValue();
                }

                if (cell.getColumnIndex() == 2) {
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        codigoTemporal = Double.valueOf(cell.getStringCellValue());
                        cod_municipio = (int) codigoTemporal;
                        continue;
                    case Cell.CELL_TYPE_NUMERIC:
                        cod_municipio = (int) cell.getNumericCellValue();
                        continue;
                    }
                }

                if (cell.getColumnIndex() == 3) {
                    municipio = cell.getStringCellValue();
                }

                if (cell.getColumnIndex() == 4) {
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        cod_localidad = Double.valueOf(cell.getStringCellValue());
                        continue;
                    case Cell.CELL_TYPE_NUMERIC:
                        cod_localidad = (double) cell.getNumericCellValue();
                        continue;
                    }
                }

                if (cell.getColumnIndex() == 5) {
                    localidad = cell.getStringCellValue();
                }

                if (cell.getColumnIndex() == 6) {
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        x = Double.valueOf(cell.getStringCellValue());
                        continue;
                    case Cell.CELL_TYPE_NUMERIC:
                        x = (double) cell.getNumericCellValue();
                        continue;
                    }
                }

                if (cell.getColumnIndex() == 7) {
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        y = Double.valueOf(cell.getStringCellValue());
                        continue;
                    case Cell.CELL_TYPE_NUMERIC:
                        y = (double) cell.getNumericCellValue();
                        continue;
                    }
                }
            }

            if (!diccionario_UbicacionLocalidad.containsKey(departamento)) {
                Hashtable<String, Hashtable<String, Double>> primerMunicipio = new Hashtable<>();
                Hashtable<String, Double> primerLocalidad = new Hashtable<>();
                Hashtable<Double, String> primerLocalidadInv = new Hashtable<>();
                codigo_Dpto.put(cod_departamento, departamento);
                codigo_Municipio.put(cod_municipio, municipio);
                codigo_localidad.put(cod_localidad, localidad);
                localidad_X.put(cod_localidad, x);
                localidad_Y.put(cod_localidad, y);

                primerLocalidadInv.put(cod_localidad, localidad);
                codigo_municipioLocalidad.put(cod_municipio, primerLocalidadInv);
                primerLocalidad.put(localidad, cod_localidad);
                primerMunicipio.put(municipio, primerLocalidad);
                diccionario_UbicacionLocalidad.put(departamento, primerMunicipio);

            } else if (!diccionario_UbicacionLocalidad.get(departamento).containsKey(municipio)) {
                Hashtable<String, Double> primerLocalidad = new Hashtable<>();
                codigo_Municipio.put(cod_municipio, municipio);
                codigo_localidad.put(cod_localidad, localidad);
                localidad_X.put(cod_localidad, x);
                localidad_Y.put(cod_localidad, y);
                primerLocalidad.put(localidad, cod_localidad);

                Hashtable<Double, String> primerLocalidadInv = new Hashtable<>();

                primerLocalidadInv.put(cod_localidad, localidad);
                codigo_municipioLocalidad.put(cod_municipio, primerLocalidadInv);

                diccionario_UbicacionLocalidad.get(departamento).put(municipio, primerLocalidad);

            } else if (!diccionario_UbicacionLocalidad.get(departamento).get(municipio)
                    .containsKey(localidad)) {
                codigo_localidad.put(cod_localidad, localidad);
                localidad_X.put(cod_localidad, x);
                localidad_Y.put(cod_localidad, y);
                codigo_municipioLocalidad.get(cod_municipio).put(cod_localidad, localidad);
                diccionario_UbicacionLocalidad.get(departamento).get(municipio).put(localidad, cod_localidad);
            }
        }
    }

}

From source file:standarapp.algorithm.Lecture.java

private void fixXLSX(String nameIn, String nameOut, int sheet, int columnas[]) {
    XSSFWorkbook xwb = lectureXLSX(nameIn);
    XSSFSheet xsheet = xwb.getSheetAt(sheet);

    for (Row row : xsheet) {
        //xsheet.createRow(row.getRowNum());
        for (Cell cell : row) {
            try {
                if (columnas.length == 0 || containsInColumns(columnas, cell.getColumnIndex())) {
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        String contenido = cell.getStringCellValue();
                        if (!cell.getStringCellValue().equals("")) {
                            contenido = fixWords(contenido);
                            xsheet.getRow(row.getRowNum()).getCell(cell.getColumnIndex())
                                    .setCellValue(contenido);
                        } else {
                            xsheet.getRow(row.getRowNum()).getCell(cell.getColumnIndex()).setCellValue("");
                        }/*from   w w w .ja  v a2  s  .  c  om*/
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        double contenido_Numerico = cell.getNumericCellValue();
                        xsheet.getRow(row.getRowNum()).createCell(cell.getColumnIndex())
                                .setCellValue(contenido_Numerico);
                        break;
                    default:
                        System.err.print(cell + "\t\t");
                        xsheet.getRow(row.getRowNum()).createCell(cell.getColumnIndex())
                                .setCTCell((CTCell) cell);
                        break;
                    }
                }
            } catch (Exception e) {
            }
        }
    }

    try (FileOutputStream outputStream = new FileOutputStream(nameOut)) {
        xwb.write(outputStream);
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ReadRegistry.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(ReadRegistry.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:standarapp.algorithm.Lecture.java

public static XSSFSheet lectureXLSX(String nameFile, int page) {
    FileInputStream file;// w w  w  .  j ava2  s . c  o  m
    XSSFWorkbook excelFile = new XSSFWorkbook();
    XSSFSheet xsheet = excelFile.createSheet();
    //Reading the file which contains registries
    //Lectura del archivo xls de registros
    try {
        file = new FileInputStream(new File(nameFile));
        excelFile = new XSSFWorkbook(file);
        xsheet = excelFile.getSheetAt(page);
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ReadRegistry.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(ReadRegistry.class.getName()).log(Level.SEVERE, null, ex);
    }

    return xsheet;
}

From source file:steffen.haertlein.file.FileObject.java

License:Apache License

private void readExcelDocument() {
    try {//from w  ww .  j  a v a2  s .  com
        FileInputStream fs = new FileInputStream(f);
        XSSFWorkbook wb = new XSSFWorkbook(fs);
        XSSFSheet sh;
        String text = "";
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            sh = wb.getSheetAt(i);
            for (int j = sh.getFirstRowNum(); j <= sh.getLastRowNum(); j++) {
                XSSFRow currRow = sh.getRow(j);
                if (currRow == null || currRow.getFirstCellNum() == -1) {
                    continue;
                } else {
                    for (int k = currRow.getFirstCellNum(); k < currRow.getLastCellNum(); k++) {
                        if (currRow.getCell(k, Row.RETURN_BLANK_AS_NULL) == null) {
                            continue;
                        } else {
                            text += currRow.getCell(k) + "; ";
                        }
                    }
                    text += System.lineSeparator();
                }
            }
        }
        fs.close();
        wb.close();
        String[] xlsxLines = text.split(System.lineSeparator());
        for (String line : xlsxLines) {
            lines.add(line);
        }
    } catch (IOException e) {
        JOptionPane.showMessageDialog(null, "Fehler in readExcelDocument", "Fehler", JOptionPane.ERROR_MESSAGE);
        e.printStackTrace();
    }
}