Question

Workflow to export data to an Excel document/template

Workflow Version 10

Updated May 4, 2020

asked on October 5, 2018

Hello,

I have a workflow that exports metadata based on entry search results to a CSV file. I'm wondering if it's possible to do a similar thing but export to an Excel file where I could pre-define a template so that the resulting file is more user-friendly in its format. We're on version 10.2.

Thanks,

Julie

0 0

Answer

SELECTED ANSWER

replied on October 5, 2018 • Show version history

This is what I do,

Select or create a folder on a drive the Workflow server and service account can access (we use a D drive on the Workflow server itself)
Create 2 Excel files in the folder, something like Spreadsheet and Spreadsheet_Template
In Windows ODBC, create a read/write ODBC connection to Spreadsheet (you may need to install the ODBC drivers for Excel if the server doesn't have them yet).
In your Workflow, create a data source that connects to the ODBC connection for your file
Add a Script activity (doesn't need to be SDK Script) at the start of your workflow that overwrites the Spreadsheet file with a copy of the Spreadsheet_Template file (this ensures you have a clean slate each time the process runs)
Use an Insert Data activity to add data to the spreadsheet/data source
Use a Script activity to copy the completed file to your target location (I use a placeholder document in the repository, add the excel file to it using an SDK Script, then email the document using workflow activities).

Limitations: This would have to be a one-at-a-time process because ODBC will only allow a single connection. For example, if you try to run the workflow while you have the spreadsheet open it will fail to connect to the data source.

1 0

View 1 previous reply

replied on October 9, 2018

Hey Jason,

Thanks for the response. I have done what you said; but have a question. In the template file, I setup the headers to have a nice format (e.g., bold text, light blue background). But when I use the Insert Data activity, it inserts data rows with the same formatting as the header. Have you run into this and is there a way to have it insert rows not using the header format?

Thanks,
Julie

0 0

replied on October 9, 2018

Do you have the header configured as a filter/data header row in Excel? I don’t use coloring, but I do have the headers in bold with borders and I haven’t had any issues.

1 0

replied on October 10, 2018

That did it! Thanks so much Jason.

Julie

0 0

replied on May 4, 2020

Hi Jason,

Interested in trying your steps out on one of our processes. Newbie to WF, what would the WF look like? Not familiar with scripting, what would the script look like for the 2 points you have:

- Add a Script activity (doesn't need to be SDK Script) at the start of your workflow that overwrites the Spreadsheet file with a copy of the Spreadsheet_Template file (this ensures you have a clean slate each time the process runs)

- Use a Script activity to copy the completed file to your target location (I use a placeholder document in the repository, add the excel file to it using an SDK Script, then email the document using workflow activities).

Any help is much appreciated!

Thank you,

~Jo Anne

0 0

Replies

replied on October 5, 2018

To do it directly in Workflow, you'd have to do your export in a Script activity using the Open XML SDK to directly manipulate the Excel file. I've also had success using the Closed XML library to make working with Open XML a little easier.

Another idea to throw out there is to use a regular reporting solution such as SSRS. You could cache the metadata in a table somewhere, and then build a report that looks the way you want. Then you can email the user a link to the report, with the default set to export as an Excel file.

1 0

replied on October 9, 2018

Thanks for the response Devin. This is very helpful; and I wish I could mark two responses as an Answer but I can't. Since I'm using the solution from the other response, I'm marking that as the answer.

Thanks again,

Julie

1 0

replied on October 9, 2018

No worries! I'm a developer, so my answers are always going to be biased to that side of things.

1 0

replied on October 9, 2018

I'm actually a developer too; so may end up going your route if I don't get the desired result from the Insert Data activity :-).

Thanks,

Julie

0 0

You are not allowed to follow up in this post.

Question

Question

Workflow to export data to an Excel document/template

Answer

Replies

Sign in to reply to this post.