#Region "Excel Download" ''' ''' Generate an excel file with data in GridView control ''' ''' <param name="datatable">DataTable object</param> ''' <param name="filepath">The Path of exported excel file</param> Public Sub ExportToExcel(ByVal datatable As DataTable, ByVal filepath As String, ByVal sheetname1 As String) ' Initialize an instance of SpreadSheet Document Try sheetname = sheetname1 Using _spreadsheetDocument As SpreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook) CreateExcelFile(_spreadsheetDocument, datatable) End Using Catch ex As Exception MsgBox("SuffixCode export to excel error, " & ex.Message) End Try End Sub ''' ''' Create SpreadSheet Document and Fill datas ''' ''' <param name="spreadsheetdoc">SpreadSheet Document</param> ''' <param name="table">DataTable Object</param> Private Sub CreateExcelFile(ByRef spreadsheetdoc As SpreadsheetDocument, ByVal table As DataTable) ' Initialize an instance of WorkbookPart Try Dim workBookPart As WorkbookPart = spreadsheetdoc.AddWorkbookPart() ' Create WorkBook CreateWorkBookPart(workBookPart) ' Add WorkSheetPart into WorkBook Dim worksheetPart1 As WorksheetPart = workBookPart.AddNewPart(Of WorksheetPart)("rId1") CreateWorkSheetPart(worksheetPart1, table) ' Add SharedStringTable Part into WorkBook Dim sharedStringTablePart As SharedStringTablePart = workBookPart.AddNewPart(Of SharedStringTablePart)("rId2") CreateSharedStringTablePart(sharedStringTablePart, table) ' Add WorkbookStyles Part into Workbook Dim workbookStylesPart As WorkbookStylesPart = workBookPart.AddNewPart(Of WorkbookStylesPart)("rId3") CreateWorkBookStylesPart(workbookStylesPart) ' Save workbook workBookPart.Workbook.Save() Catch ex As Exception 'MsgBox("Error in calling CreateExcelFile foe End Try End Sub ''' ''' Create an Workbook instance and add its children ''' ''' <param name="workbookPart">WorkbookPart Object</param> Private Sub CreateWorkBookPart(ByRef workbookPart As WorkbookPart) Dim workbook As New Workbook() Dim sheets As New Sheets() ' Initilize an instance of Sheet Object Dim sheet1 As New Sheet() With { _ .Name = sheetname, _ .SheetId = Convert.ToUInt32(1), _ .Id = "rId1" _ } ' Add the sheet into sheets collection sheets.Append(sheet1) Dim calculationProperties1 As New CalculationProperties() With { _ .CalculationId = Convert.ToUInt32(111222) _ } ' Add elements into workbook workbook.Append(sheets) workbook.Append(calculationProperties1) workbookPart.Workbook = workbook End Sub ''' ''' Generates content of worksheetPart ''' ''' <param name="worksheetPart">WorksheetPart Object</param> ''' <param name="table">DataTable Object</param> Private Sub CreateWorkSheetPart(ByRef worksheetPart As WorksheetPart, ByVal table As DataTable) ' Initialize worksheet and set the properties Try Dim worksheet1 As New Worksheet() With { _ .MCAttributes = New MarkupCompatibilityAttributes() With { _ .Ignorable = "x14ac" _ } _ } worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships") worksheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006")
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)