Skip to main content
Question

Excel Writer into Sharepoint - Auto Fit Row Height?

epro_admin
Contributor
Forum|alt.badge.img+7

I have a workspace set up to write into an Excel spreadsheet in Sharepoint. I am able to control the cell width in the writer parameters to a designated number.

 

I also want to set the cell height to auto fit because I have the text set to wrap, but some entries have a lot more text than others. When there's longer text entries some of the text gets hidden until you double click the cell. Is this possible in the FME Excel Writer?

 

If I were to manually do it in Excel this is the step I would take.

image

3 replies

kailinatsafe
Safer
Forum|alt.badge.img+19

Hello @epro_admin​, thanks for posting and sorry for not reaching out sooner! I believe the only way to control cell height using FME is with a template file. I was optomistic, but I came across a snippet in the following article (that discusses writing images to excel):

 

"(6) Add the Excel Writer: In the Writer Parameter dialog set Overwrite Existing File = Yes. Set the template file to be FireHallsTemplate.xlsx. We use a template file that has the row heights preset. Otherwise the images will overlap."

 

Unless you're able to use the formatting options on Excel Writer > User Attributes > Formatting > Edit... > Text Control, exploring the template option could be a good alternative:

 

ExcelFormattingOptions 

Further, I haven't seen any FME Ideas for controlling cell height in FME. If this is functionality you would like to see added to FME in the future, please consider creating an idea. If you create an FME Idea, feel free to link it for other users to find! Happy to help, Kailin.

warrendev
Enthusiast
Forum|alt.badge.img+23
  • warrendevEnthusiast
  • Enthusiast
  • August 9, 2023
kailinatsafe wrote:

Hello @epro_admin​, thanks for posting and sorry for not reaching out sooner! I believe the only way to control cell height using FME is with a template file. I was optomistic, but I came across a snippet in the following article (that discusses writing images to excel):

 

"(6) Add the Excel Writer: In the Writer Parameter dialog set Overwrite Existing File = Yes. Set the template file to be FireHallsTemplate.xlsx. We use a template file that has the row heights preset. Otherwise the images will overlap."

 

Unless you're able to use the formatting options on Excel Writer > User Attributes > Formatting > Edit... > Text Control, exploring the template option could be a good alternative:

 

ExcelFormattingOptions 

Further, I haven't seen any FME Ideas for controlling cell height in FME. If this is functionality you would like to see added to FME in the future, please consider creating an idea. If you create an FME Idea, feel free to link it for other users to find! Happy to help, Kailin.

One idea would be to have a VBA macro within the Excel template so that it will run on open when the Excel file is written. Here is a link to one that might be of use.

Automatically autofit row heights for columns where the text wraps when loading an Excel sheet? - Microsoft Q&A

epro_admin
Contributor
Forum|alt.badge.img+7
  • epro_adminContributor
  • Author
  • Contributor
  • August 23, 2023

@kailinatsafe​ and @Chris Warren​ - thank you both for your replies. I am neither an FME or VBA macro expert, but I will give these a try anyway! If anyone else has any other ideas keen to hear. Thanks again for your time.

Reply


Helpful Members This Week

Community Stats

30,909
Posts
117,110
Replies
38,708
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

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