#!/usr/bin/env python # xlslu.py - Match Strings In XLS Spreadsheets # Embed the source control ID string for use by program CVSID='$Id: xlslu.py,v 1.201 $' ##### # Program Information ##### PROGNAME = "xlslu.py" PROGENV = "xlslu".upper() VERSION = CVSID.split()[2] PROGVER = PROGNAME + " " + VERSION + " - Match Strings In XLS Spreadsheets" ##### # Copyright Information ##### COPYRIGHT = "" ##### # List Of All Legal Options - Update This When You Add More!!!! ##### OPTIONSLIST = 'ahv' #----------------------------------------------------------# # Variables User Might Change # #----------------------------------------------------------# #------------------- Nothing Below Here Should Need Changing ------------------# #----------------------------------------------------------# # Imports # #----------------------------------------------------------# import getopt import os import sys import xlrd #----------------------------------------------------------# # Aliases & Redefinitions # #----------------------------------------------------------# #----------------------------------------------------------# # Constants & Literals # #----------------------------------------------------------# ##### # Constants & Flags ##### ALLSTRINGS = False MATCHSEP = "---> " ##### # Literals ##### #----------------------------------------------------------# # Prompts, & Application Strings # #----------------------------------------------------------# ##### # Debug Messages ##### ##### # Debug Messages ##### DEBUGFLAG = "-d" dDEBUG = "DEBUG" dPROGENV = "$" + PROGENV ##### # Error Messages ##### eBADARG = "Invalid command line: %s!" eERROR = "ERROR" ##### # Informational Messages ##### iINFO = "INFO" ##### # Usage Prompts ##### # Make the options list more human readable optionslist = OPTIONSLIST.replace(':', ' arg -').strip() if optionslist[-1] == '-': optionslist = optionslist[:-1].strip() uTable = [PROGVER, "usage: " + PROGNAME + " [-%s]" % optionslist + " file.xls string string string ...", " where,", " -a require all strings to be present (AND matching)", " -h print this help information", " -v print detailed version information", " file.xls name of spreadsheet file to read", " string ... list of strings that will trigger a match", ] #----------------------------------------------------------# # Global Variables & Data Structures # #----------------------------------------------------------# CFGFILE = os.path.join(os.getenv("HOME"), "." + "xlslu") # conf file #--------------------------- Code Begins Here ---------------------------------# #----------------------------------------------------------# # Object Base Class Definitions # #----------------------------------------------------------# #----------------------------------------------------------# # Supporting Function Definitions # #----------------------------------------------------------# def ColumnPad(list, padchar=" ", padwidth=20): retval = "" for l in list: l = str(l) retval += l + ((padwidth - len(l)) * padchar) return retval.strip() # End of 'ColumnPad()' ##### # Print A Debug Message ##### def DebugMsg(msg): PrintStderr(PROGNAME + " " + dDEBUG + ": " + msg) # End of 'DebugMsg()' ##### # Dump The State Of The Program ##### def DumpState(): # Dump the command line DebugMsg(ColumnPad(["Command Line", sys.argv])) # Names of all the state variables we want dumped state = [ ] for k in state: DebugMsg(ColumnPad([k, eval(k)])) # End of 'DumpState()' ##### # Print An Error Message ##### def ErrorMsg(emsg): PrintStderr(PROGNAME + " " + eERROR + ": " + emsg) # End of 'ErrorMsg()' ##### # Print An Info Message ##### def InfoMsg(imsg): PrintStderr(PROGNAME + " " + iINFO + ": " + imsg) # End of 'InfoMsg()' ##### # Print To stderr ##### def PrintStderr(msg, trailing="\n"): sys.stderr.write(msg + trailing) # End of 'PrintStderr()' ##### # Print To stdout ##### def PrintStdout(msg, trailing="\n"): sys.stdout.write(msg + trailing) # End of 'PrintStdout' ##### # Print Usage Information ##### def Usage(): for line in uTable: PrintStdout(line) # End of 'Usage()' #----------------------------------------------------------# # Program Entry Point # #----------------------------------------------------------# # Command line processing - Process any options set in the # environment first, and then those given on the command line OPTIONS = sys.argv[1:] envopt = os.getenv(PROGENV) if envopt: OPTIONS = envopt.split() + OPTIONS try: opts, args = getopt.getopt(OPTIONS, OPTIONSLIST) except getopt.GetoptError as cli_errors: (errmsg, badarg) = cli_errors.args ErrorMsg(eBADARG % errmsg) sys.exit(1) for opt, val in opts: if opt == "-a": ALLSTRINGS = True if opt == "-h": Usage() sys.exit(0) if opt == "-v": PrintStdout(CVSID) sys.exit(0) SPREADSHEET = args[0] MATCHSTRINGS = args[1:] ##### # Read In Spreadsheet ##### book = xlrd.open_workbook(SPREADSHEET) sheet = book.sheet_by_index(0) # Iterate over all rows looking for lines that contain any of the # strings specified on the command line. for row in range(sheet.nrows): matched = False nummatched = 0 content = [] for col in range(sheet.ncols): # Get value value = str(sheet.cell(row, col).value) # Save, in case we match below and have to report it content.append(value.strip()) # Check for matching strings and flag accordingly for matchstring in MATCHSTRINGS: if value.lower().count(matchstring.lower()): matched = True nummatched += 1 # Deal with AND Matching if ALLSTRINGS and (nummatched != len(MATCHSTRINGS)): matched = False # Report matching entries if matched: PrintStdout(MATCHSEP + ", ".join(content), trailing="\n\n")