Use Python to manipulate Excel's xlsx file introduction

Source: Internet
Author: User
Some time ago to do a project, you have to use Python to generate Excel files directly, and then as the requirements change, but also to the existing Excel file to read. So think about the record, this article is mainly for you to introduce Python operation Excel's xlsx file related information, the need for friends can refer to.

Objective

The previous processing of Excel read and write with XLRD/XLWT, but the two libraries have a disadvantage is that only the XLS format processing is better, the format of the end of xlsx is not. Because now everyone is using the latest version of the Office,excel format is xlsx, so at this time to continue with XLRD/XLWT processing is not appropriate, fortunately for the xlsx file read and write, we can also use OPENPYXL to operate.

I am not familiar with Excel, usually do not use, so the processing of Excel is very simple, just simple read and write, here is also a simple read and write operation, the specific advanced features, you can refer to the link address after the text.

One: Write an Excel file as follows

From OPENPYXL import Workbook from openpyxl.utils import get_column_letter  # Creates a Workbook object in memory and creates at least one worksheet WB = Workbook ()  #获取当前活跃的worksheet, the default is the first worksheet ws = Wb.active  #设置单元格的值, A1 equals 6 (the test indicates that the row and column numbers of OPENPYXL are calculated from 1), B1 equals 7 Ws.cell (Row=1, column=1). Value = 6 Ws.cell ("B1"). Value = 7  #从第2行开始, writes 9 rows and 10 columns of data with the corresponding column ordinals A, B, C, D ... for row in rang E (2,11): for  col in Range (1,11):   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"])  #保存 wb.save (filename= "/users/budong/desktop/a.xlsx")

Two: Read the Excel content just written below

 from OPENPYXL import load_workbook #打开一个workbook wb = Load_workbook (filename= "/ Users/budong/desktop/a.xlsx ") #获取当前活跃的worksheet, the default is the first worksheet #ws = wb.active #当然也可以使用下面的方法 #获取所有表格 (worksheet) name Sheets = wb.get_sheet_names () #第一个表格的名称 Sheet_first = sheets[0] #获取特定的worksheet ws = Wb.get_sheet_by_name (Sheet_first) #获 Fetch all rows and columns of the table, both of which are iterated rows = ws.rows columns = ws.columns #迭代所有的行 for row in rows:line = [Col.value for col in row] Print Line #通过坐标读取值 print Ws.cell (' A1 '). Value # A represents the column, and 1 represents the row print Ws.cell (row=1, column=1). Value 

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.