确定可以实现,不过是输入到浏览器端的,不知道是不是你想要的 我就直接贴我的代码了Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click Dim Strformat As String Dim Fname As String Select Case Dlist_format.SelectedIndex Case 0 Fname = "attachment;filename=Export.xls" Strformat = "application/vnd.ms-excel" Case 1 Fname = "attachment;filename=Export.doc" Strformat = "application/msword" Case 2 Fname = "attachment;filename=Export.html" Strformat = "text/html" End Select '2008-09-17 Dingminghua Dim dt As New DataTable Err = "SearchLog->Button5_Click" If ViewState("StrSql") Is Nothing Then ClientScript.RegisterStartupScript(Me.GetType, "Menu", "<script>alert('请先进行查询!');</script>") Exit Sub End If ExecuteNonquery(ViewState("StrSql"), Err, dt) Log_Export(dt, Strformat, Fname) End Sub'如果不指定列名则以数据集的列为准 Private Sub Log_Export(ByVal tb As DataTable, ByVal Str As String, ByVal Filename As String) Dim dgrid As System.Web.UI.WebControls.DataGrid = Nothing Dim Context As System.Web.HttpContext = System.Web.HttpContext.Current Dim StrOur As System.IO.StringWriter = Nothing Dim htmlWriter As System.Web.UI.HtmlTextWriter = Nothing If Not IsNothing(tb) Then Context.Response.ContentType = Str Context.Response.AppendHeader("content-disposition", Filename) Context.Response.ContentEncoding = System.Text.Encoding.Default Context.Response.Charset = "" StrOur = New IO.StringWriter htmlWriter = New System.Web.UI.HtmlTextWriter(StrOur) dgrid = New DataGrid tb.Columns(0).ColumnName = "时间日期" tb.Columns(1).ColumnName = "Ip地址" tb.Columns(2).ColumnName = "用户名" tb.Columns(3).ColumnName = "操作类型" tb.Columns(4).ColumnName = "具体操作" tb.Columns(5).ColumnName = "结果" tb.Columns(6).ColumnName = "备注" 'tb.Columns(7).ColumnName = "序号" dgrid.DataSource = tb dgrid.AllowPaging = False dgrid.DataBind() 'dgrid.Columns(0).HeaderText = "时间" dgrid.RenderControl(htmlWriter) Context.Response.Write(StrOur.ToString) Context.Response.Flush() Context.Response.End() dgrid.Dispose() StrOur.Dispose() End If End Sub
或使用二维数组绑定range
string[,] arr= new string[row,colCount];
Excel.Range range = (Excel.Range)workSheet.Cells[top,left];
range = range.get_Resize(row,colCount);
range.Value = arr;
或gridview等控件导出到excel
http://blog.bossma.cn/dotnet/csharp-exorpt-excel-process-kill/
{
string strTempFileName = DateTime.Now.ToString("yyyyMMddHHmmss")+".xls";
string strModName = Server.MapPath(".") + @"\a.xls";
string strDownFileName = @"./" + strTempFileName;
string strFileName = Server.MapPath(".") + @"\" + strTempFileName; Excel.Application m_objExcel = new Excel.Application();
m_objExcel.Workbooks.Open(strModName, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss); Excel.Workbooks m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
Excel.Workbook m_objBook = m_objExcel.ActiveWorkbook;
Excel.Worksheet sheet = (Excel.Worksheet)m_objBook.ActiveSheet;
for (int i = 0; i < total.Rows.Count; i++)
{
for (int j = 0; j < total.Columns.Count; j++)
{ Excel.Range range = sheet.get_Range(m_objExcel.Cells[i + 1, j + 1], m_objExcel.Cells[i + 1, j + 1]);
range.set_Value(miss, total.Rows[i][j].ToString());
range.set_Value(miss, total.Rows[i][j].ToString());
}
}
m_objBook.SaveAs(@strFileName, miss, miss, miss, miss,
miss, Excel.XlSaveAsAccessMode.xlNoChange,
Excel.XlSaveConflictResolution.xlLocalSessionChanges, miss, miss, miss, miss);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objExcel);
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)sheet);
m_objExcel = null;
m_objBooks = null;
m_objBook = null;
sheet = null;
GC.Collect(); Response.Redirect(strDownFileName, false);
}
{
Response.Clear();
Response.Buffer = true;
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls");
// 如果设置为 GetEncoding("GB2312");导出的文件将会出现乱码!!!
Response.ContentEncoding = System.Text.Encoding.UTF7;
Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.GridView1.RenderControl(oHtmlTextWriter);
Response.Output.Write(oStringWriter.ToString());
Response.Flush();
Response.End(); } public override void VerifyRenderingInServerForm(Control control)
{
//override VerifyRenderingInServerForm.
}
1.使用active加javascript 不用刷新页面在客户端运行
2.在服务器端导出excel 对客户端没有要求 需要安装excel组件,在导出过程中可能导致excel进程无法关闭,出现大量excel进程
3.通过先绑定到控件然后导出 对客户端没有要求,不需要安装excel组件,可以定义字体样式,能一次绑定批量导出。 只能用于web
可能大家常用的是2和3,特别是在windows项目中那就只能使用方法2了。但是我认为在web asp.net项目中使用第3种更方便。因此我也根据实际需要写了一个类,可以快速调用: Code
public class ExportHelper
{ public static void ExportToExcel(IList dataList, string[] fields, string[] headTexts,string title)
{
GridView gvw = new GridView();
int ColCount, i; //如果筛选的字段和对应的列头名称个数相对的情况下只导出指定的字段
if (fields.Length != 0 && fields.Length == headTexts.Length)
{
ColCount = fields.Length;
gvw.AutoGenerateColumns = false; for (i = 0; i < ColCount; i++)
{
BoundField bf = new BoundField();
bf.DataField = fields[i];
bf.HeaderText = headTexts[i];
gvw.Columns.Add(bf);
}
}
else
{
gvw.AutoGenerateColumns = true;
} SetStype(gvw);
gvw.DataSource = dataList;
gvw.DataBind(); ExportToExcel(gvw,title);
}
/// <summary>
/// 导出数据到Excel
/// </summary>
/// <param name="DataList">IList Data</param>
/// <param name="Fields">要导出的字段</param>
/// <param name="HeadName">字段对应显示的名称</param>
public static void ExportToExcel(IList dataList, string[] fields, string[] headTexts)
{
ExportToExcel(dataList, fields, headTexts, string.Empty);
} /// <summary>
/// 设置样式
/// </summary>
/// <param name="gvw"></param>
private static void SetStype(GridView gvw)
{
gvw.Font.Name = "Verdana";
gvw.BorderStyle = System.Web.UI.WebControls.BorderStyle.Solid;
gvw.HeaderStyle.BackColor = System.Drawing.Color.LightCyan;
gvw.HeaderStyle.ForeColor = System.Drawing.Color.Black;
gvw.HeaderStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;
gvw.HeaderStyle.Wrap = false;
gvw.HeaderStyle.Font.Bold = true;
gvw.HeaderStyle.Font.Size = 10;
gvw.RowStyle.Font.Size = 10;
}
/// <summary>
/// 导出GridView中的数据到Excel
/// </summary>
/// <param name="gvw"></param>
/// <param name="DataList"></param>
private static void ExportToExcel(GridView gvw,string title)
{
string fileName;
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
fileName = string.Format("Export-File {0:yyyy-MM-dd_HH_mm}.xls", DateTime.Now);
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
StringWriter tw = new System.IO.StringWriter();
HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
gvw.RenderControl(hw);
if (!string.IsNullOrEmpty(title))
{
HttpContext.Current.Response.Write("<b><center><font size=3 face=Verdana color=#0000FF>" +title +"</font></center></b>");
}
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.Close();
HttpContext.Current.Response.End(); gvw.Dispose();
tw.Dispose();
hw.Dispose();
gvw = null;
tw = null;
hw = null; }
}
*****************************调用***********************************************************
List <Order> orders=this. GetOrders()
ExportHelper.ExportToExcel(orders,
new string[] { "OrderNo", "CustomerNo", "UserNo", "ModelNo","Quantity","Price","Amount","OrderDate" },
new string[] { "订单","客户代码","用户代码","型号","数量","价格","金额","订货日期"});
}
http://wenku.baidu.com/view/80cfa06925c52cc58bd6beb7.html
很好的例子,或许可以帮帮你
{
HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
string colHeaders = "", ls_item = "";
int i = 0; //定义表对象与行对像,同时用DataSet对其值进行初始化
DataTable dt = ds.Tables[0];
DataRow[] myRow = dt.Select("");
// typeid=="1"时导出为Excel格式文件;typeid=="2"时导出为XML格式文件
if (typeid == "1")
{
//取得数据表各列标题,各标题之间以\t分割,最后一个列标题后加回车符
for (i = 0; i < dt.Columns.Count - 1; i++)
{
colHeaders += dt.Columns[i].Caption.ToString() + "\t";
//colHeaders +=dt.Columns[i].Caption.ToString() +"\n";
}
colHeaders += dt.Columns[i].Caption.ToString() + "\n";
//向HTTP输出流中写入取得的数据信息
resp.Write(colHeaders);
//逐行处理数据
foreach (DataRow row in myRow)
{
//在当前行中,逐列获得数据,数据之间以\t分割,结束时加回车符\n
for (i = 0; i < dt.Columns.Count - 1; i++)
{
ls_item += row[i].ToString() + "\t";
//ls_item += row[i].ToString() +"\n";
}
ls_item += row[i].ToString() + "\n";
//当前行数据写入HTTP输出流,并且置空ls_item以便下行数据
resp.Write(ls_item);
ls_item = "";
}
}
else
{
if (typeid == "2")
{
//从DataSet中直接导出XML数据并且写到HTTP输出流中
resp.Write(ds.GetXml());
}
}
//写缓冲区中的数据到HTTP头文件中
resp.End();
}
oframe.open( "excel文件地址", true );
接着将后台的datalist写入excel具体搜索dsoframer用法会找到的啊
ApplicationClass excel;
_Workbook xBk;
_Worksheet xSt;
excel = new ApplicationClass();
xBk = excel.Workbooks.Add(true);
xSt = (_Worksheet)xBk.ActiveSheet;
excel.Cells[1, 1] = "aaa";赋值给第一行第一列
oAccess.Visible = false;
try
{
//ACCESS9:
oAccess.OpenCurrentDatabase("d:\\测试.mdb",false,"");
//导入access
oAccess.DoCmd.TransferSpreadsheet(Access.AcDataTransferType.acExport,Access.AcSpreadSheetType.acSpreadsheetTypeExcel9,"测试","d:\\测试.xls",true,null,null);
//导入txt
// oAccess.DoCmd.TransferText(Access.AcTextTransferType.acExportDelim,"","测试","d:\\测试.txt",true,"",0);
oAccess.CloseCurrentDatabase();
oAccess.DoCmd.Quit(Access.AcQuitOption.acQuitSaveNone);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oAccess);
oAccess = null;
MessageBox.Show("导入成功");
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
GC.Collect();
}
或使用二维数组绑定range
string[,] arr= new string[row,colCount];
Excel.Range range = (Excel.Range)workSheet.Cells[top,left];
range = range.get_Resize(row,colCount);
range.Value = arr;
或gridview等控件导出到excel
这个里面Excel.Application m_objExcel = new Excel.Application();
的Excel为什么不能够被识别?是不是需要加什么using????
Excel.Range range = (Excel.Range)workSheet.Cells[top,left];
这个Excel也是不能够被识别的啊
导出的Excel还具有gridview的样式,需要的是没有样式的
{
System.Web.HttpResponse resp = System.Web.HttpContext.Current.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
DateTime NowDate = DateTime.Now;
string OrderDate = NowDate.ToString("yyyyMM");
string FileName = "审批订单" + OrderDate + ".xls";
string FileNameXML = "审批订单" + OrderDate + ".xml";
if (typeid == "1") resp.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8)); //inline,attachment
else
resp.AppendHeader("Content-Disposition ", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(FileNameXML, System.Text.Encoding.UTF8));// + FileName); Response.ContentType = "application/ms-excel ";
string colHeaders = " ", ls_item = " ";
int i = 0; //定义表对象与行对像,同时用DataSet对其值进行初始化
DataTable dt = ds.Tables[0];
DataRow[] myRow = dt.Select(" ");
// typeid== "1 "时导出为EXCEL格式文件;typeid== "2 "时导出为XML格式文件
if (typeid == "1")
{
//取得数据表各列标题,各标题之间以\t分割,最后一个列标题后加回车符
for (i = 0; i < dt.Columns.Count; i++)
{
colHeaders += dt.Columns[i].Caption.ToString() + "\t ";
} colHeaders += "\n ";
//向HTTP输出流中写入取得的数据信息
resp.Write(colHeaders);
//逐行处理数据
foreach (DataRow row in myRow)
{
//在当前行中,逐列获得数据,数据之间以\t分割,结束时加回车符\n
for (i = 0; i < row.ItemArray.Length - 1; i++)
{
ls_item += row[i].ToString() + "\t ";
}
ls_item += row[i].ToString() + "\n ";
//当前行数据写入HTTP输出流,并且置空ls_item以便下行数据
resp.Write(ls_item);
ls_item = " ";
}
}
else
{
if (typeid == "2 ")
{
//从DataSet中直接导出XML数据并且写到HTTP输出流中
resp.Write(ds.GetXml());
}
} if (ds.Tables.Count > 0)
ds.Tables.Remove(ds.Tables[0]); //写缓冲区中的数据到HTTP头文件中
resp.End(); }//这个我用过 能到处来 但是导出的是cvs文件 可以看 但是导入其他系统有点麻烦 可以参考
我就直接贴我的代码了Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
Dim Strformat As String
Dim Fname As String
Select Case Dlist_format.SelectedIndex
Case 0
Fname = "attachment;filename=Export.xls"
Strformat = "application/vnd.ms-excel"
Case 1
Fname = "attachment;filename=Export.doc"
Strformat = "application/msword"
Case 2
Fname = "attachment;filename=Export.html"
Strformat = "text/html"
End Select
'2008-09-17 Dingminghua
Dim dt As New DataTable
Err = "SearchLog->Button5_Click"
If ViewState("StrSql") Is Nothing Then
ClientScript.RegisterStartupScript(Me.GetType, "Menu", "<script>alert('请先进行查询!');</script>")
Exit Sub
End If
ExecuteNonquery(ViewState("StrSql"), Err, dt)
Log_Export(dt, Strformat, Fname)
End Sub'如果不指定列名则以数据集的列为准
Private Sub Log_Export(ByVal tb As DataTable, ByVal Str As String, ByVal Filename As String)
Dim dgrid As System.Web.UI.WebControls.DataGrid = Nothing
Dim Context As System.Web.HttpContext = System.Web.HttpContext.Current
Dim StrOur As System.IO.StringWriter = Nothing
Dim htmlWriter As System.Web.UI.HtmlTextWriter = Nothing
If Not IsNothing(tb) Then
Context.Response.ContentType = Str
Context.Response.AppendHeader("content-disposition", Filename)
Context.Response.ContentEncoding = System.Text.Encoding.Default
Context.Response.Charset = ""
StrOur = New IO.StringWriter
htmlWriter = New System.Web.UI.HtmlTextWriter(StrOur)
dgrid = New DataGrid
tb.Columns(0).ColumnName = "时间日期"
tb.Columns(1).ColumnName = "Ip地址"
tb.Columns(2).ColumnName = "用户名"
tb.Columns(3).ColumnName = "操作类型"
tb.Columns(4).ColumnName = "具体操作"
tb.Columns(5).ColumnName = "结果"
tb.Columns(6).ColumnName = "备注"
'tb.Columns(7).ColumnName = "序号"
dgrid.DataSource = tb
dgrid.AllowPaging = False
dgrid.DataBind()
'dgrid.Columns(0).HeaderText = "时间"
dgrid.RenderControl(htmlWriter)
Context.Response.Write(StrOur.ToString)
Context.Response.Flush()
Context.Response.End() dgrid.Dispose()
StrOur.Dispose()
End If
End Sub