使用 Pandas 将 SQL 导出到 Excel，如何进行下拉？

Question

我有一个简单的 MSSQL 表，其中包含如下数据：

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)

我导出数据了它到Excel使用文件Python和Pandas这样的：

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()

我得到了一小堆 Excel 文件。每个文件BATCH_ID都加载了所有这些结果。效果很好。我需要做的是让其中一列成为 Excel 下拉菜单，如下所示：

我当然可以进入 Excel 并为每个文件制作它，但我相信您可以看出这只是示例数据。我将制作数千个 Excel 文件。

如何使用我在 Python 中拥有的内容使其中一列成为下拉菜单，就像我在图片中所拥有的那样？是否有某种模板选项我可以利用，我对任何事情都持开放态度。我可以控制 SQL 数据，所以我可以添加值，如果这有助于使它更容易。提前致谢！

Answer 1

我无法Pandas在下拉列表中添加，但我能够读回文件，更新它，然后像这样写回：from openpyxl.worksheet.datavalidation import DataValidationfrom openpyxl import load_workbookdef add_drop_down(file_path, file_name, row_total):&nbsp; &nbsp; print("adding drop down")&nbsp; &nbsp; wb = load_workbook("".join([file_path, file_name]))&nbsp; &nbsp; ws = wb['Sheet1']&nbsp; &nbsp; ws['S2'] = 'Yes'&nbsp; &nbsp; ws['S3'] = 'No'&nbsp; &nbsp; ws['S4'] = 'Maybe'&nbsp; &nbsp; ws['S5'] = 'OK'&nbsp; &nbsp; ws['S6'] = 'Not OK'&nbsp; &nbsp; ws['S7'] = 'Check'&nbsp; &nbsp; ws['T2'] = 'What1'&nbsp; &nbsp; ws['T3'] = 'What2'&nbsp; &nbsp; ws['T4'] = 'What3'&nbsp; &nbsp; current_row = 2&nbsp; &nbsp; while current_row < row_total + 2:&nbsp; &nbsp; &nbsp; &nbsp; data_val_results = DataValidation(type="list",formula1='=S2:S7')&nbsp; &nbsp; &nbsp; &nbsp; data_val_status = DataValidation(type="list",formula1='=T2:T4')&nbsp; &nbsp; &nbsp; &nbsp; ws.add_data_validation(data_val_results)&nbsp; &nbsp; &nbsp; &nbsp; ws.add_data_validation(data_val_status)&nbsp; &nbsp; &nbsp; &nbsp; row_results = "".join(["O", str(current_row)])&nbsp; &nbsp; &nbsp; &nbsp; row_status = "".join(["P", str(current_row)])&nbsp; &nbsp; &nbsp; &nbsp; data_val_results.add(ws[row_results])&nbsp; &nbsp; &nbsp; &nbsp; data_val_status.add(ws[row_status])&nbsp; &nbsp; &nbsp; &nbsp; current_row += 1&nbsp; &nbsp; wb.save("".join([file_path, file_name]))可能有一种更聪明的方法来做到这一点，但对于一次性出口和运输，这很好用！在本例中，下拉菜单一直向下延伸到指定的行号。我还在这个例子中放置了两个不同的下拉菜单。感谢您为我指出正确的方向！