def ClearExcel(ws, cellRange, clearContents, clearFormats): # get origin and extent from range string # if no range supplied apply formatting to entire sheet if cellRange != None: origin = ws.Cells(bb.xlRange(cellRange)[1], bb.xlRange(cellRange)[0]) extent = ws.Cells(bb.xlRange(cellRange)[3], bb.xlRange(cellRange)[2]) else: origin = ws.Cells(ws.UsedRange.Row, ws.UsedRange.Column) extent = ws.Cells(ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row, ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column) if clearContents: ws.Range[origin, extent].ClearContents() if clearFormats: ws.Range[origin, extent].ClearFormats() return ws
def StyleData(ws, gs, cellRange):
# get range
if ":" in cellRange:
origin = ws.Cells(bb.xlRange(cellRange)[1], bb.xlRange(cellRange)[0])
extent = ws.Cells(bb.xlRange(cellRange)[3], bb.xlRange(cellRange)[2])
rng = ws.Range[origin, extent]
else:
# this is a named cell range
rng = ws.Range(cellRange)
# format cell fill style
if gs.fillStyle != None:
fs = gs.fillStyle
if fs.patternType != None:
rng.Interior.Pattern = fs.PatternType()
if fs.backgroundColor != None:
rng.Interior.Color = fs.BackgroundColor()
if fs.patternColor != None:
rng.Interior.PatternColor = fs.PatternColor()
# format cell text style
if gs.textStyle != None:
ts = gs.textStyle
if ts.name != None:
rng.Font.Name = ts.Name()
if ts.size != None:
rng.Font.Size = ts.Size()
if ts.color != None:
rng.Font.Color = ts.Color()
if ts.horizontalAlign != None:
rng.HorizontalAlignment = ts.HorizontalAlign()
if ts.verticalAlign != None:
rng.VerticalAlignment = ts.VerticalAlign()
if ts.bold != None:
rng.Font.Bold = ts.Bold()
if ts.italic != None:
rng.Font.Italic = ts.Italic()
if ts.underline != None:
rng.Font.Underline = ts.Underline()
if ts.strikethrough != None:
rng.Font.Strikethrough = ts.Strikethrough()
# format cell border style
if gs.borderStyle != None:
bs = gs.borderStyle
if bs.lineType != None:
rng.Borders.LineStyle = bs.LineType()
if bs.weight != None:
rng.Borders.Weight = bs.Weight()
if bs.color != None:
rng.Borders.Color = bs.Color()
return ws
def StyleData(ws, gs, cellRange): # get range if ":" in cellRange: origin = ws.Cells(bb.xlRange(cellRange)[1], bb.xlRange(cellRange)[0]) extent = ws.Cells(bb.xlRange(cellRange)[3], bb.xlRange(cellRange)[2]) rng = ws.Range[origin, extent] else: # this is a named cell range rng = ws.Range(cellRange) # format cell fill style if gs.fillStyle != None: fs = gs.fillStyle if fs.patternType != None: rng.Interior.Pattern = fs.PatternType() if fs.backgroundColor != None: rng.Interior.Color = fs.BackgroundColor() if fs.patternColor != None: rng.Interior.PatternColor = fs.PatternColor() # format cell text style if gs.textStyle != None: ts = gs.textStyle if ts.name != None: rng.Font.Name = ts.Name() if ts.size != None: rng.Font.Size = ts.Size() if ts.color != None: rng.Font.Color = ts.Color() if ts.horizontalAlign != None: rng.HorizontalAlignment = ts.HorizontalAlign() if ts.verticalAlign != None: rng.VerticalAlignment = ts.VerticalAlign() if ts.bold != None: rng.Font.Bold = ts.Bold() if ts.italic != None: rng.Font.Italic = ts.Italic() if ts.underline != None: rng.Font.Underline = ts.Underline() if ts.strikethrough != None: rng.Font.Strikethrough = ts.Strikethrough() # format cell border style if gs.borderStyle != None: bs = gs.borderStyle if bs.lineType != None: rng.Borders.LineStyle = bs.LineType() if bs.weight != None: rng.Borders.Weight = bs.Weight() if bs.color != None: rng.Borders.Color = bs.Color() return ws
def ReadData(ws, cellRange, byColumn):
# get range
if ":" in cellRange:
origin = ws.Cells(bb.xlRange(cellRange)[1], bb.xlRange(cellRange)[0])
extent = ws.Cells(bb.xlRange(cellRange)[3], bb.xlRange(cellRange)[2])
rng = ws.Range[origin, extent].Value2
else:
# this is a named cell range
rng = ws.Range(cellRange).Value2
if not byColumn:
dataOut = [[] for i in range(rng.GetUpperBound(0))]
for i in range(rng.GetLowerBound(0) - 1, rng.GetUpperBound(0), 1):
for j in range(rng.GetLowerBound(1) - 1, rng.GetUpperBound(1), 1):
dataOut[i].append(rng[i, j])
return dataOut
else:
dataOut = [[] for i in range(rng.GetUpperBound(1))]
for i in range(rng.GetLowerBound(1) - 1, rng.GetUpperBound(1), 1):
for j in range(rng.GetLowerBound(0) - 1, rng.GetUpperBound(0), 1):
dataOut[i].append(rng[j, i])
return dataOut
def ReadData(ws, cellRange, byColumn): # get range if ":" in cellRange: origin = ws.Cells(bb.xlRange(cellRange)[1], bb.xlRange(cellRange)[0]) extent = ws.Cells(bb.xlRange(cellRange)[3], bb.xlRange(cellRange)[2]) rng = ws.Range[origin, extent].Value2 else: # this is a named cell range rng = ws.Range(cellRange).Value2 if not byColumn: dataOut = [[] for i in range(rng.GetUpperBound(0))] for i in range(rng.GetLowerBound(0)-1, rng.GetUpperBound(0), 1): for j in range(rng.GetLowerBound(1)-1, rng.GetUpperBound(1), 1): dataOut[i].append(rng[i,j]) return dataOut else: dataOut = [[] for i in range(rng.GetUpperBound(1))] for i in range(rng.GetLowerBound(1)-1, rng.GetUpperBound(1), 1): for j in range(rng.GetLowerBound(0)-1, rng.GetUpperBound(0), 1): dataOut[i].append(rng[j,i]) return dataOut
if runMe: message = None try: errorReport = None message = "Success!" if filePath == None: # run excel in live mode xlApp = LiveStream() wb = xlApp.ActiveWorkbook if sheetName == None: ws = xlApp.ActiveSheet else: ws = xlApp.Sheets(sheetName) if not isinstance(cellRange, list): origin = ws.Cells(bb.xlRange(cellRange)[1], bb.xlRange(cellRange)[0]) extent = ws.Cells(bb.xlRange(cellRange)[3], bb.xlRange(cellRange)[2]) ConditionFormatCells(origin, extent, ws, formatConditions) else: for index, (range, format) in enumerate(zip(cellRange, formatConditions)): origin = ws.Cells(bb.xlRange(range)[1], bb.xlRange(range)[0]) extent = ws.Cells(bb.xlRange(range)[3], bb.xlRange(range)[2]) ConditionFormatCells(origin, extent, ws, format) else: try: xlApp = SetUp(Excel.ApplicationClass()) if os.path.isfile(unicode(filePath)): xlApp.Workbooks.open(unicode(filePath)) wb = xlApp.ActiveWorkbook ws = xlApp.Sheets(sheetName) if not isinstance(cellRange, list):
fill.Pattern = fStyle.PatternType() if fStyle.patternColor != None: fill.PatternColor = fStyle.PatternColor() if fStyle.backgroundColor != None: fill.Color = fStyle.BackgroundColor() return None if runMe: message = None try: xlApp = LiveStream() errorReport = None wb = xlApp.ActiveWorkbook ws = xlApp.Sheets(sheetName) # get chart size and location from range origin = ws.Cells(bb.xlRange(size)[1], bb.xlRange(size)[0]) extent = ws.Cells(bb.xlRange(size)[3], bb.xlRange(size)[2]) left = GetWidthHeight(origin, extent, ws)[0] top = GetWidthHeight(origin, extent, ws)[1] width = GetWidthHeight(origin, extent, ws)[2] height = GetWidthHeight(origin, extent, ws)[3] # get existing chart with same name or create new if ws.ChartObjects().Count > 0: for i in range(1, ws.ChartObjects().Count + 1, 1): if ws.ChartObjects().Item(i).Name == title: chartObject = ws.ChartObjects().Item(i) else: chartObjects = ws.ChartObjects() chartObject = chartObjects.Add(left, top, width, height) if title != None: chartObject.Name = title