c#中datagrid数据导出execl 如何实现, c#中datagrid数据导出execl 另存为.xsl 或 .cvs格式,高手指点以下,谢谢! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 http://singlepine.cnblogs.com/articles/305843.html 可以考虑使用excel.dll来实现这个好像就和datagrid没关系了呵呵或者使用第三方控件,比如说Component One 点击"添加移除工具箱项",引用EXCEL的COM组件下面是将dataset的内容导出excel的简单方法:Excel.Application excel = new Excel.Application ();excel.Application.Workbooks.Add ( true );for(int i=1;i<=dss.Tables["chuchelv"].Columns.Count;i++){ excel.Cells[1,i]=dss.Tables["chuchelv"].Columns[i-1].ColumnName.ToString();}for(int i=2;i<=dss.Tables["chuchelv"].Rows.Count+1;i++) for(int j=1;j<=dss.Tables["chuchelv"].Columns.Count;j++) { excel.Cells[i,j]=dss.Tables["chuchelv"].Rows[i-2][j-1].ToString(); }excel.Visible = true ; private void Page_Load(object sender, System.EventArgs e){Response.Clear(); Response.Buffer= true; Response.AppendHeader("Content-Disposition","attachment;filename="+DateTime.Now.ToString("yyyyMMdd")+".xls"); Response.ContentEncoding=System.Text.Encoding.UTF8;Response.ContentType = "application/vnd.ms-excel";this.EnableViewState = false;string sql = @"select * from customers";SqlConnection conn = new SqlConnection(ConnectionString);SqlCommand cmd = new SqlCommand(sql, conn);cmd.CommandTimeout = 3600;conn.Open();cmd.CommandText = sql;dgSearch.DataSource = cmd.ExecuteReader();dgSearch.DataBind();} http://singlepine.cnblogs.com/articles/305843.html private void btnCsvDown_Click(object sender, System.EventArgs e) { if (object.Equals(null, SessionMana.ReadSession("csvdatatable"))) { lblErrorMsg.Text = MessgaeUtility.GetMessage("E0012"); } else { StringWriter objSW = new StringWriter(); objSW.WriteLine("ContactID,種類,日期,タイトル,重要度"); DataTable dtContactInfo = SessionMana.ReadSession("csvdatatable") as DataTable; foreach (DataRow drContactInfo in dtContactInfo.Rows) { objSW.WriteLine(drContactInfo[ContactColumn.c_strCONTACTID] + "," + drContactInfo[ContactColumn.c_strTYPE] + "," + drContactInfo[ContactColumn.c_strCREATEDATETIME] + "," + drContactInfo[ContactColumn.c_strSUBJECT] + "," + drContactInfo[ContactColumn.c_strIMPORTANCE]); } objSW.Close(); GC.Collect(); Response.AddHeader("Content-Disposition", "attachment; filename=ContactInfo.csv"); Response.ContentType = "application/ms-excel"; Response.ContentEncoding = Encoding.GetEncoding(932); Response.Write(objSW); Response.End(); } } //Datagrid中数据导出Excel#region 导出到excel //导出到excel public void OutPutExcel(DataTable myTable, string strTitle) { try { Excel.Application excel; int rowIndex=4; int colIndex=0; Excel._Workbook xBk; Excel._Worksheet xSt; excel= new Excel.ApplicationClass(); xBk = excel.Workbooks.Add(true); xSt = (Excel._Worksheet)xBk.ActiveSheet; // //取得标题 // foreach(DataColumn col in myTable.Columns) { colIndex++; excel.Cells[4,colIndex] = col.ColumnName; xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[4,colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐 } // //填充数据 // foreach(DataRow row in myTable.Rows) { rowIndex++; colIndex = 0; foreach(DataColumn col in myTable.Columns) { colIndex++; // //居中显示 // xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; } } int rowSum = rowIndex; //int colSum = 2; // //设置选中的部分的颜色 // //xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Select(); //xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex = 19;//设置为浅黄色,共计有56种 // //取得整个报表的标题 // excel.Cells[2,2] = strTitle; // //设置整个报表的标题格式 // xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Bold = true; xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Size = 22; xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Name = "楷体"; // //设置报表表格为最适应宽度 // xSt.get_Range(excel.Cells[4,1],excel.Cells[rowSum,colIndex]).Select(); xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Columns.AutoFit(); // //设置整个报表的标题为跨列居中 // //xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).Select(); xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex-1]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; // //绘制边框 // xSt.get_Range(excel.Cells[4,1],excel.Cells[rowSum,colIndex]).Borders.LineStyle = 1; // //设置字体 // xSt.get_Range(excel.Cells[4,1],excel.Cells[rowSum,colIndex]).Font.Name = "宋体"; // //设置字体大小 // xSt.get_Range(excel.Cells[4,1],excel.Cells[rowSum,colIndex]).Font.Size = 10; // //设置表头颜色 // //xSt.get_Range(excel.Cells[4,1],excel.Cells[4,colIndex]).Font.ColorIndex = 3; // //设置粗体 // xSt.get_Range(excel.Cells[4,1],excel.Cells[4,colIndex]).Font.Bold = true; // //设置字体颜色 // //xSt.get_Range(excel.Cells[4,1],excel.Cells[rowSum,colIndex+1]).Font.ColorIndex = 3; //xSt.get_Range(excel.Cells[4,1],excel.Cells[rowSum,2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;//设置左边线加粗 //xSt.get_Range(excel.Cells[4,1],excel.Cells[4,colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;//设置上边线加粗 //xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[rowSum,colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;//设置右边线加粗 //xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;//设置下边线加粗 excel.Visible = true; } catch(Exception Err) { MessageBox.Show(Err.Message); } } #endregion 注意: TextBox1.Text里面是你输入的excel文件名 Response.Clear(); Response.Buffer=true; Response.Charset="gb2312";//设置了类型为中文防止乱码的出现 Response.AppendHeader("Content-Disposition","attachment;filename="+TextBox1.Text+".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); this.LogGrid.RenderControl(oHtmlTextWriter); Response.Write(oStringWriter.ToString()); 怎样实现程序启动前动态画面 求解.C# windows窗体应用程序问的问题 表之间关系,如何实现? 如何 停止 或 暂停 已经启动的线程........... 计算机 树型控件问题菜鸟问题!~急急急!!~~ database插入问题,请老兄指点!在线等待 window form有没有类似web Form限定只能输入数字的控件! 急问急问:c#应用程序发电子邮件的问题 出错拉。。。。 获取对发ip 添加数据库出错了~
呵呵或者使用第三方控件,比如说Component One
下面是将dataset的内容导出excel的简单方法:
Excel.Application excel = new Excel.Application ();
excel.Application.Workbooks.Add ( true );for(int i=1;i<=dss.Tables["chuchelv"].Columns.Count;i++)
{
excel.Cells[1,i]=dss.Tables["chuchelv"].Columns[i-1].ColumnName.ToString();
}
for(int i=2;i<=dss.Tables["chuchelv"].Rows.Count+1;i++)
for(int j=1;j<=dss.Tables["chuchelv"].Columns.Count;j++)
{
excel.Cells[i,j]=dss.Tables["chuchelv"].Rows[i-2][j-1].ToString();
}
excel.Visible = true ;
{
Response.Clear();
Response.Buffer= true;
Response.AppendHeader("Content-Disposition","attachment;filename="+DateTime.Now.ToString("yyyyMMdd")+".xls");
Response.ContentEncoding=System.Text.Encoding.UTF8;
Response.ContentType = "application/vnd.ms-excel";
this.EnableViewState = false;string sql = @"select * from customers";
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandTimeout = 3600;conn.Open();
cmd.CommandText = sql;
dgSearch.DataSource = cmd.ExecuteReader();
dgSearch.DataBind();
}
{
if (object.Equals(null, SessionMana.ReadSession("csvdatatable")))
{
lblErrorMsg.Text = MessgaeUtility.GetMessage("E0012");
}
else
{
StringWriter objSW = new StringWriter();
objSW.WriteLine("ContactID,種類,日期,タイトル,重要度");
DataTable dtContactInfo = SessionMana.ReadSession("csvdatatable") as DataTable;
foreach (DataRow drContactInfo in dtContactInfo.Rows)
{
objSW.WriteLine(drContactInfo[ContactColumn.c_strCONTACTID]
+ ","
+ drContactInfo[ContactColumn.c_strTYPE]
+ ","
+ drContactInfo[ContactColumn.c_strCREATEDATETIME]
+ ","
+ drContactInfo[ContactColumn.c_strSUBJECT]
+ ","
+ drContactInfo[ContactColumn.c_strIMPORTANCE]);
}
objSW.Close();
GC.Collect();
Response.AddHeader("Content-Disposition", "attachment; filename=ContactInfo.csv");
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = Encoding.GetEncoding(932);
Response.Write(objSW);
Response.End();
}
}
#region 导出到excel
//导出到excel
public void OutPutExcel(DataTable myTable, string strTitle)
{
try
{
Excel.Application excel;
int rowIndex=4;
int colIndex=0; Excel._Workbook xBk;
Excel._Worksheet xSt; excel= new Excel.ApplicationClass();
xBk = excel.Workbooks.Add(true);
xSt = (Excel._Worksheet)xBk.ActiveSheet; //
//取得标题
//
foreach(DataColumn col in myTable.Columns)
{
colIndex++;
excel.Cells[4,colIndex] = col.ColumnName;
xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[4,colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐
} //
//填充数据
//
foreach(DataRow row in myTable.Rows)
{
rowIndex++;
colIndex = 0;
foreach(DataColumn col in myTable.Columns)
{
colIndex++;
//
//居中显示
//
xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
} int rowSum = rowIndex;
//int colSum = 2; //
//设置选中的部分的颜色
//
//xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Select();
//xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex = 19;//设置为浅黄色,共计有56种
//
//取得整个报表的标题
//
excel.Cells[2,2] = strTitle;
//
//设置整个报表的标题格式
//
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Bold = true;
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Size = 22;
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Name = "楷体";
//
//设置报表表格为最适应宽度
//
xSt.get_Range(excel.Cells[4,1],excel.Cells[rowSum,colIndex]).Select();
xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Columns.AutoFit();
//
//设置整个报表的标题为跨列居中
//
//xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).Select();
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex-1]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
//
//绘制边框
//
xSt.get_Range(excel.Cells[4,1],excel.Cells[rowSum,colIndex]).Borders.LineStyle = 1;
//
//设置字体
//
xSt.get_Range(excel.Cells[4,1],excel.Cells[rowSum,colIndex]).Font.Name = "宋体";
//
//设置字体大小
//
xSt.get_Range(excel.Cells[4,1],excel.Cells[rowSum,colIndex]).Font.Size = 10;
//
//设置表头颜色
//
//xSt.get_Range(excel.Cells[4,1],excel.Cells[4,colIndex]).Font.ColorIndex = 3;
//
//设置粗体
//
xSt.get_Range(excel.Cells[4,1],excel.Cells[4,colIndex]).Font.Bold = true;
//
//设置字体颜色
//
//xSt.get_Range(excel.Cells[4,1],excel.Cells[rowSum,colIndex+1]).Font.ColorIndex = 3;
//xSt.get_Range(excel.Cells[4,1],excel.Cells[rowSum,2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;//设置左边线加粗
//xSt.get_Range(excel.Cells[4,1],excel.Cells[4,colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;//设置上边线加粗
//xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[rowSum,colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;//设置右边线加粗
//xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;//设置下边线加粗 excel.Visible = true;
}
catch(Exception Err)
{
MessageBox.Show(Err.Message);
}
}
#endregion
Response.Clear();
Response.Buffer=true;
Response.Charset="gb2312";//设置了类型为中文防止乱码的出现
Response.AppendHeader("Content-Disposition","attachment;filename="+TextBox1.Text+".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);
this.LogGrid.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());