import xlrd
import csv
import os

## worksheet_name = "Archaeological reports"


# https://code.activestate.com/recipes/546518-simple-conversion-of-excel-files-into-csv-and-yaml/download/1/
def format_excelval(book, type, value, wanttupledate):
    """ Clean up the incoming excel data """
    ##  Data Type Codes:
    ##  EMPTY   0
    ##  TEXT    1 a Unicode string 
    ##  NUMBER  2 float 
    ##  DATE    3 float 
    ##  BOOLEAN 4 int; 1 means TRUE, 0 means FALSE 
    ##  ERROR   5 
    returnrow = []
    if   type == 2: # TEXT
        if value == int(value): value = int(value)
    elif type == 3: # NUMBER
        datetuple = xlrd.xldate_as_tuple(value, book.datemode)
        value = datetuple if wanttupledate else tupledate_to_isodate(datetuple)
    elif type == 5: # ERROR
        value = xlrd.error_text_from_code[value]
    return value


def excel_to_bound_pdf_csv(excel_filename):
    workbook = xlrd.open_workbook(excel_filename, formatting_info=True)
    ## worksheet = workbook.sheet_by_name(worksheet_name)
    worksheet = workbook.sheet_by_index(0)

    excel_filename_split = os.path.split(excel_filename)
    excel_dirname = excel_filename_split[0]
    excel_file = excel_filename_split[1]
    excel_file_splitext = os.path.splitext(excel_file)
    excel_file_root = excel_file_splitext[0];

    pdfbound_filename = os.path.join(excel_dirname,"PDF-BOUND "+excel_file_root+".csv")
    unbound_filename  = os.path.join(excel_dirname,"UNBOUND "+excel_file_root+".csv")

    # print "Worksheet: " + worksheet_name
    csv_pdfbound_ofile = open(pdfbound_filename, 'wb')
    #csv_unbound_ofile  = open('{}.csv'.format(excel_file_root), 'wb')
    csv_unbound_ofile  = open(unbound_filename, 'wb')

    pdfbound_wr = csv.writer(csv_pdfbound_ofile, quoting=csv.QUOTE_ALL)
    unbound_wr  = csv.writer(csv_unbound_ofile, quoting=csv.QUOTE_ALL)

    # 1. Skip lines until "Report Identifier" encountered
    # 2. Remove "Report Identifier" line entries to turn 
    #     into Greenstone friendly metadata names
    # 3. Process the rest of the file, checking if accompanying 
    #     PDF file present or not

    num_rows = worksheet.nrows

    row_i = 0
    found_header = False
    while row_i<num_rows:
        first_cell = worksheet.cell_value(row_i,0);
        if first_cell == "Report Identifier":
            found_header = True
            break
        print "Skipping row {} as not yet encountered 'Report Identifier' metadata label in column 0".format(row_i)
        row_i = row_i + 1

    if found_header:
        entry_utf8_row = []
        for entry in worksheet.row_values(row_i):
            if entry == "Report Identifier":
                entry = "hnz.Identifier"

            entry_utf8 = unicode(entry).encode("utf-8")
            metadata_entry_utf8 = entry_utf8.replace(" ", "")

            entry_utf8_row.append(metadata_entry_utf8)
        unbound_wr.writerow(entry_utf8_row)

        # Add in 'Filename' as first column in pdfbound CSV file
        entry_utf8_row.insert(0, "Filename")
        pdfbound_wr.writerow(entry_utf8_row)


        row_i = row_i + 1
        while row_i<num_rows:
            num_cols = worksheet.row_len(row_i)
            entry_utf8_row = []

            found_pdf = False

#            for entry in worksheet.row_values(row_i):
            for col_j in range(num_cols):
                cell = worksheet.cell(row_i,col_j)
#                xf = workbook.xf_list[cell.xf_index]
#                format = workbook.format_map[xf.format_key]
#                format_str = format.format_str

#                print 'rowx=%d colx=%d ctype=%d xfx=%d s_value=%s fmt=%s' \
#                    % (row_i, col_j, cell.ctype, cell.xf_index, str(cell.value), format_str)

                cell_type  = worksheet.cell_type(row_i,col_j)
                cell_value = worksheet.cell_value(row_i,col_j)

                format_cell = format_excelval(workbook,cell_type,cell_value,False)
                #print "**** format cell str = " +str(format_cell)

                # entry = worksheet.cell_value(row_i,col_j)
                entry = format_cell

                if col_j == 0:
                    # Check to see if companion PDF file present
                    pdf_filename = os.path.join(excel_dirname,"pdfs",entry+".pdf")
                    if os.path.exists(pdf_filename):
                        found_pdf = True
                        pdf_file = os.path.join("pdfs",entry+".pdf")
                        entry_utf8_row.insert(0, pdf_file)
                                 
                entry_utf8 = unicode(entry).encode("utf-8")
                entry_utf8_row.append(entry_utf8)
            if found_pdf:
                pdfbound_wr.writerow(entry_utf8_row)
            else:
                unbound_wr.writerow(entry_utf8_row)

            row_i = row_i + 1
    else:
        print "Failed to find metadata label 'Report Identifier' in column 0"


    csv_pdfbound_ofile.close()
    csv_unbound_ofile.close()

if __name__ == "__main__":
    excel_to_bound_pdf_csv(os.sys.argv[1])