pythonexcel

import os, re, openpyxl
filename = r'D:\bar\办公自动化设备.xlsx'
inwb = openpyxl.load_workbook(filename)
print(inwb.sheetnames[0])
ws = inwb[inwb.sheetnames[0]]
rows = ws.max_row+1
col = ws.max_column+1
outwb = openpyxl.Workbook()  # 打开一个将写的文件
outws = outwb.create_sheet(index=0)  # 在将写的文件创建sheet
r = 1
for i in range(1, col):
    var = ws.cell(2, i).value
    outws.cell(r, i).value = var
    outws.cell(r, i+col-1).value = var+"1"
    print(var)

for j in range(3, rows):
    var = ws.cell(j, col-1).value
    print(var)
    if "在用" in ws.cell(j, col-1).value or "报废" in ws.cell(j, col-1).value:

            for i in range(1, col):
                 outws.cell(j-1, i).value=ws.cell(j, i).value

outwb.save(r"D:\bar\test.xlsx")

import os, re, openpyxl
import time

filename = r'信息设备台帐 (1).xlsx'
inwb = openpyxl.load_workbook(filename)
print(inwb.sheetnames[1])
ws = inwb[inwb.sheetnames[1]]
rows = ws.max_row


# cols= ws.max_col

def regix(str):
    pattern = re.compile("公用")
    if pattern.search(str) is not None:
        return False
    else:
        return True


result = ""

outwb = openpyxl.Workbook()  # 打开一个将写的文件
outws = outwb.create_sheet(index=0)  # 在将写的文件创建sheet
r=1
outws.cell(r, 1).value, outws.cell(r, 2).value, outws.cell(r, 3).value, outws.cell(r,4).value, outws.cell(r, 5).value="设备名称","设备编号","责任部门","责任人","硬盘序列号"
for r in range(2, rows):
    if ws.cell(r, 12).value is not None or ws.cell(r, 13).value is not None:
        if ws.cell(r, 9).value is not None:
            if regix(ws.cell(r, 9).value):
                if "闲置" in ws.cell(r, 9).value or "报废" in ws.cell(r, 9).value or "待报废" in ws.cell(r,
                                                                                                  9).value or "未分配" in ws.cell(
                        r, 9).value:
                    pass
                else:
                    if ws.cell(r, 12).value is not None:

                        outws.cell(r, 1).value, outws.cell(r, 2).value, outws.cell(r, 3).value, outws.cell(r,
                                                                                                           4).value, outws.cell(
                            r, 5).value = ws.cell(r, 4).value, ws.cell(r, 2).value, ws.cell(r, 7).value, ws.cell(r,
                                                                                                                 9).value, ws.cell(
                            r,
                            12).value
                        # #财产标签
                        # print(ws.cell(r,2).value)
                        # #品牌型号
                        # print(ws.cell(r,4).value)
                        # #所属部门
                        # print(ws.cell(r,7).value)
                        # #责任人
                        # print(ws.cell(r, 9).value)
                        # #序列号
                        # print(ws.cell(r, 12).value)
                        # print(ws.cell(r, 13).value)


                    else:

                        outws.cell(r, 1).value, outws.cell(r, 2).value, outws.cell(r, 3).value, outws.cell(r,
                                                                                                           4).value, outws.cell(
                            r, 5).value = ws.cell(r, 4).value, ws.cell(r, 2).value, ws.cell(r, 7).value, ws.cell(r,
                                                                                                                 9).value, ws.cell(
                            r,
                            13).value
            else:
                content = "%s  %s   %s   %s    %s" % (
                ws.cell(r, 4).value, ws.cell(r, 2).value, ws.cell(r, 7).value,"", "")
                result = result + content + "\n"

    else:
         content = "%s  %s   %s   %s    %s" % (ws.cell(r, 4).value, ws.cell(r, 2).value, ws.cell(r, 7).value, ws.cell(r, 9).value, "")
         result = result + content + "\n"


outwb.save("test.xlsx")
with open("excel1.txt", "w", encoding="UTF-8") as o:
    o.write(result)

# -*- coding: utf-8 -*-

import os, re, openpyxl
import time


def file_name(file_dir):
    listfile = os.listdir(file_dir)
    strfile = listfile.__str__()
    # print(strfile)
    return strfile


def regix(listname, strfile):

    for name in listname:
        pattern = re.compile(name)

        if pattern.search(strfile) is not None:
            pass
        else:
            print(name)



def readExel(ws):
    listname = []
     # 获取第一个sheet内容
    # 获取sheet的最大行数和列数
    rows = ws.max_row
    for r in range(3, rows):
        for c in range(10, 11):
            if ws.cell(r, c).value is not None:
                listname.append(ws.cell(r, c).value)
                # print(ws.cell(r, c).value)

    return listname



filename = r'D:\bar\信息设备台帐.xlsx'
inwb = openpyxl.load_workbook(filename)
strfile = file_name(r"\\172.16.1.20\AcmeData\检查报告")

for sheetnames in inwb.sheetnames:
    print("@@@@@@@"+sheetnames+"@@@@@@@")
    ws = inwb[sheetnames]  # 获取第一个sheet内容
    listname = readExel(ws)
    regix(listname, strfile)




def writeExcel(self):
    outwb = openpyxl.Workbook()  # 打开一个将写的文件
    outws = outwb.create_sheet(index=0)  # 在将写的文件创建sheet
    for row in range(1, 70000):
        for col in range(1, 4):
            outws.cell(row, col).value = row * 2  # 写文件
        print(row)
    saveExcel = "D:\\work\\Excel_txtProcesss\\test.xlsx"
    outwb.save(saveExcel)  # 一定要记得保存