Retaining an Excel cell’s format when utilizing putexcel

In a earlier weblog entry, I talked concerning the new Stata 13 command putexcel and the way we might use putexcel with a Stata command’s saved outcomes to create tables in an Excel file.

After the entry was posted, a number of customers identified two options they needed added to putexcel:

1. Retain a cell’s format after writing numeric knowledge to it.
2. Enable putexcel to format a cell.

In Stata 13.1, we added the brand new possibility keepcellformat to putexcel. This selection retains a cell’s format after writing numeric knowledge to it. keepcellformat is helpful for individuals who wish to automate the updating of a report or paper.

To evaluate, the fundamental syntax of putexcel is as follows:

putexcel excel_cell=(expression) … utilizing filename[, options]

If you’re working with matrices, the syntax is

putexcel excel_cell=matrix(expression) … utilizing filename[, options]

Within the earlier weblog publish, we exported a easy desk created by the correlate command by utilizing the instructions under.

. sysuse auto
(1978 Vehicle Information)

. correlate overseas mpg
(obs=74)

             |  overseas      mpg
-------------+------------------
     overseas |   1.0000
         mpg |   0.3934   1.0000

. putexcel A1=matrix(r(C), names) utilizing corr

These instructions created the file corr.xlsx, which contained the desk under within the first worksheet.

As you may see, this desk will not be formatted. So, I formatted the desk by hand in Excel in order that the correlations had been rounded to 2 digits and the column and row headers had been daring with a blue background.

putexcel‘s default habits is to take away the formatting of cells. Thus, if we wish to change the correlated variables in our command from overseas and mpg to overseas and weight utilizing the under instructions, the brand new correlations proven in Excel will revert to the default format:

. sysuse auto, clear
(1978 Vehicle Information)

. correlate overseas weight
(obs=74)

             |  overseas   weight
-------------+------------------
     overseas |   1.0000
      weight |  -0.5928   1.0000

. putexcel A1=matrix(r(C), names) utilizing corr, modify

As of Stata 13.1, now you can use the keepcellformat choice to protect a numeric cell’s format when writing to it. For instance, the command

. putexcel A1=matrix(r(C), names) utilizing corr, modify keepcellformat

will produce

Let’s have a look at a real-world downside and actually see how the keepcellformat possibility will help us. Suppose we have to export the next tabulate desk to a report we wrote in Phrase.

. webuse auto2, clear
(1978 Vehicle Information)

. label variable rep78 "Restore File"

. tabulate rep78

     Restore |
     File |      Freq.     %        Cum.
------------+-----------------------------------
       Poor |          2        2.90        2.90
       Honest |          8       11.59       14.49
    Common |         30       43.48       57.97
       Good |         18       26.09       84.06
  Wonderful |         11       15.94      100.00
------------+-----------------------------------
      Complete |         69      100.00

Within the earlier putexcel weblog publish, I discussed my user-written command tab2xl, which exports a one-way tabulation to an Excel file. I’ve since up to date the command in order that it makes use of the brand new keepcellformat choice to protect cell formatting. You may obtain the up to date tab2xl command by typing the next:

. internet set up http://www.stata.com/customers/kcrow/tab2xl, change

Utilizing this command, I can now export my tabulate desk to Excel by typing

. tab2xl rep78 utilizing tables, row(1) col(1)

As soon as the desk is in Excel, I format it by hand in order that it appears to be like like this:

I then hyperlink this Excel desk to a Phrase doc. Whenever you hyperlink an Excel desk to a Phrase doc, it

1. preserves the formatting of the desk and
2. robotically updates the Phrase doc whenever you replace the Excel desk.

It’s pretty simple to hyperlink an Excel desk to a Phrase doc or PowerPoint presentation. In Excel/Phrase 2010, you’ll do as follows:

1. Spotlight the desk/knowledge in Excel.
2. On the Residence tab, click on on the Copy button.
3. Open the Phrase doc and scroll to the place you need the desk pasted.
4. On the Residence tab of Phrase, click on on the Paste button.
5. Choose Hyperlink & Preserve Supply Formatting, , from the Paste icon menu.

My report now appears to be like like this:

With the Excel desk linked into Phrase, any time we replace our Excel desk utilizing putexcel, we additionally replace our desk in Phrase.

Suppose that after a number of weeks, we get extra restore file knowledge. We now must replace our report, and our new tabulate desk appears to be like like this:

. tabulate rep78

     Restore |
     File |      Freq.     %        Cum.
------------+-----------------------------------
       Poor |          4        2.90        2.90
       Honest |          8        5.80        8.70
    Common |         60       43.48       52.17
       Good |         44       31.88       84.06
  Wonderful |         22       15.94      100.00
------------+-----------------------------------
      Complete |        138      100.00

To replace the report, we merely must reissue the putexcel command after tabulate.

. tabulate rep78
. tab2xl rep78 utilizing tables, row(1) col(1)

The linked Phrase report will robotically replicate the modifications: