trjhtr

I wrote this to parse through an Excel doc, extract desired columns, parse certain cells and output another formatted Excel doc.

This is the original Excel doc I have condensed most of the columns for space

Here are the the results of the program that I am mostly happy with, it parses the name column and writes a unit identifier(i.e 83) along with a tower identifier(i.e 5) on a new line along with a completed date if the completed column is TRUE. I think the only thing I would change would be getting rid of the blank line under the row that has H 7

And here is the code

import xlrd
import xlwt
import re

# open and create workbook
workbookread = xlrd.open_workbook('seedtestexcelbytask.xls')
workbookwrite = xlwt.Workbook('output.xlsx')

# open and create sheets
worksheetread = workbookread.sheet_by_index(0)
worksheetwrite = workbookwrite.add_sheet('Sheet1')

timelist = 
towerlist = 

i = 0
l = 0
t = 0

# iterate through rows in worksheet, get columns completed, completed_at and name.
# add to timelist and towerlist if completed == true
for rows in range(worksheetread.nrows):
 cell0 = worksheetread.cell(rows, 2).value
 cell1 = worksheetread.cell(rows, 3).value
 cell2 = worksheetread.cell(rows, 15).value

 if cell0 == 1:
 parsedvalue = re.split(', |/', cell2)
 timelist.append(cell1)
 towerlist.append(parsedvalue)

b = 2
g = 1
for rows in range(len(timelist)):
 row = worksheetwrite.row(i)

# get current index from timelist and towerlist and assign to currenttime and currentvalue

 currenttime = timelist[t]
 currentname = towerlist[t]

# get the length of the current index this will help us decide how to parse
 length = len(towerlist[t])

# if length <= 2 the index is not formatted properly so we only need index 1 which is the cafe and towers together
 if length <= 2:
 row.write(0, currenttime)
 row.write(1, currentname[1])
 i += 1
 l += 1

# if length = 4 index is formatted properly and we just grab the columns we need
 elif length == 4:
 a = 2

 for values in range(0, 2):
 row = worksheetwrite.row(l)
 row.write(0, currenttime)
 row.write(1, currentname[1])
 row.write(2, currentname[a])
 a += 1
 i += 1
 l += 1

# if length > 4 we need to iterate through each index and write them
 else:

 # define the length of the current cell of the excel sheet we have
 lengthd = (len(currentname)+1)/2
 length = int(lengthd)

 for values in range(length):
 # set variables that we will need to hold on to
 currentcafe = currentname[g]
 currenttower = currentname[b]

 # if the value of the current tower is 2,4,5,6,7 we need to write the current cafe and current tower
 # then move on to the next value and set that as the current cafe, we also need to jump to the
 # value after the current cafe and set that as the current tower
 if currenttower == "2" or currenttower == "4" or currenttower == "5" or currenttower == "6" or currenttower == "7":
 row = worksheetwrite.row(l)
 row.write(0, currenttime)
 row.write(1, currentcafe)
 row.write(2, currenttower)

 b += 1
 i += 1
 g = b
 b = g + 1
 # otherwise we can just write the value fo the current cafe and current tower, advance to the next value
 # which is another tower and set current tower to that
 else:
 row = worksheetwrite.row(l)
 row.write(0, currenttime)
 row.write(1, currentcafe)
 row.write(2, currenttower)
 b += 1
 i += 1

 l += 1
 i += 1
 t += 1
workbookwrite.save('output.xls')

This is the first time I have written anything with Python and I am fairly new to programming, any comments would be greatly appreciated.