Converting Excel to Word Tables with Python Maintaining Formatting

Data management and conversion play an important role in daily work. Often, we need to import data from Excel into Word documents to generate reports, create presentations, or archive documents. However, this process involves not just simple data transfer but also ensuring the integrity of the format to maintain the document's professionalism and readability. This article will teach you how to use Spire.XLS for Python and Spire.Doc for Python to easily export Excel data and create beautiful tables in Word, thereby enhancing your work efficiency.

Environment Setup

First, ensure you have the required libraries installed. You will need Spire.XLS and Spire.Doc. Spire.XLS is a powerful library for processing Excel files that supports reading, editing, and generating Excel files (.xlsx and .xls formats). Spire.Doc is a robust library for processing Word documents that allows users to create, edit, and read Word documents (.doc and .docx formats).

Installation commands:

pip install spire.xls spire.doc

Code Implementation

Here is a complete code example for exporting Excel data as a Word table:

from spire.xls import *
from spire.doc import *

def MergeCells(sheet, table):
    """Merge corresponding cells in the Word table based on merged cells in the Excel worksheet."""
    if sheet.HasMergedCells:
        ranges = sheet.MergedCells
        for i in range(len(ranges)):
            startRow = ranges[i].Row
            startColumn = ranges[i].Column
            rowCount = ranges[i].RowCount
            columnCount = ranges[i].ColumnCount

            if rowCount > 1 and columnCount > 1:
                for j in range(startRow, startRow + rowCount):
                    table.ApplyHorizontalMerge(j - 1, startColumn - 1, startColumn - 1 + columnCount - 1)
                table.ApplyVerticalMerge(startColumn - 1, startRow - 1, startRow - 1 + rowCount - 1)

            if rowCount > 1 and columnCount == 1:
                table.ApplyVerticalMerge(startColumn - 1, startRow - 1, startRow - 1 + rowCount - 1)

            if columnCount > 1 and rowCount == 1:
                table.ApplyHorizontalMerge(startRow - 1, startColumn - 1, startColumn - 1 + columnCount - 1)

def CopyStyle(wTextRange, xCell, wCell):
    """Copy cell style from Excel to Word."""
    # Copy font styles
    wTextRange.CharacterFormat.TextColor = Color.FromRgb(xCell.Style.Font.Color.R, xCell.Style.Font.Color.G, xCell.Style.Font.Color.B)
    wTextRange.CharacterFormat.FontSize = float(xCell.Style.Font.Size)
    wTextRange.CharacterFormat.FontName = xCell.Style.Font.FontName
    wTextRange.CharacterFormat.Bold = xCell.Style.Font.IsBold
    wTextRange.CharacterFormat.Italic = xCell.Style.Font.IsItalic

    # Copy background color
    if xCell.Style.FillPattern is not ExcelPatternType.none:
        wCell.CellFormat.BackColor = Color.FromRgb(xCell.Style.Color.R, xCell.Style.Color.G, xCell.Style.Color.B)

    # Copy alignment
    wCell.CellFormat.HorizontalAlignment = {
        HorizontalAlignType.Left: HorizontalAlignment.Left,
        HorizontalAlignType.Center: HorizontalAlignment.Center,
        HorizontalAlignType.Right: HorizontalAlignment.Right
    }.get(xCell.HorizontalAlignment)

    wCell.CellFormat.VerticalAlignment = {
        VerticalAlignType.Bottom: VerticalAlignment.Bottom,
        VerticalAlignType.Center: VerticalAlignment.Middle,
        VerticalAlignType.Top: VerticalAlignment.Top
    }.get(xCell.VerticalAlignment)

# Load the Excel file
workbook = Workbook()
workbook.LoadFromFile("Contact list.xlsx")

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

# Create a Word document
doc = Document()
section = doc.AddSection()
section.PageSetup.Orientation = PageOrientation.Landscape

# Add a table
table = section.AddTable(True)
table.ResetCells(sheet.LastRow, sheet.LastColumn)

# Merge corresponding cells in the Word table based on merged cells in the Excel worksheet
MergeCells(sheet, table)

# Export data and cell styles from Excel to the Word table
for r in range(1, sheet.LastRow + 1):
    table.Rows[r - 1].Height = float(sheet.Rows[r - 1].RowHeight)

    for c in range(1, sheet.LastColumn + 1):
        xCell = sheet.Range[r, c]
        wCell = table.Rows[r - 1].Cells[c - 1]

        # Copy data
        textRange = wCell.AddParagraph().AppendText(xCell.NumberText)

        # Copy cell styles
        CopyStyle(textRange, xCell, wCell)

# Save the Word document to a file
doc.SaveToFile("Excel to Word Table.docx", FileFormat.Docx)

Run

Code Explanation

1. Merging Cells (MergeCells Function) : This function manages the merging of cells to ensure that merged cells in Excel are preserved in Word.
2. Copying Styles (CopyStyle Function) : This function is designed to accurately copy the formatting of Excel cells (such as font, color, and alignment) into the Word table.
3. Loading and Processing Data : Using Spire.XLS to read data from the Excel file, a Word document is created, and a table is built within it.
4. Exporting Data : By iterating through each row and column of Excel, data is imported into the Word table while simultaneously applying styles.

Summary

With the Spire.XLS and Spire.Doc libraries, Python developers can easily export Excel data to Word documents while ensuring formatting integrity. This conversion not only enhances work efficiency but also improves the professionalism of documents, making it suitable for various business and academic scenarios. I hope the code examples in this article help streamline your data processing tasks.