CODEWITHSUNDEEP
c#exceldatatabledatasetvsto
UmaN
UmaN

Reputation: 915

How to get data from C# to Excel smoothly using VSTO and back again

I'm writing an application level add-in for Excel in C#.

The add-in is to be used for getting data from a foreign source (the add-in provides some GUI options for this etc.) into Excel. This data is not going to be updated and sent back to the data source or anything like that - although the user is of course free to edit the data in the local Excel application.

The data arrives in an XML format and currently I have used a code generation tool to be able to deserialize the xml documents into C# objects. The data follows a relational model.

The things I'm thinking about right now:

Some other questions as well...

I've never worked with this before so apologizing if some questions seem stupid. Any help is appreciated.

Upvotes: 2

Views: 4661

Answers (4)

Alexandre C.
Alexandre C.

Reputation: 56956

Excel tables (aka "ListObjects") give you formatting for free, and they are easy to use. The corresponding type is ListObject.

You can use them with LINQ, without having to manufacture a DataSet object:

ListObject myTable; // usually declared somewhere else, eg. via the designer

var data = from x in myObjects select new
{
    Foo = x.Foo,
    Bar = x.Bar
};

myTable.SetDataBinding(data.ToList());

This will fill the table with your data using reflection. In the example above, you will have two columns titled Foo and Bar, and as many rows as you had elements in myObjects.

Of course, you can use more complex queries. In your case, using Linq to XML is probably a good idea. The point is that you can do exactly what you want in a handful of lines.

Also, you can put any IList<object> into SetDataBinding.

Upvotes: 0

Brijesh Mishra
Brijesh Mishra

Reputation: 2748

Using range along with 2d array in Excel will give you beter performance. Here as you are deserializing incoming xml to object, there is no need to convert it into dataset then to 2d array. Would recommend in view layer of your code you directly tranform your object to 2d array and then bind with range in excel sheet. For reading back read data into 2d array from range and then tranform it back to object which you can serialize and send it back to server. Now how effectively or exactly you use range or array will depend on how data in your sheet looks. To distinguish between header and data you may have a look at named range, it can be helpful.

Upvotes: 1

UmaN
UmaN

Reputation: 915

Not sure why my question was downvoted... At least give reasons, how else can they become better next time around?

Anyway. The best solution, it seems to me, is to get my data into a DataSet and then create a ListObject in the Excel application and use it's data binding features to get my data into Excel.

Was not aware of this great control before.

Upvotes: 1

aerobrain
aerobrain

Reputation: 623

Here are some example from my previous work to open excel and get data from excel: public class ExcelModule { private Excel.Application excelApp; private Excel.Workbook excelBook; private Excel.Worksheet excelSheet;

    object misValue = System.Reflection.Missing.Value;
    object oMissing = System.Reflection.Missing.Value;

    public ExcelModule()
    {

    }

    public void OpenWorksheet(string fileName, int sheetNum)
    {
        excelApp = new Excel.Application();
        excelBook = excelApp.Workbooks.Open(fileName,
                0,
                true,
                5,
                "",
                "",
                true,
                Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
                "\t",
                false,
                false,
                0,
                true,
                1,
                0);
        excelSheet = (Excel.Worksheet)excelBook.Worksheets.get_Item(sheetNum);

    }

    public string GetValue(string cellAddress)
    {
        if (excelSheet.get_Range(cellAddress, cellAddress).Value2 != null)
            return excelSheet.get_Range(cellAddress, cellAddress).Value2.ToString();
        else
            return "";
    }

    public int Close()
    {
        excelApp.Quit();
        return 0;
    }

    ~ExcelModule()
    {
        excelApp.Quit();
    }
}

To write data into Excel you may use:

excelSheet.get_Range(cellAddress, cellAddress).Value2 = "your text";

Notes: *I'm using VS10 with Office2007

Upvotes: 1

Related Questions