Use powershell to merge many excel files as one

in utopian-io •  2 years ago  (edited)

What Will I Learn?

  • How to use powershell to merge many excel files as one excel file

    GIF.gif

Requirements

  • Windows or other system like linux,macos

  • Powershell

    图片.png

  • Coding IDE

    图片.png

  • .NET framework

Difficulty

  • Intermediate

Tutorial Contents

Before contents:

If you do not know any about powershell ,you had better reading this post firstly as well as consulting more from the books

some powershell foundation


Create excel application object

$ExcelObject=New-Object -ComObject excel.application
$ExcelObject.visible=$true

图片.png

  • New-Object : Create new excel application com object
  • visible : Set it visible so we can see the running process

Read the excel files

$ExcelFiles=Get-ChildItem -Path C:\Users\Administrator\Desktop\excel

图片.png

  • Get-ChildItem: Get all the files in the folder
  • Path : Set the folder path to read

Show all the excel files in the folder

foreach($ExcelFile in $ExcelFiles){
 
$ExcelFile.FullName

 
}

图片.png

  • FullName :Get the files full name ,which contains the url path and file name


Create Workbookin the excel application to save the data

$Workbook=$ExcelObject.Workbooks.add()
$Worksheet=$Workbook.Sheets.Item("Sheet1")

图片.png

  • Workbook : Add new workbook to write data

  • Sheets : Among the three sheets ,just use the first one worksheet


Run a foreach function to load all the excel files

foreach($ExcelFile in $ExcelFiles){
 
$Everyexcel=$ExcelObject.Workbooks.Open($ExcelFile.FullName)
$Everysheet=$Everyexcel.sheets.item(1)
$Everysheet.Copy($Worksheet)
$Everyexcel.Close()
 
}

图片.png

  • ExcelFile :Get every excel file in the folder
  • Copy :Copy every excel file data to our workbook
  • Close : After copying ,close the excel file

Save our excel workbook as a excel file then quit running

$Workbook.SaveAs("C:\Users\Administrator\Desktop\excel\merge.xlsx")
$ExcelObject.Quit()

图片.png

  • SaveAs: Save the excel application workbook to a excel file

The whole program

$ExcelObject=New-Object -ComObject excel.application
$ExcelObject.visible=$true
$ExcelFiles=Get-ChildItem -Path C:\Users\Administrator\Desktop\excel

$Workbook=$ExcelObject.Workbooks.add()
$Worksheet=$Workbook.Sheets.Item("Sheet1")

foreach($ExcelFile in $ExcelFiles){
 
$Everyexcel=$ExcelObject.Workbooks.Open($ExcelFile.FullName)
$Everysheet=$Everyexcel.sheets.item(1)
$Everysheet.Copy($Worksheet)
$Everyexcel.Close()
 
}
$Workbook.SaveAs("C:\Users\Administrator\Desktop\excel\merge.xlsx")
$ExcelObject.Quit()


Check the effect of the program

GIF.gif



Posted on Utopian.io - Rewarding Open Source Contributors

Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
Sort Order:  

Thank you for the contribution. It has been approved.

You can contact us on Discord.
[utopian-moderator]

Thanks @deathwing.i really hope to join your team .

Hey @cha0s0000 I am @utopian-io. I have just upvoted you!

Achievements

  • You have less than 500 followers. Just gave you a gift to help you succeed!
  • Seems like you contribute quite often. AMAZING!

Suggestions

  • Contribute more often to get higher and higher rewards. I wish to see you often!
  • Work on your followers to increase the votes/rewards. I follow what humans do and my vote is mainly based on that. Good luck!

Get Noticed!

  • Did you know project owners can manually vote with their own voting power or by voting power delegated to their projects? Ask the project owner to review your contributions!

Community-Driven Witness!

I am the first and only Steem Community-Driven Witness. Participate on Discord. Lets GROW TOGETHER!

mooncryption-utopian-witness-gif

Up-vote this comment to grow my power and help Open Source contributions like this one. Want to chat? Join me on Discord https://discord.gg/Pc8HG9x

Great guide will definitely be using this, Powershell saves so much time!