span8
span4
span8
span4
This example expands on the articleOverview of Excel Writer Parameters.In that example you learnt how to write some weather data stored in a CSV file to an Excel Spreadsheet.We're going to expand on that example.
What if you had taken the weather data results and,using Excel,you carried out some analysis of the data: added some additional worksheets with charts,data summaries and pivot tables:
After doing all this work you obtain data for a different city or,in this case,a new CSV file with weather data from 1940-2012 instead of 2005-2009 for the original data.Using FME,you can rewrite the RawData worksheet,and the results will be reflected in the charts,summaries and pivot table you built.This is how you use anExcel Template Filein the FME Excel writer.
The Excel Template File used by FME is any Excel file that has a consistent location where you can write the raw data.The easiest location to use is a named range.In the example below,we'll be using a template file called WeatherDataTemplate.xlsx,which is packaged up in the attached Workspace Template.The data location is on the RawData sheet in a named range calledWeatherTable.All the other sheets in the Excel file,Summary&Chart,Pivot and PivotTable reference the named range.
1)Start FME Workbench,and generate a new workspace.
2)Choose CSV format for the reader,and select "Vancouver Weather Data1940-2012.csv" as the source dataset.
3)Choose Microsoft Excel as the writer,and select a name and location for the new file that will be created.
4)The writer that appears on the canvas will create a new worksheet in our Excel Spreadsheet called CSV.Set the writer's General Parameters Sheet Name to "RawData/WeatherTable".This means you'll be writing to the named range called WeatherTable in the RawData sheet.You can write to multiple named ranges in the same sheet.
5)Edit the User Attributes and add the CUMULATIVE attribute to the list of attributes.
6)In the Format Parameters tab set the following values:
By setting the "Truncate Existing Sheet/Named Range" parameter to "Yes",FME will drop the Raw Data WeatherTable named range and recreate it with the new data.
We're also going to create a formula to calculate the cumulative precipitation for each year.For the first row of data the Excel formula will look like: =SUM(C2:N2)
7)Add the Counter transformer and set the Count Output Attribute = _row.Set the Count Start = 2.The data starts on row 2 because the headers are on row 1.
8)Add the AttributeManager transformer.Set the Output Attribute "CUMULATIVE.formula" and,using the Text Editor,set the Attribute Value : SUM(C@Value(_row):N@Value(_row)).For each data row,the Excel formula will be created.
Although the CUMULATIVE attribute on the Excel Writer Feature Type is red,the Excel writer will populate it with the formulas from CUMULATIVE.formula.Refer to the section on Formulas in theExcel reader/writer documentation.
You workspace will look something like:
9)Run the workspace and check the results.Your RawData sheet should be populated with the new data and the charts and pivot tables automatically updated by Excel
How to Convert CSV to Microsoft Excel
How to Convert Microsoft Excel to Esri Shapefile
Advanced manipulation of Excel tables using AttributeExploder
Converting multi-line records into single features
Example scripts for deleting Excel files prior to writing (FME 2013 SP1 and earlier)
Pivoting Between Columns,Lists and Records
© 2019 亚搏在线Safe Software Inc |Legal