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

This was first published in February 2003

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

Back to top