python读excel

一 python读取excel（xlrd）

# -*- coding:utf-8 -*-
# Author : liuqingzheng
# Data : 2018/11/27 17:26
# 导入模块
import xlrd
from datetime import datetime,date
# 打开要读的excel
tt=xlrd.open_workbook('tt.xlsx')
# 打印所有表格名字
print(tt.sheet_names())
#通过索引获取表格
sheet1 = tt.sheet_by_index(0)
print(sheet1)
#通过名字获取表格
# sheet2 = tt.sheet_by_name('个人信息')
# sheet1.nrows 该表格行数
# sheet1.ncols 该表格列数
print(sheet1.name,sheet1.nrows,sheet1.ncols)
# 根据行数，获取该行所有内容，放到列表中
rows = sheet1.row_values(2)
print(rows)
# 根据列数，获取该列所有内容，放到列表中
cols = sheet1.col_values(3)#获取列内容
print(cols)
# 获取表格里的内容，三种方式（获取第一行第二列，从0开始计算）
print(sheet1.cell(1, 2).value)
print(sheet1.cell_value(1, 2))
print(sheet1.row(1)[2].value)
# 通过列取
print(sheet1.col(2)[1].value)


# print(sheet1.row(4))

print(sheet1.cell(1,3).ctype)
# print(tt.datemode)
'''
默认情况下，Excel for Windows使用1900日期系统，而Excel for Macintosh使用1904日期系统。

找了一通资料才知道原來是为了要处理闰年问题… XD (註) 另，原來 Excel (on Windows) 內部资料也是用 1900-based 的日期 (这是为了和旧式的 Lotus 1-2-3 相容)，而 Excel (on Mac) 是 1904-based
'''
#**********处理日期格式
date_value = xlrd.xldate_as_tuple(sheet1.cell_value(1,3),tt.datemode)
# 打印出数组形式的日期
print(date_value)
# 用data模块处理一下时间
# print(date(*date_value[0:3]))
print(date(*date_value[:3]))
print(date(*date_value[:3]).strftime('%Y-%m-%d'))
# *********merged_cells
# (1, 3, 4, 5)的含义是：第1到2行（不包括3）合并，(7, 8, 2, 5)的含义是：第2到4列合并
# [(4, 5, 2, 6)]:表示第2列到第5列合并
print(sheet1.merged_cells)
print(sheet1.row_values(4,2))
# 所以要取值，要取第4行，第2列，也就是低位索引
print(sheet1.cell_value(4,2))


merge = []
print(sheet1.merged_cells)
for (rlow,rhigh,clow,chigh) in sheet1.merged_cells:
    merge.append([rlow,clow])
for index in merge:
    print(sheet1.cell_value(index[0],index[1]))

 常用函数

import xlrd

# 读取文件
work_book = xlrd.open_workbook("/Users/jerry/Desktop/公司机密数据.xlsx")
# 选取一个表

# 获取所有所有表格名称
print(work_book.sheet_names())

# 选择第2个  索引从0开始
sheet = work_book.sheet_by_index(1)

# 表格名称
print(sheet.name)

# 行数
print(sheet.nrows)
# 列数
print(sheet.ncols)


#批量读取行数据
# 取出第6行的全部内容包含数据类型
print(sheet.row(6))
# 取出第6行的内容包含数据类型 从第3列开始获取
print(sheet.row_slice(6,start_colx=3))

# 取出第6行的内容包含数据类型 从第3列开始获取
print(sheet.row_slice(6,start_colx=4,end_colx=5))

# 获取该行所有数据类型 一数字表示
# print(sheet.row_types(6))
# print(sheet.row_values(6))


# 单元格的处理
print(sheet.cell(0,0).value) # 取值
print(sheet.cell(0,0).ctype) # 取类型
print(sheet.cell_value(2,0)) # 直接取值

print(sheet.row(0)[0]) # 先取行再取单元格
print(sheet.col(0)) # 第0列所有数据
print(sheet.col(0)) # 先取列再取单元格

print(sheet.cell_type(0,0))

# 单元格位置转换
print(xlrd.cellname(2,1))
print(xlrd.cellnameabs(0,2))
print(xlrd.colname(5))


# 时间类型转换
# print(sheet.cell(6,5).value)
# print(xlrd.xldate_as_datetime(sheet.cell(6,5).value,1))

实战案例

读取一个报价单 其第二个sheet包含合并单元格

文件地址:https://share.weiyun.com/5GaLY2m

import xlrd

sheet = xlrd.open_workbook("报价单.xlsx").sheet_by_index(1)

def get_text(row,col):
    # 判断该坐标是否是被合并的单元格    合并单元格的数据都在合并区域的第一个位置 
    for ces in sheet.merged_cells:
        if (row >= ces[0] and row < ces[1]) and (col >= ces[2] and col < ces[3]):
            return sheet.cell(ces[0],ces[2]).value # 取出合并区域的第一个数据
    return sheet.cell(row,col).value #正常取出对应数据

keys = sheet.row_values(1) # 获取所有的列标题
data = []
for row in range(2,sheet.nrows):
    dic = {}
    for col in range(sheet.ncols):
        k = keys[col] #确定key
        res = get_text(row,col)
        dic[k] = res # 确定值 并存储
    data.append(dic)


print(data)
# 序列化为json
import json
json.dump(data,open("test.json","wt"),ensure_ascii=False)

二 python写excel（xlwt）

是python中一个第三方的用于写入excle数据到表格的模块 ​ 用代码来编写exlce是非常低效的 所以该模块了解即可

import xlwt
# 创建工作簿
work = xlwt.Workbook()
# 创建一个表
sheet = work.add_sheet("员工信息数据")


#创建一个字体对象
font = xlwt.Font()
font.name = "Times New Roman" # 字体名称
font.bold = True # 加粗
font.italic = True # 斜体
font.underline = True # 下划线

#创建一个样式对象
style = xlwt.XFStyle()
style.font = font


# 写入标题
for k in keys:
    sheet.write(0,keys.index(k),k,style)

# 写入数据
for i in infos:
    for k in keys:

 三 读取excel并写入数据库

import xlrd
import pymysql

# 读取文件
work_book = xlrd.open_workbook("/xxx/xxx.xlsx")
# 选取一个表
sheet = work_book.sheet_by_index(0)

# 遍历表格数据
datas = []
for row in range(1,sheet.nrows):
    temp_list =[]
    for col in range(sheet.ncols):
        value = sheet.cell_value(row,col)
        temp_list.append(value)
    datas.append(temp_list)

# 打开数据库连接
db = pymysql.connect(host='localhost', port=3306,
                     user='username', passwd='password', db='database_name', charset='utf8')

# 使用cursor()方法获取操作游标
cursor = db.cursor()

# SQL 插入语句
sql = "INSERT INTO SHOP(shop_code, shop_name, month) VALUES (%s,%s,%s)"