1

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.

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. enter image description here

1

EPPlus

EPPlus is a .net library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx).

Below solution is not tested

You can copy over the Excel InterOp dll's and then execute PowerShell referencing the Excel functions that are available in the interop.

For example: You can create a new excel file using below statements

$excel = New-Object -ComObject excel.application
$workbook = $excel.Workbooks.Add()
$workbook.SaveAs("C:\Sample.xlsx")
$excel.Quit()

You can color code the file using

$s1= $workbook.Worksheets.Item(1)
$s1.Cells.Item($row,$column).Font.Color = 8210719

More on this article http://learn-powershell.net/2012/12/20/powershell-and-excel-adding-some-formatting-to-your-report/

  • I tried installing the Interop dlls but still it gives me an error .New-Object : Cannot load COM type excel.application. At line:1 char:20 + $excel = New-Object <<<< -ComObject excel.application + CategoryInfo : InvalidType: (:) [New-Object], PSArgumentExcepti on + FullyQualifiedErrorId : CannotLoadComObjectType,Microsoft.PowerShell.Com mands.NewObjectCommand – santosh kondapalli Oct 30 '14 at 13:46
  • Do we need to install office 2010 in order to use interop services. Because when i try the above script in power shell where office is installed it works perfectly. Can you please let me know if any other alternate solution for the same. – santosh kondapalli Oct 31 '14 at 9:50
  • If you are not allowed to install excel, then I would suggest to try with EPPlus. – Amal Hashim Oct 31 '14 at 11:51
  • I think we ca not use this EPPLUS in power shell right ?? – santosh kondapalli Nov 4 '14 at 12:23
  • Its a .Net Library so you should be able to.. Not Sure though – Amal Hashim Nov 4 '14 at 12:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.