Um export Delphi armazena o número de conta 123456789012 como número; uma coluna larga do Excel mostra 1.23457E+11, enquanto =A1&"" retorna os doze dígitos completos. O Excel transforma números em texto por duas regras, onze caracteres para a exibição General e quinze dígitos significativos dentro de fórmulas, então um export HotXLS lê a primeira do FormattedText e constrói a segunda com o motor de fórmulas
Quantos caracteres o formato General permite?
Onze, com o sinal de menos fora da contagem. A ECMA-376 Part 1 §18.8.30 diz que a aplicação "deve tentar exibir o número completo com até 11 dígitos (incl. ponto decimal)", e a regra de ponto flutuante dela é mais precisa: o "comprimento total máximo para exibição da célula é 11, sem incluir o sinal negativo". O padrão então esboça quatro condições para mudar para forma exponencial e para por aí. O arredondamento fica em aberto. O ponto de transição para números pequenos também. O comportamento abaixo é o que o Excel mostra numa coluna larga, e é o que o HotXLS reproduz no texto de exibição de uma célula e no TEXT(A1, "General"):
- Um número cuja parte inteira cabe em onze caracteres fica em notação fixa e abre mão das decimais:
12345.6789012345aparece como12345.6789, e12345678901.5arredonda para12345678902 - Uma parte inteira de doze dígitos não cabe, então
123456789012vira1.23457E+11, com a mantissa arredondada para deixar espaço ao expoente - Um número abaixo de 1 mantém notação fixa enquanto ela mostra ao menos tantos dígitos significativos quanto a forma científica de seis dígitos:
0.00012345678exibe como0.000123457, mas0.0000123456789vira1.23457E-05 - O expoente sempre carrega um sinal e ao menos dois dígitos, então 1020 exibe como
1E+20
O sinal fica fora do orçamento. O -12345678901 exibe com os onze dígitos completos atrás do sinal de menos, doze caracteres no total, enquanto o -123456789012 vira -1.23457E+11. O tipo Variant que você atribui não muda o resultado. O Excel guarda todo valor numérico de célula como double, e o HotXLS passa valores Integer, Int64, Currency e Double pela mesma regra de onze caracteres, então Cells[1, 1].Value := Int64(123456789012) também exibe como 1.23457E+11. A regra segue para formatos customizados: texto literal numa seção é adicionado em volta do resultado General, então General" kg" mostra o mesmo número como 1.23457E+11 kg. Formatos com seções vazias ou só de literal seguem regras próprias e podem esconder o número por completo
Mais um limite fica fora de qualquer biblioteca. O padrão acrescenta que "o método ótimo de exibição se baseia na largura de célula disponível", e numa coluna de largura padrão o Excel comprime ainda mais, até 1.2E+11 ou uma fileira de #, dependendo das métricas de fonte da máquina que mostra a planilha. Um servidor que gera arquivos não tem coluna na tela, então o HotXLS deixa esse último passo para quem desenha a célula. Alargue a coluna antes de comparar. Caso contrário você está medindo o fallback de largura do Excel contra a regra General, e os dois vão discordar em qualquer valor mais longo que a coluna
Texto de fórmula mantém quinze dígitos significativos
Assim que um número vira texto dentro de uma fórmula — o operador &, CONCAT, TEXTJOIN, LEN, LEFT — outra regra assume. A ECMA-376 §18.17 define a gramática de fórmulas mas nenhum formato número-para-texto para este passo, então a saída do Excel é a única referência. O Excel arredonda a quinze dígitos significativos e os escreve em notação posicional plain enquanto isso ocupar no máximo vinte caracteres além do sinal. Daí o =1/3&"" retornar 0.333333333333333, quinze três. O =1E+19&"" retorna 10000000000000000000, e o =1E-18&"" retorna 0.000000000000000001. Passando de vinte caracteres, o texto muda para notação científica com o mesmo expoente com sinal de dois dígitos que a grade usa: 1E+20, 1.23456789012345E-05. O separador é sempre um ponto. O HotXLS avalia fórmulas sem consultar as configurações regionais da máquina que roda o seu programa, então uma chave de lookup construída com & sai idêntica num servidor inglês e num alemão
O texto de fórmula tem um canto em que o próprio Excel depende da máquina. CHAR e CODE mapeiam números acima de 127 pela página de código ANSI do computador que roda o Excel, então =CHAR(128) é o sinal do euro numa instalação Windows da Europa Ocidental e um caractere diferente sob outras páginas de código. Um serviço Delphi não pode saber qual página de código o leitor eventual usa. O HotXLS avalia esses códigos como Windows-1252, a página de código da Europa Ocidental, em toda máquina, então =CHAR(150) dá um en dash em qualquer servidor. Se as suas pastas de trabalho se destinam a outra página de código, calcule esses caracteres em Delphi e armazene-os como texto
FloatToStr não segue nenhuma das regras
| Valor | Exibição General / TEXT | Texto de fórmula (&"") | Delphi FloatToStr |
|---|---|---|---|
| 123456789012 | 1.23457E+11 | 123456789012 | 123456789012 |
| 1234567890123456 | 1.23457E+15 | 1234567890123460 | 1.23456789012346E15 |
| 1/3 | 0.333333333 | 0.333333333333333 | 0.333333333333333 |
| 1E20 | 1E+20 | 1E+20 | 1E20 |
| 0.000001 | 0.000001 | 0.000001 | 1E-6 |
| 0.00000123456789 | 1.23457E-06 | 0.00000123456789 | 1.23456789E-6 |
A última coluna explica a deriva. O FloatToStr usa o formato general do Delphi com quinze dígitos, escreve expoentes sem sinal de mais nem zero de preenchimento, e muda para notação exponencial nos seus próprios limiares: abaixo de 0.00001, ou quando a parte inteira precisa de mais de quinze dígitos. Ele também tira o separador decimal do FormatSettings do host. É uma função ótima para arquivos de log. Não reproduz nenhuma das regras do Excel, e um único helper encarregado de servir exibição e texto de fórmula estará errado em toda linha em que as regras discordam — quatro das seis linhas acima
Qual texto um export ou uma chave de lookup deve usar?
Escolha a regra perguntando onde a string será comparada. Texto para pessoas — um relatório PDF, uma tabela HTML, um CSV que alguém abre ao lado da pasta de trabalho — deve casar com a grade, então leia o texto de exibição da célula. Texto usado como dado deve seguir a regra da fórmula: uma chave concatenada, um valor comparado com algo que uma fórmula Excel produziu, uma string de lookup. Um CSV que será recarregado no Excel ou num banco de dados conta como dado também. Escrever 1.23457E+11 num arquivo desse joga fora cinco dígitos do número de conta de vez, porque o importador parseia o que lê e não recupera o original. O primeiro exemplo imprime as três strings lado a lado. As amostras são Doubles, mas um valor Int64 ou Currency exibe do mesmo jeito:
uses
SysUtils, Variants, lxHandleX;
procedure CompareNumberTexts;
const
Samples: array[0..5] of Double =
(123456789012.0, 1234567890123456.0, 1 / 3, 1E20, 0.000001,
0.00000123456789);
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
I: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Numbers');
for I := 0 to High(Samples) do
begin
Sheet.Cells[1, 1].Value := Samples[I]; // formato General por padrão
Writeln(Format('%-22s %-22s %s', [
string(Sheet.Cells[1, 1].FormattedText), // o que a grade mostra
VarToStr(Sheet.Calculate('A1&""')), // o que uma fórmula vê
FloatToStr(Samples[I])])); // a ideia da própria RTL
end;
finally
Book.Free;
end;
end;
Cada linha impressa deve casar com a linha correspondente da tabela. Só os separadores se movem. Num host alemão a coluna de exibição e a coluna FloatToStr mudam para vírgula decimal, como a grade do Excel faz naquela máquina, enquanto a coluna de fórmula mantém o ponto. O segundo exemplo põe a escolha num exporter que escreve texto de relatório do jeito que o Excel exibe e colunas de chave do jeito que as fórmulas Excel constroem:
uses
SysUtils, Classes, Variants, lxHandleX, lxCalc;
// Texto de relatório como exibido no Excel, mais uma coluna de chave independente de locale
procedure ExportInvoiceLines(Sheet: TXLSXWorksheet; FirstRow, LastRow: Integer;
Output: TStrings);
var
Row: Integer;
Shown, Key: string;
begin
for Row := FirstRow to LastRow do
begin
Shown := Sheet.Cells[Row, 3].FormattedText; // valor, como exibido
Key := VarToStr(Sheet.EvaluateFormulaAt(Row, 6,
Format('A%d&"-"&C%d', [Row, Row])).Value); // chave de valor da fatura
Output.Add(Format('%s;%s', [Shown, Key]));
end;
end;
O EvaluateFormulaAt avalia uma fórmula como se ela estivesse na célula dada sem armazená-la, então a chave vem da mesma regra que o Excel aplica a uma coluna de chave dentro da pasta de trabalho. Se o valor carrega um formato de moeda ou de milhares, o FormattedText aplica esse formato, e a regra General só governa células sem um. Alimentar o cálculo com o valor de exibição arredondado é um interruptor diferente no Excel, precisão como exibido, e ele reescreve o double armazenado em si, então mantenha-o fora de um export que só precisa de texto
O que acontece com um ID maior que quinze dígitos?
Ele perde a cauda no momento em que vira texto. Um double guarda todo inteiro até 253, cerca de 9,007 × 1015, então o número de cliente de dezesseis dígitos 1234567890123456 sobrevive intacto na célula e no arquivo, onde dezessete dígitos significativos bastam para um round trip de double XLSX sem perdas. O texto é outra história. A regra General mostra 1.23457E+15, e a regra da fórmula arredonda a quinze dígitos significativos e completa com um zero, então =A1&"" retorna 1234567890123460 — um cliente diferente. O Excel também mantém só quinze dígitos significativos de um número digitado numa célula, então um usuário que redigita o valor cai em 1234567890123450. Um cliente acaba com três números: 1234567890123456 na célula, 1234567890123460 numa chave de fórmula e 1234567890123450 depois de redigitar
Armazene identificadores como texto desde o início. Números de conta, números de cartão e IDs de pedido são rótulos escritos em dígitos; ninguém soma eles, e um zero à esquerda pertence ao rótulo. Atribuídos como strings, eles chegam à grade e a toda fórmula como os mesmos dezesseis caracteres, e um lookup contra eles nunca pode derivar. O Excel marca essas células com um pequeno triângulo verde de números armazenados como texto, um preço justo por uma chave que mantém o último dígito:
// Identificadores entram como texto; valores ficam numéricos
procedure WriteCustomerRow(Sheet: TXLSXWorksheet; Row: Integer;
const CustomerNo: string; Balance: Currency);
begin
Sheet.Cells[Row, 1].Value := WideString(CustomerNo); // '1234567890123456' fica inteiro
Sheet.Cells[Row, 2].Value := Balance; // exibido pela regra General
end;
Antes de mexer no exporter, abra numa planilha do Excel uma pasta de trabalho que ele produziu, alargue a coluna do número de conta e ponha =A1&"" ao lado. Se o sistema downstream compara com a string da esquerda, peça ao componente Excel HotXLS Delphi o FormattedText; se compara com a da direita, construa o texto com EvaluateFormulaAt