[Tutor] saving .csv file as an xl worksheet

[János Juhász]

Hi,

last week I had to make a simple solution to convert prn file to excel. It
seems to be very similar.
I just wondered how easy to create an xml file that can be opened with
excel.
It can have functions, autofilters, format descriptions.
It is about 100 times faster to create than on the win32com way.
It is twice bigger than the binary excel, but it can be compressed into the
half size of the compressed binary.
People working with excel can't feel the difference :)

I don't know how can it be opened with StarOffice, but it works well with
excel2003.


##################################################################
import os

xmlhead = """<?xml version="1.0" encoding="iso-8859-1"?>
<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="XML XLS test">
  <Table>
"""

xmlsum = """   <Row>
    <Cell ss:Index="3" ss:Formula="=SUM(R[-%d]C:R[-1]C)"><Data
ss:Type="Number"></Data></Cell>
   </Row>
"""

xmlfoot = """  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Unsynced/>
   <Selected/>
  </WorksheetOptions>
  <AutoFilter x:Range="R1C1:R%dC%d"
   xmlns="urn:schemas-microsoft-com:office:excel">
  </AutoFilter>
 </Worksheet>
</Workbook>
"""

headrow = ('1st;2nd;3rd')
lines = ('1;2;3', '2;3;4', '3;4;5', '4;5;6')
dest = 'Test.xls'
xml = open(dest, 'wt')
rownum = 1

## Header
xml.write(xmlhead)

## HeadRow
xml.write('    <Row>\n')
for data in headrow.split(';'):
      xml.write('     <Cell><Data ss:Type="String">%s</Data></Cell>\n' %
data)
xml.write('    </Row>\n')
rownum += 1

## Rows with data
for line in lines:
      colnum = len(line.split(';'))
      xml.write('    <Row>\n')
      for data in line.split(';'):
            xml.write('     <Cell><Data
ss:Type="Number">%s</Data></Cell>\n' % data)
      xml.write('    </Row>\n')
      rownum += 1

## Function with reference
xml.write(xmlsum % (rownum-2))

## Foot
xml.write(xmlfoot % (rownum, colnum))
xml.close()


os.execl(r'c:\Program Files\Microsoft Office\Office10\EXCEL.EXE', dest)
##################################################################


Yours sincerely,
______________________________
János Juhász



Date: Tue, 7 Mar 2006 16:38:46 +1100
From: andrew clarke <mail at ozzmosis.com>
Subject: Re: [Tutor] saving .csv file as an xl worksheet
To: tutor at python.org
Message-ID: <20060307053846.GA32299 at ozzmosis.com>
Content-Type: text/plain; charset=us-ascii

On Mon, Mar 06, 2006 at 02:46:36PM +0530, arun wrote:

>   Can i save a file with a desired extension??
> for ex:  I have a .csv file (test.csv) and i want to save this file as
> test.xls from a python script.

Just changing the file extension from .csv to .xls won't change the file
format.

OpenOffice 2.0 supports both .csv and .xls file formats and includes
some sort of Python integration.  It may be possible to use that.

Regards
Andrew