python连接MS数据库查询结果保存为excel并自适应列宽加边框居中

首要具备条件：pycharm、python 3以上版本，便于引用该代码依自身实际情况调试；运行python主机安装有office 2007以上版本软件；安装python odbc for sql server,附odbc支持对应sql server版本信息如下：

{SQL Server} - released with SQL Server 2000

{SQL Native Client} - released with SQL Server 2005 (also known as version 9.0)

{SQL Server Native Client 10.0} - released with SQL Server 2008

{SQL Server Native Client 11.0} - released with SQL Server 2012

{ODBC Driver 11 for SQL Server} - supports SQL Server 2005 through 2014

{ODBC Driver 13 for SQL Server} - supports SQL Server 2005 through 2016

{ODBC Driver 13.1 for SQL Server} - supports SQL Server 2008 through 2016

{ODBC Driver 17 for SQL Server} - supports SQL Server 2008 through 2019 (depending on minor version)

{ODBC Driver 18 for SQL Server} - supports SQL Server 2012 through 2019

备注：df = pd.read_sql("select * from EXTENSION", conn)依实际情况修改为自身需要的查询语句；同时查询结果excel保存路径和名称依自身情况而进行修改。

实现代码如下(应用成功实例windows sql server 2012)：

import pandas as pd

import xlsxwriter

import pyodbc

import time

server = 'MS数据库IP地址'

database = '数据库库名'

username = '用户名'

password = '密码'

driver= '{ODBC Driver 17 for SQL Server}'

with pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn:

with pd.ExcelWriter(r"C:\Users\public.DESKTOP-0HSVIBN\Documents\output.xlsx", engine="xlsxwriter",engine_kwargs={"options": {"strings_to_numbers": True,"strings_to_formulas": False}}) as writer:

try:

df = pd.read_sql("select * from EXTENSION", conn)

df.to_excel(writer, sheet_name = "Sheet1", header = True, index = False)

print("File saved successfully!")

except:

print("There is an error")

conn.close()

time.sleep(10)

import xlwings as xw

app=xw.App(visible=True,add_book=False)

app.display_alerts=False

app.screen_updating=False

wb=app.books.open(r'C:\Users\public.DESKTOP-0HSVIBN\Documents\output.xlsx')

sht = wb.sheets.active

value = sht.range('A1').expand('table')

wb.sheets['Sheet1'].autofit(axis='c')

wb.sheets['Sheet1'].autofit(axis='r')

value.api.HorizontalAlignment = xw.constants.HAlign.xlHAlignCenter

value.api.VerticalAlignment = xw.constants.VAlign.xlVAlignCenter

ws = wb.sheets['Sheet1']

last_column = ws.range(1, 1).end('right').get_address(0, 0)[0]

last_row = ws.range(1, 1).end('down').row

a_range = f'A1:{last_column}{last_row}'

ws.range(a_range).api.Borders.LineStyle = 1

wb.save(r'C:\Users\public.DESKTOP-0HSVIBN\Documents\output_fit.xlsx')

wb.close()

app.quit()

您的支持与鼓励，是我前行的动力；希望可以解决您的问题。