python学习笔记系列——Chapter 164: Python与Excel

pip install xlrd

import xlrd
book=xlrd.open_workbook('sample.xlsx')

print(book.nsheets)

print(book.sheet_names())

sheet=book.sheet_by_index(1)

cell = sheet.cell(row, col)  # 其中row是行号，col是列号
print(cell.value)  # 打印单元格内容

num_rows=sheet.nrows
num_col=sheet.ncols

sheets = book.sheet_names()
cur_sheet = book.sheet_by_name(sheets[0])

import xlsxwriter

# 创建一个新文件
workbook = xlsxwriter.Workbook('your_file.xlsx')

# 添加一些新格式供工作簿使用
percent_format = workbook.add_format({'num_format': '0%'})
percent_with_decimal = workbook.add_format({'num_format': '0.0%'})
bold_format = workbook.add_format({'bold': True})
red_font_format = workbook.add_format({'font_color': 'red'})

# 添加一个新工作表
worksheet = workbook.add_worksheet()

# 设置列宽和格式
columns_settings = [
    ('A:A', 30, None),          # 列A宽度为30，无特殊格式
    ('B:B', 20, percent_format) # 列B宽度为20，应用百分比格式
]

for col_range, width, fmt in columns_settings:
    worksheet.set_column(col_range, width, fmt)

# 移除第一行的格式（将高度设置为None）
worksheet.set_row(0, None)  # 第一行（索引从0开始）的高度设置为默认值，移除格式

# 关闭工作簿
workbook.close()

import os, sys
from openpyxl import Workbook
from datetime import datetime

dt = datetime.now()
list_values = [["01/01/2016", "05:00:00", 3], \
               ["01/02/2016", "06:00:00", 4], \
               ["01/03/2016", "07:00:00", 5], \
               ["01/04/2016", "08:00:00", 6], \
               ["01/05/2016", "09:00:00", 7]]

# 在Excel中创建一个工作簿：
wb = Workbook()
sheet = wb.active
sheet.title = 'data'

# 将标题打印到Excel工作簿中：
row = 1
sheet['A' + str(row)] = 'Date'
sheet['B' + str(row)] = 'Hour'
sheet['C' + str(row)] = 'Value'

# 用数据填充
for item in list_values:
    row += 1
    sheet['A' + str(row)] = item[0]
    sheet['B' + str(row)] = item[1]
    sheet['C' + str(row)] = item[2]

# 按日期保存文件：
filename = 'data_' + dt.strftime("%Y%m%d_%I%M%S") + '.xlsx'
wb.save(filename)

# 为用户打开文件：
os.chdir(sys.path[0])
os.system('start excel.exe "%s\\%s"' % (sys.path[0], filename, ))

import openpyxl as opx
# 要更改现有工作簿，我们通过引用其路径来定位它
workbook = opx.load_workbook(workbook_path)

workbook = opx.load_workbook(workbook_path, read_only=True)

first_sheet = workbook.worksheets[0]

sheet = workbook.get_sheet_by_name('Sheet Name')

for row in sheet.rows:
  print row[0].value

# 调用`Workbook()`函数在内存中创建一个新的工作簿
wb = opx.Workbook()

# 然后我们可以在`wb`中创建一个新的工作表
ws = wb.create_sheet('Sheet Name', 0)  # 0表示工作簿中工作表顺序的索引

ws.sheet_properties.tabColor = 'FFC0CB'

wb.save('filename.xlsx')

import xlsxwriter

# 示例数据
chart_data = [
    {'name': 'Lorem', 'value': 23},
    {'name': 'Ipsum', 'value': 48},
    {'name': 'Dolor', 'value': 15},
    {'name': 'Sit', 'value': 8},
    {'name': 'Amet', 'value': 32}
]

# Excel文件路径
xls_file = 'chart.xlsx'

# 工作簿
workbook = xlsxwriter.Workbook(xls_file)

# 向工作簿添加工作表
worksheet = workbook.add_worksheet()

row_ = 0
col_ = 0

# 写入标题
worksheet.write(row_, col_, 'NAME')
col_ += 1
worksheet.write(row_, col_, 'VALUE')
row_ += 1

# 写入示例数据
for item in chart_data:
    col_ = 0
    worksheet.write(row_, col_, item['name'])
    col_ += 1
    worksheet.write(row_, col_, item['value'])
    row_ += 1

# 创建饼图
pie_chart = workbook.add_chart({'type': 'pie'})

# 向饼图添加系列
pie_chart.add_series({
    'name': 'Series Name',
    'categories': '=Sheet1!$A$3:$A$%s' % row_,
    'values': '=Sheet1!$B$3:$B$%s' % row_,
    'marker': {'type': 'circle'}
})

# 插入饼图
worksheet.insert_chart('D2', pie_chart)

# 创建柱状图
column_chart = workbook.add_chart({'type': 'column'})

# 向柱状图添加系列
column_chart.add_series({
    'name': 'Series Name',
    'categories': '=Sheet1!$A$3:$A$%s' % row_,
    'values': '=Sheet1!$B$3:$B$%s' % row_,
    'marker': {'type': 'circle'}
})

# 插入柱状图
worksheet.insert_chart('D20', column_chart)

workbook.close()