3
\$\begingroup\$

I am new to Python and am looking for any feedback/criticism on scripts I have written for work. This script works as I intended, creating multiple .xls files from a workbook with multiple worksheets. I'm just looking to develop a better understanding of how to use Python. So, please, feel free to rip in to what I've done!

import xlrd
import xlwt
from xlutils.copy import copy
from xlwt import Workbook, easyxf
from xlrd import open_workbook,XL_CELL_TEXT
import re
import csv
import os

#######################################################################################
def create_del_note():
    #Define output workbook styles
    font_size_style1 = xlwt.easyxf('font: name Calibri, bold off, height 220;''borders: left medium, right medium, top medium, bottom medium;')
    font_size_style2 = xlwt.easyxf('font: name Calibri, bold off, height 220;''borders: left medium, right medium, top medium, bottom medium;''alignment: horizontal right, vertical centre;')
    font_size_style3 = xlwt.easyxf('font: name Calibri, bold off, height 220;''borders: left medium, right medium, top medium, bottom medium;''alignment: horizontal left, vertical centre;')
    font_size_style4 = xlwt.easyxf('font: name Calibri, bold off, height 220;''borders: left medium, right medium, top medium, bottom medium;''alignment: horizontal centre, vertical centre;')
    font_size_style5 = xlwt.easyxf('font: name Calibri, bold off, height 220;''borders: left medium, right medium, top medium;''alignment: horizontal centre, vertical centre;')
    font_size_style6 = xlwt.easyxf('font: name Calibri, bold off, height 220;''borders: left medium, right medium;''alignment: horizontal centre, vertical centre;')
    font_size_style7 = xlwt.easyxf('font: name Calibri, bold off, height 220;''borders: left medium, right medium, bottom medium;''alignment: horizontal centre, vertical centre;')
    font_size_style_title = xlwt.easyxf('font: name Calibri, italic on, height 360;''alignment: horizontal centre, vertical centre;')

    #Insert IT Services image
    del_note.insert_bitmap('ITSERVICES.bmp', 0, 1)

    #Create text & fields
    del_note.write_merge(r1=7, c1=2, r2=7, c2=6,label="DELIVERY NOTE",style=font_size_style_title)
    del_note.write_merge(r1=9, c1=0, r2=9, c2=3,label="Delivery Address",style=font_size_style3)
    del_note.write_merge(r1=10, c1=0, r2=10, c2=3,style=font_size_style5)
    del_note.write_merge(r1=11, c1=0, r2=11, c2=3,style=font_size_style6)
    del_note.write_merge(r1=12, c1=0, r2=12, c2=3,style=font_size_style6)
    del_note.write_merge(r1=13, c1=0, r2=13, c2=3,style=font_size_style6)
    del_note.write_merge(r1=14, c1=0, r2=14, c2=3,style=font_size_style6)
    del_note.write_merge(r1=15, c1=0, r2=15, c2=3,style=font_size_style6)
    del_note.write_merge(r1=16, c1=0, r2=16, c2=3,style=font_size_style6)
    del_note.write_merge(r1=17, c1=0, r2=17, c2=3,style=font_size_style7)
    del_note.write_merge(r1=9, c1=5, r2=9, c2=6,label="Shipment Date:",style=font_size_style2)
    del_note.write_merge(r1=9, c1=7, r2=9, c2=9,style=font_size_style4)
    del_note.write_merge(r1=11, c1=5, r2=11, c2=6,label="Consignment Type:",style=font_size_style2)
    del_note.write_merge(r1=11, c1=7, r2=11, c2=9,style=font_size_style4)
    del_note.write_merge(r1=13, c1=5, r2=13, c2=6,label="Your Ref:",style=font_size_style2)
    del_note.write_merge(r1=13, c1=7, r2=13, c2=9,style=font_size_style4)
    del_note.write_merge(r1=15, c1=5, r2=15, c2=6,label="Our Ref:",style=font_size_style2)
    del_note.write_merge(r1=15, c1=7, r2=15, c2=9,style=font_size_style4)
    del_note.write_merge(r1=17, c1=5, r2=17, c2=6,label="FAO:",style=font_size_style2)
    del_note.write_merge(r1=17, c1=7, r2=17, c2=9,style=font_size_style4)

    del_note.write_merge(r1=19, c1=0, r2=19, c2=1,label="Quantity",style=font_size_style4)
    del_note.write_merge(r1=20, c1=0, r2=20, c2=1,style=font_size_style5)
    del_note.write_merge(r1=21, c1=0, r2=21, c2=1,style=font_size_style6)
    del_note.write_merge(r1=22, c1=0, r2=22, c2=1,style=font_size_style6)
    del_note.write_merge(r1=23, c1=0, r2=23, c2=1,style=font_size_style6)
    del_note.write_merge(r1=24, c1=0, r2=24, c2=1,style=font_size_style6)
    del_note.write_merge(r1=25, c1=0, r2=25, c2=1,style=font_size_style6)
    del_note.write_merge(r1=26, c1=0, r2=26, c2=1,style=font_size_style6)
    del_note.write_merge(r1=27, c1=0, r2=27, c2=1,style=font_size_style6)
    del_note.write_merge(r1=28, c1=0, r2=28, c2=1,style=font_size_style6)
    del_note.write_merge(r1=29, c1=0, r2=29, c2=1,style=font_size_style6)
    del_note.write_merge(r1=30, c1=0, r2=30, c2=1,style=font_size_style6)
    del_note.write_merge(r1=31, c1=0, r2=31, c2=1,style=font_size_style6)
    del_note.write_merge(r1=32, c1=0, r2=32, c2=1,style=font_size_style6)
    del_note.write_merge(r1=33, c1=0, r2=33, c2=1,style=font_size_style6)
    del_note.write_merge(r1=34, c1=0, r2=34, c2=1,style=font_size_style6)
    del_note.write_merge(r1=35, c1=0, r2=35, c2=1,style=font_size_style6)
    del_note.write_merge(r1=36, c1=0, r2=36, c2=1,style=font_size_style6)
    del_note.write_merge(r1=37, c1=0, r2=37, c2=1,style=font_size_style6)
    del_note.write_merge(r1=38, c1=0, r2=38, c2=1,style=font_size_style6)
    del_note.write_merge(r1=39, c1=0, r2=39, c2=1,style=font_size_style6)
    del_note.write_merge(r1=40, c1=0, r2=40, c2=1,style=font_size_style6)
    del_note.write_merge(r1=41, c1=0, r2=41, c2=1,style=font_size_style6)
    del_note.write_merge(r1=42, c1=0, r2=42, c2=1,style=font_size_style6)
    del_note.write_merge(r1=43, c1=0, r2=43, c2=1,style=font_size_style6)
    del_note.write_merge(r1=44, c1=0, r2=44, c2=1,style=font_size_style6)
    del_note.write_merge(r1=45, c1=0, r2=45, c2=1,style=font_size_style6)
    del_note.write_merge(r1=46, c1=0, r2=46, c2=1,style=font_size_style7)

    del_note.write_merge(r1=19, c1=2, r2=19, c2=5,label="Description",style=font_size_style4)
    del_note.write_merge(r1=20, c1=2, r2=20, c2=5,style=font_size_style5)
    del_note.write_merge(r1=21, c1=2, r2=21, c2=5,style=font_size_style6)
    del_note.write_merge(r1=22, c1=2, r2=22, c2=5,style=font_size_style6)
    del_note.write_merge(r1=23, c1=2, r2=23, c2=5,style=font_size_style6)
    del_note.write_merge(r1=24, c1=2, r2=24, c2=5,style=font_size_style6)
    del_note.write_merge(r1=25, c1=2, r2=25, c2=5,style=font_size_style6)
    del_note.write_merge(r1=26, c1=2, r2=26, c2=5,style=font_size_style6)
    del_note.write_merge(r1=27, c1=2, r2=27, c2=5,style=font_size_style6)
    del_note.write_merge(r1=28, c1=2, r2=28, c2=5,style=font_size_style6)
    del_note.write_merge(r1=29, c1=2, r2=29, c2=5,style=font_size_style6)
    del_note.write_merge(r1=30, c1=2, r2=30, c2=5,style=font_size_style6)
    del_note.write_merge(r1=31, c1=2, r2=31, c2=5,style=font_size_style6)
    del_note.write_merge(r1=32, c1=2, r2=32, c2=5,style=font_size_style6)
    del_note.write_merge(r1=33, c1=2, r2=33, c2=5,style=font_size_style6)
    del_note.write_merge(r1=34, c1=2, r2=34, c2=5,style=font_size_style6)
    del_note.write_merge(r1=35, c1=2, r2=35, c2=5,style=font_size_style6)
    del_note.write_merge(r1=36, c1=2, r2=36, c2=5,style=font_size_style6)
    del_note.write_merge(r1=37, c1=2, r2=37, c2=5,style=font_size_style6)
    del_note.write_merge(r1=38, c1=2, r2=38, c2=5,style=font_size_style6)
    del_note.write_merge(r1=39, c1=2, r2=39, c2=5,style=font_size_style6)
    del_note.write_merge(r1=40, c1=2, r2=40, c2=5,style=font_size_style6)
    del_note.write_merge(r1=41, c1=2, r2=41, c2=5,style=font_size_style6)
    del_note.write_merge(r1=42, c1=2, r2=42, c2=5,style=font_size_style6)
    del_note.write_merge(r1=43, c1=2, r2=43, c2=5,style=font_size_style6)
    del_note.write_merge(r1=44, c1=2, r2=44, c2=5,style=font_size_style6)
    del_note.write_merge(r1=45, c1=2, r2=45, c2=5,style=font_size_style6)
    del_note.write_merge(r1=46, c1=2, r2=46, c2=5,style=font_size_style7)

    del_note.write_merge(r1=19, c1=6, r2=19, c2=9,label="Serial Number",style=font_size_style4)
    del_note.write_merge(r1=20, c1=6, r2=20, c2=9,style=font_size_style5)
    del_note.write_merge(r1=21, c1=6, r2=21, c2=9,style=font_size_style6)
    del_note.write_merge(r1=22, c1=6, r2=22, c2=9,style=font_size_style6)
    del_note.write_merge(r1=23, c1=6, r2=23, c2=9,style=font_size_style6)
    del_note.write_merge(r1=24, c1=6, r2=24, c2=9,style=font_size_style6)
    del_note.write_merge(r1=25, c1=6, r2=25, c2=9,style=font_size_style6)
    del_note.write_merge(r1=26, c1=6, r2=26, c2=9,style=font_size_style6)
    del_note.write_merge(r1=27, c1=6, r2=27, c2=9,style=font_size_style6)
    del_note.write_merge(r1=28, c1=6, r2=28, c2=9,style=font_size_style6)
    del_note.write_merge(r1=29, c1=6, r2=29, c2=9,style=font_size_style6)
    del_note.write_merge(r1=30, c1=6, r2=30, c2=9,style=font_size_style6)
    del_note.write_merge(r1=31, c1=6, r2=31, c2=9,style=font_size_style6)
    del_note.write_merge(r1=32, c1=6, r2=32, c2=9,style=font_size_style6)
    del_note.write_merge(r1=33, c1=6, r2=33, c2=9,style=font_size_style6)
    del_note.write_merge(r1=34, c1=6, r2=34, c2=9,style=font_size_style6)
    del_note.write_merge(r1=35, c1=6, r2=35, c2=9,style=font_size_style6)
    del_note.write_merge(r1=36, c1=6, r2=36, c2=9,style=font_size_style6)
    del_note.write_merge(r1=37, c1=6, r2=37, c2=9,style=font_size_style6)
    del_note.write_merge(r1=38, c1=6, r2=38, c2=9,style=font_size_style6)
    del_note.write_merge(r1=39, c1=6, r2=39, c2=9,style=font_size_style6)
    del_note.write_merge(r1=40, c1=6, r2=40, c2=9,style=font_size_style6)
    del_note.write_merge(r1=41, c1=6, r2=41, c2=9,style=font_size_style6)
    del_note.write_merge(r1=42, c1=6, r2=42, c2=9,style=font_size_style6)
    del_note.write_merge(r1=43, c1=6, r2=43, c2=9,style=font_size_style6)
    del_note.write_merge(r1=44, c1=6, r2=44, c2=9,style=font_size_style6)
    del_note.write_merge(r1=45, c1=6, r2=45, c2=9,style=font_size_style6)
    del_note.write_merge(r1=46, c1=6, r2=46, c2=9,style=font_size_style6)

    del_note.write_merge(r1=47, c1=0, r2=48, c2=9,style=font_size_style4)
#######################################################################################
#Specify Spreadsheet containing data to be imported
fin = 'Pre-stage.xlsx'

#Define input Excel workbook
workbook_in = xlrd.open_workbook(fin)
#Create dictionary to store address
address_dict = {}

#Iterate through worksheets
for s in workbook_in.sheets():
    #Open output workbook snd activate worksheet
    workbook_out = Workbook()
    del_note = workbook_out.add_sheet('Delivery Note',cell_overwrite_ok=True)
    store = s.name
    worksheet = workbook_in.sheet_by_name(store)
    #Pick data out of spreadsheet
    store_name = worksheet.cell(0,1)
    store_address_cell = s.cell(1,1)
    store_code = worksheet.cell(3,1)
    store_address = re.split("[,.]",store_address_cell.value)
    i = 1
    #Load address into dictionary
    address_dict = {}
    for address_line in store_address:
        address_dict[i] = address_line
        i += 1
    #Define devices in each worksheet, where devices5 is a list of devices
    devices1 = worksheet.col_values(4,6,15)
    devices2 = worksheet.col_values(1,17,36)
    devices3 = list(set(devices1))
    devices3.remove('')
    devices4 = list(set(devices2))
    devices5 = devices3 + devices4
    #Save sheet as csv file
    with open(store + '-temp.csv', 'w') as csvfile1:
        c = csv.writer(csvfile1)
        for r in range(worksheet.nrows):
            c.writerow(worksheet.row_values(r))
    #######################################################################################
    #Call delivery note creation function
    print '\n\tCreating %s (%s) delivery note...\n' % (store_name.value, store_code.value)
    create_del_note()
    #Write store name and address to delivery note
    del_note.write(10,0,store_name.value)
    del_note.write(11,0,address_dict.get(1))
    del_note.write(12,0,address_dict.get(2))
    del_note.write(13,0,address_dict.get(3))
    del_note.write(14,0,address_dict.get(4))
    del_note.write(15,0,address_dict.get(5))
    del_note.write(16,0,address_dict.get(6))
    del_note.write(17,0,store_code.value,easyxf('borders: bottom medium;'))
    #Parse csv file for device and serial data
    device1 = devices5[0]
    device2 = devices5[1]
    device3 = None
    if len(devices5) > 3:
        device3 = devices5[2]
    device4 = devices5[-1]
    #Create empty device lists
    device1_list = []
    device2_list = []
    device3_list = []
    device4_list = []
    #Build device lists
    with open(store + '-temp.csv', 'r') as csvfile2:
        lines = csvfile2.readlines()
        for line in lines:
            if device1 in line and (line.split(',')[-2] != ''):
                device1_list.append(line.split(',')[-2])
            elif device2 in line and (line.split(',')[-2] != ''):
                device2_list.append(line.split(',')[-2])
            elif (len(devices5) > 3) and device3 in line and (line.split(',')[-2] != ''):
                device3_list.append(line.split(',')[-2])
            elif device4 in line and (line.split(',')[2] != ''):
                device4_list.append(line.split(',')[2])
    #Write device lists to excel file
    font_style_data = xlwt.easyxf('alignment: horizontal centre, vertical centre;')
    font_style_data_bold = xlwt.easyxf('font:bold on;''alignment: horizontal centre, vertical centre;')
    i = 20
    j = i + len(device1_list)
    k = j + len(device2_list)
    l = k + len(device3_list)
    total = len(device1_list) + len(device2_list) + len(device3_list) + len(device4_list)
    del_note.write(i,0,len(device1_list),font_style_data)
    del_note.write(i,2,device1,font_style_data)
    for item in device1_list:
        del_note.write(i,6,item,font_style_data)
        i += 1
    del_note.write(j,0,len(device2_list),font_style_data)
    del_note.write(j,2,device2,font_style_data)
    for item in device2_list:
        del_note.write(j,6,item,font_style_data)
        j += 1
    del_note.write(k,0,len(device3_list),font_style_data)
    del_note.write(k,2,device3,font_style_data)
    for item in device3_list:
        del_note.write(k,6,item,font_style_data)
        k += 1
    del_note.write(l,0,len(device4_list),font_style_data)
    del_note.write(l,2,device4,font_style_data)
    for item in device4_list:
        del_note.write(l,6,item,font_style_data)
        l += 1
    total_boxes = '%d BOXES IN TOTAL' % total
    del_note.write(47,0,total_boxes,font_style_data)

    #Delete temp csv files
    os.remove(store + '-temp.csv')

    #Save excel file
    filepath = '/home/robphoenix/Host/delivery_note/DELIVERYNOTES/'
    workbook_out.save(filepath + store_code.value + '-' + total_boxes + '.xls')
\$\endgroup\$
0

2 Answers 2

1
\$\begingroup\$

The font style variables should either be given descriptive names or placed into a collection. Having a set of variables that are suffixed with a number is almost never the correct option. I would prefer descriptive names so that a reader doesn't need to scroll to the top of the file to see what font_size_style6 means. (This is not the only instance of this issue.)

The first 7 styles all start with the same description. The only thing that differs is at the end. This makes it hard for the reader to find the important part of the string, the part that makes this style different than the others. It also makes a very long line shorter, which also helps readability.

common_style = 'font: name Calibri, bold off, height 220;''borders: left medium, right medium'
    font_size_style1 = xlwt.easyxf(common_style + ', top medium, bottom medium;')
    font_size_style2 = xlwt.easyxf(common_style + ', top medium, bottom medium;''alignment: horizontal right, vertical centre;')
    font_size_style3 = xlwt.easyxf(common_style + ', top medium, bottom medium;''alignment: horizontal left, vertical centre;')
    font_size_style4 = xlwt.easyxf(common_style + ', top medium, bottom medium;''alignment: horizontal centre, vertical centre;')
    font_size_style5 = xlwt.easyxf(common_style + ', top medium;''alignment: horizontal centre, vertical centre;')
    font_size_style6 = xlwt.easyxf(common_style + ';''alignment: horizontal centre, vertical centre;')
    font_size_style7 = xlwt.easyxf(common_style + ', bottom medium;''alignment: horizontal centre, vertical centre;')

There are a lot of write_merge() calls that only differ from the one above it by the r1 and r2 arguments. In addition, these values are incrementing. This is a perfect example of when a for loop can be used.

for row in range(11, 18):
    del_note.write_merge(r1=row, c1=0, r2=row, c2=3,style=font_size_style6)

In fact, this same pattern is used multiple times, You can wrap this in a function that takes c1, c2, style and the start and stop rows. This will almost reduce the number of lines of this code in half. On top of that, it will make it much easier if you need to change the end from 17 to 20. With this, you change one number. As the code is now, you have to copy the line three more times and hope you don't make a mistake when updating the arguments.

Similarly to the above comment, since these lines look so similar to recognize that r2 was also incrementing. When someone is quickly reading code they have not seen before, they will assume that everything is the same once they have noticed a pattern, even if the pattern is slightly more complex.


create_del_note() is accessing a global variable. If someone were to change the variable name the rest of the code would break and it would not be obvious to the person making the change. It would be better to pass in the worksheet to manipulate as an argument instead.

Additionally, the function name is unclear. It is not creating the delivery note worksheet, it is formating the sheet so that it could take the appropriate values.


devices1 = worksheet.col_values(4,6,15)
devices2 = worksheet.col_values(1,17,36)
devices3 = list(set(devices1))
devices3.remove('')
devices4 = list(set(devices2))
devices5 = devices3 + devices4

This is another case with bad variable names. devices1 and devices2 clearly represent specific sets. What distinguishes them from each other? Put that in the variable name so the reader has some context. devices3 and devices4 are just temporary variables and serve no purpose. This is another case where you could write one function and use it to create both device lists. In the end, you only care about devices5. Maybe you don't need names for devices1 and devices2, just add up the results of the two function calls.


(line.split(',')[-2] != '')

This is a complicated operation that is done multiple times and means nothing to the reader. Extracting it to a function and giving the function a descriptive name will allow a reader to understand what is being checked without having to decipher what is happening.


Move configuration variables (like file paths) to the beginning where they are easily found and changed. Don't make it hard to run the code on a computer that doesn't have exactly the same folder structure.


Summary

  • Use better variable names
  • Extract common code into sub-functions with descriptive names.
  • Stay away from repeated lines that look very similar, but have subtle differences. Take steps to extract away the common points so that the differences are more apparent.
\$\endgroup\$
1
  • \$\begingroup\$ Thankyou @unholysampler that's really helpful, and makes total sense. I'll read through this again, process it and start making changes. I appreciate you taking the time to point these things out. \$\endgroup\$ Commented Dec 22, 2014 at 16:56
0
\$\begingroup\$

In response to the helpful comments from @unholysampler here is the updated code:

#!/usr/bin/env python

'''
Title:      Delivery Note Creation
Usage:      Creates multiple excel workbooks, each one a delivery note 
            based on each worksheet in a main inventory database excel workbook.
            Copies over store name, address & code; lists the serial numbers
            of every device shipping to each store, grouped by model name; and 
            lists the quantities of each model and the total number of boxes shipping.
Date:       23/12/2014
Version:    2.0
''' 

import re
import csv
import os
import xlrd
import xlwt
from xlwt import Workbook, easyxf
from xlrd import open_workbook,XL_CELL_TEXT

#Ask user to specify Excel workbook to create delivery notes from
fin = raw_input('\n\tPlease enter filename: ')

#Open input Excel workbook
workbook_in = xlrd.open_workbook(fin)

#Define output workbook styles
common_style = 'font: name Calibri, bold off, height 220;''borders: left medium, right medium'
all_borders_right_align = xlwt.easyxf(common_style + ', top medium, bottom medium;''alignment: horizontal right, vertical centre;')
all_borders_left_align = xlwt.easyxf(common_style + ', top medium, bottom medium;''alignment: horizontal left, vertical centre;')
all_borders_centre_align = xlwt.easyxf(common_style + ', top medium, bottom medium;''alignment: horizontal centre, vertical centre;')
left_and_right_border_left_align = xlwt.easyxf(common_style + ';''alignment: horizontal left, vertical centre;')
delivery_note_title = xlwt.easyxf('font: name Calibri, italic on, height 360;''alignment: horizontal centre, vertical centre;')

def format_delivery_note(deliverynote): 
    #Insert IT Services image
    deliverynote.insert_bitmap('ITSERVICES.bmp', 0, 1)
    #Create text & fields (row1,row2,col1,col2)
    deliverynote.write_merge(7,7,2,6,"DELIVERY NOTE",delivery_note_title)
    deliverynote.write_merge(9,9,0,3,"Delivery Address",all_borders_left_align)
    deliverynote.write_merge(9,9,5,6,"Shipment Date:",all_borders_right_align)
    deliverynote.write_merge(9,9,7,9,"",all_borders_centre_align)
    deliverynote.write_merge(11,11,5,6,"Consignment Type:",all_borders_right_align)
    deliverynote.write_merge(11,11,7,9,"",all_borders_centre_align)
    deliverynote.write_merge(13,13,5,6,"Your Ref:",all_borders_right_align)
    deliverynote.write_merge(13,13,7,9,"",all_borders_centre_align)
    deliverynote.write_merge(15,15,5,6,"Our Ref:",all_borders_right_align)
    deliverynote.write_merge(15,15,7,9,"",all_borders_centre_align)
    deliverynote.write_merge(17,17,5,6,"FAO:",all_borders_right_align)
    deliverynote.write_merge(17,17,7,9,"",all_borders_centre_align)
    deliverynote.write_merge(19,19,0,1,"Quantity",all_borders_centre_align)
    deliverynote.write_merge(19,19,2,5,"Description",all_borders_centre_align)
    deliverynote.write_merge(19,19,6,9,"Serial Number",all_borders_centre_align)

def main():
    #Iterate through worksheets
    for sheet in workbook_in.sheets():
        store = sheet.name
        worksheet = workbook_in.sheet_by_name(store)
        #Grab data from input workbook
        store_name = worksheet.cell(0,1)
        store_address_cell = sheet.cell(1,1)
        store_code = worksheet.cell(3,1)
        store_address = re.split("[,.]",store_address_cell.value)
        #Create dictionary to store address
        address_dict = {}
        #Load address into dictionary
        i = 1
        for address_line in store_address:
            address_dict[i] = address_line
            i += 1
        #Define devices in each worksheet, where all_devices is a list of devices
        switch_col = worksheet.col_values(4,6,15)
        ap_col = worksheet.col_values(1,17,36)
        list_of_switches = list(set(switch_col))
        list_of_switches.remove('')
        list_of_aps = list(set(ap_col))
        all_devices = list_of_switches + list_of_aps
        #Save sheet as temporary csv file for easier data collection
        with open(store + '-temp.csv', 'w') as csv_in:
            csv_temp = csv.writer(csv_in)
            for row in range(worksheet.nrows):
                csv_temp.writerow(worksheet.row_values(row))
        #Parse csv file for device and serial data
        device1 = all_devices[0]
        device2 = all_devices[1]
        device3 = None
        if len(all_devices) > 3:
            device3 = all_devices[2]
        device4 = all_devices[-1]
        #Create empty device lists, where the name of each list is a different device model
        device1_list = []
        device2_list = []
        device3_list = []
        device4_list = []
        #Build device lists with device serial numbers
        with open(store + '-temp.csv', 'r') as csv_out:
            lines = csv_out.readlines()
            for line in lines:
                if device1 in line and (line.split(',')[-2] != ''):
                    device1_list.append(line.split(',')[-2])
                elif device2 in line and (line.split(',')[-2] != ''):
                    device2_list.append(line.split(',')[-2])
                elif (len(all_devices) > 3) and device3 in line and (line.split(',')[-2] != ''):
                    device3_list.append(line.split(',')[-2])
                elif device4 in line and (line.split(',')[2] != ''):
                    device4_list.append(line.split(',')[2])
        #Show progress to user
        print '\n\tCreating %s (%s) delivery note...\n' % (store_name.value, store_code.value)
        #Create delivery note workbook
        workbook_out = Workbook()
        del_note = workbook_out.add_sheet('Delivery Note',cell_overwrite_ok=True)
        #Format delivery note workbook
        format_delivery_note(del_note)
        #Write store name and address to delivery note
        store_code_statement = 'Store code: %s' % store_code.value
        del_note.write_merge(10,10,0,3,store_name.value,left_and_right_border_left_align)
        del_note.write_merge(11,11,0,3,address_dict.get(1),left_and_right_border_left_align)
        del_note.write_merge(12,12,0,3,address_dict.get(2),left_and_right_border_left_align)
        del_note.write_merge(13,13,0,3,address_dict.get(3),left_and_right_border_left_align)
        del_note.write_merge(14,14,0,3,address_dict.get(4),left_and_right_border_left_align)
        del_note.write_merge(15,15,0,3,address_dict.get(5),left_and_right_border_left_align)
        del_note.write_merge(16,16,0,3,address_dict.get(6),left_and_right_border_left_align)
        del_note.write_merge(17,17,0,3,store_code_statement,all_borders_centre_align)
        #Specify device quantities
        i = 20
        j = i + len(device1_list)
        k = j + len(device2_list)
        l = k + len(device3_list)
        total_no_of_devices = len(device1_list) + len(device2_list) + len(device3_list) + len(device4_list)
        #write device model names to delivery note
        del_note.write_merge(i,i,2,5,device1,all_borders_centre_align)
        del_note.write_merge(j,j,2,5,device2,all_borders_centre_align)
        del_note.write_merge(k,k,2,5,device3,all_borders_centre_align)
        del_note.write_merge(l,l,2,5,device4,all_borders_centre_align)
        #Write device quantities to delivery note
        del_note.write_merge(i,i,0,1,len(device1_list),all_borders_centre_align)
        del_note.write_merge(j,j,0,1,len(device2_list),all_borders_centre_align)
        del_note.write_merge(k,k,0,1,len(device3_list),all_borders_centre_align)
        del_note.write_merge(l,l,0,1,len(device4_list),all_borders_centre_align)
        #Write device lists to excel file
        for item in device1_list:
            del_note.write_merge(i,i,6,9,item,all_borders_centre_align)
            i += 1
        for item in device2_list:
            del_note.write_merge(j,j,6,9,item,all_borders_centre_align)
            j += 1
        for item in device3_list:
            del_note.write_merge(k,k,6,9,item,all_borders_centre_align)
            k += 1
        for item in device4_list:
            del_note.write_merge(l,l,6,9,item,all_borders_centre_align)
            l += 1
        #Write number of boxes to delivery note
        total_boxes = '%d BOXES IN TOTAL' % total_no_of_devices
        row1 = total_no_of_devices + 20
        row2 = row1 + 1
        del_note.write_merge(row1,row2,0,9,total_boxes,all_borders_centre_align)
        #Delete temp csv files
        os.remove(store + '-temp.csv')
        #Save excel file
        filepath = '~/DELIVERYNOTES/'
        workbook_out.save(filepath + store_code.value + '-' + total_boxes + '.xls')

if __name__ == '__main__':
    main()
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.