Hello, is it possible to set the cell/column width in a dynamic Excel Writer. I can imagine to precalculate the width for each column or to use a auto mode. Hendrik
Solved
Set cell width in dynamic Excel writer
Best answer by helmoet
Hi @hendrik @xiaomengatsafe @gio @krisvesweco , I faced the same problem writing a dynamic feature type on an Excel writer, and this is the solution I came up with:
The warnings do not show up anymore and Excel is generating correct column widths. No post processing needed.
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
+15- Contributor
if you have the python module
"xlsxwriter" then you can use the PythonCaller.
Its easy, look for examples on stack exchange (for instance)
Hi gio,
i could use this to edit the Excel file in a post process. In my opinion this isn't the optimal solution but i assume that there is no direkt FME function.
Hendrik
+15- Contributor
There is no fme solution, though I think they could provide it.
But you can use the pythoncaller with the xlsxwriter python module which you can download to do it.
I fail to see why this would invoke negative reactions...that's all. (thanks for the deduction in negatives though..;)
You can also use windows api to make the columns fit the content hwne writing, also using python (or tcl, or java)
+11- Safer
Did you find a better solution rather than postprocessing?
Hi @krisvewsp, than you for your interest in this scenario. This other post has a potential solution suggested by David_r (April 12th). Although it might be a slow translation, if you are working with a large dataset. There is also an idea tracking the interest for an easier solution for this problem. Please add your vote and comment to let us know of your interest! Thank you!
+4- Contributor
Hi @krisvewsp, than you for your interest in this scenario. This other post has a potential solution suggested by David_r (April 12th). Although it might be a slow translation, if you are working with a large dataset. There is also an idea tracking the interest for an easier solution for this problem. Please add your vote and comment to let us know of your interest! Thank you!
Thanks! Not sure how that would help setting the column width's in the excel writer? In the log I can see that I get errors like this:
Excel Writer: Invalid column width 'width' specified for column 'M2_RevAvser5' of sheet 'd_y_n'. Value must be numeric. Using Excel default column widthIs there an existing column attribute to specify the column width for the excel writer?
+11- Safer
Thanks! Not sure how that would help setting the column width's in the excel writer? In the log I can see that I get errors like this:
Excel Writer: Invalid column width 'width' specified for column 'M2_RevAvser5' of sheet 'd_y_n'. Value must be numeric. Using Excel default column widthIs there an existing column attribute to specify the column width for the excel writer?
Hi @krisvewsp, my initial idea was that david_r's method can be used in combination with a dynamic workflow where destination schema is derived from a schema feature. This method in theory should allow us to take the max string length and set the schema feature with that number. e.g. fme_data_type fme_varchar(14), where 14 is the result of the max string length calculation.
But in my test, this didn't seem to change how wide the column that displays in MS Excel. I still needed to double click on the column boarder to have it fit the width of the text in that column. So, it didn't work as I expected.
I'm out of ideas at the moment...
+8Hi @hendrik @xiaomengatsafe @gio @krisvesweco , I faced the same problem writing a dynamic feature type on an Excel writer, and this is the solution I came up with:
The warnings do not show up anymore and Excel is generating correct column widths. No post processing needed.
+18- Contributor
Hi @hendrik @xiaomengatsafe @gio @krisvesweco , I faced the same problem writing a dynamic feature type on an Excel writer, and this is the solution I came up with:
The warnings do not show up anymore and Excel is generating correct column widths. No post processing needed.
This works great! But depending on your language setting of Excel its either ; or , in the "width,x" part (Dutch is ; while English Excel is ,) the way it works in your functions in Excel
