Excel

Incoming / Outgoing Excel

The Excel Transports must be routed to and from.  E.g. Excel files are received via Email, FTP, etc and then routed to the Excel transport for further processing.

Crossfire supports both xlsx and xls formats. 

Transport Options

Data Source: Required for outbound only.   This is the temporary path where the file is saved to before it is sent.   

Table Name: This is the name of the WorkSheet within the Excel file which is to be written to or read from. 

You can also specify excel range names in here using the following format: “SheetName$A1:C99”

This allows you to specify the area on the worksheet to be read. Otherwise excel will automatically establish the bounds of the worksheet using the data which has been entered.

The transport options for importing files.

• • "*" in the Table Name field will import the first sheet - hidden or otherwise

  • "Active" in the Table Name field will import the active sheet. This is the same sheet that will be shown by default when opening the XML or XLSX file in MS Excel

  • "[sheetname]" in the Table name field will try to find the sheet named and process it.

Create New Table (Outbound Only): If the WorkSheet specified in the Table Name field does not exist, it will create a new WorkSheet with this name.

Notes

When Crossfire reads Excel cells, if they contain a function (e.g VLOOKUP), then it will try to evaluate that function and use the result of the function as the value it reads.   If the function cannot be run (e.g. the VLOOKUP value doesn't exist) then it will use the text which is in the cell, which is typically the function text. 

New Sheet feature for Outgoing Excel

Please follow the steps below if you want the Excel file to have multiple sheets:

1. Add a new field in the Excel Message Standard 

   • Create a field with the name   (just a blank space).

   • This field can be placed at the start or end of a line. In the example below, it is placed at the start of the line.

2. Add a tag to specify sheets

   • Assign a function (or any other mapping type) that outputs the tag in the format ##STARTSHEET##sheet_name##ENDSHEET## in the newly created field in the Process.

   • This tag will be used to separate the sheets.

   • Replace sheet_name with the name you want for the new sheet.

   • Lines following this tag will appear in the specified sheet until a new tag or the end of the file.

   • If the sheet_name is not specified, it will use the sheet name from the transport setting.

   • Duplicate sheet_name values will automatically append a number at the end. E.g: Test, Test1, Test2, Test3.

Check the example below with the explanation.

In the images below, left is the input and right is the output file (Excel file).

 ,Name,Age,Speed - The first column is the "space" field, so all the tags will appear at the start of each line.

##STARTSHEET##Boys##ENDSHEET## - This will start a new sheet with the sheet name "Boys", and all the lines after this tag will fall under this sheet until a new tag appears. So the sheet "Boys" will have the lines Nick, Tim and Tom.

##STARTSHEET##Girls##ENDSHEET## - This will start a new sheet with the sheet name "Girls", and all the lines after this tag will fall under this sheet until a new tag appears or EOF. So the sheet "Girls" will have the lines May and June.