python的pylightxl模块
pylightxl 比较小，没有依赖，对python2、3都支持
- https://pylightxl.readthedocs.io/en/latest/index.html pylightxl 的文档，可以自己去查看
- 支持的文件后缀：.xlsx, .xlsm 和.csv
* 不支持.xls文件（Microsoft Excel 2003 和更早的文件）
* 不支持单元格数据以外的任何内容（不支持图形、图像、宏、格式）
* 不支持超过 536,870,912 个单元格的工作表单元格数据（32 位列表限制），如果需要更多数据存储，请使用 64 位。
-
安装
pip install pylightxl -
读取excel文件
import pylightxl as xl
strExcelFilePath = "C:\\Users\\Administrator\\Desktop\\LogTestFile\\1111.xlsx"
db = xl.readxl(strExcelFilePath)
#当然了有更多的方式去读取
import pylightxl as xl
strExcelFilePath = "C:\\Users\\Administrator\\Desktop\\LogTestFile\\1111.xlsx"
db = xl.readxl(strExcelFilePath , ws="Sheet1")#这里的ws是excel里面的工作簿
当excel文件里面的内容为：
db.ws(ws='Sheet1').address(address='A1')
db.ws(ws='Sheet1').index(row=1,col=1)
#这两句代表的意思都是一样的，是去读取A1位置上的数据是多少
#如果A1这个位置上没有数据，就会返回一个空，返回的值：**''**
执行结果：
db.ws(ws='Sheet1').address(address='C1', output='f')#如果单元格有公式就输出公式
db.ws(ws='Sheet1').address(address='C2', output='c')#如果有注释就输出注释
db.ws(ws='Sheet1').set_emptycell(val=0)#将所有没有数据的单元格都写上0
db.ws(ws='Sheet1').range(address='A1:C2')#指定一个范围进行输出，返回值是一个列表
db.ws(ws='Sheet1').range(address='A1:B1', output='f')#指定一个范围查看公式，没有公式的单元格会返回一个"="，这个返回也是列表哦
db.ws(ws='Sheet1').row(row=1)#获取整行的数据，返回列表
db.ws(ws='Sheet1').col(col=1)#获取整列的数据，返回列表
- 更新数据，也可以叫写入数据
db.ws(ws='Sheet1').update_range(address='A1:B1', val=10)#更新数据，也可以叫写入数据
db.ws(ws='Sheet1').update_address(address='A1', val=100)#也可以这样写入，也可以叫写入数据
db.ws(ws='Sheet1').update_index(row=1, col=1, val=10)#还可以使用这样的方式写入数据
db.ws(ws='Sheet1').update_address(address='C1', val='=B1+100')#更新单元格公式，写入公式
db.ws(ws='Sheet1').update_index(row=1, col=3, val='=B1+100')#更新单元格公式，写入公式
- 指定一个区域，只对这个区域做操作
db.add_nr(name='name1', ws='Sheet1', address='A1:B2') #name 可以随便写，ws是工作簿的名字，address是范围，这里不能用index（row 、col）
db.nr(name='name1')#获取定义这个范围里面的内容
db.nr_loc(name='name1')#查看定义这个范围是那些
db.update_nr(name='name1', val=10)#更新一个范围内的值
db.nr_names #查看定义了多少个范围
db.remove_nr(name='name1')#删除一个范围
- 保存，有没有发现上面的语句更新了数据，但是我们打开excel文件还是之前的东西，那是因为没有进行写入
xl.writexl(db=db, fn='你要保存的excel文件位置，可以是之前的excel文件，也可以是一个新的excel文件')#写入数据
import pylightxl as xl
# take this list for example as our input data that we want to put in column A
mydata = [10,20,30,40]
# create a blank db
db = xl.Database()
# add a blank worksheet to the db
db.add_ws(ws="Sheet1")
# loop to add our data to the worksheet
for row_id, data in enumerate(mydata, start=1):
db.ws(ws="Sheet1").update_index(row=row_id, col=1, val=data)
# write out the db
xl.writexl(db=db, fn=strExcelFilePath2)
API
3.1. readxl
pylightxl.pylightxl.readxl(fn, ws=None)
Reads an xlsx or xlsm file and returns a pylightxl database
Parameters:
fn (Union[str, pathlib.Path]) – Excel file path, also supports Pathlib.Path object, as well as file-like object from with/open
ws (Union[str,List[str]], optional) – sheetnames to read into the database, if not specified - all sheets are read entry support single ws name (ex: ws=’sh1’) or multi (ex: ws=[‘sh1’, ‘sh2’]), defaults to None
Returns:
pylightxl Database
Return type:
Database
3.2. writexl
pylightxl.pylightxl.writexl(db, fn)
Writes an excel file from pylightxl.Database
Parameters:
db (Database) – database contains sheetnames, and their data
fn (Union[str, pathlib.path]) – file output path
3.3.1. Database Class
classpylightxl.pylightxl.Database
add_nr(name, ws, address)
Add a NamedRange to the database. There can not be duplicate name or addresses. A named range that overlaps either the name or address will overwrite the database’s existing NamedRange
Parameters:
name (str) – NamedRange name
ws (str) – worksheet name
address (str) – range of address (single cell ex: “A1”, range ex: “A1:B4”)
add_ws(ws, data=None)
Logs worksheet name and its data in the database
Parameters:
ws (str) – worksheet name
data (dict, optional) – dictionary of worksheet cell values (ex: {‘A1’: {‘v’:10,’f’:’’,’s’:’’, ‘c’: ‘’}, ‘A2’: {‘v’:20,’f’:’’,’s’:’’, ‘c’: ‘’}}), defaults to None
nr(name, formula=False, output='v')
Returns the contents of a name range in a nest list form [row][col]
Parameters:
name (str) – NamedRange name
formula (bool, optional) – flag to return the formula of this cell, defaults to False
output (str, optional) – output request “v” for value, “f” for formula, “c” for comment, defaults to ‘v’
Returns:
nest list form [row][col]
Return type:
List[list]
nr_loc(name)
Returns the worksheet and address loction of a named range
Parameters: name (str) – NamedRange name
Returns: [worksheet, address]
Return type: List[str]
nr_names
Returns the dictionary of named ranges ex: {unique_name: unique_address, …}
Returns: {unique_name: unique_address, …}
Return type: Dict[str, str]
remove_nr(name)
Removes a Named Range from the database
Parameters: name (str) – NamedRange name
remove_ws(ws)
Removes a worksheet and its data from the database
Parameters: ws (str) – worksheet name
rename_ws(old, new)
Renames an existing worksheet. Caution, renaming to an existing new worksheet name will overwrite
Parameters:
old (str) – old name
new (str) – new name
set_emptycell(val)
Custom definition for how pylightxl returns an empty cell
Parameters: val (Union[str,int,float]) – (default=’’) empty cell value
update_nr(name, val)
Updates a NamedRange with a single value. Raises UserWarning if name not in workbook.
Parameters:
name (str) – NamedRange name
val (Union[int,float,str]) – cell value; equations are string and must being with “=”
ws(ws)
Indexes worksheets within the database
Parameters: ws (str) – worksheet name
Returns: pylightxl.Database.Worksheet class object
Return type: Worksheet
ws_names
Returns a list of database stored worksheet names
Returns: list of worksheet names
Return type: List[str]
3.3.2. Worksheet Class
classpylightxl.pylightxl.Worksheet(data=None)
address(address, formula=False, output='v')
Takes an excel address and returns the worksheet stored value
Parameters:
address (str) – Excel address (ex: “A1”)
formula (bool, optional) – flag to return the formula of this cell, defaults to False
output (str, optional) – output request “v” for value, “f” for formula, “c” for comment, defaults to ‘v’
Returns:
cell value
Return type:
Union[int, float, str, bool]
col(col, formula=False, output='v')
Takes a col index input and returns a list of cell data
Parameters:
col (int) – col index (start at 1 that corresponds to column “A”)
formula (bool, optional) – flag to return the formula of this cell, defaults to False
output (str, optional) – output request “v” for value, “f” for formula, “c” for comment, defaults to ‘v’
Returns:
list of cell data
Return type:
List[Union[int, float, str, bool]]
cols
Returns a list of cols that can be iterated through
Returns: list of cols-lists (ex: [[11,21],[12,22],[13,23]] for 2 rows with 3 columns of data
Return type: Iterable[List[Union[int, float, str, bool]]]
index(row, col, formula=False, output='v')
Takes an excel row and col starting at index 1 and returns the worksheet stored value
Parameters:
row (int) – row index (starting at 1)
col (int) – col index (start at 1 that corresponds to column “A”)
formula (bool, optional) – flag to return the formula of this cell, defaults to False
output (str, optional) – output request “v” for value, “f” for formula, “c” for comment, defaults to ‘v’
Returns:
cell value
Return type:
Union[int, float, str, bool]
keycol(key, keyindex=1)
Takes a column key value (value of any cell within keyindex row) and returns the entire column, no match returns an empty list
Parameters:
key (Union[str,int,float,bool]) – any cell value within keyindex row (type sensitive)
keyindex (int, optional) – option keyrow override. Must be >0 and smaller than worksheet size, defaults to 1
Returns:
list of the entire matched key column data (only first match is returned)
Return type:
List[Union[str,int,float,bool]]
keyrow(key, keyindex=1)
Takes a row key value (value of any cell within keyindex col) and returns the entire row, no match returns an empty list
Parameters:
key (Union[str,int,float,bool]) – any cell value within keyindex col (type sensitive)
keyindex (int, optional) – option keyrow override. Must be >0 and smaller than worksheet size, defaults to 1
Returns:
list of the entire matched key row data (only first match is returned)
Return type:
List[Union[str,int,float,bool]]
range(address, formula=False, output='v')
Takes a range (ex: “A1:A2”) and returns a nested list [row][col]
Parameters:
address (str) – cell range (ex: “A1:A2”, or “A1”)
formula (bool, optional) – returns the values if false, or formulas if true of cells, defaults to False
output (str, optional) – output request “v” for value, “f” for formula, “c” for comment, defaults to ‘v’
Returns:
nested list [row][col] regardless if range is a single cell or a range
Return type:
_type_
row(row, formula=False, output='v')
Takes a row index input and returns a list of cell data
Parameters:
row (int) – row index (starting at 1)
formula (bool, optional) – flag to return the formula of this cell, defaults to False
output (str, optional) – output request “v” for value, “f” for formula, “c” for comment, defaults to ‘v’
Returns:
list of cell data
Return type:
List[Union[int, float, str, bool]]
rows
Returns a list of rows that can be iterated through
Returns: list of rows-lists (ex: [[11,12,13],[21,22,23]] for 2 rows with 3 columns of data
Return type: Iterable[List[Union[int, float, str, bool]]]
set_emptycell(val)
Custom definition for how pylightxl returns an empty cell
Parameters: val (Union[int, float, str]) – (default=’’) empty cell value
size
Returns the size of the worksheet (row/col)
Returns: list of [maxrow, maxcol]
Return type: List[int]
ssd(keyrows='KEYROWS', keycols='KEYCOLS')
Runs through the worksheet and looks for “KEYROWS” and “KEYCOLS” flags in each cell to identify the start of a semi-structured data. A data table is read until an empty header is found by row or column. The search supports multiple tables.
Parameters:
keyrows (str, optional) – a flag to indicate the start of keyrow’s cells below are read until an empty cell is reached, defaults to ‘KEYROWS’
keycols (str, optional) – a flag to indicate the start of keycol’s cells to the right are read until an empty cell is reached, defaults to ‘KEYCOLS’
Returns:
list of data dict in the form of [{‘keyrows’: [], ‘keycols’: [], ‘data’: [[], …]}, {…},]
Return type:
List[Dict[str,list]]
update_address(address, val)
Update worksheet data via address
Parameters:
address (str) – excel address (ex: “A1”)
val (Union[int, float, str, bool]) – cell value; equations are strings and must begin with “=”
update_index(row, col, val)
Update worksheet data via index
Parameters:
row (int) – row index
col (int) – column index
val (Union[int, float, str, bool]) – cell value; equations are strings and must begin with “=”
update_range(address, val)
Update worksheet data via address range with a single value
Parameters:
address (str) – excel address (ex: “A1:B3”)
val (Union[int, float, str, bool]) – cell value; equations are strings and must begin with “=”
3.3.3. Support Functions
pylightxl.pylightxl.utility_address2index(address)
Convert excel address to row/col index
Parameters: address (str) – Excel address (ex: “A1”)
Returns: list of [row, col]
Return type: List[int]
pylightxl.pylightxl.utility_index2address(row, col)
Converts index row/col to excel address
Parameters:
row (int) – row index (starting at 1)
col (int) – col index (start at 1 that corresponds to column “A”)
Returns:
str excel address
Return type:
str
pylightxl.pylightxl.utility_columnletter2num(text)
Takes excel column header string and returns the equivalent column count
Parameters: text (str) – excel column (ex: ‘AAA’ will return 703)
Returns: int of column count
Return type: int
pylightxl.pylightxl.utility_num2columnletters(num)
Takes a column number and converts it to the equivalent excel column letters
Parameters: num (int) – column number
Returns: excel column letters
Return type: str
