python excel

15. 读取excle 表格

from collections import OrderedDict

from pyexcel_xls import get_data

from pyexcel_xls import save_data

 

def get_dianpu_list(self):

       '''

       读取表格里边的　　所有店铺　信息，　返回　　所有店铺列表

 

       '''

       xls_data = get_data(r"bijben02.xlsx")

       # print("....:", type(xls_data))

       # print(xls_data)

       # print(xls_data.keys())

       # print(type(xls_data['Sheet']))

       # for sheet_n in xls_data.keys():

       #     print(sheet_n,":", xls_data[sheet_n])

       all_list = xls_data['Sheet']

       # for i in range(2,len(all_list)):

       for i in range(8, 80):    # 行数

           d_l = {}

           shop_id = all_list[i][1]    # 第I 行 的 第一个

           if shop_id != '':

                # print(shop_url)

                # print('店铺Ｉｄ　　', shop_id)

                shop_name = all_list[i][0]

                shop_url = all_list[i][2]

                # 更新　店铺　表

                #self.connection_mysql01(shop_name, shop_id, shop_url)

 

                d_l['shop_id'] = shop_id

                d_l['shop_url'] = shop_url

                self.dianpu_list.append(d_l)

       print('+++++ dianpu ++++ 店铺数量　',len(self.dianpu_list))

 

 

15.1 写入excel表格

from openpyxl import Workbook    # 保存为ｅｘｃｅｌ表格

 

    #保存为ｅｘｃｅｌ表格

   def __init__(self):

       self.wb = Workbook()

       self.ws = self.wb.active

       self.ws.append(['标题', '价格', '商品ｉｄ', '评论数'])  # 设置表头

 

   def process_item(self, item, spider): # 工序具体内容

       line = [item['title'], item['price'], item['goods_id'],item['comment']]  # 把数据中每一项整理出来

       self.ws.append(line)  # 将数据以行的形式添加到xlsx中

       self.wb.save('phone99.xlsx')  # 保存xlsx文件

       return item

 17. 批注   定义名称

 

# coding=utf-8
import re
from openpyxl import Workbook    # 写入
from openpyxl.comments import Comment    # 批注
# from openpyxl.workbook.defined_name import DefinedName    # 定义名称
from openpyxl import load_workbook    # 读取

def pizhu():
    wb1 = Workbook()
    ws1 = wb1.active
    # sheet1 = wb1.get_sheet_names()
    # sheet1.title = 'Sheet1'
    # my_range = wb1.defined_names['my_range']


    # 读取execl
    # 默认可读写，若有需要可以指定write_only和read_only为True
    filename = 'zidongduqu'
    wb = load_workbook('%s.xlsx' % filename)
    # 获取工作表--Sheet
    # 获得所有sheet的名称
    # print(wb.get_sheet_names())
    # 根据sheet名字获得sheet
    # a_sheet = wb.get_sheet_by_name('Sheet')
    # 获得sheet名
    # print(a_sheet.title)
    # 获得当前正在显示的sheet, 也可以用wb.get_active_sheet()
    sheet = wb.active

    l_num = sheet.max_column  # 最大列
    h_num = sheet.max_row  # 最大行
    print(sheet.max_row)
    print(sheet.max_column)
    all_data = []
    one_rows_data = []
    for i in range(1, h_num + 1):  # h_num + 1
        for j in range(1, l_num + 1):
            p = sheet.cell(row=1, column=j)
            pz = p.value    #  第一行的值  字段
            a = sheet.cell(row=i, column=j)  # 每列标题
            aa = a.value  # 此单元格里边的值  写入新表格
            coord = a.coordinate  # 此单元格坐标  A1  B1
            # print(aa, type(aa))  # class= str
            # print(coord, type(coord))  # class= str
            items = {}

            # 添加批注
            comment = Comment('%s' % pz, 'wyang')
            ws1['%s' % coord].comment = comment    # 批注


            # 写入新表格
            one_rows_data.append(aa)
            ws1['%s' % coord] = aa
            ws1['%s' % coord].value = aa

    print(one_rows_data)
    # ws1.append(one_rows_data)
    wb1.save('%s.xlsx' % (filename + '01'))

if __name__ == '__main__':
    pizhu()

from openpyxl import load_workbook
from openpyxl.workbook.defined_name import DefinedName
from openpyxl.utils import absolute_coordinate, quote_sheetname

p = ws.cell(row=1, column=j)pz = p.value # 第一行的值 字段a = ws.cell(row=i, column=j) # 每列标题# print(j)# aa = a.value # 此单元格里边的值 写入新表格coord = a.coordinate # 此单元格坐标 A1 B1print(coord)l = re.findall(r'^[A-Z]*', coord)[0]h = re.findall(r'[0-9]*$', coord)[0]# 把sheet1 里的A2 设定为rpt_date# dname = DefinedName(name='rpt_date')# dname.value = "Sheet1!$A$2"bb = "Sheet1!$" + l + '$' + hprint(bb)dname = DefinedName(name='%s' % pz)dname.value = bbwb.defined_names.append(dname) # 定义名称# print('>>>', dname, '---', type(dname))#wb.save(xl_savefile)wb.close()