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
When I specify the range on some sheets, Indesign crashes
The question has to do with table formatting options. I tried this. I see that others have struggled to get this line to yield the desired results.
app.excelImportPreferences.tableFormatting = TableFormattingOptions.EXCEL_FORMATTED_TABLE
It seems to me that the only way I can set the desired formatting option is to do it manually in the UI, then run my script.
Hi Paul,
I made a quick test.
For example, let's place the first spreadsheet: a range -- from A3 to F8 -- as formatted table by script.
main();
function main() {
var textFrame = app.selection[0], // Select a text for pacing an Excel file
excelFile = new File("~/Desktop/Test/Test place Excel.xlsx");
if (textFrame.isValid && excelFile.exists) placeExcel(textFrame, excelFile);
}
function placeExcel(textFrame, excelFile) {
try {
with (app.excelImportPreferences) {
sheetIndex = 0;
tableFormatting = TableFormattingOptions.EXCEL_FORMATTED_TABLE;
rangeName = "A3:F8";
}
var placedStory = textFrame.place(excelFile);
}
catch(err) {
$.writeln(err.message + ", line: " + err.line);
}
}
The code works for me as expected in CC 2019 (Windows)
Regards,
Kasyan
AdChoices