Category Archives: Excel Services

How to Create a Smart Table in SharePoint Online 2013


You may also be interested in: SharePoint Services with mindfiresolutions


 

Editor’s note: Contributor Alexandru Dionisie is an Internet Professional and Technical Writer. Follow him @AlexDionisie

According to Microsoft:

- "Excel Interactive View is a new technology, powered by Excel Services, that generates Excel table and chart views on-the-fly, in the browser, from an HTML table hosted on a web page." -

By using Excel Interactive View (EIV) in SharePoint we can change the way that a table is presented. From a regular table, using EIV we can convert it into a smart table, which can perform different operations:

  • Sorting;
  • Filtering;
  • Add charts;
  • etc.

More than that, this technology can be used in WordPress. The Excel Mashup team has created a plugin for WordPress. Here is a page where the EIV plugin is used: EIV.

Of course, you can use the embed code, if you don’t wanna use the plugin.

Resources:

Note: The Romanian version of this article is posted here: Cum creăm tabele inteligente în SharePoint Online 2013?

How do we add EIV into SharePoint?

  • on the Excel Mashup website generate your code

  • in SharePoint go into Edit Mode

  • from the Insert tab choose to insert a Content Editor web part

  • go into Edit mode for the web part and click inside the web part
  • create your table as you wish
  • from the Format Text tab click on Edit Source

  • copy the code generated on the Excel Mashup website – code from Step 1

  • paste the code above the table tag

  • copy the code generated on the Excel Mashup website – code from Step 2

  • paste the code above the closing body tag

Note: because the SharePoint website uses https, you must change the second code. Change only the source part: from http to https. If you do not change it, every time the page loads, a security pop-up message will be displayed.

Now, save the changes.

As seen above, the EIV button is displayed above the table.

If you click that button, the magic happens.

You can choose to open the workbook using the Excel Web App to see what happens behind the scenes.

The workbook contains Slicers, a table and charts. All those elements combined gives us the Excel Interactive View technology.

Note: if you don’t use a web part, the second code will be removed, once you validate the changes.

How to Use Microsoft Excel to Query a SharePoint List


You may also be interested in: SharePoint Hosting by Fpweb.net


 

Editor’s note: Contributor Alexandru Dionisie is an Internet Professional and Technical Writer. Follow him @tutorialeoffice

A great way to extract and expose data from SharePoint is by using Microsoft Excel. Of course, not by doing a simple copy-paste, but by using an Excel query.

Even though Excel’s query editor is not that great looking (like the one from Microsoft Access), it still is functional and very helpful.

I am going to use a query in Excel to extract only a part of a SharePoint list data.

Here are the steps

  • Open the SharePoint list and from the list tab click on the Export to Excel command button.

2012-10-28-ExcelToQuery-01.png

  • Save the Microsoft Excel Web Query file.
  • If you want to see the query file content, open it using Notepad.

2012-10-28-ExcelToQuery-02.png

  • Double click on the Microsoft Excel Web Query file to open it using Microsoft Excel.

A new Excel file opens and displays the SharePoint list data. The biggest disadvantage of that query file is that you can’t edit it and add custom SQL code.

Still, you can create your own query, with a custom SQL code. As you can see in the bellow image, that workbook is connected to the SharePoint list through that Microsoft Excel Web Query file.

2012-10-28-ExcelToQuery-03.png

Save the above Excel file and then open a blank workbook. From the Data tab click on the From Other Sources button and then click on the From Microsoft Query option.

2012-10-28-ExcelToQuery-04.png

  • make sure that the Use the Query wizard to create/edit queries is unchecked.
  • click on the New Data Source option (form 1) and then click on the OK button.
  • in form 2:
    • add a name for the new source (step 1);
    • select the Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) - step 2
    • click on the Connect command button (step 3);
  • in form 3 click on the Select Workbook command button;
  • in form 4 select your workbook.

2012-10-28-ExcelToQuery-05.png

Returning to form 2, we have to choose a default table (or sheet) – step 4.

2012-10-28-ExcelToQuery-06.png

Now we have a new source. To add a custom SQL code, just click on the OK command button.

2012-10-28-ExcelToQuery-07.png

In the query editor I will enable the Criteria pane (from the View menu) because I want to see certain products that have a custom price.

More than that I want to see only some column: Categorie, Produs and Pret.

Criteria:

  • Product category (Categorie = LCD 22);
  • Custom Price (Pret >=400)

2012-10-28-ExcelToQuery-08.png

If I want to see what the SQL code looks like, I will click on the SQL button from the toolbar.

2012-10-28-ExcelToQuery-09.png

To add the query result in a sheet, just close the query editor.

Now, the Import Data form is displayed.

Choose a cell to put the data in and the click the Properties command button.

2012-10-28-ExcelToQuery-10.png

In this form we have to check the first and the third option, so that the data updates in the background and when the file open.

2012-10-28-ExcelToQuery-11.png

After we confirm all the options, the data will be imported in the sheet. Now we can create custom reports and charts, based on this data.

2012-10-28-ExcelToQuery-12.png

If you want others to use this method, just export the query from the query editor. Then, all they have to do is to double click on the query and the data is imported.

To edit the query you can use Notepad or the query editor.

After some tests, it seems that the data is not refreshing.

Why ? Because the first Excel workbook (the one created after running the Web Query) wasn’t updated either. So, update the first workbook and then the second one.

To avoid the above procedure, in the first workbook (created by the Web Query) we must export the second query and import it into the first workbook.

Now, we have only one workbook that contains the Web Query and the ODBC Query.

2012-10-28-ExcelToQuery-14.png