Data to Specific COLUMN+ROW in Excel

Hello, 

 

 

I think you just need to:

1. expose the xlsx_col_id and xlsx_row_id as format attributes on the writer
2. set your feature type name on the writer to be the same as your excel sheet name
3. add your CNT attribute as a user attribute

If that doesnt work, try renaming the CNT attribute to be the same as the column name or letter eg 'B'.

 

 

Owen

Thank Owen,

 

 

I have done the format attribute for ROW since the option is available in writer.

 

But for column I have added xlsx_offset_col and xlsx_col_id as attributes but it didnt worked out.

 

 

Do let me if any attributes / settings, if I have missed out...

 

 

As you have suggested the second option to change the column has been done as of now as it is urgent but it is big job and more manual intervention is required in coming days since the location of the CNT value is varies from data to data.

 

 

Pratap

There is no format attribute named "xlsx_col_id" as each row is a record, else i don't think this question would be posed.

 

 

You can create the attributes using an attributecreator.

 

Attrbiute name: @Value(XLSX_COL_ID) and Value: @Value(CNT)

 

 

Downside is, the attributes are now hidden and need to be exposed.

 

Someway you must make a schema to map, if you dont want to do manual exposure.

 

 

 

 

 

If you write at least the result as in your picture to an excel. You can use AttributeExposer to expose those attributes by importing this written excell.

 

Write out as excell, update and choose your XLSX_ROW_ID as row id attribute.

 

result:

 

 

 

 

Thank Gio,

 

 

I have just placed xlsx_col_id on "trial and error".

 

 

I have made caps of XLSX_COL_ID but result is not as required.

 

 

I dont think the problem is related to schema. The problem is related to the data which is not falling at the location required in output excel file.

 

 

Pratap

An excelrow is a record.

 

You must link the row_ids. 

 

 

The column attribute can then be chosen.

 

 

I see my prior pic forgot something.

 

 

But here it is.

 

 

You must expose your attributes, as i said before.

 

"XLXS_column_id" is not a format parameter.

 

 

But to get the columns like  A, B...A1,B1 etc.

 

Set "output filed names" to "NO"

 

and

 

set "Use Attribute Names As Column Positions" to "YES"

 

 

This way, You get the A,B,C,D,E  etc. columns.

 

ANd this correct result.

 

 

 

 

 

try it out.... ;)

 

 

 

 

 

...and of course "Writer Mode" to "UPDATE"

Updating a fille excel also works as it should:

 

 

 

 

Same workspace as above, just written to a randomly filled sheet.

..dang, wrong sheet...lol. soz.

..i forgot tho change featuretypename on writer..so it just added a sheet to the excell.

 

here is correctly updated sheet.

 

 

Thanks Gio,

 

 

May be this may work but I dont have option "Use Attribute Names As Column Positions" in my writer may me this is present in higher version.

 

 

Thank you.

 

Pratap

It is better, if the column id is also present just like row (xlsx_row_id) rather than making all these changes :)

This is done in fme 2014sp2.

 

The excelwriter for this version has these options. Though one has to figure out oneself how it works..lol!

 

 

Yes, that would be handy. But columns in Excel dont have id's so that would be difficult.

..playing around i see that the XLSXW2 writer does have a "xlsx_col_id" format attribute..

 

 

But strangely there is no way to invoke it at the writer as a format parameter

 

I'd say this writer needs rewriting..