Wykresy i obrazy HotXLS: rysunki Excela w Delphi

Wszystko, co unosi się nad siatką arkusza (wykres, logo, pieczątka, dymek objaśnienia), jest obiektem rysunkowym, a obiekt rysunkowy definiują dwie rzeczy: czym jest i gdzie jest zakotwiczony. To zakotwiczenie ludzie mylą najczęściej. Wykres nie mieszka w komórce; siedzi w prostokącie przypiętym do zakresu wierszy i kolumn, a dane, które kreśli, to osobny zbiór odwołań A1, o którym zakotwiczenie nic nie wie. Przesuń ramkę, a wykres zostanie ten sam. Wstaw pod nim wiersze, a ramka zjedzie razem z nimi. Utrzymanie tych dwóch układów współrzędnych w porządku to większość tego, co sprawia, że kod rysunków zachowuje się poprawnie

HotXLS jest natywną biblioteką w Object Pascalu, która czyta i zapisuje XLS oraz XLSX bez automatyzacji Excela, i niesie dwa osobne modele rysunków, ponieważ oba formaty plików przechowują rysunki inaczej. Format .xls w wersji BIFF8 trzyma wykresy na ich własnych dedykowanych arkuszach, a kształty pływające w strumieniu OfficeArt dołączonym do arkusza. Format OOXML .xlsx potrafi osadzić wykres wewnątrz siatki, zakotwiczony do prostokąta komórek, obok tych samych pływających obrazów i kształtów. Model obiektowy odzwierciedla ten podział, a awarie warte opisania biorą się wszystkie ze stosowania reguł jednego formatu do drugiego

Który kontener co pomieści

Wybór kontenera musi poprzedzać jakikolwiek kod wykresów, ponieważ dostępne typy obiektów różnią się między nimi:

• XLS (BIFF8): wykresy mieszkają na dedykowanych arkuszach wykresów tworzonych przez AddChartSheet na kolekcji Sheets. Obrazy, pola tekstowe, prostokąty, owale i linie to kształty OfficeArt zarządzane przez kolekcję Shapes arkusza. Nie ma API do osadzania wykresu wewnątrz siatki zwykłego arkusza
• XLSX (OOXML): wykresy można osadzić wprost w arkuszu przez TXLSXWorksheet.AddChart, zakotwiczone do prostokąta komórek, albo umieścić na dedykowanym arkuszu wykresu przez TXLSXWorkbook.AddChartSheet. Obrazy wchodzą przez AddImage albo AddImageFromFile, a pływające etykiety przez AddTextBox

Wymaganie sformułowane jako „arkusz pulpitu z wykresem obok liczb” jest więc w istocie wymaganiem na .xlsx. W .xls możesz je tylko przybliżyć, wypychając wykres na własny arkusz, co zmienia sposób nawigowania użytkownika po pliku i zmienia to, jak musi zachowywać się twój kod. Arkusz zwracany przez AddChartSheet po stronie XLS jest podstrumieniem wykresu, a nie siatką: zapisywanie do niego przez Cells.Item produkuje niespójny strumień rysunku, który generuje się bez błędu, a który Excel następnie odrzuca przy otwarciu. Wykres po prostu znika, a nic w logu budowania nie mówi dlaczego. Potraktuj zwrócony arkusz jako przeznaczony wyłącznie na wykres, a cała klasa zgłoszeń o „brakującym wykresie” zniknie

Osadzanie wykresu w arkuszu XLSX

Ścieżka XLSX daje pole manewru i to na niej dwa układy współrzędnych z początku artykułu stają się konkretne. Prostokąt zakotwiczenia przekazywany do AddChart wyraża się w wierszach i kolumnach arkusza i ustala, gdzie siedzi ramka wykresu. Dane serii wyraża się jako bezwzględne odwołania A1 zawierające nazwę arkusza. Są niezależne: możesz przesunąć ramkę na drugi koniec arkusza, a i tak będzie kreślić te same komórki

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Chart: TXLSXChart;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Sales');
    Sheet.Cells[1, 1].Value := 'Region';
    Sheet.Cells[1, 2].Value := 'Revenue';
    Sheet.Cells[2, 1].Value := 'East';
    Sheet.Cells[2, 2].Value := 1184350;
    Sheet.Cells[3, 1].Value := 'Central';
    Sheet.Cells[3, 2].Value := 902210;
    Sheet.Cells[4, 1].Value := 'West';
    Sheet.Cells[4, 2].Value := 1010675;

    // Ramka zakotwiczona do wierszy 6..22, kolumn 1..8
    Chart := Sheet.AddChart(xlsxChartColumn, 'Revenue by Region', 6, 1, 22, 8);
    Chart.AddSeries('Revenue', 'Sales!$A$2:$A$4', 'Sales!$B$2:$B$4');
    Chart.ValueAxisTitle := 'USD';

    Sheet.AddImageFromFile(1, 5, 'logo.png');
    Book.SaveAs('dashboard.xlsx');
  finally
    Book.Free;
  end;
end;

Argumentem, który gryzie, jest łańcuch zakresu podawany do AddSeries. To literał, uchwycony w chwili wywołania, i nie ma pojęcia, że możesz potem dopisać jeszcze dwadzieścia wierszy danych. Buduj go z liczby wierszy policzonej po zapisaniu danych, nigdy przed. Wykresy punktowe i bąbelkowe przeciążają te same dwa argumenty innymi znaczeniami: zakres kategorii dostarcza teraz wartości X, zakres wartości dostarcza Y, a promień bąbelka pochodzi z trzeciego odwołania ustawianego przez BubbleSizeRange na zwróconej serii TXLSXChartSeries. Gdy opuszczasz rodzinę kolumnową i słupkową, czytaj to wywołanie jako „X, Y, rozmiar”, a nie jako „kategorie, wartości”

TXLSXChartType obejmuje wykresy kolumnowe, słupkowe, liniowe, kołowe, warstwowe, pierścieniowe, punktowe, bąbelkowe i radarowe, co pokrywa codzienny repertuar raportowy. Dla wykresu na całą stronę bez otaczającej siatki Book.AddChartSheet zwraca arkusz, którego właściwość IsChartSheet jest prawdą. To odpowiednik starszego arkusza wykresu po stronie .xlsx i niesie to samo oczekiwanie: nie zapisuj do niego treści komórek

Obrazy wchodzą jako bajty, a wymiaruje się je w EMU

Istnieją dwa przeciążenia wstawiania obrazu, a ich pomylenie to błąd obrazkowy pojawiający się najczęściej w przeglądzie kodu. AddImage(ARow, ACol, AData, AFormat) chce w AData już zakodowanych bajtów obrazu: surowej zawartości pliku PNG, JPEG, GIF albo BMP. Podaj mu ścieżkę pliku, a zapiszesz czterdziestobajtowy łańcuch, którego żadna przeglądarka nie zdekoduje, czyli dokładnie to zgłoszenie o ikonie zepsutego obrazka, którego nie chcesz debugować po wdrożeniu. Gdy źródłem jest plik na dysku, wywołaj zamiast tego AddImageFromFile i pozwól bibliotece odczytać bajty i sklasyfikować format za ciebie

Potem przychodzi wymiarowanie. DrawingML nie mierzy w pikselach; mierzy w angielskich jednostkach metrycznych, gdzie 914400 EMU składa się na cal, a przy 96 DPI 9525 EMU składa się na piksel. Obiekt TXLSXImage udostępnia WidthEMU i HeightEMU, więc logo mające renderować się jako 180 na 60 pikseli potrzebuje 1714500 na 571500 EMU. Umieść tę konwersję w nazwanej stałej i licz względem niej. Magiczne liczby w rodzaju 1714500 rozsypane po kodzie są nieczytelne i po cichu błędne, gdy ktoś pierwszy raz zmieni docelowe DPI. Wiersz i kolumna zakotwiczenia są nawiasem mówiąc liczone od 1, zgodnie z resztą API komórek, a nie od 0 jak arytmetyka EMU

Arkusze wykresów i kształty w starszych plikach XLS

Po stronie BIFF8 bogatsze przeciążenie AddChartSheet przyjmuje typ wykresu, tytuły osi oraz otwartą tablicę rekordów TXLSChartSeriesInfo, gdzie każdy rekord trzyma nazwę oraz zakres kategorii i zakres wartości w postaci łańcuchów. Kształty pływające to osobna sprawa: idą na sam arkusz danych, przez jego kolekcję Shapes, a nie na arkusz wykresu

var
  Book: IXLSWorkbook;
  Data, Trend: IXLSWorksheet;
  Series: array[0..0] of TXLSChartSeriesInfo;
begin
  Book := TXLSWorkbook.Create;   // liczony przez interfejs: nie wywołuj Free
  Data := Book.Sheets.Add;
  Data.Name := 'Data';
  Data.Cells.Item[1, 1].Value := 'Month';
  Data.Cells.Item[1, 2].Value := 'Units';
  Data.Cells.Item[2, 1].Value := 'Apr';
  Data.Cells.Item[2, 2].Value := 1530;
  Data.Cells.Item[3, 1].Value := 'May';
  Data.Cells.Item[3, 2].Value := 1721;

  Series[0].Name := 'Units';
  Series[0].Categories := 'Data!$A$2:$A$3';
  Series[0].Values := 'Data!$B$2:$B$3';
  Trend := Book.Sheets.AddChartSheet('Trend', xlsChartTypeLine,
    'Units sold', 'Month', 'Units', Series);
  // Trend jest podstrumieniem wykresu: nigdy nie wywołuj na nim metod komórek

  Data.Shapes.AddTextBox('Source: ERP nightly export', 6, 1, 8, 4);
  Data.Shapes.AddPicture('approved-stamp.bmp');
  Book.SaveAs('trend.xls');
end;

Dwa szczegóły czasu życia mają tu znaczenie i ciągną w przeciwne strony. TXLSWorkbook trzyma się przez interfejs IXLSWorkbook i jest liczony referencjami, więc samodzielne wywołanie na nim Free wyzwala podwójne zwolnienie. TXLSXWorkbook z poprzednich sekcji jest zwykłym obiektem i musi zostać zwolniony w try..finally. Ten sam recenzent kodu, który sygnalizuje brakujące Free po stronie XLSX, musi sygnalizować jego obecność po stronie XLS, a to prawdziwa pułapka, gdy pracujesz z oboma formatami w tym samym module. Same pomocniki kształtów są jednolite: AddRectangle, AddOval i AddLine, z DeleteInRange do czyszczenia obszaru z rysunków, wszystkie kotwiczą przez pary wiersz-kolumna, więc szablon wstawiający nad nimi wiersze przesuwa je razem z siatką

Jeszcze jedna właściwość zarabia na siebie przy starszych plikach. TXLSPicture.TransparentColor maskuje wybrany kolor tła bitmapy, i tak właśnie kładzie się nieprostokątną pieczątkę (pieczęć „Approved”, znak wodny) na siatce w formacie, którego renderowanie BIFF nigdy nie nauczyło się kanału alfa PNG. Ustaw kolor, pod który pieczątkę zaprojektowano, a otaczający prostokąt zniknie

Kolory motywu nie przeżywają podróży przez BIFF8

Wypełnienia rysunków w OOXML mogą wskazywać gniazdo koloru motywu, i dlatego przekolorowanie całego pliku .xlsx przez podmianę motywu jest tanie. Rekordy rysunków BIFF8 nie mają takiego gniazda. Gdy HotXLS stosuje kolor motywu do rysunku XLS, rozwiązuje ten kolor do literalnej wartości RGB i ją zapisuje; indeks motywu, z którego pochodził, znika w chwili zapisania pliku, a ponowne otwarcie go nie odzyska. Łapie to szczególnie narzędzia raportowe typu white-label, te, które przebrandowują ten sam wygenerowany dokument dla wielu klientów. Trzymaj odwzorowanie motywu na RGB we własnej konfiguracji i stosuj je ponownie przy każdym generowaniu, zamiast liczyć na odczytanie go z zapisanego pliku .xls

Powiązana decyzja pojawia się po stronie wydajności. Fasadzie XLS można kazać całkowicie pominąć parsowanie warstwy rysunków, gdy z dużego starszego pliku chcesz wyłącznie danych komórek, ustawiając _DisableGraphics na prawdę, a to ścina realny czas z odczytów masowych. Haczyk jest trwały: skoroszyt otwarty w ten sposób nie ma w pamięci strumienia OfficeArt, więc jego zapisanie wymazuje rysunki z istnienia. Zarezerwuj tę flagę dla analitycznych zadań tylko do odczytu. Szerszy obraz wydajności jest w naszych notatkach o wydajności dużych skoroszytów w HotXLS

Utrzymanie stabilnych zakotwiczeń, gdy siatka się zmienia

Raporty rzadko zostają w rozmiarze, w jakim je wygenerowano, i to tutaj model zakotwiczenia z początku artykułu się opłaca. Operacje strukturalne fasady XLSX (InsertRows, DeleteRows i odpowiedniki kolumnowe) przesuwają zależne warstwy razem z komórkami. Obszary scalone, hiperłącza, komentarze, zablokowane okienka, zakresy filtrów, formaty warunkowe, walidacje, tabele, nazwy zdefiniowane i, dla tego tematu, zakotwiczenia obrazów i wykresów podróżują wszystkie razem. Logo zakotwiczone w wierszu 1 zostaje na górze, gdy pod nim wejdzie dziesięć wierszy. Ramka wykresu zakotwiczona pod blokiem danych zjeżdża w dół, gdy blok rośnie. Jedyną rzeczą, która nie zostaje przepisana, jest łańcuch zakresu uchwycony jako literał przed wstawieniem, ponieważ jest to zwykły tekst, do którego biblioteka nie ma powodu wracać. To ustala bezpieczną kolejność wypełniania szablonu: najpierw zapisz i przekształć dane, a wykresy twórz i obrazy umieszczaj w ostatnim przebiegu, z każdym łańcuchem zakresu wyprowadzonym z liczby wierszy, którą masz po wstawieniach, a nie przed

Dwa mniejsze narzędzia domykają zestaw do rozmieszczania. TXLSTextBox.SetArea po stronie XLS zakotwicza istniejące pole tekstowe albo autokształt na nowym prostokącie komórek, co bije usuwanie i tworzenie go od nowa, gdy blok stopki się przesuwa. A bitmapowe przeciążenie AddPicture przyjmuje żywy TBitmap z opcjonalną flagą przezroczystości, więc wszystko, co potrafi narysować twój własny kod VCL (wskaźnik, pasek wykresu przebiegu w czasie, typ wykresu, którego lista natywna nie oferuje), można odbić wprost w arkuszu bez zapisywania najpierw pliku tymczasowego

Wykresy i obrazy są niemal zawsze warstwą wykańczającą już ustrukturyzowany raport, i dlatego o tym, czy wylądują czysto, decydują fundamenty. Wypełnianie danych, do których wykres będzie się odwoływał, opisuje generowanie raportów sterowane szablonem, a utrzymanie stabilnej siatki pod twoimi zakotwiczeniami jest tematem artykułu o scalonych komórkach i kontroli układu. Pełna dokumentacja klas i metod mieszka na stronie produktu komponentu HotXLS dla Delphi