Axolot Data

Posted: **Tue Feb 02, 2016 8:08 pm**

XLSReadWriteII5 v5.20.69

I've been trying for the last several hours to apply simple DataBar conditional formatting to a range of cells without success. I've tried various combinations of values in everything I can find to no avail. I've been looking inside the workbook at the resulting XML to see if I'm making progress, and getting nowhere. Any attempt to do so results in Excel generating an error when loading the file:

Repaired Part: /xl/worksheets/sheet1.xml part with XML error. Catastrophic failure Line 55, column 10.

Here's the XML for a DataBar I've created in Excel itself, extracted from Sheet1 (the only change was to clean up the formatting to make it readable). The DataBar was set up by simply selecting the indicated cells, going to Conditional Formatting->DataBars->Gradient Fill, and selecting the second down from the left corner of the gallery:

Code: Select all

	<conditionalFormatting sqref="A1:C2">
		<cfRule type="dataBar" priority="1">
			<dataBar>
				<cfvo type="min"/>
				<cfvo type="max"/>
				<color rgb="FFFFB628"/>
			</dataBar>
			<extLst>
				<ext uri="{B025F937-C7B1-47D3-B67F-A62EFF666E3E}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
					<x14:id>{F5134837-558B-49F4-81AF-82775B5808CD}</x14:id>
				</ext>
			</extLst>
		</cfRule>
	</conditionalFormatting>

The closest I've gotten with every combination I can come up with leaves me with this (formatting preserved as output by XMLReadWriteII5). I've included the data.

Code: Select all

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" 
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" 
>
<dimension ref="A1:C4" />
<sheetData>
<row r="1" ht="15" >
<c r="A1" >
<v>0</v>
</c>
<c r="B1" >
<v>7</v>
</c>
<c r="C1" >
<v>215</v>
</c>
</row>
<row r="2" ht="15" >
<c r="A2" >
<v>50</v>
</c>
<c r="B2" >
<v>68</v>
</c>
<c r="C2" >
<v>167</v>
</c>
</row>
<row r="3" ht="15" >
<c r="A3" >
<v>79</v>
</c>
<c r="B3" >
<v>40</v>
</c>
<c r="C3" >
<v>93</v>
</c>
</row>
<row r="4" ht="15" >
<c r="A4" >
<v>106</v>
</c>
<c r="B4" >
<v>20</v>
</c>
<c r="C4" >
<v>118</v>
</c>
</row>
</sheetData>
<conditionalFormatting sqref="A1:D4" >
<cfRule type="dataBar" dxfId="0" priority="2" >
<dataBar>
</dataBar>
</cfRule>
</conditionalFormatting>
<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3" />
<pageSetup paperSize="5" orientation="portrait" />
</worksheet>

As you can see, there's no values being output in the <dataBar></dataBar> at all. Here's my latest code that produced the XML above (exception handling omitted for brevity).

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

uses
  SysUtils, XLSReadWriteII5, Xc12DataWorksheet5, XLSCondFormat5, Xc12Utils5;

var
  XLS: TXLSReadWriteII5;
  CF: TXLSConditionalFormat;
  Rule: TXc12CfRule;

begin
  XLS := TXLSReadWriteII5.Create(nil);
  XLS[0].FillRandom('A1:C4', 250);
  CF := XLS[0].ConditionalFormats.AddCF;
  CF.SQRef.Add(0, 0, 3, 3);

  Rule := CF.CfRules.Add;
  Rule.Priority := 2;
  Rule.Type_ := x12ctDataBar;

  Rule.DataBar.Color := RGBColorToXc12($FFFFB628);
  Rule.DataBar.ShowValue := True;
  Rule.DataBar.Cfvo1.Type_ := x12ctMin;
  Rule.DataBar.Cfvo2.Type_ := x12ctMax;
  CF.SetStyle(Rule, [], Rule.DataBar.Color);
  XLS.SaveToFile('E:\TempData\DataBarTest.xlsx');
  XLS.Free;
end.

Can someone provide an example of applying a DataBar to a small range of cells?

Posted: **Mon Feb 08, 2016 8:30 am**

Hello

You must set the Val property as well.

Example:

Code: Select all

Rule.DataBar.Cfvo2.Val := '0';

Posted: **Tue Feb 09, 2016 2:07 pm**

Thanks, Lars. Works perfectly.

Axolot Data

Unable to create DataBar conditional formatting

Unable to create DataBar conditional formatting

Re: Unable to create DataBar conditional formatting

Re: Unable to create DataBar conditional formatting