What is the best way to generate xslx file on a website? Perhaps with millions of lines?

Make sure your tests are representative of the actual data. Excel handles simple number cells much more efficiently than simple text cells, especially when all text cells are unique. Thus, if your data is indeed 10 unique characters, be sure to use this as a test case. If in fact it will be mostly a number, make sure your tests reflect this fact.

For example. I built a simple test using SpreadsheetGear for .NET to create an Open XML (.xlsx) column workbook of 300,000 lines of 50. It took 13.62 seconds to create and save to disk with unique numbers on my nearly two year old overclocked processor QX6850 while creating and saving 300,000 rows by 50 columns. An xlsx worksheet with 10 characters of unique strings took 78 seconds - 6 times longer for text than for numbers. I'll paste the code below and you can run it with a free SpreadsheetGear trial that you can download here .

It is important to note that Open XML (.xlsx) is compressed, so if your data has a lot of redundancy, you will likely end up with smaller .xlsx files than .csv files. This can have a big performance impact if you are creating books on a web server for consumption over the network.

SpreadsheetGear, using the IWorkbook.SaveToStream method and most other third-party Excel compatible libraries, will allow you to directly save the response stream in an ASP.NET application, so you can avoid saving it to disk on the server.

Disclaimer: I own SpreadsheetGear LLC

Here's the test code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SpreadsheetGear;

namespace ConsoleApplication11
{
    class Program
    {
        static void Main(string[] args)
        {
            var timer = System.Diagnostics.Stopwatch.StartNew();
            int rows = 300000;
            int sheets = 1;
            var workbook = Factory.GetWorkbook();
            var sb = new System.Text.StringBuilder();
            int counter = 0;
            bool numeric = true;
            for (int sheet = 0; sheet < sheets; sheet++)
            {
                // Use the SpreadsheetGear Advanced API which is faster than the IRange API.
                var worksheet = (sheet == 0) ? workbook.Worksheets[0] : workbook.Worksheets.Add();
                var values = (SpreadsheetGear.Advanced.Cells.IValues)worksheet;
                for (int row = 0; row < rows; row++)
                {
                    for (int col = 0; col < 50; col++)
                    {
                        if (numeric)
                            values.SetNumber(row, col, ++counter);
                        else
                        {
                            sb.Length = 0;
                            // Make a 10 character unique string.
                            sb.Append(++counter);
                            System.Diagnostics.Debug.Assert(sb.Length <= 10);
                            // Make it 10 characters long.
                            while (sb.Length < 10)
                                sb.Append((char)('A' + (char)sb.Length));
                            values.SetText(row, col, sb);
                        }
                    }
                }
            }
            Console.WriteLine("Created {0} cells in {1} seconds.", counter, timer.Elapsed.TotalSeconds);
            workbook.SaveAs(@"C:\tmp\BigWorkbook.xlsx", FileFormat.OpenXMLWorkbook);
            Console.WriteLine("Created and saved {0} cells in {1} seconds.", counter, timer.Elapsed.TotalSeconds);
        }
    }
}

      

        
        
        
      

    

You might want to check out the NPOI library for reading and writing excel files at http://npoi.codeplex.com/ . As for saving to the server, which is an option, but remember that you will have to clean up the files after downloading them.

Take a look at the Simple OOXML project at Codeplex.

This might be what you are looking for.

PS. Excel is basically spreadsheet software, without having to replace the database. Are you sure you want to dump a million lines for the end user?

Excel cannot handle millions of lines, try creating a CSV output file instead, this can be read in Excel.

And it is not recommended to add a huge amount of data to excel at the request of the user He will need to wait until the file is loaded.