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
Download
Copy to clipboard1 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
Tags: database
