如何在ASP.NET中使用C#从Excel文件加载GridView？

Question

我尝试从Excel文件中加载一个GridView，但是我得到了以下错误：

Microsoft Jet数据库引擎“Sheet1 $”无法找到对象。请确保该对象存在且名称拼写正确以及路径正确。

并且在这一行中出现错误：excelDataAdapter.Fill(dt)；

我在App_Data文件夹中有一个.xls文件。

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


}
}

- Mhmt

---

首先从 App_Data 中加载它：Server.MapPath("~/App_Data/data.xls")。 - Antonio Bakula

Answer 1

这段代码在我的设备上运行良好。

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

- Raghubar

---

谢谢兄弟。你的答案解决了问题。 - Mhmt

这里的FileUpload1是什么？当我写它时，它会抛出“在当前上下文中不存在”的错误。它来自哪个库很重要？ - abidinberkay

Fileupload1 是界面中的文件上传控件，用户可以选择 Excel 文件。 - Raghubar

Answer 2

     <div class="row">
                                                                                <div class="col-lg-4">
                                                                                    <div class="form-group has-error">
                                                                                        <label class="form-label">
                                                                                            Upload Excel</label>
                                                                                        <asp:FileUpload ID="fuUploadExcelName" runat="server" class="form-control input-sm m-bot15"
                                                                                            Font-Bold="false" Style="font-weight: bold" TabIndex="0" />
                                                                                    </div>
                                                                                </div>
                                                                                <div class="col-lg-4 text-right">
                                                                                    <div class="form-group">
                                                                                        <br />
                                                                                        <button type="button" id="btnUpload" runat="server" onserverclick="btnUpload_Click"
                                                                                            class="btn btn-default btn-border  btn-sm">
                                                                                            <i class="fa fa-upload "></i>Upload & Fill</button>
                                                                                        <button type="button" id="Button1" runat="server" onserverclick="Button1_Click" class="btn btn-success btn-border btn-sm" visible="false">Fill New Salary</button>
                                                                                    </div>
                                                                                </div>
                                                                            </div>

 protected void btnUpload_Click(object sender, EventArgs e)
    {

        if (Page.IsValid)
        {
            bool logval = true;
            if (logval == true)
            {
                String img_1 = fuUploadExcelName.PostedFile.FileName;
                String img_2 = System.IO.Path.GetFileName(img_1);
                string extn = System.IO.Path.GetExtension(img_1);

                string frstfilenamepart = "";
                frstfilenamepart = "DateExcel" + DateTime.Now.ToString("ddMMyyyyhhmmss"); ;
                UploadExcelName.Value = frstfilenamepart + extn;
                fuUploadExcelName.SaveAs(Server.MapPath("~/Employee/DateExcel/") + "/" + UploadExcelName.Value);
                string PathName = Server.MapPath("~/Employee/DateExcel/") + "\\" + UploadExcelName.Value;
                GetExcelSheetForUAN(PathName, UploadExcelName.Value);
                GetExcelSheetForEmployeeCode(PathName);

            }
        }


    }


    private void GetExcelSheetForEmployeeCode(string filename)
    {
        int count = 0;
        int selectedcheckbox = 0;
        string empcodeexcel = "";
        string empcodegrid = "";
        string excelFile = "Employee/DateExcel" + filename;
        OleDbConnection objConn = null;
        System.Data.DataTable dt = null;
        try
        {
            DataSet ds = new DataSet();
            String connString = "Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=True;Extended Properties=Excel 12.0 Xml;Data Source=" + filename;
            // Create connection. 
            objConn = new OleDbConnection(connString);
            // Opens connection with the database. 
            if (objConn.State == ConnectionState.Closed)
            {
                objConn.Open();
            }
            // Get the data table containing the schema guid, and also sheet names. 
            dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dt == null)
            {
                return;
            }
            String[] excelSheets = new String[dt.Rows.Count];
            int i = 0;
            // Add the sheet name to the string array. 
            // And respective data will be put into dataset table 
            foreach (DataRow row in dt.Rows)
            {
                if (i == 0)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    OleDbCommand cmd = new OleDbCommand("SELECT DISTINCT * FROM [" + excelSheets[i] + "]", objConn);
                    OleDbDataAdapter oleda = new OleDbDataAdapter();
                    oleda.SelectCommand = cmd;
                    oleda.Fill(ds, "TABLE");
                    if (ds.Tables[0].ToString() != null)
                    {
                        for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
                        {
                            for (int k = 0; k < GrdEmplistFromAtt.Rows.Count; k++)
                            {
                                empcodeexcel = ds.Tables[0].Rows[j][0].ToString();
                                date.Value = ds.Tables[0].Rows[j][1].ToString();

                                Label lbl_EmpCode = (Label)GrdEmplistFromAtt.Rows[k].FindControl("lblGrdEmpCode");
                                empcodegrid = lbl_EmpCode.Text;
                                CheckBox chk = (CheckBox)GrdEmplistFromAtt.Rows[k].FindControl("chkSingle");
                                TextBox txt_NewSalary = (TextBox)GrdEmplistFromAtt.Rows[k].FindControl("txtNewSalary");


                                if ((empcodegrid == empcodeexcel) && (date.Value != ""))
                                {
                                    chk.Checked = true;
                                    txt_NewSalary.Text = date.Value;
                                    selectedcheckbox = selectedcheckbox + 1;
                                    lblSelectedRecord.InnerText = selectedcheckbox.ToString();
                                    count++;
                                }
                                if (chk.Checked == true)
                                {

                                }
                            }

                        }

                    }
                }
                i++;
            }

        }
        catch (Exception ex)
        {
            ShowMessage(ex.Message.ToString(), 0);
        }
        finally
        {
            // Clean up. 
            if (objConn != null)
            {
                objConn.Close();
                objConn.Dispose();
            }
            if (dt != null)
            {
                dt.Dispose();
            }
        }
    }

    private void GetExcelSheetForUAN(string PathName, string UploadExcelName)
    {
        string excelFile = "DateExcel/" + PathName;
        OleDbConnection objConn = null;
        System.Data.DataTable dt = null;
        try
        {

            DataSet dss = new DataSet();
            String connString = "Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=True;Extended Properties=Excel 12.0 Xml;Data Source=" + PathName;
            objConn = new OleDbConnection(connString);
            objConn.Open();
            dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dt == null)
            {
                return;
            }
            String[] excelSheets = new String[dt.Rows.Count];
            int i = 0;
            foreach (DataRow row in dt.Rows)
            {
                if (i == 0)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + excelSheets[i] + "]", objConn);
                    OleDbDataAdapter oleda = new OleDbDataAdapter();
                    oleda.SelectCommand = cmd;
                    oleda.Fill(dss, "TABLE");

                }
                i++;
            }
            grdUANDetailByExcel.DataSource = dss.Tables[0].DefaultView;
            grdUANDetailByExcel.DataBind();
            lblTotalRec.InnerText = Convert.ToString(grdUANDetailByExcel.Rows.Count);

        }

        catch (Exception ex)
        {
            ViewState["Fuletypeidlist"] = "0";
            grdUANDetailByExcel.DataSource = null;
            grdUANDetailByExcel.DataBind();
        }
        finally
        {
            if (objConn != null)
            {
                objConn.Close();
                objConn.Dispose();
            }
            if (dt != null)
            {
                dt.Dispose();
            }
        }

    }

- Code