我正在嘗試\tmp\使用 PS 腳本從目錄中下載所有 CSV 檔案，并將它們轉換為一個 excel 檔案作為報告，并將其放在\reports\名稱下的目錄中LDAP.xlsx。我的 CSV 檔案有不同數量的保存資料。
在論壇中，我發現了這個how-to-export-a-csv-to-excel-using-powershell，我的代碼如下所示：
Clear-Host
# SOURCE
##########
# config file
$conf_file = "C:\PS_LDAP_searchlight\config\searchlight_conf.conf"
$conf_values = Get-Content $conf_file | Out-String | ConvertFrom-StringData
# variables from config file
$main_path = $conf_values.main_path
$tmp_path = $conf_values.tmp_path
$reports_path = $conf_values.reports_path
# PROGRAM
##########
$workingdir = $main_path $tmp_path "*.csv"
$reportsdir = $main_path $reports_path
$csv = dir -path $workingdir
foreach($inputCSV in $csv){
$outputXLSX = $reportsdir "\" $inputCSV.Basename ".xlsx"
### Create a new Excel Workbook with one empty sheet
$excel = New-Object -ComObject excel.application
$excel.DisplayAlerts = $False
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)
### Build the QueryTables.Add command
### QueryTables does the same as when clicking "Data ? From Text" in Excel
$TxtConnector = ("TEXT;" $inputCSV)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)
### Set the delimiter (, or ;) according to your regional settings
### $Excel.Application.International(3) = ,
### $Excel.Application.International(5) = ;
$query.TextFileOtherDelimiter = $Excel.Application.International(5)
### Set the format to delimited and text for every column
### A trick to create an array of 2s is used with the preceding comma
$query.TextFileParseType = 1
$query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1
### Execute & delete the import query
$query.Refresh()
$query.Delete()
### Save & close the Workbook as XLSX. Change the output extension for Excel 2003
$Workbook.SaveAs($outputXLSX,51)
$excel.Quit()
# Cleaner
$inputCSV = $null
$outputXLSX = $null
}
## To exclude an item, use the '-exclude' parameter (wildcards if needed)
#remove-item -path $workingdir -exclude *Crab4dq.csv
# CLEANER
###############################
# SOURCE
###############################
# config file
$conf_file = $null
$conf_values = $null
# variables from config file
$main_path = $null
$tmp_path = $null
$reports_path = $null
# PROGRAM
###############################
$workingdir = $null
$csv = $null
$reportsdir = $null
該代碼讀取所有檔案，但一對一寫入。我需要有關如何進行多對一選擇的幫助和解釋。我希望每個 CSV 檔案以其自己的名稱保存為單獨的作業表，例如：
excel\sheet1 中的 users_all_inf.csv =>
excel\sheet2中的 users_all_inf
active_users_last_logon_year_ago.csv => excel\next_sheet 中的 active_users_last_logon_year_ago nextfilename.csv => nextfilename
這樣所有資料都將在一個 excel report.xlsx 檔案中可用。
對于轉換代碼的任何提示或幫助，我將不勝感激。
uj5u.com熱心網友回復：
我找到了部分解決我的問題的解決方案：
# variables from config file
$main_path = $conf_values.main_path
$tmp_path = $conf_values.tmp_path
$reports_path = $conf_values.reports_path
$system_name = $conf_values.system_name
$tmp_dir = $main_path $tmp_path "*"
$csvs = Get-ChildItem $tmp_dir -Include *.csv
$y = $csvs.Count
Write-Host "Detected the following CSV files: ($y)"
foreach ($csv in $csvs)
{
Write-Host " "$csv.Name
}
$outputfilename = $(get-date -f yyyyMMdd) "_" $system_name "_report.xlsx" #creates file name with date/username
Write-Host Creating: $outputfilename
$excelapp = new-object -comobject Excel.Application
$excelapp.sheetsInNewWorkbook = $csvs.Count
$xlsx = $excelapp.Workbooks.Add()
$sheet=1
foreach ($csv in $csvs) {
$row=1
$column=1
$worksheet = $xlsx.Worksheets.Item($sheet)
$worksheet.Name = $csv.Name
$file = (Get-Content $csv)
foreach($line in $file) {
$linecontents=$line -split ',(?!\s*\w ")'
foreach($cell in $linecontents) {
$worksheet.Cells.Item($row,$column) = $cell
$column
}
$column=1
$row
}
$sheet
}
$output = $main_path $reports_path $outputfilename
$xlsx.SaveAs($output)
$excelapp.quit()
這解決了多對一的問題，但在作業表中，所有資料都存盤在一列中。此時我有：csv檔案：
data1;data2;data3;...
data1;data2;data3;...
報告如：
| A | B |
|-----------------------|---|
| data1;data2;data3;... | |
|-----------------------|---|
| data1;data2;data3;... | |
我需要幫助將資料拆分成如下列：
| A | B | C | ... |
|-------|-------|-------|-----|
| data1 | data2 | data3 | ... |
|-------|-------|-------|-----|
| data1 | data2 | data3 | ... |
uj5u.com熱心網友回復：
請找到關于使用 ImportExcel 模塊的 github 鏈接。 https://github.com/dfinke/ImportExcel/tree/master/Examples
$outputFile = "C:\Temp\OutputExcelFile.xlsx" #Output File
$csvFiles = Get-childItem -Filter *.csv # Filtering CSV file in my present working dir
foreach ($csvFile in $csvFiles) {
#Import csv file and export it contents to Output excel file and rename the sheet.
Import-csv $csvFile | Export-Excel $outputFile -WorksheetName $csvFile.BaseName
}
希望能幫助到你。
uj5u.com熱心網友回復：
最后我的代碼看起來像：
Clear-Host
# config file
$conf_file = "C:\SEARCHLIGHT\config\searchlight_conf.conf"
$conf_values = Get-Content $conf_file | Out-String | ConvertFrom-StringData
# variables from config file
$main_path = $conf_values.main_path
$tmp_path = $conf_values.tmp_path
$reports_path = $conf_values.reports_path
$system_name = $conf_values.system_name
$tmp_dir = $main_path $tmp_path "*" # source file
$outputfilename = $(get-date -f yyyyMMdd) "_" $system_name "_report.xlsx" # destination file with date
# get list of csvs files
$csvs = Get-ChildItem $tmp_dir -Include *.csv
$y = $csvs.Count
Write-Host "Detected the following CSV files: ($y)"
foreach ($csv in $csvs) {
Write-Host " "$csv.Name
}
Write-Host Creating: $outputfilename
# Create a new Excel workbook
$excelapp = new-object -comobject Excel.Application
$excelapp.sheetsInNewWorkbook = $csvs.Count
$xlsx = $excelapp.Workbooks.Add()
$sheet=1
$delimiter = ";" # delimiter used in the csv file
foreach ($csv in $csvs) {
#$row=1
#$column=1
$worksheet = $xlsx.Worksheets.Item($sheet)
$worksheet.Name = $csv.Name
# Build the QueryTables.Add command and reformat the data
$TxtConnector = ("TEXT;" $csv)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)
$query.TextFileOtherDelimiter = $delimiter
$query.TextFileParseType = 1
$query.TextFileColumnDataTypes = ,1 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1
# Execute & delete the import query
$query.Refresh()
$query.Delete()
$sheet
} # end foreach ($csv in $csvs)
# Save & close the Workbook as XLSX
$output = $main_path $reports_path $outputfilename
$xlsx.SaveAs($output)
$excelapp.quit()
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/534710.html
標籤：擅长电源外壳格式文件