python 将txt导入excel

Question

要处理的txt文件如下：
就是一个ip，对应 该主机用户，大概有几百台

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')

结果出来的是这样的。

而我想要的是这样的：

请帮忙指导一下，谢谢！

Answer 1

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")

有点瑕疵，不过可以用。

Answer 2

贴一段代码给你参考参考吧：

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()