none
Reading Excel file content from SharePoint 2010 document library using C Sharp

    Question

  • Hi,

    I have created a visual web-part using Visual studios 2010 and deployed it on server.

    My visual web-part contains 5 fields as Server Name, Library Name, File Name, Sheet Name and Cell Name where user enters the necessary values for each of them and on click of the button below the fields the value of Cell (whose name is entered in Cell name field) should be visible on the label on the form.

    I am able to succeed with the part where I have to check whether a file is present in document library or not by using the following code and logic (you can use this code for your reference too as this works fine) but now I want to modify the code to reach the level so that I can read the content of cells too.

    using System;

    using System.Web.UI;

    using System.Web.UI.WebControls;

    using System.Web.UI.WebControls.WebParts;

    using Microsoft.SharePoint;

    using System.IO;

    using System.Web;

     

    namespace FileCheck.FileCheck

    {

        public partial class FileCheckUserControl : UserControl

        {

            protected void Page_Load(object sender, EventArgs e)

            {

     

            }

     

            protected void Button1_Click(object sender, EventArgs e)

            {

                Label4.Text = "http://" + TextBox3.Text + '/' + TextBox2.Text + '/' + TextBox1.Text;

                using (SPSite site = new SPSite("http://dc2"))

                 {

                    using (SPWeb web = site.OpenWeb())

                    {

                     

                       // string fileUrl = string.Format("{0}/{1}/{2}", web.Url, documentLibName, fileName);

                        string fileUrl = Label4.Text;

                        SPFile file = web.GetFile(fileUrl);

                       

                        if (file.Exists)

                            Label5.Text = "File exist";

                        else

                            Label5.Text = "File not found!!!";

                    }

                }

            }

        }


    Sachin D (Sharepoint Developer)
    Monday, August 01, 2011 8:10 AM

Answers

  • Hi All/Bhavyesh,

    It seems I figure out the code that will read the excel content and display on the page. Below is the Eureka code :) :P

     

     
    using System;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using Microsoft.SharePoint;
    using System.IO;
    using System.Web;
    using Microsoft.Office.Excel.Server.WebServices;
    using Microsoft.Office.Excel.Server;
    using System.Web.Services;
    using System.Data; 
    namespace FileCheck.FileCheck
    
    {
    public partial class FileCheckUserControl : UserControl
    {
    protected void Page_Load(object sender, EventArgs e)
    {
    using (SPSite site = new SPSite(http://dc2))
    {
    using 
    (SPWeb web = site.OpenWeb())
    { 
     
     
     
    // Code to show excel data in table format but only of couple of cells
     
     
     
    //Instantiate the Web service and make a status array object
     
     
    ExcelService xlservice =
    new 
    ExcelService(); 
    Status[] outStatus;
     
     
     
    //Using workbookPath this way will allow you to call the workbook remotely.
     
     
     
    string targetWorkbookPath = "http://servername/doclibname/book1.xlsx" 
    ; 
     
     
     
    try
     
     
    {
     
    //Call open workbook, and point to the trusted 
     
     
     
    //location of the workbook to open.
     
     
     
    string sessionId = xlservice.OpenWorkbook(targetWorkbookPath, "en-US", "en-US", out 
    outStatus); 
     
     
     
    object[] rangeResults = xlservice.GetRangeA1(sessionId, "Sheet1", "B2:S8", true, out 
    outStatus); 
     
     
     
    //byte[] workbook = xlservice.GetWorkbook(sessionId, WorkbookType.FullWorkbook, out outStatus);
     
     
     
    foreach (object[] rangeResult in 
    rangeResults) 
    {
     
     
     
    String str = (String.Format("{0}{1}{2}{3}{4}{5}{6}{7}{8}" 
    , rangeResult[0], rangeResult[1], rangeResult[2], rangeResult[3], rangeResult[4], rangeResult[5], rangeResult[6], rangeResult[7], rangeResult[8])); 
     
     
     
    DataList.DataSource = str; //Added a AASP.NET datalist controol on the form to show the excel data
    DataList 
    .DataBind(); //Bindind to datalist
     
    }
    xlservice.CloseWorkbook(sessionId,
    out 
    outStatus); 
    }
     
     
     
    catch (Exception 
    ) 
    {
    Label4.Text = "Error" 
    ; 
    }
    }
    }
    }
    }
    }
    


    The above code will display all the content of "Sheet1" with "B2:S8" depending on the array assigned to them.

    Do let me know in case of any further explanation.

     

    Sachin D (Sharepoint Developer)


    Wednesday, August 10, 2011 7:38 AM

All replies

  • Hi Sachin,

    If you want to use Microsoft Office API for reading excel file, you can use code similar to the one mentioned in this post

    http://www.sharepointwithattitude.com/archives/61


    A random burst of technology notes Blog: http://rhythmiccoding.blogspot.com
    Monday, August 01, 2011 8:21 AM
  • Thanks for such a prompt reply Bhavyesh,

    Howver I am very new to .Net Programming and the code that is shown under this link http://www.sharepointwithattitude.com/archives/61 seems quite complex for me :(

    Can you please help me by guiding, where i should add the code from the link to my above code.

    Your Help is much appricated.


    Sachin D (Sharepoint Developer)
    Monday, August 01, 2011 8:29 AM
  • Hi Sachin,

    mention not. That's what we are here for, guiding each other in technology related queries/issues and moving on.

    you get the SPFile object right, now take its url property, write code from the post below that and see if you can download your excel file properly.

    New code must be added where I have put the comment:

     using (SPWeb web = site.OpenWeb())
            {
             
              // string fileUrl = string.Format("{0}/{1}/{2}", web.Url, documentLibName, fileName);
              string fileUrl = Label4.Text;
              SPFile file = web.GetFile(fileUrl);
              //NEW CODE HERE
              if (file.Exists)
                Label5.Text = "File exist";
              else
                Label5.Text = "File not found!!!";
            }

    Please note you need microsoft office interop dlls for this to work.


    A random burst of technology notes Blog: http://rhythmiccoding.blogspot.com
    Monday, August 01, 2011 8:48 AM
  • Thanks again Bhavyesh, Let me try this... I am so excited to do this and see the output as this would be a major success for me being an

    begginer in coding world.


    Sachin D (Sharepoint Developer)
    Monday, August 01, 2011 9:00 AM
  • Hi Bhavyesh,

    Below is the code that I wrote as per your instruction but unfortunately it doesn't work. BTW I want to show the content of only one cell (whose name is entered in the Cell name field) in the label.

    protected void Button1_Click(object sender, EventArgs e)
            {
                Label4.Text = "http://" + TextBox3.Text + '/' + TextBox2.Text + '/' + TextBox1.Text;
                using (SPSite site = new SPSite("http://dc2"))
                {
                    using (SPWeb web = site.OpenWeb())
                    {

                        // string fileUrl = string.Format("{0}/{1}/{2}", web.Url, documentLibName, fileName);
                        string fileUrl = Label4.Text;
                        SPFile file = web.GetFile(fileUrl);
                       
                        web.AllowUnsafeUpdates = true;
                        string workbookPath = Label4.Text;

                        ApplicationClass excel = new ApplicationClass();
                        excel.Visible = false;
                        Workbook excelWorkbook = excel.Workbooks.Open(workbookPath, 0, true, 5, "", "", false, 0, true, false, false);

                        Sheets sheets = excelWorkbook.Worksheets;
                        Worksheet worksheet = (Worksheet)sheets.get_Item(1);
                        Range range = worksheet.get_Range("A1", "M500");
                       Excelvalues = (System.Array)range.Cells.Value2;
                       
                        excel.Workbooks.Close();
                       
                        if (file.Exists)
                            Label5.Text = "File exist";
                         // Label5.Text=range.Cells.Value2;
                        else
                            Label5.Text = "File not found!!!";
                    }

                }

            }


    Sachin D (Sharepoint Developer)
    Monday, August 01, 2011 12:16 PM
  • Hi Sachin,

    what exactly are you doing here: string workbookPath = Label4.Text;

    The code won't directly work, you must tweak it to work in your environment. Based on your SPFile, download the file to local system. Then use that path and pass it in excel.Workbooks.Open method to get the excel sheet.

    You will have to debug and see where you are getting error. 

    Once you start getting the excel sheet, then next task would be to find that particular cell you want.


    A random burst of technology notes Blog: http://rhythmiccoding.blogspot.com
    Monday, August 01, 2011 12:21 PM
  • Hi Bhavyesh,

    See I don't want content of excel files to be read from local drive instead i want the content should be read from excel in sharepoint document library and I have the following code that works well to check whether the file present/absent in any doc lib.

    using System;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using Microsoft.SharePoint;
    using System.IO;
    using System.Web;

    namespace FileCheck.FileCheck
    {
        public partial class FileCheckUserControl : UserControl
        {
            protected void Page_Load(object sender, EventArgs e)
            {

            }

            protected void Button1_Click(object sender, EventArgs e)
            {
                Label4.Text = "http://" + TextBox3.Text + '/' + TextBox2.Text + '/' + TextBox1.Text;
                using (SPSite site = new SPSite("http://dc2"))
                 {
                    using (SPWeb web = site.OpenWeb())
                    {
                     
                       // string fileUrl = string.Format("{0}/{1}/{2}", web.Url, documentLibName, fileName);
                        string fileUrl = Label4.Text;
                        SPFile file = web.GetFile(fileUrl);
                       
                        if (file.Exists)
                            Label5.Text = "File exist";
                        else
                            Label5.Text = "File not found!!!";
                    }
                }


            }
        }

    Now my requirement is to create a custom web-part which contains 5 fields as Server Name, Library Name, File Name, Sheet Name and Cell Name where user enters the necessary values for each of the fields and on click of the button below the fields the value of Cell (whose name is entered in Cell name field) should be visible on the label on the form.

    Please feel free to let me knwo in case of any further clarifications required.


    Sachin D (Sharepoint Developer)
    Monday, August 01, 2011 12:32 PM
  • Hi All/Bhavyesh,

    It seems I figure out the code that will read the excel content and display on the page. Below is the Eureka code :) :P

     

     
    using System;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using Microsoft.SharePoint;
    using System.IO;
    using System.Web;
    using Microsoft.Office.Excel.Server.WebServices;
    using Microsoft.Office.Excel.Server;
    using System.Web.Services;
    using System.Data; 
    namespace FileCheck.FileCheck
    
    {
    public partial class FileCheckUserControl : UserControl
    {
    protected void Page_Load(object sender, EventArgs e)
    {
    using (SPSite site = new SPSite(http://dc2))
    {
    using 
    (SPWeb web = site.OpenWeb())
    { 
     
     
     
    // Code to show excel data in table format but only of couple of cells
     
     
     
    //Instantiate the Web service and make a status array object
     
     
    ExcelService xlservice =
    new 
    ExcelService(); 
    Status[] outStatus;
     
     
     
    //Using workbookPath this way will allow you to call the workbook remotely.
     
     
     
    string targetWorkbookPath = "http://servername/doclibname/book1.xlsx" 
    ; 
     
     
     
    try
     
     
    {
     
    //Call open workbook, and point to the trusted 
     
     
     
    //location of the workbook to open.
     
     
     
    string sessionId = xlservice.OpenWorkbook(targetWorkbookPath, "en-US", "en-US", out 
    outStatus); 
     
     
     
    object[] rangeResults = xlservice.GetRangeA1(sessionId, "Sheet1", "B2:S8", true, out 
    outStatus); 
     
     
     
    //byte[] workbook = xlservice.GetWorkbook(sessionId, WorkbookType.FullWorkbook, out outStatus);
     
     
     
    foreach (object[] rangeResult in 
    rangeResults) 
    {
     
     
     
    String str = (String.Format("{0}{1}{2}{3}{4}{5}{6}{7}{8}" 
    , rangeResult[0], rangeResult[1], rangeResult[2], rangeResult[3], rangeResult[4], rangeResult[5], rangeResult[6], rangeResult[7], rangeResult[8])); 
     
     
     
    DataList.DataSource = str; //Added a AASP.NET datalist controol on the form to show the excel data
    DataList 
    .DataBind(); //Bindind to datalist
     
    }
    xlservice.CloseWorkbook(sessionId,
    out 
    outStatus); 
    }
     
     
     
    catch (Exception 
    ) 
    {
    Label4.Text = "Error" 
    ; 
    }
    }
    }
    }
    }
    }
    


    The above code will display all the content of "Sheet1" with "B2:S8" depending on the array assigned to them.

    Do let me know in case of any further explanation.

     

    Sachin D (Sharepoint Developer)


    Wednesday, August 10, 2011 7:38 AM
  • Hi,

    Further to the above code I now want to display entire content on excel sheet1 of my workbook.
    With reference to some good Microsoft article
    http://msdn.microsoft.com/en-us/library/ms519570.aspx I think this is possible to achieve too.

    Below is the code which I have written for the same but the code is still displaying junk values  in dataset on page. Can anyone please suggest me the solution to display the content of my entire excel sheet.

     
    using System;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using Microsoft.SharePoint;
    using System.IO;
    using System.Web;
    //using Microsoft.Office.Interop.Excel;
    using Microsoft.Office.Excel.Server.WebServices;
    using Microsoft.Office.Excel.Server;
    using System.Web.Services;
    using System.Data;
    
    
    
    
    namespace FileCheck.FileCheck
    {
      public partial class FileCheckUserControl : UserControl
      {
        protected void Page_Load(object sender, EventArgs e)
        {
          using (SPSite site = new SPSite("http://dc2"))
          {
            using (SPWeb web = site.OpenWeb())
            {
    
              // Code to show excel data in table format but only of couple of cells
              //Instantiate the Web service and make a status array object
              ExcelService xlservice = new ExcelService();
              Status[] outStatus;
              //Using workbookPath this way will allow you to call the workbook remotely.
              string targetWorkbookPath = "http://dc2/japan/book1.xlsx";
    
              try
              {
                //Call open workbook, and point to the trusted  
                //location of the workbook to open.
                string sessionId = xlservice.OpenWorkbook(targetWorkbookPath, "en-US", "en-US", out outStatus);
                byte[] workbook = xlservice.GetWorkbook(sessionId, WorkbookType.FullWorkbook, out outStatus);
                DataList.DataSource = workbook;
                DataList.DataBind();
                //string value = System.Text.Encoding.Default.GetString(workbook);
                //GridView1.DataSource = value;
                //GridView1.DataBind();
                xlservice.CloseWorkbook(sessionId, out outStatus);
              }
              catch (Exception)
              {
                Label4.Text = "Error";
              }
            }
          }
        }
      }
    }
    

    Note: I tried converting byte to string and then displaying it using string value = System.Text.Encoding.Default.GetString(workbook); but still I am getting junk values.


    Sachin D (Sharepoint Developer)
    Wednesday, August 10, 2011 7:57 AM
  • Sachin

    This has been posted long back but this is the code I have written to read my file I'm getting an error saying "you do not have permissions". Please suggest.

    Monday, April 01, 2013 7:13 AM
  • Try using run with SPSecurity.RunWithElevatedPrivileges method and deploy your solution as elivited priviliges.

    Useful Links:

    http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spsecurity.runwithelevatedprivileges.aspx

    http://www.sharepointsteve.com/2009/08/running-sharepoint-code-with-elevated-privileges-a-real-example/


    Sachin D Sharepoint Developer

    Tuesday, April 23, 2013 8:17 AM