Hi everyone,
I was wondering if anyone knows a way to open a table as a spreadsheet in a dashboard, allowing the user to create, remove, and edit the rows as a traditional spreadsheet.
I already use the SQL table editor for updating tables, but it's not very user friendly, especially when the user has to add many rows.
Any help is appreaciated!
OneStream EmployeeWe used OpenXml (DocumentFormat.OpenXml.dll) to handle this, and it works very well — though it took some effort to build. See the screenshot below: the list in the background is our SQL table editor, and the list in the foreground is the same data rendered in the spreadsheet component. Users can create, remove, and edit rows in the spreadsheet and then import the changes back into the database.
Our process is:
1. Read the SQL table
2. Generate an Excel file using OpenXML
3. Store the file in the application’s documents/users folder
4. Link the file to the spreadsheet component
When importing, we reverse the flow: save the spreadsheet back to the documents/users folder, open and read it with OpenXML, and write the changes to the database.
If you'd like the full walkthrough, feel free to email me at chriskenworthy@revfore.com — happy to share the details.
We also incorporated this pattern into our OneStream partner solution, Revfore Flex, which simplifies planning with relational data. For more information visit: www.revfore.com.
