Click here to Skip to main content
15,911,762 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
C#
protected void Button2_Click(object sender, EventArgs e)
        {
            string srvFileLocation = "";
            string fileName = "", fileExtension = "";
            string connectionString = "";
            if (FileUpload1.HasFile)
            {
                fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
                fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
                string fileLocation = Server.MapPath("~/App_Data/" + fileName);
                FileUpload1.SaveAs(fileLocation);
                Label1.Text = "File Uploaded: " + FileUpload1.FileName;
                //Check whether file extension is xls or xslx

                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\"";
                }
                //  String cn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source=C:\\Users\\Charu\\Documents;" +"Extended Properties=Excel 8.0;";
                //Create OleDB Connection and OleDb Command

                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);
                con.Close();
                GridView1.DataSource = dtExcelRecords;
                GridView1.DataBind();

            }
            else
            {
                Label1.Text = "No File Uploaded.";
            }

            if (FileUpload1.HasFile)
            {
                fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
                fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
                srvFileLocation = Server.MapPath("~/App_Data/" + fileName);
                FileUpload1.SaveAs(srvFileLocation);

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

            // Create Connection to Excel 
            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection);
                connection.Open();
                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=SNSWAMY\\SQLEXPRESS;Initial Catalog=payroll;Persist Security Info=True;User ID=avoc;Password=avoc123";

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
                    {
                        //bulkCopy.ColumnMappings.Add("process_datetime", "process_datetime");
                        bulkCopy.DestinationTableName = "leave_tran";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }
            }

this code imports files correctly.. But in this i want to insert one field form text box and displays in gridview along with import file...
how to combine that textbox field and import fields....
Whether any one help me...
Posted
Updated 5-Mar-12 22:32pm
v2
Comments
Mohd Imran Saifi 6-Mar-12 8:38am    
what's ur error
Charuroopa 6-Mar-12 11:20am    
in this code i have no error.. In this i want to insert one column to girdview from textbox which will not be in xl what we are importing...
in this code where we have to insert that query..
If possible can anyone tell what the query will be...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900