835 lines
36 KiB
PowerShell
835 lines
36 KiB
PowerShell
# SharePointFunctions.ps1
|
|
|
|
$script:config = $null # Initialize config variable
|
|
|
|
# Load configuration from config.json
|
|
function Load-Config {
|
|
try {
|
|
$configPath = Join-Path $PSScriptRoot "config.json"
|
|
if (Test-Path $configPath) {
|
|
$script:config = Get-Content $configPath -Raw | ConvertFrom-Json
|
|
return $true
|
|
} else {
|
|
Write-Host "Config file not found at: $configPath"
|
|
return $false
|
|
}
|
|
}
|
|
catch {
|
|
Write-Host "Error loading configuration: $($_.Exception.Message)"
|
|
return $false
|
|
}
|
|
}
|
|
|
|
# Save configuration to config.json
|
|
function Save-Config {
|
|
param (
|
|
[string]$SiteUrl,
|
|
[string]$ProductionLibrary,
|
|
[string]$TempLibrary
|
|
)
|
|
try {
|
|
$configPath = "$PSScriptRoot\config.json"
|
|
$configData = @{
|
|
SiteUrl = $SiteUrl
|
|
ProductionLibrary = $ProductionLibrary
|
|
TempLibrary = $TempLibrary
|
|
}
|
|
$configJson = ConvertTo-Json -InputObject $configData -Compress
|
|
$configJson | Out-File $configPath
|
|
$script:txtStatus.Text += "Configuration saved successfully.`n"
|
|
} catch {
|
|
$script:txtStatus.Text += "Error saving configuration: $($_.Exception.Message)`n"
|
|
}
|
|
}
|
|
|
|
function Get-FolderBrowser {
|
|
param (
|
|
[string]$ParentFolderUrl = ""
|
|
)
|
|
try {
|
|
# Get site ID first
|
|
$siteUrl = $script:txtSiteUrl.Text.TrimEnd('/')
|
|
|
|
# Get site directly using the URL
|
|
$encodedUrl = [System.Web.HttpUtility]::UrlEncode($siteUrl)
|
|
$site = Invoke-MgGraphRequest -Method GET -Uri "https://graph.microsoft.com/v1.0/sites/sutterhill.sharepoint.com:/sites/tax" -ErrorAction Stop
|
|
|
|
if (-not $site) {
|
|
throw "Site not found at $siteUrl"
|
|
}
|
|
|
|
$script:txtStatus.Text += "Found site: $($site.webUrl)`n"
|
|
|
|
# Get drive (document library)
|
|
$drives = Get-MgSiteDrive -SiteId $site.id
|
|
# Handle "Shared Documents" special case
|
|
$libraryName = if ($script:txtProdLib.Text -eq "Shared Documents") {
|
|
"Documents"
|
|
} else {
|
|
$script:txtProdLib.Text
|
|
}
|
|
$drive = $drives | Where-Object { $_.Name -eq $libraryName }
|
|
|
|
if (-not $drive) {
|
|
$script:txtStatus.Text += "Available libraries: $($drives.Name -join ', ')`n"
|
|
throw "Library not found: $libraryName"
|
|
}
|
|
|
|
# Get root folder first
|
|
# Get root folder first
|
|
if ($ParentFolderUrl) {
|
|
# Get the folder ID first
|
|
# Remove the /sites/tax/Shared Documents/ part from the folder path
|
|
$relativePath = $ParentFolderUrl.Replace("/sites/tax/Shared Documents/", "").TrimStart("/")
|
|
|
|
# URL encode the relative path, but replace + with %20 for spaces
|
|
$encodedPath = [System.Web.HttpUtility]::UrlEncode($relativePath).Replace("+", "%20")
|
|
|
|
$folderUri = "https://graph.microsoft.com/v1.0/drives/$($drive.Id)/root:/$encodedPath"
|
|
$script:txtStatus.Text += "Folder URI: $folderUri`n"
|
|
try {
|
|
$folderItem = Invoke-MgGraphRequest -Method GET -Uri $folderUri -ErrorAction Stop
|
|
} catch {
|
|
throw "Folder not found: $($_.Exception.Message)"
|
|
}
|
|
$root = $folderItem
|
|
}
|
|
else {
|
|
$root = Get-MgDriveRoot -DriveId $drive.Id
|
|
}
|
|
|
|
# Get folders using the root item's ID
|
|
$folders = Get-MgDriveItemChild -DriveId $drive.Id -DriveItemId $root.Id | Where-Object { $_.Folder }
|
|
|
|
# Create custom objects for the grid view
|
|
$folderObjects = $folders | ForEach-Object {
|
|
[PSCustomObject]@{
|
|
Name = $_.Name
|
|
ServerRelativeUrl = "/sites/tax/Shared Documents/$($_.Name)"
|
|
IsFolder = $true # Add a property to indicate it's a folder
|
|
}
|
|
}
|
|
|
|
# Add ".." entry to go up a level if not at the root
|
|
if ($ParentFolderUrl) {
|
|
$parentUrl = Split-Path -Path $ParentFolderUrl -Parent
|
|
$folderObjects = @([PSCustomObject]@{
|
|
Name = ".."
|
|
ServerRelativeUrl = $parentUrl
|
|
IsFolder = $true
|
|
}) + $folderObjects
|
|
}
|
|
|
|
# Create a form and listbox for folder selection
|
|
$form = New-Object System.Windows.Forms.Form
|
|
$form.Text = "Select Folder"
|
|
$form.Size = New-Object System.Drawing.Size(400, 400)
|
|
$listBox = New-Object System.Windows.Forms.ListBox
|
|
$listBox.Size = New-Object System.Drawing.Size(360, 320)
|
|
$listBox.Location = New-Object System.Drawing.Point(10, 10)
|
|
$okButton = New-Object System.Windows.Forms.Button
|
|
$okButton.Location = New-Object System.Drawing.Point(150, 340)
|
|
$okButton.Size = New-Object System.Drawing.Size(75, 23)
|
|
$okButton.Text = "OK"
|
|
$cancelButton = New-Object System.Windows.Forms.Button
|
|
$cancelButton.Location = New-Object System.Drawing.Point(230, 340)
|
|
$cancelButton.Size = New-Object System.Drawing.Size(75, 23)
|
|
$cancelButton.Text = "Cancel"
|
|
$selectFolderButton = New-Object System.Windows.Forms.Button
|
|
$selectFolderButton.Location = New-Object System.Drawing.Point(70, 340)
|
|
$selectFolderButton.Size = New-Object System.Drawing.Size(75, 23)
|
|
$selectFolderButton.Text = "Select Folder"
|
|
$form.Controls.Add($listBox) | Out-Null
|
|
$form.Controls.Add($okButton) | Out-Null
|
|
$form.Controls.Add($cancelButton) | Out-Null
|
|
$form.Controls.Add($selectFolderButton) | Out-Null
|
|
$form.FormBorderStyle = 'FixedDialog'
|
|
$form.StartPosition = 'CenterScreen'
|
|
$form.Add_Shown({$Form.Activate()})
|
|
|
|
# Populate listbox
|
|
foreach ($folder in $folderObjects) {
|
|
$listBox.Items.Add($folder.Name) | Out-Null
|
|
}
|
|
|
|
# Handle OK button click
|
|
$okButton.Add_Click({
|
|
$form.Tag = $listBox.SelectedItem
|
|
$form.Close()
|
|
})
|
|
|
|
# Handle Select Folder button click
|
|
$selectFolderButton.Add_Click({
|
|
$form.Tag = $listBox.SelectedItem + "|SELECT" # Add a flag to indicate folder selection
|
|
$form.Close()
|
|
})
|
|
|
|
# Handle Cancel button click
|
|
$cancelButton.Add_Click({
|
|
$form.Tag = $null
|
|
$form.Close()
|
|
})
|
|
|
|
# Show the Form as a Dialog
|
|
[void]$form.ShowDialog()
|
|
|
|
# Get the selected folder
|
|
$selected = $folderObjects | Where-Object {$_.Name -eq $form.Tag.Split('|')[0]}
|
|
|
|
# Check if the "Select Folder" button was clicked
|
|
if ($form.Tag -like "*|SELECT") {
|
|
return $selected # Return the selected folder
|
|
}
|
|
# If a folder is selected, browse into it
|
|
elseif ($selected -and $selected.IsFolder -eq $true -and $selected.Name -ne "..") {
|
|
return Get-FolderBrowser -ParentFolderUrl $selected.ServerRelativeUrl
|
|
}
|
|
elseif ($selected -and $selected.Name -eq "..") {
|
|
return Get-FolderBrowser -ParentFolderUrl $selected.ServerRelativeUrl
|
|
}
|
|
elseif ($selected) {
|
|
return $selected
|
|
}
|
|
else {
|
|
return $null
|
|
}
|
|
}
|
|
catch {
|
|
$script:txtStatus.Text += "Folder Error: $($_.Exception.Message)`n"
|
|
return $null
|
|
}
|
|
}
|
|
|
|
function Connect-SharePoint {
|
|
return Connect-Graph
|
|
}
|
|
|
|
function List-XlsFiles {
|
|
try {
|
|
# Clear the DataGridView before listing new files
|
|
$script:dataGridView.Rows.Clear()
|
|
|
|
$script:txtStatus.Text += "Connecting to SharePoint...`n"
|
|
if (-not (Connect-SharePoint)) {
|
|
throw "Failed to connect to SharePoint"
|
|
}
|
|
|
|
$script:txtStatus.Text += "Getting site...`n"
|
|
$site = Invoke-MgGraphRequest -Method GET -Uri "https://graph.microsoft.com/v1.0/sites/sutterhill.sharepoint.com:/sites/tax" -ErrorAction Stop
|
|
|
|
$script:txtStatus.Text += "Getting document library...`n"
|
|
$drives = Get-MgSiteDrive -SiteId $site.id
|
|
$libraryName = if ($script:txtProdLib.Text -eq "Shared Documents") {
|
|
"Documents"
|
|
} else {
|
|
$script:txtProdLib.Text
|
|
}
|
|
$drive = $drives | Where-Object { $_.Name -eq $libraryName }
|
|
|
|
if (-not $drive) {
|
|
throw "Library not found: $libraryName. Available libraries: $($drives.Name -join ', ')"
|
|
}
|
|
|
|
$script:txtStatus.Text += "Getting root folder...`n"
|
|
$root = Get-MgDriveRoot -DriveId $drive.Id
|
|
|
|
$fileList = @()
|
|
$folderPath = $script:txtFolder.Text
|
|
|
|
$script:txtStatus.Text += "Searching for XLS files...`n"
|
|
|
|
# If a specific folder is selected, search only in that folder
|
|
if (-not [string]::IsNullOrWhiteSpace($folderPath)) {
|
|
$script:txtStatus.Text += "Searching in folder: $folderPath`n"
|
|
# Get the folder ID first
|
|
# Construct the correct URI for the folder
|
|
# Remove the /sites/tax/Shared Documents/ part from the folder path
|
|
$relativePath = $folderPath.Replace("/sites/tax/Shared Documents/", "").TrimStart("/")
|
|
|
|
# URL encode the relative path, but replace + with %20 for spaces
|
|
$encodedPath = [System.Web.HttpUtility]::UrlEncode($relativePath).Replace("+", "%20")
|
|
|
|
$folderUri = "https://graph.microsoft.com/v1.0/drives/$($drive.Id)/root:/$encodedPath"
|
|
$script:txtStatus.Text += "Folder URI: $folderUri`n"
|
|
try {
|
|
$folderItem = Invoke-MgGraphRequest -Method GET -Uri $folderUri -ErrorAction Stop
|
|
} catch {
|
|
throw "Folder not found: $($_.Exception.Message)"
|
|
}
|
|
#$items = Get-MgDriveItemChild -DriveId $drive.Id -DriveItemId $folderItem.id
|
|
|
|
# Call the recursive function to process files and subfolders
|
|
$script:txtStatus.Text += "Listing files in current folder...`n"
|
|
$fileList = Get-XlsFilesCurrentFolder -DriveId $drive.Id -DriveItemId $folderItem.id
|
|
} else {
|
|
$script:txtStatus.Text += "Searching in root folder...`n"
|
|
$fileList = Get-XlsFilesCurrentFolder -DriveId $drive.Id -DriveItemId $root.Id
|
|
}
|
|
|
|
if ($fileList.Count -gt 0) {
|
|
$script:txtStatus.Text += "Saving file list...`n"
|
|
# Display each file on a new line
|
|
foreach ($file in $fileList) {
|
|
$row = @($file.OriginalPath, $file.OriginalFileName)
|
|
$script:dataGridView.Rows.Add($row)
|
|
}
|
|
#$fileList | ForEach-Object {
|
|
# $script:txtStatus.Text += "$($_.OriginalFileName)`n"
|
|
#}
|
|
$fileList | ConvertTo-Json | Out-File "$env:TEMP\FileList.json"
|
|
$script:txtStatus.Text += "Found $($fileList.Count) XLS files`n"
|
|
} else {
|
|
$script:txtStatus.Text += "No XLS files found`n"
|
|
}
|
|
}
|
|
catch {
|
|
$script:txtStatus.Text += "Error listing files: $($_.Exception.Message)`n"
|
|
}
|
|
}
|
|
|
|
# Function to get XLS files from only the current folder
|
|
function Get-XlsFilesCurrentFolder {
|
|
param (
|
|
[string]$DriveId,
|
|
[string]$DriveItemId
|
|
)
|
|
|
|
$allFiles = @()
|
|
|
|
# Check if DriveId or DriveItemId is null or empty
|
|
if ([string]::IsNullOrEmpty($DriveId)) {
|
|
$script:txtStatus.Text += "Error: DriveId is null or empty`n"
|
|
return $allFiles
|
|
}
|
|
if ([string]::IsNullOrEmpty($DriveItemId)) {
|
|
$script:txtStatus.Text += "Error: DriveItemId is null or empty`n"
|
|
return $allFiles
|
|
}
|
|
|
|
# Get items in the current folder
|
|
try {
|
|
$items = Get-MgDriveItemChild -DriveId $DriveId -DriveItemId $DriveItemId -ErrorAction Stop
|
|
}
|
|
catch {
|
|
$script:txtStatus.Text += "Error getting items: $($_.Exception.Message)`n"
|
|
return $allFiles
|
|
}
|
|
|
|
foreach ($item in $items) {
|
|
$script:txtStatus.Text += "Checking item: $($item.Name)`n"
|
|
|
|
# If it's an Excel file, add it to the list - only .xls files, not .xlsx
|
|
if ($item.Name -like "*.xls" -and -not ($item.Name -like "*.xlsx")) {
|
|
$script:txtStatus.Text += "Found XLS file: $($item.Name)`n"
|
|
$allFiles += [PSCustomObject]@{
|
|
OriginalPath = $item.WebUrl
|
|
OriginalFileName = $item.Name
|
|
}
|
|
}
|
|
}
|
|
|
|
return $allFiles
|
|
}
|
|
|
|
function Convert-Files {
|
|
try {
|
|
# Get site and drive information first
|
|
$script:txtStatus.Text += "Getting SharePoint site information...`n"
|
|
$site = Invoke-MgGraphRequest -Method GET -Uri "https://graph.microsoft.com/v1.0/sites/sutterhill.sharepoint.com:/sites/tax" -ErrorAction Stop
|
|
|
|
if (-not $site) {
|
|
throw "Could not find SharePoint site"
|
|
}
|
|
|
|
$script:txtStatus.Text += "Getting document library information...`n"
|
|
$drives = Get-MgSiteDrive -SiteId $site.id
|
|
$libraryName = if ($script:txtProdLib.Text -eq "Shared Documents") {
|
|
"Documents"
|
|
} else {
|
|
$script:txtProdLib.Text
|
|
}
|
|
$drive = $drives | Where-Object { $_.Name -eq $libraryName }
|
|
|
|
if (-not $drive) {
|
|
$script:txtStatus.Text += "Available libraries: $($drives.Name -join ', ')`n"
|
|
throw "Library not found: $libraryName"
|
|
}
|
|
|
|
# Load the file list from the JSON file
|
|
$fileListPath = "$env:TEMP\FileList.json"
|
|
if (Test-Path $fileListPath) {
|
|
$fileList = Get-Content $fileListPath -Raw | ConvertFrom-Json
|
|
} else {
|
|
$script:txtStatus.Text += "Error: FileList.json not found. Please list files first.`n"
|
|
return
|
|
}
|
|
|
|
# Check if the file list is empty
|
|
if ($fileList.Count -eq 0) {
|
|
$script:txtStatus.Text += "No files to convert.`n"
|
|
return
|
|
}
|
|
|
|
# Store drive ID in script scope for use in download/upload operations
|
|
$script:driveId = $drive.Id
|
|
$script:txtStatus.Text += "Using drive ID: $($drive.Id)`n"
|
|
|
|
# Iterate through the file list and convert each file
|
|
foreach ($file in $fileList) {
|
|
$originalPath = $file.OriginalPath
|
|
$originalFileName = $file.OriginalFileName
|
|
$tempPath = $file.TempPath
|
|
|
|
$script:txtStatus.Text += "Converting file: $originalFileName`n"
|
|
|
|
# Download the file from SharePoint
|
|
$downloadPath = "$env:TEMP\$originalFileName"
|
|
try {
|
|
$script:txtStatus.Text += "Downloading file: $originalFileName`n"
|
|
|
|
# Get the file path relative to the library root
|
|
$relativePath = $originalFileName
|
|
if ($script:txtFolder.Text) {
|
|
$folderPath = $script:txtFolder.Text.Replace("/sites/tax/Shared Documents/", "").TrimStart("/")
|
|
$relativePath = "$folderPath/$originalFileName"
|
|
$script:txtStatus.Text += "Using relative path: $relativePath`n"
|
|
}
|
|
|
|
# Construct the download URI using the stored drive ID
|
|
$encodedPath = [System.Web.HttpUtility]::UrlEncode($relativePath).Replace("+", "%20")
|
|
$downloadUri = "https://graph.microsoft.com/v1.0/drives/$($script:driveId)/root:/$($encodedPath):/content"
|
|
$script:txtStatus.Text += "Download URI: $downloadUri`n"
|
|
|
|
# Ensure temp directory exists
|
|
$tempDir = [System.IO.Path]::GetDirectoryName($downloadPath)
|
|
if (-not (Test-Path $tempDir)) {
|
|
New-Item -ItemType Directory -Path $tempDir -Force | Out-Null
|
|
}
|
|
|
|
# Download the file with progress tracking
|
|
$script:txtStatus.Text += "Downloading to: $downloadPath`n"
|
|
try {
|
|
$response = Invoke-MgGraphRequest -Uri $downloadUri -Method GET -OutputFilePath $downloadPath
|
|
Start-Sleep -Seconds 2 # Give file system time to catch up
|
|
|
|
if (Test-Path $downloadPath) {
|
|
$fileInfo = Get-Item $downloadPath
|
|
$script:txtStatus.Text += "Download complete. File size: $($fileInfo.Length) bytes`n"
|
|
} else {
|
|
throw "File download appeared to succeed but file not found at: $downloadPath"
|
|
}
|
|
} catch {
|
|
$script:txtStatus.Text += "Download failed: $($_.Exception.Message)`n"
|
|
if ($_.Exception.Response) {
|
|
$script:txtStatus.Text += "Response Status Code: $($_.Exception.Response.StatusCode)`n"
|
|
}
|
|
throw
|
|
}
|
|
|
|
# Verify the downloaded file
|
|
if (-not (Test-Path -LiteralPath $downloadPath)) {
|
|
throw "Failed to download file from SharePoint. File not found at: $downloadPath"
|
|
}
|
|
|
|
$fileSize = (Get-Item $downloadPath).Length
|
|
if ($fileSize -eq 0) {
|
|
throw "Downloaded file is empty: $downloadPath"
|
|
}
|
|
|
|
$script:txtStatus.Text += "File downloaded successfully to: $downloadPath`n"
|
|
}
|
|
catch {
|
|
$script:txtStatus.Text += "Error downloading file: $($_.Exception.Message)`n"
|
|
if ($_.Exception.Response) {
|
|
$script:txtStatus.Text += "Response Status Code: $($_.Exception.Response.StatusCode)`n"
|
|
}
|
|
$script:txtStatus.Text += "Stack Trace: $($_.Exception.StackTrace)`n"
|
|
continue # Move to the next file
|
|
}
|
|
|
|
# Convert the file to XLSX
|
|
$xlsxFileName = $originalFileName -replace "\.xls[m]?$", ".xlsx"
|
|
$xlsxPath = Join-Path $env:TEMP $xlsxFileName
|
|
|
|
# Ensure the path is resolved
|
|
$xlsxPath = [System.IO.Path]::GetFullPath($xlsxPath)
|
|
|
|
# Verify the path is not null
|
|
if (-not $xlsxPath) {
|
|
throw "XLSX path is null or empty"
|
|
}
|
|
|
|
# Verify the download file exists
|
|
if (-not (Test-Path -LiteralPath $downloadPath)) {
|
|
throw "Downloaded file not found at: $downloadPath"
|
|
}
|
|
|
|
try {
|
|
$script:txtStatus.Text += "Opening Excel...`n"
|
|
$excel = New-Object -ComObject Excel.Application
|
|
$excel.Visible = $false
|
|
$excel.DisplayAlerts = $false
|
|
|
|
try {
|
|
# Create a safe temporary path without spaces
|
|
$safeTempDir = Join-Path $env:TEMP "XLSConversion"
|
|
if (-not (Test-Path -Path $safeTempDir)) {
|
|
New-Item -ItemType Directory -Path $safeTempDir -Force | Out-Null
|
|
}
|
|
|
|
# Create safe filenames without spaces
|
|
$safeOriginalName = $originalFileName.Replace(" ", "_")
|
|
$safeOriginalPath = Join-Path $safeTempDir $safeOriginalName
|
|
|
|
# Resolve the full path
|
|
$safeOriginalPath = [System.IO.Path]::GetFullPath($safeOriginalPath)
|
|
|
|
# Copy the downloaded file to the safe path
|
|
$script:txtStatus.Text += "Copying file to safe path: $safeOriginalPath`n"
|
|
Copy-Item -LiteralPath $downloadPath -Destination $safeOriginalPath -Force
|
|
|
|
# Verify the copy
|
|
if (-not (Test-Path -LiteralPath $safeOriginalPath)) {
|
|
throw "Failed to copy file to safe path"
|
|
}
|
|
|
|
$script:txtStatus.Text += "Opening workbook from: $safeOriginalPath`n"
|
|
try {
|
|
# Try opening with different recovery options
|
|
try {
|
|
$workbook = $excel.Workbooks.Open($safeOriginalPath)
|
|
}
|
|
catch {
|
|
$script:txtStatus.Text += "Standard open failed, trying with OpenAndRepair...`n"
|
|
$workbook = $excel.Workbooks.OpenAndRepair($safeOriginalPath)
|
|
}
|
|
|
|
$script:txtStatus.Text += "Workbook opened successfully`n"
|
|
|
|
# Create safe XLSX path
|
|
$safeXlsxName = [System.IO.Path]::GetFileNameWithoutExtension($safeOriginalName) + ".xlsx"
|
|
$safeXlsxPath = Join-Path $safeTempDir $safeXlsxName
|
|
$safeXlsxPath = [System.IO.Path]::GetFullPath($safeXlsxPath)
|
|
|
|
$script:txtStatus.Text += "Attempting to save as XLSX: $safeXlsxPath`n"
|
|
|
|
# Try multiple SaveAs approaches with progressive fallbacks
|
|
$saveSuccess = $false
|
|
|
|
# Approach 1: Standard SaveAs with explicit format
|
|
try {
|
|
$script:txtStatus.Text += "Trying standard SaveAs method...`n"
|
|
$excel.DisplayAlerts = $false
|
|
$workbook.SaveAs([string]$safeXlsxPath, [int]51) # 51 = xlOpenXMLWorkbook
|
|
$saveSuccess = $true
|
|
}
|
|
catch {
|
|
$script:txtStatus.Text += "Standard SaveAs failed: $($_.Exception.Message)`n"
|
|
}
|
|
|
|
# Approach 2: Try with compatibility options disabled
|
|
if (-not $saveSuccess) {
|
|
try {
|
|
$script:txtStatus.Text += "Trying SaveAs with compatibility options disabled...`n"
|
|
$excel.DisplayAlerts = $false
|
|
if ($workbook.PSObject.Properties.Name -contains "CheckCompatibility") {
|
|
$workbook.CheckCompatibility = $false
|
|
}
|
|
$workbook.SaveAs([string]$safeXlsxPath, [int]51)
|
|
$saveSuccess = $true
|
|
}
|
|
catch {
|
|
$script:txtStatus.Text += "Compatibility SaveAs failed: $($_.Exception.Message)`n"
|
|
}
|
|
}
|
|
|
|
# Approach 3: Try with Excel 2007-2013 format
|
|
if (-not $saveSuccess) {
|
|
try {
|
|
$script:txtStatus.Text += "Trying SaveAs with Excel 2007-2013 format...`n"
|
|
$excel.DisplayAlerts = $false
|
|
$workbook.SaveAs([string]$safeXlsxPath, [int]52) # 52 = xlExcel12 (Excel 2007-2013)
|
|
$saveSuccess = $true
|
|
}
|
|
catch {
|
|
$script:txtStatus.Text += "Excel 2007-2013 SaveAs failed: $($_.Exception.Message)`n"
|
|
}
|
|
}
|
|
|
|
# Approach 4: Last resort - try with Excel binary format then convert again
|
|
if (-not $saveSuccess) {
|
|
try {
|
|
$script:txtStatus.Text += "Trying intermediate binary format conversion...`n"
|
|
$excel.DisplayAlerts = $false
|
|
|
|
# First save as Excel Binary Workbook
|
|
$safeBinaryPath = [System.IO.Path]::ChangeExtension($safeXlsxPath, ".xlsb")
|
|
$workbook.SaveAs([string]$safeBinaryPath, [int]50) # 50 = xlExcel12 (Excel Binary)
|
|
$workbook.Close($false)
|
|
|
|
# Then open the binary and save as XLSX
|
|
$binaryWorkbook = $excel.Workbooks.Open($safeBinaryPath)
|
|
$binaryWorkbook.SaveAs([string]$safeXlsxPath, [int]51)
|
|
$binaryWorkbook.Close($false)
|
|
|
|
$saveSuccess = $true
|
|
}
|
|
catch {
|
|
$script:txtStatus.Text += "Binary conversion failed: $($_.Exception.Message)`n"
|
|
}
|
|
}
|
|
|
|
if (-not $saveSuccess) {
|
|
throw "All conversion methods failed. Unable to convert file."
|
|
}
|
|
|
|
Start-Sleep -Seconds 2 # Give the file system time to catch up
|
|
|
|
if (-not (Test-Path -LiteralPath $safeXlsxPath)) {
|
|
throw "File was not created after SaveAs operation"
|
|
}
|
|
|
|
$script:txtStatus.Text += "Save successful, verifying file...`n"
|
|
$fileInfo = Get-Item -LiteralPath $safeXlsxPath
|
|
$script:txtStatus.Text += "Saved file size: $($fileInfo.Length) bytes`n"
|
|
|
|
# Copy the file back to the original destination with spaces
|
|
$script:txtStatus.Text += "Copying to final destination: $xlsxPath`n"
|
|
Copy-Item -LiteralPath $safeXlsxPath -Destination $xlsxPath -Force
|
|
|
|
$script:txtStatus.Text += "Successfully saved XLSX file`n"
|
|
}
|
|
catch {
|
|
$script:txtStatus.Text += "Error during Excel operations: $($_.Exception.Message)`n"
|
|
throw
|
|
}
|
|
}
|
|
finally {
|
|
# Thorough cleanup of Excel objects
|
|
if ($workbook) {
|
|
try {
|
|
$workbook.Close($false)
|
|
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
|
|
}
|
|
catch { }
|
|
$workbook = $null
|
|
}
|
|
if ($binaryWorkbook) {
|
|
try {
|
|
$binaryWorkbook.Close($false)
|
|
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($binaryWorkbook) | Out-Null
|
|
}
|
|
catch { }
|
|
$binaryWorkbook = $null
|
|
}
|
|
if ($excel) {
|
|
try {
|
|
$excel.Quit()
|
|
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
|
|
}
|
|
catch { }
|
|
$excel = $null
|
|
}
|
|
[System.GC]::Collect()
|
|
[System.GC]::WaitForPendingFinalizers()
|
|
|
|
# Force cleanup of Excel processes
|
|
Get-Process -Name "EXCEL" -ErrorAction SilentlyContinue |
|
|
Where-Object { $_.SI -eq [System.Security.Principal.WindowsIdentity]::GetCurrent().SessionId } |
|
|
Stop-Process -Force
|
|
}
|
|
|
|
# Upload the converted file to SharePoint
|
|
try {
|
|
# Verify file exists before trying to read it
|
|
if (-not (Test-Path -LiteralPath $xlsxPath)) {
|
|
throw "Converted file not found at: $xlsxPath"
|
|
}
|
|
|
|
# Get the folder path from the original file
|
|
$folderPath = $script:txtFolder.Text
|
|
$uploadFileName = $xlsxFileName # Use the converted file name
|
|
|
|
# Construct the upload URI for the original library
|
|
$uploadPath = if ($folderPath) {
|
|
# Extract the relative path if needed
|
|
$relativePath = $folderPath -replace "/sites/tax/Shared Documents/", ""
|
|
"$relativePath/$uploadFileName" # Include folder structure
|
|
} else {
|
|
$uploadFileName # Just use the filename to save in root
|
|
}
|
|
|
|
# URL encode the path but preserve forward slashes and encode spaces as %20
|
|
$encodedPath = $uploadPath.Split('/') |
|
|
ForEach-Object {
|
|
[System.Web.HttpUtility]::UrlEncode($_).Replace("+", "%20")
|
|
} |
|
|
Join-String -Separator '/'
|
|
|
|
# Fix the URI construction to avoid the colon issue
|
|
$baseUri = "https://graph.microsoft.com/v1.0/drives"
|
|
$driveId = $script:driveId
|
|
$uploadUri = "${baseUri}/${driveId}/root:/${encodedPath}:/content"
|
|
|
|
$script:txtStatus.Text += "Uploading to: $uploadUri`n"
|
|
|
|
# Read the file content
|
|
$fileContent = [System.IO.File]::ReadAllBytes($xlsxPath)
|
|
|
|
# Upload the file
|
|
Invoke-MgGraphRequest -Uri $uploadUri -Method PUT -Body $fileContent -ContentType "application/octet-stream"
|
|
|
|
$script:txtStatus.Text += "Successfully uploaded to library: /sites/tax/$libraryName/$uploadPath`n"
|
|
}
|
|
catch {
|
|
$script:txtStatus.Text += "Error uploading file: $($_.Exception.Message)`n"
|
|
if (-not (Test-Path -LiteralPath $xlsxPath)) {
|
|
$script:txtStatus.Text += "File not found at: $xlsxPath`n"
|
|
}
|
|
throw
|
|
}
|
|
}
|
|
catch {
|
|
$script:txtStatus.Text += "Error in Excel operations: $($_.Exception.Message)`n"
|
|
throw
|
|
}
|
|
|
|
# Clean up temporary files
|
|
try {
|
|
Remove-Item $downloadPath -ErrorAction SilentlyContinue
|
|
Remove-Item $xlsxPath -ErrorAction SilentlyContinue
|
|
}
|
|
catch {
|
|
$script:txtStatus.Text += "Error cleaning up temporary files: $($_.Exception.Message)`n"
|
|
}
|
|
}
|
|
|
|
$script:txtStatus.Text += "File conversion complete.`n"
|
|
}
|
|
catch {
|
|
if ($script:txtStatus) {
|
|
$script:txtStatus.Text += "Error in Convert-Files function: $($_.Exception.Message)`n"
|
|
} else {
|
|
Write-Host "Error in Convert-Files function: $($_.Exception.Message)"
|
|
}
|
|
}
|
|
}
|
|
|
|
function Move-Files {
|
|
try {
|
|
$script:txtStatus.Text += "Moving converted files back to original location...`n"
|
|
|
|
# Load the file list from the JSON file
|
|
$fileListPath = "$env:TEMP\FileList.json"
|
|
if (Test-Path $fileListPath) {
|
|
$fileList = Get-Content $fileListPath -Raw | ConvertFrom-Json
|
|
} else {
|
|
$script:txtStatus.Text += "Error: FileList.json not found. Please list files first.`n"
|
|
return
|
|
}
|
|
|
|
# Check if the file list is empty
|
|
if ($fileList.Count -eq 0) {
|
|
$script:txtStatus.Text += "No files to move.`n"
|
|
return
|
|
}
|
|
|
|
# Get site and drive information first
|
|
$script:txtStatus.Text += "Getting SharePoint site information...`n"
|
|
$site = Invoke-MgGraphRequest -Method GET -Uri "https://graph.microsoft.com/v1.0/sites/sutterhill.sharepoint.com:/sites/tax" -ErrorAction Stop
|
|
|
|
if (-not $site) {
|
|
throw "Could not find SharePoint site"
|
|
}
|
|
|
|
$script:txtStatus.Text += "Getting document library information...`n"
|
|
$drives = Get-MgSiteDrive -SiteId $site.id
|
|
$libraryName = if ($script:txtProdLib.Text -eq "Shared Documents") {
|
|
"Documents"
|
|
} else {
|
|
$script:txtProdLib.Text
|
|
}
|
|
$drive = $drives | Where-Object { $_.Name -eq $libraryName }
|
|
|
|
if (-not $drive) {
|
|
$script:txtStatus.Text += "Available libraries: $($drives.Name -join ', ')`n"
|
|
throw "Library not found: $libraryName"
|
|
}
|
|
|
|
# Get the drive for the temp library
|
|
$tempLibrary = if ($script:txtTempLib.Text -eq "Shared Documents") {
|
|
"Documents"
|
|
} else {
|
|
$script:txtTempLib.Text
|
|
}
|
|
$tempDrive = $drives | Where-Object { $_.Name -eq $tempLibrary }
|
|
if (-not $tempDrive) {
|
|
throw "Temp library not found: $tempLibrary"
|
|
}
|
|
|
|
# Iterate through the file list and move each file
|
|
foreach ($file in $fileList) {
|
|
$originalPath = $file.OriginalPath
|
|
$originalFileName = $file.OriginalFileName
|
|
$xlsxFileName = $originalFileName -replace "\.xls[m]?$", ".xlsx"
|
|
|
|
# Use the full TempPath to maintain folder structure
|
|
$tempPath = $file.TempPath.Replace(".xls", ".xlsx")
|
|
|
|
$script:txtStatus.Text += "Moving file: $xlsxFileName`n"
|
|
|
|
try {
|
|
# Construct the source and destination URIs with proper space encoding
|
|
$sourceUri = "https://graph.microsoft.com/v1.0/drives/$($tempDrive.Id)/root:/" +
|
|
[System.Web.HttpUtility]::UrlEncode($tempPath).Replace("+", "%20")
|
|
|
|
# For the destination, we need to extract the folder path
|
|
$folderPath = $script:txtFolder.Text
|
|
$destinationPath = if ($folderPath) {
|
|
"$folderPath/$xlsxFileName"
|
|
} else {
|
|
$xlsxFileName
|
|
}
|
|
|
|
$destinationUri = "https://graph.microsoft.com/v1.0/drives/$($drive.Id)/root:/" +
|
|
[System.Web.HttpUtility]::UrlEncode($destinationPath).Replace("+", "%20")
|
|
|
|
$script:txtStatus.Text += "Source URI: $sourceUri`n"
|
|
$script:txtStatus.Text += "Destination URI: $destinationUri`n"
|
|
|
|
# Move the file
|
|
$moveResponse = Invoke-MgGraphRequest -Uri $sourceUri -Method PATCH -Body @{
|
|
parentReference = @{
|
|
path = "/drives/$($drive.Id)/root:/$folderPath"
|
|
}
|
|
name = $xlsxFileName
|
|
} -ContentType "application/json"
|
|
|
|
$script:txtStatus.Text += "Successfully moved file: $xlsxFileName`n"
|
|
}
|
|
catch {
|
|
$script:txtStatus.Text += "Error moving file: $($_.Exception.Message)`n"
|
|
continue
|
|
}
|
|
}
|
|
|
|
$script:txtStatus.Text += "File move complete.`n"
|
|
}
|
|
catch {
|
|
$script:txtStatus.Text += "Error in Move-Files function: $($_.Exception.Message)`n"
|
|
$script:txtStatus.Text += "Stack Trace: $($_.Exception.StackTrace)`n"
|
|
}
|
|
}
|
|
|
|
# Update the status text box with proper newline handling
|
|
function Update-StatusText {
|
|
param (
|
|
[string]$Message
|
|
)
|
|
|
|
if ($script:txtStatus) {
|
|
$script:txtStatus.AppendText("$Message`r`n")
|
|
$script:txtStatus.ScrollToCaret()
|
|
}
|
|
}
|