Insert to Excel file from ASP.NET
To insert data into an Excel file using an OleDb connection in ASP.NET, you can follow these steps:
Import the required namespaces:
using System.Data;
using System.Data.OleDb;
Create a method to insert data into the Excel file:
protected void InsertDataIntoExcel(string filePath, string sheetName)
{
// Create the connection string for the Excel file
string connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filePath};Extended Properties='Excel 12.0 Xml;HDR=YES;'";
// Create the INSERT statement
string insertQuery = $"INSERT INTO [{sheetName}$] (ColumnName1, ColumnName2, ColumnName3) VALUES (@Value1, @Value2, @Value3)";
// Provide your column names and corresponding values in the insert query
// Example:
// string insertQuery = $"INSERT INTO [{sheetName}$] (Name, Age, City) VALUES (@Value1, @Value2, @Value3)";
// Provide your actual column names in the insert query
// Create the OleDb connection
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
// Open the connection
connection.Open();
// Create the command
using (OleDbCommand command = new OleDbCommand(insertQuery, connection))
{
// Add parameters to the command
command.Parameters.AddWithValue("@Value1", "John");
command.Parameters.AddWithValue("@Value2", 25);
command.Parameters.AddWithValue("@Value3", "New York");
// Execute the command
command.ExecuteNonQuery();
}
}
}
Call the InsertDataIntoExcel method, providing the file path and sheet name as parameters:
string filePath = "path_to_your_excel_file.xlsx";
string sheetName = "Sheet1";
InsertDataIntoExcel(filePath, sheetName);
Make sure to replace "path_to_your_excel_file.xlsx" with the actual path to your Excel file and provide the correct sheet name and column names in the insert query.
This code will establish an OleDb connection to the Excel file, create an INSERT statement to insert data into the specified sheet and columns, and execute the command to insert the data into the Excel file.
Related Topics