- 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
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