首页 > 编程语言 > 详细

python操作Excel

时间:2019-05-21 00:47:18      阅读:166      评论:0      收藏:0      [点我收藏+]
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

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

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!