如何将DATAGRID的数据导入到Excel中

string aaa=this.saveFileDialog1.FileName.Trim(); string strLine;
System.IO.FileStream objFileStream;
System.IO.StreamWriter objStreamWriter;

objFileStream = new FileStream(aaa, FileMode.OpenOrCreate, FileAccess.Write);
objStreamWriter = new StreamWriter(objFileStream,System.Text.Encoding.Default);

strLine = "";

//添加标题.

for(int i=0;i<this.dataGridTableStyle1.GridColumnStyles.Count;i++)
{
strLine = strLine +this.dataGridTableStyle1.GridColumnStyles[i].HeaderText+Convert.ToChar((9)); }

objStreamWriter.WriteLine(strLine);
strLine="";
//添加内容
for (int j=0;j<=dataSet1.Tables[this.dataGrid1.DataSource.ToString()].Rows.Count-1;j++)
{
for(int l=0;l<dataSet1.Tables[this.dataGrid1.DataSource.ToString()].Columns.Count;l++)
{
strLine = strLine +dataSet1.Tables[this.dataGrid1.DataSource.ToString()].Rows[j][l].ToString()+Convert.ToChar((9));
}
objStreamWriter.WriteLine(strLine);
strLine="";
} objStreamWriter.Close();
objFileStream.Close();

http://www.syncfusion.com/FAQ/WinForms/FAQ_c44c.asp

Please add OWC as a reference first,and then using OWC;

private void ExportToExcel(SpreadsheetClass xlsheet1,DataTable dt)
{
xlsheet1=new SpreadsheetClass();
dt=(DataTable)Session["result"];

int row=2; //输出字段
for(int i=0;i<dt.Columns.Count-1;i++)
{
xlsheet1.ActiveSheet.Cells[1,i+1] = dt.Columns[i].ToString();
}
//输出数据
int j; foreach(DataRow dr in dt.Rows)
{
for(j=0;j<dt.Columns.Count-1;j++)
{
xlsheet1.ActiveSheet.Cells[row,j+1]=dr[j].ToString();
}
j+=1;
row++;
}
dt.Clear();
dt.Dispose();
try
{
xlsheet1.ActiveSheet.Export(Server.MapPath(".")+"/result.xls",SheetExportActionEnum.ssExportActionNone);
Page.RegisterClientScriptBlock("tips","<script language=javascript>alert('导出成功')</script>");
}
catch(System.Runtime.InteropServices.COMException exp)
{
Page.RegisterClientScriptBlock("tips","<script language=javascript>alert('"+exp.StackTrace+"')</script>");
return;
}
}

YoueDataGrid.DataSource=DataAccess.GetZuanJingDuiJingChiAndAllXiangMuZuDataBySgdw(DateTime.Parse(sdate),DateTime.Parse(edate),int.Parse(sgdw));
YoueDataGrid.DataBind();
Response.Clear();
Response.Buffer=true;
Response.ContentType="application/vnd.ms-excel";
Response.Charset="";
this.EnableViewState=false;StringWriter sw=new StringWriter();
HtmlTextWriter htw=new HtmlTextWriter(sw);
YoueDataGrid.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();

public void WriteExcel(string strSQL,string strSerMap)
{
SqlConnection conn = new SqlConnection(Sys.strSmsAppDBConn);
if(conn.State==ConnectionState.Closed) conn.Open();
SqlCommand sql = new SqlCommand(strSQL,conn);
SpreadsheetClass xlsheet = new SpreadsheetClass();
SqlDataReader reader = sql.ExecuteReader();
int numbercols = reader.FieldCount;
int row=1;
while (reader.Read())
{
for (int i=0;i<numbercols;i++)
{
xlsheet.ActiveSheet.Cells[row,i+1] = reader.GetValue(i).ToString();
}
row++;
}
reader.Close();
xlsheet.ActiveSheet.Export( strSerMap,OWC.SheetExportActionEnum.ssExportActionNone);
}

将你保存click 按钮的事件写成下面的方式就可以保存出来了（注意改一下gridname)
   但是这种办法只能保存没有排序功能的datagrid
   对于有排序功能的，我现在的做法是同时生成一个相同的不排序的grid （隐藏），在save的时候获取不排序的datagrid中的数据就可以了 public void btnsave_Click(object sender , System.EventArgs e)
           {
string strFileName = "test11";
Response.Clear();
Response.Buffer= true;
Response.Charset="GB2312";
Response.AppendHeader("Content-Disposition","attachment;filename="+ strFileName +".xls");
Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
this.EnableViewState = false;
System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN",true);
System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
                             // GridName -- 需要保存数据的Grid名字 this.GridName.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}

自己回答:
把DataTable按照模板输出到Excel
/// <summary>
/// 向ＥＸＣＥＬ表格里插入数据库记录
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnExcel_Click(object sender, System.EventArgs e)
{
//查询数据库内容判断 AccFlage的值，如果其值是１则用'√'替换，如果是０则为空
string query_sql = "select AccDate,No,Abr_AccId,Abr_Content,Abr_Person,(select case when AccFlag = 1 then '√' when AccFlag = 0 then ''else ''end as 'AccFlag'),DbtAmt,CrdAmt,EndAmt from accounttype where accno = '" + cboNo.SelectedValue + "'and AccDate between '" + dateStart.Value.Date + "' and '" + dateEnd.Value.Date + "'";
SqlConnection con = new SqlConnection(ConStr);
SqlDataAdapter da = new SqlDataAdapter(query_sql,con);
DataSet ds = new DataSet();
da.Fill(ds,"accounttype"); string query = "select endamt from accounttype where id = '" + this.txtDate.Text + "'";
SqlDataAdapter daNow = new SqlDataAdapter(query,con);
DataSet dsNow = new DataSet();
daNow.Fill(dsNow,"accounttype");
txtNow.DataBindings.Clear();
txtNow.DataBindings.Add("Text",dsNow,"AccountType.EndAmt"); string str;
if(this.radioMoney.Checked)
{
str = "0";
}
else
{
str = "1";
} if(str == "0")
{
string query_money = "select * from money where MoneyAttr = 2";
SqlDataAdapter damoney = new SqlDataAdapter(query_money,con);
DataSet dsmoney = new DataSet();
damoney.Fill(dsmoney,"money");
txtMoney.DataBindings.Clear();
txtMoney.DataBindings.Add("Text",dsmoney,"Money.Money");
}
else
{
string query_money = "select * from money where MoneyAttr = 3";
SqlDataAdapter damoney = new SqlDataAdapter(query_money,con);
DataSet dsmoney = new DataSet();
damoney.Fill(dsmoney,"money");
txtMoney.DataBindings.Clear();
txtMoney.DataBindings.Add("Text",dsmoney,"Money.Money");
}

string filename="";
//将模板文件复制到一个新文件中
SaveFileDialog mySave = new SaveFileDialog();
mySave.Filter="Excel文件(*.XLS)|*.xls|所有文件(*.*)|*.*";
if(mySave.ShowDialog()!=DialogResult.OK)
{
return;
}
else
{
filename = mySave.FileName;
//将模板文件copy到新位置，建议实际开发时用相对路径，如Application.StartupPath.Trim()+"\\report\\normal.xls"

FileInfo mode=new FileInfo(Application.StartupPath.Trim() + @"\Report\CashReport.xls");
try
{
mode.CopyTo(filename,true);
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
//打开复制后的文件
object missing = Missing.Value;
Excel.Application myExcel = new Excel.Application ( );
//打开新文件
myExcel.Application.Workbooks.Open(filename,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing);
//将Excel显示出来
myExcel.Visible=true;
//将列标题和实际内容选中
Excel.Workbook myBook = myExcel.Workbooks[1];
Excel.Worksheet mySheet = (Excel.Worksheet)myBook.Worksheets[1];
mySheet.Cells[2,5] = this.dateStart.Value.Date;
mySheet.Cells[2,8] = this.dateEnd.Value.Date;
mySheet.Cells[1,1] = "安徽省建行现金出纳统计表------" + this.cboNo.SelectedValue;
//判断是否有余额,如果没有就把初期额添加入EXCEL中
if(this.txtDate.Text == "")
{
mySheet.Cells[5,9] = txtMoney.Text.ToString();
}
else
{
mySheet.Cells[5,9] = txtNow.Text.ToString(); }
//向ＥＸＣＥＬ里插记录

int HeadLines=5;
int j=0; for(int r = 0;r<ds.Tables[0].Rows.Count;r++)
{
if (((r+1) % 22)==0)
{
for(int i = 0;i<ds.Tables[0].Columns.Count;i++)
{
mySheet.Cells[j*2+r+HeadLines+1,i+1] = ds.Tables[0].Rows[r][i];
}
mySheet.Cells[j*2+r+HeadLines+2,4] = "过次页";
mySheet.Cells[j*2+r+HeadLines+3,4] = "呈上页";
mySheet.Cells[j*2+r+HeadLines+3,9] = ds.Tables[0].Rows[r][8];;
j++;
　　　　　　　　}
else
{
for(int i = 0;i<ds.Tables[0].Columns.Count;i++)
{
mySheet.Cells[j*2+r+HeadLines+1,i+1] = ds.Tables[0].Rows[r][i];
}
}
}
}
感谢楼上的，我写的希望对大家有所帮助

调试易

如何将DATAGRID的数据导入到Excel中

解决方案 »