Skip navigation
Oscar@Medusa
Currently Being Moderated

Indesign Script for placing excel-file

Dec 13, 2011 8:12 AM

Tags: #tables #place #excel

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;
 
 
Replies
  • Currently Being Moderated
    Dec 13, 2011 8:38 AM   in reply to Oscar@Medusa

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

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 13, 2011 9:39 AM   in reply 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.

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 16, 2011 2:56 AM   in reply 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.

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 16, 2011 4:37 AM   in reply 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

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 20, 2011 6:46 AM   in reply 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.

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 20, 2011 10:43 AM   in reply 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.

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 21, 2011 3:04 AM   in reply 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

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points