将Excel数据上传到gridview时出现问题 [英] Problem in uploading excel data to gridview

问题描述

大家好，



早上好...

我正在开发一个页面，用于将excel文件数据上传到gridview，是代码..

 string connString =; 
 string strFileType = Path.GetExtension（FileuplaodControl.FileName）.ToLower（）; 
 string path = FileuplaodControl.PostedFile.FileName; 
 // Excel工作簿的连接字符串
 if（strFileType.Trim（）==。xls）
 {
 connString =Provider = Microsoft.Jet.OLEDB.4.0 ;数据源=+路径+;扩展属性= \Excel 8.0; HDR =是; IMEX = 2 \; 
} 
 else if（strFileType.Trim（）==。xlsx）
 {
 connString =Provider = Microsoft.ACE.OLEDB.12.0; Data Source = + path +;扩展属性= \Excel 12.0; HDR =是; IMEX = 2 \; 
} 
 
 OleDbConnection con = new OleDbConnection（connString）; 
 OleDbCommand cmdnew = new OleDbCommand（）; 
 cmdnew.CommandType = System.Data.CommandType.Text; 
 cmdnew.Connection = con; 
 OleDbDataAdapter dAdapter = new OleDbDataAdapter（cmdnew）; 
 DataTable dtExcelRecords = new DataTable（）; 
 con.Open（）; 
 DataTable dtExcelSheetName = con.GetOleDbSchemaTable（OleDbSchemaGuid.Tables，null）; 
 string getExcelSheetName = dtExcelSheetName.Rows [0] [Table_Name]。ToString（）; 
 cmdnew.CommandText =SELECT * FROM [+ getExcelSheetName +]; 
 dAdapter.SelectCommand = cmdnew; 
 dAdapter.Fill（dtExcelRecords）; 
 Grd.DataSource = dtExcelRecords; 
 Grd.DataBind（）; 
 con.Close（）; 
 con.Dispose（）; 
 

在线获取错误：

 con.Open（）; 

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



使用VS 2010和Office 2010 ..



通过观察上面的错误消息，它表明文件可能被打开或者可能没有访问权限，我已经检查了所有方式...但仍然无法解决问题..

解决方案

getExcelSheetName this您的Excel工作表名称对吗？







并检查一次......





如果是正确的话



尝试这一个



cmdnew.CommandText =SELECT * FROM [getExcelSheetName

< blockquote>;

我使用了它为我工作的相同代码

 使用 System.Data.OleDb; 
 使用 System.Data; 
 使用 System.IO; 

 protected void Button1_Click（object sender，EventArgs e）
 {
 
 string connectionString =; 
 if（FileUpload1.HasFile）
 {
 string fileName = Path.GetFileName（FileUpload1.PostedFile.FileName）; 
 string fileExtension = Path.GetExtension（FileUpload1.PostedFile.FileName）; 
 string fileLocation = Server.MapPath（〜/ App_Data /+ fileName）; 
 FileUpload1.SaveAs（fileLocation）; 
 
 if（fileExtension ==。xls）
 {
 connectionString =Provider = Microsoft.Jet.OLEDB.4.0; Data Source =+ fileLocation +; Extended属性= \Excel 8.0; HDR =是; IMEX = 2 \; 
} 
 else if（fileExtension ==。xlsx）
 {
 connectionString =Provider = Microsoft.ACE.OLEDB.12.0; Data Source =+ fileLocation + ;扩展属性= \Excel 12.0; HDR =是; IMEX = 2 \; 
} 
 
 
 
 OleDbConnection con = new OleDbConnection（connectionString）; 
 OleDbCommand cmdnew = new OleDbCommand（）; 
 cmdnew.CommandType = System.Data.CommandType.Text; 
 cmdnew.Connection = con; 
 OleDbDataAdapter dAdapter = new OleDbDataAdapter（cmdnew）; 
 DataTable dtExcelRecords = new DataTable（）; 
 con.Open（）; 
 DataTable dtExcelSheetName = con.GetOleDbSchemaTable（OleDbSchemaGuid.Tables，null）; 
 string getExcelSheetName = dtExcelSheetName.Rows [0] [Table_Name]。ToString（）; 
 cmdnew.CommandText =SELECT * FROM [+ getExcelSheetName +]; 
 dAdapter.SelectCommand = cmdnew; 
 dAdapter.Fill（dtExcelRecords）; 
 Grd.DataSource = dtExcelRecords; 
 Grd.DataBind（）; 
 con.Close（）; 
 con.Dispose（）; 
} 
} 

Hi Everyone,

Good morning...
I'm developing a page for uploading excel file data to gridview, following is the the code..

string connString = "";
          string strFileType = Path.GetExtension(FileuplaodControl.FileName).ToLower();
          string path = FileuplaodControl.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;HDR=Yes;IMEX=2\"";
          }

          OleDbConnection con = new OleDbConnection(connString);
          OleDbCommand cmdnew = new OleDbCommand();
          cmdnew.CommandType = System.Data.CommandType.Text;
          cmdnew.Connection = con;
          OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmdnew);
          DataTable dtExcelRecords = new DataTable();
          con.Open();
          DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
          string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
          cmdnew.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
          dAdapter.SelectCommand = cmdnew;
          dAdapter.Fill(dtExcelRecords);
          Grd.DataSource = dtExcelRecords;
          Grd.DataBind();
          con.Close();
          con.Dispose();

Getting error at line :

con.Open();

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.

Using VS 2010 and Office 2010..

By observing the above error message it states that the file may be opened or may not have access permission, i have checked in all the ways... but still not able to solve the problem..

解决方案

getExcelSheetName this Your Excel sheet name right?



and check once ...


if it is correct

try this one

cmdnew.CommandText = "SELECT * FROM [getExcelSheetName

";

I used the same code it is working for me

using System.Data.OleDb;
using System.Data;
using System.IO;

protected void Button1_Click(object sender, EventArgs e)
    {
       
        string connectionString = "";
        if (FileUpload1.HasFile)
        {
            string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            string fileLocation = Server.MapPath("~/App_Data/" + fileName);
            FileUpload1.SaveAs(fileLocation);           

            if (fileExtension == ".xls")
            {
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            }
            else if (fileExtension == ".xlsx")
            {
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }



            OleDbConnection con = new OleDbConnection(connectionString);
            OleDbCommand cmdnew = new OleDbCommand();
            cmdnew.CommandType = System.Data.CommandType.Text;
            cmdnew.Connection = con;
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmdnew);
            DataTable dtExcelRecords = new DataTable();
            con.Open();
            DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
            cmdnew.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
            dAdapter.SelectCommand = cmdnew;
            dAdapter.Fill(dtExcelRecords);
            Grd.DataSource = dtExcelRecords;
            Grd.DataBind();
            con.Close();
            con.Dispose();
        }
    }

这篇关于将Excel数据上传到gridview时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！