Hi all,
Quite often I have to write scripts that use data originated from an Excel worksheet. I (and most scripters) believed that the easiest approach was to use a CSV-file exported from Excel. However, this takes an extra step for the user so I decided to write a function that opens an Excel book in background, reads the data from its first spreadsheet and returns array.
The function has three arguments:
- excelFilePath — The platform-specific full path name for the xlsx-file — fsName. If you pass it as a string, make sure to double the backslashes in the path like in the line below:
var excelFilePath = "D:\\My Test Folder\\SampleBook.xlsx";
- splitChar — [Optional] the character to use for splitting the columns in the spreadsheed: e.g. semicolon (;) or tab (\t)
If it isn't set, semicolon will be used by default. - sheetNumber — [Optional] the worksheet number: either String or Number. If it isn't set, the first worksheet will be used by default
The data in Excel
The same data transfered to InDesign as Array
The function (current version):
Main();
function Main() {
// The platform-specific full path name for the xlsx-file -- fsName
// If you pass it as a string, make sure to double the backslashes in the path like in the line below
var excelFilePath = "D:\\My Test Folder\\SampleBook.xlsx";
// [Optional] the character to use for splitting the columns in the spreadsheed: e.g. semicolon (;) or tab (\t)
// If it isn't set, semicolon will be used by default
var splitChar = ";";
// [Optional] the worksheet number: either string or number. If it isn't set, the first worksheet will be used by default
sheetNumber = "1";
var data = GetDataFromExcelPC(excelFilePath, splitChar, sheetNumber); // returns array
}
function GetDataFromExcelPC(excelFilePath, splitChar, sheetNumber) {
if (typeof splitChar === "undefined") var splitChar = ";";
if (typeof sheetNumber === "undefined") var sheetNumber = "1";
var vbs = 'Public s, excelFilePath\r';
vbs += 'Function ReadFromExcel()\r';
vbs += 'Set objExcel = CreateObject("Excel.Application")\r';
vbs += 'Set objBook = objExcel.Workbooks.Open("' + excelFilePath + '")\r';
vbs += 'Set objSheet = objExcel.ActiveWorkbook.WorkSheets(' + sheetNumber + ')\r';
vbs += 'objExcel.Visible = False\r';
vbs += 'matrix = objSheet.UsedRange\r';
vbs += 'maxDim0 = UBound(matrix, 1)\r';
vbs += 'maxDim1 = UBound(matrix, 2)\r';
vbs += 'For i = 1 To maxDim0\r';
vbs += 'For j = 1 To maxDim1\r';
vbs += 'If j = maxDim1 Then\r';
vbs += 's = s & matrix(i, j)\r';
vbs += 'Else\r';
vbs += 's = s & matrix(i, j) & "' + splitChar + '"\r';
vbs += 'End If\r';
vbs += 'Next\r';
vbs += 's = s & vbCr\r';
vbs += 'Next\r';
vbs += 'objBook.close\r';
vbs += 'Set objBook = Nothing\r';
vbs += 'Set objExcel = Nothing\r';
vbs += 'End Function\r';
vbs += 'Function SetArgValue()\r';
vbs += 'Set objInDesign = CreateObject("InDesign.Application")\r';
vbs += 'objInDesign.ScriptArgs.SetValue "excelData", s\r';
vbs += 'End Function\r';
vbs += 'ReadFromExcel()\r';
vbs += 'SetArgValue()\r';
app.doScript(vbs, ScriptLanguage.VISUAL_BASIC, undefined, UndoModes.FAST_ENTIRE_SCRIPT);
var str = app.scriptArgs.getValue("excelData");
app.scriptArgs.clear();
var tempArrLine, line,
data = [],
tempArrData = str.split("\r");
for (var i = 0; i < tempArrData.length; i++) {
line = tempArrData;
if (line == "") continue;
tempArrLine = line.split(splitChar);
data.push(tempArrLine);
}
return data;
}
I wrote and tested it in InDesign CC 2017 and Excel 2016 (Version 16) on Windows 10. I wonder if/how it works in other versions of Excel. Also, I'd like to get some feedback: bug reports, suggestions and new ideas.
Here's the permanent link on my site.
— Kas
70
Replies
Hi pjleveno ,
just tested Kasyan's version 2 script with an Excel file on Windows 10.
The function returns data, that is an array of arrays. Perhaps that was not clear.
So you cannot directly use it as contents for an InDesign table.
Here a representation of the data of an Excel file with only one worksheet and a data representation like that:
The array's length is 5. Every entry represents a row in the worksheet.
So every entry of the 5 entries of the outer array is an array of 3 elements like that:
1,1,1
2,2,2
3,3,3
4,4,4
5,5,5
You could flatten the array of arrays to one array of string entries. Only then you could assign this array as contents for a table. In my reply above I was under the false impression that Kasyan's function returns an flat array. Totally my fault, because Kasyan made that very clear in his initial post.
Or, you do not flatten the result array and loop through the outer array to assign the inner arrays with the 3 entries as contents for rows of a table in InDesign.
Below a sample snippet where I pointed the script to an Excel file on drive F: and directed the entries of the result data as contents to the rows of an InDesign table in my open InDesign document:
function Main() {
// The platform-specific full path name for the xlsx-file -- fsName
// If you pass it as a string, make sure to double the backslashes in the path like in the line below
var excelFilePath = "F:\\Excel-TestFolder\\Columns-3-Cells-5.xlsx";
// [Optional] the character to use for splitting the columns in the spreadsheed: e.g. semicolon (;) or tab (\t)
// If it isn't set, semicolon will be used by default
var splitChar = ";";
// [Optional] the worksheet number: either string or number. If it isn't set, the first worksheet will be used by default
var sheetNumber = "1";
var data = GetDataFromExcelPC(excelFilePath, splitChar, sheetNumber); // returns array of arrays
//~ $.writeln( data.constructor.name );
//~ $.writeln( "data.length" +" : "+ data.length );
//~
//~ $.writeln(data.join("\r"));
//~
var myInDesignTable = app.activeDocument.textFrames[0].tables[0];
for( var n=0;n<data.length;n++ )
{
myInDesignTable.rows
.contents = data ; };
}
Screenshot of the result:
Sorry. Couldn't test the Mac OSX version code.
All my tests with Excel 2010 and InDesign CC 2019 version 14.0.1.209 on Windows 10 Pro.
Regards,
Uwe
//EDITED
I just tested it (Windows 10, InDesign 14.0.2, Excel 2019 (16.0.11727.20222)), but this doesn't happen on my side.
Spreadsheet
Array in data browser
The same array converted to string and placed as contents into a text frame by script
I have a superficial knowledge of Excel and have no idea why it works differently for you. Probably you have to play with some settings.
— Kas
Probably you won't need it.
For example (assuming a text frame is selected):
main();
function main() {
var textFrame = app.selection[0];
var str = "left single quotation mark - \u2018\nright single quotation mark - \u2019\nleft double quotation mark - \u201C\nright double quotation mark - \u201D";
textFrame.contents = str;
}
I get quotes as expected.
Also, in my opinion, these encode/decode functions have a different purpose: encode/decode special chars in URI.
Anyway, taking pairs of single and double quotation marks as an example, in ESTK they work like so:
Not what you expect to get (I guess).
There might be dll/framework solutions for sure. I have been told one but it's a commercial one and it's like 1.3k € to distribute it. So it's quite expensive if it's for selling a script 500€
It seems python has some utilities too but it implies python being installed.
I wish I knew C++ 
Loic
AdChoices