Document Assembly Solution for SpreadsheetML
In this post, I will show you how to populate a spreadsheet with data from a real SQL database and create a cool looking chart based on that data, all without using Excel client.
Scenario
Today’s scenario theme is document assembly, which is all about constructing a file based on external data sources, like other files or databases.
You can also set a reference to excel in Access, and then use the application object to turn off the warning in excel before doing the import.
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Set objXL = New Excel.Application
‘turn off excel warnings
objXL.DisplayAlerts = False
‘Open the Workbook
Set objWkb = objXL.Workbooks.Open(fpath)
‘import excel object
DoCmd.TransferSpreadsheet acImport, 8, “applicantImport”, fpath, True, “A1:V10000″
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”)