如何使用c＃将excel文件导入到asp.net中的网格视图并将网格视图保存到数据库sql server 2005 [英] How to import excel file to grid view in asp.net using c# AND save grid view to database sql server 2005

问题描述

如何使用c＃将excel文件导入到asp.net中的网格视图并将网格视图保存到数据库sql server 2005.

How to import excel file to grid view in asp.net using c# AND save grid view to database sql server 2005.

推荐答案

您可以从Excel获取数据比如数据库使用 System.Data.OleDb ;

You can get data from Excel like database using System.Data.OleDb;

using System.Data.OleDb;

public DataSet ImportFromExcel(string file)
{
    // Create new dataset
    DataSet ds = new DataSet();

    // -- Start of Constructing OLEDB connection string to Excel file
    Dictionary<string, string> props = new Dictionary<string, string>();

    // For Excel 2007/2010
    if (file.EndsWith(".xlsx"))
    {
        props["Provider"] = "Microsoft.ACE.OLEDB.12.0;";
        props["Extended Properties"] = "Excel 12.0 XML";
    }
    // For Excel 2003 and older
    else if (file.EndsWith(".xls"))
    {
        props["Provider"] = "Microsoft.Jet.OLEDB.4.0";
        props["Extended Properties"] = "Excel 8.0";
    }
    else
        return null;

    props["Data Source"] = file;

    StringBuilder sb = new StringBuilder();

    foreach (KeyValuePair<string, string> prop in props)
    {
        sb.Append(prop.Key);
        sb.Append('=');
        sb.Append(prop.Value);
        sb.Append(';');
    }

    string connectionString = sb.ToString();
    // -- End of Constructing OLEDB connection string to Excel file

    // Connecting to Excel File
    using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
        conn.Open();
        OleDbCommand cmd = new OleDbCommand();
        cmd.Connection = conn;

        // Get all Sheets in Excel File
        DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        // Loop through all Sheets to get data
        foreach (DataRow dr in dtSheet.Rows)
        {
            string sheetName = dr["TABLE_NAME"].ToString();

            // Get all rows from the Sheet
            cmd.CommandText = "SELECT * FROM [" + sheetName + "]";

            DataTable dt = new DataTable();
            dt.TableName = sheetName.Replace("

， string .Empty）;

OleDbDataAdapter da = new OleDbDataAdapter（cmd）;
da.Fill（dt）;

// 将表添加到DataSet
ds.Tables。加入（DT）;
}

cmd = null ;
conn.Close（）;
}

return ds;
}

", string.Empty); OleDbDataAdapter da = new OleDbDataAdapter(cmd); da.Fill(dt); // Add table into DataSet ds.Tables.Add(dt); } cmd = null; conn.Close(); } return ds; }

然后，您可以将数据转储到GridView中，如下所示：

Then, you can dump the data into GridView like this:

void LoadDataIntoGridView()
{
    string file = "C:\\MyExcel.xlsx";
    System.Data.DataSet ds = ImportFromExcel(file);
    foreach (System.Data.DataTable dt in ds.Tables)
    {
        GridView gv = new GridView();
        gv.DataSource = dt;
        gv.AutoGenerateColumns = true;
        gv.DataBind();
        Panel1.Controls.Add(gv);
    }
}

关于将数据插入SQL数据库。你需要学习SQL数据库。

如果不知道它的基础，你将无法正常使用SQL数据库：

- 如何连接到SQL数据库

- 如何创建数据库和创建表

- 如何执行SELECT，INSERT，UPDATE，DELETE的基本操作



CREATE TABLE示例：

About inserting data into SQL database. You need to learn SQL database.
You will not be able to work properly with SQL database without knowing the basic of it:
- How to connect to a SQL database
- How to CREATE DATABASE and CREATE TABLE
- How to perform the basic operations of SELECT, INSERT, UPDATE, DELETE

Example of CREATE TABLE:

CREATE TABLE `department`(
   id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
   name varchar(50) NOT NULL,
   dateformation datetime NULL
)

示例INSERT：

Example of INSERT:

INSERT INTO department(id,name,dateformation)VALUES(1,'Sales','2012-10-27 09:00');

每个地方都可以轻松找到SQL数据库的教程。

Tutorials of SQL database can be easily found every where.

查看此链接。



将Gridview数据导出到ASP.NET中的Excel [ ^ ]

它可能会有所帮助

Check this link out.

Export Gridview Data to Excel in ASP.NET[^]
it might be helpful

这篇关于如何使用c＃将excel文件导入到asp.net中的网格视图并将网格视图保存到数据库sql server 2005的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！