javascript - Export html table with rowspan and colspan to csv or xls - Stack Overflow

admin管理员组

文章数量:1327667

I have a grid which looks as in the image below.

Here we are using rowspans and colspans to make it span to some number of rows and columns.

I want to export the same content to either excel or csv but it should retain the format of rows and columns being spanned.

Is there an easier way I can do this using Javascript and HTML?

I have a grid which looks as in the image below.

Here we are using rowspans and colspans to make it span to some number of rows and columns.

I want to export the same content to either excel or csv but it should retain the format of rows and columns being spanned.

Is there an easier way I can do this using Javascript and HTML?

• javascript
• html
• css

Share Improve this question Follow asked Apr 17, 2017 at 9:59 MuktaMukta 7911 gold badge11 silver badge77 bronze badges 1

• Possible Duplicate – Farooq Ahmed Khan Commented Apr 17, 2017 at 10:03

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 2

Short answer: Sorry, No. There is no easy way.

Long answer: While there are easy ways to convert structured html tables to xls or csv (see for example: HTML Table to CSV/Excel Converter), but the irregular and multi-level header in your table along with the multiple instances of colspan and rowspan, subvert the logical relationships and predictability in the table.

Tip/Suggestion: With a few alterations to the structure of your table, it can easily be:

1. more machine readable, and thus easier to export and manipulate;
2. more human-readable, including more accessible to users using assistive technology.

Specifically:

• Use a list, rather than table cells for listing the cities
• Nix any use of colspan and rowspan on the other cells
• Delete the multi-level table header. The top row housing "Place desc" colspan is definitely redundant, and ultimately unnecessary.

i had the same issue, solved by me self with the following code:

full code to download html table to csv file.

the code is structured only if you can base the col numbers in a specific row. in my situation it's the last in .

function download_table_as_csv(separator = ',') {
        // Change here to your table with jquery selector, by table_id or table class. this code uses CLASS 'table'.
        var rows = $('.table tr');
        // Construct csv
        var csv = [];
        var lastrow = []; var repeatrowval = [];
        var tabledata = [];
// the code is structured only if you can base the col numbers in a spesific row. in my situation it's the last tr in thead.         
var cols = $('.table > thead > tr:last > th').length; $('.table > thead > tr:last > th').attr('rowSpan','1');
        for (var i = 0; i < cols; i ++) {repeatrowval.push(1); lastrow.push('none')}
        for (var i = 0; i < rows.length -1; i++) { //loop every row
            console.log("row: " + i)
            var row = []; var col = rows[i].querySelectorAll('td, th');
            var col_len = 0;
            for (var j = 0; j < cols; j++) {
                var a=0;
                console.log(repeatrowval[j]);
                if (repeatrowval[j] > 1) {
                    data = lastrow[j]; repeatrowval[j] = repeatrowval[j] - 1;
                    console.log("row: " + i + " reapet_col: " + j + " = " + data);
                    row.push('"' + data + '"');
                } else {
                    if(col[col_len] === undefined) break;
                    var colspan = col[col_len].colSpan ?? 1 ;
                    console.log("col: " + j + ", colspan = " + colspan + ", repeatrowval: " + repeatrowval[j])
                    for (var r = 0; r < colspan; r++) { 
                        
                        var rowspan = col[col_len].rowSpan ?? 1 ;
                        //console.log('rowspan: ' +rowspan)
                        if (rowspan == 1) {
                            // Clean innertext to remove multiple spaces and jumpline (break csv)
                            var data = col[col_len].innerText.replace(/(\r\n|\n|\r)/gm, '').replace(/(\s\s)/gm, ' ')
                            // Escape double-quote with double-double-quote (see https://stackoverflow./questions/17808511/properly-escape-a-double-quote-in-csv)
                            data = data.replace(/"/g, '""');
                            repeatrowval[j] = 1;
                            lastrow[j] = data
                            //}
                        } else {
                            // Clean innertext to remove multiple spaces and jumpline (break csv)
                            var data = col[col_len].innerText.replace(/(\r\n|\n|\r)/gm, '').replace(/(\s\s)/gm, ' ')
                            // Escape double-quote with double-double-quote (see https://stackoverflow./questions/17808511/properly-escape-a-double-quote-in-csv)
                            data = data.replace(/"/g, '""');
                            lastrow[j] = data; repeatrowval[j] = rowspan;
                        }
                        // Push escaped string
                        console.log("row: " + i + " col: " + j + " = " + data);
                        row.push('"' + data + '"');
                        if(colspan > 1) {a++};
                    }
                    col_len++
                }
                if(a>0){j=+a};
            }
            csv.push(row.join(separator));
            tabledata.push(row);
        }
        var csv_string = csv.join('\n');
        // Download it
        var filename = 'export_Report_' + new Date().toLocaleDateString() + '.csv';
        var link = document.createElement('a');
        link.style.display = 'none';
        link.setAttribute('target', '_blank');
        link.setAttribute('href', 'data:text/csv;charset=utf-8,' + encodeURIComponent(csv_string));
        link.setAttribute('download', filename);
        document.body.appendChild(link);
        link.click();
        document.body.removeChild(link);
    }       

<script src="https://cdnjs.cloudflare./ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<button onclick="download_table_as_csv();">Download Table as csv file</button>
<table class="table"><thead><tr><th colspan="3" rowspan="2"></th><th colspan="7">Attendance</th></tr><tr><th colspan="7">Nov</th></tr><tr><th>Class</th><th rowspan="1" colspan="2">Student Name</th><th colspan="1" rowspan="2">17</th><th colspan="1" rowspan="2" >18</th><th colspan="1" rowspan="2" >19</th><th colspan="1" rowspan="2" >23</th><th colspan="1" rowspan="2" >24</th><th colspan="1" rowspan="2" >25</th><th colspan="1" rowspan="2" >26</th></tr></thead><tbody><tr><th rowspan="25" >2-A</th><th rowspan="1" colspan="2" >Alan, Ahoshe</th><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td></tr><tr><th rowspan="1" colspan="2" >Ael, Teddy</th><td>✅</td><td >✅</td><td >✅</td><td >✅</td><td >✅</td><td >✅</td><td >✅</td></tr><tr><th rowspan="1" colspan="2" >Bel, Kham</th><td>✅</td><td >✅</td><td >✅</td><td >✅</td><td >✅</td><td >✅</td><td >✅</td></tr><tr><th rowspan="1" colspan="2" >Fohr, Nlili</th><td>✅</td><td >❌</td><td >✅</td><td >❌</td><td >✅</td><td >❌</td><td >✅</td></tr><tr><th rowspan="1" colspan="2" >Foman, Mdel</th><td>✅</td><td >✅</td><td >✅</td><td >✅</td><td >✅</td><td >✅</td><td >✅</td></tr><tr><th rowspan="1" colspan="2" >Gorger, Cim</th><td>✅</td><td >✅</td><td >✅</td><td >✅</td><td >❌</td><td >✅</td><td >✅</td></tr><tr><th rowspan="1" colspan="2" >Goes, Joph</th><td>✅</td><td >✅</td><td >✅</td><td >✅</td><td >✅</td><td >✅</td><td >❌</td></tr><tr><th rowspan="1" colspan="2" >Hich, Cheel</th><td>✅</td><td >✅</td><td >✅</td><td >✅</td><td >✅</td><td >✅</td><td >✅</td></tr><tr><th rowspan="1" colspan="2" >Kain, Nali</th><td>✅</td><td >✅</td><td >✅</td><td >✅</td><td >✅</td><td >✅</td><td >✅</td></tr><tr><th rowspan="1" colspan="2" >Klon, Levpi</th><td>✅</td><td >✅</td><td>✅</td><td >✅</td><td >✅</td><td >✅</td><td >✅</td></tr><tr><th rowspan="1" colspan="2" >Le, Shlo</th><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td></tr><tr><th rowspan="1" colspan="2" >Ahi, Hufd</th><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td></tr><tr><th rowspan="1" colspan="2" >Leitz, Shru</th><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td></tr><tr><th rowspan="1" colspan="2" >Rerg, Aeh</th><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>❌</td><td>❌</td><td>✅</td></tr><tr><th rowspan="1" colspan="2" >Roeld, Did</th><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td></tr><tr><th rowspan="1" colspan="2" >Rothb, An</th><td>❌</td><td>❌</td><td>❌</td><td>✅</td><td>❌</td><td>❌</td><td>❌</td></tr><tr><th rowspan="1" colspan="2" >Rubein, Moha</th><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td></tr><tr><th rowspan="1" colspan="2" >Shel, Khak</th><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td></tr><tr><th rowspan="1" colspan="2" >Sofer, Shia</th><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td></tr><tr><th rowspan="1" colspan="2" >Strar, Jacob</th><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td></tr><tr><th rowspan="1" colspan="2" >Teim, Jalb</th><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td></tr><tr><th rowspan="1" colspan="2" >Wes, Pel</th><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td></tr><tr><th rowspan="1" colspan="2" >Wes, Shan</th><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td></tr><tr><th rowspan="1" colspan="2" >Wemer, Am</th><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>❌</td></tr><tr><th rowspan="1" colspan="2" >Werer, Son</th><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td></tr><tr><th rowspan="19" >2-B</th><th rowspan="1" colspan="2" >Ekin, Puda</th><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td></tr><tr><th rowspan="1" colspan="2" >Fon, Shpoem</th><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>❌</td><td>❌</td></tr><tr><th rowspan="1" colspan="2" >Fron, Aor</th><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td><td>✅</td></tr></tbody></table>

本文标签： javascriptExport html table with rowspan and colspan to csv or xlsStack Overflow