using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using Microsoft.Office.Interop.Excel;
using System.Reflection;


namespace Dashboard
{       
    public partial class Form1 : Form
    {
        public OleDbConnection con,con1;
        public OleDbCommand cmd,cmd1;
        public OleDbDataReader dr;
        public OleDbDataAdapter db;

         public string output3,output2,output4,output10,output11,output5,output6,output9,output7;
                      
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            
        }

        private void button1_Click(object sender, EventArgs e)
        {

            try
            {
                con1 = new OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;data source=d:\rep2\uu.XLS;Extended Properties=Excel 8.0;");

                con = new OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;data source=d:\rep2\tkgrp2.XLS;Extended Properties=Excel 8.0;");
                cmd = new OleDbCommand("select * from [nave$]", con);
                con.Open();
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    string[] output1 = { dr.GetValue(1).ToString() };
                    string[] output8 = { dr.GetValue(0).ToString() };

                    for (int t = 0; t < output1.Length; t++)
                    {
                         output2 = output1[t].Substring(output1[t].IndexOf('(') + 1, output1[t].IndexOf(')') - output1[t].IndexOf('(') - 1);
                         output3 = output1[t].Substring(8, 5).ToString();
                         output4 = output1[t].Substring(0, 3).ToString();
                         output5 = output1[t].Substring(0, 16).ToString();
                         output10 = output1[t].Substring(3, 2).ToString();
                         output11 = output1[t].Substring(0, 3).ToString();
                                              

                        for (int t1 = 0; t1 < output8.Length; t1++)
                        {
                             output6 = output8[t].Substring(0, output8[t].IndexOf(' ') + 1);
                             output7 = output8[t].Substring(output8[t].IndexOf(' ') + 1);
                             output9 = output7.ToString();

                            //string str = "insert into [navv$] (DPC,TG_Number,Circle,Operators,UMG,TG_Name,Dates,Times) values('" + output3 + "','" + output2 + "','" + output4 + "','" + output10 + "','" + output11 + "','" + output5 + "','" + output6 + "','" + output9 + "')";
                            //OleDbDataAdapter data2 = new OleDbDataAdapter(str, con1);
                            //DataSet b = new DataSet();
                            //data2.Fill(b);
                           
                        }
                    }

                    string str = "insert into [navv$] (DPC,TG_Number,Circle,Operators,UMG,TG_Name,Dates,Times) values('" + output3 + "','" + output2 + "','" + output4 + "','" + output10 + "','" + output11 + "','" + output5 + "','" + output6 + "','" + output9 + "')";
                    OleDbDataAdapter data2 = new OleDbDataAdapter(str, con1);
                    DataSet b = new DataSet();
                    data2.Fill(b);
                }
                cmd.Connection.Close();
                MessageBox.Show("done");
            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.Message);
            } 
        }
    }
}

please help me its urgent........

Recommended Answers

All 4 Replies

I am exporting data to excel but inserting rows is very slow about 2 seconds per row, the line of code is

pls help its urgent......

my vb.net c# code are below

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using Microsoft.Office.Interop.Excel;


namespace Dashboard
{       
    public partial class Form1 : Form
    {
        public OleDbConnection con,con1;
        public OleDbCommand cmd,cmd1;
        public OleDbDataReader dr;
        public OleDbDataAdapter db;
                      
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                con1 = new OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;data source=d:\rep2\uu.XLS;Extended Properties=Excel 8.0;");

                con = new OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;data source=d:\rep2\tkgrp2.XLS;Extended Properties=Excel 8.0;");
                cmd = new OleDbCommand("select * from [nave$]", con);
                con.Open();
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    string[] output1 = {dr.GetValue(1).ToString() };
                    string[] output8 = { dr.GetValue(0).ToString()};
                    
                    for (int t = 0; t < output1.Length; t++)
                    {
                        string output2 = output1[t].Substring(output1[t].IndexOf('(') + 1, output1[t].IndexOf(')')-output1[t].IndexOf('(')-1);
                        string output3 = output1[t].Substring(8, 5).ToString();
                        string output4 = output1[t].Substring(0,3).ToString();
                        string output5 = output1[t].Substring(0, 16).ToString();
                        string output10 = output1[t].Substring(3, 2).ToString();
                        string output11 = output1[t].Substring(0, 3).ToString();

                        if(output4.Equals("DEL"))
                        {
                            output4 = "Delhi";
                        }
                        else if (output4.Equals("HAR"))
                        {
                            output4 = "Haryana";                        
                        }
                        else if (output4.Equals("RAJ"))
                        {
                            output4="Rajasthan";                        
                        }
                        else if (output4.Equals("MAH"))
                        {
                            output4 = "Maharastra";
                        }
                        else if (output4.Equals("GUJ"))
                        {
                            output4 = "Gujrat";
                        }
                        else if (output4.Equals("MAP"))
                        {
                            output4 = "MP";
                        }
                        else if (output4.Equals("MUM"))
                        {
                            output4 = "Mumbai";
                        }
                        else if (output4.Equals("KEL"))
                        {
                            output4 = "Kerala";
                        }
                        else if (output4.Equals("ANP"))
                        {
                            output4 = "AP";
                        }
                        else if (output4.Equals("TNC"))
                        {
                            output4 = "TN";
                        }
                        else if (output4.Equals("JNK"))
                        {
                            output4 = "J&K";
                        }
                        else if (output4.Equals("UPW"))
                        {
                            output4 = "UP West";
                        }
                        else if (output4.Equals("PUN"))
                        {
                            output4 = "Punjab";
                        }
                        else if (output4.Equals("BIH"))
                        {
                            output4 = "Bihar";
                        }
                        else if (output4.Equals("UPE"))
                        {
                            output4 = "UP East";
                        }
                        else if (output4.Equals("PUB"))
                        {
                            output4 = "Punjab";
                        }
                        else if (output4.Equals("KAR"))
                        {
                            output4 = "Karnataka";
                        }
                        else if (output4.Equals("CHN"))
                        {
                            output4 = "Chennai";
                        }
                        else if (output4.Equals("KOL"))
                        {
                            output4 = "Kolkata";
                        }
                        else if (output4.Equals("PNB"))
                        {
                            output4 = "Punjab";
                        }
                        else if (output4.Equals("HIP"))
                        {
                            output4 = "HP";
                        }
                        else if (output4.Equals("WEB"))
                        {
                            output4 = "WB";
                        }
                        else if (output4.Equals("ASM"))
                        {
                            output4 = "Assam";
                        }
                        else if (output4.Equals("NOE"))
                        {
                            output4 = "North East";
                        }
                        else if (output4.Equals("ORI"))
                        {
                            output4 = "Orissa";
                        }
                        
                        if (output10.Equals("ID"))
                        {
                            output10 = "Idea";
                        }
                        else if (output10.Equals("SC"))
                        {
                            output10 = "MTS";
                        }

                        else if (output10.Equals("TM"))
                        {
                            output10 = "TTSL";
                        }
                        else if (output10.Equals("AC"))
                        {
                            output10 = "Aircel";
                        }
                        else if (output10.Equals("AM"))
                        {
                            output10 = "Airtel";
                        }
                        else if (output10.Equals("RC"))
                        {
                            output10 = "Rcom";
                        }
                        else if (output10.Equals("VM"))
                        {
                            output10 = "Vodafone";
                        }
                        else if (output10.Equals("BG"))
                        {
                            output10 = "BSNL";
                        }
                        else if (output10.Equals("LP"))
                        {
                            output10 = "Loop";
                        }
                        else if (output10.Equals("RT"))
                        {
                            output10 = "RTL";
                        }
                        else if (output10.Equals("SP"))
                        {
                            output10 = "Spice";
                        }
                        else if (output10.Equals("UT"))
                        {
                            output10 = "UniTech";
                        }
                        else if (output10.Equals("AL"))
                        {
                            output10 = "Etislat";
                        }
                        else if (output10.Equals("MG"))
                        {
                            output10 = "MTNL";
                        }
                        else if (output10.Equals("HG"))
                        {
                            output10 = "HFCL";
                        }
                        else if (output10.Equals("TG"))
                        {
                            output10 = "TTSL";
                        }
                        else if (output10.Equals("DT"))
                        {
                            output10 = "Videocon";
                        }
                        else if (output10.Equals("AC"))
                        {
                            output10 = "Aircel";
                        }
                        else if (output10.Equals("TC"))
                        {
                            output10 = "TTSL";
                        }
                        else if (output10.Equals("RM"))
                        {
                            output10 = "Rcom";
                        }
                        else if (output10.Equals("TF"))
                        {
                            output10 = "TTSL";
                        }
                        else if (output10.Equals("DN"))
                        {
                            output10 = "Aircel";
                        }
                        else if (output10.Equals("BF"))
                        {
                            output10 = "BSNL";
                        }
                        else if (output10.Equals("DN"))
                        {
                            output10 = "Aircel";
                        }
                        else if (output10.Equals("VS"))
                        {
                            output10 = "VSNL";
                        }
                        else if (output10.Equals("TN"))
                        {
                            output10 = "TATA NLD";
                        }
                        else if (output10.Equals("MF"))
                        {
                            output10 = "MTNL Fixed";
                        }
                        else if (output10.Equals("ST"))
                        {
                            output10 = "STEL";
                        }
                        else if (output10.Equals("VA"))
                        {
                            output10 = "VAS";
                        }

                        if (output11.Equals("MAH"))
                        {
                            output11 = "Pune";
                        }
                        else if (output11.Equals("GUJ"))
                        {
                            output11 = "Gandhinagar";
                        }
                        else if (output11.Equals("MAP"))
                        {
                            output11 = "Indore";
                        }
                        else if (output11.Equals("MUM"))
                        {
                            output11 = "Mumbai";
                        }
                        else if (output11.Equals("KEL"))
                        {
                            output11 = "Cochin";
                        }
                        else if (output11.Equals("ANP"))
                        {
                            output11 = "Hyderabad";
                        }
                        else if (output11.Equals("TNC"))
                        {
                            output11 = "Coimbatore";
                        }
                        else if (output11.Equals("DEL"))
                        {
                            output11 = "Noida";
                        }
                        else if (output11.Equals("RAJ"))
                        {
                            output11 = "Jaipur";
                        }
                        else if (output11.Equals("HAR"))
                        {
                            output11 = "Ambala";
                        }
                        else if (output11.Equals("JNK"))
                        {
                            output11 = "Mohali";
                        }
                        else if (output11.Equals("UPW"))
                        {
                            output11 = "Meerut";
                        }
                        else if (output11.Equals("PUN"))
                        {
                            output11 = "Pune";
                        }
                        else if (output11.Equals("BIH"))
                        {
                            output11 = "Patna";
                        }
                        else if (output11.Equals("UPE"))
                        {
                            output11 = "Lucknow";
                        }
                        else if (output11.Equals("PUB"))
                        {
                            output11 = "Mohali";
                        }
                        else if (output11.Equals("KAR"))
                        {
                            output11 = "Banglore";
                        }
                        else if (output11.Equals("CHN"))
                        {
                            output11 = "Coimbatore";
                        }
                        else if (output11.Equals("KOL"))
                        {
                            output11 = "Kolkatta";
                        }
                        else if (output11.Equals("PNB"))
                        {
                            output11 = "Mohali";
                        }
                        else if (output11.Equals("HIP"))
                        {
                            output11 = "Mohali";
                        }
                        else if (output11.Equals("WEB"))
                        {
                            output11 = "Kolkatta";
                        }
                        else if (output11.Equals("ASM"))
                        {
                            output11 = "Kolkatta";
                        }
                        else if (output11.Equals("NOE"))
                        {
                            output11 = "Kolkatta";
                        }
                        else if (output11.Equals("ORI"))
                        {
                            output11 = "Bhuwaneshwar";
                        }
                                                
                        for (int t1 = 0; t1 < output8.Length; t1++)
                        {
                            string output6 = output8[t].Substring(0, output8[t].IndexOf(' ') + 1);
                            string output7 = output8[t].Substring(output8[t].IndexOf(' ') + 1);
                            string output9 = output7.ToString();

                            string str = "insert into [navv$] (DPC,TG_Number,Circle,Operators,UMG,TG_Name,Dates,Times) values('" + output3 + "','" + output2 + "','" + output4 + "','"+output10+"','"+output11+"','" + output5 + "','" + output6 + "','" + output9 + "')";
                            OleDbDataAdapter data2 = new OleDbDataAdapter(str, con1);
                            DataSet b = new DataSet();
                            data2.Fill(b);


                            //cmd1 = new OleDbCommand(str, con1);
                            //cmd1.Connection.Open();
                            //int g1 = cmd1.ExecuteNonQuery();
                            //cmd1.Connection.Close();
                        }
                    }                  
                }
                cmd.Connection.Close();   
                MessageBox.Show("done");                
            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.Message);
            }                                    
        }
    }
}

hai adam_k

can u correct this code pls.........

Sorry friend, we don't provide ready made code here. I believe I've pointed you in the correct direction. Do your part and post again if you are facing problems.

PS: Even if I wanted I couldn't help as your code is in C# and I only know VB
PS2: If you are having problems post in the C# section.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.