Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2

Thread: Format as Table

  1. #1
    New to the CF scene
    Join Date
    Apr 2014
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Format as Table

    The following function works like a dream. However, I would like to format the output as a Table in Excel but can't seem to get the syntax right.
    My code is just before the //autofit the columns

    <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;
    }
    }
    var alphabet = new Array();
    alphabet[0] = " ";
    alphabet[1] = "a";
    alphabet[2] = "b";
    alphabet[3] = "c";
    alphabet[4] = "d";
    alphabet[5] = "e";
    alphabet[6] = "f";
    alphabet[7] = "g";
    alphabet[8] = "h";
    alphabet[9] = "i";
    alphabet[10] = "j";
    alphabet[11] = "k";
    alphabet[12] = "l";
    alphabet[13] = "m";
    alphabet[14] = "n";
    alphabet[15] = "o";
    alphabet[16] = "p";
    alphabet[17] = "q";
    alphabet[18] = "r";
    alphabet[19] = "s";
    alphabet[20] = "t";
    alphabet[21] = "u";
    alphabet[22] = "v";
    alphabet[23] = "w";
    alphabet[24] = "x";
    alphabet[25] = "y";
    alphabet[26] = "z";
    myvalue = Math.floor((Math.random()*10)+1);
    var lastrow = rows.length
    var lastcol = columns.length
    var k = alphabet[lastcol]
    var a = "a1:"
    XlSheet.name="Tab_1"
    XlSheet.ListObjects.Add(Range(a + k +lastrow),xlYes).Name = "Tab_1"

    XlSheet.ListObjects("Tab_1").TableStyle = "TableStylemedium" + myvalue
    //autofit the columns
    XlSheet.columns.autofit;
    // Make visible:
    xlApp.visible = true;
    xlApp.DisplayAlerts = true;
    CollectGarbage();
    //xlApp.Quit();
    }
    }
    </script>

  • #2
    New to the CF scene
    Join Date
    Apr 2014
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I appreciate any help


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •