Python3操作excel

作者: ropon 分类: Python 发布时间: 2020-01-17 20:29
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2020/1/17 19:55
# @Author  : Ropon
# @File    : excel_test.py

# 读excel
# pip3 install xlrd
# import xlrd

# excelName = r"./excel/1-12月.xls"
# book = xlrd.open_workbook(excelName)
# 获取所有sheet页的名字
# sheetNames = book.sheet_names()
# print(sheetNames)
# # 根据sheet索引获取表格sheet
# sheet = book.sheet_by_index(8)
# # 根据sheet名字获取表格sheet
# sheet2 = book.sheet_by_name("9月")
# 获取当前sheet的名称
# print(sheet.name)

# 获取指定行和列的数据 cell(行索引, 列索引)
# r1 = sheet.cell(0, 0).value
# r2 = sheet.cell(1, 0).value
# r3 = sheet.cell(1, 1).value
# r4 = sheet.cell(1, 2).value
# print(r1, r2, r3, r4)

# 获取单元格数据的类型
# ctype_text = {
#     XL_CELL_EMPTY: 'empty', 0
#     XL_CELL_TEXT: 'text', 1
#     XL_CELL_NUMBER: 'number', 2
#     XL_CELL_DATE: 'xldate', 3
#     XL_CELL_BOOLEAN: 'bool', 4
#     XL_CELL_ERROR: 'error', 5
#     XL_CELL_BLANK: 'blank', 6
# }
# cType = sheet.cell(100, 10).ctype
# print(cType)

# 获取excel有多少行和列
# rows = sheet.nrows
# cols = sheet.ncols
# print(rows, cols)

# 根据索引获取某一行所有数据
# rowData = sheet.row_values(0)
# print(rowData)

# 根据索引获取某一列所有数据
# colData = sheet.col_values(2)
# print(colData)

# 写excel
# pip3 install xlwt
import xlwt
# 只能写不能读

# 创建样式字体红色并加粗
style = "font:colour_index red, bold on;"
styleBoldRed = xlwt.easyxf(style)

# 新建一个excel
book = xlwt.Workbook()
# 添加sheet页
sheet = book.add_sheet("技术部")
# 将数据写入指定行和列
sheet.write(0, 0, "one", styleBoldRed)
sheet.write(0, 1, "two")
sheet.write(1, 1, "three")
sheet.write(1, 2, "four")
# 保存
book.save("writeTest.xls")

# 修改excel
# pip3 install xlutils3
# import xlrd
# from xlutils3.copy import copy
#
# excelName = "./excel/1-12月.xls"
# book = xlrd.open_workbook(excelName)
# writeBook = copy(book)
#
# sheet = book.sheet_by_name("9月")
# r1 = sheet.cell(2, 2).value
# print(r1)
# data = r1 + "测试写入"
# # 查看类的成员
# # print(dir(writeBook))
#
# # 根据sheet索引获取表格sheet
# writeSheet = writeBook.get_sheet(8)
# writeSheet.write(2, 16, data)
# writeBook.save("1-12月_ok.xls")

# openpyxl 读excel 不支持xls
# import openpyxl

# excelName = r"./excel/1-12月1.xlsx"
# book = openpyxl.load_workbook(excelName)
# 获取所有sheet页的名字
# sheetNames = book.get_sheet_names()
# print(sheetNames)
# 根据sheet索引获取表格sheet
# sheet = book.worksheets[8]
# 根据sheet名字获取表格sheet
# sheet2 = book.get_sheet_by_name("9月")
# 获取当前sheet的名称
# print(sheet.title)

# 获取excel有多少行和列
# rows = sheet.max_row
# cols = sheet.max_column
# print(rows, cols)


# 获取每一行的内容,这是一个生成器,有每一行的数据 每一行数据有一个元素类型包裹
# sheet.rows

# 获取每一列的内容,这是一个生成器,有每一列的数据 每一列数据有一个元素类型包裹
# sheet.columns

# 根据索引获取第一行所有数据
# for cell in list(sheet.rows)[0]:
#     print(cell.value)

# 根据索引获取某一列所有数据
# for cell in list(sheet.columns)[1]:
#     print(cell.value)

# 获取指定行和列的数据 cell(行索引, 列索引) 索引从1开始
# r1 = sheet.cell(1, 1).value
# r2 = sheet.cell(2, 1).value
# r3 = sheet.cell(2, 2).value
# r4 = sheet.cell(2, 3).value
# print(r1, r2, r3, r4)

# 获取第一行第一列(A)单元格数据
# print(sheet["A1"].value)

# openpyxl 写excel 不支持xls
# import openpyxl
#
# # 新建一个excel
# book = openpyxl.Workbook()
# # 添加sheet页
# sheet = book.create_sheet("技术部")
# # 获取当前活跃页 默认第一工作页
# # sheet = book.active
#
# # 将数据写入指定行和列 索引从1开始
# sheet.cell(1, 1, "one")
# sheet.cell(1, 2, "two")
# sheet.cell(2, 2, "three")
# sheet.cell(2, 3, "four")
# # 保存
# book.save("writeTest2.xlsx")

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!