如何从C＃在asp.net中一个excel文件加载gridview的？ [英] How to load gridview from a excel file in asp.net by c#?

问题描述

我试图加载从Excel file.But一个GridView我得到这个错误

Microsoft Jet数据库引擎的工作表Sheet1 $找不到对象。请确保该对象存在并且其名称拼写正确和路径。

和有这行错误：excelDataAdapter.Fill（DT）;

我在程序App_Data folder.Wh一个.xls文件

 使用系统;
使用System.Collections.Generic;
使用System.Linq的;
使用的System.Web;
使用System.Web.UI程序;
使用System.IO;
使用System.Data这;
使用System.Data.OleDb;
命名空间的Excell
{
公共部分类LoadExcelToGrid：System.Web.UI.Page
{
    保护无效的Page_Load（对象发件人，EventArgs的发送）
    {
       gv.DataSource = exceldata（使用Server.Mappath（〜/ data.xls））;
       gv.DataBind（）;
    }
    公共静态数据集exceldata（字符串filelocation）
    {
        DataSet的DS =新的DataSet（）;
        OleDbCommand的excelCommand =新的OleDbCommand（）; OleDbDataAdapter的excelDataAdapter =新
 OleDbDataAdapter的（）;
        字符串excelConnStr =供应商= Microsoft.Jet.OLEDB.4.0;数据源=+ filelocation +
;扩展属性= Excel的8.0;;
        OleDbConnection的excelConn =新的OleDbConnection（excelConnStr）;
        excelConn.Open（）;
        DataTable的DT =新的DataTable（）;
        excelCommand =新的OleDbCommand（，excelConn从[工作表Sheet1 $] SELECT *）;
        excelDataAdapter.SelectCommand = excelCommand;
        excelDataAdapter.Fill（DT）;        ds.Tables.Add（DT）;        返回DS;
    }
}
}
 

解决方案

这code为我工作。

 保护无效btnUpload_Click（对象发件人，EventArgs的发送）
{
字符串strConnection =ConnectionString的;
字符串的connectionString =;
如果（FileUpload1.HasFile）
{
    字符串文件名= Path.GetFileName（FileUpload1.PostedFile.FileName）;
    字符串fileExtension = Path.GetExtension（FileUpload1.PostedFile.FileName）;
    字符串fileLocation =使用Server.Mappath（〜/ App_Data文件/+文件名）;
    FileUpload1.SaveAs（fileLocation）;
    如果（fileExtension ==的.xl​​s）
    {
        的connectionString =供应商= Microsoft.Jet.OLEDB.4.0;数据源=+
          fileLocation +;扩展属性= \\Excel中8.0; HDR =是; IMEX = 2 \\;
    }
    否则，如果（fileExtension ==的.xl​​sx）
    {
        的connectionString =供应商= Microsoft.ACE.OLEDB.12.0;数据源=+
          fileLocation +;扩展属性= \\Excel的12.0; HDR =是; IMEX = 2 \\;
    }
    OleDbConnection的CON =新的OleDbConnection（的connectionString）;
    OleDbCommand的CMD =新的OleDbCommand（）;
    cmd.CommandType = System.Data.CommandType.Text;
    cmd.Connection = CON;
    OleDbDataAdapter的dAdapter =新OleDbDataAdapter的（CMD）;
    数据表dtExcelRecords =新的DataTable（）;
    con.Open（）;
    数据表dtExcelSheetName = con.GetOleDbSchemaTable（OleDbSchemaGuid.Tables，NULL）;
    。字符串getExcelSheetName = dtExcelSheetName.Rows [0] [TABLE_NAME]的ToString（）;
    cmd.CommandText =SELECT * FROM [+ getExcelSheetName +];
    dAdapter.SelectCommand = CMD;
    dAdapter.Fill（dtExcelRecords）;
    GridView1.DataSource = dtExcelRecords;
    GridView1.DataBind（）;
}
 

I tried load a gridview from excel file.But I get this error

The Microsoft Jet database engine 'Sheet1 $' could not find the object. Make sure the object exists and that its name is spelled correctly and the path.

And there is an error in this line : excelDataAdapter.Fill(dt);

I have a .xls file in App_Data folder.Wh

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




namespace Excell
{
public partial class LoadExcelToGrid: System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
       gv.DataSource = exceldata(Server.MapPath("~/data.xls"));
       gv.DataBind();
    }
    public static DataSet exceldata(string filelocation)
    {
        DataSet ds = new DataSet();
        OleDbCommand excelCommand = new OleDbCommand(); OleDbDataAdapter excelDataAdapter = new 
 OleDbDataAdapter();
        string excelConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filelocation + 
"; Extended Properties=Excel 8.0;";
        OleDbConnection excelConn = new OleDbConnection(excelConnStr);
        excelConn.Open();
        DataTable dt = new DataTable();
        excelCommand = new OleDbCommand("Select * from [Sheet1$]", excelConn);
        excelDataAdapter.SelectCommand = excelCommand;
        excelDataAdapter.Fill(dt);

        ds.Tables.Add(dt);

        return ds;
    }


}
}

解决方案

This Code works for me.

protected void btnUpload_Click(object sender, EventArgs e)
{
String strConnection = "ConnectionString";
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 cmd = new OleDbCommand();
    cmd.CommandType = System.Data.CommandType.Text;
    cmd.Connection = con;
    OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
    DataTable dtExcelRecords = new DataTable();
    con.Open();
    DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
    cmd.CommandText = "SELECT * FROM [" + getExcelSheetName +"]";
    dAdapter.SelectCommand = cmd;
    dAdapter.Fill(dtExcelRecords); 
    GridView1.DataSource = dtExcelRecords;
    GridView1.DataBind(); 
}

这篇关于如何从C＃在asp.net中一个excel文件加载gridview的？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！