CRÉATION D'UN FICHIER EXCEL EN UTILISANT OPENXML

Imports System.IO
Imports System.IO.Packaging
Imports System.Xml

Partial Class _Default
 Inherits System.Web.UI.Page

 Const _kSpreadsheetML As String = "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
 Const _kRelationSchema As String = "http://schemas.openxmlformats.org/officeDocument/2006/relationships"
 Const _kWorkbookContentType As String = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"
 Const _kWorksheetContentType As String = "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"
 Const _kStylesheetContentType As String = "application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"
 Const _kStringsContentType As String = "application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"

 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

  If Not Me.IsPostBack Then

   HelloWorldExcel()

  End If

 End Sub

 Private Sub HelloWorldExcel()

  Dim vBufferStream As MemoryStream = New MemoryStream()
  Dim vPackage As Package = Package.Open(vBufferStream, FileMode.Create, FileAccess.ReadWrite)

  'Création du classeur Excel
  Dim vXmlDocumentWorkBook As New XmlDocument()
  Dim vXmlElementWorkBook As XmlElement = vXmlDocumentWorkBook.CreateElement("workbook", _kSpreadsheetML)
  Dim vNameSpaceAttribute As XmlAttribute = vXmlDocumentWorkBook.CreateAttribute("xmlns", "r", "http://www.w3.org/2000/xmlns/")
  vNameSpaceAttribute.Value = _kRelationSchema
  vXmlElementWorkBook.Attributes.Append(vNameSpaceAttribute)
  vXmlDocumentWorkBook.AppendChild(vXmlElementWorkBook)
  Dim vXmlElementSheets As XmlElement = vXmlDocumentWorkBook.CreateElement("sheets", _kSpreadsheetML)
  vXmlElementWorkBook.AppendChild(vXmlElementSheets)
  Dim vXmlElementSheet As XmlElement = vXmlDocumentWorkBook.CreateElement("sheet", _kSpreadsheetML)
  vXmlElementSheet.SetAttribute("name", "Feuil1")
  vXmlElementSheet.SetAttribute("sheetId", "1")
  vXmlElementSheet.SetAttribute("id", _kRelationSchema, "rId1")
  vXmlElementSheets.AppendChild(vXmlElementSheet)

  'Création de la feuille Excel
  Dim vXmlDocumentWorkSheet As New XmlDocument()
  Dim vXmlElementWorkSheet As XmlElement = vXmlDocumentWorkSheet.CreateElement("worksheet", _kSpreadsheetML)
  vXmlElementWorkSheet.SetAttribute("xmlns:r", _kRelationSchema)
  vXmlDocumentWorkSheet.AppendChild(vXmlElementWorkSheet)
  Dim vXmlElementSheetData = vXmlDocumentWorkSheet.CreateElement("sheetData", _kSpreadsheetML)
  vXmlElementWorkSheet.AppendChild(vXmlElementSheetData)

  'Sauvegarde d'un package temporaire pour créer le bon content type
  Dim vUriDefaultContentType As Uri = New Uri("/default.xml", UriKind.Relative)
  Dim vPackageTemp As PackagePart = vPackage.CreatePart(vUriDefaultContentType, "application/xml")

  'Sauvegarde dans le package de classeur Excel
  Dim vUriWorkBook As Uri = New Uri("/xl/workbook.xml", UriKind.Relative)
  Dim vPackageWorkBook As PackagePart = vPackage.CreatePart(vUriWorkBook, _kWorkbookContentType)
  Dim vStreamWriterWorkBook As StreamWriter = New StreamWriter(vPackageWorkBook.GetStream(FileMode.Create, FileAccess.Write))
  vXmlDocumentWorkBook.Save(vStreamWriterWorkBook)
  vStreamWriterWorkBook.Close()
  vPackage.Flush()

  'Sauvegarde dans le classeur de la feuille Excel
  Dim vUriWorkSheet As Uri = New Uri("/xl/worksheets/sheet1.xml", UriKind.Relative)
  Dim vPackageWorkSheet As PackagePart = vPackage.CreatePart(vUriWorkSheet, _kWorksheetContentType)
  Dim vStreamWriterWorkSheet As StreamWriter = New StreamWriter(vPackageWorkSheet.GetStream(FileMode.Create, FileAccess.Write))
  vXmlDocumentWorkSheet.Save(vStreamWriterWorkSheet)
  vStreamWriterWorkSheet.Close()
  vPackage.Flush()

  'Création des relations
  vPackage.CreateRelationship(vUriWorkBook, TargetMode.Internal, _kRelationSchema & "/officeDocument", "rId1")
  vPackageWorkBook.CreateRelationship(vUriWorkSheet, TargetMode.Internal, _kRelationSchema & "/worksheet", "rId1")

  'Suppression du package temporaire
  vPackage.DeletePart(vUriDefaultContentType)

  vPackage.Flush()
  vPackage.Close()

  'Ecriture du package (le fichier xlsx sur l'objet response
  Response.ClearHeaders()
  Response.ContentType = "application/octet-stream"
  Response.AddHeader("content-disposition", "attachement; filename=Hello.xlsx")
  Response.ContentEncoding = System.Text.Encoding.UTF8
  Response.ClearContent()

  vBufferStream.Position = 0
  Dim vWriter As New BinaryWriter(Response.OutputStream)
  Dim vReader As New BinaryReader(vBufferStream)
  vWriter.Write(vReader.ReadBytes(vBufferStream.Length))
  vReader.Close()
  vWriter.Close()
  vBufferStream.Close()
  Response.Flush()
  Response.Close()

 End Sub

End Class

Télécharger le zip