将excel文件数据上载到sql server数据库表中 [英] Upload excel file data into sql server database tables

问题描述

嗨朋友们，



我的要求是，我不会从网页导入excel（.xls或.xlsx格式）文件，之后我就不会插入这个excel文件数据通过使用c＃进入sql server数据库中的特定行表。所以请给我一个代码。



提前致谢

Hi friends,

My requirements is, i wont to import the excel(.xls or .xlsx format) file from webpage and after that i wont to insert this excel file data into specific rows of tables in sql server database by using c#. So please provide me a code.

Thanks in advance

推荐答案

protected void btnupload_Click(object sender, EventArgs e)
        {
            if (!FileUpload1.HasFile || string.Compare(".xlsx", Path.GetExtension(FileUpload1.FileName), true) != 0)
            {
                base.DisplayAlert("Please select a proper EXCEL file to upload and try again.");
                return;
            }

            string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
            string FilePath = Server.MapPath(FolderPath + FileName);
            FileUpload1.SaveAs(FilePath);
            GC.Collect();
            OleDbConnection objConn = null;
            DataTable dt = null;
            try
            {
                // Looping through each Excel Sheet

                DataSet ds = new DataSet();
                String connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + FilePath + ";Extended Properties=Excel 12.0;";
                objConn = new OleDbConnection(connString);
                objConn.Open();
                dt = objConn.GetOleDbSchemaTable(
                    OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                if (dt == null)
                {
                    return;
                }

                foreach (DataRow schemaRow in dt.Rows)
                {
                    string sheet = schemaRow["TABLE_NAME"].ToString().Trim();

                    try
                    {

                        OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "] ", objConn);
                        cmd.CommandType = CommandType.Text;
                        DataTable outputTable = new DataTable(sheet);
                        ds.Tables.Add(outputTable);
                        new OleDbDataAdapter(cmd).Fill(outputTable);
                        cmd.ExecuteNonQuery();
                        
                    }

嗨..

将excel数据导入mysql [ ^ ]

谢谢你。

Hi..
Import excel data into mysql[^]
Thank u.

这篇关于将excel文件数据上载到sql server数据库表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！