Tip

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

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.