0

I am looking for powershell solution which will read xml file in document library and loop throgh all rows and add/modify rows/columns on the excel file and upload it back to sharepoint. Tried OpenXMl solution but not got any success. Any solution lead will be appreciated.

Environment in sharepoint online.Trying to relate this solution in powershell

0

I was able to figure this out on my own. Sharing code part. This will help others who are trying to achieve something similar. Answer has how to read excel data from the file stored in SharePoint and also update the excel file and store it back to SharePoint.

#Get SharePoint File Stream
$fileServerRelativeUrl = "/sites/DevTeamSiteModern/MyLibrary/test.xlsx";
$file = $Context.Web.GetFileByServerRelativeUrl($fileServerRelativeUrl)
$data = $file.OpenBinaryStream();
$Context.Load($file)
$Context.ExecuteQuery()

#$stream = [System.IO.MemoryStream]::new()

#Copy Sharepoint file stream to System.IO.MemoryStream
$stream = New-Object -TypeName System.IO.MemoryStream
$data.Value.CopyTo($stream)

[DocumentFormat.OpenXml.Packaging.SpreadsheetDocument]$document = $null

#Open Document with the Stream value
$document = [DocumentFormat.OpenXml.Packaging.SpreadsheetDocument]::Open($stream, $true)

[DocumentFormat.OpenXml.Spreadsheet.SheetData]$sheetData = ($document.WorkbookPart.WorksheetParts.RootElement |? { $_.LocalName -eq "sheetData" })[0]

#Get All rows
[DocumentFormat.OpenXml.OpenXmlElementList]$rows = $sheetData.ChildElements

#Generating String table to read existing values
$stringTable  = $document.WorkbookPart.SharedStringTablePart

#Iterating through All rows
for ($i=0; $i -lt $rows.Count; $i += 1) {
    [DocumentFormat.OpenXml.Spreadsheet.Row]$row = $rows.GetItem($i)

    # iterate row cells and printing its values refer this for reading excel data
    [DocumentFormat.OpenXml.OpenXmlElementList]$cells = $row.ChildElements
    for ($j=0; $j -lt $cells.Count; $j += 1) {           
        [DocumentFormat.OpenXml.Spreadsheet.Cell]$cell = $cells.GetItem($j)
        $cellCount = $cell.InnerText
        $searchString = $stringTable.SharedStringTable.ChildElements[$cellCount].InnerText
        Write-Host $searchString
    }

    # Adding data to cell of spreadsheet
    $t = $i + 1
    #$cellObject = [DocumentFormat.OpenXml.Spreadsheet.Cell]::new()
    $cellObject = New-Object -TypeName DocumentFormat.OpenXml.Spreadsheet.Cell
    $cellref = "M" + $t; #Cell Number which we want to update eg. M1 , M2 , M3 refering this example
    $cellObject.CellReference = $cellref;
    $cellObject.DataType = [DocumentFormat.OpenXml.Spreadsheet.CellValues]::String;
    $cellObject.CellValue = "Sample Data to Input in excel file"
    $row.Append($cellObject)

}

$document.Close()
#Saving this update stream back to SharePoint. This will update the file in sharepoint.
$stream.Seek(0, [System.IO.SeekOrigin]::Begin)
[byte[]]$bytes = [System.Byte[]]::CreateInstance([System.Byte],$stream.Length)
$stream.Read($bytes,0,$stream.Length)
$input = New-Object System.IO.MemoryStream( , $bytes )

[Microsoft.SharePoint.Client.File]::SaveBinaryDirect($Context, $fileServerRelativeUrl, $input, $true)
| improve this answer | |
-1

Per my knowledge this is not a scenario you can do with only PowerShell. You will need to write a custom solution in VS for all your requirements

| improve this answer | |
  • No , it is possible with Powershell as well. Please refer my answer. I was able to read and update excel data of the file in sharepoint – Bhaskar Dhone Jun 2 at 15:33

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.