# 示例：openpyxl讀取 → pandas處理 → openpyxl美化輸出
from openpyxl import load_workbook
import pandas as pd

# 1. openpyxl增量讀取大文件
wb = load_workbook("large_file.xlsx", read_only=True)
data = [row for row in wb.active.values][1:]  # 跳過標(biāo)題
wb.close()

# 2. pandas處理數(shù)據(jù)
df = pd.DataFrame(data, columns=["Name", "Age"])
df_filtered = df[df["Age"] > 30].groupby("Name").mean()

# 3. openpyxl輸出帶樣式
with pd.ExcelWriter("output.xlsx", engine="openpyxl") as writer:
    df_filtered.to_excel(writer, sheet_name="Summary")
    workbook = writer.book
    worksheet = writer.sheets["Summary"]
    # 設(shè)置標(biāo)題樣式
    for cell in worksheet[1]:
        cell.font = Font(bold=True, color="FFFFFF")
        cell.fill = PatternFill(start_color="366092", fill_type="solid")

pip install pandas

import pandas as pd  
  
# 讀取Excel文件  
df = pd.read_excel('example.xlsx', sheet_name='Sheet1', header=0, index_col=None, usecols=None, dtype=None)  
  
# 顯示前幾行數(shù)據(jù)  
print(df.head())  
  
# 如果文件沒有列標(biāo)題  
df_no_header = pd.read_excel('example_no_header.xlsx', header=None, names=['Column1', 'Column2', 'Column3'])  
print(df_no_header.head())  
  
# 讀取多個工作表  
xls = pd.ExcelFile('example.xlsx')  
df1 = pd.read_excel(xls, 'Sheet1')  
df2 = pd.read_excel(xls, 'Sheet2')  
# 或者  
dfs = pd.read_excel(xls, sheet_name=None)  # 返回一個字典，鍵為工作表名，值為DataFrame

# 假設(shè)我們只想讀取'A'列和'C'列的前兩行  
df = pd.read_excel('example.xlsx', usecols=['A', 'C']).head(2)

# 創(chuàng)建一個簡單的DataFrame  
df = pd.DataFrame({  
    'A': [1, 2, 3, 4],  
    'B': ['foo', 'bar', 'baz', 'qux'],  
    'C': [1.0, 2.1, 3.2, 4.3]  
})  
  
# 寫入Excel文件  
df.to_excel('output.xlsx', sheet_name='Sheet1', index=False, header=True)  
  
# 如果要寫入多個工作表  
with pd.ExcelWriter('output_multiple_sheets.xlsx') as writer:  
    df.to_excel(writer, sheet_name='Sheet1', index=False)  
    df.to_excel(writer, sheet_name='Sheet2', index=False, startrow=10)  # 從第11行開始寫入

# 使用xlsxwriter引擎寫入帶有樣式的Excel  
with pd.ExcelWriter('styled_output.xlsx', engine='xlsxwriter') as writer:  
    df.to_excel(writer, sheet_name='Sheet1', index=False)  
    workbook = writer.book  
    worksheet = writer.sheets['Sheet1']  
      
    # 創(chuàng)建一個格式對象  
    format = workbook.add_format({'bold': True, 'font_color': 'red'})  
      
    # 應(yīng)用格式到特定單元格  
    worksheet.write('A1', 'Styled Cell', format)

pip install openpyxl

from openpyxl import load_workbook  
  
wb = load_workbook(filename='example.xlsx')

# 通過名稱  
ws = wb['Sheet1']  
  
# 或者通過索引（索引從0開始）  
ws = wb.worksheets[0]  
  
# 也可以使用 active 屬性獲取當(dāng)前活動的工作表  
ws = wb.active

# 讀取單元格的值  
cell_value = ws['A1'].value  
print(cell_value)  
  
# 遍歷行  
for row in ws.iter_rows(values_only=True):  
    print(row)  
  
# 遍歷列  
for col in ws.iter_cols(values_only=True):  
    for value in col:  
        print(value)

# 寫入數(shù)據(jù)  
ws['B2'] = 'Hello, openpyxl!'  
  
# 保存修改  
wb.save('modified_example.xlsx')

from openpyxl import load_workbook  
  
# 加載工作簿  
wb = load_workbook('example.xlsx')  
  
# 激活工作表  
ws = wb.active  
  
# 讀取單元格數(shù)據(jù)  
print(ws['A1'].value)  
  
# 修改單元格數(shù)據(jù)  
ws['B2'] = 'New Value'  
  
# 保存修改后的工作簿  
wb.save('modified_example.xlsx')

from openpyxl import Workbook  
from openpyxl.styles import Font, Color, Alignment, Border, Side  
  
# 創(chuàng)建一個新的工作簿和工作表  
wb = Workbook()  
ws = wb.active  
  
# 創(chuàng)建一個字體對象  
font = Font(name='Calibri', size=11, bold=True, italic=False, vertAlign=None,  
            underline='none', strike=False, color='FF0000')  
  
# 創(chuàng)建一個對齊對象  
alignment = Alignment(horizontal='general', vertical='bottom',  
                      text_rotation=0, wrap_text=False,  
                      shrink_to_fit=False, indent=0)  
  
# 創(chuàng)建一個邊框?qū)ο? 
thin = Side(border_style="thin", color="000000")  
border = Border(top=thin, left=thin, right=thin, bottom=thin)  
  
# 應(yīng)用樣式到單元格  
ws['A1'].font = font  
ws['A1'].alignment = alignment  
ws['A1'].border = border  
ws['A1'] = 'Styled Cell'  
  
# 保存工作簿  
wb.save('styled_workbook.xlsx')

pip install xlrd==1.2.0  # 最好選擇一個支持.xlsx的舊版本

import xlrd  
  
# 加載工作簿  
workbook = xlrd.open_workbook('example.xls')

# 通過索引訪問（索引從0開始）  
sheet = workbook.sheet_by_index(0)  
  
# 或者通過名稱訪問  
sheet = workbook.sheet_by_name('Sheet1')

# 通過行號和列號讀取  
cell_value = sheet.cell_value(0, 0)  # 讀取第一行第一列的數(shù)據(jù)  
print(cell_value)  
  
# 或者使用 cell 方法（返回單元格對象，然后可以調(diào)用 value 屬性）  
cell = sheet.cell(0, 0)  
print(cell.value)  
  
# 通過單元格名稱讀?。ㄐ枰惭bxlutils或類似庫來轉(zhuǎn)換A1表示法）  
# 注意：xlrd本身不直接支持A1表示法，這里僅作為說明  
# 通常，你會通過計(jì)算行號和列號來訪問單元格

import xlrd  
  
# 加載工作簿  
workbook = xlrd.open_workbook('example.xls')  
  
# 通過名稱訪問工作表  
sheet = workbook.sheet_by_name('Sheet1')  
  
# 讀取并打印第一行和第一列的數(shù)據(jù)  
print(sheet.cell_value(0, 0))  
  
# 遍歷所有行和列  
for row_idx in range(sheet.nrows):  
    for col_idx in range(sheet.ncols):  
        print(sheet.cell_value(row_idx, col_idx), end='\t')  
    print()  # 換行