使用python操作excel，怎么才能按顺序显示

mb0221 · 发表于 2020-11-13 09:59:15

您需要登录才可以下载或查看，没有账号？立即注册

x

#coding=gbk
import openpyxl
from openpyxl.utils import column_index_from_string
filename = r'./waf策略统计.xlsx'
tongji = openpyxl.load_workbook( filename )
#——————————————服务器ip：10.254.221.140————————————————————————————————————————
tongji1 = tongji['Sheet1']
maxtongji_hang = tongji1.max_row
daochu = openpyxl.load_workbook( r'./waf导出日志.xlsx' )
daochu1 = daochu['Sheet1']
maxdaochu_hang = daochu1.max_row
maxdaochu_lie = daochu1.max_column
for i in range(2,maxdaochu_hang + 1):
tongji1.cell(maxtongji_hang + 1,column_index_from_string('A')).value = maxtongji_hang + 0
if daochu1.cell(i,column_index_from_string('C')).value == '10.254.221.140' and daochu1.cell(i,column_index_from_string('D')).value == 8068:
tongji1.cell(i,column_index_from_string('B')).value = daochu1.cell(i,column_index_from_string('C')).value
tongji1.cell(i,column_index_from_string('C')).value = daochu1.cell(i,column_index_from_string('D')).value
maxtongji_hang += 1
#统计次数
times = 0
file_value={}
for each in daochu1['C']:
if each.value == '10.254.221.140':
times+=1
#print(times)
tongji.active['K2']=times
#————
tongji.save('./xin.xlsx')

复制代码

目前我这边导出的表格并不是按顺序显示而是原表格的哪行就到新表格的哪一行，如果我有说的不明白的地方尽管说哈

逃兵 · 发表于 2020-11-13 11:30:20

你可以把大问题切成几个小问题
一个一个问题解决最后拼接在一起
这个问题你主要是想解决怎样的排序问题？

kogawananari · 发表于 2020-11-13 11:44:40

from openpyxl import load_workbook
def read_excel_xlsx(path:str, sheet_index:int=0, is_read_head:bool=False):
wb = load_workbook(path, read_only=True)
table = wb[wb.sheetnames[sheet_index]]
rows = table.max_row
cols = table.max_column
if rows < 2:
raise Exception("读取不到数据,或者只有一个表头,可以尝试把table命名为\'Sheet%d\'"%(1 + sheet_index))
querylist = map(lambda row: tuple(map(lambda col: col.value, row)),table.iter_rows(min_row=int(not is_read_head)+1, max_col=cols, max_row=rows))
return querylist
from openpyxl import Workbook
def write_excel_xlsx(path:str, sheet_index:int, value:list):
index = len(value)
wb = Workbook()
sheet = wb[wb.sheetnames[sheet_index]]
for i in range(0, index):
for j in range(0, len(value[i])):
sheet.cell(row = i + 1, column = j + 1, value=value[i][j])
wb.save(path)
qs = read_excel_xlsx('./waf策略统计.xlsx',is_read_head=True)
qs = filter(lambda x:x[2]==''10.254.221.140'' and x[3]=='8068', qs)
write_excel_xlsx('./xin.xlsx',0,value=list(qs))

复制代码

没给你统计次数你再改改

mb0221 · 发表于 2020-11-13 13:59:19

逃兵发表于 2020-11-13 11:30
你可以把大问题切成几个小问题
一个一个问题解决最后拼接在一起
这个问题你主要是想解决怎样的排序问题？

就是

tongji1.cell(maxtongji_hang + 1,column_index_from_string('A')).value = maxtongji_hang + 0
if daochu1.cell(i,column_index_from_string('C')).value == '10.254.221.140' and daochu1.cell(i,column_index_from_string('D')).value == 8068:
tongji1.cell(i,column_index_from_string('B')).value = daochu1.cell(i,column_index_from_string('C')).value
tongji1.cell(i,column_index_from_string('C')).value = daochu1.cell(i,column_index_from_string('D')).value

复制代码

第一句是表格里面的序号，如果匹配条件就把原表格的内容赋值到新的表格，但是不是按顺序的，比如原表格第一条有这个条件，第三条也有，新生成的表格就会显示第一条有，第二条空，第三条有，怎样让新生成的表格顺着排下去就行这样

逃兵 · 发表于 2020-11-13 16:01:52

是这个意思吗？

#coding=gbk
import openpyxl
from openpyxl.utils import column_index_from_string
filename = r'./waf策略统计.xlsx'
tongji = openpyxl.load_workbook( filename )
#——————————————服务器ip：10.254.221.140————————————————————————————————————————
tongji1 = tongji['Sheet1']
maxtongji_hang = tongji1.max_row
daochu = openpyxl.load_workbook( r'./waf导出日志.xlsx' )
daochu1 = daochu['Sheet1']
maxdaochu_hang = daochu1.max_row
maxdaochu_lie = daochu1.max_column
for i in range(2,maxdaochu_hang + 1):
tongji1.cell(maxtongji_hang + 1,column_index_from_string('A')).value = maxtongji_hang + 0
if daochu1.cell(i,column_index_from_string('C')).value == '10.254.221.140' and daochu1.cell(i,column_index_from_string('D')).value == 8068:
tongji1.cell(i,column_index_from_string('B')).value = daochu1.cell(i,column_index_from_string('C')).value
tongji1.cell(i,column_index_from_string('C')).value = daochu1.cell(i,column_index_from_string('D')).value
maxtongji_hang += 1
#统计次数
times = 0
file_value={}
for each in daochu1['C']:
if each.value == '10.254.221.140':
times+=1
#print(times)
ws = tongji.active
a = 0
for i in ws['B']:
a+=1
if i.value == None:
ws.delete_rows(a)
a-=1
ws['K2']=times
#————
tongji.save('./xin.xlsx')

复制代码

账号		自动登录	找回密码
密码			立即注册