Microsoft KB Archive/326103

From BetaArchive Wiki

Article ID: 326103

Article Last Modified on 2/22/2007


APPLIES TO

  • Microsoft Office Web Components
  • Microsoft Office Web Components


This article was previously published under Q326103

SUMMARY

REFERENCES

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


back to the top

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.

  1. 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"?>&#13;

<Workbook 

xmlns="urn:schemas-microsoft-com:office:spreadsheet"&#13;
 
xmlns:o="urn:schemas-microsoft-com:office:office"&#13;
 
xmlns:x="urn:schemas-microsoft-com:office:excel"&#13;
 
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"&#13;
 
xmlns:html="http://www.w3.org/TR/REC-html40">&#13;

 

<DocumentProperties 
xmlns="urn:schemas-microsoft-com:office:office">&#13;
  

<Author>Microsoft Corporation</Author>&#13;
  

<Created>2002-06-27T23:44:24Z</Created>&#13;
  
<Company>Microsoft Corporation</Company>&#13;
  
<Version>10.3501</Version>&#13;
 

</DocumentProperties>&#13;

 
<OfficeDocumentSettings 

xmlns="urn:schemas-microsoft-com:office:office">&#13;
  
<DownloadComponents/>&#13;

<LocationOfComponents HRef="file:///\\"/>&#13;
 
</OfficeDocumentSettings>&#13;


<ExcelWorkbook 
xmlns="urn:schemas-microsoft-com:office:excel">&#13;
  
<WindowHeight>10995</WindowHeight>&#13;
  
<WindowWidth>14235</WindowWidth>&#13;
  

<WindowTopX>360</WindowTopX>&#13;
  
<WindowTopY>105</WindowTopY>&#13;
  
<ProtectStructure>False</ProtectStructure>&#13;
  
<ProtectWindows>False</ProtectWindows>&#13;
 

</ExcelWorkbook>&#13;

 
<Styles>&#13;

<Style ss:ID="Default" ss:Name="Normal">&#13;
  
<Alignment ss:Vertical="Bottom"/>&#13;
   

<Borders/>&#13;
   
<Font/>&#13;

<Interior/>&#13;

<NumberFormat/>&#13;
   
<Protection/>&#13;

</Style>&#13;

</Styles>&#13;


<Worksheet 
ss:Name="Sheet1">&#13;

  <Table ss:ExpandedColumnCount="256" 
   ss:ExpandedRowCount="65536"&#13;

   x:FullColumns="1" 
   x:FullRows="1">&#13;


      <Row>&#13;

      <Cell><Data 
ss:Type="String">Apples</Data></Cell>&#13;
      

      <Cell><Data 
ss:Type="Number">6</Data>
      </Cell>&#13;

      </Row>&#13;
   

      <Row>&#13;

      <Cell><Data 
ss:Type="String">Oranges</Data></Cell>&#13;
     

      <Cell><Data 
ss:Type="Number">7</Data></Cell>&#13;
         

      </Row>&#13;
   

      <Row>&#13;

      <Cell><Data 
ss:Type="String">Pears</Data></Cell>&#13;
     

      </Row>&#13;
   

      <Row>&#13;

      <Cell><Data 
ss:Type="String">Cherries</Data></Cell>&#13;
    

      <Cell><Data 
ss:Type="Number">12</Data></Cell>&#13;
        

      </Row>&#13;
   

      <Row>&#13;

      <Cell><Data 
ss:Type="String">Kiwis</Data></Cell>&#13;
       

      <Cell><Data 
ss:Type="Number">4</Data></Cell>&#13;
         

      </Row>&#13;
   

      <Row>&#13;

      <Cell><Data 
ss:Type="String">Bananas</Data></Cell>&#13;
   

      <Cell><Data 
ss:Type="Number">10</Data></Cell>&#13;
        

      </Row>&#13;
  
  </Table>&#13;

  <WorksheetOptions 
xmlns="urn:schemas-microsoft-com:office:excel">&#13;
   
<ProtectObjects>False</ProtectObjects>&#13;
   
<ProtectScenarios>False</ProtectScenarios>&#13;
  

</WorksheetOptions>&#13;
  
<PublishObjects 

xmlns="urn:schemas-microsoft-com:office:excel">&#13;
   
<PublishObject>&#13;

    <Id>28139</Id>&#13;
    
<DivID>Book1_28139</DivID>&#13;
    
<SourceType>SourceSheet</SourceType>&#13;
    
<HtmlType>HtmlCalc</HtmlType>&#13;

    <Location x:HRef="C:\Documents and 
Settings\mico\Desktop\Page3.htm"/>&#13;
   

</PublishObject>&#13;
  
</PublishObjects>&#13;
 

</Worksheet>&#13;

</Workbook>&#13;
">
</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

  2. Start Microsoft Internet Explorer and then move to MyChart.htm.
  3. 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:

Office XP Tool: Web Components


To install the Microsoft Office 2003 Web Components, visit the following Microsoft Web site:

Office 2003 Add-in: Office Web Components



back to the top

REFERENCES

For more information about using Office XP Web components, visit the following Microsoft Web Site:

Microsoft Office Development with Visual Studio
http://support.microsoft.com/ofd


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


back to the top


Additional query words: owc kbVBScript webchart owc10 gap null discontinuous OWC11

Keywords: kbhowtomaster kbdownload kbofficewebchart KB326103



Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.


Retrieved from ‘https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/326103&oldid=380637’
the Creative Commons 3.0 ShareAlike (except the Microsoft KB Archive).
Powered by MediaWiki