将Excel另存为CSV格式文件

时间：2021-12-15 08:53:43

直接将Excel另存为CSV，速度很快：

$CurrentPath = $MyInvocation.MyCommand.Path.substring(0,$MyInvocation.MyCommand.Path.LastIndexOf('\')+1)

$NewADUserInfo_excel = Join-Path $CurrentPath NewADUserInfo_excel.xlsx

$NewADUserInfo_csv = Join-Path $CurrentPath NewADUserInfo_csv.csv

If (Test-Path $NewADUserInfo_csv)

    {

     write-host "$NewADUserInfo_csv 已存在，按任意键将退出，请删除该文件后再重新运行脚本" -ForegroundColor Red

     cmd /c "pause"

     exit

    }

$Excel = New-Object -Com Excel.Application

$Excel.visible = $False

$Excel.displayalerts=$False

$WorkBook = $Excel.Workbooks.Open($NewADUserInfo_excel)

#保存为CSV格式，需要设置参数值为6

$Workbook.SaveAs($NewADUserInfo_csv,6)

$Excel.quit()

#重新保存CSV文件为UTF8格式，否则无法正常导入（不识别中文字符）

$Temp_Info = gc $NewADUserInfo_csv

Start-Sleep -Milliseconds 600

$Temp_Info| Set-Content $NewADUserInfo_csv -Encoding utf8

$aa=Import-Csv $NewADUserInfo_csv

$aa

Start-Sleep -Milliseconds 800

remove-item $NewADUserInfo_csv -force

csv包含中文的话，直接通过import-csv会报错，可以使用如下两种方法：
1)  Import-CSV d:\a.csv -Encoding default

2)  gc d:\a.csv | convertfrom-csv

逐行读取Excel，然后将其保存为CSV，效率很低：

#读取负载均衡信息表

$strpath="D:\My Documents\Work\IP映射表.xlsx"

$excel=new-object -comobject excel.application

$WorkBook = $excel.Workbooks.Open($strpath)

$WorkSheet = $WorkBook.Worksheets.Item(1)

#已用行数

$usedrows = $worksheet.usedrange.rows.count

$Public_ip_maps_info = $Public_ip_maps_infos = $null

$Public_ip_maps_infos = @()

For($i=2;$i -le $usedrows;$i++)

{

 $Public_ip_maps_info = ""|Select public_ip,internal_ip,is_vip

 $Public_ip_maps_info.public_ip = $WorkSheet.Cells.Item($i,2).value2

 $Public_ip_maps_info.internal_ip = $WorkSheet.Cells.Item($i,3).value2

 $Public_ip_maps_info.is_vip = $WorkSheet.Cells.Item($i,4).value2

 $Public_ip_maps_infos += $Public_ip_maps_info

}

$Public_ip_maps_infos

$excel.Quit()

$WorkSheet =$null

$WorkBook = $null

$excel = $null

[GC]::Collect()

将CSV另存为Excel，参考：

http://www.pstips.net/converting-csv-to-excel-file.html

相关文章