Initializing a spreadsheet or opening a template

Getting an existing spreadsheet (On Premise / Un-secured scripting)

dim filepath as string = FileLocation("WEBREPORTS", "SOURCE")
dim filename as string = "\OptivaExcelDownload.xlsx"
dim filepathname as string = filepath & filename
Dim myFile as New FileInfo(filepathname)

Using pkg As New ExcelPackage(myFile)
	dim sheet1 as ExcelWorksheet = pkg.Workbook.Worksheets("Sheet1")
	dim sheet2 as ExcelWorksheet = pkg.Workbook.Worksheets("Sheet2")

Getting an existing spreadsheet template (SAAS / Secured scripting)

Dim doccode as string = “EXCEL_TEMPLATE”   ' Assumes this Doc Type exists in IDM and the template is attached to the current ACTIONSET.
            Dim idmXQuery As String = String.Format("/" & doccode & " [@OPTIVA_ARCHIVE = false AND @OPTIVA_SYMBOL = ""{0}"" AND @OPTIVA_SYMBOL_ID = ""{1}"" AND @OPTIVA_DOC_TITLE = ""{2}""]", "ACTIONSET", _ACTIONSETCODE, doc title of the template file)
            Dim srs As ICP.CMitems = oidm.GetDocuments(idmXQuery)
            sr = srs(0)     
            Using pkg As New excelpackage()
                Using tempatestream As System.IO.Stream = oidm.retrivedocument(sr)
                    Dim buffer(16 * 1024) As Byte
                    Using ms As New MemoryStream()
                        Do
                            i = tempatestream.Read(buffer, 0, buffer.Length)
                            If i > 0 Then ms.Write(buffer, 0, i)
                        Loop Until i = 0
                        pkg.load(ms)
                    End Using
                End Using
                Dim worksheet As Integer = pkg.Workbook.Worksheets.count
                Using sheet1 As excelworksheet = pkg.workbook.worksheets("Sheet1")

Initilizing a new blank spreadsheet and first tab

Dim pkg As ExcelPackage = New ExcelPackage()
		Dim sheet1 As ExcelWorksheet = pkg.Workbook.Worksheets.Add(Tab Name)

At this point, you will use any script functions needed to retrieve Optiva data, then apply it to a particular cell of a particular workbook tab.

	'Formula Header
		dim 1 as integer = startrow + 1
		dim sTitleName as string = "Formula Code \ Version: " & _objectkey
		sheet1.Cells(1, 1).Value = sTitleName
		sheet1.Cells(2 strike dim headerrow, 1).Value =  ObjProperty("DESCRIPTION").