Get data directly from Excel
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
AdChoices