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

Introduction

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

Prototype

@Override
public short getLastCellNum() 

Source Link

Document

Gets the index of the last cell contained in this row PLUS ONE.

Usage

From source file:Import.Utils.XSSFConvert.java

/**
 * @param destination the sheet to create from the copy.
 * @param the sheet to copy./*  www. jav  a2s .c  om*/
 * @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:Import.Utils.XSSFConvert.java

/**
 * @param srcSheet the sheet to copy.//from  w w w .  j  a v  a  2 s .co  m
 * @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:info.vancauwenberge.filedriver.filereader.xls.XlsFileReader.java

License:Mozilla Public License

public void openFile(File f) throws ReadException {
    trace.trace("Reading " + f.getAbsolutePath());
    //If the file is existing, open and read it
    try {//from w w  w .  j a  v a2  s.  c  om
        FileInputStream fin = new FileInputStream(f);
        POIFSFileSystem poifs = new POIFSFileSystem(fin);
        wb = new HSSFWorkbook(poifs);
    } catch (IOException e) {
        throw new ReadException("Error while trying to read file " + f.getAbsolutePath(), e);
    }
    currentSheet = wb.getSheet(sheetName);
    //If we do not have a sheet with the given name, throw exception.
    if (currentSheet == null)
        throw new ReadException("No sheet with name " + sheetName + " found in file " + f.getAbsolutePath(),
                null);
    nextRowNumber = currentSheet.getFirstRowNum();

    //If we have a aheader row, read it to get the actual schema
    if (hasHeader) {
        if (useHeaderNames) {
            HSSFRow row = currentSheet.getRow(nextRowNumber);
            //Last cell num is zero based => +1
            String[] fields = new String[row.getLastCellNum()];
            trace.trace("Number of fields:" + fields.length);
            Iterator<Cell> iter = row.cellIterator();
            while (iter.hasNext()) {
                HSSFCell element = (HSSFCell) iter.next();
                String value = element.getStringCellValue();
                fields[element.getCellNum()] = value;
            }
            //We might have some nulls in the array. Default them.
            for (int i = 0; i < fields.length; i++) {
                String string = fields[i];
                if (string == null)
                    fields[i] = "_Unknown_" + i + "_";
            }
            schema = fields;
            nextRowNumber++;
        }
    }
}

---

From source file:it.filippovitale.fineco2qif.logic.ExcelSheetAnalysisLogic.java

License:Apache License

private static void dumpSheet(HSSFSheet sheet) {
    if (sheet == null) {
        log.debug("The sheet to dump is null!");
        return;/*  w  ww  . j ava 2s.c o m*/
    }

    for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
        log.debug("row#" + i + "=");
        HSSFRow row = sheet.getRow(i);
        if (row == null) {
            log.debug("\t| " + NULL_RAPPRESENTATION);
            continue;
        }
        for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
            HSSFCell cell = row.getCell((short) j);
            String cellValue = getCellValue(cell);
            log.debug("\t| " + cellValue);
        }
        log.debug("\n");
    }
}

---

From source file:javaapplication2.Frame1.java

void fillData(File file) {
    int index = -1;
    HSSFWorkbook workbook = null;/*  w  w w.j  a va2s .com*/
    try {
        try {
            FileInputStream inputStream = new FileInputStream(file);
            workbook = new HSSFWorkbook(inputStream);
        } catch (IOException ex) {
            Logger.getLogger(Frame1.class.getName()).log(Level.SEVERE, null, ex);
        }

        String[] strs = new String[workbook.getNumberOfSheets()];
        //get all sheet names from selected workbook
        for (int i = 0; i < strs.length; i++) {
            strs[i] = workbook.getSheetName(i);
        }
        JFrame frame = new JFrame("Input Dialog");

        String selectedsheet = (String) JOptionPane.showInputDialog(frame,
                "Which worksheet you want to import ?", "Select Worksheet", JOptionPane.QUESTION_MESSAGE, null,
                strs, strs[0]);

        if (selectedsheet != null) {
            for (int i = 0; i < strs.length; i++) {
                if (workbook.getSheetName(i).equalsIgnoreCase(selectedsheet))
                    index = i;
            }
            HSSFSheet sheet = workbook.getSheetAt(index);
            HSSFRow row = sheet.getRow(0);

            headers.clear();
            //int value=row.getLastCellNum();
            for (int i = 0; i < row.getLastCellNum(); i++) {
                HSSFCell cell1 = row.getCell(i);
                headers.add(cell1.toString());

            }

            data.clear();
            for (int j = 1; j < sheet.getLastRowNum() + 1; j++) {
                Vector d = new Vector();
                row = sheet.getRow(j);
                int noofrows = row.getLastCellNum();
                for (int i = 0; i < noofrows; i++) { //To handle empty excel cells 
                    HSSFCell cell = row.getCell(i, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK);
                    d.add(cell.toString());
                }
                d.add("\n");
                data.add(d);
            }
        } else {
            return;
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

---

From source file:javaexecelprocess.JavaExecelProcess.java

public long[] getFieldsLen() {

    //        Integer dat = new Integer(7);
    int iCols = getColumns();
    long[] fieldsLen = new long[iCols];
    for (int i = 0; i < iCols; i++) {
        fieldsLen[i] = -1;/* w  w  w  . j a v a  2  s.c  om*/
    }
    HSSFSheet activeSheet = wb.getSheetAt(0);
    int iFirstRow = activeSheet.getFirstRowNum();
    int iLastRow = activeSheet.getLastRowNum();
    for (int i = iFirstRow + 1; i <= iLastRow; i++) {
        HSSFRow row = activeSheet.getRow(i);
        int iFirstCol = row.getFirstCellNum();
        int iLastCol = row.getLastCellNum();
        for (int j = iFirstCol; j < iLastCol; j++) {
            HSSFCell cell = row.getCell(j);
            int cellType = cell.getCellType();
            if (HSSFCell.CELL_TYPE_STRING == cellType) {
                long tmpLen = cell.getStringCellValue().length();
                if (fieldsLen[j - iFirstCol] < tmpLen) {
                    fieldsLen[j - iFirstCol] = tmpLen;
                }
            } else if (HSSFCell.CELL_TYPE_NUMERIC == cellType) {
                fieldsLen[j - iFirstCol] = -1;
            } else {

            }
        }
    }

    return fieldsLen;
}

---

From source file:javaexecelprocess.JavaExecelProcess.java

public boolean isDBFormat() {
    boolean ret = true;
    HSSFSheet activeSheet = wb.getSheetAt(0);
    int iFirstRow = activeSheet.getFirstRowNum();
    int iLastRow = activeSheet.getLastRowNum();
    List<Integer> fieldsType = getFieldsType();
    if (null == fieldsType) {
        ret = false;//from   w ww .j  av a  2s . co m
        return ret;
    }
    for (int i = iFirstRow + 1; i <= iLastRow; i++) {
        HSSFRow row = activeSheet.getRow(i);
        int iFirstCol = row.getFirstCellNum();
        int iLastCol = row.getLastCellNum();
        for (int j = iFirstCol; j < iLastCol; j++) {
            HSSFCell cell = row.getCell(j);
            //                String cessStr = cell.toString();
            int cellType = cell.getCellType();
            //                if(HSSFCell.CELL_TYPE_BLANK == cellType
            //                        || HSSFCell.CELL_TYPE_ERROR == cellType){
            Integer colType = fieldsType.get(j);
            if (colType.intValue() != cellType) {
                ret = false;
                break;
            }
        }

        if (false == ret) {
            break;
        }
    }

    return ret;
}

---

From source file:javaexecelprocess.JavaExecelProcess.java

private List<Integer> getFieldsType() {
    List<Integer> fieldsType = null;

    HSSFSheet activeSheet = wb.getSheetAt(0);
    int iFirstRow = activeSheet.getFirstRowNum();
    int iLastRow = activeSheet.getLastRowNum();
    HSSFRow row = activeSheet.getRow(iFirstRow + 1);
    int iFirstCol = row.getFirstCellNum();
    int iLastCol = row.getLastCellNum();
    int iCols = row.getPhysicalNumberOfCells();
    if (0 != iCols) {
        fieldsType = new ArrayList<>();
    }/*www  . j  a  v a  2 s  . com*/
    for (int j = iFirstCol; j < iLastCol; j++) {
        HSSFCell cell = row.getCell(j);
        int cellType = cell.getCellType();
        fieldsType.add(cellType);
    }

    return fieldsType;
    //        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
}

---

From source file:javaexecelprocess.JavaExecelProcess.java

public void insertDataFromExcel() {
    String templateInsert = "INSERT INTO `mytbl1` (`id`, `myf1`, `myf2`) VALUES (NULL, 'h111', 'h222');";
    //        String templateInsert = "INSERT INTO `mytbl1` (`id`, `myf1`, `myf2`) VALUES ('2', 'h111', 'h222');";
    String sqlHead = "";
    sqlHead += "INSERT INTO `" + fields.get(0) + "` (`id`,";
    int i = 0;//  ww w  .  j av a2  s . co m
    for (i = 0; i < fields.size() - 2; i++) {
        sqlHead += " `" + fields.get(i + 1) + "`,";
    }
    sqlHead += " `" + fields.get(i + 1) + "`) VALUES (NULL,";

    PreparedStatement pst = null;
    HSSFSheet activeSheet = wb.getSheetAt(0);
    int iFirstRow = activeSheet.getFirstRowNum();
    int iLastRow = activeSheet.getLastRowNum();
    for (i = iFirstRow + 1; i <= iLastRow; i++) {
        String sql = sqlHead;
        HSSFRow row = activeSheet.getRow(i);
        int iFirstCol = row.getFirstCellNum();
        int iLastCol = row.getLastCellNum();
        int j = 0;
        for (j = iFirstCol; j < iLastCol - 1; j++) {
            HSSFCell cell = row.getCell(j);
            String cessStr = cell.toString();
            sql += " '" + cessStr + "',";
        }
        HSSFCell cell = row.getCell(j);
        String cessStr = cell.toString();
        sql += " '" + cessStr + "');";
        try {
            pst = mysqlConn.prepareStatement(sql);
            pst.execute();
        } catch (SQLException ex) {
            Logger.getLogger(JavaExecelProcess.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("insert data exception.");
        }
    }
    //        sql += "INSERT INTO `mytbl1` (`id`, `myf1`, `myf2`) VALUES (NULL, 'f1111', 'f2222');";

}

---

From source file:jp.dbcls.bp3d.kaorif.visiblebody.nerves.VisibleBodyNerves.java

License:Open Source License

/**
 * ???(manuallyMapped.txt)?/*  ww  w.  jav  a2 s  . c  om*/
 * @throws Exception
 */
public void readManuallyMapped() throws Exception {
    POIFSFileSystem filein = new POIFSFileSystem(new FileInputStream(this.MANUALLY_MAPPED));
    HSSFWorkbook wb = new HSSFWorkbook(filein);
    HSSFSheet sheet = wb.getSheet("manuallyMapped");

    for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
        HSSFRow row = sheet.getRow(i);

        HSSFCell cell = null;

        String original = "";
        cell = row.getCell(0);
        if (cell != null) {
            original = cell.getRichStringCellValue().toString().trim();
        }

        String av = "";
        cell = row.getCell(1);
        if (cell != null) {
            av = cell.getRichStringCellValue().toString().trim();
        }

        String renamed = "";
        cell = row.getCell(2);
        if (cell != null) {
            renamed = cell.getRichStringCellValue().toString().trim();
        }

        String remark = "";
        for (int j = 3; j < row.getLastCellNum(); j++) {
            cell = row.getCell(j);
            if (cell != null) {
                remark += cell.getRichStringCellValue().toString().trim() + "\t";
            }
        }

        VisibleBodyManuallyMapped mm = new VisibleBodyManuallyMapped();
        mm.setOriginal(original);
        mm.setAv(av);
        mm.setRenamed(renamed);
        mm.setRemark(remark);

        this.manuallyMapped.add(mm);

    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• »