Comments to output in Excel format

[Sergiy Radyakin]

Dear All,

I am looking for:

• a way (example) to programmatically insert comments to XLS/XLSX files generated with Stata's command -putexcel- , or
• a firm answer that it is not possible to do it from -putexcel- command and or any other Stata command.

I understand it could be done by involving Python or by calling external applications. I'd like to seek for a solution that is using only base functionality of a modern shipped Stata.

Thank you very much,
Sergiy Radyakin

[Daniel Schaefer]

In theory, yes I think this is possible. What you need is a command that can parse a do file as a set of lines (strings), then loop through the file line by line, using string functions to parse out your comments. In practice I really struggled with this. The problem is that any time you have a string stored in a macro, Stata wants to do macro substitution with it. So if the file you are parsing is a do file that contains macros, you're going to run into problems with Stata trying to fill in the pieces of your string, or otherwise trying to execute the other do file. This makes for very buggy code that is highly dependent on the do file you are trying to parse. More problems arise when you try to parse the parsing program itself. In the code below, Stata would try to recursively fill in my `line' macro infinitely. I think it would also be a real problem if the new command prevented you from using certain macro names, like `line'. I tried a few different workarounds, but I ended up settling on moving the string to Mata, then back to Ado as a string scalar. This way, at no time will the interpreter try to execute the code from the do file you are parsing.

I don't support all possible kinds of comment in the following command. I really intended this as a proof of concept, not a full implementation.

//set trace on
//set traceexpand on

quietly capture program drop putcomments
program putcomments
    putexcel set `2', replace
    file open dofile using `1', read
    capture noisily{
        file read dofile line
        local cellcounter = 1
        while r(eof)==0 {
            mata: scalar_line = st_local("line")
            mata: st_strscalar("scalar_line", scalar_line)
            if strpos(scalar_line, "//") == 1 | strpos(scalar_line, "*") == 1{
                putexcel A`cellcounter' = (scalar_line)
                local cellcounter = `cellcounter' + 1
            }
            file read dofile line
        }
    }
    file close dofile
end

putcomments "putcomments.do" "comments.xls" // This is not a supported comment type here at the end of this line
/* this is not a supported comment */
/* 
    this is definitely not a supported comment. comment blocks in general are  
    awkward to work with in this line by line context. A character stream of 
    the entire file might work better here, but this is certainly still 
    possible while parsing line by line.
*/
// this is a supported comment
* this is also a supported comment

[Leonardo Guizzetti]

I read Sergiy's request as wanting to programmatically create Excel comments rather than place text into a cell within Excel. I don't see any support for this within either Stata or Mata documentation. For a native solution you will have to crack open the OpenXML spec for Excel and creating comments and then use filefilter or similar to modify the Excel file. Frankly, it sounds like a big pain.

Why not contact Stata Technical Services and get a definitive answer from them? If they have a way, you can share it here.

[Daniel Schaefer]

Ah, in that case I have to agree with Leonardo. You could work with the excel file directly, which I understand is essentially a zipped xml document but

Frankly, it sounds like a big pain.

[Sergiy Radyakin]

Dear Leonardo,

you've got the question correctly, indeed I want to add comments to Excel cells in the output that I produce in Stata. I believe this is a very useful tool and I would be glad to have it in the Stata's arsenal of tools.

I also appreciate the time that Daniel has taken to provide his suggestion. I am sorry that, perhaps, because of my imprecise description the question was interpreted differently.

Daniel Schaefer , at some point I had a similar code (though not in Stata) which goes through the file and picks specifically all the comments starting with "ToDo" and then presents them in a form of a to-do list. These days the ticketing systems and version tracking software have eliminated the need for it, though I'd still prefer to have some more built-in support for comments, perhaps as simple as to define foldable regions of code (groups of procedures), todo-items, warnings, etc.

PS: Leonardo Guizzetti , I am sure the forum is monitored by the Stata developers, so if we came to an erroneous conclusion, that Stata doesn't support something, while in fact it does, someone would eventually jump in.

Thank you and best regards, Sergiy

[Bjarte Aagnes]

comments vs notes in Excel for Microsoft 365

[Daniel Schaefer]

Sergiy Radyakin I have to admit, although my answer wasn't very useful for you, that program above was a good deal of fun to write. I probably should have taken more care as I read your post.

These days the ticketing systems and version tracking software have eliminated the need for it, though I'd still prefer to have some more built-in support for comments, perhaps as simple as to define foldable regions of code (groups of procedures), todo-items, warnings, etc.

I've used a few platforms that allow you to use XML tags in comments so that you can create dynamically generated documentation from the XML. The IDE may even generate some of the XML for you. R has something like this for package development, and so does C# and Java, and I imagine other platforms as well. Something similar for Stata would be pretty neat.