import xlrd
 
# 讀取xls文件中的工作對象
wb = xlrd.open_workbook('示例文件/xxx物理學與信息技術(shù)學院.xls')
print(wb)
 
# 獲取所有的工作表名稱
sheet_names = wb.sheet_names()
# print(sheet_names)
 
# 選擇要讀取的具體工作表對象
sheet = wb.sheet_by_name(sheet_names[0])
# print(sheet)
 
# 查看工作表的行和列數(shù)
# print(sheet.nrows,sheet.ncols)  # 329 6
 
# 通過循環(huán)的方式獲取工作表中每行和每列的數(shù)據(jù)
for row in range(sheet.nrows):
    for col in range(sheet.ncols):
        # print(row,col)
        # 通過cell對象中的value屬性獲取具體單元格式中的數(shù)據(jù)
        value = sheet.cell(row, col).value
        # print(value)
'''
        # 將第一行以外的數(shù)據(jù)進行格式化處理
        if row > 0:
            # 將第一列的數(shù)據(jù)轉(zhuǎn)化為年月日的格式
            if col == 0:
                value = xlrd.xldate_as_tuple(value,0)
                # print(value)
                # :>2d 表示月份和日期保留兩位數(shù)
                value = f'{value[0]}年{value[1]:>02d}月{value[2]:>02d}日'
                # print(value)
            else:
                # 將除了第一列的數(shù)據(jù)之外的數(shù)據(jù)保留兩位小數(shù)
                value = f'{value:.2f}'
            print(value,end='\t')
        print()  # 實現(xiàn)換行的效果
'''

import xlrd

wb = xlrd.open_workbook('示例文件/xxx物理學與信息技術(shù)學院.xls')
print(wb)

sheet_names = wb.sheet_names()
# print(sheet_names)

sheet = wb.sheet_by_name(sheet_names[0])
# print(sheet)

# print(sheet.nrows, sheet.ncols)  # 329 6

for row in range(sheet.nrows):
    for col in range(sheet.ncols):
        value = sheet.cell(row, col).value
        # 將第一行以外的數(shù)據(jù)進行格式化處理
        if row > 0:
            # 將第一列的數(shù)據(jù)轉(zhuǎn)化為年月日的格式
            if col == 0:
                value = xlrd.xldate_as_tuple(value, 0)
                value = f'{value[0]}年{value[1]:>02d}月{value[2]:>02d}日'
            else:
                # 將除了第一列的數(shù)據(jù)之外的數(shù)據(jù)保留兩位小數(shù)
                value = f'{value:.2f}'
            print(value, end='\t')
        print()  # 實現(xiàn)換行的效果

import xlwt
import random
 
# 創(chuàng)建工作薄對象
wb = xlwt.Workbook()
 
# 創(chuàng)建工作表對象
sheet = wb.add_sheet('高一三班')
 
# 定義學生姓名
students = ['張三','李四','王五','里爾','飛天','閏土']
 
# 通過列表生成式生成 語數(shù)英成績
scores = [[random.randrange(50,151) for i in range(3)] for j in range(6)]
# print(scores)
 
# 創(chuàng)建表頭數(shù)據(jù)
titles = ['姓名','語文','數(shù)學','英語']
 
for index,title in enumerate(titles):
    # print(index,title)
    # 將數(shù)據(jù)添加到sheet工作表中
    # 第一個參數(shù)表示工作行 第二個參數(shù)表示列 第三個參數(shù)表示具體內(nèi)容
    sheet.write(0,index,title)
 
# 將學生的姓名成績依次寫入到文件中
for row in range(len(scores)):
    # print(row)
    sheet.write(row+1,0,students[row])  # 從第二行開始寫入相關(guān)的數(shù)據(jù)
    for col in range(len(scores[row])):
        sheet.write(row+1,col+1,scores[row][col])
# 將數(shù)據(jù)寫入到工作薄當中
wb.save('示例文件/考試成績.xls')

import xlwt
import random

wb = xlwt.Workbook()

sheet = wb.add_sheet('高一三班')

students = ['張三', '李四', '王五', '里爾', '飛天', '閏土']

scores = [[random.randrange(50, 151) for i in range(3)] for j in range(6)]

titles = ['姓名', '語文', '數(shù)學', '英語']

for index, title in enumerate(titles):
    sheet.write(0, index, title)

for row in range(len(scores)):
    sheet.write(row + 1, 0, students[row])  # 從第二行開始寫入相關(guān)的數(shù)據(jù)
    for col in range(len(scores[row])):
        sheet.write(row + 1, col + 1, scores[row][col])

wb.save('示例文件/考試成績.xls')

import openpyxl
 
# 加載工作薄
wb = openpyxl.load_workbook(r'示例文件\xx碩士信息.xlsx')
 
# 獲取工作表名稱
print(wb.sheetnames)  # ['Sheet1', 'Sheet2', 'Sheet3']
 
# 獲取要操作的工作表
sheet = wb.worksheets[0]
 
# 獲取工作表中單元格的范圍
# print(sheet.dimensions)  # A1:E64814
 
# 獲取行數(shù)和列數(shù)
# print(sheet.max_row,sheet.max_column)  # 64814 5
 
# 獲取指定單元格的值
# print(sheet.cell(3,3).value)
 
# 獲取多個單元格的值
# print(sheet['A2:C5'])
 
# 獲取單元格中所有的數(shù)據(jù)
for row in range(2,69):
    for col in "ABCDE":
        value = sheet[f"{col}{row}"].value
        print(value,end='\t')
    print()  # 換行

import openpyxl

wb = openpyxl.load_workbook(r'示例文件\清華碩士信息.xlsx')

print(wb.sheetnames)  # ['Sheet1', 'Sheet2', 'Sheet3']

sheet = wb.worksheets[0]

print(sheet.dimensions)  # A1:E64814

print(sheet.max_row,sheet.max_column)  # 64814 5

print(sheet.cell(3,3).value)

print(sheet['A2:C5'])

for row in range(2,69):
    for col in "ABCDE":
        value = sheet[f"{col}{row}"].value
        print(value,end='\t')
    print()  # 換行

import random
import openpyxl
 
# 創(chuàng)建工作薄
wb = openpyxl.Workbook()
 
# 添加工作表
sheet = wb.active
sheet.title = '測試成績'
 
# 寫入數(shù)據(jù) 定義標題
titles = ("姓名", "射箭", "射擊", "足球")
 
# 將標題寫入到文件中
for col_index, title in enumerate(titles):
    sheet.cell(1, col_index + 1, title)
 
# 定義學生姓名
students = ("echo", "時針", "小夢", "大盒", "小羽", "snowy", "辰辰", "痞老板", "Gscsed")
 
# 將姓名和成績寫入到文件中
for row_index, student in enumerate(students):
    sheet.cell(row_index + 2, 1, student)
    for col_index in range(2, 5):
        sheet.cell(row_index + 2, col_index, random.randrange(50, 101))
# 將數(shù)據(jù)保存到工作薄中
wb.save('示例文件\測試成績.xlsx')

import random
import openpyxl

wb = openpyxl.Workbook()

sheet = wb.active
sheet.title = '測試成績'

titles = ("姓名", "射箭", "射擊", "足球")

for col_index, title in enumerate(titles):
    sheet.cell(1, col_index + 1, title)

students = ("echo", "時針", "小夢", "大盒", "小羽", "snowy", "辰辰", "痞老板", "Gscsed")

for row_index, student in enumerate(students):
    sheet.cell(row_index + 2, 1, student)
    for col_index in range(2, 5):
        sheet.cell(row_index + 2, col_index, random.randrange(50, 101))

wb.save('示例文件\測試成績.xlsx')