Trabalhar com pastas de trabalho usando a API JavaScript do Excel
Este artigo fornece exemplos de código que mostram como executar tarefas comuns com pastas de trabalho usando a API JavaScript do Excel. Para obter a lista completa de propriedades e métodos compatíveis com o Workbook objeto, consulte Objeto Workbook (API JavaScript para Excel). Este artigo aborda também ações em nível de pasta de trabalho executadas através do objeto Application.
O objeto Workbook é o ponto de entrada para que se suplemento interaja com o Excel. Ele mantém conjuntos de planilhas, tabelas, Tabelas Dinâmicas e muito mais, através dos quais os dados do Excel são acessados e alterados. O objeto WorksheetCollection dá a seu suplemento acesso a todos os dados de pastas de trabalho através de planilhas individuais. Especificamente, ele permite seu suplemento adicione planilhas, navegue entre elas e atribua manipuladores a eventos de planilhas. O artigo Trabalhar com planilhas usando a API JavaScript do Excel descreve como acessar e editar planilhas.
Obter a célula ativa ou o intervalo selecionado
O objeto Workbook contém dois métodos que obtêm um intervalo de células que o usuário ou o suplemento selecionaram: getActiveCell() e getSelectedRange(). getActiveCell() obtém a célula ativa da pasta de trabalho como um objeto Range. O exemplo a seguir mostra uma chamada para getActiveCell(), seguida do endereço da célula que está sendo impresso no console.
await Excel.run(async (context) => {
let activeCell = context.workbook.getActiveCell();
activeCell.load("address");
await context.sync();
console.log("The active cell is " + activeCell.address);
});
O método getSelectedRange() retorna o intervalo único selecionado atualmente. Se houver vários intervalos selecionados, será gerado um erro InvalidSelection. O exemplo a seguir mostra uma chamada para getSelectedRange() que, em seguida, define a cor de preenchimento do intervalo como amarelo.
await Excel.run(async (context) => {
let range = context.workbook.getSelectedRange();
range.format.fill.color = "yellow";
await context.sync();
});
Criar uma pasta de trabalho
O suplemento pode criar uma nova pasta de trabalho separada da instância do Excel, na qual o suplemento está sendo executado atualmente. O objeto do Excel tem o método createWorkbook para esta finalidade. Quando esse método é chamado, a nova pasta de trabalho é aberta imediatamente e exibida em uma nova instância do Excel. O suplemento permanece aberto e em execução com a pasta de trabalho anterior.
Excel.createWorkbook();
O método createWorkbook também cria uma cópia de uma pasta de trabalho existente. O método aceita uma representação de cadeia de caracteres codificada em Base64 de um arquivo .xlsx como parâmetro opcional. A pasta de trabalho resultante será uma cópia desse arquivo, supondo que o argumento da cadeia de caracteres seja um arquivo. xlsx válido.
Você pode obter a pasta de trabalho atual do suplemento como uma cadeia de caracteres codificada com base64 usando o corte de arquivo. A classe FileReader pode ser usada para converter um arquivo em uma cadeia de caracteres codificada com Base64, como demonstrado no seguinte exemplo.
// Retrieve the external workbook file and set up a `FileReader` object.
let myFile = document.getElementById("file");
let reader = new FileReader();
reader.onload = (function (event) {
Excel.run(function (context) {
// Remove the metadata before the base64-encoded string.
let startIndex = reader.result.toString().indexOf("base64,");
let externalWorkbook = reader.result.toString().substr(startIndex + 7);
Excel.createWorkbook(externalWorkbook);
return context.sync();
});
});
// Read the file as a data URL so we can parse the base64-encoded string.
reader.readAsDataURL(myFile.files[0]);
Inserir uma cópia de uma pasta de trabalho para a seção atual
O exemplo anterior mostra uma nova pasta de trabalho criada a partir de uma pasta de trabalho. Você também pode copiar algumas ou todas de uma pasta de trabalho para a atualmente associada com o suplemento. Uma pasta de trabalho tem o insertWorksheetsFromBase64 método para inserir cópias das planilhas da pasta de trabalho de destino em si mesma. O arquivo da outra pasta de trabalho é passado como uma cadeia de caracteres codificada com base64, assim como a Excel.createWorkbook chamada.
insertWorksheetsFromBase64(base64File: string, options?: Excel.InsertWorksheetOptions): OfficeExtension.ClientResult<string[]>;
Importante
O insertWorksheetsFromBase64 método tem suporte para Excel na Web, no Windows e no Mac. Não há suporte para iOS. Além disso, em Excel na Web, esse método não dá suporte a planilhas de origem com elementos PivotTable, Chart, Comment ou Slicer. Se esses objetos estiverem presentes, o insertWorksheetsFromBase64 método retornará o UnsupportedFeature erro em Excel na Web.
O exemplo de código a seguir mostra como inserir planilhas de outra pasta de trabalho na pasta de trabalho atual. Esse exemplo de código primeiro processa um arquivo de pasta de trabalho com um FileReader objeto e extrai uma cadeia de caracteres codificada base64 e insere essa cadeia de caracteres codificada base64 na pasta de trabalho atual. As novas planilhas são inseridas após a planilha chamada Sheet1. Observe que [] é passado como o parâmetro para a propriedade InsertWorksheetOptions.sheetNamesToInsert . Isso significa que todas as planilhas da pasta de trabalho de destino são inseridas na pasta de trabalho atual.
// Retrieve the external workbook file and set up a `FileReader` object.
let myFile = document.getElementById("file");
let reader = new FileReader();
reader.onload = (event) => {
Excel.run((context) => {
// Remove the metadata before the base64-encoded string.
let startIndex = reader.result.toString().indexOf("base64,");
let externalWorkbook = reader.result.toString().substr(startIndex + 7);
// Retrieve the current workbook.
let workbook = context.workbook;
// Set up the insert options.
let options = {
sheetNamesToInsert: [], // Insert all the worksheets from the source workbook.
positionType: Excel.WorksheetPositionType.after, // Insert after the `relativeTo` sheet.
relativeTo: "Sheet1" // The sheet relative to which the other worksheets will be inserted. Used with `positionType`.
};
// Insert the new worksheets into the current workbook.
workbook.insertWorksheetsFromBase64(externalWorkbook, options);
return context.sync();
});
};
// Read the file as a data URL so we can parse the base64-encoded string.
reader.readAsDataURL(myFile.files[0]);
Protege a estrutura da pasta de trabalho
O suplemento pode controlar a capacidade de um usuário de editar dados em uma pasta de trabalho. A propriedade protection do objeto Workbook é um objeto WorkbookProtection com um método protect(). O exemplo a seguir mostra um cenário básico ativando/desativando a proteção da pasta de trabalho.
await Excel.run(async (context) => {
let workbook = context.workbook;
workbook.load("protection/protected");
await context.sync();
if (!workbook.protection.protected) {
workbook.protection.protect();
}
});
O método protect aceita um parâmetro opcional de cadeia de caracteres. Esta cadeia de caracteres representa a senha necessária para um usuário ignorar a proteção e alterar a estrutura da pasta de trabalho.
A proteção também ser definida no nível da planilha para prevenir a edição de dados indesejada. Para saber mais, confira a seção Proteção de dadosdo artigo Trabalhar com planilhas usando a API JavaScript do Excel.
Observação
Para saber mais sobre a proteção de pastas de trabalho no Excel, confira o artigo Proteger uma pasta de trabalho.
Acessar propriedades do documentos
Objetos Workbook têm acesso aos metadados dos arquivos do Office, que são conhecidos como propriedades de documentos. A propriedade do properties objeto Workbook é um objeto DocumentProperties que contém alguns desses valores de metadados. O exemplo a seguir mostra como definir a author propriedade.
await Excel.run(async (context) => {
let docProperties = context.workbook.properties;
docProperties.author = "Alex";
await context.sync();
});
Você também pode definir propriedades personalizadas. O objeto DocumentProperties contém uma propriedade custom que representa um conjunto de pares de valores-chave para propriedades definidas pelo usuário. Para obter um exemplo de configuração de propriedades personalizadas, consulte a seção Dados XML personalizados no Excel e Word do artigo Estado e configurações do suplemento Persist.
Acessar configurações do documentos
As configurações da pasta de trabalho são semelhantes ao conjunto de propriedades personalizadas. A diferença é que as configurações são exclusivas para um único arquivo do Excel e emparelhamento de suplementos, enquanto que as propriedades estão somente conectadas ao arquivo. O exemplo a seguir mostra como criar e acessar uma configuração.
await Excel.run(async (context) => {
let settings = context.workbook.settings;
settings.add("NeedsReview", true);
let needsReview = settings.getItem("NeedsReview");
needsReview.load("value");
await context.sync();
console.log("Workbook needs review : " + needsReview.value);
});
Acessar configurações de cultura de aplicativo
Uma pasta de trabalho tem configurações de linguagem e cultura que afetam a forma como determinados dados são exibidos. Essas configurações podem ajudar a localizar dados quando os usuários do suplemento estão compartilhando pastas de trabalho em diferentes idiomas e culturas. Seu suplemento pode usar a análise de cadeia de caracteres para localizar o formato de números, datas e horários com base nas configurações de cultura do sistema para que cada usuário veja dados no formato de sua própria cultura.
Application.cultureInfo define as configurações de cultura do sistema como um objeto CultureInfo . Isso contém configurações como o separador decimal numérico ou o formato de data.
Algumas configurações de cultura podem ser alteradas por meio da interface do usuário do Excel. As configurações do sistema são preservadas no CultureInfo objeto. Todas as alterações locais são mantidas como propriedades no nível do aplicativo, como Application.decimalSeparator.
O exemplo a seguir altera o caractere separador decimal de uma cadeia de caracteres numérica de um '', para o caractere usado pelas configurações do sistema.
// This will convert a number like "14,37" to "14.37"
// (assuming the system decimal separator is ".").
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let decimalSource = sheet.getRange("B2");
decimalSource.load("values");
context.application.cultureInfo.numberFormat.load("numberDecimalSeparator");
await context.sync();
let systemDecimalSeparator =
context.application.cultureInfo.numberFormat.numberDecimalSeparator;
let oldDecimalString = decimalSource.values[0][0];
// This assumes the input column is standardized to use "," as the decimal separator.
let newDecimalString = oldDecimalString.replace(",", systemDecimalSeparator);
let resultRange = sheet.getRange("C2");
resultRange.values = [[newDecimalString]];
resultRange.format.autofitColumns();
await context.sync();
});
Controlar o comportamento do cálculo
Configurar o modo de cálculo
Por padrão, o Excel recalcula os resultados das fórmulas sempre que uma célula referenciada é alterada. O desempenho de seu suplemento pode se beneficiar do ajuste desse comportamento de cálculo. O objeto Application tem uma propriedade calculationMode do tipo CalculationMode. Ele pode ser definido como os valores a seguir.
automatic: O comportamento de recálculo padrão em que o Excel calcula novos resultados das fórmulas sempre que o dado relevante é alterado.automaticExceptTables: Igual aautomatic, exceto que as alterações feitas nos valores em tabelas serão ignoradas.manual: Os cálculos ocorrem somente quando o usuário ou suplemento os solicita.
Configurar o tipo de cálculo
O objeto Application fornece um método para forçar um recálculo imediato. Application.calculate(calculationType) inicia o recálculo manual baseado no calculationType especificado. Os valores a seguir podem ser especificados.
full: Recalcule todas as fórmulas em todas as pastas de trabalho abertas, independentemente de elas terem sido alteradas desde o último recálculo.fullRebuild: Verifique as fórmulas dependentes e depois recalcule todas as fórmulas em todas as pastas de trabalho abertas, independentemente de elas terem sido alteradas desde o último recálculo.recalculate: Recalcule as fórmulas que foram alteradas (ou marcadas por programação para recálculo) desde o último cálculo, e as fórmulas dependentes nelas, em todas as pastas de trabalho ativas.
Observação
Para saber mais sobre o recálculo, confira o artigo Alterar o recálculo, a iteração ou a precisão de fórmulas.
Suspender os cálculos temporariamente
A API do Excel também permite que os suplementos desativem os cálculos até que RequestContext.sync() seja chamado. Isso é feito pelo suspendApiCalculationUntilNextSync(). Use esse método quando seu suplemento estiver editando intervalos extensos sem precisar acessar os dados entre as edições.
context.application.suspendApiCalculationUntilNextSync();
Detectar ativação de pasta de trabalho
Seu suplemento pode detectar quando uma pasta de trabalho é ativada. Uma pasta de trabalho fica inativa quando o usuário alterna o foco para outra pasta de trabalho, para outro aplicativo ou (em Excel na Web) para outra guia do navegador da Web. Uma pasta de trabalho é ativada quando o usuário retorna o foco para a pasta de trabalho. A ativação da pasta de trabalho pode disparar funções de retorno de chamada no suplemento, como atualizar dados de pasta de trabalho.
Para detectar quando uma pasta de trabalho é ativada, registre um manipulador de eventos para o evento onActivated de uma pasta de trabalho. Os manipuladores de eventos do onActivated evento recebem um objeto WorkbookActivatedEventArgs quando o evento é acionado.
Importante
O onActivated evento não detecta quando uma pasta de trabalho é aberta. Esse evento só detecta quando um usuário alterna o foco de volta para uma pasta de trabalho já aberta.
O exemplo de código a seguir mostra como registrar o onActivated manipulador de eventos e configurar uma função de retorno de chamada.
async function run() {
await Excel.run(async (context) => {
// Retrieve the workbook.
let workbook = context.workbook;
// Register the workbook activated event handler.
workbook.onActivated.add(workbookActivated);
await context.sync();
});
}
async function workbookActivated(event) {
await Excel.run(async (context) => {
// Retrieve the workbook and load the name.
let workbook = context.workbook;
workbook.load("name");
await context.sync();
// Callback function for when the workbook is activated.
console.log(`The workbook ${workbook.name} was activated.`);
});
}
Salvar a pasta de trabalho
Workbook.save salva a pasta de trabalho para armazenamento persistente. O save método usa um único parâmetro opcional saveBehavior que pode ser um dos valores a seguir.
Excel.SaveBehavior.save(padrão): o arquivo será salvo sem solicitar que o usuário especifique o nome do arquivo e local de salvamento. Se o arquivo não tiver sido salvo anteriormente, ele será salvo no local padrão. Se o arquivo tiver sido salvo anteriormente, ele será salvo no mesmo local.Excel.SaveBehavior.prompt: se o arquivo ainda não foi salvo anteriormente, o usuário será solicitado a especificar o nome do arquivo e o local de salvamento. Se o arquivo tiver sido salvo anteriormente, ele será salvo no mesmo local sem que o usuário seja solicitado.
Cuidado
Se o usuário for solicitado a salvar e, em vez disso, cancelar a operação, save gera uma exceção.
context.workbook.save(Excel.SaveBehavior.prompt);
Fechar a pasta de trabalho
Workbook.close fecha a pasta de trabalho, além de suplementos que estão associados com a pasta de trabalho (o aplicativo Excel permanece aberto). O close método usa um único parâmetro opcional closeBehavior que pode ser um dos valores a seguir.
Excel.CloseBehavior.save(padrão): o arquivo será salvo antes de fechar. Se o arquivo não tiver sido salvo anteriormente, o usuário será solicitado a especificar o nome do arquivo e o local para salvá-lo.Excel.CloseBehavior.skipSave: o arquivo é fechado imediatamente, sem ser salvo. Quaisquer alterações não salvas serão perdidas.
context.workbook.close(Excel.CloseBehavior.save);