Python excel and pythonexcel

Python excel and pythonexcel

Using Python to Operate Excel is quite common at work, because Excel is a huge data management software for users.

Note: This code runs in the Python3 environment.

First, import the two modules xlrd and xlwt. xlrd is used to read the Excel table. xlwt is used to write the Excel table.

Enter the following command in the command prompt line:

Pip install xlrd

Pip install xlwt

Next we will learn how to use these two modules.

I. xlrd

1. First import the module, import xlrd

2. First, write a table for experiment. The table content is as follows:

 

Import xlrdfile_path = '1.xlsx 'xlrd. book. encoding = "utf8" # Set the encoding data = xlrd. open_workbook (file_path) # open the file sheet_names = data. sheet_names () # view the name of the sheet contained in the file

You can obtain a workbook in three ways.

# Table = data. sheets () [0] # table = data. sheet_by_name ('sheet1') table = data. sheet_by_index (0) # obtain the first Workbook (three methods)

Obtain the total number of rows and total number of Columns

Rows_count = table. nrows # retrieve the total number of rows cols_count = table. ncols # retrieve the total number of Columns

Obtain the first row and the first column of data

Row_data = table. row_values (0) # obtain the first row of data (array) col_data = table. col_values (0) # obtain the first column of data (array)

Obtains the value of a cell.

Cell_data = row_data [0] # set the value of cell_data_A1 = table. cell (1, 1). value # set the value of column 0th in row 0th to Li Bai.

Read all data cyclically

for row in range(0,rows_count):    for col in range(0,cols_count):        data1 = table.cell(row,col).value        print(data1,end=' ')    print('\n')

This is a basic operation. The following is an extension.

Time Format:

Let's take a look at the data in the Time column.

['Time', 42993.0, '192. 100', 2017]

This is not what we want. The time format is the data obtained in Excel, starting from January 1, January 1, 1900, in days.

In fact, in this module, there is an attribute that can display the type of cell data, ctype, there are these types:

Ctype = 1 # Type 0 empty, 1 string, 2 number, 3 date, 4 boolean, 5 error

Let's see what type it prints.

print(table.cell(1,6).ctype)#3print(table.cell(2,6).ctype)#1print(table.cell(3,6).ctype)#3

It turns out that he can recognize the time format, so we have to handle it and let him display the formatting time.

You can use this command to convert

date_value = xlrd.xldate_as_tuple(table.cell_value(1,6),data.datemode)

print(date_value)#(2017, 9, 15, 0, 0, 0)

This format can be used.

from datetime import date,datetimeprint(date(*date_value[:3]))#2017-09-15  print(date(*date_value[:3]).strftime('%Y/%m/%d'))#2017/09/15

So when we read and print the data cyclically, we can make a judgment. If the ctype of the data is 3, we will make such a conversion.

For row in range (0, rows_count): for col in range (0, cols_count): if (table. cell (row, col ). ctype = 3): # If you read data in the time format, convert data_value = xlrd. xldate_as_tuple (table. cell_value (row, col), data. datemode) data1 = date (* data_value [: 3]). strftime ('% Y/% m/% D') else: data1 = table. cell (row, col ). value print (data1, end = '') print ('\ n ')

Warrior assassin mage tank auxiliary shooter time old husband Li Bai Ji Arthur Cai wenji Luban Jun /09/15 Yang Han Xin Xiao Qiao Niu magic Joe Di Renjie 2017.01.12 Dian Wei Zhao Yun Ji Zhong Wuyan Sun Bin Yu Ji 2011/12/23

 

 

In fact, xlrd can also implement simple data modification, but this is only temporary modification, it seems useless

# Simple write data row = 1col = 0 ctype = 1 # Type 0 empty, 1 string, 2 number, 3 date, 4 boolean, 5 errorvalue = '000000' xf = 0 # extended formatting (0 by default) table. put_cell_unragged (row, col, ctype, value, xf) table. put_cell (row, col, ctype, value, xf) # Only temporary modification, and only modification of existing data

Then read the data:

For row in range (0, rows_count): for col in range (0, cols_count): if (table. cell (row, col ). ctype = 3): # If you read data in the time format, convert data_value = xlrd. xldate_as_tuple (table. cell_value (row, col), data. datemode) data1 = date (* data_value [: 3]). strftime ('% Y/% m/% D') else: data1 = table. cell (row, col ). value print (data1, end = '') print ('\ n ')

The result is modified, but this is temporary. The original table is not modified.

Warrior assassin mage tank auxiliary shooter time 1212 Li Bai Ji Arthur Cai wenji Luban Jun /09/15 Yang Han Xin Xiao Qiao Niu magic Joe Di Renjie 2017.01.12 Dian Wei Zhao Yun Ji Zhong Wuyan Sun Bin Yu Ji

 

Reading merged Cells

By default, when reading merged cells, only values are available at the first position in the merged cells. Other merged positions are empty. We can obtain the position in the table where the merged cells are located.

Use a new table with the following content:

 

Workbook = xlrd.open_workbook('2.xlsx ') table2 = workbook. sheet_by_index (0) print (table2.merged _ cells) # [(3, 5, 3, 4), (3, 5, 0, 1)] Read the table where merged cells exist

# The meanings of the four parameters returned by merged_cells are: (row, row_range, col, col_range), where [row, row_range)
# The meaning of (3, 5, 3, 4) is: 3rd to 4 columns (4th), to 5 rows (), (3, 5, 0, 1) The meaning is: 0th to 5 rows (4th) of 1 column () are merged.

Ii. xlwt

1. import xlwt

2. Create a workbook and a worksheet

MyWorkbook = xlwt. Workbook () # create A Workbook mySheet = myWorkbook. add_sheet ('a Test Sheet ') # create A worksheet

3. Create a data format and write data

MyStyle = xlwt. easyxf ('font: name Times New Roman, color-index red, bold on ', num_format_str =' #,## 0.00 ') # data format mySheet. write (3, 0, 'abcd', myStyle) # You can use this format when writing data, or use mySheet. write (2, 0, 1) # write A3. The value is equal to 1mySheet. write (2, 1, 1) # write B3. The value is equal to 1mySheet. write (2, 2, xlwt. formula ("A3 + B3") # Write C3. The value is equal to 2.

4. Save

MyWorkbook.save('test.xls ') # Save