Topic
  • 3 replies
  • Latest Post - ‏2012-10-26T17:43:03Z by SystemAdmin
SystemAdmin
SystemAdmin
7615 Posts

Pinned topic Using custom HTML and javascript how to alter excel

‏2012-09-25T11:41:40Z |
Hi All,

I have couple of requirements regarding excel export.

I am using custom HTML and javascript to excel coach data to excel. This example is given in wiki. I want first cell to be bold. Also all coumn headers to be bold. I want new excel to be protected that is read only. Also while exporting I dont want to export rows which have status rejected in coach grid. Could you please help me. I am pasting code here.

Export
<script>

//Modify the value of these variable for your coach
var tableIdsToExport = "Table1";

// a comma delimited list of the ids of the tables that you want to include in the excel export
// if you include a tableId that does not exist in your coach, you will recieve a client side error message

//You do not need to modify the following part of the script

var form = document.createElement('div');
form.innerHTML = '<form name="exportData" method="post" target="_new" action="data-redirect.jsp"><input type="hidden" name="contentType" value="text/csv"><input type="hidden" name="data" value=""><input type="hidden" name="fileName" value="filename=reportData.xls"></form>';
//Work around a bug in IE: http://support.microsoft.com/default.aspx/kb/927917
document.getElementsByTagName("H1")[0].appendChild(form);
//alert( document.getElementsByTagName("H1")[0].appendChild(form));
//document.body.appendChild(form);

function addExportData(csvTable) {
if (document.forms.exportData == null || document.forms.exportData.data == null) {
return;
}

var excelHeader = "Counterparty Check Report (CCR)";
document.forms.exportData.data.value = excelHeader+"\n\n"+csvTable;
}
function doSubmitExport() {
var tableArr = tableIdsToExport.split(",");
for (var i=0;i<tableArr.length;i++) {
addTableToCSV(tableArr[i]);
}
document.forms.submit();
}

function addTableToCSV(tableId) {
var table;

try {
table = document.getElementById(tableId);

var a = table.innerHTML;
//replace existing commas with semi-colons. Poor mans way of handling embedded commas in a csv file
a = a.replace(/,/g, ";");

//get rid of javascript blocks
a = a.replace(/<script(.|\n)*?<\/script>/gi, "");

//insert commas at the end of a table cell
a = a.replace(/<\/td>/g, ",");
a = a.replace(/<\/TD>/g, ",");
a = a.replace(/<\/th>/g, ",");
a = a.replace(/<\/TH>/g, ",");

//insert a newline tag at the end of every row. Need to do this before removing all tags
a = a.replace(/<\/tr>/g, "---newline---");
a = a.replace(/<\/TR>/g, "---newline---");

//remove html tags
a = a.replace(/<\/?^>+(>|$)/g, "");

//remove whitespace (regexs found via google)
a = a.replace(/\r/g, " ");
a = a.replace(/[^ A-Za-z0-9`~!@#\$%\^&\*\(\)-_=\+\\\|][\}\{'";:\?\/\.>,<]/g, "");
a = a.replace(/'/g, "");
a = a.replace(/ +/g, " ");
a = a.replace(/^\s/g, "");
a = a.replace(/\s$/g, "");

//put newlines in
a = a.replace(/---newline---/g, "\n");

//replace &nbsp which the coach designer inserts
a = a.replace(/\ /g, " ");
//a now holds a resonable csv that I can put in excel
addExportData(a);
return true;
} catch (e) {
alert("Table Export Error: " + e);
}
return true;
}
</script>

Regards,
Yasmin
Updated on 2012-10-26T17:43:03Z at 2012-10-26T17:43:03Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    7615 Posts

    Re: Using custom HTML and javascript how to alter excel

    ‏2012-09-25T13:09:54Z  
    This is creating a CSV file and allowing you to open it in excel. CSV has no understanding of any format, it is, as its name implies, a set of Comma Separated Values. If you want to have some control over how things look you will need to alter this code to generate an HTML table. If you do that, then you will have the ability to do things like make entries bold.

    The section "addTableToCSV" is grabbing the HTML of the target table and iterating over it to generate the CSV. You should just modify that code to generate an HTML table instead.

    Andrew Paier | Director of Special Operations | BP3 Global, Inc. www.bp-3.com
  • JaydeepSapariya
    JaydeepSapariya
    14 Posts

    Re: Using custom HTML and javascript how to alter excel

    ‏2012-10-06T18:28:23Z  
    This is creating a CSV file and allowing you to open it in excel. CSV has no understanding of any format, it is, as its name implies, a set of Comma Separated Values. If you want to have some control over how things look you will need to alter this code to generate an HTML table. If you do that, then you will have the ability to do things like make entries bold.

    The section "addTableToCSV" is grabbing the HTML of the target table and iterating over it to generate the CSV. You should just modify that code to generate an HTML table instead.

    Andrew Paier | Director of Special Operations | BP3 Global, Inc. www.bp-3.com
    Hi Yasmin,
    Do we have any alternative to export table data into excel with BPM 8.0? I tried the same code but document.getElementById("Table0"); is not working with new coach.

    Any help would be appreciated.
  • SystemAdmin
    SystemAdmin
    7615 Posts

    Re: Using custom HTML and javascript how to alter excel

    ‏2012-10-26T17:43:03Z  
    Hi Yasmin,
    Do we have any alternative to export table data into excel with BPM 8.0? I tried the same code but document.getElementById("Table0"); is not working with new coach.

    Any help would be appreciated.
    I appreciate any help exporting repeating table data to excel in BPM 8.0