Accessing an Excel data file on the Client side using ASP .NET vbscript

you website will need to support the upload of the excel file. then you can use OpenXml sdk to read the the uploaded file.

https://learn.microsoft.com/en-us/office/open-xml/open-xml-sdk

note: you will need to use vb.net or c# to use the sdk. if you are an old active server pages site, you will need to migrate to asp.net

It is not possible for the ASP.NET Web app to obtain the data directly from the Excel file located in user's PC.

I have an Excel file in my system with some data. I want to write a code that will fetch the data from the Excel and write into a table in SQL Server.

As one of possible ways to realize the above-mentioned requirement, I suggest the followings:

(1) develop a desktop app such as Windows Forms which can open the Excel file, read data as required and send the data to the ASP.NET app using HttpClient at the client side, and

(2) add API in the ASP.NET app which can receive the data sent by the desktop app and save the received data to the database at the server side.

Hi there,

Here's a way you can go about it:

1. Create a Desktop Application (Optional): Since you can't access the local file directly from the web app, consider developing a Windows Forms application that can read the Excel file and send the data to your ASP.NET web app via HTTP requests.
2. API in ASP.NET: In your ASP.NET application, set up an API endpoint that can accept the data sent by your desktop app and insert it into your SQL Server database.
3. File Upload Method: Another approach is to implement a file upload feature in your web app. Users can upload their Excel file directly to the server. You can use the EPPlus library to read the data from the uploaded Excel file. EPPlus is much more reliable than the older OLEDB providers, especially in cloud environments.
4. Using Connection Strings: If you go the route of uploading, you'll typically set up a connection string in your code to manage interaction with the SQL Server. Make sure you properly configure the connection string based on your environment.
5. Reading Excel Data with EPPlus: Here's how you can read Excel data using EPPlus (which is much better than the old OLEDB approach):

First, install EPPlus via NuGet Package Manager:

• Right-click your project → Manage NuGet Packages
• Search for "EPPlus" and install it

! Noted that you need to use C# for this.

Important: Handle EPPlus Licensing Recent versions of EPPlus require license setup. Add this to your Page_Load or Application_Start:

// For EPPlus 8+ (non-commercial use) 
ExcelPackage.License.SetNonCommercialOrganization("Your Organization");
 
// Or for personal use: 
ExcelPackage.License.SetNonCommercialUser("Your Name");

Here's my code example:

using System;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using OfficeOpenXml;
 
public void ProcessExcelFile(HttpPostedFile uploadedFile)
{
    try
    {
        // Set license (required for EPPlus 8+)
        ExcelPackage.License.SetNonCommercialOrganization("Test Organization");
        using (var package = new ExcelPackage(uploadedFile.InputStream))
        {
            var worksheet = package.Workbook.Worksheets[0];
            int rowCount = worksheet.Dimension.Rows;
            for (int row = 2; row <= rowCount; row++) // Skip header row
            {
                var col1Value = worksheet.Cells[row, 1].Value?.ToString() ?? string.Empty;
                var col2Value = worksheet.Cells[row, 2].Value?.ToString() ?? string.Empty;
                // Skip empty rows
                if (!string.IsNullOrEmpty(col1Value) || !string.IsNullOrEmpty(col2Value))
                {
                    InsertIntoDatabase(col1Value, col2Value);
                }
            }
        }
    }
    catch (Exception ex)
    {
        throw new Exception("Error processing Excel file: " + ex.Message);
    }
}
 
private void InsertIntoDatabase(string col1, string col2)
{
    string connectionString = ConfigurationManager.ConnectionStrings["YourConnectionString"].ConnectionString;
    try
    {
        using (var connection = new SqlConnection(connectionString))
        {
            string query = "INSERT INTO YourTable (Column1, Column2) VALUES (@col1, @col2)";
            using (var command = new SqlCommand(query, connection))
            {
                command.Parameters.AddWithValue("@col1", col1 ?? (object)DBNull.Value);
                command.Parameters.AddWithValue("@col2", col2 ?? (object)DBNull.Value);
                connection.Open();
                command.ExecuteNonQuery();
            }
        }
    }
    catch (Exception ex)
    {
        throw new Exception($"Database error: {ex.Message}");
    }
}

A few important tips:

• EPPlus is much more reliable than OLEDB providers, especially in cloud environments
• Always use parameterized queries to prevent SQL injection
• The license setup is crucial for EPPlus 8+ - without it, you'll get runtime errors
• Consider adding error handling and validation for production use
• If your Excel files are large, you might want to process them in batches