5 Replies Latest reply on Jul 5, 2013 7:27 AM by Jon.Nelson

    How do I create a series of text frames using values from Excel list?

    Jon.Nelson

      First of all, this is the very first script I'm attempting to write from scratch. I'm completely green at scripting, and I've picked up a few snippets from Adobe's ID scripting guide, but nothing has really stuck yet relating to this particular objective.

       

      My supervisor keeps a master list of ad spaces, with the name of the account, how wide the space is, and how tall the space is, all in an Excel sheet. These spaces can number in the hundreds, and I am stuck manually drawing one rectangle for every space, which takes a very long time.

       

      I'd like to create/have help creating a script that will take these values and "automagically" draw these spaces in the form of text frames, with the width (in columns) and the height (in inches) defined by the values in the master list, as well as the name of each account appearing in the subsequent text frames.

       

      The script doesn't necessarily need to be able to pull the values straight from the Excel sheet; I can transfer the values to a support text file if needed, or directly into the script, changing it as I need it. A big thing (if it is not able to pull right from an Excel sheet) is that the number of spaces changes weekly, and so do the accounts, and the width and the height. Accordingly, it would be ideal if the values from the sheet could be changed easily, so as to create a new set of spaces as needed.

       

      Positioning for each space is not crucial, only height and width. If they all appear on top of each other on the same page, that will be a result for me. The main idea is to not have to draw them all manually, one by one.

       

      To me, this sounds like a tall order, but hopefully some experienced scripters out there can assist me, as I wish to become experienced as well.

       

      So, the TL;DR version:

      - Script needs to draw a series of text frames.

      - Text frames dimensions need to be defined by width and height values from Excel sheet.

      - Text frames must have account name as contents (from account names in Excel sheet).

      - Accounts, width and height change every week in the Excel sheet, so must be relatively easy to exchange all of the values.

      - The width values are on the Excel sheet as columns. It would be ideal if the script could convert those numbers into multiples of columns as needed.

      - (Optional) Script can pull values directly from Excel sheet.

      - (Optional) Script can define text frame fill color as gray. (If it works as I think it will, I could just select all the resulting text frames myself and set them all to gray at once... I'm not that lazy )

       

      Thanks in advance to whomever can assist in any possible way, even if it is just a push in the right direction. This script will save 1-2 hours of tedium every week.

        • 1. Re: How do I create a series of text frames using values from Excel list?
          Andreas Jansson Level 2

          Sound like the perfect thing for InDesign Scripting.

           

          I would copy the Excel contents into a text file, to get a format that is easily read from InDesign, and there will automatically be a TAB for each "cell" just using copy/paste.

           

          Here is a piece of code, that you perhaps could go on with (adding variable to change pages and location on page, and other stuff).

          The readFileLineByLine function, can be easily re-used with any function using "callback". You simply supply the function to it, that you want to be executed for every line of text that is read:

           

          const COLUMN_WIDTH = 2; // Define the column width in inch
          
          var pageIndex;
          var textFramesExported; // not implemented.
          
          // Add a new dokument. Set myDoc to app.activeDocument to use 
          // the current document instead of creating a new one.
          var myDoc = app.documents.add();
          
          // The doSomethingWithTextRow function is called upon for every line of text read.
          readFileLineByLine('c:\\test.txt', doSomethingWithTextRow);
          
          function doSomethingWithTextRow(row){
              // We expect the text line to be TAB separated (\t = TAB). We get that from just copying the contents of an
              // excel file into a text document.
              var cells = row.split('\t');
              var companyName = cells[0]; // The Company name in the first slot of the array
              var width = COLUMN_WIDTH * cells[1];
              var height = cells[2];
          
              // Create a new text frame for every row handled
              if (pageIndex==undefined) pageIndex = 0; // Count up when you have exported a number of texts, I leave this for you to do.
              var newTextFrame = myDoc.pages[pageIndex].textFrames.add();
              newTextFrame.contents = companyName;
          
              // The text frame is created in the top left corner.
              newTextFrame.geometricBounds = [0, 0, height + ' in', width + ' in']; // Top, Left, Bottom, Right 
          
              // You might want to move the textframes to other positions, keeping track of how many you put out per page.
              newTextFrame.move( [10, 10] );
          }
          
          
          function readFileLineByLine(path, callbackFn){
              var myFileIn = new File(path);
              if (File.fs == 'Windows'){
                  // This was probably added to recognize UTF-8 (even without its start marker?)
                  myFileIn.encoding = 'UTF-8';
              }
              myFileIn.open('r');
              var myEncoding = myFileIn.encoding;
              try{
                  if (!myFileIn.exists){
                      throw('Missing file: ' + myFileIn.fsName)
                  }
                  var ln = '';
                  while(!myFileIn.eof){
                      // Read the lines from the file, until an empty line is found [now as a remark].
                      ln = myFileIn.readln()
                      // if(ln !='' && ln!='\n'){
                         // Call the function supplied as argument
                         callbackFn(ln);
                      // }
                  }
              }catch(e){
                  alert(e);
                  gCancel = true;
              }
              finally{
                  myFileIn.close();
              }
          }
          

           

           

          The file in C:\ in my example was saved as UTF-8 and looks like this (showing hidden characters):

           

          Capture.PNG

           

          Message was edited by: Andreas Jansson

          • 2. Re: How do I create a series of text frames using values from Excel list?
            Jon.Nelson Level 1

            I had to do a bit of tweaking, but you have saved me hours of tedious work. It works perfectly and easily. I just have to copy the cells from the Excel file to a text file (which did, indeed, create tabs for each column), and replace the text file with every sheet. The only thing is that I have to still manually change the fill color, alignment, justification, etc. but those are small matters compared to what has been accomplished here. Again, thank you for your help.

            • 3. Re: How do I create a series of text frames using values from Excel list?
              [Jongware] Most Valuable Participant

              jonnelson2011 wrote:

               

              .. The only thing is that I have to still manually change the fill color, alignment, justification, etc. but those are small matters compared to what has been accomplished here.

               

              Ah, but it isn't too hard

               

              1. Fill color: http://jongware.mit.edu/idcs6js/pc_TextFrame.html#fillColor

              2. Alignment: http://jongware.mit.edu/idcs6js/pc_TextFramePreference.html#verticalJustification (I assume you mean 'vertical alignment)

              3. Justification: http://jongware.mit.edu/idcs6js/pc_Text.html#justification

               

              The first one is easy. fillColor is a base property of text frames, so all you need is something like

               

              newTextFrame.fillColor = app.activeDocument.swatches.item('Gray');

               

              ('Gray' because you said they were. If you meant 'a tint of black', use "Black" and fillTint.)

               

              The next one is slightly more difficult. Vertical alignment is not a property of text frames per se, but rather of its own "Preferences" object. (Why? Ask Adobe...) Scroll to the top of the html document to see how this relates to the frame, and to the bottom to see how it's actually called for a Text Frame.

               

              The last one is again a larger challenge, and I suggest you tackle this only after solving the 2nd one. This is because now you have to navigate through several layers of abstraction, only to set the text justification! If you do this, however, it will show you have a good grasp of the basic principles of InDesign's internal Document Object Model -- and at that point, you'll be able to find your way around my reference with confidence.

              • 4. Re: How do I create a series of text frames using values from Excel list?
                Andreas Jansson Level 2

                I agree with Jongware, it's not that hard, and also there are more than one way to skin a kat:

                I don't think I would assign the remaining properties "individually" from the script... I'd rather make an object style manually, with the fill and vertical alignment set, and in that object style I would also set the paragraph style to use (in the paragraph style, font face, size, justification, and other properties are set).

                In this way you can change almost everything from the styles.

                Using nested styles in the paragraph styles you can achieve lots of things, if you need to have different styles on the header and the body text in every textframe, for example.

                 

                You could make a template document that contains the styles, and have the script open that one instead of creating a completely new empty document.

                (Or if you refer to activeDocument from the script, you just add the styles to your document.)

                 

                With the styles on place you could let the script assign the object style, using a single line as below:

                newTextFrame.appliedObjectStyle = myDoc.objectStyles.item('TheNelsonStyle'); // ...or whatever name you decide for the object style ; -)

                 

                Always try to make use of styles! At least they usually tend to save you from more trouble than one cause by not using them!

                 

                Good luck!

                Andreas Jansson

                • 5. Re: How do I create a series of text frames using values from Excel list?
                  Jon.Nelson Level 1

                  Jongware,

                   

                  In my preliminary searches, I did happen upon that very website to see if I could figure out how to invoke certain methods (is methods the word? ). I will admit, I can't exactly get around your reference with much confidence, if any at all, simply because, as I mentioned in my first post, I'm completely new to scripting (at this level at least... I can put together a "Hello World" all by myself!)

                   

                  I managed to figure out how to set fill color (which was indeed a tint of black), alignment, and justification, but I still cannot figure out font size. I saw a reference to pointSize, and I tried all sorts of configurations in the line to make it work, but all InDesign gave me was "cannot identify property pointSize" (or something to that nature). If I could get this, then my script (ahem... Andreas's script) will be perfect.

                   

                  And yes, while I could have defined a paragraph style and simply referred to it in a single line of code, I wanted to get my hands dirty, so I preferred setting everything property by property.

                   

                  I believe if I do end up using a style for this script, it will be called "TheNelsonStyle."

                   

                  Thank you both for all your help.