Python Operations Excel table

Using Python to manipulate excel in the work is still quite common, because after all do not understand Excel is a user of large data management software

Note: This code runs in Python3 environment

First import two modules xlrd and XLWT,XLRD to read Excel table, XLWT is to write Excel table

At the command prompt, enter the command:

Pip Install Xlrd

Pip Install XLWT

Let's take a look at the use of these two modules

First, XLRD

1. First import the module, import xlrd

2. First write a table for the experiment, the contents of the table are as follows:

Import'1.xlsx'"UTF8"# Set encoding data = Xlrd.open_workbook (file_path)# Open file sheet_names = Data.sheet_names ()  # View the name of the file that contains the sheet

Get a workbook in three different ways

# table = data.sheets () [0] # table = data.sheet_by_name (' Sheet1 ') # Take the first workbook (three methods)

Get total number of rows and columns

# Fetch total number of rows cols_count = table.ncols# number of columns

Get first row and first column data

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

Get the value of a cell

Cell_data = row_data[0]  # take No. 0 row No. 0 column value   warrior cell_data_a1 = Table.cell (1, 1). Value  # take the value of 1th row 1th column    Li Bai

Loop through all data

 for inch Range (0,rows_count):      for inch Range (0,cols_count):         = Table.cell (row,col). Value        print(data1,end=")     Print('\ n')

This is the basic operation, the following extension point

About the time format:

Take a look at the time that column of data, get what

[' time ''2017.01.12', 40900.0]

This is not what we want, this time format is in Excel, with January 1, 1900 as the starting time, the obtained data in days

In fact, in this module, there is a property to display the type of cell data, CType, there are several types:

CType = 1 # type 0 empty,1 string, 2 number, 3 date, 4 Boolean, 5 error

We can look at what type of this is it printed

Print (Table.cell (1,6). CType) # 3 Print (Table.cell (2,6). CType) # 1 Print (Table.cell (3,6). CType) # 3

Turns out he can identify this is the time format, so we have to deal with it, let him show the format of the 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 will work.

 from Import Date,datetime Print (Date (*date_value[:3])) # 2017-09-15   Print (Date (*date_value[:3]). Strftime ('%y/%m/%d')) # 2017/09/15

So when we loop through and print the data, we can make a judgment, if the data CType is 3, do such a conversion

 forRowinchRange (0,rows_count): forColinchRange (0,cols_count):if(Table.cell (Row, col). CType = = 3):#If you read the data in the time format, the conversionData_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). ValuePrint(data1,end=' ')    Print('\ n')

2017/09/152017.01.12

In fact, XLRD can also implement simple modification data, but this is only a temporary modification, as if there is no use

# Simple Write data row = 1=#  type 0 empty,1 string, 2 number, 3 date, 4 Boolean, 5 error
   
    1212
    '
    
    # 
     extension (default is 0)
    table.put_cell_unragged (Row, col , CType, value, XF) Table.put_cell (row, col, CType, value, XF)
    # is 
    only a temporary modification, and can only modify existing data
   

And then read the data:

 forRowinchRange (0,rows_count): forColinchRange (0,cols_count):if(Table.cell (Row, col). CType = = 3):#If you read the data in the time format, the conversionData_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). ValuePrint(data1,end=' ')    Print('\ n')

The result is modified, but it's only temporary, and the original form has not been modified.

1212 Li Baiyi Chiaser cai Wenji luban 2017/09/152017.01.12

Read about merged cells

By default, when a merged cell is read, only the first position in the merged cell appears, and the other merge position is empty, and we can get the position of the merged cell in the same table.

With a new table, the contents are as follows

Workbook = Xlrd.open_workbook ('2.xlsx'=print(table2.merged _cells)#[(3, 5, 3, 4), (3, 5, 0, 1)]  read the position of the merged cell in the table

#merged_cells返回的这四个参数的含义是: (Row,row_range,col,col_range), where [Row,row_range]
The meaning of (3, 5, 3, 4) is: The 4th to 5th Row (3,5) of the 3rd to 4th column (3,4) is merged, (3, 5, 0, 1) meaning: The 4th to 5th Row (3,5) of column No. 0 to 1th (0,1) is merged.

Second, XLWT

1. Importing module Import XLWT

2. Create workbooks, worksheets

MyWorkbook = XLWT. Workbook ()# Create a workbook MySheet = Myworkbook.add_sheet ('a Test sheet')#  Create a worksheet

3. Create data format, write data

MyStyle = XLWT.EASYXF ('font:name times New Roman, Color-index red, bold on', num_format_str='#,# #0. XX')#Data FormatMysheet.write (3, 0,'ABCD', MyStyle)#You can use this format when writing data, or you can notMysheet.write (2, 0, 1)#write A3, value equals 1Mysheet.write (2, 1, 1)#write B3, value equals 1Mysheet.write (2, 2, XLWT. Formula ("a3+b3"))#write C3, value equals 2

4. Save

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

Python Operations Excel table