12 Replies Latest reply: Dec 21, 2011 3:04 AM by Laubender RSS

    Indesign Script for placing excel-file

    Oscar@Medusa Community Member
    Oscar@Medusa Dec 13, 2011 8:12 AM

      Hi all, I'm new to Indesign Scripting, and I'm hoping someone can help me with the following code:

      All I want to do is:

      1. open indesign file
      2. open excel file
      3. place excelfile as a table (with flow enabled)
      4. save document & export as PDF

       

      Thanks!

       

      //Define file to open
var myDocumentFile = new File("/d/Projecten/Indesign Server/document.indd");
//Define file to save
//var mySaveFile = new File("/d/Projecten/Indesign Server/document_save.indd");
//Define file to export
var exportPDF = new File("/d/Projecten/Indesign Server/document_save.pdf");
//Open xls-file to place as table
var myExcelFile = new File ("/d/Projecten/Indesign Server/products.xls");

var result = "";
if(!myDocumentFile.exists) {
    result = "Unable to find DOCUMENT:  " + myDocumentFile.path;
    $.writeln(result);
    exit();
} else if(!myExcelFile.exists) {
    result = "Unable to find EXCEL:  " + myDocumentFile.path;
    $.writeln(result);
    exit();
} else {
    // Open file
    var myDocument = app.open(myDocumentFile);
    with(app.documents.item(0).pages.item(0)){
        myTarget = textFrames.item("tabel");
    }
    var myPage = myDocument.pages.item(0);

    //Set excel placement properties
    setXLimportPrefs();

    // Place Excel file
       try {
             $.writeln("Place Excel file");
            //HELP!! HOW TO PLACE EXCEL FILE?
            //myTextFrame.place(myFile);
        }
        catch (e) {
             $.writeln("Place Excel file "  + e + " " + app.excelImportPreferences.errorCode);
            myDocument.close();
            exit();
        } 
    if(!exportPDF.parent.exists && !exportPDF.parent.create()) {
        result = "Not exported.  Unable to create the folder:  " + exportPDF.parent.fullName;
        $.writeln(result);
    } else {
        app.documents.item(0).exportFile(ExportFormat.pdfType, exportPDF, app.pdfExportPresets.item("[Press Quality]"));
    }
    myDocument.exportFile(ExportFormat.PDF_TYPE,exportPDF);
    myDocument.close();
}

function setXLimportPrefs(){    $.writeln("setXLimportPrefs()");
        with(app.excelImportPreferences){
            //alignmentStyle property can be:
            //AlignmentStyleOptions.centerAlign
            //AlignmentStyleOptions.leftAlign
            //AlignmentStyleOptions.rightAlign
            //AlignmentStyleOptions.spreadsheet
            alignmentStyle = AlignmentStyleOptions.spreadsheet;
            decimalPlaces = 4;
            preserveGraphics = true;
            //Enter the range you want to import as "start cell:end cell".
            rangeName = "A1:B16";
            sheetIndex = 1;
            sheetName = "Appetizers";
            showHiddenCells = false;
            //tableFormatting property can be:
            //TableFormattingOptions.excelFormattedTable
            //TableFormattingOptions.excelUnformattedTabbedText
            //TableFormattingOptions.excelUnformattedTable
            tableFormatting = TableFormattingOptions.excelFormattedTable;
            useTypographersQuotes = true;
            viewName = "";
        }
}
result;
        • 1. Re: Indesign Script for placing excel-file
          [Jongware] CommunityMVP
          [Jongware] Dec 13, 2011 8:38 AM (in response to Oscar@Medusa)

          Doesn't this script work, or what? Do we have to try it to see?

          • 2. Re: Indesign Script for placing excel-file
            Oscar@Medusa Community Member
            Oscar@Medusa Dec 13, 2011 9:15 AM (in response to [Jongware])

            It doesn't work where you see:

                        //HELP!! HOW TO PLACE EXCEL FILE?

            • 3. Re: Indesign Script for placing excel-file
              [Jongware] CommunityMVP
              [Jongware] Dec 13, 2011 9:39 AM (in response to Oscar@Medusa)

              myTextFrame is undefined in your script.

               

              This works for me:

              myTextFrame = app.selection[0];
var myExcelFile = new File ("~/Documents/idml/some file.xlsx");
myTextFrame.place (myExcelFile);

               

              -- when I select a text frame, of course.

               

              To debug this, you might want to remove the try ... catch statements because they are effectively hiding the actual error right now: InDesign can place the file but the variable didn't exist.

               

              No sweat, I've fallen for this many times before.

              • 4. Re: Indesign Script for placing excel-file
                Oscar@Medusa Community Member
                Oscar@Medusa Dec 16, 2011 1:00 AM (in response to [Jongware])

                Indeed, placing is simpeler compared to what I had in mind.

                Removing the try...catch also helped locating the error.

                Thanks for directing me!!

                 

                2 questions remain:

                1. How do I 'replace' a textframe with the 'placement' of the excel-file, so the location & width can be pre-defined in an Indesign Document?
                2. How do I 'place' a file through javascript so it automatically adds new pages?
                • 5. Re: Indesign Script for placing excel-file
                  [Jongware] CommunityMVP
                  [Jongware] Dec 16, 2011 2:56 AM (in response to Oscar@Medusa)

                  1. My snippet of code doesn't replace the text frame, it imports the data into it. So no need to mess around with frames.

                  2. CS4 and earlier: keep on checking if the last text frame overflows, and if so, add a new page and link in a new text frame. Take care to back-check if the overflow persists (if your data also doesn't fit in this new frame, e.g. when there is some weirdly large single cell), otherwise the script will keep on adding pages until it runs out the max of 9,999 pages.

                   

                  For CS5 and newer, you could probably use Smart Reflow options. This has been discussed before, so a search of the forum might turn up something useful.

                  • 6. Re: Indesign Script for placing excel-file
                    Oscar@Medusa Community Member
                    Oscar@Medusa Dec 16, 2011 3:41 AM (in response to [Jongware])

                    1. Indeed. But I'm writing a script for Indesign Server, so I don't have a selection. I know I should use "Script label" to give the textframe an ID, but I didn't find a way to "search" through the textframes to find one with the name "table", so I can place the excel-table in it

                    2. Thanks! I'll seach for "Smart Reflow options"

                     

                    Thank you for all your help, Jongware!

                    • 7. Re: Indesign Script for placing excel-file
                      [Jongware] CommunityMVP
                      [Jongware] Dec 16, 2011 4:37 AM (in response to Oscar@Medusa)

                      A-ha, you need to identify some specific textframe. Pre-CS5 it was as simple as "textFrames.item("yourlabel") but that very useful feature wsa removed, so now you have to use the Versioning Trick (tell ID you are using an older version of the script language), or manually loop through all your frames to find the one. This thread should help you with that: http://forums.adobe.com/thread/615381

                      • 8. Re: Indesign Script for placing excel-file
                        Oscar@Medusa Community Member
                        Oscar@Medusa Dec 20, 2011 5:07 AM (in response to [Jongware])

                        Hi Jongware, So far I've got it working:

                         

                         

                        myDocument.textPreferences.smartTextReflow = true;
myDocument.textPreferences.limitToMasterTextFrames = false;
myDocument.textPreferences.deleteEmptyPages = true;
myDocument.textPreferences.addPages = AddPageOptions.END_OF_STORY;
app.scriptPreferences.version = "6.0";
      myTarget = myDocument.textFrames.item("tabel").getElements();
app.scriptPreferences.version = "7.0"
myTarget[0].place(myExcelFile);

                         

                        This works fine, EXCEPT that it doesn't add pages when the placed excel-file doesn't fit on one page!

                         

                        What am I missing here?

                        I want to find the Javascript equivalent for "PLACE... and SHIFT + CLICK"

                        • 9. Re: Indesign Script for placing excel-file
                          [Jongware] CommunityMVP
                          [Jongware] Dec 20, 2011 6:46 AM (in response to Oscar@Medusa)

                          That would be your

                           

                          2. Thanks! I'll seach for "Smart Reflow options"

                           

                          I don't Do CS5, but a quick visit to the Javascript reference shows the various reflow options are all located in TextPreferences.

                           

                          Search the forum; I found this one particularly funny (http://forums.adobe.com/thread/868977) because apparently it's a 'stupid feature'

                           

                          Me Hardcore Coder, Me Create Pages & Frames By Myself. It also makes my scripts CS4-and-older-friendly.

                          • 10. Re: Indesign Script for placing excel-file
                            Oscar@Medusa Community Member
                            Oscar@Medusa Dec 20, 2011 9:23 AM (in response to [Jongware])

                            *dropping down to my knees, saluting with both hands in the air*

                            Oh wise Hard Coder, please enlighten my path!

                             

                             

                            ok, so there's a bug in using "Smart Reflow options" on indesign server.

                            (even adding [ myTarget[0].recompose(); ] doesn't change anything)

                             

                            How should the 'old fashioned' method work?

                            Hoe do I know how many pages should be added with a "threaded text frame"?

                             

                            Like, if the excel-file I'm 'placing' will span 2 1/2 pages, how should I write the 'add-page-loop' to continue adding pages until the the bottom of the excel-file is reached?

                            • 11. Re: Indesign Script for placing excel-file
                              [Jongware] CommunityMVP
                              [Jongware] Dec 20, 2011 10:43 AM (in response to Oscar@Medusa)

                              Since you know which text frame you are importing in to, all you need to do is check its Overflows property. If so, add a page the usual way and put a new text frame on it (you should decide on the size & position beforehand). Set your current text frame's nextTextFrame property (http://jongware.mit.edu/idcsjs5.5/pc_TextFrame.html#nextTextFrame) to this newly created one. Then simply repeat until the final frame doesn't overflow anymore.

                               

                              There is a small catch I think in blindly importing tables: if a row doesn't fit on a single page, ID will bump it automatically and you'll be adding pages forever (even for a fairly small hard disc, "forever" might still take a while). It depends on your data; you might want to add some check, as in "I just loaded a table into a text frame but it still contains nothing". I wonder how I'd check that, actually.

                              • 12. Re: Indesign Script for placing excel-file
                                Laubender CommunityMVP
                                Laubender Dec 21, 2011 3:04 AM (in response to [Jongware])

                                "I just loaded a table into a text frame but it still contains nothing". I wonder how I'd check that, actually.

                                @Jongware:
                                To prevent this situation you could set the max. allowed cell height below your min. text frame height.

                                 

                                If this is not applicable, because in some situations you cannot know the usable text frame height beforehand, you can iterate through all your set rows and check if the parentTextFrames[0].parentPage.name property of the first insertionPoint of its first cell is defined. If it's undefined and your last text frames parentPage.name is not equal to the last defined parentPage.name of your check your table will remain overset.

                                 

                                Thinking about that method I must admit, that you can get a false positive, if a text wrap object on an applied master page comes into play…

                                 

                                So no, I see no fail-safe check for this situation.

                                 

                                Uwe