Zastosowanie formuł arkusza wykresu w prezentacjach w .NET
Przegląd
Wykresy PowerPoint zazwyczaj przechowują dane źródłowe w osadzonym arkuszu. W Aspose.Slides dla .NET możesz uzyskać dostęp do tego arkusza poprzez skoroszyt danych wykresu, zapisywać wartości wejściowe, przypisywać formuły do komórek, obliczać obsługiwane formuły i używać obliczonych komórek jako danych wykresu.
Ten artykuł wyjaśnia kompletny przepływ pracy z formułami: tworzenie wykresu, wypełnianie jego arkusza, przypisywanie formuł w stylu A1 lub R1C1, ponowne ich obliczanie, odczytanie obliczonych wartości, połączenie tych komórek z serią wykresu i zapis prezentacji. Opisuje także obsługiwaną składnię formuł, wbudowany podzbiór funkcji, wartości w pamięci podręcznej, nieobsługiwane formuły oraz błędy specyficzne dla arkuszy kalkulacyjnych.
Arkusze wykresów i formuły
Arkusz wykresu zawiera kategorie, nazwy serii i wartości używane przez wykres. W PowerPoint możesz przejrzeć arkusz, otwierając edytor danych wykresu:
W Aspose.Slides arkusz jest udostępniany poprzez arkusz danych wykresu. Użyj właściwości Formuła dla formuł w stylu A1 oraz R1C1Formuła dla formuł w stylu R1C1. Po zmianie komórek wejściowych lub formuł wywołaj ObliczFormuły, aby ponownie obliczyć obsługiwane formuły i zaktualizować odpowiadające wartości komórek.
Obliczona komórka nadal udostępnia swój wynik poprzez właściwość Wartość. Jest to istotne, gdy trzeba sprawdzić wynik formuły w kodzie lub użyć komórki jako punktu danych wykresu.
Utworzenie wykresu i obliczenie formuł w arkuszu
Poniższy przykład demonstruje pełny przepływ pracy. Tworzy wykres kolumnowy skumulowany, czyści przykładowe dane, zapisuje kwartalne przychody i wydatki, oblicza zysk przy użyciu formuł, odczytuje wyniki, używa obliczonych komórek jako wartości wykresu i zapisuje prezentację.
using System;
using Aspose.Slides;
using Aspose.Slides.Charts;
using Aspose.Slides.Export;
using var presentation = new Presentation();
var slide = presentation.Slides[0];
var chart = slide.Shapes.AddChart(ChartType.ClusteredColumn, 50, 50, 600, 350);
var workbook = chart.ChartData.ChartDataWorkbook;
var worksheetIndex = 0;
chart.ChartData.Series.Clear();
chart.ChartData.Categories.Clear();
workbook.Clear(worksheetIndex);
var category1 = workbook.GetCell(worksheetIndex, "A2", "Q1");
var category2 = workbook.GetCell(worksheetIndex, "A3", "Q2");
var category3 = workbook.GetCell(worksheetIndex, "A4", "Q3");
workbook.GetCell(worksheetIndex, "B1", "Revenue");
workbook.GetCell(worksheetIndex, "C1", "Expenses");
workbook.GetCell(worksheetIndex, "D1", "Profit");
workbook.GetCell(worksheetIndex, "B2").Value = 120.0;
workbook.GetCell(worksheetIndex, "C2").Value = 80.0;
workbook.GetCell(worksheetIndex, "B3").Value = 150.0;
workbook.GetCell(worksheetIndex, "C3").Value = 95.0;
workbook.GetCell(worksheetIndex, "B4").Value = 135.0;
workbook.GetCell(worksheetIndex, "C4").Value = 110.0;
var profit1 = workbook.GetCell(worksheetIndex, "D2");
var profit2 = workbook.GetCell(worksheetIndex, "D3");
var profit3 = workbook.GetCell(worksheetIndex, "D4");
profit1.Formula = "B2-C2";
profit2.Formula = "B3-C3";
profit3.Formula = "B4-C4";
workbook.CalculateFormulas();
var q1Profit = Convert.ToDouble(profit1.Value); // 40
var q2Profit = Convert.ToDouble(profit2.Value); // 55
var q3Profit = Convert.ToDouble(profit3.Value); // 25
Console.WriteLine($"Q1 profit: {q1Profit}");
Console.WriteLine($"Q2 profit: {q2Profit}");
Console.WriteLine($"Q3 profit: {q3Profit}");
chart.ChartData.Categories.Add(category1);
chart.ChartData.Categories.Add(category2);
chart.ChartData.Categories.Add(category3);
var profitSeries = chart.ChartData.Series.Add(workbook.GetCell(worksheetIndex, "D1"), chart.Type);
profitSeries.DataPoints.AddDataPointForBarSeries(profit1);
profitSeries.DataPoints.AddDataPointForBarSeries(profit2);
profitSeries.DataPoints.AddDataPointForBarSeries(profit3);
profitSeries.Labels.DefaultDataLabelFormat.ShowValue = true;
presentation.Save("chart-formulas.pptx", SaveFormat.Pptx);
Punkty danych wykresu odwołują się do D2:D4, więc wykres używa obliczonych wartości zysku. W tym przepływie nie ma osobnego wywołania odświeżenia wykresu: najpierw ponownie oblicz skoroszyt, a dopiero potem użyj lub zapisz dane wykresu wskazujące na obliczone komórki.
Używanie formuł w stylu A1
Notacja A1 identyfikuje kolumny literami, a wiersze liczbami. Przypisz wyrażenia w stylu A1 poprzez IChartDataCell.Formula.
using Aspose.Slides;
using Aspose.Slides.Charts;
using var presentation = new Presentation();
var slide = presentation.Slides[0];
var chart = slide.Shapes.AddChart(ChartType.ClusteredColumn, 50, 50, 500, 300);
var workbook = chart.ChartData.ChartDataWorkbook;
workbook.GetCell(0, "C3").Value = 10;
workbook.GetCell(0, "F2").Value = 2;
workbook.GetCell(0, "G2").Value = 3;
workbook.GetCell(0, "H2").Value = 4;
var cell = workbook.GetCell(0, "A2");
cell.Formula = "C3+SUM(F2:H2)";
workbook.CalculateFormulas();
var value = cell.Value; // 19
Typowe formy odwołań A1 to:
| Odwołanie | Względne | Bezwzględne | Mieszane |
|---|---|---|---|
| Komórka | A2 |
$A$2 |
A$2, $A2 |
| Wiersz | 2:2 |
$2:$2 |
— |
| Kolumna | A:A |
$A:$A |
— |
| Zakres | A2:C4 |
$A$2:$C$4 |
A$2:$C4, $A2:C$4 |
Względne odwołania mogą się zmienić, gdy formuła zostanie przeniesiona lub skopiowana w aplikacji arkusza kalkulacyjnego. Odwołania bezwzględne utrzymują oba współrzędne stałe, a odwołania mieszane utrwalają tylko wiersz lub kolumnę.
Używanie formuł w stylu R1C1
Notacja R1C1 identyfikuje zarówno wiersze, jak i kolumny liczbami. Odwołania względne używają przesunięć w kwadratowych nawiasach. Przypisz tę składnię poprzez IChartDataCell.R1C1Formula.
using Aspose.Slides;
using Aspose.Slides.Charts;
using var presentation = new Presentation();
var slide = presentation.Slides[0];
var chart = slide.Shapes.AddChart(ChartType.ClusteredColumn, 50, 50, 500, 300);
var workbook = chart.ChartData.ChartDataWorkbook;
workbook.GetCell(0, "B2").Value = 12;
workbook.GetCell(0, "C2").Value = 5;
var cell = workbook.GetCell(0, "D2");
cell.R1C1Formula = "RC[-2]-RC[-1]";
workbook.CalculateFormulas();
var value = cell.Value; // 7
Typowe formy odwołań R1C1 to:
| Odwołanie | Względne | Bezwzględne | Mieszane |
|---|---|---|---|
| Komórka | R[2]C[3] |
R2C3 |
R2C[3], R[2]C3 |
| Wiersz | R[2] |
R2 |
— |
| Kolumna | C[3] |
C3 |
— |
| Zakres | R[2]C[3]:R[5]C[7] |
R2C3:R5C7 |
R2C3:R[5]C[7], R[2]C3:R5C[7] |
Na przykład w komórce D2 wyrażenie RC[-2] oznacza komórkę w tym samym wierszu dwie kolumny w lewo (B2).
Stałe i operatory formuł
Wbudowany evaluator formuł obsługuje wartości logiczne, literały liczbowe, łańcuchy znaków, wartości błędów arkusza, operatory arytmetyczne i operatory porównania.
Stałe i literały
| Typ | Przykłady | Uwagi |
|---|---|---|
| Logiczny | TRUE, FALSE |
Można używać bezpośrednio w wyrażeniach logicznych, np. A2=TRUE. |
| Numeryczny | 1, 0.5, .3, 1E-2 |
Obsługiwane są notacje zwykła i naukowa. |
| Tekstowy | "abc", "2/3/2020 12:00" |
Literały tekstowe są otoczone podwójnymi cudzysłowami wewnątrz formuły. |
| Wynik błędu | #DIV/0!, #N/A, #REF! |
Poprawna formuła może zwrócić wartość błędu arkusza zamiast wyniku. |
Ten przykład używa kilku typów stałych:
using Aspose.Slides;
using Aspose.Slides.Charts;
using var presentation = new Presentation();
var slide = presentation.Slides[0];
var chart = slide.Shapes.AddChart(ChartType.ClusteredColumn, 50, 50, 500, 300);
var workbook = chart.ChartData.ChartDataWorkbook;
workbook.GetCell(0, "A2").Value = false;
workbook.GetCell(0, "B2").Formula = "A2=TRUE";
workbook.GetCell(0, "C2").Formula = "1+0.5";
workbook.GetCell(0, "D2").Formula = ".3*1E-2";
workbook.GetCell(0, "E2").Formula = "\"abc\"";
workbook.GetCell(0, "F2").Formula = "2/0";
workbook.CalculateFormulas();
var logicalValue = workbook.GetCell(0, "B2").Value; // Fałsz
var numericValue = workbook.GetCell(0, "C2").Value; // 1.5
var scientificValue = workbook.GetCell(0, "D2").Value; // 0.003
var stringValue = workbook.GetCell(0, "E2").Value; // abc
var errorValue = workbook.GetCell(0, "F2").Value; // #DIV/0!
Operatory arytmetyczne
| Operator | Znaczenie | Przykład |
|---|---|---|
+ |
Dodawanie lub znak plus jednopodstawowy | 2+3 |
- |
Odejmowanie lub negacja | 2-3, -3 |
* |
Mnożenie | 2*3 |
/ |
Dzielenie | 2/3 |
% |
Procent | 30% |
^ |
Potęgowanie | 2^3 |
Używaj nawiasów, aby wyraźnie określić kolejność obliczeń, np. (A2+B2)*C2.
Operatory porównania
Wyrażenia porównawcze zwracają wartości logiczne.
| Operator | Znaczenie | Przykład |
|---|---|---|
= |
Równe | A2=3 |
<> |
Nierówne | A2<>3 |
> |
Większe niż | A2>3 |
>= |
Większe lub równe | A2>=3 |
< |
Mniejsze niż | A2<3 |
<= |
Mniejsze lub równe | A2<=3 |
Obsługiwane wbudowane funkcje
Aspose.Slides zawiera wbudowany evaluator formuł dla arkuszy wykresów, ale nie jest pełnym silnikiem obliczeniowym Excel. Udokumentowany zestaw funkcji ogranicza się do poniższych funkcji. Nie zakładaj, że dowolna funkcja Excel może być przeliczoną przez ObliczFormuły.
| Funkcja | Cel lub obsługiwana forma | Przykład |
|---|---|---|
ABS |
Wartość bezwzględna | ABS(A2) |
AVERAGE |
Średnia arytmetyczna | AVERAGE(B2:B5) |
CEILING |
Zaokrąglenie w górę do wielokrotności | CEILING(A2,5) |
CHOOSE |
Wybór wartości po indeksie | CHOOSE(A2,"Low","High") |
CONCAT |
Łączenie wartości tekstowych | CONCAT(A2,B2) |
CONCATENATE |
Łączenie wartości tekstowych | CONCATENATE(A2," ",B2) |
DATE |
Tworzenie wartości daty przy użyciu systemu dat 1900 | DATE(2026,8,19) |
DAYS |
Zwraca liczbę dni pomiędzy datami | DAYS(B2,A2) |
FIND |
Znajduje tekst w innym tekście | FIND("-",A2) |
FINDB |
Wyszukiwanie tekstu bajtowo | FINDB("a",A2) |
IF |
Wynik warunkowy | IF(A2>0,A2,0) |
INDEX |
Forma odwołania | INDEX(A2:C4,2,3) |
LOOKUP |
Forma wektorowa | LOOKUP(A2,B2:B5,C2:C5) |
MATCH |
Forma wektorowa | MATCH(A2,B2:B5,0) |
MAX |
Maksymalna wartość | MAX(B2:B5) |
SUM |
Suma wartości | SUM(B2:B5) |
VLOOKUP |
Wyszukiwanie pionowe | VLOOKUP(A2,B2:D10,3,FALSE) |
Ograniczenia przedstawione w tabeli są istotne: INDEX jest udokumentowany w formie odwołania, natomiast LOOKUP i MATCH w ich formach wektorowych. DATE używa systemu dat 1900. Funkcje i cechy nie wymienione tutaj powinny być traktowane jako nieobsługiwane przez evaluator formuł Aspose.Slides, chyba że są udokumentowane osobno.
Obliczanie formuł z preferowaną kulturą
Niektóre funkcje skoroszytu wykresu interpretują tekst zgodnie z regułami specyficznymi dla kultury. Jest to szczególnie ważne dla funkcji przeznaczonych dla języków używających podwójnych bajtów (DBCS). Aby prawidłowo obliczyć takie formuły, utwórz OpcjeŁadowania, ustaw ISpreadsheetOptions.PreferredCulture poprzez OpcjeŁadowania.SpreadsheetOptions, a następnie wczytaj prezentację.
Poniższy przykład wybiera kulturę japońską, otwiera prezentację z skonfigurowanymi opcjami ładowania i wywołuje IChartDataWorkbook.CalculateFormulas dla każdego skoroszytu wykresu:
using System.Globalization;
using Aspose.Slides;
using Aspose.Slides.Charts;
var loadOptions = new LoadOptions
{
SpreadsheetOptions = new SpreadsheetOptions
{
PreferredCulture = CultureInfo.GetCultureInfo("ja-JP")
}
};
using var presentation = new Presentation("presentation.pptx", loadOptions);
foreach (var slide in presentation.Slides)
{
foreach (var shape in slide.Shapes)
{
if (shape is IChart chart)
{
chart.ChartData.ChartDataWorkbook.CalculateFormulas();
}
}
}
Preferowana kultura jest częścią konfiguracji ładowania prezentacji, więc określ ją przed utworzeniem instancji Presentation. Użyj kultury oczekiwanej przez formuły skoroszytu; na przykład użyj ja-JP dla formuł, które powinny stosować japońskie reguły DBCS.
Ponowne obliczanie i wartości w pamięci podręcznej
Pliki arkuszy często przechowują zarówno formułę, jak i jej ostatnio obliczoną wartość. Aspose.Slides może więc odczytać wartość z pamięci podręcznej z IChartDataCell.Value, gdy prezentacja zostanie wczytana i odpowiednie dane wykresu nie zostały zmienione.
Po zmianie komórek wejściowych lub formuł nie polegaj na starej zapamiętanej wartości. Wywołaj IChartDataWorkbook.CalculateFormulas przed odczytaniem obliczonych wartości lub zapisem danych wykresu zależnych od nich.
Dla formuł spoza obsługiwanego podzbioru Aspose.Slides może nie być w stanie sparsować formuły ani ustalić jej zależności. Jeśli skoroszyt został zmodyfikowany, poprzednia zapamiętana wartość nie może być uznana za wiarygodną. W takiej sytuacji odczyt wartości komórki z nieobsługiwanymi danymi może spowodować podniesienie CellUnsupportedDataException.
Jeśli Twój wykres zależy od funkcji Excel, których Aspose.Slides nie ocenia, oblicz te formuły przy użyciu silnika arkusza obsługującego je i zapisz uzyskane wyniki z powrotem do skoroszytu wykresu. Nie zastępuj nieobsługiwanych formuł zgadywanymi wartościami.
Obsługa błędów formuł
Istnieją dwa różne rodzaje problemów do rozróżnienia.
Formuła może być poprawna, ale zwrócić wynik błędu arkusza, taki jak #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! lub #VALUE!. W takim wypadku token błędu jest wynikiem komórki i może być zwrócony przez Value.
Formuła może także nie powieść się na etapie parsowania, odwołania, zależności lub danych obsługiwanych. Aspose.Slides udostępnia specyficzne dla arkuszy wyjątki: CellInvalidFormulaException, CellInvalidReferenceException, CellCircularReferenceException, oraz CellUnsupportedDataException.
Gdy formuły pochodzą z szablonów lub danych wejściowych użytkownika, obsłuż te wyjątki wokół ponownego obliczania i dostępu do wartości:
using System;
using Aspose.Slides;
using Aspose.Slides.Charts;
using Aspose.Slides.Spreadsheet;
using var presentation = new Presentation();
var slide = presentation.Slides[0];
var chart = slide.Shapes.AddChart(ChartType.ClusteredColumn, 50, 50, 500, 300);
var workbook = chart.ChartData.ChartDataWorkbook;
var cell = workbook.GetCell(0, "A2");
cell.Formula = "SUM(B2:B5)";
try
{
workbook.CalculateFormulas();
Console.WriteLine(cell.Value);
}
catch (CellInvalidFormulaException ex)
{
Console.Error.WriteLine($"Invalid formula: {ex.Message}");
}
catch (CellInvalidReferenceException ex)
{
Console.Error.WriteLine($"Invalid cell reference: {ex.Message}");
}
catch (CellCircularReferenceException ex)
{
Console.Error.WriteLine($"Circular reference: {ex.Message}");
}
catch (CellUnsupportedDataException ex)
{
Console.Error.WriteLine($"Unsupported spreadsheet data: {ex.Message}");
}
Praktyczne ograniczenia
Wsparcie formuł w arkuszach wykresów jest przeznaczone dla określonego podzbioru obliczeń arkusza, a nie dla pełnej kompatybilności z Excelem. Miej te ograniczenia na uwadze przy projektowaniu przepływu raportowania:
- Używaj wyłącznie udokumentowanych stałych, operatorów, odwołań i funkcji, gdy potrzebujesz, aby Aspose.Slides przeliczał formuły.
- Ponownie obliczaj po zmianie komórek, od których zależą wyniki formuł.
- Traktuj wartości z pamięci podręcznej wczytanych prezentacji jako migawki, a nie jako zamiennik ponownego obliczania po edycjach.
- Testuj formuły z istniejących szablonów przed poleganiem na ich obliczonych wartościach, szczególnie gdy używają funkcji spoza udokumentowanej listy.
- Dla formuł wymagających pełnego silnika obliczeniowego arkusza, oblicz je zewnętrznie, a następnie zaktualizuj skoroszyt wykresu uzyskanymi wartościami.
FAQ
Jaka jest różnica między Formuła a R1C1Formuła?
Formuła przechowuje wyrażenie w stylu A1, takie jak B2-C2. R1C1Formuła przechowuje wyrażenie w stylu R1C1, takie jak RC[-2]-RC[-1]. Użyj notacji, która najlepiej pasuje do sposobu generowania lub kopiowania formuł.
Czy muszę odczytać samą komórkę czy jej wartość po obliczeniu?
IChartDataWorkbook.GetCell zwraca IChartDataCell. Aby uzyskać obliczony wynik, odczytaj właściwość Wartość tej komórki po ponownym obliczeniu.
Kiedy powinienem wywołać CalculateFormulas?
Wywołaj ObliczFormuły po zmianie wartości wejściowych lub formuł i przed zależnością od wyników obliczeń. To zaktualizuje wartości formuł obsługiwanych przez wbudowany evaluator.
Czy Aspose.Slides obsługuje każdą funkcję Excel?
Nie. Wbudowany evaluator obsługuje udokumentowany podzbiór funkcji. Funkcje poza tym podzbiorem nie powinny być uznawane za prawidłowo przeliczane. Jeśli wymagana jest pełna kompatybilność z formułami Excel, wykonaj obliczenia przy użyciu odpowiedniego silnika arkusza i zapisz ostateczne wartości w skoroszycie wykresu.
Co się stanie, jeśli wczytana prezentacja zawiera nieobsługiwaną formułę?
Jeśli dane wykresu nie zostały zmienione, skoroszyt może nadal zawierać wcześniej obliczoną zapisaną wartość. Po modyfikacji powiązanych danych ta zapisana wartość może przestać być ważna. Dostęp do komórki, której formuła nie może być obsłużona, może podnieść CellUnsupportedDataException.
Czy wartości błędów formuły są tym samym co wyjątki .NET?
Nie. Wynik taki jak #DIV/0! jest wartością arkusza wygenerowaną przez prawidłowe obliczenie. Wyjątki takie jak CellInvalidFormulaException czy CellCircularReferenceException wskazują, że formuła nie może być przetworzona w normalny sposób.
Czy wykres aktualizuje się automatycznie po zmianie komórki formuły?
Seria wykresu może odwoływać się do komórek skoroszytu. Najpierw oblicz skoroszyt, a potem zapisz lub renderuj prezentację. Jeśli punkty danych wykresu odwołują się do obliczonych komórek, wykres użyje zaktualizowanych wartości; nie jest wymagane osobne wywołanie odświeżenia wykresu w tym przepływie.
Czy wykresy mogą używać zewnętrznego skoroszytu Excel?
Tak, dane wykresu można skonfigurować tak, aby korzystały z zewnętrznego skoroszytu przez API danych wykresu. Jednak opisany w tym artykule przepływ obliczania formuł dotyczy skoroszytu danych wykresu i podzbioru formuł ocenianych przez Aspose.Slides. Nie zakładaj, że ObliczFormuły zapewnia pełne przeliczenie dowolnych formuł w zewnętrznym pliku XLSX.
Czy mogę używać formuł odwołujących się do innego arkusza lub skoroszytu?
Odwołania w stylu Excel mogą istnieć w skoroszytach wykresów, ale ocena formuły jest ograniczona przez obsługiwany parser i zestaw funkcji. Jeśli odwołanie między arkuszami lub do zewnętrznego skoroszytu jest niezbędne, zweryfikuj dokładną formułę z wersją Aspose.Slides, której używasz. Dla przepływów wymagających szerokiej kompatybilności odwołań Excel, oblicz skoroszyt zewnętrznie i zapisz rozwiązane wartości z powrotem do danych wykresu.
Czy ciągi formuł powinny zaczynać się od =?
Przykłady API Aspose.Slides przypisują wyrażenia takie jak B2-C2 lub SUM(B2:B5) bez wiodącego =. Używanie tej formy utrzymuje generowane formuły zgodne z udokumentowanymi przykładami API.