如何在asp.net中导入excel表 [英] how to import excel sheets in asp.net

问题描述

大家好，

当我尝试在asp.net gridview上传excel表时，我的编码在IE中运行，但在Chrome和mozilla中不起作用，它给出了以下错误，为什么呢？ ？ plz建议......



Microsoft Office Access数据库引擎无法打开或写入文件'。它已经由其他用户专门打开，或者你需要查看和写入其数据的权限。

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

推荐答案

控件和属性：



1. FileUpload控件。 name =FileUpload1



2.按钮。 name =btnUploadText =Upload



3. GridView：name =GridView1





NameSpaces：

Controls and Properties:

1. FileUpload Control. name="FileUpload1″

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

3. GridView: name="GridView1″


NameSpaces:

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:

<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页码：



1.上传Excel文件：

aspx.cs Page Code:

1. Uploading an Excel File:

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.在GridView控件中导入Excel文件：protected void Page_Load（object sender，EventArgs e）

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

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();
    }

http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/3479cc5a-2bca-4e9c-9fdb-ec3f96065a52/ [< a href =http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/3479cc5a-2bca-4e9c-9fdb-ec3f96065a52/\"target =_ blanktitle =New Window> ^ ]

浏览您的代码，看看您获取数据的位置并发送到数据库。必须有一些遗失的封闭物品。



这是一篇完整的文章，可以帮助你，

http://csharpdotnetfreak.blogspot.com/2011/12/upload-and-read-excel- file-in-aspnet.html [ ^ ]



希望这有助于

欢呼

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

这篇关于如何在asp.net中导入excel表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！