I have a HTML table of data:
- Code: Select all
<table border="0" cellpadding="0" cellspacing="0" id="tblrepeat">
<caption><img src="../Images/invoicing.jpg" border="0" onclick="ExportToExcel()"/></a>
</caption>
<tr class="checkpoint" >
<th class="checkpoint" colspan="5">Project / Document</th>
<th class="checkpoint" colspan="4">Analysis</th>
<th class="checkpoint" colspan="3">Costing / Discount</th>
</tr>
<tr>
<th height="21">Project No.</th>
<th>Project Title</th>
<th>Druck-NR</th>
<th>Type</th>
<th>Deadline</th>
<th>Total</th>
<th>Full</th>
<th>Fuzzy</th>
<th>Proof</th>
<th>Full Price</th>
<th>Discount Price</th>
<th>Total</th>
</tr>
<tr>
<td>3614</td>
<td>A_581_5681842_en_GB</td>
<td>5692842</td>
<td>XML</td>
<td>26/11/2009</td>
<td>70</td>
<td>40</td>
<td>20</td>
<td>10</td>
<td>€ 11.90</td>
<td>€ 2.30</td>
<td>€ 9.60</td>
</tr>
<tr>
<td>3611</td>
<td>A_578_5811437_en_GB</td>
<td>234</td>
<td>XML</td>
<td>26/11/2009</td>
<td>70</td>
<td>40</td>
<td>20</td>
<td>10</td>
<td>€ 11.90</td>
<td>€ 2.30</td>
<td>€ 9.60</td>
</tr>
<tr>
<td colspan="12"> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td><div align="right"><span class="blueBold">Totals:</span></div></td>
<td><span class="blueBold">140</span></td>
<td><span class="blueBold">80</span></td>
<td><span class="blueBold">40</span></td>
<td><span class="blueBold">20</span></td>
<td><span class="blueBold">€ 23.80</span></td>
<td><span class="blueBold">€ 4.60</span></td>
<td><span class="blueBold">€ 19.20</span></td>
</tr>
</table>
And I have a Javascript function, that exports the data to an Excel Spreadsheet:
- Code: Select all
<script language="javascript" type="text/javascript">
function ExportToExcel() {
input_box=confirm("Export to Microsoft Excel?");
if (input_box==true) {
var xlApp = new ActiveXObject("Excel.Application");
// Silent-mode:
xlApp.Visible = true;
xlApp.DisplayAlerts = false;
var xlBook = xlApp.Workbooks.Add();
xlBook.worksheets("Sheet1").activate;
var XlSheet = xlBook.activeSheet;
XlSheet.Name="JavaScript Export to Excel";
// Store the sheet header names in an array
var rows = tblrepeat.getElementsByTagName("tr");
var columns = tblrepeat.getElementsByTagName("th");
var data = tblrepeat.getElementsByTagName("td");
// Set Excel Column Headers and formatting from array
for(i=0;i<columns.length;i++){
XlSheet.cells(1,i+1).value= columns[i].innerText; //XlSheetHeader[i];
XlSheet.cells(1,i+1).font.color="6";
XlSheet.cells(1,i+1).font.bold="true";
XlSheet.cells(1,i+1).interior.colorindex="45";
}
//run over the dynamic result table and pull out the values and insert into corresponding Excel cells
var d = 0;
for (r=2;r<rows.length+1;r++) { // start at row 2 as we've added in headers - so also add in another row!
for (c=1;c<columns.length+1;c++) {
XlSheet.cells(r,c).value = data[d].innerText;
d = d + 1;
}
}
//autofit the columns
XlSheet.columns.autofit;
// Make visible:
xlApp.visible = true;
xlApp.DisplayAlerts = true;
CollectGarbage();
//xlApp.Quit();
}
}
</script>
The problem is that the Excel Spreadsheet formatting doesn't match the HTML format.
HTML:
EXCEL:
Is there:
a. any way to match the layout?
b. add company logo and/or address in the javascript, so it appears in the Excel Spreadsheet?
