I have a PHP Web Application, which uses the ActiveXObject to export the contents of a table into an Excel spreadsheet. I have a bit of formatting (cell alignment, currency, dates, etc,.) for some cells, but I need to merge the top 3 cells and then import an image. I've been searching, but haven't had any joy. If anyone knows if this is possible, please let me know! Cheers, Samuel
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="Company Report";
// Store the sheet header names in an array
var rows = tblreport_invoice.getElementsByTagName("tr");
var columns = tblreport_invoice.getElementsByTagName("th");
var data = tblreport_invoice.getElementsByTagName("td");
// Set Excel Column Headers and formatting from array
for(i=0;i<columns.length;i++){
XlSheet.cells(2).value= "Company Quote";
XlSheet.cells(3,i+1).value= columns[i].innerText; //XlSheetHeader[i];
XlSheet.cells(3,i+1).font.color="6";
XlSheet.cells(3,i+1).font.bold="true";
XlSheet.cells(3,i+1).interior.colorindex="37";
XlSheet.Range("K4:K200").NumberFormat = "€ #,##0.00";
XlSheet.Range("L4:L200").NumberFormat = "€ #,##0.00";
XlSheet.Range("O4:O200").NumberFormat = "€ #,##0.00";
XlSheet.Range("P4:P200").NumberFormat = "€ #,##0.00";
XlSheet.Range("Q4:Q200").NumberFormat = "€ #,##0.00";
XlSheet.Range("R4:R200").NumberFormat = "€ #,##0.00";
XlSheet.Range("K4:L1000").HorizontalAlignment = -4152;
XlSheet.Range("M4:M1000").HorizontalAlignment = -4152;
XlSheet.Range("N4:Q1000").HorizontalAlignment = -4152;
XlSheet.Range("R4:R1000").HorizontalAlignment = -4152;
XlSheet.Range("F4:F1000").NumberFormat = "MM/DD/YYYY";
}
//run over the dynamic result table and pull out the values and insert into corresponding Excel cells
var d = 0;
for (r=4;r<rows.length+3;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();
}
}