How to import .xlsx to InDesign

Question

With following code I am trying to import excel file to indesign. I can get data but if in table is more then 3 rows it just continue in line and doesn't create new line.

var doc = app.activeDocument;

// get a text from the XLSX file
var inputFile = File("~/Desktop/test.xlsx");
var temp_frame = doc.textFrames.add();
temp_frame.place(inputFile);
var text = temp_frame.parentStory.contents;
temp_frame.remove();

// make a table from the text
text = text.split('\r');

var table = [];
for (var row = 1; row < text.length; row++) {
    table.push(text[row].split('\t'));
}

// loop through the table and make the cards
for (var i = 0; i < table.length; i++) {

    var title       = table[i][0];
    var description = table[i][1];
    var price       = table[i][2];

    var card = make_card(title, description, price);

    // move card to some place
    card.move([10,10]);
    card.move(undefined, [i*75, 0]); 


     if(i > 2){
        card.move([20,10]);
        card.move(undefined, [i*75, 20]);
    } 
}



// the function to create and return a card
function make_card(title, description, price) {
    var doc = app.activeDocument;

    var title_frame = doc.textFrames.add();
    title_frame.geometricBounds = [20, 80, 30, 150];
    title_frame.contents = title;

    var description_frame = doc.textFrames.add();
    description_frame.geometricBounds = [30, 80, 80, 150];
    description_frame.contents = description;

    var price_frame = doc.textFrames.add();
    price_frame.geometricBounds = [80, 80, 100, 150];
    price_frame.contents = price;

    var group = doc.groups.add([title_frame, description_frame, price_frame]);
    return group;
}

My question is how to write code that card doesn't go out of the document but follow on next line and as well as how to add new page automatically when current document is already full.

I tried do do it with following code in loop if index is heigher than 2 move card down. It move it down but it follow in the same line (out of the document) and I also think there is better way how to write it because if excel has 1000 rors and I have to set up this if index condition for every third element ... code will be one mash

     if(i > 2){
        card.move([20,10]);
        card.move(undefined, [i*75, 20]);
    } 

Answer 1

For this case the simplest straightforward implementation could be like this:

var doc = app.activeDocument;

// get a text from the XLSX file
var inputFile = File("~/Desktop/test.xlsx");
var temp_frame = doc.textFrames.add();
temp_frame.place(inputFile);
var text = temp_frame.parentStory.contents;
temp_frame.remove();

// make a table from the text
var rows = text.split('\r');
var table = [];
for (var i = 1; i < rows.length; i++) table.push(rows[i].split('\t'));

// width and height of cards, gaps, properties of the grid
var card_width = 70;
var card_height = 80;
var gap = 5;
var cols = 2;
var rows = 3;
var cards_per_page = cols * rows;

// calculate and add pages
var cards = table.length;
var pages_to_add = Math.ceil(cards / cards_per_page) - 1;
while(pages_to_add--) doc.pages.add();

var page_num = 0; // start page
var start_point = [10,10]; // start point for a first card on a page

main_loop:
for (var i = 0; i < cards; i++) {

    for (var row = 0; row < rows; row++) {

        for (var col = 0; col < cols; col++) {

            // break the loop if there is no more rows in the table
            if (table.length == 0) break main_loop;

            var table_row   = table.shift(); // take a first row from the table
            var title       = table_row[0];
            var description = table_row[1];
            var price       = table_row[2];

            // make a card
            var card = make_card(title, description, price);

            // send the card to the some page and move at some place
            card.move(doc.pages[page_num]);
            card.move(start_point);
            card.move(undefined, [(card_width + gap)*col, (card_height + gap)*row]);
        }
    }

    if (i > (page_num-1) * cards_per_page) page_num++; // increase the page number

}


// the function to create and return a card
function make_card(title, description, price) {
    var doc = app.activeDocument;

    var title_frame = doc.textFrames.add();
    title_frame.geometricBounds = [20, 80, 30, 150];
    title_frame.contents = title;

    var description_frame = doc.textFrames.add();
    description_frame.geometricBounds = [30, 80, 80, 150];
    description_frame.contents = description;

    var price_frame = doc.textFrames.add();
    price_frame.geometricBounds = [80, 80, 100, 150];
    price_frame.contents = price;

    var group = doc.groups.add([title_frame, description_frame, price_frame]);
    return group;
}