Powershell : Read excel file data in document library and then modify it and upload it back using powershell in SharePoint Online

Question

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

Answer 1

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)

Answer 2

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