The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.

Forum Discussion

Sridhar_M's avatar
Sridhar_M
Contributor
2 years ago
Solved

Is it possible to use spreadsheet component (Table View in it) in the Book ?

Hi All, 

I create a spreadsheet business rule to refresh the data from external table using Table Views, now our users wants to drop these files into OneStream file share on daily basis so external systems can consume. 

Is it possible to use parcel service to refresh these files (using table views with in the spreadsheet) and drop them into file share every time the job trigger ? 

Or Is there any alternate approaches to export data from custom table to excel files ?

Thank you. 

dashboards
Excel Add In
Extensible Documents
Reports
  • JackLacava's avatar
    JackLacava
    2 years ago

    There are 3 approaches I am aware of, via standard Data Management features:

    • Build your view as a Grid View powered by a Data Adapter, put it in a Dashboard, then use a DM Step of type "Export Report" pointing to the dashboard.
    • Use a DM Step of type "Export File", and configure your Excel file with Extensible Document features. The job has an option to process them.
    • Use a DM Step of type "Execute Business Rule", and just produce an Excel file in code.

    Then you can probably use Parcel Service to send out the resulting output, but I'm not very familiar with it myself.

6 Replies

  • JackLacava's avatar
    JackLacava
    Icon for OneStream Employee rankOneStream Employee
    2 years ago

    There are 3 approaches I am aware of, via standard Data Management features:

    • Build your view as a Grid View powered by a Data Adapter, put it in a Dashboard, then use a DM Step of type "Export Report" pointing to the dashboard.
    • Use a DM Step of type "Export File", and configure your Excel file with Extensible Document features. The job has an option to process them.
    • Use a DM Step of type "Execute Business Rule", and just produce an Excel file in code.

    Then you can probably use Parcel Service to send out the resulting output, but I'm not very familiar with it myself.

    • Sridhar_M's avatar
      Sridhar_M
      Contributor
      2 years ago

      Thank you JackLacava for providing those details. 

      I tried all 3 steps, 2nd and 3rd is working. But for the 1st step if I try to export using DM step with SQL Table Editor/Grid View components with in dashboard, it is just exporting empty file with out data. 

      Thank you. 

      Sridhar 

      • MarkBird's avatar
        MarkBird
        Contributor III
        2 years ago

        Hi Sridhar 

        We're having a similar issue. Did you manage to resolve this?

        Regards,

        Mark

  • Sridhar_M's avatar
    Sridhar_M
    Contributor
    1 year ago

    Hi jwagner 

    Here’s a code snippet that generates an Excel file, with the DataTable passed as an argument to the function.

     

    #Region "Create Excel File"
     
    Public Sub CreateExcelFileFromDataTable(ByVal si As SessionInfo, dataTable As DataTable)
     
     
    'Create a new Excel file
     
    Dim filePath As String = "\\FolderPath\"FileName".xlsx"
     
    BRApi.ErrorLog.LogMessage(si,filePath & dataTable.Rows.Count)
     
    'Create the SpreadsheetDocument object and set its type to Workbook
    Using document As SpreadsheetDocument = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook)
    'Create the workbook
    Dim workbookPart As WorkbookPart = document.AddWorkbookPart()
    workbookPart.Workbook = New Workbook()
    'Create the worksheet
    Dim worksheetPart As WorksheetPart = workbookPart.AddNewPart(Of WorksheetPart)()
    worksheetPart.Worksheet = New Worksheet()
    'Create the sheet data
    Dim sheetData As SheetData = worksheetPart.Worksheet.AppendChild(New SheetData())
    'Create the header row
    Dim headerRow As Row = New Row()
    'Loop through each column in the DataTable and add it to the header row
    For Each column As DataColumn In dataTable.Columns
    headerRow.AppendChild(New Cell() With {.DataType = CellValues.String, .CellValue = New CellValue(column.ColumnName)})
    Next
     
    'Add the header row to the sheet data
    sheetData.AppendChild(headerRow)
     
    'Populate the data rows
    For Each dataRow As DataRow In dataTable.Rows
        Dim row As New Row()
        
        'Loop through each column in the DataTable and add the corresponding cell value to the current row
        For Each column As DataColumn In dataTable.Columns
            Dim cellValue As String = dataRow(column.ColumnName).ToString()
            Dim cell As New Cell()
     
            ' Check the data type and set the cell data type accordingly
            If column.DataType Is GetType(Integer) OrElse column.DataType Is GetType(Long) OrElse column.DataType Is GetType(Double) OrElse column.DataType Is GetType(Decimal) Then
                ' Handle numeric types
                If Not String.IsNullOrEmpty(cellValue) Then
                    cell.DataType = CellValues.Number
                    cell.CellValue = New CellValue(cellValue)
                Else
                    ' Handle empty numeric cells as blank
                    cell.DataType = CellValues.Number
                    cell.CellValue = New CellValue("0")
                End If
            ElseIf column.DataType Is GetType(DateTime) Then
                ' Handle DateTime types
                Dim dateValue As DateTime
                If DateTime.TryParse(cellValue, dateValue) Then
                    cell.DataType = CellValues.Date
                    cell.CellValue = New CellValue(dateValue.ToString("yyyy-MM-ddTHH:mm:ss"))
                Else
                    cell.DataType = CellValues.String
                    cell.CellValue = New CellValue("")
                End If
            Else
                ' Default to string for all other data types
                cell.DataType = CellValues.String
                cell.CellValue = New CellValue(cellValue)
            End If
     
            row.AppendChild(cell)
        Next
     
        'Add the row to the sheet data
        sheetData.AppendChild(row)
    Next
     
    'Create the sheets
    Dim sheets As Sheets = workbookPart.Workbook.AppendChild(New Sheets())
    Dim sheet As Sheet = New Sheet() With {.Id = workbookPart.GetIdOfPart(worksheetPart), .SheetId = 1, .Name = "Data"}
    sheets.Append(sheet)
    'Save changes
    workbookPart.Workbook.Save()
    End Using
     
    End Sub
     
    #End Region