Modern workflows often span multiple platforms-while analysts work with data in Excel, polished reports are created in Word. Manually copying data between these documents can lead to errors, version conflicts, and inconsistent formatting. Python-driven automation provides an efficient solution by seamlessly integrating Excel's data capabilities with Word's formatting strengths. This integration ensures data integrity, reduces repetitive formatting, and accelerates report creation for financial, academic, and compliance-related tasks.
This article explores how to use Spire.Office for Python to insert Excel tables into Word documents using Python code.
- Read Excel Data and Insert It into Word Documents
- Copy Data and Formatting from Excel to Word
- Integrate Excel Worksheets as OLE into Word Documents
Install Spire.Office for Python
This scenario requires Spire.Office for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.
pip install Spire.Office
Read Excel Data and Insert It into Word Documents
With Spire.XLS for Python, developers can extract data from Excel worksheets while preserving number formatting using the CellRange.NumberText property. The extracted data can then be inserted into a Word table created using Spire.Doc for Python. This method is ideal for simple Excel worksheets and cases requiring table reformatting.
Steps to Read Excel Data and Insert It into Word:
- Create an instance of the Workbook class and load an Excel file using the Workbook.LoadFromFile() method.
- Retrieve the worksheet using the Workbook.Worksheets.get_Item() method and obtain the used cell range with the Worksheet.AllocatedRange property.
- Initialize a Document instance to create a Word document.
- Add a section using the Document.AddSection() method and insert a table using the Section.AddTable() method.
- Define the number of rows and columns based on the used cell range with the Table.ResetCells() method.
- Iterate through the rows and columns of the used cell range.
- Retrieve the corresponding table cell using the Table.Rows.get_Item().Cells.get_Item() method and add a paragraph using the TableCell.AddParagraph() method.
- Extract the cell value using the CellRange.get_Item().NumberText property and append it to the paragraph using the Paragraph.AppendText() method.
- Apply the required formatting to the Word table.
- Save the Word document using the Document.SaveToFile() method.
- Python
from spire.doc import Document, AutoFitBehaviorType, FileFormat, DefaultTableStyle
from spire.xls import Workbook
# Specify the file names
excel_file = "Sample.xlsx"
word_file = "output/ExcelDataToWord.docx"
# Create a Workbook instance
workbook = Workbook()
# Load the Excel file
workbook.LoadFromFile(excel_file)
# Get the first worksheet
sheet = workbook.Worksheets.get_Item(0)
# Get the used cell range in the first worksheet
allocatedRange = sheet.AllocatedRange
# Create a Document instance
doc = Document()
# Add a section to the document and add a table to the section
section = doc.AddSection()
table = section.AddTable()
# Reset the number of rows and columns in the Word table to match the number of rows and columns in the Excel worksheet
table.ResetCells(allocatedRange.RowCount, allocatedRange.ColumnCount)
# Loop through each row and column in the used cell range
for rowIndex in range(allocatedRange.RowCount):
# Loop through each column in the row
for colIndex in range(allocatedRange.ColumnCount):
# Add a cell to the Word table and add a paragraph to the cell
cell = table.Rows.get_Item(rowIndex).Cells.get_Item(colIndex)
paragraph = cell.AddParagraph()
# Append the cell value to the Word table
paragraph.AppendText(allocatedRange.get_Item(rowIndex + 1, colIndex + 1).NumberText)
# Auto-fit the table to the window and apply a table style
table.AutoFit(AutoFitBehaviorType.AutoFitToWindow)
table.ApplyStyle(DefaultTableStyle.GridTable1LightAccent6)
# Save the Word document
doc.SaveToFile(word_file, FileFormat.Docx2019)
# Dispose resources
doc.Dispose()
workbook.Dispose()
Copy Data and Formatting from Excel to Word
Spire.XLS for Python and Spire.Doc for Python can also be used together to copy both data and formatting from Excel to Word, preserving the table's original structure and appearance.
To handle format preservation, two helper methods are needed:
- MergeCells: Merges table cells in Word according to the merged cells in the Excel worksheet.
- CopyFormatting: Copies Excel cell formatting (font style, background color, horizontal and vertical alignment) to the Word table.
Steps to Copy Data and Formatting:
- Create a Workbook instance and load an Excel file using the Workbook.LoadFromFile() method.
- Retrieve a worksheet using the Workbook.Worksheets.get_Item() method.
- Initialize a Document instance and add a section with the Document.AddSection() method.
- Insert a table using the Section.AddTable() method.
- Adjust the table’s structure based on the worksheet using the Table.ResetCells() method.
- Apply cell merging using the MergeCells() method.
- Iterate through each worksheet row and set row heights using the Table.Rows.get_Item().Height property.
- For each column in a row:
- Retrieve worksheet cells using the Worksheet.Range.get_Item() method and table cells using the TableRow.Cells.get_Item() method.
- Extract cell data using the CellRange.NumberText property and append it to the table cell using the TableCell.AddParagraph().AppendText() method.
- Apply formatting using the CopyFormatting() method.
- Save the Word document using the Document.SaveToFile() method.
- Python
from spire.xls import Workbook, HorizontalAlignType, ExcelPatternType, VerticalAlignType
from spire.doc import Document, Color, HorizontalAlignment, VerticalAlignment, PageOrientation, FileFormat
def MergeCells(worksheet, wordTable):
# Check if there are merged cells
if not worksheet.HasMergedCells:
return
for cell_range in worksheet.MergedCells:
start_row, start_col = cell_range.Row, cell_range.Column
row_count, col_count = cell_range.RowCount, cell_range.ColumnCount
# Process horizontal merging
if col_count > 1:
for row in range(start_row, start_row + row_count):
wordTable.ApplyHorizontalMerge(row - 1, start_col - 1, start_col - 1 + col_count - 1)
# Process vertical merging
if row_count > 1:
wordTable.ApplyVerticalMerge(start_col - 1, start_row - 1, start_row - 1 + row_count - 1)
def CopyFormatting(tableTextRange, excelCell, wordCell):
# Copy font styles
font = excelCell.Style.Font
tableTextRange.CharacterFormat.TextColor = Color.FromRgb(font.Color.R, font.Color.G, font.Color.B)
tableTextRange.CharacterFormat.FontSize = float(font.Size)
tableTextRange.CharacterFormat.FontName = font.FontName
tableTextRange.CharacterFormat.Bold = font.IsBold
tableTextRange.CharacterFormat.Italic = font.IsItalic
# Copy background colors
if excelCell.Style.FillPattern != ExcelPatternType.none:
wordCell.CellFormat.BackColor = Color.FromRgb(excelCell.Style.Color.R, excelCell.Style.Color.G,
excelCell.Style.Color.B)
# Copy the horizontal alignment
hAlignMap = {
HorizontalAlignType.Left: HorizontalAlignment.Left,
HorizontalAlignType.Center: HorizontalAlignment.Center,
HorizontalAlignType.Right: HorizontalAlignment.Right
}
if excelCell.HorizontalAlignment in hAlignMap:
tableTextRange.OwnerParagraph.Format.HorizontalAlignment = hAlignMap[excelCell.HorizontalAlignment]
# Copy the vertical alignment
vAlignMap = {
VerticalAlignType.Top: VerticalAlignment.Top,
VerticalAlignType.Center: VerticalAlignment.Middle,
VerticalAlignType.Bottom: VerticalAlignment.Bottom
}
if excelCell.VerticalAlignment in vAlignMap:
wordCell.CellFormat.VerticalAlignment = vAlignMap[excelCell.VerticalAlignment]
# Specify the file names
excelFileName = "Sample.xlsx"
wordFileName = "output/ExcelDataFormatToWord.docx"
# Create a Workbook instance and load the Excel file
workbook = Workbook()
workbook.LoadFromFile(excelFileName)
# Get a worksheet
sheet = workbook.Worksheets.get_Item(0)
# Create a Document instance
doc = Document()
# Add a section to the document and set the page orientation
section = doc.AddSection()
section.PageSetup.Orientation = PageOrientation.Landscape
# Add a table to the section
table = section.AddTable()
# Set the number of rows and columns according to the number of rows and columns in the Excel worksheet
table.ResetCells(sheet.LastRow, sheet.LastColumn)
# Execute the MergeCells method to merge cells
MergeCells(sheet, table)
# Iterate through each row and column in the Excel worksheet
for r in range(1, sheet.LastRow + 1):
tableRow = table.Rows.get_Item(r - 1)
tableRow.Height = float(sheet.Rows.get_Item(r - 1).RowHeight)
for c in range(1, sheet.LastColumn + 1):
# Get the corresponding cell in the Excel worksheet and the cell in the Word table
eCell = sheet.Range.get_Item(r, c)
wCell = table.Rows.get_Item(r - 1).Cells.get_Item(c - 1)
# Append the cell value to the Word table
textRange = wCell.AddParagraph().AppendText(eCell.NumberText)
# Copy the cell formatting
CopyFormatting(textRange, eCell, wCell)
# Save the Word document
doc.SaveToFile(wordFileName, FileFormat.Docx2019)
doc.Dispose()
workbook.Dispose()
Integrate Excel Worksheets as OLE into Word Documents
Beyond copying data and formatting, Excel worksheets can be embedded as OLE objects in Word documents. This approach enables full worksheet visualization and allows users to edit Excel data directly from the Word document.
Using the Paragraph.AppendOleObject(str: filename, DocPicture, OleObjectType.ExcelWorksheet) method, developers can easily insert an Excel file as an OLE object.
Steps to Insert an Excel Worksheet as an OLE Object:
- Create a Workbook instance and load an Excel file using the Workbook.LoadFromFile() method.
- Retrieve a worksheet using the Workbook.Worksheets.get_Item() method and save it as an image using the Worksheet.ToImage().Save() method.
- Initialize a Document instance to create a Word document.
- Add a section using the Document.AddSection() method and insert a paragraph using the Section.AddParagraph() method.
- Create a DocPicture instance and load the saved image using the DocPicture.LoadImage() method.
- Resize the image according to the page layout using the DocPicture.Width property.
- Insert the Excel file as an OLE object into the paragraph using the Paragraph.AppendOleObject() method.
- Set the DocOleObject.DisplayAsIcon property to False to ensure that the OLE object updates dynamically after worksheet edits.
- Save the Word document using the Document.SaveToFile() method.
- Python
from spire.doc import Document, DocPicture, FileFormat, OleObjectType from spire.xls import Workbook # Specify the file path and names excelFileName = "Sample.xlsx" wordFileName = "output/ExcelOleToWord.docx" tempImageName = "SheetImage.png" # Create a Workbook instance and load the Excel file workbook = Workbook() workbook.LoadFromFile(excelFileName) # Save the first worksheet as an image sheet = workbook.Worksheets.get_Item(0) sheet.ToImage(1, 1, sheet.LastRow, sheet.LastColumn).Save(tempImageName) # Initialize a Document instance to create a Word document doc = Document() # Add a section to the document and add a paragraph to the section section = doc.AddSection() paragraph = section.AddParagraph() # Create a DocPicture instance and load the image pic = DocPicture(doc) pic.LoadImage(tempImageName) # Set the image width pic.Width = section.PageSetup.PageSize.Width - section.PageSetup.Margins.Left - section.PageSetup.Margins.Right # Insert the Excel file into the Word document as an OLE object and set the saved image as the display image ole = paragraph.AppendOleObject(excelFileName, pic, OleObjectType.ExcelWorksheet) # Set to not display the OLE object as an icon ole.DisplayAsIcon = False # Save the Word document doc.SaveToFile(wordFileName, FileFormat.Docx2019) workbook.Dispose() doc.Dispose()
Get a Free License
To fully experience the capabilities of Install Spire.Office for Python without any evaluation limitations, you can request a free 30-day trial license.
