Lets Learn

Opinion Matters

Archive for February, 2011

Open XML SDK Learning Resources

Posted by Ankush on February 24, 2011

Open XML SDK 2.0 FAQ
http://social.msdn.microsoft.com/Forums/en-US/oxmlsdk/thread/19032efe-80af-4a16-b954-1f5a6d2f2236

Building Office Open XML Files
http://msdn.microsoft.com/en-us/magazine/cc163478.aspx

Introducing the Office (2007) Open XML File Formats
http://msdn2.microsoft.com/en-us/library/ms406049.aspx

How to: Manipulate Office Open XML Formats Documents
http://msdn2.microsoft.com/en-us/library/aa982683.aspx#Office2007ManipulatingXMLDocs_creating

Building Server-Side Document Generation Solutions Using the Open XML Object Model
http://msdn.microsoft.com/en-us/library/bb735940.aspx (Part 1 of 2)
http://msdn.microsoft.com/en-us/library/bb735939.aspx (Part 2 of 2)

How to use the Office XML file format and the packaging components from the .NET Framework 3.0 to create a simple Excel 2007 workbook or a simple Word 2007 document
http://support.microsoft.com/?id=931866

How Do I…Open XML SDK v2.0
http://msdn.microsoft.com/en-us/library/bb491088(office.14).aspx

Nanipulating Excel 2007 and PowerPoint 2007 Files with the Open XML Object Model (Part 1 of 2)
http://msdn.microsoft.com/en-us/library/bb739834.aspx

Manipulating Excel 2007 and PowerPoint 2007 Files with the Open XML Object Model (Part 2 of 2)
http://msdn.microsoft.com/en-us/library/bb727373.aspx

Reading Data from SpreadsheetML
http://blogs.msdn.com/brian_jones/archive/2008/11/10/reading-data-from-spreadsheetml.aspx

How to use components of the .NET Framework 3.0 to create and then to stream an Office Word 2007 document and an Office Excel 2007 workbook to a client computer
http://support.microsoft.com/kb/932921

Welcome to the Open XML Developer Workshop
http://msdn.microsoft.com/en-us/office/bb738430.aspx

Open XML Explained e-book
http://openxmldeveloper.org/articles/1970.aspx

Good Site to Search ECMA standard online :
http://www.documentinteropinitiative.org/default.aspx

Open XML SDK Documentation
http://msdn.microsoft.com/en-us/office/ee441239.aspx

Open XML Format SDK 2.0: Getting Started Best Practices
http://blogs.msdn.com/erikaehrli/archive/2009/05/14/open-xml-format-sdk-2-0-getting-started-best-practices.aspx

Office Open XML Formats: Retrieving Lists of Excel 2007 Worksheets
http://msdn.microsoft.com/en-us/library/bb332456.aspx

Manipulating Excel 2007 and PowerPoint 2007 Files with the Open XML Format API (Part 1 of 2)
http://msdn.microsoft.com/en-us/library/bb739834.aspx

Manipulating Excel 2007 and PowerPoint 2007 Files with the Open XML Format API (Part 2 of 2)
http://msdn.microsoft.com/en-us/library/bb727373.aspx

2007 Office System Sample: Open XML Format SDK 2.0 Code Snippets for Visual Studio 2008
http://www.microsoft.com/downloads/details.aspx?FamilyID=78bea298-a3f9-44cf-bde0-b4f30dc986df&displaylang=en

Writing Large Excel Files with the Open XML SDK
http://blogs.msdn.com/b/brian_jones/archive/2010/06/22/writing-large-excel-files-with-the-open-xml-sdk.aspx

Parsing and Reading Large Excel Files with the Open XML SDK
http://blogs.msdn.com/b/brian_jones/archive/2010/05/27/parsing-and-reading-large-excel-files-with-the-open-xml-sdk.aspx

How to efficiently generate OpenXML Workbook from Excel files containing very large amount of data
http://blogs.msdn.com/b/vsod/archive/2010/05/31/how-to-efficiently-generate-openxml-workbook-from-excel-files-containing-very-large-amount-of-data.aspx

Very very very good blogs to through..Its must if you would like to know more about formats:

Where is the documentation for Office’s docx/xlsx/pptx formats? Part 1: Office 2007
http://blogs.msdn.com/b/chrisrae/archive/2010/09/25/where-is-the-documentation-for-office-2010-s-docx-xlsx-pptx-formats.aspx

Where is the documentation for Office’s docx/xlsx/pptx formats? Part 2: Office 2010
http://blogs.msdn.com/b/chrisrae/archive/2010/10/06/where-is-the-documentation-for-office-s-docx-xlsx-pptx-formats-part-2-office-2010.aspx

I’ll keep on updating this list and will try to categorize it as it becomes bigger ūüôā

Posted in Open XML SDK | Tagged: , , , , | 4 Comments »

Question 7: How to find whether a DLL or EXE is managed or unmanaged

Posted by Ankush on February 24, 2011

What is best way to check if an assembly is managed or not?

Cheers!

 

Posted in Questions | Tagged: , | 2 Comments »

Custom UI part In Word and Open XML SDK 2.0

Posted by Ankush on February 24, 2011

Consider a scienrio you have a Word 2010 document in which you aretrying to custmization by adding custom UI part and you want to open this document in Open XML SDK, it WILL NOT WORK.

Why….?

Lets try this:

Create a new Word 2010 document and add a custom UI part [ You can use custom UI editor tool to do this]. Use the following XML to do the ribbon custmization.

<?xml version=”1.0″ encoding=”UTF-8″?>
<customUI xmlns=”http://schemas.microsoft.com/office/2009/07/customui“>
<ribbon>
<tabs>
<tab idMso=”TabDeveloper” visible=”false” />
<tab idMso=”TabReferences” visible=”false” />
</tabs>
</ribbon>
</customUI>

Now if you open this document using Open XML SDK 2.0, you will get an error message.

WordprocessingDocument doc = WordprocessingDocument.Open(‚Äú<Path of the document>‚ÄĚ, false);

Exception Message : ‚ÄúSpecified argument was out of the range of valid values.Parameter name: relationshipType‚ÄĚ

Call Stack:

DocumentFormat.OpenXml.Packaging.WordprocessingDocument.CreatePartCore(String relationshipType)
DocumentFormat.OpenXml.Packaging.OpenXmlPartContainer.CreateOpenXmlPart(String relationshipType)DocumentFormat.OpenXml.Packaging.OpenXmlPartContainer.LoadReferencedPartsAndRelationships(OpenXmlPackage openXmlPackage, OpenXmlPart sourcePart, ackageRelationshipCollection
relationshipCollection, Dictionary`2 loadedParts)
DocumentFormat.OpenXml.Packaging.OpenXmlPackage.Load()
DocumentFormat.OpenXml.Packaging.OpenXmlPackage.OpenCore(Stream stream, Boolean readWriteMode)
DocumentFormat.OpenXml.Packaging.WordprocessingDocument.Open(Stream stream, Boolean isEditable, OpenSettings openSettings)DocumentFormat.OpenXml.Packaging.WordprocessingDocument.Open(Stream stream, Boolean isEditable)

Lets get into more detail.

If you look into the callstack, it is failing in CreatePartCore function. So I used .net reflector tool and anlyzed the Open XML SDK assembly i.e. defination of CreatePartCore. This is what I see:

The¬†relationship¬†namespace for Office 2010 is¬†‚Äúhttp://schemas.microsoft.com/office/2007/relationships/ui/extensibility‚ÄĚ and if you look at the image, it is not included. In result, you get an exception.

This is all about the problem.

Solution: The only solution which will work her to create an addin and then do the ribbon customization.

Posted in Open XML SDK, Word | Tagged: , , , | 1 Comment »

Question 6: Controlling Delegates and Multicast Delegate

Posted by Ankush on February 23, 2011

You have created a multicast delegate. When you invoke it, each delegate gets fired. You need want more control on this. Basically you want a fine control over:

a. order in which each delegate is invoked

b. firing only a subset of delegates

c. firing each delegate based on the success or failure of previous delegates

How will you achieve this?

Hint:
A delegate, when called, will invoke all delegatess to read within its invocation list. These delegates are usually invoked sequentially from the first to the last one added.With the use of the GetInvocationList method of the MulticastDelegate class, you can obtain each delegate in the invocation list of a multicast delegate. This method accepts no parameters and returns an array of Delegate objects that corresponds to the invocation list of the delegate on which this method was called.

Now can anybody offer the code solution:::)

Posted in Questions | Tagged: , , | 2 Comments »

.NET Reflector is not free anymore

Posted by Ankush on February 21, 2011

It is really sad when I heard that .NET reflector tool is not free anymore. RedGate company has announced that they will charge 35$ for a ‚Äúperpetual license‚ÄĚ. So, it‚Äôs not free. Anyway it‚Äôs worth to purchase a license of this beautiful software which has really helped but in case if you are looking for some free “Open Source”, then have a look:

  1. ILSpy , an open-source .NET Assembly browser and decompiler.
  2. Common Compiler Infrastructure (CCI)
  3. Mono Cecil
  4. Kaliro
  5. Dotnet IL Editor (DILE)
  6. Monoflector
  7. http://www.jetbrains.com/resharper/download/index.html
  8. http://blogs.jetbrains.com/dotnet/2011/02/resharper-6-bundles-decompiler-free-standalone-tool-to-follow/

Let us know if you have seen/used any tool instead of .net reflector and It is as good as .NET reflector is.

Posted in Troubleshooting Basics | Tagged: , , , , , | 1 Comment »

Question – 5 : Make sure Exceptions Are Not Lost in Finally Blocks

Posted by Ankush on February 21, 2011

Let’s say you have an exception and you have added a finally block. Excetion will be lost and you will not not know what caused the excetion, it will be lost. And You want to protect against losing exception information when using multiple trycatch-finally blocks.

Hint : This really helps when you have multiple try/catch handler and you want to correctly rethrow the exception so that it bubblesup to the outer exception handler.

Do you have any suggestions which you can leave here as a part of the comment?

Posted in Questions | Tagged: , , , | 8 Comments »

Inserting Items in/Connecting to SharePoint List using OLEDB (ACE) Provider

Posted by Ankush on February 18, 2011

Recently I worked on a case where customer was trying to insert a record using OLE DB provider in SharePoint list.  Well that’s easy then where is the problem???? Problem came when list has a date filed and for some reason OLEDB provider was not able to update the date field.

So here is some more detail about it:

Problem: Consider you have created a VB.Net which uses the OLEDB provider to connect to a SharePoint list and insert a new record. Now the list contains some date field which should also be modified.

Environment: SharePoint 2007, VB.Net project (or as a matter of fact you can do it anywhere. You just need to refer to the provider. I also did it in using VBA)

Complete Solution:

Assumptions: You have created a list name Test which contains two columns:  Title and Start (which is of date type)

<< Solution which didn’t work>>

Step 1: Create the connection string and declare the appropriate variables. Please note that I have used the GUID of the list which you can easily get.

Dim conSharePoint As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strConnection As String
Dim intIMEX As Integer
Dim strDatabase As String
Dim strList As String
Dim strTable As String

intIMEX = 2¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬† ‘IMEX=1 for Read only, IMEX=2 for Read/Write
strDatabase = “http://abha21597318:9354
strList = “{46218811-6D9A-4DBD-AEE4-9F122692E0AE}”¬† ‘LIST=GUID to specific list or view
strTable = “Test”¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬† ‘TABLE=Display name of list
strConnection = “Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=” & intIMEX & “;RetrieveIds=Yes;” & “DATABASE=” & strDatabase & “;” & “LIST=” & strList & “;VIEW=;RetrieveIds=Yes;TABLE=” & strTable

Step 2: Open the connection and recordset

conSharePoint = New ADODB.Connection()
conSharePoint.Open(strConnection)
rst = New ADODB.Recordset()
rst.Open(“Test”, conSharePoint, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)

Step 3: Add a new record

With rst
.AddNew()
.Fields(“Title”).Value = “Test”
.Fields(“Start”).Value = “2011-04-15 00:00:00” ‘ THIS IS WHERE IT FAILS
.Update()
End With
Now I tried a lots of date formats but nothing seems to worked for me. If you happen to know a workaround/date format which works, please leave a comment here.

<< Solution which DID work>>

I started thinking about other workarounds and I used the popular workaround SOAP toolkit. I downloaded the SOAP toolkit and used the following code to do it. Fiddler tool really helped me in building the SOAP queries.

Sub InsertUsingSOAP()

Dim oSOAPClnt As SoapClient30
Dim sht As String
Dim wbkPath As String
Dim sts() As Object
Dim sId As String
Dim dom As IXMLDOMSelection
Dim ndListView As IXMLDOMNode
Set oSOAPClnt = New SoapClient30
Dim myXMLNodeList As MSXML2.IXMLDOMNodeList
Dim root As MSXML2.IXMLDOMElement
Dim xmlDoc As New MSXML2.DOMDocument30
Dim strListID As String
Dim xmlText As String

oSOAPClnt.MSSoapInit (“http://abha21597318:9354/_vti_bin/Lists.asmx?WSDL“)
‘ Test -> List Name
Set dom = oSOAPClnt.GetListAndView(“Test”, “”)
Set ndListView = dom.item(0)
‘ Make sure to Change the Attributes index
strListID = ndListView.ChildNodes(0).Attributes(7).NodeValue
xmlDoc.async = False

xmlText = “<root>” + _
“<Batch OnError=’Continue’ ListVersion=’24’ PreCalc=’TRUE’ xmlns=”>” + _
“<Method ID=’1′ Cmd=’New’>” + _
“<Field Name=’Title’>Test Insert from VBA</Field>” + _
“<Field Name=’Start’>2011-04-15 00:00:00</Field>” + _
“</Method>” + _
“</Batch>” + _
“</root>”
xmlDoc.LoadXML (xmlText)
If xmlDoc.parseError.ErrorCode <> 0 Then
Dim myErr
Set myErr = xmlDoc.parseError
MsgBox (myErr.reason)
Else
MsgBox xmlDoc.XML
End If
‘ Set up IXMLDOMNodeList object

Set root = xmlDoc.DocumentElement
Set myXMLNodeList = root.ChildNodes
oSOAPClnt.UpdateListItems strListID, myXMLNodeList

End Sub

Note : The same code which doesnt work in SP 2007 works in SP 2010. So you can use OLEDB provider to insert a record in list in SP 2010.

There is also one interesting scenerio comes when you want to use user name and password in SOAP tool kit. So this is what you can do to do that.

Sample Code
=============================
‘provide authentication info in case of basic auth. For windows ‘ authe. you don’t need to pass this info. Our connector asks windows security ‘ layer for this

SoapClient3.ConnectorProperty(“AuthUser”) = “username”
SoapClient3.ConnectorProperty(“AuthPassword”)=”password”
=============================
Basically If you don’t pass authentication information then the SoapToolkit’s Connector will¬† automatically query the windows security layer for the integrated authentication information for the current user and will use these credentials.If you want to supply the authentication information then you can use the ConnectorProperty as demonstrated above to pass the username and password
information.

Happy Reading!!!!!

Update : I thought about some other interestingoptions and here is the list:

1. Have an access database , linked table and then update records from there
2. Create a .net application which can consume web services and make it COM visible. Use this assembly in VBA
3. Use XMLHttp and directly invoke webservice. It will be a bit  tedious job as it requires one to manipulate and construct the XML.
http://msdn.microsoft.com/en-us/library/dd582945(v=office.11).aspx

 

Posted in Sp 2007 | Tagged: , , , , , , , , , , | 4 Comments »

Project Management Certifications

Posted by Ankush on February 17, 2011

So..this is how it started..I thought to get some knowledge to learn what are the different IT project management certifications available. When I searched about them over the web,I find a list¬†which gets bigger and bigger …but none of the site provides a comparison between them and most importantly for me nobody answered the questions like what/why.

I started thinking about ITIL¬†and then PMP,Scrum Master, Prince2 etc..but I would really love to see which one is a better fit in which situation. When I started looking out for this information, I couldn’t find anything.

So, I thought why not ask this question to the management community and see what they have to say..

Please suggest which one you think is better (or best) and why??

Posted in Certifications | 5 Comments »

Question # 4

Posted by Ankush on February 9, 2011

Using reflection, you invoke a method that generates an exception. You want to obtain the real exception object to work with it.

Any possible solution ( small code snippet will help :))?

Posted in Questions | 3 Comments »

 
%d bloggers like this: