Python:Excel.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-
#
# Excel.py version: 1.0
#
# (C) Filnik, 2007
#
# Devolped: http://botwiki.sno.cc/
#
# Distributed under the terms of the MIT license.
#
 
"""
Module written to make more easy using excel's function. It needs pyWin libraries ^__^
and (at least) python 2.5
 
Some code is stolen around searching with google ^__^ but i don't think that's
so important a part from the saving format that i thank a lot the author (very useful ^__^)
"""
 
from win32com import client
import time
 
class main():
    def __init__(self, visible = 0, directory = "C:\\"):
        """
        Staring the excel's file.
        example: r = excel.main()
        """
 
        try:
            excel = client.Dispatch("Excel.Application.11") # Excel 2003
        except com_error:
            try:
                excel = client.Dispatch("Excel.Application.10") # Excel XP
            except com_error:
                try:
                    excel = client.Dispatch("Excel.Application.9") # Excel 2000
                except com_error:
                    try:
                        excel = client.Dispatch("Excel.Application.8") # Excel 97
                    except com_error:
                        try:
                            excel = client.Dispatch("Excel.Application") # Excel 5.0?
                        except com_error:
                            print "No Office..."
                            exit()
 
        if visible == True:
            excel.Visible = 1
        else:
            excel.Visible = 0
        workbook = excel.Workbooks.Open()
        sheet = excel.Workbooks(1).Sheets(1)
        self.excel = excel
        self.sheet = sheet
        self.workbook = workbook
    def RangeDefine(self, cells):
        """
        Use this function to select a range of cells
        example: cells = r.RangeDefine("A1:B1")
        """
        cells = self.excel.ActiveWorkbook.ActiveSheet.Range(cells)
        return cells
    def read(self, cell = "A1"):
        """
        Use this function to read a cell
        example: cells = r.read("A1")
        """
        cellRead = self.excel.ActiveWorkbook.ActiveSheet.Range(cell).Formula
        return cellRead
    def formula(self, cell = "A1", text = None):
        """ Use this function to write in a cell
        example: cells = r.formula("A1")
        """
        self.excel.ActiveWorkbook.ActiveSheet.Range(cell).Formula = text  
    def borders(self, cell, number, weight = -4138):
        """
        Use this function to modify the borders weight
        of the cells. example: r.borders("A1", 4)
        """
        self.excel.ActiveWorkbook.ActiveSheet.Range(cell).Borders(number).Weight = weight
    def delete(self, number = 1):
        """
        Use this function to delete a sheet from your active workbook (if the sheet is the
        first use 1, i've put a -1 to use real number ;-) )
        example: r.delete(1)
        """
        sheetnumber = int(number) - 1 
        self.workbook.Worksheets[sheetnumber].Delete()
    def rename(self, number = 1, name = "Excel"):
        """ Use this function to rename a sheet (for the sheet's number, see delete)
        example: r.rename(1, "Rock!")
        """
        sheetnumber = int(number) - 1
        self.workbook.Worksheets[sheetnumber].Name = name
    def save(self, directory, encoding = None):
        """
        Use this function to save your excel's file. It doesn't support all
        the normale SaveAs option but i think that these are the most important
        example: r.save("C:\\excel\\excel.xls", 44)
        """
        if encoding != None:
            self.workbook.SaveAs(Filename = directory, FileFormat = encoding)
        else:
            self.workbook.SaveAs(directory)            
        print "File saved"
    def close(self):
        """
        Use this function to close your workbook.
        example: r.close()
        """
        print "Closing..."
        self.excel.Quit()
 
class IE():
    def __init__(self, visible = 0):
        """ example: r = excel.main) """
        try:
            ie = client.Dispatch("InternetExplorer.Application")
        except:
            print "Error!"
            exit()
        if visible == True:
            ie.Visible = 1
        else:
            ie.Visible = 0                            
        self.ie = ie
    def navigate(self, site):
        self.ie.Navigate(site)
        while self.ie.Busy:
            time.sleep(0.5)
    def form(self, value):
        self.ie.Document.Forms[0].Elements['q'].value = value
    def submit(self):
        self.ie.Document.Forms[0].submit()
    def close(self):
        self.ie.Quit()
 
excel = False
if __name__ == "__main__":
    if excel == True:
        print "Performing excel's test..."
        try:
            r = main(False, 'C:\\Documents and Settings\\Fil\\Desktop\\excel\\test.xls')
            print r.RangeDefine("A1:B1")
            print r.read("A1")
            r.formula("A1", "Test!")
            print r.read("A1")
            r.borders("A1", 4)
            r.delete(2)
            r.rename(2, "Ciao")
            r.save("C:\\excel\\test.xls")
            r.close()
        except:
            exit()
    elif excel == False:
        print "Performing IE's test..."
        r = IE(True)
        r.navigate("www.google.it")
        r.form("Ciao")
        r.submit()    
 
"""
# These are all possible codes to use with the
# workbook.SaveAs()
##xlAddIn	18	Microsoft Excel 2007 Add-In
##xlAddIn8	18	Microsoft Excel 97-2003 Add-In
##xlCSV	6	CSV
##xlCSVMac	22	Macintosh CSV
##xlCSVMSDOS	24	MSDOS CSV
##xlCSVWindows	23	Windows CSV
##xlCurrentPlatformText	-4158	Current Platform Text
##xlDBF2	7	DBF2
##xlDBF3	8	DBF3
##xlDBF4	11	DBF4
##xlDIF	9	DIF
##xlExcel12	50	Excel12 (.xlsb file extension)
##xlExcel2	16	Excel2
##xlExcel2FarEast	27	Excel2 FarEast
##xlExcel3	29	Excel3
##xlExcel4	33	Excel4
##xlExcel4Workbook	35	Excel4 Workbook
##xlExcel5	39	Excel5
##xlExcel7	39	Excel7
##xlExcel8	56	Excel8 (.xls file extension)
##xlExcel9795	43	Excel9795
##xlHtml	44	HTML format
##xlIntlAddIn	26	International Add-In
##xlIntlMacro	25	International Macro
##xlOpenXMLAddIn	55	Open XML Add-In
##xlOpenXMLTemplate	54	Open XML Template
##xlOpenXMLTemplateMacroEnabled	53	Open XML Template Macro Enabled
##xlOpenXMLWorkbook	51	Open XML Workbook
##xlOpenXMLWorkbookMacroEnabled	52	Open XML Workbook Macro Enabled
##xlSYLK	2	SYLK
##xlTemplate	17	Template
##xlTemplate8	17	Template 8
##xlTextMac	19	Macintosh Text
##xlTextMSDOS	21	MSDOS Text
##xlTextPrinter	36	Printer Text
##xlTextWindows	20	Windows Text
##xlUnicodeText	42	Unicode Text
##xlWebArchive	45	Web Archive
##xlWJ2WD1	14	WJ2WD1
##xlWJ3	40	WJ3
##xlWJ3FJ3	41	WJ3FJ3
##xlWK1	5	WK1
##xlWK1ALL	31	WK1ALL
##xlWK1FMT	30	WK1FMT
##xlWK3	15	WK3
##xlWK3FM3	32	WK3FM3
##xlWK4	38	WK4
##xlWKS	4	Worksheet
##xlWorkbookDefault	51	Workbook default
##xlWorkbookNormal	-4143	Workbook normal
##xlWorks2FarEast	28	Works2 FarEast
##xlWQ1	34	WQ1
##xlXMLSpreadsheet	46	XML Spreadsheet
#
#A couple more extensions, without a code
#
##              51 (.xlsx extension)
##              52 (.xlsm extension)
"""