SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Export to Excel Formatting Issue

    Hi Chaps,

    I have a HTML table of data:
    HTML Code:
    <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>
    And I have a Javascript function, that exports the data to an Excel Spreadsheet:
    Code:
    <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?

  2. #2
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I notice that "columns" is the elements with a tag name TH. You have two rows with TH tags. The first row has 3 and the second row 12. You use columns later to split the TD elements into rows but you are using 15 not 12 so the rows are not breaking at the right point.

    Have you tried selecting the html table and copying it to the clipboard. Opening a new Excel spreadsheet and doing a paste. On Excel 2007 at least this populates the spreadsheet with the table and maintains a lot of the formatting (merged cells, font size, colour and weight). It still quite good on Excel 97 but without the formatting.

  3. #3
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi there....thanks for that,

    I've now got the columns in check now

    However, I've noticed that the figures for the Full, Discount and Total prices are missing the '0' at the end.....and the '' is also not showing....

    Code:
    <td><div align="right"> <?php echo number_format($row_rsInvPending['pricegross'], 1, '.', '').'0'; ?></div></td>
    HTML Code:
    <td><div align="right"> 4.80</div></td>
    Excel:
    Code:
    4.8

  4. #4
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Isn't that just down it not been defined, in Excel, as a General formatted cell rather than as currency formatted cell with two fix decimal places. You would have thought that the euro sign was a give away but rather than make use of it it seems to just get thrown away.

    Did you use the Javascript or the cut and paste?

    If you use the cut and paste you can import not table stuff as well such as your company logo.

  5. #5
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I used the Javascript option. I don't want to use the Cut and Paste option, its fine for me, but not for employees.

    Do you know if PHP can handle tabluar data / formatting exporting function?

  6. #6
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Since you are using javascript you can format the cells in the script.

    For example: XlSheet.Range("C3:C4").NumberFormat = "€#,##0.00"

    Afraid, I have only a rudimentary knowledge of PHP so can not help you there. It might be sensible to pose the question in the PHP forum.


Bookmarks

Posting Permissions

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