Writing Excel file to Azure Storage

[grokkit]

Hi All.

I have written a script that will do the following.

Export data to 2 csv files.

Combine the csv files onto a single excel workbook.

Password protect the files and save the file onto C:\file.

Now I want to change the script such that it runs within an azure setup, running the code from a pipeline as opposed to my local machine, the excel file should also be saved on an azure storage account.

PowerShell

$csvFilePath = "D:\extract\A.csv"   $excelFilePath = "D:\extract\B.xls" $csvFilePath2 = "D:\extract\C.csv"   $results | export-csv  $csvFilePath   -NoTypeInformation -Force $results2 | export-csv  $csvFilePath2   -NoTypeInformation -Force $filepath='D:\extract\*' $excelfile = "D:\extract\Extract_ $(get-date -format ddMMyyyy).xls"  function Add-Worksheet{     Param(         $filepath     )         Try{             Write-Host $_.Fullname -ForegroundColor green               $ws=$wb.Sheets.Add($mv,$mv,$mv,$filepath)             $ws.Cells.EntireColumn.AutoFit()         }         Catch{             Write-Host "File cannot be loaded: $filepath" -ForegroundColor red         } } $mv=[System.Reflection.Missing]::Value  $Excel = New-Object -Com Excel.Application $Excel.visible=$false $wb=$Excel.Workbooks.Add() Get-ChildItem $filepath -Include *.csv |     Sort-Object Name -desc|     ForEach-Object{         Add-Worksheet $_.Fullname     } $wb.Worksheets.Item('Sheet1').Delete() $xlExcel8 = 56 $Excel.DisplayAlerts = $false; $wb.SaveAs($excelfile,$xlExcel8,"password")  $wb.Close() $excel.quit()  $excel = $null $Attach += $excelFilePath $Attach +=$csvFilePath  #Delete the old csv files. Get-ChildItem $filepath -include *.csv -Recurse | Remove-Item

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79

$csvFilePath = "D:\extract\A.csv"     $excelFilePath = "D:\extract\B.xls"   $csvFilePath2 = "D:\extract\C.csv"     $results | export-csv  $csvFilePath   -NoTypeInformation -Force   $results2 | export-csv  $csvFilePath2   -NoTypeInformation -Force   $filepath='D:\extract\*'   $excelfile = "D:\extract\Extract_ $(get-date -format ddMMyyyy).xls"    function Add-Worksheet{       Param(           $filepath       )           Try{               Write-Host $_.Fullname -ForegroundColor green                 $ws=$wb.Sheets.Add($mv,$mv,$mv,$filepath)               $ws.Cells.EntireColumn.AutoFit()           }           Catch{               Write-Host "File cannot be loaded: $filepath" -ForegroundColor red           }   }   $mv=[System.Reflection.Missing]::Value    $Excel = New-Object -Com Excel.Application   $Excel.visible=$false   $wb=$Excel.Workbooks.Add()   Get-ChildItem $filepath -Include *.csv |       Sort-Object Name -desc|       ForEach-Object{           Add-Worksheet $_.Fullname       }   $wb.Worksheets.Item('Sheet1').Delete()   $xlExcel8 = 56   $Excel.DisplayAlerts = $false;   $wb.SaveAs($excelfile,$xlExcel8,"password")    $wb.Close()   $excel.quit()    $excel = $null   $Attach += $excelFilePath   $Attach +=$csvFilePath    #Delete the old csv files.   Get-ChildItem $filepath -include *.csv -Recurse | Remove-Item

• This topic was modified 3 months, 2 weeks ago by grokkit. Reason: code formatting - please read the guide

[Author]

Posts