Article by Mallika Biswas,Sonata software Limited.
This article explains how to copy table content of a WordprocessingML document to a SpreadsheetML document using System.IO.Packaging API.
Steps:
1. Take an existing word document having a table with any no of rows and column.
2. Write a XSLT code which will take document.xml of the word document as input and will create sheet.xml as output taking value from each column of each row of the table of the word document.
3. Now create an spreadsheet document having sheet.xml generated by the XSLT code.
Implementation:
1. Create a new c# project. Go to references, add the WindowsBase.dll.
· Go to Add reference dialog box and go to browse tab.
· From C:\Program Files\Referenc Assemblies\Microsoft\Framework\v3.0
add WindowsBase.dll.
2. On the Form add two text boxes to take input from user.
3. Open the existing word document from where values has to be taken.
pack =Package.Open(filepath,FileMode.Open, FileAccess.ReadWrite);
4. Get the main document part.
foreach (System.IO.Packaging.PackageRelationship relationship in pack.GetRelationshipsByType(documentRelationshipType))
{
documentUri = PackUriHelper.ResolvePartUri(new Uri("/", UriKind.Relative), relationship.TargetUri);
documentpart = pack.GetPart(documentUri);
break;
}
5. Load the document.xml and save it in a xml file to use it as input of the XSLT.
xdoc.Load(documentpart.GetStream());
xdoc.Save("strInputFile.xml");
6. Add the XSLT in your project.
7. Create an object of CompileTtransform and load the XSLT there.
String filename = "strInputFile.xml";
String stylesheet = "../../sheet.xslt";
XslCompiledTransform xslt = new XslCompiledTransform(true);
xslt.Load(stylesheet);
8. Load the file to transform
XPathDocument doc = new XPathDocument(filename);
9. Take a xml file to save the output of the transformation.
StreamWriter stmOut = new StreamWriter("../../sheet1.xml");
10. Take input(starting row no and column no) from user and pass it to the XSLT.
XsltArgumentList n = new XsltArgumentList();
n.AddParam("rowNum", "", tboxRowNo.Text);
n.AddParam("colNum", "", tboxColoumnNo.Text);
11. Transform the file(document.xml)
xslt.Transform(doc, n, stmOut);
12. Now create the XLSX file.
13. Open the package in create mode where you will put your created excel sheet.
packageout = Package.Open(path, FileMode.Create, FileAccess.ReadWrite);
14. Create sheet part and load the sheet1.xml(output of XSLT code) in it.
worksheet.Load(@"../../sheet1.xml");
sheetUri = new Uri("/xl/worksheets/sheet1.xml", UriKind.Relative);
worksheetPart = packageout.CreatePart(sheetUri, worksheetContentType);
StreamWriter sheetStreamWriter = new StreamWriter(worksheetPart.GetStream(FileMode.Create, FileAccess.Write));
worksheet.Save(sheetStreamWriter);
15. Create workbook part.
workbookUri = new Uri("/xl/workbook.xml", UriKind.Relative);
workbookPart = packageout.CreatePart(workbookUri, workbookContentType);
16. Create realationship of the workbook with the package.
packageout.CreateRelationship(workbookUri, TargetMode.Internal, relationSchema, "rId1");
17. Create realationship of the sheet part with workbook.
workbookPart.CreateRelationship(sheetUri, TargetMode.Internal, worksheetRels, "rId1");
XSLT code:
1. Declare the parameters in which you have taken user’s input.
<xsl:param name="rowNum"></xsl:param>
<xsl:param name="colNum"></xsl:param>
2. For each row(w:tr) of the document.xml create row(<row>) for the sheet.
In $rowNum,the starting row no will be there.
<row>
<xsl:variable name="a">
<xsl:number value="position()" format="1"/>
</xsl:variable>
<xsl:variable name="row" select="$rowNum + $a - 1"/>/
<xsl:attribute name="r">
<xsl:value-of select="$row"/>
</xsl:attribute>
--
3. For each coloumn of each row,create<c> elment and its attributes for the sheet.
<c>
<xsl:attribute name="r">
<xsl:variable name="val">
<xsl:number value="position() + $colNum - 1" format="A" />
</xsl:variable>
<xsl:value-of select="concat($val,$row)"/>
</xsl:attribute>
4. Check for the value of the coloumn is a string or a no.If it is a string create an attribute named “t” and set the value “inlineStr” to the attribute.
<xsl:when test="string(number(w:p/w:r/w:t))='NaN'">
<xsl:attribute name="t">
<xsl:value-of select="'inlineStr'"/>
</xsl:attribute>
<is>
<t>
<xsl:value-of select="w:p"/>
</t>
</is>
</xsl:when>
Otherwise simply take the value within <v> element.
<xsl:otherwise>
<v>
<xsl:value-of select="w:p"/>
</v>
</xsl:otherwise>
This is a simple demo which demonstrates use of System.IO.Packaging API for transporting table content from WordProcessingML document to SpreadsheetML document. The example demo is attached with the article as a zip file.