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:
[sourcecode language=”javascript”]
[/sourcecode]
And in order to call the function we implement a link:
[sourcecode language=”html”]
Daten aus Excel Test
[/sourcecode]
In this case the specified cell will be displayed in the “Data from Excel file” Webpart.
..:: I LIKE SHAREPOINT ::..
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‘);
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
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’);
};
It was because I had limited the scripting access in IE!
Thank you, it works now also for me 🙂
thank you for sharing.
Hi Karsen,
Can you please explain this part?
Daten aus Excel
Hi Arun,
you can delete it or replace with something you like.
If you need to access Excel document on a server-side, for example, get cell value within a SharePoint workflow, then you need to query Excel services (not a simple task). Or you may look at ready to use third-party actions to set or get cell values: http://www.harepoint.com/Products/HarePointWorkflowExtensions/Office-SharePoint-Workflow-Action.aspx