Rename an xslx file with the right info using Powershell-CodePudding

I have an XLSX file named DOM-TEST-JJMMYYYY which includes a header

    Sequence    Number  Subscriber  Code    Name    Channel Date    Date end    Amount  Total   State

    000655  KDZ_SSFA/493J39 141230  3393Jd  XXX, Amber  -   01-01-1999  02-01-2022  29,50   0   Approved

    000658  KDZ_SFA/893J39  181230  3398Jd  XXX, Manuel -   01-01-2021  02-01-2022  23,20   0   Approved

    ...

The script I want (in powershell) should read an xlsx file, count the number of rows for the first sheet, and the total amount (a rounded sum of the "Amount" column). The goal of the script is to rename the file like this :

DOM-TEST-JJMMYYYY-LLLLLL-MMMMMMM.XLSX

Where

LLLLLL = Number of line without pre-zeros

MMMMMMM = rounded sum of the "amount" column

I'm still learning PowerShell and very much a beginner so I have no idea how to approach this. I'm blocked here in my code. I prefer a solution where we don't use formula inside the file.

$sheetname = "Sheet1"
$file = "C:/Data/DOM-TEST-01-01-2022"
$objExcel = New-Object -ComObject Excel.Application
$workbook = $objExcel.Workbooks.Open($file)
$sheet = $workbook.Worksheets.Item($sheetname)
$objExcel.Visible=$false

$rowMax = ($sheet.UsedRange.Rows).count

Can you help me to move further please ? thank you !

CodePudding user response：

Assuming your Excel file has the headers in the first row, this should work:

$sheetname = "Sheet1"
$file      = "C:\Data\DOM-TEST-01-01-2022.xlsx"

$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $false

$workbook = $objExcel.Workbooks.Open($file)
$sheet    = $workbook.Worksheets.Item($sheetname)
$sheet.Activate()

$rowMax = $sheet.UsedRange.Rows.Count
$colMax = $sheet.UsedRange.Columns.Count
$columnIndex = 0
$totalAmount = 0

# get the index for column 'Amount'
for ($col = 1; $col -le $colMax; $col  ) {
    $name = $sheet.Cells.Item(1, $col).Value()  
    if ($name -eq 'Amount') { $columnIndex = $col; break }
}

if ($columnIndex) {
    # sum the values in this column in variable $totalAmount
    for ($row = 2; $row -le $rowMax; $row  ) {
        $totalAmount  = ($sheet.Cells.Item($row, $columnIndex).Value2 -as [double])
    }
}

$objExcel.Quit()
# IMPORTANT: clean-up used Com objects
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

# You can now rename the file using the $rowMax and $totalAmount values
$newFileName = '{0}-{1}-{2:F0}.xlsx' -f [System.IO.Path]::GetFileNameWithoutExtension($file), $rowMax, $totalAmount
Rename-Item -Path $file -NewName $newFileName