Convert a Unix file to .xls format.

sumitdevbharadwaj · 04-12-2009, 09:44 AM

Hi, I have one doubt. How can I convert a file in unix to MS excel format.
Suppose if I have a file named 'Studata' and it contains like:

/root # cat Studata
Roll no. Name Age Sex
1 ABC 12 M
2 DEF 13 M
3 XYZ 12 F

I want this table to be converted to .xls format and to be saved on my local server. I have perl script but I dont know perl so I want the code in shell scripting.
Is it possible? If yes, can anybody please help me out as how can this be achieved.
Thanks in advance.

Warm regards
Sumit Dev Bharadwaj

amani · 04-12-2009, 10:26 AM

Use Gnumeric or OOcalc

IBall · 04-12-2009, 10:39 AM

You could convert it into a .csv (Comma Separated Values), which will open by default in Excel and be displayed as a spreadsheet.

I doubt you could make a .xls - hell even Microsofts own .Net can't do that easily...

--Ian

sumitdevbharadwaj · 04-12-2009, 11:41 PM

Thanks Ian.

billybadfoot · 04-14-2009, 09:43 AM

Spreadsheets can be saved as xml in both MS Excel and in OpenOffice Calc. If you store the following with a .xml extension, and open it with Excel or OpenOffice, then you get it opening as a spreadsheet with the worksheet name set to Student Data. After opening it in Excel or OpenOffice, you can 'save as' in spreadsheet format.

Code:

<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <Worksheet ss:Name="Student Data">
  <Table>
   <Row>
    <Cell><Data ss:Type="String">Roll No.</Data></Cell>
    <Cell><Data ss:Type="String">Name</Data></Cell>
    <Cell><Data ss:Type="String">Age</Data></Cell>
    <Cell><Data ss:Type="String">Sex</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="String">ABC</Data></Cell>
    <Cell><Data ss:Type="Number">12</Data></Cell>
    <Cell><Data ss:Type="String">M</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">2</Data></Cell>
    <Cell><Data ss:Type="String">DEF</Data></Cell>
    <Cell><Data ss:Type="Number">13</Data></Cell>
    <Cell><Data ss:Type="String">M</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">3</Data></Cell>
    <Cell><Data ss:Type="String">XYZ</Data></Cell>
    <Cell><Data ss:Type="Number">12</Data></Cell>
    <Cell><Data ss:Type="String">F</Data></Cell>
   </Row>
  </Table>
 </Worksheet>
</Workbook>

ghostdog74 · 04-14-2009, 10:03 AM

Quote:

Originally Posted by sumitdevbharadwaj

How can I convert a file in unix to MS

change the spaces to commas then import from Excel

Code:

# awk '{$1=$1}1' OFS="," file
1,ABC,12,M
2,DEF,13,M
3,XYZ,12,F

another way, if you have Perl and can install the Spreadsheet::WriteExcel module

Code:

#!/usr/bin/perl

use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new("test.xls"); 
my $worksheet = $workbook->add_worksheet();
open(FH,"<file") or die "Cannot open file: $!\n";
my ($x,$y) = (0,0);
while (<FH>){ 
 chomp;
 @list = split /\s+/,$_;
 foreach my $c (@list){
    $worksheet->write($x, $y++, $c);     
 }
 $x++;$y=0;
}
close(FH);
$workbook->close();

sumitdevbharadwaj · 04-18-2009, 12:18 AM

Hi Ghostdog74 and Billybadfoot,

Great ideas, I have used both of them and it really worked for me. Thanks in loads.