This project has moved. For the latest updates, please go here.

confused newby trying to use excel

Mar 25, 2013 at 4:19 PM
Hi
apologies if this has all been dealt with elsewhere, but I just get more confused the deeper I delve :(
I am trying to provide a user with an add-in that will pop-up a form to allow them to enter validated data, which will then be used to fill in the next available row in the current sheet. Seems a reasonable enough task.
I can generate a button in the ribbon, I can create a windows from which pops up and runs a function when the OK button is clicked.
Using Interop, I can detect the range of occupied cells, but to locate the next row I need to do some extreme regex parsing of the range returned by Application.ActiveSheet.UsedRange as their may be empty cells (so using .End or .Next doesn't behave correctly.
I was hoping that NetOffice would a) help me over this and/or provide me with office version independence. However I can see no way of getting hold of the ActiveSheet, and all of the examples seem to work by creating new instance of excel and then creating a new workbook within that instance.
Am I simply missing the point of what/how netoffice does - or am I simply going about things in an unreasonable way?
advice please?
pretty please?

Justin
Developer
Mar 26, 2013 at 11:02 AM
jathalls wrote:
I was hoping that NetOffice would a) help me over this and/or provide me with office version independence. However I can see no way of getting hold of the ActiveSheet, and all of the examples seem to work by creating new instance of excel and then creating a new workbook within that instance.
Here is an example of getting active sheet in VB.NET. _application variable is already defined in addin code generated with NetOffice Project Wizard.
Dim WithEvents _application As Excel.Application
...
Dim sheet As Excel.Worksheet
sheet = _application.ActiveSheet

Mar 26, 2013 at 2:19 PM
artnib wrote:
Here is an example of getting active sheet in VB.NET. _application variable is already defined in addin code generated with NetOffice Project Wizard.
Dim WithEvents _application As Excel.Application
...
Dim sheet As Excel.Worksheet
sheet = _application.ActiveSheet

Hi
thanks for the response. I am actually using C# (Visual Studio 2010 Ult, with office 2010 installed on the development machine) but I can generally interpret from VB. I had found the Excel.Application class and the static method GetActiveWorksheet(), but any reference to Excel.Application simply generates a compiler error 'Interop type 'NetOffice.ExcelApi.Application' cannot be embedded. Use the applicable interface instead.' There was a mention of this in an earlier thread in the discussion which referred to changing a property to false, but it did not say what it was a property of or how to access it so it was a bit of a dead end.
:)
Developer
Mar 27, 2013 at 9:33 AM
To get rid of 'Interop type 'NetOffice.ExcelApi.Application' cannot be embedded. Use the applicable interface instead.' error do the following:
  1. in the Solution Explorer right-click on Excel reference and choose Properties item
  2. set Embed Interop Values property value to False.
Mar 27, 2013 at 10:21 AM
Edited Mar 27, 2013 at 10:31 AM
Hello jathalls,

I had this issue often too.

When you download http://netoffice.codeplex.com/downloads/get/454192

Download and try to go in NetOffice 1.5.1.2 [RC] folder and choose the correct version of the .NET framework (3.5 for example) -> Assemblies -> Any CPU
Copy the correct .dll you need, really important!

Copy them in your project.

Project (top) -> Add References -> Browse -> choose the .dll you need

Then, from your Explorer window, just drag and drop the .dll in your Assemblies file for example and as artnib mentioned -> set Embed Interop Values property value to False.

In your Reference, you can always right click -> Properties -> Local Copy -> True

In case it works, you can test and do :
using Excel = NetOffice.ExcelApi;
It should show you the ExcelApi properties

Or what else you can do is to copy the needed .dll files into C:\Windows\System32

And open command shell with cmd.exe -> cd C:\Windows\System32 -> regsvr32 <filename>.dll but I don't think it will be necessary here and use the Assemblies from there.

Later, while working with COM's don't forget to close them, or your memory will be overloaded.
Process[] processes = System.Diagnostics.Process.GetProcessesByName("EXCEL");
foreach (Process p in processes)
p.Kill();
To create an application;
Excel.Application xlsApp = new Excel.Application();
Creating a new workbook:
Excel.Workbook workbook;
workbook = xlsApp.Workbooks.Open(<open from another xls file for example>, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                Type.Missing, Type.Missing);
Now if you want to loop through worksheets, cells and loop through shapes/images/figure:
Excel.Worksheet workSheet;
foreach (Excel.Worksheet sheet in workbook.Sheets)
{   
                        workSheet = (Excel.Worksheet)xlsApp.Worksheets[worksheetnumber];
                        foreach (Excel.Range cell in workSheet.UsedRange)
                        {
                        }
}
foreach (Excel.Worksheet sheet in workbook.Sheets)
{
                    foreach (Excel.Shape shape in sheet.Shapes)
                    {
                          Console.WriteLine(shape.AlternativeText);
                    }
}
If you want to Paste some content from Clipboard:
Clipboard.SetText(html);
sheet.Range("A1").PasteSpecial();
Now, don't forget to close/quit everything you created.
workbook.SaveAs(<file destination>);
workbook.Close();
xlsApp.Quit();
Very important, if you plan to do modifications on an existing Excel file, don't work on the original one, make a copy of it and work with the copy:
File.Copy(TemplateFile,ExportedFile);
I hope, this might help you to overcome some future issues. ( I had those ).

For the regex part, I don't really understood what exactly you are looking for, but this might give you a hint.
string html = Clipboard.GetText(<your range>);
int start = html.IndexOf("<HEAD>");
int end = html.IndexOf("</TABLE>");
if (start != -1)
{
          html = html.Substring(start, ((end + 9) - start - 1));
          Regex reTH = new Regex(@"<TH", RegexOptions.IgnoreCase);
          Regex reTD = new Regex(@"<TD", RegexOptions.IgnoreCase);
          html = reTH.Replace(html, "<TH class='convertTableHeader' ");
           if (reTD.IsMatch(html))
            {
                         html = reTD.Replace(html, "<TD class='convertTableCell' ");
            }
          html = html.Replace("<TABLE ", "<TABLE class='convertTable'");
}
Give a feedback, if someone fits your issue.
Mar 27, 2013 at 11:06 AM
Many thanks Mike, and artnib too - that gets the whole project moving again. I can do the regex business to parse a $A$1:$B$3 style reference to extract the $A$4 that I then need to write to, it just seemed a longwinded way of navigating about the sheet. Nevertheless it will do, and I can get on now, thanks again
Justin