Scroll By Pixel

SpreadJS provides extensive support for Excel-like precision scrolling to ensure you can enjoy a smooth and flawless scrolling experience while having additional control over your worksheets.

Users can scroll a spreadsheet by pixels, manually or automatically. In manual scrolling, pixels can be set and the arrows at the end of scrollbar can be used to scroll through the spreadsheet by specified pixels in horizontal or vertical direction. Whereas in automatic scrolling, vertical and horizontal pixels can be set which automatically scrolls the spreadsheet to the specified pixels.

Benefits of Pixel Scrolling

The scroll by pixel feature provides an enhanced user experience by allowing users to move steadily around the cells of the worksheet. 

Let's take a look at a few scenarios where pixel scrolling is helpful: 

• When the cells in the spreadsheet are extremely tall (containing multi-line text) or wide (increased column width).

• When users want to resize a row or column by grabbing its edges.

• When users are trying to position a shape or picture within the cell.

• When users need to fill a lot of data in a single cell. For example - Entry Forms, Survey Forms, Records etc. in spreadsheets.

• When users are scrolling worksheets on a small screen, such as a tablet or mobile.

• When users want to traverse to a specific viewport or cells containing desired data in a spreadsheet.

Supported User Interactions

Users can scroll by pixel in both the directions - horizontal direction (for scrolling columns by pixel) or vertical direction (for scrolling rows by pixel).

SpreadJS provides support for the following mousewheel and keyboard actions while using the scroll by pixel feature in the worksheets:

• Mousewheel or trackpad on viewport area where the row viewport index is 1 or column viewport index is 1.

• Clicking the left/right/up/down buttons of the scroll bar using mousewheel or trackpad.

• Using the scroll arrow buttons, mousewheel or trackpad on the scrollbar.

• Touch scrolling on the viewport area.

• Touch or tap the left/right/up/down button or the track button of the scroll bar.

Enable Pixel Scrolling (Manual)

By default, both Excel and SpreadJS scroll only by rows or columns. You can enable pixel scrolling in the worksheet by setting the scrollByPixel property to "true" as shown in the code snippet shared below. Once set, the spreadsheet can be scrolled manually by the specified pixels using the arrow at the end of scroll bar.

Usage Scenario

Let's say you're working on a spreadsheet that depicts product analysis of different countries and the states within a particular country. While scrolling past the rows and columns, you may want to look at the last entry(state) of a specific country or the last column (product details). In this scenario, working with default scrolling won't help because it will completely jump over to an entire row or column at a time and the whole process will eventually become tedious while trying to grab the edges.

The following image depicts pixel scrolling in a worksheet:

// Enable the precision scrolling by pixel
spread.options.scrollByPixel = true;
spread.options.scrollPixel = 5;

Note: The following limitations must be kept in mind while using the pixel scrolling feature in spreadsheets:

- While scrolling by pixel on touch-based devices, the touch gesture will not support boundary feedback effect and scrolling to the entire rows or columns.

• While exporting to a PDF file or printing the spreadsheets, users won't be able to use the scroll by pixel feature. However, pixel scrolling works with all other features without any hassle.

Using Code

The following example code allows users to set custom pixel size and scroll rows and columns in a worksheet as per their specific preferences.

    var data = [
   {
       Country: "Canada",
       State: "Ontario",
       City: "Ottawa",
       Product: "Kraft Grated Parmesan Cheese",
   },
   {
       Country: "Canada",
       State: "Ontario",
       City: "Belleville",
       Product: "KIND Bars Almond & Coconut Gluten Free",
   },
   {
       Country: "Canada",
       State: "Ontario",
       City: "Alliston",
       Product: "Kraft Grated Parmesan Cheese",
   },
   {
       Country: "Canada",
       State: "Saskatchewan",
       City: "Prince Albert",
       Product: "Smartfood Popcorn",
   },
   {
       Country: "Canada",
       State: "Alberta",
       City: "Red Deer",
       Product: "Smartfood Popcorn",
   },
   {
       Country: "Canada",
       State: "Alberta",
       City: "Calgary",
       Product: "Planters Deluxe Whole Cashew",
   },
   {
       Country: "Canada",
       State: "Alberta",
       City: "Calgary",
       Product: "Kraft Grated Parmesan Cheese",
   },
   {
       Country: "Canada",
       State: "Alberta",
       City: "Okotoks",
       Product: "Smartfood Popcorn",
   },
   {
       Country: "India",
       State: "Andhra Pradesh",
       City: "Hyderabad",
       Product: "Teddy Grahams Crackers",
   },
   {
       Country: "South Africa",
       State: "Gauteng",
       City: "Roodepoort",
       Product: "Jaybee's Gourmet Nuts Gift Pack (3 Lb)",
   },
   {
       Country: "Finland",
       State: "Ita-Suomen Laani",
       City: "Kuopio",
       Product: "Planters Deluxe Whole Cashew",
   },
   {
       Country: "Switzerland",
       State: "Geneve",
       City: "Vesenaz",
       Product: "KIND Bars Almond & Coconut Gluten Free",
   },
   {
       Country: "Switzerland",
       State: "Vaud",
       City: "Lausanne",
       Product: "Smartfood Popcorn",
   },
   {
       Country: "Switzerland",
       State: "Vaud",
       City: "Morges",
       Product: "Kraft Real Mayo",
   },
   {
       Country: "Denmark",
       State: "Frederiksborg",
       City: "Helsingor",
       Product: "Planters Deluxe Whole Cashew",
   },
   {
       Country: "Denmark",
       State: "Kobenhavn",
       City: "Kobenhavn",
       Product: "Kraft Grated Parmesan Cheese",
   },
   {
       Country: "Denmark",
       State: "Storstrom",
       City: "Nakskov",
       Product: "Kraft Grated Parmesan Cheese",
   },
   ];

   $(document).ready(function () {
   // Initializing Spread
   var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {
       sheetCount: 1,
   });

   // Get the activesheet
   var sheet = spread.getSheet(0);
   spread.suspendPaint();

   // Enable the precision scrolling by pixel
   spread.options.scrollByPixel = true;

   // Bind data source
   sheet.setRowHeight(0, 30, 1);
   sheet.autoGenerateColumns = false;
   sheet.setDataSource(data);
   var colInfos = [
       { name: "Country", size: 180 },
       { name: "State", size: 120 },
       { name: "City", size: 120 },
       { name: "Product", size: 280 },
   ];
   sheet.bindColumns(colInfos);

   // Add span
   var defaultStyle = sheet.getDefaultStyle();
   defaultStyle.vAlign = GC.Spread.Sheets.VerticalAlign.center;
   defaultStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
   sheet.setDefaultStyle(defaultStyle);
   var cc = sheet.getColumnCount(),
       rc = sheet.getRowCount();
   for (var c = 0; c < cc; c++) {
       var tempValue = null,
       tempRow = -1,
       tempRowCount = -1;
       for (var r = 0; r < rc; r++) {
       var cellValue = sheet.getValue(r, c);
       if (tempValue == null) {
           tempValue = cellValue;
           tempRow = r;
           tempRowCount = 1;
       } else if (tempValue === cellValue) {
           tempRowCount++;
       } else {
           if (tempRowCount > 1) {
           sheet.addSpan(tempRow, c, tempRowCount, 1);
           }
           tempValue = cellValue;
           tempRow = r;
           tempRowCount = 1;
       }
       }
       if (tempRowCount > 1) {
       sheet.addSpan(tempRow, c, tempRowCount, 1);
       }
   }
   spread.resumePaint();
   // Bind events to set scrollByPixel
   var scrollByPixel = document.getElementById("scrollByPixel");
   scrollByPixel.addEventListener("change", function () {
       spread.options.scrollByPixel = scrollByPixel.checked;
   });
   var scrollPixel = document.getElementById("scrollPixel");
   document
       .getElementById("setScrollPixel")
       .addEventListener("click", function () {
       spread.options.scrollPixel = parseInt(scrollPixel.value);
       });
   });

Enable Pixel Scrolling (Automatic)

You can enable automatic scrolling by pixels in the worksheet by using scroll

method which allows you to set vertical (vpixels) and horizontal (hpixels) pixels to scroll in the respective directions. By doing this, the worksheet will scroll to the specified pixels automatically. 

• If the value of Pixels is positive, worksheet will scroll down for vpixels and right for hpixels

• If the value of Pixels is negative, worksheet will scroll up for vpixels and left for hpixels

• If the value of Pixels is 0, worksheet won't scroll in any direction

Along with scroll method, you can also use scrollByPixel property which affects the scrolling behavior in following way:

• When scrollByPixel is true, worksheet will scroll to new top row or left column index and new top row or left column offset (as defined by scroll method)

• When scrollByPixel is false, worksheet will scroll to new top rowor left column index (as defined by scroll method), but new top row or left column offset will be always 0

Usage Scenario

Let's say you're working on a spreadsheet that depicts product analysis of different countries and the states within a particular country. While scrolling past the rows and columns, you may want to look at a specific viewport of the spreadhseet which displays all the data relating to one country. In this case, you can use automatic scrolling to view the desired data.

The following image depicts automatic pixel scrolling in a worksheet:

Using Code

The following example code allows users to set custom pixel size for horizontal and vertical directions and scrolls to the specified position in the worksheet.

// get the activesheet
var activeSheet = spread.getActiveSheet();
spread.options.scrollByPixel = true;
//This example scrolls down the sheet 30 pixels
//and scrolls right the sheet 15 pixels.
activeSheet.scroll(30, 15);