要使用Prometheus API和Python将查询结果导出为Excel，需要安装以下模块：
- prometheus-api-client：用于连接和查询Prometheus API
- pandas：用于处理数据和生成Excel文件
- openpyxl：用于创建Excel工作簿
首先，使用prometheus-api-client模块连接到Prometheus服务器并执行查询：
from prometheus_api_client import PrometheusConnect
# Connect to Prometheus server
prom = PrometheusConnect(url='http://localhost:9090')
# Query node_systemd_unit_state metric
query = 'node_systemd_unit_state{state="active"}'
result = prom.custom_query(query)
然后，将查询结果转换为pandas DataFrame格式，并根据需要进行数据清理和转换：
import pandas as pd
# Convert result to pandas DataFrame
df = pd.DataFrame(result)
# Clean up data and rename columns
df.columns = ['labels', 'value', 'timestamp']
df['service'] = df['labels'].apply(lambda x: x['name'])
df['unit'] = df['labels'].apply(lambda x: x['unit'])
df.drop(columns=['labels'], inplace=True)
最后，使用openpyxl模块创建一个新的Excel工作簿，并将pandas DataFrame写入其中：
from openpyxl import Workbook
# Create a new Excel workbook and worksheet
wb = Workbook()
ws = wb.active
# Write the DataFrame to the worksheet
for r in dataframe_to_rows(df, index=False, header=True):
ws.append(r)
# Save the workbook to a file
wb.save('output.xlsx')
完整代码示例：
from prometheus_api_client import PrometheusConnect
import pandas as pd
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
# Connect to Prometheus server
prom = PrometheusConnect(url='http://localhost:9090')
# Query node_systemd_unit_state metric
query = 'node_systemd_unit_state{state="active"}'
result = prom.custom_query(query)
# Convert result to pandas DataFrame
df = pd.DataFrame(result)
# Clean up data and rename columns
df.columns = ['labels', 'value', 'timestamp']
df['service'] = df['labels'].apply(lambda x: x['name'])
df['unit'] = df['labels'].apply(lambda x: x['unit'])
df.drop(columns=['labels'], inplace=True)
# Create a new Excel workbook and worksheet
wb = Workbook()
ws = wb.active
# Write the DataFrame to the worksheet
for r in dataframe_to_rows(df, index=False, header=True):
ws.append(r)
# Save the workbook to a file
wb.save('output.xlsx')
