nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Import multiple worksheets

medini_1060108009

Hi,

I would like to create a script that allows the user to select and import multiple Excel worksheets from a workbook at once. I have followed the guidance from the post "Easy import from Excel" which uses the library excel.js and as stated in the post the importer is limited to a single worksheet. I think it can only select one sheet because the excel importer calls the selectOption( ) function, which is defined in UI.js. This function doesn't allow for multiple selections e.g. it doesn't have an "ok" button once you have finished with your selections. Any suggestions for going about with this? Are there any example scripts for importing multiple worksheets?

Thanks!

Find more posts tagged with

medini

medini legacy

Accepted answers

medini_1023909254

Hi Maisy Lu,

In general a script can also process more than one sheet in an Excel file. The way may be slightly different whether you are implementing it in a call-back style (as explained int the article you are referencing) or just directly accessing the data in the Excel file. In fact you can get the list of sheets and read data from them in arbitrary order. See below a small snippet on this

// Select and Open the Excel file with FTA data
 var file = openFile(["*.xlsx","*.xls"]);
 var document =undefined;
 if(file)
 document= ExcelDocument.open(file, true);
 else
 {
 alert("No input file selected");
 return; 
 }
// get all worksheets (to be accessed by name)
 var sheets = document.workbook.worksheets;
 var sheetsByName = {};
 sheets.forEach(function (s) {
// use lower case sheet names
 sheetsByName[s.name.toLowerCase()] = s;
 });

// And now handle the data in the different sheets
 ws = sheetsByName["sheet 1"];
// Get the rows of the sheet
 rows = ws.getRows();
// read the conetent from cell (A1)
 var content = getTextInCell(rows[1],1);
//now take the next sheet
 ws = sheetsByName["sheet 2"];
// Get the rows of the sheet
 rows = ws.getRows();
// ...

Eckhardt

All comments

medini_1023909254

Hi Maisy Lu,

// Select and Open the Excel file with FTA data
 var file = openFile(["*.xlsx","*.xls"]);
 var document =undefined;
 if(file)
 document= ExcelDocument.open(file, true);
 else
 {
 alert("No input file selected");
 return; 
 }
// get all worksheets (to be accessed by name)
 var sheets = document.workbook.worksheets;
 var sheetsByName = {};
 sheets.forEach(function (s) {
// use lower case sheet names
 sheetsByName[s.name.toLowerCase()] = s;
 });

// And now handle the data in the different sheets
 ws = sheetsByName["sheet 1"];
// Get the rows of the sheet
 rows = ws.getRows();
// read the conetent from cell (A1)
 var content = getTextInCell(rows[1],1);
//now take the next sheet
 ws = sheetsByName["sheet 2"];
// Get the rows of the sheet
 rows = ws.getRows();
// ...

Eckhardt

medini_1060108009

Hi Eckhardt,

Thanks for your reply.

I am implementing it in the callback style as it integrates with the user interface. I would also like to know whether it is possible to take multiple selections from the dialogue box that appears with the function selectOption( ) ? (selectOption is used in Excel.js and it's defined in ui.js) It seems to only allow for one selection.

Thanks!

medini_1024169437

Hi Hi Maisy Lu,

the last parameter for selectOption is an array of options, so yes, you can have multiple options here.

For example:

var selection = selectOption("Make Your Choice", "Do you want to continue?", [ "Yes", "No, later please"]);

Jan

medini_1060108009

Hi Jan,

Apologies for the confusion. What I mean is to be able to select multiple in one go and not just have multiple choices of buttons. For the example below, I would like to have var selection returning an array of sheets that are selected.

var selection = selectOption("Make Your Choice", "Select the worksheet(s) you would like to import", [ "Sheet1", "Sheet2", "Sheet3"]);

Thanks!

medini_1024169437

Ah, this is currently not possible. Maybe you can make the decision *inside* Excel, basically derive the decision from changes there,

for example user has to mark sheets hidden that shall be ignored, or give them a prefix.

Jan

medini_1023909254

Hi Maisy,

actually also the call-back style is implemented to process only one sheet of the Excel file. So probably you would have to try it rather in th approach as mentioned by me before. Moreover - as Jan mentioned there is no multiple-choice selection within a dialog is available, you may also ask for each sheet individually whther or not it should be processed:

// ...
// get all worksheets (to be accessed by name)
 var sheets = document.workbook.worksheets;
 var sheetsByName = {};
 sheets.forEach(function (s) {
// use lower case sheet names
 if(selectOption("Sheet Selection", "Include sheet " + s.name +"?", ["NO", "YES"])
 sheetsByName[s.name.toLowerCase()] = s;
 });
//sheetsByName includes now all sheets which shall be processed
// ...

Eckhardt

medini_1060108009

Hi,

I have adapted the workflow so that reading all the worksheet in Excel is ok. Thanks for the help!