本文目录导读：
我来介绍几种批量转换CSV为Excel的方法：
方法1：Python脚本（推荐）
使用pandas库
import pandas as pd
import os
from pathlib import Path
def csv_to_excel(input_folder, output_folder=None):
"""
批量转换CSV文件为Excel文件
"""
# 设置输出文件夹
if output_folder is None:
output_folder = os.path.join(input_folder, "excel_output")
# 创建输出文件夹
os.makedirs(output_folder, exist_ok=True)
# 获取所有CSV文件
csv_files = list(Path(input_folder).glob("*.csv"))
for csv_file in csv_files:
try:
# 读取CSV文件
df = pd.read_csv(csv_file, encoding='utf-8-sig')
# 生成输出文件名
excel_name = csv_file.stem + ".xlsx"
excel_path = os.path.join(output_folder, excel_name)
# 保存为Excel文件
df.to_excel(excel_path, index=False, engine='openpyxl')
print(f"转换成功: {csv_file.name} -> {excel_name}")
except Exception as e:
print(f"转换失败 {csv_file.name}: {str(e)}")
# 使用示例
csv_to_excel("你的CSV文件夹路径")
带更多选项的版本
import pandas as pd
import os
import glob
def csv_to_excel_advanced(input_pattern, **kwargs):
"""
高级转换，支持更多选项
"""
# 参数设置
output_folder = kwargs.get('output_folder', 'excel_output')
encoding = kwargs.get('encoding', 'utf-8-sig')
delimiter = kwargs.get('delimiter', ',')
os.makedirs(output_folder, exist_ok=True)
# 获取文件列表
csv_files = glob.glob(input_pattern)
for csv_file in csv_files:
try:
# 读取CSV
df = pd.read_csv(
csv_file,
encoding=encoding,
delimiter=delimiter
)
# 生成输出文件名
base_name = os.path.splitext(os.path.basename(csv_file))[0]
output_path = os.path.join(output_folder, f"{base_name}.xlsx")
# 保存Excel
with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
df.to_excel(writer, sheet_name='Sheet1', index=False)
# 自动调整列宽
worksheet = writer.sheets['Sheet1']
for column in df:
column_length = max(df[column].astype(str).map(len).max(), len(column))
col_idx = df.columns.get_loc(column)
worksheet.column_dimensions[chr(65 + col_idx)].width = min(column_length + 2, 50)
print(f"✓ 转换完成: {os.path.basename(csv_file)}")
except Exception as e:
print(f"✗ 转换失败 {csv_file}: {str(e)}")
# 使用示例
csv_to_excel_advanced(
"data/*.csv", # 通配符模式
output_folder="converted_excel",
encoding='gbk', # 如果是中文CSV，尝试gbk编码
delimiter=',' # 分隔符
)
方法2：批处理脚本（Windows）
PowerShell脚本
# 批量转换CSV为Excel的PowerShell脚本
# 需要安装Excel COM组件
# 设置路径
$csvFolder = "C:\CSV_Files"
$excelFolder = "C:\Excel_Output"
# 创建输出文件夹
New-Item -ItemType Directory -Force -Path $excelFolder
# 创建Excel对象
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
# 遍历所有CSV文件
Get-ChildItem -Path $csvFolder -Filter "*.csv" | ForEach-Object {
$csvFile = $_.FullName
$excelFile = Join-Path $excelFolder "$($_.BaseName).xlsx"
try {
# 打开CSV文件
$workbook = $excel.Workbooks.Open($csvFile)
# 保存为Excel
$workbook.SaveAs($excelFile, 51) # 51 = xlOpenXMLWorkbook
# 关闭工作簿
$workbook.Close()
Write-Host "转换完成: $($_.Name)"
}
catch {
Write-Host "转换失败: $($_.Name) - $_"
}
}
# 清理Excel对象
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
简化版PowerShell（无需Excel安装）
# 需要安装ImportExcel模块
Install-Module -Name ImportExcel -Scope CurrentUser
# 批量转换
$csvFolder = "C:\CSV_Files"
$excelFolder = "C:\Excel_Output"
Get-ChildItem -Path $csvFolder -Filter "*.csv" | ForEach-Object {
$csv = $_.FullName
$xlsx = Join-Path $excelFolder "$($_.BaseName).xlsx"
Import-Csv $csv | Export-Excel $xlsx
Write-Host "转换完成: $($_.Name)"
}
方法3：VBA脚本（Excel宏）
Sub BatchCSVtoExcel()
Dim csvFolder As String
Dim excelFolder As String
Dim csvFile As String
Dim wb As Workbook
Dim fileName As String
' 设置路径
csvFolder = "C:\CSV_Files\" ' 修改为你的路径
excelFolder = "C:\Excel_Output\"
' 确保输出文件夹存在
If Dir(excelFolder, vbDirectory) = "" Then
MkDir excelFolder
End If
' 查找CSV文件
csvFile = Dir(csvFolder & "*.csv")
' 关闭屏幕刷新以提高速度
Application.ScreenUpdating = False
' 遍历所有CSV文件
Do While csvFile <> ""
' 打开CSV文件
Set wb = Workbooks.Open(csvFolder & csvFile)
' 生成文件名
fileName = Left(csvFile, InStrRev(csvFile, ".") - 1) & ".xlsx"
' 保存为Excel
wb.SaveAs excelFolder & fileName, xlOpenXMLWorkbook
' 关闭工作簿
wb.Close False
csvFile = Dir
Loop
Application.ScreenUpdating = True
MsgBox "转换完成！"
End Sub
方法4：使用Excel命令行工具
使用Excel命令行的批处理文件
@echo off
setlocal enabledelayedexpansion
REM 设置路径
set CSV_DIR=C:\CSV_Files
set EXCEL_DIR=C:\Excel_Output
REM 创建输出目录
if not exist "%EXCEL_DIR%" mkdir "%EXCEL_DIR%"
REM 创建VBS脚本
echo Set objExcel = CreateObject("Excel.Application") > Convert.vbs
echo objExcel.Visible = False >> Convert.vbs
echo Set objFSO = CreateObject("Scripting.FileSystemObject") >> Convert.vbs
echo csvFolder = "%CSV_DIR%" >> Convert.vbs
echo excelFolder = "%EXCEL_DIR%" >> Convert.vbs
echo For Each objFile In objFSO.GetFolder(csvFolder).Files >> Convert.vbs
echo If LCase(objFSO.GetExtensionName(objFile.Name)) = "csv" Then >> Convert.vbs
echo Set objWorkbook = objExcel.Workbooks.Open(objFile.Path) >> Convert.vbs
echo excelName = objFSO.GetBaseName(objFile.Name) ^& ".xlsx" >> Convert.vbs
echo objWorkbook.SaveAs excelFolder ^& "\" ^& excelName, 51 >> Convert.vbs
echo objWorkbook.Close False >> Convert.vbs
echo WScript.Echo "转换完成: " ^& objFile.Name >> Convert.vbs
echo End If >> Convert.vbs
echo Next >> Convert.vbs
echo objExcel.Quit >> Convert.vbs
REM 运行VBS脚本
cscript //nologo Convert.vbs
REM 删除临时VBS文件
del Convert.vbs
echo 所有转换完成！
pause
安装依赖
Python依赖安装
pip install pandas openpyxl
PowerShell模块安装
Install-Module -Name ImportExcel -Scope CurrentUser
使用建议
- Python方法最灵活、跨平台，推荐使用
- 编码问题：中文CSV可能需要指定
encoding='gbk'或encoding='utf-8-sig' - 大数据量：如果CSV文件很大，考虑分块读取或使用dask
- 错误处理：建议添加异常处理和日志记录
选择适合你环境的方法即可！
