Hiding or unhiding rows and columns in Excel gives you precise control over the visibility of specific data within a worksheet. By hiding rows or columns, you can temporarily remove irrelevant information from view, reducing visual clutter and creating a cleaner workspace. This makes it easier to work with the data that truly matters and enhances your productivity. On the other hand, unhiding rows or columns allows you to restore visibility and regain access to previously hidden information whenever you need it. This is advantageous when you have hidden data that requires further review, modification, or analysis. In this article, we will explain how to hide or unhide rows and columns in Excel in Python using Spire.XLS for Python.

• Hide Specific Rows and Columns in Excel in Python
• Unhide Specific Hidden Rows and Columns in Excel in Python
• Hide Multiple Rows and Columns at Once in Excel in Python
• Unhide All Hidden Rows and Columns in Excel in Python

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Hide Specific Rows and Columns in Excel in Python

Spire.XLS for Python offers the Worksheet.HideRow(rowIndex) and Worksheet.HideColumn(columnIndex) methods to hide a specific row and column in an Excel worksheet. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet through Workbook.Worksheets[sheetIndex] property.
• Hide specific rows in the worksheet using Worksheet.HideRow(rowIndex) method.
• Hide Specific columns in the worksheet using Worksheet.HideColumn(columnIndex) method.
• Save the result file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Input.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Hide the 3rd and the 7th rows
sheet.HideRow(3)
sheet.HideRow(7)

# Hide the 3rd and the 6th columns
sheet.HideColumn(3)
sheet.HideColumn(6)

# Save the result file
workbook.SaveToFile("HideRowsAndColumns.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Unhide Specific Hidden Rows and Columns in Excel in Python

You can use the Worksheet.ShowRow(rowIndex) and Worksheet.ShowColumn(columnIndex) methods to unhide a specific hidden row and column. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet through Workbook.Worksheets[sheetIndex] property.
• Unhide specific hidden rows in the worksheet using Worksheet.ShowRow(rowIndex) method.
• Unhide specific hidden columns in the worksheet using Worksheet.ShowColumn(columnIndex) method.
• Save the result file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("HideRowsAndColumns.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Unhide the 3rd and the 7th rows
sheet.ShowRow(3)
sheet.ShowRow(7)

# Unhide the 3rd and the 6th columns
sheet.ShowColumn(3)
sheet.ShowColumn(6)

# Save the result file
workbook.SaveToFile("ShowRowsAndColumns.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Hide Multiple Rows and Columns at Once in Excel in Python

To hide multiple rows and columns at once, you can use the Worksheet.HideRows(startRowIndex, rowCount) and Worksheet.HideColumns(startColumnIndex, columnCount) methods. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet through Workbook.Worksheets[sheetIndex] property.
• Hide multiple rows in the worksheet using the Worksheet.HideRows(startRowIndex, rowCount) method.
• Hide multiple columns in the worksheet using Worksheet.HideColumns(startColumnIndex, columnCount) method.
• Save the result file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Input.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Hide 3, 4 and 5 rows
sheet.HideRows(3, 3)

# Hide 5, 6 and 7 columns
sheet.HideColumns(5, 3)

# Save the result file
workbook.SaveToFile("HideMultipleRowsAndColumns.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Unhide All Hidden Rows and Columns in Excel in Python

To unhide all hidden rows and columns, you first need to loop through the used rows and columns in the worksheet. Next, find the hidden rows and columns using Worksheet.GetRowIsHide(rowIndex) and Worksheet.GetColumnIsHide(columnIndex) methods, and then unhide them using Worksheet.ShowRow(rowIndex) and Worksheet.ShowColumn(columnIndex) methods. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet through Workbook.Worksheets[sheetIndex] property.
• Iterate through the used rows in the worksheet and find the hidden rows using Worksheet.GetRowIsHide(rowIndex) method.
• Unhide every hidden row using Worksheet.ShowRow(rowIndex) method.
• Iterate through the used columns in the worksheet and find the hidden columns using Worksheet.GetColumnIsHide(columnIndex) method.
• Unhide every hidden column using Worksheet.ShowColumn(columnIndex) method.
• Save the result file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("HideMultipleRowsAndColumns.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Iterate through the used rows in the worksheet
for i in range(1, sheet.LastRow + 1):
    # Check if the current row is hidden
    if sheet.GetRowIsHide(i):
        # Unhide the hidden row
        sheet.ShowRow(i)

# Iterate through the used columns in the worksheet
for j in range(1, sheet.LastColumn + 1):
    # Check if the current column is hidden
    if sheet.GetColumnIsHide(j):
        # Unhide the hidden column
        sheet.ShowColumn(j)

# Save the result file
workbook.SaveToFile("ShowAllHiddenRowsAndColumns.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Hyperlinks are a useful tool for connecting and navigating between different sections of your document or external resources such as websites or files. However, there may be instances where you need to modify the hyperlinks in your Word document. For example, you may need to update the text or URL of a hyperlink to ensure accuracy, or change the appearance of a hyperlink to improve visibility. In this article, you will learn how to update or change hyperlinks in a Word document in Python using Spire.Doc for Python.

• Update a Hyperlink in a Word Document in Python
• Change the Appearance of a Hyperlink in a Word Document in Python

Install Spire.Doc for Python

This scenario requires Spire.Doc for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.Doc

If you are unsure how to install, please refer to this tutorial: How to Install Spire.Doc for Python on Windows

Update a Hyperlink in a Word Document in Python

A hyperlink is recognized as a FormField object by Spire.Doc for Python. In order to modify a specific hyperlink, we need to retrieve all hyperlinks in the document and get the desired one by its index. The display text and URL of a hyperlink can be reset by the FormField.Text property and the FormField.Code property. The following are the steps to update a hyperlink in a Word document using Spire.Doc for Python.

• Create a Document object.
• Load a Word file using Document.LoadFromFile() method.
• Loop through the elements in the document to find all hyperlinks.
• Get a specific hyperlink from the hyperlink collection.
• Update the display text of the hyperlink through FormField.FieldText property.
• Update the URL of the hyperlink through FormField.Code property.
• Save the document to a different Word file using Document.SaveToFile() method.

from spire.doc import *
from spire.doc.common import *

# Create a Document object
doc = Document()

# Load a Word file
doc.LoadFromFile("C:/Users/Administrator/Desktop/input.docx")

# Find all hyperlinks in the document
hyperlinks = []
for i in range(doc.Sections.Count):
    section = doc.Sections.get_Item(i)
    for j in range(section.Body.ChildObjects.Count):
        sec = section.Body.ChildObjects.get_Item(j)
        if sec.DocumentObjectType == DocumentObjectType.Paragraph:
            for k in range((sec if isinstance(sec, Paragraph) else None).ChildObjects.Count):
                para = (sec if isinstance(sec, Paragraph)
                        else None).ChildObjects.get_Item(k)
                if para.DocumentObjectType == DocumentObjectType.Field:
                    field = para if isinstance(para, Field) else None
                    if field.Type == FieldType.FieldHyperlink:
                        hyperlinks.append(field)

# Get a specific hyperlink
hyperlink = hyperlinks[0]

# Update the display text of the hyperlink
hyperlink.FieldText = "HYPERTEXT MARKUP LANGUAGE"

# Update the URL of the hyperlink
hyperlink.Code ="HYPERLINK \"" + "https://en.wikipedia.org/wiki/HTML" + "\""

# Save the document to a docx file
doc.SaveToFile("output/UpdateHyperlink.docx", FileFormat.Docx)
doc.Close()

Change the Appearance of a Hyperlink in a Word Document in Python

After a hyperlink is obtained, it's easy to change the appearance of it through the FormField.CharacterFormat object. Specifically, the CharacterFormat object offers the properties such as TextColor, FontName, FontSize, UnderlineStyle to change the style of the characters of a hyperlink. The following are the detailed steps.

• Create a Document object.
• Load a Word file using Document.LoadFromFile() method.
• Loop through the elements in the document to find all hyperlinks.
• Get a specific hyperlink from the hyperlink collection.
• Change the appearance of the hyperlink through the properties under FormField.CharacterFormat object.
• Save the document to a different Word file using Document.SaveToFile() method.

from spire.doc import *
from spire.doc.common import *

# Create a Document object
doc = Document()

# Load a Word file
doc.LoadFromFile("C:/Users/Administrator/Desktop/input.docx")

# Find all hyperlinks in the Word document
hyperlinks = []
for i in range(doc.Sections.Count):
    section = doc.Sections.get_Item(i)
    for j in range(section.Body.ChildObjects.Count):
        sec = section.Body.ChildObjects.get_Item(j)
        if sec.DocumentObjectType == DocumentObjectType.Paragraph:
            for k in range((sec if isinstance(sec, Paragraph) else None).ChildObjects.Count):
                para = (sec if isinstance(sec, Paragraph)
                        else None).ChildObjects.get_Item(k)
                if para.DocumentObjectType == DocumentObjectType.Field:
                    field = para if isinstance(para, Field) else None
                    if field.Type == FieldType.FieldHyperlink:
                        hyperlinks.append(field)

# Get a specific hyperlink
hyperlink = hyperlinks[0]

# Change the appearance of the hyperlink
hyperlink.CharacterFormat.UnderlineStyle = UnderlineStyle.none
hyperlink.CharacterFormat.TextColor = Color.get_Purple()
hyperlink.CharacterFormat.Bold = True

# Save the document to a docx file
doc.SaveToFile("output/ChangeAppearance.docx", FileFormat.Docx)
doc.Close()

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Headers and footers in Word are sections at the top and bottom margins of each page. They can contain additional information such as page numbers, document titles, dates, author names, and other identifying details. By default, the headers or footers on all pages are the same, but in certain scenarios, you can also insert different headers or footers on the first page, odd pages, or even pages. This article will demonstrate how to insert headers and footers into a Word document in Python using Spire.Doc for Python.

• Insert Headers and Footers into a Word Document
• Add Different Headers and Footers for the First Page and Other Pages
• Add Different Headers and Footers for Odd and Even Pages

Install Spire.Doc for Python

This scenario requires Spire.Doc for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip commands.

pip install Spire.Doc

If you are unsure how to install, please refer to this tutorial: How to Install Spire.Doc for Python on Windows

Insert Headers and Footers into a Word Document in Python

To insert a header or a footer into a Word document, you first need to get them through the Section.HeadersFooters.Header and Section.HeadersFooters.Footer properties, and then add paragraphs to them to insert pictures, text, page numbers, dates, or other information.

The following are steps to add headers and footers in Word:

• Create a Document object.
• Load a Word document using Document.LoadFromFile() method.
• Get a specified section using Document.Sections[] property.
• Add Header
  • Get header using Section.HeadersFooters.Header property.
  • Add a paragraph to the header using HeaderFooter.AddParagraph() method and set paragraph alignment.
  • Add an image to the header paragraph using Paragraph.AppendPicture() method, and then set the text wrapping style and position of the image.
  • Add text to the header paragraph using Paragraph.AppendText() method, and then set the font name, size, color, etc.
• Add Footer
  • Get footer using Section.HeadersFooters.Footer property.
  • Add a paragraph to the footer and then add text to the footer paragraph.
  • Get the borders of the footer paragraph using Paragraph.Format.Borders property, and then set the top border style and space.
• Save the result document using Document.SaveToFile() method.

from spire.doc import *
from spire.doc.common import *

# Create a Document object
document = Document()

# Load a Word document 
document.LoadFromFile("Sample.docx")

# Get the first section
section = document.Sections[0]

# Get header
header = section.HeadersFooters.Header

# Add a paragraph to the header and set its alignment style
headerParagraph = header.AddParagraph()
headerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Left

# Add an image to the header paragraph and set its text wrapping style, position
headerPicture = headerParagraph.AppendPicture("Logo.png")
headerPicture.TextWrappingStyle = TextWrappingStyle.Square
headerPicture.VerticalOrigin = VerticalOrigin.Line
headerPicture.VerticalAlignment = ShapeVerticalAlignment.Center

# Add text to the header paragraph and set its font style
text = headerParagraph.AppendText("E-iceblue Co. Ltd."+ "\nYour Office Development Master")
text.CharacterFormat.FontName = "Arial"
text.CharacterFormat.FontSize = 10
text.CharacterFormat.Bold = True
text.CharacterFormat.TextColor = Color.get_Blue()

# Get footer
footer = section.HeadersFooters.Footer

# Add a paragraph to the footer paragraph and set its alignment style
footerParagraph = footer.AddParagraph()
footerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Center

# Add text to the footer paragraph and set its font style
text = footerParagraph.AppendText("Copyright © 2013 e-iceblue. All Rights Reserved.")
text.CharacterFormat.FontName = "Arial"
text.CharacterFormat.FontSize = 10

# Set the border of the footer paragraph
footerParagraph.Format.Borders.Top.BorderType = BorderStyle.Single
footerParagraph.Format.Borders.Top.Space = 0.05

# Save the result file
document.SaveToFile("HeaderAndFooter.docx", FileFormat.Docx)
document.Close()

Add Different Headers and Footers for the First Page and Other Pages in Word in Python

Sometimes you may need to insert a header and footer only on the first page, or you may want the header and footer on the first page different from other pages.

Spire.Doc for Python offers the Section.PageSetup.DifferentFirstPageHeaderFooter property to enable a different first page header or footer. The following are the detailed steps to accomplish the task.

• Create a Document object.
• Load a Word document using Document.LoadFromFile() method.
• Get a specified section using Document.Sections[] property.
• Enable different headers and footers for the first page and other pages by setting the Section.PageSetup.DifferentFirstPageHeaderFooter property to True.
• Get the first page header using Section.HeadersFooters.FirstPageHeader property.
• Add a paragraph to the first page header and then add an image to the header paragraph.
• Get the first page footer using Section.HeadersFooters.FirstPageFooter property.
• Add a paragraph to the first page footer and then add text to the footer paragraph.
• Set headers and footers for other pages. (There's no need to set this if you only need the header & footer for the first page.)
• Save the result document using Document.SaveToFile() method.

from spire.doc import *
from spire.doc.common import *

# Create a Document object
doc = Document()

# Load a Word document 
doc.LoadFromFile("Sample.docx")

# Get the first section
section = doc.Sections[0]

# Enable different headers and footers for the first page and other pages
section.PageSetup.DifferentFirstPageHeaderFooter = True

# Add a paragraph to the first page header and set its alignment style
headerParagraph = section.HeadersFooters.FirstPageHeader.AddParagraph()
headerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Right

# Add an image to the header paragraph
headerimage = headerParagraph.AppendPicture("E-iceblue.png")

# Add a paragraph to the first page footer and set its alignment style
footerParagraph = section.HeadersFooters.FirstPageFooter.AddParagraph()
footerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Center

# Add text to the footer paragraph and set its font style
text = footerParagraph.AppendText("Different First Page Footer")
text.CharacterFormat.FontSize = 11

# Set the header & footer for other pages. If you only headers & footers for the first page, don't set this.
para = section.HeadersFooters.Header.AddParagraph()
para.Format.HorizontalAlignment = HorizontalAlignment.Left
paraText = para.AppendText("A Professional Word Python API")
paraText.CharacterFormat.FontSize = 12
paraText.CharacterFormat.TextColor = Color.get_DeepPink()
para.Format.Borders.Bottom.BorderType = BorderStyle.Single
para.Format.Borders.Bottom.Space = 0.05

paragraph = section.HeadersFooters.Footer.AddParagraph()
paragraph.Format.HorizontalAlignment = HorizontalAlignment.Center
paraText = paragraph.AppendText("E-iceblue Co. Ltd.")
paraText.CharacterFormat.FontSize = 12
paraText.CharacterFormat.Bold = True
paraText.CharacterFormat.TextColor = Color.get_DodgerBlue()

# Save the result document
doc.SaveToFile("DifferentFirstPage.docx", FileFormat.Docx)
doc.Close()

Add Different Headers and Footers for Odd and Even Pages in Word in Python

To have different headers and footers on odd and even pages, Spire.Doc for Python provides the Section.PageSetup.DifferentOddAndEvenPagesHeaderFooter property. The following are the detailed steps.

• Create a Document object.
• Load a Word document using Document.LoadFromFile() method.
• Get a specified section using Document.Sections[] property.
• Enable different headers and footers for the odd and even pages by setting the Section.PageSetup.DifferentOddAndEvenPagesHeaderFooter property to True.
• Get the header and footer of odd pages using Section.HeadersFooters.OddHeader and Section.HeadersFooters.OddFooter properties.
• Add paragraphs to the header and footer of odd pages and then add text to them.
• Get the header and footer of even pages using Section.HeadersFooters.EvenHeader and Section.HeadersFooters.EvenFooter properties.
• Add paragraphs to the header and footer of even pages and then add text to them.
• Save the result document using Document.SaveToFile() method.

from spire.doc import *
from spire.doc.common import *

# Create a Document object
doc = Document()

# Load a Word document 
doc.LoadFromFile("Sample.docx")

# Get the first section
section = doc.Sections[0]

# Enable different headers and footers for the odd and even pages
section.PageSetup.DifferentOddAndEvenPagesHeaderFooter = True

# Add headers and footers to odd pages
OHpara = section.HeadersFooters.OddHeader.AddParagraph()
OHtext = OHpara.AppendText("Odd Page Header")
OHpara.Format.HorizontalAlignment = HorizontalAlignment.Center
OHtext.CharacterFormat.FontName = "Arial"
OHtext.CharacterFormat.FontSize = 12
OHtext.CharacterFormat.TextColor = Color.get_Red()

OFpara = section.HeadersFooters.OddFooter.AddParagraph()
OFtext = OFpara.AppendText("Odd Page Footer")
OFpara.Format.HorizontalAlignment = HorizontalAlignment.Center
OFtext.CharacterFormat.FontName = "Arial"
OFtext.CharacterFormat.FontSize = 12
OFtext.CharacterFormat.TextColor = Color.get_Red()

# Add headers and footers to even pages
EHpara = section.HeadersFooters.EvenHeader.AddParagraph()
EHtext = EHpara.AppendText("Even Page Header")
EHpara.Format.HorizontalAlignment = HorizontalAlignment.Center
EHtext.CharacterFormat.FontName = "Arial"
EHtext.CharacterFormat.FontSize = 12
EHtext.CharacterFormat.TextColor = Color.get_Blue()

EFpara = section.HeadersFooters.EvenFooter.AddParagraph()
EFtext = EFpara.AppendText("Even Page Footer")
EFpara.Format.HorizontalAlignment = HorizontalAlignment.Center
EFtext.CharacterFormat.FontName = "Arial"
EFtext.CharacterFormat.FontSize = 12
EFtext.CharacterFormat.TextColor = Color.get_Blue()

# Save the result document
doc.SaveToFile("OddAndEvenHeaderFooter.docx", FileFormat.Docx)
doc.Close()

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.