2 Replies Latest reply on Jun 28, 2011 10:41 PM by romanobstuder

    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];
            }
            }