Manage Learn to apply best practices and optimize your operations.

Excel independent chart on Web

This tip describes how to create Excel independent charts on the Web.

While making an Excel chart from Lotuscript we always have Excel dependency. This means in order to view the exported file(Excel chart) on a browser you need to have Excel on your machine. Here is a solution where Excel is only required on a server or at the machine where this agent runs to create the Excel chart.


The agent/script actually works on the methodolgy that:

  1. Calls Ms Excel object
  2. Creats excel chart on the exported data
  3. Selects the chart area.
  4. Converts the chart area in to a GIF Image. The gif image can be viewed by all kinds of browser and that makes it Excel independent for viewers.
This will go to initialize section:

Sub Initialize
 Dim ses As New notessession
 Dim object As NotesEmbeddedObject
 Dim object1 As NotesEmbeddedObject 
 Dim objectbitmap As 
 Dim db As notesdatabase
 Set db=ses.currentdatabase
 path$="d:" ' this is path of the temporary file
 graphwidth$="1.67"'width of the graph
 graphheight$="1.97"'height of the graph
 gridfont$=4 'parameter for the grid font size
'***********This block deletes the files if 
exists in the d: drive of th emachine 
 On Error Resume Next
 Kill path$+"ettrspan.gif" 
 On Error Resume Next
 Dim xlApp As Variant
 Dim oWorkbook As Variant 
 ChartName$ = "Graph" 
 Set xlApp = CreateObject("Excel.Application") 
 xlApp.Visible =False 'set to invisible 
 Set oworkbook = xlApp.Workbooks.Add 
'handle to Workbook
'/*  Building  DATA
 i = 2
 For i=2 To 15
  xlApp.cells(i,1) = "FW"+Cstr(i)
  xlApp.cells(i,2) = i+10
  xlApp.cells(i,3) = i+11
  xlApp.cells(i,4) = i+12
  xlApp.cells(i,5) = i+13
  xlApp.cells(i,6) = i+14
  xlApp.cells(i,7) = i+15
  xlApp.cells(i,8) = i+16
 xlApp.cells(1,2) = "A"
 xlApp.cells(1,3) = "B"
 xlApp.cells(1,4) = "C"
 xlApp.cells(1,5) = "D"
 xlApp.cells(1,6) = "E"
 xlApp.cells(1,7) = "F"
 xlApp.cells(1,8) = "G"
'select the data rows and create a chart
 xlChartType =xl3DBar'chart type; 
 xlLocationAsObject = 2
 With xlApp.ActiveWorkbook.ActiveChart
  .Name = ChartName$
  .HasTitle = True
  .HasLegend = False
  .ChartTitle.Text = "YOUR CHART TITLE" 
'Span Cave Chart Test:  
  .ChartType = xlChartType 
  .PlotArea.Interior.ColorIdex = "0" 
  .Location xlLocationAsObject, 
ChartName$ 'move chart to worksheet
 End With 
 xlApp.ActiveChart.HasDataTable = True
ShowLegendKey = True
 xlApp.Selection.AutoScaleFont = True
 With xlApp.Selection.Font
  .Name = "Arial"
  .FontStyle = "Regular"
  .Size = gridfont$
  .Strikethrough = False
  .Superscript = False
  .Subscript = False
  .OutlineFont = False
  .Shadow = False
  .Underline = xlUnderlineStyleNone
  .ColorIndex = xlAutomatic
  .Background = xlAutomatic
 End With
 With xlApp.Selection.Font
  .Name = "Arial"
  .FontStyle = "Bold"
  .Size = 9
  .Strikethrough = False
  .Superscript = False
  .Subscript = False
  .OutlineFont = False
  .Shadow = False
  .Underline = xlUnderlineStyleNone
  .ColorIndex = 3
  .Background = xlAutomatic
 End With
 xlApp.ActiveSheet.Shapes("Chart 1").
ScaleWidth graphwidth$, msoFalse,
'change chart dimensions xlApp.ActiveSheet.Shapes("Chart 1"). ScaleHeight graphheight$, msoFalse, msoScaleFromTopLeft xlApp.ActiveChart.ChartGroups(1). GapWidth = 30 'reduce width between columns 'oworkbook.Saveas(path$+"ettrspan.xls") 'this is a temporary path final path will be in the srever's root directory(html/Graph) xlApp.ActiveChart.Export path$+" ettrspan.gif", "gif"'tested done xlApp.ActiveWorkbook.saved = True ' closes EXCEL without prompting Call xlApp.quit Exit Sub '**************** End Sub This will go to declaration section 'these inumeraters are for the object type refrence. Const xlRangeAutoFormatColor1=7 Const xlClipboardFormatStandardFont=28 Const xlDialogActiveCellFont=476 Const xlDialogFont=26 Const xlDialogReplaceFont=134 Const xlDialogStandardFont=190 Const xlPie=5 Const xlLegendPositionBottom=-4107 Const xl3DArea=-4098 Const xl3DAreaStacked=78 Const xl3DAreaStacked100=79 Const xl3DBar=-4099 Const xl3DBarClustered=60 Const xl3DBarStacked=61 Const xlLine=4 Const xlLineMarkers=65 Const xlLineMarkersStacked=66 Const xlLineMarkersStacked100=67 Const xlLineStacked=63 Const xlLineStacked100=64 Const xl3DBarStacked100=62 Const xl3DColumn=-4100 Const xl3DColumnClustered=54 Const xl3DColumnStacked=55 Const xl3DColumnStacked100=56 Const xl3DLine=-4101 Const xl3DPie=-4102 Const xl3DPieExploded=70 Const xl3DSurface=-4103 Const xlAreaStacked=76 Const xlAreaStacked100=77 Const xlBarClustered=57 Const xlBarOfPie=71 Const xlBarStacked=58 Const xlBarStacked100=59 Const xlBubble=15 Const xlBubble3DEffect=87 Const xlColumnStacked=52 Const xlColumnStacked100=53 Const xlConeBarClustered=102 Const xlConeBarStacked=103 Const xlConeBarStacked100=104 Const xlConeCol=105 Const xlConeColClustered=99 Const xlConeColStacked=100 Const xlConeColStacked100=101 Const xlCylinderBarClustered=95 Const xlCylinderBarStacked=96 Const xlCylinderBarStacked100=97 Const xlCylinderCol=98 Const xlCylinderColClustered=92 Const xlCylinderColStacked=93 Const xlCylinderColStacked100=94 Const xlColor1=7 Const xlColor2=8 Const xlColor3=9

Dig Deeper on LotusScript

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.




  • iSeries tutorials's tutorials provide in-depth information on the iSeries. Our iSeries tutorials address areas you need to know about...

  • V6R1 upgrade planning checklist

    When upgrading to V6R1, make sure your software will be supported, your programs will function and the correct PTFs have been ...

  • Connecting multiple iSeries systems through DDM

    Working with databases over multiple iSeries systems can be simple when remotely connecting logical partitions with distributed ...