如何将DataGridView中的数据导入Excel 如何将DataGridView中的数据导入Excel?? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 http://blog.csdn.net/ChengKing/archive/2006/08/27/1128385.aspx private void ExportExcel(DataTable dt, string strExcelFileName) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); excel.Visible = true; Microsoft.Office.Interop.Excel._Workbook wkb = excel.Workbooks.Add(true); Microsoft.Office.Interop.Excel._Worksheet wks = (Microsoft.Office.Interop.Excel._Worksheet)wkb.ActiveSheet; wks.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetVisible; int rowIndex = 1; int colIndex = 0; foreach (DataColumn col in dt.Columns) { colIndex++; excel.Cells[1, colIndex] = col.ColumnName; } foreach (DataRow row in dt.Rows) { rowIndex++; colIndex = 0; foreach (DataColumn col in table.Columns) { colIndex++; excel.Cells[rowIndex, colIndex] = " " +row[col.ColumnName].ToString(); } } wkb.SaveAs(strExcelFileName, true, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null); wkb.Close(false,true , null); excel.Quit(); } public static void OutputExcel(Page page, DataGrid grid, DataSet ds) { string strFileName = page.ToString(); strFileName = strFileName.Remove(0, 4); strFileName = strFileName.Remove(strFileName.Length - 5, 5); page.Response.Clear(); page.Response.Buffer = true; page.Response.Charset = "utf-7"; //下面这行很重要, attachment 参数表示作为附件下载,您可以改成 online在线打开 //filename=FileFlow.xls 指定输出文件的名称,注意其扩展名和指定文件类型相符,可以为:.doc || .xls || .txt ||.htm page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + strFileName + ".xls"); //Response.ContentType指定文件类型 可以为application/ms-excel || application/ms-word || application/ms-txt || application/ms-html || 或其他浏览器可直接支持文档 page.Response.ContentType = "application/ms-excel"; //grid分页会造成乱码,所以设置为Unicode page.Response.ContentEncoding = Encoding.UTF8;// System.Text.Encoding.GetEncoding("utf-8"); page.EnableViewState = false; //处理DataSet中的数字字符串,比如员工编号、合同号、身份证号等,以免excel显示成科学计数法 DataTable dt = ds.Tables[0]; for(int i = 0; i < dt.Rows.Count; i ++) { for(int j = 0; j < dt.Columns.Count; j ++) { if(dt.Columns[j].DataType == Type.GetType("System.String")) { string strValue = string.Empty; if(! dt.Rows[i].IsNull(j)) { strValue = (string)dt.Rows[i][j]; } long lValue = -1; try { lValue = long.Parse(strValue); } catch { continue; } finally { if(lValue != -1) { dt.Rows[i][j] = "'" + strValue; } } } } } //排除不需要显示或可能出现问题的因素,比如操作列、ImageButton、排序列等 if(grid.Items.Count > 0) { for(int i = 0; i < grid.Columns.Count; i++) { //操作列 if(grid.Columns[i].HeaderText == "操作") { grid.Columns[i].Visible = false; } //排序列 if(grid.Columns[i].SortExpression != string.Empty) { grid.Columns[i].SortExpression = string.Empty; } //ImageButton foreach(System.Web.UI.Control ctrl in grid.Items[0].Cells[i].Controls) { if(ctrl.GetType().ToString() == "System.Web.UI.WebControls.ImageButton") { grid.Columns[i].Visible = false; } } } } //采用不分页下载execl grid.AllowPaging = false; grid.DataSource = dt.DefaultView; grid.DataBind(); //绑定后的处理:CheckBox列、避免数字字符串显示成科学计数法 for(int i = 0; i < grid.Items.Count; i ++) { for(int j = 0; j < grid.Columns.Count; j ++) { if(! grid.Columns[j].Visible) { continue; } foreach(System.Web.UI.Control ctrl in grid.Items[i].Cells[j].Controls) { //将CheckBox列的值显示为:是、否 if(ctrl.GetType().ToString() == "System.Web.UI.WebControls.CheckBox") { grid.Items[i].Cells[j].Text = ((CheckBox)ctrl).Checked ? "是" : "否"; grid.Items[i].Cells[j].Controls.Remove(ctrl); } //避免数字字符串显示成科学计数法 //编辑列的情况 if(ctrl.GetType().ToString() == "System.Web.UI.WebControls.Label") { if(((Label)ctrl).Text.StartsWith("'")) { ((Label)ctrl).Text = ((Label)ctrl).Text.Remove(0, 1); grid.Items[i].Cells[j].Style.Add("vnd.ms-excel.numberformat", "@"); continue; } } } //避免数字字符串显示成科学计数法 //绑定列的情况 if(grid.Items[i].Cells[j].Text.StartsWith("'")) { grid.Items[i].Cells[j].Text = grid.Items[i].Cells[j].Text.Remove(0, 1); grid.Items[i].Cells[j].Style.Add("vnd.ms-excel.numberformat", "@"); } } }// System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("ZH-CN", true);// StringWriter sw = new StringWriter(ci); StringWriter sw = new StringWriter(); HtmlTextWriter htw = new HtmlTextWriter(sw); grid.RenderControl(htw); page.Response.Write(sw.ToString()); page.Response.End(); } C#sql查询数据。 傳多個不同的參數到sql中 从Excel中得到sheet表名,如sheet1,mysheet等,在线等待 只有安装.Net Framework才能运行的用.net开发的windows桌面程序吗 C/S局域网考试系统自动评分功能 问一句代码 这是什么控件 C# 操作蓝牙 winform listview下拉到底判断 在Visula C# 中如何使焦点移动到 textBox1 控件上来?请编码!(高分相送) 怎么把treeView的某些节点灰掉,就是我显示它,但是不可用???大家进来下 谢谢 能不能将TabStrip和MultiPage控件抽成用户自定义控件?
{ Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
excel.Visible = true;
Microsoft.Office.Interop.Excel._Workbook wkb = excel.Workbooks.Add(true);
Microsoft.Office.Interop.Excel._Worksheet wks = (Microsoft.Office.Interop.Excel._Worksheet)wkb.ActiveSheet;
wks.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetVisible; int rowIndex = 1;
int colIndex = 0; foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
} foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[rowIndex, colIndex] = " " +row[col.ColumnName].ToString();
}
}
wkb.SaveAs(strExcelFileName, true, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
wkb.Close(false,true , null);
excel.Quit();
}
{
string strFileName = page.ToString();
strFileName = strFileName.Remove(0, 4);
strFileName = strFileName.Remove(strFileName.Length - 5, 5); page.Response.Clear();
page.Response.Buffer = true;
page.Response.Charset = "utf-7"; //下面这行很重要, attachment 参数表示作为附件下载,您可以改成 online在线打开
//filename=FileFlow.xls 指定输出文件的名称,注意其扩展名和指定文件类型相符,可以为:.doc || .xls || .txt ||.htm
page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + strFileName + ".xls"); //Response.ContentType指定文件类型 可以为application/ms-excel || application/ms-word || application/ms-txt || application/ms-html || 或其他浏览器可直接支持文档
page.Response.ContentType = "application/ms-excel"; //grid分页会造成乱码,所以设置为Unicode
page.Response.ContentEncoding = Encoding.UTF8;// System.Text.Encoding.GetEncoding("utf-8"); page.EnableViewState = false; //处理DataSet中的数字字符串,比如员工编号、合同号、身份证号等,以免excel显示成科学计数法
DataTable dt = ds.Tables[0]; for(int i = 0; i < dt.Rows.Count; i ++)
{
for(int j = 0; j < dt.Columns.Count; j ++)
{
if(dt.Columns[j].DataType == Type.GetType("System.String"))
{
string strValue = string.Empty;
if(! dt.Rows[i].IsNull(j))
{
strValue = (string)dt.Rows[i][j];
} long lValue = -1; try
{
lValue = long.Parse(strValue);
}
catch
{
continue;
}
finally
{
if(lValue != -1)
{
dt.Rows[i][j] = "'" + strValue;
}
}
}
}
} //排除不需要显示或可能出现问题的因素,比如操作列、ImageButton、排序列等
if(grid.Items.Count > 0)
{
for(int i = 0; i < grid.Columns.Count; i++)
{
//操作列
if(grid.Columns[i].HeaderText == "操作")
{
grid.Columns[i].Visible = false;
} //排序列
if(grid.Columns[i].SortExpression != string.Empty)
{
grid.Columns[i].SortExpression = string.Empty;
} //ImageButton
foreach(System.Web.UI.Control ctrl in grid.Items[0].Cells[i].Controls)
{
if(ctrl.GetType().ToString() == "System.Web.UI.WebControls.ImageButton")
{
grid.Columns[i].Visible = false;
}
}
}
} //采用不分页下载execl
grid.AllowPaging = false;
grid.DataSource = dt.DefaultView;
grid.DataBind(); //绑定后的处理:CheckBox列、避免数字字符串显示成科学计数法
for(int i = 0; i < grid.Items.Count; i ++)
{
for(int j = 0; j < grid.Columns.Count; j ++)
{
if(! grid.Columns[j].Visible)
{
continue;
} foreach(System.Web.UI.Control ctrl in grid.Items[i].Cells[j].Controls)
{
//将CheckBox列的值显示为:是、否
if(ctrl.GetType().ToString() == "System.Web.UI.WebControls.CheckBox")
{
grid.Items[i].Cells[j].Text = ((CheckBox)ctrl).Checked ? "是" : "否";
grid.Items[i].Cells[j].Controls.Remove(ctrl);
} //避免数字字符串显示成科学计数法
//编辑列的情况
if(ctrl.GetType().ToString() == "System.Web.UI.WebControls.Label")
{
if(((Label)ctrl).Text.StartsWith("'"))
{
((Label)ctrl).Text = ((Label)ctrl).Text.Remove(0, 1);
grid.Items[i].Cells[j].Style.Add("vnd.ms-excel.numberformat", "@");
continue;
}
}
} //避免数字字符串显示成科学计数法
//绑定列的情况
if(grid.Items[i].Cells[j].Text.StartsWith("'"))
{
grid.Items[i].Cells[j].Text = grid.Items[i].Cells[j].Text.Remove(0, 1);
grid.Items[i].Cells[j].Style.Add("vnd.ms-excel.numberformat", "@");
}
}
}// System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("ZH-CN", true);
// StringWriter sw = new StringWriter(ci);
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw); grid.RenderControl(htw);
page.Response.Write(sw.ToString());
page.Response.End();
}