Skip to content

Instantly share code, notes, and snippets.

@pythonexcels
pythonexcels / row_height.py
Created September 19, 2012 07:37
Set Excel spreadsheet row height with Python
#
# Set row heights and align text within the cell
#
import win32com.client as win32
excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Add()
ws = wb.Worksheets("Sheet1")
ws.Range("A1:A2").Value = "1 line"
ws.Range("B1:B2").Value = "Two\nlines"
ws.Range("C1:C2").Value = "Three\nlines\nhere"
@pythonexcels
pythonexcels / Macro1
Created September 19, 2012 08:01
Excel Macro
Sub Macro1()
'
' Macro1 Macro
'
Range("B2:K22").Select
Selection.FormatConditions.AddColorScale ColorScaleType:=3
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
xlConditionValueLowestValue
With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
@pythonexcels
pythonexcels / conditionalformatting.py
Created September 19, 2012 08:03
Conditional Formatting in Excel using Python
#
# conditionalformatting.py
# Create two tables and apply Conditional Formatting
#
import win32com.client as win32
excel = win32.gencache.EnsureDispatch('Excel.Application')
#excel.Visible = True
wb = excel.Workbooks.Add()
ws = wb.Worksheets('Sheet1')
ws.Range("B2:K2").Value = [i for i in range(1,11)]
@pythonexcels
pythonexcels / erpdata.py
Created September 22, 2012 20:43
Process Corporate ERP Data
#
# erpdata.py: Load raw EPR data and clean up header info
#
import win32com.client as win32
import sys
excel = win32.gencache.EnsureDispatch('Excel.Application')
#excel.Visible = True
try:
wb = excel.Workbooks.Open('ABCDCatering.xls')
except:
@pythonexcels
pythonexcels / Macro2
Created September 22, 2012 21:25
Excel Macro
'
' Macro2 Macro
'
'
Selection.CurrentRegion.Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet2!R1C1:R791C13", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet3!R3C1", TableName:="PivotTable1", DefaultVersion _
@pythonexcels
pythonexcels / erpdatapivot.py
Created September 22, 2012 21:31
Excel ERP Data Pivot Tables using Python
#
# erpdatapivot.py:
# Load raw EPR data, clean up header info and
# build 5 pivot tables
#
import win32com.client as win32
win32c = win32.constants
import sys
import itertools
tablecount = itertools.count(1)
@pythonexcels
pythonexcels / erppivotextended.py
Created September 22, 2012 21:46
Excel ERP Data Pivot Tables using Python (Extended)
#
# erppivotextended.py:
# Load raw EPR data, clean up header info,
# insert additional data fields and build 5 pivot tables
#
import win32com.client as win32
win32c = win32.constants
import sys
import itertools
tablecount = itertools.count(1)
@pythonexcels
pythonexcels / erppivotdragdrop.py
Created September 22, 2012 22:03
Excel ERP Data Pivot Tables using Python (Minimal GUI Interface)
#
# erppivotdragdrop.py:
# Load raw EPR data, clean up header info,
# insert additional data fields and build 5 pivot tables
# Support drag and drop of multiple spreadsheets
#
import win32com.client as win32
win32c = win32.constants
import sys
import itertools
@pythonexcels
pythonexcels / payroll_steve_jeff.py
Created September 22, 2012 23:23
Extract Payrate from Multiple Spreadsheets
#
# payroll_steve_jeff.py
# Report payrates for two employees across multiple spreadsheets
#
import win32com.client as win32
import glob
import os
xlfiles = sorted(glob.glob("*.xls"))
print "Reading %d files..."%len(xlfiles)