Write Data to an Excel File

I had the opportunity today to respond to a post on the Symantec Workflow forums, here, regarding writing data to an Excel file.  Here’s a quick go-to on how to do it.

Let’s have a look at two different scenarios.  The first being that there’s a file that’s being output to a specific location (like a log file), and the second, that a user is downloading report data to an excel file that will be saved to their default download directory.

Both scenarios are illustrated in the demo project for this post, and require the import of the LogicBase.Components.Office.dll library (Microsoft Office).

*Note that this method can also be applied to a number of other scenarios, but an important third scenario would be sending an Excel file via the Send Email component as the file attachment.


In the first scenario, in which we’re writing a file to a specific location, we’re going to make use of the Excel Write Component.  There are two of these files; one in the Automation directory and another in the Native directory.  Warnings fire when you pull anything from the Automation directory, so let’s stick to the Native version.

2015-01-27_17-23-09

For this demo we’re going to use a simple Key Value Pair array as our output data.

Initialize a Key Value Pair array in an Initialize Data component and give it a few values.

2015-01-27_20-29-50

Next, in the “Input Type” field of the Excel component, select Key Value Pair.  Use the array you added to the Initialize Data component in the “Input Data” field.  The input data fields can be toggled on and off, depending on whether you want the column included in the Excel file.

2015-01-27_20-31-06

Give the component an “Output File Name” (in this context, this value is arbitrary) and “Output File Variable Name”, and you’re ready to move on.

2015-01-27_20-32-28

Next, we’re going to use a Write File component to write the Excel data to a file.  In this demo, we’re going to use a Full Path and write to a test directory located at C:\Test (this directory must already exist).   Select the “File Variable Name” variable (this will be the “Output File Name” variable from the previous Excel component).  The “Output File Name” field must include the actual file name itself.  This value will overwrite the “Output File Name” field from the Excel file (rendering that value arbitrary, as mentioned above).  So the value of this field will be something like “C:\Test\excelfile.xls”.

2015-01-27_20-33-08

This will work as a functional test for our first scenario.  Run it and the file should be written to the directory entered in the last field above.


For the second scenario, we’re assuming a user will be wanting to download a report in an Excel-formatted file.  For this, we will use a Button Download component with a dynamic file source model.  For the “File Data” field, select “Dynamic Model” as the value source.

2015-01-27_20-34-12

Inside this model, use an Excel Write Component just like in the first scenario, above.  The only difference here is that, because we have no Write File component, the “Output File Name” variable is no longer arbitrary, and will be used to name the file that is being downloaded.

2015-01-27_20-34-41

Configure the End component of this submodel with the output variable of the Excel component.

2015-01-27_20-34-56

When a user clicks the button, the excel-formatted data will be downloaded and saved as an .xls file in the user’s default downloads folder.


Featured Components

Excel Write Component

Initialize Data

Write File

Button Download

End


4 thoughts on “Write Data to an Excel File

  1. Hi Mike – I think the easiest way to approach this is to put together a webex to have a look at your project. Send me an email if you want to do this – the email is on the Info page of this blog.

    Like

  2. Hi! Any ready solutions on how to read/write xlsx format? Symantec does not seem to have that as any priority at the moment. At this time, when someone tries to import an .xlsx into our workflow, the workflow calls a 3rd party EXE that converts the .xlsx file and then reads the converted .xls into the workflod instead… .xlsx:s are basicly zips and xml, so we could also hack those open ourselves, but which way would be the smartests….

    Like

      1. At this time we receive .XLSX files via email to a mailbox that we monitor. A monitoring workflow then downloads all unprocessed (unread) attachments into a temp folder and adds them into a list of “processed attachements” in our database so we don’t download them again. Then the monitoring workflow calls the “Execute Process (Run)” component to run a freeware command line utility (EXE) that converts all .XLSX files in the temp folder into .XLS files. After that the monitoring workflow scans the folder for .XLS files, reads them and can finally do something with the Excel contents.

        This works, but i’ve been looking for a solution without the middle phase of converting files outside of workflow.

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s