Generate Microsoft Office Documents from command line

In previous posts (e.g. Export to Excel) I wrote about using HTML format to export to Excel.

Let’s do it for real, let’s dive into the .xls file format and learn how to generate dynamic excel from Unix!

1) create one time your excel file manually. With graphs, colors, sounds, up to you. Or Word, Powerpoint or whatever (minimum MS Office 2007)
excel-screenshot

2) save as excel 2007 or later format (.xlsx)
this is called the Office Open XML format. It is neither OpenOffice nor OpenSource. It is XML and license restriction may apply.

3) transfer the excel file to your favorite platform

4) unzip the excel file (yes, you read it correctly, unzip the .xlsx file)


$ unzip /tmp/DynamicExcel.xlsx
Archive:  /tmp/DynamicExcel.xlsx
  inflating: [Content_Types].xml
  inflating: _rels/.rels
  inflating: xl/_rels/workbook.xml.rels
  inflating: xl/workbook.xml
  inflating: xl/styles.xml
  inflating: xl/worksheets/sheet2.xml
  inflating: xl/worksheets/_rels/sheet1.xml.rels
  inflating: xl/worksheets/_rels/sheet2.xml.rels
  inflating: xl/drawings/_rels/drawing1.xml.rels
  inflating: xl/theme/theme1.xml
  inflating: xl/worksheets/sheet1.xml
  inflating: xl/drawings/drawing2.xml
  inflating: xl/charts/chart1.xml
  inflating: xl/drawings/drawing1.xml
  inflating: xl/sharedStrings.xml
  inflating: docProps/core.xml
  inflating: docProps/app.xml

5) now substitute the data with some script output (for instance select * from v$backup_redologs). Here I am substituing all datas from row r=2


cd xl/worksheets

tr -d '\r' < sheet1.xml | sed 's,<row r="2".*,,' > head
sqlplus -s -L / as sysdba <<'EOF' > body
set feed off pages 0 lin 2000 longc 2000 long 2000
SELECT XMLELEMENT (
          "row",
          xmlattributes ((rownum+1) AS "r",
                         '1:2' AS "spans",
                         '0.2' AS "x14ac:dyDescent"),
          XMLELEMENT ("c",
                      xmlattributes ('A' || (rownum+1) AS "r", '1' AS "s"),
                      XMLELEMENT ("v", d-date '1899-12-30')),
          XMLELEMENT ("c",
                      xmlattributes ('B' || (rownum+1) AS "r", '2' AS "s"),
                      XMLELEMENT ("v", c)))
          x
  FROM (  SELECT TRUNC (next_time, 'DD') d, COUNT (*) c
            FROM v$backup_redolog
           WHERE next_time BETWEEN TRUNC (SYSDATE - 90)
                               AND TRUNC (SYSDATE) - 1 / 86400
        GROUP BY TRUNC (next_time, 'DD')
        ORDER BY 1);
EOF
tr -d '\r' < sheet1.xml | sed -n 's,.*</sheetData>,</sheetData>,p' > tail
cat head body tail | tr -d '\n' > sheet1.xml
rm head body tail

6) recreate zip file


$ cd ../..
$ zip -r /tmp/DynamicExcel2.xlsx *
  adding: [Content_Types].xml (deflated 78%)
  adding: docProps/ (stored 0%)
  adding: docProps/core.xml (deflated 51%)
  adding: docProps/app.xml (deflated 53%)
  adding: _rels/ (stored 0%)
  adding: _rels/.rels (deflated 60%)
  adding: xl/ (stored 0%)
  adding: xl/_rels/ (stored 0%)
  adding: xl/_rels/workbook.xml.rels (deflated 71%)
  adding: xl/workbook.xml (deflated 42%)
  adding: xl/styles.xml (deflated 56%)
  adding: xl/worksheets/ (stored 0%)
  adding: xl/worksheets/sheet2.xml (deflated 45%)
  adding: xl/worksheets/_rels/ (stored 0%)
  adding: xl/worksheets/_rels/sheet1.xml.rels (deflated 39%)
  adding: xl/worksheets/_rels/sheet2.xml.rels (deflated 39%)
  adding: xl/worksheets/sheet1.xml (deflated 81%)
  adding: xl/drawings/ (stored 0%)
  adding: xl/drawings/_rels/ (stored 0%)
  adding: xl/drawings/_rels/drawing1.xml.rels (deflated 39%)
  adding: xl/drawings/drawing2.xml (deflated 58%)
  adding: xl/drawings/drawing1.xml (deflated 61%)
  adding: xl/theme/ (stored 0%)
  adding: xl/theme/theme1.xml (deflated 79%)
  adding: xl/charts/ (stored 0%)
  adding: xl/charts/chart1.xml (deflated 85%)
  adding: xl/sharedStrings.xml (deflated 22%)

7) Check it

DynamicExcel2.xlsx

3 thoughts on “Generate Microsoft Office Documents from command line”

  1. Great tip, thx. I had a similar problem ( how to create xls on unix ), but you has the solution. Many thanks!

    Regards from Vienna

  2. there is also means of converting Text document to Excel with the OpenOffice command line interface, kind of
    soffice --headless --convert-to xls
    but I have not tried myself.

  3. There are many ways to do this conversation, but your example is that, what is ( for me ) the usable is.
    I has many vba scripts to convert plain data files to semi-database-excel-sheets, and then make diags, but there are some features in excel, which makes your formatting nearby impossible. Wrong, it is not impossible, it is only not so pretty.
    With this way, i can forget formatting. I make a template with dummy texts, and i change only this texts on unix, and than send the result by mail for the managment. No more vba for this quick reports, yess! :) Thanks again.

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>