locked
c# call excel which locate inside sharepoint RRS feed

  • Question

  • I would like to use MS chart to project some Excel data. However, my excel is in sharepoint, What is my connection string should look like? My code to call Excel.

     

    string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\tmp\\test.xls;Extended Properties=\"Excel 8.0;HDR=YES\"";

     

    OleDbConnection cn = new OleDbConnection();

    cn.ConnectionString = sConnectionString;

    cn.Open();

     

    string sSQL = "SELECT * FROM [Sheet1$]";

     

    OleDbCommand cmd = cn.CreateCommand();

    cmd.CommandText = sSQL;

     

    OleDbDataAdapter da = new OleDbDataAdapter(cmd);

     

     

    da.Fill(excelList);

    dataGridView1.DataSource = excelList;


    Thanks.


    http://lamjoel.blogspot.com/
    • Moved by OmegaMan Wednesday, May 13, 2009 1:51 PM (From:Visual C# General)
    Wednesday, May 13, 2009 3:51 AM

Answers

  • Let me try to be a bit proactive about this.  In case anyone is looking to do something similar.

    Of course if you have Excel services, you would open the spreadsheet up using the Excel Services Web Service (ASMX) you will find under the "_vti_bin" directory of the web application - IF you have the enterprise edition of MOSS.  Excel Services IS the "excel object model" running on the server!

    If you don't, no big deal.  But PLEASE make sure the sheet is Excel 2007 format (XLSX) - the Open XML standard.  Then you can use Open XML programming to access the information in your spreadsheet - even modify it  - or even create a whole new Excel file.  WITHOUT needing the Excel object model.

    OPEN XML development - http://www.openxmldeveloper.org.

    It's not the most fun programming in the entire world, but Microsoft has developed an SDK for open XML to make it MUCH easier:
    http://www.microsoft.com/downloads/details.aspx?FamilyId=C6E744E5-36E9-45F5-8D8C-331DF206E0D0&displaylang=en

    Remember the spreadsheet is just a "file" in your "virtual file system" of a web site (in the content database).  You can use SPFolder and SPFile to "get" at your spreadsheet (and/or SPList / SPDocumentLibrary).  It's like standard file IO.

    Ed.
    Wednesday, May 20, 2009 11:47 AM

All replies

  • Are you saying that the Excel sheet is in a SharePoint document library and you wish to access it programmatically?  Is it (or can it be) in Excel 2007 format?  Do you have Enterprise Edition available to access the file using Excel services?  Ed.

    Tuesday, May 19, 2009 9:59 PM
  • Let me try to be a bit proactive about this.  In case anyone is looking to do something similar.

    Of course if you have Excel services, you would open the spreadsheet up using the Excel Services Web Service (ASMX) you will find under the "_vti_bin" directory of the web application - IF you have the enterprise edition of MOSS.  Excel Services IS the "excel object model" running on the server!

    If you don't, no big deal.  But PLEASE make sure the sheet is Excel 2007 format (XLSX) - the Open XML standard.  Then you can use Open XML programming to access the information in your spreadsheet - even modify it  - or even create a whole new Excel file.  WITHOUT needing the Excel object model.

    OPEN XML development - http://www.openxmldeveloper.org.

    It's not the most fun programming in the entire world, but Microsoft has developed an SDK for open XML to make it MUCH easier:
    http://www.microsoft.com/downloads/details.aspx?FamilyId=C6E744E5-36E9-45F5-8D8C-331DF206E0D0&displaylang=en

    Remember the spreadsheet is just a "file" in your "virtual file system" of a web site (in the content database).  You can use SPFolder and SPFile to "get" at your spreadsheet (and/or SPList / SPDocumentLibrary).  It's like standard file IO.

    Ed.
    Wednesday, May 20, 2009 11:47 AM
  • Let me try to be a bit proactive about this.  In case anyone is looking to do something similar.

    Of course if you have Excel services, you would open the spreadsheet up using the Excel Services Web Service (ASMX) you will find under the "_vti_bin" directory of the web application - IF you have the enterprise edition of MOSS.  Excel Services IS the "excel object model" running on the server!

    If you don't, no big deal.  But PLEASE make sure the sheet is Excel 2007 format (XLSX) - the Open XML standard.  Then you can use Open XML programming to access the information in your spreadsheet - even modify it  - or even create a whole new Excel file.  WITHOUT needing the Excel object model.

    OPEN XML development - http://www.openxmldeveloper.org.

    It's not the most fun programming in the entire world, but Microsoft has developed an SDK for open XML to make it MUCH easier:
    http://www.microsoft.com/downloads/details.aspx?FamilyId=C6E744E5-36E9-45F5-8D8C-331DF206E0D0&displaylang=en

    Remember the spreadsheet is just a "file" in your "virtual file system" of a web site (in the content database).  You can use SPFolder and SPFile to "get" at your spreadsheet (and/or SPList / SPDocumentLibrary).  It's like standard file IO.

    Ed.
    Thanks for the reply! I get your idea, but our company is using office 2003 and wss 3.0...

    actually, I want to create a asp.net application with MS chart to display the data from a excel file which locate inside sharepoint.

    any idea..?
    Thanks in advance!

    Joel

    http://lamjoel.blogspot.com/
    Thursday, May 21, 2009 4:09 PM
  • Hi Joel, 

    By the way.  JUST ONE PERSON needs Excel 2007!  Surely that is worth the pain avoidance.  If you can upgrade the workbook(s) you need to work with to Excel 2007 format and then publish??

    But if 2003..  Alas then that is not really a "SharePoint" issue, per se (except for where the file might be stored - a trivial issue to find and open).  You'd face the same difficulty if you were just writing a plain old ASP.NET application.  And THAT unpleasant answer usually involves installing the office run time on the server (to have access to the object model).

    MS has a good article on this:
    http://support.microsoft.com/default.aspx/kb/257757 

    Which has the following text: Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

    I do wish you well in your endeavours!!!
    Ed.
    Thursday, May 21, 2009 6:00 PM
  • Thanks Musters.

    I thought I can access by the simply way I mentioned... seems there is no easy way to do this... openxml sounds a bit complex....

    maybe I will give up to use sharepoint for this file..


    http://lamjoel.blogspot.com/
    Monday, May 25, 2009 2:50 AM