Querying an Excel spreadsheet and returning results? But which program?
Question: A company I am working for has recently asked the head tech to compile a
spreadsheet that has every single computer name listed, along with the person
that uses that computer. The idea is that Help Desk can have this spreadsheet
open, so when the user calls, they can find their name and therefor their
associated computer number.

However, this seems a bit clunky to me. We have around 1000 users, so that
spreadsheet would just be a pain imho. I thought it might be easier to have a
simple program that asks you for the User's name, you type it in, and it
returns the computer number.

So, I am assuming this program would reference this spreadsheet and then
return your information. Of course, it doesn't have to reference the
spreadsheet, it could reference something else.

I am only experienced with Python, and only because I took a Introductory
class on it for college. However, I am thinking a Batch file would work
better? Or would something else work better? I am taking this as a chance to
learn and perhaps offer a neat utility to our Help Desk, and was just looking
for advice. :)

Thank you.


Answer: If you're planning on using python with the excel, there's two (most common,
there are others) ways to go:

  1. use the windows API, which lets you run native excel commands through python. i.e.:
    
        from win32com.client import Dispatch
    excel = Dispatch('Excel.Application')
    excel.Visible = True #if you want to see it
    sheet = excel.Workbooks.open('Book1').Sheets('Sheet1')
    # get value from a cell:
    sheet.Cells(1, 1).value
    
    # get range
    sheet.Range('A1:A2000')
    
    # etc. google about vlookup and other functionalities. You can run anything you would in excel through python here, it's just an API
    

this will be the fastest most efficient method, as it uses native excel
functionalities and those are optimized. On the other hand, it's not very
user-friendly, as the windows API is kinda messy (that's my opinion at least).

  2. Now if you're like me, and prefer to go pythonic, you can use the excellent [xlrd library](http://www.python-excel.org/) which is very easy to use
    
        import xlrd
    book = xlrd.open_workbooks('Book1')
    sheet = book.sheet_by_name('Sheet1')
    sheel.col(1)
    

I'm not gonna delve any deeper. You'll have to figure it out yourself, and as
you can see, it ain't too hard. Also, note that if you'd like to also _write
to the file_ you'll need another library called xlwt.

Of course, there's also a third option - pass the data to a proper database
(SQLite will probably suffice) and use SQL queries to fetch the data. That
would be the most fast and efficient way to go, if that's important to you
(SQL _was_ created for this exact purpose, unlike excel. so uh... y'know).

Good luck!

