When using REST to access portions of a workbook, people sometimes use direct addressing (Sheet1!A1). I thought I’d drop a line about the icky way this works when you have single quotes in your sheet name.
First – a short reminder. This is how Excel Services REST works when trying to access a range:
http://server/_vti_bin/ExcelRest.aspx/doclib/File.xlsx/Model/Ranges('sheet1!A1')?$format=html
This will give back an HTML fragment representing the range. Now, what if sheet1 had a space in it? Say it was called Sheet 1?
The standard way of handling spaces in sheet names is to surround them in single quotes. And herein lies the problem – we already use single quotes to specify the range. The solution is to escape them (not URL escape – that will not help us). So here's what this looks like:
http://server/_vti_bin/ExcelRest.aspx/doclib/File.xlsx/Model/Ranges('''sheet 1''!A1')?$format=html
This may be a little unclear. Here's what's inside the braces:
<single quote-1><single quote-2><single quote-3>sheet<space>1<single quote-4><single quote-5>!A1<single quote-6>
1 – Denotes the beginning of the range name.
2,3 – the first single-quote in 'Sheet 1' encoded by doubling it.
4,5 – the second single quote in Sheet 1' encoded by doubling it.
6 – The end of the range name.
What if the sheet name has a single quote in it? Say: Sheet '1 (that's Sheet<space><single quote>1). In Excel, this would be represented in this fashion:
See the double-single-quote in there? It's because Excel faces the same issue as we did in the previous example – it needs to escape the character. But what does that look like in REST?
http://server/_vti_bin/ExcelRest.aspx/doclib/File.xlsx/Model/Ranges('''sheet ''''1''!A1')?$format=html
Yeah. That's four single quotes in there…
<sq-1><sq-2><sq-3>sheet<space><sq-4><sq-5><sq-6><sq-7>1<sq-8><sq-9>!A1<sq-10>
2,3 – the first single-quote in 'Sheet ''1' encoded by doubling it.
4,5 – the second single quote character in 'Sheet ''1' encoded by doubling it.
6, 7 – the third single quote character in 'Sheet ''1' encoded by doubling it.
8, 9 – the fourth single quote character in 'Sheet ''1' encoded by doubling it.
10 – The end of the range name.