I have written the power shell script to export the view in to a CSV file. But I want the same data to be exported to EXCel. I don't have excel installed on my share point server and my client does not allow to install on the server.
The Excel should be in the below format with color formatting.Any help on this is appreciated.
I tried with Office Interop but still no luck. It gives Com application Error.
Power shell Code:
Add-PsSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue -WarningAction SilentlyContinue | Out-Null
$site = new-object Microsoft.SharePoint.SPSite("http://mytest.services.com")
$web = $site.RootWeb
$list = $web.Lists["MyCustomList"]
$view = $list.Views["MyView"]
$items = $list.GetItems($view)
$items | %{ select-object -input $_ -prop @{Name='ID';expression={$_.ID;}}, @{Name='Issue ID';expression={$_["Issue ID"];}},@{Name='Title';expression={$_.Title;}},@{Name='Issue Type';expression={$_["Issue Type"];}},@{Name='Priority';expression={$_["Priority"];}},@{Name='Issue Status';expression={$_["Issue Status"];}},@{Name='Assigned To';expression={$userfield=New-Object Microsoft.SharePoint.SPFieldUserValue($web,$_["Assigned To"]);$userfield.User.DisplayName;}},@{Name='Reported On';expression={$_["Reported On"];}},@{Name='Reported By';expression={$_["Reported By"];}},@{Name='Description';expression={[Microsoft.SharePoint.Utilities.SPHttpUtility]::ConvertSimpleHtmlToText($_["Description"],-1) -replace '\s+', ' ';}},@{Name='Comments';expression={[Microsoft.SharePoint.Utilities.SPHttpUtility]::ConvertSimpleHtmlToText($_["Comments"],-1) -replace '\s+', ' ';}},@{Name='Resolution Notes';expression={[Microsoft.SharePoint.Utilities.SPHttpUtility]::ConvertSimpleHtmlToText($_["Resolution Notes"],-1) -replace '\s+', ' ';}},@{Name='Date Closed';expression={$_["Date Closed"];}};} | Export-Csv -Path "D:\Test\MyView $(get-date -f MM-dd-yyyy).csv" -NoType
Out Format of Excel should be like this with Color formatting.
