How to create a sample to sync Excel file with database using XlsIO?
An Excel file can be generated based on a selected value from database using XlsIO. The following steps illustrates to sync an Excel file with database.
Step 1: Create a database and load values of a specific field from the database to a dropdown on the web form.
Step 2: Select an ID from the provided drop down to download an excel file.
Step 3: Make some changes on the downloaded file. Then upload it using upload button.
Step 4: Now the changes are made in the database. It can be viewed in a grid after clicking the Upload Excel button.
Here is the code snippet to pull values from the database.
C#
OleDbConnection oleDB = new OleDbConnection(); oleDB.ConnectionString = string.Format(ConnectionString, ResolveApplicationDataPath(DBFileName, false)); OleDbCommand command = new OleDbCommand("SELECT CustomerID FROM Customers", oleDB); oleDB.Open(); OleDbDataReader reader = command.ExecuteReader(); while (reader.Read()) { string customerID = reader.GetString(0); DropDownCustomers.Items.Add(customerID); } DropDownCustomers.SelectedIndex = 1; reader.Close(); oleDB.Close();
VB
Dim oleDB As OleDbConnection = New OleDbConnection() oleDB.ConnectionString = String.Format(ConnectionString, ResolveApplicationDataPath(DBFileName, False)) Dim command As OleDbCommand = New OleDbCommand("SELECT CustomerID FROM Customers", oleDB) oleDB.Open() Dim reader As OleDbDataReader = command.ExecuteReader() While reader.Read() Dim customerID As String = reader.GetString(0) DropDownCustomers.Items.Add(customerID) End While DropDownCustomers.SelectedIndex = 1 reader.Close() oleDB.Close()
Figure 1: UI of the sample
You can also refer to the sample here.