Skip to main content
  • EN
  • Home
  • Forums
  • FME Form
  • Data
  • Combine two Excel files with multiple worksheets into a single Excel file and preserve the formatting on each worksheet

Hi FME experts,

I have two Excel files. One has 4 worksheets and the other one has just 1 worksheet. I want to combine the two Excel files into a single file, basically copying the 1 worksheet in my second Excel file and paste it into the first Excel file while preserving the formatting on each worksheet (I can combine the two Excel files into one, but just couldn't preserve the formatting on the worksheet from the second Excel file). The desired result should be a single Excel file with 5 worksheets. And each of the worksheet should have its original sheet name and formatting.

Here are the two sample Excel files if anybody wants to give it a shot.

Thanks in advance!

Henry

Hi @sunhenry617,

One way to accomplish this is to read in both Excel datasets as Single Merged Feature Types then add an Excel Writer to the workspace.

Connect the Readers to the Writer and set the Sheet Name to fme_feature_type in the Excel Writer Parameters then run the workspace.

I've attached a sample workspace using your data.

Hope this helps.

- Chris

xlsx-example.fmwt

Hi @sunhenry617,

This can also be done using a FeatureWriter transformer, by having each sheet as an individual input, the transformer should pick up the sheet name but if not you can open the parameter editor to set this up. See image below for what this looks like:

Also please see the final file here - mergeexcel.xlsx

Hi @sunhenry617,

This can also be done using a FeatureWriter transformer, by having each sheet as an individual input, the transformer should pick up the sheet name but if not you can open the parameter editor to set this up. See image below for what this looks like:

Also please see the final file here - mergeexcel.xlsx

Thanks @hollyatsafe for your quick response. This is still not quite what I wanted. I want the field names to be bold as in the original Excel files. I was able to keep that for the 4 sheets from file a.xlsx in the result, but just couldn't do that for the 1 sheet from file b.xlsx.

 

 

Henry

 

Hi @sunhenry617,

One way to accomplish this is to read in both Excel datasets as Single Merged Feature Types then add an Excel Writer to the workspace.

Connect the Readers to the Writer and set the Sheet Name to fme_feature_type in the Excel Writer Parameters then run the workspace.

I've attached a sample workspace using your data.

Hope this helps.

- Chris

xlsx-example.fmwt

Thanks @ChrisAtSafe for your quick response. I am using FME 2016, so the workspace you sent to me is not quite compatible with my version. Earlier I had tried to use "fme_feature_type" as sheet name, but could not preserve all the formatting from the original sheets such as bold field names. If you can do that, please send me a copy of the result you got.

 

 

Thanks,

 

 

Henry

 

Hi @sunhenry617,

This can also be done using a FeatureWriter transformer, by having each sheet as an individual input, the transformer should pick up the sheet name but if not you can open the parameter editor to set this up. See image below for what this looks like:

Also please see the final file here - mergeexcel.xlsx

Thank you so much @hollyatsafe! This is what I needed and it turns out that FME 2016 (the version I am using) doesn't have the "Read formatting" option. But still, it is good to know the function is there for me when I get the new version of FME.

 

 

Thanks again for your help!

 

 

Henry

 

Reply


Community Stats

30,514
Posts
115,742
Replies
38,181
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.