*!  excel2latex 1.0   15April2018 by gc_zhao@foxmail.com

prog define excel2latex,rclass

   version 13

   syntax, [saving(string)   ///
     using(string)         ///
     tabpos(string)         ///
     CAPtion(string)        ///
     LABel(string)          ///
     FONTSize(string)       ///
     makebox(numlist max=1) ///
     Landscape              ///
     as(string)             ///
	 BASELine(real 1)       ///
	 ARRAYstretch(real 1.2) ///
	 hline(numlist >0 integer sort) ///
	 DROPRow(numlist>0 integer sort) ///
	 TITLECell(numlist max=3 min=2 integer) ///
	 MULTIColumn(string)    ///
	 COLAlignment(string)   ///
	 noNote                 ///
	 NOTECell(string)       ///
	 NOTEScontent(string)   ///
	 NOTEFONTSize(string)   ///
	 DISPlay ]

****** import the table from a file *****
   if "`using'"!="" {
      tokenize "`using'", parse(",")
      local usename="`1'"
      local uopt="`3'"
      if "`uopt'"~="excel" & "`uopt'"~="txt" & "`uopt'"~="csv" & "`uopt'"~="dta" {
	       dis as error "error: excel2latex does not support .`uopt' files."
	       exit
      }
      if "`uopt'"=="excel" {
         tokenize "`usename'",parse(":")
         local tablename=trim("`1'")
	       if substr("`usename'",-4,4)~=".xls" | substr("`usename'",-5,5)~=".xlsx" {
		      import excel "`tablename'",sheet("`3'") clear
		   }
		   else {
		   cap import excel "`tablename'.xls",sheet("`3'") clear
		   if _rc~=0 cap import excel "`tablename'.xlsx",sheet("`3'") clear
		   }
      }
      else if "`uopt'"=="txt" {
      	 local usename=trim("`usename'")
         if substr("`usename'",-4,4)~=".txt" qui insheet using "`usename'.txt",clear nonames t
      	 else qui insheet using "`usename'",clear nonames t
      }
      else if "`uopt'"=="csv" {
      	 local usename=trim("`usename'")
      	 if substr("`usename'",-4,4)~=".csv" qui insheet using "`usename'.csv",clear nonames c
      	 else qui insheet using "`usename'",clear nonames c
      }
      else if "`uopt'"=="dta" {
         local usename=trim("`usename'")
         use "`usename'",clear
      }
      local v=1
	    foreach i of varlist _all {
	       capture confirm string variable `i'
		     qui if _rc!=0 tostring `i',replace
		     qui replace `i'=trim(`i')
	       cap assert `i'=="." | `i'==""
		     if _rc==0 drop `i'
		     else qui rename `i' var`v'
		     local v=`v'+1
	    }

   }
   else {
      qui des _all
      local tmpk=r(k)
      local tmpn=r(N)
      if `tmpk'==0 | `tmpn'==0 {
         dis as error "error: no table to convert!"
         exit
      }
      else{
      	 local v=1
      	 foreach i of varlist _all {
      	 	  capture confirm string variable `i'
		        qui if _rc!=0 tostring `i',replace
		        qui replace `i'=trim(`i')
	          cap assert `i'=="." | `i'==""
		        if _rc==0 drop `i'
		        else qui rename `i' var`v'
		        local v=`v'+1
      	 }
      }
   }

   preserve

***** hanld the signs ans marks *****
   foreach i of varlist _all {
      qui replace `i'=subinstr(`i',"_","\_",.)
      qui replace `i'=subinstr(`i',"#","\#",.)
      qui replace `i'=subinstr(`i',"$","\$",.)
      qui replace `i'=subinstr(`i',"<","$<$",.)
      qui replace `i'=subinstr(`i',">","$>$",.)
   }

***** title of the table *****
   if "`titlecell'"!="" {
      local j=1
	    local tr=word("`titlecell'",1)
      local tc=word("`titlecell'",2)
	    local tp=word("`titlecell'",3)
      local title=var`tc' in `tr'
	    if "`tp'"~="" local title=substr("`title'",`tp',.)
   }

***** notes of the table *****
   if "`notecell'"!="" {
      tokenize "`notecell'",parse(",")
	    local j=1
	    while "`1'"!="" {
	       local nr`j'=word("`1'",1)
		     local nc`j'=word("`1'",2)
		     if `nr`j''>_N {
		        dis as error "Warning: The row number of the Note is larger than the number of rows of the table. Check the option notecell()"
		        exit
		     }
		     local nt`j'=var`nc`j'' in `nr`j''
		     macro shift 2
		     local j=`j'+1
	    }
	    local numnotes=`j'-1
	    forvalues i=1/`numnotes' {
	       if `i'==1 local NR="`nr`i''"
		     else local NR="`NR' `nr`i''"
	    }
   }

  local N=_N
	gen tmpobs1=_n

***** drop some rows *****
	if "`droprow'"=="" local droprow=.
	if "`titlecell'"=="" local tr=.
	if "`notecell'"=="" local NR=.

	if "`titlecell'"!="" | "`droprow'"!="" | "`notecell'"!="" {
	   qui mata: dropr("`droprow'","`tr'","`NR'")
	   mat dropr=r(dropr)
	   local rowdropr=rowsof(dropr)
	   forval i=1/`rowdropr' {
		    qui drop if tmpobs1==dropr[`i',1]
	   }
	}

	gen tmpobs2=_n
***** hanld the multicolumn cells ******
  if "`multicolumn'"!="" {
     mata: mct=multic("`multicolumn'")
	   mat mc=r(mc)
	   mat colname mc=row col length

	   local mcn=rowsof(mc)
	   forva i=1/`mcn' {
	      local mcr=mc[`i',1]
	      local mcc=mc[`i',2]
	      qui replace var`mcc'=" " if tmpobs2==`mcr'
	   }
	}
	else {
	   mat mc=`N'*10,.,.
	}
	qui compress

***** add "&" into the table *****
	cap drop tmp?
	local j=1
	foreach i of varlist var* {
	   qui {
	   gen tmp1=length(`i')
	   sum tmp1
	   local maxlength=r(max)
	   gen tmp2=" "*(`maxlength'-tmp1)
	   replace `i'=`i'+tmp2
	   if "`j'"~="1" replace `i'="&"+`i'
	   drop tmp1 tmp2
	   }
	   local j=`j'+1
	   global lvar "`i'"
	}
	qui replace ${lvar}=${lvar}+" \\"

****** write the table in a text file *****

   if "`as'"=="" local as="tex"

   if "`caption'"=="" & "`titlecell'"=="" & "`label'"~="" {
      dis as error "error: label() is only be used when caption() or titlecell() is specified!"
	  exit
   }

   qui des var*,varlist
   local varlist=r(varlist)
   local K=wordcount("`varlist'")
   local N=r(N)

   if "`saving'"=="" & "`display'"=="" {
   	 dis as error "error: at least one of saving() or display should be specified!"
   	 exit
   }

   cap file close tabname
   if "`saving'"~="" {
      tokenize "`saving'",parse(",")
      local savname=rtrim("`1'")
      local sopt="`3'"
      local nspot: word count "`sopt'"
      if `nspot'>1 | ("`sopt'"~="replace" & "`sopt'"~="append") {
         dis as error `"error: only one of "replace" and "append" should be specified"'
	     exit
      }
	  nois file open tabname using "`savname'.`as'",`sopt' write text
	 }
	 else {
	    tempfile temptable
	    local savname="`temptable'"
	    nois file open tabname using "`savname'.`as'",replace write text
	 }

******** head of the table ********
// table position
if "`tabpos'"=="" local tabpos="h"

// font size
if "`fontsize'"=="" local fontsize="\small"
else local fontsize="\"+"`fontsize'"

if "`notefontsize'"=="" local notefontsize="`fontsize'"
else local notefontsize="\"+"`notefontsize'"

// column alignment
if "`colalignment'"=="" {
   local K2=`K'-2
   local c="c"
   forva i=1/`K2'{
      local c="`c'"+"c"
   }
   local colalignment="l"+"`c'"
}
else {
   tokenize "`colalignment'",parse(",")
   if "`3'"~="force" {
      local colanovl=subinstr("`colalignment'","|","",.)
      if length("`colanovl'")<`K' {
         dis as error "At least the alignment for one column is not specified by the option colalignment()"
	     exit
      }
      else if length("`colanovl'")>`K' {
         dis as error "The alignment of additional column(s) is/are specified by the option colalignment()"
	     exit
      }
   }
   else local colalignment="`1'"
}


//if "`saving'"~="" {
	if "`savname'"~="" {
  file write tabname "%%%%%%%%%%%%Table: `caption' (beginning)%%%%%%%%%%%%%" _n
	if "`landscape'"~="" file write tabname "\begin{landscape}" _n
	file write tabname "\renewcommand{\arraystretch}{`arraystretch'}" _n
	file write tabname "\begin{table}[`tabpos']" _n
	if "`landscape'"~="" file write tabname "\centering" _n
	if "`makebox'"~="" file write tabname "\noindent \makebox[\textwidth][c]{\begin{minipage}[c]{`makebox'\textwidth}" _n
	if "`caption'"~="" file write tabname "\caption{`caption'}" _n
	if "`caption'"=="" & "`titlecell'"~="" file write tabname "\caption{`title'}" _n
	if "`label'"~="" file write tabname "\label{`label'}" _n
	if "`makebox'"~="" file write tabname "\end{minipage}}" _n
	file write tabname "{\renewcommand\baselinestretch{`baseline'}\selectfont" _n
	file write tabname "{`fontsize'" _n
	if "`makebox'"=="" {
	   file write tabname "\begin{tabularx}{\textwidth}{@{\extracolsep{\fill}} `colalignment'}" _skip(1)
	}
	else {
	   file write tabname "\noindent\makebox[\textwidth][c]{" _n
	   file write tabname "\begin{tabularx}{`makebox'\textwidth}{@{\extracolsep{\fill}} `colalignment'}" _skip(1)
	}
	file write tabname "\hline \hline" _n

	// content of the table
  mata: hliner("`hline'")
	mat hlvec=r(hliner)

  local x=1
	local hliner=hlvec[`x',1]

  local m=1
	local mcr=mc[1,1]
	local mcc=mc[1,2]
	local mcl=mc[1,3]
	local rowmc=rowsof(mc)

	** i: row; j: column
	forva i=1/`N' {
	   local j=1
	   while `j'<=`K' {
	      local tmp=var`j' in `i'
	      if `i'~=`mcr' {
		       if `j'<`K' file write tabname "`tmp'" _skip(1)
	            else {
	            if "`i'"=="`hliner'" {
			           file write tabname "`tmp' \hline" _n
				         local x=`x'+1
				         local hliner=hlvec[`x',1]
		          }
			     else file write tabname "`tmp'" _n
		       }
		       local j=`j'+1
		    }
		    else {
		       if `j'<`K' & `j'<`mcc' {
			     file write tabname "`tmp'" _skip(1)
				   local j=`j'+1
			     }
			     else if `j'<`K' & `j'==`mcc' & `mcc'+`mcl'-1<=`K'  {
				      mata: mcalign=mct[strtoreal(st_local("m")),1]
				      mata: mctext=mct[strtoreal(st_local("m")),2]
				      mata: st_strscalar("mctext",mctext)
				      mata: st_strscalar("mcalign",mcalign)
				      local mctext=mctext
				      local mcalign=mcalign
			        if (`mcc'+`mcl'-1<`K') {
				         if (`j'==1) file write tabname "\multicolumn{`mcl'}{@{\extracolsep{\fill}} `mcalign'}{`mctext'}" _skip(1)
				         else file write tabname "&\multicolumn{`mcl'}{@{\extracolsep{\fill}} `mcalign'}{`mctext'}" _skip(1)
				         local j=`j'+`mcl'
				      }
				      else {
				         if (`j'==1) {
				            if ("`i'"=="`hliner'") {
					             file write tabname "\multicolumn{`mcl'}{@{\extracolsep{\fill}} `mcalign'}{`mctext'} \\ \hline" _n
						           local x=`x'+1
				               local hliner=hlvec[`x',1]
					          }
					          else file write tabname "\multicolumn{`mcl'}{@{\extracolsep{\fill}} `mcalign'}{`mctext'} \\" _n
				         }
				         else {
				            if ("`i'"=="`hliner'") {
					             file write tabname "&\multicolumn{`mcl'}{@{\extracolsep{\fill}} `mcalign'}{`mctext'}  \\ \hline" _n
						           local x=`x'+1
						           local hliner=hlvec[`x',1]
				            }
					          else file write tabname "&\multicolumn{`mcl'}{@{\extracolsep{\fill}} `mcalign'}{`mctext'}  \\" _n
				         }
				         local j=`K'+1
				      }
				      if `m'<`rowmc' local m=`m'+1
				      local mcr=mc[`m',1]
	            local mcc=mc[`m',2]
	            local mcl=mc[`m',3]
		       }
			     else {
				      if `j'==`K' & `i'==`hliner' {
				      	 file write tabname "`tmp' \hline" _n
				      	 local x=`x'+1
				      	 local hliner=hlvec[`x',1]
				      }
				      else if `j'==`K' & `i'~=`hliner' file write tabname "`tmp'" _n
				      local j=`j'+1
			     }
		    }
	   }
	}

	// end of the table

	file write tabname "\hline \hline" _n
	file write tabname "\end{tabularx}" _skip(1)
	if "`makebox'"~="" file write tabname "}" _skip(1)
	file write tabname "}" _n
	file write tabname "\par}" _n

	if "`note'"~="nonote" {
	   file write tabname "{\renewcommand\baselinestretch{`baseline'}\selectfont" _n
	   file write tabname "{`notefontsize' " _n
	   if "`makebox'"~="" file write tabname "\noindent \makebox[\textwidth][c]{\begin{minipage}[c]{`makebox'\textwidth}" _n
	   if "`notecell'"=="" & "`notescontent'"=="" file write tabname "Notes: ...." _n
	   else if "`notescontent'"~="" file write tabname "Notes: `notescontent'" _n
	   else if "`notescontent'"=="" & "`notecell'"~="" {
	      forvalues i=1/`numnotes' {
		       if `i'==1 & `numnotes'>1 file write tabname "`nt`i'' \\" _n
			     else file write tabname "`nt`i'' " _n
		    }
	   }
	   if "`makebox'"~="" file write tabname "\end{minipage}}" _n
	   file write tabname "}" _n
	   file write tabname "\par }" _n
	}
	file write tabname "\end{table}" _n
	if "`landscape'"~="" file write tabname "\end{landscape}" _n
	file write tabname "%%%%%%%%%%%%Table: `caption'(end)%%%%%%%%%%%%%" _n(4)
	file close tabname
}

if "`display'"=="display" {
   file open latextable using "`savname'.`as'",read
   file read latextable line
   while r(eof)==0 {
   	  dis as result "`line'"
   	  file read latextable line
   }
   file close latextable
}

restore

end

********************************************************************************
cap mata: mata drop multic()
mata:
string matrix multic(string scalar multicolumn)
{
   string matrix table
   real matrix obs

   table=st_sdata(.,.)
   table=st_sdata(.,1..(cols(table)-2))
   obs=st_data(.,("tmpobs1 tmpobs2"))

   tokenmultic=tokens(multicolumn,",")
   tokenmultic=select(tokenmultic,tokenmultic[1,.]:~=",")

   for (i=1; i<=cols(tokenmultic); i++) {
	  mc=tokens(tokenmultic[1,i])
	  if (i==1) {
		 mcr=select(obs[.,2],obs[.,1]:==strtoreal(mc[1]))
		 mcc=strtoreal(mc[2])
		 mcl=strtoreal(mc[3])
		 mca=mc[4]
		 mct=table[mcr,mcc]
	  }
	  else {
		 mcr=mcr\select(obs[.,2],obs[.,1]:==strtoreal(mc[1]))
		 mcc=mcc\strtoreal(mc[2])
		 mcl=mcl\strtoreal(mc[3])
		 mca=mca\mc[4]
		 mct=mct\table[select(obs[.,2],obs[.,1]:==strtoreal(mc[1])),strtoreal(mc[2])]
	  }
   }
   mc=mcr,mcc,mcl
   mct=mca,mct
   st_matrix("r(mc)",mc)
   return(mct)
}
end

*******************************************************************************

cap mata: mata drop hliner()
mata:
void hliner(string scalar hline)
{
   real matrix obs

   obs=st_data(.,("tmpobs1 tmpobs2"))

   tokenhline=tokens(hline)

   for (i=1; i<=cols(tokenhline); i++) {
	  if (i==1) {
		 hliner=select(obs[.,2],obs[.,1]:==strtoreal(tokenhline[i]))
	  }
	  else {
		 hliner=hliner\select(obs[.,2],obs[.,1]:==strtoreal(tokenhline[i]))
	  }
   }
   st_matrix("r(hliner)",hliner)
}
end


********************************************************************************

cap mata: mata drop dropr()
mata:

void dropr(string scalar droprow,string scalar tr,string scalar NR)

{
   droprow=tokens(droprow)
   droprow=strtoreal(droprow)
   tr=strtoreal(tr)
   NR=tokens(NR)
   NR=strtoreal(NR)

   if (anyof(droprow,tr)==0) {
      droprow=droprow,tr
   }
   i=1
   do {
      nr=NR[1,i]
      if (anyof(droprow,nr)==0) {
	     droprow=droprow,nr
	  }
	  i=i+1
   } while (i<=cols(NR))

   droprow=sort(droprow',1)
   st_matrix("r(dropr)",droprow)
}

end