我将查询结果显示在表格中,要注意以下几点:
1.表格是在.cs文件中写出来的,即Response.Write("<table name="toexcel" border=1>");
2.表格中的内容有链接,并且有分页功能;
3.在分页功能下,选择全部时能导出全部内容,选择某一页时,只导出这一页内容.
4.将表格内容导出到excel中时,希望是一个标准的表格(在excel中内容无链接,字体颜色一致,有表格线),请问有什么好的方法?
5.不用web按钮调用所写的方法,最好用js.
1.表格是在.cs文件中写出来的,即Response.Write("<table name="toexcel" border=1>");
2.表格中的内容有链接,并且有分页功能;
3.在分页功能下,选择全部时能导出全部内容,选择某一页时,只导出这一页内容.
4.将表格内容导出到excel中时,希望是一个标准的表格(在excel中内容无链接,字体颜色一致,有表格线),请问有什么好的方法?
5.不用web按钮调用所写的方法,最好用js.
解决方案 »
- 问个多表查询的问题
- 点击图片放大后如何滑动滚轮放大和缩小
- 关于事件的使用
- ajax效率
- JavaScript的一个问题
- 关于Cookies 的问题。。。。。。
- 用javascript了<INPUT id="fdate" onfocus="javascript:calendar();" runat="server">页面怎么还是刷新一下?
- 插入语句为什么只执行了一次?
- 续:基于.NET技术的人力资源管理信息系统源代码发布!
- 正则小问题求解
- 看看这个类,为什么会这样的错误:'addarticle.pubs' denotes a 'field' where a 'class' was expected
- 如何在不关闭浏览器的情况下重新发送客户证书给服务器?
http://dotnet.aspx.cc/ShowDetail.aspx?id=8A4CBF47-B888-4832-3389-ED3A3A3C8AAB
var DataTable = document.all("tblView");
var DataExcel = document.all("DataExcel");
var ssConstants = DataExcel.Constants;
DataExcel.HTMLData = DataTable.outerHTML;
DataExcel.ActiveSheet.UsedRange.Borders.Weight = ssConstants.owcLineWeightThin;
DataExcel.ActiveSheet.UsedRange.AutoFitColumns();
try
{
DataExcel.ActiveSheet.Export();
}
catch(e){}
}
<asp:table id="tblView" runat="server" Width="100%" BorderStyle="Solid" EnableViewState="False"
BorderColor="Black" CellPadding="0" CellSpacing="0" BorderWidth="0px" Font-Size="X-Small"></asp:table>得用一个控件中间倒数据。
<OBJECT id="DataExcel" classid="clsid:0002E510-0000-0000-C000-000000000046">
<PARAM NAME="HTMLURL" VALUE="">
<PARAM NAME="HTMLData" VALUE="<html xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"> <head> <style type="text/css"> <!--tr 	{mso-height-source:auto;} td 	{white-space:nowrap;} .wc8EE73A 	{white-space:nowrap; 	font-family:宋体; 	mso-number-format:General; 	font-size:auto; 	font-weight:auto; 	font-style:auto; 	text-decoration:auto; 	mso-background-source:auto; 	mso-pattern:auto; 	mso-color-source:auto; 	text-align:general; 	vertical-align:bottom; 	border-top:none; 	border-left:none; 	border-right:none; 	border-bottom:none; 	mso-protection:locked;} --> </style> </head> <body> <!--[if gte mso 9]><xml> <x:ExcelWorkbook> <x:ExcelWorksheets> <x:ExcelWorksheet> <x:OWCVersion>9.0.0.3821</x:OWCVersion> <x:Label Style='border-top:solid .5pt silver;border-left:solid .5pt silver; border-right:solid .5pt silver;border-bottom:solid .5pt silver'> <x:Caption>Microsoft Office Spreadsheet</x:Caption> </x:Label> <x:Name>Sheet1</x:Name> <x:WorksheetOptions> <x:Selected/> <x:Height>7620</x:Height> <x:Width>15240</x:Width> <x:TopRowVisible>0</x:TopRowVisible> <x:LeftColumnVisible>0</x:LeftColumnVisible> <x:ProtectContents>False</x:ProtectContents> <x:DefaultRowHeight>210</x:DefaultRowHeight> <x:StandardWidth>2377</x:StandardWidth> </x:WorksheetOptions> </x:ExcelWorksheet> </x:ExcelWorksheets> <x:MaxHeight>80%</x:MaxHeight> <x:MaxWidth>80%</x:MaxWidth> </x:ExcelWorkbook> </xml><![endif]--> <table class=wc8EE73A x:str> <col class=wc8EE73A width="56"> <tr height="14"> <td class=wc8EE73A></td> </tr> </table> </body> </html> ">
<PARAM NAME="DataType" VALUE="HTMLDATA">
<PARAM NAME="AutoFit" VALUE="0">
<PARAM NAME="DisplayColHeaders" VALUE="-1">
<PARAM NAME="DisplayGridlines" VALUE="-1">
<PARAM NAME="DisplayHorizontalScrollBar" VALUE="-1">
<PARAM NAME="DisplayRowHeaders" VALUE="-1">
<PARAM NAME="DisplayTitleBar" VALUE="-1">
<PARAM NAME="DisplayToolbar" VALUE="-1">
<PARAM NAME="DisplayVerticalScrollBar" VALUE="-1">
<PARAM NAME="EnableAutoCalculate" VALUE="-1">
<PARAM NAME="EnableEvents" VALUE="-1">
<PARAM NAME="MoveAfterReturn" VALUE="-1">
<PARAM NAME="MoveAfterReturnDirection" VALUE="0">
<PARAM NAME="RightToLeft" VALUE="0">
<PARAM NAME="ViewableRange" VALUE="1:65536">
</OBJECT>
<input type="hidden" name="out_excel" onclick="AutomateExcel();" value="导出到excel" class="notPrint"> <title>浏览器表格导出到Excel</title>
<input type="button" name="out_word1" onclick="javascript:AutomateExcel() " value="导出到excel" class="notPrint">
<table id="data" width="200" border="1">
<tr>
<td>我是中国人</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>
<HEAD>
</HEAD>
<BODY>
<table id="tblData1">
<tr><td>gdsssa</td><td>445</td></tr>
<tr><td>gdsssa</td><td>445</td></tr>
</table>
<table id="tblData" width="100%" height="165" border="0" cellpadding="1" cellspacing="1" id="List" dwcopytype="CopyTableRow">
<tr class="tableHead">
<td width="51" height="9" style="TEXT-ALIGN: right">序号</td>
<td width="111">项目名称</td>
<td width="206" style="TEXT-ALIGN: left">机组选择</td>
<td width="216" height="9" style="TEXT-ALIGN: left">功能描述</td>
<td width="184" height="9" style="TEXT-ALIGN: left">备注</td>
</tr>
<tr class="cellCenter">
<td style="TEXT-ALIGN: right" height="10">14</td>
<td>#3高加端差</td>
<td style="TEXT-ALIGN: left"> <input type="checkbox" id="checkbox12215" name="checkbox12214" value="2" checked onclick="runCheckSeclect()">
#5
<input type="checkbox" id="checkbox22215" name="checkbox22214" value="1" onclick="runCheckSeclect()">
#6 </td>
<td style="TEXT-ALIGN: left" height="10"> </td>
<td height="10" style="TEXT-ALIGN: left"> </td>
</tr>
</table>
<input type="button" value="export" onclick="exportExcel(tblData)">
<P> </P>
</BODY>
<script language="javascript">
function exportExcel(atblData)
{
if(typeof(EXPORT_OBJECT)!="object")
{
document.body.insertAdjacentHTML("afterBegin","<OBJECT style='display:none' classid=clsid:0002E510-0000-0000-C000-000000000046 id=EXPORT_OBJECT></Object>");
}
with (EXPORT_OBJECT)
{
DataType = "HTMLData";
HTMLData =atblData.outerHTML;
try{
ActiveSheet.Export("C:\\owcToExcel.xls", 0);
alert('汇出完毕');
}
catch (e){
alert('汇出Excel表失败,请确定已安装Excel2000(或更高版本),并且没打开同名xls文件');
}
}
}
</script>
</HTML>
Inherits="aspxWeb.mengxianhui.com.DataGridToExcel"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title id="mengxianhui" runat="server"></title>
<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
<meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body MS_POSITIONING="GridLayout" style="FONT-SIZE:9pt">
<form id="Form1" method="post" runat="server">
<asp:Label id="Label1" runat="server"></asp:Label>
<asp:TextBox ID="xlfile" Runat="server"></asp:TextBox>
<br>
<br>
<asp:Button ID="ExportDataBase2Excel" Runat="server" />
<asp:Button ID="ExportDataGrid2Excel" Runat="server" />
<br>
<asp:DataGrid id="DataGrid1" runat="server" AutoGenerateColumns="False" BorderColor="#CC9966"
BorderStyle="None" BorderWidth="1px" BackColor="White" CellPadding="4">
<ItemStyle ForeColor="#330099" BackColor="White"></ItemStyle>
<HeaderStyle Font-Bold="True" ForeColor="#FFFFCC" BackColor="#990000"></HeaderStyle>
<Columns>
<asp:BoundColumn DataField="Title"></asp:BoundColumn>
<asp:BoundColumn DataField="Author"></asp:BoundColumn>
</Columns>
</asp:DataGrid>
</form>
</body>
</HTML>
Imports System.Data
Imports System.Data.OleDb
Imports OWCPublic Class DataGridToExcel
Inherits System.Web.UI.Page
Protected WithEvents xlfile As System.Web.UI.WebControls.TextBox
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
Protected WithEvents ExportDataGrid2Excel As System.Web.UI.WebControls.Button
Protected WithEvents ExportDataBase2Excel As System.Web.UI.WebControls.Button
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected mengxianhui As New HtmlGenericControl() Private cnn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="_
+ Server.MapPath("Test.mdb"))
Private sql As OleDbCommand = New OleDbCommand("SELECT TOP 50 Title,Author FROM Document", cnn)#Region " Web Form Designer Generated Code " 'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent() End Sub Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs)_
Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub#End Region Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)_
Handles MyBase.Load
Label1.Text = "请输入要保存得文件名字:"
ExportDataGrid2Excel.Text = "由DataGrid生成Excel文件"
ExportDataBase2Excel.Text = "数据库直接生成Excel文件"
DataGrid1.Columns(0).HeaderStyle.HorizontalAlign = HorizontalAlign.Center
DataGrid1.Columns(0).HeaderText = "文章名称"
DataGrid1.Columns(1).HeaderText = "作者"
DataGrid1.Columns(0).HeaderStyle.Font.Bold = True
DataGrid1.Style.Add("font-size", "9pt")
mengxianhui.InnerText = "【孟宪会之精彩世界】- 将DataGrid输出到Excel文件"
Me.BindDataGrid()
End Sub Private Sub BindDataGrid()
cnn.Open()
Dim reader As OleDbDataReader = sql.ExecuteReader()
Me.DataGrid1.DataSource = reader
Me.DataGrid1.DataBind()
reader.Close()
cnn.Close()
End Sub Private Sub WriteDataGrid2Excel()
Dim xlsheet As New SpreadsheetClass()
cnn.Open()
Dim reader As OleDbDataReader = Me.sql.ExecuteReader()
Dim numbercols As Integer = reader.FieldCount
Dim row As Integer = 2
Dim i As Integer = 0
' 输出标题
For i = 0 To numbercols - 1
xlsheet.ActiveSheet.Cells(1, i + 1) = reader.GetName(i).ToString()
Next ' 输出字段内容
While (reader.Read())
For i = 0 To numbercols - 1
xlsheet.ActiveSheet.Cells(row, i + 1) = reader.GetValue(i).ToString()
Next
row = row + 1
End While
reader.Close()
cnn.Close()
Try
xlsheet.ActiveSheet.Export(Server.MapPath(".") + "\Images\" + Me.xlfile.Text,_
OWC.SheetExportActionEnum.ssExportActionNone)
Catch e As System.Runtime.InteropServices.COMException
Response.Write("错误:" + e.Message)
End Try
End Sub Private Sub WriteDataGrid2Excel2()
Dim xlsheet As New SpreadsheetClass()
Dim i As Integer = 0
Dim j As Integer = 0
'Response.End()
' 输出标题
Dim oItem As DataGridColumn
For Each oItem In DataGrid1.Columns
xlsheet.ActiveSheet.Cells(1, i + 1) = oItem.HeaderText
'xlsheet.ActiveSheet.Range(xlsheet.ActiveSheet.Cells(1, 1),_
xlsheet.ActiveSheet.Cells(1, i + 1)).Font.Bold = True
'设置格式
xlsheet.Range(xlsheet.Cells(1, 1), xlsheet.Cells(1, i + 1)).Font.Bold = True
xlsheet.Range(xlsheet.Cells(1, 1), xlsheet.Cells(1, i + 1)).Font.Color = "red"
i = i + 1
Next Dim numbercols As Integer = DataGrid1.Items.Item(0).Cells.Count
' 输出字段内容
For j = 0 To DataGrid1.Items.Count - 1
For i = 0 To numbercols - 1
xlsheet.Range(xlsheet.Cells(2, 2), xlsheet.Cells(j + 2, i + 1)).Font.Color = "blue"
'xlsheet.Range("A2:B14").WrapText = True
xlsheet.Range(xlsheet.Cells(2, 1), xlsheet.Cells(j + 2, i + 1)).AutoFitColumns()
xlsheet.ActiveSheet.Cells(j + 2, i + 1) = DataGrid1.Items.Item(j).Cells(i).Text.Replace(" ", " ")
Next
Next
Try
xlsheet.ActiveSheet.Export(Server.MapPath(".") + "\Images\" + Me.xlfile.Text,_
OWC.SheetExportActionEnum.ssExportActionNone)
Catch e As System.Runtime.InteropServices.COMException
Response.Write("错误:" + e.Message)
End Try
End Sub Private Sub ExportDataGrid2Excel_Click(ByVal sender As Object,_
ByVal e As System.EventArgs) Handles ExportDataGrid2Excel.Click
If (Me.xlfile.Text.Trim() <> "") Then
Me.WriteDataGrid2Excel2()
End If
End Sub Private Sub ExportDataBase2Excel_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles ExportDataBase2Excel.Click
If (Me.xlfile.Text.Trim() <> "") Then
Me.WriteDataGrid2Excel()
End If
End SubEnd Class
注意设置要保存文件得目录具有匿名可修改的权限,这一步怎样做.