Skip to main content
  • EN
Hi All,

 

 

I want to place the data in specific column+row like D3, C5, F8...

 

 

I have mapped the column and row to attributes where I want for each record. I'm able to place to the data in required ROW with the help of "xlsx_row_id" and Writer mode to "Update Cell Contents".

 

 

Could any one please guide me how to add the column number to Excel (writer)...

 

 

I have atatched the output in inspector for reference...

 

 

 

 

I want to place value of CNT  in respective cell

 

 

Pratap

 

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..

Reply


Community Stats

30,108
Posts
114,295
Replies
37,743
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.