How to add new cell at beginning of each row of the table.

shree1705

Hello, I want to know how to add cell <td> at beginning of each row<tr> of the table and populate cell value by title of <tr>.

I used below code to export table data into excel sheet and populated <th> value as "Title" in function getHeaderNames(table).

function getHeaderNames(table) {
    // Gets header names.
    //params:
    //  table: table ID.
    //Returns:
    //  Array of column header names.
 
    var header = $(table).DataTable().columns().header().toArray();
 
    var names = ["Title"];
    header.forEach(function(th) {
    names.push($(th).html());
    });
    return names;
}
 
function buildCols(data) {
    // Builds cols XML.
    //To do: deifne widths for each column.
    //Params:
    //  data: row data.
    //Returns:
    //  String of XML formatted column widths.
 
    var cols = '<cols>';
 
    for (i=0; i<data.length; i++) {
        colNum = i + 1;
        cols += '<col min="' + colNum + '" max="' + colNum + '" width="20" customWidth="1"/>';
    }
 
    cols += '</cols>';
 
    return cols;
}
 
function buildRow(data, rowNum, styleNum) {
    // Builds row XML.
    //Params:
    //  data: Row data.
    //  rowNum: Excel row number.
    //  styleNum: style number or empty string for no style.
    //Returns:
    //  String of XML formatted row.
 
    var style = styleNum ? ' s="' + styleNum + '"' : '';
 
    var row = '<row r="' + rowNum + '">';
 
    for (i=0; i<data.length; i++) {
        colNum = (i + 10).toString(36).toUpperCase();  // Convert to alpha
 
        var cr = colNum + rowNum;
 
        row += '<c t="inlineStr" r="' + cr + '"' + style + '>' +
        '<is>' +
        '<t>' + data[i] + '</t>' +
        '</is>' +
        '</c>';
    }
 
    row += '</row>';
 
    return row;
}
 
function getTableData(table, title) {
    // Processes Datatable row data to build sheet.
    //Params:
    //  table: table ID.
    //  title: Title displayed at top of SS or empty str for no title.
    //Returns:
    //  String of XML formatted worksheet.
 
    var header = getHeaderNames(table);
    var table = $(table).DataTable();
    var rowNum = 1;
    var mergeCells = '';
    var ws = '';
 
    ws += buildCols(header);
    ws += '<sheetData>';
 
    if (title.length > 0) {
        ws += buildRow([title], rowNum, 51);
        rowNum++;
 
        mergeCol = ((header.length - 1) + 10).toString(36).toUpperCase();
 
        mergeCells = '<mergeCells count="1">'+
        '<mergeCell ref="A1:' + mergeCol + '1"/>' +
        '</mergeCells>';
    }
 
    ws += buildRow(header, rowNum, 2);
    rowNum++;
 
    // Loop through each row to append to sheet.   
    table.rows().every( function ( rowIdx, tableLoop, rowLoop ) {
        var data = this.data();
 
        // If data is object based then it needs to be converted
        // to an array before sending to buildRow()
        ws += buildRow(data, rowNum, '');
 
        rowNum++;
    } );
 
    ws += '</sheetData>' + mergeCells;
 
    return ws;
 
}
 
function setSheetName(xlsx, name) {
    // Changes tab title for sheet.
    //Params:
    //  xlsx: xlxs worksheet object.
    //  name: name for sheet.
 
    if (name.length > 0) {
        var source = xlsx.xl['workbook.xml'].getElementsByTagName('sheet')[0];
        source.setAttribute('name', name);
    }
}
 
function addSheet(xlsx, table, title, name, sheetId) {
    //Clones sheet from Sheet1 to build new sheet.
    //Params:
    //  xlsx: xlsx object.
    //  table: table ID.
    //  title: Title for top row or blank if no title.
    //  name: Name of new sheet.
    //  sheetId: string containing sheetId for new sheet.
    //Returns:
    //  Updated sheet object.
 
    //Add sheet2 to [Content_Types].xml => <Types>
    //============================================
    var source = xlsx['[Content_Types].xml'].getElementsByTagName('Override')[1];
    var clone = source.cloneNode(true);
    clone.setAttribute('PartName','/xl/worksheets/sheet2.xml');
    xlsx['[Content_Types].xml'].getElementsByTagName('Types')[0].appendChild(clone);
 
    //Add sheet relationship to xl/_rels/workbook.xml.rels => Relationships
    //=====================================================================
    var source = xlsx.xl._rels['workbook.xml.rels'].getElementsByTagName('Relationship')[0];
    var clone = source.cloneNode(true);
    clone.setAttribute('Id','rId3');
    clone.setAttribute('Target','worksheets/sheet2.xml');
    xlsx.xl._rels['workbook.xml.rels'].getElementsByTagName('Relationships')[0].appendChild(clone);
 
    //Add second sheet to xl/workbook.xml => <workbook><sheets>
    //=========================================================
    var source = xlsx.xl['workbook.xml'].getElementsByTagName('sheet')[0];
    var clone = source.cloneNode(true);
    clone.setAttribute('name', name);
    clone.setAttribute('sheetId', sheetId);
    clone.setAttribute('r:id','rId3');
    xlsx.xl['workbook.xml'].getElementsByTagName('sheets')[0].appendChild(clone);
 
    //Add sheet2.xml to xl/worksheets
    //===============================
    var newSheet = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'+
    '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" mc:Ignorable="x14ac">'+
    getTableData(table, title) +
 
    '</worksheet>';
 
    xlsx.xl.worksheets['sheet2.xml'] = $.parseXML(newSheet);
 
}  

now my table looks as below (rows and cells are not hardcoded but is a resultSet)

<table id="DataTables_Table_1">
<thead>
<tr>
<th>title</th>
<th>other</th>
<th>other1</th>
.
.
<th>other n</th>
</thead>
<tbody>
<tr>
<td>other.cell1<td>
<td>other1.cell2</td>
.
.
<td>other n.celln</td>
</tr>
<tr>
<td>other.cell1<td>
<td>other1.cell2</td>
.
.
<td>other n.celln</td>
</tr>
<tr>
<td>other.cell1<td>
<td>other1.cell2</td>
.
.
<td>other n.celln</td>
</tr> </tbody>
//...(n <tr>) 
</table>

But output I need in <tbody> section is as below

<table id="DataTables_Table_1">
<thead>
<tr>
<th>title</th>
<th>other</th>
<th>other1</th>
.
.
<th>other n</th>
</thead>
<tbody>
<tr>
<td>title.cell1</td>
<td>other.cell2<td>
<td>other1.cell3</td>
.
.
<td>other n.celln</td>
</tr>
<tr>
<td>title.cell1</td>
<td>other.cell2<td>
<td>other1.cell3</td>
.
.
<td>other n.celln</td>
</tr>
<tr>
<td>title.cell1</td>
<td>other.cell2<td>
<td>other1.cell3</td>
.
.
<td>other n.celln</td>
</tr> </tbody>
//...(n <tr>)</table>

Please let me know how I can do add new cells at beginning (without removing existing value of tables) for all rows in each sheet of excel. I used .append() method and it doesn't work, thanks!

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

colin

You can create any content for a column, either with columns.render or columns.defaultContent - see example here.

If that doesn't help, we're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin