diff --git a/README.md b/README.md new file mode 100644 index 0000000..1aa0e5d --- /dev/null +++ b/README.md @@ -0,0 +1,4 @@ +Simple Excel spreadsheet string search. + +`xlslu.py -h` will give you all the documentation you should need. + diff --git a/xlslu.py b/xlslu.py new file mode 100755 index 0000000..0ff8493 --- /dev/null +++ b/xlslu.py @@ -0,0 +1,319 @@ +#!/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")