Use python to operate XLS files (ET in WPS)

Once te needed a ** information list. I exported a TXT file from Python and threw it to them. As a result, they were very unhappy, because they want to output hundreds of pieces of data to the xls file as a list, and the workload is too large, they asked me to export the data to the xls file and then give it to them. It is too difficult for me to shirk the programming implementation, today, I read the python Method for operating excle and found that it is quite simple. Well, it's hard for te ......

I found out on the internet that there are at least two methods. The first method is to directly operate the excle com library. Of course, the python lib has encapsulated the implementation for us, you can use it directly, win32com. client, this method can even directly call the excle process. The usage is very simple. The online articles are full of resources and I will not explain them in detail. Here is a small example. This type can only be run in windows, and MS Excel must be installed.

#-*-Coding: UTF-8-*-<br/> from win32com. client import constants, dispatch </P> <p> xlsapp = Dispatch ("Excel. application ") <br/> # You can assign a value of true or false to determine whether to call up excle. <br/> xlsapp. visible = 1 </P> <p> # xlsbook = xlsapp. workbooks. open ("C: // magictong.xls") <br/> # xlssht = xlsbook. worksheets ("sheet1") </P> <p> xlsbook = xlsapp. workbooks. add () <br/> xlssht = xlsbook. sheets. add () <br/> xlssht. cells (2, 3 ). value = "Tecent QQ" <br/> xlssht. cells (2, 3 ). font. color = 0xff0000 </P> <p> xlssht. name = "GCD go to bell" <br/> xlsbook. saveas ("C: // magictong.xls") <br/> xlsapp. quit () </P> <p> Print "_ end" <br/>

 

The second method is to use the pyexcelerator open-source library. Is a zip package. The decompressed file is as follows:

The examples directory provides many small examples, which are very comprehensive. To understand these examples, you can fully understand the basic methods of using pyexcelerator. You don't need to worry about other directories, there are some conversion tools in the tools directory, from the xls file to the TXT file, HTML, you can also look at it if you are interested. Next, let's talk about the installation of idea. Actually, the readme.txt file has already been clearly stated. Let's take a closer look at this file. Hey, there is a setup in this directory. py file, which is used for library installation. The installation method is Python setup. PY install, directly write a BAT file under Win32 to write this sentence, put it in and setup. py is in the same level directory, and it will be over when it runs directly. There is CMD in it. the bat file is added to it.

After the installation is complete, let's start using it. First, let's take a look at the parsing of the xls file, just a few simple words:

From pyexcelerator import * <br/> sheets = parse_xls ("C:/a.xls") <br/> Print sheets

The source file of XLS is as follows:

Output is not simple:

[(U 'sheet1', {(0, 1): 1.0, (7, 3): 10.890000000000001, (0, 0): U 'tonglei', (1, 1): u'qq', (2, 2): U'/u7acb/u91cc/u4e09/u77f3 ', (0, 3): 5.0, (0, 2 ): 2.0}), (u 'sheet2', {}), (u 'sheet3', {})]

Take a closer look at this data. The outermost layer is a list, which contains three tuples, corresponding to three sheets. each tuple has two pieces of data, and the first is the sheet name, for example, "sheet1", the second item is a dictionary. The key of each item in the dictionary is a tuple with two items in the column and column of the xls file as the element, and value is the value corresponding to the column and column, however, the types in this file may not be as rich as in XLS. What can I do if I get the data ...... I will not talk about it.

Let's see how to write the xls file:

#-*-Coding: cp936-*-<br/> from pyexcelerator import * </P> <p> # create a worksheet <br/> W = Workbook () <br/> # Add a sheet and set the name <br/> Ws = W. add_sheet (U "she文sheshe") </P> <p> # Set the style <br/> font0 = font () <br/> font0.name = 'times new Roman '<br/> font0.struck _ out = true <br/> font0.bold = true </P> <p> style0 = xfstyle () <br/> style0.font = font0 </P> <p> ws. write (1, 1, U "magictong", style0) <br/> ws. write (1, 2, 45) </P> <p> # I want to write a value <br/> style1 = xfstyle () <br/> style1.num _ format_str = "0" <br/> ws. write (2, 4, 12, style1) <br/> ws. write (2, 5, 4, style1) </P> <p> # Save <br/> W. save ("C: // mini.xls ")

The output xls file is as follows:

 

There are many output formats. You can refer to an example:

#! /Usr/bin/ENV python <br/> #-*-coding: Windows-1251-*-<br/> # copyright (c) 2005 kiseliov Roman <br/>__ rev_id _ = "$ ID: num_formats.py, v 1.1 07:24:11 rvk exp $ "" </P> <p> from pyexcelerator import * </P> <p> W = Workbook () <br/> Ws = W. add_sheet ('Hey, dude') </P> <p> fmts = [<br/> 'Total', <br/> '0 ', <br/> '0. 00', <br/> '#, # 0', <br/>' #, #0.00 ', <br/>' "$ "#, #0 _); ("$" #,## ', <br/>' "$" #,# 0 _); [Red] ("$" #, # ', <br/>' "$" #, #0.00 _); ("$ "#, ##', <br/> '"$" #,## 0.00 _); [Red] ("$ "#,##', <br/> '0% ', <br/> '0. 00% ', <br/> '0. 00e + 00', <br/> '#? /? ', <Br/> '#?? /?? ', <Br/>'m/D/yy', <br/> 'd-MMM-YY', <br/> 'd-mmm ', <br/> 'mmm-yy', <br/> 'H: Mm AM/PM ', <br/> 'H: mm: ss am/PM ', <br/> 'H: mm', <br/> 'H: mm: ss', <br/>'m/D/yy H: mm ', <br/> '_ (#, #0 _); (#, #0)', <br/> '_ (#, #0 _); [Red] (#, #0) ', <br/>' _ (#, #0.00 _); (#, #0.00 )', <br/> '_ (#,## 0.00 _); [Red] (#,## 0.00)', <br/> '_ ("$ "*#, #0 _); _ ("$" * (#, #0); _ ("$ "*"-"_);_(@_)', <br/> '_ (* #, #0 _); _ (* (#, #0 );_(*"-"_); _ (@ _) ', <B R/> '_ ("$" * #, #0.00 _); _ ("$" * (#, #0.00 ); _ ("$ "*"-"?? _); _ (@ _) ', <Br/>' _ (* #, #0.00 _); _ (* (#, #0.00 ); _(*"-"?? _); _ (@ _) ', <Br/> 'Mm: ss', <br/>' [H]: mm: ss ', <br/> 'Mm: ss.0 ', <br/>' # 0.0e + 0 ', <br/> '@' <br/>] </P> <p> I = 0 <br/> for FMT in fmts: <br/> ws. write (I, 0, FMT) </P> <p> style = xfstyle () <br/> style. num_format_str = FMT </P> <p> ws. write (I, 4,-1278.9078, style) </P> <p> I + = 1 </P> <p> inclusave('num_formats.xls ') <br/>

You can run it to see what formats can be output, but I am looking for it and I don't know how to output a standard value.

 

[End]