C#winform点击按钮下载数据库中表的字段到Excel上

C#winform点击按钮下载数据库中表的字段到Excel上

需求：C#winform点击按钮下载数据库中表的字段到Excel，并计算下载消耗的时间以及文件存放位置。

C#实现

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Net;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using OfficeOpenXml;
using System.Threading.Tasks;
using Newtonsoft.Json;
using System.Net.Http;

namespace WindowsFormsApp1
{
    public partial class 下载表字段 : Form
    {
		/ 数据库连接字符串
        private string connectionString = "server=127.0.0.1;uid=sa;pwd=xyz@0123456;database=test";
		
		private async void buttonDownload_Click(object sender, EventArgs e)
        {
            using (SaveFileDialog saveFileDialog = new SaveFileDialog())
            {
                saveFileDialog.Filter = "Excel files (*.xlsx)|*.xlsx";
                saveFileDialog.Title = "Save Excel Template";
                saveFileDialog.FileName = "用户模板";
                if (saveFileDialog.ShowDialog() == DialogResult.OK)
                {
                    string savePath = saveFileDialog.FileName;

                    try
                    {
                        // 连接到 SQL Server 数据库
                        using (SqlConnection sqlConnection = new SqlConnection(connectionString))
                        {
                            await sqlConnection.OpenAsync();

                            // 从数据库中读取字段名
                            using (SqlCommand command = new SqlCommand("SELECT * FROM Employee", sqlConnection))
                            using (SqlDataReader reader = await command.ExecuteReaderAsync())
                            {
                                // 获取字段数量
                                int totalColumns = reader.FieldCount;

                                // 初始化进度条
                                progressBar1.Maximum = totalColumns + 1;
                                progressBar1.Value = 0;

                                // 创建 Excel 文件
                                using (ExcelPackage excelPackage = new ExcelPackage())
                                {
                                    ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Template");

                                    // 写入字段名
                                    int columnIndex = 1;
                                    for (int i = 0; i < totalColumns; i++)
                                    {
                                        worksheet.Cells[1, columnIndex].Value = reader.GetName(i);
                                        columnIndex++;

                                        // 更新进度条
                                        progressBar1.Value++;
                                        await Task.Delay(10); // 延时以更新进度条显示
                                    }

                                    excelPackage.SaveAs(new FileInfo(savePath));
                                }
                            }



                            // 计算所导出Excel消耗的时间
                            //TimeSpan elapsedTime = DateTime.Now - startTime;
                            //MessageBox.Show("导出Excel消耗时间: " + elapsedTime.ToString("g"));
                            // 计算所导出 Excel 消耗的时间（秒）
                            TimeSpan elapsedTime = DateTime.Now - startTime;
                            double seconds = elapsedTime.TotalSeconds;

                            //MessageBox.Show("导出 Excel 消耗时间: " + seconds.ToString("F2") + " 秒");
                            MessageBox.Show("下载完成。" + "文件存放位置:" + savePath + "导出 Excel 消耗时间: " + seconds.ToString("F2") + " 秒");
                            // 打开文件
                            //System.Diagnostics.Process.Start(savePath);
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("下载出错：" + ex.Message);
                    }
                }
            }
		}

    }
}