SPJS Charts for SharePoint: Export to Excel and SP2013 support

A few months ago I received this email from Aymeric:

Hi Alexander,

I’ve created a JS API to use with Sharepoint. You may want to have a look at it: http://aymkdn.github.com/SharepointPlus/
I think this library could be useful for some other people. It’s on GitHub so feel free to share any comments.

Thank you,
Aymeric

It took me “forever” to look at this API, but I finally had a go at it, and decided to use one of the methods: createFile in the SPJS Charts for SharePoint solution to be able to export the chart data to Excel as a CSV file.

New in v3.4 of SPJS Charts for SharePoint

You find the “Enable export to Excel as comma-separated values (CSV)” under the “Advanced options” section in the SPJS Charts for SharePoint GUI. When you enable this feature (done individually for each chart), you can hover over the chart to show a label in the bottom right corner of the chart. Hit this to create the file. When the file is successfully created, you are prompted to open it directly.

To achieve this I included the file “sharepointplus-3.0.3.js” in the CEWP code, and I created a function that exports the chart data as comma-separated values, which I then “inject” in a dedicated document library using Aymerics API method “createFile”. This document library is automatically created the first time you save a chart with the “Export feature” enabled.

You do NOT have to load the “sharepointplus-3.0.3.js” file if you do not intend to use the export to CSV feature. If you try to enable it, and “sharepointplus-3.0.3.js” is not loaded, you will get instruction on how to get hold of the file.

The exported files

The files are named from the path to the page where the chart is located, the chart id and the user ID of the user exporting the chart. This means that the library “SPJS_ExcelExports” will not get filled up as the file is overwritten for each time the user exports the same chart. If you want to keep the file, save it in another location, or at least save it with another name to prevent it from being overwritten.

Example of the CEWP code with “sharepointplus-3.0.3.js” loaded:

<style type="text/css">
div.chartLoadingOverlay{
	font-style:italic;
	color:gray;
	border:1px silver solid;
	background-color:#F5F5F5;
	line-height:250px;
	height:250px;
	width:500px;	
	text-align:center;	
	margin:2px;
}
</style>

<!-- The chart container is set up with an overlay to let the user know the chart is rendering -->
<div id="MyChart1">
	<div class='chartLoadingOverlay'>Loading chart please be patient...</div>
</div>

<script type="text/javascript">
/*****************************************************
		Address all containers
*****************************************************/
// All charts must be represented by a container with a unique id. 
// This container  must be present in the page
var arrOfChartContainers = ["MyChart1"];
var loadRC = false;
// Set this to true to allow for the use of variables in the "Filter setup textarea"
var allowEval = false;
// Set this to true to delay the loading of the chart.
var loadManually = true;
// You can override chart options set in the GUI
var chartOptionOverride = {"TheChartID":{"title":"This is the chart title!"}};
</script>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript" src="/test/English/Charts/Javascript/SPJS_ChartsForSharePoint_v3.4.js"></script>
<!-- Used to enable export of chart data as CSV -->
<script type="text/javascript" src="/test/English/Charts/Javascript/sharepointplus-3.0.3.js"></script>
<!-- Used to enable export of chart data as CSV -->
<script type="text/javascript">
// This function will load the charts
function manualLoad(){
	loadManually = false;
	spjs_GenerateChart();
}

// Delay the call to the function by 10ms to give the overlay time to render 
setTimeout(function(){
	manualLoad();
},10);
</script>
SharePoint 2013

In v3.4 I have made a “quick pass” trough the code to fix some compatibility issues for SharePoint 2013.

Because this solution is funded with an occasional beer donation, I cannot fully test it as if it was a commercial product. Therefore I must rely on you guys to find the rest of the bugs. Post them here, and I’ll fix them as soon as I can manage.

Alexander

49 Comments on “SPJS Charts for SharePoint: Export to Excel and SP2013 support”

  1. Pingback: SPJS Charts for SharePoint: Export to Excel and SP2013 support | JOSHUASCOTT.NET

  2. Hi Alexander,

    Thank you for your hard work on this awesome plugin you are sharing with us.

    I am experiencing an issue whereby the exported CSV is actually a colon separated value file. Hence excel doesn’t parse it correctly.
    Is it to do with my end?

    Thank you.
    Vinz

    2. Hi,
      It appears Excel expects a comma delimiter for American/English version, and semicolon for European versions. I tested this in my setup and did not think Excel would handle it differently in other language-version.

      I’ll include a delimiter-selector in the next release.

      Alexander

  3. Could I humbly suggest two features in your solutions that I reckon could be quite useful?

    -One would be to have the dropdown filtered by available items. Namely if “Company A” is included in a filter setup but there is no record for it in the list, “Company A” should be removed from the filter dropdown.
    Whether to apply this to a filter could be defined in the filter object as a property, like
    {“label”:”Company”,”urlKey”:”Company”,”hideMissing”:true,…}

    -The other would be to extend the export functionality to also export PNG.
    I am using this code which so far works well, also exports multi-svg charts (e.g. Gauges).
    It needs two extra references to canvg.googlecode.com/svn/trunk/rgbcolor.js and canvg.googlecode.com/svn/trunk/canvg.js 

    function downloadChartImage(chartID,docLib){
	var siteURL=window.location.protocol+'//'+window.location.host;	
	$SP().createFile({
		content:chart2DataURI(chartID + '_chart'),
		destination:siteURL+L_Menu_BaseUrl+'/'+docLib+'/'+chartID+'.png',
		url:siteURL,
		encoded:true,
		after:function() {
			location.href=L_Menu_BaseUrl+'/_layouts/download.aspx?SourceURL='+docLib+'/'+chartID+'.png';
		}
	});
	function chart2DataURI(chartID){
		var $box=$('');
		var $svg=$('svg','#'+chartID);
		$svg.each(function(){
			$box.find('>svg').append($(this).clone().attr({
				'y':$(this).offset().top-$('#'+chartID).offset().top,
				'x':$(this).offset().left-$('#'+chartID).offset().left}))
			});				
		$box.find('>svg').attr({			
			height:Math.max.apply(null, $svg.map(function (){return $(this).offset().top-$('#'+chartID).offset().top+$(this).height();}).get()),
			width:Math.max.apply(null, $svg.map(function (){return $(this).offset().left-$('#'+chartID).offset().left+$(this).width();}).get())
			});
		var $canv = $('').attr({height:$box.find('>svg').attr('height'),width:$box.find('>svg').attr('width')});
		canvg($canv[0],$box.html());		
		return $canv[0].toDataURL('image/png').match(/,(.*)/)[1]
	}}

    It could be written much better but that’s as good as it going to get with me.
    It obviously only exports charts if the browser supports SVG, but whatever button is bound to the function could be hidden if the chart is not in SVG.

    Hope this makes sense.

    Thanks for taking the time to read through

    1. Hi,
      Thank you for the feedback.

      The filter issue is not so easy to fix as, when applied, the items pulled from SharePoint is already filtered by the CAML-query, and therefore we don’t know whether or not the other filter values will result in a “hit” when applied.

      There could be a workaround using a series om “probing queries” initially to check whether or not the list contains matching data. This probing would result in more traffic on the server, and will slow down the loading of the chart, but I’ll see what I can do in the next release.

      The export to image ability have already been considered, but as it does not support IE below version 9 – and the fact that SP2010 forces IE 8 compatibility mode, it does not work at all in SP 2007 / 2010 when using IE, but will in SP2013 as this does not force IE 8 compatibility mode.

      It’s on the list of things to do.

      Alexander

      1. Hi,

        Thanks for listening to my suggestions

        For cleaning the filter the CAML needs to be parsed and the {filter:urlKey} condition(s) replaced with a wildcard expression. You then need an extra call to GetListItem to make an array of the returned filter field values to compare against the filter options.
        It is slower, but it doesn’t bother if this is triggered after the chart is rendered. Plus the owner could choose whether to apply this or not.

        As for the compatibility mode forced by SP that’s a good point. I didn’t think of it at all. Annoying..

        Thank you.

        p.s. They way I posted code is obviously wrong as it got stripped of HTML tags. Is there any recommended way of displaying code as code in a post?

  6. hi alexander: this seems like a nice tool. i am testing it for use. i am having a problem. i want to make a stacked column chart. this seems entirely possible. but one of the columns holding data that i need to stack is not showing in the dropdown list of columns from the list. i think this is because the column name includes a slash, as there are three columns with a slash in the name and none of them appear in the dropdown, though everything else does. is there any hope for me? cheers

      1. That sounds like there may be hope 🙂

        The field type is: Choice

      3. I see. Yes, indeed. My own conundrum. Let me think…

        Thanks much for your insight.

  7. Hi Alex,

    I was wondering can you add a hyperlink within the chart to the list it is pulling the data. I have set up a dashboard using the table view option however I would like the option for the end user to click on the chart somewhere to take them to the list view so they can see the data. I can code links below the chart however this is a messy solution if you have multiple charts within one web part.

    Thanks

    1. Hi,
      I have previously given an example of such code here: https://spjsblog.com/2011/08/16/charting-for-sharepoint-using-google-visualization-api-update-16-08-2011/

      This is the code you can use as a starting point:

      function chartSelectionHandler(data,chart,chartID){
	var selection, item, x, y, z;
	selection = chart.getSelection();
	if(selection.length>0){
		item = selection[0];
		if(item.row!==undefined){
			x = data.getFormattedValue(item.row, 0);
			if(item.column!==undefined){
				z =  data.getFormattedValue(item.row, item.column)
				y = data.getColumnLabel(item.column);
			}
		}
		if(x===undefined && y===undefined && z===undefined){
			return;
		}
		// Create your filter
		alert("These are the variables you can use for your filter:nnx = "+x+"ny = "+y+"nz = "+z);
		if(confirm("Open filtered list?")){
			if(chartID==='MyChart1'){	
				window.open('/test/English/Charts/Lists/ByStatus/AllItems.aspx?FilterField1=Status&FilterValue1='+x,'','width=1000');
			}else if(chartID==='MyChart2'){	
				window.open('/test/English/Charts/Lists/Coffee2/AllItems.aspx?FilterField1='+y+'&FilterValue1='+z,'','width=1000');
			}
		}
	}	
}

      Alexander

      2. Alexander,

        I can’t seem to get the above solution working in SPJS v4. I have this working in an earlier version.. I am setting the chart ID to equal the generated chart id eg: “ca96d69a-9f65-45e7-b9ac-0b66b101f6a4”

        Any thoughts?

        Cheers

      3. Hi,
        In v4 I have changed the function a bit. You can use this code as starting point:

        function spjs_chartSelectionHandler(chartID,selection,data){
	var message, item, value;
	message = "";
	value = "";
	item = selection[0];
	if(item.row !== null && item.column !== null){
		message = "{row:" + item.row + ",column:" + item.column + "}";
		value = data.getValue(item.row, item.column);
		if(message === "") {
			message = "nothing";
		}
		alert("You selected " + message +", the value is: "+value);	
	}
}

        Add the code to the master cewp.

        Alexander

    1. Hi,
      I’m glad you liked it! “sliceColorByValue” has already been implemented in version 3.3.83 (February 28, 2013).

      Look at the very bottom of the “Options” section and click the “Special options by chart type” link to learn how to use it.

      Alexander

      2. Sorry, but this has been added for pie charts only in this version. I might add support for other chart types in the future.

        You wont find this in the Google API as it has been custom made by me for this solution.

        Alexander

  9. Hi again. I’m marching toward success, but I need your help again. I am trying to format dates and numbers in columns of a table. the data is coming from the sharepoint list. but the date is always displayed at 2013-08-15 00:00:00. I cannot for the life of me discover the correct syntax for formatting this date column. I am getting the column number correct when i try using a formatter because i can see my value showing up as text replacing all the dates. I would really like to convert the date to this 08-AUG-13. Please teach me 🙂

  10. Hi Alex

    I want use an option “series” in Combo charts with this config:
    option series Value {0:{type:”line”}}, but always appears this error: Parameter: series Invalid JSON: {0:{type:”line”}}. I don’t want put chartOptionOverride in CEWP because I have 3 ChartsID and I want specifics rules in each chart. Could you help me the correct syntax?

  11. Hi Alexander,

    I always read you Javascript tips and tricks.

    In related to create the chart, there is a note written at http://www.aasoftech.com/SitePages/How%20to%20create%20a%20Chart%20from%20SharePoint%20List.aspx

    This code is reading data from SharePoint list and create the Google Chart from it.

    This tool allows you to create Google chart using client object model. The code is ready to use. The demo is located at http://www.aasoftech.com/demo . You can download the code from http://www.aasoftech.com/download

  12. Hello Alexander,
    I’ve implemented v3.4 but I’m having trouble with pie charts…..no doubt I am missing something very simple and hoping you can help.
    I have a list with a field KPI – Red/Amber/Green and I’ve created the chart with column 0 = KPI, string merge duplicates, then column 1 = KPI, count.
    All I get is a blank chart with the number of items at the bottom?
    If I change column 1 to count inc/exc blanks, it displays the chart but only for one instance of the KPI, I’ve played around but seem to be stuck now…..any help?

    Thanks,

    Jason

  13. Hi, Thanks for your script and I can see a beautiful chart on my SP. However I still see some errors as below. The line 802 is spjs_GenerateChart();

    Any ideas? Thanks

    Webpage error details

    User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; WOW64; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; InfoPath.2; MS-RTC LM 8; .NET4.0C; .NET4.0E)
    Timestamp: Tue, 6 Aug 2013 02:30:32 UTC

    Message: Object expected
    Line: 807
    Char: 2
    Code: 0
    URI: http://—/sites2/spqkmwhp/risk/SitePages/TestPage.aspx

    /*****************************************************
    Address all containers
    *****************************************************/
    // All charts must be represented by a container with a unique id.
    // This container must be present in the page
    var arrOfChartContainers = [“MyChart1”];
    var loadRC = false;
    var allowEval = false;
    // Set this to true to delay the loading of the chart.
    var loadManually = true;
    // You can override chart options set in the GUI
    var chartOptionOverride = {“TheChartID”:{“title”:”This is the chart title!”}};

    // This function will load the charts
    function manualLoad(){
    loadManually = false;
    spjs_GenerateChart();
    }

    1. Hard to tell what causes this error without seeing the full CEWP code (with the script tags). Could you send it to me by mail so I can look at it?

      If possible, include a few screenshots of the setup as well.

      Alexander

  14. Hello Alexander. I need to create a graph Combo type, where the first column shows the month, second column counting the number of items per month, and the third counts quantity of items equal to a certain status. In the fourth column is my difficulty, It should shows a value which is the ratio between the third column and the second column to display as a percentage. Is it possible to do it? I want to show a combo chart with bars and lines showing as an indicator.

    1. Hi,
      To accomplice this I believe you will have to create a calculated column in the SharePoint list which calculates this number and then use this calculated column as the fourth series column in the chart.

      Alexander

  16. Hi Alex, I tried to use the Combo Chart with Options. I got syntax error during the chart loading when I enter the series:

    Options: series
    Value: {5:type:”line”}}

    For the seriesType and chartArea.left are fine.

    Thanks

  17. I have found that when “Create filter above the chart” is used and configured for multiselect and multiple options are pre-selected, only the last selected option is actually used to filter the chart when it it initially rendered although the selector control above the chart shows all options selected. If I then select options with the mouse, the chart is subsequently filtered correctly.

  18. Hi Alex, I tried to use SPJS_ChartsForSharePoint_v3.3.6.js and configure a Combo Chart with Options. I got syntax error during the chart loading when I enter the series:

    Options: series
    Value: {5:type:”line”}}

    For the seriesType and chartArea.left are fine.

    Can you give me some lights?

    Thanks

  20. Alexander, should I be able to pass an array in the options? Some configuration options like “slices” for the pie chart are arrays and I haven’t been able to get them to work with getting a syntax error from your script. Thanks!

    2. Ok, the syntax above worked for the slices option, but I don’t know how to pass objects. For example, how would I mark up the values for the vAxis.ticks option? Google API syntax is:

      {ticks: [5,10,15,20]}

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.