Guest_imported
New member
- Jan 1, 1970
- 0
- 0
- 0
I wish to automate the "copy & paste" process on the contents of an HTML table so that users can export the displayed data to a local spreadsheet.
Any suggestions?
Any suggestions?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How can I Export an HTML table?
- Thread starter Guest_imported
- Start date
- Status
This is how we do it...put this button under the table in your document.
<input style="FONT-FAMILY: arial; FONT-SIZE: 8pt" value="Export to Excel" type="button" class="inputControl" onclick="exportIntoExcel>
Function exportIntoExcel()
Dim j
Dim myRange
on error resume next
Set ExcelApp = CreateObject("Excel.Application"
If Err Then
window.alert "Error starting Excel. Make sure you have Excel installed and that your browser's security setting for 'Initialize and script ActiveX controls not marked as safe' is not disabled."
Exit Function
End If
ON Error Goto 0
ExcelApp.Visible = true
Set ExcelWB = ExcelApp.Workbooks.Add
set s = ExcelWB.ActiveSheet
if not document.all("topTable" is nothing then
addTableToSheet(document.all.topTable)
end if
if not document.all("reportTable" is nothing then
addTableToSheet(document.all.reportTable)
end if
Set s= nothing
Set myRange = nothing
Set ExcelWB = Nothing
Set ExcelApp = Nothing
maxColCount = 0
maxRowCount = 0
self.focus
window.alert "Excel spreadsheet created."
End Function
'global
Dim ExcelApp
Dim ExcelWB
Dim s
Dim maxColCount : maxColCount = 0
Dim maxRowCount : maxRowCount = 0
Function addTableToSheet(thisTable)
Dim z
Dim i
Dim j
for i=0 to thisTable.rows.length - 1
for j=0 to thisTable.rows(i).cells.length-1
z = thisTable.rows.item(i).cells.item(j).innerText
s.Cells(i+1+maxRowCount,j+1) = z
s.Cells(i+1+maxRowCount,j+1).font.size = 10
if j+1 > maxColCount then
maxColCount = j+1
end if
next
next
'make a blank line
maxRowCount = maxRowCount + thisTable.rows.length + 1
End Function
<input style="FONT-FAMILY: arial; FONT-SIZE: 8pt" value="Export to Excel" type="button" class="inputControl" onclick="exportIntoExcel>
Function exportIntoExcel()
Dim j
Dim myRange
on error resume next
Set ExcelApp = CreateObject("Excel.Application"
If Err Then
window.alert "Error starting Excel. Make sure you have Excel installed and that your browser's security setting for 'Initialize and script ActiveX controls not marked as safe' is not disabled."
Exit Function
End If
ON Error Goto 0
ExcelApp.Visible = true
Set ExcelWB = ExcelApp.Workbooks.Add
set s = ExcelWB.ActiveSheet
if not document.all("topTable"
is nothing thenaddTableToSheet(document.all.topTable)
end if
if not document.all("reportTable"
is nothing thenaddTableToSheet(document.all.reportTable)
end if
Set s= nothing
Set myRange = nothing
Set ExcelWB = Nothing
Set ExcelApp = Nothing
maxColCount = 0
maxRowCount = 0
self.focus
window.alert "Excel spreadsheet created."
End Function
'global
Dim ExcelApp
Dim ExcelWB
Dim s
Dim maxColCount : maxColCount = 0
Dim maxRowCount : maxRowCount = 0
Function addTableToSheet(thisTable)
Dim z
Dim i
Dim j
for i=0 to thisTable.rows.length - 1
for j=0 to thisTable.rows(i).cells.length-1
z = thisTable.rows.item(i).cells.item(j).innerText
s.Cells(i+1+maxRowCount,j+1) = z
s.Cells(i+1+maxRowCount,j+1).font.size = 10
if j+1 > maxColCount then
maxColCount = j+1
end if
next
next
'make a blank line
maxRowCount = maxRowCount + thisTable.rows.length + 1
End Function
- Thread starter
- #4
Guest_imported
New member
- Jan 1, 1970
- 0
- 0
- 0
Link9,
I copied your code into a file and executed it. The Excel spreadsheet comes up but there's nothing in the spread sheet. Any ideas?
Fengshui_1998
' *************************
<html>
<head>
<script language="javascript">
function fnDoCopy(){
textRange = document.body.createTextRange();
textRange.moveToElementText(listTable);
textRange.execCommand("Copy"
;window.open("outputFile.xls","printing"
;}
</script>
</head>
<body>
<table name=listTable id=listTable>
<tr>
<td>This will be what gets printed</td>
</tr>
</table>
<br>
<input type=button value=Print onClick="fnDoCopy();">
</body>
</html>
Correct... when the spreadsheet loads, there will be nothing in it...
You have to [CTRL]-V to get it in there.
The code in the FAQ only copies it to the clipboard. I have been unable to figure out how to get it to automatically paste it in there. I don't think it's possible. Just paste it in there, and you'll be fine.
Paul Prewett
You have to [CTRL]-V to get it in there.
The code in the FAQ only copies it to the clipboard. I have been unable to figure out how to get it to automatically paste it in there. I don't think it's possible. Just paste it in there, and you'll be fine.
Paul Prewett
- Thread starter
- #6
Guest_imported
New member
- Jan 1, 1970
- 0
- 0
- 0
to "flies4fun"
This looks good, and with my dummy table works fine...
The problem now is that when my table has been dynamically generated by a 3rd party call and is displayed on the HTML page, I don't have any way of labelling it as <table name=ANYNAME" ID="ANYID"> or whatever.
So I am having trouble calling it with your script
Earlier, using Paul's javascript solution, when I had to paste it by hand in the spreadsheet, that worked OK if I encased the command that creates this table in <table name="mytable><--!AAGNAME = values --></table>
Do you have any further ideas? What is the format for a simple "paste" instruction inside this object model?
This looks good, and with my dummy table works fine...
The problem now is that when my table has been dynamically generated by a 3rd party call and is displayed on the HTML page, I don't have any way of labelling it as <table name=ANYNAME" ID="ANYID"> or whatever.
So I am having trouble calling it with your script
Earlier, using Paul's javascript solution, when I had to paste it by hand in the spreadsheet, that worked OK if I encased the command that creates this table in <table name="mytable><--!AAGNAME = values --></table>
Do you have any further ideas? What is the format for a simple "paste" instruction inside this object model?
Yea, that is a limitation for sure. Has the be the same table format each time that you create so that you can name it what you need to, and like I posted above, I have been unable to figure out how to get it to automatically paste it into the spreadsheet.
Like the FAQ says, printing has always been my biggest nightmare, but I guess I'm lucky in that I can use this method, so I haven't dug any further.
I wish I could help you out more. :-(
Like the FAQ says, printing has always been my biggest nightmare, but I guess I'm lucky in that I can use this method, so I haven't dug any further.
I wish I could help you out more. :-(
- Thread starter
- #8
Guest_imported
New member
- Jan 1, 1970
- 0
- 0
- 0
I found that if you are using ASP pages and you put this before the table tag, it will display your content in an Excel spread sheet.
Response.ContentType = "application/vnd.ms-excel"
Good luck!
- Status
