数据表导出到EXCEL.docx
《数据表导出到EXCEL.docx》由会员分享，可在线阅读，更多相关《数据表导出到EXCEL.docx（10页珍藏版）》请在冰点文库上搜索。
数据表导出到EXCEL
数据表导出到EXCEL
我的代码是从表中导出到excel中，你自己改一个就可以用。
usingSystem;
usingSystem.Collections.Generic;
usingSystem.ComponentModel;
usingSystem.Data;
usingSystem.Drawing;
usingSystem.Text;
usingSystem.Windows.Forms;
usingSystem.Data.SqlClient;
namespaceT1TOOL
{
publicpartialclasszhuye:
Form
{
publiczhuye（）
{
InitializeComponent（）;
}
privatevoidc2_CheckedChanged（objectsender,EventArgse）
{
if（c2.Checked）
{
c1.Checked=false;
}
}
privatevoidc1_CheckedChanged（objectsender,EventArgse）
{
if（c1.Checked）
{
c2.Checked=false;
}
}
privatevoidb1_Click（objectsender,EventArgse）
{
DataTabletable=newDataTable（）;
SqlConnectionsqlcon=sql.getcon（）;
SqlDataAdaptersqlda=newSqlDataAdapter（"select*fromfitemss00",sqlcon）;
DataSetmyds=newDataSet（）;
sqlda.Fill（table）;
Excel.Applicationexcel=newExcel.Application（）;
excel.Workbooks.Add（true）;
excel.Visible=true;
intcolindext=0;//excel的列从0开始
introwindext=1;//excel的行从2开始，第一行是表名，第二行是列名，第三行才是数据
//Excel.Rangerange=excel.get_Range（excel.Cells[1,1],excel.Cells[1,table.Columns.Count]）;
//range.MergeCells=true;//表名是否跨列显示,从单元格excel.Cells[1,1]到单元格excel.Cells[1,table.Columns.Count]
//Excel.Rangerange1=excel.get_Range（excel.Cells[3,1],excel.Cells[table.Rows.Count+3,10]）;
//range1.NumberFormat="@";
//excel.ActiveCell.FormulaR1C1="fitemss00";//表名
//excel.ActiveCell.Font.Size=18;//表名的字体
//excel.ActiveCell.Font.Bold=true;//粗体
foreach（DataColumncolintable.Columns）//将列的名称输入到excel
{
colindext=colindext+1;
excel.Cells[1,colindext]=col.ColumnName;
}
for（introw=0;row{
//excel的行
rowindext=rowindext+1;
//excel的列
colindext=0;
for（intcol=0;col{
colindext=colindext+1;
excel.Cells[rowindext,colindext]=table.Rows[row][col];
}
}
//Excel.Rangerange4=excel.get_Range（excel.Cells[2,1],excel.Cells[table.Rows.Count+3,table.Columns.Count]）;
//range4.Cells.Borders.LineStyle=1;//加边框
//excel.Cells.EntireColumn.AutoFit（）;//自动调整列宽，使数据能正常显示
//excel.Cells.HorizontalAlignment=Excel.Constants.xlCenter;//居中显示
//ObjectoMissing=System.Reflection.Missing.Value;
//excel.Worksheets.PrintOut（oMissing,oMissing,oMissing,true,oMissing,oMissing,oMissing,oMissing）;
//excel.OlePropertyGet（"ActiveWorkBook"）.OlePropertyGet（"ActiveSheet"）.OleFunction（"PrintOut"）;
}
}
}
#region导出为Excel
publicoverridevoidVerifyRenderingInServerForm（Controlcontrol）
{
//ConfirmsthatanHtmlFormcontrolisrenderedfor
}
privatevoidToExcel（Controlctl,stringFileName）
{
HttpContext.Current.Response.Charset="UTF-8";
HttpContext.Current.Response.ContentEncoding=System.Text.Encoding.UTF8;
HttpContext.Current.Response.ContentType="application/ms-excel";
HttpContext.Current.Response.AppendHeader（"Content-Disposition","attachment;filename="+""+FileName）;
ctl.Page.EnableViewState=false;
System.IO.StringWritertw=newSystem.IO.StringWriter（）;
HtmlTextWriterhw=newHtmlTextWriter（tw）;
ctl.RenderControl（hw）;
HttpContext.Current.Response.Write（tw.ToString（））;
HttpContext.Current.Response.End（）;
}
privatevoidtoExcelClk（）
{
gvSysUser.AllowPaging=false;
gvSysUser.AllowSorting=false;
gvSysUser.DataBind（）;
ToExcel（gvSysUser,"OFS_Data.xls"）;
gvSysUser.AllowPaging=true;
gvSysUser.AllowSorting=true;
gvSysUser.DataBind（）;
}
#endregion
protectedvoidButton1_Click（objectsender,EventArgse）
{
ExcelOut（this.GridView1）;
}
publicvoidExcelOut（GridViewgv）
{
if（gv.Rows.Count>0）
{
Response.Clear（）;
Response.ClearContent（）;
Response.AddHeader（"Content-Disposition","attachment;filename="+DateTime.Now.ToString（"_yyyyMMdd_HHmmss"）+".xls"）;
Response.ContentEncoding=System.Text.Encoding.UTF8;
Response.ContentType="application/ms-excel";
StringWritersw=newStringWriter（）;
HtmlTextWriterhtw=newHtmlTextWriter（sw）;
gv.RenderControl（htw）;
Response.Write（sw.ToString（））;
Response.Flush（）;
Response.End（）;
}
else
{
Response.Write（"没有数据"）;
}
}
publicoverridevoidVerifyRenderingInServerForm（Controlcontrol）
{
//base.VerifyRenderingInServerForm（control）;
}
{
StreamWritersw=newStreamWriter（"D:
\\人员详细.xls",false,Encoding.Default）;
stringstr="";
try
{
for（inti=0;i{
if（i>0）
{
str+="\t";//"\t":
水平制表符相当于按下“tab”键！
}
str+=dataGridView1.Columns[i].HeaderText;
}
sw.Write（str）;
for（intj=0;j{
stringtempStr="";
for（intk=0;k{
if（k>0）
{
tempStr+="\t";
tempStr+=dataGridView1.Rows[j].Cells[k].Value.ToString（）;
}
else
{
tempStr+=dataGridView1.Rows[j].Cells[k].Value.ToString（）;
}
}
sw.WriteLine（tempStr）;
}
sw.Close（）;
}
catch
{
MessageBox.Show（e.ToString（））;
}
finally
{
sw.Close（）;
sw.Dispose（）;
}
}
publicvoidExcelOut（GridViewgv）
{//导出Excel表的方法
if（gv.Rows.Count>0）
{//有数据行
Response.Clear（）;
Response.ClearContent（）;
Response.AddHeader（"Content-Disposition","attachment;filename="+DateTime.Now.ToString（"yyyyMMddHHmmss"）+".xls"）;//以系统时间设置为文件名
Response.ContentEncoding=System.Text.Encoding.UTF8;//UTF8编码
Response.ContentType="application/ms-excel";//文件类型
StringWritersw=newStringWriter（）;
HtmlTextWriterhtw=newHtmlTextWriter（sw）;
gv.RenderControl（htw）;
Response.Write（sw.ToString（））;
Response.Flush（）;
Response.End（）;//结束
}
else
{
Response.Write（"没有数据记录"）;
}
}
{//导出按钮事件
ExcelOut（GridView1）;//调用方法
}
{
HttpContext.Current.Response.Charset="UTF-8";
HttpContext.Current.Response.ContentEncoding=System.Text.Encoding.Default;
HttpContext.Current.Response.ContentType="application/ms-excel";
HttpContext.Current.Response.AppendHeader（"Content-Disposition","attachment;filename="+""+FileName+".xls"）;
ctl.Page.EnableViewState=false;
System.IO.StringWritertw=newSystem.IO.StringWriter（）;
System.Web.UI.HtmlTextWriterhw=newSystem.Web.UI.HtmlTextWriter（tw）;
ctl.RenderControl（hw）;
HttpContext.Current.Response.Write（tw.ToString（））;
HttpContext.Current.Response.End（）;
}//导入excel
publicoverridevoidVerifyRenderingInServerForm（Controlcontrol）
{
//输出Excel文件用
}
{
ExcelOut（this.GridView1）;
}
publicvoidExcelOut（GridViewgv）
{
if（gv.Rows.Count>0）
{
Response.Clear（）;
Response.ClearContent（）;
Response.AddHeader（"Content-Disposition","attachment;filename="+DateTime.Now.ToString（"_yyyyMMdd_HHmmss"）+".xls"）;
Response.ContentEncoding=System.Text.Encoding.UTF8;
Response.ContentType="application/ms-excel";
StringWritersw=newStringWriter（）;
HtmlTextWriterhtw=newHtmlTextWriter（sw）;
gv.RenderControl（htw）;
Response.Write（sw.ToString（））;
Response.Flush（）;
Response.End（）;
}
else
{
Response.Write（"没有数据"）;
}
}
publicoverridevoidVerifyRenderingInServerForm（Controlcontrol）
{
//base.VerifyRenderingInServerForm（control）;
}
Response.Clear（）;
Response.Charset="UTF-8";
Response.AddHeader（"Content-Disposition","inline;filename="+tick1+".xls"）;//inline（在线打开）attachment（下载）
Response.AddHeader（"Content-Length",fleInfo.Length.ToString（））;
Response.ContentType="application/vnd.ms-excel";
Response.WriteFile（fleInfo.FullName）;
Response.End（）;
{
if（string.IsNullOrEmpty（FileName））
{
stringfilename=DateTime.Now.Ticks.ToString（）+".xls";
FileName=this.Parent.Page.Server.MapPath（"~/Temp/"+filename）;
}
//去除分页、排序
this.AllowPaging=false;
this.AllowSorting=false;
this.AutoDataBind（）;
//生成html
this.DisableControls（this）;
HttpContext.Current.Response.Charset="UTF-8";
HttpContext.Current.Response.ContentEncoding=System.Text.Encoding.UTF8;
HttpContext.Current.Response.ContentType="application/ms-excel";
HttpContext.Current.Response.AppendHeader（"Content-Disposition","attachment;filename="+""+FileName）;
this.Page.EnableViewState=false;
System.IO.StringWritertw=newSystem.IO.StringWriter（）;
HtmlTextWriterhw=newHtmlTextWriter（tw）;
this.RenderControl（hw）;
stringstr=tw.ToString（）;
str=（Regex.Replace（str,@"（>）|","",RegexOptions.IgnoreCase））;
HttpContext.Current.Response.Write（str）;
HttpContext.Current.Response.End（）;
//还原分页、排序
this.AllowPaging=true;
this.AllowSorting=true;
this.AutoDataBind（）;
}
HttpContext.Current.Response.Charset="UTF-8";
HttpContext.Current.Response.ContentEncoding=System.Text.Encoding.UTF8;
HttpContext.Current.Response.ContentType="application/ms-excel";
HttpContext.Current.Response.AppendHeader（"Content-Disposition","attachment;filename="+""+FileName）;
ctl.Page.EnableViewState=false;
System.IO.StringWritertw=newSystem.IO.StringWriter（）;
HtmlTextWriterhw=newHtmlTextWriter（tw）;
ctl.RenderControl（hw）;
HttpContext.Current.Response.Write（tw.ToString（））;
HttpContext.Current.Response.End（）;
升级会员 