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で大規模なワークシートを埋めるときに選択するロードメソッドは、ファイルの生成時間に大きな影響を与えます。 以下のベンチマークは、同じデータセットに対する3つのアプローチを比較しています：20,000行 × 55列、各列はディスクに.xlsxファイルとして書き込まれます。

テストの概要

すべてのテストは同じデータを埋め込み、結果をディスクに書き込み、秒単位の期間と結果となるファイルサイズの両方を記録します。

  • セルごとのデータ追加: 約24秒、3094KB
  • DataTableからの読み込み： 約13秒、3094 KB
  • CSVから読み込み: 約9秒、3094KB

ファイルサイズは3つの方法すべてで同一です。 生成にかかる時間だけが変わります。

方法

オプション1: セルごとのデータ追加

これは3つのうち最も遅い方法です。 各セルはネストされたループで個別に割り当てられます。

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に注意してください：列参照を列ごとに1回だけ構築することで、内側のループで補間する代わりに、毎回新しい文字列を割り当てることを避けることができます。 本当にセルごとのロジックやスタイリングが必要なケースにこのメソッドを使いましょう。

セルごとの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はワンコールで全テーブルをインポートします。このため、各セルを手作業で書き込む場合よりも優れています。 データがすでに構造化されたメモリに存在する場合にこれを使用します。

DataTable読み込みが13.31秒かかるVisual Studio出力

オプション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は一括で区切りテキストを解析し、それが他の2つのアプローチよりも優れている理由です。

CSV読み込みが9.041秒かかるVisual Studio出力

結論

IronXLで大規模なデータセットを最も効果的に動作させるために：

  • CSVを使用する: すでにフラットなソースの場合、未処理データをインポートする最速の方法です。
  • DataTableを使用する： データが既に構造化メモリに存在している場合に理想的です。
  • セルごとの書き込みを避ける: 必要である特定のセルロジックやスタイリングが必要なときにのみ価値があります。

すべての方法で同じファイルサイズが生成されます。読み込み方式がファイルの生成時間を大幅に変えます。

Curtis Chau
Curtis Chau
  今すぐエンジニアリングチームとチャット
テクニカルライター

Curtis Chauは、カールトン大学でコンピュータサイエンスの学士号を取得し、Node.js、TypeScript、JavaScript、およびReactに精通したフロントエンド開発を専門としています。直感的で美しいユーザーインターフェースを作成することに情熱を持ち、Curtisは現代のフレームワークを用いた開発や、構造の良い視覚的に魅力的なマニュアルの作成を楽しんでいます。

開発以外にも、CurtisはIoT（Internet of Things）への強い関心を持ち、ハードウェアとソフトウェアの統合方法を模索しています。余暇には、ゲームをしたりDiscordボットを作成したりして、技術に対する愛情と創造性を組み合わせています。

準備はできましたか？
Nuget ダウンロード 2,134,203 | バージョン: 2026.7 リリースされたばかり
Still Scrolling Icon

まだスクロールしていますか?

すぐに証拠が欲しいですか? PM > Install-Package IronXL.Excel
サンプルを実行する あなたのデータがスプレッドシートになるのを見る。