Tuesday, July 30, 2013

Office Interop - ComExcetion HRESULT: 0x800A03EC when large text values in formulas

Office Interop in .Net

Office interop cannot be avoided, if you are developing applications for Windows platform. It is 90% sure that your customers will ask for MS Office integration when the project grows or your marketing people will add office integration regardless the client actually needs or not.

Before Open XML era (docx,xlsx etc...) developers had only one way to interact with office files from .Net or any other applications. Yes the Office interop APIs. In .Net it can be done by Adding reference to the interop assemblies.Only issue I had faced is the non type safe APIs provided. Another issue is the memory leak due to unmanaged objects but that can be avoided by using Marshal.ReleaseComObject method.

If you are working in .Net for 2 years and don't know what is Office interop and how to use it to create an Excel file programatically, just google for it and try at least once. Let me know, if you are not able to get any result in google

Adding formula to Excel cells from C#.Net

Formula in Excel should be known to everyone who uses Windows operating system seriously. It can be added from the Excel application by prefixing a '=' symbol to mathematical or logical expressions when we edit cells. For example if you want to display the sum of values in cells E1 to E9 in E10 cell, write the below into E10 cell

=SUM(E1:E9)

The same can be done from our .Net application as well. Below is an code which adds little more complicated formulae to the cell


Application app = new Application();
Workbook wb= app.Workbooks.Open(excelFilePath);
Worksheet firstSheet = wb.Sheets[1] as  Worksheet;
string message = "There is a 'No' in the range B1-B10";
string formula = @"=IF(COUNTIF(B1:B10,""No"") >0,"""+message+@""","""")";
firstSheet.Cells[1, 1]= formula;//This is cell A1
wb.Save();
wb.Close();
Marshal.ReleaseComObject(app);


This means

  1. It will create an excel file in the path mentioned in the variable excelFilePath
  2. It will add a formula in the cell A1.
  3. The formula will be '=IF(COUNTIF(B1:B10,"No") > 0,"There is a No in the range B1-B10","")
  4. The formula means, if any cell in the range B1 to B10 contains "No" string it will show the message "There is a No in the range B1-B10" in the cell A1
This is simple right. But what about the below ie I want to set a big text to the A1 cell when there is a "No" in the range.

      

Application app = new Application();
Workbook wb= app.Workbooks.Open(excelFilePath);
Worksheet firstSheet = wb.Sheets[1] as  Worksheet;
 
string message = "Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur";

string formula = @"=IF(COUNTIF(B1:B10,""No"") >0,"""+message+@""","""")";
firstSheet.Cells[1, 1]= formula;
wb.Save();
wb.Close();
Marshal.ReleaseComObject(app);

This simply fails with a message

A first chance exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll
Additional information: Exception from HRESULT: 0x800A03EC

Any idea? It doesn't talk about what happened inside the code.Or at least what is the actual error?

This is the time to show the your practical debugging skills. Most of the developers complain here that, its just not working. But the intelligent developers think in a different way. Is it because of long message? Can't I assign long text via interop? or Excel itself just don't accept this much big message?

Most of them ends up in trying out the same scenario in Excel. Technically this is called as "reproducing the issue in a stripped down environment". Whoever try it in Excel will get the actual problem.

Text values in formulas are limited to 255 characters. To create text values longer than 255 characters in a formula, use the CONCATENATE function or the concatenation operator (&)

Yes there is a limitation in Excel and it itself giving the solution. So lets modify our code to have concatenation.

Application app = new Application();
Workbook wb = app.Workbooks.Open(excelFilePath);
Worksheet firstSheet = wb.Sheets[1] as Worksheet;

string message = "Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. ";
message+=@"""&"""+"Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur";
string formula = @"=IF(COUNTIF(B1:B10,""No"") >0,""" + message + @""","""")";
firstSheet.Cells[1, 1] = formula;
wb.Save();
wb.Close();
Marshal.ReleaseComObject(app);

Looks good. But in real scenario things will not be easy like this. Your application may be getting the message from some other sources which requires a splitting on the fly.

Happy debugging.

Monday, July 22, 2013

DSOFile in 64bit to deal with Office 2013 custom properties

DSOFile.dll is the way to edit Microsoft office document properties without the office installed. Microsoft claims that it is independent, but I had came across scenarios where it requires one more file installed in the machine which is msoshext.dll at the location.

C:\Program Files\Common Files\Microsoft Shared\OFFICE<version>\msoshext.dll
eg: C:\Program Files (x86)\Common Files\microsoft shared\OFFICE15\msoshext.dll

This post is about an issue which you may face, if you run your application which uses DSOFile.dll in 64 bit machines with 32bit Office 2013 installed. Mainly when you run 32 bit apps in 64 bit.The issue occurs when ever the supporting file msoshext.dll is not present for target version of the applications which uses the DSOFile.dll. Lets see the scenario in deep.

The below code snippet is something which adds a custom property to the word document. If you are not familiar about the custom properties in office document just open a word file in MS word, go to properties page and select the custom tab.


                OleDocumentPropertiesClass doc = new OleDocumentPropertiesClass();
                doc.Open("test.docx"falsedsoFileOpenOptions.dsoOptionOpenReadOnlyIfNoWriteAccess);
                object val = "joysvalue";
                doc.CustomProperties.Add("joyskey"ref val);
                doc.Save();


You may experience as it just stopped updating the custom properties in the above environment (64Bit machines which have 32bit office). ie if you look at the custom properties dialog after opening the docx file you cannot see it there.

But interesting thing is, if you again try to add the same custom property you will get an exception. I tried as much as I can to identify where the custom property is being stored but I can't find out. We can verify it as well by executing the below code


                OleDocumentPropertiesClass oleDocument = new OleDocumentPropertiesClass();
                oleDocument.Open("test.docx"falsedsoFileOpenOptions.dsoOptionOpenReadOnlyIfNoWriteAccess);
                foreach (CustomProperty property in oleDocument.CustomProperties)
                {
                    Console.WriteLine("Pro Name {0},Value{1}", property.Name, property.get_Value());
                }


Simply saying, the first code snippet added the custom property to the document. Its accessible, if we iterate the CustomProperties collection but cannot visible in the office application's custom properties page.

Alternative

When you put this question in developer forums you will get an advise to go with open xml sdk. This is the solution to the problem but the drawback is it will work only with the open xml office documents such as docx,pptx,xlsx etc...The old office documents such as .doc, .ppt, .xls etc... are not at all supported by open xml sdk.So if your application needs to deal with both the formats using same code base you need to find the solution to the DSOFile issues in 64 bit. 

Solution

Install all the latest possible office 2013 updates and make sure you have the msoshext.dll in the right location. If your application is 32 bit you need to have the file in the c:\program files (x86)\ folder. Also make sure that the DSOFile.dll is registered properly for 32 bit (Using C:\Windows\SysWOW64\regsvr32.exe) and 64 bit in the 64 bit environment.

The above updating is applicable to all versions of office, if its installed in 64 bit machines.

Tuesday, July 16, 2013

Recording VSTS webtest of windows application which uses https web services

Whenever we develop a web site or web application, it is mandatory that we should do load testing before production. It will give us an idea about how many users and requests it can handle per second in a given server environment. In theory its easy to say we should do the load testing. But in practice, very less people knows, how it is being worked out and what are the software tools used to do load testing.

One of the load testing tool is Visual Studio itself ! But before going into load testing in Visual studio, let us see what a load testing software in general should provide and how a load testing is carried out.

Features of load testing software

  • Recording
    • Ability to record the different use cases of the application. Mainly uses script for storing purpose.
    • Ability to add parameters / tokens into the recorded use cases / script for handling virtual users and passing other runtime values.
  • Play back
    • Ability to run the recorded use cases / scripts by replacing the tokens with run time values
    • Ability to run simultaneously from multiple machines or agents.
    • Controller agent model to collect data from the nodes which are executing the scripts.
    • Ability to create virtual users in the controller agent environment.

Steps to do load testing

  1. Record the use cases in a format which can be playable
  2. Adding parameters (optional)
    1. Add tokens or place holders to the recorded script so that it can be replaced by actual values at run time
    2. Write the token / parameter replacement logic.
  3. Define web load test cases. eg: 25 virtual users
  4. Setup the agents.
  5. Run the recorded load test cases and collect the results.

Web performance load testing using Visual Studio

This feature is available in high end versions (Ultimate) of Visual Studio. If your Visual Studio supports load testing, you can see the webtest template and load test template. If your application is a web site its very easy to record the scripts. This is because the Visual Studio has an in built mechanism to record the http(s) traffic from internet explorer. If your internet explorer is in record mode, you can easily perform use cases and record the action. But if you are developing a windows application which uses http(s), it is little difficult to record because Visual Studio cannot intercept your windows desktop application to capture the http(s) traffic. So what to do? In fact that is the aim of this post.

Recording visual studio web test in desktop world

There is a really really useful tool called fiddler. It must be familiar to the web developers but not for windows developers. This tool can act as proxy which sits between any application and the network to capture all the http(s) requests and responses. Now a days most of its duties can be done by the F12 browser tools. But in the desktop environment, fiddler still has importance. Below is the sequence to create web test of windows applications using fiddler.
  1. Run Fiddler
    1. Setup support for https if required
  2. Run the application
  3. Execute a use case
  4. Goto fiddler and select File -> Export Sessions -> All Sessions
  5. Select Visual Studio WebTest
  6. Add the exported web test to Visual Studio project.
  7. Add tokens / parameters to the web test.
  8. Clear the fiddler session
  9. Repeat the steps 3-8 for all the use cases in your application which you want to load test.

Tuesday, July 9, 2013

Error - Azure 2.0 installation via Web Installer - You have a newer version of NuGet installed that's incompatible with ASP.NET MVC 3 Tools Update

When

During installing Windows Azure SDK and Tools 2.0 for VS 2010 through Web Platform Installer 4.5. Also during installing some more products via Web Platform Installer.

Full Error

"You have a newer version of NuGet installed that's incompatible with ASP.NET MVC 3 Tools Update. To continue installing this product, please refer to http://go.microsoft.com/fwlink/?LinkId=266638."

If we click on the link it will redirect to ASP.Net/MVC site. No idea why there is a problem with nuget version and ASP.Net MVC 3 when installing Azure

Machine configuration / Environment

Windows 7 64bit
Visual Studio 2010 with Nuget installed via VS Extension manager.
Microsoft Web Platform Installer 4.5

Microsoft Status 

Resolved & Closed. There is a work around mentioned in MSFT connect which instructs to uninstall the nuget and install again.

My status

Some people confirmed that they are able to. But still it exists in my environment, even if I uninstall Nuget and try using Web Platform installer.

My resolution / workaround

Without using, Web platform installer directly install by downloading full installer from here.This is applicable to all the products which shows error in Web Platform Installer.

Monday, July 1, 2013

What,Why & How to resolve OutOfMemoryException in .Net

This is a normal exception happens in most of the .net applications. When ever developers see this they start thinking about the RAM of the system and divert their thoughts towards physical memory. But before going to that area there are some other things to be noted. 

What is OutOfMemoryException

  • An Exception in .Net which will be fired when the .net runtime is not able to allocate memory for object
  • The MSIL instructions such as newobj,newarr and box may throw this exception

Why OutOfMemoryException is occuring

  • Its not actually because there is not enough memory in the machine.(Memory shown in Task manager) 
  • Its because there is not enough continuous free memory in the process's address space to do memory allocation for objects

How to resolve

  • Facts before you try to solve this issue.
  • Steps for quick fix.
    • Convert the application to 64bit. This will remove the 2GB memory limit on process and largely increase the process address space so that there will be lesser fragmentation which provides more continuous free memory space.
  • Steps for actual fix
    • Use tools such WinDbg, .Net memory profiler etc... to find out the size of objects in the process memory. In most cases this will be very less than the memory shown in task manager.You may also use performance monitor counters to do the same.
    • If the size of objects and task manager reading has less difference ,it means you actually uses so many objects or the object you created are not deallocated properly. Check for memory leaks and fix it.
    • Check for LOH (Large Object Heap) fragmentation and fix it.
    • If you really want to work with huge objects use MemoryFailPoint class
Reference
http://blogs.msdn.com/b/tess/archive/tags/memory+issues/
http://msdn.microsoft.com/en-us/magazine/cc163528.aspx
http://stackoverflow.com/questions/4767651/what-is-the-cause-of-outofmemoryexception-in-net-on-the-windows-service-under-a