Welcome, guest | Sign In | My Account | Store | Cart
from win32com.client import Dispatch
import os

STYLE_HEADING1 = "style_heading1"
STYLE_HEADING2 = "style_heading2"
STYLE_BORDER_BOTTOM = "style_border_bottom"
STYLE_GREY_CELL = "style_grey_cell"
STYLE_PALE_YELLOW_CELL = "style_pale_yellow_cell"
STYLE_ITALICS = "style_italics"

#these are the constant values in one particular version of EXCEL - if having problems, check your own
XL_CONST_EDGE_LEFT = 7
XL_CONST_EDGE_BOTTOM = 9
XL_CONST_CONTINUOUS = 1
XL_CONST_AUTOMATIC = -4105
XL_CONST_THIN = 2
XL_CONST_GRAY16 = 17
XL_CONST_SOLID = 1

RGB_PALE_GREY = 15132390 #Debug.Print RGB(230,230,230) in Excel Immediate window
RGB_PALE_YELLOW = 13565951 # RGB(255,255,206)

class ExcelWriter(object):
    """Excel class for creating spreadsheets - esp writing data and formatting them
    Based in part on #http://snippets.dzone.com/posts/show/2036,
    and http://www.markcarter.me.uk/computing/python/excel.html
    """
    def __init__(self, file_name, default_sheet_name, make_visible=False):
        """Open spreadsheet"""
        self.excelapp = Dispatch("Excel.Application")
        if make_visible:
            self.excelapp.Visible = 1 #fun to watch!
        self.excelapp.Workbooks.Add()
        self.workbook = self.excelapp.ActiveWorkbook
        self.file_name = file_name
        self.default_sheet = self.excelapp.ActiveSheet
        self.default_sheet.Name = default_sheet_name
    
    def getExcelApp(self):
        """Get Excel App for use"""
        return self.excelapp
    
    def addSheetAfter(self, sheet_name, index_or_name):
        """
        Add new sheet to workbook after index_or_name (indexing starts at 1).
        """
        sheets = self.workbook.Sheets
        sheets.Add(None, sheets(index_or_name)).Name = sheet_name #Sheets.Add(Before, After, Count, Type) - http://www.functionx.com/vbaexcel/Lesson07.htm
    
    def deleteSheet(self, sheet_name):
        """Delete named sheet"""
        #http://www.exceltip.com/st/Delete_sheets_without_confirmation_prompts_using_VBA_in_Microsoft_Excel/483.html
        sheets = self.workbook.Sheets
        self.excelapp.DisplayAlerts = False        
        sheets(sheet_name).Delete()        
        self.excelapp.DisplayAlerts = True
        
    def getSheet(self, sheet_name):
        """
        Get sheet by name.
        """
        return self.workbook.Sheets(sheet_name)
    
    def activateSheet(self, sheet_name):
        """
        Activate named sheet.
        """
        sheets = self.workbook.Sheets
        sheets(sheet_name).Activate() #http://mail.python.org/pipermail/python-win32/2002-February/000249.html
    
    def add2cell(self, row, col, content, sheet=None):
        """
        Add content to cell at row,col location.  
        NB only recommended for small amounts of data http://support.microsoft.com/kb/247412.
        """
        if sheet == None:
            sheet = self.default_sheet
        sheet.Cells(row,col).Value = content

    def addRow(self, row_i, data_tuple, start_col=1, sheet=None):
        """
        Add row in a single operation.  Takes a tuple per row.
        Much more efficient than cell by cell. http://support.microsoft.com/kb/247412.
        """
        if sheet == None:
            sheet = self.default_sheet  
        col_n = len(data_tuple)
        last_col = start_col + col_n - 1
        insert_range = self.getRangeByCells((row_i, start_col), (row_i, last_col), sheet)
        insert_range.Value = data_tuple
        
    def addMultipleRows(self, start_row, list_data_tuples, start_col=1, sheet=None):
        """
        Adds data multiple rows at a time, not cell by cell. Takes list of tuples
        e.g. cursor.fetchall() after running a query
        One tuple per row.
        Much more efficient than cell by cell or row by row. 
        http://support.microsoft.com/kb/247412.
        Returns next available row.
        """
        if sheet == None:
            sheet = self.default_sheet
        row_n = len(list_data_tuples)
        last_row = start_row + row_n - 1
        col_n = len(list_data_tuples[0])
        last_col = start_col + col_n - 1        
        insert_range = self.getRangeByCells((start_row, start_col), (last_row, last_col), sheet)
        insert_range.Value = list_data_tuples
        next_available_row = last_row + 1
        return next_available_row

    def getRangeByCells(self, (cell_start_row, cell_start_col), (cell_end_row, cell_end_col), sheet=None):
        """Get a range defined by cell start and cell end e.g. (1,1) A1 and (7,2) B7"""
        if sheet == None:
            sheet = self.default_sheet
        return sheet.Range(sheet.Cells(cell_start_row, cell_start_col), 
            sheet.Cells(cell_end_row, cell_end_col))
    
    def fitCols(self, col_start, col_end, sheet=None):
        """
        Fit colums to contents.
        """
        if sheet == None:
            sheet = self.default_sheet
        col_n = col_start
        while col_n <= col_end:
            self.fitCol(col_n, sheet)
            col_n = col_n + 1
    
    def fitCol(self, col_n, sheet=None):
        """
        Fit column to contents.
        """
        if sheet == None:
            sheet = self.default_sheet
        sheet.Range(sheet.Cells(1, col_n), sheet.Cells(1, col_n)).EntireColumn.AutoFit()
    
    def setColWidth(self, col_n, width, sheet=None):
        """
        Set column width.
        """
        if sheet == None:
            sheet = self.default_sheet
        sheet.Range(sheet.Cells(1, col_n), sheet.Cells(1, col_n)).ColumnWidth = width        
        
    def formatRange(self, range, style):
        """
        Add formatting to a cell/group of cells.
        To get methods etc record a macro in EXCEL and look at it.
        To get the value of Excel Constants such as xlEdgeLeft (7) or xlThin (2)
        type e.g. Debug.Print xlEdgeLeft in the Immediate window of the VBA editor and press enter.
        http://www.ureader.com/message/33389340.aspx
        For changing the pallete of 56 colours ref: http://www.cpearson.com/excel/colors.htm    
        """
        if style == STYLE_HEADING1:
            range.Font.Bold = True
            range.Font.Name = "Arial"
            range.Font.Size = 12
        elif style == STYLE_HEADING2:
            range.Font.Bold = True
            range.Font.Name = "Arial"
            range.Font.Size = 10.5
        elif style == STYLE_BORDER_BOTTOM:
            range.Borders(XL_CONST_EDGE_BOTTOM).LineStyle = XL_CONST_CONTINUOUS
            range.Borders(XL_CONST_EDGE_BOTTOM).Weight = XL_CONST_THIN
            range.Borders(XL_CONST_EDGE_BOTTOM).ColorIndex = XL_CONST_AUTOMATIC
        elif style == STYLE_GREY_CELL:
            self.resetColorPallet(1, RGB_PALE_GREY)
            range.Interior.ColorIndex = 1
            range.Interior.Pattern = XL_CONST_SOLID
        elif style == STYLE_PALE_YELLOW_CELL:
            self.resetColorPallet(1, RGB_PALE_YELLOW)
            range.Interior.ColorIndex = 1
            range.Interior.Pattern = XL_CONST_SOLID
        elif style == STYLE_ITALICS:
            range.Font.Italic = True
        else:
            raise Exception, "Style '%s' has not been defined" % style        

    def resetColorPallet(self, color_index, color):
        """
        Reset indexed color in pallet (limited to 1-56).
        Get color values by Debug.Print RGB(230,230,230) in Excel Immediate window
        """
        if color_index < 1 or color_index > 56:
            raise Exception, "Only indexes between 1 and 56 are available in the Excel color pallet."
        colors_tup = self.workbook.Colors #immutable of course
        colors_list = list(colors_tup)
        colors_list[color_index-1] = RGB_PALE_GREY #zero-based in list but not Excel pallet
        new_colors_tup = tuple(colors_list)
        self.workbook.Colors = new_colors_tup

    def mergeRange(self, range):
        """Merge range"""
        range.Merge()

    def save(self):
        """Save spreadsheet as filename - wipes if existing"""
        if os.path.exists(self.file_name):
            os.remove(self.file_name)
        self.workbook.SaveAs(self.file_name)

    def close(self):
        """Close spreadsheet resources"""
        self.workbook.Saved = 0 #p.248 Using VBA 5
        self.workbook.Close(SaveChanges=0) #to avoid prompt
        self.excelapp.Quit()
        self.excelapp.Visible = 0 #must make Visible=0 before del self.excelapp or EXCEL.EXE remains in memory.
        del self.excelapp

History

  • revision 4 (14 years ago)
  • previous revisions are not available