Changeset 32813 for main

Timestamp:

2019-02-25T22:06:34+13:00 (5 years ago)

Author:

davidb

Message:

Changes after testing

Location:

main/trunk/model-sites-dev/heritage-nz/collect/reports-2019/prepare

Files:

• README.txt (modified) (1 diff)
• xls-to-csv.py (modified) (3 diffs)

main/trunk/model-sites-dev/heritage-nz/collect/reports-2019/prepare/README.txt

@@ -5 +5 @@
   cd virtualenv-16.4.1
 
-  python virtualenv.py myEnv
+  python virtualenv.py ../my-python-env
+
+  cd ..
+  source my-python-env/bin/activate 
 
   pip install xlrd

main/trunk/model-sites-dev/heritage-nz/collect/reports-2019/prepare/xls-to-csv.py

@@ -3 +3 @@
 import os
 
-worksheet_name = "Archaeological reports"
+## 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)
-    worksheet = workbook.sheet_by_name(worksheet_name)
-    
+    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]
@@ -47 +70 @@
         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(" ", "")
@@ -67 +93 @@
 #            for entry in worksheet.row_values(row_i):
             for col_j in range(num_cols):
-                entry = worksheet.cell_value(row_i,col_j)
+                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: