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)
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
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
tr -d '\r' < sheet1.xml | sed 's,
sqlplus -s -L / as sysdba <<'EOF' > body
set feed off pages 0 lin 2000 longc 2000 long 2000
SELECT XMLELEMENT (
xmlattributes ((rownum+1) AS "r",
'1:2' AS "spans",
'0.2' AS "x14ac:dyDescent"),
xmlattributes ('A' || (rownum+1) AS "r", '1' AS "s"),
XMLELEMENT ("v", d-date '1899-12-30')),
xmlattributes ('B' || (rownum+1) AS "r", '2' AS "s"),
XMLELEMENT ("v", c)))
FROM ( SELECT TRUNC (next_time, 'DD') d, COUNT (*) c
WHERE next_time BETWEEN TRUNC (SYSDATE - 90)
AND TRUNC (SYSDATE) - 1 / 86400
GROUP BY TRUNC (next_time, 'DD')
ORDER BY 1);
tr -d '\r' < sheet1.xml | sed -n 's,.*,,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
Great tip, thx. I had a similar problem ( how to create xls on unix ), but you has the solution. Many thanks!
Regards from Vienna
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.
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.