from excel column to array
Copy link to clipboard
Copied
Hey!!
quick question.
I've extracted a cloumn from an excel sheet and i want to save this column in an array, how could i?
app.excelImportPreferences.properties = {
sheetIndex : 0,
aligmentStyle : AlignmentStyleOptions.SPREADSHEET,
rangeName :"B2:B114"
}
var myProperties = app.excelImportPreferences.properties;
myTextFrame.place(fileExcel, false, myProperties);
TBH, I've taken this code from the community and now I'm wondering how to save the range in array. Because if i manged to save it in array then i can loop through it, to manuplate it.
thanks all.
*I'm using javascript
Copy link to clipboard
Copied
If you successfully imported that Excel sheet as table in your text frame, the text frame or the story of the text frame should contain a table object. From that table object you could access the contents of a column. If there are more than one rows that contents is already an array you could loop. Something like that should work:
var contentsArrayOfFirstColumn = myTextFrame.tables[0].columns[0].contents;
Regards,
Uwe Laubender
( ACP )
Copy link to clipboard
Copied
Hey Uwe, Thank you!
but i've question, how i know that i imported that Excel sheet as table?? becuse the indesign alert me that the object is invalid
Copy link to clipboard
Copied
What is the page with your text frame showing?
Is a table imported or not?
Regards,
Uwe Laubender
( ACP )
Copy link to clipboard
Copied
I didn't specify any pages. How can I import the table? Th excel file are saved in variable
Copy link to clipboard
Copied
Have you defined?
myTextFrame
If not, then add this code. It will add a text frame that fills the entire first page of the active document.
var myTextFrame = app.activeDocument.pages[0].textFrames.add();
myTextFrame.geometricBounds = app.activeDocument.pages[0].bounds;
Copy link to clipboard
Copied
Yes I've defined it, but if you may tell me how to import my excel sheet to table?
Copy link to clipboard
Copied
hi,
try this code:
function test(path){
if(path != null){
var doc = app.documents.add()
doc.properties = {
documentPreferences:{
pageHeight:'297mm',
pageWidth:'210mm',
}
}
var tmp_textframe = doc.pages[0].textFrames.add()
tmp_textframe.properties = {
textFramePreferences:{
autoSizingReferencePoint:1953459301,
autoSizingType:1752069993,
}
}
try{
tmp_textframe.place(path)
tmp_textframe.texts.everyItem().convertToTable('\t')
}
catch(e){
alert(e)
doc.close(1852776480)
}
}
}
app.doScript("test(File.openDialog('select file', '*.*'))", ScriptLanguage.JAVASCRIPT, undefined, UndoModes.ENTIRE_SCRIPT, 'test')
Copy link to clipboard
Copied
Many thank to you! it's very helpful i also, I've tried to extract one column in var so i can loop through it:
var id = tmp_textframe.tables[0].columns[1].contents;
but it didnt work this way, do you know why?
other small question, do you know if there are any replacement to place method? becuse i don't want to render the whole table to the page
tmp_textframe.place(path)
thanks a lot again!
Copy link to clipboard
Copied
var id = tmp_textframe.tables[0].columns[1].contents;
don't work because your table contains only one column.
if you want to place a part of the table you should use .place(path, true) because you don't know how many row in the table, so this line is error :
rangeName :"B2:B114"
Copy link to clipboard
Copied
I've more than 10 columns.
Copy link to clipboard
Copied
try to restart indesign, it remembers excelImportPreferences so you are getting error.
if you are using my code but with tmp_textframe.place(path, true) remove this line:
tmp_textframe.texts.everyItem().convertToTable('\t')
Copy link to clipboard
Copied
Ok now I've the column i want but how to itreate each cell? the properties of table are not accessible for the text frame
Copy link to clipboard
Copied
okay, now I am trying to explain ...
when you use any excelImportPreferences including .place(path, true)
tmp_textframe is already table so this line is not need :
tmp_textframe.texts.everyItem().convertToTable('\t')
but... if you don't use any excelImportPreferences
line above needs to stay
I suggest this code :
function test(path){
if(path != null){
var doc = app.documents.add()
doc.properties = {
documentPreferences:{
pageHeight:'297mm',
pageWidth:'210mm',
}
}
var tmp_textframe = doc.pages[0].textFrames.add()
tmp_textframe.properties = {
textFramePreferences:{
autoSizingReferencePoint:1953459301,
autoSizingType:1752069993,
}
}
/*
// part of your code
app.excelImportPreferences.properties = {
sheetIndex : 0,
aligmentStyle : AlignmentStyleOptions.SPREADSHEET,
rangeName :"B2:B114"
}
*/
try{
// work with part of your code
//tmp_textframe.place(path, false, app.excelImportPreferences.properties)
// work with window of import options
//tmp_textframe.place(path, true)
// file to place like text
tmp_textframe.place(path)
// only if file to place like text
tmp_textframe.texts.everyItem().convertToTable('\t')
}
catch(e){
alert(e)
doc.close(1852776480)
}
var table = tmp_textframe.tables[0].cells.length == 1 ? tmp_textframe.tables[0].cells[0].tables[0] : tmp_textframe.tables[0]
// here you may manipulate the table
table.columns.add(LocationOptions.AT_END)
//table.rows.add(LocationOptions.AT_BEGINNING)
//table.columns[1].remove()
}
}
app.doScript("test(File.openDialog('select file', '*.*'))", ScriptLanguage.JAVASCRIPT, undefined, UndoModes.ENTIRE_SCRIPT, 'test')
now I hope I helped you
Copy link to clipboard
Copied
Your were a BIG HELP!! Thanks a LOT!!! wish you all the best.
Copy link to clipboard
Copied
Hey again, i want to ask you about this line of code
var tmp_textframe = doc.pages[0].textFrames.add()
tmp_textframe.properties = {
textFramePreferences:{
autoSizingReferencePoint:1953459301,
autoSizingType:1752069993,
}
}
you specified the size of the text, but i figured a problem. which is the table has a limt and dosn't read the entire column in the excel sheet. so do you know how can i make it more flix so it can read the entire row? no worries if the table exceeds the page i'm going to delete it at the end.
and thank again!
Copy link to clipboard
Copied
I suggested this code for demonstrated placing the table in indesign, at work I usage other code (similar like this) :
function place_excel(path){
app.excelImportPreferences.properties = {tableFormatting:2018858068}
var doc = app.documents.add()
var tmp_textframe = doc.pages[0].textFrames.add()
var tmp_content = ''
try{
tmp_textframe.place(path)
tmp_content = tmp_textframe.parentStory.contents.split('\r')
}
catch(e){alert('File opened by another program\nor cannot be placed in InDesign')}
doc.close(1852776480)
return tmp_content
}
var exceltable = app.doScript("place_excel(File.openDialog('select file', '*.*'))", ScriptLanguage.JAVASCRIPT, undefined, UndoModes.ENTIRE_SCRIPT, 'place_excel')
Copy link to clipboard
Copied
I've already build my code so i dont have the brivlige to change somthing right know:( i'm sorry and thank you!
I tried to use this function but still didn't read the entire column.
var tmp_textframe = doc.pages[0].textFrames.add()
tmp_textframe.textFramePreferences.autoSizingType = AutoSizingTypeEnum.HEIGHT_AND_WIDTH_PROPORTIONALLY;
tmp_textframe.textFramePreferences.autoSizingReferencePoint = AutoSizingReferenceEnum.TOP_LEFT_POINT;
Copy link to clipboard
Copied
I am very glad for you 🙂
Try not to add textFramePreferences and see what happens
Copy link to clipboard
Copied
I've tried to remove it. In the next step when i want to save the table in array it says their is no table!
Copy link to clipboard
Copied
Also, if iwant to manuplate the table how? these lines are to place it and format the file
tmp_textframe.place(path)
tmp_textframe= tmp_textframe.texts.everyItem().convertToTable('\t')
i want to save it and manuplate the data, for instance ;
table.rows.everyItem().height = "36pt";
Copy link to clipboard
Copied
Hi salshaw749,
if you want to do a range of cells use the code in your initial post first.
Regards,
Uwe Laubender
( ACP )
Copy link to clipboard
Copied
yes but i can't process the data
Copy link to clipboard
Copied
If your text frame contains a table you could access the table with:
if( tmp_textframe.texts[0].tables.length > 0 )
{
var column1contentsArray = tmp_textframe.texts[0].tables[0].columns[0].contents;
}
else{ alert("Error: No table found in text frame.")};
From that on you could loop the contents array.
Perhaps you should show a screenshot of your page after you placed the Excel file.
Regards,
Uwe Laubender
( ACP )
Copy link to clipboard
Copied
Thanks! I maneged to exctract the data in varible! thanks again it was really helpfull!
Other thing if you don't mind.
for(var i = 1; i < column1contentsArray.length; i++){
if (column1contentsArray[i].row == column1contentsArray[i++].row){
alert(" equal")
}
else{
alert("not equal")
}
}
I want to loop throgh each cell to compare the content so based on it i'm going to do somthing. Anyhow, it alwayes go to the first condtion and alert me that all the content are equeal. but its not! do you know where is the issue?
and thanks again you were a big help for me. i hope all the best for you


-
- 1
- 2