Using Python to operate xlsx files in Excel,

Source: Internet
Author: User

Using Python to operate xlsx files in Excel,

Preface

Previously, xlrd/xlwt was used for reading and writing excel files. However, the two databases only process the xls Format better and the format ending with xlsx won't work. Because all of you are using the latest version of office, and the excel format is xlsx, it is not appropriate to continue to use xlrd/xlwt to process the xlsx file. Fortunately, the xlsx file is read and written, we can also use openpyxl for operations.

I am not familiar with excel, and I am not very familiar with it at ordinary times. Therefore, the processing of excel is very simple, but simple reading and writing. Here we demonstrate simple reading and writing operations, specific advanced functions, you can refer to the link address after the document.

I. Write an excel file as follows:

From openpyxl import Workbook from openpyxl. utils import get_column_letter # create a workbook object in the memory, and at least one worksheet wb = Workbook () # obtain the currently active worksheet. By default, the first worksheet ws = wb. active # Set the cell value. A1 is equal to 6 (the test shows that the row and column numbers of openpyxl are calculated from 1), and B1 is equal to 7 ws. cell (row = 1, column = 1 ). value = 6 ws. cell ("B1 "). value = 7 # Write 10 columns of data in 9 rows starting from row 2nd. The values are column numbers A, B, C, and D... for row in range (): for col in range (): ws. cell (row = row, column = col ). value = get_column_letter (col) # You Can Use append to insert a row of Data ws. append (["I", "you", "she"]) # Save wb. save (filename = "/Users/budong/Desktop/a.xlsx ")

Ii. Read the content of the newly written excel file as follows:

From openpyxl import load_workbook # open a workbook wb = load_workbook (filename = "/Users/budong/Desktop/a.xlsx") # obtain the active worksheet, the first worksheet # ws = wb by default. active # Of course, you can also use the following method # obtain the name of all tables (worksheet) sheets = wb. get_sheet_names () # Name of the first table sheet_first = sheets [0] # obtain a specific worksheet ws = wb. get_sheet_by_name (sheet_first) # obtain all rows and columns in the Table. Both of them are iteratable rows = ws. rows columns = ws. columns # iterate all rows for row in rows: line = [col. value for col in row] print line # Read the value print ws through coordinates. cell ('a1 '). value # A indicates the column, and 1 indicates the row print ws. cell (row = 1, column = 1 ). value

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.