python3对excel读写openpyxl

读取：
'''
sheet1里面的值按照行取出来,每行组成一个列表
[[1,2,6],[3,4,5],[7,8,9]]

'''
import openpyxl
from openpyxl import load_workbook
def excel_rows_value(file_name):
    wb = openpyxl.load_workbook(file_name)   #打开excel
    ws = wb.active   #打开当前活跃的sheet

    rows = []
    for row in ws.rows:
        row1 = []
        for cell in row:
            row1.append(cell.value)
        rows.append(row1)
    return rows

print(excel_rows_value('test_case.xlsx'))

'''
文件中的值，以行为单位取出，以键值对方式存储
[{"name":"xgy","age":31,"sex":"男"},{"name":"zf","age":28,"sex":"女"}]

'''
#方法一：
import openpyxl
from openpyxl import load_workbook

def excel_row_value_dice(file_name):
    wb = openpyxl.load_workbook(file_name)
    ws = wb.active
    row_key = []
    row_value = []

    #获取表头内容
    for row in ws.iter_rows('A1:C1'):
        for cell in row:
            row_key.append(cell.value)
    #print(row_key)
    num = ws.max_column
    #print(num)
    #获取值
    for row in ws.iter_rows('A2:C'+str(num)):
        row_value_list = []
        for cell in row:
            row_value_list.append(cell.value)
        row_value.append(row_value_list)
    #print(row_value)

    result = []

    for i in range(len(row_value)):
        row_dict = {}
        for j in range(len(row_key)):
            row_dict[row_key[j]] = row_value[i][j]
        result.append(row_dict)
    #print(result)
    return result

print(excel_row_value_dice('test_case.xlsx'))


#方法二：
import openpyxl
from openpyxl import load_workbook

def excel_row_value_dice(file_name):
    wb = openpyxl.load_workbook(file_name)
    ws = wb.active

    #取出每行的值，以list方式存放
    rows_list = []
    for row in ws.rows:
        row_list = []
        for cell in row:
            row_list.append(cell.value)
        rows_list.append(row_list)
    #print(rows_list)

    #结果转换成键值对的形式存放
    result = []
    for i in range(len(rows_list)-1):
        row_dict = {}
        for j in range(len(rows_list[0])):
            row_dict[rows_list[0][j]] = rows_list[i+1][j]
        result.append(row_dict)
    #print(reslut)
    return result


case = excel_row_value_dice('test_case.xlsx')
for i in case:
    print(i)

excel的写：

创建sheet

from openpyxl import Workbook

wb = Workbook()  #创建文件对象

#ws = wb.active   #打开sheet

ws1 = wb.create_sheet(title='第一个sheet',index=0)    #新建一个指定名的sheet，默认放到最后面,可指定具体位置
ws2 = wb.create_sheet()
ws3 = wb.create_sheet(title='最前面的sheet',index=0)
ws4 = wb.create_sheet('1')
ws5 = wb.create_sheet('2')
ws6 = wb.create_sheet('3')
ws7 = wb.create_sheet(title='11111',index=1)
#ws8.title = u'暑期专题'
ws1.sheet_properties.tabColor = "1072BA"   #设定sheet的标签的背景颜色

#print(wb['最前面的sheet'])
#print(wb.sheetnames)

#获取全部sheet 的名字，遍历sheet名字
# for i in wb.sheetnames:
#     print(i)

#遍历获取sheet对象，按照sheet顺序获取
# for sheet in wb:
#     print (sheet)
#     print(sheet.title)
#删除某个sheet
del wb['3']

wb.save('smple.xlsx')

写入内容：

from openpyxl  import Workbook
import time
import datetime
import locale

wb = Workbook()
ws = wb.active

#写入单个单元格，可以写入数字，中午，字符串
ws['A1'] = 1
ws['B1'] = '暑期专题' +'shuqi'

#写入多个单元格，已经存在的文件覆盖,第几次写入，就写在第几行
ws['B1'] = 7
ws.append([4,5,6])
ws.append([1,2,3])

ws['A2'] = time.time()

#插入一个当前时间
ws['A3'] = datetime.datetime.now()
#写入一个自定义的时间格式
locale.setlocale(locale.LC_CTYPE, 'chinese')
ws['A4'] =time.strftime("%Y年%m月%d日 %H时%M分%S秒",time.localtime())

wb.save('smple1.xlsx')

#4、 操作单元格

from openpyxl import Workbook
wb = Workbook()

ws = wb.create_sheet('Mysheet')  #创建一个sheet

ws['A1'] = 123.11
ws['B2'] = '暑期专题'
d = ws.cell(row=4, column=2, value=10)

print(ws['A1'].value)
print(ws['B2'].value)
print(d.value)
print(ws.cell(row=4, column=2, value=10).value)  #行号和列号从1开始

wb.save('smple1.xlsx')