首页 > 编程语言 > 详细

python操作Excel

时间:2019-05-21 00:47:18      阅读:81      评论:0      收藏:0      [点我收藏+]

标签:tex   pen   util   write   shee   read   file   num   value   

import openpyxl
import pandas as pd 
import numpy as np 
import openpyxl as pyxl
import xlrd
import xlwt
from xlutils.copy import copy
import os  


totalPath = input("请输入总清单Excel:")  
file_dir = input("请输入打包清单路径:")

for root, dirs, files in os.walk(file_dir):  
    print(root) #当前目录路径  
    print(dirs) #当前路径下所有子目录  
    print(files) #当前路径下所有非目录子文件 
    for file in files: 
        packPath = os.path.join(root, file)

        savefile = root + "/" + os.path.splitext(file)[0] + "含图纸编号.xls"
        #修改Excel
        bk1 = xlrd.open_workbook(packPath)
        bk2 = copy(bk1)

        xl = pd.ExcelFile(packPath)
        sheetName = xl.sheet_names 

        print(sheetName)

        for i in range(len(sheetName)):
            packDFRaw = pd.read_excel(packPath,sheetname=i,header=1)
            #删除最后一行
            rowEnd = len(packDFRaw)
            packDF = packDFRaw.drop(index = [rowEnd-1])
            #获得模板编号
            packBoardId = packDF["模板编号"]

            #从总清单中找到模板编号对应的图纸编号
            totalDFRow = pd.read_excel(totalPath,header=1)
            # drop_duplicate方法是对DataFrame格式的数据,去除特定列下面的重复行
            # 返回DataFrame格式的数据
            totalDF = totalDFRow.drop_duplicates("模板编号", first, inplace=False)
            totalSeries = pd.Series(totalDF["图纸编号"].values, index = totalDF["模板编号"])
            #imgeIdList图纸编号
            imageIdList = []
            for boardId in packBoardId:
                print(boardId)    
                temp = totalSeries[boardId]
                print(temp)
                imageIdList.append(temp)
            print(type(imageIdList))


            ws = bk2.get_sheet(i)
            ws.write(1,3,"图纸编号")
            for j in range(0, len(imageIdList) ):
                print(imageIdList[j])
                ws.write(2+j,3,imageIdList[j])

        bk2.save(savefile)

 

python操作Excel

标签:tex   pen   util   write   shee   read   file   num   value   

原文:https://www.cnblogs.com/vijing/p/10897440.html

(0)
(0)
   
举报
评论 一句话评论(0
登录后才能评论!
© 2014 bubuko.com 版权所有 鲁ICP备09046678号-4
打开技术之扣,分享程序人生!
             

鲁公网安备 37021202000002号