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
Funny enough I am involved in a XLSX based project and I could proceed the file with this :
var xlsx = File ( '/myFile.xlsx' );
var fo = Folder ( Folder.temp+"/myFolder" );
fo.create();
app.unpackageUCF ( xlsx, fo );
then you can introspect xml files 
HTH
Loic
Me too.
I have taken the place route. But, I think I have found a problem with the Excel place method.
My spread sheet.
The result after doing a place with the UI. ( I get the same result using my code )
This is a snippet from a very large spread sheet. Maybe my spread sheet is corrupt, I don't think so, as when I export from excel to a tab delimited file the -1 appears.
I am using Mac CC 2015.4
Here is my place code.
//===
function getxlsxData( xFile )
{
var retValue = "";
var myPreset = app.documentPresets.add();
myPreset.createPrimaryTextFrame = true;
var tmpDoc = app.documents.add(false, {documentPreset:myPreset});
with( newFrame = tmpDoc.pages[0].textFrames[0] )
{
place ( xFile );
retValue = parentStory.contents;
}
myPreset.remove ();
tmpDoc.close ( SaveOptions.NO );
return ( retValue );
}
//=====
P.
Hi Pickory,
CSV is fine most of the times but it can have its downsides. Dealing with carriage returns inside data is a nightmare and character encodings can be a problem too.
Loic's method, unpacking the xslx file and inspecting the XML data has its charm.
It has also its dark side that I keep digging and digging 
Retrieving text strings is trivial but for other types of data, the stored string is often nothing but what is rendered in MS Excel. I get this case wher a cell hosts a number such as 20112017 which is internally stored as 201120e17 (don't ask me why). So you can't presume the stored string really stands for the displayed value.
The only "interest" I can see in reading the inner xml files is to get agnostic of the OS and avoid dependancies (like Excel installed) but of course if one can guarantee the context, there is no point of not using MS Excel in combination with Applescript or VB.
Loic
Loic.Aigon wrote
… It has also its dark side that I keep digging and digging
…
Oh yes. Exactly that is what I feared. 
With the XML there should be a formatting command—an attribute perhaps—for the cell and Excel has to interpret that and render the contents accordingly. So. Back to square one: We need Excel installed to get the right contents. Maybe also the right version of Excel? I'm not sure…
Regards,
Uwe
Hi there,
No, you don't have to mess with the function: add something at the end. Also, the array elements are already strings so there's no need to use .toString().
Let's illustrate it with a working example accompanied with before and after test files.
For example, we want to replace fonts using the data in an Excel spreadsheet.
Before
After
Note: Letter Gothic wasn't changed since it is not in the list.
The function returns a 'ready to use array' of strings. However, I usually use an additional function, like so, to validate the data: skip empty rows/cells, headers, convert strings to numbers, if necessary, etc.
Here's the script:
main();
//--------------------------------------------------------------------------------------------------------------------------------------------------------
function main() {
try { // if something goes wrong in the try-catch block, the batch processor won't stop here. It will log the error message and continue further
var newFont, paragraphStyle, characterStyle, changed, report,
doc = app.activeDocument, // The frontmost document
paragraphStyles = doc.allParagraphStyles,
characterStyles = doc.allCharacterStyles,
scriptFile = getActiveScript(),
scriptFolder = scriptFile.parent,
scriptFolderPath = scriptFolder.fsName,
excelFilePath = scriptFolderPath + "\\Change fonts list.xlsx",
excelFile = new File(excelFilePath),
countParStyles = countCharStyles = countLocals = 0;
if (!excelFile.exists) {
exit();
}
var fontList = GetFontListFromExcel(excelFilePath, ";");
// Change in paragraph styles
for (var p = 1; p < paragraphStyles.length; p++) {
paragraphStyle = paragraphStyles
;
newFont = getNewFont(paragraphStyle.appliedFont.name, fontList);
if (newFont != null) {
paragraphStyle.appliedFont = newFont;
countParStyles++;
}
}
// Change in character styles
for (var c = 1; c < characterStyles.length; c++) {
characterStyle = characterStyles
; newFont = getNewFont(characterStyles
.appliedFont + "\t" + characterStyles .fontStyle, fontList); if (newFont != null) {
characterStyles
.appliedFont = newFont; countCharStyles++;
}
}
for (var i = 0; i < fontList.length; i++) {
app.findTextPreferences = app.changeTextPreferences = NothingEnum.NOTHING;
newFont = getNewFont(fontList[0], fontList);
if (newFont != null) {
app.findTextPreferences.appliedFont = fontList[0];
app.changeTextPreferences.appliedFont = newFont;
changed = doc.changeText();
countLocals += changed.length;
app.findTextPreferences = app.changeTextPreferences = NothingEnum.NOTHING;
}
}
if (countParStyles == 0 && countCharStyles == 0 && changed.length == 0) {
report = "No changes have been made to the document.";
}
else {
report = "Changed fonts in " + countParStyles + " paragraph style" + ((countParStyles == 1) ? ", " : "s, ") + countCharStyles + " character style" + ((countCharStyles == 1) ? "," : "s,") + " and " + countLocals + " instance" + ((countLocals == 1) ? "" : "s") + " of locally formatted text.";
}
}
catch(err) {}
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------
function getNewFont(oldFontName, fontList) {
var newFontName,
newFont = null;
for (var p = 0; p < fontList.length; p++) {
newFontName = fontList
[1];
if (oldFontName == fontList
[0]) {
newFont = app.fonts.itemByName(newFontName);
if (newFont.index == -1 || !newFont.isValid) {
newFont = null;
}
break;
}
}
return newFont;
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------
function getActiveScript() {
try {
return app.activeScript;
}
catch(err) {
return new File(err.fileName);
}
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------
function GetFontListFromExcel(excelFilePath, splitChar) {
if (typeof splitChar == "undefined") var splitChar = ";";
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(1)\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([
eval("\"" + tempArrLine[0] + "\""), // A -- Find
eval("\"" + tempArrLine[1]+ "\"") // B -- Change to
]);
}
return data;
}
Hope it helps!
— Kas
AdChoices