Last edit: 05-08-30 Graham Wideman |
Excel |
XLChartMatic Part 4. Detailed Data Layout Article created: 2005-03-04 |
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.
]
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. |
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. |
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. |
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$.)
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.
|
|
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. |
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.
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: or up to XLChartMatic for Microsoft Excel