Over a million developers have joined DZone.
Platinum Partner

Script Excel From Python

· Web Dev Zone

The Web Dev Zone is brought to you in partnership with Mendix.  Discover how IT departments looking for ways to keep up with demand for business apps has caused a new breed of developers to surface - the Rapid Application Developer.

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()

The Web Dev Zone is brought to you in partnership with Mendix.  Learn more about The Essentials of Digital Innovation and how it needs to be at the heart of every organization.


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

{{ parent.tldr }}

{{ parent.urlSource.name }}