1. IronXL
  2. 故障排除
  3. IronXL資料載入方法效能

IronXL資料載入方法性能

Curtis Chau
Curtis Chau
已更新:
This article was translated from English: Does it need improvement?
Translated
View the article in English

當您在IronXL中填充一個大型工作表時，您選擇的載入方法對生成文件所需的時間有重大影響。 以下基準比較了三種方法在相同資料集上的效果：20,000行和55列，每列寫入磁盤作為.xlsx檔案。

測試概述

每次測試都填充相同的資料並將結果寫入磁碟，記錄持續時間（以秒為單位）和生成的文件大小。

  • 逐個儲存格新增資料： ~24秒，3094 KB
  • 從DataTable載入： 約13秒，3094 KB
  • 從CSV載入： ~9秒，3094 KB

所有這三種文件大小都是相同的。 只有構建文件所需的時間改變了。

方法

選項1：逐個儲存格新增資料

這是三種中最慢的。 通過巢狀迴圈單獨分配每個儲存格。

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

注意快取的columnLetter: 每列僅構建一次列引用而不是在內層迴圈中插值，避免了在每次迭代時分配置新的字串。 為需要真正逐儲存格邏輯或樣式的情況保留此方法。

適用於逐個單元格的IronXL基準測試運行的Visual Studio診斷

選項2：從DataTable載入

一個穩固的中間選擇，明顯快於手動儲存格插入。 在記憶體中構建一個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 在一次呼叫中攝取整個表格，這就是為什麼它能勝過逐個單元格手動寫入。 當您的資料已經存在於結構化記憶體中時，請使用此方法。

Visual Studio輸出顯示DataTable載入耗時13.31秒

選項3：從CSV載入

整體最快，是平面分隔資料的自然選擇。 將行寫入CSV檔案，然後用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 批量解析定界文字，這使得它比其他兩種方法更快。

Visual Studio輸出顯示CSV載入耗時9.041秒

結論

要從IronXL中獲得大型資料集的最佳性能：

  • 使用CSV： 當來源已經是平面時，原始資料導入的最快途徑。
  • 使用DataTable： 在資料已存在於結構化記憶體中時最理想。
  • 避免逐格撰寫： 只有當您需要逐個單元格的邏輯或樣式時，才值得。

所有這三種方法產生的文件大小是一樣的。載入方式是影響生成文件所需時間的因素。

Curtis Chau
Curtis Chau
  立即與工程團隊聊天
技術作家

Curtis Chau擁有Carleton大學的電腦科學學士學位，專精於前端開發，擁有Node.js、TypeScript、JavaScript和React的專業知識。Curtis熱衷於建立直觀且美觀的使用者介面，喜愛使用現代框架並建立結構良好、視覺吸引力的手冊。

除了開發，Curtis對物聯網（IoT）有濃厚的興趣，探索創新的方法來整合硬體和軟體。在空閒時間，他喜歡玩遊戲和建立Discord機器人，結合他對技術的熱愛與創造力。

準備好開始了嗎？
Nuget 下載 2,134,203 | 版本： 2026.7 剛剛發布
Still Scrolling Icon

仍在滾動嗎？

想要快速證明嗎？ PM > Install-Package IronXL.Excel
運行一個範例 觀看您的資料成為試算表。