Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt

Introduction

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

Prototype

@Override
public HSSFSheet getSheetAt(int index) 

Source Link

Document

Get the HSSFSheet object at the given index.

Usage

From source file:gov.nih.nci.caBIOApp.report.HSSFExcelSpreadsheet.java

License:BSD License

HSSFExcelSpreadsheet(HSSFWorkbook wb, int worksheetNum) throws IndexOutOfBoundsException {
    _workbook = wb;/* w  ww .ja  va2 s  .com*/
    int numSheets = wb.getNumberOfSheets();
    if (worksheetNum < 0 || worksheetNum >= numSheets) {
        throw new IndexOutOfBoundsException("No worksheet at index " + (worksheetNum + 1));
    }
    _sheet = wb.getSheetAt(worksheetNum);
}

---

From source file:gov.nih.nci.evs.browser.utils.ExcelUtil.java

License:Open Source License

public static String getHSSFHeader(String file, int sheet) {
    StringBuffer buf = new StringBuffer();
    try {/*from  w w  w.j  a  v a  2 s  .  c  om*/
        FileInputStream fis = new FileInputStream(new File(file));
        //Get the workbook instance for XLS file
        HSSFWorkbook workbook = new HSSFWorkbook(fis);
        try {
            fis.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        //Get first sheet from the workbook
        HSSFSheet hSSFSheet = workbook.getSheetAt(sheet);
        HSSFRow row = hSSFSheet.getRow(0);

        int cells = row.getPhysicalNumberOfCells();
        for (int c = 0; c < cells; c++) {
            HSSFCell cell = row.getCell(c);
            String value = null;

            switch (cell.getCellType()) {

            case HSSFCell.CELL_TYPE_FORMULA:
                value = cell.getCellFormula();
                break;

            case HSSFCell.CELL_TYPE_NUMERIC:
                value = "" + cell.getNumericCellValue();
                break;

            case HSSFCell.CELL_TYPE_STRING:
                value = cell.getStringCellValue();
                break;

            default:
            }
            buf.append(value);
            if (c < cells - 1) {
                buf.append("|");
            }
        }
    } catch (Exception ex) {
        ex.printStackTrace();
    }
    return buf.toString();
}

---

From source file:gov.nih.nci.evs.browser.utils.ExcelUtil.java

License:Open Source License

public static int getHSSFStartRow(String file, int sheet, int col, String code) {
    try {/*  w ww.ja  v  a  2  s  .c  om*/
        FileInputStream fis = new FileInputStream(new File(file));
        //Get the workbook instance for XLS file
        HSSFWorkbook workbook = new HSSFWorkbook(fis);
        try {
            fis.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        //Get first sheet from the workbook
        HSSFSheet hSSFSheet = workbook.getSheetAt(sheet);

        if (col == -1) {
            return 1;
        }

        //Get iterator to all the rows in current sheet
        Iterator<Row> rowIterator = hSSFSheet.iterator();

        //Get iterator to all cells of current row
        int lcv = 0;
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            if (row == null)
                return -1;
            //if (row.getCell(0).getStringCellValue().compareTo(code) == 0 ||
            if (row.getCell(col).getStringCellValue().compareTo(code) == 0) {
                return lcv;
            }

            lcv++;
        }
    } catch (Exception ex) {
        ex.printStackTrace();
    }
    return -1;
}

---

From source file:gov.nih.nci.evs.browser.utils.ExcelUtil.java

License:Open Source License

public static int getHSSFEndRow(String file, int sheet, int col, String code) {
    int num = -1;
    try {// w  ww .  ja  va 2 s . c  o  m
        FileInputStream fis = new FileInputStream(new File(file));
        //Get the workbook instance for XLS file
        HSSFWorkbook workbook = new HSSFWorkbook(fis);
        try {
            fis.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        //Get first sheet from the workbook
        HSSFSheet hSSFSheet = workbook.getSheetAt(sheet);

        if (col == -1) {
            return hSSFSheet.getLastRowNum();
        }

        //Get iterator to all the rows in current sheet
        Iterator<Row> rowIterator = hSSFSheet.iterator();

        //Get iterator to all cells of current row
        int lcv = 0;

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            if (row == null)
                return -1;
            //if (row.getCell(0).getStringCellValue().compareTo(code) == 0 ||
            if (row.getCell(col).getStringCellValue().compareTo(code) == 0) {
                num = lcv;
            }
            lcv++;
        }
    } catch (Exception ex) {
        ex.printStackTrace();
    }
    return num;
}

---

From source file:gr.abiss.calipso.domain.ExcelFile.java

License:Open Source License

public ExcelFile(InputStream is) {
    POIFSFileSystem fs = null;//from   w ww . ja v a 2s  . c om
    HSSFWorkbook wb = null;
    try {
        fs = new POIFSFileSystem(is);
        wb = new HSSFWorkbook(fs);
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow r = null;
    HSSFCell c = null;
    int row = 0;
    int col = 0;
    columns = new ArrayList<Column>();
    //========================== HEADER ====================================
    r = sheet.getRow(row);
    while (true) {
        c = r.getCell((short) col);
        if (c == null) {
            break;
        }
        String value = c.getStringCellValue();
        if (value == null || value.trim().length() == 0) {
            break;
        }
        Column column = new Column(value.trim());
        columns.add(column);
        col++;
    }
    //============================ DATA ====================================
    rows = new ArrayList<List<Cell>>();
    while (true) {
        row++;
        r = sheet.getRow(row);
        if (r == null) {
            break;
        }
        List rowData = new ArrayList(columns.size());
        boolean isEmptyRow = true;
        for (col = 0; col < columns.size(); col++) {
            c = r.getCell((short) col);
            Object value = null;
            switch (c.getCellType()) {
            case (HSSFCell.CELL_TYPE_STRING):
                value = c.getStringCellValue();
                break;
            case (HSSFCell.CELL_TYPE_NUMERIC):
                // value = c.getDateCellValue();
                value = c.getNumericCellValue();
                break;
            case (HSSFCell.CELL_TYPE_BLANK):
                break;
            default: // do nothing
            }
            if (value != null && value.toString().length() > 0) {
                isEmptyRow = false;
                rowData.add(new Cell(value));
            } else {
                rowData.add(null);
            }
        }
        if (isEmptyRow) {
            break;
        }
        rows.add(rowData);
    }
}

---

From source file:gtu._work.etc.TestCaseExcelMakerUI.java

License:Open Source License

private void initGUI() {
    try {//from ww  w  .  j a v a 2s.c  o m
        BorderLayout thisLayout = new BorderLayout();
        setDefaultCloseOperation(WindowConstants.DISPOSE_ON_CLOSE);
        getContentPane().setLayout(thisLayout);
        {
            jTabbedPane1 = new JTabbedPane();
            getContentPane().add(jTabbedPane1, BorderLayout.CENTER);
            {
                jPanel1 = new JPanel();
                BorderLayout jPanel1Layout = new BorderLayout();
                jPanel1.setLayout(jPanel1Layout);
                jTabbedPane1.addTab("jPanel1", null, jPanel1, null);
                {
                    jScrollPane1 = new JScrollPane();
                    jPanel1.add(jScrollPane1, BorderLayout.CENTER);
                    jScrollPane1.setPreferredSize(new java.awt.Dimension(573, 364));
                    {
                        DefaultTableModel model = JTableUtil.createModel(false, "??", "??",
                                "1", "2");
                        model.addRow(new Object[] { "", "", "", "" });
                        jTable1 = new JTable();
                        JTableUtil.defaultSetting(jTable1);
                        jScrollPane1.setViewportView(jTable1);
                        jTable1.setModel(model);
                        jTable1.addMouseListener(new MouseAdapter() {
                            public void mouseClicked(MouseEvent evt) {
                                System.out.println("jTable1.mouseClicked, event=" + evt);
                                List<JMenuItem> menuList = JTableUtil.newInstance(jTable1)
                                        .getDefaultJMenuItems();
                                JPopupMenuUtil.newInstance(jTable1).addJMenuItem(menuList).applyEvent(evt)
                                        .show();
                            }
                        });
                    }
                }
            }
            final HSSFWorkbook workBook = readFile();
            {
                jPanel2 = new JPanel();
                jTabbedPane1.addTab("jPanel2", null, jPanel2, null);
                {
                    executeBtn = new JButton();
                    jPanel2.add(executeBtn);
                    executeBtn.setText("execute");
                    executeBtn.setPreferredSize(new java.awt.Dimension(117, 45));
                    executeBtn.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent evt) {
                            File outputDir = JCommonUtil._jFileChooser_selectDirectoryOnly();
                            if (outputDir == null) {
                                JCommonUtil._jOptionPane_showMessageDialog_error("dir is not correct!");
                                return;
                            }

                            HSSFSheet sheet = workBook.getSheetAt(0);
                            HSSFCell cell_RC = sheet.getRow(0).getCell(1);
                            HSSFCell cell_model = sheet.getRow(1).getCell(1);
                            HSSFCell cell_model_Chn = sheet.getRow(1).getCell(5);
                            HSSFCell cell_Controller = sheet.getRow(4).getCell(0);
                            HSSFCell cell_Controller_persent = sheet.getRow(4).getCell(4);
                            HSSFCell cell_Controller_test = sheet.getRow(4).getCell(7);
                            HSSFCell cell_Controller_pic = sheet.getRow(4).getCell(8);
                            HSSFCell cell_Service = sheet.getRow(5).getCell(0);
                            HSSFCell cell_Service_persent = sheet.getRow(5).getCell(4);
                            HSSFCell cell_Service_test = sheet.getRow(5).getCell(7);
                            HSSFCell cell_Service_pic = sheet.getRow(5).getCell(8);

                            DefaultTableModel model = JTableUtil.newInstance(jTable1).getModel();

                            for (int ii = 0; ii < model.getRowCount(); ii++) {
                                String opName = (String) model.getValueAt(ii, 0);
                                String opChName = (String) model.getValueAt(ii, 1);
                                String persent1 = (String) model.getValueAt(ii, 2);
                                String persent2 = (String) model.getValueAt(ii, 3);
                                String operation = opName.substring(0, 2).toUpperCase();
                                opName = opName.replaceAll("[-_]", "").toLowerCase();
                                opName = opName.substring(0, 1).toUpperCase() + opName.substring(1);
                                String modelName = "FNM_" + operation + "_FR_"
                                        + opName.toUpperCase().substring(2);
                                String fileName = "FNM_" + operation + "_FR_"
                                        + opName.toUpperCase().substring(2);

                                cell_RC.setCellValue(operation);
                                cell_model.setCellValue(modelName);
                                cell_model_Chn.setCellValue(opChName);
                                cell_Controller.setCellValue(opName + "Controller");
                                cell_Controller_persent.setCellValue(persent1);
                                cell_Controller_test.setCellValue(opName + "ControllerTest");
                                cell_Controller_pic.setCellValue(opName + "ControllerReport.jpg");
                                cell_Service.setCellValue(opName + "ServiceImpl");
                                cell_Service_persent.setCellValue(persent2);
                                cell_Service_test.setCellValue(opName + "ServiceImplTest");
                                cell_Service_pic.setCellValue(opName + "ServiceImplReport.jpg");

                                try {
                                    ExcelUtil.getInstance().writeExcel(new File(outputDir, fileName + ".xls"),
                                            workBook);
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }
                            }

                        }
                    });
                }
            }
        }
        pack();
        this.setSize(594, 431);
        loadInitExcel();
    } catch (Exception e) {
        //add your error handling code here
        e.printStackTrace();
    }
}

---

From source file:gtu._work.etc.TestCaseExcelMakerUI.java

License:Open Source License

void loadInitExcel() throws Exception {
    File file = new File("C:/Users/gtu001/Desktop/RL-10?.xls");
    HSSFWorkbook book = ExcelUtil.getInstance().readExcel(file);
    Map<String, String> map = new HashMap<String, String>();
    for (int ii = 0; ii < book.getNumberOfSheets(); ii++) {
        HSSFSheet sheet = book.getSheetAt(ii);
        for (int jj = 0; jj <= sheet.getLastRowNum(); jj++) {
            if (sheet.getRow(jj).getCell(0) == null) {
                continue;
            }//from  ww  w.  j a  v  a  2s.c o m
            if (sheet.getRow(jj).getCell(1) == null) {
                continue;
            }
            String key = ExcelUtil.getInstance().readHSSFCell(sheet.getRow(jj).getCell(0)).toUpperCase();//title num
            String value = ExcelUtil.getInstance().readHSSFCell(sheet.getRow(jj).getCell(1));//title chn
            map.put(key, value);
        }
    }
    File dir = new File("C:/Users/gtu001/Desktop/ (2)");
    Set<String> list = new HashSet<String>();
    for (File f : dir.listFiles()) {
        list.add(f.getName().replaceAll("\\.jpg", "").replaceAll("[a-zA-Z]$", "").toUpperCase());
    }
    JTableUtil util = JTableUtil.newInstance(jTable1);
    StringBuilder sb = new StringBuilder();
    for (Iterator<String> it = list.iterator(); it.hasNext();) {
        String key = it.next();
        if (map.containsKey(key)) {
            util.getModel().addRow(new Object[] { "rl" + key, map.get(key), "", "" });
        } else {
            sb.append(key + "\n");
        }
    }
    if (sb.length() != 0) {
        JCommonUtil._jOptionPane_showMessageDialog_error(sb.toString());
    }
}

---

From source file:guardias.CalendarioExcel.java

private void leerExcelFile(File excelFile, List<Medico> listadoMedicos) {
    InputStream excelStream = null;
    try {/*from   ww  w  .  j  av a 2  s . co m*/
        excelStream = new FileInputStream(excelFile);
        // Representacin del ms alto nivel de la hoja excel.
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(excelStream);
        // Elegimos la hoja que se pasa por parmetro.
        HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
        // Objeto que nos permite leer un fila de la hoja excel, y de aqu extraer el contenido de las celdas.
        HSSFRow hssfRow;
        // Obtengo el nmero de filas ocupadas en la hoja
        int rows = hssfSheet.getLastRowNum();
        // Cadena que usamos para almacenar la lectura de la celda
        Date cellCalendario;
        String cellFestivo;
        String cellPeticion;

        this.leerGuardiasPrevistas(hssfSheet.getRow(FILA_DEFINICION_GUARDIAS_PREVISTAS), listadoMedicos);

        List<DiaCalendario> listadoDias = new ArrayList<>();
        // Para este ejemplo vamos a recorrer las filas obteniendo los datos que queremos            
        for (int r = COMIENZO_FILAS; r < rows; r++) {
            hssfRow = hssfSheet.getRow(r);
            if (hssfRow == null) {
                break;
            } else {
                cellCalendario = hssfRow.getCell(COLUMNA_FECHA_MES).getDateCellValue();

                if (cellCalendario != null) {
                    cellFestivo = hssfRow.getCell(COLUMNA_FESTIVO) == null ? ""
                            : hssfRow.getCell(COLUMNA_FESTIVO).getStringCellValue();
                    LOGGER.log(Level.FINE, "Row: {0} -> [Columna {1}: {2}] [Columna {3}: {4}] ",
                            new Object[] { r, COLUMNA_FECHA_MES, cellFestivo, COLUMNA_FESTIVO, cellFestivo });

                    DiaCalendario diaCalendario = new DiaCalendario();
                    diaCalendario.setTime(cellCalendario.getTime());

                    //Se comprueba si es sabado o Domingo
                    if (TratarFechas.esFinde(diaCalendario)) {
                        diaCalendario.setEsFinde(Boolean.TRUE);
                        //TODO setear nivel de importancia (cada dia del finde tiene una importancia)
                    } else {
                        diaCalendario.setEsFinde(Boolean.FALSE);
                    }

                    if (ES_FESTIVO.equalsIgnoreCase(cellFestivo)) {
                        //Por norma general los festivos se pondrn a dedo
                        diaCalendario.setEsFestivo(Boolean.TRUE);
                    } else {
                        diaCalendario.setEsFestivo(Boolean.FALSE);
                    }

                    //No se tiene en cuenta que tipo de falta tiene (solo tiene que estar vacio)
                    this.obtenerDisponibilidadMedicosEnExcel(listadoMedicos, hssfRow, diaCalendario,
                            listadoMedicos);

                    cellPeticion = hssfRow.getCell(COLUMNA_PETICION) == null ? ""
                            : hssfRow.getCell(COLUMNA_PETICION).getStringCellValue();
                    LOGGER.log(Level.FINE, "----{0}", cellPeticion);
                    diaCalendario.setPeticionMedico(cellPeticion);

                    listadoDias.add(diaCalendario);
                }
            }
        }
        this.setListadoDiasCalendario(listadoDias);
    } catch (FileNotFoundException fileNotFoundException) {
        LOGGER.log(Level.WARNING, "The file not exists (No se encontro el fichero): {0}",
                fileNotFoundException);
    } catch (IOException ex) {
        LOGGER.log(Level.WARNING, "Error in file procesing (Error al procesar el fichero): {0}", ex);
    } catch (ExceptionColumnaDisponibilidad ex) {
        LOGGER.log(Level.WARNING,
                "Error en lectura de celdas al leer la dispoibilidad, hay alguna celda que no es ni "
                        + CONSTANTE_FIESTA + " ni " + CONSTANTE_CONSULTA + "{0}",
                ex);
    } finally {
        try {
            excelStream.close();
        } catch (IOException ex) {
            LOGGER.log(Level.WARNING,
                    "Error in file processing after close it (Error al procesar el fichero despues de cerrarlo): {0}",
                    ex);
        }
    }
}

---

From source file:guineu.modules.configuration.parameters.ParameterDialog.java

License:Open Source License

private void openExcel(String filePath) {
    try {//from   w ww  .ja v a 2s. c  om
        HSSFWorkbook book;
        book = this.openExcelFile(filePath);
        HSSFSheet sheet;
        try {
            sheet = book.getSheetAt(0);
            this.readRows(sheet);
        } catch (Exception exception) {
        }
    } catch (IOException ex) {
    }
}

---

From source file:helpers.Excel.ExcelDataFormat.java

public Object unmarshal(Exchange exchng, InputStream in) throws Exception {
    logger.info("Unmarshalling XLS");
    Object res = exchng.getIn().getBody();
    GenericFile genfile = (GenericFile) res;

    if (genfile.getFileNameOnly().endsWith("xlsx")) {
        return unmarshalXLSX(exchng, in);

    }/*  ww w .ja v a 2s.c om*/
    HSSFWorkbook workbook = new HSSFWorkbook(in);
    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

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

    if (importType != ImportType.FORMATTED) {
        return marshalAsArray(sheet.iterator());
    } else {
        OneExcel excel = new OneExcel();
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            OneExcelSheet onesheet = marshalAsStructure(workbook.getSheetAt(i).iterator(), evaluator);
            logger.info("Loading sheet:" + i);
            logger.info("Data:" + onesheet.data.size());
            if (onesheet.data.size() > 0)
                excel.sheets.add(onesheet);
        }
        logger.info("Total sheets:" + excel.sheets.size());

        ArrayList<HashMap<String, Object>> resu = excel.GenerateResult();
        HashMap<String, Object> mappings = excel.GenerateMappings();

        exchng.getOut().setHeader("mappings", mappings);
        exchng.getOut().setHeader("xlsdata", resu);

        return resu;

    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »