0

I am exporting data to excel but inserting rows is very slow about 2 seconds per row

pls help

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();
 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);
            }                                    
        }
    }
}

Edited by Dani: Formatting fixed

2
Contributors
1
Reply
3
Views
5 Years
Discussion Span
Last Post by skatamatic
0

The COM interop for excel is REALLY crappy for some reason. I wrote a class that loads in at max 100x100 grid. Takes about 45 seconds to load it...... I didn't use OLE, I used the interop, but I suspect they are similarly bad. You can try using the COM interop - it might be a bit faster. Note that excel is compatible with csv files, and those can be created lightning fast :)

Edited by skatamatic: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.