极速40分钟写出SQLite数据展示与导出功能【技术创作101训练营】

public class MyConnDB : SQLiteConnection
{
    public TableQuery<card_log> Card_log { get { return this.Table<card_log>(); } }
    public TableQuery<card_no> Card_no { get { return this.Table<card_no>(); } }
    public MyConnDB(string dbPath) : base(dbPath)
    {
        // CreateTable<card_log>(); // 创建数据表
    }
}

dbPath = $"{Environment.CurrentDirectory}\DB.db"; // 引用SQLite的数据文件
using (var db = new MyConnDB(dbPath))
{
    var Card_no = db.Card_no.ToList();
    var Card_log = db.Card_log.ToList();
    var Card_totle = db.Card_log.GroupBy(p => p.IDCardNo)
        .Select(group => new { IDCardNo = group.Key, Count = group.Count() })
        .ToList();
    var result = from c in Card_log
                 join p in Card_no on c.IDCardNo equals p.IDCardNo
                 join t in Card_totle on c.IDCardNo equals t.IDCardNo
                 select new { 卡号 = c.IDCardNo, 姓名 = p.UName, 刷卡日期 = c.Itime, 累计刷卡数 = t.Count, 记录时间 = c.VCreatedTime };
    this.label1.Text = $"{DateTime.Now.ToLongDateString()}, 查到{Card_log.Count}条记录";
    this.dataGridView1.DataSource = result.ToList();
}

/// <summary>
/// 导出Excel
/// </summary>
/// <param name="fileName">文件路径</param>
/// <param name="myDGV">控件DataGridView</param>
private void ExportExcels(string fileName, DataGridView myDGV)
{
    string saveFileName = "";
    SaveFileDialog saveDialog = new SaveFileDialog();
    saveDialog.DefaultExt = "xls";
    saveDialog.Filter = "Excel文件|*.xls";
    saveDialog.FileName = fileName;
    saveDialog.ShowDialog();
    saveFileName = saveDialog.FileName;
    if (saveFileName.IndexOf(":") < 0) return; // 被点了取消
    Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
    if (xlApp == null)
    {
        MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
        return;
    }
    Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
    Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
    Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
    //写入标题
    for (int i = 0; i < myDGV.ColumnCount; i++)
    {
        worksheet.Cells[1, i + 1] = myDGV.Columns[i].HeaderText;
    }
    //写入数值
    for (int r = 0; r < myDGV.Rows.Count; r++)
    {
        for (int i = 0; i < myDGV.ColumnCount; i++)
        {
            worksheet.Cells[r + 2, i + 1] = myDGV.Rows[r].Cells[i].Value;
        }
        System.Windows.Forms.Application.DoEvents();
    }
    worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
    if (saveFileName != "")
    {
        try
        {
            workbook.Saved = true;
            workbook.SaveCopyAs(saveFileName);
        }
        catch (Exception ex)
        {
            MessageBox.Show("导出文件时出错,文件可能正被打开！
" + ex.Message);
        }
    }
    xlApp.Quit();
    GC.Collect();//强行销毁 
    MessageBox.Show("文件已导出成功", "提示", MessageBoxButtons.OK);
}

private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
{
    this.dataGridView1.TopLeftHeaderCell.Value = "序号";
    Rectangle rectangle = new Rectangle(e.RowBounds.Location.X, e.RowBounds.Location.Y, this.dataGridView1.RowHeadersWidth - 4, e.RowBounds.Height);
    TextRenderer.DrawText(e.Graphics, (e.RowIndex + 1).ToString(),
        this.dataGridView1.RowHeadersDefaultCellStyle.Font,
        rectangle,
        this.dataGridView1.RowHeadersDefaultCellStyle.ForeColor,
        TextFormatFlags.VerticalCenter | TextFormatFlags.Right);
}