oledb connection to excel file

[TripleB]

Hello all,

 

I have written a class that exports all the rows of a table in a dataset to an excel file, I use an oledb connection to do this.

 

now it works no problem but I wonder if I can speed it up, this is where you specialist come in, this is my code

 

this one creates the excel file and the first cells ( headers )

try
		{
			if(System.IO.File.Exists(System.IO.Path.GetFullPath(_FileName)))
				System.IO.File.Delete(System.IO.Path.GetFullPath(_FileName));

			DT = _ds.Tables[0];
			StringBuilder CreateTable = new StringBuilder("CREATE TABLE ");
			CreateTable.Append(_ds.DataSetName);
			CreateTable.Append(" (");
			foreach(DataColumn col in DT.Columns)
			{
			
				CreateTable.Append(col.ColumnName);
				CreateTable.Append(" " + DBDataType(col.DataType,col.MaxLength) + ", ");
			
			}
			CreateTable.Replace(",",")",CreateTable.Length - 2,1);
			CreateTable.Append(";");
			Console.WriteLine(CreateTable.ToString());
			Console.WriteLine( BVQuery.Excel(_FileName).ExecuteNonQuery(CreateTable.ToString()) );
			return true;
		}
		catch(OleDbException)
		{
			return false;
		}

 

this inserts the records into the exsisting excel file.

 

StringBuilder InsertString = new StringBuilder();
		foreach(DataRow row in DT.Rows)
		{
			if(InsertString.Length > 0)
				InsertString.Remove(0,InsertString.Length);
			InsertString.Append("INSERT INTO ");
			InsertString.Append(_ds.DataSetName);
			InsertString.Append(" (");
			foreach(DataColumn col in DT.Columns)
			{
				InsertString.Append(col.ColumnName + ",");
			}
			InsertString.Replace(",",")",InsertString.Length - 1,1);
			InsertString.Append(" VALUES (");
			for(int i = 0;i<row.ItemArray.Length;i++)
			{
				InsertString.Append("'" + row.ItemArray.GetValue(i).ToString() + "',");
			}
			InsertString.Replace(",",")",InsertString.Length -1,1);
			InsertString.Append(";");
			Console.WriteLine(InsertString.ToString());
			Console.WriteLine(BVQuery.Excel(_FileName).ExecuteNonQuery(InsertString.ToString())); 
			
		}

 

the BVQuery.excel.executenonquery just executes the sql string given on a oledb connection to the excel file

 

All comment apreciated

 

Greetz to you all

[TripleB]

Hello all,

 

I have written a class that exports all the rows of a table in a dataset to an excel file, I use an oledb connection to do this.

 

now it works no problem but I wonder if I can speed it up, this is where you specialist come in, this is my code

 

this one creates the excel file and the first cells ( headers )

try
		{
			if(System.IO.File.Exists(System.IO.Path.GetFullPath(_FileName)))
				System.IO.File.Delete(System.IO.Path.GetFullPath(_FileName));

			DT = _ds.Tables[0];
			StringBuilder CreateTable = new StringBuilder("CREATE TABLE ");
			CreateTable.Append(_ds.DataSetName);
			CreateTable.Append(" (");
			foreach(DataColumn col in DT.Columns)
			{
			
				CreateTable.Append(col.ColumnName);
				CreateTable.Append(" " + DBDataType(col.DataType,col.MaxLength) + ", ");
			
			}
			CreateTable.Replace(",",")",CreateTable.Length - 2,1);
			CreateTable.Append(";");
			Console.WriteLine(CreateTable.ToString());
			Console.WriteLine( BVQuery.Excel(_FileName).ExecuteNonQuery(CreateTable.ToString()) );
			return true;
		}
		catch(OleDbException)
		{
			return false;
		}

 

this inserts the records into the exsisting excel file.

 

StringBuilder InsertString = new StringBuilder();
		foreach(DataRow row in DT.Rows)
		{
			if(InsertString.Length > 0)
				InsertString.Remove(0,InsertString.Length);
			InsertString.Append("INSERT INTO ");
			InsertString.Append(_ds.DataSetName);
			InsertString.Append(" (");
			foreach(DataColumn col in DT.Columns)
			{
				InsertString.Append(col.ColumnName + ",");
			}
			InsertString.Replace(",",")",InsertString.Length - 1,1);
			InsertString.Append(" VALUES (");
			for(int i = 0;i<row.ItemArray.Length;i++)
			{
				InsertString.Append("'" + row.ItemArray.GetValue(i).ToString() + "',");
			}
			InsertString.Replace(",",")",InsertString.Length -1,1);
			InsertString.Append(";");
			Console.WriteLine(InsertString.ToString());
			Console.WriteLine(BVQuery.Excel(_FileName).ExecuteNonQuery(InsertString.ToString())); 
			
		}

 

the BVQuery.excel.executenonquery just executes the sql string given on a oledb connection to the excel file

 

All comment apreciated

 

Greetz to you all

 

Ok, the reason my code was so slow was actually my own dumb fault, I opened and closed the connection everytime I added an record so seriously perfomance drop, aftering altering this method in first adding the sql string to an string array, and than executing them all in 1 open connection time was divided by 45 so i would say thats a reasonable improvement :).

 

I can now add approx 250 records in 1,52 sec whats ok for me, i am still going to try an other approach with first writing it to csv and then import that file into excel, the writing of the csv takes only 0.25 sec so unless importing it in excel takes forever it might just be a bit faster

 

I'll post my findings back here,

 

Greetz

Edited August 5, 2005 by TripleB