DataTable to csv file

I have created a web page using bootstrap/thymeleaf where I show some data tables using Ajax. And I have the buttons to export to CSV, but what I need is to generate a temporary file from this datatable and get the path to my controller when the datatable is already loaded with the data using a button.

So I need a way to do that creating a new function, but I don’t really know how to do it. May be without call again my sql repository because tables are very large.

JavaScript Code

function mostrarMaticulaEstudiant(data) {

    var url = "/9avaldoval/administracio/cercarMaticulaEstudiant?idEdicio=" + data;
    dataTableMatriculaEstudiant(url);
}

function dataTableMatriculaEstudiant(url) {

    var columns;
    var columnsConfig;
    var columnsFiltres;

    columns = crearColumnesTaulaMatriculaEstudiant();
    //columnsConfig = configurarColumnaBotonsEstudis(columns.length);
    var sorting = [[0, "asc"]];

    var htmlTable = crearTaulaHtmlMatriculaEstudiant(14);

    $('#divTaula').html(htmlTable);


    var table = $('#taulaMatriculaEstudiant').DataTable({
        scrollX: true,
        processing: false,
        ajaxSource: url,
        fnServerData: function(sSource, aoData, fnCallback) {
            aoData = $("#formAdmBlue").serializeArray();
            $.ajax({
                "dataType": 'json',
                "type": "POST",
                "url": sSource,
                "data": aoData,
                "success": fnCallback
            });
        },

        columns: columns,
        columnDefs: columnsConfig,
        sorting: sorting,
        language: {
            "lengthMenu": "Mostra _MENU_ files per pàgina",
            "search": "Filtre: ",
            "zeroRecords": "Sense resultats",
            "info": "Pàgina _PAGE_ de _PAGES_",
            "infoEmpty": "Files no trobades a la cerca",
            "infoFiltered": "(filtrat de _MAX_ files totals)",
            "sProcessing": "<img src='/9avaldoval/img/loading_gear.gif' width='48px' height='48px'>",
            "paginate": {
                "next": "<span class='oi oi-chevron-right'></span>",
                "previous": "<span class='oi oi-chevron-left'></span>"
            }
        },
        dom: "<'row'<'col-sm-12 col-md-4'B><'col-sm-12 col-md-4'l><'col-sm-12 col-md-4'f>>" +
            "<'row'<'col-sm-12'tr>>" +
            "<'row'<'col-sm-12 col-md-5'i><'col-sm-12 col-md-7'p>>",
        buttons: ['copy', 'excel', 'pdf'],
        error: function() {
            alert("No s'ha pogut obtenir la informació");
        }
    });


}


function crearTaulaHtmlMatriculaEstudiant(numColumnes) {

    var htmlTableOpen = '<table style="width: 100%;" cellpadding="0" cellspacing="0" border="0" class="table table-hover" id="taulaMatriculaEstudiant"><thead>';

    var htmlTableHeaderRow = '<tr>';
    var htmlTh = '<th></th>';
    for (var i = 0; i < numColumnes; i++) {
        htmlTableHeaderRow = htmlTableHeaderRow + htmlTh;
    }
    htmlTableHeaderRow = htmlTableHeaderRow + '</tr>';
    var htmlTableClose = '</thead></table>';
    var htmlTable = htmlTableOpen + htmlTableHeaderRow + htmlTableClose;
    return htmlTable;

}


function crearColumnesTaulaMatriculaEstudiant() {


    var columns = [];
    columns.push({ "sTitle": "IdAssigEdicio", "mData": "IdAssigEdicio", "sWidth": "10%", "defaultContent": "" });
    columns.push({ "sTitle": "IdEdicio", "mData": "IdEdicio", "sWidth": "10%", "defaultContent": "" });
    columns.push({ "sTitle": "IdCodiassigPare", "mData": "IdCodiassigPare", "sWidth": "10%", "className": "text-center", "defaultContent": "" });
    columns.push({ "sTitle": "IdUsuariUPF", "mData": "IdUsuariUPF", "sWidth": "10%", "defaultContent": "" });
    columns.push({ "sTitle": "IdCodiAssigFill", "mData": "IdCodiAssigFill", "sWidth": "10%", "defaultContent": "" });
    columns.push({ "sTitle": "IdAssigFill", "mData": "IdAssigFill", "sWidth": "10%", "defaultContent": "" });
    columns.push({ "sTitle": "EsRepetidor", "mData": "EsRepetidor", "sWidth": "10%", "defaultContent": "" });
    columns.push({ "sTitle": "CodiCentreEstudiant", "mData": "CodiCentreEstudiant", "sWidth": "10%", "defaultContent": "" });
    columns.push({ "sTitle": "CentreEstudiant", "mData": "CentreEstudiant", "sWidth": "10%", "defaultContent": "" });
    columns.push({ "sTitle": "EstudiEstudiant", "mData": "EstudiEstudiant", "sWidth": "10%", "defaultContent": "" });
    columns.push({ "sTitle": "CodiEstudiEstudiantL", "mData": "CodiEstudiEstudiantL", "sWidth": "10%", "defaultContent": "" });
    columns.push({ "sTitle": "DescEstudi_es", "mData": "DescEstudi_es", "sWidth": "10%", "defaultContent": "" });
    columns.push({ "sTitle": "DescEstudi_en", "mData": "DescEstudi_en", "sWidth": "10%", "defaultContent": "" });
    columns.push({ "sTitle": "DescEstudi_ca", "mData": "DescEstudi_ca", "sWidth": "10%", "defaultContent": "" });

    return columns;
}

Controller

@RequestMapping(value = "cercarMaticulaEstudiant", params = "idEdicio")
    public @ResponseBody byte[] cercarMaticulaEstudiant(@RequestParam("idEdicio") String idEdicio, Model model)
            throws UnsupportedEncodingException, ParseException {

        log.debug("***** cercarEdicions AJAX ****");

        byte[] dataTableSource = null;
        List<BlueMatriculaEstudiant> llistaMatriculaEstudiants;

        llistaMatriculaEstudiants = blueMatriculaEstudiantService
                .cercarBlueMatriculaEstudiant(Integer.parseInt(idEdicio));

        dataTableSource = operacionsService.blueMatriculaEstudiantsToJson(llistaMatriculaEstudiants);

        return dataTableSource;
    }

HTML/Bootstrap

<div class="col-lg-12" 
                        style="margin-bottom: 40px;">
                        <br>
                        <div class="card">
                            <h5 class="card-header" th:id="llistat"
                                data-th-text="#{administracio.blue.llistat}"></h5>
                            <div class="card-body">
                                <div class="row"
                                    style="padding-left: 10px; padding-right: 10px;">
                                    <div class="col-md-12">
                                        <div id="divTaula"></div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

Answer

I think the easiest way would be to create the csv files using JS on the client-side.

First of all, you will need to get the data from your DataTable, their documentation shows it could be done like this:

let table = $('#taulaMatriculaEstudiant').DataTable()
 
let data = table
    .rows()
    .data()

Now, csv format is basically a text format with separators. So you need to iterate over the rows in data and merge cells to separate text rows, something like this:

let text = '';
data.map( row => text += row.join( ';' ) + 'n' ) // replace ';' with your preferred CSV separator

Then you only need to create a document and download it. To do that I would suggest the usage of filesaver.js:

let blob = new Blob( [text], {type: "text/csv;charset=utf-8"} )
saveAs( blob, 'taulaMatriculaEstudiant.csv' ) // any name with CSV extension

Please note that I have never worked with DataTables, so I might be mistaken about the data structure in which it returns the data from table after .rows().data() (I assumed it returns an array of arrays that represent the cells in the rows). The general approach should be correct.

Edit

However I would not recommend sending data from the browser to the server – just create the correct file on the server side insted. E.g. like this:

@Controller // note it's not @RestController
public class CsvController {
    private CommonDataService dataService; // service with your table data logic. Don't forget to add @Service to it

    @GetMapping( "/csv/example" )
    public void exampleCsv( HttpServletResponse response ) throws IOException {
        byte[] processedFile = dataService.getTaulaMatriculaEstudiant(); // prepare your data the same way as your main data controller and create the csv table the way you like it

        ContentDisposition disposition = ContentDisposition.builder( "attachment" )
            .filename( "taulaMatriculaEstudiant.csv", StandardCharsets.UTF_8 )
            .build();
        response.setContentType( "text/csv" );
        response.setHeader( "Cache-Control", "no-cache" );
        response.setHeader( "Expires", "0" );
        response.setHeader( "Pragma", "no-cache" );

        IOUtils.copy( new ByteArrayInputStream( processedFile ), response.getOutputStream() ); // IOUtils from Apache Commons-IO
    } 

    @Autowired
    public void setDataService( CommonDataService dataService ) {
        this.dataService = dataService;
    }
}