1.同目录下创建file_handle.py文件
file_handle.py
import xlwt, datetime
from xlwt import *
import xlsxwriter
# 写入excel文件函数
def wite_to_excel(n, head_data, records, download_url, table_name):
# 获取时间戳
timestr = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
# 工作表
wbk = xlwt.Workbook(style_compression=2, encoding='utf-8')
sheet1 = wbk.add_sheet('sheet1', cell_overwrite_ok=True)
# 写入表头
for filed in range(0, len(head_data)):
sheet1.write(0, filed, head_data[filed], excel_head_style())
# 写入数据记录
for row in range(1, n + 1):
for col in range(0, len(head_data)):
sheet1.write(row, col, records[row - 1][col], excel_record_style())
# 设置默认单元格宽度
sheet1.col(col).width = 256 * 15
wbk.save(download_url + table_name + '.xls')
return timestr
# 写入excel文件函数
def wite_to_excel_text(n, head_data, records, download_url, table_name):
# 获取时间戳
timestr = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
# 工作表
wbk = xlsxwriter.Workbook(download_url + table_name + '.xls')
sheet1 = wbk.add_worksheet('sheet1')
# 写入表头
for filed in range(0, len(head_data)):
sheet1.write(0, filed, head_data[filed])
# 写入数据记录
for row in range(1, n + 1):
for col in range(0, len(head_data)):
sheet1.write(row, col, records[row - 1][col])
# 设置默认单元格宽度
# sheet1.col(col).width = 256 * 15
# wbk.save(download_url + table_name + '.xls')
wbk.close()
return timestr
# 定义导出文件表头格式
def excel_head_style():
# 创建一个样式
style = XFStyle()
# 设置背景色
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = Style.colour_map['light_green'] # 设置单元格背景色
style.pattern = pattern
# 设置字体
font0 = xlwt.Font()
font0.name = u'微软雅黑'
font0.bold = True
font0.colour_index = 0
font0.height = 240
style.font = font0
# 设置文字位置
alignment = xlwt.Alignment() # 设置字体在单元格的位置
alignment.horz = xlwt.Alignment.HORZ_CENTER # 水平方向
alignment.vert = xlwt.Alignment.VERT_CENTER # 竖直方向
style.alignment = alignment
# 设置边框
borders = xlwt.Borders() # Create borders
borders.left = xlwt.Borders.THIN # 添加边框-虚线边框
borders.right = xlwt.Borders.THIN # 添加边框-虚线边框
borders.top = xlwt.Borders.THIN # 添加边框-虚线边框
borders.bottom = xlwt.Borders.THIN # 添加边框-虚线边框
style.borders = borders
return style
# 定义导出文件记录格式
def excel_record_style():
# 创建一个样式
style = XFStyle()
# 设置字体
font0 = xlwt.Font()
font0.name = u'微软雅黑'
font0.bold = False
font0.colour_index = 0
font0.height = 200
style.font = font0
# 设置文字位置
alignment = xlwt.Alignment() # 设置字体在单元格的位置
alignment.horz = xlwt.Alignment.HORZ_CENTER # 水平方向
alignment.vert = xlwt.Alignment.VERT_CENTER # 竖直方向
style.alignment = alignment
# 设置边框
borders = xlwt.Borders() # Create borders
borders.left = xlwt.Borders.THIN # 添加边框-虚线边框
borders.right = xlwt.Borders.THIN # 添加边框-虚线边框
borders.top = xlwt.Borders.THIN # 添加边框-虚线边框
borders.bottom = xlwt.Borders.THIN # 添加边框-虚线边框
style.borders = borders
return style
2.views.py
class ExportExelc(APIView):
# authentication_classes = (JSONWebTokenAuthentication, SessionAuthentication)
# permission_classes = (IsAuthenticated,)
def post(self, request, *args, **kwargs):
'''导出数据'''
table_name = request.query_params['table_name']
# 表头字段
# records表示要添加的数据
# 写入备注
head_data = []
records = []
record = []
queryset = get_query_exprot(connection, table_name, DATABASES['default']['NAME'])
for sql_info in queryset:
head_data.append(sql_info['COLUMN_NAME']) # 设置标题字段
record.append(sql_info['COLUMN_COMMENT']) # 设置标题字段
records.append(record)
cursor = connection.cursor()
page = 0
page_size = 1000
while True:
start = page * page_size
page += 1
num = cursor.execute(f'select * from {table_name} limit {start},{page_size};')
if not num:
break
for i in cursor.fetchall():
record = []
for j in i:
record.append(str(j))
records.append(record)
n = len(records)
# 设置生成文件所在路径
download_url = os.path.dirname(os.path.abspath(__file__)) + '\\export_excel\\'
# excel文件名称
table_name = table_name.replace('database_data_', '')
# 写入数据到excel中
# wite_to_excel(n, head_data, records, download_url, table_name)
wite_to_excel_text(n, head_data, records, download_url, table_name)
from django.http import StreamingHttpResponse
def file_iterator(file_name, chunk_size=512):
with open(file_name, 'rb') as f:
while True:
c = f.read(chunk_size)
if c:
yield c
else:
break
# 显示在弹出对话框中的默认的下载文件名
the_file_name = table_name + '.xls'
# 设置生成文件所在路径
download_url = download_url
response = StreamingHttpResponse(file_iterator(download_url + the_file_name))
response['Content-Type'] = 'application/octet-stream'
response['Content-Disposition'] = 'attachment;filename="{0}"'.format(the_file_name)
return response
3.query
def get_query_exprot(connection, table, key):
"""
表的结构和数据
:param connection:
:param table:
:return:
"""
cursor = connection.cursor()
cursor_columns = connection.cursor()
sql = f"select * from information_schema.columns where table_schema ='{key}' and table_name = '{table}';"
data_nums = cursor.execute(sql)
if data_nums:
cursor_columns.execute("show columns from information_schema.columns")
columns = [i[0] for i in cursor_columns.fetchall()]
queryset = [{c: str(j) for c, j in zip(columns, i)} for i in cursor.fetchall()]
return queryset
4.在xls文件上追加数据
import xlrd
from xlutils.copy import copy
workbook = xlrd.open_workbook(file_name, formatting_info=True) # 读取xls文件
write_data = copy(workbook)
sheet_data = write_data.get_sheet(0)
sheet_data.write(i, 12, 'succeed') # i是行 12 是列 'succeed' 追加要写入的数据
write_data.save('.\\operations.xls')
