Setting Excel Worksheet Views with Python: View Modes, Zoom, Gridlines, and Tab Colors

Two people can open the same report and see very different things. One lands on the right page with aligned columns and clearly coloured tabs; the other lands on page one, zoomed too far out to read, with gridlines cutting the layout into fragments. The data is identical — the view settings are not. Adjusting them by hand takes a few seconds, but when reports are generated by a script, those few seconds come around again for every file.

Excel stores these display properties inside the workbook: each worksheet keeps its own view mode, zoom level, gridline switch, and tab colour, while the workbook keeps the tab bar visibility, the active sheet, and the active cell. This article uses Spire.XLS for Python to write these settings into scripts, so generated files open in a readable state.

Setting Up the Environment

Install Spire.XLS and import the modules:

pip install Spire.XLS

from spire.xls import *
from spire.xls.common import *

Choosing a View Mode

A worksheet can be shown in one of three modes: Normal, Page Layout (Layout), and Page Break Preview (Preview). The mode is stored on the worksheet and set through the ViewMode property:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[0]
sheet.Range["B2"].Text = "Quarterly report"

# Open the file directly in Page Break Preview
sheet.ViewMode = ViewMode.Preview

workbook.SaveToFile("ViewMode.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

For reports meant to be printed, Preview is the practical choice: the page breaks are visible as soon as the file opens, with no view switching. Layout suits documents where headers and footers must be positioned, and Normal suits spreadsheets used mainly for data entry.

Setting the Zoom Level

Zoom is set through Zoom as a percentage:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Fill some data so the zoom effect is visible
for r in range(1, 16):
    for c in range(1, 6):
        sheet.Range[r, c].NumberValue = r * c

# Zoom the normal view to 85%
sheet.Zoom = 85

workbook.SaveToFile("ZoomFactor.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Page Break Preview has its own zoom value, ZoomScalePageBreakView, which does not affect the normal view — each mode keeps its own level when switching:

workbook = Workbook()
sheet = workbook.Worksheets[0]
sheet.ViewMode = ViewMode.Preview
sheet.ZoomScalePageBreakView = 80
workbook.SaveToFile("PageBreakPreview.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

When Zoom is anything other than 100, Excel shows the current percentage in the status bar, and the user can still change it by hand. The script sets the initial state, not a lock.

Controlling Gridlines

Gridlines are controlled per worksheet; set GridLinesVisible to False to hide them:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet1 = workbook.Worksheets[0]
sheet2 = workbook.Worksheets[1]

sheet1.Range["B2"].Text = "Data sheet without gridlines"
sheet2.Range["B2"].Text = "Reference sheet with gridlines"

# Hide gridlines on the data sheet, keep them on the reference sheet
sheet1.GridLinesVisible = False
sheet2.GridLinesVisible = True

workbook.SaveToFile("ShowOrHideGridLine.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Report worksheets usually look better without gridlines, which otherwise show up in printouts and screenshots as extra lines. Tables that are checked cell by cell are easier to read with gridlines kept. Since the setting is per worksheet, one workbook can treat them differently.

Colouring Worksheet Tabs

Once a workbook has several sheets, tab colours are the quickest way to group them. TabColor accepts a Color object:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.Worksheets[0].Range["B2"].Text = "Summary"
workbook.Worksheets[1].Range["B2"].Text = "Details"
workbook.Worksheets[2].Range["B2"].Text = "Raw data"

# Colour the three tabs red, green, and light blue
workbook.Worksheets[0].TabColor = Color.get_Red()
workbook.Worksheets[1].TabColor = Color.get_Green()
workbook.Worksheets[2].TabColor = Color.get_LightBlue()

workbook.SaveToFile("SetTabColor.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Color offers a set of predefined colours — get_Red(), get_Green(), get_LightBlue(), and others — and colours can also be constructed from raw values. A colour is only a visual marker and does not affect data, but for files shared over a long period it helps to agree on what each colour means: red for a summary sheet, blue for raw data, for example.

Activating a Worksheet and Cell

Which sheet and which cell are in focus when the workbook opens are decided by the active worksheet and the active cell:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[1]
sheet.Range["B2"].Text = "Start here"

# Activate the second worksheet and make B2 the active cell
sheet.Activate()
sheet.SetActiveCell(sheet.Range["B2"])

# Scroll so that row 2 and column 1 sit at the top-left of the viewport
sheet.FirstVisibleColumn = 1
sheet.FirstVisibleRow = 2

workbook.SaveToFile("MakeCellActive.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Activate() selects the worksheet. SetActiveCell() decides where the cursor lands, and it moves the scroll position along with it. When the viewport must be pinned to a specific region rather than simply follow the active cell, set FirstVisibleRow and FirstVisibleColumn as well — they specify the row and column at the top-left corner of the visible area.

Hiding the Tab Bar

When a workbook holds only one or two sheets, or readers should follow a fixed order, the tab bar at the bottom can be hidden:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.Worksheets[0].Range["B2"].Text = "Tabs are hidden"

# Hide the worksheet tab bar
workbook.ShowTabs = False

workbook.SaveToFile("HideTab.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

The worksheets still exist; only the tabs disappear from the bottom of the window. Set ShowTabs back to True to show them again.

Combining the Settings

In real report generation these settings are written together. The example below creates three worksheets and configures the view mode, zoom, gridlines, and tab colour of each:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()

summary = workbook.Worksheets[0]
details = workbook.Worksheets[1]
raw = workbook.Worksheets[2]

summary.Range["B2"].Text = "Summary"
details.Range["B2"].Text = "Details"
raw.Range["B2"].Text = "Raw data"

# Summary: no gridlines, page break preview, red tab
summary.GridLinesVisible = False
summary.ViewMode = ViewMode.Preview
summary.ZoomScalePageBreakView = 90
summary.TabColor = Color.get_Red()

# Details: normal view, 85% zoom, green tab
details.ViewMode = ViewMode.Normal
details.Zoom = 85
details.TabColor = Color.get_Green()

# Raw data: gridlines kept, blue tab
raw.GridLinesVisible = True
raw.TabColor = Color.get_LightBlue()

# Open the file on the Details sheet, at B2
details.Activate()
details.SetActiveCell(details.Range["B2"])

workbook.SaveToFile("ReportViewSettings.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Practical Tips

• View settings are per worksheet: zoom, gridlines, view mode, and tab colour are saved per sheet, so one workbook can differ from sheet to sheet.
• Page break zoom is separate: ZoomScalePageBreakView and Zoom do not overwrite each other; each view mode keeps its own level.
• The active cell sets the first impression: Activate() plus SetActiveCell() open the file at the start of the data.
• Scroll position is independent: FirstVisibleRow and FirstVisibleColumn pin the viewport to a region.
• Give colours a meaning: tab colours are a convention for readers, so tie them to business meaning in the script.
• Release resources: call Dispose() on each workbook when done.

Conclusion

This article demonstrated setting Excel worksheet views from Python: choosing Normal, Page Layout, or Page Break Preview with ViewMode; controlling the zoom of both views with Zoom and ZoomScalePageBreakView; toggling gridlines per sheet with GridLinesVisible; colouring tabs with TabColor; deciding where a file opens with Activate(), SetActiveCell(), FirstVisibleRow, and FirstVisibleColumn; and hiding the tab bar with ShowTabs. These properties leave the data untouched but determine how readable the file is when opened, which makes them a natural part of report generation scripts alongside the content settings.