2

I want to export SharePoint2013 list data into .xlsx or xls using PowerShell.

In my server, there is no Excel installed.

bumped to the homepage by Community 2 days ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

  • I don't want to use Export-CSV , i want only in .xlsx or xls . – phani Jun 7 '17 at 12:28
0

Please try below script for export list data csv format:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Get the Web
$web = Get-SPWeb -identity "**Please enter your web URL**"

#Get the Target List
$list = $web.Lists["**Please enter your list name**"]

#Array to Hold Result - PSObjects
$ListItemCollection = @()

 #Get All List items
 $list.Items | foreach {
 $ExportItem = New-Object PSObject
 $ExportItem | Add-Member -MemberType NoteProperty -name "Title" -value $_["Title"]

**#Please create your column variable as "Title" column variable**

 #Add the object with property to an Array
 $ListItemCollection += $ExportItem
 }
 #Export the result Array to CSV file
 $ListItemCollection | Export-CSV "c:\ListData.csv" -NoTypeInformation                       

#Dispose the web Object
$web.Dispose()


### Set input and output path
$inputCSV = "c:\ListData.csv"
$outputXLSX = "c:\ListData.xlsx"

### Create a new Excel Workbook with one empty sheet
$excel = New-Object -ComObject excel.application 
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)

### Build the QueryTables.Add command
### QueryTables does the same as when clicking "Data » From Text" in Excel
$TxtConnector = ("TEXT;" + $inputCSV)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)

### Set the delimiter (, or ;) according to your regional settings
$query.TextFileOtherDelimiter = $Excel.Application.International(5)

### Set the format to delimited and text for every column
### A trick to create an array of 2s is used with the preceding comma
$query.TextFileParseType  = 1
$query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1

### Execute & delete the import query
$query.Refresh()
$query.Delete()

### Save & close the Workbook as XLSX. Change the output extension for Excel 2003
$Workbook.SaveAs($outputXLSX,51)
$excel.Quit()

It will work only in SharePoint OnPremise.

Reference Link: http://www.sharepointdiary.com/2013/04/export-sharepoint-list-items-to-csv-using-powershell.html

Hope it works for you.

  • Thanks , i don't want to use Export-CSV . i want to export data only in xls or xlsx format only. – phani Jun 7 '17 at 12:26
  • Please try edited answer – Samir Khimani Jun 7 '17 at 12:30
  • I am getting the error, at the time of creating the excel for this line."$excel = New-Object -ComObject excel.application " "New-Object : Retrieving the COM class factory for component with CLSID {00000000-0000-0000-0000-000000000000} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG))." – phani Jun 7 '17 at 12:57
  • You need to have MS Office installed in the machine in which you run the above powershell code. I suppose it should be a 64bit version of MS Office. – Karthik Jaganathan Jun 8 '17 at 5:03
0

Try the below code, The below code tries to create xls file, in order to create it, the code needs MS Office needs to be installed in the machine, in which you run this PowerShell

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Get the Web
$web = Get-SPWeb -identity "**Please enter your web URL**"

#Get the Target List
$list = $web.Lists["**Please enter your list name**"]

#Array to Hold Result - PSObjects
$ListItemCollection = @()

 #Get All List items
 $list.Items | foreach {
 $ExportItem = New-Object PSObject
 $ExportItem | Add-Member -MemberType NoteProperty -name "Title" -value $_["Title"]

**#Please create your column variable as "Title" column variable**

 #Add the object with property to an Array
 $ListItemCollection += $ExportItem
 }
 #Export the result Array to CSV file
 $ListItemCollection | Export-CSV "c:\ListData.csv" -NoTypeInformation                       

#Dispose the web Object
$web.Dispose()

#This below line needs, MS office to be installed in the machine to create an object of xls type.
$xl = new-object -comobject excel.application
$xl.visible = $true
$Workbook = $xl.workbooks.open("c:\ListData.csv")
$Workbook.SaveAs("c:\ListData.xls",1)
$Workbook.Saved = $True
$xl.Quit()

Remove-PSSnapin Microsoft.SharePoint.PowerShell 

If you don't have MS Office and run the code, then you might get the following error,

New-Object : Retrieving the COM class factory for component with CLSID {00000000-0000-0000-0000-000000000000} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).

the error is produced by the below code,

$xl = new-object -comobject excel.application

AFAIK there is no direct approach to create xls file from SharePoint list using PowerShell without MS Office installed.

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.