如何将dataset 导出 excel(指定导出列)

[dl_wang]

dataset 中 的表 table1 表字段： a ,b , c

现在只想把 a b 导出 excel。怎么实现？

[cpp2017]

绑定到一个gridview,然后下载，只绑定两列．

[cpp2017]

导出的代码：

public static void ExportExcel(GridView rp, string strFileName)
		{
		 
			strFileName	= System.Web.HttpUtility.UrlEncode(strFileName,System.Text.Encoding.UTF8);
			
			 
			 
			System.Web.HttpContext.Current.Response.Clear(); 
			System.Web.HttpContext.Current.Response.Buffer = true; 
			System.Web.HttpContext.Current.Response.Charset = "gb2312";
			System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "online; filename=" + strFileName + ".xls");
			System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
			System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";
			 
			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);
		 
			rp.RenderControl(oHtmlTextWriter); 
		 
			rp = null;
			System.Web.HttpContext.Current.Response.Write(oStringWriter.ToString().Replace("<td","<td STYLE='MSO-NUMBER-FORMAT:\\@'"));
			System.Web.HttpContext.Current.Response.Buffer = false; 
			System.Web.HttpContext.Current.Response.End();
		}

[liaoyukun111]

你注意下表头
DataTable dt = new DataTable();
DataTable dt2 = new DataTable();
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
if (i == 0)
{
dt.Columns.Add("准考证号", typeof(System.String));
dt2.Columns.Add("准考证号", typeof(System.String));

}
else
{
dt.Columns.Add(dataGridView1.Columns[i].HeaderText, typeof(System.String));
dt2.Columns.Add(dataGridView1.Columns[i].HeaderText, typeof(System.String));
}
}

/////这是打印分数条的格式
DataRow dr;
for (int r = 0; r < dataGridView1.SelectedRows.Count; r++)
{
dr = dt.NewRow();
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
dr[i] = dataGridView1.SelectedRows[r].Cells[i].Value.ToString();
}
dt.Rows.Add(dr);
}
DataRow dr2;
for (int i = dt.Rows.Count - 1; i >= 0; i--)
{
dr2 = dt.NewRow();
for (int j = 0; j < dt.Columns.Count; j++)
{
string ss = dt.Rows[i][j].ToString();
dr2[j] = dt.Rows[i][j];

}
dt2.Rows.Add(dr2.ItemArray);//不加ItemArray报错
}
//放到G2中再放入EXCEL
// MessageBox.Show(dataGridView1.CurrentCell.RowIndex.ToString());
DataTable dts = new DataTable();
Excel.Application excel = new Excel.Application();
excel.Application.Workbooks.Add(true);
//// excel.Columns.EntireColumn.AutoFit();// 列宽自适应

excel.Visible = true;
//生成字段名称
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
}

for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
if (i == 0)
{
dts.Columns.Add("准考证号", typeof(System.String));

}
else
{
dts.Columns.Add(dataGridView1.Columns[i].HeaderText, typeof(System.String));
}
}

for (int i = 0; i < dt2.Rows.Count; i++)
{

for (int j = 0; j < dt2.Columns.Count; j++)
{
excel.Cells[2 + i, 1 + j] = dt2.Rows[i][j].ToString();
}
}

excel.Columns.EntireColumn.AutoFit();//写在前面还有点问题

[andy796746]

[Quote=引用 2 楼 cpp2017 的回复:]
导出的代码：

C# codepublicstaticvoid ExportExcel(GridView rp,string strFileName)
{

strFileName= System.Web.HttpUtility.UrlEncode(strFileName,System.Text.Encoding.UTF8);



System.Web.HttpContext.Current.Response.Clear();
System.Web.HttpContext.Current.Response.Buffer=true;
System.Web.HttpContext.Current.Response.Charset="gb2312";
System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition","online; filename="+ strFileName+".xls");
System.Web.HttpContext.Current.Response.ContentEncoding= System.Text.Encoding.GetEncoding("gb2312");
System.Web.HttpContext.Current.Response.ContentType="application/ms-excel";

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);

rp.RenderControl(oHtmlTextWriter);

rp=null;
System.Web.HttpContext.Current.Response.Write(oStringWriter.ToString().Replace("<td","<td STYLE='MSO-NUMBER-FORMAT:\\@'"));
System.Web.HttpContext.Current.Response.Buffer=false;
System.Web.HttpContext.Current.Response.End();
}
[/Quote]

[wuyq11]

gridview只绑定两列导出数据，或用模板
public void INSERT_Excel(DataView dv,string strPath,string Name)
{
string s="";
Excel.Application app=new Application();
Excel._Workbook book;
Excel._Worksheet sheet;
book=(Excel._Workbook)app.Workbooks.Open(strPath,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,
Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
sheet=(Excel._Worksheet)book.Sheets[1];
int j=dvs.Count;
Excel.Range ran1=app.ActiveCell;
ran1=sheet.get_Range(sheet.Cells[1,1],sheet.Cells[1,9]);
ran1.Value2=Name;
for(int i=0;i<dvs.Count;i++)
{
try
{
s=Convert.ToString(i);
sheet.Cells[i+1,"A"]=dv[i]["a"].ToString().Trim();
sheet.Cells[i+1,"B"]=dv[i]["b"].ToString().Trim();
}
catch(Exception ex)
{
HttpContext.Current.Response.Write("<script language='javascript'>alert('"+ex.Message+"')</script>");
book.Close(null,null,null);
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
sheet=null;
book=null;
app=null;
GC.Collect();
HttpContext.Current.Response.Write("<script language='javascript'>alert('导出失败！')</script>");
return;
}

}
book.Save();
book.Close(null,null,null);
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
sheet=null;
book=null;
app=null;
GC.Collect();
GC.Collect();
GC.Collect();
HttpContext.Current.Response.Write("<script language='javascript'>alert('导出成功！')</script>");
HttpContext.Current.Response.Write("<script language='javascript'>window.open('../Template_temp/A.xls','_bank')</script>");
}



protected void Btn_ExportClick(object sender, EventArgs e)
{
string style = @"<style> .text { mso-number-format:\@; } </script> ";
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls");
Response.ContentType = "application/excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
gv.RenderControl(htw);
Response.Write(style);
Response.Write(sw.ToString());
Response.End();

}
public override void VerifyRenderingInServerForm(Control control)

{

}

[dl_wang]

只在gridview 中绑定 两列，导出来的还是3列

[liaoyukun111]

有罪我的,发个winform 的
遍历DATASET 当它为第三列时不要
组成一个新的DATATABLE 再遍历它放入EXECL,当然两个遍历也可以简为一个

[dl_wang]

只在gridview 中绑定 两列，导出来的还是3列

[jinjazz]

导出excel可以不用excel组件，参考

http://blog.csdn.net/jinjazz/archive/2008/08/01/2753869.aspx