python使用openpyxl读取数据_Python-openpyxl读取和写入数据1

1.读取单元格保存到文件，注意python中文件报错或者不能写入时改文件后缀：.xls

from openpyxl import Workbook

from openpyxl import load_workbook

from openpyxl.styles import NamedStyle, Border, Side, Alignment

wb = load_workbook(r"C:\\Users\\Desktop\\报表数据\\运营1月报数据底稿_20190808_V 1.0.xlsx")

sh = wb["test_data"]

#读取头

#用enumerate包装一个可迭代对象，可以同时使用索引和迭代项

fp1 = open('test1.xls', 'w')

for index, item in enumerate(sh["A1":"AA1"]):

if index > 0:

print("\n")

for cell in item:

print(cell.value, sep=" ",end=" ",file=fp1)

#读取运营部数据

fp = open('test.xls', 'w')

for index, item in enumerate(sh["A29":"AA48"]):

if index > 0:

print("\n")

for cell in item:

print(cell.value, sep=" ",end=" ",file=fp)

print(cell.value, end=" ", file=fp, flush=True)

fp1.close()

fp.close()

2.读入数据并创建新的sheet写入,添加了一列日期

from openpyxl import Workbook

from openpyxl import load_workbook

from openpyxl.styles import NamedStyle, Border, Side, Alignment

wb = load_workbook(r"C:\\Users\\Desktop\\报表数据\\运营1月报数据底稿.xlsx")

sh = wb["test_data"]

#写入表头

dilei_head = ['产品线/产品','内部顾问费','通用外包费','实施费(固定价)','软件维护费','软件-摊销','硬件-折旧','硬件维保','IDC运维费','网络租金','差旅费','直接成本',

'部门间采购','总投入','集团部室','SBU/BU','集团外','外部收入小计','部门间销售','总收入','直接销售收入','直接利润率','DBA收入还原',

'运营','建设','考核营收','内部营收','日期']

dilei_col = ['IT运营服务部','资金管理系统','财务合并','全面预算','财务核算','ERP管理','供应商管理','税务管理系统','商业智能平台','ITSM平台'

,'企业服务总线','安全身份管理平台','桌面及总部VIP运维','邮件系统','主数据','DBA服务','集团SAP','服务台','ISO认证','服务计量平台']

sheet0Name = '运营分析'

sheet0 = wb.create_sheet(sheet0Name, index=0)

for i, item in enumerate(dilei_head):

sheet0.cell(row = 1,column=i+1,value=item)

for i, item in enumerate(dilei_col):

sheet0.cell(row = i+2,column=1,value=item)

# 写入数据

for index, item in enumerate(sh["A29":"AA48"]):

index = index + 2

for j, val in enumerate(item):

sheet0.cell(row=index, column=j + 1, value=val.value)

sheet0.cell(index,28).value = '2019-01'

wb.save( 'test.xls')

3.合并单元格处理

import pandas as pd

import numpy as np

import xlrd

import xlwt

from openpyxl.workbook import Workbook

from openpyxl.writer.excel import ExcelWriter

pd.set_option('display.max_rows',2000)

pd.set_option('display.width',2000)

def read_excel(file_path):

data=xlrd.open_workbook(file_path)

table1=data.sheet_by_name('月报数据底稿')

nrows=table1.nrows

ncols = table1.ncols

#print(ncols)

colspan = {}

if table1.merged_cells:

for item in table1.merged_cells:

for row in range(item[0], item[1]):

for col in range(item[2], item[3]):

# 合并单元格的首格是有值的，所以在这里进行了去重

if (row, col)!= (item[0], item[2]):

colspan.update({(row, col):(item[0],item[2])})

# 读取每行数据

row1 = []

for i in range(1,2):

for j in range(ncols):

# 假如碰见合并的单元格坐标，取合并的首格的值即可

if colspan.get((i, j)):

row1.append(table1.cell_value(*colspan.get((i, j))))

else:

row1.append(table1.cell_value(i, j))

print(row1)

for i in range(29, 49):

row2 = []

for j in range(ncols):

# 假如碰见合并的单元格坐标，取合并的首格的值即可

if colspan.get((i, j)):

row2.append(table1.cell_value(*colspan.get((i, j))))

else:

row2.append(table1.cell_value(i, j))

print(row2)

#保存数据

new_x=[list(t) for t in set(tuple(_) for _ in x)]

wb = Workbook()

ws = wb.active

ws.title = u'1月份数据'

# 向第一个sheet页写数据吧

i = 1

r = 1

for line in new_x:

for col in range(1, len(line) + 1):

ColNum = r

ws.cell(row=r, column=col).value = line[col - 1]

i += 1

r += 1

# 工作簿保存到磁盘

wb.save('data.xlsx')

4.批量读取excel文件

import pandas as pd

import numpy as np

import xlrd

import xlwt

pd.set_option('display.max_rows',2000)

pd.set_option('display.width',2000)

for i in range(1,8):

excel_name='C:\\Users\\Desktop\\报表数据\\运营%d月报数据底稿_20190808_V 1.0.xlsx'% i

wb=xlrd.open_workbook(excel_name)

#wb=xlwt.Workbook()

sheets_name=wb.sheet_names()

#print(sheets_name)

table1=wb.sheet_by_name('月报数据底稿')

table2=wb.sheet_by_name('10-收入明细')

#print(table1.name, table1.nrows, table1.ncols)

for p in range(1,27):

for m in range(1,27):

#for j in range(0,23):

rows1 = table1.row_values(p) # 获取行内容

rows2 = table1.row_values(m) # 获取行内容

#cols = table1.col_values(j) # 获取列内容

#获取单元格内容

print(table1.cell_value(p,m))