Example usage for org.apache.poi.xssf.usermodel XSSFRow getCell

List of usage examples for org.apache.poi.xssf.usermodel XSSFRow getCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFRow
  6. getCell

Introduction

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

Prototype

@Override
public XSSFCell getCell(int cellnum)

Source Link

Document

Returns the cell at the given (0 based) index, with the org.apache.poi.ss.usermodel.Row.MissingCellPolicy from the parent Workbook.

Usage

From source file:com.krawler.spring.importFunctionality.ImportUtil.java

License:Open Source License

/**
 * @param filename/*from   www.j  a va  2  s. c  o m*/
 * @param sheetNo
 * @param startindex
 * @param importDao
 * @return
 * @throws ServiceException
 */
public static void dumpXLSXFileData(String filename, int sheetNo, int startindex, ImportDAO importDao,
        HibernateTransactionManager txnManager) throws ServiceException {
    boolean commitedEx = false;
    DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    def.setName("import_Tx");
    def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
    TransactionStatus status = txnManager.getTransaction(def);
    Session session = txnManager.getSessionFactory().getCurrentSession();
    try {
        String destinationDirectory = storageHandlerImpl.GetDocStorePath() + "xlsfiles";
        FileInputStream fs = new FileInputStream(destinationDirectory + "/" + filename);
        XSSFWorkbook wb = new XSSFWorkbook(fs);
        XSSFSheet sheet = wb.getSheetAt(sheetNo);
        //DateFormat sdf = new SimpleDateFormat(df_full);
        int maxRow = sheet.getLastRowNum();
        int maxCol = 0;
        String tableName = importDao.getTableName(filename);
        int flushCounter = 0;
        for (int i = startindex; i <= maxRow; i++) {
            XSSFRow row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            if (i == startindex) {
                maxCol = row.getLastCellNum(); //Column Count
            }
            ArrayList<String> dataArray = new ArrayList<String>();
            JSONObject dataObj = new JSONObject();
            for (int j = 0; j < maxCol; j++) {
                XSSFCell cell = row.getCell(j);
                String val = null;
                if (cell == null) {
                    dataArray.add(val);
                    continue;
                }
                String colHeader = new CellReference(i, j).getCellRefParts()[2];
                switch (cell.getCellType()) {
                case XSSFCell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        val = Long.toString(cell.getDateCellValue().getTime());
                    } else {
                        val = dfmt.format(cell.getNumericCellValue());
                    }
                    break;
                case XSSFCell.CELL_TYPE_STRING:
                    val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString());
                    break;
                }
                dataObj.put(colHeader, val);
                dataArray.add(val); //Collect row data
            }
            //Insert Query
            if (dataObj.length() > 0) { // Empty row check (if lenght==0 then all columns are empty)
                importDao.dumpFileRow(tableName, dataArray.toArray());
                if (flushCounter % 30 == 0) {
                    session.flush();
                    session.clear();
                }
                flushCounter++;
            }

        }
        try {
            txnManager.commit(status);
        } catch (Exception ex) {
            commitedEx = true;
            throw ex;
        }
    } catch (IOException ex) {
        throw ServiceException.FAILURE("dumpXLSXFileData: " + ex.getMessage(), ex);
    } catch (Exception ex) {
        if (!commitedEx) { //if exception occurs during commit then dont call rollback
            txnManager.rollback(status);
        }
        throw ServiceException.FAILURE("dumpXLSXFileData: " + ex.getMessage(), ex);
    }
}

From source file:com.mycompany.fyp_emr.ExcelReaders.java

public static Diagnostic_Algorithm ReadFullGraph(String file) throws IOException {
    Diagnostic_Algorithm DA = new Diagnostic_Algorithm();

    FileInputStream fs = new FileInputStream(file);
    XSSFWorkbook wb = new XSSFWorkbook(fs);
    XSSFSheet sheet = wb.getSheetAt(0);/*  ww  w .  j  av a2s  . c o  m*/
    XSSFRow row;
    int id, id1, id2;
    String label, type, color, available, label_e;

    Node n;
    Edge e;
    LinkedList<Edge> edges = new LinkedList<Edge>();

    int rows = sheet.getPhysicalNumberOfRows();

    for (int r = 2; r < rows; r++) {
        row = sheet.getRow(r);
        if (row != null) {
            if (row.getCell(0) != null) {
                row.getCell(0).setCellType(Cell.CELL_TYPE_NUMERIC);
                id = (int) row.getCell(0).getNumericCellValue();
                if (id != 0) {
                    label = row.getCell(1).getStringCellValue();
                    type = row.getCell(2).getStringCellValue();
                    color = row.getCell(3).getStringCellValue();
                    available = row.getCell(4).getStringCellValue();

                    n = new Node();
                    n.setID(id);
                    n.setLabel(label);
                    n.setType(type);
                    n.setAvailable(available);
                    n.setColor(color);

                    DA.addNode(n);
                }
            }
            if (row.getCell(6) != null) {
                row.getCell(6).setCellType(Cell.CELL_TYPE_NUMERIC);
                row.getCell(7).setCellType(Cell.CELL_TYPE_NUMERIC);
                id1 = (int) row.getCell(6).getNumericCellValue();
                id2 = (int) row.getCell(7).getNumericCellValue();
                label_e = row.getCell(8).getStringCellValue();

                e = new Edge(id1, id2);
                e.setLabel(label_e);
                edges.add(e);
            }
        }
    }
    for (int i = 0; i < edges.size(); i++)
        DA.addEdge(edges.get(i));

    return DA;
}

From source file:com.mycompany.owl.fxml.FXMLFilterController.java

@FXML
public void saveFileTransformed() throws FileNotFoundException, IOException {
    FileInputStream fileInputStream = new FileInputStream(file);
    XSSFWorkbook workbookToModify = new XSSFWorkbook(fileInputStream);
    XSSFSheet sheet = workbookToModify.getSheetAt(0);
    XSSFRow row;
    String atcMask = getATCMask();
    ArrayList<String> firstRowCells = new ArrayList<>();
    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);//from   www. j a  v a 2  s.  c om
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            System.out.print(row.getCell(j).getRawValue() + "\t");
            if (i == 0)
                firstRowCells.add(row.getCell(j).getRawValue());
        }
        System.out.println("");
    }

    XSSFWorkbook transformedWB = new XSSFWorkbook();
    transformedWB.createSheet();
    XSSFSheet transformedS = transformedWB.getSheetAt(0);
    for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
        transformedS.createRow(i);
    }
    /*
    elkezdnk vgigmenni az alap sheeten
    ha megvan az index, ahol van match, akkor createrow(0) s bele a tbbit 0. helyre
    */
    ArrayList<Integer> matchingIndexes = matchingIndexes(firstRowCells);
    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);
        int sum = 0;
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            if (i != 0 && j != 0) {
                for (int index : matchingIndexes) {
                    if (index == j) {
                        sum += Integer.valueOf(row.getCell(j).getRawValue());
                    }
                }
            }
        }
        System.out.println(sum);
        if (i > 0) {
            row = sheet.getRow(i);
            row.createCell(row.getLastCellNum()).setCellValue(sum);
        }
    }
    for (int index : matchingIndexes) {
        for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            row.getCell(index).setCellValue(3.14159);
        }
        /*for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
        if(i == index){
            for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {
                row = sheet.getRow(j);
                transformedS.getRow(j).createCell(transformedColumnCount).setCellValue(
                        row.getCell(i).getRawValue()
                );
            }
            transformedColumnCount++;
        }
        }*/
    }
    int columnsInTransformed = 0;
    for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
        row = sheet.getRow(i);
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            String cellValue = row.getCell(j).getRawValue();
            if (!cellValue.equals("3.14159")) {
                transformedS.getRow(i).createCell(columnsInTransformed);
                transformedS.getRow(i).getCell(columnsInTransformed).setCellValue(cellValue);
                columnsInTransformed++;
            }
        }
        columnsInTransformed = 0;
    }

    row = transformedS.getRow(0);
    row.createCell(row.getLastCellNum()).setCellValue(atcMask);

    File file = fileChooser.showSaveDialog(new Stage());
    if (file != null) {
        try {
            FileOutputStream fop = new FileOutputStream(file);
            transformedWB.write(fop);
            fop.close();
        } catch (Exception e) {
            System.out.println("Exception: " + e.getMessage());
        }
    }

}

From source file:com.mycompany.owl.fxml.FXMLFilterController.java

@FXML
public void saveFileFiltered() throws FileNotFoundException, IOException {
    FileInputStream fileInputStream = new FileInputStream(file);
    XSSFWorkbook workbookToModify = new XSSFWorkbook(fileInputStream);
    XSSFSheet sheet = workbookToModify.getSheetAt(0);
    XSSFRow row;
    String atcMask = getATCMask();
    ArrayList<String> firstRowCells = new ArrayList<>();
    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);//from  ww w.  j  a  v a  2  s .  c  o  m
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            System.out.print(row.getCell(j).getRawValue() + "\t");
            if (i == 0)
                firstRowCells.add(row.getCell(j).getRawValue());
        }
        System.out.println("");
    }
    row = sheet.getRow(0);
    row.createCell(row.getLastCellNum()).setCellValue("ATC mask:");
    row.createCell(row.getLastCellNum()).setCellValue(atcMask);

    ArrayList<Integer> matchingIndexes = matchingIndexes(firstRowCells);
    System.out.println("SUMS");
    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);
        int sum = 0;
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            if (i != 0 && j != 0) {
                for (int index : matchingIndexes) {
                    if (index == j) {
                        sum += Integer.valueOf(row.getCell(j).getRawValue());
                    }
                }
            }
        }
        System.out.println(sum);
        if (i > 0) {
            row = sheet.getRow(i);
            row.createCell(row.getLastCellNum() + 1).setCellValue(sum);
        }
    }

    File file = fileChooser.showSaveDialog(new Stage());
    if (file != null) {
        try {
            FileOutputStream fop = new FileOutputStream(file);
            workbookToModify.write(fop);
            fop.close();
        } catch (Exception e) {
            System.out.println("Exception: " + e.getMessage());
        }
    }
}

From source file:com.niles.excel2json.objects.ExcelFile.java

License:Open Source License

public void process() throws Exception {
    File excel = new File(path);
    FileInputStream fis = SystemTools.getFileInputStream(excel);

    XSSFWorkbook wb = null;/* ww  w .  ja va  2 s.  c  o  m*/

    try {
        wb = new XSSFWorkbook(fis);
    } catch (IOException ex) {
        logger.error("Unable to process file [{}]\r\n{}", path, ex.getMessage());
        return;
    }

    this.fileName = excel.getName().replace(StringConstants.XLSX, "");
    this.folderPath = fileName + " Files";

    folderPath = SystemTools.createFolder(folderPath);

    int sheetCount = wb.getNumberOfSheets();

    int currentSheetNumber = 0;
    int rowCount = 0;
    int columnCount = 0;

    HashMap<Integer, String> headers = new HashMap<Integer, String>();

    /*
     * Itterate through the Excel sheets here and convert them to JSON
     */
    while (currentSheetNumber < sheetCount) {
        XSSFSheet current = wb.getSheetAt(currentSheetNumber);
        sheetName = current.getSheetName();

        // System.out.println(sheetName);
        if (current.getRow(0) == null) {
            // logger.error("[{}] Sheet contains no data", sheetName);
        } else {
            //logger.info("[{}] Processing sheet", sheetName);
            rowCount = current.getLastRowNum() + 1;
            columnCount = current.getRow(0).getPhysicalNumberOfCells();

            // System.out.println("Col Count: " + columnCount);
            // System.out.println("Row Count: " + rowCount);
            JSONArray myJSONArray = new JSONArray();
            JSONObject currentJSONObject = null;
            for (int a = 0; a < rowCount; a++) {
                XSSFRow currentRow = current.getRow(a);

                if (a == 0) {
                    logger.info("[{}] Loading header information", sheetName);
                    for (int currentCellNumber = 0; currentCellNumber < columnCount; currentCellNumber++) {
                        XSSFCell currentCell = currentRow.getCell(currentCellNumber);

                        String header = "Header" + currentCellNumber;

                        if (currentCell != null) {
                            if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                                header = currentCell.getStringCellValue();
                            }

                            if (currentCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                                header = currentCell.getNumericCellValue() + "";
                            }
                        }

                        headers.put(currentCellNumber, header.replaceAll(" ", ""));
                    }
                } else {
                    currentJSONObject = new JSONObject();

                    for (int currentCellNumber = 0; currentCellNumber < columnCount; currentCellNumber++) {
                        XSSFCell currentCell = currentRow.getCell(currentCellNumber);

                        String value = "";

                        if (currentCell != null) {
                            if (currentCell.getCellType() != XSSFCell.CELL_TYPE_ERROR
                                    && currentCell.getCellType() != XSSFCell.CELL_TYPE_FORMULA) {
                                if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                                    value = currentCell.getStringCellValue();
                                }
                                if (currentCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                                    value = currentCell.getNumericCellValue() + "";
                                }
                                if (currentCell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
                                    value = currentCell.getBooleanCellValue() + "";
                                }

                                if (value == null) {
                                    value = "";
                                }
                            }
                        }

                        currentJSONObject.put(headers.get(currentCellNumber), value);
                    }
                    myJSONArray.add(currentJSONObject);
                }
            }
            writeToJson(myJSONArray);
        }
        currentSheetNumber++;
    }
}

From source file:com.saba.CalendarDemo.java

License:Apache License

private static void prepareContactDetailsTableAndData(Map<String, Object> data, XSSFSheet sheet,
        Map<String, CellStyle> styles) {
    XSSFRow titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(16);/*  ww w  .j av  a 2 s  . c  om*/
    for (int i = 0; i <= 1; i++) {
        titleRow.createCell(i).setCellStyle(styles.get("title"));
    }
    XSSFCell titleCell = titleRow.getCell(0);
    titleCell.setCellValue(awardHeaders[0].toString());
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$B$1"));

    for (int i = 0; i < contactDetails.length; i++) {
        XSSFRow row = sheet.createRow(i + 1);
        XSSFCell cell = row.createCell(0);
        cell.setCellValue(contactDetails[i].toString());
        cell.setCellStyle(styles.get("item_left"));
        cell = row.createCell(1);
        populateDynamicObject(cell, data.get(contactDetails[i]));
        cell.setCellStyle(styles.get("item_right"));
    }
}

From source file:com.saba.CalendarDemo.java

License:Apache License

private static void prepareAwardBidDetailsTableAndData(Map<String, Object> data, XSSFSheet sheet,
        Map<String, CellStyle> styles, int contactdetrow) {
    XSSFRow titleRow;
    XSSFCell titleCell;//from   www  .j a  v a  2  s  .co  m
    titleRow = sheet.createRow(contactdetrow);
    titleRow.setHeightInPoints(16);
    for (int i = 0; i <= 1; i++) {
        titleRow.createCell(i).setCellStyle(styles.get("title"));
    }
    titleCell = titleRow.getCell(0);
    titleCell.setCellValue(awardHeaders[1].toString());
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$" + (contactdetrow + 1) + ":$B$" + (contactdetrow + 1)));
    for (int i = 0; i < awardedBidDetails.length; i++) {
        XSSFRow row = sheet.createRow(contactdetrow + i + 1);
        XSSFCell cell = row.createCell(0);
        cell.setCellValue(awardedBidDetails[i]);
        cell.setCellStyle(styles.get("item_left"));
        cell = row.createCell(1);
        populateDynamicObject(cell, data.get(awardedBidDetails[i]));
        cell.setCellStyle(styles.get("item_right"));
    }
}

From source file:com.saba.CalendarDemo.java

License:Apache License

private static void prepareProductDetailsTable(XSSFWorkbook workbook, XSSFSheet sheet,
        Map<String, CellStyle> styles, int awardDetailsRow) {
    XSSFRow titleRow;
    XSSFCell titleCell;/*from  ww  w .  j  av  a2  s  .  co  m*/
    titleRow = sheet.createRow(awardDetailsRow);
    titleRow.setHeightInPoints(16);
    for (int i = 0; i < productDetailsHeaders.length; i++) {
        titleRow.createCell(i).setCellStyle(styles.get("title"));
    }
    titleCell = titleRow.getCell(0);
    titleCell.setCellValue(awardHeaders[2].toString());
    String columId = productDetailsHeaders.length > 0 && productDetailsHeaders.length < 27
            ? String.valueOf((char) (productDetailsHeaders.length + 'A' - 1))
            : null;
    String cellMergeRange = "$A$" + (awardDetailsRow + 1) + ":$" + columId + "$" + (awardDetailsRow + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(cellMergeRange));
    XSSFRow row = sheet.createRow(awardDetailsRow + 1);
    for (int i = 0; i < productDetailsHeaders.length; i++) {
        XSSFCell cell = row.createCell(i);
        cell.setCellValue(productDetailsHeaders[i]);
        //create header style for product Details table
        CellStyle headerStyle = createHeaderStyleForAward(workbook);
        cell.setCellStyle(headerStyle);
    }
}

From source file:com.sandstone_tech.lendfastselenium2.util.PoiFileReader.java

/**
 * Extracts the content of the excel file to be converted as
 * {@link TestDataModel} object usable by the data provider
 * /*from w  w  w .  j av a 2  s. co  m*/
 * @param fileName
 * @return
 */
protected Object[][] readExcelFileAsTDM(String fileName) {

    try {
        XSSFWorkbook wb = new XSSFWorkbook(FileUtils.openInputStream(new File(fileName)));

        // CONFIGURATION
        //         XSSFSheet sheet = wb.getSheetAt(0);

        // DATA
        XSSFSheet dataSheet = wb.getSheetAt(1);

        if (dataSheet != null && dataSheet.getPhysicalNumberOfRows() > 1) {

            Object[][] testData = new Object[dataSheet.getPhysicalNumberOfRows() - 2][1];

            for (int rowCount = 2; rowCount < dataSheet.getPhysicalNumberOfRows(); rowCount++) {
                XSSFRow row = dataSheet.getRow(rowCount);
                TestDataModel testDataModel = new TestDataModel();

                testDataModel.setUsername(getStringValue(row.getCell(0)));
                testDataModel.setPassword(getStringValue(row.getCell(1)));
                testDataModel.setApplicationType(getStringValue(row.getCell(2)));

                testDataModel.setOrginationFormModel(this.getOrigination(row));
                testDataModel.setPersonalDetailsModel(this.getPersonalDetail(row));
                testDataModel.setSignedDeclaration(this.getSignedDeclaration(row));

                testDataModel.setCurrentAddress(this.getCurrentAddress(row));
                testDataModel.setEmploymentDetails(this.getEmploymentDetails(row));
                testDataModel.setPreviousEmploymentDetails(this.getPreviousEmploymentDetails(row));
                testDataModel.setPurchaseProperty(
                        new PurchaseProperty(this.getPropertyContract(row), this.getPropertyPurpose(row)));

                testData[rowCount - 2][0] = testDataModel;
            }

            wb.close();
            return testData;

        }

        wb.close();

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

    }

    return null;
}

From source file:com.sandstone_tech.lendfastselenium2.util.PoiFileReader.java

/**
 * //w  w  w  . ja v  a 2  s  . c o  m
 * 
 * @param row
 * @return
 */
private OriginationFormModel getOrigination(XSSFRow row) {

    OriginationFormModel origination = new OriginationFormModel();
    origination.setOriginationChannel(getStringValue(row.getCell(3)));
    origination.setOriginationSearchBranch(getStringValue(row.getCell(4)));
    origination.setOriginationBranch(getStringValue(row.getCell(5)));
    origination.setOriginationSalesUsername(getStringValue(row.getCell(6)));
    origination.setOriginationSalesPerson(getStringValue(row.getCell(7)));

    return origination;
}