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

This article is to share with you about the common methods of Python reading and writing EXCEL files, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article. Don't say much, follow the editor to have a look.

There are many ways for python to read and write excel, and different modules have slightly different ways of reading and writing. Here I mainly introduce a few commonly used ways.

Read and write excel with xlrd and xlwt

Read and write excel with openpyxl

Read and write excel with pandas

| 2 | 0 data preparation |

To facilitate the demonstration, I have created a new data.xls and data.xlsx file here. The content of the first worksheet sheet1 area "A1:E5" is as follows, which is used to test the code for reading and writing excel:

3 | 0xlrd and xlwt

Xlrd is a library for reading data and formatting information in .xls format from Excel files

Xlwt is a library for writing data and formatting information to older Excel files (for example: .xls).

| 3 | 1 example |

Pip install xlrd pip install xlwt

Let's start reading the contents of the file.

Import xlrd import os file_path = os.path.dirname (os.path.abspath (_ file__)) base_path = os.path.join (file_path, 'data.xlsx') book = xlrd.open_workbook (base_path) sheet1 = book.sheets () [0] nrows = sheet1.nrows print (' Table Total rows', nrows) ncols = sheet1.ncols print ('Table Total columns', ncols) row3_values = sheet1.row_values (2) print ('3rd Row value' Row3_values) col3_values = sheet1.col_values (2) print ('values of column 3', col3_values) cell_3_3 = sheet1.cell (2,2). Value print ('values of cells in row 3, column 3:', cell_3_3)

Next let's write, there are so many operations that can be done by writing, I only list the commonly used operations here.

Import xlwt import datetime # create a workbook setting encoding workbook = xlwt.Workbook (encoding='utf-8') # create a worksheet worksheet = workbook.add_sheet ('Worksheet') # write excel parameter corresponding row, column, value worksheet.write (0,0, label=' test') # set cell width worksheet.col (0). Width = 3333 # set cell height tall_style = xlwt.easyxf ('font:height 520 ') worksheet.row (0). Set_style (tall_style) # set alignment alignment = xlwt.Alignment () # Create Alignment # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED alignment.horz = xlwt.Alignment.HORZ_CENTER # May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED VERT_DISTRIBUTED alignment.vert = xlwt.Alignment.VERT_CENTER style = xlwt.XFStyle () # Create Style style.alignment = alignment # Add Alignment to Style worksheet.write (2,0, 'centered', style) # write data with color background pattern = xlwt.Pattern () # Create the Pattern # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12 pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 5 # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow, almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on... Style = xlwt.XFStyle () # Create the Pattern style.pattern = pattern # Add Pattern to Style worksheet.write (0,1, 'color', style) # write date style = xlwt.XFStyle () # Other options: D-MMM-YY, D-MMM, MMM-YY, h:mm, h:mm:ss, h:mm, h:mm:ss, M/D/YY h:mm, mm:ss, [h]: mm:ss Mm:ss.0 style.num_format_str ='M Damp YY 'worksheet.write (0,2, datetime.datetime.now (), style) # write the formula worksheet.write (0,3,5) # Outputs 5 worksheet.write (0,4,2) # Outputs 2 # Should output "10" (A1 [5] * A2 [2]) worksheet.write (1,3) Xlwt.Formula ('D1icing E1') # Should output "7" (A1 [5] + A2 [2]) worksheet.write (1,4, xlwt.Formula (' SUM (D1PowerE1)') # write hyperlink worksheet.write (1,0, xlwt.Formula ('HYPERLINK ("http://www.baidu.com";)") "Baidu") # Save workbook.save ('Excel_test.xls')

It is important to note that it is best to execute from the command line under the current path, otherwise the file cannot be generated.

4 | 0openpyxl

Openpyxl is a Python library for reading / writing to Excel 2010 xlsx/xlsm/xltx/xltm files.

Installation package

Pip install openpyx

When the installation is complete, you can start reading data.

Import openpyxl import os file_path = os.path.dirname (os.path.abspath (_ file__)) base_path = os.path.join (file_path, 'data.xlsx') workbook = openpyxl.load_workbook (base_path) worksheet = workbook.get_sheet_by_name (' Sheet1') row3= [item.value for item in list (worksheet.rows) [2]] print ('third line value', row3) col3= [item.value for item in list (worksheet.columns) [2]] print ('third line value' Col3) cell_2_3=worksheet.cell (row=2,column=3) .value print ('row 2, column 3 values', cell_2_3) max_row=worksheet.max_row print ('maximum row', max_row)

Now let's start writing data.

Import zipfile # create file handle file = zipfile.ZipFile ("test .zip",'r') # extract the contents of the compressed file, note that the password must be in bytes format, and path indicates which file.extractall to extract (path='.', pwd='123'.encode ('utf-8'))

5 | 0pandas

Pandas supports xls, xlsx, xlsm, xlsb, odf, ods, and odt file extensions to be read from the local file system or URL. The option to read a single worksheet or list of worksheets is supported.

The first thing is still to install the package.

Pip install pandas

Syntax:

Pd.read_excel (io, sheet_name=0, header=0, names=None, index_col=None, usecols=None, squeeze=False,dtype=None, engine=None, converters=None, true_values=None, false_values=None, skiprows=None, nrows=None, na_values=None, parse_dates=False, date_parser=None, thousands=None, comment=None, skipfooter=0, convert_float=True, * * kwds)

Storage path of io,Excel

Sheet_name, the name of the worksheet to read

Header, which line is used as the column name

Names, customize the final column name

Index_col, the column used as the index

Usecols, which columns need to be read

Squeeze, when the data contains only one column

Converters, enforcing column data types

Skiprows, skip specific lines

Nrows, the number of rows to be read

Skipfooter, skip the last n lines

Import pandas as pd import os file_path = os.path.dirname (os.path.abspath (_ _ file__)) base_path = os.path.join (file_path, 'data.xlsx') df = pd.read_excel (base_path) print (df)

Write data

Syntax:

DataFrame.to_excel (excel_writer, sheet_name='Sheet1', na_rep='', float_format=None, columns=None, header=True, index=True, index_label=None, startrow=0, startcol=0, engine=None, merge_cells=True, encoding=None, inf_rep='inf', verbose=True, freeze_panes=None)

Parameter description:

Excel_writer: file path or existing ExcelWriter

Sheet_name: the name of the worksheet that will contain the data file

Na_rep: missing data representation

Float_format: a string that formats floating-point numbers. For example, the format float_format = "% .2f" is 0.1234 to 0.12.

Columns: column

Header: write the column name. Given a list of strings, it is assumed to be an alias for the column name.

Index: write row name (index)

Index_label: the column label of the index column, if necessary. If not specified, and the header and index are true, the index name is used. If DataFrame uses multiple indexes, you should give a sequence.

Startrow: the cell row in the upper left corner dumps the data frame.

Startcol: the upper-left cell column dumps the data frame.

Engine: write the engine "openpyxl" or "xlsxwriter" to use. You can also set it with the options io.excel.xlsx.writer,io.excel.xls.writer and io.excel.xlsm.writer.

Merge_cells: writes multiple indexes and hierarchical rows to the merged cell.

Encoding: encodes the generated excel file. Only xlwt is necessary, and other writers themselves support unicode.

Inf_rep: stands for infinity.

Verbose: displays more information in the error log.

Freeze_panes: specify the bottommost row and rightmost column to freeze

From pandas import DataFrame data = {'name': [' Zhang San','Li Si', 'Wang Wu'], 'age': [11, 12, 13],' sex': ['male', 'female', 'male']} df = DataFrame (data) df.to_excel ('file.xlsx')

The above are the common methods for Python to read and write EXCEL files, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.