Example usage for org.apache.poi.hssf.usermodel HSSFSheet getRow

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. getRow

Introduction

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

Prototype

@Override
public HSSFRow getRow(int rowIndex)

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

From source file:hr.restart.sisfun.frmReportxList.java

License:Apache License

void fillDataProc(File orig, HSSFWorkbook wb) {
    DataSet logo = dM.getDataModule().getLogotipovi();
    DataSet orgs = dM.getDataModule().getOrgstruktura();
    String corg = jpc.getCorg();// w  ww . j  a  va 2 s.  com
    while (!ld.raLocate(logo, "CORG", corg)) {
        if (!ld.raLocate(orgs, "CORG", corg)) {
            JOptionPane.showMessageDialog(this.getWindow(), "Greka u organizacijskim jedinicama!", "Greka",
                    JOptionPane.ERROR_MESSAGE);
            return;
        }
        if (orgs.getString("PRIPADNOST").equals(corg)) {
            JOptionPane.showMessageDialog(this.getWindow(), "Nije definiran logotip za knjigovodstvo!",
                    "Greka", JOptionPane.ERROR_MESSAGE);
            return;
        }
        corg = orgs.getString("PRIPADNOST");
    }
    raProcess.checkClosing();

    StorageDataSet gk = Gkstavke.getDataModule().getScopedSet("BROJKONTA ID IP");
    raProcess.fillScratchDataSet(gk, "SELECT brojkonta,id,ip FROM gkstavke WHERE "
            + jpc.getCondition().and(Condition.between("DATUMKNJ", fld, "DATFROM", "DATTO")));
    StorageDataSet ogk = Gkstavke.getDataModule().getScopedSet("BROJKONTA ID IP");
    Timestamp old = Util.getUtil().addYears(fld.getTimestamp("DATFROM"), -1);
    raProcess.fillScratchDataSet(ogk,
            "SELECT brojkonta,id,ip FROM gkstavke WHERE " + jpc.getCondition().and(Condition.between("DATUMKNJ",
                    Util.getUtil().getFirstDayOfYear(old), Util.getUtil().getLastDayOfYear(old))));
    gk.enableDataSetEvents(false);
    gk.setSort(new SortDescriptor(new String[] { "BROJKONTA" }));
    ogk.enableDataSetEvents(false);
    ogk.setSort(new SortDescriptor(new String[] { "BROJKONTA" }));

    HSSFDataFormat df = wb.createDataFormat();

    HSSFSheet sh = wb.getSheetAt(0);
    if (sh == null)
        throw new RuntimeException("Greka u plahti!");

    DataSet rep = Repxdata.getDataModule().getTempSet(Condition.equal("CREP", reps));
    rep.open();

    raProcess.checkClosing();
    for (rep.first(); rep.inBounds(); rep.next()) {
        HSSFRow hr = sh.getRow((short) (rep.getInt("RED") - 1));
        HSSFCell cell = hr.getCell((short) (rep.getInt("KOL") - 1));
        if ("S".equals(rep.getString("TIP"))) {
            fillString(cell, logo, rep.getString("DATA"));
            cell.getCellStyle().setDataFormat(df.getFormat("text"));
        } else if ("2".equals(rep.getString("TIP"))) {
            fillNum(cell, gk, ogk, rep.getString("DATA"));
            cell.getCellStyle().setDataFormat(df.getFormat("#,##0.00"));
        } else if ("D".equals(rep.getString("TIP"))) {
            fillDate(cell, rep.getString("DATA"));
            cell.getCellStyle().setDataFormat(df.getFormat("dd.mm.yyyy"));
        }
        raProcess.checkClosing();
    }
    String oname = orig.getAbsolutePath();
    oname = oname.substring(0, oname.length() - 4);

    FileOutputStream out = null;

    try {
        out = new FileOutputStream(oname + "-RA.xls");
        wb.write(out);
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (out != null)
            try {
                out.close();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
    }
}

From source file:ia_app.StatsPanel.java

/**
 * Creates new form StatsPanel//  w w w .  j a v  a  2 s.c om
 */
public StatsPanel() throws FileNotFoundException, IOException {
    initComponents();

    FileInputStream fis = new FileInputStream(new File("games.xls"));
    HSSFWorkbook wb = new HSSFWorkbook(fis);
    HSSFSheet sheet = wb.getSheetAt(0);
    FormulaEvaluator forEval = wb.getCreationHelper().createFormulaEvaluator();
    Row row1 = sheet.getRow(1);
    for (Cell cell : row1) {
        switch (forEval.evaluateInCell(cell).getCellType()) {
        case Cell.CELL_TYPE_NUMERIC:
            this.makes += cell.getNumericCellValue();
            break;
        case Cell.CELL_TYPE_STRING:
            break;
        }
    }
    this.jTextFieldMakes.setText(makes + "");

    Row row2 = sheet.getRow(2);
    for (Cell cell : row2) {
        switch (forEval.evaluateInCell(cell).getCellType()) {
        case Cell.CELL_TYPE_NUMERIC:
            this.attempts += cell.getNumericCellValue();
            break;
        case Cell.CELL_TYPE_STRING:
            break;
        }
    }
    this.jTextFieldAttempts.setText(attempts + "");
    this.jTextFieldFGPct.setText("58.3%");

    Row row3 = sheet.getRow(3);
    for (Cell cell : row3) {
        switch (forEval.evaluateInCell(cell).getCellType()) {
        case Cell.CELL_TYPE_NUMERIC:
            this.threeMakes += cell.getNumericCellValue();
            break;
        case Cell.CELL_TYPE_STRING:
            break;
        }
    }

    Row row4 = sheet.getRow(4);
    for (Cell cell : row4) {
        switch (forEval.evaluateInCell(cell).getCellType()) {
        case Cell.CELL_TYPE_NUMERIC:
            this.threeAttempts += cell.getNumericCellValue();
            break;
        case Cell.CELL_TYPE_STRING:
            break;
        }
    }
    this.jTextField3Pct.setText("80%");

    /*for(Row column : sheet){
    for(Cell cell : column){
        switch(forEval.evaluateInCell(cell).getCellType()){
            case Cell.CELL_TYPE_NUMERIC:
                System.out.print(cell.getNumericCellValue() + "\t\t");
                break;
            case Cell.CELL_TYPE_STRING:
                break;
        }
    } 
    }
    */

}

From source file:Import.Utils.XSSFConvert.java

/**
 * @param destination the sheet to create from the copy.
 * @param the sheet to copy./*from   w ww.  ja  va  2 s  .  c  o  m*/
 * @param copyStyle true copy the style.
 */
public static void copySheets(HSSFSheet source, XSSFSheet destination, boolean copyStyle) {
    int maxColumnNum = 0;
    Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null;
    for (int i = source.getFirstRowNum(); i <= source.getLastRowNum(); i++) {
        HSSFRow srcRow = source.getRow(i);
        XSSFRow destRow = destination.createRow(i);
        if (srcRow != null) {
            copyRow(source, destination, srcRow, destRow, styleMap);
            if (srcRow.getLastCellNum() > maxColumnNum) {
                maxColumnNum = srcRow.getLastCellNum();
            }
        }
    }
    for (int i = 0; i <= maxColumnNum; i++) {
        destination.setColumnWidth(i, source.getColumnWidth(i));
    }
}

From source file:Importers.ExcelImporter.java

License:Apache License

@Override
public DefaultMutableTreeNode readFile(File file) {
    System.out.println("==ExcelImporter=readFile: " + file.getAbsolutePath());
    DefaultMutableTreeNode root = new DefaultMutableTreeNode("vulns");
    try {//from w w  w. j a  va  2  s  .  c  o m

        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getPhysicalNumberOfRows();

        int cols = 0; // No of columns
        int tmp = 0;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                tmp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (tmp > cols) {
                    cols = tmp;
                }
            }
        }

        for (int r = 1; r < rows; r++) {
            row = sheet.getRow(r);
            if (row != null) {

                // Create a new vuln
                Vulnerability vuln = new Vulnerability();
                vuln.setTitle("NEW");
                vuln.setIs_custom_risk(true);
                vuln.setRisk_category("None");

                for (int c = 0; c < cols; c++) {
                    cell = row.getCell(c);
                    if (cell != null) {
                        // Your code here
                        String value = cell.getStringCellValue();
                        switch (c) {
                        case 1:// title
                            vuln.setTitle(value);
                            break;
                        case 2: // Risk
                            CellStyle style = cell.getCellStyle();
                            short colorIdx = style.getFillForegroundColor();
                            HSSFPalette palette = ((HSSFWorkbook) wb).getCustomPalette();
                            HSSFColor color = palette.getColor(colorIdx);
                            String cc = color.getHexString();
                            System.out.println(cc);
                            if (cc.equalsIgnoreCase("8080:8080:0")) {
                                vuln.setRisk_category("Critical");
                            } else if (cc.equalsIgnoreCase("FFFF:0:0")) {
                                vuln.setRisk_category("High");
                            } else if (cc.equalsIgnoreCase("FFFF:6666:0")) {
                                vuln.setRisk_category("Medium");
                            } else if (cc.equalsIgnoreCase("F2F2:ECEC:0")) {
                                vuln.setRisk_category("Low");
                            } else if (cc.equalsIgnoreCase("0:0:FFFF")) {
                                vuln.setRisk_category("Info");
                            }

                            break;
                        case 3:// cvss string
                            System.out.println(value);
                            if (value.equalsIgnoreCase("No CVSS Vector")) {
                                vuln.setIs_custom_risk(true);
                            } else {
                                vuln.setIs_custom_risk(false);
                                vuln.setCvss_vector_string("CVSS2#" + value);
                            }
                            break;
                        case 4://Description
                            vuln.setDescription(value);
                            break;
                        case 5://Recommendation
                            vuln.setRecommendation(value);
                            break;
                        case 6://Affected Hosts
                            try {
                                String[] lines = value.split("\n");

                                for (String line : lines) {
                                    String[] bits = line.split(" ");
                                    Host host = new Host();
                                    host.setIp_address(bits[0]);
                                    String portprotocol = bits[2];
                                    host.setPortnumber(portprotocol.split("/")[0]);
                                    host.setProtocol(portprotocol.split("/")[1]);
                                    vuln.addAffectedHost(host);
                                }
                            } catch (Exception ex) {
                                ;
                            }
                            break;
                        }

                    }
                }
                System.out.println(vuln);

                root.add(new DefaultMutableTreeNode(vuln));
            }
        }

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

    return root;
}

From source file:in.darbose.classroom.statistics.StatisticsFragment.java

License:Apache License

/**
 * Converts all attendances into excel format
 *///  w w w  .j a v  a2s .com
private void convertToExcel() {
    int length = classroomArrayList.size();

    HSSFWorkbook wb = new HSSFWorkbook();
    for (int i = 0; i < length; i++) {
        Classroom classroom = classroomArrayList.get(i);

        HSSFSheet sheet = wb.createSheet(classroom.getName());

        //header
        HashMap<String, Integer> date_column_map = new HashMap<String, Integer>();
        ArrayList<String> dates = new ArrayList<String>();
        int rowNumber = 0;
        int colNumber = 1;
        HSSFRow row = sheet.createRow(rowNumber);

        for (int j = 0; j < attendanceArrayList.size(); j++) {
            Attendance attendance = attendanceArrayList.get(j);

            if (classroom.getId() == attendance.getClassroomId() && !dates.contains(attendance.getDateTime())) {

                HSSFCell cellDate = row.createCell(colNumber);
                cellDate.setCellStyle(ExcelStyleManager.getHeaderCellStyle(wb));

                cellDate.setCellValue(attendance.getDateTime());

                dates.add(attendance.getDateTime());
                date_column_map.put(attendance.getDateTime(), colNumber);

                colNumber++;
            }
        }

        //students list at the left column
        HashMap<Integer, Integer> student_row_map = new HashMap<Integer, Integer>();
        ArrayList<Integer> studentIds = new ArrayList<Integer>();
        rowNumber = 1;
        for (int j = 0; j < attendanceArrayList.size(); j++) {
            Attendance attendance = attendanceArrayList.get(j);

            if (classroom.getId() == attendance.getClassroomId()) {
                if (!studentIds.contains(attendance.getStudentId())) { //another student
                    row = sheet.createRow(rowNumber);

                    HSSFCell cellStudent = row.createCell(0);
                    cellStudent.setCellStyle(ExcelStyleManager.getLeftColumnCellStyle(wb));

                    cellStudent.setCellValue(attendance.getStudentName());

                    studentIds.add(attendance.getStudentId());
                    student_row_map.put(attendance.getStudentId(), rowNumber);

                    rowNumber++;
                }
            }
        }

        //now get column number from date columns
        //and get row number from student rows
        //match row-column pair and print into cell
        for (int j = 0; j < attendanceArrayList.size(); j++) {
            Attendance attendance = attendanceArrayList.get(j);

            if (classroom.getId() == attendance.getClassroomId()) {
                rowNumber = student_row_map.get(attendance.getStudentId());
                colNumber = date_column_map.get(attendance.getDateTime());

                row = sheet.getRow(rowNumber);

                HSSFCell cellPresence = row.createCell(colNumber);
                cellPresence.setCellStyle(ExcelStyleManager.getContentCellStyle(wb));

                cellPresence.setCellValue(attendance.getPresent());
            }
        }
    }

    if (length > 0)
        writeIntoFile(wb);

    swipeRefreshLayout.setRefreshing(false);
}

From source file:include.excel_import.Outter.java

License:Open Source License

private boolean blankTitle() {
    Vector vector = getTablesName();
    for (int i = 0; i < vector.size(); i++) {
        HSSFSheet hssfsheet = wb.getSheetAt(i);
        HSSFRow hssfrow = hssfsheet.getRow(0);
        if (hssfrow == null) {
            message += ",SHEET";
            return true;
        }/*from   ww w  .j av a2s .c  o  m*/
        Iterator iterator = hssfrow.cellIterator();
        int j;
        for (j = 0; iterator.hasNext(); j++) {
            HSSFCell hssfcell = (HSSFCell) iterator.next();
        }

        for (int k = 0; k < j - 1; k++) {
            HSSFCell hssfcell1 = hssfrow.getCell((short) k);
            if (hssfcell1 == null)
                return true;
            if (hssfcell1.getCellType() != 1) {
                message += (String) vector.elementAt(i) + "" + (k + 1) + "?<br>";
                return true;
            }
            if (hssfcell1.getCellType() == 3) {
                message += (String) vector.elementAt(i) + "" + (k + 1) + "<br>";
                return true;
            }
        }

    }

    return false;
}

From source file:include.excel_import.Outter.java

License:Open Source License

private void chop() {
    Vector vector = getTablesName();
    try {//from w  w w .j ava2  s. c o  m
        for (int i = 0; i < vector.size(); i++) {
            boolean flag = true;
            HSSFSheet hssfsheet = wb.getSheetAt(i);
            int j = getColumnCount(hssfsheet);
            for (int k = 0; k < j; k++)
                if (isBlankColumn(k, hssfsheet))
                    removeColumn(k, hssfsheet);

            int l = getRowCount((String) vector.elementAt(i));
            for (int i1 = 0; i1 < l; i1++) {
                HSSFRow hssfrow = hssfsheet.getRow(i1);
                if (isBlankRow(hssfrow))
                    hssfsheet.removeRow(hssfrow);
            }

        }

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

From source file:include.excel_import.Outter.java

License:Open Source License

private int getColumnCount(HSSFSheet hssfsheet) {
    HSSFRow hssfrow = hssfsheet.getRow(0);
    Iterator iterator = hssfrow.cellIterator();
    int i;/*from   www.  j a  v  a  2 s. c  o  m*/
    for (i = 0; iterator.hasNext(); i++) {
        HSSFCell hssfcell = (HSSFCell) iterator.next();
    }

    return i;
}

From source file:include.excel_import.Outter.java

License:Open Source License

public String getItemType(String s, String s1) throws Exception {
    int i = getItemsName(s1).indexOf(s);
    if (i == -1)/*from w  ww  . j a va 2 s  .  c om*/
        throw new Exception("Item not found");
    HSSFSheet hssfsheet = wb.getSheetAt(getTablesName().indexOf(s1));
    HashMap hashmap = new HashMap();
    for (int j = 1; j < getRowCount(s1); j++) {
        HSSFRow hssfrow = hssfsheet.getRow(j);
        HSSFCell hssfcell = hssfrow.getCell((short) i);
        String s2 = getCellType(hssfcell);
        if (!hashmap.containsKey(s2))
            hashmap.put(s2, new Integer(1));
        else
            hashmap.put(s2, new Integer(((Integer) hashmap.get(s2)).intValue() + 1));
    }

    Set set = hashmap.keySet();
    Iterator iterator = set.iterator();
    Integer integer = new Integer(0);
    String s3 = "BLANK";
    int k = 0;
    while (iterator.hasNext()) {
        String s4 = (String) iterator.next();
        if (k == 0) {
            integer = (Integer) hashmap.get(s4);
            s3 = s4;
            k++;
        } else if (integer.compareTo((Integer) hashmap.get(s4)) < 0) {
            integer = (Integer) hashmap.get(s4);
            s3 = s4;
        }
    }
    return s3;
}

From source file:include.excel_import.Outter.java

License:Open Source License

public Vector getItemsName(String s) throws Exception {
    Vector vector = new Vector();
    int i = getTablesName().indexOf(s);
    if (i == -1)/*from w  w w  .  j av  a  2s .c om*/
        throw new Exception("Table not found");
    HSSFSheet hssfsheet = wb.getSheetAt(i);
    HSSFRow hssfrow = hssfsheet.getRow(0);
    if (hssfrow == null)
        return null;
    Iterator iterator = hssfrow.cellIterator();
    int j;
    for (j = 0; iterator.hasNext(); j++) {
        HSSFCell hssfcell = (HSSFCell) iterator.next();
    }

    for (int k = 0; k < j; k++) {
        HSSFCell hssfcell1 = hssfrow.getCell((short) k);
        if (isBlankColumn(k, hssfsheet) && hssfcell1 != null)
            removeColumn(k, hssfsheet);
        if (hssfcell1 != null)
            pump(vector, hssfcell1);
    }

    return vector;
}