Example usage for org.apache.poi.hssf.usermodel HSSFRow getCell

List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFRow
  6. getCell

Introduction

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

Prototype

@Override
public HSSFCell getCell(int cellnum)

Source Link

Document

Get the hssfcell representing a given column (logical cell) 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 w  w . j ava  2 s  .c o m
    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:Import.Utils.XSSFConvert.java

/**
 * @param srcSheet the sheet to copy.//w  ww .  j  a v a 2  s .  c om
 * @param destSheet the sheet to create.
 * @param srcRow the row to copy.
 * @param destRow the row to create.
 * @param styleMap -
 */
public static void copyRow(HSSFSheet srcSheet, XSSFSheet destSheet, HSSFRow srcRow, XSSFRow destRow,
        Map<Integer, HSSFCellStyle> styleMap) {
    // manage a list of merged zone in order to not insert two times a
    // merged zone
    Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
    destRow.setHeight(srcRow.getHeight());
    // pour chaque row
    for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
        HSSFCell oldCell = srcRow.getCell(j); // ancienne cell
        XSSFCell newCell = destRow.getCell(j); // new cell
        if (oldCell != null) {
            if (newCell == null) {
                newCell = destRow.createCell(j);
            }
            // copy chaque cell
            copyCell(oldCell, newCell, styleMap);
            // copy les informations de fusion entre les cellules
            // System.out.println("row num: " + srcRow.getRowNum() +
            // " , col: " + (short)oldCell.getColumnIndex());
            CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(),
                    (short) oldCell.getColumnIndex());

            if (mergedRegion != null) {
                // System.out.println("Selected merged region: " +
                // mergedRegion.toString());
                CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(),
                        mergedRegion.getLastRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
                // System.out.println("New merged region: " +
                // newMergedRegion.toString());
                CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
                if (isNewMergedRegion(wrapper, mergedRegions)) {
                    mergedRegions.add(wrapper);
                    destSheet.addMergedRegion(wrapper.range);
                }
            }
        }
    }

}

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 ava2 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: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  a  va 2 s  . 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

public String getItemType(String s, String s1) throws Exception {
    int i = getItemsName(s1).indexOf(s);
    if (i == -1)/*w  ww.  j a  va2s  .  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 getItemValues(String s, String s1) throws Exception {
    Vector vector = new Vector();
    int i = getItemsName(s1).indexOf(s);
    if (i == -1)//www  .j  av  a 2s . c  om
        throw new Exception("Item not found");
    HSSFSheet hssfsheet = wb.getSheetAt(getTablesName().indexOf(s1));
    Iterator iterator = hssfsheet.rowIterator();
    int j = 0;
    while (iterator.hasNext())
        if (hssfsheet != null) {
            HSSFRow hssfrow = (HSSFRow) iterator.next();
            HSSFCell hssfcell = hssfrow.getCell((short) i);
            if (j > 0 && hssfcell != null)
                pump(vector, hssfcell, getItemType(s, s1));
            j++;
        }
    return vector;
}

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)/*ww w . j a  v  a 2s.  co  m*/
        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;
}

From source file:include.excel_import.Outter.java

License:Open Source License

public Vector getRowItemsValues(String s, int i) throws Exception {
    Vector vector = new Vector();
    int j = getTablesName().indexOf(s);
    if (j == -1)//from  w ww.j  a v  a2s. c o m
        throw new Exception("Table not found");
    HSSFSheet hssfsheet = wb.getSheetAt(j);
    HSSFRow hssfrow = hssfsheet.getRow((short) i);
    Iterator iterator = hssfrow.cellIterator();
    int k;
    for (k = 0; iterator.hasNext(); k++) {
        HSSFCell hssfcell = (HSSFCell) iterator.next();
    }

    for (int l = 0; l < k; l++) {
        HSSFCell hssfcell1 = hssfrow.getCell((short) l);
        pump(vector, hssfcell1);
    }

    return vector;
}

From source file:include.excel_import.Outter.java

License:Open Source License

public boolean hasBlank() {
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        HSSFSheet hssfsheet = wb.getSheetAt(i);
        String s = wb.getSheetName(i);
        for (int j = 0; j < getRowCount(hssfsheet); j++) {
            HSSFRow hssfrow = hssfsheet.getRow(j);
            for (int k = 0; k < getColumnCount(hssfsheet); k++) {
                HSSFCell hssfcell = hssfrow.getCell((short) k);
                if (hssfcell == null) {
                    message += s + "(" + (k + 1) + ":" + (j + 1) + ")(" + s
                            + "" + getRowCount(hssfsheet) + "??)<br>";
                    return true;
                }/*from  www. j av  a  2 s.  co m*/
            }

        }

    }

    return false;
}

From source file:include.excel_import.Outter.java

License:Open Source License

private boolean isBlankColumn(int i, HSSFSheet hssfsheet) {
    int j = getRowCount(hssfsheet);
    for (int k = 0; k < j; k++) {
        HSSFRow hssfrow = hssfsheet.getRow(k);
        if (hssfrow == null)
            return true;
        HSSFCell hssfcell = hssfrow.getCell((short) i);
        if (hssfcell == null)
            return true;
        if (hssfcell.getCellType() != 3)
            return false;
    }//from   w w w .  j a v  a2 s.com

    return true;
}