Embed excel sheet in C# user control

Question

I need to embed an excel sheet portion (not the whole sheet) in a user control in C# language. And I want to manipulate the data on it and save it in a collection.

What is the best way to go about it?

Answer 1

Why not use a DataGridView populated with the data you want to alter?

Once any alterations are complete, save the data from the DataGridView over the portion of the Excel file you pulled it from to begin with.

You may also find these links useful!

Answer 2

Look into the Microsoft.Office.Interop.Excel library. There are a lot of excel controls in there to work with excel files and operations.

Answer 3

I would open the spreadsheet with the Excel COM Library. If you add a reference to the Microsoft Excel Object Library, you can get to the Com interface.

Add these using statements:

using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;

Then you can read from the spreadsheet by doing something like this:

   private void GetData(string fileName, string tabName)
    {
        Workbook theWorkbook;

        Application ExcelObj = null;
        ExcelObj = new Application();

        theWorkbook = ExcelObj.Workbooks.Open(fileName,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing);


        Sheets sheets = theWorkbook.Worksheets;
        Worksheet worksheet = (Worksheet)sheets[tabName];

        Range range = worksheet.get_Range("A1:A1", Type.Missing);

        string data = range.Text as string;

        //
        // TODO : store the data
        //

        theWorkbook.Close(false, fileName, null);
    }

This code would read the contents of the A1 cell into a string.

One of the quirks of working with the Excel COM interface is that you have to access data in a Range, even if you just want one cell. You can set the range to be a group of cells, then you can iterate over the collection that it returns to get the contents of each cell.

You would also want to add some error checking/handling on the file name and tab name.

There is also a way to use ODBC to read from Excel, but the spreadsheet has to be formatted in a certain way. There has to be a header data in row 1. I've found it easier to use the COM interface.

Once you have acquired the data you need, you could put it into a typed DataSet. Then you could bind that dataset to a DataGridView if you're using WinForms or a ListBox in WPF. If you just want to save the data in an XML format, you could use the DataSet WriteXml function to store the data to a file.