Highlighted

Importing excel cell range information to Indesign with javascript

Community Beginner ,
May 20, 2020

Copy link to clipboard

Copied

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.

 

 

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

2020-05-20_23-26-08.png

 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

TOPICS
How to, Import and export, Scripting, SDK

Views

804

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Importing excel cell range information to Indesign with javascript

Community Beginner ,
May 20, 2020

Copy link to clipboard

Copied

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.

 

 

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

2020-05-20_23-26-08.png

 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

TOPICS
How to, Import and export, Scripting, SDK

Views

805

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
May 20, 2020 1
Adobe Community Professional ,
May 20, 2020

Copy link to clipboard

Copied

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 )

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 20, 2020 0
Guru ,
May 20, 2020

Copy link to clipboard

Copied

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

2020-05-20_23-26-08.png

 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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 20, 2020 1
Community Beginner ,
May 22, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 22, 2020 0
Adobe Community Professional ,
May 22, 2020

Copy link to clipboard

Copied

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 )

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 22, 2020 0