How do create and make user download an Excel file from variable data in arrays?

Question

I'm working on a project in .NET Core 1.1, and now I have to give the possibility to the user of downloading an Excel file which data is dependant on parameters chosen by the user, so the Excel should be created in the moment when the user clicks the "Export to Excel" button and downloaded.

I've been searching on the internet but I haven't gotten any clear answers to be honest. I guess I will have to use the Open XML SDK, but in order to create it in memory and such, I don't have enough knowledge.

To sum up, I have data in arrays, and I would like to be able, in the moment the user clicks a button, create the excel virtually with the data previously stored in arrays and then download it in the users browser.

It might be worth asking something more specific as it's not clear if you're struggling with creating and downloading the file in memory (see the answer [here](https://stackoverflow.com/questions/29887503/how-to-create-excel-file-using-openxml-without-creating-a-local-file/29950024#29950024)) or if you're struggling to create the contents of the file (there are lots of samples for that, e.g. [this one](https://stackoverflow.com/questions/44572018/using-open-xml-to-create-excel-file/44588074#44588074) so to help you we'd need a specific problem). — petelids, Mar 28 '19 at 09:45

Massimo Variolo · Accepted Answer · 2019-03-28T10:17:38.853

1

Do you also want to show your data before download? Maybe you can use DataTables:

DataTables is a plug-in for the jQuery Javascript library. It is a highly flexible tool, built upon the foundations of progressive enhancement, that adds all of these advanced features to any HTML table.

Pagination Previous, next and page navigation. Instant search Filter results by text search.

edited Mar 28 '19 at 10:17

answered Mar 28 '19 at 08:14

Massimo Variolo

4,669
6
38
64

the data is being through a graph, and the I give the possibility to get the raw data – Miguel Cordero Mar 28 '19 at 08:25
Here is exact link to export using Datatables -> [link](https://datatables.net/extensions/buttons/examples/initialisation/export.html) – Marcin - user2676388 Mar 28 '19 at 08:31
The OP asked how to generate Excel files, not how to display the data – Panagiotis Kanavos Mar 28 '19 at 10:19
@PanagiotisKanavos reading the question is not clear if he need/want to display the data. – Massimo Variolo Mar 28 '19 at 10:59
1

@MassimoVariolo the title says `create and make user download`. There's nothing about displaying data – Panagiotis Kanavos Mar 28 '19 at 11:41
1

I used datatables, it's great, I just hid the table and left the button from File Export, @MassimoVariolo – Miguel Cordero Apr 16 '19 at 16:45

score 0 · Answer 2 · answered Mar 28 '19 at 08:40

using NPOI https://github.com/tonyqus/npoi something in the line of this:

void safearrayAsExcel(object[,] rows,string filename){
    var workbook = new HSSFWorkbook();
    var sheet = workbook.CreateSheet("New Sheet");

    for(int i = 0; i < rows.length;i++)
    {
        var row sheet.CreateRow(i);
        for(int j = 0; j < row.length;j++)
            row .CreateCell(j).SetCellValue(rows[i,j]);
    }
    FileStream fileOut = new FileStream(fileName, FileMode.Create);
    workbook.Write(fileOut);
}

you can ofcourse use a memorystream instead of filestream and do whatever you want with the generated excel

use HSSFWorkbook for xls format. and XSSFWorkbook for xlsx format.

i dont completely know about compatibliy issues with .net core 1.1 and .net standard 2.0 but there should be a way go get it work

How do create and make user download an Excel file from variable data in arrays?

2 Answers2