28,391
社区成员
发帖
与我相关
我的任务
分享table内容导出为EXCEL
请问有什么方法可以比较好的将table导出为EXCEL的方法？
我希望得到一个函数，输入table的名字，然后选择存放路径，就可以将指定的TABLE转换为EXCEL，生成的EXCEL除了内容以外，还要有原表格中单元格的颜色。
实在不知道做了，请各位高手帮忙！多谢！
我希望得到一个函数，输入table的名字，然后选择存放路径，就可以将指定的TABLE转换为EXCEL，生成的EXCEL除了内容以外，还要有原表格中单元格的颜色。
实在不知道做了，请各位高手帮忙！多谢！
...全文
请发表友善的回复…
发表回复
Ellen_hot 2005-11-23
- 打赏
- 举报
为什么按按钮后什么反应都没有呢，没有任何提示，也找不到导出的文件！why??
tvmark 2005-08-06
- 打赏
- 举报
不错，学习中！
plghqr 2005-08-06
- 打赏
- 举报
<%@ Language=VBScript CODEPAGE="932"%>
<%
Response.ContentType="Excel"
Response.AddHeader "Content-Disposition","attachment; filename=Statis.xls"
%>
<HTML xmlns:x='urn:schemas-microsoft-com:office:excel'>
<HEAD>
<style>
<!--table @page {mso-header-data:'&CMultiplication Table\\000ADate\\: &D\\000APage&P';mso-page-orientation:landscape;}
br {mso-data-placement:same-cell;}
-->
</style>
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Excel文件的Sheet的名字</x:Name>
<x:WorksheetOptions>
<x:Print>
<x:ValidPrinterInfo/>
</x:Print>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml><![endif]-->
</HEAD>
<BODY>
<table width="100%" border="1" bordercolor="#000000" cellspacing="0" cellpadding="0" align="center" style="border-collapse:collapse;border-top:0;">
<tr valign="top">
<Td style="padding-left:10px;padding-top:5px" height="500px">数据库列标题，可以任意变换颜色（只要你会HTML编辑）</TD>
</TR>
<%
'从数据库中读相应的值
while not objRs.EOF
%>
<tr valign="top">
<Td style="padding-left:10px;padding-top:5px" height="500px"><%=objRs("fdsf")%></TD>
</TR>
<%
end if
%>
</Table>
运行该代码，可以直接下载对应的Excel文件，并不需要启动Excel进程
<%
Response.ContentType="Excel"
Response.AddHeader "Content-Disposition","attachment; filename=Statis.xls"
%>
<HTML xmlns:x='urn:schemas-microsoft-com:office:excel'>
<HEAD>
<style>
<!--table @page {mso-header-data:'&CMultiplication Table\\000ADate\\: &D\\000APage&P';mso-page-orientation:landscape;}
br {mso-data-placement:same-cell;}
-->
</style>
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Excel文件的Sheet的名字</x:Name>
<x:WorksheetOptions>
<x:Print>
<x:ValidPrinterInfo/>
</x:Print>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml><![endif]-->
</HEAD>
<BODY>
<table width="100%" border="1" bordercolor="#000000" cellspacing="0" cellpadding="0" align="center" style="border-collapse:collapse;border-top:0;">
<tr valign="top">
<Td style="padding-left:10px;padding-top:5px" height="500px">数据库列标题，可以任意变换颜色（只要你会HTML编辑）</TD>
</TR>
<%
'从数据库中读相应的值
while not objRs.EOF
%>
<tr valign="top">
<Td style="padding-left:10px;padding-top:5px" height="500px"><%=objRs("fdsf")%></TD>
</TR>
<%
end if
%>
</Table>
运行该代码，可以直接下载对应的Excel文件，并不需要启动Excel进程
tvmark 2005-08-06
- 打赏
- 举报
我按 ljqhbt(半个杭州人)(不爱QQ) 做了一个导出Excel,可扫执行onclick事件时报错：Automation服务器不能创建议对象，请问这是不是应该在服务器上做些设置呢？
wgqqgw 2005-08-03
- 打赏
- 举报
偶尔看到，获益不浅，不过如果我还希望保持原有样式（合并、拆分了的单元格；颜色），不知道能否实现？
钛元素 2005-07-27
- 打赏
- 举报
楼上正解
ljqhbt 2005-07-27
- 打赏
- 举报
<input type="hidden" name="out_word" onclick="vbscript:buildDoc" value="导出到word" class="notPrint">
<input type="hidden" name="out_excel" onclick="AutomateExcel();" value="导出到excel" class="notPrint">
<title>浏览器表格导出到Excel代码</title>
<input type="button" name="out_word" onclick="vbscript:buildDoc" value="导出到word" class="notPrint">
<input type="button" name="out_word1" onclick="javascript:AutomateExcel() " value="导出到excel" class="notPrint">
<table id="data" width="200" border="1">
<tr>
<td>11</td>
<td>11</td>
</tr>
<tr>
<td>22</td>
<td>22</td>
</tr>
<tr>
<td>33</td>
<td>33</td>
</tr>
<tr>
<td>44 </td>
<td>44</td>
</tr>
</table>
<SCRIPT LANGUAGE="JavaScript">
<!--
function AutomateExcel()
{
// Start Excel and get Application object.
var oXL = new ActiveXObject("Excel.Application");
// Get a new workbook.
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;
var table = document.all.data;
var hang = table.rows.length;
var lie = table.rows(0).cells.length;
// Add table headers going cell by cell.
for (i=0;i<hang;i++)
{
for (j=0;j<lie;j++)
{
oSheet.Cells(i+1,j+1).Value = table.rows(i).cells(j).innerText;
}
}
oXL.Visible = true;
oXL.UserControl = true;
}
//-->
</SCRIPT>
导出到Word代码
<script language="vbscript">
Sub buildDoc
set table = document.all.data
row = table.rows.length
column = table.rows(1).cells.length
Set objWordDoc = CreateObject("Word.Document")
'objWordDoc.Application.Documents.Add theTemplate, False
objWordDoc.Application.Visible=True
Dim theArray(20,10000)
for i=0 to row-1
for j=0 to column-1
theArray(j+1,i+1) = table.rows(i).cells(j).innerTEXT
next
next
objWordDoc.Application.ActiveDocument.Paragraphs.Add.Range.InsertBefore("综合查询结果集") //显示表格标题
objWordDoc.Application.ActiveDocument.Paragraphs.Add.Range.InsertBefore("")
Set rngPara = objWordDoc.Application.ActiveDocument.Paragraphs(1).Range
With rngPara
.Bold = True //将标题设为粗体
.ParagraphFormat.Alignment = 1 //将标题居中
.Font.Name = "隶书" //设定标题字体
.Font.Size = 18 //设定标题字体大小
End With
Set rngCurrent = objWordDoc.Application.ActiveDocument.Paragraphs(3).Range
Set tabCurrent = ObjWordDoc.Application.ActiveDocument.Tables.Add(rngCurrent,row,column)
for i = 1 to column
objWordDoc.Application.ActiveDocument.Tables(1).Rows(1).Cells(i).Range.InsertAfter theArray(i,1)
objWordDoc.Application.ActiveDocument.Tables(1).Rows(1).Cells(i).Range.ParagraphFormat.alignment=1
next
For i =1 to column
For j = 2 to row
objWordDoc.Application.ActiveDocument.Tables(1).Rows(j).Cells(i).Range.InsertAfter theArray(i,j)
objWordDoc.Application.ActiveDocument.Tables(1).Rows(j).Cells(i).Range.ParagraphFormat.alignment=1
Next
Next
End Sub
</SCRIPT>
<input type="hidden" name="out_excel" onclick="AutomateExcel();" value="导出到excel" class="notPrint">
<title>浏览器表格导出到Excel代码</title>
<input type="button" name="out_word" onclick="vbscript:buildDoc" value="导出到word" class="notPrint">
<input type="button" name="out_word1" onclick="javascript:AutomateExcel() " value="导出到excel" class="notPrint">
<table id="data" width="200" border="1">
<tr>
<td>11</td>
<td>11</td>
</tr>
<tr>
<td>22</td>
<td>22</td>
</tr>
<tr>
<td>33</td>
<td>33</td>
</tr>
<tr>
<td>44 </td>
<td>44</td>
</tr>
</table>
<SCRIPT LANGUAGE="JavaScript">
<!--
function AutomateExcel()
{
// Start Excel and get Application object.
var oXL = new ActiveXObject("Excel.Application");
// Get a new workbook.
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;
var table = document.all.data;
var hang = table.rows.length;
var lie = table.rows(0).cells.length;
// Add table headers going cell by cell.
for (i=0;i<hang;i++)
{
for (j=0;j<lie;j++)
{
oSheet.Cells(i+1,j+1).Value = table.rows(i).cells(j).innerText;
}
}
oXL.Visible = true;
oXL.UserControl = true;
}
//-->
</SCRIPT>
导出到Word代码
<script language="vbscript">
Sub buildDoc
set table = document.all.data
row = table.rows.length
column = table.rows(1).cells.length
Set objWordDoc = CreateObject("Word.Document")
'objWordDoc.Application.Documents.Add theTemplate, False
objWordDoc.Application.Visible=True
Dim theArray(20,10000)
for i=0 to row-1
for j=0 to column-1
theArray(j+1,i+1) = table.rows(i).cells(j).innerTEXT
next
next
objWordDoc.Application.ActiveDocument.Paragraphs.Add.Range.InsertBefore("综合查询结果集") //显示表格标题
objWordDoc.Application.ActiveDocument.Paragraphs.Add.Range.InsertBefore("")
Set rngPara = objWordDoc.Application.ActiveDocument.Paragraphs(1).Range
With rngPara
.Bold = True //将标题设为粗体
.ParagraphFormat.Alignment = 1 //将标题居中
.Font.Name = "隶书" //设定标题字体
.Font.Size = 18 //设定标题字体大小
End With
Set rngCurrent = objWordDoc.Application.ActiveDocument.Paragraphs(3).Range
Set tabCurrent = ObjWordDoc.Application.ActiveDocument.Tables.Add(rngCurrent,row,column)
for i = 1 to column
objWordDoc.Application.ActiveDocument.Tables(1).Rows(1).Cells(i).Range.InsertAfter theArray(i,1)
objWordDoc.Application.ActiveDocument.Tables(1).Rows(1).Cells(i).Range.ParagraphFormat.alignment=1
next
For i =1 to column
For j = 2 to row
objWordDoc.Application.ActiveDocument.Tables(1).Rows(j).Cells(i).Range.InsertAfter theArray(i,j)
objWordDoc.Application.ActiveDocument.Tables(1).Rows(j).Cells(i).Range.ParagraphFormat.alignment=1
Next
Next
End Sub
</SCRIPT>
