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

This article mainly introduces "how to realize Python office automation Excel". In daily operation, I believe many people have doubts about how to realize Python office automation Excel. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "how to achieve Python office automation Excel". Next, please follow the editor to study!

Preface

The package that Python handles Excel is openpyxl, and the file types that support operations are: .xlsx, .xlsm, .xltx, .xltm.

Pip install openpyxl

After the installation is complete, we should take a look at the components of Excel, as shown in the following figure:

Three objects in the Excel file

Workbook: workbook, is an excel file sheet: worksheet, a workbook has multiple worksheets, table name identification, such as "sheet1", "sheet2" and so on. Cell: cells, storing data objects

First, create and open excel files

1. Create a blank workbook

Create an empty workbook and rename the default worksheet

# Import the workbook in the library from openpyxl import Workbook # create the workbook wb = Workbook () # get the current sheet, activate sheet = wb.active # set the worksheet name, or do not set the worksheet default name Sheet sheet.title = "worksheet 1" print (sheet.title) # print worksheet name # Save table wb.save ("worksheet")

Successfully created as follows

2. Open existing files

From openpyxl import Workbook, load_workbook # load excel file wb = load_workbook ("new_excel.xlsx") # get the worksheet sheet = wb.active# print worksheet name print (sheet.title) # output "worksheet 1" II, data manipulation in worksheet

Writing data to excel is written in terms of coordinates, and each cell can be represented by "ordinate Abscissa", as follows:

Write data

Method 1: customize the location to write data

Write data at C3 and D3 as follows

# Import the workbook from openpyxl import Workbook in the library, load_workbook # load the file wb = load_workbook ("new_excel.xlsx") # get the worksheet of the file sheet = wb.active# print worksheet name print (sheet.title) # output worksheet 1 sheet ["C3"] = "girl" sheet ["D3"] = "beautiful" # Save wb.save ("new_excel.xlsx")

Method 2: automatically add data to each row

The data you want to write is automatically written from top to bottom, starting from the next row of the row with data, from left to right.

# Import the workbook from openpyxl import Workbook in the library, load_workbook # load the file wb = load_workbook ("new_excel.xlsx") # or the worksheet where the file is located sheet = wb.active# print worksheet name print (sheet.title) # output worksheet 1 sheet.append ([11Jing 22pint 33pr 44hel55]) sheet.append (['aa','bb','cc','dd','ee']) # Save wb.save ("new_excel.xlsx")

Read data

The table is as follows

# Import the workbook from openpyxl import Workbook in the library, load_workbook # load the file wb = load_workbook ("cma.xx.xlsx") # 1. Print all worksheets print (wb.sheetnames) # output ['cma.xx',' cma2.xx'] # 2. Select a worksheet to enter sheet = wb ["cma.xx"] # print (sheet) # output # 3. Get the value of cell A2 print (sheet ["A2"] .value) # output http://api.data.xxx.cn:8090 # 4. Get the values of multiple cells, and for cell in sheet ["A2:C2"]: for i in cell: print (i.value) # output specific cell values # 5 in the same row. For cell in sheet ["A2:B4"]: for i in cell: print (i.value) # outputs the specific cell value # 6 in different lines. Print all the data in the table for row in sheet: # iterate through each row for i in row: # iterate through every cell in each row print (i.valueend = ",") # print the cell data print () # 7 for each row. Get the data in the specified range cell for row in sheet.iter_rows (min_row=2, max_row=5, max_col=6): for i in row: print (i.valuetraining endgame collection,') print () # 8. Traversing by column, that is, reading data vertically for row in sheet.columns: # traversing for i in row from the first column of data: # getting every data in each column print (i.valueend = ",") print () deleting the data

1. Delete entire column of data

# Import the workbook from openpyxl import Workbook in the library, load_workbook # load the file wb = load_workbook ("cma.xx.xlsx") # Select a worksheet to enter sheet = wb ["cma.xx"] sheet.delete_cols (2) # specify delete the second column sheet.delete_cols (2 cma.xx.xlsx 3) # delete the second column of the lower three columns, including the second column wb.save ("cma.xx.xlsx") 3, csv file to xlsx

When we convert files, we need to pay attention to the encoding of the two files, and when using pandas to read csv files, we also need to specify the decoding method.

1. First of all, check the encoding mode of the ccv file to be converted. Open the csv file using notepad and view-status bar. You can see that there is an encoding method at the bottom of the file: ANSI

two。 The script is as follows

Import pandas as pd def csv_to_xlsx (): csv = pd.read_csv ('cma.xx.csv', encoding='ANSI') csv.to_excel (' 2.xlsxx, sheet_name='data', index=False) if _ _ name__ = ='_ main__': csv_to_xlsx () at this point, the study on "how to implement Python office automation Excel" is over, hoping to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.