#!/usr/bin/env python
# xlslu.py - Simple Excel Spreadsheet String Search
# Embed the source control ID string for use by program
# Copyright (c) 2013-2020 TundraWare Inc.
#####
# Program Information
#####
PROGNAME = "xlslu.py"
PROGENV = "xlslu".upper()
VERSION = "1.201"
PROGVER = PROGNAME + " " + VERSION + " - Simple Spreadsheet String Search"
#####
# 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 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
# They at least have to provide a filename
# Does not catch the case where they provide an option but no filename
if len(sys.argv) < 2:
Usage()
sys.exit(1)
else:
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(PROGVER)
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
matchedstrings = {}
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
matchedstrings[matchstring] = True
# Deal with AND Matching
if ALLSTRINGS and (len(matchedstrings) != len(MATCHSTRINGS)):
matched = False
# Report matching entries
if matched:
PrintStdout(MATCHSEP + ", ".join(content), trailing="\n\n")
