The Microsoft Office Access database engine cannot open or write to the file ''

c# asp.net excel oledbconnection oledbexception

15,329

Save the uploaded file to a disk location with FileUpload.SaveAs before trying to process it. As the docs warn,

The FileUpload control does not automatically save a file to the server ...

Files are cached in memory or a temporary folder on disk until you save them.

You should probably consider using a different method of processing Excel files, like EPPlus (for xlsx), NPOI (xls and xlsx) or just the Open XML SDK (xlsx). They don't need the Jet driver, have less quirks and some of the can read a stream directly.

This would allow you to read the uploaded contents directly from the uploaded file's InputStream property. Using Open XML SDK for example, you can write:

using (SpreadsheetDocument spreadsheetDocument =
SpreadsheetDocument.Open(fileuploadExcel.PostedFile.InputStream, false))
{
    ...
}

15,329

Author by

Krithi07

Poetic Data Nerd | Self Learned Data Scientist | Blogger

Updated on June 04, 2022

Comments

Krithi07 almost 2 years

Data needs to be read into .net gridview from excel worksheet. Here are the aspx and aspx.cs codes.

ASPX:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Read and Display Data From an Excel File (.xsl or .xlsx) in ASP.NET</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<b>Please Select Excel File: </b>
<asp:FileUpload ID="fileuploadExcel" runat="server" />&nbsp;&nbsp;
<asp:Button ID="btnImport" runat="server" Text="Import Data" OnClick="btnImport_Click" />
<br />
<asp:Label ID="lblMessage" runat="server" Visible="False" Font-Bold="True" ForeColor="#009933"></asp:Label><br />
<asp:GridView ID="grvExcelData" runat="server">
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
</asp:GridView>
</div>
</form>
</body>
</html>

ASPX.CS

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.IO;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void btnImport_Click(object sender, EventArgs e)
{
    string connString = "";
    string strFileType = Path.GetExtension(fileuploadExcel.FileName).ToLower();
    string path = fileuploadExcel.PostedFile.FileName;
    //Connection String to Excel Workbook
    if (strFileType.Trim() == ".xls")
    {
        connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
    }
    else if (strFileType.Trim() == ".xlsx")
    {
        connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path +";Extended Properties=\"Excel 12.0 Xml;HDR=Yes;IMEX=2;\"";
    }

    string query = "SELECT [UserName],[Education],[Location] FROM [Sheet1$]";
    OleDbConnection conn = new OleDbConnection(connString);
        conn.Open();
    OleDbCommand cmd = new OleDbCommand(query, conn);
    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);
    grvExcelData.DataSource = ds.Tables[0];
    grvExcelData.DataBind();
    da.Dispose();
    conn.Close();
    conn.Dispose();
}

conn.Open(); gives the following error:

The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

Network service has been provided all the permissions for the folder in which the excel sheet is stored.