How to fill word table template in excel using python

Question

I have Word document inside tables, I need to fill the tables using data inside an Excel file.

I have tried, But I am not getting any help related python codes.

[import xlrd
loc=('D:/SAIKUMAR/TEMPLATE/Dynamic_Reporting_Excel_Input.xlsx')
file=xlrd.open_workbook(loc,on_demand=True  )
sheet=file.sheet_by_index(0)
print("First row index: ",sheet.cell_value(0,0))#index for first columns 
print("no.of rows:",sheet.nrows)# count the number of rows
print("no of columns:",sheet.ncols)
for i in range(0,sheet.ncols):# print which row data you want 
    print(sheet.cell_value(0,i))
print('____________________')
for i in range(sheet.nrows): # print which column data you want 
    print(sheet.cell_value(i,6))

print(sheet.row_values(1))    

#arrayofvalues = sheet.col_values(columnindex)
arrayofvalues = sheet.col_values(6)

print(arrayofvalues)


########################################################
from docx.api import Document

# Load the first table from your document. In your example file,
# there is only one table, so I just grab the first one.
document = Document('Dynamic_Reporting_Word_Template.docx')
table = document.tables\[1\]
#print(table)
# Data will be a list of rows represented as dictionaries
# containing each row's data.
data = \[\]
keys = None
for i, row in enumerate(table.rows):
    text=(cell.text for cell in row.cells)
    if i==0:
        keys=tuple(text)
        continue
    row_data=dict(zip(keys, text))
    data.append(row_data)][1]

That is my template, i given by image, actually it was stored on word document.

If you have it in xlsx, you can have it converted to .csv And after that you can easily find boilerplate scripts to populate excel cells using .csv values. You may find this helpful. https://pbpython.com/python-word-template.html — shuberman, Aug 13 '19 at 10:50
But my template is not looking like that, Its looking like tables — Saikumar Kshatriya, Aug 13 '19 at 11:09
You may find this helpfiul -- https://stackoverflow.com/questions/10366596/how-to-read-contents-of-an-table-in-ms-word-file-using-python/33775294#33775294 — shuberman, Aug 13 '19 at 11:20
I want to know how the excel data comes to word table, based on columns heading. — Saikumar Kshatriya, Aug 13 '19 at 13:35
Ok so you wanted to fetch data from your excel tables to a word template. — shuberman, Aug 13 '19 at 14:06
Yes, That's what i am waiting to you understand, Sincerely Thank you. — Saikumar Kshatriya, Aug 14 '19 at 04:30
It would be wise to make use of VBScript for this purpose, I have done one such automation in the past but using VBScripts Macros. If you allow me to post such a solution let me know. — shuberman, Aug 14 '19 at 09:18
My answer would result a down vote because your tag mentions `python` .either change the tag to `VBScript` and macros or post your query in a new question. I hope you understand my concern because others will down vote it for giving an unrelated answer. — shuberman, Aug 14 '19 at 12:12

score 0 · Answer 1 · edited Aug 15 '19 at 10:43

In the VBA editor, I used the Tools > References menu option to add a reference to the Microsoft Excel 12.0 Object Library.

At the top of the module in VBA, I added the following declaration to use 1, instead of 0, as the starting index of an array. (This is not entirely necessary, but it is relevant to my sample code.)

Option Base 1

Here is my sample code for my main procedure:

Sub TestLoadingExcelData()
    Const TemplatePath = "C:\Users\Rupert\AppData\Roaming\Microsoft\Templates\New_LPD.dotm"
    Const SpreadsheetPath = "D:\Rupert\Documents\MyTimeLine.xlsx"
    Const targetPath = "D:\Rupert\Documents\tmp\"

    Const SheetName = "Sheet1"
    Const NumberOfCols = 12
    Const StartingRow = 2
    Const MaxRows = 500
    Dim TwoDimArray(MaxRows, NumberOfCols) As String
    Dim i As Long, j As Long
    Dim tmpText As String

    Call LoadDataFromOneSheet(SpreadsheetPath, SheetName, NumberOfCols, StartingRow, TwoDimArray, MaxRows)

    'Type the text from the array.
    For i = StartingRow To MaxRows
        If TwoDimArray(i, 1) = "" Then Exit For
        Documents.Add Template:=TemplatePath, NewTemplate:=False, DocumentType:=0

        'Go to the starting point to write text in the template. I'm using some text that I happened to have in my template.
            Selection.Find.ClearFormatting
        With Selection.Find
            .Text = "This is the built-in Heading 2."
            .Replacement.Text = ""
            .Forward = True
            .Wrap = wdFindContinue
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False
        End With
        Selection.Find.Execute
        'Delete the placeholder text.
        Selection.Delete

        For j = 1 To NumberOfCols
            tmpText = TwoDimArray(i, j)
            If j = 1 Then
                Selection.TypeText Text:=tmpText & vbCr
            ElseIf tmpText <> "" Then
                Selection.TypeText Text:="---" & tmpText & vbCr
            End If
        Next j
        ActiveDocument.SaveAs FileName:="""" & targetPath & TwoDimArray(i, 1) & ".docx"""
        ActiveDocument.Close
    Next i

End Sub

Here is my sample code for the procedure that loads the array from Excel. I use a separate procedure for this to isolate errors that may occur in the Excel application.

Private Sub LoadDataFromOneSheet(fSpreadsheetPath, fSheetName, fNumberOfCols, fStartingRow, ByRef fTwoDimArray, fMaxRows)

    On Error GoTo ErrorHandler
    Dim tmpText As String
    Dim i As Long, j As Long, emptyRowCount As Long

    Dim wbXL
    Set wbXL = CreateObject("excel.application")
    wbXL.Workbooks.Open FileName:=fSpreadsheetPath
    Dim MyWorksheet

    For Each MyWorksheet In wbXL.Worksheets
        If Trim(MyWorksheet.Name) = fSheetName Then
            For i = fStartingRow To fMaxRows
                If SmartRTrim(CStr(MyWorksheet.Cells(i, 1).Value)) = "" Then
                    'Quit when you reach an empty cell in the first column.
                    Exit For
                Else
                    For j = 1 To fNumberOfCols
                        fTwoDimArray(i, j) = SmartRTrim(CStr(MyWorksheet.Cells(i, j).Value))
                    Next j
                End If
            Next i
        End If
    Next MyWorksheet

    wbXL.Workbooks(1).Close
    wbXL.Quit
    Set wbXL = Nothing
Exit Sub
' Error-handling routine.
ErrorHandler:
    wbXL.Quit
    Set wbXL = Nothing
    MsgBox SpreadsheetPath & vbCr & Err.Description
End Sub

Here is the function that I use to trim extraneous control characters from Excel cell text:

Public Function SmartRTrim(fstring) As String
    'Remove control characters and spaces from the end of the string.
    Dim tmpString As String
    Dim i As Integer

    tmpString = fstring
    For i = 1 To 300
        If tmpString = "" Then
            Exit For
        Else
            If Asc(Right(tmpString, 1)) < 30 Or Right(tmpString, 1) = " " Then
                tmpString = Left(tmpString, Len(tmpString) - 1)
            Else
                Exit For
            End If
        End If
    Next i
    SmartRTrim = RTrim(tmpString)
End Function

This code should give you a general idea of how I get data from excel to a word template.

How to fill word table template in excel using python

1 Answers1