英特尔®游戏设计大赛100美元现金周周送	专题改版：Java Web 专题

如何把DataGrid数据导入到Excel中去？

楼主wolfpzp（）2003-12-04 13:53:05 在 .NET技术 / C# 提问

在用C#开发的软件中（不是ASP）需要把DataGrid数据导入到Excel中，请问该如何做问题点数：50、回复次数：10Top

1 楼qqsyb（（一天到晚游泳的鱼））回复于 2003-12-04 14:09:38 得分 20

private void Button2_Click(object sender, System.EventArgs e)
{

#region 用例说明
/*
【输入】
sender:
e:
【输出】
无
【流程定义】
1、将生成的DataSet的数据导到指定的Execl文件里，并显示出来
【可选流程】

【问题】

*/
#endregion

Random rd=new Random(int.Parse(DateTime.Now.ToString("MMddhhmmss")));
string strFileName = "";
// string strFileName =DateTime.Now.ToString("yyyyMMdd")+DateTime.Now.Hour+DateTime.Now.Minute+DateTime.Now.Second+txtBeginTime.Text.Trim()+txtFinishTime.Text.Trim()+rd.Next(999999).ToString()+ ".XLS";

//Excel.Application excel= new Excel.ApplicationClass();//Excel.Application();
Excel.Application excel ;
//excel = new Excel.Application();
excel = new Excel.ApplicationClass();
Excel.WorkbookClass oWB;
Excel.Worksheet oSheet;
oWB = (Excel.WorkbookClass)(excel.Workbooks.Add(true));
oSheet = (Excel.Worksheet)oWB.ActiveSheet;

oSheet.Cells[1, 1] ="11111"; //合并项
oSheet.Cells[2, 1] = "2222";//合并项
oSheet.Cells[4, 1] = "话机号码" ;//合并项
oSheet.Cells[4, 2] = "月份" ;//合并项
oSheet.Cells[3, 3] = "通话总次数（次）";
oSheet.Cells[3, 7] = "通话总时长（分）";
oSheet.Cells[3,11] = "通话总金额（元）";
oSheet.Cells[4, 3] = "国际";
oSheet.Cells[4, 4] = "国内";
oSheet.Cells[4, 5] = "港澳台";
oSheet.Cells[4, 6] = "合计";
oSheet.Cells[4, 7] = "国际";
oSheet.Cells[4, 8] = "国内";
oSheet.Cells[4, 9] = "港澳台";
oSheet.Cells[4,10] = "合计";
oSheet.Cells[4,11] = "国际";
oSheet.Cells[4,12] = "国内";
oSheet.Cells[4,13] = "港澳台";
oSheet.Cells[4,14] = "合计";

oSheet.get_Range("A1","N4").Font.Size= 9 ;
oSheet.get_Range("A1","N4").Font.Bold = true;
oSheet.get_Range("A1","N4").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("A1","N4").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

//该范围内设置外面一层边框
oSheet.get_Range("A1","N4").BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin ,Excel.XlColorIndex.xlColorIndexAutomatic,Color.Black.ToArgb()) ;

//该范围内设置里面所有单元格的边框
oSheet.get_Range("A1","N4").Borders.Weight = Excel.XlBorderWeight.xlThin ;
oSheet.get_Range("A1","N4").Borders.Color = Color.Black.ToArgb() ;
//范围内单元格添加背景颜色
oSheet.get_Range("A1","N2").Cells.Interior.Color =Color.FromArgb(153,255,204).ToArgb();
oSheet.get_Range("A1","N2").Cells.Interior.Pattern = Excel.XlBackground.xlBackgroundAutomatic ;

oSheet.get_Range("A3","N4").Cells.Interior.Color = Color.LemonChiffon.ToArgb();
oSheet.get_Range("A3","N4").Cells.Interior.Pattern = Excel.XlBackground.xlBackgroundAutomatic ;

System.Data.DataTable dt = (System.Data.DataTable)Session["OPER_LOG"];
DataRow dr;
int count=dt.Rows.Count+5;
string strExcelLength = "N"+count.ToString().Trim();

int I,J;
for(I = 5;I< count;I++)
{
dr=dt.Rows[I-5];

for(J = 1;J<(dt.Columns.Count +1) ;J++)
{
oSheet.Cells[I,J] = dr[J-1].ToString().Trim() ;

}

}

oSheet.get_Range("A5",strExcelLength).Font.Size= 9 ;
oSheet.get_Range("A5",strExcelLength).Font.Bold = false;
oSheet.get_Range("A5",strExcelLength).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("A5",strExcelLength).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//该范围内设置外面一层边框
oSheet.get_Range("A5",strExcelLength).BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin ,Excel.XlColorIndex.xlColorIndexAutomatic,Color.Black.ToArgb()) ;

//该范围内设置里面所有单元格的边框
oSheet.get_Range("A5",strExcelLength).Borders.Weight = Excel.XlBorderWeight.xlThin ;
oSheet.get_Range("A5",strExcelLength).Borders.Color = Color.Black.ToArgb() ;
//范围内单元格添加背景颜色
oSheet.get_Range("A5",strExcelLength).Cells.Interior.Color = Color.PaleTurquoise.ToArgb();
oSheet.get_Range("A5",strExcelLength).Cells.Interior.Pattern = Excel.XlBackground.xlBackgroundAutomatic ;

oSheet.get_Range("A1","N1").Merge(true);
oSheet.get_Range("A2","N2").Merge(true);

oSheet.get_Range("A3","A4").Merge(true);
oSheet.get_Range("B3","B4").Merge(true);
oSheet.get_Range("C3","F3").Merge(true);
oSheet.get_Range("G3","J3").Merge(true);
oSheet.get_Range("K3","N3").Merge(true);

string path=Server.MapPath(".") + "\\..\\..\\excel-file\\" + strFileName;

// oWB.SaveAs(path,Missing.Value,"","",true,false,Excel.XlSaveAsAccessMode.xlNoChange,1,false,Missing.Value,Missing.Value);

//oWB.SaveCopyAs(path);

oWB.Close(false,null,null);
excel.Workbooks.Close();
excel.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject (excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oWB);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oSheet);

oSheet = null;
oWB= null;
excel = null;

GC.Collect();

string test="../../excel-file/" + strFileName;
Response.Redirect(test);
}Top

2 楼roczou（就爱没事不出门）回复于 2003-12-04 14:23:00 得分 2

在DataGrid.DataBind()下加入一下代码
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=exportfilename.xls");
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "UTF-8";
Response.ContentEncoding=System.Text.Encoding.Default;
this.EnableViewState= false;
System.IO.StringWriter tw = new System.IO.StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
DataGrid2.RenderControl(hw);
Response.Write(tw);
Response.End();Top

3 楼tangyanjun1（唐延军）回复于 2003-12-04 14:27:44 得分 2

给你一个例子吧。
ExcelExport.aspx

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="ExcelExport.aspx.vb"
Inherits="aspxWeb.mengxianhui.com.ExcelExport"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>ExcelExport</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">
<form id="Form1" method="post" runat="server">
<asp:datagrid id="DataGrid1" runat="server" CellPadding="4" BackColor="White"
BorderColor="#CC9966" BorderWidth="1px" BorderStyle="None" Width="100%" Height="100%"
Font-Size="9pt" Font-Names="宋体">
<SelectedItemStyle Font-Bold="True" ForeColor="#663399" BackColor="#FFCC66"></SelectedItemStyle>
<AlternatingItemStyle BackColor="#FFCC99"></AlternatingItemStyle>
<ItemStyle BorderWidth="2px" ForeColor="#330099" BorderStyle="Solid"
BorderColor="Black" BackColor="White"></ItemStyle>
<HeaderStyle Font-Bold="True" HorizontalAlign="Center" BorderWidth="2px"
ForeColor="#FFFFCC" BorderStyle="Solid" BorderColor="Black" BackColor="#990000"></HeaderStyle>
</asp:datagrid>
</form>
</body>
</HTML>

ExcelExport.aspx.vb

Public Class ExcelExport
Inherits System.Web.UI.Page
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
#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
'在此处放置初始化页的用户代码
' 定义是否是 SQL Server 数据库，这里为False
Dim blnIsSQLServer As System.Boolean = False
Dim strSQL As String
Dim objDataset As New DataSet()
Dim objConn As Object
Dim strCnn As String
If blnIsSQLServer Then
strCnn = "User ID=sa;Initial Catalog=Northwind;Data Source=.\NetSDK;"
objConn = New System.Data.SqlClient.SqlConnection(strCnn)
objConn.Open()
Dim objAdapter As New System.Data.SqlClient.SqlDataAdapter()
strSQL = "Select * from customers where country='USA'"
objAdapter.SelectCommand = New System.Data.SqlClient.SqlCommand(strSQL, objConn)
objAdapter.Fill(objDataset)
Else
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("Test.mdb")
objConn = New System.Data.OleDb.OleDbConnection(strCnn)
objConn.Open()
Dim objAdapter As New System.Data.OleDb.OleDbDataAdapter()
strSQL = "Select Top 10 Title From Document"
objAdapter.SelectCommand = New System.Data.OleDb.OleDbCommand(strSQL, objConn)
objAdapter.Fill(objDataset)
End If
Dim oView As New DataView(objDataset.Tables(0))
DataGrid1.DataSource = oView
DataGrid1.DataBind()
objConn.Close()
objConn.Dispose()
objConn = Nothing
If Request.QueryString("bExcel") = "1" Then
Response.ContentType = "application/vnd.ms-excel"
' 从Content-Type header中去除charset设置
Response.Charset = ""
' 关闭 ViewState
Me.EnableViewState = False
Dim tw As New System.IO.StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
' 获取control的HTML
DataGrid1.RenderControl(hw)
' 把HTML写回浏览器
Response.Write(tw.ToString())
Response.End()
End If
End Sub
End Class

Top

4 楼wolfpzp（）回复于 2003-12-04 14:34:01 得分 0

我编译的时候报错
显示“找不到类型或命名空间名称‘Excel’”错误Top

5 楼rottenapple（伪程序员）回复于 2003-12-04 14:36:33 得分 2

using Excel=Microsoft.Office.Interop.Excel;

需要这个组件,是office10.0里面的

最好可以做一个c#读写excel类,可以方便的导出导入Top

6 楼CMIC（大象）回复于 2003-12-04 14:45:46 得分 2

http://www.csdn.net/Develop/Read_Article.asp?Id=15544Top

7 楼wolfpzp（）回复于 2003-12-04 17:32:40 得分 0

Excel.Application excel ;
excel = new Excel.ApplicationClass();
Excel.WorkbookClass oWB;
Excel.Worksheet oSheet;
oWB = (Excel.WorkbookClass)(excel.Workbooks.Add(true));
oSheet = (Excel.Worksheet)oWB.ActiveSheet;

oWB.Close(false,path,null);
excel.Workbooks.Close();
excel.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject (excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oWB);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oSheet);

oSheet = null;
oWB= null;
excel = null;
按以上代码执行，能把数据导到Excel中去，但是如果程序没有关闭的话，该Excel文件就打不开
请问还有什么地方需要释放的？Top

8 楼tangyanjun1（唐延军）回复于 2003-12-05 14:56:55 得分 2

这个我建议把你操纵excel的代码放在
using{}里去，这样说不清楚的那些没释放的资源就会释放。Top

9 楼cheing（五代）回复于 2003-12-05 15:44:44 得分 2

roczou(乘浪破风) :
我想把它做成自动保存到excel中,要怎么实现?Top

10 楼panxf316（天外飞仙）回复于 2004-01-18 10:48:52 得分 18

用线程实现，然后关闭线程
Top