export SQL to Excel with Pandas, how to make a drop down?

Question

I have a simple MSSQL table with data in it like this:


IF OBJECT_ID('MY_BIG_TABLE') IS NOT NULL
    DROP TABLE [dbo].[MY_BIG_TABLE]

CREATE TABLE [dbo].[MY_BIG_TABLE](
    [ID] [int] NOT NULL,
    [PERSON] [varchar](50) NOT NULL,
    [STREET] [varchar](50) NOT NULL,
    [PHONE] [varchar](50) NOT NULL,
    [BATCH_ID] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO MY_BIG_TABLE (ID, PERSON, STREET, PHONE, BATCH_ID)
VALUES 
    (1, 'BOB', 'MAIN STREET', '555-555-5555', 100),
    (2, 'SANDY', 'ELM', '666-555-5555', 100),
    (3, 'FRED', 'PINE', '777-555-5555', 200),
    (8, 'BOB', 'DIRT', '888-555-5555', 200),
    (52, 'GEORGE', 'RIVER ROAD', '999-555-5555', 700)

I'm exporting data out of it into Excel files using Python and Pandas like this:

import pypyodbc
import pandas

def main():

    server_name = "SERVER_NAME"
    database = "TEST_DATABASE"

    connection = pypyodbc.connect(
        "Driver={SQL Server};"
        "Server=" + server_name + ";"
        "Database=" + database + ";"
        "Trusted_Connection=yes;"
        )


    batch_id_list = [100,200,700]

    for batch_id in batch_id_list:
            print ("--------------------------------------------")
            print ("".join(["reading batch_id:", str(batch_id)]))
            file_name = "".join(["EXPORT_", str(batch_id), ".xlsx"])
            the_sql = """
            SELECT * FROM
            MY_BIG_TABLE
            WHERE BATCH_ID = ?"""
            data = pandas.read_sql(the_sql, connection, params=[batch_id])
            print ("".join(["writing batch_id:", str(batch_id)]))
            data.to_excel("".join(["c:/temp/", file_name]))

if __name__ == "__main__":
    main()

And I get a nice little pile of Excel files. One file per BATCH_ID with all of those results loaded in. Works great. What I need to do is have one of the columns be an Excel drop down like this:

I could certainly go into Excel and make it for each file, but as I'm sure you can tell this is just sample data. I'm going to be making thousands of Excel files.

How can I using what I have in Python make one of the columns a drop down like what I have in the picture? Is there some kind of template option I can take advantage of, I'm open to anything. I have control over the SQL data so I can add values if that helps make it easier. Thanks in advance!

https://stackoverflow.com/questions/51497731/openpyxl-is-it-possible-to-create-a-dropdown-menu-in-an-excel-sheet — BENY, Mar 29 '19 at 13:58
Try [this post](https://stackoverflow.com/questions/11957420/python-how-to-turn-on-excel-auto-filter-on-cells-in-non-first-row) or [this one](https://stackoverflow.com/questions/1948224/how-to-create-an-excel-file-with-an-autofilter-in-the-first-row-with-xlwt) — Jacob H, Mar 29 '19 at 13:58
read the docs: https://xlsxwriter.readthedocs.io/working_with_pandas.html, https://xlsxwriter.readthedocs.io/example_data_validate.html — It_is_Chris, Mar 29 '19 at 14:09
there we go, thanks for the comments folks! Reading through them I was able to hack up a solution that I'll post shortly. — sniperd, Mar 29 '19 at 17:26

score 0 · Accepted Answer · answered Mar 29 '19 at 17:34

I was not able to use Pandas to add in the drop down, but I was able read the file back in, update it, and write it back out like this:

from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl import load_workbook

def add_drop_down(file_path, file_name, row_total):
    print("adding drop down")
    wb = load_workbook("".join([file_path, file_name]))
    ws = wb['Sheet1']
    ws['S2'] = 'Yes'
    ws['S3'] = 'No'
    ws['S4'] = 'Maybe'
    ws['S5'] = 'OK'
    ws['S6'] = 'Not OK'
    ws['S7'] = 'Check'

    ws['T2'] = 'What1'
    ws['T3'] = 'What2'
    ws['T4'] = 'What3'

    current_row = 2
    while current_row < row_total + 2:
        data_val_results = DataValidation(type="list",formula1='=S2:S7')
        data_val_status = DataValidation(type="list",formula1='=T2:T4')

        ws.add_data_validation(data_val_results)
        ws.add_data_validation(data_val_status)

        row_results = "".join(["O", str(current_row)])
        row_status = "".join(["P", str(current_row)])

        data_val_results.add(ws[row_results])
        data_val_status.add(ws[row_status])

        current_row += 1
    wb.save("".join([file_path, file_name]))

There is probably a more clever way to do this but for one shot export and shipping it, this works great! In this example the drop downs go all the way down the sheet up to a specified row number. I also put two different drop downs in this example. Thanks for pointing me in the right direction guys!

export SQL to Excel with Pandas, how to make a drop down?

1 Answers1