Bloques de filas y guardado por streaming en HotXLS

En SpreadsheetML una columna que lleva un formato de moneda necesita exactamente un elemento: <col min="2" max="2" style="3"/>. ECMA-376 Part 1 adjunta un estilo de celda solo en tres sitios: una columna (§18.3.1.13), una fila (§18.3.1.73) y una celda (§18.3.1.4). Reduce el formato a B2:D500000 y SpreadsheetML no tiene un único elemento para esa forma: un estilo de columna correría más allá de la fila 500000 y un estilo de fila cubriría todas las columnas, así que un escritor acaba emitiéndolo como celdas de solo estilo, 1.499.997 de ellas

Un modelo de hoja de cálculo en memoria choca con el mismo muro un paso antes. Si formatear significa fijar una propiedad en cada objeto de celda, un clic en la cabecera de una columna asigna un millón de objetos para cargar un entero pequeño, y un proceso Delphi que solo quería un formato numérico se queda sin espacio de direcciones en un libro que ocupa unos kilobytes en disco. La pregunta de diseño real es dónde vive el formato mientras editas, y cuándo se convierte en datos por celda

Cómo formatea HotXLS un rango sin crear celdas

HotXLS guarda el formato de rangos como intervalos. Cada setter de estilo de TXLSXRange — SetNumberFormat, SetNumberFormatIndex, SetFontIndex, SetFillIndex, SetBorderIndex, SetAlignmentIndex — añade un registro a la lista StyleOverlays de la hoja: cuatro coordenadas, las partes de estilo que la llamada posee y los índices de pool para esas partes. Las celdas que ya existen dentro del rectángulo recogen las partes nuevas al momento. Las coordenadas vacías se quedan vacías, y no se asigna nada para ellas

var
  Workbook: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Edge: TXLSXBorder;
  BorderIdx, Row: Integer;
  Stats: TXLSXCellStorageStatistics;
begin
  Workbook := TXLSXWorkbook.Create;
  try
    Sheet := Workbook.Sheets.Add('Ledger');
    Sheet.WriteStringRowValues(1, 2, ['Net', 'Tax', 'Gross']);
    for Row := 2 to 1001 do
      Sheet.WriteFloatRowValues(Row, 2, [Row * 1.5, Row * 0.3, Row * 1.8]);

    // Columnas B..D sobre las 1.048.576 filas: un intervalo por llamada
    Sheet.RCRange[1, 2, XlsxMaxRow, 4].SetNumberFormat('#,##0.00 "EUR"');

    // Construye el borde una vez y aplícalo por índice
    Edge := TXLSXBorder.Create;
    try
      Edge.SetAll(xlsxBorderThin);
      BorderIdx := Workbook.Borders.LookupOrAdd(Edge); // base 1
    finally
      Edge.Free;
    end;
    Sheet.RCRange[1, 2, XlsxMaxRow, 4].SetBorderIndex(BorderIdx);

    Stats := Sheet.Cells.GetStorageStatistics;
    // Espera 3.003: tres celdas de cabecera más 3.000 valores
    WriteLn('Cells: ', Stats.CellCount);
    if Workbook.SaveAs('ledger.xlsx') <> 1 then
      raise Exception.Create('Save failed');
  finally
    Workbook.Free;
  end;
end;

Las dos llamadas de formato cubrieron 3.145.728 coordenadas y no añadieron nada a ese recuento. Los intervalos empiezan en la fila 1 a propósito: un intervalo de columna completa se guarda como un elemento <col>, y las cabeceras de texto de la fila 1 no se ven afectadas por un formato numérico de todos modos. Los rangos que devuelven RCRange, Range['A1:C5'], EntireColumn y EntireRow pertenecen a la hoja, así que el ejemplo nunca los libera

TXLSXFormatParts es lo que evita que varios intervalos se peleen. Sus miembros son xfpFont, xfpFill, xfpBorder, xfpNumberFormat, xfpAlignment, xfpProtection y xfpCellControl, y cada setter de TXLSXRange reclama exactamente uno de ellos. Cuando se resuelve una celda, los intervalos que la cubren se aplican en el orden en que se añadieron, cada uno escribiendo solo sus propias partes, y el formato explícito sobre la propia celda gana a todos. Un intervalo de columna que fija el formato numérico y un intervalo de fila que fija un relleno llegan ambos intactos a la celda B5

Cuando un registro debe cargar varias partes, Sheet.StyleOverlays.Add(Row1, Col1, Row2, Col2, Parts, State) toma un TXLSXCellStyleState con los índices de pool rellenados. Add recorta las coordenadas a la cuadrícula de 1.048.576 × 16.384, intercambia esquinas invertidas y devuelve nil sin registrar nada cuando Parts está vacío. Las ediciones estructurales mantienen los registros al día: InsertRows dentro de un bloque formateado ensancha el intervalo el número de insertadas, e insertar encima lo desplaza entero hacia abajo

¿Por qué SetBorders crea celdas cuando SetBorderIndex no?

TXLSXRange.SetBorders(Kind, Style, Color) tiene que mirar cada celda por su cuenta, porque la respuesta depende de la posición. Con xlsxEdgeOutline la fila superior recibe un borde superior, la columna izquierda un borde izquierdo, y el interior nada; con xlsxEdgeInside es más o menos al revés. La llamada además funde los bordes nuevos con el borde que la celda ya tenga, así que Outline seguido de Inside construye una cuadrícula completa. Esa composición por celda pasa por Cells[R, C], que crea una celda cuando no existe. En un bloque de informe de 10 × 40 es exactamente lo correcto. En EntireColumn(3) con xlsxEdgeAll son 1.048.576 objetos de celda nuevos

Así que la división es simple. Para cualquier cosa más alta que una pantalla de datos, construye el borde una vez y aplícalo por índice, como hace el primer ejemplo: TXLSXBorder.SetAll, luego Workbook.Borders.LookupOrAdd para un índice base 1, luego SetBorderIndex. Reserva SetBorders para bloques cuyo contorno y líneas internas difieran, como el bloque de informe de 10 × 40 de arriba

Leer los formatos sin deshacer el trabajo

La misma trampa caza al código que solo lee. Cells[R, C] es un accesor de creación ante fallo: pregúntale por el formato numérico de B500000 y obtienes la respuesta correcta más una celda nueva, con estilo y vacía en B500000 que ahora se almacenará y guardará. Haz eso en bucle sobre una columna para auditar el formato y habrás reconstruido el millón de objetos que el intervalo quería evitar, una lectura inocente cada vez

TXLSXWorksheet.GetCellVisibleFormat resuelve el estilo efectivo de una coordenada sin añadir celda. Funda el valor por defecto de la hoja, los intervalos de fila y columna, los intervalos de rectángulo, los estilos de tabla y cualquier formato explícito sobre la celda. La sobrecarga con un parámetro out ABuiltinNumberFormat informa de un formato Excel incorporado como 14 (fecha corta) separado de NumberFormatIndex, que apunta al pool propio NumberFormats del libro. Cells.HasCell(R, C) responde si existe una celda física

var
  Sheet: TXLSXWorksheet;
  Before, After: TXLSXCellStorageStatistics;
  State: TXLSXCellStyleState;
  Row, Builtin, Unformatted, Physical: Integer;
begin
  Sheet := Workbook.Sheets[1];  // Sheets es base 1
  Before := Sheet.Cells.GetStorageStatistics;
  Unformatted := 0;
  Physical := 0;
  for Row := 1 to 200000 do
  begin
    // Estilo efectivo, resuelto sin añadir una celda a la hoja
    State := Sheet.GetCellVisibleFormat(Row, 2, Builtin);
    if (State.NumberFormatIndex = 0) and (Builtin = 0) then
      Inc(Unformatted);
    if Sheet.Cells.HasCell(Row, 2) then
      Inc(Physical);
    // Sheet.Cells[Row, 2].NumberFormatIndex responde la misma pregunta,
    // pero deja un objeto de celda nuevo tras cada fila que visita
  end;
  After := Sheet.Cells.GetStorageStatistics;
  // Ambos recuentos deberían ser idénticos antes y después del bucle
  WriteLn(Format('cells %d -> %d, styled blanks %d -> %d',
    [Before.CellCount, After.CellCount,
     Before.StyledBlankCellCount, After.StyledBlankCellCount]));
end;

Ejecuta una comprobación así una vez contra tu carga de trabajo real. StyledBlankCellCount cuenta las celdas que tienen estilo y ni valor, ni fórmula, ni texto enriquecido. Si sube durante una pasada que se suponía de solo lectura, alguna ayuda está creando celdas puramente para cargar estilo, normalmente llamando a Cells[R, C] para decidir si una celda necesita formato

Lo que significan los bloques de 256 filas para tu código

Las celdas físicas viven en bloques de 256 filas. Dentro de un bloque, cada fila poblada guarda sus celdas en una lista ordenada por columna; una fila sin celdas es solo una ranura vacía, y un tramo de 256 filas sin celdas no tiene bloque alguno. Una hoja dispersa con datos en las filas 3, 7 y 512 guarda por tanto dos bloques. El guardado recorre la misma estructura en orden por filas, que es el orden que SpreadsheetML exige para los elementos <row> y sus hijos <c>

La referencia TXLSXCell que sujetas sigue siendo el mismo objeto mientras otras celdas se añaden o quitan a su alrededor; los bloques reorganizan su propia contabilidad, no tus handles. Los escritores en masa como WriteFloatRowValues y WriteStringRowValues toman una vía más rápida en una hoja virgen y añaden valores por filas a un almacén empaquetado. El primer acceso por celda a través de Cells[R, C] convierte ese almacén en objetos de celda una vez. GetStorageStatistics muestra en qué estado estás mediante PackedStorageActive y PackedCellCount, junto a RowBlockCount y PopulatedRowCount

Los pools de estilos se recortan al guardar. Antes de escribir styles.xml, HotXLS reconstruye los pools de fuentes, rellenos, bordes, formatos numéricos, alineaciones y protecciones para que contengan solo las entradas aún en uso, y un libro que un usuario estuvo restilizando una hora no distribuye cada relleno abandonado. Las celdas fijadas a un FormatIndex absoluto conservan su ranura intacta

Guardados con flujo de filas y los números que lo demuestran

TXLSXWorkbook.StreamingWrite vale True por defecto. Con él activado, SaveAs genera cada <row> de hoja y lo escribe directamente en el stream deflate de su entrada ZIP, así que el mayor trozo de XML de hoja en memoria en cada momento es una sola fila. Con él desactivado, el XML de cada hoja se construye primero como una cadena completa y se comprime después, y la memoria pico sigue a la hoja más grande. Las celdas de solo estilo que los intervalos de rectángulo se convierten, el caso B2:D500000 de la apertura, se generan dentro de ese bucle por filas también, así que añaden al archivo pero jamás se sientan en memoria como un bloque único de XML

Las cadenas compartidas y los registros de caché de tabla dinámica siguen el mismo patrón. Cada entrada <si> o <r> se genera y escribe por su cuenta, así que en un libro analítico ancho la tabla de cadenas compartidas, frecuentemente mayor que cualquier hoja individual, nunca tiene que existir como un bloque único de XML

Nada de esto hay que tomárselo por fe. Tras cada guardado el libro expone cuatro propiedades de solo lectura: LastSaveUsedStreaming, LastSavePeakRowXmlChars (la fila única más larga, en caracteres), LastSaveBufferedWorksheetXmlChars (el XML de hoja total retenido en memoria, cero con streaming) y LastSavePeakAuxiliaryXmlChars (la entrada de cadena compartida o tabla dinámica más larga). LastSaveUsedStreaming es True para salida .xlsx, .xltx y .xltm y para el escritor binario .xlsb; la salida OpenDocument pasa por su propio escritor e informa False

procedure ReportSave(Workbook: TXLSXWorkbook; const FileName: string;
  Streaming: Boolean);
begin
  Workbook.StreamingWrite := Streaming;
  if Workbook.SaveAs(FileName) <> 1 then
    raise Exception.CreateFmt('Save failed: %s', [FileName]);
  WriteLn(Format('streaming=%s peakRow=%d buffered=%d peakAux=%d',
    [BoolToStr(Workbook.LastSaveUsedStreaming, True),
     Workbook.LastSavePeakRowXmlChars,
     Workbook.LastSaveBufferedWorksheetXmlChars,
     Workbook.LastSavePeakAuxiliaryXmlChars]));
end;

// El mismo libro, los dos modos; mira la memoria del proceso en tu profiler
ReportSave(Workbook, 'ledger-stream.xlsx', True);
ReportSave(Workbook, 'ledger-buffered.xlsx', False);

Sobre tus propios datos, la línea con búfer muestra una cifra buffered del orden del XML de la hoja entera, mientras que la línea con streaming muestra cero y una peakRow no mayor que tu fila más ancha. Deja StreamingWrite activado. El flag decide solo si el XML de la hoja se ensambla entero antes de comprimir, y LastSaveBufferedWorksheetXmlChars informa del tamaño de ese ensamblado en caracteres

Dónde se va el resto de la memoria

Una vez que celdas y estilos son compactos, los siguientes mayores consumidores en un libro grande son la tabla de cadenas compartidas y las partes satélite que lleve el archivo: cachés de tabla dinámica, dibujos y XML preservado de partes que el modelo de objetos no modela. Esas tienen sus propias palancas, y ningún ajuste único las cubre todas

Si el cuello de botella es abrir en lugar de guardar, la carga selectiva es el sitio por donde empezar: el recorrido de la carga de hojas solo metadatos y selectiva cubre leer un libro sin pagar por las hojas que no tocarás. Para rendimiento de la vía de lectura en archivos muy grandes, mira las notas sobre el parseo XLSX en paralelo y el asignador de memoria, y para trabajos de solo salida que nunca necesitan un modelo de objetos, las escrituras con streaming para lotes de servidor encaja mejor que cualquier cantidad de ajustes aquí

HotXLS lee y escribe XLS y XLSX desde código nativo de Delphi y C++Builder sin Excel ni automatización OLE, así que cada número de arriba sale de tu propio proceso y puede comprobarse ahí. Los formatos soportados y las versiones de RAD Studio están listados en la página del componente de hojas de cálculo HotXLS para Delphi