question

nafets-3016 avatar image
0 Votes"
nafets-3016 asked nafets-3016 edited

ImportExcel: Save xlsx with formula as csv

Hi,
I have the following problem, but I don't know where to get help.
I import a CSV file, which I expand in each row by a column with a formula. This also works so far.
Now I want to save the result as a CSV file again, where the result of the formula is in the inserted column. However, this column is always empty in the CSV file.
I hope you understand my problem and can give me a tip?
Here is my code:

$file = "E:\out.xlsx"

$xlPkg = Import-Csv -Path "E:\in.csv" -Delimiter ";" -Header "KArt","Material","BuKr","Betrag","Einh.","pro","Einhh.","Price" | Export-Excel $file -PassThru

$ws = $xlPkg.Sheet1


2..($ws.Dimension.Rows) | ForEach-Object { $ws.Cells["H$"].Formula = "=D$/F$_" }

$ws.Cells.AutoFitColumns()

$xlPkg.Sheet1.DeleteRow(2)
$xlPkg.Sheet1.Column(8) | Set-ExcelRange -NumberFormat 'General'


Export-Excel -ExcelPackage $xlPkg


Import-Excel "E:\out.xlsx" | Export-Csv "final.csv" -NoTypeInformation

Thanks
Stefan

office-scripts-excel-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

0 Answers