• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Export HTML table data to Excel sheet using ActiveX

 
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can any body help me for Export Html report data from jsp page to Excel .
i have written code for that its working fine but in html table there is one column in which varchar data 0021 like prsent while it converting in excel the preseding zeros eleminated and data 21 is diplaying only in excel , i just wanaa store all data is correct format like as number as number , varchar as varchar2.
kindly find the code here
i dont want to use ""+detailsTable.rows(y).cells(x).innerText;
or like "@"+detailsTable.rows(y).cells(x).innerText;

function toExcel(tableID)
{


var detailsTable = document.getElementById(tableID);

var oExcel = new ActiveXObject("Excel.Application");
var oBook = oExcel.Workbooks.Add;
var oSheet = oBook.Worksheets(1);
for (var y=0;y<detailsTable.rows.length;y++)
{
for (var x=0;x<detailsTable.rows(y).cells.length;x++)
{
oSheet.Cells(y+1,x+1) =detailsTable.rows(y).cells(x).innerText;

}
}
oExcel.Visible = true;
oExcel.UserControl = true;
}



Thanks
 
Ranch Hand
Posts: 608
Firefox Browser Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You'll have to set the data type of that cell to 'text'. Excel is pretty stubborn about trimming leading '0' in case of number cells. You would have to consult the documentation of the ActiveX to see how to mark the cell as a 'text' cell,

Alternately the workaround you have mentioned is good -- add a quote or @. You could also add a 'space' char at the end of the number and see if that's helpful.
 
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
try this

<html>
<head>
<script type="text/javascript">
function write_to_excel()
{
str="";

var mytable = document.getElementsByTagName("table")[0];
var row_Count = mytable.rows.length;
var col_Count = mytable.getElementsByTagName("tr")[0].getElementsByTagName("td").length;

var ExcelApp = new ActiveXObject("Excel.Application");
var ExcelSheet = new ActiveXObject("Excel.Sheet");
ExcelSheet.Application.Visible = true;

for(var i=0; i < row_count ; i++)
{
for(var j=0; j < col_Count; j++)
{
str= mytable.getElementsByTagName("tr")[i].getElementsByTagName("td")[j].innerHTML;
ExcelSheet.ActiveSheet.Cells(i+1,j+1).Value = str;
}
}
}
</script></script></head>
<body>
<input type="submit" value="Export to EXCEL" onclick="write_to_excel();"/>

<!-- ************************************************-->
<!--**** INSERT THE TABLE YOU WANT EXPORT HERE ****-->
<table><tr><td>First</td><td>second</td></tr></table>
<!-- *******************example given above****************-->

</body>
</html>
 
author
Posts: 15385
6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
When you post code useCodeTags.

Eric
 
Saifuddin Merchant
Ranch Hand
Posts: 608
Firefox Browser Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

praneel shah wrote:try this
str= mytable.getElementsByTagName("tr")[i].getElementsByTagName("td")[j].innerHTML;
ExcelSheet.ActiveSheet.Cells(i+1,j+1).Value = str;



Not sure this would help.

I think the cell type for the Excel would need to be set using the Excel API .. something like ExcelSheet.ActiveSheet.Cells(i+1,j+1).Cell(Type,Range) -- Not sure if the syntax is correct. Have a look @ http://www.techonthenet.com/excel/formulas/cell.php and see if it helps!
reply
    Bookmark Topic Watch Topic
  • New Topic