合并相同表结构的多个Excel文件（.csv、.xlsx）

原创

Windows技术交流

修改于 2024-05-03 18:25:13

1.6K0

文章被收录于专栏：Windows技术交流Windows技术交流

1、合并相同表结构的多个.csv文件

首先新建一个目录，把相同表结构的多个.csv文件放到这个目录

然后打开cmd

cd /d ".csv文件所在目录绝对路径"
copy *.csv merged.csv

2、合并相同表结构的多个.xlsx文件（替换下目录路径为自己的）

Set-executionpolicy -ExecutionPolicy Unrestricted -Scope CurrentUser -Force;

[System.Net.ServicePointManager]::SecurityProtocol.HasFlag([Net.SecurityProtocolType]::Tls12)

if(([System.Net.ServicePointManager]::SecurityProtocol.HasFlag([Net.SecurityProtocolType]::Tls12)) -eq $false){
[Net.ServicePointManager]::SecurityProtocol = [Net.ServicePointManager]::SecurityProtocol -bor [Net.SecurityProtocolType]::Tls12
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
[System.Net.ServicePointManager]::SecurityProtocol = [System.Net.SecurityProtocolType]::Tls12
}

[System.Net.ServicePointManager]::SecurityProtocol.HasFlag([Net.SecurityProtocolType]::Tls12)

Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force

Install-Module -Name ImportExcel -Scope CurrentUser

#上面那些powershell是为这句做铺垫，如果没有上面的，会报下图的错
Import-Module ImportExcel

$sourceFolder = "G:\hebing"
$destinationFile = "G:\hebing\merged.xlsx"

$files = Get-ChildItem -Path $sourceFolder -Filter "*.xlsx"


$mergedData = @()


foreach ($file in $files) {
    $data = Import-Excel -Path $file.FullName
    $mergedData += $data
}


$mergedData | Export-Excel -Path $destinationFile -WorksheetName "Merged Data" -AutoSize