11 Replies Latest reply on Mar 5, 2015 8:57 PM by Senthilvel S

    Create Excel file using Indesign javascript

    Senthilvel S Level 1

      Hi All,

       

      We can create csv file using indesign javascript. Is there any way to create excel file using indesign javascript?

       

      Thanks in Advance

        • 1. Re: Create Excel file using Indesign javascript
          Jump_Over Level 5

          Hi,

           

          If found - beware Microsoft!

           

          Jarek

          • 2. Re: Create Excel file using Indesign javascript
            pixxxel schubser Level 5

            Try as workaround:

            create a tab separated*txt. This file can be opened in MS Excel.

            • 3. Re: Create Excel file using Indesign javascript
              Peter Kahrel Adobe Community Professional & MVP

              In short, the answer is 'No, you can't create an Excel file using JavaScript.' You might, if you know Excel's file format, but that would be a nonsensical exercise. Do as pixxxel schubser said: create a tab-separated text file because that can be opened in Excel.

               

              Peter

              • 4. Re: Create Excel file using Indesign javascript
                Loic.Aigon Adobe Community Professional

                Hi all,

                 

                CSV files should be openable too. It's just that Excel uses semi-colons as separator for what I can remember.  Pretty certain I opened csv files with excel. That's why I second Peter on the non sense here.

                 

                Loic

                • 5. Re: Create Excel file using Indesign javascript
                  Colin Flashman Adobe Community Professional

                  There is a year-old post that may do something similar to what the OP wants: Exporting data in tables to an Excel spreadsheet?

                  • 6. Re: Create Excel file using Indesign javascript
                    pixxxel schubser Level 5

                    Loic.Aigon schrieb:

                     

                    Hi all,

                     

                    CSV files should be openable too. It's just that Excel uses semi-colons as separator for what I can remember.  Pretty certain I opened csv files with excel. That's why I second Peter on the non sense here.

                     

                    Loic

                    Loic.Aigon,

                    yes and no.

                    Yes, because of: you can create CSV or tab separated TXT by using Javascript.

                    Yes, because of: you can copy the contents with Javascript and open MS Excel im combination with VBS or Applescript in Javascript and save the file as XLS

                    No, because of: this was not the question.

                    Senthilvel S schrieb:

                    … Is there any way to create excel file using indesign javascript?

                     

                    Thanks in Advance

                    And the short answer is (like Peter Kahrel said before): No, there is no way with Javascript (alone).

                    • 7. Re: Create Excel file using Indesign javascript
                      Obi-wan Kenobi Adobe Community Professional

                      Hi all,

                       

                      If InDesign and Excel app. are open, why not?

                       

                      In InDesign app., just select a table or tab delimited text, copy it!

                      Switch to Excel app., create a new file, just past, save the file [with a special name in a special folder] and close it.

                      Return to InDesign app.

                       

                      Can this be automatized?

                      • 8. Re: Create Excel file using Indesign javascript
                        Loic.Aigon Adobe Community Professional

                        Hello,

                         

                        No, because of: this was not the question.

                         

                        What I implied with my answer is that looking for creating an excel file on the fly might be possible in extendscript. A fellow of us wrote an extendscript library to parse excel files. We used it in a project and it worked fine. At this time it was designed to retrieve data from excel for files which couldn' simply got exported as CSV for datamerge or other technologies. Once that said, when I saw this thread I thought about this work and I was somehow convinced tht one could write such a processor ( data to xls ).

                        But and I come here to my statement, if one can open csv in excel thus geberating an excel file, what's the point of re coding a data to xls processor unless to save a few seconds and one manipulation.

                         

                        If one thinks it's worth it then it's ok to me. But I can't still see the advantages of such a work but it's my humble opinion. I would have no problem being contradicted

                         

                        Obi-wan Kenobi, I was presuming that we were in a data (not the indesign layout itself) to xls process here.

                         

                        Loic

                        http://www.ozalto.com

                        • 9. Re: Create Excel file using Indesign javascript
                          nicolaik Level 1

                          Yes, but quite difficult and time consuming.

                           

                          If you are looking at the latest Excel file format (xlsx), it is a zipped file with bunch of XML files inside. You can use your JavaScript to produce XML files and command line script to zip them. If it helps, you can use xslt on the way.

                           

                          Older Excel xls format is just one uncompressed xml file, which is much easier to produce.

                           

                          The biggest advantage of this approach is that you can add formatting to ecxel, custom groups, formulas and multiple work sheets.

                           

                          On a mac all tools are available from OS, on PC you will might need to add some command line utilities, depends on your Windows version.

                           

                          By all means, it is good fun to do but not a five minutes job. If I did it for a client I would quote a week. I would advise to look at simpler workarounds suggested before - scv export, applescript, etc..

                          • 10. Re: Create Excel file using Indesign javascript
                            Trevorׅ Adobe Community Professional

                            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;
                            };
                            
                            
                            • 11. Re: Create Excel file using Indesign javascript
                              Senthilvel S Level 1

                              Thanks to everyone. Hi "Trevor" thank you very much for the solution.