A row and a column have different formatting, but their crossing looks different after an XLSX report is saved. Row defaults take precedence over column defaults in the file. HotXLS writes an explicit cell style where the crossing needs a different result to preserve the intended formatting order
An XLSX worksheet can store row and column defaults independently. A stored cell can carry a style of its own, giving the crossing a third source of formatting. If you find one location changes appearance after saving, open the worksheet XML and trace the row style, the column range and any cell record at that address. Those records tell a more complete story than the displayed color alone
A small example exposes the problem
Take a heading row with a gray fill and a column subsequently formatted to identify a category. The intended result at their crossing depends on both changes and their order. If the file contains only row and column defaults, the spreadsheet reader applies its precedence rules; a cell style can express a result that differs from the row default at that one coordinate. HotXLS checks the complete style at affected intersections and writes cell formatting where needed. Complete means more than fill. Two crossings may have the same background while differing in font, border, number format, alignment or protection. Templates make the issue easy to overlook: one crossing may contain a value and have a cell record, while another appears empty. A test limited to populated cells can miss the input area that users fill in after the workbook is reopened
Stored cells have their own style
A stored cell can select its own style. In XLSX, a stored cell without an s attribute uses the default cell style, as does one with s="0"; the presence of row and column defaults does not simply replace that stored style. Look for the cell record before you treat the coordinate as an unoccupied location governed only by those defaults. A cell that appears blank may still carry a formula, a style or a deliberately stored empty value. An invoice template, for example, may outline empty input cells before anyone enters a number. If a diagnostic discards every visually blank location, it can discard the very border or number format the template is meant to preserve
Inspect the resolved format in Delphi
HotXLS exposes GetCellVisibleFormat for a coordinate. The overload used below also returns the built-in number-format identifier. The routine opens a workbook and prints resolved style indexes for row 3, column 2, giving a starting point for tracing the crossing
uses
SysUtils, lxHandleX;
procedure InspectIntersection(const FileName: string);
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Style: TXLSXCellStyleState;
BuiltinNumberFormat: Integer;
begin
Book := TXLSXWorkbook.Create;
try
if Book.Open(FileName) <> 1 then
raise Exception.Create('Cannot open workbook');
if Book.Sheets.Count = 0 then
raise Exception.Create('Workbook has no sheets');
Sheet := Book.Sheets[1];
Style := Sheet.GetCellVisibleFormat(3, 2, BuiltinNumberFormat);
Writeln('Font index: ', Style.FontIndex);
Writeln('Fill index: ', Style.FillIndex);
Writeln('Border index: ', Style.BorderIndex);
Writeln('Built-in number format: ', BuiltinNumberFormat);
finally
Book.Free;
end;
end;The workbook must contain a first worksheet, and the coordinates should be changed to the report being investigated. An index points into that workbook's style collections; it is not itself a color or a font name. Follow the referenced definitions when you decide whether the format describes the intended result before and after a save
Why can an empty intersection need a cell style?
A later column change can need to remain visible at a crossing where the saved row default would otherwise win. The writer may express that result through an explicit cell style, even when the application did not place a value at that coordinate. HotXLS checks the complete style against the serialized row default before counting a crossing for this work; an equivalent result needs no compensating cell. Existing cells whose own output is certain can cover coordinates too, while a cell-write callback that may skip a cell calls for a more cautious count. The size of the formatted ranges matters. A whole row crossing a whole column can describe many possible locations even when the report occupies only a small part of the sheet
Inspect the saved intersections
Build a small grid with a formatted row, a differently formatted column and three kinds of crossing: a cell with a value, a stored cell that looks blank, and a coordinate with no cell record. Apply the row and column changes in both orders so you can trace their effect, save each workbook and reopen it. Read the resolved style at the crossings, including font, fill, border, alignment and number format. A reset to a default value belongs in the exercise too; zero can be an intentional formatting operation. For the package view, open the worksheet XML with xl/styles.xml: the worksheet supplies row, column and cell references, while the style table gives those references meaning. We would start with this small grid before moving to a whole-sheet template, where a single unexpected crossing is harder to isolate
The second routine walks a grid around the crossing and prints resolved indexes for its neighboring coordinates. Pass a worksheet from the open workbook; the row and column coordinates in this API are one-based. Check the same coordinates after reopening
procedure InspectCrossings(Sheet: TXLSXWorksheet);
var
Row, Col, BuiltinNumberFormat: Integer;
Style: TXLSXCellStyleState;
begin
for Row := 2 to 4 do
for Col := 1 to 3 do
begin
Style := Sheet.GetCellVisibleFormat(Row, Col, BuiltinNumberFormat);
Writeln(Format('R%dC%d font=%d fill=%d border=%d numFmt=%d',
[Row, Col, Style.FontIndex, Style.FillIndex, Style.BorderIndex,
BuiltinNumberFormat]));
end;
end;A save can reorganize style pools, so an index in one workbook should be resolved against that workbook's own definitions before it is compared with another. At the XML level, a coordinate without a c element differs from a stored cell whose displayed value is empty. A blank input cell may already carry a border or number format, and that formatting can matter before the user types anything. Include both forms when tracing the worksheet's style references and the output that a spreadsheet application displays. The grid routine prints indexes for font, fill and border together with the built-in number-format identifier. You can use those numbers to locate the associated definitions, then check the properties that the report actually depends on. For example, a matching fill does not establish that a date format, border or font has been preserved. Those properties need their own checks at the crossing and at its neighboring cells. When a crossing changes, also follow the row and column references for the coordinates next to it. They provide the surrounding defaults against which the crossing's explicit cell style is interpreted. That view is useful when a report contains a formatted input area as well as populated heading cells
How large can the formatted intersection be?
Formatting very broad ranges can create a large number of candidate crossings. HotXLS allows up to 1,048,576 coordinates requiring compensating cell styles in a worksheet build; this count concerns those intersections, rather than all source cells in the workbook. A report can look mostly empty while broad row and column defaults still cover a large area. Limit formatting to your report region when that matches the design instead of styling an entire column simply for convenience. For an existing template, read the resolved format in HotXLS and open the output in the spreadsheet application used by recipients. If a cell-write callback participates, record which cells it writes and repeat the grid without that callback: a skipped cell can otherwise be confused with a style decision
For other layout questions, see merged cells in report templates and XLSX round-trip preservation. The Delphi workbook and style APIs above belong to the HotXLS Delphi Component