How To Automate Bulk Excel File Generation with PowerShell: A Step-by-Step Guide

In today’s data-driven world, efficiently generating multiple Excel files is crucial for reporting and analysis. Manually creating these files can be tedious and error-prone. This video demonstrates a PowerShell script that automates the process, generating 100 Excel files with a single click. Each file includes a header and sample data, showcasing how PowerShell can streamline repetitive tasks. This article will delve into the script’s details, from environment setup to code execution, providing a comprehensive guide for users of all levels. By following these steps, you can harness PowerShell’s capabilities to enhance productivity and reduce manual effort.

1. Setting Up the Environment and Installing Required Modules

Before running the script, ensure your system is properly configured. PowerShell comes pre-installed on Windows, but you may need to install additional modules. The video uses the ImportExcel module, which simplifies Excel operations without requiring Microsoft Excel. Here’s how to set up your environment:

1. Open PowerShell Console: Run PowerShell as an administrator to avoid permission issues.
2. Install the ImportExcel Module: Execute the command `Install-Module -Name ImportExcel -Scope CurrentUser -Force -AllowClobber`. This downloads and installs the module from the PowerShell Gallery, with `-Scope CurrentUser` ensuring it’s available for your account.
3. Verify Installation: Run `Get-Module -ListAvailable | Where-Object Name -eq “ImportExcel”` to confirm the module is installed. If it returns details like the version, you’re ready to proceed.
4. Summary: Proper environment setup is essential for script success. Installing the ImportExcel module ensures smooth execution and avoids common pitfalls.

2. Detailed Source Code

$OutputEncoding = [System.Text.Encoding]::UTF8

[Console]::OutputEncoding = [System.Text.Encoding]::UTF8

Import-Module ImportExcel -ErrorAction Stop


$excelCount = 100  # 要生成的 Excel 文件数量
$outputDir = Join-Path -Path "D:\Work\" -ChildPath "ExcelFiles"  # 文件输出目录（当前目录下的 ExcelFiles 文件夹）
$filePrefix = "Report_"  # 文件名前缀

# 2. 创建输出目录（若不存在）
if (-not (Test-Path -Path $outputDir)) {
    New-Item -Path $outputDir -ItemType Directory #| Out-Null
    Write-Host "已创建输出目录: $outputDir"
}

# 3. 循环生成 Excel 文件
for ($i = 1; $i -le $excelCount; $i++) {
    # 构建文件名（如 Report_001.xlsx、Report_002.xlsx，确保编号位数一致）
    $fileName = "{0}{1:D3}.xlsx" -f $filePrefix, $i
    $filePath = Join-Path -Path $outputDir -ChildPath $fileName

    # 生成简单的示例数据（可选，也可生成空 Excel）
    $data = [PSCustomObject]@{
        序号    = $i
        名称    = "项目$i"
        创建时间 = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
        状态    = "正常"
    }

    # Write-Host "文件保存路径：$filePath"  # 输出结果，看路径是否合理（比如是否有中文乱码、特殊符号）

    # 导出到 Excel（-PassThru 仅用于获取文件对象，可省略）
    $excel = $data | Export-Excel -Path $filePath -AutoSize -TableName "Sheet1" -PassThru

    # 必须手动保存！
    $excel.Save()
    $excel.Dispose()  # 释放 COM 资源，避免文件被锁定


    # 输出进度信息
    Write-Progress -Activity "生成 Excel 文件" -Status "已完成 $i/$excelCount" -PercentComplete ($i / $excelCount * 100)
    Write-Host "已生成: $fileName"
}

Write-Host "`n批量生成完成！所有文件已保存至: $outputDir"
Read-Host

3. Running the Script and Troubleshooting Common Issues

To execute the script, double-click the PowerShell file or run it in VS Code. The video shows a pop-up window generating files, but issues may arise. Here’s how to run it and address potential problems:

1. Execution Methods: Double-click the script or type its path in PowerShell. If it doesn’t run, check file associations or use `.\ScriptName.ps1` in the console.
2. Permission Settings: If scripts are blocked, run `Set-ExecutionPolicy RemoteSigned -Scope CurrentUser` to allow execution of local scripts.
3. Path Validation: Ensure the save path `D:\Work\ExcelFiles` is valid and accessible. The script creates the directory if missing, but avoid special characters in paths.
4. Module Issues: If ImportExcel isn’t loaded, reinstall it or use `Import-Module ImportExcel`. Restart PowerShell if necessary.
5. Performance Tips: For large batches, consider adding delays with `Start-Sleep -Seconds 1` or optimizing the loop to prevent system slowdown.
6. Running the script requires attention to permissions and paths. Using VS Code for debugging can help identify errors quickly, making the process more efficient.

4. Conclusion: Extending the Script and Further Learning

This script can be adapted for various scenarios, such as exporting data from databases or automating reports. For instance, modify the data source to use CSV files or APIs, or add formatting options. PowerShell’s versatility makes it a powerful tool for automation. Explore more features of the ImportExcel module, like chart creation or data import. As mentioned in the video, feel free to share questions in the comments for collaborative learning. By practicing and expanding on this foundation, you can automate more tasks and boost your workflow efficiency.

5. Demo Video

You can watch the following demo video by select the subtitle to your preferred subtitle language.