5 people like it.

Create Open XML Spreadsheet with data

Create a Open Xml spreadsheet fromxml. In this snippet I did not reference the Open Xml SDK

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
27: 
28: 
29: 
30: 
31: 
32: 
33: 
34: 
35: 
36: 
37: 
38: 
39: 
40: 
41: 
42: 
43: 
44: 
45: 
46: 
47: 
48: 
49: 
50: 
51: 
52: 
53: 
54: 
55: 
56: 
57: 
58: 
59: 
60: 
61: 
62: 
63: 
64: 
65: 
66: 
67: 
68: 
69: 
70: 
71: 
72: 
73: 
74: 
75: 
76: 
77: 
78: 
79: 
80: 
81: 
82: 
83: 
84: 
85: 
86: 
87: 
88: 
89: 
90: 
91: 

//reference to the package 
#r "WindowsBase"

//reference to the Xml and Linq 
#r "System.Xml"
#r "System.Xml.Linq"

open System
open System.IO
open System.IO.Packaging
open System.Xml
open System.Xml.Linq

let spreadsheetML = @"http://schemas.openxmlformats.org/spreadsheetml/2006/main"
let relationSchema = @"http://schemas.openxmlformats.org/officeDocument/2006/relationships"
let workbookContentType = @"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"
let worksheetContentType = @"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"

let xnameEmpty str =
    XName.Get(str)
    
let xname str =
    XName.Get(str, spreadsheetML)

let ns = XNamespace.Get(spreadsheetML)
let nsRelation = XNamespace.Get(relationSchema)

let createPackagePart uriString contentType (xDocument:XDocument) (package:Package) =
    let uri  = new Uri(uriString, UriKind.Relative)
    let part = package.CreatePart(uri, contentType)
    using (new StreamWriter(part.GetStream(FileMode.Create, FileAccess.Write)))(fun stream ->
            xDocument.Save(stream)
        )
    (uri, part)

let createDocument (content:XElement) =
    let doc =new XDocument(new XDeclaration("1.0", "utf-8", "true"))
    content |> doc.Add
    doc

let createWorkBook sheetName=
    let content = new XElement(xname "workbook",
                    new XAttribute(XNamespace.Xmlns + "x", ns),
                    new XElement(xname "sheets",
                        new XElement(xname "sheet",
                            new XAttribute(xnameEmpty "name", sheetName),
                            new XAttribute(xnameEmpty "sheetId", "1"),
                            new XAttribute(XName.Get("id", relationSchema), "rId1"),
                            new XAttribute(XNamespace.Xmlns + "r", nsRelation))))
    content |> createDocument 

let createSheet (sheetData:XElement)=
    let content = new XElement(xname "worksheet",
                    new XAttribute(XNamespace.Xmlns + "x", ns),
                    new XElement(sheetData))
    content |> createDocument 

//add the document to package and save
let createFile (sheetData:XElement) (fileName:string)  =
    using (Package.Open(fileName, FileMode.Create, FileAccess.ReadWrite))(fun package ->
        
        let (uriStartPart,partWorkbook) = createPackagePart "/xl/workbook.xml" workbookContentType (createWorkBook "test") package
        let (uriWorksheet, _ ) = createPackagePart "/xl/worksheets/sheet1.xml" worksheetContentType (createSheet sheetData)package

        package.CreateRelationship(uriStartPart, TargetMode.Internal, relationSchema+"/officeDocument", "rId1") |> ignore
        partWorkbook.CreateRelationship(uriWorksheet, TargetMode.Internal, relationSchema + "/worksheet", "rId1") |> ignore

    )


let sheetData = new XElement(xname "sheetData")
let fileName = @"D:\Tmp\test.xlsx"
createFile sheetData fileName;;

let sheetData2 = new XElement(xname "sheetData",
                                new XElement(xname "row",
                                    new XAttribute(xnameEmpty "r", "1"),
                                    new XElement(xname "c",
                                        new XAttribute(xnameEmpty "r", "A1"),
                                        new XAttribute(xnameEmpty "t", "inlineStr"),
                                            new XElement(xname "is",
                                                new XElement(xname "t", "test"))),
                                    new XElement(xname "c",
                                        new XAttribute(xnameEmpty "r", "B1"),
                                        new XAttribute(xnameEmpty "t", "n"),
                                            new XElement(xname "v", 123
                                                ))
                                ))

let fileName2 = @"D:\Tmp\test2.xlsx"
createFile sheetData2 fileName2;;
namespace System
namespace System.IO
namespace System.IO.Packaging
namespace System.Xml
namespace System.Xml.Linq
val spreadsheetML : string

Full name: Script.spreadsheetML
val relationSchema : string

Full name: Script.relationSchema
val workbookContentType : string

Full name: Script.workbookContentType
val worksheetContentType : string

Full name: Script.worksheetContentType
val xnameEmpty : str:string -> XName

Full name: Script.xnameEmpty
val str : string
type XName =
  member Equals : obj:obj -> bool
  member GetHashCode : unit -> int
  member LocalName : string
  member Namespace : XNamespace
  member NamespaceName : string
  member ToString : unit -> string
  static member Get : expandedName:string -> XName + 1 overload

Full name: System.Xml.Linq.XName
XName.Get(expandedName: string) : XName
XName.Get(localName: string, namespaceName: string) : XName
val xname : str:string -> XName

Full name: Script.xname
val ns : XNamespace

Full name: Script.ns
type XNamespace =
  member Equals : obj:obj -> bool
  member GetHashCode : unit -> int
  member GetName : localName:string -> XName
  member NamespaceName : string
  member ToString : unit -> string
  static member Get : namespaceName:string -> XNamespace
  static member None : XNamespace
  static member Xml : XNamespace
  static member Xmlns : XNamespace

Full name: System.Xml.Linq.XNamespace
XNamespace.Get(namespaceName: string) : XNamespace
val nsRelation : XNamespace

Full name: Script.nsRelation
val createPackagePart : uriString:string -> contentType:string -> xDocument:XDocument -> package:Package -> Uri * PackagePart

Full name: Script.createPackagePart
val uriString : string
val contentType : string
val xDocument : XDocument
Multiple items
type XDocument =
  inherit XContainer
  new : unit -> XDocument + 3 overloads
  member Declaration : XDeclaration with get, set
  member DocumentType : XDocumentType
  member NodeType : XmlNodeType
  member Root : XElement
  member Save : fileName:string -> unit + 6 overloads
  member WriteTo : writer:XmlWriter -> unit
  static member Load : uri:string -> XDocument + 7 overloads
  static member Parse : text:string -> XDocument + 1 overload

Full name: System.Xml.Linq.XDocument

--------------------
XDocument() : unit
XDocument([<ParamArray>] content: obj []) : unit
XDocument(other: XDocument) : unit
XDocument(declaration: XDeclaration, [<ParamArray>] content: obj []) : unit
val package : Package
type Package =
  member Close : unit -> unit
  member CreatePart : partUri:Uri * contentType:string -> PackagePart + 1 overload
  member CreateRelationship : targetUri:Uri * targetMode:TargetMode * relationshipType:string -> PackageRelationship + 1 overload
  member DeletePart : partUri:Uri -> unit
  member DeleteRelationship : id:string -> unit
  member FileOpenAccess : FileAccess
  member Flush : unit -> unit
  member GetPart : partUri:Uri -> PackagePart
  member GetParts : unit -> PackagePartCollection
  member GetRelationship : id:string -> PackageRelationship
  ...

Full name: System.IO.Packaging.Package
val uri : Uri
Multiple items
type Uri =
  new : uriString:string -> Uri + 5 overloads
  member AbsolutePath : string
  member AbsoluteUri : string
  member Authority : string
  member DnsSafeHost : string
  member Equals : comparand:obj -> bool
  member Fragment : string
  member GetComponents : components:UriComponents * format:UriFormat -> string
  member GetHashCode : unit -> int
  member GetLeftPart : part:UriPartial -> string
  ...

Full name: System.Uri

--------------------
Uri(uriString: string) : unit
Uri(uriString: string, uriKind: UriKind) : unit
Uri(baseUri: Uri, relativeUri: string) : unit
Uri(baseUri: Uri, relativeUri: Uri) : unit
type UriKind =
  | RelativeOrAbsolute = 0
  | Absolute = 1
  | Relative = 2

Full name: System.UriKind
field UriKind.Relative = 2
val part : PackagePart
Package.CreatePart(partUri: Uri, contentType: string) : PackagePart
Package.CreatePart(partUri: Uri, contentType: string, compressionOption: CompressionOption) : PackagePart
val using : resource:'T -> action:('T -> 'U) -> 'U (requires 'T :> IDisposable)

Full name: Microsoft.FSharp.Core.Operators.using
Multiple items
type StreamWriter =
  inherit TextWriter
  new : stream:Stream -> StreamWriter + 6 overloads
  member AutoFlush : bool with get, set
  member BaseStream : Stream
  member Close : unit -> unit
  member Encoding : Encoding
  member Flush : unit -> unit
  member Write : value:char -> unit + 3 overloads
  static val Null : StreamWriter

Full name: System.IO.StreamWriter

--------------------
StreamWriter(stream: Stream) : unit
StreamWriter(path: string) : unit
StreamWriter(stream: Stream, encoding: Text.Encoding) : unit
StreamWriter(path: string, append: bool) : unit
StreamWriter(stream: Stream, encoding: Text.Encoding, bufferSize: int) : unit
StreamWriter(path: string, append: bool, encoding: Text.Encoding) : unit
StreamWriter(path: string, append: bool, encoding: Text.Encoding, bufferSize: int) : unit
PackagePart.GetStream() : Stream
PackagePart.GetStream(mode: FileMode) : Stream
PackagePart.GetStream(mode: FileMode, access: FileAccess) : Stream
type FileMode =
  | CreateNew = 1
  | Create = 2
  | Open = 3
  | OpenOrCreate = 4
  | Truncate = 5
  | Append = 6

Full name: System.IO.FileMode
field FileMode.Create = 2
type FileAccess =
  | Read = 1
  | Write = 2
  | ReadWrite = 3

Full name: System.IO.FileAccess
field FileAccess.Write = 2
val stream : StreamWriter
XDocument.Save(writer: XmlWriter) : unit
XDocument.Save(textWriter: TextWriter) : unit
XDocument.Save(stream: Stream) : unit
XDocument.Save(fileName: string) : unit
XDocument.Save(textWriter: TextWriter, options: SaveOptions) : unit
XDocument.Save(stream: Stream, options: SaveOptions) : unit
XDocument.Save(fileName: string, options: SaveOptions) : unit
val createDocument : content:XElement -> XDocument

Full name: Script.createDocument
val content : XElement
Multiple items
type XElement =
  inherit XContainer
  new : name:XName -> XElement + 4 overloads
  member AncestorsAndSelf : unit -> IEnumerable<XElement> + 1 overload
  member Attribute : name:XName -> XAttribute
  member Attributes : unit -> IEnumerable<XAttribute> + 1 overload
  member DescendantNodesAndSelf : unit -> IEnumerable<XNode>
  member DescendantsAndSelf : unit -> IEnumerable<XElement> + 1 overload
  member FirstAttribute : XAttribute
  member GetDefaultNamespace : unit -> XNamespace
  member GetNamespaceOfPrefix : prefix:string -> XNamespace
  member GetPrefixOfNamespace : ns:XNamespace -> string
  ...

Full name: System.Xml.Linq.XElement

--------------------
XElement(name: XName) : unit
XElement(other: XElement) : unit
XElement(other: XStreamingElement) : unit
XElement(name: XName, content: obj) : unit
XElement(name: XName, [<ParamArray>] content: obj []) : unit
val doc : XDocument
Multiple items
type XDeclaration =
  new : other:XDeclaration -> XDeclaration + 1 overload
  member Encoding : string with get, set
  member Standalone : string with get, set
  member ToString : unit -> string
  member Version : string with get, set

Full name: System.Xml.Linq.XDeclaration

--------------------
XDeclaration(other: XDeclaration) : unit
XDeclaration(version: string, encoding: string, standalone: string) : unit
XContainer.Add([<ParamArray>] content: obj []) : unit
XContainer.Add(content: obj) : unit
val createWorkBook : sheetName:'a -> XDocument

Full name: Script.createWorkBook
val sheetName : 'a
Multiple items
type XAttribute =
  inherit XObject
  new : other:XAttribute -> XAttribute + 1 overload
  member IsNamespaceDeclaration : bool
  member Name : XName
  member NextAttribute : XAttribute
  member NodeType : XmlNodeType
  member PreviousAttribute : XAttribute
  member Remove : unit -> unit
  member SetValue : value:obj -> unit
  member ToString : unit -> string
  member Value : string with get, set
  ...

Full name: System.Xml.Linq.XAttribute

--------------------
XAttribute(other: XAttribute) : unit
XAttribute(name: XName, value: obj) : unit
property XNamespace.Xmlns: XNamespace
val createSheet : sheetData:XElement -> XDocument

Full name: Script.createSheet
val sheetData : XElement
val createFile : sheetData:XElement -> fileName:string -> unit

Full name: Script.createFile
val fileName : string
Multiple items
val string : value:'T -> string

Full name: Microsoft.FSharp.Core.Operators.string

--------------------
type string = String

Full name: Microsoft.FSharp.Core.string
Package.Open(stream: Stream) : Package
Package.Open(path: string) : Package
Package.Open(stream: Stream, packageMode: FileMode) : Package
Package.Open(path: string, packageMode: FileMode) : Package
Package.Open(stream: Stream, packageMode: FileMode, packageAccess: FileAccess) : Package
Package.Open(path: string, packageMode: FileMode, packageAccess: FileAccess) : Package
Package.Open(path: string, packageMode: FileMode, packageAccess: FileAccess, packageShare: FileShare) : Package
field FileAccess.ReadWrite = 3
val uriStartPart : Uri
val partWorkbook : PackagePart
val uriWorksheet : Uri
Package.CreateRelationship(targetUri: Uri, targetMode: TargetMode, relationshipType: string) : PackageRelationship
Package.CreateRelationship(targetUri: Uri, targetMode: TargetMode, relationshipType: string, id: string) : PackageRelationship
type TargetMode =
  | Internal = 0
  | External = 1

Full name: System.IO.Packaging.TargetMode
field TargetMode.Internal = 0
val ignore : value:'T -> unit

Full name: Microsoft.FSharp.Core.Operators.ignore
PackagePart.CreateRelationship(targetUri: Uri, targetMode: TargetMode, relationshipType: string) : PackageRelationship
PackagePart.CreateRelationship(targetUri: Uri, targetMode: TargetMode, relationshipType: string, id: string) : PackageRelationship
val sheetData : XElement

Full name: Script.sheetData
val fileName : string

Full name: Script.fileName
val sheetData2 : XElement

Full name: Script.sheetData2
val fileName2 : string

Full name: Script.fileName2
Raw view Test code New version

More information

Link:http://fssnip.net/5F
Posted:13 years ago
Author:Piet Amersfoort
Tags: open xml , spreadsheet

This web site is created using F# and Suave web server. It is hosted on Azure and the source code is on GitHub. Contributions are welcome!

The first version of fssnip.net has been created by @tomaspetricek back in 2010. This web site is a new, open-source and contribution-friendly version.