Solved

How do you databind a DataSet to an Excel Worksheet from a VSTO Plugin?

Posted on 2008-09-30
1
4,180 Views
Last Modified: 2013-12-17
I am pretty sure that the key is a listObject but I cannot figure out how to create one in code.

Here is my Setup:
Visual Studios 2008
Visual Studio Template: Excel 2007 Addin
Code Executed from a UserControl.

So far I have the code calling a webservice and returning a DataSet. I have the populated dataset but cannot get the data from that dataset into the active worksheet.

I have seen a few examples of how to do this but all of them seem to be from a different project type (Excel 2007 workbook from what I can tell) and the same commands do not seem to be available.

If someone could show me some sample code of a Dataset being bound in someway to an excel worksheet it would be greatly appreciated.
0
Comment
Question by:docstar
1 Comment
 

Accepted Solution

by:
docstar earned 0 total points
ID: 22609592
Got It, finally after a full day researching just this one item:
http://theengineroom.provoke.co.nz/archive/2007/06/19/how-to-bulk-insert-data-into-an-excel-worksheet-using-c-and-office-12.aspx

Attached is the slightly modified version for those having a simular issue:
/// <summary>

        /// Submits Report and populates Excel with the results.

        /// All parameters should have been validated prior to calling this method.

        /// </summary>

        private void SubmitReport()

        {

            try

            {

                ReportService.ASGReportsServiceClient sc = new ExcelReportPlugin.ReportService.ASGReportsServiceClient();

                ReportService.ReturnCode oReturn = new ExcelReportPlugin.ReportService.ReturnCode();

                DataSet ds = sc.ExecuteReport(ut, SelReport, ref oReturn);

                if (oReturn.Result == ExcelReportPlugin.ReportService.Code.cSuccess)

                {

                    //Bind Dataset to the Active Excel Sheet.

                    Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;

                    worksheet.UsedRange.Clear();

                    Microsoft.Office.Interop.Excel.Range rng = AddData(ds.Tables[0], worksheet);

                    Addtable(rng, "ThisIsMyTable");                    

                }

                else

                {

                    throw new Exception(oReturn.Description, oReturn.Exception);

                }

            }

            catch (Exception e) { ExceptionHandler(e); }

        }

        /// <summary>

        /// Add data to the sheets, with the columns names at the top 

        /// </summary>

        /// <param name="dataTable">The data to be added to the sheet</param>

        /// <param name="sheetToAddTo">The worksheet to add the data to</param>

        /// <returns>The range of the data that has been added</returns>

        private Microsoft.Office.Interop.Excel.Range AddData(System.Data.DataTable dataTable, Microsoft.Office.Interop.Excel.Worksheet sheetToAddTo)

        {

            //create the object to store the column names

            object[,] columnNames;

            columnNames = new object[1, dataTable.Columns.Count];

            //add the columns names from the datatable

            for (int i = 0; i < dataTable.Columns.Count; i++)

            {

                columnNames[0, i] = dataTable.Columns[i].ColumnName;

            }
 

            //get a range object that the columns will be added to

            Microsoft.Office.Interop.Excel.Range columnsNamesRange = (Microsoft.Office.Interop.Excel.Range)sheetToAddTo.get_Range(sheetToAddTo.Cells[1, 1]

           , sheetToAddTo.Cells[1, dataTable.Columns.Count]);
 

            //a simple assignement allows the data to be transferred quickly

            columnsNamesRange.Value2 = columnNames;
 

            //release the columsn range object now it is finished with

            columnsNamesRange = null;
 

            //create the object to store the dataTable data

            object[,] rowData;

            rowData = new object[dataTable.Rows.Count, dataTable.Columns.Count];
 

            //insert the data into the object[,]

            for (int iRow = 0; iRow < dataTable.Rows.Count; iRow++)

            {

                for (int iCol = 0; iCol < dataTable.Columns.Count; iCol++)

                {

                    rowData[iRow, iCol] = dataTable.Rows[iRow][iCol];

                }

            }
 

            //get a range to add the table data into 

            //it is one row down to avoid the previously added columns

            Microsoft.Office.Interop.Excel.Range dataCells = (Microsoft.Office.Interop.Excel.Range)sheetToAddTo.get_Range(sheetToAddTo.Cells[2, 1],

            sheetToAddTo.Cells[dataTable.Rows.Count + 1, dataTable.Columns.Count]);
 

            //assign data to worksheet

            dataCells.Value2 = rowData;

            

            //release range

            dataCells = null;

            

            //return the range to the new data

            return sheetToAddTo.get_Range(sheetToAddTo.Cells[1, 1],

           sheetToAddTo.Cells[dataTable.Rows.Count + 1, dataTable.Columns.Count]);

        }

        /// <summary>

        /// This function adds a new Excel table 

        /// (with nice formatting and filtering etc) 

        /// to the tableRange passed in. It names the range tableName. 

        /// This will error if tableName is already used.

        /// This function uses XlYesNoGuess.xlYes to 

        /// automatically make the first row the headings.

        /// </summary>

        /// <param name="tableRange">Range to convert to a table</param>

        /// <param name="tableName">Name of new table</param>

        private void Addtable(Microsoft.Office.Interop.Excel.Range tableRange, string tableName)

        {

            Microsoft.Office.Interop.Excel.Worksheet activeSheet = (Microsoft.Office.Interop.Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;

            Microsoft.Office.Interop.Excel.ListObject newList = tableRange.Worksheet.ListObjects.Add(Microsoft.Office.Interop.Excel.XlListObjectSourceType.xlSrcRange, 

                                                                    tableRange,null, Microsoft.Office.Interop.Excel.XlYesNoGuess.xlYes, tableRange);

            newList.Name = tableName;

        }

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help with deleting records with two duplicate columns using VB.NET 12 43
Unable  to create new object 9 38
Close word object 13 38
Runtime Error 2 27
For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now