Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to QueryTables.Add method or Range.CopyFromRecordset???
I am moving data from an ADO.NET DataTable object in C# and
I am having performance issues. QueryTables.Add, at least when called from C#, seems to spin off its own thread. So I go doing a bunch of other stuff to an invisible sheet AFTER I want the data transfer to occur, however, even after everything else completes (including showing the file) it is still executing. I can't use Range.CopyFromRecordset as that method seems to interface with my .NET DataTable. So I am essentially setting cell values like so: for (int i=0; i< myDTable.Rows.Count; i++) { for (int j=0; j< myDTable.Columns.Count; j++) { myCell.Value = myDTable.Rows[i][j]; //this is pseudo } } and this is horribly inefficient, even for modest sized data sets. Any way to dump this to the sheet faster. I wouldn't mind a non-looping approach to putting a DataRow object in some Range, I just don't know how to do it. Thanks. thomas |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to QueryTables.Add method or Range.CopyFromRecordset???
Hi Thomas,
Can you provide some sample of querytable in excel using C#. I am working for some assignment, but the query table.add method gives error. I wonder what the problem is. I am new to programming of excel and querytables. We are using Excel 11.0 libraray in C#. The code snippet:- static void Main(string[] args) { try { Excel.Application oXL; Excel._Workbook oWB; Excel._Worksheet oSheet; // start Excel and get Application object oXL = new Excel.Application(); oXL.Visible = true; // get a new workbook oWB = (Excel._Workbook)(oXL.Workbooks.Add(System.Reflect ion.Missing.Value)); oSheet = (Excel._Worksheet)oWB.ActiveSheet; // I had a problem here; // import data to excel sheet oSheet.QueryTables.Add("server=XXXX;database=XXXX; uid=sa;pwd=sa;",oSheet.get_Range("A1", "A1"), "exec prc_X"); // NOTE: for the 3rd argument I do a stored procedure //call, but this can also be a straigh sql statement... // the QueryTables collection of the worksheet seems to //be indexed starting at 1 oSheet.QueryTables[1].Refresh(true); oXL.Visible = true; } catch (Exception ex) { Console.WriteLine (ex.Message + "\n" + ex.StackTrace); } The error message is as following:- Message "Exception from HRESULT: 0x800A03EC." String _stackTraceString " at System.RuntimeType.ForwardCallToInvokeMember(Strin g memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)\r\n at Excel.QueryTables.Add(Object Connection, Range Destination, Object Sql)\r\n at ConsoleApplication2.Class1.Main(String[] args) in c:\\documents and settings\\amitba\\my documents\\visual studio projects\\consoleapplication2\\class1.cs:line 40" string Your prompt assistance is highly expected. Thanks for your time and effort. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Alternative copy/delete method needed | Excel Discussion (Misc queries) | |||
IF statement inside a SUMIF statement.... or alternative method | Excel Worksheet Functions | |||
CopyFromRecordset method | Excel Programming | |||
CopyFromRecordset method | Excel Programming | |||
Using Add Method of QueryTables gives error | Excel Programming |