Jean Paul's Blog

There are 2 types of People in the World, One who Likes SharePoint and..

    Advertisements
  • Microsoft MVP

  • MindCracker MVP

  • CodeProject MVP

  • eBook on SharePoint 2010

  • eBook on Design Patterns

  • eBook on Windows Azure

  • NLayers Framework @ CodePlex

  • MSDN Forums

  • .Net vs. Java

    Due to Public Demand
  • Advertisements

Posts Tagged ‘excel’

Excel with Parameters

Posted by Paul on February 10, 2015

In this article we can explore how to use Excel Parameters along with Excel Web View web part.

Parameters

Parameters are Names associated with cells in an Excel workbook. Parameters allows users to change the cell values while in the Excel Web View web part.

image

Excel Web View web part does not allow editing of cells.

Excel 2013

For the experiment I am using Excel 2013 client application.

image

Create Parameters

Create a new Excel work book. Add the following cell values and a chart.

image

Click on the FORMULAS tab.

image

Now click on cell A1 and then on the toolbar Define Name button. In the appearing dialog enter the name as Parameter1 as shown below.

image

Repeat the same for other 4 cells and name them respectively. You can see the Name Manager to ensure all the cells are named correctly.

image

Now we are ready with our Parameters.

Expose Parameters

Now we need to expose the parameters in Browser View options. Those parameters exposed will be workable when the workbook is interacted through a browser.

image

In the appearing dialog choose the Parameters tab and select the parameters as shown below.

image

Save the file & now you are ready to deploy the workbook to SharePoint.

Inside SharePoint

Upload the above workbook to a document library, create a new page & insert the Excel Web Access web part into it. Then select the above workbook & Save changes to the page.

You should be able to see the Parameters in the right pane as shown below.

image

You can try changing the parameter values and click the APPLY button. The graph too should change.

References

http://bit.ly/19hRHso

Summary

In this article we have explored Excel Parameters with Excel Web View web part.

Advertisements

Posted in SharePoint, SharePoint 2013 | Tagged: , , | Leave a Comment »

Editing an Excel file using Excel Web Access web part

Posted by Paul on January 29, 2015

In this article we can explore how to edit an excel file using the Excel Web Access web part.

Add Excel Web Access Web Part

Create a sample excel file like below & upload to a document library.

image

Create a new page and add the Excel Web Access web part to it.

image

Then select the excel file

image

In the appearing tool pane select the excel work which was uploaded to a document library

image

Change the following property of the web part. This is the important step.

· Typing and formula entry

image

Once the property is changes, Save the web part to see the excel view as shown below. You can try typing in the cells & the view will accept changes.

image

image

The changes can be downloaded using the File > Download option.

Summary

In this article we have explored how to enable edit in an excel web access web part.

Posted in SharePoint, SharePoint 2013 | Tagged: , , | Leave a Comment »

Hiding Download Option of Excel Web Access Web Part

Posted by Paul on January 19, 2015

In this article we can explore how to hide the download options of an excel web access web part.

In the previous article we have observed how to use an excel web access web part.

http://www.jeanpaulva.com/index.php/2015/01/17/using-excel-web-access-web-part/

Advantages of Hiding

Following are the advantages of hiding:

· Prevent users from procuring your proprietary formulas & calculations

· Show a read-only view to the user

Download Options

Following are the download options available:

image

Download option allows downloading of entire file. In this way the entire excel workbook can be protected.

Download a Snapshot allows downloading of only values & formatting. In this way the proprietary formulas and calculations can be protected.

Prevent Download

Open the page in edit mode & choose the Edit Web Part option of the excel web access web part. Uncheck the option Open in Excel, Download, Download a Snapshot.

image

Save changes & Refresh the page. You should be seeing the Download options are hidden now.

image

References

https://technet.microsoft.com/en-us/library/ee424405(v=office.15).aspx

Summary

In this article we have explored how to hide the download options of an excel web access web part.

Posted in SharePoint, SharePoint 2013 | Tagged: , , , | Leave a Comment »

Excel Error :: This workbook cannot be opened..

Posted by Paul on December 28, 2014

While working with Excel Services we might often get an error shown below:

This workbook cannot be opened because it is not stored in a trusted location.

image

Problem

The location of excel file (library) may not be a trusted file location.  You can have to add the library location to the Trusted File Locations through SharePoint Central Administration.

 Solution

Central Administration > Service Applications > Excel Service Application > Trusted File Locations for this.

image

Create a new item with http:// as the string.

image

Check the Children Trusted label and Save the item.  Your new item will look like  below.

image

References

http://technet.microsoft.com/en-us/library/jj219698%28v=office.15%29.aspx

Summary

In this post we have explored a common Excel Services error scenario & solution.

Posted in SharePoint, SharePoint 2013 | Tagged: , , | Leave a Comment »

Display Worksheet using Excel Services in SharePoint 2013

Posted by Paul on December 27, 2014

Display Worksheet using Excel Services in SharePoint 2013

In this article we can explore how to display an Excel Sheet using Excel Services in SharePoint 2013.

Create Excel Sheet with Graph

Create a new excel sheet, fill some data & insert a graph as shown below.

image

Upload to SharePoint Library

Now you can upload the above file to a SharePoint Library.

image

Trusted File Locations

Ensure you have added the library path to the Trusted File Locations.  You need to open Central Administration > Service Applications > Excel Service Application > Trusted File Locations for this.

image

Create a new item with http:// as the starting address and check the Children Trusted column.  This will make sure all URLs with given starting address are trusted.  

image

Open File

Back in SharePoint user interface; open the Excel File using context menu.

image

You can see the excel file get opened in the browser.

image

This concludes our simple experiment with Excel Services.

image

The http:// string as trusted entry is not recommended for production scenarios.

References

http://technet.microsoft.com/en-us/library/jj219698%28v=office.15%29.aspx

Summary

In this article we can explore how to display an Excel Sheet using Excel Services in SharePoint 2013.

Posted in SharePoint, SharePoint 2013 | Tagged: , | Leave a Comment »

Excel Services in SharePoint 2013

Posted by Paul on December 19, 2014

In this article we can explore Excel Services in SharePoint 2013.

Excel Services

Excel Services is a Shared Service Application in SharePoint 2013.

Following are the properties of Excel Services:

  1. Can render an excel sheet in a browser
  2. Only available in Enterprise Edition of SharePoint
  3. Business Intelligence category

Excel Services consists of following three:

  1. Excel Calculation Services
  2. Excel Web Access web part
  3. Excel Web Services

image

Excel Calculation Services is the engine which loads the work book, does calculations, resolves external connections and maintains sessions.

Excel Web Access Web Part takes care of rendering the web part in browser.

Excel Web Services provides API for developers.

References

http://technet.microsoft.com/en-us/library/ee424405(v=office.15).aspx

Summary

In this post we have explored an overview of Excel Services in SharePoint 2013.

Posted in SharePoint, SharePoint 2013 | Tagged: , | Leave a Comment »