http://xml.sz.luohuedu.net/xml/ShowDetail.asp?id=6AFBF00B-459D-4642-AD14-8A4765FFAFCC
解决方案 »
- 数据库定义了一个datetime类型的数据 怎么输入数据的时候 生成输入数据的时间?
- 母版页中Gridview控件数据子页调用问题!!
- 关于在多线程中访问session的问题(能解决给100)
- 第一次使用AjaxPro在firefox上正常,ie上显示null
- 关于自定义控件属性的描述的显示问题
- SqlServer2005 安装 F:\soft\cs_sql_2005_dev_all_dvd\SQL Server x64\Servers\Setup.exe 不是有效的Win32应用程序
- GridView如何绑定不在同一表中的字段
- 在DataGrid中添加三个CheckBox后如何实现全选、单选功能?
- ##高手請進!!!#怎么限制文件上传的类型呢,accept屬性沒用阿~
- Iframe 转到 外部页面的问题
- 我看到一个网页上有一个菜单折叠的功能,不知道是怎么实现的
- 还有一个问题要请教?
{
Excel.Application excel=new Excel.Application();
Excel.Workbooks workbooks = excel.Workbooks;
Excel._Workbook workbook1=excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets sheets = workbook1.Worksheets;
_Worksheet worksheet = (_Worksheet) sheets.get_Item(1); int rowIndex=1;
int colIndex=0; //循环读取生成新表格
excel.Application.Workbooks.Add(true); //生成EXCEL的格式设定
excel.Cells.Font.Bold = false;
excel.Cells.FillLeft();
excel.Cells.Font.Size = "9";
//引用函数GetData中所取出的数据,得到数据源
System.Data.DataTable table =dt;
//将所得到的表的列名,赋值给单元格
foreach(DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[1,colIndex]=col.ColumnName;
}
//同样方法处理数据 foreach(DataRow row in table.Rows)
{
rowIndex++;
colIndex=0;
//处理各列的数据
foreach(DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString();
}
}
//不可见,即后台处理
excel.Visible=true;
//友好界面提示
//Label1.Text="操作成功,请另存生成的表格!";
Excel.XlSaveAsAccessMode asm=Excel.XlSaveAsAccessMode.xlShared;
object Nothing=System.Reflection.Missing.Value;
string FileNameString =System.DateTime.Now.ToString("yyyyMMddhhmmss");
string aa="d:/Inetpub/wwwroot/temp/"+FileNameString+".xls";
excel.ActiveWorkbook.SaveAs( aa,Nothing,Nothing,Nothing,Nothing,Nothing,asm,Nothing,Nothing,Nothing,Nothing,Nothing);
excel.Workbooks.Close();
excel.Quit();
excel=null;
return FileNameString;
}
http://xml.sz.luohuedu.net/xml/ShowDetail.asp?id=6AFBF00B-459D-4642-AD14-8A4765FFAFCC
acewang(**^o^**)大侠说了啊
呵呵,没有看到
^_^
huzhishan.yeah.net 2003-10-28 个人工作室
在Web From上输出数据到Excel有两种方法,一个是有数据库直接导出;另外一个方法是由DataGrid直接输出到Excel文件。下面得代码实现了这两个功能。注意:在使用时要引用Microsoft Office Web Components 9.0 COM组件,另外注意设置要保存文件得目录具有匿名可修改的权限。</P><P>DataGridToExcel.aspx</P><P><%@ Page Language="vb" EnableViewState="False" AutoEventWireup="false" Codebehind="DataGridToExcel.aspx.vb"
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></P><P>DataGridToExcel.aspx.vb</P><P>Imports System
Imports System.Data
Imports System.Data.OleDb
Imports OWC</P><P>Public 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()</P><P> 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)</P><P>#Region " Web Form Designer Generated Code "</P><P> 'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()</P><P> End Sub</P><P> 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</P><P>#End Region</P><P> 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</P><P> Private Sub BindDataGrid()
cnn.Open()
Dim reader As OleDbDataReader = sql.ExecuteReader()
Me.DataGrid1.DataSource = reader
Me.DataGrid1.DataBind()
reader.Close()
cnn.Close()
End Sub</P><P> 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</P><P> ' 输出字段内容
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</P><P> 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</P><P> 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</P><P> 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</P><P> 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 Sub</P><P>End Class</P><P>