Document Solutions for Excel, Java Edition | Document Solutions
Features / Worksheet / Worksheet Views
In This Topic
    Worksheet Views
    In This Topic

    DsExcel offers various options to customize display settings that are applied to a worksheet. You can either choose from pre-defined views or customize the view settings to get the preferred display. You can also save customized views in a workbook and apply them later.

    Pre-defined Views

    DsExcel Java, similar to MS Excel, provides pre-defined views to make it easier for users to preview the page layout and page breaks before printing the document.

    These pre-defined views can be set using setViewType method of the IWorksheetView interface.

    Java
    Copy Code
    // Create a new workbook
    Workbook workbook = new Workbook();
    IWorksheet worksheet = workbook.getActiveSheet();
            
    worksheet.getRange("J12").setValue(1);
            
    //Set the view mode of the worksheet to PageBreakPreview.
    worksheet.getSheetView().setViewType(ViewType.PageBreakPreview);
    
    //Modify the zoom of the PageBreakPreview to 80%.
    worksheet.getSheetView().setZoom(80);
    
    workbook.save("PageBreak.xlsx");

    View Settings

    In order to view a worksheet as per their own preferences, users can use the methods of the IWorksheet interface, IPane interface and IWorksheetView interface.

    The following table describes some of the methods that can be used to customize the view settings while working with worksheets.

    Method Description
    IWorksheet.splitPanes(int row, int column) This method can be used to lock the rows and columns in a worksheet in order to divide the worksheet into multiple areas that can be scrolled independently. Users need to provide the cell index as parameters in this method to specify the location where they want the split.
    IWorksheet.unsplitPanes(int row, int column) This method can be used to unsplit the split panes. Using this method is similar to using IWorksheet.SplitPanes(0,0).

    IWorksheet.getSplitRow

    IWorksheet.getSplitColumn

    This method gets the split distances (row count and column count) from top (in case of row) or left (in case of column).
    IWorksheet.getPanes A range object that represents the frozen or split panes of the worksheet.
    IWorksheet.getActivePane This method can be used to get the active pane in a worksheet.
    IPane.activate() This method activates the current pane.
    IPane.getIndex This method can be used to get the index of the current pane in IWorksheet.Panes.

    IPane.setScrollColumn

    IPane.setScrollRow

    This method can be used to get or set the top left cell position of the current pane.
    IWorksheet.getSheetView This method can be used to get the view of the worksheet.
    IWorksheetView.setZoom This method can be used to get and set a variant numeric value that represents the display size of the worksheet as a percentage where the 100 equals normal size, 200 equals double size, and so on.
    IWorksheetView.setGridlineColor This method can be used to get and set the gridline color.
    IWorksheetView.setScrollColumn This method can be used to get and set the number of the leftmost column in the worksheet.
    IWorksheetView.setScrollRow This method can be used to get and set  the number of the row that appears at the top of the worksheet.
    IWorksheetView.setDisplayRightToLeft This method can be used to get and set whether the specified worksheet is displayed from right to left instead of from left to right.
    IWorksheetView.setDisplayFormulas This method can be used to get and set whether the worksheet displays formulas.
    IWorksheetView.setDisplayGridlines This method can be used to get and set whether the gridlines are displayed.
    IWorksheetView.setDisplayVerticalGridlines This method can be used to get and set whether the vertical gridlines are displayed.
    IWorksheetView.setDisplayHorizontalGridlines This method can be used to get and set whether the horizontal gridlines are displayed.
    IWorksheetView.setDisplayHeadings This method can be used to get and set whether the headers are displayed.
    IWorksheetView.setDisplayOutline This method can be used to get and set whether the outline symbols are displayed.
    IWorksheetView.setDisplayRuler This method can be used to get and set whether a ruler is displayed for the specified worksheet.
    IWorksheetView.setDisplayWhitespace This method can be used to get and set whether the whitespace is displayed.
    IWorksheetView.setDisplayZeros This method can be used to get and set whether the zero values are displayed.

    In order to set custom view for a worksheet using different methods of the IWorksheet interface, refer to the following example code.

    Java
    Copy Code
    // Configure Sheet Settings
    Workbook workbook = new Workbook();
            
    // Fetch the default worksheet
    IWorksheet worksheet = workbook.getWorksheets().get(0);
            
    // Assign the values to the cells
    worksheet.getRange("B1").setValue("ABCD");
    worksheet.getRange("B2").setValue(3);
    worksheet.getRange("C1").setValue("Documents");
    worksheet.getRange("C2").setValue(4);
    worksheet.getRange("D1").setValue("Google");
    worksheet.getRange("D2").setValue("ABCD");
    worksheet.getSheetView().setDisplayRightToLeft(true);

    The following code snippet shows how to use the SplitPanes() method to split the worksheet into panes.

    Java
    Copy Code
    // Split worksheet to panes using splitPanes() method.
    worksheet.splitPanes(worksheet.getRange("B3").getRow(), worksheet.getRange("B3").getColumn());

    The following code snippet shows how to use the setDisplayVerticalGridlines and setDisplayHorizontalGridlines methods to display the vertical and horizontal gridlines of a worksheet. These gridlines are only visible while interacting with SpreadJS by doing JSON I/O and are not visible in Excel or PDF.

    Java
    Copy Code
    // Create a new workbook
    Workbook workbook = new Workbook();
    IWorksheet worksheet = workbook.getWorksheets().get(0);
    worksheet.getRange("A1:I88").setValue(10);
    
    // Set to not show horizontal gridlines
    worksheet.getSheetView().setDisplayHorizontalGridlines(false);
    
    // Set to show vertical gridlines
    worksheet.getSheetView().setDisplayVerticalGridlines(true);
    
    // Save workbook to ssjson
    String json = workbook.toJson();
    try {
        BufferedWriter out = new BufferedWriter(
                new OutputStreamWriter(new FileOutputStream("HorizontalVerticalGridlines.ssjson"), "utf-8"));
        out.write(json);
        out.flush();
        out.close();
    } catch (IOException e) {
        e.printStackTrace();
    }

    Note: If the value of setDisplayGridlines is set, setDisplayVerticalGridlines and setDisplayHorizontalGridlines are also set to the same value.

    DsExcel Java also lets you save custom views in the workbook. To learn more about custom views, see Workbook Views.