export large data to Excel file in C# asp.net Core 대용량 데이터 엑셀 추출, 메모리 사용량

최대 약 4만개 정도의 row를 엑셀로 추출해야하는 일이 생겼다

지금까지는 엑셀 형태에 값을 채워넣는 개발만 해서 비슷하게 구현했는데

프로세스 메모리가 2GB까지나 올라가며 여러명이 요청을 보냈을 경우 서버가 터졌다,,

로컬에서만 테스트 하다보니 시간이 오래걸리는건 그렇다 쳐도 메모리는 고려하지 못했다

 

C# Asp.Net Core MVC에서 대용량 데이터를 엑셀로 export하는 방법

 

• HTML 방식으로 엑셀 다운로드: 기존 코드처럼 데이터를 HTML로 변환하여 엑셀 파일을 다운로드하는 방식으로 바꾸는 것을 고려할 수 있습니다. 이 방식은 메모리 사용량을 줄이고, 사용자에게 즉각적인 응답을 제공하는 데 유리합니다.
• 스트리밍 방식 사용: MemoryStream 대신에 파일을 바로 클라이언트로 스트리밍하는 방식을 사용하는 것도 방법입니다. 이를 통해 메모리에 모든 데이터를 올리지 않고 처리할 수 있습니다.
• 데이터 양에 따라 처리 방식 조정: 데이터 양이 많다면, 엑셀 파일 생성 작업을 백그라운드 작업으로 처리하고, 완료 후에 다운로드 링크를 제공하는 방식도 고려할 수 있습니다. 이 방법은 사용자 경험을 향상시키는 데 유리할 수 있습니다.

 

보통은 스트리밍 방식을 사용한다고 한다. 많이 사용하는 라이브러리는 EPPlus.

하지만 유로 라이브러리라서 ` ClosedXML`을 사용했다.

[HttpPost("search/excel")]
public async Task<IActionResult> ExportToExcel(SearchCRM data)
{
    // 스트리밍을 위한 MemoryStream 생성
    var stream = new MemoryStream();
    using (var workbook = new XLWorkbook())
    {
        var worksheet = workbook.Worksheets.Add("Data");
        worksheet.Cell(1, 1).InsertTable(await _service.GetDataAsync(data));
        workbook.SaveAs(stream);
        stream.Position = 0;
        return new FileStreamResult(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
        {
            FileDownloadName = $"DataExport_{DateTime.Now:yyyyMMddHHmmss}.xlsx"
        };
    }
}

하지만 응답 시간이나 메모리 사용량은 거의 비슷하게 높았다. 

ClosedXML이 EPPlus보다 메모리 사용량이 더 크다고 한다..

데이터가 많다면 chunks을 사용해서 데이터를 작게 쪼개는 방법이 있기도 했다

응답이 완료되고 브라우저에서 다운로드 되는 방식을 위해 동기로 chunks을 사용하려 했으나

마찬가지로 시간과 메모리 사용량이 높았다..

문제점

ASP.NET Core에서는 파일이 메모리에 모두 생성된 후에야 응답이 클라이언트에게 전송됩니다. MemoryStream을 사용해 데이터를 메모리에 저장하고, 그 후에야 클라이언트로 보내기 때문에 메모리 사용량이 더 높아질 수 있습니다.

ClosedXML 라이브러리를 사용해 Excel 파일을 생성합니다. ClosedXML은 모든 데이터를 메모리에 로드하고, 엑셀 파일 전체를 메모리에 저장한 후에야 클라이언트로 전송할 수 있습니다. 이로 인해 대용량 데이터를 처리할 때 메모리 사용량이 높아집니다.

---

추가적으로, 파일 생성 후 링크를 통해 다운로드 하는 방법이 있다.

이 방법은 API를 두번 호출해야해서 번거롭다는 단점이 있다.. URL을 만들어서 바로 뿌려줘도 메모리 사용은 비슷함..

그리고 파일을 서버에 저장해야하기 때문에 저장소 문제도 있을 것 같아서 패스했다

public async Task<IActionResult> ExportToExcel(SearchCRM data)
{
    var filePath = await _fileService.GenerateExcelFileAsync(data);
    var downloadLink = Url.Action("DownloadFile", new { filePath });
    return Ok(new { downloadLink });
}

public IActionResult DownloadFile(string filePath)
{
    var fileBytes = System.IO.File.ReadAllBytes(filePath);
    return File(fileBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", Path.GetFileName(filePath));
}

---

결국 Stream을 사용하지 않고 최종 선택한 방법은 HTML 방식

Controller API가 아닌 Asp.Net Web Forms를 사용하면 이 방식을 사용한다. (코드는 여기 참고)

HTML 변환의 경우 데이터가 너무 많으면 오히려 느려질 수도 있다고 했지만 다른 방법에 비해

메모리 사용량이 제일 많이 줄었고 응답 속도도 제일 빨랐다

 

아래는 최종 코드 입니다 참고용으로만 봐주세요 :)

데이터는 DataTable 형태로 불러왔고 데이터를 HTML로 변환하여 반환했다

헤더도 원하는 형태로 변경할 수 있고 대소분자를 꼭 구분해야한다!

[HttpPost("search/excel")]
public async Task<IActionResult> GenerateExcelHtml(SearchCRM data)
{
    try
    {
        var dataTable = await _service.GetDataAsync(data);
        ModifyColumnHeaders(dataTable);

        var html = GenerateHtmlTable(dataTable);
        var fileName = $"리스트_{DateTime.Now:yyyyMMddHHmmss}.xls"; // .xls 확장자로 설정

        // Convert HTML to byte array
        var bytes = System.Text.Encoding.UTF8.GetBytes(html);

        // Create FileContentResult
        var response = new FileContentResult(bytes, "application/vnd.ms-excel")
        {
            FileDownloadName = fileName
        };

        return response;
    }
    catch (Exception ex)
    {
        System.Diagnostics.Debug.WriteLine($"Error: {ex.Message}");
        return StatusCode(500, $"Internal server error: {ex.Message}");
    }
}

private string GenerateHtmlTable(DataTable dataTable)
{
    var sb = new StringBuilder();
    sb.Append("<table border='1'>");

    // Header Row
    sb.Append("<tr>");
    foreach (DataColumn column in dataTable.Columns)
    {
        sb.AppendFormat("<th>{0}</th>", column.ColumnName);
    }
    sb.Append("</tr>");

    // Data Rows
    foreach (DataRow row in dataTable.Rows)
    {
        sb.Append("<tr>");
        foreach (DataColumn column in dataTable.Columns)
        {
            sb.AppendFormat("<td>{0}</td>", row[column]);
        }
        sb.Append("</tr>");
    }

    sb.Append("</table>");
    return sb.ToString();
}
private void ModifyColumnHeaders(DataTable dataTable)
{
    var headerMapping = new Dictionary<string, string>
    {
        { "classify", "구분" },
        { "type", "유형" },
        ...
    };

    foreach (DataColumn column in dataTable.Columns)
    {
        if (headerMapping.ContainsKey(column.ColumnName))
        {
            column.ColumnName = headerMapping[column.ColumnName];
        }
    }
}

 

  public async Task<DataTable> GetDataAsync(SearchCRM search)
  {
      DataTable dataTable = new DataTable("dt");

      using (var connection = new SqlConnection(_connectionString))
      {
          await connection.OpenAsync();

          using (var command = new SqlCommand("dbo.Select_List", connection))
          {
              command.CommandType = CommandType.StoredProcedure;
              command.CommandTimeout = 2000;
              command.Parameters.AddWithValue("@classify",search.Classify);
              command.Parameters.AddWithValue("@type", search.Type );
              // Use SqlDataAdapter to fill the DataTable
              using (var adapter = new SqlDataAdapter(command))
              {
                  try
                  {
                      adapter.Fill(dataTable);
                  }
                  catch (Exception ex)
                  {
                      // Log the error and rethrow or handle as appropriate
                      System.Diagnostics.Debug.WriteLine($"Error: {ex.Message}");
                      throw; // Optionally rethrow if you want to handle it further up the call stack
                  }
              }
          }
      }
      return dataTable;
  }

 

*프론트(React)에서 응답은 fetch로 받아서 처리해줬다

const onExcel = async () => {
    const response = await fetch("/api/search/excel", {
        method: "POST",
        headers: {
            "Content-Type": "application/json",
            Authorization: `Bearer ${UserApi.GetToken()}`,
        },
        body: JSON.stringify(searchFormExcel),
    });

    if (response.ok) {
        const blob = await response.blob();
        const url = window.URL.createObjectURL(blob);
        const a = document.createElement("a");
        a.href = url;
        a.download = "리스트_" + moment().format("YYYYMMDDHHmmss") + ".xls"; // 파일명과 확장자 설정
        document.body.appendChild(a);
        a.click();
        a.remove();
        window.URL.revokeObjectURL(url);
    } else {
        console.error("Failed to generate file");
    }
};

 

데이터가 정말 많다면 페이지를 나누거나 EPPlus 라이브러리를 사용해도 좋을 것 같다

blob을 사용한다면 서버에 파일을 저장해두고 URL을 보낸다거나,, 

 

더 좋은 방법과 좋은 라이브러리가 있으면 알려주세요