I am trying to read cells from .xls file and place them into particular spots in a static text and then have that generated as .txt file.
I managed to write code that generates what I need, but it only works for the first row in the .xls file. It generates 4 files according to the number of rows, but every file has values from the first row. I understand that I have static cell values for the first row in the code, but I am not sure how to amend this and get the code to go row by row/cell by cell and generate relevant .txt file for each row. My guess is that it needs to be incorporated into the for loop somehow?
For context, each row represents one printer, and these files are then used to import the printers into a system.
I'll be glad for any help/suggestions.
This is what my test .xls file looks like
prnt server desc shortnam driver type
-----------------------------------------------------------
deupr fransf pr deu ds22 adasd2 bizhub
frapr fransf pr fra ds23 asdasda3 hp
grcpr fransf pr grc ds24 asdasdq5 testhub
sinpr fransf pr sin ds25 qdsad8 testhp
This is the state of my code (excuse the mess - I am a complete beginner):
import xlrd
xlsfilename='Testing.xls'
test = xlrd.open_workbook(xlsfilename)
number_subjetcs=4 #will update this to require user input based on number of rows, test file has 4
number_columns=6 #is static
for row in range(number_subjetcs):
txtfilename = 'testfile' str(row) '.txt'
with open(txtfilename, "w") as f:
for row in range(1):
PrinterName = test.sheets()[0].cell(1,0).value
ShortName = test.sheets()[0].cell(1,3).value
Driver = test.sheets()[0].cell(1,4).value
Model = test.sheets()[0].cell(1,5).value
Desc = test.sheets()[0].cell(1,2).value
Server = test.sheets()[0].cell(1,1).value
f.write('VERSION = "0.1"')
f.write('\n' 'TIME = "20201015111814"')
f.write('\n' 'SYSTEM = "PR0"')
f.write('\n' 'RELEASE = "750"')
f.write('\n' '* DEVICE = {')
f.write('\n' ' NAME = "' PrinterName '"')
f.write('\n' ' PADEST = "' ShortName '"')
f.write('\n' ' PATYPE = "' Driver '"')
f.write('\n' ' PAMODEL = "' Model '"')
f.write('\n' ' PACLASS = ""')
f.write('\n' ' PAARCHIVER = ""')
f.write('\n' ' PALANGU = ""')
f.write('\n' ' PADISABLED = ""') #second file needs to have X here
f.write('\n' ' PANOQUERY = ""')
f.write('\n' ' PADUPCOPY = ""')
f.write('\n' ' PADUPLEX = ""')
f.write('\n' ' PASTORELOC = ""')
f.write('\n' ' PADFLTUTTL = ""')
f.write('\n' ' PADFLTSTTL = ""')
f.write('\n' ' PASYNC = ""')
f.write('\n' ' PAMONI = ""')
f.write('\n' ' PASTANDORT = "' Desc '"')
f.write('\n' ' PAMSG = "' Desc '"')
f.write('\n' ' PAMSSERVER = "SERVER_0"')
f.write('\n' ' PAMETHOD = "C"')
f.write('\n' ' PAPROSNAME = "\\\\' Server "\\" PrinterName '"')
f.write('\n' ' PALOMS = ""')
f.write('\n' ' PALPRCMD = ""')
f.write('\n' ' PALPDHOST = ""')
f.write('\n' ' PALPDPORT = "0"')
f.write('\n' ' PACONNTIME = "0"')
f.write('\n' ' PAREADTIME = "0"')
f.write('\n' ' PATRAYS = "0"')
f.write('\n' ' PAXSHIFT = "0"')
f.write('\n' ' PAYSHIFT = "0"')
f.write('\n' ' PAXSHUNIT = "MM"')
f.write('\n' ' PAYSHUNIT = "MM"')
f.write('\n' ' PACRYPTMOD = ""')
f.write('\n' ' PACRYPTMET = ""')
f.write('\n' ' PANOVSERVR = ""')
f.write('\n' ' PAPOOLART = "P"')
f.write('\n' ' PATRACE2 = ""')
f.write('\n' ' PATRACEF = ""')
f.write('\n' ' PAPROTDATA = ""')
f.write('\n' ' PAPROTCMD = ""')
f.write('\n' ' PAPROTRES = ""')
f.write('\n' ' PAKEEPFILE = ""')
f.write('\n' ' CHGNAME1 = ""')
f.write('\n' ' CHGSAPREL1 = "750"')
f.write('\n' ' CHGSAPSYS1 = "PR0"')
f.write('\n' ' PADEVGRP = ""')
f.write('\n' ' COLORPRT = "X"')
f.write('\n' ' PRINTMODE = ""')
f.write('\n' ' INPUTTRAY = ""')
f.write('\n' ' PANOCHOICE = ""')
f.write('\n' '}')
CodePudding user response：
The following code works fine on my computer after creating an Excel file with your sample data. Here's what I changed to make it work: The second for loop wasn't necessary so I removed it and outdented the code that was formerly in that loop. I changed the range() invocation to start at one and go one past the number of rows since the row numbers start at zero in xlrd. And I changed the .cell method calls to use the row variable instead of always getting values from row one.
import xlrd
xlsfilename='Testing.xls'
test = xlrd.open_workbook(xlsfilename)
number_subjetcs=4 #will update this to require user input based on number of rows, test file has 4
number_columns=6 #is static
for row in range(1, number_subjetcs 1):
txtfilename = 'testfile' str(row - 1) '.txt'
with open(txtfilename, "w") as f:
PrinterName = test.sheets()[0].cell(row, 0).value
ShortName = test.sheets()[0].cell(row, 3).value
Driver = test.sheets()[0].cell(row, 4).value
Model = test.sheets()[0].cell(row, 5).value
Desc = test.sheets()[0].cell(row, 2).value
Server = test.sheets()[0].cell(row, 1).value
f.write('VERSION = "0.1"')
f.write('\n' 'TIME = "20201015111814"')
f.write('\n' 'SYSTEM = "PR0"')
f.write('\n' 'RELEASE = "750"')
f.write('\n' '* DEVICE = {')
f.write('\n' ' NAME = "' PrinterName '"')
f.write('\n' ' PADEST = "' ShortName '"')
f.write('\n' ' PATYPE = "' Driver '"')
f.write('\n' ' PAMODEL = "' Model '"')
f.write('\n' ' PACLASS = ""')
f.write('\n' ' PAARCHIVER = ""')
f.write('\n' ' PALANGU = ""')
f.write('\n' ' PADISABLED = ""') #second file needs to have X here
f.write('\n' ' PANOQUERY = ""')
f.write('\n' ' PADUPCOPY = ""')
f.write('\n' ' PADUPLEX = ""')
f.write('\n' ' PASTORELOC = ""')
f.write('\n' ' PADFLTUTTL = ""')
f.write('\n' ' PADFLTSTTL = ""')
f.write('\n' ' PASYNC = ""')
f.write('\n' ' PAMONI = ""')
f.write('\n' ' PASTANDORT = "' Desc '"')
f.write('\n' ' PAMSG = "' Desc '"')
f.write('\n' ' PAMSSERVER = "SERVER_0"')
f.write('\n' ' PAMETHOD = "C"')
f.write('\n' ' PAPROSNAME = "\\\\' Server "\\" PrinterName '"')
f.write('\n' ' PALOMS = ""')
f.write('\n' ' PALPRCMD = ""')
f.write('\n' ' PALPDHOST = ""')
f.write('\n' ' PALPDPORT = "0"')
f.write('\n' ' PACONNTIME = "0"')
f.write('\n' ' PAREADTIME = "0"')
f.write('\n' ' PATRAYS = "0"')
f.write('\n' ' PAXSHIFT = "0"')
f.write('\n' ' PAYSHIFT = "0"')
f.write('\n' ' PAXSHUNIT = "MM"')
f.write('\n' ' PAYSHUNIT = "MM"')
f.write('\n' ' PACRYPTMOD = ""')
f.write('\n' ' PACRYPTMET = ""')
f.write('\n' ' PANOVSERVR = ""')
f.write('\n' ' PAPOOLART = "P"')
f.write('\n' ' PATRACE2 = ""')
f.write('\n' ' PATRACEF = ""')
f.write('\n' ' PAPROTDATA = ""')
f.write('\n' ' PAPROTCMD = ""')
f.write('\n' ' PAPROTRES = ""')
f.write('\n' ' PAKEEPFILE = ""')
f.write('\n' ' CHGNAME1 = ""')
f.write('\n' ' CHGSAPREL1 = "750"')
f.write('\n' ' CHGSAPSYS1 = "PR0"')
f.write('\n' ' PADEVGRP = ""')
f.write('\n' ' COLORPRT = "X"')
f.write('\n' ' PRINTMODE = ""')
f.write('\n' ' INPUTTRAY = ""')
f.write('\n' ' PANOCHOICE = ""')
f.write('\n' '}')