*! version 1.2.4 20261103 github.com/mdroste/stata-ctools program define cexport, rclass version 14.1 * Check observation limit (Stata plugin API limitation) if _N > 2147483647 { di as error "ctools does not support datasets exceeding 2^31 (2.147 billion) observations" di as error "This is a limitation of Stata's plugin API" exit 920 } * Parse the subcommand gettoken subcmd 0 : 0, parse(" ,") if inlist("`subcmd'", "delim", "delimi", "delimit", "delimite") local subcmd "delimited" if "`subcmd'" == "dbas" local subcmd "dbase" if inlist("`subcmd'", "exc", "exce") local subcmd "excel" if "`subcmd'" == "sasxport" & _caller()<16 local subcmd "sasxport5" if "`subcmd'" == "shp" { _cio_shp_export `0' return add exit } if inlist("`subcmd'", "sas", "spss", "sasxport5", "sasxport8", "dbase") { _cio_export `subcmd' `0' return add exit } if "`subcmd'" == "excel" { * Dispatch to Excel export handler cexport_excel `0' return add exit } else if "`subcmd'" != "delimited" { di as error "cexport: unknown subcommand `subcmd'" di as error "Supported file formats: delimited, excel, spss, sasxport5, sasxport8, dbase, shp, sas" exit 198 } * Check that data is loaded (match export delimited rc=102) if c(k) == 0 { di as error "no variables defined" exit 102 } * Parse the rest with export delimited-style syntax * Support both "using filename" and just "filename" (like export delimited does) syntax [anything] [using/] [if] [in], [Delimiter(string) NOVARNames QUOTE /// NOQUOTEif REPLACE DATAfmt DATEString(string) NOLabel Verbose /// MMAP NOFSYNC DIRECT PREFAULT CRLF NOPARALLEL THReads(integer 0)] * The if is evaluated now, as export delimited does, before any helper * replaces the r() or e() results it may name. local iftouse "" if `"`if'"' != "" { tempvar iftouse mark `iftouse' `if' `in' local if "if `iftouse'" } * Handle filename - can come from using/ or as last positional argument if `"`using'"' == "" & `"`anything'"' != "" { gettoken using extra : anything if trim(`"`extra'"') != "" { di as error "specify using before the filename when selecting variables" exit 198 } local anything "" } if `"`using'"' == "" { di as error "cexport delimited: filename required" di as error "Syntax: cexport delimited [varlist] [using] filename [, options]" exit 198 } mata: st_local("__basename", pathbasename(st_local("using"))) if !strpos(`"`__basename'"', ".") local using `"`using'.csv"' * Resolve varlist from anything, leaving out the sample marker. if strtrim(`"`anything'"') != "" { unab varlist : `anything' } else { qui ds local varlist `r(varlist)' } local varlist : list varlist - iftouse * Validate file if "`replace'" == "" { capture confirm file `"`using'"' if !_rc { di as error "file `using' already exists" di as error "use the replace option to overwrite" exit 602 } } * Set default delimiter if `"`delimiter'"' == "" { local delimiter "," } * Handle tab delimiter - pass "tab" keyword to plugin (not actual char) local plugin_delim `"`delimiter'"' if `"`delimiter'"' == "tab" | `"`delimiter'"' == "\t" { local plugin_delim "tab" local delim_display "tab" } else { local delim_display `"`delimiter'"' * Validate delimiter (tab is handled specially) if length(`"`delimiter'"') != 1 { di as error "cexport: delimiter must be a single character" exit 198 } * A byte token survives whitespace and quote parsing in the dispatcher. mata: st_local("plugin_delim", "byte="+strofreal(ascii(st_local("delimiter")))) } * Handle value labels: decode labeled variables unless nolabel specified local export_varlist "" local decoded_vars "" local original_names "" * Preformatted string columns, which the writer never quotes local formatted_vars "" local ndec 0 if "`nolabel'" == "" { * Default: export value labels foreach var of local varlist { * Check if variable has a value label attached local vallbl : value label `var' if "`vallbl'" != "" { * Has value label - decode to temporary string variable. The * tempvar is named by a counter, so any variable name fits; the * replace statements below widen the decoded str# as needed. local ++ndec tempvar dec`ndec' local dname `dec`ndec'' qui decode `var', gen(`dname') local labfmt : format `var' if !(substr("`labfmt'",1,2) == "%t" | substr("`labfmt'",1,3) == "%-t") local labfmt "%18.0g" * Under quote, native export quotes only the label text; a value * without a label is still written as an unquoted number (and * system missing as an empty unquoted field). Quote the label * cells here and mark the column preformatted so the writer * leaves the numeric fallbacks bare. if "`quote'" != "" { qui replace `dname' = char(34) + subinstr(`dname', char(34), char(34)+char(34), .) + char(34) if `dname'!="" local formatted_vars `formatted_vars' `dname' } qui replace `dname' = strtrim(string(`var',"`labfmt'")) if `dname'=="" & `var' != . local export_varlist `export_varlist' `dname' local decoded_vars `decoded_vars' `dname' local original_names `original_names' `var' } else { local export_varlist `export_varlist' `var' } } } else { * nolabel specified: export raw numeric values local export_varlist `varlist' } * Prepare display-format fields; the C writer still owns CSV encoding and I/O. * Native export writes date/time variables (%t and %-t) with their display * format by default; datafmt extends that to every numeric variable. local new_export_varlist "" local export_idx = 0 foreach var of local export_varlist { local ++export_idx local origvar : word `export_idx' of `varlist' local fmt : format `origvar' local is_time = substr("`fmt'",1,2) == "%t" | substr("`fmt'",1,3) == "%-t" capture confirm numeric variable `var' if !_rc & ("`datafmt'" != "" | `is_time') { if `"`datestring'"' != "" & `is_time' local fmt `"`datestring'"' * As export delimited does, a decimal-comma format is written with * a decimal point (%9,2fc as %9.2fc) and only leading blanks * removed; any other comma format loses its thousands separators * and zero padding (%012.0gc as %12.0g), and a left-justified one * is not zero padded (%-08.2f). local trim strtrim if !`is_time' { if strpos("`fmt'", ",") { local fmt = subinstr("`fmt'", ",", ".", 1) local trim strltrim } else { if substr("`fmt'", 1, 3) == "%-0" local fmt = "%-" + substr("`fmt'", 4, .) if substr("`fmt'", -1, 1) == "c" { local fmt = substr("`fmt'", 1, length("`fmt'") - 1) if substr("`fmt'", 1, 2) == "%0" local fmt = "%" + substr("`fmt'", 3, .) } } } tempvar formatted quietly gen `formatted' = cond(`var' == ., "", `trim'(string(`var', `"`fmt'"'))) local new_export_varlist `new_export_varlist' `formatted' local formatted_vars `formatted_vars' `formatted' } else local new_export_varlist `new_export_varlist' `var' } local export_varlist `new_export_varlist' * Count variables and observations local nvars : word count `varlist' qui count `if' `in' local nobs = r(N) if `nvars' == 0 { di as error "cexport: no variables to export" exit 198 } * Load the platform-appropriate ctools plugin if not already loaded _ctools_load * Stata scopes plugin registrations to the calling ado program. capture program ctools_plugin, plugin using("`__ctools_plugin'") if _rc != 0 & _rc != 110 exit 601 capture confirm number 0 * Build plugin arguments local opt_noheader = cond("`novarnames'" != "", "noheader", "") local opt_quote = cond("`quote'" != "", "quote", "") local opt_noquoteif = cond("`noquoteif'" != "", "noquoteif", "") local opt_verbose = cond("`verbose'" != "", "verbose", "") * Performance options local opt_mmap = cond("`mmap'" != "", "mmap", "") local opt_nofsync = cond("`nofsync'" != "", "nofsync", "") local opt_direct = cond("`direct'" != "", "direct", "") local opt_prefault = cond("`prefault'" != "", "prefault", "") local opt_crlf = cond("`crlf'" != "", "crlf", "") local opt_noparallel = cond("`noparallel'" != "", "noparallel", "") * Build threads option string local threads_code "" if `threads' > 0 { local threads_code "threads(`threads')" } * Pass variable storage types to the plugin * Types: 1=byte, 2=int, 3=long, 4=float, 5=double, 0=string local vartypes "" foreach var of local export_varlist { local vtype : type `var' if `: list var in formatted_vars' { local vartypes `vartypes' 6 } else if "`vtype'" == "byte" { local vartypes `vartypes' 1 } else if "`vtype'" == "int" { local vartypes `vartypes' 2 } else if "`vtype'" == "long" { local vartypes `vartypes' 3 } else if "`vtype'" == "float" { local vartypes `vartypes' 4 } else if "`vtype'" == "double" { local vartypes `vartypes' 5 } else { * String type (str#, strL) local vartypes `vartypes' 0 } } * Each metadata field has an independent local; no aggregate buffer limit. local metadata_count : word count `varlist' forvalues metadata_i=1/`metadata_count' { local __cexport_name_`metadata_i' : word `metadata_i' of `varlist' if "`firstrow'" == "varlabels" { local header_var : word `metadata_i' of `varlist' * Labels may come from someone else's file: Mata only mata: st_local("__cexport_name_`metadata_i'", st_varlabel("`header_var'") != "" ? st_varlabel("`header_var'") : st_local("__cexport_name_`metadata_i'")) } local __cexport_type_`metadata_i' : word `metadata_i' of `vartypes' local __cexport_date_`metadata_i' : word `metadata_i' of `date_cols' if "`__cexport_date_`metadata_i''" == "" local __cexport_date_`metadata_i' 0 } local __cexport_filename `"`using'"' local __cexport_filename_len = strlen(`"`__cexport_filename'"') local __cexport_sheet `"`sheet'"' local __cexport_sheet_len = strlen(`"`__cexport_sheet'"') local __cexport_missing `"`missing'"' local __cexport_missing_len = strlen(`"`__cexport_missing'"') local opt_replace = cond("`replace'" != "", "replace", "") * Record start time local __ct_elapsed99 = 0 if "`__ct_elapsed99'" == "" local __ct_elapsed99 = 0 quietly _ctools_clock __ct_start99 * Set string width metadata for flat buffer optimization _ctools_strw `export_varlist' * Stata answers an if one observation at a time. Without one the plugin * skips the check; the if marked after syntax is passed last, as a * sample marker the plugin reads in parallel. local opt_select "skipif" local marker "" local ifcond "" if `"`if'"' != "" { local marker "`iftouse'" local opt_select "touse" } * Call the C plugin * Plugin expects options only; paths/metadata are length-checked locals. * Use export_varlist (may contain decoded temp vars for value labels) capture noisily plugin call ctools_plugin `export_varlist' `marker' `ifcond' `in', /// "cexport `threads_code' `plugin_delim' `opt_replace' `opt_noheader' `opt_quote' `opt_noquoteif' `opt_verbose' `opt_mmap' `opt_nofsync' `opt_direct' `opt_prefault' `opt_crlf' `opt_noparallel' `opt_select'" local export_rc = _rc * Clean up global macros * The plugin reports a write-protected destination as r(608) if `export_rc' == 608 { di as error `"file `using' is read-only; cannot be modified or erased"' exit 603 } if `export_rc' { di as error "Error exporting data (rc=`export_rc')" exit `export_rc' } quietly _ctools_clock __ct_now local __ct_elapsed99 = `__ct_elapsed99' + (`__ct_now' - `__ct_start99') * Elapsed seconds are held in caller-local timing state. local elapsed = `__ct_elapsed99' * Display summary di as text "" di as text "file " as result `"`using'"' as text " saved" di as text "(" as result %12.0fc `nobs' as text " observations written)" * Display timing if requested if "`verbose'" != "" { capture local __time_load = _cexport_time_load if _rc != 0 local __time_load = . capture local __time_format = _cexport_time_format if _rc != 0 local __time_format = . capture local __time_write = _cexport_time_write if _rc != 0 local __time_write = . capture local __time_total = _cexport_time_total if _rc != 0 local __time_total = . if `__time_total' != . { local plugin_overhead = `elapsed' - `__time_total' if `plugin_overhead' < 0 local plugin_overhead = 0 } else { local plugin_overhead = . } di as text "" di as text "{hline 55}" di as text "cexport timing breakdown:" di as text "{hline 55}" di as text " C plugin internals:" di as text " Data load: " as result %8.4f `__time_load' " sec" di as text " Format: " as result %8.4f `__time_format' " sec" di as text " Write to file: " as result %8.4f `__time_write' " sec" di as text " {hline 53}" di as text " C plugin total: " as result %8.4f `__time_total' " sec" di as text "" di as text " Stata overhead:" di as text " Plugin call overhead: " as result %8.4f `plugin_overhead' " sec" di as text " {hline 53}" di as text " Stata overhead total: " as result %8.4f `plugin_overhead' " sec" di as text "{hline 55}" di as text " Wall clock total: " as result %8.4f `elapsed' " sec" di as text "{hline 55}" if `elapsed' > 0 { local rows_per_sec = `nobs' / `elapsed' di as text " Throughput: " as result %12.0fc `rows_per_sec' as text " rows/sec" } * Display thread diagnostics capture local __threads_max = _cexport_threads_max if _rc == 0 { capture local __openmp_enabled = _cexport_openmp_enabled if _rc != 0 local __openmp_enabled = 0 di as text "" di as text " Thread diagnostics:" di as text " OpenMP enabled: " as result %8.0f `__openmp_enabled' di as text " Max threads available: " as result %8.0f `__threads_max' di as text "{hline 55}" } } * Return results return scalar N = `nobs' return scalar k = `nvars' return scalar time = `elapsed' return local filename `"`using'"' local __ct_elapsed99 = 0 end /******************************************************************************* * cexport_excel: Export to Excel (.xlsx) files * * Syntax: cexport excel [varlist] [using] filename [if] [in], [options] * * Options: * sheet(name) - Worksheet name (default: "Sheet1") * firstrow(variables|nonames) - First row handling * replace - Overwrite existing file * nolabel - Export numeric values instead of value labels * verbose - Display timing information ******************************************************************************/ program define cexport_excel, rclass version 14.1 * Check observation limit (Stata plugin API limitation) if _N > 2147483647 { di as error "ctools does not support datasets exceeding 2^31 (2.147 billion) observations" di as error "This is a limitation of Stata's plugin API" exit 920 } * Support both "using filename" and just "filename" (like export excel does) syntax [anything] [using/] [if] [in], [SHEET(string asis) FIRSTrow(string) /// REPLACE DATAfmt DATEString(string) NOLabel Verbose /// CELL(string) MISSING(string) KEEPCELLfmt THReads(integer 0) /// SHEETMODify SHEETREPlace LOCALE(string)] * The if is evaluated now, as export excel does, before any helper * replaces the r() or e() results it may name. local iftouse "" if `"`if'"' != "" { tempvar iftouse mark `iftouse' `if' `in' local if "if `iftouse'" } _cexport_excel_sheet `macval(sheet)' local sheet `"`r(sheet)'"' local sheet_action `r(action)' * As export excel does: the separate sheetmodify and sheetreplace options * exclude each other and take precedence over the sheet() suboption. local sheet_option `"sheet(..., `=substr("`sheet_action'", 6, .)')"' if "`sheetmodify'" != "" & "`sheetreplace'" != "" { di as error "invalid syntax; option sheetmodify may not be combined with option sheetreplace" exit 198 } if "`sheetmodify'`sheetreplace'" != "" { local sheet_action "`sheetmodify'`sheetreplace'" local sheet_option "`sheet_action'" } if "`sheet_action'" != "" & "`replace'" != "" { di as error "invalid syntax; option `sheet_option' may not be combined with option replace" exit 198 } * Handle filename - can come from using/ or as last positional argument if `"`using'"' == "" & `"`anything'"' != "" { local __tokens `"`anything'"' local __filename "" local __varlist "" while `"`__tokens'"' != "" { gettoken __tok __tokens : __tokens if `"`__tokens'"' == "" local __filename `"`__tok'"' else { local __varlist `"`__varlist' `__tok'"' } } if `"`__filename'"' == "" { di as error "cexport excel: filename required" di as error "Syntax: cexport excel [varlist] [using] filename [, options]" exit 198 } local using `"`__filename'"' local anything `"`__varlist'"' } if `"`using'"' == "" { di as error "cexport excel: filename required" di as error "Syntax: cexport excel [varlist] [using] filename [, options]" exit 198 } * Resolve varlist from anything. * Must happen BEFORE marksample, which creates a touse tempvar * that ds would include in the variable list. if strtrim(`"`anything'"') != "" { unab varlist : `anything' } else { qui ds local varlist `r(varlist)' } local varlist : list varlist - iftouse * Mark sample marksample touse, novarlist * Validate cell option format (e.g., A1, B5, AA10) if `"`cell'"' != "" { local cell_upper = upper(`"`cell'"') * Check format: 1-3 letters followed by 1 or more digits if !regexm("`cell_upper'", "^[A-Z]+[0-9]+$") { di as error "cexport excel: cell() must be a valid cell reference (e.g., A1, B5)" exit 198 } local cell `"`cell_upper'"' } * Native export assumes .xlsx when no Excel extension is supplied. if !inlist(lower(substr(`"`using'"',-4,4)),".xls") & lower(substr(`"`using'"',-5,5)) != ".xlsx" { local using `"`using'.xlsx"' } * Validate file extension local ext = substr(lower(`"`using'"'), -5, 5) local excel_engine "cexport_xlsx" if lower(substr(`"`using'"',-4,4)) == ".xls" local excel_engine "cexport_xls" if "`ext'" != ".xlsx" & "`excel_engine'" != "cexport_xls" { di as error "cexport excel: file must have .xls or .xlsx extension" exit 198 } * Parse firstrow option local opt_firstrow = "nofirstrow" if "`firstrow'" != "" & substr("variables",1,strlen("`firstrow'")) == "`firstrow'" local firstrow "variables" if strlen("`firstrow'") >= 4 & substr("varlabels",1,strlen("`firstrow'")) == "`firstrow'" local firstrow "varlabels" if "`firstrow'" != "" { if "`firstrow'" == "nonames" { local opt_firstrow = "nofirstrow" } else if inlist("`firstrow'", "variables", "varlabels") { local opt_firstrow "firstrow" } else { di as error "cexport excel: firstrow() must be 'variables' or 'nonames'" exit 198 } } * Handle value labels local export_varlist "" local decoded_vars "" local ndec 0 if "`nolabel'" == "" { foreach var of local varlist { local vallbl : value label `var' if "`vallbl'" != "" { * A counter names the tempvar, so any variable name fits local ++ndec tempvar dec`ndec' local dname `dec`ndec'' qui decode `var', gen(`dname') * Unlabeled values, extended missing values included (.b), are * written as numbers as native export excel does; only system * missing is left empty qui replace `dname' = string(`var',"%18.0g") if `dname'=="" & `var' != . local export_varlist `export_varlist' `dname' local decoded_vars `decoded_vars' `dname' } else { local export_varlist `export_varlist' `var' } } } else { local export_varlist `varlist' } * Handle datafmt and datestring(): format date/time variables as strings if "`datafmt'" != "" | `"`datestring'"' != "" { local new_export_varlist "" local formatted_vars "" local export_idx = 0 foreach var of local export_varlist { local export_idx = `export_idx' + 1 * Get original variable name for format lookup local origvar : word `export_idx' of `varlist' * Check if this is a date/time variable by looking at its format local fmt : format `origvar' local is_date_fmt = 0 if substr("`fmt'", 1, 2) == "%t" | substr("`fmt'", 1, 3) == "%-t" { local is_date_fmt = 1 } * The old %d is a daily format, as export excel reads it if substr("`fmt'", 1, 2) == "%d" | substr("`fmt'", 1, 3) == "%-d" local is_date_fmt = 1 if `is_date_fmt' { * This is a date/time variable - convert to formatted string tempvar fmtvar_`export_idx' * Use datestring format if specified, otherwise use variable's display format * Note: missing values should become empty strings (like Stata's behavior) if `"`datestring'"' != "" { qui gen str `fmtvar_`export_idx'' = cond(missing(`origvar'), "", string(`origvar', `"`datestring'"')) } else { qui gen str `fmtvar_`export_idx'' = cond(missing(`origvar'), "", string(`origvar', "`fmt'")) } local new_export_varlist `new_export_varlist' `fmtvar_`export_idx'' local formatted_vars `formatted_vars' `fmtvar_`export_idx'' } else { * Not a date variable - keep as is local new_export_varlist `new_export_varlist' `var' } } local export_varlist `new_export_varlist' } else { * No datafmt/datestring: convert date values to Excel serial numbers * so they are stored as native Excel dates (not strings) local new_export_varlist "" local date_converted_vars "" local export_idx = 0 local has_date_cols = 0 foreach var of local export_varlist { local export_idx = `export_idx' + 1 local origvar : word `export_idx' of `varlist' local fmt : format `origvar' local is_date_fmt = 0 if substr("`fmt'", 1, 2) == "%t" | substr("`fmt'", 1, 3) == "%-t" { local is_date_fmt = 1 } * The old %d is a daily format, as export excel reads it if substr("`fmt'", 1, 2) == "%d" | substr("`fmt'", 1, 3) == "%-d" local is_date_fmt = 1 if substr("`fmt'", 1, 2) == "%d" local fmt = "%t" + substr("`fmt'", 2, .) if substr("`fmt'", 1, 3) == "%-d" local fmt = "%-t" + substr("`fmt'", 3, .) * Determine date type: strip % and optional - local fmtsub = substr("`fmt'", 2, .) if substr("`fmtsub'", 1, 1) == "-" local fmtsub = substr("`fmtsub'", 2, .) local datetype = substr("`fmtsub'", 2, 1) * A generic %tg value has no calendar unit: write the raw number if `is_date_fmt' & !inlist("`datetype'", "d", "m", "q", "w", "h", "y", "b", "c", "C") local is_date_fmt = 0 if `is_date_fmt' { local has_date_cols = 1 tempvar xldate_`export_idx' if "`datetype'" == "b" { * Business dates count calendar days: %tb[:display] local bcal = substr("`fmtsub'", 3, .) if strpos("`bcal'", ":") local bcal = substr("`bcal'", 1, strpos("`bcal'", ":") - 1) qui gen double `xldate_`export_idx'' = dofb(`origvar', "`bcal'") + 21916 if !missing(`origvar') } else if "`datetype'" == "d" { qui gen double `xldate_`export_idx'' = `origvar' + 21916 if !missing(`origvar') } else if "`datetype'" == "m" { qui gen double `xldate_`export_idx'' = dofm(`origvar') + 21916 if !missing(`origvar') } else if "`datetype'" == "q" { qui gen double `xldate_`export_idx'' = dofq(`origvar') + 21916 if !missing(`origvar') } else if "`datetype'" == "w" { qui gen double `xldate_`export_idx'' = dofw(`origvar') + 21916 if !missing(`origvar') } else if "`datetype'" == "h" { qui gen double `xldate_`export_idx'' = dofh(`origvar') + 21916 if !missing(`origvar') } else if "`datetype'" == "y" { qui gen double `xldate_`export_idx'' = dofy(`origvar') + 21916 if !missing(`origvar') } else if "`datetype'" == "c" | "`datetype'" == "C" { if "`datetype'" == "C" { qui gen double `xldate_`export_idx'' = cofC(`origvar') / 86400000 + 21916 if !missing(`origvar') } else { qui gen double `xldate_`export_idx'' = `origvar' / 86400000 + 21916 if !missing(`origvar') } } else { qui gen double `xldate_`export_idx'' = `origvar' + 21916 if !missing(`origvar') } * Excel inserts a nonexistent 29feb1900 at serial 60. qui replace `xldate_`export_idx'' = `xldate_`export_idx'' - 1 if `xldate_`export_idx'' < 61 local new_export_varlist `new_export_varlist' `xldate_`export_idx'' local date_converted_vars `date_converted_vars' `xldate_`export_idx'' } else { local new_export_varlist `new_export_varlist' `var' } } if `has_date_cols' { local export_varlist `new_export_varlist' } } * Count variables and observations local nvars : word count `varlist' qui count `if' `in' local nobs = r(N) if `nvars' == 0 { di as error "cexport excel: no variables to export" exit 198 } * As export excel does: an empty dataset is r(2000), an empty selection * r(198) if _N == 0 { di as error "no observations" exit 2000 } if `nobs' == 0 { if "`excel_engine'" == "cexport_xls" di as error "too many or no observations specified" else di as error "observations must be between 1 and 1048576" exit 198 } * Load plugin _ctools_load * Stata scopes plugin registrations to the calling ado program. capture program ctools_plugin, plugin using("`__ctools_plugin'") if _rc != 0 & _rc != 110 exit 601 capture confirm number 0 * Build plugin arguments local opt_replace = cond("`replace'" != "", "replace", "") local opt_nolabel = cond("`nolabel'" != "", "nolabel", "") local opt_verbose = cond("`verbose'" != "", "verbose", "") local opt_cell = cond(`"`cell'"' != "", `"cell=`cell'"', "") local opt_keepcellfmt = cond("`keepcellfmt'" != "", "keepcellfmt", "") * Pass variable names to plugin * Pass variable types local vartypes "" foreach var of local export_varlist { local vtype : type `var' if "`vtype'" == "byte" { local vartypes `vartypes' 1 } else if "`vtype'" == "int" { local vartypes `vartypes' 2 } else if "`vtype'" == "long" { local vartypes `vartypes' 3 } else if "`vtype'" == "float" { local vartypes `vartypes' 4 } else if "`vtype'" == "double" { local vartypes `vartypes' 5 } else { * String local vartypes `vartypes' 0 } } * Cell style codes for the plugin, assigned from the display format as * export excel assigns them: 1 date (m/d/yy), 3 date and time * (m/d/yy h:mm), 4 time of day (h:mm:ss AM/PM) when a %tc format starts * with an hour, minute or am/pm code, 2 a %tc format that shows seconds * with s (native writes empty text; cexport writes the value with * milliseconds), 5 0 for %tg; numbers 7 0.00 (.2f), 9 #,##0.00 (.2fc), * 8 #,##0 (gc), 10 0.00E+00 (.2e) and 6 0 otherwise. 0 is General: text, * and numbers under keepcellfmt, which keeps a cell's format for * everything but %t formats. local date_cols "" local export_idx = 0 foreach var of local export_varlist { local export_idx = `export_idx' + 1 local origvar : word `export_idx' of `varlist' local fmt : format `origvar' if substr("`fmt'", 1, 2) == "%d" local fmt = "%t" + substr("`fmt'", 2, .) if substr("`fmt'", 1, 3) == "%-d" local fmt = "%-t" + substr("`fmt'", 3, .) local is_date = 0 capture confirm numeric variable `var' local isnum = !_rc if `isnum' & (substr("`fmt'", 1, 2) == "%t" | substr("`fmt'", 1, 3) == "%-t") { * The unit follows %t or %-t; %tg is written as its raw number local unit = substr(subinstr("`fmt'", "%-t", "%t", 1), 3, 1) local custom = substr(subinstr("`fmt'", "%-t", "%t", 1), 4, .) local is_date = 5 if inlist("`unit'", "d", "m", "q", "w", "h", "y", "b") local is_date = 1 else if inlist("`unit'", "c", "C") { local is_date = 3 if inlist(substr("`custom'", 1, 1), "H", "h", "a") | inlist(substr("`custom'", 1, 2), "MM", "mm") local is_date = 4 if strpos("`custom'", "s") local is_date = 2 } } if `isnum' & !`is_date' & "`keepcellfmt'" == "" { local is_date = 6 if substr("`fmt'", -2, 2) == "gc" local is_date = 8 else if substr("`fmt'", -4, 4) == ".2fc" local is_date = 9 else if substr("`fmt'", -3, 3) == ".2f" local is_date = 7 else if substr("`fmt'", -3, 3) == ".2e" local is_date = 10 } local date_cols `date_cols' `is_date' } * Each metadata field has an independent local; no aggregate buffer limit. local metadata_count : word count `varlist' forvalues metadata_i=1/`metadata_count' { local __cexport_name_`metadata_i' : word `metadata_i' of `varlist' if "`firstrow'" == "varlabels" { local header_var : word `metadata_i' of `varlist' * Labels may come from someone else's file: Mata only mata: st_local("__cexport_name_`metadata_i'", st_varlabel("`header_var'") != "" ? st_varlabel("`header_var'") : st_local("__cexport_name_`metadata_i'")) } local __cexport_type_`metadata_i' : word `metadata_i' of `vartypes' local __cexport_date_`metadata_i' : word `metadata_i' of `date_cols' if "`__cexport_date_`metadata_i''" == "" local __cexport_date_`metadata_i' 0 } local __cexport_filename `"`using'"' local __cexport_filename_len = strlen(`"`__cexport_filename'"') local __cexport_sheet `"`sheet'"' local __cexport_sheet_len = strlen(`"`__cexport_sheet'"') local __cexport_missing `"`missing'"' local __cexport_missing_len = strlen(`"`__cexport_missing'"') local opt_replace = cond("`replace'" != "", "replace", "") * Record start time local __ct_elapsed99 = 0 if "`__ct_elapsed99'" == "" local __ct_elapsed99 = 0 quietly _ctools_clock __ct_start99 * Set string width metadata for flat buffer optimization _ctools_strw `export_varlist' local threads_code "" if `threads' > 0 local threads_code "threads(`threads')" * Call plugin with if `touse' so SF_ifobs() filters correctly capture noisily plugin call ctools_plugin `export_varlist' if `touse', /// "`excel_engine' `threads_code' `opt_firstrow' `opt_replace' `opt_nolabel' `opt_verbose' `opt_cell' `opt_keepcellfmt' `sheet_action'" local export_rc = _rc * Clean up * The plugin reports a write-protected destination as r(608) if `export_rc' == 608 { di as error `"file `using' could not be saved"' exit 603 } if `export_rc' { di as error "Error exporting XLSX data (rc=`export_rc')" exit `export_rc' } quietly _ctools_clock __ct_now local __ct_elapsed99 = `__ct_elapsed99' + (`__ct_now' - `__ct_start99') * Elapsed seconds are held in caller-local timing state. local elapsed = `__ct_elapsed99' * Display summary di as text "" di as text "file " as result `"`using'"' as text " saved" di as text "(" as result %12.0fc `nobs' as text " observations written)" if "`verbose'" != "" { di as text "" di as text "Time: " as result %9.3f `elapsed' as text " seconds" } * Return results return scalar N = `nobs' return scalar k = `nvars' return scalar time = `elapsed' return local filename `"`using'"' local __ct_elapsed99 = 0 end * Parse the nested native sheet(name, modify|replace) syntax independently. * As export excel does: one name, quoted or not, then modify or replace * spelled in full; when both are given the first applies. program define _cexport_excel_sheet, rclass version 14.1 gettoken sheet rest : 0, parse(" ,") quotes if `"`macval(sheet)'"' == "," { local rest `", `macval(rest)'"' local sheet "" } else gettoken sheet : sheet gettoken comma rest : rest, parse(" ,") local action "" local bad = !inlist(`"`comma'"', "", ",") | (`"`comma'"' == "" & `"`macval(rest)'"' != "") while !`bad' & `"`macval(rest)'"' != "" { gettoken word rest : rest, quotes if !inlist(`"`macval(word)'"', "modify", "replace") local bad 1 else if "`action'" == "" local action "sheet`word'" } if `bad' { di as error "invalid argument in option sheet()" exit 198 } return local sheet `"`macval(sheet)'"' return local action "`action'" end