"""
Stough/Lowden
7/11

Description from Mia:
ELISA is a method in biology that allows a scientist to determine if a
particular protein is present a sample and what amount is present.
Since samples may be derived from experimental animals or human
patients, results obtained from ELISA help research and patient care.
Often many samples are tested at once and information about the samples
must be matched with ELISA data generated for each sample. The program
xl2prism.py takes a list of the names and values associated with a set
of samples and matches them to ELISA data such that the labeled data
can easily be analyzed by statistical software. The program accepts
Excel Workbooks compatible with Excel 1997 to 2003. Each Excel file
must contain three sheets, 1) sample names; 2) values associated with
preparing the samples; and  3) ELISA data. The worksheets are
formatted to match the layout of the samples on the plate used for the
ELISA method; these plates are 24x16 grids. The output of xl2prism.py
is a new Excel Workbook containing the original three worksheets plus
a fourth worksheet where the information contained in the original
workbook is arranged in columns containing the sample names, values,
and corresponding ELISA results. In contrast to the grid format, the
column format allows the data to be pasted into statistical software
for further data analysis.

Notes on Mia's description, relative to this program:
-The size of the sheets does not matter as long as they are the same.


My notes:
Reads the input excel file and prints the three sheets,
then adds a sheet with an N x 4 of the results (see below):
sheet 1 has descriptions
sheet 2 has values (either known hormone or known drug)
sheet 3 has quantablu values.

Currently working in python 2.5 (maybe later, but probably
not 3). Requires xlrd, xlwt, xlutils, which can be found at
http://www.python-excel.org/

each description/value comes in pairs, as these tests are
usually performed in duplicate, but the output we
want is for each row:
uniqueDescription uniqueVal testBlu1 testBlu2

The order of output (4th sheet) is determined by a column-wise
traversal of the description sheet.

If you happen to be performing triplicate, there will be three
quantaBlu results.



-And a more detailed protocol:
-Install python 2.7 from http://python.org/download/
-Install the xlrd, xlwt, xlutils from http://www.python-excel.org/


-Make sure python program xl2prism.py is in the same folder as
the file you want to process
-Go to this folder, through the file manager (My Computer, for example)
-Right click on the icon for the xl2prism.py program and select
'edit with IDLE'
-The program opens in a little red 'Tk' window and the text is colored
-From this window go to Run>Run module
-A new window will pop open and ask for the file name
-Enter the name of the excel file, including the extension which should
be xls the file needs to be saved in 1997 to 2003 format
    A tip to enter long file names
    Type a single quote '
    Start typing the file name (at least the first letter)
    Press tab and a menu will appear with file names in the directory
    Use the arrow keys to navigate to the right file and press enter
    remove the single quote and press enter
-The program should spit out a ton of info, and then, hopefully, tell
you what xls file it just wrote.  That file is the one containing the
fourth sheet, useful for copying into statistical software.
"""

import sys, os, xlrd, xlwt
from xlutils.copy import copy

print "Hello.  I will attempt to combine the " \
      "description/value/quanta sheets \n of an " \
      "excel file for appropriate input into prism.\n" \
      "Please make sure you've saved the file in 97-2003 " \
      "format. \n\n"

filename = raw_input('Please enter file name: ')

if not os.path.exists(filename):
    print "Sorry, I cannot see the file %s." % (filename)
    print "Current directory is %s." % (os.path.abspath('.'))
    raise ValueError('File not found.')

print "Reading the input file %s..." % (filename),

layout = xlrd.open_workbook(filename)

if layout.nsheets != 3:
    print "The excel file must have 3 sheets.\n"\
          "Sheet 1: matrix of descriptions, including duplicates.\n"\
          "Sheet 2: matrix of values (either known hormone or known drug or non-empty placeholder.\n"\
          "Sheet 3: matrix of the plate sheet output from QuantaBlu.\n"
    raise ValueError('File must have only three sheets.')
else:
    print "read.\n\n"

print "Outputing the sheets:\n"

dex = layout.sheet_by_index(0)
vals = layout.sheet_by_index(1)
qus = layout.sheet_by_index(2)

for sh in [dex, vals, qus]:
    print "Sheet is %i by %i.\n" % (sh.nrows, sh.ncols)
    for r in range(sh.nrows):
        for c in range(sh.ncols):
            print "%20s " % (sh.cell_value(r,c)), 
        print
    print "\n"


print "Building output table..."

#table will be a dictionary with key description and value
#a list of [description, value, quanta1, quanta2]

table = {}
keys_list = []
for c in range(dex.ncols):
    for r in range(dex.nrows):
        key = dex.cell_value(r,c)
        if key not in table:
            keys_list.append(key)
            table[key] = [key, vals.cell_value(r,c), qus.cell_value(r,c)]
        else:
            table[key].append(qus.cell_value(r,c))

print "Table is built: \n"
print "The order of output is determined by a column-wise \n"\
      "traversal of the description sheet.\n"

for key in keys_list:
    for e in table[key]:
        print "%10s " % (e),
    print
    

print "Adding 'Prism Input' sheet...",

outputWB = copy(layout)
prismSheet = outputWB.add_sheet('Prism Input')

for r,key in enumerate(keys_list):
    for c,s in enumerate(table[key]):
        prismSheet.write(r,c,s)
print "added.\n"


fileparts = filename.split('.')
outfile = '.'.join(fileparts[:-1]) + '_ForPrism.' + fileparts[-1]

print "Saving to %s..." % (outfile),
outputWB.save(outfile)
print "saved.\n"

print "Thank you, and have a great day.\n"