How can make excel file using text file?

Question

I made some script for mad excel using python. I just study beginner about python. So my script is too long. Who can make short?

And I want made cell borders

I want read some text file. And till to cell.

Below is text file.

Node1_aggr0,492GB,469GB,22GB,95%
Node1_aggr0/.snapshot,0GB,0GB,0GB,0%
Node1_aggr1,73333GB,65602GB,7731GB,89%
Node1_aggr1/.snapshot,0GB,0GB,0GB,0%
Node1_aggr2,19194GB,16147GB,3047GB,84%
Node1_aggr2/.snapshot,0GB,0GB,0GB,0%
Node2_aggr0,492GB,469GB,22GB,95%
Node2_aggr0/.snapshot,0GB,0GB,0GB,0%
Node2_aggr1,73333GB,66823GB,6510GB,91%
Node2_aggr1/.snapshot,0GB,0GB,0GB,0%
Node2_aggr2,19194GB,16834GB,2359GB,88%
Node2_aggr2/.snapshot,0GB,0GB,0GB,0%

And i made below script. How can made short?

from openpyxl import Workbook
from openpyxl.styles import Font, Side, Border

wb = Workbook()
ws1 = wb.active
ws1.title = "Example1"

ws1['A1'] = "aggr info"
ws1['A2'] = "aggr"
ws1['B2'] = "total(GB)"
ws1['C2'] = "used(GB)"
ws1['D2'] = "avail(GB)"

with open("excel.txt", "r") as f:
        n = 2
        for line in f:
                line = line.split(',')
                n = int(n)
                n += 1
                n = str(n)
'''
                c1 = "A" + n
                c2 = "B" + n
                c3 = "C" + n
                c4 = "D" + n
'''
                c1, c2, c3, c4 = ["A" + n, "B" + n, "C" + n, "D" + n]
                c1 = ws1.cell(c1)
                c2 = ws1.cell(c2)
                c3 = ws1.cell(c3)
                c4 = ws1.cell(c4)
                c1.value = line[0]
                c2.value = line[1]
                c3.value = line[2]
                c4.value = line[3]
#               c1.font = Font(name='Arial', size=14)
                c1.border = Border(left=Side(border_style="thin", color='FF000000'),right=Side(border_style="thin", color='FF000000'),top=Side(border_style="thin", color='FF000000'),bottom=Side(border_style="thin", color='FF000000'))
                c2.border = Border(left=Side(border_style="thin", color='FF000000'),right=Side(border_style="thin", color='FF000000'),top=Side(border_style="thin", color='FF000000'),bottom=Side(border_style="thin", color='FF000000'))
                c3.border = Border(left=Side(border_style="thin", color='FF000000'),right=Side(border_style="thin", color='FF000000'),top=Side(border_style="thin", color='FF000000'),bottom=Side(border_style="thin", color='FF000000'))
                c4.border = Border(left=Side(border_style="thin", color='FF000000'),right=Side(border_style="thin", color='FF000000'),top=Side(border_style="thin", color='FF000000'),bottom=Side(border_style="thin", color='FF000000'))

wb.save('test.xlsx')

I want this result.

ksai · Accepted Answer · 2017-07-24 05:40:30Z

1

It would be very easy if you use pandas.read_table method.

import pandas as pd
file = 'text_file.txt' # your text file 
table = pd.read_table(file, encoding='utf_16', sep=',', header=None)

table.to_csv('newfile.csv') # to get a csv file
table.to_excel('newfile.xlsx') # to get excel file

In case, if you don't have pandas installed, you can install it by following the instructions here.

edited Jul 24, 2017 at 5:40

answered Jul 24, 2017 at 5:33

ksai

9776 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

KJ9 Over a year ago

Thank you so much. your answer. I want line borders on cells. I modify my question. Please read again. Thank you

ksai Over a year ago

what do you mean by borders on cells.

KJ9 Over a year ago

that is mean "excel cell border line" like in my attached pic. My script has

c1.border = Border(left=Side(border_style="thin", color='FF000000'),right=Side(border_style="thin", color='FF000000'),top=Side(border_style="thin", color='FF000000'),bottom=Side(border_style="thin", color='FF000000'))

for cell border line.

Maarten Fabré Over a year ago

on this blog post there are some examples on how to do more advanced layout of Excel files via python

Collectives™ on Stack Overflow

How can make excel file using text file?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related