数据分析智能体与报表生成
<h1 id="数据分析智能体与报表生成">数据分析智能体与报表生成</h1><h2 id="前言">前言</h2>
<p>在前一篇文章中，我们完成了企业客服智能助手的实战项目。在本系列的最后一篇文章中，我们将构建另一个实用的Agent应用：数据分析智能助手。</p>
<p>这个智能助手可以让用户使用自然语言查询数据库、自动生成数据报表、进行数据可视化分析。通过这个项目，你将学会如何将自然语言转换为SQL查询、如何构建数据分析工作流、如何生成专业的报表文档。</p>
<h2 id="一项目概述">一、项目概述</h2>
<h3 id="11-需求分析">1.1 需求分析</h3>
<p>数据分析智能助手需要满足以下核心需求：</p>
<p><strong>自然语言查询</strong>：用户可以用自然语言描述想要查询的数据，如"上个月销售额最高的产品是什么"。</p>
<p><strong>SQL生成</strong>：将自然语言转换为标准的SQL查询语句。</p>
<p><strong>报表生成</strong>：根据查询结果自动生成专业的报表文档，支持Excel、PDF等格式。</p>
<p><strong>可视化分析</strong>：生成图表和数据可视化，帮助用户理解数据。</p>
<p><strong>多数据源支持</strong>：支持连接多个数据库，进行跨库查询和分析。</p>
<h3 id="12-技术架构">1.2 技术架构</h3>
<p><strong>自然语言理解层</strong>：解析用户输入，提取查询意图和实体。</p>
<p><strong>SQL生成层</strong>：将自然语言转换为SQL语句。</p>
<p><strong>数据执行层</strong>：执行SQL查询，获取数据。</p>
<p><strong>报表生成层</strong>：将数据转换为专业的报表格式。</p>
<p><strong>可视化层</strong>：生成图表和数据可视化。</p>
<h2 id="二核心实现">二、核心实现</h2>
<h3 id="21-自然语言转sql">2.1 自然语言转SQL</h3>
<p>这是数据分析Agent的核心能力：</p>
<pre><code class="language-csharp">// NLToSqlConverter.cs
public class NLToSqlConverter
{
private readonly IAIAgent _agent;
private readonly IDatabaseSchemaProvider _schemaProvider;
private readonly ILogger<NLToSqlConverter> _logger;
public NLToSqlConverter(
IAIAgent agent,
IDatabaseSchemaProvider schemaProvider,
ILogger<NLToSqlConverter> logger)
{
_agent = agent;
_schemaProvider = schemaProvider;
_logger = logger;
}
public async Task<SqlQueryResult> ConvertAsync(string naturalLanguage, string? context = null)
{
// 1. 获取数据库模式信息
var schema = await _schemaProvider.GetSchemaAsync();
// 2. 构建提示
var prompt = BuildPrompt(naturalLanguage, schema, context);
// 3. 调用LLM生成SQL
try
{
var sqlResponse = await _agent.CompleteAsync(prompt);
// 4. 解析SQL
var sql = ParseSqlFromResponse(sqlResponse);
// 5. 验证SQL
var validationResult = await ValidateSqlAsync(sql, schema);
_logger.LogInformation("NL转SQL成功: NL={NaturalLanguage}, SQL={Sql}",
naturalLanguage, sql);
return new SqlQueryResult
{
Success = true,
Sql = sql,
Confidence = validationResult.Confidence,
Parameters = ExtractParameters(sql)
};
}
catch (Exception ex)
{
_logger.LogError(ex, "NL转SQL失败: {NaturalLanguage}", naturalLanguage);
return new SqlQueryResult
{
Success = false,
Error = ex.Message
};
}
}
private string BuildPrompt(string naturalLanguage, DatabaseSchema schema, string? context)
{
return $"""
你是一个SQL专家。请根据用户的问题生成SQL查询语句。
## 数据库模式
{FormatSchema(schema)}
## 用户问题
{naturalLanguage}
## 额外上下文
{context ?? "无"}
## 要求
1. 只返回SQL语句，不要其他解释
2. 使用标准的T-SQL语法
3. 注意SQL注入防护，使用参数化查询
4. 只生成SELECT语句，不要生成UPDATE、DELETE等语句
5. 合理使用JOIN、GROUP BY、ORDER BY等子句
6. 如果问题不明确，返回最可能的查询
7. 使用中文列名别名
""";
}
private string FormatSchema(DatabaseSchema schema)
{
var lines = new List<string>();
foreach (var table in schema.Tables)
{
lines.Add($"表名: {table.Name}");
lines.Add($"描述: {table.Description}");
foreach (var column in table.Columns)
{
var nullable = column.IsNullable ? "NULL" : "NOT NULL";
var primary = column.IsPrimaryKey ? "主键" : "";
lines.Add($"- {column.Name}: {column.Type} ({nullable}) {primary} - {column.Description}");
}
lines.Add("");
}
return string.Join("\n", lines);
}
private string ParseSqlFromResponse(string response)
{
// 尝试提取SQL代码块
var match = Regex.Match(response, @"```sql\s*(.*?)\s*```", RegexOptions.Singleline);
if (match.Success)
{
return match.Groups.Value.Trim();
}
// 如果没有代码块，尝试直接解析
match = Regex.Match(response, @"(SELECT.*)", RegexOptions.Singleline | RegexOptions.IgnoreCase);
if (match.Success)
{
return match.Groups.Value.Trim();
}
// 返回清理后的响应
return response.Trim();
}
private async Task<SqlValidationResult> ValidateSqlAsync(string sql, DatabaseSchema schema)
{
// 检查是否为SELECT语句
var trimmed = sql.TrimStart().ToUpperInvariant();
if (!trimmed.StartsWith("SELECT"))
{
return new SqlValidationResult
{
IsValid = false,
Confidence = 0,
Error = "只支持SELECT查询语句"
};
}
// 提取使用的表名
var usedTables = ExtractTableNames(sql);
// 验证表是否存在
var validTables = schema.Tables.Select(t => t.Name.ToUpperInvariant()).ToHashSet();
foreach (var table in usedTables)
{
if (!validTables.Contains(table.ToUpperInvariant()))
{
return new SqlValidationResult
{
IsValid = false,
Confidence = 0,
Error = $"表 {table} 不存在"
};
}
}
return new SqlValidationResult
{
IsValid = true,
Confidence = 0.9
};
}
private List<string> ExtractTableNames(string sql)
{
var tables = new List<string>();
// 匹配FROM和JOIN后面的表名
var matches = Regex.Matches(sql, @"(?:FROM|JOIN)\s+(\[?\]?(\w+)\]?)",
RegexOptions.IgnoreCase);
foreach (Match match in matches)
{
tables.Add(match.Groups.Value);
}
return tables.Distinct().ToList();
}
private Dictionary<string, object> ExtractParameters(string sql)
{
var parameters = new Dictionary<string, object>();
// 提取参数（如 @参数名）
var matches = Regex.Matches(sql, @"@(\w+)");
foreach (Match match in matches)
{
var paramName = match.Groups.Value;
parameters = GetDefaultValue(paramName);
}
return parameters;
}
private object GetDefaultValue(string paramName)
{
// 根据参数名推断默认值
if (paramName.Contains("date", StringComparison.OrdinalIgnoreCase))
{
return DateTime.Now;
}
if (paramName.Contains("id", StringComparison.OrdinalIgnoreCase))
{
return 0;
}
if (paramName.Contains("name", StringComparison.OrdinalIgnoreCase))
{
return "";
}
return "";
}
}
public class SqlQueryResult
{
public bool Success { get; set; }
public string? Sql { get; set; }
public double Confidence { get; set; }
public string? Error { get; set; }
public Dictionary<string, object> Parameters { get; set; } = new();
}
public class SqlValidationResult
{
public bool IsValid { get; set; }
public double Confidence { get; set; }
public string? Error { get; set; }
}
</code></pre>
<h3 id="22-数据查询执行器">2.2 数据查询执行器</h3>
<pre><code class="language-csharp">// DataQueryExecutor.cs
public class DataQueryExecutor
{
private readonly string _connectionString;
private readonly IMetrics _metrics;
private readonly ILogger<DataQueryExecutor> _logger;
public DataQueryExecutor(
string connectionString,
IMetrics metrics,
ILogger<DataQueryExecutor> logger)
{
_connectionString = connectionString;
_metrics = metrics;
_logger = logger;
}
public async Task<QueryResult> ExecuteAsync(string sql, Dictionary<string, object>? parameters = null)
{
var stopwatch = Stopwatch.StartNew();
_logger.LogInformation("执行查询: {Sql}", sql);
try
{
using var connection = new SqlConnection(_connectionString);
await connection.OpenAsync();
using var command = new SqlCommand(sql, connection);
// 添加参数
if (parameters != null)
{
foreach (var param in parameters)
{
command.Parameters.AddWithValue($"@{param.Key}", param.Value ?? DBNull.Value);
}
}
// 执行查询
using var reader = await command.ExecuteReaderAsync();
// 获取列信息
var columns = new List<ColumnInfo>();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(new ColumnInfo
{
Name = reader.GetName(i),
Type = reader.GetFieldType(i).Name
});
}
// 读取数据
var rows = new List<Dictionary<string, object?>>();
while (await reader.ReadAsync())
{
var row = new Dictionary<string, object?>();
for (int i = 0; i < columns.Count; i++)
{
row.Name] = reader.IsDBNull(i) ? null : reader.GetValue(i);
}
rows.Add(row);
}
stopwatch.Stop();
_metrics.RecordQueryDuration(stopwatch.Elapsed);
_metrics.IncrementQueryCount();
_logger.LogInformation("查询成功: Rows={RowCount}, Duration={Duration}ms",
rows.Count, stopwatch.ElapsedMilliseconds);
return new QueryResult
{
Success = true,
Columns = columns,
Rows = rows,
RowCount = rows.Count,
ExecutionTime = stopwatch.Elapsed
};
}
catch (Exception ex)
{
stopwatch.Stop();
_metrics.RecordQueryError();
_logger.LogError(ex, "查询失败: {Sql}", sql);
return new QueryResult
{
Success = false,
Error = ex.Message,
ExecutionTime = stopwatch.Elapsed
};
}
}
}
public class QueryResult
{
public bool Success { get; set; }
public List<ColumnInfo> Columns { get; set; } = new();
public List<Dictionary<string, object?>> Rows { get; set; } = new();
public int RowCount { get; set; }
public TimeSpan ExecutionTime { get; set; }
public string? Error { get; set; }
}
public class ColumnInfo
{
public string Name { get; set; } = string.Empty;
public string Type { get; set; } = string.Empty;
}
</code></pre>
<h3 id="23-报表生成器">2.3 报表生成器</h3>
<pre><code class="language-csharp">// ReportGenerator.cs
public class ReportGenerator
{
private readonly IChartGenerator _chartGenerator;
private readonly ILogger<ReportGenerator> _logger;
public ReportGenerator(
IChartGenerator chartGenerator,
ILogger<ReportGenerator> logger)
{
_chartGenerator = chartGenerator;
_logger = logger;
}
public async Task<Report> GenerateAsync(
QueryResult queryResult,
ReportOptions options)
{
_logger.LogInformation("开始生成报表: Title={Title}", options.Title);
var report = new Report
{
Title = options.Title,
GeneratedAt = DateTime.UtcNow,
Options = options
};
// 1. 生成数据表格
if (options.IncludeTable)
{
report.Table = GenerateTable(queryResult);
}
// 2. 生成图表
if (options.Charts.Any())
{
report.Charts = await GenerateChartsAsync(queryResult, options.Charts);
}
// 3. 生成统计摘要
if (options.IncludeSummary)
{
report.Summary = GenerateSummary(queryResult);
}
// 4. 导出为指定格式
report.Content = options.Format switch
{
ReportFormat.Html => GenerateHtml(report),
ReportFormat.Excel => await GenerateExcelAsync(report),
ReportFormat.Pdf => await GeneratePdfAsync(report),
ReportFormat.Json => GenerateJson(report),
_ => GenerateHtml(report)
};
_logger.LogInformation("报表生成完成: Title={Title}, Format={Format}",
report.Title, options.Format);
return report;
}
private ReportTable GenerateTable(QueryResult result)
{
return new ReportTable
{
Headers = result.Columns.Select(c => c.Name).ToList(),
Rows = result.Rows.Select(r =>
result.Columns.Select(c => r.GetValueOrDefault(c.Name)?.ToString() ?? "").ToList()
).ToList(),
RowCount = result.RowCount
};
}
private async Task<List<ReportChart>> GenerateChartsAsync(
QueryResult result,
List<ChartOptions> chartOptions)
{
var charts = new List<ReportChart>();
foreach (var options in chartOptions)
{
var chartData = ExtractChartData(result, options);
var chartImage = await _chartGenerator.GenerateAsync(chartData, options);
charts.Add(new ReportChart
{
Title = options.Title,
Type = options.Type,
Data = chartData,
ImageBase64 = chartImage
});
}
return charts;
}
private ChartData ExtractChartData(QueryResult result, ChartOptions options)
{
var data = new ChartData
{
Labels = new List<string>(),
Datasets = new List<Dataset>()
};
// 简化实现：根据配置提取数据
var labelColumn = options.LabelColumn ?? result.Columns.FirstOrDefault()?.Name;
var valueColumn = options.ValueColumn ?? result.Columns.Skip(1).FirstOrDefault()?.Name;
if (labelColumn == null || valueColumn == null)
{
return data;
}
// 获取标签
data.Labels = result.Rows
.Take(10)
.Select(r => r.GetValueOrDefault(labelColumn)?.ToString() ?? "")
.ToList();
// 获取数值
var values = result.Rows
.Take(10)
.Select(r => Convert.ToDouble(r.GetValueOrDefault(valueColumn) ?? 0))
.ToList();
data.Datasets.Add(new Dataset
{
Label = valueColumn,
Values = values
});
return data;
}
private ReportSummary GenerateSummary(QueryResult result)
{
var summary = new ReportSummary
{
TotalRows = result.RowCount,
ExecutionTime = result.ExecutionTime,
ColumnCount = result.Columns.Count
};
// 计算数值列的统计信息
var numericColumns = result.Columns
.Where(c => IsNumericType(c.Type))
.ToList();
foreach (var column in numericColumns)
{
var values = result.Rows
.Select(r => r.GetValueOrDefault(column.Name))
.Where(v => v != null)
.Select(v => Convert.ToDouble(v))
.ToList();
if (values.Any())
{
summary.ColumnStats = new ColumnStats
{
Sum = values.Sum(),
Average = values.Average(),
Min = values.Min(),
Max = values.Max(),
Count = values.Count
};
}
}
return summary;
}
private bool IsNumericType(string type)
{
var numericTypes = new[] { "Int32", "Int64", "Decimal", "Double", "Float", "Single" };
return numericTypes.Contains(type, StringComparer.OrdinalIgnoreCase);
}
private string GenerateHtml(Report report)
{
// 生成HTML报表
var html = $"""
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>{report.Title}</title>
<style>
body {{ font-family: Arial, sans-serif; margin: 20px; }}
h1 {{ color: #333; }}
table {{ border-collapse: collapse; width: 100%; margin: 20px 0; }}
th, td {{ border: 1px solid #ddd; padding: 8px; text-align: left; }}
th {{ background-color: #4CAF50; color: white; }}
.chart {{ margin: 20px 0; }}
.summary {{ background-color: #f9f9f9; padding: 15px; margin: 20px 0; }}
</style>
</head>
<body>
<h1>{report.Title}</h1>
<p>生成时间：{report.GeneratedAt:yyyy-MM-dd HH:mm:ss}</p>
{GenerateSummaryHtml(report.Summary)}
{GenerateTableHtml(report.Table)}
{string.Join("\n", report.Charts.Select(GenerateChartHtml))}
</body>
</html>
""";
return html;
}
private string GenerateSummaryHtml(ReportSummary? summary)
{
if (summary == null) return "";
return $"""
<div class="summary">
<h2>统计摘要</h2>
<p>总行数：{summary.TotalRows}</p>
<p>执行时间：{summary.ExecutionTime.TotalMilliseconds}ms</p>
</div>
""";
}
private string GenerateTableHtml(ReportTable? table)
{
if (table == null || !table.Rows.Any()) return "";
var headers = string.Join("", table.Headers.Select(h => $"<th>{h}</th>"));
var rows = string.Join("", table.Rows.Select(r =>
$"<tr>{string.Join("", rows.Select(c => $"<td>{c}</td>"))}</tr>"));
return $"""
<table>
<thead><tr>{headers}</tr></thead>
<tbody>{rows}</tbody>
</table>
""";
}
private string GenerateChartHtml(ReportChart chart)
{
return $"""
<div class="chart">
<h3>{chart.Title}</h3>
<img src="data:image/png;base64,{chart.ImageBase64}" />
</div>
""";
}
private string GenerateJson(Report report)
{
return JsonSerializer.Serialize(report, new JsonSerializerOptions
{
WriteIndented = true
});
}
private async Task<byte[]> GenerateExcelAsync(Report report)
{
// 使用ClosedXML生成Excel
using var workbook = new XLWorkbook();
// 添加数据表
var worksheet = workbook.Worksheets.Add("数据");
if (report.Table != null)
{
// 写入表头
for (int i = 0; i < report.Table.Headers.Count; i++)
{
worksheet.Cell(1, i + 1).Value = report.Table.Headers;
}
// 写入数据
for (int row = 0; row < report.Table.Rows.Count; row++)
{
for (int col = 0; col < report.Table.Rows.Count; col++)
{
worksheet.Cell(row + 2, col + 1).Value = report.Table.Rows;
}
}
}
using var stream = new MemoryStream();
workbook.SaveAs(stream);
return await Task.FromResult(stream.ToArray());
}
private async Task<byte[]> GeneratePdfAsync(Report report)
{
// 使用QuestPDF生成PDF
var document = Document.Create(container =>
{
container.Page(page =>
{
page.Size(PageSizes.A4);
page.Margin(30);
page.DefaultTextStyle(x => x.FontSize(12));
page.Header().Element(c => c.Text(report.Title).Bold().FontSize(20));
page.Content().Element(c =>
{
c.Padding(10).Text($"生成时间：{report.GeneratedAt:yyyy-MM-dd HH:mm:ss}");
if (report.Summary != null)
{
c.Padding(10).Text($"总行数：{report.Summary.TotalRows}");
}
});
});
});
using var stream = new MemoryStream();
document.GeneratePdf(stream);
return await Task.FromResult(stream.ToArray());
}
}
</code></pre>
<h3 id="24-数据分析agent">2.4 数据分析Agent</h3>
<p>整合所有组件，创建完整的数据分析Agent：</p>
<pre><code class="language-csharp">// DataAnalysisAgent.cs
public class DataAnalysisAgent
{
private readonly NLToSqlConverter _sqlConverter;
private readonly DataQueryExecutor _queryExecutor;
private readonly ReportGenerator _reportGenerator;
private readonly IConversationManager _conversationManager;
private readonly ILogger<DataAnalysisAgent> _logger;
public DataAnalysisAgent(
NLToSqlConverter sqlConverter,
DataQueryExecutor queryExecutor,
ReportGenerator reportGenerator,
IConversationManager conversationManager,
ILogger<DataAnalysisAgent> logger)
{
_sqlConverter = sqlConverter;
_queryExecutor = queryExecutor;
_reportGenerator = reportGenerator;
_conversationManager = conversationManager;
_logger = logger;
}
public async Task<AnalysisResult> AnalyzeAsync(
string userId,
string conversationId,
string naturalLanguageQuery,
AnalysisOptions? options = null)
{
options ??= new AnalysisOptions();
_logger.LogInformation("开始分析: UserId={UserId}, Query={Query}",
userId, naturalLanguageQuery);
var result = new AnalysisResult
{
UserQuery = naturalLanguageQuery,
AnalyzedAt = DateTime.UtcNow
};
try
{
// 1. 自然语言转SQL
var sqlResult = await _sqlConverter.ConvertAsync(
naturalLanguageQuery,
options.Context);
if (!sqlResult.Success)
{
result.Success = false;
result.Error = $"SQL生成失败：{sqlResult.Error}";
return result;
}
result.GeneratedSql = sqlResult.Sql;
result.Confidence = sqlResult.Confidence;
// 2. 执行SQL查询
var queryResult = await _queryExecutor.ExecuteAsync(
sqlResult.Sql!,
sqlResult.Parameters);
if (!queryResult.Success)
{
result.Success = false;
result.Error = $"查询执行失败：{queryResult.Error}";
return result;
}
result.QueryResult = queryResult;
// 3. 生成响应
result.Response = GenerateResponse(queryResult, options);
// 4. 生成报表（如需要）
if (options.GenerateReport)
{
var reportOptions = new ReportOptions
{
Title = options.ReportTitle ?? $"数据分析报告 - {DateTime.Now:yyyyMMdd}",
Format = options.ReportFormat ?? ReportFormat.Html,
IncludeTable = true,
IncludeSummary = true,
Charts = options.Charts ?? new List<ChartOptions>()
};
var report = await _reportGenerator.GenerateAsync(queryResult, reportOptions);
result.Report = report;
}
result.Success = true;
_logger.LogInformation("分析完成: UserId={UserId}, Rows={RowCount}",
userId, queryResult.RowCount);
}
catch (Exception ex)
{
_logger.LogError(ex, "分析失败: UserId={UserId}", userId);
result.Success = false;
result.Error = $"分析过程出错：{ex.Message}";
}
return result;
}
private string GenerateResponse(QueryResult queryResult, AnalysisOptions options)
{
if (!queryResult.Rows.Any())
{
return "查询结果为空，没有找到匹配的数据。";
}
var response = new StringBuilder();
// 生成数据摘要
response.AppendLine($"查询完成，共找到 {queryResult.RowCount} 条数据。");
response.AppendLine($"查询耗时：{queryResult.ExecutionTime.TotalMilliseconds}毫秒。");
// 尝试生成洞察
if (queryResult.RowCount <= 10)
{
// 数据量小，展示完整数据
response.AppendLine("\n数据详情：");
foreach (var row in queryResult.Rows.Take(5))
{
var items = row.Select(kvp => $"{kvp.Key}: {kvp.Value}");
response.AppendLine($"- {string.Join(", ", items)}");
}
}
else
{
// 数据量大，展示统计摘要
var summary = GenerateQuickSummary(queryResult);
response.AppendLine("\n" + summary);
}
// 添加后续建议
if (options.GenerateReport == false)
{
response.AppendLine("\n如需生成报表，请告诉我"生成报表"或"导出Excel"。");
}
return response.ToString();
}
private string GenerateQuickSummary(QueryResult result)
{
var summary = new StringBuilder("数据概览：\n");
// 查找数值列并生成统计
var numericColumns = result.Columns
.Where(c => IsNumericType(c.Type))
.ToList();
foreach (var column in numericColumns.Take(3))
{
var values = result.Rows
.Select(r => r.GetValueOrDefault(column.Name))
.Where(v => v != null)
.Select(v => Convert.ToDouble(v))
.ToList();
if (values.Any())
{
summary.AppendLine($"- {column.Name}: 总计 {values.Sum():N2}, 平均 {values.Average():N2}, 最大 {values.Max():N2}, 最小 {values.Min():N2}");
}
}
return summary.ToString();
}
private bool IsNumericType(string type)
{
var numericTypes = new[] { "Int32", "Int64", "Decimal", "Double", "Float", "Single" };
return numericTypes.Contains(type, StringComparer.OrdinalIgnoreCase);
}
}
public class AnalysisOptions
{
public string? Context { get; set; }
public bool GenerateReport { get; set; }
public string? ReportTitle { get; set; }
public ReportFormat ReportFormat { get; set; }
public List<ChartOptions> Charts { get; set; } = new();
}
public class AnalysisResult
{
public bool Success { get; set; }
public string UserQuery { get; set; } = string.Empty;
public string? GeneratedSql { get; set; }
public double Confidence { get; set; }
public QueryResult? QueryResult { get; set; }
public string? Response { get; set; }
public Report? Report { get; set; }
public string? Error { get; set; }
public DateTime AnalyzedAt { get; set; }
}
public enum ReportFormat
{
Html,
Excel,
Pdf,
Json
}
</code></pre>
<h2 id="三图表生成">三、图表生成</h2>
<h3 id="31-图表生成器">3.1 图表生成器</h3>
<pre><code class="language-csharp">// ChartGenerator.cs
public class ChartGenerator : IChartGenerator
{
private readonly ILogger<ChartGenerator> _logger;
public ChartGenerator(ILogger<ChartGenerator> logger)
{
_logger = logger;
}
public async Task<string> GenerateAsync(ChartData data, ChartOptions options)
{
_logger.LogInformation("生成图表: Type={Type}, Title={Title}",
options.Type, options.Title);
// 使用SkiaSharp生成图表
using var surface = SKSurface.Create(new SKImageInfo(800, 400));
var canvas = surface.Canvas;
// 背景
canvas.Clear(SKColors.White);
// 绘制标题
using var titlePaint = new SKPaint
{
Color = SKColors.Black,
TextSize = 20,
IsAntialias = true,
Typeface = SKTypeface.FromFamilyName("Arial", SKFontStyle.Bold)
};
canvas.DrawText(options.Title, 20, 30, titlePaint);
// 根据类型绘制图表
switch (options.Type)
{
case ChartType.Bar:
DrawBarChart(canvas, data, 60, 60, 700, 300);
break;
case ChartType.Line:
DrawLineChart(canvas, data, 60, 60, 700, 300);
break;
case ChartType.Pie:
DrawPieChart(canvas, data, 400, 200, 150);
break;
}
// 导出为PNG
using var image = surface.Snapshot();
using var pngData = image.Encode(SKEncodedImageFormat.Png, 100);
using var stream = new MemoryStream();
pngData.SaveTo(stream);
return Convert.ToBase64String(stream.ToArray());
}
private void DrawBarChart(SKCanvas canvas, ChartData data, float x, float y, float width, float height)
{
if (!data.Datasets.Any() || !data.Labels.Any()) return;
var values = data.Datasets.Values;
var maxValue = values.Max();
var barWidth = width / values.Count - 10;
var scale = height / maxValue;
using var barPaint = new SKPaint
{
Color = SKColor.Parse("#4CAF50"),
Style = SKPaintStyle.Fill
};
using var textPaint = new SKPaint
{
Color = SKColors.Black,
TextSize = 12,
IsAntialias = true
};
for (int i = 0; i < values.Count; i++)
{
var barHeight = values * scale;
var barX = x + i * (barWidth + 10);
var barY = y + height - barHeight;
// 绘制柱状
canvas.DrawRect(barX, barY, barWidth, barHeight, barPaint);
// 绘制标签
if (i < data.Labels.Count)
{
canvas.DrawText(data.Labels, barX, y + height + 20, textPaint);
}
}
}
private void DrawLineChart(SKCanvas canvas, ChartData data, float x, float y, float width, float height)
{
if (!data.Datasets.Any() || !data.Labels.Any()) return;
var values = data.Datasets.Values;
var maxValue = values.Max();
var minValue = values.Min();
var range = maxValue - minValue;
var pointSpacing = width / (values.Count - 1);
var scale = height / (range > 0 ? range : 1);
using var linePaint = new SKPaint
{
Color = SKColor.Parse("#2196F3"),
StrokeWidth = 3,
Style = SKPaintStyle.Stroke,
IsAntialias = true
};
using var pointPaint = new SKPaint
{
Color = SKColor.Parse("#2196F3"),
Style = SKPaintStyle.Fill,
IsAntialias = true
};
var path = new SKPath();
for (int i = 0; i < values.Count; i++)
{
var px = x + i * pointSpacing;
var py = y + height - (float)((values - minValue) * scale);
if (i == 0)
{
path.MoveTo(px, py);
}
else
{
path.LineTo(px, py);
}
}
canvas.DrawPath(path, linePaint);
// 绘制数据点
for (int i = 0; i < values.Count; i++)
{
var px = x + i * pointSpacing;
var py = y + height - (float)((values - minValue) * scale);
canvas.DrawCircle(px, py, 4, pointPaint);
}
}
private void DrawPieChart(SKCanvas canvas, ChartData data, float cx, float cy, float radius)
{
if (!data.Datasets.Any()) return;
var values = data.Datasets.Values;
var total = values.Sum();
var colors = new[]
{
SKColor.Parse("#FF6384"),
SKColor.Parse("#36A2EB"),
SKColor.Parse("#FFCE56"),
SKColor.Parse("#4BC0C0"),
SKColor.Parse("#9966FF")
};
var startAngle = -90f;
for (int i = 0; i < values.Count; i++)
{
var sweepAngle = (float)(values / total * 360);
using var paint = new SKPaint
{
Color = colors,
Style = SKPaintStyle.Fill,
IsAntialias = true
};
var rect = new SKRect(cx - radius, cy - radius, cx + radius, cy + radius);
using var path = new SKPath();
path.MoveTo(cx, cy);
path.ArcTo(rect, startAngle, sweepAngle, false);
path.Close();
canvas.DrawPath(path, paint);
startAngle += sweepAngle;
}
}
}
</code></pre>
<h2 id="四api接口">四、API接口</h2>
<h3 id="41-web-api定义">4.1 Web API定义</h3>
<pre><code class="language-csharp">// AnalysisController.cs
")]
public class AnalysisController : ControllerBase
{
private readonly DataAnalysisAgent _agent;
private readonly ILogger<AnalysisController> _logger;
public AnalysisController(
DataAnalysisAgent agent,
ILogger<AnalysisController> logger)
{
_agent = agent;
_logger = logger;
}
public async Task<ActionResult<AnalysisResponse>> Query(
AnalysisRequest request,
string userId)
{
if (string.IsNullOrEmpty(request.Query))
{
return BadRequest("查询语句不能为空");
}
var conversationId = request.ConversationId ?? Guid.NewGuid().ToString();
var options = new AnalysisOptions
{
Context = request.Context,
GenerateReport = request.GenerateReport,
ReportTitle = request.ReportTitle,
ReportFormat = request.ReportFormat ?? ReportFormat.Html,
Charts = request.Charts ?? new List<ChartOptions>()
};
var result = await _agent.AnalyzeAsync(userId, conversationId, request.Query, options);
var response = new AnalysisResponse
{
Success = result.Success,
Query = result.UserQuery,
Sql = result.GeneratedSql,
Response = result.Response,
Confidence = result.Confidence,
RowCount = result.QueryResult?.RowCount ?? 0,
ExecutionTime = result.QueryResult?.ExecutionTime.TotalMilliseconds ?? 0,
ReportUrl = result.Report != null ? $"/api/reports/{result.Report.Id}" : null,
Error = result.Error
};
if (result.Success)
{
return Ok(response);
}
else
{
return BadRequest(response);
}
}
public async Task<IActionResult> GetReport(string reportId)
{
// 获取报表内容
// 实现略
return NotFound();
}
public async Task<ActionResult<SchemaInfo>> GetSchema()
{
// 获取数据库模式信息
// 实现略
return Ok(new SchemaInfo());
}
}
public class AnalysisRequest
{
public string Query { get; set; } = string.Empty;
public string? ConversationId { get; set; }
public string? Context { get; set; }
public bool GenerateReport { get; set; }
public string? ReportTitle { get; set; }
public ReportFormat? ReportFormat { get; set; }
public List<ChartOptions>? Charts { get; set; }
}
public class AnalysisResponse
{
public bool Success { get; set; }
public string Query { get; set; } = string.Empty;
public string? Sql { get; set; }
public string? Response { get; set; }
public double Confidence { get; set; }
public int RowCount { get; set; }
public double ExecutionTime { get; set; }
public string? ReportUrl { get; set; }
public string? Error { get; set; }
}
</code></pre>
<h2 id="五总结与展望">五、总结与展望</h2>
<p>通过本文的学习，我们完成了数据分析智能助手的完整实现：</p>
<ol>
<li>✅ <strong>自然语言转SQL</strong>：将用户的自然语言查询转换为SQL语句</li>
<li>✅ <strong>数据查询执行</strong>：安全高效地执行数据库查询</li>
<li>✅ <strong>报表生成</strong>：支持HTML、Excel、PDF多种格式</li>
<li>✅ <strong>图表生成</strong>：支持柱状图、折线图、饼图等多种图表</li>
<li>✅ <strong>完整API</strong>：提供Web API接口供外部调用</li>
</ol>
<p>这是本系列文章的最后一篇。通过这10篇文章，我们从基础概念到实战应用，系统地学习了Microsoft Agent Framework的核心知识和实践技巧。</p>
<h2 id="系列总结">系列总结</h2>
<p><strong>本系列涵盖的内容：</strong></p>
<p>第一部分：基础入门</p>
<ul>
<li>Agent Framework介绍与环境搭建</li>
<li>第一个智能体的创建</li>
<li>工具集成</li>
</ul>
<p>第二部分：核心功能</p>
<ul>
<li>多轮对话与状态管理</li>
<li>记忆与持久化</li>
<li>工作流编排</li>
</ul>
<p>第三部分：高级应用</p>
<ul>
<li>自定义工具与中间件开发</li>
<li>监控与可观测性</li>
</ul>
<p>第四部分：实战案例</p>
<ul>
<li>企业客服智能助手</li>
<li>数据分析智能助手</li>
</ul>
<p><strong>下一步学习建议：</strong></p>
<ol>
<li>深入学习LLM提示工程技巧</li>
<li>探索多Agent协作系统</li>
<li>学习向量数据库和知识图谱</li>
<li>关注AI安全和对齐问题</li>
</ol>
<p><strong>相关资源：</strong></p>
<ul>
<li>Agent Framework官方文档</li>
<li>OpenAI API文档</li>
<li>QuestPDF报表生成</li>
</ul>
<hr>
<p><em>"数据分析不应该只是专家的专利，每个人都应该能够用自然语言探索数据的价值。"</em></p><br><br>
来源：https://www.cnblogs.com/fanshaoO/p/19709785
頁:
[1]