将多个excel工作表中的库存数据复制到单个excel交付说明中。

Question

我是Python新手，正在寻找对我为工作编写的脚本的任何反馈/批评。此脚本按照我的意愿工作，从包含多个工作表的工作簿中创建多个.xls文件。我只是想更好地理解如何使用Python。所以，请随便说说我所做的！

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

Answer 1

字体样式变量应该给出描述性名称，或者放在集合中。拥有一组以数字为后缀的变量几乎不是正确的选项。我更喜欢描述性的名称，这样读者就不需要滚动到文件的顶部来查看font_size_style6的意思了。(这不是这个问题的唯一实例。)

前7种样式都是以相同的描述开始的。唯一不同的地方是在最后。这使得读者很难找到字符串的重要部分，这部分使这种风格与其他部分不同。它还使很长的行更短，这也有助于可读性。

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

有许多write_merge()调用仅通过r1和r2参数与上面的调用不同。此外，这些值正在增加。这是一个什么时候可以使用for循环的完美例子。

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

实际上，这个相同的模式被多次使用，您可以将它包装在一个函数中，该函数接受c1、c2、style以及开始行和停止行。这将几乎将此代码的行数减少一半。最重要的是，如果你需要把结尾从17改为20，这会让你更容易。用这个，你会改变一个数字。正如现在的代码一样，您必须再复制这一行三次，并希望在更新参数时不要出错。

类似于上面的注释，因为这些行看起来非常类似于识别r2也在递增。当某人正在快速阅读他们以前从未见过的代码时，他们会假设一旦他们注意到一个模式，一切都是一样的，即使模式稍微复杂一些。

create_del_note()正在访问一个全局变量。如果有人要更改变量名，那么代码的其余部分就会中断，对进行更改的人来说，这是不明显的。最好将工作表作为参数进行操作。

此外，函数名还不清楚。它不是在创建交付说明工作表，而是在格式化工作表，以便它能够获得适当的值。

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

这是另一个变量名称错误的案例。devices1和devices2清楚地表示特定的集合。他们之间的区别是什么？把它放在变量名中，这样读者就有了一些上下文。devices3和devices4只是临时变量，没有用途。这是另一种情况，您可以编写一个函数并使用它创建两个设备列表。最后，您只关心devices5。也许您不需要devices1和devices2的名称，只需将两个函数调用的结果相加即可。

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

这是一个复杂的操作，多次进行，对读者毫无意义。将其提取到一个函数并给函数一个描述性的名称，将允许读者理解正在检查的内容，而不必破译正在发生的事情。

将配置变量(如文件路径)移至易于查找和更改的起始位置。不要让在没有完全相同文件夹结构的计算机上运行代码变得困难。

摘要

• 使用更好的变量名称
• 将通用代码提取到具有描述性名称的子函数中。
• 远离重复的线条，这些线条看起来非常相似，但有细微的差别。采取措施，去掉共同点，使差异更加明显。

Answer 2

针对@unholysampler的有益评论，下面是更新的代码：

#!/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()