鍍金池/ 問答/Python  Office/ python 將txt導(dǎo)入excel

python 將txt導(dǎo)入excel

要處理的txt文件如下：
就是一個(gè)ip，對(duì)應(yīng) 該主機(jī)用戶，大概有幾百臺(tái)
clipboard.png

import xlwt
import xlrd

fopen=open(r"C:\Users\ly\Documents\ps")
lines=fopen.readlines()
file=xlwt.Workbook(encoding='utf-8')
sheet=file.add_sheet('data')

i = 0
for line in lines:
    sheet.write(i, 0, line)
    i += 1

file.save('mini.xls')

結(jié)果出來的是這樣的。

clipboard.png

而我想要的是這樣的：

clipboard.png

請(qǐng)幫忙指導(dǎo)一下，謝謝！

回答
編輯回答
怣痛

貼一段代碼給你參考參考吧：

import xlwt

def set_style(name,height,bold=False):
    style = xlwt.XFStyle()

    font = xlwt.Font()
    font.name = name
    font.bold = bold
    font.color_index = 4
    font.height = height

    style.font = font

    return style


def write_excel():
    f = xlwt.Workbook()

    sheet1 = f.add_sheet(u'sheet1',cell_overwrite_ok=True)
    row0 = ['1','2','3','4','5','6','7','8']
    column0 = ['a','b','c','d','e']
    status = ['q1','q2','q3','q4']

    for i in range(0,len(row0)):
        sheet1.write(0,i,row0[i],set_style('Times New Roman',220,True))

    i, j = 1, 0
    while i < 4*len(column0) and j < len(column0):
        sheet1.write_merge(i,i+3,0,0,column0[j],set_style('Arial',220,True))
        sheet1.write_merge(i,i+3,7,7)
        i += 4
        j += 1

    sheet1.write_merge(21,21,0,1,'total',set_style('Times New Roman',220,True))

    i = 0
    while i < 4*len(column0):
        for j in range(0,len(status)):
            sheet1.write(j+i+1,1,status[j])
        i += 4

    f.save('demo1.xls')

if __name__ == '__main__':
    write_excel()

clipboard.png

2017年2月6日 05:15
編輯回答
念初
import pandas as pd
import re

text = """
192.168.1.1 | success | rc=0 >>
root
bin
abc
efg
hig
192.168.1.2 | success | rc=0 >>
klm
nop
qrs
tuv
wxy
z12
"""

data = text.strip().replace(" | success | rc=0 >>","").split("\n")

rs = []
ip = ""
for d in data:
    if re.match('\d+\.\d+\.\d+\.\d', d):
        ip = d
    else:
        rs.append((ip, d))
df = pd.DataFrame(rs, columns=["ip", "user"])
df = df.groupby(df["ip"]).apply(lambda x: x["user"])
df.to_excel("/path/to/test.xlsx")

有點(diǎn)瑕疵，不過可以用。

2018年3月7日 21:51