# Open excel and create a worksheet with an array of data.  Sum the entries in each column.
# The worksheet remains active after this program terminates.
#  The basic concepts are from Roy H. Han

from win32com.client import Dispatch    #, constants
import win32con as con
import win32api, winsound
import time
import numpy as np

noise = True        # Beep after each entry if True.  Set to False if beeps are annoying.
sleep = 1.0
if noise: winsound.PlaySound("computer_on.wav",winsound.SND_FILENAME)

# Open excel
excel = Dispatch('Excel.application')
excel.visible = 1
#excel.windowState = con.WS_MAXIMIZE
excel.workbooks.Add()
sheet = excel.ActiveSheet
#x = np.arange(0, 1, 0.01)
#f = np.int32((np.sin(2.0*np.pi*x) + 1.0) * 5000) + 37
#if noise :
    #for ff in f :
        #win32api.Beep(ff, 10)
#time.sleep(1.0)

# Specify cell contents

# Some description of the contents:
cell = sheet.Cells(1,1)
cell.value = "Array"
cell.Font.Size = 14
cell.Font.Bold = True
time.sleep(0.3)

# Enter data row by row.
data = range(0,10)
row_start = 3
rows = 5
rows = range(row_start, rows + row_start)
addresses = []          # These cell addresses will be used later.
for row in rows :
    column_start = 2
    cell = sheet.Cells(row, column_start - 1)
    cell.value = 'Data Set' + str(row - row_start)
    column = column_start
    row_addresses = []
    freq = 100
    for item in data :
        cell = sheet.Cells(row, column)
        cell.value = item * row
        row_addresses.append(cell.Address)      # Record the cell address.
        column += 1
        if noise : win32api.Beep(freq, 100)
        freq += 100
        #time.sleep(0.10)
    addresses.append(row_addresses)
    time.sleep(sleep)

if noise: winsound.PlaySound("computer_working.wav",winsound.SND_FILENAME)

# Create a 'Total' row and enter a summation formula at the end of each column.
cell = sheet.Cells(row + 1, column_start - 1)
cell.value = 'Total'
# Make sum formulas
column = column_start
row += 1
freq = 100
for item in data :
    cell = sheet.Cells(row, column)
    cell.value = '=sum(' + addresses[0][column-column_start] + ":" + addresses[len(addresses)-1][column-column_start] + ')'
    column += 1
    if noise : win32api.Beep(freq, 50)
    freq += 100

# Delete all knowledge of the excel process for this Python session.
del excel
if noise: winsound.PlaySound("logical.wav",winsound.SND_FILENAME)
if noise: winsound.PlaySound("aye2.wav",winsound.SND_FILENAME)