Over a million developers have joined DZone.

Script Excel From Python

DZone's Guide to

Script Excel From Python

· Web Dev Zone
Free Resource

Tips, tricks and tools for creating your own data-driven app, brought to you in partnership with Qlik.

from win32com.client import constants, Dispatch
import pythoncom
import os

borderTop = 3
borderBottom = 4
borderLeft = 1
borderRight = 2
borderSolid = 1
borderDashed = 2
borderDotted = 3
colorBlack = 1
directionUp = -4162
directionDown = -4121
directionLeft = -4131
directionRight = -4152

class ExcelDocument(object):
  Some convenience methods for Excel documents accessed
  through COM.

  def __init__(self, visible=False):
    self.app = Dispatch("Excel.Application")
    self.app.Visible = visible
    self.sheet = 1

  def new(self, filename=None):
    Create a new Excel workbook. If 'filename' specified,
    use the file as a template.

  def open(self, filename):
    Open an existing Excel workbook for editing.

  def set_sheet(self, sheet):
    Set the active worksheet.
    self.sheet = sheet

  def get_range(self, range):
    Get a range object for the specified range or single cell.
    return self.app.ActiveWorkbook.Sheets(self.sheet).Range(range)

  def set_value(self, cell, value=''):
    Set the value of 'cell' to 'value'.
    self.get_range(cell).Value = value

  def get_value(self, cell):
    Get the value of 'cell'.
    value = self.get_range(cell).Value
    if isinstance(value, tuple):
      value = [v[0] for v in value]
    return value

  def set_border(self, range, side, line_style=borderSolid, color=colorBlack):
    Set a border on the specified range of cells or single cell.
    'range' = range of cells or single cell
    'side' = one of borderTop, borderBottom, borderLeft, borderRight
    'line_style' = one of borderSolid, borderDashed, borderDotted, others?
    'color' = one of colorBlack, others?
    range = self.get_range(range).Borders(side)
    range.LineStyle = line_style
    range.Color = color

  def sort(self, range, key_cell):
    Sort the specified 'range' of the activeworksheet by the
    specified 'key_cell'.
    range.Sort(Key1=self.get_range(key_cell), Order1=1, Header=0, OrderCustom=1, MatchCase=False, Orientation=1)

  def hide_row(self, row, hide=True):
    Hide the specified 'row'.
    Specify hide=False to show the row.
    self.get_range('a%s' % row).EntireRow.Hidden = hide

  def hide_column(self, column, hide=True):
    Hide the specified 'column'.
    Specify hide=False to show the column.
    self.get_range('%s1' % column).EntireColumn.Hidden = hide

  def delete_row(self, row, shift=directionUp):
    Delete the entire 'row'.
    self.get_range('a%s' % row).EntireRow.Delete(Shift=shift)

  def delete_column(self, column, shift=directionLeft):
    Delete the entire 'column'.
    self.get_range('%s1' % column).EntireColumn.Delete(Shift=shift)

  def fit_column(self, column):
    Resize the specified 'column' to fit all its contents.
    self.get_range('%s1' % column).EntireColumn.AutoFit()

  def save(self):
    Save the active workbook.

  def save_as(self, filename, delete_existing=False):
    Save the active workbook as a different filename.
    If 'delete_existing' is specified and the file already
    exists, it will be deleted before saving.
    if delete_existing and os.path.exists(filename):

  def print_out(self):
    Print the active workbook.

  def close(self):
    Close the active workbook.

  def quit(self):
    Quit Excel.
    return self.app.Quit()

Explore data-driven apps with less coding and query writing, brought to you in partnership with Qlik.


Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}