SpreadJS and the CellsEnumerator Class

January 04 2016

You can use the CellsEnumerator class in SpreadJS to create your own custom search. This can be useful if the standard search does not return the result you are trying to find. For example, you can find the last cell with data in the row and column using the CellsEnumerator class. The following example gets the last cell with data and returns the information in the Internet Explorer console.

<!DOCTYPE html>  
<html>  
<head>  
    <title>SpreadJS</title>  
<link type="text/css" href="./css/gcspread.sheets.excel2013white.9.40.20153.0.css" rel="stylesheet" />   
<script type="text/javascript" src="./scripts/gcspread.sheets.all.9.40.20153.0.min.js"></script>     
<script type="text/javascript">  

        function bodyReady() {  
            var spread = new GcSpread.Sheets.Spread(document.getElementById("ss"));  
            var sheet = spread.getActiveSheet();  
            sheet.setValue(3, 10, "lastCol");  
            sheet.setValue(9, 2, "lastRow");  
            sheet.setValue(7, 5, "data");  
            sheet.setValue(9, 6, "lastCell");  
        }  

        function buttonClick() {  
            var spread = GcSpread.Sheets.findControl(document.getElementById("ss"));  
            var sheet = spread.getActiveSheet();  
            console.log("The last non-empty row index: " + getLastNotEmptyRowIndex(sheet));  
            console.log("The last non-empty col index: " + getLastNotEmptyColumnIndex(sheet));  
            var lastCell = getLastNotEmptyCell(sheet);  
            console.log("The last non-empty CELL :  [" + lastCell.row + ":" + lastCell.col + "]");  
        }  

        function getLastNotEmptyRowIndex(sheet) {  
            var ce = initEngine(sheet), lastRow = -1, curCell = null;  
            ce.moveNext();  
            while (curCell = ce.current()) {  
                if (curCell.row > lastRow) {  
                    lastRow = curCell.row;  
                }  
                ce.moveNext();  
            }  
            return lastRow;  
        }  

        function getLastNotEmptyColumnIndex(sheet) {  
            var ce = initEngine(sheet), lastCol = -1, curCell = null;  
            ce.moveNext();  
            while (curCell = ce.current()) {  
                if (curCell.col > lastCol) {  
                    lastCol = curCell.col;  
                }  
                ce.moveNext();  
            }  
            return lastCol;  
        }  

        function getLastNotEmptyCell(sheet) {  
            var ce = initEngine(sheet), lastCell = null, curCell = null;  
            ce.moveNext();  
            while (curCell = ce.current()) {  
                lastCell = curCell;  
                ce.moveNext();  
            }  
            return lastCell;  
        }  

        function initEngine(sheet) {  
            var searchCondition = new GcSpread.Sheets.SearchCondition();  
            searchCondition.rowStart = 0;  
            searchCondition.rowEnd = sheet.getRowCount();  
            searchCondition.columnStart = 0;  
            searchCondition.columnEnd = sheet.getColumnCount();  
            searchCondition.searchString = "*";  
            searchCondition.searchOrder = GcSpread.Sheets.SearchOrder.ZOrder;  
            searchCondition.searchTarget = GcSpread.Sheets.SearchFoundFlags.CellText;  
            searchCondition.searchFlags = GcSpread.Sheets.SearchFlags.UseWildCards;  
            var cellsEnumerator = new GcSpread.Sheets.CellsEnumerator(sheet, searchCondition);  
            return cellsEnumerator;  
        }  

    </script>  
</head>  
<body onload="bodyReady()">  
    <div id="ss" style="width:100%;height:500px;border:1px solid gray"></div>  
    <input type="button" id="btn" value="Last Cell in Console" onclick="buttonClick()" />  
</body>  
</html>

MESCIUS inc.