Python Operations Office

Http://www.cnblogs.com/youxin/p/3548647.html?utm_source=tuicool&utm_medium=referral

First introduced the next Office Win32 COM interface, this is the MS for automation to provide the operation interface, for example, we open a Word document, we can edit VB script inside, to achieve our own effect. For this discriminating ears trading, python How to let go, it built-in for the Win32 COM interface support, we can easily control.

There is nothing more authoritative than MS-provided API documentation to be proficient in using Office Win32 COM interfaces.

Some of the most important concepts in Ord are:
Application-This is undoubtedly our Word application
Document-This is an open file object
Range-this stuff has to be used well, basically all objects have a range attribute, and this also provides us with great convenience in typesetting ...
Paragraph-As the name implies, this is the meaning of the paragraph, that is, a paragraph in our document (can be text, images, etc.).
Section-When I was studying, this stuff created the biggest hurdle for me, because I was struggling to figure out how to insert a new page and start outputting content on a new page ...
ParagraphFormat-This is for formatting, you can't use it ...
With the above several of the most important concept foreshadowing, the next code basically can be flat wade, to see a piece of code first, in advance, this code excerpt from the Internet, do not know which elder brother writing, only write can write not write things (so a bit too much, but there is no substantive content), I exaggerated the treatment of a bit, To ensure a richer nutrition, we should look more practical.

Import win32com
From win32com.client import Dispatch, constants

W = win32com.client.Dispatch (' Word.Application ')
# or use the following method to start a separate process:
# w = win32com.client.DispatchEx (' Word.Application ')

# run in the background, display the program interface without warning
w.visible = 1 #这个至少在调试阶段建议打开, or if the waiting time is long, it will at least give you patience ...
W.displayalerts = 0

# Open a new file
#worddoc = W.documents.open (file_name) #这句话用来打开已有的文件, of course, before you better decide whether the file really exists ...
doc = W.documents.add () # Create a new document, I use more of this, because I want to be created, and then saved as:

# Insert Text
MyRange = doc. Range (0,0) #这句话让你获取的是doc的最前面位置, if you want to get to another location, you need to change the parameters in range, two parameters represent the starting point, the end point ...
Myrange.insertbefore (' Hello from python! ')

‘‘‘
The following paragraph is to add 10 new pages and then jump to add content to the new page ....
‘‘‘
Section_index = 0
For I in range (0, 10):
#由于增加页的使用如此频繁, we'd better extract it as a function, like Def newtable (self):
Pre_section = doc. Secitons (Section_index)
New_seciton = doc. Range (pre_section. Range.End, Pre_section. Range.End). Sections.add ()
New_range = New_seciton. Range

CONTENT_PG = New_range. Paragraphs.add ()
Content_pg. Range.font.name,content_pg. Range.Font.Size = ' Times New Roman ', 24
Caption_pg. Range.ParagraphFormat.Alignment = 0 # 0,1,2 Align Left, center, right, respectively
Caption_pg. Range.insertbefore (' hello,page ' + str (i+1))

Section_index = Section_index + 1 #记录这个的目的是为了方便的找到doc的尾端, otherwise, I really did not think how to do ...

# Body Text Replacement
W.selection.find.clearformatting ()
W.selection.find.replacement.clearformatting ()
W.selection.find.execute (Oldstr, False, False, False, False, False, True, 1, True, NEWSTR, 2)

#设置页眉文字, if you want to set the footer value needs to change the SeekView from 9 to 10.
W.activewindow.activepane.view.seekview = 9 #9-header; 10-footer
w.selection.paragraphformat.alignment = 0
W.selection.text = ' New Header '
W.activewindow.activepane.view.seekview = 0 # release focus, return to main document

# Header text Substitution
W.activedocument.sections[0]. Headers[0]. Range.Find.ClearFormatting ()
W.activedocument.sections[0]. Headers[0]. Range.Find.Replacement.ClearFormatting ()
W.activedocument.sections[0]. Headers[0]. Range.Find.Execute (Oldstr, False, False, False, False, False, True, 1, False, NEWSTR, 2)

# Add a page at the end of the document and a table ...
Pre_section = doc. Secitons (Section_index)
New_seciton = doc. Range (pre_section. Range.End, Pre_section. Range.End). Sections.add ()
New_range = New_seciton. Range
new_table = New_range. Tables.add (Doc. Range (New_range. End,new_range. END), 5, 5) #在文档末尾添加一个5 table
#接下来Table怎么操作, here will not elaborate, retrieve table object reference is OK ...

# Table Operations
Doc. Tables[0]. Rows[0]. Cells[0]. Range.Text = ' 123123 '
WordDoc. Tables[0]. Rows.Add () # Add a row

# Convert to HTML
WC = win32com.client.constants
W.ACTIVEDOCUMENT.WEBOPTIONS.RELYONCSS = 1
W.activedocument.weboptions.optimizeforbrowser = 1
W.activedocument.weboptions.browserlevel = 0 # Constants.wdbrowserlevelv4
W.activedocument.weboptions.organizeinfolder = 0
W.activedocument.weboptions.uselongfilenames = 1
W.ACTIVEDOCUMENT.WEBOPTIONS.RELYONVML = 0
W.activedocument.weboptions.allowpng = 1
W.activedocument.saveas (FileName = filenameout, FileFormat = wc.wdformathtml)

# Print
Doc. PrintOut ()

# Close
# Doc. Close ()
W.documents.close (Wc.wddonotsavechanges)
W.quit ()

Simple example: (WPS)

#导入引用win32com. Client module Import win32com.client# new WPS Process Wpsapp=win32com.client.dispatch ("WPS. Application ") #可视wpsApp. visible=1# Add Document Wpsdoc=wpsapp.documents.add () #添加内容wpsDoc. content.text=" Hello world! " #保存文档wpsDoc. SaveAs ("C:\Sample.wps") #关闭文档 Wpsdoc. Close () #关闭进程 wpsapp.quit ()

Reference:

http://blog.csdn.net/lzl001/article/details/8435048

Another article:

Import win32com

From win32com.client import Dispatch, constants

Next, let's connect our Python program with MS Word.

MSWord = Dispatch (' Word.Application ')

Use the dispatch () method to activate Ms Word. If you have already executed MS Word, this will only connect with the existing MS Word. If you strongly wish to have a new MS Word program appear, use the following method:

MSWord = Dispatchex (' Word.Application ')

At this point you will notice that no MS Word appears on the screen and that he has already done it behind his back. You can see if there is a name "Winword" through the work manager. EXE "of

Process. What's good about not producing a picture is that you can take your work in the background. If you want to see if there really is a successful activation of Ms Word, please set the visible sex.

MSWord. Visible = 1 # 1 indicates that the screen is to be displayed, and if 0 does not display the canvas. You can change this nature at any time.

In addition to not showing the screen, you might want to not show some warning messages. At this time, please set the DisplayAlerts sex:

Detailed View:

Http://wenku.baidu.com/view/c248150d6c85ec3a87c2c57f.html

More:

http://wenku.baidu.com/view/d65d36728e9951e79b892737

http://my.oschina.net/duxuefeng/blog/64137

I encountered error Com_error: ( -2147221164, ' \xc3\xbb\xd3\xd0\xd7\xa2\xb2\xe1\xc0\xe0 ', none, none), online for a long time did not find a solution, said is not found word process. Finally suddenly think of their own computer is not office, but WPS, there seems to be a difference.

And searched for the next Python call WPS, similar to Word, except that the process name is different:

Import win32com.client<br/>
o = Win32com.client.Dispatch ("Wps.application") (under Office is Word.Application)
O.visible=true
doc = O.documents.add ()
Doc. content.text= "Hello world!"

Perform. You will see a WPS window pops up with a new document in the text "Hello World".

If you perform line-by-row in an interactive environment, you will see the effect of each step (sometimes you need to activate the WPS window).

Reference: http://blog.csdn.net/ccat/article/details/5784933

If you operate Excel, search the Internet, an article says:

I found on the Internet, there are at least two methods, the first is the direct operation of the Excle COM library, of course, Python's own lib inside has been packaged for us to achieve, direct use on it, Win32com.client, this method can even call the excle process directly. The usage is very simple, the article on the net also voluminous, do not explain in detail, give a small example, hehe. This can only be run under windows and requires Ms Excel to be installed.

WPS under the Excel.Application called Et.application

#-*-Coding:utf-8-*-from win32com.client Import constants, Dispatchxlsapp = Dispatch ("Excel.Application") (WPs called Et.app lication) # By assigning a value of Visible to TRUE or false can control whether to call up exclexlsapp.visible = # Xlsbook = XlsApp.Workbooks.Open ("c:// Magictong.xls ") # Xlssht = Xlsbook.worksheets (" Sheet1 ") (first Sheet1). Xlsbook = XlsApp.Workbooks.Add () (WPS has already created the Sheet1,sheet2,sheet3 for you by default, creating another sheet here,)Xlssht = XLSBOOK.SHEETS.ADD () Xlssht.cells (2, 3). Value = "Tecent QQ" Xlssht.cells (2, 3). Font.Color = 0xff0000xlssht.name = "GCD go to Bell" Xlsbook.saveas ("C://magictong.xls") xlsapp.quit () print "__end"

Reference: http://blog.csdn.net/magictong/article/details/4966822

A class that operates on word:

Http://wenku.baidu.com/view/ef59d6d728ea81c758f578da.html

A class that operates in Excel: ("Python programming on Win32" is also described in detail)

#!/usr/bin/python#coding=utf-8import win32comfrom win32com.client Import dispatch,constantsclass easyexcel:def __ini T__ (Self,filename=none): Self.xlapp=win32com.client.dispatch ("ET. Application ") Self.xlapp.visible=true if Filename:self.filename=filename self.xlbook= Self.xlApp.Workbooks.Open (filename) else:self.xlbook=self.xlapp.workbooks.add () Self.filenam                E= ' def Save (Self,newfilename=none): If Newfilename:self.filename=newfilename            Self.xlBook.SaveAs (NewFileName) else:self.xlBook.Save () def close (self): #self. Xlbook.close (savechanges=0) #self. xlApp.Quit () #del self.xlapp print "Close" D            EF Getcell (Self,sheet,row,col): "Get value of one cell" sht=self.xlbook.worksheets (sheet) Return sht. Cells (Row,col). Value def Setcell (self,sheet,row,col,value):            ' Set value of one cell ' sht=self.xlbook.worksheets (sheet) sht. Cells (Row,col).            Value=value def getRange (self,sheet,row1,col1,row2,col2): ' Return a 2d array (Ie:tuple of tuples ' Sht=self.xlbook.worksheets (sheet) return sht. Range (sht. Cells (row1,col1), sht. Cells (Row2,col2)). Value def addpicture (self,sheet,picturename,left,top,width,height): ' Insert a picture in sheet ' s Ht=self.xlbook.worksheets (sheet) sht. Shapes.addpicture (picturename,1,1,left,top,width,height) def cpsheet (self,before): ' Copy sheet ' s Hts=self.xlbook.worksheets shts (1). Copy (None,shts (1)) def addsheet (self,sheetname= ' MySheet1 '): Self.sht1=self.xlbook.sheets.add () s        Elf.sht1.name=sheetname if __name__== "__main__": pic= ' d:/image/sunyanzi/yanzi760_580.jpg ' Xls=easyexcel () Sht1=xls.addsheet () xls.addpicture ("MySheet1", pic,20,20,700,700) Xls.setcell ("MySheet1", "1_1_value") Xls.setcell ("MySheet1", "1_2_value"); Xls.setcell ("MySheet1", 1,3, "1_3_value") Xls.setcell ("MySheet1", 2,1, "2_1_value") Xls.setcell ("MySheet1", 2, 2,                                             "2_2_value") Xls.save ("D:/mysheet.xls") Xls.close ()

A more detailed class: Http://blog.sina.com.cn/s/blog_3fcd4ff90100n2mb.html

Setting-a-cells-fill-rgb-color-with-pywin32-in-excel

Http://stackoverflow.com/questions/11444207/setting-a-cells-fill-rgb-color-with-pywin32-in-excel

Interior.Color expects a hex value If you want to specify in RGB form below code can is used.

DefRgb_to_hex(Rgb):strvalue= '%02x%02x%02x ' % RGB Ivalue = Int (strvalue,16 return Ivaluexl. Activesheet. Cellsrow, Columninterior.= Rgb_to_hex ((255 ,255,0) 
 
   The font color can be set directly:
Sht. Rows (1). Font.color= 0xff0000

Very good Documentation:
http://pythonexcels.com/python-excel-mini-cookbook/

Ranges and offsets

This script illustrates different techniques for addressing cells by using the Cells() and Range() operators. Individual cells can addressed using Cells(row,column) , where is the row number, was the row column number column , both start from 1. Groups of cells can be addressed using Range() , where the argument in the parenthesis can is a single cell denoted by its Textual name (eg "A2" ), a group noted by a textual name with a colon (eg "A3:B4" ) or a group denoted with a Cells() identi Fiers (eg ws.Cells(1,1),ws.Cells(2,2) ). The Offset method provides a to address a cell based on a reference to another cell.

# # Using Ranges and Offsets#import win32com.client as Win32excel = Win32.gencache.EnsureDispatch (' Excel.Application ') WB = Excel. Workbooks.Add () WS = WB. Worksheets ("Sheet1") ws. Cells (a). Value = "Cell A1" ws. Cells (a). Offset (2,4). Value = "Cell D2" ws. Range ("A2"). Value = "Cell A2" ws. Range ("A3:b4"). Value = "A3:b4" ws. Range ("A6:b7,a9:b10"). Value = "A6:B7,A9:B10" WB. SaveAs (' ranges_and_offsets.xlsx ') Excel. Application.Quit ()

Autofill Cell Contents

This script uses Excel's autofill capability to examine data in cells A1 and A2, then autofill the remaining column of cel LS through A10.

# # Autofill Cell Contents#import win32com.client as Win32excel = Win32.gencache.EnsureDispatch (' excel.application ') WB = Excel. Workbooks.Add () WS = WB. Worksheets ("Sheet1") ws. Range ("A1"). Value = 1ws. Range ("A2"). Value = 2ws. Range ("A1:a2"). AutoFill (ws. Range ("a1:a10"), Win32.constants.xlFillDefault) WB. SaveAs (' autofill_cells.xlsx ') Excel. Application.Quit ()

Cell Color

This script illustrates adding a interior color to the cell using Interior.ColorIndex . Column A, rows 1 through is filled with A number and assigned ColorIndex .

# # ADD An interior color to cells#import win32com.client as Win32excel = Win32.gencache.EnsureDispatch (' Excel.Application ') WB = Excel. Workbooks.Add () WS = WB. Worksheets ("Sheet1") for I in Range (1,21):    ws. Cells (i,1). Value = i    ws. Cells (i,1). Interior.ColorIndex = IWB. SaveAs (' cell_color.xlsx ') Excel. Application.Quit ()

Column formatting

This script creates columns of data, one narrow and one wide, then formats the column width with the property ColumnWidth . Y OU can also use the Columns.AutoFit() function to AutoFit all columns in the spreadsheet.

# # Set column Widths#import win32com.client as Win32excel = Win32.gencache.EnsureDispatch (' excel.application ') WB = Excel . Workbooks.Add () WS = WB. Worksheets ("Sheet1") ws. Range ("a1:a10"). Value = "A" ws. Range ("B1:b10"). Value = "This was a very long line of text" WS. Columns (1). ColumnWidth = 1ws. Range ("B:b"). ColumnWidth = 27# Alternately, you can autofit all columns in the worksheet# ws. Columns.AutoFit () WB. SaveAs (' column_widths.xlsx ') Excel. Application.Quit ()

Copying Data from Worksheet to Worksheet

This script uses the method to copy data from one location to all and worksheets in the FillAcrossSheets() workbook. Specifically, the data in the range a1:j10 are copied from Sheet1 to sheets Sheet2 and Sheet3.

# # Copy data and formatting from a range of one worksheet# to all other worksheets in a workbook#import win32com.client as Win32excel = Win32.gencache.EnsureDispatch (' excel.application ') WB = Excel. Workbooks.Add () WS = WB. Worksheets ("Sheet1") ws. Range ("A1:j10"). Formula = "=row () *column ()" WB. Worksheets.fillacrosssheets (WB). Worksheets ("Sheet1"). Range ("A1:j10")) WB. SaveAs (' copy_worksheet_to_worksheet.xlsx ') Excel. Application.Quit ()

Format Worksheet Cells

This script creates with columns of data, then formats the font type and font size used in the worksheet. Five different fonts and sizes are used, the numbers is formatted using a monetary format.

# # Format cell font name and size, format numbers in monetary format#import win32com.client as Win32excel = Win32.gencache . Ensuredispatch (' Excel.Application ') WB = Excel. Workbooks.Add () WS = WB. Worksheets ("Sheet1") for I,font in enumerate (["Arial", "Courier New", "Garamond", "Georgia", "Verdana"]):    ws. Range (ws. Cells (i+1,1), WS. Cells (i+1,2)). Value = [font,i+i]    ws. Range (ws. Cells (i+1,1), WS. Cells (i+1,2)). Font.Name = Font    ws. Range (ws. Cells (i+1,1), WS. Cells (i+1,2)). Font.Size = 12+iws. Range ("A1:a5"). HorizontalAlignment = Win32.constants.xlRightws.Range ("B1:b5"). NumberFormat = "$###,# #0. xx" ws. Columns.AutoFit () WB. SaveAs (' format_cells.xlsx ') Excel. Application.Quit ()

Setting Row Height

This script illustrates row height. Similar to column height, row height can is set with the RowHeight method. You can also with AutoFit() automatically adjust the row height based on cell contents

# # Set Row heights and align text within the Cell#import win32com.client as Win32excel = Win32.gencache.EnsureDispatch (' Ex Cel. Application ') WB = Excel. Workbooks.Add () WS = WB. Worksheets ("Sheet1") ws. Range ("A1:a2"). Value = "1 line" WS. Range ("B1:b2"). Value = "Two\nlines" ws. Range ("C1:c2"). Value = "Three\nlines\nhere" ws. Range ("D1:d2"). Value = "This\nis\nfour\nlines" ws. Rows (1). RowHeight = 60ws. Range ("2:2"). RowHeight = 120ws. Rows (1). VerticalAlignment = Win32.constants.xlCenterws.Range ("2:2"). VerticalAlignment = win32.constants.xlcenter# Alternately, you can autofit all rows in the worksheet# ws. Rows.autofit () WB. SaveAs (' row_height.xlsx ') Excel. Application.Quit ()

Prerequisites

Python (refer to http://www.python.org)

Win32 Python Module (refer to Http://sourceforge.net/projects/pywin32)

Microsoft Excel (refer to Http://office.microsoft.com/excel)

Source Files and Scripts

Source for the program and data text file is available athttp://github.com/pythonexcels/examples

Transferred from: http://pythonexcels.com/python-excel-mini-cookbook/

If you set a row of the background color, then the border will not be visible, it is very uncomfortable, to set the format of the cell border display format, there are line style, and so on, how to use the program

Setting it up? Looking for n long, finally found,

Sht. Rows (1). Borders.linestyle=1

Python Operations Office