none
Reading Excel File from Document Library? RRS feed

  • Question

  • Hi All,
    I am trying to read and Excel file held in Document Library. I need to read the data (column n Row data) through excel file and populate the data to the list. I am not allowed to download the file on the machine. I have tried to do it but I have got the file from the document library in SPListItem object and I am not able to cast or convert it to Excel workbook object. Does anyone can help me out plz????

    Thanks,
    JOhn
    • Edited by Mike Walsh FIN Tuesday, July 14, 2009 9:00 AM ??? removed from Title. ONE question mark only
    Thursday, July 24, 2008 1:53 PM

Answers

  • Hi,
    I was also working on the same problem..
    following is the code for this...
    First add reference of two COM components.
    1. Microsoft Office Object Library 
    2. Microsoft Excel Object Library


    excel = new ApplicationClass();
    excel.Visible = false;
    string workbookPath = url;
    Workbook excelWorkbook = excel.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false);
                
    Sheets sheets = excelWorkbook.Worksheets;
    Worksheet worksheet = (Worksheet)sheets.get_Item(1);
    bool flag = false; ;
    for (int i = 1; i <= worksheet.Rows.Count; i++)
    {
             Range range = worksheet.get_Range("A" + i.ToString(), "I" + i.ToString());
             Array myvalues = (System.Array)range.Cells.Value2;
             string[] strArray = ConvertToStringArray(myvalues);
    }

           


    yugal
    Friday, October 10, 2008 11:53 AM

All replies

  • Are you running MOSS? If so are you running the Excel Calculation Service? You may be able to get what you need using this service MOSS with out having to install excel on the server. 

    http://msdn.microsoft.com/en-us/library/ms546696.aspx
    http://www.certdev.com
    Thursday, July 24, 2008 2:50 PM
  • I am running moss but I am not allowed to use Excel Calculation Sevices, i need to do it with object model
    Thursday, July 24, 2008 8:46 PM
  • Instead of getting a SPListItem Object, you need to get the SPFile object for the same item.  SPLists have a RootFolder property to help you out here.

    After you have the SPFile object, you can use OpenBinary, and read the contents in memory.  Then find a method on the Excel object that takes a byte array, or convert to a stream. 

    Read your values and cleanup the objects.

    Good luck
    -DK http://dkeeling.spaces.live.com
    Monday, July 28, 2008 5:57 AM
  • Hi,

    Please share code to read excel file data from sharepoint document library.

    Thanks,
    Ganesh
    Tuesday, September 9, 2008 2:41 AM
  • Hi,
    I was also working on the same problem..
    following is the code for this...
    First add reference of two COM components.
    1. Microsoft Office Object Library 
    2. Microsoft Excel Object Library


    excel = new ApplicationClass();
    excel.Visible = false;
    string workbookPath = url;
    Workbook excelWorkbook = excel.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false);
                
    Sheets sheets = excelWorkbook.Worksheets;
    Worksheet worksheet = (Worksheet)sheets.get_Item(1);
    bool flag = false; ;
    for (int i = 1; i <= worksheet.Rows.Count; i++)
    {
             Range range = worksheet.get_Range("A" + i.ToString(), "I" + i.ToString());
             Array myvalues = (System.Array)range.Cells.Value2;
             string[] strArray = ConvertToStringArray(myvalues);
    }

           


    yugal
    Friday, October 10, 2008 11:53 AM
  • If you are building a server side solution see the following first: http://support.microsoft.com/kb/257757

    Posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, October 10, 2008 12:10 PM
  • Hi Yugal,

    i am trying to implement the same, i just copied your code everything is fine but debugging the application after Workbook excelWorkbook = excel.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false); this line it is not proceeding to the next line. Please help me. Thanks in Advance. 
    Monday, July 13, 2009 8:12 PM
  • For

     string workbookPath = url;

    What exactly is the URL? is it the http://url path or is it //server path?

    Thanks

     

    Wednesday, April 21, 2010 5:58 PM
  • Me also facing the same problem. please share the code if you resloved.

    Wednesday, August 22, 2012 7:13 AM
  • Please create a folder in below path. This will resolve..

    ・Windows 2008 Server x64

    Please make this folder.

    C:\Windows\SysWOW64\config\systemprofile\Desktop

    ・Windows 2008 Server x86

    Please make this folder.

    C:\Windows\System32\config\systemprofile\Desktop

    Thanks & Regards

    Rakesh

    Tuesday, October 30, 2012 2:06 PM