3 Replies Latest reply on Sep 16, 2017 7:27 AM by GusSoares

    Import all sheets from Excel

    romanobstuder Level 1

      Hi all

      I am trying to create a script that will import _all_ sheets of a giveen .xls into an Indesign table.

      At this point I got as far as importing one sheet. It is always the first one, or a random one (!).

      I have tried to import specific sheets from a given .xls file but have no luck with that.

      Maybe somebody can give me some hints.

      Romano

       

       

      I commented out all atributes in the "function setXLimportPrefs()", as in my experiments I got very random results.

      I can say that things did change when switching some of these on - just not in a predictable way.

      It gave me some confidence in as "the code is doing something, just not sure what"

       

      In my searches on the web I also came accross this code fragment:

      myDoc.pages[0].place(myFile,[0,0],undefined,false,true);

      But I could not find any documentation on what the additional attributes inside the () do.

      Again, maybe somebody can give me some hints here.

       

       

       

      Here is my working code

      ==================

       

      function chooseFile(){

      myFile = File.openDialog("Choose an Excel file");

      if (myFile == null) { exit() }

      }

      function myImport(myDocument){
      app.scriptPreferences.userInteractionLevel = UserInteractionLevels.neverInteract; 
      myTextFrame.geometricBounds = myGetBounds(myDocument, myPage);
      setXLimportPrefs();
      try {
      myTextFrame.place(myFile);
      }
      catch (e) {
      alert (e + " " + app.excelImportPreferences.errorCode);
      exit();
      }
      app.scriptPreferences.userInteractionLevel = UserInteractionLevels.interactWithAll;
      }
      function setXLimportPrefs() {
      with (app.excelImportPreferences) {
      //alignmentStyle = [AlignmentStyleOptions.spreadsheet,
      //AlignmentStyleOptions.leftAlign,
      //AlignmentStyleOptions.rightAlign,
      //AlignmentStyleOptions.centerAlign][0];
      //decimalPlaces = 3;
      //errorCode = 0;
      //preserveGraphics = true; // Not sure what this does either
      //rangeName = ""; // Hopefully leaving this blank will cause ranges to be ignored
      //sheetIndex = 0;
      //sheetName = "";
      //showHiddenCells = true;
      //tableFormatting = [TableFormattingOptions.excelFormattedTable,
      // TableFormattingOptions.excelUnformattedTable,
      //TableFormattingOptions.excelUnformattedTabbedText][1];
      //useTypographersQuotes = true;
      //viewName = "";
      }
      }

        • 1. Re: Import all sheets from Excel
          John Hawkinson Level 5
          In my searches on the web I also came accross this code fragment:

          myDoc.pages[0].place(myFile,[0,0],undefined,false,true);

          But I could not find any documentation on what the additional attributes inside the () do.

          Again, maybe somebody can give me some hints here.

           

          Page.Place:

          Array of

          any

          place (

          fileNameFile[, placePoint: Array of Measurement Unit (Number or String)][, destinationLayer: Layer][, showingOptions: bool=false][, autoflowingbool=false][, withProperties: Object])
          Places the file.

           

           

          ParameterTypeDescription
          fileNameFileThe file to place
          placePointArray of Measurement Unit (Number or String)The point at which to place (Optional)
          destinationLayerLayerThe layer on which to place (Optional)
          showingOptionsboolWhether to display the import options dialog (Optional) (default: false)
          autoflowingboolWhether to autoflow placed text (Optional) (default: false)
          withPropertiesObjectInitial values for properties of the placed object(s) (Optional)
          1 person found this helpful
          • 2. Re: Import all sheets from Excel
            romanobstuder Level 1

            Hi John

            Thank you for the definition.

            Turns out my script is coming together without the exact definitions in the import.

            I now manage to cycle through all the Sheets in the excel files and import all tables.

            I also found a nice autoflow script fragment that keeps adding pages until all content is placed.

            See bleow

            I am very happy thank you for your help.

            Romano

             

             

             

             

            function myImport(myDocument){

             

            app.scriptPreferences.userInteractionLevel = UserInteractionLevels.neverInteract; 

            myTextFrame.geometricBounds = myGetBounds(myDocument, myPage);

            var errorFromExcel = 0;

             

            for(var mySheetIndexCounter = 0; errorFromExcel == 0; mySheetIndexCounter++){

            setXLimportPrefs(mySheetIndexCounter);

            try {

            myTextFrame.parentStory.insertionPoints.item(-1).place(myFile);

            }

            catch (e) {

            //set errorFromExcel

            errorFromExcel = 1;

            //alert (e + " " + app.excelImportPreferences.errorCode);

            //exit();

            }

            flow (myDocument)

             

            }

            app.scriptPreferences.userInteractionLevel = UserInteractionLevels.interactWithAll;

            }

            function flow (myDocument) {
            var m = myDocument.pages[0].marginPreferences;
            var gbounds = [m.top, m.left, myDocument.documentPreferences.pageHeight - m.bottom, myDocument.documentPreferences.pageWidth - m.right];
            myDocument.viewPreferences.rulerOrigin = RulerOrigin.pageOrigin;
            while (myDocument.pages[-1].textFrames[0].overflows) {
            var tf = myDocument.pages.add().textFrames.add ({geometricBounds: gbounds});
            tf.previousTextFrame = myDocument.pages[-2].textFrames[0];
            }
            }

            1 person found this helpful
            • 3. Re: Import all sheets from Excel
              GusSoares Level 1

              Hey,

               

              Thanks a lot for posting this code! I was struggling to loop through sheets within an excel file.

              I was going for the errorCode approach, but the way you used it in the for loop was very elegant and solved this part of the code for me.

               

              Best!

              Gus