how to import excel sheets in asp.net

Question

0.00/5 (No votes)

See more:

hi everyone,
When i try to upload excel sheet on asp.net gridview, my coding works in IE, but doesn't work in Chrome and mozilla, its give below error, why so? plz suggest...

"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."

Posted 21-Aug-12 1:49am

Gopal Rakhal

Updated 13-Sep-16 6:14am

Add a Solution

Comments

AmitGajjar 21-Aug-12 8:21am

can you post your code for reading your uploaded file ?

Gopal Rakhal 22-Aug-12 2:04am

//sqlconnection
string StrCon = ConfigurationManager.ConnectionStrings["idol"].ToString();
SqlConnection objcon = new SqlConnection(StrCon);
objcon.Open();
//oledbconnection
string filepath = FileUpload1.PostedFile.FileName;
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
string FilePath = Server.MapPath(FolderPath + FileName);
string stroledbCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=Excel 12.0 ";
// "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";

OleDbConnection oledbCon = new OleDbConnection(stroledbCon);
oledbCon.Open();
string strOledbQuery = "select * from [Sheet1$]";
OleDbCommand cmd = new OleDbCommand(strOledbQuery, oledbCon);
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string strinsert = "insert into student_tbl(slNo,Rollno,Name) VALUES ('"+dr[0].ToString ()+"','" + dr[1].ToString() + "','" + dr[2].ToString() + "')";
SqlCommand cmd2 = new SqlCommand(strinsert, objcon);
cmd2.ExecuteNonQuery();
}

AmitGajjar 22-Aug-12 2:12am

Use Improve question link to update your question. this is not readable.

[no name] 21-Aug-12 9:05am

Suggest what exactly? The error message is perfectly clear.

6 solutions

Solution 1

http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/3479cc5a-2bca-4e9c-9fdb-ec3f96065a52/[^]

Posted 21-Aug-12 1:58am

archanasri

Updated 21-Aug-12 10:19am

v2

Solution 2

Go through your code and see where you get the data and send to database. There must be some missing closed object.

Here is a complete article which will help you,
http://csharpdotnetfreak.blogspot.com/2011/12/upload-and-read-excel-file-in-aspnet.html[^]

Hope this helps
cheers

Posted 21-Aug-12 2:30am

Sandip.Nascar

Solution 3

it is a permissions issue, please give read/write permissions to IUSER account on that particular folder and on that file...also make sure that file is not being used by any other process in the system...

You should give security permissions to the application pool user if you use IIS 7 hosting.

Give "read and write" permission to "network service" user on the file, then reboot your computer. It would be working fine

I think that will solve your problem...

Posted 21-Aug-12 3:10am

Sunil Kumar Pandab

Solution 8

I recommend you to use ready and tested packages with more features instead of writing your own code. the one i can recommend is EPPlus with great features.
you can download it via NuGet:
https://www.nuget.org/packages/EPPlus/[^]

Posted 13-Sep-16 22:25pm

zamanipour

Updated 13-Sep-16 22:27pm

Comments

Maciej Los 14-Sep-16 4:29am

Why to post an answer to the question with approved answer posted almost 4 years ago?

zamanipour 14-Sep-16 6:37am

you are right. but it was in quick answers list! maybe because of an reply down in solution 4. I'm new here and i will keep in mind to check the question date. thanks for mentioning that.

Maciej Los 14-Sep-16 10:16am

I'd suggest to remove it to avoid down-voting.

Solution 6

it was not a permission issue rather it was location issue and i solved it by getting hint from santosh_k answer..

Posted 22-Aug-12 16:15pm

Gopal Rakhal

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

santosh_k · Accepted Answer · 2012-08-21T10:09:00

Controls and Properties:

1. FileUpload Control. name=”FileUpload1″

2. Button. name=”btnUpload” Text=”Upload”

3. GridView: name=”GridView1″

NameSpaces:

C#

using System;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;

Source Code:

ASP.NET

<form id="form1"  runat="server">
 <div>
 <asp:fileupload id="FileUpload1" runat="server" forecolor="#993399" xmlns:asp="#unknown" />
       <asp:button id="btnUpload" runat="server" text="Upload" onclick="btnUpload_Click" xmlns:asp="#unknown" />
       <asp:gridview id="GridView1" runat="server" xmlns:asp="#unknown">
          OnPageIndexChanging="PageIndexChanging" BackColor="White" BorderColor="#999999"
          BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical">
          <headerstyle cssclass="hdr" backcolor="#000084" font-bold="True">
             ForeColor="White" />
          <alternatingrowstyle backcolor="#DCDCDC" />
          <footerstyle cssclass="ftr" backcolor="#CCCCCC" forecolor="Black" />
          <pagerstyle backcolor="#999999" forecolor="Black" horizontalalign="Center" />
          <rowstyle backcolor="#EEEEEE" forecolor="Black" />
          <selectedrowstyle backcolor="#008A8C" font-bold="True" forecolor="White" />
          <sortedascendingcellstyle backcolor="#F1F1F1" />
          <sortedascendingheaderstyle backcolor="#0000A9" />
          <sorteddescendingcellstyle backcolor="#CAC9C9" />
          <sorteddescendingheaderstyle backcolor="#000065" />
       </headerstyle></asp:gridview>
 </div>
</form>

aspx.cs Page Code:

1. Uploading an Excel File:

C#

protected void btnUpload_Click(object sender, EventArgs e)
 {
        if (FileUpload1.HasFile)
 {
            string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
            string FilePath = Server.MapPath(FolderPath + FileName);
            FileUpload1.SaveAs(FilePath);
            Import_To_Grid(FilePath, Extension);
        }
    }

2. Import Excel File in GridView Control:protected void Page_Load(object sender, EventArgs e)

C#

private void Import_To_Grid(string FilePath, string Extension)
 {
        string conStr = "";
        switch (Extension)
 {
            case ".xls": //Excel 97-03
                conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
            case ".xlsx": //Excel 07
                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                break;
        }
        conStr = String.Format(conStr, FilePath, 1);
        OleDbConnection connExcel = new OleDbConnection(conStr);
        OleDbCommand cmdExcel = new OleDbCommand();
        OleDbDataAdapter oda = new OleDbDataAdapter();
        DataTable dt = new DataTable();
        cmdExcel.Connection = connExcel;
        connExcel.Open();
        DataTable dtExcelSchema;
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
        connExcel.Close();

        //Read Data from First Sheet
        connExcel.Open();
        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
        oda.SelectCommand = cmdExcel;
        oda.Fill(dt);
        connExcel.Close();
        GridView1.DataSource = dt;
 GridView1.DataBind();

    }

protected void PageIndexChanging(object sender, GridViewPageEventArgs e)
 {
        string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
        string FileName = GridView1.Caption;
        string Extension = Path.GetExtension(FileName);
        string FilePath = Server.MapPath(FolderPath + FileName);
        Import_To_Grid(FilePath, Extension);
 GridView1.PageIndex = e.NewPageIndex;
 GridView1.DataBind();
    }