Question on Class Type (Sealed, Public , Virtual)

While desgingn the application we normally create classes. Here is a very interesting question:

What’s your opinion on whether to choose “sealed” classes as default, or to leave everything open (public or virtual)?

Share your ideas/thoughts…

life Saver of a Developer: Troubleshooting Tools

Its been a long time since I have posted anything here. Don’t think I am not concerned about it,it’s just that I am really busy with SharePoint 2010..Learning new things a lot..So here comes a new topic to my mind. Actually I really wanted to write a blog on this a long time back. But as they say, it’s never too late

So what is this blog all about. Day in day out we deal with different development situations and we always get help with some tools or they help us in RAPID. So what I would like to do: Is to prepare a list of all the tools and put it in one place. In case if you  wish to help the community and want to put a link to a good troubleshooting tool,  please leave the link here with a small note and I will add to the list.

The list is going to grow..so please make sure you add this to Favourites….

Here is how it should be :

Process Explorer
http://technet.microsoft.com/en-us/sysinternals/bb896653

Ever wondered which program has a particular file or directory open? Now you can find out. Process Explorer shows you information about which handles and DLLs processes have opened or loaded

Easy one huh?? But what this tool can also do, to help you know about the Integrity level of a process.  Have a look at this: http://en.wikipedia.org/wiki/Mandatory_Integrity_Control

Named objects, including files, registry keys or even other processes and threads, have an entry in the ACL governing access to them, that defines the minimum integrity level of the process that can use the object. Windows makes sure that a process can write to or delete an object only when its integrity level is equal to or higher than the requested integrity level specified by the object.[2] Additionally, process objects with higher IL are out-of-bounds for even read access.

Consequently, a process cannot interact with another process that has a higher IL. So a process cannot perform functions such as inject a DLL into a higher IL process by using the CreateRemoteThread()API function or send data to a different process by using the WriteProcessMemory() function. However, the higher IL process can execute such functions against the lower IL process.[1] However, they can still communicate by using files, Named pipes, LPC or other shared objects. The shared object must have an integrity level as low as the low IL process and should be shared by both the Low-IL and High-IL process.

Content Types, Features and Events

Recently I worked on a case where I had to deploy the XML based site content type through feature and I noticed a strange behavior which makes sense once you know the complete picture. The complete exercise was a very good learning so I thought I will post a blog (or may be a series) on this.

Requirement
=============
Create a site content type based on XML and have an event receiver attached to it.
Use the content type in a list
Modify the Event handler (add some more events)

Actual Results
============
The newly added event will not fire

Expected Result
==============
New events should fire. Even though you update the content type but this doesn’t work. why? Lets just start the learning and the final conclusion process.

Step # 1 –> Create the Site Content Type
============
I started looking out for documentation on this topic and there was not much documentation available> Even if I look into wss.xsd, there was not much help available. So I started building a smaple content type.

So, we need some fields ..lets define fields in an XML file, fields.xml. Create a new XML file in VS and then attach the WSS.XSD and then start typing it.  Please have a look at the sample XML file I have created:

<?xml version="1.0" encoding="utf-8" ?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">

<Field Type=”Note”
DisplayName=”Notes”
Required=”FALSE”
MaxLength=”50″
Group=”Sample Columns”
ID=”{165B2AA9-DC9C-4ca5-A004-0CDD19042F20}”
SourceID=”http://schemas.microsoft.com/sharepoint/v3″
StaticName=”SampleNotes”
Name=”SampleNotes”
RichText=”TRUE”
IsolateStyles=”TRUE”
Sortable=”FALSE”
NumLines=”4″
Hidden=”FALSE”
ReadOnly=”FALSE” />

<Field Type=”URL”
DisplayName=”How To Link”
Group=”Sample Columns”
Required=”FALSE”
Format=”Hyperlink”
ID=”{57632474-85EA-4255-ABE5-A1E6B545766B}”
SourceID=”http://schemas.microsoft.com/sharepoint/v3″
StaticName=”SampleHowToLink”
Name=”SampleHowToLink”/>

<Field Type=”Choice”
DisplayName=”Status”
Required=”FALSE”
Group=”Task Management Columns”
ID=”{10333110-A154-4321-A04D-6B1D9654DEB8}”
SourceID=”http://schemas.microsoft.com/sharepoint/v3″
StaticName=”SampleStatus”
Name=”SampleStatus”
Format=”Dropdown”
FillInChoice=”FALSE”>

<CHOICES>
<CHOICE>Active</CHOICE>
<CHOICE>Completed</CHOICE>
</CHOICES>
<Default>Active</Default>
</Field>

</Elements>

 

Once, it is done, lets create the content type which will use these fields (name the file, ContentType.xml):

<?xml version="1.0" encoding="utf-8" ?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <ContentType ID="0x012000ACD1F08FAE0E4478960D38AEFE7769C4"
                   Name="Sample Content"
                   Group="Sample Content Management"
                   Version="0"
                   Sealed="FALSE"
                   Description="Contains Sample Field.">
    <FieldRefs>
      <FieldRef ID="{165B2AA9-DC9C-4ca5-A004-0CDD19042F20}" Name="SampleNotes" Required="TRUE" ShowInNewForm="FALSE"/>
      <FieldRef ID="{57632474-85EA-4255-ABE5-A1E6B545766B}" Name="SampleHowToLink" Required="TRUE" ShowInEditForm="FALSE"/>
      <FieldRef ID="{10333110-A154-4321-A04D-6B1D9654DEB8}" Name="SampleStatus" Required="FALSE" ShowInEditForm="FALSE"/>

</FieldRefs>

</ContentType>
</Elements>

 

You can easily get help on the attributes declared here doing a web search or in SDK so I am not going in detail explaining about them.

Now all we need is a feature.xml which will use the above 2 files.

<?xml version="1.0" encoding="utf-8" ?>
<Feature xmlns="http://schemas.microsoft.com/sharepoint/" Id="32F1588E-138B-4abd-ABA2-47A585A61DB3" Scope="Site" Title="Sample Feature">
  <ElementManifests>
    <ElementManifest Location="Fields.xml"/>
    <ElementManifest Location="ContentType.xml"/>
  </ElementManifests>
</Feature>

 

Now we have everything in place, lets try to activate it.

Go to C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\FEATURES and create a folder, SampleContentType and copy Fields.xml, ContentType.xml, feature.xml into this folder.

Open command prompt and browse to C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN. (If the environment variable is not set for SharePoint). Run the following command
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>STSADM.EXE -o installfeature -name SampleContentType

Go to Site collection Features and check if the “Sample Feature” is available. If yes, activate it and use it in a list and see how it appears.

Well this is all simple huh..now in the next exercise we will add the event receivers and then try to update them.

How to Delete a Column in Excel using Open XML SDK

Recently there was a question posted on my blog as how we can delete a column in spreadsheet (Excel) using Open XML SDK. Actually, it’s a very tricky requirement because you need to update so much of information i.e. changing the cell index. So it’s really depends how you want to achieve it. You may want to write a COM Addin/VSTO addin to do the job but then you need to deploy the add-in at client side. So, if you are looking for a server side solution, here is the approach. You may want to optimize it according to your need but I will explain in detail what/why I did.

So, let’s start with the concept. Let’s say have created a sample Excel workbook like this:

If you look the XML representation of the data, it will be like this

<x:sheetData xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">

<x:row r="1" spans="1:3" x14ac:dyDescent="0.25" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
<x:c r="A1">
<x:v>1</x:v>
</x:c>
<x:c r="B1">
<x:v>2</x:v>
</x:c>
<x:c r="C1">
<x:v>3</x:v>
</x:c>
</x:row>

<x:row r="2" spans="1:3" x14ac:dyDescent="0.25" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
<x:c r="A2">
<x:v>1</x:v>
</x:c>
<x:c r="B2">
<x:v>2</x:v>
</x:c>
<x:c r="C2">
<x:v>3</x:v>
</x:c>
</x:row>
</x:sheetData>

So pay attention to how rows and cells are declared. So the declaration is:

Row1
Cell1
Cell2
Cell3

Row2
Cell1
Cell2
Cell3

So for ex: if you delete cell2 from all the rows, you need to update the rowIndex (x:r) in the remaining cells. Let’s say you delete column B , the updated XML should look like this:

<x:row r="1" spans="1:3" x14ac:dyDescent="0.25" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
<x:c r="A1">
<x:v>1</x:v>
</x:c>
<x:c r="B1">
<x:v>3</x:v>
</x:c>
</x:row>

<x:row r="2" spans="1:3" x14ac:dyDescent="0.25" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
<x:c r="A2">
<x:v>1</x:v>
</x:c>
<x:c r="B2">
<x:v>3</x:v>
</x:c>
</x:row>

And here is the C# code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using DocumentFormat.OpenXml;

using DocumentFormat.OpenXml.Packaging;

using DocumentFormat.OpenXml.Spreadsheet;

using System.Xml.Linq;

namespace DeleteSpreadSheetColumn

{

    class Program

    {

        static XDocument columnsXL;

        static void Main(string[] args)

        {

            LoadColumnName("");

            string result = DeleteAColumn(@"C:\Users\abhatia\Desktop\Test1.xlsx", "Sheet1", "I");

        }

        public static void LoadColumnName(string XMLFile)

        {

            columnsXL = XDocument.Load(@"C:\Users\abhatia\Desktop\ExcelColumn.xml");

        }

        // Given a document, a worksheet name, a column name

        // deletes a column from a specified worksheet.

        public static string DeleteAColumn(string docName, string sheetName, string colName)

        {

            // Open the document for editing.

            using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))

            {

                IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName);

                if (sheets.Count() == 0)

                {

                    // The specified worksheet does not exist.

                    return sheetName + "doesn't exist";

                }

                string relationshipId = sheets.First().Id.Value;

                WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);

                // Get the Total Rows

                IEnumerable<Row> rows = worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>();

                if (rows.Count() == 0)

                {

                    return "Rows doesn't exist";

                }

                // Loop through the rows and adjust Cell Index

                foreach (Row row in rows)

                {

                    int index = (int)row.RowIndex.Value;

                    IEnumerable<Cell> cells = row.Elements<Cell>();

                    IEnumerable<Cell> cellToDelete = cells.Where(c => string.Compare(c.CellReference.Value, colName + index, true) == 0);

                    if (cellToDelete.Count() > 0)

                        cellToDelete.First().Remove();

                    int intColumnToBeDel = (int)(from c in columnsXL.Descendants("col") where c.Attribute("name").Value == colName select c.NodesBeforeSelf().Count()).FirstOrDefault() + 1;

                    foreach (Cell cell in cells)

                    {

                        string col = GetColunName(cell.CellReference.Value);

                        int intColumnPres = (int)(from c in columnsXL.Descendants("col") where c.Attribute("name").Value == col select c.NodesBeforeSelf().Count()).FirstOrDefault() + 1;

                        if (intColumnPres > intColumnToBeDel)

                        {

                            string ColNamefromXML = ((System.Xml.Linq.XElement)(from c in columnsXL.Descendants("col")

                                                                                where c.Attribute("name").Value + index == cell.CellReference.Value

                                                                                select c.PreviousNode).FirstOrDefault()).FirstAttribute.Value;

                            cell.CellReference.Value = ColNamefromXML + index;

                        }

                    }

                }

                worksheetPart.Worksheet.Save();

                return "Deleted";

            }

        }

        public static bool isNumeric(string val, System.Globalization.NumberStyles NumberStyle)

        {

            Double result;

            return Double.TryParse(val, NumberStyle,

                System.Globalization.CultureInfo.CurrentCulture, out result);

        }

        private static string GetColunName(string p)

        {

            Boolean breakLoop = true;

            while (breakLoop)

            {

                if (isNumeric(p.Last().ToString(), System.Globalization.NumberStyles.Integer))

                    p = p.Substring(0, p.Length - 1);

                else

                    breakLoop = false;

            }

            return p;

        }

    }

}

 

Please note that ExcelColumn.xml is just an XML file which contains all the column name. I find it an easy way to get the column name. You can download the complete project and XML file from http://cid-956eb159f7975780.skydrive.live.com/redir.aspx?resid=956EB159F7975780!197

Feel free to optimize it according to your need and let me know if you found any problem with this one!!!

Purging/Deleting a Huge/Large SharePoint List

Recently I worked on a case where customer was trying to delete a huge list (it could be a simple list or workflow history list) and was looking for a custom solution to do it.

In case if it is a workflow history list then you can create a new list and set this as a history list and then you can try to delete the list items.

I started working on this and found a very good sample is already available on the web:

http://code.msdn.microsoft.com/CleanWorkflowHistory

But if you look at the code, it gets the ListItem and then it reads the List Item id so that it can create a batch processing to delete the item.

Everything is fine but still the process was slow…I reserached on this and suggested few points which I wanted to highlight here:

1. When you delete the List Items from List, it goes to first recycle bin and then it goes to the second one. So you may want to turn this off when deleting the items. Sometimes it is not practle to delete the items..so think about the overhead.. ..delete the item from a list, then 1st recycle bin and then 2nd recycle bin.

2. Delete Items in batch. instead of deleting them as a big list, delete them in chunk. (for ex: start with 2000 items)

3. This is something not recommended but can be tried in one-off case. Basically you can read List Item ID from the database instead of reading it using Object Model.

=========

============
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SharePoint;
using System.Data.SqlClient;

namespace PurgeList
{
class Program
{
private static int iteration = 5;
private static int Count = 2000;

static void Main(string[] args)
{
if (args.Length != 1)
{
Console.WriteLine(“first argument: No of iteration”);
Console.WriteLine(“YourprgramName.exe 5″);
Console.WriteLine(“******Program terminated : Argument missing! Please give one argument ****”);

}
else
{
try
{
iteration = Convert.ToInt32(args[0]);

}
catch (Exception ex)
{
Console.WriteLine(“Error : Failed to convert arguments: {0}”, ex.Message);

return; // exit
}
Console.WriteLine(“Current System Time (Start) : ” + DateTime.Now.ToString());
try
{

for (int iterate = 0; iterate < iteration; iterate++)
{
int start = GetMinID();
Console.WriteLine(“Start Index: ” + start.ToString());
using (SPSite site = new SPSite(“http://anksps2010“))
{
using (SPWeb web = site.OpenWeb())
{
SPList wflist = web.Lists["Workflow History"]; //workflow history
string wflistID = wflist.ID.ToString();

Console.WriteLine(“No of items before deletion: ” + wflist.ItemCount);

Console.WriteLine(“Building query… “);
StringBuilder batchString = new StringBuilder();
batchString.Append(“xml version=\”1.0\” encoding=\”UTF-8\”?>”);
int end = start + Count – 1;
for (int i = start; i <= end; i++)
{
batchString.Append(“<Method>”);
batchString.Append(“<SetList Scope=\”Request\”>” + wflistID + “”);
batchString.Append(“<SetVar Name=\”ID\”>” + Convert.ToString(i) + “”);
batchString.Append(“<SetVar Name=\”Cmd\”>Delete”);
batchString.Append(“</Method>”);

}

batchString.Append(“</Batch>”);

//Console.WriteLine(batchString.ToString());

try
{
web.AllowUnsafeUpdates = true;
Console.WriteLine(“Executing query… “);
Console.WriteLine(“Batch Execution (Start) : ” + DateTime.Now.ToString());
string result = web.ProcessBatchData(batchString.ToString());
//Console.WriteLine(result);
web.AllowUnsafeUpdates = false;

Console.WriteLine(“Batch Execution (END) : ” + DateTime.Now.ToString());

}
catch (Exception ex)
{
Console.WriteLine(“Process batch error : ” + ex.Message);

}
Console.WriteLine(“No of items before deletion: ” + wflist.ItemCount);
using (SPSite site1 = new SPSite(“http://anksps2010“))
{
using (SPWeb web1 = site1.OpenWeb())
{
Console.WriteLine(“No of item after deletion: ” + web1.Lists["Workflow History"].ItemCount);
}
}

Console.WriteLine(“————————————————–”);

}
}
}
Console.WriteLine(“Current System Time (End) : ” + DateTime.Now.ToString());
Console.WriteLine(“———–Program Completed————”);
}
catch (Exception ex)
{
Console.WriteLine(“Error : ” + ex.Message);
Console.WriteLine(“******Program terminated due to error **** “);

}
}

}

public static int GetMinID()
{

try
{
string connString = @”Data Source=.\SHAREPOINT;Initial Catalog=WSS_Content_c20feb22657e4e2ab82f7db433f3e4c7;Integrated Security=SSPI”;

using (SqlConnection objConn = new SqlConnection(connString))
{
objConn.Open();

string sqlString = “Select min(tp_ID) as col1 from dbo.alluserdata where tp_listid = ’6463BECE-3560-4D15-B965-B245F3203BEE’”;// workflow list id

SqlCommand cmd = new SqlCommand(sqlString, objConn);
int id = Convert.ToInt32(cmd.ExecuteScalar());
return id;

}
}
catch (Exception ex)
{
Console.WriteLine(“SQL ERROR: ” + ex.Message);
throw ex;

}
}

}
}

==================================

Try this and let me know how it goes!!!!!

Update # 1:

While working on the sample available here (http://code.msdn.microsoft.com/CleanWorkflowHistory), I hit a bug (or should I say a couple of bugs).

Bug # 1 urgeSiteRecycleBin function again picks the item from the 1st stage (end-user) recycle bin. You need to specify ItemState to get the items from the second stage recycle bin. So currently, It deletes the item twice from the 1st stage recycle bin.

bug # 2: There is no need of PurgeSiteRecycleBin function at all. Basically, when you delete the item using batch processing, it goes into 1st stage re-cycle bin. But when you delete the item from re-cycle bin, it updates the content database so it will land in 2nd stage re-cycle bin. So unless, you want to empty the 2nd stage recycle bin,you can safely remove the code.

 

SharePoint Object Model Development and 64bit

Recently i worked on a case where customer was using SharePoint object model on a 64bit machine in an ASP.NET application and it was failing. The code was failing when I tried to create SPSite object

Here is the error message:

================

The Web application at http://ank2007:45366 could not be found. Verify that you have typed the URL correctly. If the URL should be serving existing content, the system administrator may need to add a new request URL mapping to the intended application

================

I could browse to this site without any problem and SharePoint is installed on the same box. I tried setting the target platform to x64,“Any CPU” , x86 but it didn’t help. I can very well create a webpart and can use the same code without any problem. So here is the environment details:

• Your machine is 64 bit
• MOSS 2007 is 64 bit
• VS is 32 bit

So I started treoubleshooting the problem:

1. First I checked where is the server hosted (You can’t connect via OM to another SP instance on a different server.)
2. Ran the application as an administrator
3. I noticed that when I add reference for SharePOint.dll in Asp.Net web application, it reports an error about Search.dll.
4. As I was not using any serach part so I deleted the code using a post script

cd $(TargetDir)
del Microsoft.SharePoint.Search.dll
del Microsoft.SharePoint.Search.xml

After this I tried a lots of stuff – ProcMon, Fusion to see if there is any mismatch. But finally I got the solution

Basically when you try to debug the application, it try to load the 64bit MOSS dlls in 32 bit space (as VS is 32bit) and it fails with all the errors.  So this is what I did to make the deployed application to run in 64 bit:

Used http://support.microsoft.com/kb/894435 and tried this

=========
ASP.NET 2.0, 64-bit version

To run the 64-bit version of ASP.NET 2.0, follow these steps:

1. Click Start, click Run, type cmd, and then click OK.
2. Type the following command to disable the 32-bit mode:

   cscript %SYSTEMDRIVE%\inetpub\adminscripts\adsutil.vbs SET W3SVC/AppPools/Enable32bitAppOnWin64 0

3. Type the following command to install the version of ASP.NET 2.0 and to install the script maps at the IIS root and under:

   %SYSTEMROOT%\Microsoft.NET\Framework64\v2.0.50727\aspnet_regiis.exe -i

4. Make sure that the status of ASP.NET version 2.0.50727 is set to Allowed in the Web service extension list in Internet Information Services Manager.

===========

After this, I checked the app pool identity and matched it with the SharePoint ‘s app pool identity and it worked after that.

And it worked like a charm

 

 

Question # 9 : You want to cache objects (say, from a database) but still allow them to be garbage collected when necessary

Here comes another question after a long wait

any possiable solution?

libOPC version 0.0.1 is released

Source: http://blogs.msdn.com/b/dmahugh/archive/2011/04/19/libopc-version-0-0-1-released.aspx

Text taken from above article:

The first release of libOPC, a new API for Open XML development, was published on Codeplex last week. This API is the first open-source cross-platform API for developers working with Open Packaging Convention (OPC) packages as used by Open XML, XPS, and other formats. Full source code is available, and it’s written in portable C99, so can be used on all popular variants of Linux/Unix, Mac OS, Windows, Android, and many other platforms. The API uses other common cross-platform open-source APIs for some of the low-level details, including ZLIB for opening ZIP-compressed packages and libXMLfor parsing the XML streams from the parts in the package.

Historically, there have been two popular .NET APIs for Open XML development: System.IO.Packaging (which first appeared in .NET 3.0) and the Open XML SDK, released in early 2007. There’s also a COM-based native packaging API available for non-.NET Windows developers.

The libOPC API is roughly analogous to System.IO.Packaging, in that it’s focused on the details of OPC and MCE (parts 2 and 3 of IS 29500), but doesn’t provide higher-level abstractions for WordprocessingML, SpreadsheetML or PresentationML (as covered in parts 1 and 4 of IS 29500).  I say “roughly” because libOPC doesn’t yet address some of the things that System.IO.Packaging handles (e.g., digital signatures) but does include some more advanced capabilities not available in System.IO.Packaging, such as the opc_generate functionality described below, which is essentially the same as the document reflector functionality of the Open XML SDK.

The key new feature in libOPC is its cross-platform capabilities. If you’re working on a non-Microsoft platform, or working with embedded systems that have limited OS support for XML and ZIP, you now have a very fast, simple API that you can use to implement Open XML read and write capabilities in your applications. And libOPC is designed from the ground up to be wrapper-friendly, for use from programming languages other than C.

For full read, click on the above link!!

Debug a SharePoint Workflow

Its been a while since I blogged anything. Main reason was, I was on vacation and since then I have come back, I was really really busy.  It was really good to visit places like Mathura and Muzaffarnagar (my birthplace). Anyway, here I am with my first blog after my vacation.

Recently I worked on case where customer has developed a workflow to send multiple emails (using SMTP Server) when an item was added to the list. The workflow was getting fired but it sending emails only for the first list item. For the second list item, it was kind of stuck(because there was no email sent for the second list item and so forth).

I started with very basic troubleshooting steps, like checking ULS logs, Event Viewer and nothing major was there.  I wish I could have debugged the workflow but it was not possible to install Visual Studio on the problematic server. I wish I could have got workflow debug information and then I got one. As we know it is very painful to debug the workflow. So the easy thing is, open
the web.config and have these entries present

<system.diagnostics>
<switches>
<add name=”System.Workflow LogToFile” value=”1″ />
<add name=”System.Workflow.Runtime” value=”All” />
<add name=”System.Workflow.Runtime.Hosting” value=”All” />
<add name=”System.Workflow.Runtime.Tracking” value=”All” />
<add name=”System.Workflow.Activities” value=”All” />
<add name=”System.Workflow.Activities.Rules” value=”All” />
</switches>
</system.diagnostics>
</configuration>

This will create a log file under C:\WINNT\system32\inetsrv.

Once you have the log file, you can view what activates being executed and if there was any error. And this really helped.  I was able to pin point one activity where the second workflow instance was stuck and it turns out that customer was keeping a constant task id. We changed this to create an new one for each workflow instance and it worked.

Try this in your workflow projects and see if this helps!! And if it does, post a comment here.!!!

How to Insert a Hyperlink in Excel using Open XML SDK 2.0

Consider a scenario, you want to insert a hyperlink in Excel 2010 (or Excel 2007) file programmatically using a web application. You should not use Office Object Model in a web application, Please refer http://support.microsoft.com/kb/257757. So what we can do??

As we know that Office 2007 and 2010 file format is XML based (just rename any Office 2007/2010 document to .zip and you can see a lots of XML files which makes a document and they are simply zipped in a one file). To manipulate these files, MS has introduced a library, Open XML SDK 2.0. The SDK is built on the System.IO.Packaging API and provides strongly-typed classes to manipulate documents that adhere to the Office Open XML File Formats Specification. The Office Open XML File Formats specification is an open, international, ECMA-376, Second Edition and ISO/IEC 29500 standard. The Open XML file formats are useful for developers because they are an open standard and are based on well-known technologies: ZIP and XML.

The Open XML SDK 2.0 simplifies the task of manipulating Open XML packages and the underlying Open XML schema elements within a package. The Open XML SDK 2.0 encapsulates many common tasks that developers perform on Open XML packages, so that you can perform complex operations with just a few lines of code. Some benefits if you use Open XML SDK:

Open Format: These formats use XML and ZIP, and they are fully documented. Anyone can get the full specs on the formats and it is a royalty free license for anyone that wants to work with the files.

Compressed: Files saved in these new XML formats are less than 50% the size of the equivalent file saved in the binary formats. This is because we take all of the XML parts that make up any given file, and then we ZIP them. We chose ZIP because it’s already widely in use today and we wanted these files to be easy to work with.

Robust: Between the usage of XML, ZIP, and good documentation the files get a lot more robust. By compartmentalizing our files into multiple parts within the ZIP, it becomes a lot less likely that an entire file will be corrupted (instead of just individual parts). The files are also a lot easier to work with, so it’s less likely that people working on the files outside of Office will cause corruptions.

Backward compatible: You don’t have to use the new version of Office to take advantage of these formats. You can use Office Compatibility pack to open the Office 2007 document in older version.

Binary Format support: You can still use the current binary formats with the new version of Office. In fact, people can easily change to use the binary formats as the default if that’s what they’d rather do.

New Extensions: The new formats has new extensions (.docx, .pptx, .xlsx) so you can tell what format the files you are dealing with are, but to the average end user they’ll still just behave like any other Office file. Double click & it opens in the right application.

Is also important to know that what SDK CAN’T do:

The Open XML SDK 2.0:

• Does not replace the Microsoft Office Object Model and provides no abstraction on top of the file formats. You must still understand the structure of the file formats to use the Open XML SDK 2.0.
• Does not provide functionality to convert Open XML formats to and from other formats, such as HTML or XPS.
• Does not guarantee document validity of Open XML Formats when you use the Open XML SDK 2.0 or if you decide to manipulate the underlying XML directly.
• Does not provide application behavior such as layout functionality in Word or recalculation, data refresh, or adjustment functionalities in Excel.

So lets have a look at the solution. I will try to build the solution from the scratch so that you can learn how you can use Open XML tools and leverage this information in other exercise as well.

So lets start!!

Step 1: Download the Open XML SDK and the tool from http://www.microsoft.com/downloads/en/details.aspx?FamilyId=C6E744E5-36E9-45F5-8D8C-331DF206E0D0&displaylang=en. Please note that there are 2 links, one is for the tool and the second one is the actual library.

Step 2: So the objective is to insert a link into a cell. For the simplicity, we will assume that we want to insert the link into cell A1 of Sheet1. Lets create an Excel 2010 workbook and place a hyperlink in cell A1 manually. Once done, Excel file will look like this

Save this file and close it.

Step 3: If you haven’t changed the default installation location of the tool, they will appear here: C:\Program Files (x86)\Open XML SDK\V2.0\tool. Go ahead and run OpenXmlSdkTool.exe from here.

Step 4: You can simply open Office file in this tool and look at the different XML parts. (To understand different XML parts, have a look:http://ankushbhatia.wordpress.com/2010/03/04/basics-of-spreadsheet/). So go ahead and open the file in this tool. Once done, it will look like this:

Step 5: We know that we inserted the hyperlink in cell A1 of Sheet1 so go ahead and browse the Sheet1.xml file. Once you select the Sheet1, it gives you different child elements of it. Please select hyperlinks and click on “reflect Code” to view the XML. Make sure to follow the correct tree structure to get to Sheet1.xml and hyperlink object. After this, it will appear as:

Pay attention to the right side. There are 2 windows. Upper window shows XML of the section you have selected in the “Document Explorer” and the bottom window shows the C# code to generate the XML..Kind of cool….isn’t?? So now what we have..? We have XML which is required to generate a hyperlink and the equivalent C# code. Lets have a closure look at the XML

<x:hyperlinks xmlns:x=”http://schemas.openxmlformats.org/spreadsheetml/2006/main”>

<x:hyperlink ref=”A1″ r:id=”rId1″ xmlns:r=”http://schemas.openxmlformats.org/officeDocument/2006/relationships” />

</x:hyperlinks>

There is no information about the hyperlink text. If you look at the relationship ID “A1″, it appears as

<?xml version=”1.0″ encoding=”UTF-8″ standalone=”true”?>

<Relationships xmlns=”http://schemas.openxmlformats.org/package/2006/relationships“><Relationship TargetMode=”External” Target=”http://www.microsoft.com/” Type=”http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink” Id=”rId1″/></Relationships>

So where is the actual text (remember I entered Microsoft)?

As you know that Excel keeps all the text strings in a separate XML file, SharedSTring.xml and refer it back based on the index. So lets see how the data appear in cell A1

Have a look at the SheetData [where it keeps all the data . It is specific to a sheet] in the tool

If you look at the XML, it contains the information about the row which contains the data.

<x:sheetData xmlns:x=”http://schemas.openxmlformats.org/spreadsheetml/2006/main”>

<x:row r=”1″ spans=”1:1″ x14ac:dyDescent=”0.25″ xmlns:x14ac=”http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac”>

<x:c r=”A1″ s=”1″ t=”s”>

<x:v>0</x:v>

</x:c>

</x:row>

</x:sheetData>

Lets try to decode it.

<x:row r=”1″ spans=”1:1″ x14ac:dyDescent=”0.25″ xmlns:x14ac=”http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac”> :

r=1 means there is one row.

There is one child element <x:c r=”A1″ s=”1″ t=”s”>.

X:c -> Column
r=A1 -> Referenced cell is “A1″

S=1 and t=”S” it’s a string value and value is referenced from Shared String table. And S=1 means that a style is applied and the defination of the style is taken from index 1 which can be found from Style.xml

Now analyze the SharedSTring.XML using the same tool

Here you will see the Microsoft string. It is referred back in SheetData using x:v node. The starting index is 0.

So to summarize, we need to do:

1. Create a hyperlink object and insert it before the PageMargin node. Please note that Excel is enforcing a strict ordering of the elements. So make sure to add it before PageMargines node.
2. Create an entry in the SharedString Table
3. Create an entry in the SheetData section

So why we did this exercise. Every now and then we face situation where we want to these kind of things in Office using Open XML. So easy thing is, do the stuff in UI and analyze the XML and C# code and then write your own.

Lets move on and utilize this information.

Step 6: Create a new Excel file and save it. We will insert a hyperlink in this file. Create a new VS 2010 project and open the new blank file.

Step 7: Add a reference to DocumentFormat.OpenXML and WindowsBase.dll from .NET tab

Step 8: Add a namespace using XL = DocumentFormat.OpenXml.Spreadsheet;

Step 9: So basically you need to

a. Open the workbook
b. Get to the sheet where you want to add the hyperlink
c. Add the hyperlink
d. Add an entry in SheetData
e. Add an entry in SharedString.xml

The below code does exactly that.

using (SpreadsheetDocument document = SpreadsheetDocument.Open(@"C:\Users\abhatia\Desktop\Sample1.xlsx", true))

           {

               IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == "Sheet1");

               if (sheets.Count() == 0)

               {

                   // The specified worksheet does not exist.

                   return;

               }

               string relationshipId = sheets.First().Id.Value;

               WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);

               Hyperlinks hyperlinks1 = new Hyperlinks();

               Hyperlink hyperlink1 = new Hyperlink() { Reference = "A1", Id = "UNIQUE" };

               hyperlinks1.Append(hyperlink1);

               PageMargins pageMargins = worksheetPart.Worksheet.Descendants<PageMargins>().First();

               worksheetPart.Worksheet.InsertBefore<Hyperlinks>(hyperlinks1, pageMargins);

               worksheetPart.Worksheet.Save();

               worksheetPart.AddHyperlinkRelationship(new System.Uri("http://www.google.com/", System.UriKind.Absolute), true, "UNIQUE");

               // Create an element in SheetData

               // Get the SharedStringTablePart. If it does not exist, create a new one.

               SharedStringTablePart shareStringPart;

               if (document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)

               {

                   shareStringPart = document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();

               }

               else

               {

                   shareStringPart = document.WorkbookPart.AddNewPart<SharedStringTablePart>();

               }

               // Insert the text into the SharedStringTablePart.

               int index = InsertSharedStringItem("www.google.com", shareStringPart);

               // Insert cell A1 into the new worksheet.

               Cell cell = InsertCellInWorksheet("A", 1, worksheetPart);

               // Set the value of cell A1.

               cell.CellValue = new CellValue(index.ToString());

               cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);

               // Save the new worksheet.

               worksheetPart.Worksheet.Save();

           }

 

Here are the helper functions (taken from MS site):

// Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 

// and inserts it into the SharedStringTablePart. If the item already exists, returns its index.

private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)

{

    // If the part does not contain a SharedStringTable, create one.

    if (shareStringPart.SharedStringTable == null)

    {

        shareStringPart.SharedStringTable = new SharedStringTable();

    }

    int i = 0;

    // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.

    foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())

    {

        if (item.InnerText == text)

        {

            return i;

        }

        i++;

    }

    // The text does not exist in the part. Create the SharedStringItem and return its index.

    shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));

    shareStringPart.SharedStringTable.Save();

    return i;

}

 

// Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 

       // If the cell already exists, returns it. 

       private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)

       {

           Worksheet worksheet = worksheetPart.Worksheet;

           SheetData sheetData = worksheet.GetFirstChild<SheetData>();

           string cellReference = columnName + rowIndex;

           // If the worksheet does not contain a row with the specified row index, insert one.

           Row row;

           if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)

           {

               row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();

           }

           else

           {

               row = new Row() { RowIndex = rowIndex };

               sheetData.Append(row);

           }

           // If there is not a cell with the specified column name, insert one.  

           if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)

           {

               return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();

           }

           else

           {

               // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.

               Cell refCell = null;

               foreach (Cell cell in row.Elements<Cell>())

               {

                   if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)

                   {

                       refCell = cell;

                       break;

                   }

               }

               Cell newCell = new Cell() { CellReference = cellReference };

               row.InsertBefore(newCell, refCell);

               worksheet.Save();

               return newCell;

           }

       }

 

Assumptions:

I have made several assumptions while writing code for this.

1. I have assumed that Excel file is a new blank file i.e. there is no hyperlink present.
2. Sheet1 name is hardcoded.
3. File Name is Hardcoded.
4. Can you think of anything by looking at the code?

Run this code and everything will work. When you will open the Excel sheet and click on the link, it will be treated as hyperlink.

But still one thing is missing. It is not underlined and appear in the blue color. Because you need to apply a style to do that. I leave this exercise to my readers to check how the styles are applied and how they can use this information. In case of anything, let me know

Please try this approch in every situation where you use Open XML and let me know in case of any questions.