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:se.nrm.dina.dina.inventory.logic.dyntaxa.ExcelLogic.java

public void oldSpeciesSynonyms(XSSFSheet sheet) {
    logger.info("oldSpeciesSynonyms");

    theRank = "Species";
    int numOfRows = sheet.getLastRowNum();
    IntStream.range(3, numOfRows + 1).filter(n -> sheet.getRow(n) != null).forEach(nbr -> {
        XSSFRow row = sheet.getRow(nbr);
        if (row.getCell(0) != null) {
            genus = row.getCell(0).getStringCellValue().trim();
            species = row.getCell(1).getStringCellValue().trim();
            author = row.getCell(2).getStringCellValue().trim();
            scientificName = row.getCell(5).getStringCellValue().trim();
            synonymOf = row.getCell(6).getStringCellValue().trim();
            source = row.getCell(7).getStringCellValue().trim();
            agentName = row.getCell(8).getStringCellValue().trim();

            uploadSynonyms();/*  w  w w.j a  v a2 s . c  o m*/
        }
    });
}

From source file:se.nrm.dina.dina.inventory.logic.dyntaxa.ExcelLogic.java

private void collectionsToDyntaxa(XSSFSheet sheet) {
    logger.info("collectionsToDyntaxa");

    int rankId = Util.getInstance().getRankId("Species");
    item = (Taxontreedefitem) smtpDao.getEntityByJPQL(
            QueryStringBuilder.getInstance().buildGetTaxonTreeDefItem(TAXON_TREE_DEF_ID, rankId));
    IntStream.range(15, 22).filter(n -> sheet.getRow(n) != null).forEach(nbr -> {
        XSSFRow row = sheet.getRow(nbr);
        if (row.getCell(0) != null) {
            genus = row.getCell(0).getStringCellValue().trim();
            species = row.getCell(1).getStringCellValue().trim();
            author = row.getCell(2).getStringCellValue().trim();
            scientificName = row.getCell(3).getStringCellValue().trim();
            source = row.getCell(4).getStringCellValue().trim();
            agentName = row.getCell(5).getStringCellValue().trim();
            comment = row.getCell(6).getStringCellValue().trim();
            parent = genus;/*  ww w.  j  av  a 2s  .  c  o  m*/
            rankOfParent = "Genus";
            strIsManuscript = "no";
            uploadTaxon(rankId);
        }
    });

    IntStream.range(26, 32).filter(n -> sheet.getRow(n) != null).forEach(nbr -> {
        XSSFRow row = sheet.getRow(nbr);
        if (row.getCell(0) != null) {
            scientificName = row.getCell(3).getStringCellValue().trim();
            comment = row.getCell(6).getStringCellValue().trim();
            rankOfParent = "Genus";
            synonymOf = row.getCell(4).getStringCellValue().trim();
            agentName = "Ida Li";

            Taxon synonymOfTaxon = getTaxon(synonymOf, true);
            Taxon theTaxon = getTaxon(scientificName, false);

            theTaxon.setIsAccepted(Boolean.FALSE);
            theTaxon.setRemarks(comment);
            theTaxon.setAcceptedID(synonymOfTaxon);
            theTaxon.setModifiedByAgentID(getAgentFromDB(agentName));
            theTaxon.setTimestampModified(timestamp);

            smtpDao.merge(theTaxon);
        }
    });
}

From source file:se.nrm.dina.dyntaxa.dump.logic.ExcelReader.java

public void readNewTaxonExcel() {
    try (FileInputStream file = new FileInputStream(new File(NEW_TAXAN_EXCEL_FILE_PATH))) {
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        XSSFSheet sheet = workbook.getSheetAt(6);
        int numOfRows = sheet.getLastRowNum();
        IntStream.range(3, numOfRows + 1).forEach(nbr -> {
            XSSFRow row = sheet.getRow(nbr);
            String scientificName = row.getCell(0).getStringCellValue();
            String rank = row.getCell(1).getStringCellValue();
            String author = row.getCell(2).getStringCellValue();
            String parent = row.getCell(3).getStringCellValue();
            String source = row.getCell(4).getStringCellValue();
            String agent = row.getCell(5).getStringCellValue();
            String isManuscript = row.getCell(7).getStringCellValue();

            System.out.println("isManuscript : " + Boolean.valueOf(isManuscript) + isManuscript);

            if (agent.contains(" ")) {
                int index = agent.indexOf(" ");
                System.out.println("last name : " + agent.substring(index + 1));
                System.out.println("first name : " + agent.split(" ")[0]);
            }/*from  w w w .j av  a  2 s  .  c om*/
        });

    } catch (IOException ex) {
        logger.error(ex.getMessage());
    }
}

From source file:se.nrm.dina.dyntaxa.dump.logic.ExcelReader.java

public List<TaxonVO> read() {

    logger.info("read excel");
    List<TaxonVO> list = new ArrayList();
    try (FileInputStream file = new FileInputStream(new File(EXCEL_FILE_PATH))) {
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        XSSFSheet sheet = workbook.getSheetAt(0);
        int rowEndNumber = sheet.getLastRowNum();

        IntStream.range(1, rowEndNumber + 1).forEach(nbr -> {
            XSSFRow row = sheet.getRow(nbr);
            String rank = row.getCell(1).getStringCellValue();
            if (rank.equals("Genus") || rank.equals("Subgenus")) {
                currentParent = row.getCell(2).getStringCellValue();
                currentRank = rank;//ww w.ja v a  2 s  .co m
            } else if (rank.equals("Species")) {

                taxonName = row.getCell(2).getStringCellValue();
                TaxonVO vo = new TaxonVO(taxonName, currentParent, currentRank);
                System.out.println(taxonName + " --- " + currentParent + " --- " + currentRank);
                list.add(vo);
            }
        });
        System.out.println("row number : " + rowEndNumber);
    } catch (IOException ex) {
        System.out.println("error: " + ex.getMessage());
    }
    return list;
}

From source file:Servelt.ExcelWriter.java

public ExcelWriter(HttpServletRequest request) {
    logger = Logger.getLogger(ExcelWriter.class.getName());
    workbook = new XSSFWorkbook();
    Map<String, XSSFSheet> sheetMap = new TreeMap<String, XSSFSheet>();
    XSSFRow row;

    sheetMap.put(CPU.ComponentName, createSheet(workbook, new CpuSet()));
    sheetMap.put(Antennas.ComponentName, createSheet(workbook, new AntennasSet()));
    sheetMap.put(Button.ComponentName, createSheet(workbook, new ButtonSet()));
    sheetMap.put(Camera.ComponentName, createSheet(workbook, new CameraSet()));
    sheetMap.put(CardReader.ComponentName, createSheet(workbook, new CardReaderSet()));
    sheetMap.put(ClickPad.ComponentName, createSheet(workbook, new ClickPadSet()));
    sheetMap.put(Ethernet.ComponentName, createSheet(workbook, new EthernetSet()));
    sheetMap.put(KBCEBC.ComponentName, createSheet(workbook, new KbcEbcSet()));
    sheetMap.put(LcdPanel.ComponentName, createSheet(workbook, new LcdPanelSet()));
    sheetMap.put(Memory.ComponentName, createSheet(workbook, new MemorySet()));
    sheetMap.put(Sensor.ComponentName, createSheet(workbook, new SensorSet()));
    sheetMap.put(Storage.ComponentName, createSheet(workbook, new StorageSet()));
    sheetMap.put(TouchPanel.ComponentName, createSheet(workbook, new TouchPanelSet()));
    sheetMap.put(WLAN.ComponentName, createSheet(workbook, new WlanSet()));
    sheetMap.put(WWAN.ComponentName, createSheet(workbook, new WwanSet()));
    sheetMap.put(Keyboard.ComponentName, createSheet(workbook, new KeyboardSet()));
    sheetMap.put(Battery.ComponentName, createSheet(workbook, new BatterySet()));
    sheetMap.put(Charger.ComponentName, createSheet(workbook, new ChargerSet()));
    sheetMap.put(Measurement.ComponentName, createSheet(workbook, new MeasurementSet()));
    sheetMap.put(Graphic.ComponentName, createSheet(workbook, new GraphicSet()));
    sheetMap.put(AudioCodec.ComponentName, createSheet(workbook, new AudioCodecSet()));
    sheetMap.put(PanelInterfaceBridge.ComponentName, createSheet(workbook, new PanelInterfaceBridgeSet()));
    sheetMap.put(ExternalStorageCard.ComponentName, createSheet(workbook, new ExternalStorageCardSet()));
    sheetMap.put(ODD.ComponentName, createSheet(workbook, new ODDSet()));
    sheetMap.put(Speaker.ComponentName, createSheet(workbook, new SpeakerSet()));
    sheetMap.put(Mic.ComponentName, createSheet(workbook, new MicSet()));
    sheetMap.put(IoPort.ComponentName, createSheet(workbook, new IoPortSet()));
    sheetMap.put(OS.ComponentName, createSheet(workbook, new OSSet()));

    if (request != null) {
        ComponentSet set = requestToCom(request);
        filename = request.getParameter("category") + ".xlsx";

        int i = 1;

        for (Iterator<Component> iter = set.getComList().iterator(); iter.hasNext();) {
            row = sheetMap.get(set.getComponentName()).createRow(i++);
            int j = 0;

            for (Iterator<String> str = iter.next().getComponent().iterator(); str.hasNext();) {
                String data = str.next();
                row.createCell(j);//w  w w .  j a  v  a 2 s . c o m
                row.getCell(j).setCellValue(data);
                j++;
            }

        }
    }

    autoStyle(workbook);

    try (FileOutputStream out = new FileOutputStream(filepath + filename)) {
        workbook.write(out);
    } catch (Exception ex) {
        logger.log(Level.WARNING, ex.toString());
        ex.printStackTrace();
    }

}

From source file:singleton.StaticClass.java

public synchronized void copyFromExcel(String fileName) throws IOException {
    //System.out.println("start");
    Data temp;//from w  ww  .  j  a v a 2  s  . co  m
    String value = "";

    File inFile = new File(fileName);
    FileInputStream file = new FileInputStream(inFile);

    XSSFWorkbook wb = new XSSFWorkbook(file);
    XSSFSheet sheet = wb.getSheetAt(4); // Build Image Analysis page is at
    // the 4th sheet of Open source
    // license excel file.

    int rows = sheet.getPhysicalNumberOfRows();

    for (int i = 2; i < rows; ++i) { // start index should be 2 since the
        // 1st row is used for titles.
        XSSFRow row = sheet.getRow(i);
        if (row != null) {

            int cells = row.getPhysicalNumberOfCells(); // Number of cells
            // at each row.

            temp = new Data();
            for (int colIndex = 1; colIndex <= cells; colIndex++) {
                XSSFCell cell = row.getCell(colIndex);

                if (colIndex == 1) {
                    switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_BLANK:
                        temp.setBinary("");
                        break;
                    case XSSFCell.CELL_TYPE_STRING:
                        temp.setBinary(cell.getStringCellValue());
                        break;
                    }
                } else if (colIndex == 2) {
                    switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_BLANK:
                        temp.setPath("");
                        break;
                    case XSSFCell.CELL_TYPE_STRING:
                        temp.setPath(cell.getStringCellValue());
                        break;
                    }
                } else if (colIndex == 3) {
                    switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_BLANK:
                        temp.setOnok("");
                        break;
                    case XSSFCell.CELL_TYPE_STRING:
                        value = cell.getStringCellValue();
                        temp.setOnok(value);
                        break;
                    }
                } else if (colIndex == 4) {
                    switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_BLANK:
                        temp.setOssComponent("");
                        break;
                    case XSSFCell.CELL_TYPE_STRING:
                        temp.setOssComponent(cell.getStringCellValue());
                        break;
                    }
                } else if (colIndex == 6) {
                    switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_BLANK:
                        temp.setLicense("");
                        break;
                    case XSSFCell.CELL_TYPE_STRING:
                        temp.setLicense(cell.getStringCellValue());
                        break;
                    }
                } else {
                    continue;
                }
            }
            if (temp != null) {
                if (value.equalsIgnoreCase("nok")) {
                    nokList.add(temp);
                    //System.out.println("nok count : " + nokList.size());
                } else if (value.equalsIgnoreCase("ok")) {
                    okList.add(temp);
                    //System.out.println("ok count : " + okList.size());
                } else if (value.equalsIgnoreCase("nok(na)")) {
                    nokNaList.add(temp);
                    //System.out.println("nok(na) count : " + nokNaList.size());
                } else {
                    blankList.add(temp);
                    //System.out.println("blank count : " + blankList.size());
                }
                System.out.println(temp.getBinary() + "\t" + temp.getPath() + "\t\t" + temp.getOnok() + "\t\t"
                        + temp.getLicense());
            }
        }
    }
}

From source file:sv.com.mined.sieni.controller.GestionNotasController.java

public static void copyRow(XSSFSheet worksheetSource, XSSFSheet worksheetDestination, int sourceRowNum,
        int destinationRowNum) {
    // Get the source / new row
    XSSFRow origen = worksheetSource.getRow(sourceRowNum);
    XSSFRow destino = worksheetDestination.createRow(destinationRowNum);

    // Loop through source columns to add to new row
    for (int i = 0; i < origen.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        XSSFCell oldCell = origen.getCell(i);
        XSSFCell newCell = destino.createCell(i);
        // If the old cell is null jump to next cell
        if (oldCell == null) {
            newCell = null;/*from  ww w  .  j av a2 s  . c  o m*/
            continue;
        }

        //Ajustar tamaos columnas
        worksheetDestination.setColumnWidth(i, worksheetSource.getColumnWidth(i));

        // Copy style from old cell and apply to new cell
        XSSFCellStyle newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        newCell.setCellStyle(newCellStyle);

        // If there is a cell comment, copy
        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        // If there is a cell hyperlink, copy
        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());
        // Set the cell data value
        switch (oldCell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            newCell.setCellFormula(oldCell.getCellFormula());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        }

    }

}

From source file:tan.jam.jsf.Shifting.java

public static int FindShift(XSSFWorkbook workbook, XSSFSheet worksheet) {
    String s = "TOTAL BANKS";
    XSSFRow Row = worksheet.getRow(2);
    for (int i = 9; i < 26; i++) {
        XSSFCell cell = Row.getCell(i);
        String g = cell.getStringCellValue();
        if (g.equals("TOTAL BANKS")) {
            return i - 9;
        }//from   w  ww  .ja va 2s . co  m
    }
    return 0;
}

From source file:tan.jam.jsf.Shifting.java

public static void InsertRow(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum,
        int destinationRowNum) {

    worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
    XSSFRow newRow = worksheet.getRow(destinationRowNum);
    XSSFRow sourceRow = worksheet.getRow(sourceRowNum);

    if (newRow != null) {
        worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
    } else {/*from w ww .  j  av  a  2s  .c o m*/
        newRow = worksheet.createRow(destinationRowNum);
    }

    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        XSSFCell oldCell = sourceRow.getCell(i);
        XSSFCell newCell = newRow.createCell(i);

        if (oldCell == null) {
            newCell = null;
            continue;
        }

        XSSFCellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        ;
        newCell.setCellStyle(newCellStyle);

        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        newCell.setCellType(oldCell.getCellType());

        switch (oldCell.getCellType()) {
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA:
            newCell.setCellFormula("+" + "F" + destinationRowNum + "*G" + destinationRowNum);
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
            //newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
            newCell.setCellValue("");
            break;
        }
    }

    for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
        CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
        if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
            CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                    (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
                    cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
            worksheet.addMergedRegion(newCellRangeAddress);
        }
    }
    int inc = destinationRowNum + 1;
    worksheet.getRow(destinationRowNum).getCell(7).setCellFormula("+F" + inc + "*G" + inc);
}

From source file:tan.jam.jsf.Shifting.java

private static void CopyData(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum,
        int destinationRowNum, int Mov) {
    XSSFRow newRow = worksheet.getRow(destinationRowNum);
    XSSFRow sourceRow = worksheet.getRow(sourceRowNum);

    for (int i = sourceRow.getLastCellNum(); i > 8 + Mov; i--) {

        int d = i - 1;
        XSSFCell oldCell = sourceRow.getCell(d);
        XSSFCell newCell = newRow.createCell(i);

        if (oldCell == null) {
            newCell = null;//from  www . j a v a 2  s  . c o  m
            continue;
        }

        XSSFCellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        ;
        newCell.setCellStyle(newCellStyle);

        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        newCell.setCellType(oldCell.getCellType());

        switch (oldCell.getCellType()) {
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA:
            newCell.setCellFormula(oldCell.getCellFormula());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        }
    }
}