Detailed explanation of python's xlwings library reading and writing excel operation summary
- 2021-09-11 20:40:32
- OfStack
1. Summary (click Show or Hide Summary Content)
1 sentence summary:
xlwings is a third-party library of Excel in Python. It supports. xls and. xlsx reading and writing. The operation is very simple and the function is also very powerful
1. Logic in xlwings: Applying-
>
Workbook-
>
Worksheet-
>
What code does the scope correspond to?
Application: 1 application (1 xlwings program): app = xw. App (visible=True, add_book=False)
Workbook (book): excel file (excel program): wb = app. books. add ()
Worksheet (sheet): sheet: sht = wb. sheets ['sheet1']
Range: Row and column: sht. range ('a6'). expand ('table'). value = [['a', 'b'], ['d', 'e']]
import xlwings as xw
# Write to Excel Zhongqu
# add_book That is, whether to increase or not excel Adj. book
# visible=True Indicates whether the operation procedure can be displayed
app = xw.App(visible=True, add_book=False)
# Workbook
wb = app.books.add()
# Page sheet1
sht = wb.sheets['sheet1']
# Single value insertion
# sht.range('A1').value = ' Product name '
# sht.range('B1').value = ' Numbering '
# sht.range('C1').value = ' Price '
# sht.range('A2').value = ' Don't tell you '
# sht.range('B2').value = 'n110110'
# sht.range('C2').value = '688.26'
# sht.range('A3').value = ' Don't tell you 1'
# sht.range('B3').value = 'n1101101'
# sht.range('C3').value = '688.261'
# Insert 1 Row
# sht.range('a1').value = [1,2,3,4]
# Equivalent to
# sht.range('a1:d4').value = [1,2,3,4]
# Insert 1 Column
# sht.range('a2').options(transpose=True).value = [5,6,7,8]
# Insert columns and columns at the same time
# sht.range('a6').expand('table').value = [['a','b','c'],['d','e','f'],['g','h','i']]
# Generate files in the current directory
wb.save('demo1.xlsx')
wb.close()
app.quit()
# import os
# path1=os.path.abspath('.') # Represents the absolute path of the folder in which you are currently located
# print(path1)
# path2=os.path.abspath('..') # Indicates the folder in which you are currently located 1 Absolute path of level folder
# print(path2)
# For the path problem, just switch to the specified directory
2. How does the xlwings library insert values into excel?
a, insert a single value: sht. range ('A1'). value = 'Product Name'
b, insert line 1: sht. range ('a1'). value = [1, 2, 3, 4] or sht. range ('a 1: d4'). value = [1, 2, 3, 4]
c, insert 1 column: sht. range ('a2'). options (transpose=True). value = [5, 6, 7, 8]
d, insert column and column: sht. range ('a6'). expand ('table'). value = [['a', 'b', 'c'], ['d', 'e', 'f'], ['g', 'h', 'i']]
3. Basic operation steps of reading and writing excel in xlwings library?
Is to follow the xlwings library logic (applying- > Workbook- > Worksheet- > Range) operation can be opened, remember to close it, read it, write it
app = xw.App(visible=True, add_book=False)
# Workbook
wb = app.books.add()
# Page sheet1
sht = wb.sheets['sheet1']
# Insert columns and columns at the same time
sht.range('a6').expand('table').value = [['a','b','c'],['d','e','f'],['g','h','i']]
# Generate files in the current directory
wb.save('demo1.xlsx')
wb.close()
app.quit()
4. How does the xlwings library read the data in excel?
print(sht.range('a1:c7').value) You can read rows, columns, or both
# Read column and column: read A1:C7 (Just fill in the range of cells) , Get 1 A 2 Dimension list
print(sht.range('a1:c7').value)
# Read rows: of 1 Dimension list
# print(sht.range('a1:c1').value)
# Read column: of 1 Dimension list
# print(sht.range('a1:a7').value)
import xlwings as xw
app = xw.App(visible=True, add_book=False)
# Display alerts ()
app.display_alerts = True
# Screen update (in application)
app.screen_updating = True
# Open a file
wb = app.books.open('demo1.xlsx')
sht = wb.sheets['sheet1']
# Read cells through traversal
# column_name = ['A','B',"C"]
# data_list = [] # Save data to list Zhongqu
# for i in range(3): # Traverse a line
# row_list = []
# for j in range(3): # Traversing columns
# str1 = column_name[j]+str(i+1)
# a = sht.range(str1).value
# row_list.append(a)
# print(a)
# pass
# data_list.append(row_list)
# pass
# print(data_list)
# Read column and column: read A1:C7 (Just fill in the range of cells) , Get 1 A 2 Dimension list
print(sht.range('a1:c7').value)
# Read rows: of 1 Dimension list
# print(sht.range('a1:c1').value)
# Read column: of 1 Dimension list
# print(sht.range('a1:a7').value)
wb.save()
wb.close()
app.quit()2. Summary of reading and writing excel in python xlwings library
1. Write
"""
xlwings介绍
xlwings 是 Python 中操作Excel 的1个第3方库，
支持.xls读写，.xlsx读写
操作非常简单，功能也很强大
1、安装库
pip3 install xlwings
2、引入库
import xlwings as xw
3、
应用->工作簿->工作表->范围
应用：1个应用（1个xlwings程序）：
app = xw.App(visible=True, add_book=False)
工作簿（book）：
excel文件（excel程序）：wb = app.books.add()
工作表（sheet）：
sheet：sht = wb.sheets['sheet1']
范围：行列：
sht.range('a6').expand('table').value = [['a','b'],['d','e']]
xlwings.App(visible=True,add_book=False)
其中参数visible（表示处理过程是否可视，也就是处理Excel的过程会不会显示出来），add_book（是否打开新的Excel程序，也就是是不是打开1个新的excel窗口）
"""
import xlwings as xw
# 写到Excel中去
# add_book也就是是否增加excel 的book
# visible=True 表示操作过程是否可显示
app = xw.App(visible=True, add_book=False)
# 工作簿
wb = app.books.add()
# 页sheet1
sht = wb.sheets['sheet1']
# 单个值插入
# sht.range('A1').value = '产品名称'
# sht.range('B1').value = '编号'
# sht.range('C1').value = '价格'
# sht.range('A2').value = '不告诉你'
# sht.range('B2').value = 'n110110'
# sht.range('C2').value = '688.26'
# sht.range('A3').value = '不告诉你1'
# sht.range('B3').value = 'n1101101'
# sht.range('C3').value = '688.261'
# 插入1行
# sht.range('a1').value = [1,2,3,4]
# 等同于
# sht.range('a1:d4').value = [1,2,3,4]
# 插入1列
# sht.range('a2').options(transpose=True).value = [5,6,7,8]
# 同时插入行列
# sht.range('a6').expand('table').value = [['a','b','c'],['d','e','f'],['g','h','i']]
# 在当前目录下生成文件
wb.save('demo1.xlsx')
wb.close()
app.quit()
# import os
# path1=os.path.abspath('.') # 表示当前所处的文件夹的绝对路径
# print(path1)
# path2=os.path.abspath('..') # 表示当前所处的文件夹上1级文件夹的绝对路径
# print(path2)
# 关于路径问题，切换到指定目录即可
2. Read
import xlwings as xw
app = xw.App(visible=True, add_book=False)
# Display alerts ()
app.display_alerts = True
# Screen update (in application)
app.screen_updating = True
# Open a file
wb = app.books.open('demo1.xlsx')
sht = wb.sheets['sheet1']
# Read cells through traversal
# column_name = ['A','B',"C"]
# data_list = [] # Save data to list Zhongqu
# for i in range(3): # Traverse a line
# row_list = []
# for j in range(3): # Traversing columns
# str1 = column_name[j]+str(i+1)
# a = sht.range(str1).value
# row_list.append(a)
# print(a)
# pass
# data_list.append(row_list)
# pass
# print(data_list)
# Read column and column: read A1:C7 (Just fill in the range of cells) , Get 1 A 2 Dimension list
print(sht.range('a1:c7').value)
# Read rows: of 1 Dimension list
# print(sht.range('a1:c1').value)
# Read column: of 1 Dimension list
# print(sht.range('a1:a7').value)
wb.save()
wb.close()
app.quit()