Welcome, guest | Sign In | My Account | Store | Cart

Class for creating Excel spreadsheets - esp writing data and formatting them. NB OpenOffice Calc will be easily able to open the outputs too.

Python, 209 lines
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
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

If you want to export data into a newly-created Excel spreadsheet, and format some of the cells (or groups of cells), this class makes it easy.

e.g. assuming you save the code in a file called excel_writer:

import excel_writer file_name = "write a file name here.xls" writer = excel_writer.ExcelWriter(file_name, "Data", make_visible=True) title_range = writer.getRangeByCells((1, 1), (1, j)) #where j = number of columns writer.formatRange(title_range, excel_writer.STYLE1)

Then, perhaps within a loop of rows returned from a MySQL database (r refers to row number, c to column number, and content is what you want to put in the cell):

writer.add2cell(r, c, content)

Finally, save the file and clean up:

writer.save() writer.close()

NB addRow and addMultipleRows are much, much faster than add2cell