Create full format Excel file from bash scripts

This post shows how I managed to create a excel output with all the bells and whistles of the MS Excel such as merging, colors on the tables and etc.

Open your excel sheet if you have already or make a template of your needed output format.

 

Save it as XML Spreadsheet 2003 .xml format in your drive.

Ignore the following pop up and hit Yes.

 

Open the saved xml with your favorite editor, in my case Notepad++.

 

Now you can see your data filled inside <Table> tag. you can simply parse the data from your real output and replce the start and end with these Tags " <Cell ss:StyleID="s62"><Data ss:Type="Number">" and "</Data></Cell>" then write the whole data in a excel file (something.xls). In my case i have parsed a file for the numbers output and replced the start and end with the xml tags.

Eg: grep "Selected Period" PV.html|sed -ne 's/^.*Selected Period\([^T]*\).*$/\1/p'| sed 's/\\//g;s/nt*//g;s/\///g;s/\&bsp;//g;s/<[^>]*>/\n/g'|sed 's/[0-9]\.//g;s/^ //g;/^$/d;/[a-z].*/d;s/^/\<Cell ss\:StyleID="s112"\>\<Data ss\:Type="Number"\>/g;s/$/\<\/Data\>\<\/Cell\>/g' > opv.dat

Hope this helps to create an excel from Bash.

Happy Scripting.

Deva.