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 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