Skip to main content
  • EN
  • Home
  • Forums
  • FME Form
  • Data
  • How to write in a Excel file a listing with a specific format according to the data of a polygon?

Hello,

 

 

I Have a dwg reader. I use a lot of transformer to have a an excel file which contains a list of points (Point) that form polygons (PARCELLE N°). I write the list like on the Print Screen 1 but it's not the result i would like.

 

 

I want to have for each polygons a list of points and like in the Print Screen 2 where I write Parcelle N° in a cell and Surface in Another and below the list. The Polygons must be sorted PARCELLE N°.

 

 

I must repeat the format for each polygons.

 

 

The number of polygons and points is variable depending on the source dwg file.

 

 

Can you help to do this or give me some tips?

 

I think I must use some python code to solve question.

 

 

I attach 3 excels files :

 

 

- listing_surface_template.xlsx which is the templace I use.

 

- listing_surfaces_exemple.xlsx which is the file I obtain with my workspace (Print Screen 1)

 

- listing_surfaces_exemple_final.xlsx which is the file with the desired format (Print Screen 2).

 

 

Print Screeen 1

 

 

Print Screen 2

As you've probably surmised, you can't just write different feature types to the Excel writer because you'd end up with multiple worksheets.

You could use an Aggregator or ListBuilder to aggregate your features by parcelle, split by parcelle header rows, populate and clear attributes matching those defined on the Excel writer feature type, write the header rows in the order you want, then extract and write your point detail features. Then process the next aggregated parcelle the same way. I've glossed over a few details, but it should be possible to do what you want, all on the same worksheet using one feature type.

@bernarddinon I think the only way to accomplish this is to use named ranges in an Excel template. This would mean creating a report sheet with named ranges and then three 'data' sheets where you load the three sets of data. Ideas on using Excel templates and named ranges are discussed in this article: Using a Template File when Writing Excel Data

@bernarddinon I think the only way to accomplish this is to use named ranges in an Excel template. This would mean creating a report sheet with named ranges and then three 'data' sheets where you load the three sets of data. Ideas on using Excel templates and named ranges are discussed in this article: Using a Template File when Writing Excel Data

Thank for yoyr answer @markatsafe.

 

 

I Will try your proposition next Week. I'm going to tell you if it's ok.

As you've probably surmised, you can't just write different feature types to the Excel writer because you'd end up with multiple worksheets.

You could use an Aggregator or ListBuilder to aggregate your features by parcelle, split by parcelle header rows, populate and clear attributes matching those defined on the Excel writer feature type, write the header rows in the order you want, then extract and write your point detail features. Then process the next aggregated parcelle the same way. I've glossed over a few details, but it should be possible to do what you want, all on the same worksheet using one feature type.

@kweller , thank for your answer. I will try it also next Week. I'm going to tell you if it's works :)

 

@bernarddinon I think the only way to accomplish this is to use named ranges in an Excel template. This would mean creating a report sheet with named ranges and then three 'data' sheets where you load the three sets of data. Ideas on using Excel templates and named ranges are discussed in this article: Using a Template File when Writing Excel Data

@markatsafe :

 

Hello,

 

 

I just looked at your solution. I think we have a incomprehension.

 

I have one set of data from a dwg file. The set of data can contain one or several "surfaces" with a number of points which it's different for each surface.

 

 

The purpose is to have one sheet with all the data.

 

 

Best Regards

@kweller

 

 

Sorry for my answer after so long.

 

 

For the moment in the workspace I Write the list of point with a feature writer (1) and the header with the general values with a writer (2).

 

 

I try your solution with a ListBuilder. I have all the data (3) ready to write. I can see to write the data for one parcelle, but I don't see how to do for several parcelle with tranformers or writer to calculate the position of the data by parcelle in the sheet.

 

 

Do you think i must use python to solve this point?

 

 

Best Regards

 

 

@markatsafe :

 

Hello,

 

 

I just looked at your solution. I think we have a incomprehension.

 

I have one set of data from a dwg file. The set of data can contain one or several "surfaces" with a number of points which it's different for each surface.

 

 

The purpose is to have one sheet with all the data.

 

 

Best Regards

@bernarddinon If that is the case then I don't think that would be easy, or perhaps not possible, in FME. You'd probably have to use an Excel Macro or script to move the data into tables for each surface.

@kweller

 

 

Sorry for my answer after so long.

 

 

For the moment in the workspace I Write the list of point with a feature writer (1) and the header with the general values with a writer (2).

 

 

I try your solution with a ListBuilder. I have all the data (3) ready to write. I can see to write the data for one parcelle, but I don't see how to do for several parcelle with tranformers or writer to calculate the position of the data by parcelle in the sheet.

 

 

Do you think i must use python to solve this point?

 

 

Best Regards

 

 

I could do most if not all of it in FME, but it would require more transformers and a more complex workflow than what you have in your screen shot.

@bernarddinon If that is the case then I don't think that would be easy, or perhaps not possible, in FME. You'd probably have to use an Excel Macro or script to move the data into tables for each surface.

@markatsafe

 

Thank. I think it's complicate like you said. I think I must have a script.

 

 

Thank you.

I could do most if not all of it in FME, but it would require more transformers and a more complex workflow than what you have in your screen shot.

@kweller : Thank. I know i need more transformers and workflow in the part 3 of the screen.

 

 

I see what what I must with transformers and split the date but not how to write in Excel for several polygon. I will invesigate this point with some new test.

Reply


Community Stats

30,117
Posts
114,317
Replies
37,748
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.