Example usage for org.apache.poi.ss.usermodel Sheet getFirstRowNum

Introduction

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

Prototype

int getFirstRowNum();

Source Link

Document

Gets the first row on the sheet.

Usage

From source file:plugins.excel.client.util.ExcelReader.java

License:Microsoft Reference Source License

private RecordSet createRecordSetFromSheet(Sheet sheet, boolean columnNamesInFirstLine) {
    RecordSet rs = new RecordSet();
    ArrayList<String> columnNames = null;
    HashMap<String, Integer> types = null;

    if (sheet.getLastRowNum() > 0) {
        columnNames = this.getColumnNames(sheet.getRow(sheet.getFirstRowNum()), columnNamesInFirstLine);
        types = this.getColumnTypes(sheet, columnNames, columnNamesInFirstLine);

        rs.addFields(columnNames, types);

        return rs;
    }/* w  w  w  . j a  v a 2  s  . c  o  m*/
    return null;
}

---

From source file:plugins.excel.client.util.ExcelReader.java

License:Microsoft Reference Source License

public RecordSet importExcelFile(File f, boolean columnNamesInFirstLine) throws ClinSysException {
    ArrayList<String> columnNames = null;
    RecordSet rs = new RecordSet();
    HashMap<String, Object> row;
    Row rowObject = null;/*from  ww w  .  ja v a  2  s . c  o m*/
    Cell cell = null;
    FileInputStream file = null;
    Sheet sheet = null;

    try {
        file = new FileInputStream(f);
    } catch (FileNotFoundException e1) {
        e1.printStackTrace();
    }

    if (ExcelFileFilter.getExtension(f).equals("xls")) {
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(file);
            sheet = workbook.getSheetAt(0);

        } catch (IOException e) {
            e.printStackTrace();
        }
    } else {
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(file);
            sheet = workbook.getSheetAt(0);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    rs = this.createRecordSetFromSheet(sheet, columnNamesInFirstLine);
    rs.setTableName(f.getName());
    columnNames = rs.getFields();

    for (int j = sheet.getFirstRowNum() + (columnNamesInFirstLine ? 1 : 0); j <= sheet.getLastRowNum(); j++) {
        rowObject = sheet.getRow(j);
        row = new HashMap<String, Object>();

        for (int i = 0; i < columnNames.size(); i++) {
            cell = rowObject.getCell(i);
            if (cell != null) {
                row.put(columnNames.get(i), this.getCellValue(cell, rs.getType(columnNames.get(i))));
            } else {
                row.put(columnNames.get(i), null);
            }
        }

        rs.addRow(columnNames, row);
    }

    return rs;
}

---

From source file:RepairLog.Parse.java

public void ParseEXCEL(String FileName, Connection connect)
        throws IOException, InvalidFormatException, SQLException {

    System.out.print("Parsing");
    InputStream file = new FileInputStream(FileName);
    Workbook wb = WorkbookFactory.create(file);
    Sheet sheet = wb.getSheetAt(0);
    String insert = "insert into eventlog values (?,1,?,?,?,?,?,?)";
    System.out.println(sheet.getFirstRowNum() + 1);
    PreparedStatement p = connect.prepareCall(insert);
    for (int i = sheet.getFirstRowNum() + 1; i < sheet.getLastRowNum(); i++) {
        String Request = sheet.getRow(i).getCell(3).toString();
        String EmpName = sheet.getRow(i).getCell(2).toString();
        String EventType = sheet.getRow(i).getCell(4).toString();
        String case_id = sheet.getRow(i).getCell(1).toString();
        //int c = Integer.parseInt(case_id);
        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        Date time = sheet.getRow(i).getCell(5).getDateCellValue();
        long timest = time.getTime();
        java.sql.Timestamp Timestamp = new java.sql.Timestamp(timest);
        //int id = Integer.parseInt(Request);
        p.setInt(1, i);/*from   w  ww .  j a va  2 s .  c  o m*/
        p.setString(3, EmpName);
        p.setString(2, case_id);
        p.setString(4, Request);
        p.setString(5, EventType);
        p.setTimestamp(6, Timestamp);
        p.setString(7, null);
        p.executeUpdate();
    }
}

---

From source file:uk.ac.leeds.ccg.andyt.projects.pfi.XSLX2CSV.java

/**
 * Called to convert the contents of the currently opened workbook into a
 * CSV file.//from  w  w  w  .j a v a2 s  .  c  o m
 */
private void convertToCSV(File excelFile, File destination) {
    Sheet sheet = null;
    Row row = null;
    int lastRowNum = 0;
    int firstRowNum = 0;
    this.csvData = new ArrayList<ArrayList<ArrayList<String>>>();

    System.out.println("Converting files contents to CSV format.");

    // Discover how many sheets there are in the workbook....
    int numSheets = this.workbook.getNumberOfSheets();
    // and then iterate through them.
    for (int sheetid = 0; sheetid < numSheets; sheetid++) {

        this.csvData.add(new ArrayList<ArrayList<String>>());

        // Get a reference to a sheet and check to see if it contains
        // any rows.
        sheet = this.workbook.getSheetAt(sheetid);

        if (sheet.getPhysicalNumberOfRows() > 0) {

            // Note down the index number of the bottom-most row and
            // then iterate through all of the rows on the sheet starting
            // from the very first row - number 1 - even if it is missing.
            // Recover a reference to the row and then call another method
            // which will strip the data from the cells and build lines
            // for inclusion in the resylting CSV file.
            lastRowNum = sheet.getLastRowNum();
            firstRowNum = sheet.getFirstRowNum();
            for (int j = firstRowNum; j <= lastRowNum; j++) {
                row = sheet.getRow(j);
                this.rowToCSV(row, sheetid);
            }

        }
        try {
            // Save the CSV file away using the newly constricted file name
            // and to the specified directory.
            this.saveCSVFile(destination);
        } catch (IOException ex) {
            Logger.getLogger(XSLX2CSV.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

---

From source file:uk.ac.liverpool.spreadsheet.ExcelFeatureAnalysis.java

License:Apache License

private static void analyseSheet(Sheet ss, Element s, Namespace n, ExcelFeatureAnalysis efa) {
    // generic part
    boolean costumFormatting = false;
    boolean formulae = false;
    boolean UDF = false;
    boolean hasComments = false;

    Set<String> udfs = new HashSet<String>();
    FormulaEvaluator evaluator = ss.getWorkbook().getCreationHelper().createFormulaEvaluator();

    s.setAttribute("name", ss.getSheetName());
    s.setAttribute("firstRow", "" + ss.getFirstRowNum());
    s.setAttribute("lastRow", "" + ss.getLastRowNum());
    try {//from  ww w  .  j ava2  s .c o m
        s.setAttribute("forceFormulaRecalc", "" + ss.getForceFormulaRecalculation());
    } catch (Throwable x) {
        //x.printStackTrace();
    }

    // shapes in detail? 
    Footer footer = ss.getFooter();
    if (footer != null) {
        s.setAttribute("footer", "true");
    }
    Header header = ss.getHeader();
    if (header != null) {
        s.setAttribute("header", "true");
    }
    PaneInformation paneInformation = ss.getPaneInformation();
    if (paneInformation != null) {
        s.setAttribute("panels", "true");
    }

    HSSFSheet hs = null;
    XSSFSheet xs = null;
    if (ss instanceof HSSFSheet) {
        hs = (HSSFSheet) ss;
        try {
            if (hs.getDrawingPatriarch() != null) {
                if (hs.getDrawingPatriarch().containsChart())
                    s.addContent(new Element("charts", sn));
                if (hs.getDrawingPatriarch().countOfAllChildren() > 0)
                    s.addContent(new Element("shapes", sn));
            }
        } catch (Exception x) {
            x.printStackTrace();
        }

        if (hs.getSheetConditionalFormatting().getNumConditionalFormattings() > 0) {
            s.setAttribute("conditionalFormatting", "true");
        }
    }
    if (ss instanceof XSSFSheet) {
        xs = (XSSFSheet) ss;

    }
    Iterator<Row> rows = ss.rowIterator();

    int firstColumn = (rows.hasNext() ? Integer.MAX_VALUE : 0);
    int endColumn = 0;
    while (rows.hasNext()) {
        Row row = rows.next();
        short firstCell = row.getFirstCellNum();
        if (firstCell >= 0) {
            firstColumn = Math.min(firstColumn, firstCell);
            endColumn = Math.max(endColumn, row.getLastCellNum());
        }
    }
    s.setAttribute("firstColumn", "" + firstColumn);
    s.setAttribute("lastColumn", "" + endColumn);
    rows = ss.rowIterator();
    while (rows.hasNext()) {
        Row row = rows.next();
        for (Cell cell : row)
            if (cell != null) {
                try {
                    if (!cell.getCellStyle().getDataFormatString().equals("GENERAL"))
                        costumFormatting = true;
                } catch (Throwable t) {
                }

                if (cell.getCellComment() != null)
                    hasComments = true;
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    // System.out.println(cell.getRichStringCellValue().getString());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    //                        if (DateUtil.isCellDateFormatted(cell)) {
                    //                            // System.out.println(cell.getDateCellValue());
                    //                        } else {
                    //                            // System.out.println(cell.getNumericCellValue());
                    //                        }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    // System.out.println(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    // System.out.println(cell.getCellFormula());
                    formulae = true;
                    if (!UDF)
                        try {
                            evaluator.evaluate(cell);
                        } catch (Exception x) {
                            if (x instanceof NotImplementedException) {
                                Throwable e = x;

                                //e.printStackTrace();
                                while (e != null) {
                                    for (StackTraceElement c : e.getStackTrace()) {
                                        if (c.getClassName().contains("UserDefinedFunction")) {
                                            UDF = true;
                                            System.out.println("UDF " + e.getMessage());
                                            udfs.add(e.getMessage());
                                        }
                                    }
                                    e = e.getCause();
                                }

                            }
                        }
                    break;
                default:
                }

            }
    }
    if (costumFormatting) {
        Element cf = new Element("customisedFormatting", sn);
        s.addContent(cf);
    }
    if (formulae) {
        Element cf = new Element("formulae", sn);
        s.addContent(cf);
    }
    if (UDF) {
        Element cf = new Element("userDefinedFunctions", sn);
        for (String sss : udfs)
            cf.addContent(new Element("userDefinedFunction", sn).setAttribute("functionName", sss));
        s.addContent(cf);
    }
    if (hasComments) {
        Element cf = new Element("cellComments", sn);
        s.addContent(cf);
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• »