Três funcionalidades no HotXLS partilham uma folha de cálculo mas operam sobre objetos totalmente diferentes, e os problemas começam quando se assume que realizam tarefas semelhantes. A validação de dados associa uma regra a um intervalo que restringe o que o utilizador pode escrever nele. Um AutoFilter associa uma definição de critérios armazenada a uma área e altera as linhas que o visualizador exibe. Uma tabela envolve um intervalo numa estrutura nomeada e tipificada com estilos de faixas. Uma restringe a escrita, outra guarda uma vista e a terceira impõe um esquema. Nenhuma delas altera o valor de uma única célula por si só, e o AutoFilter em particular costuma induzir em erro, porque a palavra sugere uma ação quando na realidade guarda apenas uma definição. Saber qual o objeto alterado por cada chamada, e quando o efeito se manifesta na prática, é o que distingue um livro com comportamento idêntico no Excel e nos seus testes de outro que diverge silenciosamente.
O AutoFilter guarda uma definição, não oculta fisicamente as linhas
Um AutoFilter num ficheiro guardado é apenas um registo de critérios. A ocultação das linhas ocorre mais tarde, quando o Excel abre o livro e avalia os critérios em relação aos dados. O HotXLS grava esse registo fielmente e não remove nada: todas as linhas que filtrou continuam fisicamente presentes no ficheiro. Um processo que aplique um filtro para descartar encomendas rejeitadas e depois volte a ler o livro verá todas elas, incluindo as rejeitadas, o que constitui um comportamento correto segundo a API mas contrário ao modelo mental do programador. Na folha de cálculo XLSX, a função SetAutoFilter declara a área de filtragem e AddAutoFilterColumn associa critérios a uma coluna dessa área. Quando o código do servidor necessita do resultado real, seja para obter uma contagem de linhas num resumo ou para reencaminhar apenas as linhas correspondentes, a biblioteca avalia os critérios por si em vez de simular uma alteração no ficheiro:
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
R, Visible: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('orders.xlsx');
Sheet := Book.Sheets[0];
Sheet.SetAutoFilter('A1:E500');
// Column id 3 = fourth column INSIDE the filter range (0-based offset)
Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');
Visible := 0;
for R := 2 to 500 do
if Sheet.AutoFilterRowVisible(R) then
Inc(Visible);
// Visible now matches what Excel will show after opening the file
Book.SaveAs('orders-filtered.xlsx');
finally
Book.Free;
end;
end;O método AutoFilterRowVisible responde por cada linha, e PreviewAutoFilterRows percorre a região completa através de uma função de retorno (callback) quando necessita do conjunto correspondente numa única passagem. Há um caso em que nenhuma das opções é a resposta: se o requisito for que as linhas excluídas não existam de todo no ficheiro, por motivos de privacidade e não apenas de visualização, elimine as linhas definitivamente. O filtro é a ferramenta errada para esse fim, pois qualquer destinatário o consegue limpar com um clique e os dados que pretendia reter regressarão ao ecrã.
O ID da coluna é um desvio (offset), não o número da coluna
O comentário no trecho de código acima alerta para a armadilha que mais tempo de depuração consome nesta API. A função AddAutoFilterColumn identifica o destino pela sua posição baseada em 0 dentro do intervalo do filtro, e não pela coluna da folha de cálculo. Para um filtro em A1:E500, as duas numerações diferem apenas em uma unidade, o tipo de detalhe que passa num teste rápido e falha assim que um colega filtra uma coluna diferente. Para um filtro que comece na coluna C, o ID 0 significa coluna C, e a discrepância torna-se óbvia rapidamente. Quando o intervalo do filtro for calculado em tempo de execução, obtenha o ID da coluna a partir da mesma variável que construiu a string de intervalo, e nunca a partir de uma constante de coluna da folha de cálculo. Cada coluna aceita uma segunda condição através da sobrecarga que recebe dois operadores, dois critérios e um conector and/or, o que reflete a caixa de diálogo de filtros personalizados do Excel. A fachada XLS com SetAutoFilter e ApplyAutoFilter, cujos parâmetros de critérios e operadores seguem as convenções mais antigas de estilo COM e numeram o campo a partir de 1. Alternar entre fachadas significa alternar as bases dos índices, pelo que o local da chamada deve incluir um comentário a indicar qual delas está a ser usada.
As regras de validação são o contrato sob o qual os seus utilizadores editam
Das três funcionalidades, a validação é a única que restringe ativamente as introduções de dados futuras, sendo a que mais atenção de design exige nos livros distribuídos para preenchimento e devolvidos para processamento. A variante de lista assume a maior parte deste trabalho:
var
Idx: Integer;
begin
Idx := Sheet.AddListValidation('C2:C500', 'New,Approved,Blocked');
Sheet.DataValidations[Idx].SetPrompt('Status',
'Pick one of the listed states');
Sheet.DataValidations[Idx].SetError('Invalid status',
'Type or paste only listed values', xlsxDvErrStop);
Sheet.DataValidations[Idx].AllowBlank := False;
// Quantities: whole numbers, zero or more
Sheet.AddWholeNumberValidation('D2:D500', xlsxDvOpGreaterOrEqual, '0');
end;Além de listas e números inteiros, a mesma família abrange decimais, datas, horas, comprimento de texto e fórmulas livres através de AddCustomValidation, e o método genérico AddDataValidation expõe a matriz completa de tipos e operadores para construtores de regras baseados em configuração. O estilo de erro é mais relevante do que o seu nome sugere. O xlsxDvErrStop rejeita a entrada incorreta por completo; os estilos de aviso e informação permitem a gravação do valor após um clique. Escolha por coluna conforme a tolerância do código que lerá os dados a valores fora da regra. Duas limitações devem constar no texto de ajuda (prompt) ou no ficheiro README que acompanha o documento. A validação no Excel protege a escrita manual, mas colar (paste) um bloco sobre um intervalo validado contorna a regra, pelo que qualquer código de leitura deve voltar a validar em vez de confiar nas células. E uma regra cobre exatamente o intervalo que lhe indicou, pelo que configurar a validação antes de conhecer a contagem de linhas final deixa o final do intervalo desprotegido. Escreva os dados primeiro e só depois ajuste as regras à dimensão real.
A fachada antiga oferece as mesmas famílias de regras com uma diferença ergonómica. Os criadores do lado XLS, designadamente AddWholeNumberValidation, AddDecimalValidation, AddDateValidation, AddTimeValidation, AddTextLengthValidation e AddCustomValidation, devolvem o objeto TDataValidation diretamente em vez de um índice, pelo que a configuração do prompt e do erro é feita diretamente na referência devolvida, sem necessidade de pesquisa. A enumeração de operadores (xlsDvBetween, xlsDvGreaterThan, entre outros) reflete o conjunto XLSX, facilitando a portabilidade do código de regras entre fachadas, exceto pela diferença no estilo de retorno. O texto de ajuda merece tanta atenção como a regra em si. Uma lista suspensa que rejeite a entrada com uma caixa de erro em branco motiva os utilizadores a contactar o suporte; uma que indique as opções válidas ensina-os a corrigir a célula e prosseguir.
Uma inversão de polaridade que a biblioteca resolve por si
Qualquer programador que tenha analisado o XML de validação OOXML manualmente já se deparou com a inversão do atributo showDropDown: na norma ISO/IEC 29500, um valor verdadeiro significa "ocultar a seta da lista suspensa", o oposto do que o nome sugere. O HotXLS inverte este comportamento internamente, pelo que a propriedade ShowDropDown numa regra de validação atua de forma direta: o valor verdadeiro exibe a seta. O único problema pode surgir ao misturar interpretações, definindo a propriedade por código enquanto um colega audita o XML guardado e "corrige" o atributo que parece invertido. Decida se a propriedade ou o XML bruto é a referência para as suas ferramentas de auditoria e registe essa inversão por escrito.
As tabelas atribuem um esquema e um nome a um intervalo
Uma tabela de folha de cálculo (o ListObject nos termos do Excel) envolve um intervalo com um nome, colunas tipificadas, estilos de faixas e suporte para referências estruturadas. É a funcionalidade que confere um aspeto profissional a um livro gerado assim que os utilizadores começam a ordenar e expandir os dados. A criação é simétrica em ambas as fachadas, com a função AddTable a receber um nome, um intervalo e uma lista de colunas:
var
Cols: TStringList;
begin
Cols := TStringList.Create;
try
Cols.CommaText := 'OrderId,Customer,Status,Amount,Owner';
Sheet.AddTable('Orders', 'A1:E500', Cols);
finally
Cols.Free;
end;
end;Do lado XLSX, o objeto de tabela resultante expõe a propriedade StyleName (como a família predefinida TableStyleMedium2 e equivalentes), controlos de listras e a flag de linha de totais, pelo que aplicar o estilo visual da empresa é uma mera atribuição de propriedade e não uma formatação manual. Em ficheiros .xls legados, a mesma chamada grava os registos de tabela BIFF8, e a fachada disponibiliza também AddPivotTable para visualizações de resumo construídas com campos de linha, coluna e dados, lembrando que as "tabelas" no formato antigo são mais abrangentes do que o ListObject do OOXML. Atribua nomes às tabelas da mesma forma que nomeia as vistas da base de dados. O código posterior que lê Orders[Amount] por referência estruturada resiste a alterações de ordenação de colunas que quebrariam o código baseado em posições fixas.
Duas convenções poupam trabalho de correção posterior. O Excel exige que os nomes das tabelas sejam únicos em todo o livro, pelo que um gerador que crie uma folha por região necessita de um esquema como Orders_EMEA em vez de reutilizar Orders. Um nome duplicado não falha na gravação; manifesta-se como uma caixa de diálogo de erro de reparação ao abrir o ficheiro, o pior local para o detetar. A outra convenção diz respeito à linha de totais: quando ativada, situa-se imediatamente abaixo do intervalo de dados, pelo que qualquer código que adicione dados pela lógica de "última linha usada mais uma" escreverá na secção de totais e não a seguir a esta. Controle a dimensão dos dados de forma independente da tabela e as adições ocorrerão no local esperado.
Estas três funcionalidades articulam-se de forma natural em documentos de introdução de dados. A tabela define a área editável, a validação restringe as colunas de digitação e um filtro pré-configurado poupa os cliques iniciais ao utilizador. Há vantagens em entregar um documento com um filtro já aplicado para focar a atenção nas linhas relevantes, desde que recorde que os registos ocultos permanecem no ficheiro e qualquer utilizador atento os pode revelar. A introdução eficiente de resultados de consultas na folha, a fase inicial deste pipeline, é detalhada no artigo exportar resultados de bases de dados para o Excel a partir do Delphi, e os livros onde as fórmulas resumem os dados validados tiram proveito de nomes definidos para referências estáveis entre folhas.
A validação, os filtros e as tabelas constituem a diferença entre fornecer uma grelha de valores e fornecer uma pequena aplicação funcional. A referência completa para regras, filtros e tabelas está disponível na página de produto do HotXLS Component.