views:

785

answers:

1

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();
        }
}
A: 

// Insert company logo at top of the page

MyPic = XlSheet.Pictures.Insert("c:\\1.jpg");
            MyPic.Top = XlSheet.Range("A1").Top;
            MyPic.Left = XlSheet.Range("A1").Left;
            MyPic.ShapeRange.Height = XlSheet.Range("A1").RowHeight * 2;
samuel baldus