Wydajność metody ładowania danych w IronXL

Zaktualizowano:czerwca 28, 2026

Translated

View the article in English

Kiedy wypełniasz duży arkusz w IronXL, wybrana metoda ładowania ma duży wpływ na czas generowania pliku. Poniższy benchmark porównuje trzy podejścia w stosunku do tego samego zbioru danych: 20 000 wierszy na 55 kolumn, każdy zapisany na dysku jako plik .xlsx.

Przegląd testu

Każdy test wypełnia te same dane i zapisuje wynik na dysku, rejestrując zarówno czas wyrażony w sekundach, jak i rozmiar powstałego pliku.

Dodawanie danych komórka po komórce: ~24 sekundy, 3094 KB
Ładowanie z DataTable: ~13 sekundy, 3094 KB
Ładowanie z CSV: ~9 sekundy, 3094 KB

Rozmiar pliku jest identyczny w przypadku wszystkich trzech. Zmienia się jedynie czas budowy pliku.

Metody

Opcja 1: Dodawanie danych komórka po komórce

To jest najwolniejsza z trzech. Każda komórka jest przydzielana indywidualnie za pomocą pętli zagnieżdżonych.

string[] fruits = { "Apples", "Apricot", "Banana", "Blackberry", "Blueberry", "Boysenberry", "Canary Melon", "Cantaloupe",
                        "Currants", "Dates (tree dried only) Dragon Fruit", "Durian (purchase cut) Figs", "Gooseberry", "Grapes", "Grapefruit",
                        "Lemon", "Lime", "Loganberry", "Longan", "Loquat", "Lychee", "Mandarin", "Mango",
                        "Blood Orange", "Papaya", "Passion Fruit", "Peach", "Pear", "Persimmon", "Pineapple", "Plum", "Honeydew",
                        "Rambutan", "Starfruit", "Tamarind", "Yuzu", "Açaí", "Abiu", "Ackee", "Breadfruit", "Cempedak",
                        "Cherimoya", "Buddha's Hand", "Citron", "Finger Lime", "Kumquat", "Pomelo", "Tangelo", "Ugli Fruit",
                        "Galia Melon", "Kiwano (Horned Melon)", "Mouse Melon", "Muskmelon",
                        "cherry", "nectarine", "Butternut squash"};
var workbook = WorkBook.Create(ExcelFileFormat.XLSX);
var worksheet = workbook.CreateWorkSheet("Fruits");
var columnsTitles = GetExcelCellLetters(fruits.Length);
for (int col = 0; col < columnsTitles.Count; col++)
{
    string columnLetter = columnsTitles[col]; // To avoid interpolation that can cause performance issues in large loops because it build string objects in every loop
    for (int row = 1; row <= 20000; row++)
    {
        worksheet[columnLetter + row].Value = fruits[col];
        //worksheet.Rows[row].Columns[columnLetter].Value.ToString();
    }
}
workbook.SaveAs("C:\\Temp\\ManualCell-by-CellAssignment.xlsx");
private static List<string> GetExcelCellLetters(int iMaxNum)
{
    string[] alphabet = { string.Empty, "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" };
    IEnumerable<string> lst = (from c1 in alphabet
                               from c2 in alphabet
                               from c3 in alphabet.Skip(1)
                               where c1 == string.Empty || c2 != string.Empty
                               select c1 + c2 + c3).Take(iMaxNum);
    return lst.ToList();
}

string[] fruits = { "Apples", "Apricot", "Banana", "Blackberry", "Blueberry", "Boysenberry", "Canary Melon", "Cantaloupe",
                        "Currants", "Dates (tree dried only) Dragon Fruit", "Durian (purchase cut) Figs", "Gooseberry", "Grapes", "Grapefruit",
                        "Lemon", "Lime", "Loganberry", "Longan", "Loquat", "Lychee", "Mandarin", "Mango",
                        "Blood Orange", "Papaya", "Passion Fruit", "Peach", "Pear", "Persimmon", "Pineapple", "Plum", "Honeydew",
                        "Rambutan", "Starfruit", "Tamarind", "Yuzu", "Açaí", "Abiu", "Ackee", "Breadfruit", "Cempedak",
                        "Cherimoya", "Buddha's Hand", "Citron", "Finger Lime", "Kumquat", "Pomelo", "Tangelo", "Ugli Fruit",
                        "Galia Melon", "Kiwano (Horned Melon)", "Mouse Melon", "Muskmelon",
                        "cherry", "nectarine", "Butternut squash"};
var workbook = WorkBook.Create(ExcelFileFormat.XLSX);
var worksheet = workbook.CreateWorkSheet("Fruits");
var columnsTitles = GetExcelCellLetters(fruits.Length);
for (int col = 0; col < columnsTitles.Count; col++)
{
    string columnLetter = columnsTitles[col]; // To avoid interpolation that can cause performance issues in large loops because it build string objects in every loop
    for (int row = 1; row <= 20000; row++)
    {
        worksheet[columnLetter + row].Value = fruits[col];
        //worksheet.Rows[row].Columns[columnLetter].Value.ToString();
    }
}
workbook.SaveAs("C:\\Temp\\ManualCell-by-CellAssignment.xlsx");
private static List<string> GetExcelCellLetters(int iMaxNum)
{
    string[] alphabet = { string.Empty, "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" };
    IEnumerable<string> lst = (from c1 in alphabet
                               from c2 in alphabet
                               from c3 in alphabet.Skip(1)
                               where c1 == string.Empty || c2 != string.Empty
                               select c1 + c2 + c3).Take(iMaxNum);
    return lst.ToList();
}

Imports System
Imports System.Collections.Generic
Imports System.Linq

Module Module1
    Sub Main()
        Dim fruits As String() = {"Apples", "Apricot", "Banana", "Blackberry", "Blueberry", "Boysenberry", "Canary Melon", "Cantaloupe",
                                  "Currants", "Dates (tree dried only) Dragon Fruit", "Durian (purchase cut) Figs", "Gooseberry", "Grapes", "Grapefruit",
                                  "Lemon", "Lime", "Loganberry", "Longan", "Loquat", "Lychee", "Mandarin", "Mango",
                                  "Blood Orange", "Papaya", "Passion Fruit", "Peach", "Pear", "Persimmon", "Pineapple", "Plum", "Honeydew",
                                  "Rambutan", "Starfruit", "Tamarind", "Yuzu", "Açaí", "Abiu", "Ackee", "Breadfruit", "Cempedak",
                                  "Cherimoya", "Buddha's Hand", "Citron", "Finger Lime", "Kumquat", "Pomelo", "Tangelo", "Ugli Fruit",
                                  "Galia Melon", "Kiwano (Horned Melon)", "Mouse Melon", "Muskmelon",
                                  "cherry", "nectarine", "Butternut squash"}
        Dim workbook = WorkBook.Create(ExcelFileFormat.XLSX)
        Dim worksheet = workbook.CreateWorkSheet("Fruits")
        Dim columnsTitles = GetExcelCellLetters(fruits.Length)
        For col As Integer = 0 To columnsTitles.Count - 1
            Dim columnLetter As String = columnsTitles(col)
            For row As Integer = 1 To 20000
                worksheet(columnLetter & row).Value = fruits(col)
            Next
        Next
        workbook.SaveAs("C:\Temp\ManualCell-by-CellAssignment.xlsx")
    End Sub

    Private Function GetExcelCellLetters(iMaxNum As Integer) As List(Of String)
        Dim alphabet As String() = {String.Empty, "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"}
        Dim lst = (From c1 In alphabet
                   From c2 In alphabet
                   From c3 In alphabet.Skip(1)
                   Where c1 = String.Empty OrElse c2 <> String.Empty
                   Select c1 & c2 & c3).Take(iMaxNum)
        Return lst.ToList()
    End Function
End Module

$vbLabelText $csharpLabel

Zwróć uwagę na buforowany columnLetter: budowanie referencji kolumny raz na kolumnę zamiast interpolowania wewnątrz zagnieżdżonej pętli unika alokowania nowego łańcucha przy każdej iteracji. Rezerwuj tę metodę do przypadków, które wymagają prawdziwej logiki lub stylizacji na poziomie komórki.

Opcja 2: Ładowanie z DataTable

Solidny środek, a znacznie szybszy niż ręczne wstawianie komórek. Zbuduj DataTable w pamięci, a następnie przekaż całość do LoadWorkSheet.

string[] fruits = { "Apples", "Apricot", "Banana", "Blackberry", "Blueberry", "Boysenberry", "Canary Melon", "Cantaloupe",
                        "Currants", "Dates (tree dried only) Dragon Fruit", "Durian (purchase cut) Figs", "Gooseberry", "Grapes", "Grapefruit",
                        "Lemon", "Lime", "Loganberry", "Longan", "Loquat", "Lychee", "Mandarin", "Mango",
                        "Blood Orange", "Papaya", "Passion Fruit", "Peach", "Pear", "Persimmon", "Pineapple", "Plum", "Honeydew",
                        "Rambutan", "Starfruit", "Tamarind", "Yuzu", "Açaí", "Abiu", "Ackee", "Breadfruit", "Cempedak",
                        "Cherimoya", "Buddha's Hand", "Citron", "Finger Lime", "Kumquat", "Pomelo", "Tangelo", "Ugli Fruit",
                        "Galia Melon", "Kiwano (Horned Melon)", "Mouse Melon", "Muskmelon",
                        "cherry", "nectarine", "Butternut squash"};
var workbook = WorkBook.Create(ExcelFileFormat.XLSX);
DataTable table = new DataTable("Fruits");
foreach (string fruitName in fruits)
{
    table.Columns.Add(fruitName);
}
int rowCount = 20000;
for (int i = 0; i < rowCount; i++)
{
    table.Rows.Add(fruits);
}
workbook.LoadWorkSheet(table);
workbook.SaveAs("C:\\Temp\\LoadDataTableDirectly.xlsx");

string[] fruits = { "Apples", "Apricot", "Banana", "Blackberry", "Blueberry", "Boysenberry", "Canary Melon", "Cantaloupe",
                        "Currants", "Dates (tree dried only) Dragon Fruit", "Durian (purchase cut) Figs", "Gooseberry", "Grapes", "Grapefruit",
                        "Lemon", "Lime", "Loganberry", "Longan", "Loquat", "Lychee", "Mandarin", "Mango",
                        "Blood Orange", "Papaya", "Passion Fruit", "Peach", "Pear", "Persimmon", "Pineapple", "Plum", "Honeydew",
                        "Rambutan", "Starfruit", "Tamarind", "Yuzu", "Açaí", "Abiu", "Ackee", "Breadfruit", "Cempedak",
                        "Cherimoya", "Buddha's Hand", "Citron", "Finger Lime", "Kumquat", "Pomelo", "Tangelo", "Ugli Fruit",
                        "Galia Melon", "Kiwano (Horned Melon)", "Mouse Melon", "Muskmelon",
                        "cherry", "nectarine", "Butternut squash"};
var workbook = WorkBook.Create(ExcelFileFormat.XLSX);
DataTable table = new DataTable("Fruits");
foreach (string fruitName in fruits)
{
    table.Columns.Add(fruitName);
}
int rowCount = 20000;
for (int i = 0; i < rowCount; i++)
{
    table.Rows.Add(fruits);
}
workbook.LoadWorkSheet(table);
workbook.SaveAs("C:\\Temp\\LoadDataTableDirectly.xlsx");

Imports System.Data

Dim fruits As String() = {"Apples", "Apricot", "Banana", "Blackberry", "Blueberry", "Boysenberry", "Canary Melon", "Cantaloupe",
                          "Currants", "Dates (tree dried only) Dragon Fruit", "Durian (purchase cut) Figs", "Gooseberry", "Grapes", "Grapefruit",
                          "Lemon", "Lime", "Loganberry", "Longan", "Loquat", "Lychee", "Mandarin", "Mango",
                          "Blood Orange", "Papaya", "Passion Fruit", "Peach", "Pear", "Persimmon", "Pineapple", "Plum", "Honeydew",
                          "Rambutan", "Starfruit", "Tamarind", "Yuzu", "Açaí", "Abiu", "Ackee", "Breadfruit", "Cempedak",
                          "Cherimoya", "Buddha's Hand", "Citron", "Finger Lime", "Kumquat", "Pomelo", "Tangelo", "Ugli Fruit",
                          "Galia Melon", "Kiwano (Horned Melon)", "Mouse Melon", "Muskmelon",
                          "cherry", "nectarine", "Butternut squash"}

Dim workbook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim table As New DataTable("Fruits")

For Each fruitName As String In fruits
    table.Columns.Add(fruitName)
Next

Dim rowCount As Integer = 20000
For i As Integer = 0 To rowCount - 1
    table.Rows.Add(fruits)
Next

workbook.LoadWorkSheet(table)
workbook.SaveAs("C:\Temp\LoadDataTableDirectly.xlsx")

$vbLabelText $csharpLabel

LoadWorkSheet pobiera całą tabelę w jednym wywołaniu, dlatego bije ręczne pisanie każdej komórki. Sięgnij po to, gdy twoje dane już są w uporządkowanej pamięci.

Opcja 3: Ładowanie z CSV

Największa wydajność i najlepszy wybór dla płaskich, rozdzielonych danych. Zapisz wiersze do pliku CSV, a następnie odczytaj go przy użyciu LoadCSV.

string[] fruits = { "Apples", "Apricot", "Banana", "Blackberry", "Blueberry", "Boysenberry", "Canary Melon", "Cantaloupe",
                        "Currants", "Dates (tree dried only) Dragon Fruit", "Durian (purchase cut) Figs", "Gooseberry", "Grapes", "Grapefruit",
                        "Lemon", "Lime", "Loganberry", "Longan", "Loquat", "Lychee", "Mandarin", "Mango",
                        "Blood Orange", "Papaya", "Passion Fruit", "Peach", "Pear", "Persimmon", "Pineapple", "Plum", "Honeydew",
                        "Rambutan", "Starfruit", "Tamarind", "Yuzu", "Açaí", "Abiu", "Ackee", "Breadfruit", "Cempedak",
                        "Cherimoya", "Buddha's Hand", "Citron", "Finger Lime", "Kumquat", "Pomelo", "Tangelo", "Ugli Fruit",
                        "Galia Melon", "Kiwano (Horned Melon)", "Mouse Melon", "Muskmelon",
                        "cherry", "nectarine", "Butternut squash"};

            var sb = new StringBuilder();
            // Step 1: Add header row
            sb.AppendLine(string.Join(",", fruits));
            // Step 2: Add 20,000 identical rows
            string rowData = string.Join(",", fruits); // same as headers
            for (int i = 1; i < 20000; i++)
            {
                sb.AppendLine(rowData);
            }
            File.WriteAllText("C:\\Temp\\csvfile.csv", sb.ToString(), Encoding.UTF8);
            var workbook = WorkBook.LoadCSV("C:\\Temp\\csvfile.csv");
            workbook.SaveAs("C:\\Temp\\LoadFromCSV.xlsx");

string[] fruits = { "Apples", "Apricot", "Banana", "Blackberry", "Blueberry", "Boysenberry", "Canary Melon", "Cantaloupe",
                        "Currants", "Dates (tree dried only) Dragon Fruit", "Durian (purchase cut) Figs", "Gooseberry", "Grapes", "Grapefruit",
                        "Lemon", "Lime", "Loganberry", "Longan", "Loquat", "Lychee", "Mandarin", "Mango",
                        "Blood Orange", "Papaya", "Passion Fruit", "Peach", "Pear", "Persimmon", "Pineapple", "Plum", "Honeydew",
                        "Rambutan", "Starfruit", "Tamarind", "Yuzu", "Açaí", "Abiu", "Ackee", "Breadfruit", "Cempedak",
                        "Cherimoya", "Buddha's Hand", "Citron", "Finger Lime", "Kumquat", "Pomelo", "Tangelo", "Ugli Fruit",
                        "Galia Melon", "Kiwano (Horned Melon)", "Mouse Melon", "Muskmelon",
                        "cherry", "nectarine", "Butternut squash"};

            var sb = new StringBuilder();
            // Step 1: Add header row
            sb.AppendLine(string.Join(",", fruits));
            // Step 2: Add 20,000 identical rows
            string rowData = string.Join(",", fruits); // same as headers
            for (int i = 1; i < 20000; i++)
            {
                sb.AppendLine(rowData);
            }
            File.WriteAllText("C:\\Temp\\csvfile.csv", sb.ToString(), Encoding.UTF8);
            var workbook = WorkBook.LoadCSV("C:\\Temp\\csvfile.csv");
            workbook.SaveAs("C:\\Temp\\LoadFromCSV.xlsx");

Imports System.IO
Imports System.Text

Dim fruits As String() = {"Apples", "Apricot", "Banana", "Blackberry", "Blueberry", "Boysenberry", "Canary Melon", "Cantaloupe",
                          "Currants", "Dates (tree dried only) Dragon Fruit", "Durian (purchase cut) Figs", "Gooseberry", "Grapes", "Grapefruit",
                          "Lemon", "Lime", "Loganberry", "Longan", "Loquat", "Lychee", "Mandarin", "Mango",
                          "Blood Orange", "Papaya", "Passion Fruit", "Peach", "Pear", "Persimmon", "Pineapple", "Plum", "Honeydew",
                          "Rambutan", "Starfruit", "Tamarind", "Yuzu", "Açaí", "Abiu", "Ackee", "Breadfruit", "Cempedak",
                          "Cherimoya", "Buddha's Hand", "Citron", "Finger Lime", "Kumquat", "Pomelo", "Tangelo", "Ugli Fruit",
                          "Galia Melon", "Kiwano (Horned Melon)", "Mouse Melon", "Muskmelon",
                          "cherry", "nectarine", "Butternut squash"}

Dim sb As New StringBuilder()
' Step 1: Add header row
sb.AppendLine(String.Join(",", fruits))
' Step 2: Add 20,000 identical rows
Dim rowData As String = String.Join(",", fruits) ' same as headers
For i As Integer = 1 To 19999
    sb.AppendLine(rowData)
Next
File.WriteAllText("C:\Temp\csvfile.csv", sb.ToString(), Encoding.UTF8)
Dim workbook = WorkBook.LoadCSV("C:\Temp\csvfile.csv")
workbook.SaveAs("C:\Temp\LoadFromCSV.xlsx")

$vbLabelText $csharpLabel

LoadCSV analizuje tekst rozdzielony masowo, co utrzymuje go przed innymi dwoma podejściami.

Wnioski

Aby uzyskać najlepszą wydajność IronXL na dużych zbiorach danych:

Używaj CSV: najszybsza droga do importu surowych danych, gdy źródło jest już płaskie.
Użyj DataTable: idealny, gdy dane już istnieją w uporządkowanej pamięci.
Unikaj pisania komórka po komórce: warte jedynie, gdy potrzeba logiki lub stylizacji dla każdej komórki.

Wszystkie trzy generują ten sam rozmiar pliku. Metoda ładowania to, co dramatycznie zmienia czas generowania pliku.

Curtis Chau

Czat z zespołem inżynierów teraz

Autor tekstów technicznych

Curtis Chau posiada tytuł licencjata z informatyki (Uniwersytet Carleton) i specjalizuje się w front-endowym rozwoju, z ekspertką w Node.js, TypeScript, JavaScript i React. Pasjonuje się tworzeniem intuicyjnych i estetycznie przyjemnych interfejsów użytkownika, Curtis cieszy się pracą z nowoczesnymi frameworkami i tworzeniem dobrze zorganizowanych, atrakcyjnych wizualnie podrę...

Czytaj więcej

Gotowy, aby rozpocząć?

Nuget Pliki do pobrania 2,134,203 | Wersja: 2026.7 właśnie wydany