Wrong format when both Row and Column format defined

[EdwardBenson]

I have an Excel template into which I am writing values using XLSReadWriteII.

I am using XLSReadWriteII v3 and Excel 2000.

The template has Column Formats, Row Formats, and Cell Formats defined.

For cells which have both column and row format defined, when XLSReadWriteII writes data into an empty cell, the cell is created and formatted with the wrong format: It is taking the column format when it should take the row format.

I have traced the problem to TSheet.GetDefaultFormat.

I have done some testing, and it seems that if both a ROW format and a COLUMN format are defined for an empty cell, Excel uses the ROW format in precedence to the COLUMN format. (If in Excel I format the ROW first and then the COLUMN, so that the intersecting cells should take the COLUMN format, Excel automatically creates BLANK cell records with XF index pointing to appropriate the COLUMN format XF record, thus overriding the ROW format. If I format the COLUMN first and then the ROW, no BLANK cell records are created, and the ROW format takes precedence, as expected.)

Thus, when a new cell is created the cell formatting should be
If CELL format defined, use CELL format
else if ROW format is defined, use ROW format
else if COLUMN format is defined, use COLUMN format
else DEFAULT format.

I have updated my own version of function TSheet.GetDefaultFormat and now my output spreadsheets are formatted correctly.

Regards,
Edward Benson.

//------------------------------------------------------------------
// My version
function TSheet.GetDefaultFormat(Col,Row: integer): word;
var
XRow: TXLSRow;
begin
XRow := FRows.Find(Row);
// Use ROW format, if defined
if (XRow <> Nil) and (XRow.FormatIndex <> DEFAULT_FORMAT) then begin
XRow.Formats[XRow.FormatIndex].IncUsageCount;
Result := XRow.FormatIndex;
end
// Else use COLUMN format, if defined
else if (FColumns[Col] <> Nil) and (FColumns[Col].FormatIndex <> DEFAULT_FORMAT) then begin
Result := FColumns[Col].FormatIndex;
FColumns[Col].Formats[FColumns[Col].FormatIndex].IncUsageCount;
end
// Else use DEFAULT format
else
Result := DEFAULT_FORMAT;
end;

//------------------------------------------------------------------
// Old version
function TSheet.GetDefaultFormat(Col,Row: integer): word;
var
XRow: TXLSRow;
begin
if (FColumns[Col] <> Nil) and (FColumns[Col].FormatIndex <> DEFAULT_FORMAT) then begin
Result := FColumns[Col].FormatIndex;
FColumns[Col].Formats[FColumns[Col].FormatIndex].IncUsageCount;
end
else begin
XRow := FRows.Find(Row);
if (XRow <> Nil) and (XRow.FormatIndex <> DEFAULT_FORMAT) then begin
XRow.Formats[XRow.FormatIndex].IncUsageCount;
Result := XRow.FormatIndex;
end
else
Result := DEFAULT_FORMAT;
end;
end;

[larsa]

Hello

Excel uses the last selected format for a cell. That is, if you first format a cell, and then the entire row where the cell is on, the cell will have the row format. As XLSReadWriteII not is an interactive component, and you can do things you can't do in excel, there have to be a rule for selecting the format for a cell when new cells are added. The GetDefaultFormat is my choice. If you want the same functionality as in excel, you can format the cells after they are added.

[EdwardBenson]

I did not explain well.
I am setting values into an EXCEL sheet which the end-user created. The end-user creates the formatting, then my code sets the values. Sometimes the user's formatting gets corrupted when TXLSReadWriteII sets the values.

To reproduce -
In EXCEL, create an empty spreadsheet
Select COLUMNs A-E and format as Red
Then select ROWs 1-5 and format as Blue
Then select COLUMNs F-J and format as Yellow
Save
In TXLSReadWriteII open the saved spreadsheet in code
Set value of cell C3 in code, with no specific formatting
Set value of cell H3 in code, with no specific formatting
Save in code

XLSReadWrite := TXLSReadWriteII2.Create(self);
XLSReadWrite.FileName := 'C:\Test.xls';
XLSReadWrite.Read;
XLSReadWrite.Sheets[0].AsString[2,2] := 'I am in Blue Row';
XLSReadWrite.Sheets[0].AsString[7,2] := 'I am in Yellow Column';
XLSReadWrite.Write;

Reopen the spreadsheet in EXCEL
Cell C3 will have changed to Red. This is a bug - It should be Blue.
Cell H3 will be Yellow. This is correct.

Change TSheet.GetDefaultFormat to take ROW format in preference to COLUMN format, as in my original post.
Repeat the test
(N.B. You have to re-create the spreadsheet in EXCEL, you cannnot re-use the sheet previously saved by TXLSReadWriteII, as this now has incorrect formatting).
The two cells are now formatted correctly!
(The reason this works is because EXCEL takes ROW format in precedence to COLUMN format. If the end-user formats a COLUMN after a ROW (as in the yellow columns in my example), EXCEL automatically creates CELL level format records for the intersecting cells, which over-ride the ROW formatting.)

Regards,
Edward Benson.