Member Avatar for nav07

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 because: Formatting fixed
  • 2 Contributors
  • 1 Reply
  • 488 Views
  • 17 Hours Discussion Span
  • Latest Post Latest Post by skatamatic

Dani AI

Generated

Short diagnosis and immediate fixes for : the biggest cost is doing a full data adapter / DataSet operation (and opening/closing work) for every single row. Open the destination connection once, prepare a single parameterized OleDbCommand and reuse it inside the loop — don't call Fill per row or rebuild SQL strings each time.

con1.Open();
var cmd = new OleDbCommand("INSERT INTO [navv$] (DPC,TG_Number,...) VALUES (?,?,?,?,?,?,?,?)", con1);
// add parameters once: cmd.Parameters.Add(...);
foreach(var r in rows) {
  // set parameter values: cmd.Parameters[i].Value = ...
  cmd.ExecuteNonQuery();
}
con1.Close();

Faster approaches (recommended): skip the Excel OLE/COM route entirely. Writing a CSV (streamed with a single StreamWriter) or creating an XLSX using a library like ClosedXML / EPPlus / Open XML SDK is orders of magnitude faster because they write the file directly instead of issuing SQL-like inserts into a spreadsheet. Example pattern with ClosedXML:

using(var wb = new XLWorkbook()) {
  var ws = wb.Worksheets.Add("navv");
  ws.Cell(1,1).InsertData(rowsEnumerable);
  wb.SaveAs(path);
}

If you must automate Excel (Interop), minimize COM calls: set Application.ScreenUpdating = false and calculation to manual, build a single object[,] with all values and assign it to a Range.Value/Value2 in one call, then restore settings. That turns O(n) COM calls into a single bulk assignment.

Final checklist: profile with Stopwatch to confirm the hot path; keep files local (network/antivirus slows IO); parameterize commands to avoid repeated parsing; do long exports on a background thread; prefer CSV or a managed Excel library for bulk exports. These changes typically reduce per-row time from seconds to milliseconds.

Member Avatar for skatamatic

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 because: n/a
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.