Import multiple excel spreadsheets to sharepoint 2010 lists

Question

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!

Answer 1

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() 
  }
}

2. 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.