依赖环境: pip install elasticsearch openpyxl
代码如下:
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# @Author : QC
# @Date : 2018/12/15 11:08
# @desc : 导出es指定index, type的数据到excel
from elasticsearch import Elasticsearch
from elasticsearch import helpers
import openpyxl
class EsToExcel(object):
def __init__(self, es_host, index, type, file_name):
"""
:param es_host: es连接地址 例: 127.0.0.1:9200
:param index: 索引名
:param type: type名
:param file_name: 导出文件名
"""
self.es = Elasticsearch([es_host])
self.index = index
self.type = type
self.file_name = file_name
self.item_list = []
def get_field(self):
"""
获取字段信息
:return: 字段信息
"""
field_list = list(self.es.indices.get_mapping(index=self.index, doc_type=self.type)
[self.index]['mappings'][self.type]['properties'])
return field_list
def get_data(self):
"""
获取索引下所有数据
:return: 数据纪录
"""
query = {
"query": {
"match_all": {
}
}
}
scanResp = helpers.scan(client=self.es, query=query, scroll="5m", index=self.index, doc_type=self.type,
request_timeout=100)
for resp in scanResp:
item = resp['_source']
yield item
def deal_data(self, item, field_list):
"""
:param item: es查询出的数据纪录
:param field_list: 对应的字段信息
:return: 处理好的数据纪录
"""
new_item = [str(item.get(field, '')) for field in field_list]
return new_item
def write_excel(self, item_list, style):
"""
写入Excel
:param item_list: 数据集
:param style: 表头信息
:return:
"""
f = openpyxl.Workbook()
sheet1 = f.active
for index, i in enumerate(style):
# openpyxl生成xlsx文件,行列从1开始
sheet1.cell(row=1, column=index + 1, value=i)
for row, item in enumerate(item_list):
try:
for index, i in enumerate(item):
# openpyxl生成xlsx文件,行列从1开始
sheet1.cell(row=row + 2, column=index + 1, value=i)
except Exception as e:
print(e)
f.save('{}.xlsx'.format(self.file_name))
print('{}数据导出完成'.format(self.file_name))
def start(self):
"""运行主函数"""
field_list = self.get_field()
for item in self.get_data():
new_item = self.deal_data(item, field_list)
self.item_list.append(new_item)
self.write_excel(self.item_list, field_list)
if __name__ == '__main__':
export = EsToExcel(es_host='127.0.0.1', index='**', type='**', file_name='**')
export.start()
--文章转自:https://blog.csdn.net/zhang862520682/article/details/85012973