parasol.Excel

1 from win32com.client import Dispatch 2 from excel_const import constants 3 import string 4 import xlChFormula 5 import sys, os 6

7 -def squareUpRS( rs, pad='' ):

8 '''make the input data list of lists such that each row is the same length 9 add "pad" to short rows''' 10 11 maxL = len(rs[0]) 12 minL = maxL 13 for row in rs: 14 L = len(row) 15 maxL = max(maxL, L) 16 minL = min(minL, L) 17 18 #print 'in squareUpRS, maxL=',maxL,' minL=',minL 19 20 if minL<maxL: 21 rs = list(rs) 22 for i,row in enumerate(rs): 23 #print i,rs[i] 24 #padL = [pad]*(maxL-len(row)) 25 #print ' padL=',padL 26 rs[i] = list(row) + [pad]*(maxL-len(row)) 27 #print i,rs[i] 28 return rs

29 30 31 32

33 -class ExcelWrap:

34 numWkBooks = 0

35 - def __init__(self,xlsFile="", Visible=1):

36 self.xlApp = Dispatch("Excel.Application") 37 self.xlApp.Visible = Visible 38 ExcelWrap.numWkBooks = ExcelWrap.numWkBooks + 1 39 40 # I don't think the sheet and column lists should be mapped 41 # (unfortunately, they are right now) 42 self.sheetList = [] 43 self.chartList = [] 44 self.chartNColumns = [] 45 self.chartNRows = [] 46 self.leaveOpen = 1 47 self.nRows = 0 48 self.nColumns = 0 49 self.formula = xlChFormula.xlChFormula() # a scratch variable 50 51 if len(xlsFile)>0: # opening an existing XLS file 52 xlsFile = os.path.abspath(xlsFile) # Excel likes absolute path names 53 self.xlApp.Workbooks.Open(xlsFile) 54 self.initFromXLSFile() 55 self.xlBook = None 56 else: # making a new XLS file 57 self.xlBook = self.xlApp.Workbooks.Add() 58 self.xlSheet = self.xlApp.Sheets(1) 59 self.sheetList.append( self.xlSheet )

60

61 - def closeQuietly(self):

62 self.xlApp.DisplayAlerts = 0 # Allow Quick Close without Save Message

63

64 - def closeQuestion(self):

65 self.xlApp.DisplayAlerts = 1 # Get Save Question When Closing

66 67

68 - def __del__(self):

69 if (__name__ != "__main__") and (not self.leaveOpen): 70 # i.e. only close if this is not a Self Test 71 if self.xlApp != None: self.close()

72

73 - def getSheetNumberFromName(self, shtName='Sheet1'):

74 '''get workbook sheet number from name''' 75 ns=1 76 isheet = 1 # 1-based index 77 for i in range(1, self.xlApp.Sheets.Count+1): 78 if self.xlApp.Sheets(i).Type == constants.xlWorksheet: 79 if shtName==str(self.xlApp.Sheets(i).Name): ns = isheet 80 #print 'sheet #%i = %s'%(isheet,self.xlApp.Sheets(i).Name) 81 isheet += 1 82 83 #print 'selected sheet =',ns 84 return ns # for 1-based index

85

86 - def getChartSheetNumberUsingSheetData(self, datashtName='Sheet1'):

87 '''get chart sheet number which uses data from sheet name''' 88 ns = 0 89 for i in range(1, self.xlApp.Sheets.Count+1): #sheets use 1-based index 90 try: # check all sheets even if previous sheets raise an exception 91 if self.xlApp.Sheets(i).Type != constants.xlWorksheet: 92 # must be a chart if not a worksheet 93 f = xlChFormula.xlChFormula( self.xlApp.Sheets(i).SeriesCollection(1).Formula) 94 # returns last chart which uses data from sheet 95 if datashtName==f.labelSht: ns = i 96 except: 97 pass 98 #print datashtName, ns 99 return ns

100

101 - def initFromXLSFile(self):

102 '''initialize from existing XLS file. 103 (file has already been opened and assigned to self.xlApp)''' 104 105 #print "now in initFromXLSFile" 106 self.xlSheet = None 107 self.chart = None 108 109 for ns in range(1, self.xlApp.Sheets.Count+1): #sheets use 1-based index 110 try: 111 #print "starting loop",ns 112 if self.xlApp.Sheets(ns).Type == constants.xlWorksheet: 113 Ncolumns, Nrows = xlChFormula.getNcolumnsNrowsFromRange(\ 114 self.xlApp.Sheets(ns).UsedRange.AddressLocal) 115 116 # check that sheet has info in it and should be added to sheet list 117 if Ncolumns>0 and Nrows>0: 118 self.xlSheet = self.xlApp.Sheets(ns) 119 self.sheetList.append( self.xlSheet ) 120 self.nRows = Nrows 121 self.nColumns = Ncolumns 122 123 # chart and sheet lists are mapped 124 self.chartNColumns.append( self.nColumns ) 125 self.chartNRows.append( self.nRows ) 126 127 #print "calling getChartSheetNumberUsingSheetData" 128 nc = self.getChartSheetNumberUsingSheetData(self.xlApp.Sheets(ns).Name) 129 #print "found chart for sheet",ns,"chart=",nc 130 #print "chart type =",self.xlApp.Sheets(nc).ChartType," =? ",constants.xlXYScatterLines 131 if (nc>0): ###/// and (self.xlApp.Sheets(nc).ChartType==constants.xlXYScatterLines): 132 #print "assigning chart for sheet",ns,"chart=",nc 133 self.chart = self.xlApp.Sheets(nc) 134 self.chartList.append( self.chart ) 135 #else: 136 # self.chartList.append( None ) 137 138 except: 139 pass

140

141 - def getRangeValueFromRowCol(self, ULrow=1, ULcol=1, LRrow=9, LRcol=9):

142 cUL = self.formula.makeColLocation( NColumn=ULcol, NRow=ULrow) 143 cLR = self.formula.makeColLocation( NColumn=LRcol, NRow=LRrow) 144 return self.getRangeValue( cUL, cLR )

145

146 - def getRangeValue(self, ULcell='$A$1', LRcell='$D$5'):

147 try: 148 r = self.xlSheet.Range(ULcell, LRcell) 149 return r.Value 150 except: 151 return ''

152

153 - def getCellValue(self, cell='$A$1'):

154 try: 155 r = self.xlSheet.Range(cell, cell) 156 return str( r.Value ) 157 except: 158 return ''

159

160 - def getRowColCellValue(self, NColumn=1, NRow=1):

161 c = self.formula.makeColLocation( NColumn=NColumn, NRow=NRow) 162 return self.getCellValue(c)

163

164 - def getColumnName(self, NColumn=1):

165 if NColumn<=self.nColumns: 166 #///cL = '$'+ self.formula.excelColLetter( NColumn )+ '$1' 167 cL = self.formula.makeColLocation( NColumn=NColumn, NRow=1) 168 r = self.xlSheet.Range(cL, cL) 169 return str( r.Value ) 170 else: 171 return 'None'

172

173 - def getAllColumnNames(self):

174 cL = self.formula.makeColLocation( NColumn=self.nColumns, NRow=1) 175 r = self.xlSheet.Range("$a$1", cL) 176 return r.Value[0]

177

178 - def addNewSeries(self, NChart=1, NSheet=1, xColumn=1, yColumn=2):

179 # use 1-based indeces for NChart and NSheet 180 181 sh = self.sheetList[NSheet-1] 182 ch = self.chartList[NChart-1] 183 series = ch.SeriesCollection().NewSeries() 184 Ncolumns, Nrows = xlChFormula.getNcolumnsNrowsFromRange( sh.UsedRange.AddressLocal ) 185 L = self.formula.makeColRange(NColumn=xColumn, fromRow=2, toRow=Nrows) 186 rx = sh.Range(L) 187 series.XValues = rx 188 189 L = self.formula.makeColRange(NColumn=yColumn, fromRow=2, toRow=Nrows) 190 ry = sh.Range(L) 191 series.Values = ry 192 193 cL = self.formula.makeColLocation( NColumn=yColumn, NRow=1) 194 r = sh.Range(cL) 195 series.Name = r

196

197 - def addNewSeriesToCurrentSheetChart(self, xColumn=1, yColumn=2):

198 199 sh = self.xlSheet 200 ch = self.chart 201 series = ch.SeriesCollection().NewSeries() 202 Ncolumns, Nrows = xlChFormula.getNcolumnsNrowsFromRange( sh.UsedRange.AddressLocal ) 203 L = self.formula.makeColRange(NColumn=xColumn, fromRow=2, toRow=Nrows) 204 rx = sh.Range(L) 205 series.XValues = rx 206 207 L = self.formula.makeColRange(NColumn=yColumn, fromRow=2, toRow=Nrows) 208 ry = sh.Range(L) 209 series.Values = ry 210 211 cL = self.formula.makeColLocation( NColumn=yColumn, NRow=1) 212 r = sh.Range(cL) 213 series.Name = r

214 215

216 - def changeSeriesOnChart(self, NChart=1, NSeries=1, NSheet=1, xColumn=1, yColumn=2):

217 # use 1-based indeces for NChart and NSheet 218 sh = self.sheetList[NSheet-1] 219 ch = self.chartList[NChart-1] 220 Ncolumns, Nrows = xlChFormula.getNcolumnsNrowsFromRange( sh.UsedRange.AddressLocal ) 221 L = self.formula.makeColRange(NColumn=xColumn, fromRow=2, toRow=Nrows) 222 rx = sh.Range(L) 223 ch.XYGroups(1).SeriesCollection(NSeries).XValues = rx 224 225 L = self.formula.makeColRange(NColumn=yColumn, fromRow=2, toRow=Nrows) 226 ry = sh.Range(L) 227 ch.XYGroups(1).SeriesCollection(NSeries).Values = ry 228 229 cL = self.formula.makeColLocation( NColumn=yColumn, NRow=1) 230 r = sh.Range(cL) 231 ch.SeriesCollection(NSeries).Name = r

232

233 - def changeSeriesXValuesColumn(self, NColumn=2, NSeries=1):

234 if NColumn<=self.nColumns: 235 L = self.formula.makeColRange(NColumn=NColumn, fromRow=2, toRow=self.nRows) 236 r = self.xlSheet.Range(L) 237 self.chart.XYGroups(1).SeriesCollection(NSeries).XValues = r 238 self.labelXAxis( self.getAllXNames() )

239 240 #///////////// Formula approach seems buggy 241 #self.formula.setFormula(self.chart.XYGroups(1).SeriesCollection(NSeries).Formula ) 242 #self.formula.setXColumn( col=NColumn, topRow=2, botRow=self.nRows, Sheet='') 243 #self.chart.XYGroups(1).SeriesCollection(NSeries).Formula = self.formula.getFormula() 244

245 - def setAllXValueColumns(self, NColumn=1, ZeroBased=0):

246 try: 247 ns = 1 248 c = NColumn 249 if ZeroBased:c = c + 1 250 while 1: 251 L = self.formula.makeColRange(NColumn=c, fromRow=2, toRow=self.nRows) 252 r = self.xlSheet.Range(L) 253 self.chart.XYGroups(1).SeriesCollection(ns).XValues = r 254 ns = ns + 1 255 except: 256 pass 257 self.labelXAxis( self.getRowColCellValue( NColumn=c, NRow=1) )

258

259 - def changePlottedColumn(self, NColumn=2, NSeries=1):

260 try: 261 if NColumn<=self.nColumns: 262 L = self.formula.makeColRange(NColumn=NColumn, fromRow=2, toRow=self.nRows) 263 r = self.xlSheet.Range(L) 264 self.chart.XYGroups(1).SeriesCollection(NSeries).Values = r 265 266 cL = self.formula.makeColLocation( NColumn=NColumn, NRow=1) 267 r = self.xlSheet.Range(cL) 268 self.chart.SeriesCollection(NSeries).Name = r 269 #////////// Formula approach seems buggy 270 #self.formula.setFormula(self.chart.XYGroups(1).SeriesCollection(NSeries).Formula ) 271 #self.formula.setValColumn( col=NColumn, topRow=2, botRow=self.nRows, Sheet='') 272 #self.formula.setLabel( col=NColumn, row=1, Sheet='') 273 #self.chart.XYGroups(1).SeriesCollection(NSeries).Formula = self.formula.getFormula() 274 except: pass

275

276 - def formatFocusedChart(self):

277 278 self.chart.ChartType = constants.xlXYScatterLines 279 self.chart.SizeWithWindow = 1 280 281 self.chart.PlotArea.Interior.ColorIndex = constants.xlNone 282 283 self.chart.HasTitle = 1 # Need to enable title before setting text 284 self.chart.Axes( constants.xlCategory, constants.xlPrimary).HasTitle = 1 285 286 self.chart.Axes( constants.xlValue, constants.xlPrimary).HasTitle = 1 287 288 self.chart.Axes(1).HasMajorGridlines = 1 # turn on grid for x axis 289 self.chart.Axes(1).MajorGridlines.Border.LineStyle = constants.xlDot # make grid style dots 290 self.chart.Axes(2).MajorGridlines.Border.LineStyle = constants.xlDot # make grid style dots

291 292

293 - def makeNewChartOfPlottedColumns(self, cols=(2,), ZeroBased=0, chartName='' ):

294 '''use cols tuple to set all plotted columns 295 if Number of Columns is wrong, correct''' 296 297 self.chart = self.xlApp.Charts.Add() 298 self.chartList.append( self.chart ) 299 self.chartNColumns.append( self.nColumns ) 300 self.chartNRows.append( self.nRows ) 301 302 self.formatFocusedChart() 303 304 self.setAllPlottedColumns( cols=cols, ZeroBased=ZeroBased ,chartName=chartName)

305 306

307 - def setAllPlottedColumns(self, cols=(2,), ZeroBased=0 , chartName=''):

308 '''use cols tuple to set all plotted columns 309 if Number of Columns is wrong, correct''' 310 if len(cols)>0: self.setNumberOfPlotCurves( len(cols) ) 311 ns = 1 312 name = '' 313 for c in cols: 314 if ZeroBased:c = c + 1 315 self.changePlottedColumn(NColumn=c, NSeries=ns) 316 ns = ns + 1 317 if len(name)>0: name = name + ", " 318 name = name + str( self.getColumnName(c) ) 319 self.labelPrimaryYAxis(yLabel=name) 320 self.labelXAxis( self.getColumnName(1) ) 321 if len(chartName)>0: self.chart.Location(Where=1, Name=chartName)

322

323 - def focusSheet(self, N):

324 '''use 1-based index to Sheets''' 325 #print 'focusing on sheet #',N 326 self.xlSheet = self.sheetList[N-1] 327 #self.chart = self.chartList[N-1] 328 #self.nColumns = self.chartNColumns[N-1] 329 #self.nRows = self.chartNRows[N-1] 330 self.xlSheet.Activate()

331

332 - def focusSheetByName(self, shtName='Sheet1'):

333 N = self.getSheetNumberFromName( shtName=shtName) 334 self.focusSheet(N)

335

336 - def focusChart(self, N):

337 '''use 1-based index to Charts''' 338 self.xlSheet = self.sheetList[N-1] 339 self.chart = self.chartList[N-1] 340 self.nColumns = self.chartNColumns[N-1] 341 self.nRows = self.chartNRows[N-1] 342 self.chart.Activate()

343

344 - def labelPrimaryYAxis(self, yLabel="Vertical Axis"):

345 self.chart.Axes( constants.xlValue, constants.xlPrimary).HasTitle = 1 346 self.chart.Axes( constants.xlValue, constants.xlPrimary).AxisTitle.Characters.Text = yLabel 347

348 - def getAllXNames(self):

349 xLabels = {} 350 allXNames = '' 351 try: 352 ns = 1 353 while 1: 354 self.formula.setFormula(self.chart.XYGroups(1).SeriesCollection(ns).Formula) 355 ns = ns + 1 356 xLabels[self.getCellValue( cell=self.formula.labelLoc )] = 1 357 except: 358 pass 359 for k in xLabels.keys(): 360 if len(allXNames)>0:allXNames = allXNames + ', ' 361 allXNames = allXNames + k 362 return allXNames

363 364

365 - def labelXAxis(self, xLabel="Horizontal Axis"):

366 self.chart.Axes( constants.xlCategory, constants.xlPrimary).HasTitle = 1 367 self.chart.Axes( constants.xlCategory, constants.xlPrimary).AxisTitle.Characters.Text = xLabel

368

369 - def changePlotTitle(self, title="Data from Python Script"):

370 self.chart.HasTitle = 1 # Need to enable title before setting text 371 self.chart.ChartTitle.Characters.Text = title

372

373 - def setPlotTitleSize(self, pointSize=20, bold=0):

374 self.chart.HasTitle = 1 # Need to enable title before setting text 375 self.chart.ChartTitle.Characters.Font.Size = pointSize 376 self.chart.ChartTitle.Characters.Font.Bold = bold

377 378

379 - def putSeriesOnSecondary(self, J, y2Label=""):

380 '''use 1-based index to Sheets''' 381 try: 382 self.chart.SeriesCollection(J).AxisGroup = constants.xlSecondary 383 if len(y2Label)>0: 384 self.chart.Axes( constants.xlValue, constants.xlSecondary).HasTitle = 1 385 self.chart.Axes( constants.xlValue, constants.xlSecondary).AxisTitle.Characters.Text = y2Label 386 except: 387 pass

388

389 - def putSeriesOnPrimary(self, J):

390 '''use 1-based index to Sheets''' 391 try: 392 self.chart.SeriesCollection(J).AxisGroup = constants.xlPrimary 393 except: 394 pass

395

396 - def close(self):

397 if self.xlBook != None: 398 self.xlApp.DisplayAlerts=0 # Allow Quick Close without Save Message 399 self.xlBook.Close() 400 self.xlBook = None 401 self.xlApp.DisplayAlerts=1 402 ExcelWrap.numWkBooks = ExcelWrap.numWkBooks - 1 403 if ExcelWrap.numWkBooks==0: self.xlApp.Quit() 404 self.xlApp = None

405

406 - def setLineStyle(self, NSeries=1, style=1):

407 if style==0: s = constants.xlLineStyleNone 408 elif style==1: s = constants.xlContinuous 409 elif style==2: s = constants.xlDash 410 elif style==3: s = constants.xlDashDot 411 elif style==4: s = constants.xlDashDotDot 412 elif style==5: s = constants.xlDot 413 elif style==6: s = constants.xlDouble 414 elif style==7: s = constants.xlSlantDashDot 415 else:s = constants.xlContinuous 416 417 try: 418 self.chart.SeriesCollection(NSeries).Border.LineStyle = s 419 return 1 420 except: 421 return 0

422

423 - def setLineStyles(self, style=1):

424 N = 1 425 while self.setLineStyle(NSeries=N, style=style): 426 N = N + 1

427

428 - def setLineThickness(self, NSeries=1, thickness=1):

429 t=constants.xlMedium 430 if thickness<=0: 431 return self.setLineStyle( NSeries=NSeries, style=0) 432 elif thickness==1: t=constants.xlHairline 433 elif thickness==2: t=constants.xlThin 434 elif thickness==3: t=constants.xlMedium 435 else: t=constants.xlThick 436 try: 437 self.setLineStyle( NSeries=NSeries, style=1) 438 self.chart.SeriesCollection(NSeries).Border.Weight = t 439 return 1 440 except: 441 return 0

442

443 - def setLineThicknesses(self, thickness=1):

444 N = 1 445 while self.setLineThickness(NSeries=N, thickness=thickness): 446 N = N + 1

447

448 - def setSeriesColorIndex(self, NSeries=1, colorIndex=1):

449 try: 450 self.chart.SeriesCollection(NSeries).Border.ColorIndex = colorIndex 451 if self.chart.SeriesCollection(NSeries).MarkerStyle != constants.xlMarkerStyleNone: 452 self.chart.SeriesCollection(NSeries).MarkerBackgroundColorIndex = colorIndex 453 self.chart.SeriesCollection(NSeries).MarkerForegroundColorIndex = colorIndex 454 return 1 455 except: 456 return 0

457

458 - def setSeriesColor(self, NSeries=1, red=255, green=0, blue=0):

459 try: 460 RevRGB= int(red) + int(green * 256) + int(blue * 65536) 461 462 self.chart.SeriesCollection(NSeries).Border.Color = RevRGB 463 if self.chart.SeriesCollection(NSeries).MarkerStyle != constants.xlMarkerStyleNone: 464 self.chart.SeriesCollection(NSeries).MarkerBackgroundColor = RevRGB 465 self.chart.SeriesCollection(NSeries).MarkerForegroundColor = RevRGB 466 #print 'RevRGB',RevRGB 467 return 1 468 except: 469 return 0

470 471

472 - def setMarkerSize(self, NSeries=1, size=4):

473 try: 474 self.chart.SeriesCollection(NSeries).MarkerSize = size 475 return 1 476 except: 477 return 0

478

479 - def setMarkerSizes(self, size=4):

480 N = 1 481 while self.setMarkerSize(NSeries=N, size=size): 482 N = N + 1

483 484

485 - def turnMarkerOnOff(self, NSeries=1, showPoints=0):

486 try: 487 if showPoints: 488 self.chart.SeriesCollection(NSeries).MarkerStyle = constants.xlMarkerStyleAutomatic 489 else: 490 self.chart.SeriesCollection(NSeries).MarkerStyle = constants.xlMarkerStyleNone 491 return 1 492 except: 493 return 0

494

495 - def turnMarkersOnOff(self, showPoints=0):

496 N = 1 497 while self.turnMarkerOnOff(NSeries=N, showPoints=showPoints): 498 N = N + 1

499

500 - def setXScaleType(self, log=1):

501 if log: 502 self.chart.Axes(1).ScaleType = constants.xlLogarithmic 503 #self.chart.Axes(1).AxisBetweenCategories = 1 504 self.chart.Axes(1).MinorTickMark = constants.xlTickMarkCross 505 self.chart.Axes(1).HasMinorGridlines = 1 # turn on grid for x axis 506 self.chart.Axes(1).MinorGridlines.Border.LineStyle = constants.xlDot # make grid style dots 507 self.chart.Axes(1).MajorGridlines.Border.LineStyle = constants.xlContinuous 508 else: 509 self.chart.Axes(1).ScaleType = constants.xlScaleLinear

510

511 - def setYScaleType(self, log=1):

512 if log: 513 self.chart.Axes(2).ScaleType = constants.xlLogarithmic 514 #self.chart.Axes(2).AxisBetweenCategories = 1 515 self.chart.Axes(2).MinorTickMark = constants.xlTickMarkCross 516 self.chart.Axes(2).HasMinorGridlines = 1 # turn on grid for x axis 517 self.chart.Axes(2).MinorGridlines.Border.LineStyle = constants.xlDot # make grid style dots 518 self.chart.Axes(2).MajorGridlines.Border.LineStyle = constants.xlContinuous 519 else: 520 self.chart.Axes(2).ScaleType = constants.xlScaleLinear

521

522 - def setXrange(self, xmin=1.0, xmax=10.0):

523 self.chart.Axes(1).MinimumScale=xmin 524 self.chart.Axes(1).MaximumScale=xmax

525

526 - def setYrange(self, ymin=1.0, ymax=10.0):

527 self.chart.Axes(2).MinimumScale=ymin 528 self.chart.Axes(2).MaximumScale=ymax

529 530

531 - def setYrangeOnSecondary(self, ymin=1.0, ymax=10.0):

532 self.chart.Axes(constants.xlValue, constants.xlSecondary).MinimumScale=ymin 533 self.chart.Axes(constants.xlValue, constants.xlSecondary).MaximumScale=ymax

534 535

536 - def addTextBox(self,text='comment',left=100,top=100, width=50, height=50, 537 border=3, auto=1, transparency=0.3, isBold=0, fontSize=0):

538 tb = self.chart.TextBoxes().Add(left,top,width,height) 539 tb.Text=text[:255] # try limiting length to prevent errors 540 tb.Border.Weight=border 541 if isBold: 542 tb.Font.IsBold = 1 543 if fontSize: 544 tb.Font.Size = fontSize 545 if auto: 546 tb.AutoSize=True 547 tb.Interior.Color = constants.xlColorIndexAutomatic 548 549 w = self.chart.PlotArea.Width 550 h = self.chart.PlotArea.Height 551 552 tb.Left = int( w/4) 553 tb.Top = int( h/4 ) 554 555 tb.ShapeRange.Fill.Transparency = transparency

556 557

558 - def setNumberOfPlotCurves(self, NCurves=1):

559 pRange = self.xlSheet.Range('$A$1', self.xlSheet.Cells(self.nRows, NCurves+1)) 560 self.chart.SetSourceData( pRange, constants.xlColumns ) 561

562 - def makeChart(self, rs, title="Data From Python", nCurves = 1, 563 sheetName="",chartName="", showPoints=1, showLegend=1, 564 yLabel="Vertical Axis", xLabel="Horizontal Axis"):

565 '''make Excel Chart from rs 566 rs is a tuple of tuples, or a list of lists 567 Top row of titles, then rows of data''' 568 if len(self.chartList)>0: 569 self.xlSheet = self.xlApp.Sheets.Add() 570 self.sheetList.append( self.xlSheet ) 571 if len(sheetName)>0:self.xlSheet.Name = sheetName 572 573 rs = squareUpRS( rs, pad='' ) 574 575 self.nRows = len(rs) 576 self.nColumns = len(rs[0]) 577 cRange = self.xlSheet.Range('$A$1', self.xlSheet.Cells(self.nRows, self.nColumns)) 578 cRange.Value = rs 579 580 self.chart = self.xlApp.Charts.Add() 581 self.chartList.append( self.chart ) 582 self.chartNColumns.append( self.nColumns ) 583 self.chartNRows.append( self.nRows ) 584 if len(chartName)>0: self.chart.Location(Where=1, Name=chartName) 585 586 self.chart.ChartType = constants.xlXYScatterLines 587 self.chart.SizeWithWindow = 1 588 589 if self.nColumns>nCurves+1: # if number of curves is set, use it. 590 pRange = self.xlSheet.Range('$A$1', self.xlSheet.Cells(self.nRows, nCurves+1)) 591 else: # otherwise plot everything 592 pRange = cRange 593 self.chart.SetSourceData( pRange, constants.xlColumns ) 594 self.chart.PlotArea.Interior.ColorIndex = constants.xlNone 595 596 self.chart.HasTitle = 1 # Need to enable title before setting text 597 self.chart.ChartTitle.Characters.Text = title 598 599 if len(xLabel)>0: 600 self.chart.Axes( constants.xlCategory, constants.xlPrimary).HasTitle = 1 601 self.chart.Axes( constants.xlCategory, constants.xlPrimary).AxisTitle.Characters.Text = xLabel 602 603 if len(yLabel)>0: 604 self.chart.Axes( constants.xlValue, constants.xlPrimary).HasTitle = 1 605 self.chart.Axes( constants.xlValue, constants.xlPrimary).AxisTitle.Characters.Text = yLabel 606 607 self.chart.Axes(1).HasMajorGridlines = 1 # turn on grid for x axis 608 self.chart.Axes(1).MajorGridlines.Border.LineStyle = constants.xlDot # make grid style dots 609 self.chart.Axes(2).MajorGridlines.Border.LineStyle = constants.xlDot # make grid style dots 610 611 if not showPoints: self.turnMarkersOnOff(showPoints=0) 612 if not showLegend: self.chart.HasLegend = 0 # set Legend On or Off

613

614 - def makeDataSheet(self, rs, sheetName="DataSheet", autoFit=1, rowFormatL=None, 615 textFont='', textFontSize=None):

616 '''make Excel Data Sheet from rs 617 rs is a tuple of tuples, or a list of lists 618 Top row of titles, then rows of data''' 619 self.xlSheet = self.xlApp.Sheets.Add() 620 self.sheetList.append( self.xlSheet ) 621 if len(sheetName)>0:self.xlSheet.Name = sheetName 622 623 rs = squareUpRS( rs, pad='' ) 624 625 626 self.nRows = len(rs) 627 self.nColumns = len(rs[0]) 628 629 # get rid of any equals signs as 1st letter of string 630 for i in range( self.nRows ): 631 for j in range( self.nColumns ): 632 try: 633 if rs[i][j][0]=='=': 634 rs[i][j] = "'" + str(rs[i][j]) 635 except: 636 pass 637 638 639 cRange = self.xlSheet.Range('$A$1', self.xlSheet.Cells(self.nRows, self.nColumns)) 640 cRange.Value = rs 641 642 if textFont: 643 cRange.Font.Name = textFont 644 645 if textFontSize: 646 cRange.Font.Size = textFontSize 647 648 if autoFit: 649 cRange.Columns.AutoFit() 650 651 #cRange.NumberFormat = "0.00" 652 if rowFormatL: 653 for i,rowFormat in enumerate( rowFormatL ): 654 if rowFormat: 655 N = i+1 656 cLR = self.formula.makeColLocation( NColumn=self.nColumns, NRow=N) 657 rowRange = self.xlSheet.Range('$A$%i'%N, cLR) 658 rowRange.NumberFormat = rowFormat

659 #print '$A$%i'%N, rowFormat 660 661

662 - def setRangeOfDataSheet(self, rs, sheetName="DataSheet", upperLeft='$A$1'):

663 '''add to Excel Data Sheet from rs 664 rs is a tuple of tuples, or a list of lists 665 Top row of titles, then rows of data''' 666 self.focusSheetByName(shtName=sheetName) 667 668 rs = squareUpRS( rs, pad='' ) 669 670 ucol, urow = xlChFormula.getNcolNrow( cell=upperLeft) 671 lcol = len(rs[0]) + ucol - 1 672 lrow = len(rs) + urow - 1 673 674 cLR = self.formula.makeColLocation( NColumn=lcol, NRow=lrow) 675 676 #self.nRows = len(rs) 677 #self.nColumns = len(rs[0]) 678 cRange = self.xlSheet.Range(upperLeft, cLR) 679 cRange.Value = rs

680 681

682 - def setRangeOnCurrentSheet(self, rs, upperLeft='$A$1'):

683 '''add to Excel Data Sheet from rs 684 rs is a tuple of tuples, or a list of lists 685 Top row of titles, then rows of data''' 686 687 rs = squareUpRS( rs, pad='' ) 688 689 ucol, urow = xlChFormula.getNcolNrow( cell=upperLeft) 690 lcol = len(rs[0]) + ucol - 1 691 lrow = len(rs) + urow - 1 692 693 cLR = self.formula.makeColLocation( NColumn=lcol, NRow=lrow) 694 695 #self.nRows = len(rs) 696 #self.nColumns = len(rs[0]) 697 cRange = self.xlSheet.Range(upperLeft, cLR) 698 cRange.Value = rs

699 700

701 - def pageSetupForSheet(self, landscape=0, fitWidth=0, fitHeight=0, marginInches=0.0):

702 '''use active sheet''' 703 #self.xlSheet = self.sheetList[N-1] 704 #self.xlSheet.Activate() 705 if fitWidth: 706 self.xlSheet.PageSetup.FitToPagesWide = fitWidth 707 if fitHeight: 708 self.xlSheet.PageSetup.FitToPagesTall = fitHeight 709 710 if landscape: 711 self.xlSheet.PageSetup.Orientation = constants.xlLandscape 712 else: 713 self.xlSheet.PageSetup.Orientation = constants.xlPortrait 714 self.xlSheet.PageSetup.Zoom = False 715 716 if marginInches > 0.0: 717 self.xlSheet.PageSetup.LeftMargin = self.xlApp.InchesToPoints(marginInches) 718 self.xlSheet.PageSetup.RightMargin = self.xlApp.InchesToPoints(marginInches) 719 self.xlSheet.PageSetup.TopMargin = self.xlApp.InchesToPoints(marginInches) 720 self.xlSheet.PageSetup.BottomMargin = self.xlApp.InchesToPoints(marginInches)

721 722 723 if __name__ == "__main__": #Self Test 724 xl = ExcelWrap() 725 rs = ( ("x", "y", "Zee","Queue"), (1,1,1,1,1,1), (2,2,3,4), (3,3,5,8) ) 726 rs = squareUpRS( rs, pad='xxx' ) 727 728 xl.makeChart(rs, title="Now is the time for First Things",nCurves = 3, 729 chartName="First Chart", 730 sheetName="First Dataset", 731 yLabel="Vertical Axis", xLabel="Horizontal Axis") 732 rs = ( ("A", "B", "Cee", "Dee"), (11,33,33,33), (21,21,25,30), (31,11,21,25) ) 733 xl.makeChart(rs, title="This is a Second Plot",nCurves = 3, 734 chartName="Second Chart", showPoints=0, showLegend=0, 735 sheetName="Second Dataset", 736 yLabel="Vertical Axis", xLabel="Horizontal Axis") 737 xl.focusChart(1) 738 xl.putSeriesOnSecondary(1, y2Label="Second Y Axis") 739 xl.putSeriesOnSecondary(2) 740 xl.putSeriesOnPrimary(1) 741 xl.labelXAxis("Whole New X Axis Label") 742 xl.labelPrimaryYAxis("Whole New Y Axis Label") 743 xl.setLineThickness(NSeries=1, thickness=1) 744 xl.setLineThickness(NSeries=2, thickness=2) 745 xl.setLineThickness(NSeries=3, thickness=3) 746 xl.setMarkerSize( NSeries=1, size=4) 747 xl.setMarkerSize( NSeries=2, size=6) 748 xl.setMarkerSize( NSeries=3, size=8) 749 xl.xlApp.DisplayAlerts = 0 # Allow Quick Close without Save Message 750 xl.setLineStyle(NSeries=1, style=0) 751 752 xl.setSeriesColorIndex(NSeries=1, colorIndex=1) 753 #xl.setSeriesColorIndex(NSeries=2, colorIndex=22) 754 xl.setSeriesColor( NSeries=2, red=0, green=255, blue=0) 755 xl.setSeriesColor( NSeries=3, red=255, green=0, blue=0) 756 text='''abcdefghijklmnopqrstuvwxy 757 abcdefghijklmnopqrstuvwxy 758 abcdefghijklmnopqrstuvwxy 759 abcdefghijklmnopqrstuvwxy 760 abcdefghijklmnopqrstuvwxy 761 abcdefghijklmnopqrstuvwxy 762 abcdefghijklmnopqrstuvwxy 763 abcdefghijklmnopqrstuvwxy 764 abcdefghijklmnopqrstuvwxy 765 abcdefghijklmnopqrstuvwxy 766 abcdefghijklmnopqrstuvwxy 767 abcdefghijklmnopqrstuvwxy''' 768 xl.addTextBox(text=text) 769 xl.setXrange( 1.0, 3.0) 770 xl.setYrange( 1.0, 5.0) 771

Source Code for Module parasol.Excel_wrapper