Stilizza i documenti Excel e aggiungi collegamenti ipertestuali con tabelle di navigazione

Questo articolo spiega come formattare celle e aggiungere collegamenti ipertestuali ai documenti Excel che si creano con le tabelle di navigazione Power Query. Prima di iniziare, consulta i documenti Create Excel con le tabelle di navigazione per lo schema della tabella di navigazione, i tipi di parti e il comportamento di posizionamento.

Applicare gli stili

Aggiungi un Styles elenco al record di un Properties componente. Ogni elemento contiene un Target, che identifica le celle del foglio di lavoro, e un Style, che ne specifica la formattazione. Puoi applicare stili a SheetData, Table, Range, e Text parti, e alla tabella dati di supporto di un Chart pezzo.

Usa questa forma per ogni elemento della lista:

[Target = [Column = "Revenue"], Style = [NumberFormat = "$#,##0.00"]]

I nomi dei campi dei record M fanno distinzione tra maiuscole e minuscole, quindi scrivi Target, Style, Font, Color e gli altri campi esattamente come mostrato. Puoi usare valori di opzione documentati come Center, Double, e Solid senza considerare il caso, ma usa il casing documentato. I nomi delle colonne dello schema di Source Power Query nelle destinazioni record e nelle mappature dei collegamenti ipertestuali fanno distinzione tra maiuscole e minuscole.

L'esempio seguente formatta un'intestazione, due colonne numeriche, la larghezza delle colonne e l'altezza della riga di intestazione.

let
    Sales = #table(
        type table [Product = text, Revenue = number, Margin = number],
        {
            {"Northwind", 1200.5, 0.24},
            {"Contoso", 2750, 0.31}
        }
    ),
    ExcelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {
                "Sales", "SalesData", "SheetData",
                [
                    Styles =
                    {
                        [Target = "A1:C1", Style = [Font = [Name = "Aptos", Size = 12, Family = 2, Bold = true, Color = "White"], Fill = "#305496", Alignment = [Horizontal = "Center"]]],
                        [Target = [Column = "Revenue"], Style = [NumberFormat = "$#,##0.00"]],
                        [Target = [Column = "Margin"], Style = [NumberFormat = "0.0%"]],
                        [Target = "A:A", Style = [Width = 24]],
                        [Target = "B:C", Style = [Width = 14]],
                        [Target = "1:1", Style = [RowHeight = 24]]
                    }
                ],
                Sales
            }
        }
    )
in
    ExcelDocument

Scegli uno stile di destinazione

I bersagli usano coordinate assolute di fogli di lavoro. I loro indirizzi non si spostano quando una parte inizia da qualche parte diversa dalla cella A1.

Il valore Row in un record di destinazione è un numero di riga assoluto di un foglio di lavoro, con indice iniziale 1. I campi rettangoli sono l'eccezione: Top, Left, Bottom, e Right sono basati su zeri e inclusi.

Applica uno stile alle celle al di fuori dei dati di una parte

Un target può includere celle vuote al di fuori dei dati di una parte, ad esempio per aggiungere un riempimento o un bordo attorno a un'area del report. Usa un elemento Table o Range posizionato esplicitamente quando hai bisogno di questo comportamento. SheetData le destinazioni sono limitate alla relativa area dati. Con le parti posizionate automaticamente, viene generata in modo affidabile solo la decorazione al di sotto di tutti i dati del foglio.

Ogni cella creata solo per lo styling conta per i limiti di materializzazione dello stile. Preferisci obiettivi a colonna intera e a fila intera per aree ampie.

Riferimento stilistico

Ogni componente di stile è opzionale. A Style è una patch parziale, non un sostituto della formattazione completa della cella. Specifica solo i campi che vuoi modificare. I componenti omessi rimangono invariati. Anche i record annidati sono parziali. Ad esempio, [Font = [Bold = true]] cambia il grassetto senza richiedere o resettare nome, dimensione o colore del font.

Tipo di carattere

Il seguente esempio richiede Aptos in 12 punti e lo classifica come una famiglia di font sans serif:

[Font = [Name = "Aptos", Size = 12, Family = 2, Bold = true, Color = "#1F4E79"]]

Name è memorizzato nel workbook; non installa né incorpora il font. Scegli un font disponibile per chi apre il quaderno. Excel può sostituire un altro font quando il font richiesto non è installato. Familyaiuta Excel a scegliere un sostituto e di solito viene omesso a meno che non sia necessario classificare il font richiesto. Ad esempio, usare Family = 1 con Georgia, Family = 2 con Arial, o Family = 3 con Consolas.

Compila

Per un riempimento uniforme, usa un colore come stile completo, come valore Fill o come Fill[Color]:

[Target = "A1:C1", Style = "#FFF2CC"]
[Target = "A1:C1", Style = [Fill = "#FFF2CC"]]
[Target = "A1:C1", Style = [Fill = [Color = "#FFF2CC"]]]

Un record di riempimento può contenere PatternType, ForegroundColor, e BackgroundColor. PatternTypeaccetta i nomi dei pattern Excel, inclusi None, Solid, Gray125, Gray0625, DarkGrayMediumGray, , LightGray, e le varianti orizzontale scura o chiara, verticale, diagonale, a griglia e a traliccio.

Per un riempimento solido, ForegroundColor è il colore visibile. BackgroundColor viene utilizzato solo con riempimenti a modello.

Bordo

Imposta Outline = true per applicare uno stile a tutti e quattro i bordi esterni di ogni cella target. Il contorno predefinito è sottile e nero.

[Target = "B2:D5", Style = [Border = [Outline = true, Style = "Medium", Color = "Red"]]]

Per spigoli diversi, usa Left, Right, Top, Bottom, o Diagonal. Un bordo può essere un nome di stile o un record contenente Style e Color. Imposta DiagonalUp oppure DiagonalDown per selezionare la direzione diagonale.

Gli stili di bordo sono None, Thin, Medium, Dashed, Thick, Dotted, Double, Hair, MediumDashed, MediumDashDot, MediumDashDotDot, DashDot, DashDotDot e SlantDashDot.

Allineamento

Protezione

Protection supporta i campi Locked logici e Hidden. Queste impostazioni entrano in vigore solo dopo che il foglio di lavoro è protetto in Excel. Il formato della cella di default di Excel è Locked impostato su true, quindi le celle per cui non specifichi la protezione diventano bloccate quando la protezione dei fogli è attivata. Usa Locked impostato su false per le celle che devono rimanere modificabili.

[Target = "B2:B20", Style = [Protection = [Locked = false]]]

Imposta Hidden su true per nascondere la formula di una cella quando il foglio è protetto. La destinazione Excel scrive l'impostazione di protezione delle celle, ma non abilita la protezione del foglio di lavoro; attiva la protezione in Excel affinché Locked o Hidden abbiano effetto.

Formato numerico

NumberFormat accetta un codice di formato numerico di Excel. Sostituisce il formato automatico basato sul tipo della destinazione per le celle target. Le impostazioni di font, riempimento, bordo e allineamento continuano ad applicarsi.

La proprietà supporta i formati integrati di Excel e i formati personalizzati sicuri. Non si limita agli esempi presenti in questa tabella.

[Target = [Column = "Margin"], Style = [NumberFormat = "0.0%"]]
[Target = [Column = "Revenue"], Style = [NumberFormat = "$#,##0.00"]]
[Target = [Column = "OrderDate"], Style = [NumberFormat = "yyyy-mm-dd"]]
[Target = [Column = "Code"], Style = [NumberFormat = "@"]]

I codici di formato personalizzati possono contenere fino a 255 caratteri. I codici in formato Excel usano virgolette doppie attorno al testo letterale. Raddoppia le virgolette all'interno di un valore di testo M, come in "#,##0 ""USD""". Puoi anche sfuggire a un singolo personaggio letterale con una barra rovesciata. I caratteri di controllo, le parentesi angolari, le virgolette o parentesi non bilanciate, il testo non sicuro senza caratteri di escape e i token di formato non supportati vengono rifiutati con errori da 30078 a 30083.

Larghezza della colonna e altezza delle righe

Width accetta valori da 0 a 255. Applicalo a un target a colonna intera, a un target a colonna nominata o a una cella o a un intervallo limitato. Un intervallo vincolato determina la dimensione di ogni colonna che comprende.

Per dimensioni efficienti e prevedibili:

• Usa un target a colonna intera, ad esempio "A:A" quando conosci la posizione del foglio di esercizio.
• Usa [Column = "Description"] quando la posizione potrebbe cambiare ma lo schema della tabella sorgente è stabile.
• Raggruppare le colonne adiacenti che necessitano della stessa larghezza, ad esempio [Target = "B:D", Style = [Width = 14]].
• Evita grandi target cella per cella quando devi solo dimensionare le colonne. I bersagli a colonna intera non si materializzano in ogni cellula.

Quando diverse parti contribuiscono con larghezze calcolate automaticamente alla stessa colonna di fogli di lavoro, si utilizza la larghezza automatica più grande affinché il contenuto di una parte non restringa la colonna di un'altra. Le larghezze esplicite hanno invece la precedenza: all'interno di una parte, vince una regola successiva altrettanto specifica; una regola a colonna specifica prevale su una larghezza larga un foglio; e quando parti sovrapposte dimensionano esplicitamente la stessa colonna di foglo, vince la parte più alta. Definisci una larghezza esplicita per ogni colonna del foglio di lavoro quando possibile.

RowHeight accetta valori da 0 a 409 punti. Funziona solo con un target di testo a tutta la riga, come "2:2" o "2:10". Altre forme target restituiscono errore 30090.

Usa un intervallo intero di righe per righe adiacenti che condividono un'altezza, come [Target = "2:20", Style = [RowHeight = 18]]. Questo è più efficiente rispetto a regole separate per ogni riga. Quando gli intervalli di altezza delle righe si sovrappongono, le regole successive hanno la precedenza nella sovrapposizione, quindi si posiziona prima il predefinito generale e le eccezioni dopo.

Specifica i colori

I colori del carattere, del riempimento e dei bordi accettano queste forme:

Combina le regole di stile

Quando più regole influenzano una cella, i loro campi di stile si uniscono in ordine di lista. Un valore successivo sostituisce un valore precedente per lo stesso campo, mentre campi non correlati rimangono. Ad esempio, un riempimento successivo non rimuove un carattere in grassetto precedente. Regole dell'ordine dal generale al specifico.

La dimensionazione segue la precedenza descritta in Larghezza della colonna e altezza delle righe, piuttosto che la normale fusione dei campi.

Modelli comuni

Formatta l'intestazione di un report

[Target = "A1:E1", Style = [Font = [Name = "Aptos", Size = 12, Bold = true, Color = "White"], Fill = "#305496", Alignment = [Horizontal = "Center"]]]

Aggiungi riempimento alternato delle righe

[Target = "A2:E2", Style = [Fill = "#F2F2F2"]],
[Target = "A4:E4", Style = [Fill = "#F2F2F2"]]

Formata le colonne della valuta e delle percentuali

[Target = [Column = "Revenue"], Style = [NumberFormat = "$#,##0.00"]],
[Target = [Column = "Margin"], Style = [NumberFormat = "0.0%"]]

Aggiungi un bordo attorno a un blocco

[Target = "B2:D10", Style = [Border = [Outline = true, Style = "Medium"]]]

Gruppi di dimensioni di colonne e righe

[Target = "A:A", Style = [Width = 28]],
[Target = "B:E", Style = [Width = 14]],
[Target = "1:1", Style = [RowHeight = 26]],
[Target = "2:20", Style = [RowHeight = 18]]

Decora un'area di report vuota

[Target = "G3:I5", Style = [Border = [Outline = true], Fill = "#FFF2CC"]]

Usa questo modello solo con una parte Range o Table posizionata esplicitamente. Per ulteriori informazioni, vedere Applicare uno stile alle celle esterne ai dati di una parte.

Stile Parti di testo e intervalli uniti

Usa una Text parte per un titolo, una nota o un altro valore singolo. Un intervallo delimitato StartCell crea un'area unita. Gli obiettivi di stile usano ancora indirizzi assoluti dei fogli di lavoro.

L'esempio seguente crea un titolo unito e ne stilizza il riempimento, il carattere, l'allineamento, il bordo e le altezze delle righe.

let
    ExcelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {
                "Summary", "ReportTitle", "Text",
                [
                    StartCell = "B2:E3",
                    Styles =
                    {
                        [
                            Target = "B2:E3",
                            Style =
                            [
                                Font = [Bold = true, Size = 16, Color = "White"],
                                Fill = "#305496",
                                Alignment = [Horizontal = "Center", Vertical = "Center"],
                                Border = [Outline = true, Style = "Medium", Color = "#1F1F1F"]
                            ]
                        ],
                        [Target = "2:3", Style = [RowHeight = 24]]
                    }
                ],
                "Quarterly sales summary"
            }
        }
    )
in
    ExcelDocument

Excel memorizza un valore unito e la maggior parte della sua formattazione nella cella di ancoraggio in alto a sinistra. Il bordo è diverso: per disegnare un contorno attorno a un intervallo unito, la cartella di lavoro deve contenere anche la formattazione dei bordi nelle celle del perimetro coperte. La destinazione Excel crea quelle celle perimetrali quando uno stile contribuisce Left, Right, Top, o Bottom spigoli a una regione unita.

Le regole che prendono di mira le celle coperte possono aggiungere una propria formattazione. Evita di applicare ampi intervalli di bordi per singola cella a celle unite di grandi dimensioni: un'area con bordi di celle unite può comprendere al massimo 1.048.576 celle e un foglio di lavoro può comprendere al massimo 4.194.304 celle formattate.

Aggiungere collegamenti ipertestuali

Puoi aggiungere collegamenti ipertestuali a tutte le parti contenenti dati: SheetData, Table, Range, Text, e la tabella dati di supporto di Chart.

La destinazione deve essere un URL http o https completo e assoluto, ad esempio https://www.microsoft.com. URL relativi, riferimenti ai fogli di calcolo e altri schemi come file, ftp, e mailto non sono supportati. Questa restrizione garantisce che ogni collegamento ipertestuale emesso sia una relazione web esterna con un bersaglio validato. Un URL non vuoto e non supportato causa il fallimento della scrittura di destinazione. Un URL nullo o vuoto crea una cella ordinaria senza un collegamento ipertestuale.

Aggiungi i collegamenti ipertestuali in due modi:

• Aggiungere una regola alla proprietà dell'elemento Hyperlinks quando una colonna contiene URL.
• Aggiungi metadati Hyperlink ai singoli valori quando il testo visualizzato, gli URL o i tooltip variano da una riga all'altra.

Aggiungi collegamenti ipertestuali da una colonna URL

Ogni regola in Properties[Hyperlinks] supporta questi campi:

La regola non aggiunge, rimuove o rinomina colonne. Un URL nullo o vuoto rimane una cella ordinaria e non crea un collegamento ipertestuale.

Il seguente esempio mostra Open per ogni URL e sostituisce l'aspetto standard del collegamento ipertestuale con uno stile esplicito.

let
    Sites = #table(
        type table [Company = text, Website = nullable text],
        {
            {"Microsoft", "https://www.microsoft.com"},
            {"Power Query", "https://learn.microsoft.com/power-query/"},
            {"No website", null}
        }
    ),
    ExcelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {
                "Links", "CompanyLinks", "Table",
                [
                    StartCell = "A1",
                    Hyperlinks =
                    {
                        [UrlColumn = "Website", DisplayText = "Open", Tooltip = "Open website"]
                    },
                    Styles =
                    {
                        [Target = [Column = "Website"], Style = [Font = [Color = "#008272", Underline = "Double", Bold = true]]]
                    }
                ],
                Sites
            }
        }
    )
in
    ExcelDocument

Aggiungi collegamenti ipertestuali ai singoli valori

Allega i metadati nel modulo value meta [Hyperlink = [Url = ..., Tooltip = ...]]. Il Url è richiesto nel record metadati ma può essere nullo. Il Tooltip è facoltativo. Il valore base diventa il valore visualizzato e mantiene il suo normale tipo di dato e formato numerico. Se il valore base è nullo e l'URL non è vuoto, l'URL viene visualizzato.

Il seguente esempio utilizza una funzione helper per creare testo di visualizzazione specifico per riga, URL e tooltip. I valori collegati e quelli ordinari possono coesistere nella stessa colonna.

let
    MakeLink = (displayValue as any, urlValue as nullable text, optional tooltipValue as nullable text) as any =>
        if tooltipValue = null then
            displayValue meta [Hyperlink = [Url = urlValue]]
        else
            displayValue meta [Hyperlink = [Url = urlValue, Tooltip = tooltipValue]],
    Links = #table(
        type table [Resource = any],
        {
            {MakeLink("Power Query documentation", "https://learn.microsoft.com/power-query/", "Read the documentation")},
            {"Not a hyperlink"},
            {MakeLink(null, "https://www.microsoft.com")}
        }
    ),
    ExcelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {"Resources", "ResourceLinks", "SheetData", [], Links}
        }
    )
in
    ExcelDocument

Puoi allegare i metadati dei link ipertestuali a valori in colonne fortemente tipizzate, inclusi numeri e date. Quando una cella ha sia metadati di collegamento ipertestuale sia una regola di colonna corrispondente, l'URL dei metadati e la tooltip hanno la precedenza per quella cella.

Per impostazione predefinita, la destinazione usa un carattere di 11 punti, con sottolineatura singola, che utilizza il colore del collegamento ipertestuale del tema della cartella di lavoro (colore tema 10), il set di caratteri secondario e la famiglia di caratteri 2. In un nuovo workbook, il nome del font è Aptos Narrow; il tema del workbook determina il carattere esattamente visibile e il colore del collegamento ipertestuale.

Le regole esplicite Styles sono composte sopra lo stile predefinito del collegamento ipertestuale. Ad esempio, l'esempio di column-rule modifica il colore, la sottolineatura e lo spessore mantenendo il collegamento ipertestuale. I campi che non sovrascrivi mantengono i loro valori predefiniti dei link ipertestuali.

Ogni foglio di calcolo supporta fino a 65.530 collegamenti ipertestuali.

Limiti di puntamento

• Gli obiettivi devono rimanere all'interno della griglia di Excel di 1.048.576 righe e 16.384 colonne. I target oltre questi limiti restituiscono errore 30096 o 30097.
• Una regola che materializza le singole cellule può coprire al massimo 1.048.576 cellule. Un foglio di lavoro può contenere al massimo 4.194.304 celle materializzate da regole di stile.
• I target a colonna intera e a riga intera sono rappresentati in modo efficiente e non consumano il budget di materializzazione per cella semplicemente perché coprono il foglio. Usali per la formattazione e il ridimensionamento generali.
• RowHeight richiede una destinazione di testo per l'intera riga. Width Non è possibile usare un target di intera riga o di record di riga.
• La formattazione al di fuori dei dati di una sezione è soggetta alle restrizioni di posizionamento descritte in Formattare le celle al di fuori dei dati di una sezione.

Limiti e risoluzione dei problemi

Per gli errori relativi alle tabelle di navigazione e alle parti, vedere Risolvere i problemi delle tabelle di navigazione di Excel.

Problemi comuni

Una regola di larghezza non cambia la colonna attesa

Causa: Un record di destinazione come [Column = "A"] utilizza il nome dello schema della tabella di origine, non una lettera di colonna di Excel. In un foglio con parti sovrapposte, può prevalere anche un'altra larghezza esplicita.

Risoluzione: Usa "A:A" per il foglio di lavoro colonna A. Usa [Column = "Description"] per la colonna sorgente chiamata Description. Usa un proprietario esplicito di larghezza per ogni colonna del foglio di lavoro.

Una regola RowHeight restituisce l'errore 30090

Causa:RowHeight è stato applicato a un target di record o a un intervallo di celle limitate.

Risoluzione: Usa un target di testo a tutta la riga, come [Target = "2:2", Style = [RowHeight = 30]].

Un formato numerico personalizzato viene rifiutato

Causa: Il testo letterale non è racchiuso tra virgolette né preceduto da un carattere di escape, il codice contiene un token non supportato oppure le virgolette o le parentesi non sono bilanciate.

Risoluzione: Metti il testo letterale tra virgolette all'interno del codice in formato Excel. In M, raddoppia quelle virgolette, ad esempio [NumberFormat = "#,##0 ""USD"""].

Un font richiesto appare diverso in Excel

Causa: Il font richiesto non è installato sul computer che apre il quaderno, quindi Excel sostituisce un font.

Risoluzione: Utilizzare un font comunemente disponibile e opzionalmente fornire la classificazione corrispondente Family per migliorare la sostituzione.

Uno stile o un colore di bordo non appare

Causa: A livello di bordo, Style e Color si applicano solo tramite l'abbreviazione Outline.

Risoluzione: Aggiungi Outline = true, o definisci direttamente gli archi, come [Border = [Top = [Style = "Thin", Color = "Red"]]].

Un collegamento ipertestuale impedisce il completamento della scrittura nella destinazione

Causa: Un URL non vuoto è relativo o utilizza uno schema diverso da http o https.

Risoluzione: Fornisci un URL web completo come https://www.microsoft.com, oppure usa testo nullo o vuoto quando la cella non dovrebbe essere un collegamento ipertestuale.