Insert excel 2007 table to word document using python 2.7.5

This is the place for queries that don't fit in any of the other categories.

Insert excel 2007 table to word document using python 2.7.5

Postby newbieX » Mon Mar 31, 2014 11:44 pm

How can I alter my script(s) so that I put an excel table in my word document at a specified location if certain conditions are met? (I may actually add several tables if certain conditions are met, but I figured I would submit a stripped down script to get the gist of how to do it and modify my real script with this new wisdom. It’s a great way to learn the code instead of just taking someone else’s as my own)

I have a python script and a macro enabled excel workbook which is working great. The python script tests to see how many cats the owner has and constructs sentences based on this number. The script then opens a word document template, and replaces specific text keywords with the newly constructed sentences.

Right now my word document (cats.docx) looks like this:
Code: Select all
SENTENCETABLEBLURB

Thus if Owner “John” has one cat, the word document would look like this after running the python script: (I know too many names. It’s just an example)
Code: Select all
John owns 1 cat. Its name is Fluffy, Tiger, Lili, Bonzo, Mittens

5 cats and the sentence would be:
Code: Select all
John owns 5 cats. Their names are Fluffy, Tiger, Lili, Bonzo, Mittens

15 cats and the sentence would be:
Code: Select all
John owns 11 cats. Their names are: 

INSERT TABLE HERE
There are over 9 names in this table!
And they are totally awesome!

Also, if there were over 9 cats, the python script would call the following macro from my Cats.xlsm file. The macro would import a dbf file and format the data as a table and save it as Cats.xlsm.

The problem is that I don’t know how, or even if it is possible, to add this table to the word document at the INSERT TABLE HERE location. Thus the word document would really look like this if there were 15 cats:

Code: Select all
John owns 15 cats. Their names are:

Fluffy       Mittens      Tiger
Ginger       Blackie      Snoopy
Zippy        Friskit      Snowball
Oreo         Felix        Beans
Jester       Moxie        Rowdy
There are over 9 names in this table!
And they are totally awesome!


Python Code:
Code: Select all
import zipfile
import os
import shutil
import sys
import win32com.client

OWNER = ("John")
NUMBER = (15)
NAMES = ("Fluffy, Tiger, Lili, Bonzo, Mittens")

projectArea = ("C:\\tests\\Target\\results\\")
fileCheck=(projectArea + OWNER + " cats.docx")
try:
    os.remove(fileCheck)
except OSError:
    pass

template = zipfile.ZipFile( "C:\\tests\\Source\\cats.docx")
myFile = zipfile.ZipFile(projectArea + OWNER + " cats.docx", "a")

# tests to see how many cats the OWNER and has returns appropriate sentence. I want to insert the table
# that is formatted by the macro that is called at the end of this script into the word document
# the sentences are written to.

if (NUMBER) >= 9:
    NUMBER = str(NUMBER)
    sentence = (OWNER + " owns " + NUMBER+" cats. Their names are:<w:cr/><w:cr/>")
    table = ("INSERT TABLE HERE<w:cr/>")
    blurb = ("There are over 9 names in this table!<w:cr/>And they are totally awesome!")

    # Calls macro from Cats.xlsm and quits excel after macro runs.

    mySourceExcelFile = "C:\\tests\\Source\\Cats.xlsm"
    projectName = ("Cats")
    xlApp = win32com.client.DispatchEx('Excel.Application')
    xlsPath = os.path.expanduser(mySourceExcelFile)
    wb = xlApp.Workbooks.Open(Filename=xlsPath)
    wb.Application.DisplayAlerts = False
    xlApp.Run("FormatFile", projectArea, projectName)
    xlApp.Quit()

elif (NUMBER) > 1:
    NUMBER = str(NUMBER)
    sentence = (OWNER + " owns " + NUMBER+" cats. Their names are " + NAMES)
    table = ("")
    blurb = ""
elif (NUMBER) == 1:
    NUMBER = str(NUMBER)
    sentence = (OWNER + " owns " + NUMBER+" cat. Its name is " + NAMES)
    table = ("")
    blurb = ""
else:
    sentence = ("John doesn't own any cats")
    table = ("")
    blurb = ""

replaceText = {"SENTENCE" : sentence,
            "TABLE" : table,
         "BLURB" : blurb}

# open word document template and replaces text with appropriate text. I would like
# to imput the formatted excel table for the variable "table" instead of text.

with open(template.extract("word/document.xml", projectArea)) as tempXmlFile:
    tempXml = tempXmlFile.read()

for key in replaceText.keys():
    tempXml = tempXml.replace(str(key), str(replaceText.get(key)))

with open(projectArea + "temp.xml", "w+") as tempXmlFile:
    tempXmlFile.write(tempXml)

for file in template.filelist:
    if not file.filename == "word/document.xml":
      myFile.writestr(file.filename, template.read(file))

myFile.write(projectArea + "temp.xml", "word/document.xml")

myFile.close()
template.close()

# Calls macro from Cats.xlsm and quits excel after macro runs.
mySourceExcelFile = "C:\\tests\\Source\\Cats.xlsm"
projectName = ("Cats")
xlApp = win32com.client.DispatchEx('Excel.Application')
xlsPath = os.path.expanduser(mySourceExcelFile)
wb = xlApp.Workbooks.Open(Filename=xlsPath)
wb.Application.DisplayAlerts = False
xlApp.Run("FormatFile", projectArea, projectName)

xlApp.Quit()


Excel VBA Macro
Code: Select all
Sub FormatFile(strResultsDir As String, targetFileName As String)
'
'
    Application.DisplayAlerts = False
   
    Workbooks.Open fileName:=strResultsDir & targetFileName & ".dbf"
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1", ActiveCell.SpecialCells(xlLastCell)), , xlYes).Name = _
        "Table1"
    Range("Table1[#All]").Select
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight1"

    ActiveWorkbook.SaveAs fileName:=strResultsDir & targetFileName & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close
    Application.Quit

End Sub


Suggestions as to how to accomplish this? I am completely stumped as to how to do this.
I am just beginning to learn python so if I am doing this in a weird, convoluted way, please provide constructive, friendly criticism.
Last edited by Mekire on Mon Mar 31, 2014 11:49 pm, edited 1 time in total.
Reason: First post lock. Red text replaced with code tags.
newbieX
 
Posts: 1
Joined: Mon Mar 31, 2014 11:20 pm

Return to General Coding Help

Who is online

Users browsing this forum: snippsat and 2 guests