EXPORT TO EXCEL
Protected Sub Export(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button_ExportToExcel.Click
File.Copy(“c:\Template.xlsx”, “c:\SimTemp\generated.xlsx”, True)
‘Open up the copied template workbook
Using myWorkbook As SpreadsheetDocument = SpreadsheetDocument.Open(“c:\SimTemp\generated.xlsx”, True)
‘Access the main Workbook part, which contains all references
Dim workbookPart As WorkbookPart = myWorkbook.WorkbookPart
‘Grab the first worksheet
Dim worksheetPart As WorksheetPart = workbookPart.WorksheetParts.First
‘SheetData will contain all the data
Dim sheetData As SheetData = worksheetPart.Worksheet.GetFirstChild(Of SheetData)()
‘Connect to database
Dim db As New OrdersSpreadSheetDataContext
‘My data starts at row 2
Dim index As Integer = 2
Dim OrderNum As Integer = -1
OrderNum = GridView_OrderHeader.DataKeys(GridView_OrderHeader.SelectedIndex).Values(“OrderNum”)
‘Select * from BarCode_Details table
Dim DetailsQuery = From t In db.Barcode_Details Where t.OrderNum = OrderNum _
Select t
‘For each row in my database add a row to my spreadsheeet
sheetData = worksheetPart.Worksheet.GetFirstChild(Of SheetData)()
For Each item In DetailsQuery
Dim ProductNumber As String = item.ItemNum
Dim ProductName As String = item.ItemName
Dim Quantity As Decimal = item.ItemQty
Dim UnitPrice As Decimal = Math.Round(CDbl(item.ItemPrice), 2)
Dim TotalPrice As Decimal = Math.Round(CDbl(item.ItemQty * item.ItemPrice), 2)
‘Add a new row
Dim contentRow As Row = CreateContentRow(index, ProductNumber, ProductName, Quantity, UnitPrice, TotalPrice)
index += 1
‘Append new row to sheet data
sheetData.AppendChild(contentRow)
Next
worksheetPart.Worksheet.Save()
End Using
Response.Clear()
Response.AddHeader(“Content-disposition”, “attachment;filename=” & “Quote.xlsx”)
Response.ContentType = “application/excel”
Response.WriteFile(“c:\SimTemp\generated.xlsx”)
Response.End()
End Sub
Dont forget to remove Sheet 2/ Sheet 3 from the template.xlsx.
The excel sheet generated this way dose not contain meta data for XL.
