1. IronXL
  2. Solução de problemas
  3. Desempenho dos Métodos de Carregamento de Dados do IronXL

Desempenho do Método de Carregamento de Dados do IronXL

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

Quando você preenche uma grande planilha no IronXL, o método de carregamento que você escolhe tem um grande efeito sobre o tempo que o arquivo leva para ser gerado. O benchmark abaixo compara três abordagens com o mesmo conjunto de dados: 20.000 linhas por 55 colunas, cada uma escrita no disco como um arquivo .xlsx.

Visão Geral do Teste

Cada teste preenche os mesmos dados e grava o resultado no disco, registrando tanto a duração em segundos quanto o tamanho do arquivo resultante.

  • Adicionando dados célula por célula: ~24 seg, 3094 KB
  • Carregando de um DataTable: ~13 seg, 3094 KB
  • Carregamento de CSV: ~9 seg, 3094 KB

O tamanho do arquivo é idêntico em todos os três. Apenas o tempo para criar o arquivo muda.

Os Métodos

Opção 1: Adicionando Dados Célula por Célula

Esta é a mais lenta das três. Cada célula é atribuída individualmente através de loops aninhados.

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

Note o cache columnLetter: construir a referência da coluna uma vez por coluna em vez de interpolá-la dentro do loop interno evita alocar uma nova string a cada iteração. Reserve este método para casos que necessitam de lógica ou estilo genuíno por célula.

Diagnósticos do Visual Studio para execução de benchmark célula por célula do IronXL

Opção 2: Carregamento a partir de um DataTable

Um meio termo sólido, e significativamente mais rápido que a inserção manual de células. Construa um DataTable na memória, depois entregue tudo de uma vez para 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 ingere a tabela inteira em uma chamada, o que é por isso que supera escrever cada célula à mão. Use isto quando seus dados já estiverem em memória estruturada.

Saída do Visual Studio mostrando o carregamento do DataTable demorando 13,31 segundos

Opção 3: Carregamento a partir de CSV

Mais rápido no geral, e a escolha natural para dados planos e delimitados. Escreva as linhas para um arquivo CSV, depois leia de volta com 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 analisa o texto delimitado em massa, o que o mantém à frente das outras duas abordagens.

Saída do Visual Studio mostrando o carregamento de CSV demorando 9,041 segundos

Conclusão

Para obter o melhor desempenho do IronXL em grandes conjuntos de dados:

  • Use CSV: o caminho mais rápido para importação de dados brutos quando a fonte já é plana.
  • Use um DataTable: ideal quando os dados já existem em memória estruturada.
  • Evite escrita célula por célula: só vale a pena quando você necessita de lógica ou estilo por célula.

Todos os três produzem o mesmo tamanho de arquivo. O método de carregamento é o que muda drasticamente quanto tempo o arquivo leva para ser gerado.

Curtis Chau
Curtis Chau
  Converse agora mesmo com a equipe de engenharia.
Redator Técnico

Curtis Chau é bacharel em Ciência da Computação (Universidade Carleton) e se especializa em desenvolvimento front-end, com experiência em Node.js, TypeScript, JavaScript e React. Apaixonado por criar interfaces de usuário intuitivas e esteticamente agradáveis, Curtis gosta de trabalhar com frameworks modernos e criar manuais ...

Leia mais
Pronto para começar?
Nuget Downloads 2,134,203 | Versão: 2026.7 recém-lançado
Still Scrolling Icon

Ainda está rolando a tela?

Quer provas rápidas? PM > Install-Package IronXL.Excel
executar um exemplo Veja seus dados se transformarem em uma planilha.