Importing excel cell range information to Indesign with javascript

[nicolast61334553]

Hi Developorers!

I have managed to import information from an excel table into indesign with js, I am curently using the dialog box and placing the result to a certain layer:

var myExcelFile = File.openDialog("Choose the excel file");
app.documents.item(0).layers.item("data").textFrames[0]place(myExcelFile);

My question is the following:

Is there a way to import certain data from different places of the excel sheet? What I would like to do is get data from two or more pages of the spreadsheet, ie page1(a1:b1), page2(c1:c3) 

I have seen how I can import certain range data using app.excelImportPreferences, but this will only import a SINGLE range of data ie page1(a1:b1).

How can I import more data ranges using js?

The excel file is very complicated and converting everything to an array would not be a good solution in this particular case.

Any advise will be more than welcome.

[Laubender]

Hi Nicolas,

don't think it's possible to import not adjacent cell ranges in one go.

For that you need two or more import actions.

Regards,
Uwe Laubender

( ACP )

[Kasyan Servetsky · Accepted solution]

Off the top of my head, here's a very simplified approach that can be used (just for illustration):

main();

function main() {
	var doc = app.activeDocument;
	var fileExcel = new File("~/Desktop/Test.xlsx");
	
	if (fileExcel.exists) {
		var ranges = [
							"a1:b1",
							"c1:c3"
							];
		
		for (var i = 0; i < ranges.length; i++) {
			app.excelImportPreferences.rangeName = ranges[i];
			doc.pages[0].textFrames[i].place(fileExcel);		
		}
	}
}

 I have no idea what you're trying to achieve, but let's assume you have a number of text frames per page and want to fill them with data from excel. You can list all ranges in an array and place them all adding pages on the fly. To keep them in the correct order, create a template -- indt-file -- with one page. On this page, you can create a number of text frames and label them in the order you want: e.g. from top left to bottom right. Something like 'range 1', 'range 2', and so on. On adding a page, you can increment these numbers with a simple function. Then the script can find the correct frame by label. Otherwise, the script will use frames in a random order giving unexpected results.
That's the way I did it in my practice.

Alternatively, if you don't need to keep formatting and plain text data is enough for you, you can grab all the data from a spread, or (for Windows only) from all spreads and process them in InDesign. Here's a 'ready to use' function for this task.

— Kas

[nicolast61334553]

That is a very nice and simple approach, thank you! I will give it a go

[Laubender]

Hi Kasyan,

thanks for the code!

It should also be possible to place a single range to a single insertion point.

So all ranges would go to one story. But of course not to one single table.

Best,
Uwe Laubender

( ACP )