将数据从Excel导入到网格视图 [英] import data from excel to grid view

问题描述

private void button2_Click_1(object sender, EventArgs e)
{



    string query = "SELECT column1,column2 FROM [Sheet1$]";



    try
    {

        String strConnectionString = "";

        strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
             "Data Source=" + "D:\\SubjectMaster.xls" + "; Jet OLEDB:Engine Type=5;" +
                                          "Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";";
        OleDbConnection cnCSV = new OleDbConnection(strConnectionString);
        //cnCSV.Open();


        OleDbCommand cmdSelect = new OleDbCommand(query, cnCSV);
        OleDbDataAdapter daCSV = new OleDbDataAdapter();
        daCSV.SelectCommand = cmdSelect;


        OleDbDataReader dReader;
        dReader = cmdSelect.ExecuteReader();
        DataSet ds = new DataSet();

        SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnectionString);
        //Give your Destination table name
        sqlBulk.DestinationTableName = "SubjectMaster";
        sqlBulk.ColumnMappings.Add(0, 1);
        sqlBulk.ColumnMappings.Add(1, 2);
        sqlBulk.WriteToServer(dReader);


        //System.Data. DataTable dtCSV = new System.Data. DataTable();
        DataSet da = new DataSet();
        daCSV.Fill(da);
        dataGridView1.DataSource = da.Tables[0];
        cnCSV.Close();
        MessageBox.Show("Successful");
    }
    catch (Exception )
    {



SqlConnection con = new SqlConnection("Server =KK-PC;Initial Catalog = EducaBase;User id = sa;Password = sa2008;");

   //  if Not Exists (SELECT * FROM SubjectMaster WHERE ID=NULL)
con.Open();
    SqlCommand com = new SqlCommand("INSERT Into  SubjectMaster (Su_ID,Su_Name,Su_Level_ID,Su_Level_SUB) VALUES (SUB10,Physics II,PUC,PHY)");
    //com.ExecuteNonQuery();

     MessageBox.Show("Successful");

      }

当我点击按钮时excel文件没有生成。

可以帮助我解决这个问题。

谢谢。

When i Click on button the excel file is not generated.
can any one help me to solve this problem.
Thanks.

推荐答案

;



尝试
{

字符串 strConnectionString = ;

strConnectionString = Provider = Microsoft.Jet.OLEDB.4.0; +
数据源= + D：\\SubjectMaster.xls + ; Jet OLEDB：引擎类型= 5; +
扩展属性= \Excel 8.0; HDR = YES; IMEX = 1 \;;
OleDbConnection cnCSV = new OleDbConnection（strConnectionString）;
// cnCSV.Open（）;


OleDbCommand cmdSelect = new OleDbCommand（query，cnCSV）;
OleDbDataAdapter daCSV = new OleDbDataAdapter（）;
daCSV.SelectCommand = cmdSelect;


OleDbDataReader dReader;
dReader = cmdSelect.ExecuteReader（）;
DataSet ds = new DataSet（）;

SqlBulkCopy sqlBulk = new SqlBulkCopy（strConnectionString）;
// 提供目的地表名称
sqlBulk.DestinationTableName = SubjectMaster;
sqlBulk.ColumnMappings.Add（ 0 ， 1 ）;
sqlBulk.ColumnMappings.Add（ 1 ， 2 ）;
sqlBulk.WriteToServer（dReader）;


// System.Data。 DataTable dtCSV = new System.Data。 DataTable（）;
DataSet da = new DataSet（）;
daCSV.Fill（da）;
dataGridView1.DataSource = da.Tables [ 0 ];
cnCSV.Close（）;
MessageBox.Show（ 成功）;
}
catch （例外）
{



SqlConnection con = new SqlConnection（ Server = KK-PC; Initial目录= EducaBase;用户ID = sa;密码= sa2008;）;

// 如果不存在（SELECT * FROM SubjectMaster WHERE ID = NULL）
con.Open（）;
SqlCommand com = new SqlCommand（ INSERT进入SubjectMaster（Su_ID，Su_Name，Su_Level_ID，Su_Level_SUB）VALUES（SUB10，Physics II，PUC，PHY））;
// com.ExecuteNonQuery（）;

MessageBox。显示（ 成功）;

}

"; try { String strConnectionString = ""; strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + "D:\\SubjectMaster.xls" + "; Jet OLEDB:Engine Type=5;" + "Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";"; OleDbConnection cnCSV = new OleDbConnection(strConnectionString); //cnCSV.Open(); OleDbCommand cmdSelect = new OleDbCommand(query, cnCSV); OleDbDataAdapter daCSV = new OleDbDataAdapter(); daCSV.SelectCommand = cmdSelect; OleDbDataReader dReader; dReader = cmdSelect.ExecuteReader(); DataSet ds = new DataSet(); SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnectionString); //Give your Destination table name sqlBulk.DestinationTableName = "SubjectMaster"; sqlBulk.ColumnMappings.Add(0, 1); sqlBulk.ColumnMappings.Add(1, 2); sqlBulk.WriteToServer(dReader); //System.Data. DataTable dtCSV = new System.Data. DataTable(); DataSet da = new DataSet(); daCSV.Fill(da); dataGridView1.DataSource = da.Tables[0]; cnCSV.Close(); MessageBox.Show("Successful"); } catch (Exception ) { SqlConnection con = new SqlConnection("Server =KK-PC;Initial Catalog = EducaBase;User id = sa;Password = sa2008;"); // if Not Exists (SELECT * FROM SubjectMaster WHERE ID=NULL) con.Open(); SqlCommand com = new SqlCommand("INSERT Into SubjectMaster (Su_ID,Su_Name,Su_Level_ID,Su_Level_SUB) VALUES (SUB10,Physics II,PUC,PHY)"); //com.ExecuteNonQuery(); MessageBox.Show("Successful"); }

当我点击按钮时excel文件没有生成。

可以帮助我解决这个问题。

谢谢。

When i Click on button the excel file is not generated.
can any one help me to solve this problem.
Thanks.

参见使用MS Excel（xls / xlsx）使用MDAC和Oledb [ ^ ]。

试用此代码：

Try this code:

<table><tbody><tr><td><asp:fileupload id="testFileUpload" runat="server" xmlns:asp="#unknown" /></td><td><asp:button id="btnUpload" runat="server" height="21px" text="Upload" width="92px" onclick="btnUpload_Click" xmlns:asp="#unknown" /></td></tr></tbody></table>
   

    <br />  
    <asp:gridview id="dataGridView" runat="server" xmlns:asp="#unknown">
    </asp:gridview>

在您的按钮事件中：

In your button event:

try
        {
            if (testFileUpload.HasFile)
            {
                fileName = Path.GetFileName(testFileUpload.PostedFile.FileName);
                fileExtension = Path.GetExtension(testFileUpload.PostedFile.FileName);
                fileLocation = Server.MapPath("~/App_Data/" + fileName);

                GetData(fileLocation);
                Label1.Text = "File is ok to upload.";
            }
        }
        catch (Exception ex)
        {
            Label1.Text = "File is not expected formated.";
        }

public void GetData(string loc)
    {
        testFileUpload.SaveAs(fileLocation);
        //Check whether file extension is xls or xslx

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

        dtExcelRecords.Clear();
        dtExcelRecords.Reset();

        //Create OleDB Connection and OleDb Command

        con = new OleDbConnection(connectionString);

        cmd.CommandType = System.Data.CommandType.Text;
        cmd.Connection = con;
        dAdapter = new OleDbDataAdapter(cmd);

        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);
        con.Close();

        dataGridView.DataSource = dtExcelRecords;
        dataGridView.DataBind();

    }

这篇关于将数据从Excel导入到网格视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！