QUESTO E' UN POST DI ANTEPRIMA CHE STO PER PUBBLICARE SUL SITO DEGLI SVILUPPATORI OPEN XML, LO STANDARD MICROSOFT PER WORD, EXCEL E POWERPOINT 2007. APPENA HO TEMPO LO TRADURRO' ABBIATE PAZIENZA...
Microsoft.Office.DocumentFormat.OpenXml.dll, the new API for Open XML files management is very powerful, for very important features I did experiment with one of the articles of the Microsoft guru Frank Rice, you can read at the following link:
http://msdn2.microsoft.com/en-us/library//bb739834.aspx
Such simple snippet serves for removing a sheet from an Excel 2007 file and I modify it in order to be attached to a simple button of a Windows Form, in the recent Visual Studio 2008. Of course OpenXml.dll API is before to be referenced with well known steps. I tested it with a MySpread.xlsx resident in a C:\MyFolder directory and IT WORKS IN VSTO (Visual Studio Tools for Office) 2008, too:
Imports Microsoft.Office.DocumentFormat.OpenXml.Packaging
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Open Excel package, with Read/Write access
Dim DocExcel As SpreadsheetDocument = _
SpreadsheetDocument.Open("C:\MyFolder\MySpread.xlsx", True)
Dim DocXml As Xml.XmlDocument = New Xml.XmlDocument
DocXml.Load(DocExcel.WorkbookPart.GetStream)
Dim nsManager As Xml.XmlNamespaceManager = _
New Xml.XmlNamespaceManager(DocXml.NameTable)
nsManager.AddNamespace("d", DocXml.DocumentElement.NamespaceURI)
Dim strQuery As String = _
String.Format("//d:sheet [@name='{0}']", "Foglio1")
‘Nota bene: “Foglio1” in Italy, “Sheet1” in USA etc.
Dim NodoXml As Xml.XmlNode = _
DocXml.SelectSingleNode(strQuery, nsManager)
If Not NodoXml Is Nothing Then
'Get RelId attribute
Dim AttribRelaz As Xml.XmlAttribute = _
NodoXml.Attributes("r:id")
If Not AttribRelaz Is Nothing Then
Dim Rid As String = AttribRelaz.Value
DocExcel.WorkbookPart.DeletePart(Rid)
NodoXml.ParentNode.RemoveChild(NodoXml)
DocXml.Save(DocExcel.WorkbookPart.GetStream(IO.FileMode.Create))
End If
End If
End Sub
End Class
For the reasons I ‘ll expose at the end of this post and I anticipate right now: lack of information! , I cannot give more remarks with respect to Frank Rice ‘s ones. I only tell the story of my test:
1. First the Frank snippet was rejected with mysterious messages;
2. Then I examined it “theoretically” posing a doubt to our very kind Erika Ehrli Cabral, who assured me with a precise opinion;
3. Finally I discovered WHY the snippet did not work, so I could correct it and obtained the confirmation of Erika answer!
The corrections:
XmlNamespaceManager ---> Xml.XmlNamaspaceManager
XmlDocument ---> Xml. XmlDocument
XmlNode ---> Xml.XmlNode
My e-mail to Erika:
Hi Erika,
the following one is an e-mail to Frank, he did not yet give me any reply:
Hi Frank,
your articles on the new SDK for Open XML are very interesting but I cannot understand the second example (deleting a sheet). Till now I use VBA macros with DOM and when I delete some sheet1.xml I do the same with related nodes in the following parts:
-[content_types].xml
- \xl\_rels\workbook.xml.rels
- workbook.xml
with distinct instructions. In your function it seems that all it does is deleting only the specific node in the workbook part.
As a first impression it sound like an error… Maybe is it an automatism of the new SDK?
Best Regards - Gianni
Erika answer:
Hi Gianni,
Frank was out of office and is currently working on a new doc set deliverable. He should be able to answer your question soon. He is always the best one in our team to follow-up on customer issues :).
Regarding my opinion, the Open XML Format SDK has methods or functions that deal with the entire document parts. If you delete a sheet for instance, the function also deletes the related info to that part from the content_types.xml, the corresponding relationship files, and the corresponding elements from the workbook.xml file. This greatly simplifies the process of working with Open XML. Using DOM in VBA, or the System.IO.Packaging API in managed code, you have to do all this manually. However, the Open XML SDK does all that for you.
IT IS ALL TRUE! In other words, the OpenXml.dll not only with SpreadsheetDocument.Open() instruction gives transparent access to “zipped” folders & components of .docx, .xlsx etc. files (*) but the Workbook Part of the package behave as a sort of “super-part”, which automatically make it possible to affect the related .xml components and a sheet1.xml file itself.
(*) In DOM + VBA or System.IO.Packaging API I have to use ad hoc procedures, for extracting xml part from zipped filed and vice-versa (when some modifications was made). Re-usable but tedious routines,
All that is very good although a SERIOUS PROBLEM remains for me and the rest of us:
SNIPPETS ARE ONLY LIGHTS IN A DARK SKY, BECAUSE TILL NOW THERE IS A TOTAL LACK OF CLEAR AND SYSTEMATIC INFORMATION ON THIS NEW OBJECT MODEL (apart from sparse and… obscure fragments in MSDN…).
?>