1

I have a user asking if its possible to import an excel file with multiple spreadsheets to a SharePoint 2010 solution? Each spreadsheet should be turned into one list. It is an autogenerated excel file with 10-15 spreadsheets, and today they have to save each spreadsheet as a new excel file and import the files one by one which is very time consuming. Is there any easier solution without the need of coding? Thank you!

  • There might be a third party add-on that can do this for you. I don't know of any way you could do this out of the box without code. – Submits Feb 11 '16 at 9:07
0

There are two ways (apart from .NET code) how to import spreadsheets into SharePoint using PowerShell. If, of course, PowerShell doesn't mean coding for you:

  1. Convert each sheet into separate CSV file and use a PS script similar to this one:
$InvFile="file.csv"
$FileExists = (Test-Path $InvFile -PathType Leaf)
if ($FileExists) {
  $tblData = import-csv $InvFile

  foreach ($row in $tblData) { 
      $columnValue = $row."Project Code".ToString() 
  }
}
  1. Use Excel com object to open the spreadsheet. This will require Excel installed on the computer you are running the script on:

$FilePath = "C:\file.xlsx" $SheetName = "BuildSpecs" $objExcel = New-Object -ComObject Excel.Application $objExcel.Visible = $false $WorkBook = $objExcel.Workbooks.Open($FilePath) $WorkSheet = $WorkBook.sheets.item($SheetName)

and then get the data by using:

 $worksheet.Range("C3").Text $worksheet.Range("C3:C3").Text
 $worksheet.Range("C3","C3").Text $worksheet.cells.Item(3, 3).text
 $worksheet.cells.Item(3, 3).value2
 $worksheet.Columns.Item(3).Rows.Item(3).Text
 $worksheet.Rows.Item(3).Columns.Item(3).Text
 $worksheet.UsedRange.Range("c3").Text

There are plenty of tutorials on the web how to open lists and add new items in PowerShell. For example here.

Hope this helps.

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.