I am using Import-Excel
to calculate some data using excel formula.
Below is the code
# Import the ImportExcel module
Import-Module ImportExcel
# Define the path to the existing Excel file
$excelFilePath = "E:VLANReports20240702_VLAN_Data.xlsx"
# Import ImportExcel Module
Import-Module ImportExcel
# Define the path to the new Excel file
$newExcelFilePath = "E:VLAN.xlsx"
# Read the data from the "USER" sheet
Write-Output "Reading USER data from the new file..."
$userData = Import-Excel -Path $newExcelFilePath -WorksheetName "USER"
# Check if user data was imported
if ($userData -eq $null) {
Write-Output "No data found in USER sheet. Exiting script."
exit
}
# Create a new worksheet "Sheet1" and copy data from "USER"
Write-Output "Exporting USER data to Sheet1..."
$userData | Export-Excel -Path $newExcelFilePath -WorksheetName "Sheet1" -ClearSheet -AutoSize -TableName "UserData"
# Open the Excel package
Write-Output "Opening Excel package..."
$excelPackage = Open-ExcelPackage -Path $newExcelFilePath
# Get the worksheet
$sheet1 = $excelPackage.Workbook.Worksheets["Sheet1"]
if ($sheet1 -eq $null) {
Write-Output "Sheet1 not found. Exiting script."
exit
}
# Define the headers to be added starting from column N
$headers = @(
"Bckp`nSG VLanID",
"Bckp`nnetwork label",
"Bckp`nSubnet",
"Bckp`nNetmask",
"Bckp`nGateway",
"Bckp`nNetwork"
)
# Add headers manually to "Sheet1"
Write-Output "Adding headers to Sheet1..."
for ($i = 0; $i -lt $headers.Length; $i++) {
$columnIndex = 14 + $i
$headerCell = $sheet1.Cells[1, $columnIndex]
$headerCell.Value = $headers[$i]
$headerCell.Style.WrapText = $true
}
# Define the formulas for columns N, O, P, Q, R, S
$formulas = [ordered]@{
N = '=IFERROR(IFS(MID(C2,1,1)="2",XLOOKUP(NUMBERVALUE(MID(C2,2,3)),BACKUP!$C:$C,BACKUP!$C:$C),MID(C2,1,1)="3",XLOOKUP(C2+300,BACKUP!$C:$C,BACKUP!$C:$C)),"")'
O = '=IF(N2<>"",XLOOKUP(N2,BACKUP!$C:$C,BACKUP!$B:$B),"")'
P = '=IF(N2<>"",LEFT(S2,SEARCH("/",S2,1)-1),"")'
Q = '=IF(S2<>"", LEFT(S2, FIND("/", S2)-1) & " (" & TEXTJOIN(".", TRUE, MID(S2, FIND("/", S2), LEN(S2))) & ")", "")'
R = '=IF(N2<>"",XLOOKUP(N2,BACKUP!$C:$C,BACKUP!$G:$G),"")'
S = '=IF(N2<>"",XLOOKUP(N2,BACKUP!$C:$C,BACKUP!$A:$A),"")'
}
# Get the last row in the sheet
$lastRow = $sheet1.Dimension.End.Row
Write-Output "Last row in Sheet1: $lastRow"
# Add formulas to "Sheet1"
Write-Output "Adding formulas to Sheet1..."
$formulas.Keys | ForEach-Object {
$column = $_
$colIndex = [array]::IndexOf($formulas.Keys, $column) + 14
for ($row = 2; $row -le $lastRow; $row++) {
$cell = $sheet1.Cells[$row, $colIndex]
$cell.Formula = $formulas[$column] -replace '2', $row
}
}
# Force recalculation of all formulas in the workbook
Write-Output "Forcing recalculation of all formulas..."
$sheet1.Calculate()
# Save and close the Excel package
Write-Output "Saving the Excel package..."
Close-ExcelPackage $excelPackage
Write-Output "Sheet 'Sheet1' has been created with the 'USER' data, headers added, and formulas applied."
The problem is in the cells (column N onwards), I am able to see only the formula being displayed not the calculated data.
Please let me know what is wrong with the code.