C# 使用 EPPlus 库操作 Excel 文件并导入数据到数据库

using OfficeOpenXml; using System.Data;

namespace WinFormsApp1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); }

    private void button1_Click(object sender, EventArgs e)
    {
        // 创建一个新的 Excel 文件
        var fileInfo = new FileInfo('example.xlsx');
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        using (var package = new ExcelPackage(fileInfo))
        {
            // 添加一个工作表
            var worksheet = package.Workbook.Worksheets.Add('Sheet1');

            // 填充表头
            worksheet.Cells[1, 1].Value = '序号';

            // 填充数据
            for (int i = 2; i <= 11; i++)
            {
                worksheet.Cells[i, 1].Value = i - 1;
            }

            for (int i = 2; i <= 11; i++)
            {
                for (int j = 2; j <= 11; j++)
                {
                    worksheet.Cells[i, j].Value = (i - 2) * 10 + j - 1;
                }
            }

            // 保存 Excel 文件
            package.Save();
        }
    }

    private void button2_Click(object sender, EventArgs e)
    {

    }


    // 根据Excel文件路径和数据库连接字符串导入数据到数据库中
    public void ImportDataFromExcelToDatabase(string excelFilePath, string connectionString)
    {
        // Excel连接字符串
        string excelConnectionString = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' + excelFilePath
            + ';Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'';

        // 创建OleDbConnection对象，并打开连接
        using (OleDbConnection excelConnection = new OleDbConnection(excelConnectionString))
        {
            excelConnection.Open();

            // 查询Excel工作表中的数据
            string selectCommand = 'SELECT * FROM [Sheet1$]';
            using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(selectCommand, excelConnection))
            {
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);

                // 创建SqlConnection对象，并打开连接
                using (SqlConnection sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();

                    // 向数据库中插入数据
                    foreach (DataRow dataRow in dataTable.Rows)
                    {
                        SqlCommand sqlCommand = new SqlCommand('INSERT INTO YourTableName VALUES (@Column1, @Column2, @Column3)', sqlConnection);
                        sqlCommand.Parameters.AddWithValue('@Column1', dataRow['Column1']);
                        sqlCommand.Parameters.AddWithValue('@Column2', dataRow['Column2']);
                        sqlCommand.Parameters.AddWithValue('@Column3', dataRow['Column3']);
                        sqlCommand.ExecuteNonQuery();
                    }

                    sqlConnection.Close();
                }
            }

            excelConnection.Close();
        }
    }

}

}