HOW TO: Display Breaks for Null Data in an Office Web Component Line Chart (326103)
The information in this article applies to:
- Microsoft Office 2003 Web Components
- Microsoft Office XP Web Components
This article was previously published under Q326103 SUMMARY This article describes how to chart null data points in the
Chart control of Microsoft Office Web Components (OWC). The
technique in this article describes how you can use the Chart rendering events
of the component to plot a discontinuous line chart that omits or skips null
data points. This technique works only with line type or scatter type charts.
For additional information about working with a chart type
other than line or scatter, click the article number below to view the article
in the Microsoft Knowledge Base: 286317 HOWTO: Create Null (or Omitted) Data Points with the Office Chart Component
Plot a Discontinuous Line The following sample code demonstrates how to plot a
discontinuous line. Data points where the value is null are omitted in the
line. The sample code uses a line chart, but the same technique can be used
with a scatter chart.
- Use a text editor or an HTML editor to create a new Web
page. Name the Web page MyChart.htm and include the
following contents:
<html>
<head>
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>
<body>
<p>
<object classid="clsid: <Class Identifier of Chart Control>" id="chsp"
<!-- Replace the Class ID in the above line of code with the Class ID of the chart control being used-->
width="689" height="652">
<p style='margin-top:100;font-family:Arial;font-size:8.0pt'>To use this Web
page interactively, you must have Microsoft Internet Explorer 4.01 Service
Pack 1 (SP1) or later and the Microsoft Office XP Web Components or later.</p>
<a href="file:///\\files\owc\setup.exe">Click here to install the Office XP
Web Components.</a>
<p style='margin-top:100;font-family:Arial;font-size:8.0pt'>See the <a
href="http://office.microsoft.com/office/redirect/10/MSOWCPub.asp?HelpLCID=10
33">Microsoft
Office Web site</a> for more information.</p>
</object>
<object id="Spreadsheet1"
classid="CLSID: <Class Identifier of Spreadsheet Control>">
<param name=DisplayTitleBar value=false>
<param name=DataType value=XMLData>
<param name=XMLData
value="<?xml version="1.0"?>
<Workbook
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties
xmlns="urn:schemas-microsoft-com:office:office">
<Author>Microsoft Corporation</Author>
<Created>2002-06-27T23:44:24Z</Created>
<Company>Microsoft Corporation</Company>
<Version>10.3501</Version>
</DocumentProperties>
<OfficeDocumentSettings
xmlns="urn:schemas-microsoft-com:office:office">
<DownloadComponents/>
<LocationOfComponents HRef="file:///\\"/>
</OfficeDocumentSettings>
<ExcelWorkbook
xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>10995</WindowHeight>
<WindowWidth>14235</WindowWidth>
<WindowTopX>360</WindowTopX>
<WindowTopY>105</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
<Worksheet
ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="256"
ss:ExpandedRowCount="65536"
x:FullColumns="1"
x:FullRows="1">
<Row>
<Cell><Data
ss:Type="String">Apples</Data></Cell>
<Cell><Data
ss:Type="Number">6</Data>
</Cell>
</Row>
<Row>
<Cell><Data
ss:Type="String">Oranges</Data></Cell>
<Cell><Data
ss:Type="Number">7</Data></Cell>
</Row>
<Row>
<Cell><Data
ss:Type="String">Pears</Data></Cell>
</Row>
<Row>
<Cell><Data
ss:Type="String">Cherries</Data></Cell>
<Cell><Data
ss:Type="Number">12</Data></Cell>
</Row>
<Row>
<Cell><Data
ss:Type="String">Kiwis</Data></Cell>
<Cell><Data
ss:Type="Number">4</Data></Cell>
</Row>
<Row>
<Cell><Data
ss:Type="String">Bananas</Data></Cell>
<Cell><Data
ss:Type="Number">10</Data></Cell>
</Row>
</Table>
<WorksheetOptions
xmlns="urn:schemas-microsoft-com:office:excel">
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
<PublishObjects
xmlns="urn:schemas-microsoft-com:office:excel">
<PublishObject>
<Id>28139</Id>
<DivID>Book1_28139</DivID>
<SourceType>SourceSheet</SourceType>
<HtmlType>HtmlCalc</HtmlType>
<Location x:HRef="C:\Documents and
Settings\mico\Desktop\Page3.htm"/>
</PublishObject>
</PublishObjects>
</Worksheet>
</Workbook> ">
</object>
</body>
<SCRIPT language=VBScript>
Dim chartLeft,chartRight
Const useMarker = True
Sub window_onload()
Dim webConst
Dim newChart
Set webConst = chsp.Constants
' Enable the events on the chart control
chsp.EnableEvents = True
chsp.AllowRenderEvents = True
chsp.AllowPointRenderEvents = True
' Bind the chart to the spreadsheet
chsp.Clear
Set chsp.DataSource = Spreadsheet1
Set newChart = chsp.Charts.Add()
newChart.Type = chChartTypeLine
' Rely on certain information specific to line types
newChart.SetData webConst.chDimCategories, 0, "a1:a6"
newChart.SeriesCollection(0).SetData webConst.chDimValues, 0, "b1:b6"
chsp.HasChartSpaceTitle = True
chsp.ChartSpaceTitle.caption = "Do not plot null data points."
End Sub
Sub chsp_AfterRender(drawObj,chartObj)
Dim webConst
Dim oPoint
Dim preCoorX,preCoorY
Dim coor
Set webConst = chsp.Constants
If TypeName(chartObj) = "ChSeries" Then
' Initialize the starting point
preCoorX = chartObj.Left
preCoorY = 0
' Set the Line style that you want
drawObj.Line.DashStyle = webConst.chLineSolid
For each oPoint in chartObj.Points
' Do not draw a Line If Null
If(oPoint.GetValue(webConst.chDimValues) <> "") Then
Set coor = _
chartObj.valuetopoint(oPoint.GetValue(webConst.chDimCategories), _
oPoint.GetValue(webConst.chDimValues))
If preCoorY > 0 Then
drawObj.DrawLine preCoorX, preCoorY, coor.x, coor.y
' Point to point Line
If useMarker Then
drawObj.DrawRectangle coor.x - 5, coor.y + 5, _
coor.x + 5, coor.y - 5
End If ' useMarker
Else
' Draw only the marker
If useMarker Then
drawObj.DrawRectangle coor.x - 5, coor.y + 5, _
coor.x + 5, coor.y - 5
End If ' useMarker
End If 'preCoorY > 0
' Set the starting coordinates
preCoorX = coor.x
preCoorY = coor.y
Else
' Skip the Line If null by setting the starting y to zero.
preCoorY = 0
End If ' (oPoint.GetValue(webConst.chDimValues) <> "")
Next 'oPoint
End If 'TypeName(chartObj) = "ChSeries"
End sub
sub chsp_BeforeRender(drawObj,chartObj, Cancel)
If TypeName(chartObj)="ChSeries" Then
' This prevents the chart from rendering its typical line plot
Cancel.Value = true
End If ' typename(chartObj)="ChSeries"
End sub
</SCRIPT>
</html>
Note Replace the <Class Identifier of Chart Control> and
<Class Identifier of Spreadsheet Control> in the above code with the
Class ID of the chart control and spreadsheet control being used.
For
Microsoft Office Chart 10.0
0002E556-0000-0000-C000-000000000046
For Microsoft Office Spreadsheet
10.0
0002E551-0000-0000-C000-000000000046
- Start Microsoft Internet Explorer and then move to
MyChart.htm.
- After the page renders in Internet Explorer, you notice a
chart that contains one line series. The line contains a break where the data
is NULL. The chart is bound to the Spreadsheet data. The value for "Pears" in
cell B3 is NULL.
back to the top
Troubleshoot the Client Computer The client computer may report the following error message:
"Error: Object doesn't support this property or method:
'chsp.Constants'" This error message means that the system does not
have OWC 10 or OWC 11 installed. To install the Microsoft Office XP Web
Components on the client, visit the following Microsoft Web site: To install the Microsoft Office 2003 Web Components, visit the
following Microsoft Web site:
back to the top
REFERENCESFor more information about using Office XP Web components,
visit the following Microsoft Web Site: For additional information, click the article numbers
below to view the articles in the Microsoft Knowledge Base: 286212 HOWTO: Use an XML DataSource with the Office XP Chart Component
288907 INFO: Binding the Office XP Chart Component to a Data Source
303016 HOW TO: Use a DataSet with the Office XP Chart Component and ASP.NET
315695 HOW TO: Use the Office XP Web Components with XML Data Generated by an XML Web Service Created Using ASP.NET
| Modification Type: | Major | Last Reviewed: | 9/8/2006 |
|---|
| Keywords: | kbdownload kbHOWTOmaster kbOfficeWebChart KB326103 |
|---|
|