Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "what are the methods of Python operating Excel files?". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Data processing is a major application scenario of Python, and Excel is the most popular data processing software. Therefore, when using Python for data-related work, it is inevitable to deal with Excel.

If you just want to save the data as a form, you can handle it in CSV format, a comma-separated tabular data format, which is also supported by Excel. However, the standard Excel file (xls/xlsx) has a complex format, so it is not convenient to read and write directly like ordinary text files, so it needs to be implemented with the help of third-party libraries.

The commonly used libraries are the python-excel series:

Xlrd 、 xlwt 、 xlutils

Xlrd-read the Excel file

Xlwt-write to the Excel file

Xlutils-Utility for manipulating Excel files, such as copying, splitting, filtering, etc.

Although this is by far the most used Excel library, I still want to complain about why these three packages can't be put into one module. In addition, they have a drawback, which is that they can only handle xls files. If you want to use the new version of xlsx, consider openpyxl and xlsxwriter.

But today we will only talk about these three.

Installation

The method of installation is nothing special, but it has to be installed three times. You can download the installation package, download the code package, or through pip, and so on.

If you have installed the previously recommended anaconda, you already have xlrd and xlwt, but xlutils is not included in the installation package and needs to be installed separately when you use it.

Read

Combined with a simple piece of code:

Import xlrd # Open the xls file book = xlrd.open_workbook ("test.xls") print "form number:", book.nsheets print "form name:", book.sheet_names () # get the first form sh = book.sheet_by_index (0) print u "form% s% d row% d column% (sh.name, sh.nrows, sh.ncols) print" second row third column: ", sh.cell_value (1) 2) # iterate through all forms for s in book.sheets (): for rin range (s.nrows): # output the specified line print s.row (r)

Test file:

Output result:

Form Group.A: 7 rows, 3 columns, second row and third column: 15.0 [text:u'Rank', text:u'Team', text:u'Points'] [number:1.0, text:u'Brazil', number:15.0] [number:2.0, text:u'Russia', number:12.0].

Common methods:

Open_workbook opens a file

Sheet_by_index gets a form

Sheets gets all forms

Cell_value gets the data of the specified cell

Write

Or look at the code:

Import xlwt # create xls file object wb = xlwt.Workbook () # add a form sh = wb.add_sheet ('A Test Sheet') # add data by location sh.write (0, 1234.56) sh.write (1, 0, 8888) sh.write (2, 0, 'hello') sh.write (2, 1,' world') # Save file wb.save ('example.xls')

Generate the file:

Common methods:

Workbook creates a file object

Add a new form to add_sheet

Write writes data in the specified cell

Modify

Unfortunately, there is no way to modify the xls file directly. The usual practice is to read out the file, copy a piece of data, modify it, and then save it.

When copying, you need to use the methods in xlutils.

From xlrd import open_workbook from xlutils.copy import copy # Open the file rb = open_workbook ("example.xls") # copy wb = copy (rb) # Select form s = wb.get_sheet (0) # write data s.write (0,1, 'new data') # Save wb.save (' example.xls')

Modified file:

In particular, use sheet_by_index when selecting to read the form, and get_sheet when selecting to write to the form. Don't ask me why, I also want to know what the purpose of this setting is.

Time conversion

If there is time-formatted data in the form, after processing, you will find that the time data is wrong.

Output the contents of the cell:

[number:8888.0, xldate:42613.0]

Because here xldate has its own format definition. If you want to use the correct format, you must convert:

New_date = xlrd.xldate.xldate_as_datetime (date, book.datemode)

Date is the data of the corresponding cell, and book is the open file object.

In addition, when opening the file, add the parameter formatting_info=True to ensure that the time data remains intact when it is in copy.

If you write time data, you can create a time object for excel by this method:

Xlrd.xldate.xldate_from_datetime_tuple

Or specify the time format through xlwt.easyxf:

Style = xlwt.easyxf (num_format_str='D-MMM-YY') ws.write (1, 0, datetime.now (), style) "what are the methods of Python manipulating Excel files" is introduced here, thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.