Required Libraries
Open XML SDK 2.0 – This is the core SDK you need to do development for the Open XML format. You don’t have to get the larger OpenXMLSDKTool.msi (This includes the Open XML SDK 2.0 Productivity Tool for Microsoft Office which I talk about in the tools section), but I do recommend it. As a minimum, you have to get the OpenXMLSDKv2.msi.Optional Libraries
PowerTools for Open XML – This is an open source project that makes doing a lot of things easier because it has a bunch of methods that make use of the SDK, but shield you from much of the complexities. At the same time the library does put you in another world where it is difficult to get down to the SDK as needed.ExtremeML – Another open source project that makes working with Open XML format easily and much more of what you would hope. Includes support for Pivot tables. Stresses using Excel as a template.
ClosedXML – It appears to be pretty popular for easily working with spreadsheets. I have used it and I really like it so far. Most of the code I have seen shows using it to create Excel worksheets from scratch, but you can use the overloaded constructor to pass the name of the Excel file and open it. After that everything is the same. It is super easy to use for basic editing and I have to hope it would be for more complex stuff also. I did notice that it doesn't appear to support Pivot tables yet. However, you can still use the template approach where you create the Excel file using MS Excel, add your pivot table, related table, etc and then just use ClosedXML to update the table of data. If you do this you probably want to have the pivot table update on opening of the workbook. I love the template approach in general.
I haven't used this one, but it looks very good.
I haven't used this one either, but it looks very good also.
Tools
Open XML Package Editor Power Tool for Visual Studio 2010 – This is a must have for looking at the files in Visual Studio. It understands the relationships between files/parts which makes navigation and verifying relationships easier. It also allows you to use to XML formatter in Visual Studio to reformat the files so you can easily read them (there are not End of Line characters otherwise and everything is a blob of text which is difficult to read)Open XML SDK 2.0 Productivity Tool for Microsoft Office – This is another must have. It allows you to verify a file and gives you pretty good error messages compared to Excel. It also does much of the same things that Open XML Package Editor for Visual Studio 2010 does, but it takes like 300MB of RAM so it is kind of heavy to keep running. You can also compare two files and it will show you the differences. One of the coolest parts of this tool is that you have it reflect the C# code you need to generate a file or part of the file. This can be very useful. I has docs on the SDK which is handy as well.
7-zip – I like to use it to open the zip file and also edit the files directly if I am trying a quick change and want to see if it will work. I also find it useful to troubleshoot when a relationship or something like that is broken.
Documentation
Welcome to the Open XML SDK for Microsoft Office – a good place to startClass Library Reference – great reference. This is also available in the Open XML SDK 2.0 Productivity Tool for Microsoft Office
OpenXMLDeveloper.org – There are so many articles here, and definitely check out the wiki on the bottom left of the home page. They have videos, forums, several blogs, training series etc. If you want to find a how to article, this is the place to check.
No comments:
Post a Comment