Worksheet Views

This article demonstrates how to configure and manage worksheet views in Excel with DsExcel Java, including pre-defined views, customizing sheet view options, adjusting the zoom level, freezing panes, and splitting panes. These features allow you to manage the visible area and layout of your worksheet to enhance data readability.

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.

• Normal View - The default worksheet view.

• Page Layout View - Displays the worksheet as it will appear when printed, including page boundaries, headers, and footers.

• Page Break View - Displays the locations of page breaks for printing.

You can use the setViewType method of the IWorksheetView interface to set a preset worksheet view.

Refer to the following sample code to set the view to Page Break Preview.

// 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);

// Save to an excel file.
workbook.save("PageBreak.xlsx");

The output is shown in the figure below:

Sheet View Options

Users can customize how a worksheet is displayed, including options such as gridline visibility, row and column headers, formula display, orientation, gridline color, and blank area visibility.

You can configure the worksheet view by setting the getSheetView method of the IWorksheet interface or by using methods of the IWorksheetView interface. The table below lists some methods of the IWorksheetView interface.

Refer to the following sample code to set the table to display only vertical gridlines.

// 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 as an SJS file.        
workbook.save("gridlines.Sjs");

Zoom

Zoom is used to adjust the display size of worksheet content on the screen. Users can set the zoom level to enlarge or reduce the view for easier detail inspection or overview of large amounts of data.

Zoom Level

You can retrieve or specify the display scale of the worksheet (in percentage) by setting the setZoom method of the IWorksheetView interface. For example, a value of 100 represents the default size, while 200 zooms in to double the display size, and so on.

Refer to the following sample code to set the zoom level to 80%.

// Create a new workbook.
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getWorksheets().get(0);

// Set the zoom level to 80%.
worksheet.getSheetView().setZoom(80);

// Save to an excel file.
workbook.save("zoom.xlsx");

The output is shown in the figure below:

Fit to Selection

You can use the setFitSelection method of the IWorksheetView interface to automatically adjust the zoom level so that the selected area is fully visible in the current window, maximizing the display of the selected content.

Refer to the following sample code to set the zoom level to fit the selected range.

Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getWorksheets().get(0);

// Prepare data.
worksheet.setName("Data");
worksheet.getRange("A1:C13").setValue(new Object[][] {
    {null, "Blue Series", "Orange Series"},
    {"Jan", 0, 59.1883603948205},
    {"Feb", 44.6420211591501, 52.2280901938606},
    {"Mar", 45.2174930051225, 49.8093056416248},
    {"Apr", 62, 37.3065749226828},
    {"May", 53, 34.4312192530766},
    {"Jun", 31.8933622049831, 69.7834561753736},
    {"Jul", 41.7930895085093, 63.9418103906982},
    {"Aug", 73, 57.4049534494926},
    {"Sep", 49.8773891668518, 33},
    {"Oct", 50, 74},
    {"Nov", 54.7658428630216, 22.9587876597096},
    {"Dec", 32, 54},
});

// Create a chart sheet and add an area chart.
IWorksheet chartSheet = workbook.getWorksheets().add(SheetType.Chart);
chartSheet.setName("Chart");
IShape shape = chartSheet.getShapes().addChart(ChartType.Area, 0, 0, 360, 230);
shape.getChart().getSeriesCollection().add(worksheet.getRange("A1:C13"), RowCol.Columns);
shape.getChart().getChartTitle().setText("Area Chart");

// Set the chart sheet to fit selection.
chartSheet.getSheetView().setFitSelection(true);

// Save to an excel file.
workbook.save("FitSelection.xlsx");

The output is shown in the figure below:

Panes

The pane functionality enables you to divide a worksheet into multiple independent viewing areas. By using Freeze Panes or Split Panes, you can view different parts of a worksheet at the same time without excessive scrolling, significantly enhancing the convenience and efficiency of data review.

You can freeze, unfreeze, split, or unsplit panes by using the methods and methods of the IWorksheet interface. The table below lists some methods of the IWorksheet interface.

Freeze Panes

DsExcel Java supports freezing panes in a worksheet, allowing users to keep specific rows or columns visible while scrolling through the rest of the content.

You can also set a custom color for the frozen pane lines. The custom color is only visible while interacting with SpreadJS and is not visible in exported Excel or PDF files.

• Refer to the sample code below to freeze panes in the worksheet.

  // Create a new workbook.
  Workbook workbook = new Workbook();
  IWorksheet worksheet = workbook.getWorksheets().get(0);
  
  // Freeze Panes.
  worksheet.freezePanes(2, 3);
  
  // Save to an excel file.
  workbook.save("FreezePanes.xlsx");

  The output is shown in the figure below:

  
  

• Refer to the sample code below to set the frozen pane line color to blue in the worksheet.

  // Create a new workbook.
  Workbook workbook = new Workbook();
  IWorksheet worksheet = workbook.getWorksheets().get(0);
  
  // Freeze panes.
  worksheet.freezePanes(5, 5);
  
  // Set frozen line color as blue.
  worksheet.setFrozenLineColor(Color.GetBlue());
  
  // Save as an SJS file.
  workbook.save("frozenlinecolor.Sjs");
  
  

• Refer to the sample code below to unfreeze the frozen panes in the worksheet.

  // UnFreeze Panes.
  worksheet.unfreezePanes();

Freeze Trailing Panes

DsExcel Java supports freezing trailing panes, allowing the bottom rows and rightmost columns of a worksheet to remain visible while scrolling, making it easier to review and compare data.

Frozen trailing panes are only visible when interacting with SpreadJS format and are not available in exported Excel or PDF files.

• Refer to the sample code below to freeze trailing panes in a worksheet.

  // Create a new workbook.
  Workbook workbook = new Workbook();
  IWorksheet worksheet = workbook.getWorksheets().get(0);
  
  // Freeze trailing pane.
  worksheet.freezeTrailingPanes(2, 3);
  
  // Get the number of frozen trailing rows and columns.
  System.out.println("Number of trailing rows are: " + worksheet.getFreezeTrailingRow() + "\nNumber of trailing columns are: " + worksheet.getFreezeTrailingColumn());
  
  // Save as an SJS file.
  workbook.save("freezetrailingrowscolumns.Sjs");

• Refer to the sample code below to unfreeze trailing panes in a worksheet.

  // Create a new workbook.
  Workbook workbook = new Workbook();
  IWorksheet worksheet = workbook.getWorksheets().get(0);
  
  // Freeze trailing pane.
  worksheet.freezeTrailingPanes(2, 3);
  
  // Unfreeze trailing pane.
  worksheet.unfreezeTrailingPanes();
  
  // Save as an SJS file.
  workbook.save("unfreezetrailingrowscolumns.Sjs");

Split Panes

DsExcel Java allows users to split the worksheet window into multiple independent viewing areas. By dividing the panes, you can view and edit data in different sections of the same worksheet at the same time, reducing the need for frequent scrolling. Each split pane can be scrolled independently, making it easier to browse and compare large amounts of data.

• Refer to the sample code below to split a worksheet into multiple panes.

  // Create a new workbook.        
  Workbook workbook = new Workbook();
  IWorksheet worksheet = workbook.getWorksheets().get(0);
  
  // Split worksheet into independent panes.       
  worksheet.splitPanes(5,6);
  
  // Save to an excel file.        
  workbook.save("SplitPanes.xlsx");

  The output is shown in the figure below:

  
  

• Refer to the sample code below to remove split panes.

  // Unsplit Panes.
  worksheet.unsplitPanes();