python 操作Excle
import xlwt,xlrd
import openpyxl
import xlrd
import pymysql
import xlrd
import pymysql
fname = "zongshujv.xls"
bk = xlrd.open_workbook(fname)#sh
print(bk)
##shxrange = range(bk.nsheets)
fnam = "5-6.xls"
bke = xlrd.open_workbook(fnam)#sh1
try:
sh = bk.sheet_by_index(0)
except:
print("////")
try:
sh1 = bke.sheet_by_index(0)
except:
print("////")
nrows = sh.nrows
#获取列数
ncols = sh.ncols
nrows1 = sh1.nrows
#获取列数 9405536897846453963105
ncols1 = sh1.ncols
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('test')
cell_overwrite_ok=True
for b in range(1,nrows):
data1 = sh.cell(b,0)
dingdanbianhao=str(data1)[6:-1]
## print(dingdanbianhao)
data2 = sh.cell(b,1)
k2=str(data2)[6:-1]
## print(k2)
data3 = sh.cell(b,2)
k3=str(data3)[6:-1]
## print(k3)
data4 = sh.cell(b,3)
k4=str(data4)[6:-1]
## print(k4)
data5 = sh.cell(b,4)
k5=str(data5)[6:-1]
## print(k5)
data6 = sh.cell(b,5)
k6=str(data6)[6:-1]
## print(k6)
data7 = sh.cell(b,6)
k7=str(data7)[6:-1]
## print(k7)
data8 = sh.cell(b,7)
k8=str(data8)[6:-1]
## print(k8)
for i in range(0,nrows1):
y=sh1.cell(i,0)
y1=str(y)[6:-1]
## print(y1)
if y1==dingdanbianhao:
print(y1)
## db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='kuaidi')
## cursor = db.cursor()
## sql = 'INSERT INTO k1(dingdanbianhao,k2,k3,k4,k5,k6,k7,k8,k9,k10,k11,k12) values("%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s")'%(dingdanbianhao,k2,k3,k4,k5,k6,k7,k8,k9,k10,k11,k12)
#### print(sql)
## cursor.execute(sql)
## db.commit()
worksheet.write(b,1,dingdanbianhao)
worksheet.write(b,2,k2)
worksheet.write(b,3,k3)
worksheet.write(b,4,k4)
worksheet.write(b,5,k5)
worksheet.write(b,6,k6)
worksheet.write(b,7,k7)
worksheet.write(b,8,k8)
workbook.save('excelwrite.xls')
##
## if ":" in youbian:
## a=youbian[1:-1]
## worksheet.write(b,0,dingdanbianhao)
## worksheet.write(b,1,chanpingmingcheng)
## worksheet.write(b,2,huohao)
## worksheet.write(b,3,jine)
## worksheet.write(b,4,kuaididanhao)
## worksheet.write(b,5,name)
## worksheet.write(b,6,dianhua)
## worksheet.write(b,7,dizhi)
## worksheet.write(b,8,k1)
## worksheet.write(b,9,k2)
## worksheet.write(b,10,k3)
## worksheet.write(b,11,sku)
## worksheet.write(b,12,price)
## worksheet.write(b,13,a)
## workbook.save('excelwrite.xls')
## worksheet.write(b,0,dingdanbianhao)
## worksheet.write(b,1,chanpingmingcheng)
## worksheet.write(b,2,huohao)
## worksheet.write(b,3,jine)
## worksheet.write(b,4,kuaididanhao)
## worksheet.write(b,5,name)
## worksheet.write(b,6,dianhua)
## worksheet.write(b,7,dizhi)
## worksheet.write(b,8,k1)
## worksheet.write(b,9,k2)
## worksheet.write(b,10,k3)
## worksheet.write(b,11,sku)
## worksheet.write(b,12,price)
## worksheet.write(b,13,youbian)
## workbook.save('excelwrite.xls')
##
##
## worksheet.write(b,0,dingdanbianhao)
## worksheet.write(b,1,chanpingmingcheng)
## worksheet.write(b,2,huohao)
## worksheet.write(b,3,jine)
## worksheet.write(b,4,kuaididanhao)
## worksheet.write(b,5,name)
## worksheet.write(b,6,dianhua)
## worksheet.write(b,7,dizhi)
## worksheet.write(b,8,sku)
## worksheet.write(b,9,price)
## worksheet.write(b,10,youbian)
## workbook.save('excelwrite.xls')
##
##import openpyxl
##import xlrd
##import pymysql
##fname = "asd.xls"
##bk = xlrd.open_workbook(fname)
##print(bk)
##
##try:
## sh = bk.sheet_by_index(0)
##except:
## print("////")
##nrows = sh.nrows
###获取列数
##ncols = sh.ncols
##v=[1,2,3,4,5,6,7,8,9]
##for b in range(1,len(v)):
## data1 = sh.cell(b,1)
## dingdanbianha=str(data1)[6:-1]
## print(dingdanbianha)
## dingdanbianhao= int(float(dingdanbianha))
#### print(dingdanbianhao)
## zhongliang=dingdanbianhao*0.05
## a=round(zhongliang,2)
## jinzhong=dingdanbianhao*0.04
## money=dingdanbianhao*1.5
## print(a,jinzhong,money)
## data = openpyxl.Workbook() # 新建工作簿
## data.create_sheet('Sheet1') # 添加页
## #table = data.get_sheet_by_name('Sheet1') # 获得指定名称页
## table = data.active # 获得当前活跃的工作页，默认为第一个工作页
## print(b)
## table.cell(b,3).value = b # 行，列，值 这里是从1开始计数的
##data.save('asd.xlsx')
##
##from openpyxl import load_workbook
##import pandas as pd
##
##def _excelAddSheet(dataframe,excelWriter,sheet_name):
## book = load_workbook(excelWriter.path)
## excelWriter.book = book
## dataframe.to_excel(excel_writer=excelWriter,sheet_name=sheet_name,index=None)
## excelWriter.close()
##
##writer = pd.ExcelWriter('test.xlsx',engine='openpyxl')
##sheet_name = 'K=20'
##y=[4,5,6]
##x=['a','b','c']
##dataframe = pd.DataFrame(list(zip(x,y)))
##print(123)
##_excelAddSheet (dataframe, writer, sheet_name)
##
##import xlrd,xlwt
##
##from xlutils.copy import copy
##import pymysql
##fname = "LAX0412.xls"
##bk = xlrd.open_workbook(fname)
##print(bk)
##
##try:
## sh = bk.sheet_by_index(0)
##except:
## print("////")
##
####data = xlrd.open_workbook('excel_test.xls')
##excel = copy(wb=bk)
##excel_table = excel.get_sheet(0)
##nrows = sh.nrows
###获取列数
##ncols = sh.ncols
##
##
##d=1
##for b in range(1,nrows):
## data1 = sh.cell(b,18)
## print(data1)
## dingdanbianha=str(data1)[7:]
## print(dingdanbianha)
## dingdanbianhao= int(float(dingdanbianha))
#### print(dingdanbianhao)
## zhongliang=dingdanbianhao*0.05
## a=round(zhongliang,2)
## jinzhong=dingdanbianhao*0.04
## money=dingdanbianhao*1.5
#### print(a,jinzhong,money)
## import openpyxl
##
## workbook = openpyxl.Workbook()
##
## sheet=workbook.active
##
## excel_table.write(d,1,a)
##
## d = d 1
##
##workbook.save('new.xlsx')
##import xlwt,xlrd
##from xlutils.copy import copy
##data = xlrd.open_workbook('asd.xls',formatting_info=True)
##excel = copy(wb=data) # 完成xlrd对象向xlwt对象转换
##excel_table = excel.get_sheet(0) # 获得要操作的页
##table = data.sheets()[0]
##nrows = table.nrows # 获得行数
##ncols = table.ncols # 获得列数
##values = ["E","X","C","E","L"] # 需要写入的值
##for value in values:
## excel_table.write(nrows,0,value) # 因为单元格从0开始算，所以row不需要加一
## nrows = nrows 1
##excel.save('asd.xls')