Erro ao Exportar Dados Volumosos para Excel

Ao utilizar a biblioteca NPOI para gerar arquivos Excel no formato legado .xls, é comum encontrar o seguinte erro quando o volume de dados excede a capacidade do formato:

Invalid row number (65536) outside allowable range (0..65535)

Essa limitação é inerente ao formato Excel 97-2003 (HSSF), que suporta no máximo 65.536 linhas por planilha. Quando a quantidade de registros ultrapassa esse teto, a tentativa de criar uma linha com índice igual ou superior a 65536 gera a exceção.

Abordagem Original com Limitação

O código abaixo demonstra uma implementação que falha ao exportar grandes volumes de dados no formato .xls:

public void GerarRelatorioExcel(DataTable dados, string nomeArquivo)
{
    var dialogo = new SaveFileDialog
    {
        Title = "Salvar relatório",
        Filter = "Excel (*.xlsx)|*.xlsx|Excel 97-2003 (*.xls)|*.xls",
        FilterIndex = 1,
        AddExtension = true,
        RestoreDirectory = true,
        FileName = nomeArquivo
    };

    if (dialogo.ShowDialog() != DialogResult.OK)
        return;

    var caminhoArquivo = dialogo.FileName;
    var totalRegistros = dados.Rows.Count;
    var registrosProcessados = 0;

    var extensao = Path.GetExtension(caminhoArquivo).ToLower();
    IWorkbook livro;

    if (extensao == ".xlsx")
        livro = new XSSFWorkbook();
    else if (extensao == ".xls")
        livro = new HSSFWorkbook();
    else
        return;

    var nomeSheet = string.IsNullOrEmpty(nomeArquivo) ? "Dados" : nomeArquivo;
    var planilha = livro.CreateSheet(nomeSheet);

    var cronometro = Stopwatch.StartNew();

    try
    {
        // Escrita do cabeçalho
        var linhaCabecalho = planilha.CreateRow(0);
        for (var col = 0; col < dados.Columns.Count; col++)
        {
            linhaCabecalho.CreateCell(col).SetCellValue(dados.Columns[col].ColumnName);
        }

        // Escrita dos dados - falha se totalRegistros > 65535 no formato xls
        for (var idx = 0; idx < dados.Rows.Count; idx++)
        {
            var linhaDados = planilha.CreateRow(idx + 1);
            for (var col = 0; col < dados.Columns.Count; col++)
            {
                linhaDados.CreateCell(col).SetCellValue(dados.Rows[idx][col].ToString());
            }

            registrosProcessados++;
            var progresso = (int)(100 * registrosProcessados / totalRegistros);
            AtualizarBarraProgresso(totalRegistros, progresso);
            Application.DoEvents();
        }

        SalvarArquivo(livro, caminhoArquivo);
        ExibirMensagemSucesso(cronometro.ElapsedMilliseconds, caminhoArquivo);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    finally
    {
        cronometro.Stop();
    }
}

Solução com Múltiplas Planilhas

Para contornar a restrição do formato .xls, a estratégia consiste em distribuir os dados entre múltiplas planilhas. Cada sheet recebe no máximo 65.000 linhas de dados (mantendo margem de segurança), e ao atingir esse limite, uma nova planilha é criada automaticamente.

public void GerarRelatorioExcel(DataTable dados, string nomeArquivo)
{
    var dialogo = new SaveFileDialog
    {
        Title = "Salvar relatório",
        Filter = "Excel (*.xlsx)|*.xlsx|Excel 97-2003 (*.xls)|*.xls",
        FilterIndex = 1,
        AddExtension = true,
        RestoreDirectory = true,
        FileName = nomeArquivo
    };

    if (dialogo.ShowDialog() != DialogResult.OK)
        return;

    var caminhoArquivo = dialogo.FileName;
    var totalRegistros = dados.Rows.Count;
    var registrosProcessados = 0;

    var extensao = Path.GetExtension(caminhoArquivo).ToLower();
    IWorkbook livro;

    if (extensao == ".xlsx")
        livro = new XSSFWorkbook();
    else if (extensao == ".xls")
        livro = new HSSFWorkbook();
    else
        return;

    var cronometro = Stopwatch.StartNew();

    try
    {
        if (extensao == ".xlsx")
        {
            // XLSX não possui limitação significativa de linhas
            EscreverDadosEmPlanilha(livro, nomeArquivo, dados, 0, totalRegistros,
                ref registrosProcessados, totalRegistros);
        }
        else
        {
            // XLS requer divisão em múltiplas planilhas
            const int capacidadePorSheet = 65000;
            var quantidadeSheets = (int)Math.Ceiling((double)totalRegistros / capacidadePorSheet);

            for (var folha = 0; folha < quantidadeSheets; folha++)
            {
                var inicio = folha * capacidadePorSheet;
                var quantidade = Math.Min(capacidadePorSheet, totalRegistros - inicio);
                var sufixo = $"_{folha + 1}";
                var nomePlanilha = string.IsNullOrEmpty(nomeArquivo)
                    ? "Dados" + sufixo
                    : nomeArquivo + sufixo;

                EscreverDadosEmPlanilha(livro, nomePlanilha, dados, inicio, quantidade,
                    ref registrosProcessados, totalRegistros);
            }
        }

        Application.DoEvents();

        using (var memoria = new MemoryStream())
        {
            livro.Write(memoria);
            File.WriteAllBytes(caminhoArquivo, memoria.ToArray());
        }

        ExibirMensagemSucesso(cronometro.ElapsedMilliseconds, caminhoArquivo);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    finally
    {
        cronometro.Stop();
    }
}

private void EscreverDadosEmPlanilha(IWorkbook livro, string nomePlanilha,
    DataTable dados, int inicio, int quantidade, ref int processados, int total)
{
    var planilha = livro.CreateSheet(nomePlanilha);

    // Cabeçalho
    var cabecalho = planilha.CreateRow(0);
    for (var col = 0; col < dados.Columns.Count; col++)
    {
        cabecalho.CreateCell(col).SetCellValue(dados.Columns[col].ColumnName);
    }

    // Registros
    var numeroLinhas = dados.Columns.Count;
    var indiceLinha = 1;

    for (var idx = inicio; idx < inicio + quantidade; idx++)
    {
        var linha = planilha.CreateRow(indiceLinha++);
        for (var col = 0; col < numeroLinhas; col++)
        {
            linha.CreateCell(col).SetCellValue(dados.Rows[idx][col].ToString());
        }

        processados++;
        var progresso = (int)(100 * processados / total);
        AtualizarBarraProgresso(total, progresso);
        Application.DoEvents();
    }
}

Pontos Importantes da Impelmentação

• Capacidade por sheet: Utiliza-se 65.000 como limite seguro, deixando margem para o cabeçalho e eventuais variações.
• Nomenclatura das planilhas: Cada folha recebe um sufixo numérico para facilitar a navegação.
• Extração do método: A lógica de escrita foi encapsulada em EscreverDadosEmPlanilha, eliminando duplicação de código entre os formatos.
• Formato XLSX: O formato .xlsx (baseado em XML) suporta até 1.048.576 linhas, não necessitando da divisão em múltiplas planilhas.

Recomendação

Sempre que possível, opte pelo formato .xlsx para exportações com grande volume de dados. O formato .xls deve ser utilizado apenas quando houver requisito explícito de compatibilidade com versões legadas do Excel.