更新 Excel 資料庫記錄

VB C#

using IronXL;
using System.Data;
using System.Data.SqlClient;

// Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV
WorkBook workBook = WorkBook.Load("sample.xlsx");

// Convert the workbook to ToDataSet
DataSet dataSet = workBook.ToDataSet();

// Your sql query
string sql = "SELECT * FROM Users";

// Your connection string
string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    // Open connections to the database
    connection.Open();
    SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);

    // Update the values in database using the values in Excel
    adapter.Update(dataSet);
}

Imports IronXL
Imports System.Data
Imports System.Data.SqlClient

' Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV
Private workBook As WorkBook = WorkBook.Load("sample.xlsx")

' Convert the workbook to ToDataSet
Private dataSet As DataSet = workBook.ToDataSet()

' Your sql query
Private sql As String = "SELECT * FROM Users"

' Your connection string
Private connectionString As String = "Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True"
Using connection As New SqlConnection(connectionString)
	' Open connections to the database
	connection.Open()
	Dim adapter As New SqlDataAdapter(sql, connection)

	' Update the values in database using the values in Excel
	adapter.Update(dataSet)
End Using

Install-Package IronXL.Excel

更新 Excel 資料庫記錄

IronXL 的ToDataSet方法允許開發人員以最少的努力直接從 Excel 資料同步和更新 SQL 資料庫記錄。此功能透過將 Excel 工作簿轉換為DataSet ，實現了表格 Excel 資料與關聯式資料庫之間的無縫整合。它無需手動輸入資料或進行複雜的轉換，這大大簡化了資料管理工作流程。

此功能對於需要定期從外部 Excel 檔案更新資料庫記錄的應用程式（例如客戶資料、財務記錄或庫存管理系統）尤其有用。與 SQL 查詢和適配器的相容性使開發人員能夠將 Excel 資料對應到現有的資料庫架構。 IronXL 支援多種 Excel 格式（XLSX、CSV 等），確保在各種使用情境中的通用性。此功能利用SqlDataAdapter進行大量更新，從而減少資料處理時間，最大限度地減少錯誤，並確保 Excel 和資料庫記錄之間的一致性，使其成為企業級應用程式的理想解決方案。

五個步驟教你如何將 Excel 資料更新到 SQL 資料庫

var workBook = WorkBook.Load("Products.xlsx");
DataSet dataSet = workBook.ToDataSet();
string sql = "SELECT * FROM products";
使用 var adapter = new SqlDataAdapter(sql, new SqlConnection("Your Connection String"));
new SqlCommandBuilder(adapter).DataAdapter.Update(dataSet);

本代碼展示了使用 IronXL 和 ADO.NET 從 Excel 檔更新 SQL 資料庫資料的過程。 The WorkBook.Load method loads the Excel file named Products.xlsx into memory, and the ToDataSet method converts the workbook into a DataSet, which organizes the data into a tabular structure compatible with ADO.NET.

A SQL query is defined to interact with a specific table in the database (products). The SqlDataAdapter is initialized with the query and a connection string, enabling it to act as a bridge between the DataSet and the database. The SqlCommandBuilder is used to automatically generate the necessary SQL commands for inserting, updating, or deleting data in the database. Finally, the adapter.Update(dataSet) method synchronizes the changes in the DataSet (populated from the Excel file) with the corresponding database table. 這樣可以簡化資料傳輸過程，並確保資料庫反映 Excel 文件中的最新變更。

了解如何使用 IronXL 將資料集匯出到 Excel