cap program drop fmtexl
program define fmtexl, rclass
syntax using/ [, Sheet(string) /// For formatting only 1 sheet of file, default is all
				HEADFill(string) /// Header fill color, default is Gibson blue
				HEADText(string) /// Header text color, default is white
				LFill(string) /// Light row fill color, default is white
				DFill(string) /// Dark row fill color, default is light blue
				ROWcol /// Table body row alternating coloring
				DOUBlerowcol /// Alternates colors every 2 rows rather than 1
				Borders /// adds borders to all cells
				HEADRows(integer 1) ///More than 1 header row
				LABCols(integer 1) ///More than 1 label column
				SPECRange(string) ///Specify table cell range rather then detect from import excel, formatted as [A-Z]*[1-9]*:[A-Z]*[1-9]*
				BW ///Black and white journal style table, don't specify other formatting if you do so, will overwrite
				]
	global shnamelist //Clear lists from possible previous runs
	global shrangelist
	if `"`headfill'"'==`""' local headfill "020 065 092" //Set defaults for colors
	if `"`headtext'"'==`""' local headtext "white"
	if `"`lfill'"'==`""' local lfill "white"
	if `"`dfill'"'==`""' local dfill "228 238 248"
	local torest = 0
	qui count
	if `r(N)' != 0 {
	    local torest = 1
		tempfile fmtpre
		sa `fmtpre'
		clear
	}
	if `"`bw'"'!="" {
		local headfill "white" //Set defaults for colors
		local headtext "black"
		local lfill "white"
		local dfill "white"
	}
	qui import excel `"`using'"', describe //Get list of sheets and cell ranges
	******Setting up to cycle through each sheet in a excel workbook unless a sheet is specified
	*******If only one worksheet
	if `r(N_worksheet)'==1 {
		global shnamelist `"`"`r(worksheet_1)'"'"'
		if `"`specrange'"'==`""' global shrangelist `"`r(range_1)'"'
		if `"`specrange'"'!=`""' global shrangelist `"`specrange'"'
	}
	*******If multiple worksheets
	if `r(N_worksheet)'>1 {
		forvalues i=1/`r(N_worksheet)' {
			global shnamelist `"${shnamelist} `"`r(worksheet_`i')'"'"'
			if `"`specrange'"'==`""' global shrangelist `"${shrangelist} `r(range_`i')'"'
			if `"`specrange'"'!=`""' global shrangelist `"${shrangelist} `specrange'"'
		}
	}
	*******If a specfic sheet has been specified
	if `"`sheet'"'!="" {
		local shtct=0
		foreach sht of global shnamelist { //Loops over each sheet name to look for the specified sheet and get its range
			local ++shtct
			local range: word `shtct' of ${shrangelist}
			local foundsheet=0
			if `"`sht'"'==`"`sheet'"' {
				local foundsheet=1
				global shnamelist `"`"`sht'"'"'
				if `"`specrange'"'==`""' global shrangelist `"`range'"'
				if `"`specrange'"'!=`""' global shrangelist `"`specrange'"'

			}
		}
		if `foundsheet'==0 {
			di in red `"Sheet "`sheet'" not found in `using'"' // If specified sheet is not in workbook
			exit
		}
	}
	local shtct=0
	foreach sht of global shnamelist {
		local ++shtct
		local range: word `shtct' of ${shrangelist}
		putexcel set `"`using'"', modify sheet("`sht'")
		*****Getting cell ranges for formatting commands
		if regexm(`"`range'"',`"^([A-Z]+[0-9]+):([A-Z]+[0-9]+)$"') {
			local topleft=regexs(1)
			local bottomright=regexs(2)
			*di `"topleft: `topleft'"'
			*di `"bottomright: `bottomright'"'
		}
		if regexm(`"`topleft'"',`"[A-Z]+"') local leftcol=regexs(0)
		if regexm(`"`topleft'"',`"[0-9]+"') local toprow=regexs(0)
		if regexm(`"`bottomright'"',`"[A-Z]+"') local rightcol=regexs(0)
		if regexm(`"`bottomright'"',`"[0-9]+"') local bottomrow=regexs(0)
		**********Getting second row and column
		****Second row (or first non-header row
		local row2=`toprow'+`headrows'
		****Second column or first non-label column
		*******If left column is A-Z
		if length(`"`leftcol'"')==1 {
			local act=0
			foreach i in `c(ALPHA)' {
				local ++act
				if `"`i'"'==`"`leftcol'"'&`"`leftcol'"'!=`"Z"' local col2 `"`:word `=`act'+`labcols'' of `c(ALPHA)''"'
				if `"`leftcol'"'==`"Z"'&`labcols'==1 local col2 `"A`: word `=`labcols'+1' of `c(ALPHA)''"'
			}
		}
/*	REsume here.
		*!DH Note 4/7/2020: setting column widths
		import excel using `"`using'"', sheet("`sht'")
		mata:b=xl()
		mata:b.load_book("${writeto}student_list_`dname'.xlsx")
		mata:b.set_sheet(`"instructions"')
		mata:b.set_mode(`"open"')
		mata:b.set_column_width(1,1,20)
		mata:b.set_column_width(2,2,100)
		mata:b.set_row_height(6,6,70)
		mata:b.set_row_height(12,12,0)
		mata:b.close_book()
*/

		*******If left column is AA-AY
		if length(`"`leftcol'"')>1 {
			local act=0
			local letlen=`=length(`"`leftcol'"')'-1
			local firstlets=substr(`"`leftcol'"',1,`letlen')
			local lastlet=substr(`"`leftcol'"',-1,.)
			if `"`lastlet'"'!=`"Z"' {
				foreach i in `c(ALPHA)' {
					local ++act
					if `"`i'"'==`"`lastlet'"' {
						local l2 `"`:word `=`act'+`labcols'' of `c(ALPHA)''"'
						local col2 `"`firstlets'`l2'"'
					}
				}
			}
		if `"`lastlet'"'==`"Z"' {
			foreach i in `c(ALPHA)' {
				local ++act
				if `"`i'"'==`"`firstlets'"' {
					local l2 `"`:word `=`act'+1' of `c(ALPHA)''"'
					local col2 `"`l2'`:word `labcols' of `c(ALPHA)''"'
				}
			}
		}

		}
		*di `"Range: `range'"'
		*di `"row2: `row2'"'
		*di `"col2: `col2'"'
		if `headrows'==1 {
			if `"`bw'"'=="" {
				qui putexcel `leftcol'`toprow':`rightcol'`toprow', hcenter font(Calibri,11,`headtext') bold
				qui putexcel `leftcol'`toprow':`rightcol'`toprow', fpattern(solid,"`headfill'")
			}
			if `"`bw'"'!="" {
				qui putexcel `leftcol'`toprow':`rightcol'`toprow', hcenter font(Calibri,11,`headtext')
				qui putexcel `leftcol'`toprow':`rightcol'`toprow', border(bottom)
			}
		}
		if `headrows'>1 {
			if `"`bw'"'=="" {
				qui putexcel `leftcol'`toprow':`rightcol'`=`toprow'+`headrows'-1', hcenter font(Calibri,11,`headtext') bold
				qui putexcel `leftcol'`toprow':`rightcol'`=`toprow'+`headrows'-1', fpattern(solid,"`headfill'")
			}
			if `"`bw'"'!="" {
				qui putexcel `leftcol'`toprow':`rightcol'`=`toprow'+`headrows'-1', hcenter font(Calibri,11,`headtext')
				qui putexcel `leftcol'`=`toprow'+`headrows'-1':`rightcol'`=`toprow'+`headrows'-1', border(bottom)
			}
		}
		if `headrows'==1 qui putexcel `col2'`toprow':`rightcol'`toprow', txtwrap
		if `headrows'>1 qui putexcel `col2'`toprow':`rightcol'`=`toprow'+`headrows'', txtwrap
		if `labcols'==1 qui putexcel `leftcol'`toprow':`leftcol'`bottomrow', left
		if `labcols'>1 qui putexcel `leftcol'`toprow':`col2'`bottomrow', left
		if `headrows'==1 qui putexcel `col2'`toprow':`rightcol'`bottomrow', hcenter
		if `headrows'>1 qui putexcel `col2'`row2':`rightcol'`bottomrow', hcenter
		if `"`borders'"'!="" qui putexcel `topleft':`bottomright', border(all)
		if `"`rowcol'"'!=`""' {
			local lrows ""
			local drows ""
			if `"`doublerowcol'"'==`""' {
				forvalues i=`row2'/`bottomrow' {
					if  `"`=mod(`i',2)'"'=="0" local drows `"`drows' `leftcol'`i':`rightcol'`i'"'
					if  `"`=mod(`i',2)'"'!="0" local lrows `"`lrows' `leftcol'`i':`rightcol'`i'"'
				}
			}
			if `"`doublerowcol'"'!=`""' {
				global color "white"
				local cct=0
				forvalues i=`row2'/`bottomrow' {
					local ++cct
					global lcolor "${color}"
					if  `"${lcolor}"'=="${myltblue}" local drows `"`drows' `leftcol'`i':`rightcol'`i'"'
					if  `"${lcolor}"'=="white" local lrows `"`lrows' `leftcol'`i':`rightcol'`i'"'
					if `cct'==2 {
						local cct 0
						if `"${lcolor}"'=="white" global color `"${myltblue}"'
						if `"${lcolor}"'=="${myltblue}" global color `"white"'
					}
				}
			}
			qui putexcel `drows', fpattern(solid, "`dfill'")
			qui putexcel `lrows', fpattern(solid, "`lfill'")
		}
		if `"`bw'"'!=`""' qui putexcel `topleft':`bottomright', fpattern(solid, "white")
		di `"`sht' formatted (table range `range')"'
	}
	di as smcl `"CLICK TO OPEN: {browse `"`using'"'}"'
	local torest = 0
	if `torest' == 1 {
		use `fmtpre', clear
	}

end