gw_logo_08.gif (1982 bytes) 
Last edit: 05-08-30 Graham Wideman
Excel
XLChartMatic Part 4. Detailed Data Layout
Article created: 2005-03-04

Details of XLChartMatic's Use of Spreadsheet Areas

XLChartMatic requires you to establish two areas of your spreadsheet, a small area where you provide settings for the overall chart, and a larger area where you provide data for the series and points. XLChartMatic provides steps for selecting each of these areas, and for specifying whether you want the new data to replace data in an existing chart, or to create a new chart.

The following sections document the spreadsheet areas.

Chartwide Data

]

This area ("range" in Excel parlance) must have a cell labeled "ch_Type" in the top left corner. Data in this section is organized as a series of "name-value" pairs, occupying 2 columns, with names in the left column and values in the right column.

Row Name Value Type Description
ch_Type string Tells XLChartMatic what type of chart to create. Eg: XYScatter See XLChartMatic's Keywords tab for valid values.
 lbl_Size integer If you are using point labels, then this tells their size
lbl_Position string Again for use in connection with point labels, this tells their position. Values such as Above, Right, Below etc.
See XLChartMatic's Keywords tab for valid values.

Series and Point Data

This area ("range" in Excel parlance) must have a cell labeled "pt_Series" in the top left corner. XLChartMatic will follow cells rightward and include all columns whose names it recognizes, stopping when it encounters a blank cell. (No need to get rid of columns that aren't useful to XLChartMatic, just label them something innocuous.)

Similarly, XLChartMatic will read down the pt_Series column including all rows until it reaches a blank cell in the pt_Series column.

XLChartMatic assumes that the first row below the XLChartMatic-understood headings will be a row of column names meaningful to your application. When used with Access, data pasted from a table or query "datasheet view" will include these headings, so XLChartMatic's assumption here avoids having to delete this row, and also keeps you informed about which column is which.

Needless to say, XLChartMatic assumes that the actual data starts on the second row below the XLChartMatic headings.

Important: When pasting data from Access to Excel, use Excel's Paste As > Text method. If instead you use the default Paste method, then between Access and Excel, they manage to paste numbers as text. The result will look fine, but Chart won't understand numbers that are stored as text. (Access and Excel 2002). See longer note in Part 2.

Detailed Listing of Columns which XLChart Recognizes

Column
in Sheet
Type Range/
Value [2]
XYScatter feature Bubble feature Line Chart feature
[XXX to do]
Object.Prop
[1]
pt_Series string Value       Series.Name
pt_SubSeries string n/a       (used by XLChartMatic)
pt_X float/string Range X value   X category Series.XValues
pt_Y float Range Y value   Y value Series.Values
bub_Size float Range   bubble size   Series.BubbleSizes
mkr_Style keyword [2] Value marker style     S/P.MarkerStyle
mkr_Size integer Value marker size     S/P.MarkerSize
mkr_FgColor color [2] Value marker line color     S/P.MarkerFore...[3]
mkr_BgColor color [2] Value marker fill color     S/P.MarkerBack...[3]
mkr_PicFile filename n/a custom marker symbol     (used by XLCM)
mkr_Text string n/a text for custom marker     (used by XLCM)
bdr_Style keyword [2] Value connecting line style bubble border line style   S/P.Border.LineStyle
bdr_Weight keyword [2] Value connecting line weight bubble border line weight   S/P.Border.Weight
bdr_Color color [2] Value connecting line color bubble border line color   S/P.Border.Color/ColorIndex
lbl_Text string Value label text     Point.DataLabel.Text
erb_Y float Range Y error bar len, symmetrical <-- ditto   Series.Errorbars method [4]
erb_YPlus float Range Y error bar len, plus <-- ditto   Series.Errorbars method [4]
erb_YMinus float Range Y error bar len, minus <-- ditto   Series.Errorbars method [4]
erb_X float Range X error bar len, symmetrical <-- ditto   Series.Errorbars method [4]
erb_XPlus float Range X error bar len, plus <-- ditto   Series.Errorbars method [4]
erb_XMinus float Range X error bar len, minus <-- ditto   Series.Errorbars method [4]
             
Notes
1. S/P. = for those interested in exactly what this sets in Chart's object model, this column shows the objects and properties involved:  Series.Something or Point.Something
2. See more details below.
3. MarkerForegroundColor or MarkerForegroundColorIndex, depending on whether user supplies one integer or a triple as described in the "color" discussion. Similar for Background....
4. If using error bars, specify either erb_Y (for symmetrical errorbar above and below) OR one or both of erb_YPlus and erb YMinus. Similarly for X-direction error bar.

Blank cells; Blank Text

In general, if you leave a cell in the series/point data area blank, that is a signal to XLChartMatic to do nothing.  This usually means that XLChartMatic will leave the corresponding Chart property at its default value.

(For the "advanced" custom marker file feature, if you want specify "blank text", then use the string $Blank$.)

Special Cell Values

While some columns need simple integer or float numbers, a number of columns need special values. These are described in the following table.

XLChartmatic Column type Values you can use
keyword A string which tells XLChartMatic to supply one or another constant value understood by Excel.

The list of keywords understood by XLChartMatic can be viewed on XLChartMatic's "Keywords" tab. For example, in the mkr_Style column you can supply values like: Circle, Square, Star and so on.

In general the string required is the name of the Excel constant, minus its prefix.
color Option 1: ColorIndex: Supply a single number from Excel's palette.  To specify "no color" (transparent) use 0.

Option 2: Color Name: Supply a color name like Red or Lime. To see available color names, look in XLChartMatic's Keywords tab.

Option 2: RGB: Supply red, green, blue values (RGB) as a triple of integers (0-255): Fior example the following would specify Red = 255, green = 255, blue = 0, which will appear yellow.

255,255,0

 

color (for custom marker emfs) When using custom marker emfs (see later page), specify color only using RGB.
Exceptions: to specify "no change" leave the cell blank. Transparent is not available.

Subtlety: Does XLChartMatic Supply Data to Chart as Range or Values?

When XLChartMatic tells Chart about data, where possible it provides a cell range. This is the case for the pt_X and pt_Y columns for example.

However, for other parts of the data such as point formatting, Excel doesn't accept a cell range, so XLChartMatic must supply the values explicitly, by reading them from the cells and placing them into specific properties in the Chart.

This distiction is listed in the Series and Point Data table above, in the Range/Value column.

Appearance-wise, this is of little importance. However, if you later edit one of the cells in the sheet (or for that matter insert or delete rows) the impact on the Chart will be different.

You can of course edit features of your chart manually as usual. However, if you are using XLChartMatic it's probably because you have a lot of points, so the manual editing alternative is laborious.

XLChartMatic's Consolidation of Point Settings Into Series

In general, XLChartMatic applies the settings of the first spreadsheet row for a series to the Chart Series. Subsequent rows for that series may have the same or different settings. If the settings are the same, then XLChartMatic leaves them as is. If they are different, then XLChartMatic sets them explicitly in Chart for that Point.

[Comment: I don't actually know what Chart does internally when setting Series level data... does it propagate to all Points as when doing it from the UI? Do Point-level properties have a "use parent value" value? Do the Series level properties do anything live, or are they merely for copying to new points that may be added?]


Go to:  gw_logo_08.gif (1982 bytes) or up to XLChartMatic for Microsoft Excel