topic Re: How Can I get a list of sheets in an Excel workbook ? in Discussions

How Can I get a list of sheets in an Excel workbook ?

tsl — Thu, 23 Feb 2012 21:57:31 GMT

I have a situation where I am opening Excel sheets using Open Database().

This has been a good way to do it since I want specific columns from specific sheets, so using an SQL SELECT statement is ideal.

I am now discovering there is no standardization on the sheet names. So what I want to do is query the Excel workbook and get a list of sheets in it. I can then use some logic in my code to decide which ones I actually want ( because while there is no standardization, there are enough "similarities" that I could figure out what I want, by checking does the sheet name contain StringA or StringB or StringC for example )

I have no idea how this can be done in JSL. Searching onling, there are plenty of code examples using VBScripting etc which will return the sheets in an Excel workbook, but that is not much help.

I tried also to find some SQL statement that would do it, but I don't know of a table name that would list the "tables" / sheets in the workbook.

I know JMP can do it because if I use File -> DataBase -> Open Table and connect to an Excel data source it will list all the sheets available in the Schemas - Tables column. Likewise if I set a user preference to select individual Excel Worksheets and use the Open() command it will ask me to select which sheets I want to open.

So my question is does anybody know if JMP exposes that capability to figure out the Excel sheet names to the scripting language ?

many thanks

Thomas

Re: How Can I get a list of sheets in an Excel workbook ?

mpb — Fri, 24 Feb 2012 20:46:31 GMT

Here's a very clunky way which may not even be feasible if the tables are very large. Just a fragment ...

1. Set preferences to open all Excel sheets on file>open.

2. countBefore = N Table();

3. Open the Excel file using open()

4. countAfter = N Table();

5. nSheets = countAfter - countBefore

5. dtname = {};

5. for(i=1, i <= nSheets, i++, dtname = Data Table(i) << get name);

Then dtname is a list of Sheet names you can play with and you can close the tables you don't want.

Message was edited by: mpb
Corrected indexing in the code fragment.

How Can I get a list of sheets in an Excel workbook ?

mpb — Fri, 02 Mar 2012 00:50:08 GMT

Note that the code fragment above was corrected.

Re: How Can I get a list of sheets in an Excel workbook ?

Anonymous — Thu, 07 Mar 2013 16:22:02 GMT

The last step may get you messed up if you have data tables open before you open the excel worksheets. Quick correction is

for(i=1, i <= nSheets, i++, dtname = Data Table(i+countBefore) << get name);

Re: How Can I get a list of sheets in an Excel workbook ?

MathStatChem — Thu, 19 Dec 2024 17:27:55 GMT

This is an old thread, but found it useful. Here is a script that will do this by opening the contents of the XLSX file directly. XLSX files are simply ZIP files with a collection of XML files that define the properties, structure, and elements of the workbook.

Names Default To Here( 1 );

filename = pick file("Choose Excel File", "$DOCUMENTS", {"Excel Files|xlsx"} );

zipxlsx = Open( filename, Zip );

zip_file = zipxlsx << read( "xl/workbook.xml", Format( blob ) );

// Open the file
zz = Open(
	zip_file,
	invisible,
	xml,
	XML Settings(
		Stack( 0 ),
		Row( "/workbook/sheets/sheet" ),
		Col(
			"/workbook/sheets/sheet/@name",
			Column Name( "name" ),
			Fill( "Use Once" ),
			Type( "Character" ),
			Format( {"Best"} ),
			Modeling Type( "Nominal" )
		)
	)
);

worksheets=(zz:name << get values);      

close(zz, nosave);

write(worksheets);

Re: How Can I get a list of sheets in an Excel workbook ?

mmarchandFSLR — Thu, 19 Dec 2024 21:44:45 GMT

Re: How Can I get a list of sheets in an Excel workbook ?

MathStatChem — Mon, 23 Dec 2024 18:32:57 GMT

Seriously? How did I not know about this. Thanks, though, very helpful!

Re: How Can I get a list of sheets in an Excel workbook ?

txnelson — Tue, 24 Dec 2024 03:07:27 GMT

How did you not know about that.....because you are human. No one can know everything in JMP and then every 18 months or so, a plethora of new features and functions come out in a new release. Personally, I learn so much from you and your contributions on sooooooo many things in JMP that I do not know, or no longer remember.