Over a million developers have joined DZone.

Script Excel From Python

· Web Dev Zone

Easily build powerful user management, authentication, and authorization into your web and mobile applications. Download this Forrester report on the new landscape of Customer Identity and Access Management, brought to you in partnership with Stormpath.


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.
    """
    self.app.Workbooks.Add(filename)

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

  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.
    """
    self.app.ActiveWorkbook.Save()

  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):
      os.remove(filename)
    self.app.ActiveWorkbook.SaveAs(filename)

  def print_out(self):
    """
    Print the active workbook.
    """
    self.app.Application.PrintOut()

  def close(self):
    """
    Close the active workbook.
    """
    self.app.ActiveWorkbook.Close()

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


The Web Dev Zone is brought to you by Stormpath—offering a complete, pre-built User Management API for building web and mobile applications, and APIs. Download our new whitepaper: "Build Versus Buy: Customer Identity Management for Web and Mobile Applications".

Topics:
python

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

{{ parent.tldr }}

{{ parent.urlSource.name }}