Export to Excel Formatting Issue

Discussion in 'JavaScript' started by koolsamule, Nov 10, 2009.

  1. #1
    Hi Chaps,

    I have a HTML table of data:
    <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">&nbsp;</td>
          </tr>
          <tr>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</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>
    HTML:
    And I have a Javascript function, that exports the data to an Excel Spreadsheet:
    <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>
    Code (markup):
    The problem is that the Excel Spreadsheet formatting doesn't match the HTML format.
    HTML:
    [​IMG]

    EXCEL:
    [​IMG]

    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?
     
    koolsamule, Nov 10, 2009 IP