source: main/trunk/model-sites-dev/heritage-nz/collect/pdf-reports/prepare/xls-to-sanitized-csv.py@ 33224

import xlrd
import csv
import os
import sys

import xlsutil

def excel_to_sanitized_csv(excel_filename,sanitized_csv_filename):
    workbook = xlrd.open_workbook(excel_filename)

    worksheet = workbook.sheet_by_index(xlsutil.worksheet_index_pos)
    ## worksheet = workbook.sheet_by_name(xlsutil.worksheet_name)

    sanitized_csv_ofile = open(sanitized_csv_filename, 'wb')

    sanitized_csv_wr  = csv.writer(sanitized_csv_ofile, quoting=csv.QUOTE_ALL)

    # 1. For header line, Build up hashmap of header-names to column number
    # 2. Write out CSV file for only the header-names in 'sanitized_headers'

    num_rows = worksheet.nrows

    # 1. get header-map
    header_names_mapping = xlsutil.getHeaderMappingToColNum(worksheet)

    if header_names_mapping is None:
        print "Failed to find \"Report Identifier\" header in spreadsheet (Row 0 or Sheet 0)"
        exit(1)

    # 2. Write out CSV file ...
    # Work through all the row to the spreadsheet, including the header-names
    row_i = xlsutil.header_row_pos 
    
    while row_i<num_rows:
        # Work through sanitized header names building up row of utf8 values
        sanitized_utf8_row = []

        for header_name in xlsutil.sanitized_headers:

            if header_name in header_names_mapping:
                header_col_j = header_names_mapping[header_name]

                cell_value_unicode = xlsutil.cell_value_tidy_unicode(worksheet,row_i,header_col_j)
                cell_value_utf8 = cell_value_unicode.encode("utf-8")

                sanitized_utf8_row.append(cell_value_utf8)
            else:
                print("Warning: Failed to column mapping in spreadsheet for header name \""+header_name+"\" => skipping")
                
        sanitized_csv_wr.writerow(sanitized_utf8_row)

        row_i = row_i + 1

    sanitized_csv_ofile.close()

if __name__ == "__main__":
    if len(os.sys.argv) != 3:
        print >> sys.stderr, "Usage: " + os.sys.argv[0] + "input.xsl|input.xslx output.csv"
        exit(1)

    excel_to_sanitized_csv(os.sys.argv[1],os.sys.argv[2])