Próbuję zautomatyzować następujące zadanie ręczne i zmagam się z częścią z niego:

1) Otwórz plik tekstowy, który zawiera wiele wierszy zawierających dane.

2) Skopiuj zawartość tego pliku do schowka.

3) Otwórz i arkusz kalkulacyjny Excel.

4) Zmień nazwę arkusza kalkulacyjnego na Test.

5) Wklej zawartość schowka.

Gdy jest to zrobione ręcznie, zawartość jest wklejana, a każdy wiersz w pliku tekstowym jest wstawiany jako nowy wiersz w kolumnie A.

Pierwotnie klient chciał, aby cała zawartość pliku została wstrzyknięta do komórki A1. Udało mi się to osiągnąć za pomocą poniższego kodu PowerShell.

Jednak od tego czasu zmienili to z powrotem, aby każdy wiersz tekstu był umieszczany w osobnym wierszu w kolumnie A.

Nie mogę dowiedzieć się, jak to zrobić z wdziękiem za pomocą metody Get-Content kopiowania danych tekstowych. Widziałem obejścia tego problemu, w których program Excel otwiera plik tekstowy i kopiuje tekst do skoroszytu pośredniego, a następnie do skoroszytu końcowego.

Czy ktoś mógłby mnie poinformować, czy można zmienić mój już działający kod poniżej, aby dodawał tekst do wierszy w kolumnie A, a nie do komórki A1?

# Clear the screen of any previous text.
cls

$ExcelFile="C:\Users\User\Desktop\Test\Test.xlsx"
$TextFile="C:\Users\User\Desktop\Test\TestText.txt"
$Content = Get-Content $TextFile -Raw

# Perform operations in Excel based on content of the downloaded file.
$Excel = New-Object -ComObject Excel.Application

# For troubleshooting enable the below to view Excel as file is manipulated:
#$Excel.Visible=$true
# Disable Excel alerts. Hash this line out for troubleshooting.
$Excel.DisplayAlerts = $false

# Set up workbook...
$Workbook = $Excel.Workbooks.Add()
$Data = $Workbook.Worksheets.Item(1)
$Data.Name = 'Test'

# Insert Data   
$Data.Cells.Item(1,1) = "$Content"

# Format, save and quit excel
$UsedRange = $Data.UsedRange                                                                                              
$UsedRange.EntireColumn.AutoFit() | Out-Null
$Workbook.SaveAs("$ExcelFile")
$Excel.Quit()

Wiem, że część, którą musiałbym zmienić, jest następująca, ale nie jestem pewien, na co to zmienić:

# Insert Data   
$Data.Cells.Item(1,1) = "$Content"

Z góry bardzo dziękuję.

0
C3N0B1T3 23 marzec 2020, 21:13

2 odpowiedzi

Najlepsza odpowiedź

Aby to zrobić, musisz znaleźć ostatnio używany wiersz w arkuszu i napisać stamtąd każdy wiersz:

$ExcelFile = "C:\Users\User\Desktop\Test\Test.xlsx"
$TextFile  = "C:\Users\User\Desktop\Test\TestText.txt"

# Perform operations in Excel based on content of the downloaded file.
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$Excel.DisplayAlerts = $false

# open the file and select the first worksheet
$WorkBook  = $Excel.Workbooks.Open($ExcelFile)
$WorkSheet = $Workbook.Worksheets.Item(1)
# get the first unused row
$row = ($WorkSheet.UsedRange.Rows).Count + 1
# fill in the data
Get-Content -Path $TextFile | ForEach-Object {
    $WorkSheet.Cells.Item($row++, 1) = $_
}

# format column A and save the file
$UsedRange = $WorkSheet.UsedRange                                                                                              
$UsedRange.EntireColumn.AutoFit() | Out-Null
$WorkBook.Save()

# quit excel and clean up the used COM objects
$Excel.Quit()
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($WorkSheet)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($WorkBook)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
0
Theo 24 marzec 2020, 16:00

Myślę, że rozwiązaniem byłoby przeczytanie każdego wiersza w treści za pomocą pętli for lub foreach

W pętli zapisz zawartość linii w ostatnim wierszu kolumny A w pliku Excel.

To będzie coś takiego

foreach($line in $Content){
   $Data.Cells.Item($LastRow,1) = $line


}
0
ManhND 24 marzec 2020, 02:53