0

I want to programmatically create/update Sharepoint Online Item Lists every time an Excel file is checked in.

Is there any way to do this and what's the best way to do it or even start researching about it.

Any info on this would really help.

Would there also be a way to create/update Excel files programmatically based on JSON data retrieved through an external API

haknick
  • 1,892
  • 1
  • 20
  • 28
  • Thanks for the reply Marek. I am actually in the scoping phase right now and I'm trying to define how much this will take me. ItemCheckedIn is a great start http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spitemeventreceiver.itemcheckedin.aspx and seems available in sandboxed solutions which I take it that it will work on Sharepoint Online for me as long as I have the micro site Sandboxed. Do you know any calls I could use to read the Excel file and what else to be looking into creating/updating item lists. – haknick Oct 18 '12 at 22:21
  • http://stackoverflow.com/questions/15828/reading-excel-files-from-c-sharp covers this - however, you need to check if Jet.OLEDB or other appropriate driver is available in the SharePoint Online environment. – Marek Grzenkowicz Oct 19 '12 at 06:55

1 Answers1

1

You can setup an event handler on that document library. Open the file with something like epplus, parse the excel file and using Sharepoint Server Object Model update, insert List items.

Event Handlers: http://www.c-sharpcorner.com/uploadfile/Roji.Joy/event-handler-in-sharepoint-2010/

You can get the reference to the file inside the event handler as:

public override void ItemUpdated(SPItemEventProperties properties)
{
       SPFile file = properties.ListItem.File;
       byte[] fileBin = file.OpenBinary();
       //use epp to open the binary array and perform some ListItem manipulation
}
Luis
  • 5,979
  • 2
  • 31
  • 51
  • Thanks Luis, regarding the second part of my question, Is this http://msdn.microsoft.com/en-us/library/hh412217.aspx the best way to read data from a JSON API ? Do you know a better one ? I take it that once I read the data I can use epplus to write into an Excel file – haknick Oct 24 '12 at 02:19
  • What is your external API? Is this a normal REST web service? You can use a normal REST client for .net inside your event handler. I would stay away from the BCS services as they are a pain to configure. And it would be overkill for what you are trying to do. – Luis Oct 24 '12 at 03:08
  • Normal REST JSON service, would something like restsharp work or does it have to be Sharepoint Online specific ? I was going to use BCS for the Secure Store capabilities http://msdn.microsoft.com/en-us/library/hh412217.aspx#IntroToBCSinSPO_SecureStore Can I connect tie in Secure Store without BCS Thanks a lot with the help on this – haknick Oct 24 '12 at 04:21
  • Yeah, I think you can access it programatically, check this: http://msdn.microsoft.com/EN-US/library/ff394459 – Luis Oct 24 '12 at 05:13