Excel independent chart on 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 
NotesEmbeddedObject 
 
 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
 Next
 
 
 xlapp.cells(1,1)=""
 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"
 
 
'/* END OF SAMPLE DATA
 
 xlApp.Range("A1:H"+Cstr(i-1)).Select 
'select the data rows and create a chart
 xlApp.Charts.Add 
 
 xlChartType =xl3DBar'chart type; 
described in  DECLARATION SECTION
 xlLocationAsObject = 2
 
 
 With xlApp.ActiveWorkbook.ActiveChart
  .Name = ChartName$
  .HasTitle = True
  .HasLegend = False
  .PlotBy=2
  .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.ChartArea.Select
 
 xlApp.ActiveChart.HasDataTable = True
 xlApp.ActiveChart.DataTable.
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
 xlApp.ActiveChart.ChartTitle.Select
 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,
msoScaleFromTopLeft
 '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