C# WPF报表应用：SQLite多表合并及HTML导出需求

针对你的需求，我整理了一套从数据合并到HTML导出的完整实现思路，咱们一步步来搞定：

1. 数据库层面完成数据累加与插入（高效方案） #

与其在C#代码里先读数据再处理，不如直接用SQLite的聚合查询完成Table1和Table2的数值累加，同时插入Table3——这样性能更优，也减少了代码复杂度。

核心SQL语句 #

用INSERT INTO ... SELECT的组合语句，一次性完成合并与插入：

INSERT INTO Table3 (name, amntOne, amntTwo, location, entryDate)
SELECT 
    COALESCE(t1.name, t2.name) AS name,
    COALESCE(SUM(t1.amntOne), 0) + COALESCE(SUM(t2.amntOne), 0) AS amntOne,
    COALESCE(SUM(t1.amntTwo), 0) + COALESCE(SUM(t2.amntTwo), 0) AS amntTwo,
    -- Table1没有location列，这里默认取Table2的location，你可以根据需求改成固定值
    MAX(t2.location) AS location,
    -- 统一设为当前时间，也可以改成取两个表中最新的entryDate
    DATETIME('now') AS entryDate
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.name = t2.name
GROUP BY COALESCE(t1.name, t2.name);

注：如果你的SQLite版本不支持FULL OUTER JOIN，可以用LEFT JOIN+UNION的方式替代，确保不会遗漏任何name的数据。

C#代码执行SQL #

先通过NuGet安装System.Data.SQLite.Core包，然后在WPF后台代码中执行SQL：

using System.Data.SQLite;
using System.Windows;

private void MergeAndInsertData()
{
    string dbPath = @"./你的数据库文件路径（比如ReportDB.db）";
    string connectionString = $"Data Source={dbPath};Version=3;";

    // 替换成兼容低版本SQLite的SQL语句
    string mergeSql = @"
        INSERT INTO Table3 (name, amntOne, amntTwo, location, entryDate)
        SELECT 
            COALESCE(t1.name, t2.name) AS name,
            COALESCE(SUM(t1.amntOne), 0) + COALESCE(SUM(t2.amntOne), 0) AS amntOne,
            COALESCE(SUM(t1.amntTwo), 0) + COALESCE(SUM(t2.amntTwo), 0) AS amntTwo,
            MAX(t2.location) AS location,
            DATETIME('now') AS entryDate
        FROM Table1 t1
        LEFT JOIN Table2 t2 ON t1.name = t2.name
        GROUP BY COALESCE(t1.name, t2.name)
        UNION
        SELECT 
            t2.name AS name,
            COALESCE(SUM(t1.amntOne), 0) + COALESCE(SUM(t2.amntOne), 0) AS amntOne,
            COALESCE(SUM(t1.amntTwo), 0) + COALESCE(SUM(t2.amntTwo), 0) AS amntTwo,
            MAX(t2.location) AS location,
            DATETIME('now') AS entryDate
        FROM Table2 t2
        LEFT JOIN Table1 t1 ON t2.name = t1.name
        WHERE t1.name IS NULL
        GROUP BY t2.name;";

    try
    {
        using (SQLiteConnection conn = new SQLiteConnection(connectionString))
        {
            conn.Open();
            using (SQLiteCommand cmd = new SQLiteCommand(mergeSql, conn))
            {
                int rowsAffected = cmd.ExecuteNonQuery();
                MessageBox.Show($"成功插入{rowsAffected}条数据到Table3！");
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show($"数据合并失败：{ex.Message}");
    }
}

2. 生成HTML格式的导出表格 #

从Table3读取数据后，拼接成带基础样式的HTML表格，方便保存或预览。

C#代码生成HTML #

using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Windows;
using System.Windows.Controls;

private string GenerateHtmlReport()
{
    string dbPath = @"./你的数据库文件路径";
    string connectionString = $"Data Source={dbPath};Version=3;";
    List<Table3Model> dataList = new List<Table3Model>();

    // 读取Table3数据
    string selectSql = "SELECT id, name, amntOne, amntTwo, location, entryDate FROM Table3;";
    try
    {
        using (SQLiteConnection conn = new SQLiteConnection(connectionString))
        {
            conn.Open();
            using (SQLiteCommand cmd = new SQLiteCommand(selectSql, conn))
            {
                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        dataList.Add(new Table3Model
                        {
                            Id = reader.GetInt32(0),
                            Name = reader.GetString(1),
                            AmntOne = reader.GetDecimal(2),
                            AmntTwo = reader.GetDecimal(3),
                            Location = reader.IsDBNull(4) ? "无" : reader.GetString(4),
                            EntryDate = reader.GetString(5)
                        });
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show($"读取数据失败：{ex.Message}");
        return string.Empty;
    }

    // 拼接HTML内容
    StringBuilder htmlBuilder = new StringBuilder();
    htmlBuilder.Append(@"
    <!DOCTYPE html>
    <html lang='zh-CN'>
    <head>
        <meta charset='UTF-8'>
        <title>报表汇总</title>
        <style>
            table { border-collapse: collapse; width: 90%; margin: 2rem auto; }
            th, td { border: 1px solid #ddd; padding: 0.8rem; text-align: left; }
            th { background-color: #f5f5f5; font-weight: bold; }
            tr:nth-child(even) { background-color: #f9f9f9; }
            h2 { text-align: center; color: #333; }
        </style>
    </head>
    <body>
        <h2>报表汇总数据</h2>
        <table>
            <tr>
                <th>ID</th>
                <th>名称</th>
                <th>金额1</th>
                <th>金额2</th>
                <th>位置</th>
                <th>录入日期</th>
            </tr>");

    foreach (var item in dataList)
    {
        htmlBuilder.AppendFormat(@"
            <tr>
                <td>{0}</td>
                <td>{1}</td>
                <td>{2:C}</td>
                <td>{3:C}</td>
                <td>{4}</td>
                <td>{5}</td>
            </tr>",
            item.Id, item.Name, item.AmntOne, item.AmntTwo, item.Location, item.EntryDate);
    }

    htmlBuilder.Append(@"
        </table>
    </body>
    </html>");

    return htmlBuilder.ToString();
}

// 对应的实体类
public class Table3Model
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal AmntOne { get; set; }
    public decimal AmntTwo { get; set; }
    public string Location { get; set; }
    public string EntryDate { get; set; }
}

保存HTML文件 #

在WPF按钮点击事件中调用生成方法，弹出保存对话框：

private void btnExportHtml_Click(object sender, RoutedEventArgs e)
{
    string htmlContent = GenerateHtmlReport();
    if (string.IsNullOrEmpty(htmlContent)) return;

    var saveFileDialog = new SaveFileDialog();
    saveFileDialog.Filter = "HTML文件 (*.html)|*.html|所有文件 (*.*)|*.*";
    saveFileDialog.FileName = $"报表_{DateTime.Now:yyyyMMddHHmmss}.html";

    if (saveFileDialog.ShowDialog() == true)
    {
        File.WriteAllText(saveFileDialog.FileName, htmlContent, Encoding.UTF8);
        MessageBox.Show("HTML报表导出成功！");
    }
}

3. WPF界面集成 #

在窗口XAML中添加触发按钮：

<Window x:Class="ReportApp.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="简易报表应用" Height="300" Width="400">
    <StackPanel VerticalAlignment="Center" HorizontalAlignment="Center" Spacing="20">
        <Button Content="合并Table1/Table2到Table3" Click="MergeAndInsertData_Click" Width="280"/>
        <Button Content="导出HTML报表" Click="btnExportHtml_Click" Width="280"/>
    </StackPanel>
</Window>

记得在后台实现MergeAndInsertData_Click方法，直接调用前面的MergeAndInsertData()即可。

内容的提问来源于stack exchange，提问作者u4547878