SharePoint 2010 How to display data from Excel file in a Webpart

Reading data from an Excel file is nothing new. You can do it by using C#, Java, PHP and of course just by opening the file with Office. So if you google how to read data from Excel files, you will find some good resources. But what if you combine that with SharePoint?

Of Course – your first thought – migth be excel services. That might not help you for every project. Option one would be to write it down in a custom webpart. And in this post we will look at Option two: Use the Content Editor Webpart and Javascript.

So please open your SharePoint Site and add a content editor webpart to the site. In the CEWP add the following javascript:



<script type="text/javascript">
function GetExcelData(row, column){
var excel = new ActiveXObject("Excel.Application");
var excelFile = excel.Workbooks.Open("url/yourExcel.xlsx");
var excelSheet =excelFile.Worksheets.Item(1);
var excelData = excelSheet.Cells(column,row).Value;

document.getElementById("exData").innerText = excelData+"Test";
};
</script>


And in order to call the function we implement a link:



Daten aus Excel <a href="#" onclick="javascript:GetExcelData(3,1);">Test</a>
<div id="exData"></div>


In this case the specified cell will be displayed in the “Data from Excel file” Webpart.

 

..:: I LIKE SHAREPOINT ::..

The article or information provided here represents completely my own personal view & thought. It is recommended to test the content or scripts of the site in the lab, before making use in the production environment & use it completely at your own risk. The articles, scripts, suggestions or tricks published on the site are provided AS-IS with no warranties or guarantees and confers no rights.

Karsten Pohnke About Karsten Pohnke
He is Consultant for SharePoint Solutions for collaboration, communication and business processes. He provides his customers applications based on standard features as well as development or combining the power of several microsoft tools like Dynamics CRM. In his free time he tries to collect tipps and worthy experience in this blog.

8 comments on “SharePoint 2010 How to display data from Excel file in a Webpart

  1. Erich von Maurnböck

    Hello,
    I’ve tried to implement this Excel sample. I’ve copyied the code above and replaced the link of the excel file to an existing file (the link works). Then I placed the html link for calling the function also in the site.
    But it doesn’t work! Nothing happens, when I click the link. What can I do?

    Regards,
    Erich

    p.s.:
    With a “standard” hello world like this (in a CEWP) it works and it is displayed in my site:

    document.write(‘Hello World‘);

  2. Hi Erich,
    first try to set an alert after each line, in this case you can “debug” which lines the script finishing. Secondly, the script need some loading time, depends on the size of the excel file. So maybe it need some more time? Do you try with a small excel file?
    The third option i would give a try is the column, row values, maybe if you change them it works?
    Please let me know if it works at your place and thanks for your comment.
    Kind regards
    Karsten

  3. Erich von Maurnböck

    Hello,
    this is the code I use (with alerts – see below).
    When I open the site, I see my “start” alert. But nothing happens, when I use the code to call the implemented function. So I guess, that the function is not called.
    I tried to put the link inside and outside the javascipt.
    And – the Excel-File is very small.
    What can I try else?
    Kind regards,
    Erich

    alert(‘start!’);

    function GetExcelData(row, column)
    {
    var excel = new ActiveXObject(“Excel.Application”);
    alert(‘var1 Activeobject’);

    var excelFile = excel.Workbooks.Open(“http://test04/DropOffLibrary/test.xlsx”);
    alert(‘var2 file’);

    var excelSheet =excelFile.Worksheets.Item(1);
    alert(‘var1 sheet’);

    var excelData = excelSheet.Cells(column,row).Value;
    alert(‘var1 exceldata’);

    document.getElementById(“exData”).innerText = excelData+”Test”;
    alert(‘var1 getelement’);
    };

Submit comment

Allowed HTML tags: <a href="http://google.com">google</a> <strong>bold</strong> <em>emphasized</em> <code>code</code> <blockquote>
quote
</blockquote>

Please fill in the captcha: * Time limit is exhausted. Please reload CAPTCHA.