C#: eksport danych do pliku XLS

Jakiś czas temu pojawiła się w firmie, w której pracuję potrzeba uaktualnienia narzędzia do prezentacji danych. Jako, że źródła poprzedniej wersji (nie pisanej przeze mnie) poszły.. khem.. hen daleko w momencie spalenia się dysku twardego, trzeba było napisać tą aplikację od nowa, co okazało się interesującym doświadczeniem. Było to typowe zagadnienie inżynierii odwrotnej. Opisywać wyświetlania danych na DataGridView nie będę ;), skupię się na innej funkcjonalności – eksporcie danych do pliku .xls (MS Excel).

Założenia:

  • pojedynczy rekord to tablica string’ów (jeżeli zawartością są liczby potrzebne są wiodące zera, więc trzymane są jako tekst)
  • każdy rekord w obrębie pliku ma taką samą ilość pól
  • w różnych plikach ilość pól w rekordzie jest różna
  • ilość rekordów (wierszy) w pojedynczym pliku jest teoretycznie nieograniczona, ale z reguły nie przekracza 10.000
  • eksport do xls’a ma być wykonywany mniej więcej w czasie rzeczywistym (dopuszczalne są opóźnienia rzędu 3-4 s)

Microsoft dostarcza interfejsy potrzebne programiście do dobrania się do zawartości istniejącego arkusza Excel’a, utworzenie nowego arkusza oraz przeprowadzenia na nich dowolnych operacji. Należy do projektu dodać referencję do COM’owych interfejsów Excela

COMObjects

w oknie Solution Explorer pojawią się referencje do „Microsoft.Office.Core”, „Microsoft.Office.Interop.Excel” oraz „VBIDE”

SolutionExplorer

w sekcji usings powinno pojawić się

using Microsoft.Office.Interop.Excel;

aby móc korzystać z możliwości Excela w C#.

Do dalszej pracy będzie potrzebna możliwość wyznaczania nazwy kolumny w arkuszu na podstawie jej numeru, będą to kolejno „A”, „B”, .. , „Z”, „AA”, „AB”, .. , „ZZ”, „AAA” itd.
Służy do tego mała statyczna klasa:

public static class ColumnNames
{
    private static readonly List<string> Letters = new List<string>
    {
        "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"
    };

    public static string Get(int i)
    {
        List<int> list = new List<int> { i };
        int index = 0;

        while (index < list.Count)
        {
            while (list[index] >= Letters.Count)
            {
                if (index == list.Count - 1)
                {
                    list.Add(0);
                    list[index] -= Letters.Count;
                    continue;
                }
                ++list[index + 1];
                list[index] -= Letters.Count;
            }
        ++index;
        }

        list.Reverse();
        string res = string.Empty;
        foreach (var s in list) res += Letters[s];
        return res;
    }
}

Do notki dołączyłem konsolowy projekt Visual Studio (ExportToExcel – projekt Visual Studio) zawierający kod klas ColumnNames oraz Program. Komentarze w kodzie dość dobrze opisują co się tam dzieje, skupię się na najważniejszych kilku punktach:

private static string[,] data;

Tak, jak było to wyspecyfikowane, dane są trzymane jako dwuwymiarowa tablica string'ów. Pojedynczy wiersz to jeden rekord.

Microsoft.Office.Interop.Excel.Application excelApplication = new Application
{
    SheetsInNewWorkbook = 2,     // ilość arkuszy w pliku
    UserControl = true           // TRUE, jeżeli obiekt Application został stworzony programowo
};

Workbook workbook = excelApplication.Workbooks.Add(Missing.Value);
Sheets worksheets = excelApplication.Worksheets;

// pierwszy arkusz (indeks zaczyna się od 1!), tam będzie utworzona tabela przestawna
Worksheet pivotWorksheet = (Worksheet)worksheets.get_Item(1);
pivotWorksheet.Name = PivotTableSheetName;

// arkusz z danymi
Worksheet dataWorksheet = (Worksheet)worksheets.get_Item(2);
dataWorksheet.Name = DataSheetName;

ExportDataToExcel(data, dataWorksheet);
CreateExcelPivotTable(dataWorksheet, pivotWorksheet, workbook);

Powyższy fragment kodu jest odpowiedzialny za utworzenie „wirtualnej” aplikacji Excel działającej w tle podczas działania naszego programu. Wyodrębniamy też obiekt workbook symbolizujący pojedynczy plik XLS oraz obiekty dataWorksheet oraz pivotWorksheet – arkusze w pliku. W linii 18 wywołujemy metodę zapisującą dane do odpowiedniego arkusza, linia 19 zawiera wywołanie metody tworzącej tabelę przestawną (o której na pewno kiedyś oddzielnie napiszę, bo to przydatna bestia).

Najbardziej interesującym fragmentem kodu jest treść metody ExportDataToExcel. Nie przedłużając więc:

private static void ExportDataToExcel(string[,] data, Worksheet worksheet)
{
    // pobranie liczby wierszy = ilość rekordów
    var dataRows = data.GetLength(0);
    // pobranie liczby kolumn = ilość pól w rekordzie
    var dataColumns = data.GetLength(1);

    for (var c = 0; c < dataColumns; ++c)
    {
        // kolor komórki
        ((Range)worksheet.Cells[1, c + 1]).Interior.Color = ColorTranslator.ToWin32(Color.Yellow);
        // wyśrodkowanie pionowe i poziome
        ((Range)worksheet.Cells[1, c + 1]).VerticalAlignment = XlVAlign.xlVAlignCenter;
        ((Range)worksheet.Cells[1, c + 1]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
        ((Range)worksheet.Cells[1, c + 1]).Font.Bold = true;

        // tekst w komórce
        worksheet.Cells[1, c + 1] = string.Format("ColumnName{0}", c);
    }

    // obliczenie nazw komórek skrajnych (góra-lewo i dół-prawo) dla danych,
    // zaczynamy od A2, bo w 1. wierszu są nagłówki
    var topLeftLetter = ColumnNames.Get(0) + "2";
    var bottomRightLetter = ColumnNames.Get(dataColumns - 1) + (dataRows + 1);

    // kluczowa operacja - wypełnienie wskazanego zakresu danymi
    worksheet.get_Range(topLeftLetter, bottomRightLetter).Value2 = data;

    // autodopasowanie szerokości kolumn
    worksheet.get_Range(topLeftLetter, bottomRightLetter).EntireColumn.AutoFit();
}

Pierwsza pętla for ustawia nagłówki dla kolumn w wierszu 1 (żółte tło, wyśrodkowanie tekstu, bold), dalsza inwigilacja obiektu Range na MSDN dostarcza informacji o innych ciekawych właściwościach.
Linie 23 i 24 to wyznaczenie zakresu (również obiekt Range), do którego trafią dane z tablicy data. Musimy go określić za pomocą adresów dwóch komórek: „górnej-lewej” (w tym przypadku – A2) oraz „dolnej-prawej” (np. dla 30 rekordów o 50 polach każdy będzie to AX31).

Wypełnienie zakresu danymi to niepozorna linia 27

worksheet.get_Range(topLeftLetter, bottomRightLetter).Value2 = data;

Moje testy wykazały, że kopiowanie 500 rekordów o 30 polach trwa ~1.5 sekundy, co jest wartością absolutnie dopuszczalną i zadowalającą.

Kolejnym elementem jest stworzenie tabeli przestawnej, poniżej treść metody:

private static void CreateExcelPivotTable(Worksheet dataWorksheet, Worksheet pivotWorksheet, Workbook wb)
{
    // Pobierz indeksy ostatniej kolumny i wiersza z danych do utworzenia tabeli przestawnej
    int lastCol = dataWorksheet.UsedRange.Columns.Count;
    int lastRow = dataWorksheet.UsedRange.Rows.Count;

    // Utwórz zakres źródłowy - dane do utworzenia tabeli przestawnej
    var sourceRange = dataWorksheet.get_Range(dataWorksheet.Cells[1, 1], dataWorksheet.Cells[lastRow, lastCol]);

    // Utwórz zakres docelowy - tabela przestawna będzie umieszczona w komórce A3
    var destinationRange = pivotWorksheet.get_Range("A3", Missing.Value);

    /* Utworzenie tabeli przestawnej (wszystkie parametry są opcjonalne, lista poniżej)
     * [XlPivotTableSourceType] SourceType - typ źródła danych (XlPivotTableSourceType.xlDatabase oznacza dane z Excel'a)
     * [Range] SourceData - dane do tabeli
     * [Range] TableDestination - miejsce umieszczenia
     * [string] TableName - nazwa tabeli przestawnej
     * [bool] RowGrand - czy pokazać sumy w wierszach
     * [bool] ColumnGrand - czy pokazać sumy w wierszach
     * [bool] SaveData - TRUE jeżeli zapisać dane z raportu tabeli przestawnej
     * [bool] HasAutoFormat - TRUE aby automatycznie dopasowywać tabelę po odświeżeniu
     * [bool] AutoPage - używany tylko wtedy, gdy pierwszy parametr to XlPivotTableSourceType.xlConsolidation
     * [] Reserved - nieużywany przez Excel'a
     * [bool] BackgroundQuery - TRUE jeżeli zapytania do tabeli przestawnej mają być uruchamiane asynchronicznie w tle
     * [bool] OptimizeCache - czy optymalizować cache tabeli
     * [XlOrder] PageFieldOrder - kolejność przetwarzania pól przy tworzeniu tabeli, domyślnie XlOrder.xlDownThenOver
     * [int] PageFieldWrapCount - domyślnie 0
     * [bool] ReadData - TRUE aby utworzyć tabelę przestawną ze wszystkich rekordów/pól z zewnętrznego źródła danych
     * [string] Connection - Connection string zawierający ustawienia ODBC
     */
    wb.PivotTableWizard(XlPivotTableSourceType.xlDatabase,  // SourceType
                                sourceRange, // SourceData
                                destinationRange, // TableDestination
                                PivotTableSheetName, // TableName
                                true, // RowGrand
                                true, // ColumnGrand
                                true, // SaveData
                                true, // HasAutoFormat
                                Missing.Value, // AutoPage
                                Missing.Value, // Reserved
                                false, // BackgroundQuery
                                false, // OptimizeCache
                                XlOrder.xlDownThenOver, // PageFieldOrder
                                Missing.Value, // PageFieldWrapCount
                                Missing.Value, // ReadData
                                Missing.Value // Connection
                               );
}

Całej funkcjonalności (metoda PivotTableWizard) dostarczają interfejsy Excel’a, wystarczy tylko podać odpowiednie parametry (znaczenie parametrów w komentarzu przed metodą lub na MSDN).

Kod odpowiedzialny za zapisanie pliku na dysk:

// ścieżka do pliku + nazwa pliku BEZ ROZSZERZENIA
var xlsfilename = PATH + filename;

if (File.Exists(xlsfilename))
{
    if (IsOpened(xlsfilename))
    {
        // jeżeli istnieje i jest otwarty - uaktualnij dane
        OverwriteFile(xlsfilename);
        // zamknij otwarty w programie obiekt Workbook bez zapisywania
        workbook.Close(false, xlsfilename, Missing.Value);
    }
    else
    {
        // jeżeli istnieje i nie jest otwarty - skasuj stary plik
        File.Delete(xlsfilename);
        // zapisz i zamknij otwarty w programie obiekt Workbook
        workbook.Close(true, xlsfilename, Missing.Value);
    }
}
else
{
    // jeżeli nie istnieje, zapisz i zamknij otwarty w programie obiekt Workbook
    workbook.Close(true, xlsfilename, Missing.Value);
}

Przy zapisywaniu pliku na dysk nie podajemy rozszerzenia explicite, zrobi to za nas interfejs dodając rozszerzenie zgodne z używaną wersją Office’a (.xls lub .xlsx). W kodzie podałem też jak poradzić sobie z sytuacją, gdy uaktualniamy dane na otwartym pliku – jest to jak najbardziej możliwe. Arkusz z danymi jest wtedy czyszczony, dane zapisywane ponownie a tabele przestawne uaktualniane. To też było wymogiem aplikacji, którą pisałem.

Podczas pisania aplikacji na potrzeby tej notki natknąłem się na następujący problem – wyjątek konstruktora obiektu Microsoft.Office.Interop.Excel.Application, a dalej również każde odwołanie do jakiegokolwiek obiektu tej biblioteki.

COMException

Ten błąd nie występował podczas pisania aplikacji w pracy, jednak tam pracuję na Windows XP. Szczerze mówiąc, nie wiem co jest przyczyną tego błędu, ale pomogła wygooglana rada odnośnie zmiany parametru CultureInfo dla bieżącego wątku na czas trwania programu. Wystarczy dodać do kodu:

CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");

/* kod programu */

System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;

Efekt uruchomienia programu – plik na dysku

File

oraz jego zawartość

TestFile

Nie taki Excel straszny, jak go malują, dobieranie się do arkuszy, pojedynczych komórek, czy całych zakresów z poziomu C# jest łatwe, a aplikacje biznesowe mogą dużo zyskać na możliwości eksportu danych do ukochanego przez managerów formatu.

Be Sociable, Share!
czoper opublikowano dnia 2010-3-27 Kategoria: Biblioteki, Programowanie | Tagi:, , ,

Odpowiedzi: 5 Zostaw komentarz

  1. czoper @ 2015-5-17 12:24

    Do 20000 wierszy chyba nie doszedłem, natomiast wersja produkcyjna narzędzia spokojnie radziła sobie z plikami o kilku tysiącach wierszy i ponad 30 (czasami nawet do 50) kolumnach. Eksport trwał max kilka sekund.

  2. #4Marcin @ 2015-4-1 16:44

    Witam. Czy testował Pan przedstawione rozwiązanie przy bardzo dużych ilościach danych? Mam na myśli tak min. 20000 wierszy, po 10 kolumn w każdym. Jak prezentuje się wówczas sprawa wydajności? Pozdrawiam

  3. #3paszklar @ 2010-4-3 10:03

    Oczywiście mały szczegół: taka aplikacja wymaga, aby na komputerze na którym jest uruchamiana był zainstalowany Excel. O ile na komputerze osobistym większość ma ten program, to np. na jakimś serwerze to może być już problem.

  4. #2czoper @ 2010-3-27 16:00

    nie, kod był trzymany lokalnie. Teraz autor w myśl zasady „programiści dzielą się na takich, którzy robią backupy i takich, którzy BĘDĄ robić backupy” archiwizuje powstały kod 😉

  5. #1Michniewicz @ 2010-3-27 15:49

    Na SVN’ie się ten dysk spalił? ;P

Zostaw odpowiedź

(Ctrl + Enter)