Home

Python to Excel

By admin on 12 Mar | 0 comments

Finally manged to get python to communicate with excel and write data to the file.

The script below is structured so that the user selects a folder and the automatically reads in all files in that folder outputting the average and standard deviation to excel.

Enjoy - Note that the script uses gencache to establish the COM link for python more information on this type of COM interface can be found
here.

import os.path
from numpy import *
from scipy import *
from math import *
from random import *
from re import *
from string import *
 
path="C:\\DATA/sub"
 
DATASET = {} # Dictionary contain all values will be used later for exporting to excel
 
for files in os.listdir('C:\\DATA/sub'):
    print files
 
    filename = path+"/"+ files #Append files name to path
    f = open(filename,'r') # Mhh open file perhaps
    DATA=f.readlines() #Read to list so that the first few lines can be removed  
    f.close()
    print ('File Read :'+files)
    COL4 = [] # NB defines list for storing all data for intensity
 
    for line in DATA[10:]: # removes the first couple of lines of junk
        col_d = split(line)[3]
        COL4.append(eval(col_d))
 
    Intensity = array(COL4) #needs to be converted to an array
 
    #print 'Average : STD'
    #print Intensity.mean(),Intensity.std()
    print '********************'
    bins = array(linspace(0, 1, num=201)) # Define number of bins
    N,bins = histogram(Intensity,bins)
    print N,bins
   
    DATASET[files] = [Intensity.mean(),Intensity.std(),N,bins] #STORES Intensity, std,Distribution of Points, Number of Bins
 
 
 
import win32com.client
import win32com.client.dynamic
#Ex = win32com.client.dynamic.Dispatch("Excel.Application")
 
from win32com.client import gencache
gencache.EnsureModule('{00020813-0000-0000-C000-000000000046}', 0, 1, 5)
 
Ex = win32com.client.Dispatch("Excel.Application")
Ex.Visible = 1
Ex.Workbooks.Add()
Ex.Sheets("Sheet1").Select()
 
 
for postition in DATASET.keys():
    collection = DATASET[postition]
 
# Bit of a useless piece of code as the sheets has to be defined prior to acquiring data
RemainingSheets = size(DATASET.keys()) - 3
 
for data in range(RemainingSheets):
    Ex.Sheets.Add()
    sheetname = 'Sheet'+str(data+4) 
    Ex.Sheets(sheetname).Select()
 
 
f =DATASET.keys()
f.sort()
 
 
#Rename sheets to file names
flag = 1
for name in f:
    sheetname = 'Sheet'+str(flag) 
    newname = split(name,'.')[0]
    Ex.Sheets(sheetname).Name = newname
    flag = flag +1
 
flag =1
for data in f:
    datarray = DATASET[data]
    newname = split(data,'.')[0]
 
    Ex.Sheets(newname).Select()
 
    Ex.ActiveSheet.Cells(1,1).Value = 'Average'
    Ex.ActiveWorkbook.ActiveSheet.Cells(1,2).Value = 'STD'
    Ex.ActiveWorkbook.ActiveSheet.Cells(1,4).Value = 'Distribution'
    Ex.ActiveWorkbook.ActiveSheet.Cells(1,5).Value = 'Bins'
 
    Ex.ActiveSheet.Cells(2,1).Value = datarray[0]
    Ex.ActiveSheet.Cells(2,2).Value = datarray[1]
 
    cellmarker = 2         # Write the Distribution data to the excel file starting from the second row 
    for val in datarray[2]:
        Ex.ActiveWorkbook.ActiveSheet.Cells(cellmarker,4).Value = val
        cellmarker = cellmarker+1
 
    cellmarker = 2        # Write the Bin data to the excel file
    for val in datarray[3]:
        Ex.ActiveWorkbook.ActiveSheet.Cells(cellmarker,5).Value = val
        cellmarker = cellmarker+1
    
    flag = flag +1

Here is a break down of the Win32 link above

To generate a MakePy file at runtime, you need to know the unique ID of the type library (a CLSID) and its version and language identifier. This information is usually not easy to find, so the MakePy module supports a convenient method to obtain this information.
If you run the MakePy script with a -i parameter, instead of generating the source module, it prints the information necessary to force the MakePy process at run-time. The easiest way to do this is to perform the following steps:
1. Start PythonWin and select File Run.
2. Click on the Browse button and locate the file makepy.py in the win32com\client directory, i.e.

C:\Python25\Lib\site-packages\win32com\client\makepy.py

3. Enter -i in the arguments control.

Click on the OK button and again select the entry Microsoft Excel 8.0 Object Library (1.2). You should see the following text printed in the PythonWin interactive window:
{00020813-0000-0000-C000-000000000046}, lcid=0, major=1, minor=2

>>> # Use these commands in Python code to auto generate .py support
>>> from win32com.client import gencache
>>>

gencache.EnsureModule('{00020813-0000-0000-C000-000000000046}', 0, 1, 2)

Note that you copied the output of makepy -i verbatim into your source code.

Before running this code, remove the existing cache of .py files. If you run this code with a previously generated MakePy file for the Excel object, it won't be created again. To delete the cache of .py files, locate the Python\win32com\gen_py directory and delete it. You can delete the entire directory or just the files in the directory. Be sure to remove all files, not just the .py files.

If you run this code, notice that a progress bar is displayed as the .py file is generated, and this newly generated module is used for the early-bound object. If you then run this code a second time, notice you get the same output, but no generation process; this demonstrates you can force late-bound objects to be used, even when early-bound MakePy support exists for the object.
The output from this script should be:
Creating late-bound Excel object

The Excel object is
Running makepy for Excel

The Excel object is Library.Application>
from win32com.client import gencache
gencache.EnsureModule('{00020813-0000-0000-C000-000000000046}', 0, 1, 5)