/**
* 名称 : createExcel
* 功能 : 生成记录结果集Excel文件
*/
function createExcel()
{
//保存标题名称
var arrTitle = new Array();
arrTitle[0] = "A";
arrTitle[1] = "B";
arrTitle[2] = "C";
arrTitle[3] = "D";
arrTitle[4] = "E";
arrTitle[5] = "F";
arrTitle[6] = "G";
arrTitle[7] = "H";
arrTitle[8] = "I";
arrTitle[9] = "J";
arrTitle[10] = "K";
arrTitle[11] = "L";
arrTitle[12] = "M";
arrTitle[13] = "N";
arrTitle[14] = "O";
arrTitle[15] = "P";
arrTitle[16] = "Q";
arrTitle[17] = "R";
arrTitle[18] = "S";
arrTitle[19] = "T";
arrTitle[20] = "U";
arrTitle[21] = "V";
arrTitle[22] = "W";
arrTitle[23] = "X";
arrTitle[24] = "Y";
arrTitle[25] = "Z";
//保存字段名称
var arrField = new Array();
var strField = new String();
strField = document.Excel.txtField.value;
arrField = strField.split(',');
//保存记录总数
var rowCount = parseInt('<%#dt.Rows.Count%>');
//保存记录结果集
var aRS = new Array();
//保存当前记录行数
var index = 0;
<%
for(int i=0; i<dt.Rows.Count; i++){
%>
aRS[index] = new Array();
var column = 0;
<%
for(int j=0; j<dt.Columns.Count; j++){
%>
aRS[index][column] = "<%=dt.Rows[i][j]%>";
column++;
<%
}
%>
index++;
<%
}
%>
//建立Excel应用程序oXL
var oXL = new ActiveXObject("Excel.Application");
//设置可见性
oXL.Visible = true;
//建立工作薄oWB
var oWB = oXL.Workbooks.Add();
//得到当前工作表
var oSheet = oWB.ActiveSheet;
//设置列的宽度
oSheet.Range("A1:Z1").ColumnWidth = 15.00;
//创建单元格对象
var rTitle = oSheet.Range("A1:Z1");
//水平中间对齐
rTitle.HorizontalAlignment = 3;
//显示标题信息
for(var i=0; i<arrField.length; i++)
{
var T = arrTitle[i];
oSheet.Range(T+"1").Value = arrField[i];
oSheet.Range(T+"1").BorderAround(LineStyle=1);
} oXL.StatusBar = "现在正在生成,请等待...";
//显示记录信息
for(var i=0; i<aRS.length; i++)
{
for(var j=0; j<arrField.length; j++)
{
var T = arrTitle[j];
oSheet.Range(T+(i+2)).Value = "'" + aRS[i][j].toString();
oSheet.Range(T+(i+2)).BorderAround(LineStyle=1);
}
}
oXL.StatusBar = "报表已经生成完毕.";
}
</script>
* 名称 : createExcel
* 功能 : 生成记录结果集Excel文件
*/
function createExcel()
{
//保存标题名称
var arrTitle = new Array();
arrTitle[0] = "A";
arrTitle[1] = "B";
arrTitle[2] = "C";
arrTitle[3] = "D";
arrTitle[4] = "E";
arrTitle[5] = "F";
arrTitle[6] = "G";
arrTitle[7] = "H";
arrTitle[8] = "I";
arrTitle[9] = "J";
arrTitle[10] = "K";
arrTitle[11] = "L";
arrTitle[12] = "M";
arrTitle[13] = "N";
arrTitle[14] = "O";
arrTitle[15] = "P";
arrTitle[16] = "Q";
arrTitle[17] = "R";
arrTitle[18] = "S";
arrTitle[19] = "T";
arrTitle[20] = "U";
arrTitle[21] = "V";
arrTitle[22] = "W";
arrTitle[23] = "X";
arrTitle[24] = "Y";
arrTitle[25] = "Z";
//保存字段名称
var arrField = new Array();
var strField = new String();
strField = document.Excel.txtField.value;
arrField = strField.split(',');
//保存记录总数
var rowCount = parseInt('<%#dt.Rows.Count%>');
//保存记录结果集
var aRS = new Array();
//保存当前记录行数
var index = 0;
<%
for(int i=0; i<dt.Rows.Count; i++){
%>
aRS[index] = new Array();
var column = 0;
<%
for(int j=0; j<dt.Columns.Count; j++){
%>
aRS[index][column] = "<%=dt.Rows[i][j]%>";
column++;
<%
}
%>
index++;
<%
}
%>
//建立Excel应用程序oXL
var oXL = new ActiveXObject("Excel.Application");
//设置可见性
oXL.Visible = true;
//建立工作薄oWB
var oWB = oXL.Workbooks.Add();
//得到当前工作表
var oSheet = oWB.ActiveSheet;
//设置列的宽度
oSheet.Range("A1:Z1").ColumnWidth = 15.00;
//创建单元格对象
var rTitle = oSheet.Range("A1:Z1");
//水平中间对齐
rTitle.HorizontalAlignment = 3;
//显示标题信息
for(var i=0; i<arrField.length; i++)
{
var T = arrTitle[i];
oSheet.Range(T+"1").Value = arrField[i];
oSheet.Range(T+"1").BorderAround(LineStyle=1);
} oXL.StatusBar = "现在正在生成,请等待...";
//显示记录信息
for(var i=0; i<aRS.length; i++)
{
for(var j=0; j<arrField.length; j++)
{
var T = arrTitle[j];
oSheet.Range(T+(i+2)).Value = "'" + aRS[i][j].toString();
oSheet.Range(T+(i+2)).BorderAround(LineStyle=1);
}
}
oXL.StatusBar = "报表已经生成完毕.";
}
</script>
{
Response.AddHeader("Content-Disposition", "attachment; filename=exportfilename.xls");
Response.Charset = "utf-8";
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
this.EnableViewState= false;
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
this.Grid1.RenderControl(hw);
Response.Write(tw);
Response.End();}其中Grid1是DataGrid控件的ID。
楼上的,能不能把那文件发给我呀?非常感谢!
E_mail:[email protected]
Imports OWCPublic Class Excel
Inherits System.Web.UI.Page
Protected WithEvents xlfile As System.Web.UI.WebControls.TextBox
Protected WithEvents export2excel As System.Web.UI.WebControls.Button
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
Private sql As SqlCommand
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Private conn As SqlConnection
#Region " Web 窗体设计器生成的代码 " '该调用是 Web 窗体设计器所必需的。
<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: 此方法调用是 Web 窗体设计器所必需的
'不要使用代码编辑器修改它。
InitializeComponent()
End Sub#End Region Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'在此处放置初始化页的用户代码
BindGrid()
End Sub
Private Sub BindGrid()
conn = New SqlConnection("Initial Catalog=Northwind;Data Source=127.0.0.1;uid=sa;pwd=sa")
sql = New SqlCommand("select * from products", conn)
conn.Open()
Dim reader As SqlDataReader
reader = sql.ExecuteReader()
DataGrid1.DataSource = reader
DataGrid1.DataBind()
reader.Close()
conn.Close()
End Sub
Private Sub WriteDataGrid2Excel()
Dim xlsheet As New SpreadsheetClass()
conn.Open()
Dim reader As SqlDataReader
reader = sql.ExecuteReader()
Dim numbercols As Int16
numbercols = reader.FieldCount
Dim row As Int16 = 1
Dim i As Int16
While reader.Read()
For i = 0 To numbercols - 1
xlsheet.ActiveSheet.Cells(row, i + 1) = reader.GetValue(i).ToString()
Next
row += 1
End While
reader.Close()
conn.Close()
'xlsheet.ActiveSheet.Export()
Try
xlsheet.ActiveSheet.Export(Server.MapPath(".") + "\wx.xls", OWC.SheetExportActionEnum.ssExportActionNone)
Catch e As Exception
Dim wx As String
wx = e.Message
End Try
Response.Redirect("wx.xls") End Sub Private Sub export2excel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles export2excel.Click
Dim xlApp, xlBook, XlSheet
xlApp = CreateObject("Excel.Application")
xlBook = xlApp.Workbooks.add
XlSheet = xlBook.Worksheets(1)
xlApp.Caption = "test表"
XlSheet.name = "test表" XlSheet.Rows("1:1").RowHeight = 36.6
XlSheet.Range("A1").FormulaR1C1 = "test"
XlSheet.Range("A1").Font.Bold = True
XlSheet.Range("A1").Font.ColorIndex = 3
XlSheet.Range("A1").Font.Size = 16 xlApp.Visible = True 'xlApp = Nothing
'xlBook = Nothing
'XlSheet = Nothing
End Sub Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
WriteDataGrid2Excel()
End Sub
End Class