Create Excel file using Indesign javascript

Hi all,

This is a way of generating an excel file from an InDesign table using a combination of jsx, vbs and applescript.

Quite a long discussion on it Open With below is a better implementation of the suggestions there.

// Exports SIMPLE tables to proper excel file single and double quotes in the table would have to be escaped on the Mac version
// Does not take Unicode file names
// with a bit of brain racking can be developed to deal with merged cells and nested tables
// Pieced together by Trevor (wwww.creative-scripts.com coming soonish) based on the referenced sources
// Sold AS IS https://forums.adobe.com/thread/1718021

var doc = app.properties.activeDocument && app.activeDocument,
       myTable = myTable || getTable (doc);
if (!myTable) {alert ("Take a break, needs a document with a table in it!"); exit();};
var filePath = new File (Folder.temp + "/" + +new Date + ".xlsx"),
    osFilePath = filePath.fsName;
if ($.os[0] === "M")  osFilePath =  osFilePath.replace(/(.)(\/)/g,"$1:").replace(/\//, "");

exportTable (myTable, osFilePath);
if (confirm ("Open new excel file")) filePath.execute(false);
 exit()
function exportTable (myTable, filePath)
    {
        var  numberOfRows = myTable.rows.length,
               rowNumber, columnNumber,
               isMac = $.os[0] === "M",
               rowContents = [],
               setRange, openMark, closeMark;

        if (isMac)
            {
                setRange = 'set value of range "A';
                openMark = '" to {';
                closeMark = '}';
            }
        else
            {
                setRange = 'app.Range("A';
                openMark = '") = Array(';
                closeMark = ')';
            }

for (var z = 0, rowNumber = 0; rowNumber < numberOfRows; rowNumber++) {
    var  numberOfColumns = myTable.rows[rowNumber].columns.length,
            toRange = GetExcelColumnName (numberOfColumns - 1),
            columnContents = [];
     for (columnNumber = 0; columnNumber < numberOfColumns; columnNumber++) {
         var cellContents = myTable.rows[rowNumber].cells.everyItem().contents;
         columnContents  = '"' + cellContents.join('", "') + '"';
     }
    rowContents[rowNumber] = setRange + ++z  + ":"  + toRange+ z + openMark  + columnContents + closeMark;
}

var tableData = rowContents.join("\n") + "\n";

               if (isMac)
                    {
                        // Thanks Hans https://forums.adobe.com/message/5607799#5607799
                       var myAppleScript =
                        '''set excelRunning to isRunning("Microsoft Excel")
                           tell application "Microsoft Excel"
                           set theWorkbook to make new workbook
                           tell sheet (1) of theWorkbook'''
                           + tableData + '''
                           end tell
                           save workbook as theWorkbook filename "''' + filePath + '''"
                           close active workbook
                           if not excelRunning then tell application "Microsoft Excel" to quit
                           end tell
                           on isRunning(appName)
                                tell application "System Events" to (name of processes) contains appName
                           end isRunning
                        ''';
                      app.doScript (myAppleScript, ScriptLanguage.APPLESCRIPT_LANGUAGE);
                    }

                else
                    {
                        // Thanks Calos https://forums.adobe.com/message/5607799#5607799
                        // changed by me :-)
                         var vbscript =
                         '''Dim app
                            Set app = CreateObject("Excel.Application")
                            'take away the ' from the line below if you want to see excel do it's stuff
                            'app.visible = true
                            Dim newDoc, sheet
                            Set newDoc = app.Workbooks.Add()
                            Set sheet = newDoc.Worksheets(1)
                            '''
                            + tableData
                            + 'newDoc.SaveAs "' + filePath + '''"
                            app.Quit
                            Set newDoc = nothing
                            Set app = nothing
                          ''';
                        app.doScript (vbscript, ScriptLanguage.VISUAL_BASIC);
                    }

            }



function GetExcelColumnName (columnNumber) {// 0 is A 25 is Z 26 is AA etc.
    // parsed from http://stackoverflow.com/questions/181596/how-to-convert-a-column-number-eg-127-into-an-excel-column-eg-aa
     var dividend = columnNumber + 1,
            columnName = "",
            modulo;

    while (dividend > 0)  {
        modulo = (dividend - 1) % 26;
        columnName = String.fromCharCode (65 + modulo) + columnName;
        dividend = Math.floor((dividend - modulo) / 26);
    }
    return columnName;
}


function getTable (doc) { // thanks Marc http://forums.adobe.com/message/6087322#6087322
    if (!doc) return false;
    app.findTextPreferences = null;
    app.findTextPreferences.findWhat = "\x16";
    var tables = doc.findText();
    if (tables.length) return tables[0].parentStory.tables[0];
    return false;
};