Excel Addin.

kjell nilsson

Hi. I have built an Excel addin for extracting data from Sql server. Now i wonder how to imort a textstring to a sheet/workbook or other containing the selected data. Before i have imported a textfile using workbooks.opentext function for importing a file. Is there a way of importing a string or must i save it as a temporary file first

Best Regards Kjell Nilsson

Cindy Meister

Could you expand a bit more on what it is you need to do Are you just trying to add text to a cell in Excel (a label or description, for example) Or is it something else

You can assign text to a cell using the .Range.Value2 property. Rough example:

worksheet.Range("A2", missing).Value2 = "some text";

kjell nilsson

Hi. I have a textstring, comma separated, that represents a big database table. 256 columns and 10 000 rows approx. This string i want to open i Excel in some way. I want to use Excel as a reporting tool. I have done this from a textfile before with workbooks.opentext(filepath), but now i have a string in my Excel addin It is written in VS 2005, C#. One way of doing it is of course to save the string as a file and import it the same way, but i hoped for a better solution...

Best R Kjell

Ji Zhou ¨C MSFT

Hi Kjell,

I think these codes may work in your scenario:

string[] separators = new string[1];

separators = ",";

string[] split = youString.Split(separators,StringSplitOptions.None); //Assuming your string are stored in a variable named youString

int i = 0;

foreach(string s in split)

{

i++

int x=i/256+1;

int y=i%256;

Excel.Range myRange = this.Application.Cells[x,y] as Excel.Rang;

myRange.Value2 = s;

}

Hopes this will be helpful :-)

Thanks

Ji

Cindy Meister

Hi Kjell

Certainly the most efficient way to dump a large amount of data into Excel, without first saving it as a file, is to

1. Convert it to an ADO (not ADO.NET) recordset

2. Use the CopyFromRecordset method in Excel to transfer the entire recordset to a Range of values

Where is this text string coming from If you're picking it up from a database, try going directly to an ADO recordset, rather than a delimited string.

kjell nilsson

Hi. Thank you. This is for sure much faster. But I must admit that i hoped for an even faster solution. About 4 times slower than Sql management studio to load data. Any more tips on how to speed it up I have also disabled graphics updating. I took imported the data directly to the recordset as you suggested.

like this:

this.Application.ScreenUpdating = false;
this.Application.DisplayAlerts = false;

Dennis Wallentin

Kjell,

The SQL Management Studio works directly with the DB while the VSTO add-in works through several layers (pls see http://xldennis.wordpress.com/2007/01/21/creating-and-deploying-managed-com-add-ins-with-vsto-2005-se-part-iv/). All in all, You will not get the wanted performance.

If You targeting Excel 2000 to 2007 then classic ADO with the CopyFromRecordset is the way to go. However, if the add-in targets 2003 and / or 2007 then You should use ADO.NET together with the ListObject. For more information on this pls see http://xldennis.wordpress.com/2007/05/08/csv-files-listobject-controls-and-vsto/

kjell nilsson

I am targetting >2003. So Listobject could be a solution. I have finished it with copyfromrecordset though. Will it be faster with listobject More stabel I must say i prefer using ADO.net...
Best R Kjell

Dennis Wallentin

Hi Kjell,

Will it be faster with listobject

It depends on which Data Provider You use and if You're forced to also include the fieldnames or not. As a general opinion I will agree on using ADO.NET, especially if You're using the .NET Data Provider for SQL Server (2000 or 2005):