#pragma rtGlobals=1		// Use modern global access method.


// Fuction to read the raw data from Excel Sheets and concatenate all 
// columns in the same worksheet into a 2D wave (matrix)
// It reads the wave names from the first row and uses them as dimension labels

Function ExcelTo2DWave()

// select the excel file
do
	getfilefolderinfo/Q
	if (V_Flag<0)
		return -1 				//User cacelled
	elseif (V_isFolder==1) 		//User selectes folder instead of file
		DoAlert/T="Message" 0, "Please choose a file not a folder"
	elseif (V_Flag==0 && V_isFile==1) 			// make sure a file is selected
		if (stringmatch(S_Path, "*.xls*"))		// make sure it is an Excel file
		String filePath = S_Path				// full file path
		else
		V_isFile = 0
		DoAlert/T="Message" 0, "The selected file is not an Excel file. Please select an Excel file."
		endif
	endif
while (V_isFile !=1)


XLLoadWave/Q/J=1 filePath			//use XLLoadWave/J=1 to get info (the worksheets) in the Excel file
String wkShList = S_value				// a : sepearted list of sheets in the Excel file

// each excel file loads its worksheets in a seperate data folder
// this is useful when loading multiple excel files that may contain repeated worksheet names
// comment out the lines with dataFolder if not needed
String savedDataFolder = GetDataFolder(1)		
String fileName = ParseFilePath(0,filePath,":",1,0) 		// get file name from full file path
String extension = "." + ParseFilePath(4, fileName, ":", 0, 0)		// e.g., ".xls"
String dfName = RemoveEnding(fileName, extension)
NewDataFolder/O/S :$dfName

// loop for each worksheet in the excel file to load all columns in to a 2D wave
Variable i, numWkSh
i=0
numWkSh = ItemsInList(wkShList)     // the number of worksheets
String wkShLoad
do 		
wkShLoad =StringFromList(i,wkShList) 		//pick a worksheet to load wave from
LoadTo2Dwave(filePath, wkShLoad)			//pass the full file path and worksheet name to load waves
i += 1
while (i<numWkSh)
SetDataFolder savedDataFolder
End

// the function loads the columns in to 1D wave and then
//uses concatenate to merge them into a 2D wave

Function LoadTo2DWave(fName, wkShLoad)
String fName, wkShLoad

//Loads each column in "wkShLoad" to a 1D Wave
// dtermine wave type from row 10 - change as appropriate
// takes row 1 as wave names - change as appropriate
XLLoadWave/Q/S=wkShLoad/C=10/W=1/O fName 	

//create a list of wave to concatenate exclude 2D waves and Text wave
String conList = WaveList("*",";","DIMS:1,Text:0")
// get row and columns
Variable rows =numpnts($StringFromList(1,conList))
Variable cols = ItemsInlist(conList)
Make/O/N=(rows, cols) wname

//concatenate into wanme and kill source waves
Concatenate/O/KILL/DL conList, wname

// rename 2D wave as the worksheet name
Rename wname $wkShLoad

//kill remaining waves (text waves)
String kList = WaveList("*",";","Text:1")
Variable j
do 
String kWave =StringFromList(j,kList)
KillWaves $kWave
j += 1
while (j<ItemsInList(kList))

End