系列文章目录
前言
一、Python读取excel的几种方式
1、类别
- 用xlrd和wlwt进行读写
- 用openpyxl进行读写
- 用pandas进行读写
二、解释说明
1、xlrd和wlwt
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
file_path = os.path.dirname(os.path.abspath(__file__))
base_path = os.path.join(file_path,'data.xlsx')
book = xlrd.open_workbook(path)
sheet1 = book.sheets()[0]
nrows = sheet1.nrows
ncols = sheet1.ncols
row3_values = sheet1.row_values(2) #第三行值
col3_values = sheet1.col3_values(2) #第三列值
cell_3_3 = sheet1.cell(2, 2).value #单元格值
workbook = xlwt.Workbook(encoding = 'utf-8')
worksheet = workbook.add_sheet('Worksheet')
worksheet.write(0, 0, label = '测试') #写入excel对应行、列、值
worksheet.col(0).width = 3333 #单元格宽度
tall_style = xlwt.easyxf('font:height 520')
worksheet.row(0).set_style(tall_style)
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2、openpyxl
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
file_path = os.path.dirname(os.path.abspath(__file__))
base_path = os.path.join(file_path,'data.xlsx')
workbook = openpyxl.load_workbook(base_path)
worksheet = workbook.get_sheet_by_name('Sheet1')
row3 = [item.value for item in list(worksheet.rows)[2]]
col3 = [item.value for item in list(worksheet.columns)[2]]
cell_2_3 = worksheet.cell(row = 2, column = 3).value
max_row = worksheet.max_row
workbook = openpyxl.Workbook()
sheet = workbook.active
sheet['A1'] = 'python'
sheet['A2'] = datetime.datetime.now().strftime("%Y-%m-%d")
sheet.row_dimensions[2].height = 40
sheet.column_dimensions['B'].width = 30
sheet['A1'].alignment = Alignment(horizontal = 'center', vertical = 'center')
sheet.merge_cell('A2:B2') #合并单元格
sheet.unmerge_cell('A2:B2') #拆分单元格
workbook.save('new.xlsx')
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
3、pandas
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
pandas支持xls、xlsx、xlsm、xlsb、odf、ods、odt文件扩展名，支持读取单个工作表或工作表列表选项
语法：
pd.read_excel(io, sheet_name = 0, header = 0, names = None, index_col = None, usecols = None,
squeeze = False, dtype = None, engine = None, converters = None, true_values = None,
false_values = None, skiprows = None, nrows = None, na_values = None, parse_dates = False,
date_parser = None, thousands = None, comment = None, skipfooter = 0, convert_float = True, **kwds)
file_path = os.path.dirname(os.path.abspath)
base_path = os.join(file_path,'data.xlsx')
df = pd.read_excel(base_path)
DataFrame.to_excel(excel_writer, sheet_name = 'Sheet1', na_rep = '', float_format = None,
columns = None, header = True, index = True, index_label = None, startrow = 0, startcol = 0,
engine = None, merge_cells = True, encoding = None, inf_rep = 'inf', verbose = True, freeze_panes = None)
from pandas import DataFrame
data = {'name':['python','java','C++'],'age':[2021,2022,2023],'rank':['1','2','3']}
df = DataFrame(data)
df.to_excel('file.xlsx')
总结
分享：
我们的生活是有感情的、有理性的。我认为我们不应当抑制感情和理性，而应当充分发挥其本性。最重要的是，不要毁损这种本性，可是在政治上和社会上，偏偏就要毁损这种本性。