none
read excel file using open xml api and import it to SharePoint list / doc.lib RRS feed

  • Question

  • hi,

    i would like to read an excel file and read the headers in the excel file and  insert the records into  sp 2010 splist/doc lib.
    is it possible, any sample code is appeciated

    thnx

    Saturday, November 30, 2013 2:01 PM

Answers

All replies

  • Hi,

    Welcome to MSDN forum.

    According to your description, you want to read headers in the Excel workbook and insert the headers into SharePoint.

    It is possible.

    1. Read headers from an Excel workbook using OpenXml SDK.

    Here is a sample to read a header in the workbook from MSDN:

    How to: Get a column heading in a spreadsheet document (Open XML SDK)

    What you should do is to loop all columns then get every header.

    Here is another helpful blog to loop all rows and cells:

    Parsing and Reading Large Excel Files with the Open XML SDK

        2. Insert data into SharePoint list.

                 Here is a sample to add or remove list item:

                 How to: Add or Delete List Items

    In addition, if you have any question about SharePoint programming, I recommend you to get support from SharePoint 2010 - Development and Programming forum.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, December 3, 2013 1:43 AM
    Moderator
  • hi George,

    thnx for the reply.

    i have a doubt :

    1)  what if the excel  contains 20,000  rows. as per the sp 2010 limit/threshold value of a  splist is 5000 items only. if it exceeds, it will display a  message  abt the  threshold value. so  the per. of pulling the data/records form the splist reduce. this is what i  understood.[ pls correct me if i am wrong].

    so in this case, how can i handle the situation,  can i create a custom folders programmatically such that if it exceeds 5000 items then create a folder with a myfolder_datecreated_ and store the records.

    1.a) if  i implement this logic ,then the perf.again reduces. any workaround for this.

    2) assuming that the  column header in the excel changes after sometime[ 1 year / 2 years down the line]

    then how can i handle this scenario. i would like  to map these columns in excel with doc.lib columns.

    any ideas are appreciated.

    thnx again


    • Edited by SaMolPP Tuesday, December 3, 2013 5:52 AM
    Tuesday, December 3, 2013 5:52 AM
  • Hi,

    >> how can i handle the situation,  can i create a custom folders programmatically such that if it exceeds 5000 items then create a folder with a myfolder_datecreated_ and store the records.<<

    According to the link below, the default maximum value of List view threshold is 5000:

    SharePoint Server 2010 capacity management: Software boundaries and limits

    Here is a blog to change the settings you can try:

    SharePoint 2010: How to Change the List View Threshold and Other Resource Throttling Settings

    >> i would like  to map these columns in excel with doc.lib columns <<

    As far as I know, there is no such feature to link an Excel file at SharePoint side.

    If this no help, since the questions are more related to SharePoint, I recommend you repost your questions to SharePoint 2010 - General Discussions and Questions forum for more effective responses.

    In addition, this forum is used to discuss questions about VBA, VSTO, OpenXML SDK, Office apps etc., if you have questions about these technologies, feel free to post questions here.

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, December 3, 2013 10:17 AM
    Moderator