I am working on vsto application , i have one open workbook . i want to read selected sheet data from that workbook without using any oledb connection is there any way to read the data and store in datatable.
Asked
Active
Viewed 190 times
-1
-
Have you looked into Microsoft.Office.Interop.Excel – Jared Stroebele Jun 01 '16 at 16:40
-
yes, i have current selected sheet reference. – Pankaj Gupta Jun 01 '16 at 16:41
-
What kind of "vsto application": a workbook custumization or an add-in? – Cindy Meister Jun 01 '16 at 17:34
-
So are you looking for something like this [link](http://stackoverflow.com/questions/7244971/how-do-i-import-from-excel-to-a-dataset-using-microsoft-office-interop-excel)? – Jared Stroebele Jun 01 '16 at 17:34
-
@CindyMeister it's add in. actually i want to read data and after that i want to apply filter on it and get the selected data. – Pankaj Gupta Jun 01 '16 at 17:39
1 Answers
0
The tricky part is figuring out if the current selection is valid for what you want to do. In Excel's VBA world you'd work with the VBA information function TypeName to determine whether the current Selection is a Range object. C# doesn't have a direct equivalent, so you have to work around it. If all you're interested in is a Range, then you can check whether a direct conversion to an Excel.Range is valid and procede from there. A Range object will return an array, which you can put in a data set.
The following code sample shows how to test the Selection and work with the resulting array. It doesn't do anything with a dataset - that would be a different question.
object oSel = Globals.ThisAddIn.Application.Selection;
if ((oSel as Excel.Range) != null)
{
Excel.Range rngSelection = (Excel.Range)oSel;
object[,] data = rngSelection.Value2;
int rank = data.Rank;
int lbound = data.GetLowerBound(rank-1);
int ubound = data.GetUpperBound(rank-1);
for (int i = 1; i <= rank; i++)
{
for (int l = lbound; l <= ubound; l++)
{
System.Diagnostics.Debug.Print(data[i,l].ToString());
}
}
}
An alternative to using the cast test involves working with the COM APIs. If you needed to take various actions depending on the type of Selection this approach might be more effective. It's described here: https://www.add-in-express.com/creating-addins-blog/2011/12/20/type-name-system-comobject/
Cindy Meister
- 25,071
- 21
- 34
- 43
-
Thanks Cindy , just want to know selection property is what i select from excel now current excel sheet data? – Pankaj Gupta Jun 02 '16 at 16:31
-
The code I show you picks up the current selection in the Excel application - no matter what it is. It then checks if the current Selection is a Range of cells. If it is, that Range is assigned to an array, which you can then work with. Why don't you try it out? – Cindy Meister Jun 02 '16 at 17:54