0

I need to export SQL Server result to excel file (at least a million rows and minimum 50 columns)

constraints:

  1. We can't use Inter-op
  2. Data should be in only one-sheet
  3. The process should not use more than 5GB ram (Time consumption up to 45 min)
  4. Excel file processing library should be free
  5. We don't want to create a CSV file

Below are the methods I have already tried:

  1. OpenXML (Out of memory exception and if try to write in a small amount of data using a lot of Ram)
  2. ClosedXML (It is using too much of ram)
  3. EPPlus (It is using too much of ram)

How can I do this?

Improve this question
2
  • Why don't you install excel, if it solves your problem? Commented Dec 17, 2019 at 15:26
  • @JamesZ The above application may get installed on many servers, so we don't want to keep any dependency, otherwise, we have to install excel to every server. Hope you understand. Commented Dec 17, 2019 at 16:16

2 Answers 2

Reset to default
1

You can Try this

for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++) { for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++) { data = ds.Tables[0].Rows[i].ItemArray[j].ToString(); xlWorkSheet.Cells[i + 1, j + 1] = data; } }

by using Datasets

A reference to related article.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for the input. I think it is using Microsoft.Office.Interop.Excel. We can't use this in the server as it requires excel to be installed in the system.
0

Considering the following link Excel specifications, the total number of rows you can have in a sheet is 1,048,576. Making it impossible to load 10 millions rows in a single sheet.

With that in mind you do have the following option, export the data in CSV (using a library like CsvHelper), and create a Excel sheet having a connection to that file (you can check how to do it here TechNet Article)

Improve this answer

2 Comments

Thanks for the input.If we consider the limit we can still go up to 1 Million. and CSV is not an option. We can not ask the client to use CSV.
@Dhruv The ideia of the CSV is only to store the data (as it is a lot faster to dump data to the csv), and use the Excel only to show/explore the data as the TechNet Article shows. Either way, the best option would be to open a OData API or connect to a data source and use the excel only to explore the data (microsoft.com/en-us/microsoft-365/blog/2016/06/23/…)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.