把DataGridView里的数据导出到excel表格里面_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
把DataGridView里的数据导出到excel表格里面
上传于||暂无简介
阅读已结束，如果下载本文需要使用2下载券
想免费下载本文？
定制HR最喜欢的简历
你可能喜欢该问题被发起重新开启投票
投票剩余时间：
之前被关闭原因：
该问题被发起删除投票
投票剩余时间：
距离悬赏到期还有：
参与关闭投票者：
关闭原因：
该问题已经被锁定
锁定原因：（）
保护原因：避免来自新用户不合宜或无意义的致谢、跟帖答案。
该问题已成功删除，仅对您可见，其他人不能够查看。
using Susing System.Collections.GponentMusing System.Dusing System.Dusing System.Data.Lusing System.Tusing System.Windows.Fusing System.Rusing Microsoft.Office.Interop.Eusing System.IO;namespace InfoManager.Helper{
public class Export
private DataGridView _DataGridV
private string _FileName = "";
public void SetDataGridView(DataGridView g)
_DataGridView =
public Export(DataGridView g)
_DataGridView =
public void ExportToTxt()
System.Windows.Forms.SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "txt文件|*.txt";
sfd.Title = "导出文本文件";
if (sfd.ShowDialog() == DialogResult.OK)
string fileName = sfd.FileN
if (fileName != "")
//实例化一个文件流---&与写入文件相关联
FileStream fs = new FileStream(fileName, FileMode.Create);
//实例化一个StreamWriter--&与fs相关联
StreamWriter sw = new StreamWriter(fs);
//开始写入
foreach (DataGridViewColumn dgvc in this._DataGridView.Columns)
sw.Write(dgvc.HeaderText + "\t");
sw.WriteLine();
int nRows = this._DataGridView.RowC
int nCols = this._DataGridView.ColumnC
for (int i = 0; i & nR i++)
for (int j = 0; j & nC j++)
sw.Write(this._DataGridView[j, i].Value.ToString() + "\t");
sw.WriteLine();
//清空缓冲区
sw.Flush();
sw.Close();
fs.Close();
this.OpenFile(fileName);
catch (System.Exception err)
MessageBox.Show(err.Message);
/// &summary&
/// 导出到Excel
/// &/summary&
/// &param name="pfileName"&文件名,不需要带扩展名&/param&
public void ExportToExcel(string pfileName)
_FileName = pfileName == null ? "未命名" : pfileName.Trim();
System.Windows.Forms.SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Microsoft Excel|*.xls";
sfd.Title = "Microsoft Excel Document";
if (sfd.ShowDialog() == DialogResult.OK)
string fileName = sfd.FileN
if (fileName != "")
ExportTo(fileName);
OpenFile(fileName);
catch (System.Exception err)
MessageBox.Show(err.Message);
/// &summary&
/// 导出到
/// &/summary&
/// &param name="fileName"&文件名&/param&
private void ExportTo(string fileName)
//获取指定文件是否存在
bool isExist = System.IO.File.Exists(fileName);
//定义一个缺少的object对象
object oMis = System.Reflection.Missing.V
//定义一个Excel区域对象,用于保存选择的区域
Microsoft.Office.Interop.Excel.Range selectR
//声明一Excel Application 对象
Microsoft.Office.Interop.Excel.ApplicationClass App =
//声明一Excel Workbook 对象
Microsoft.Office.Interop.Excel.Workbook wb =
//声明一Excel Worksheet 对象
Microsoft.Office.Interop.Excel.Worksheet ws =
//将当前水标状态保存到临时变量中后将光标置为忙状态
Cursor currentCursor = Cursor.C
Cursor.Current = Cursors.WaitC
App = new Microsoft.Office.Interop.Excel.ApplicationClass();
//判断指定的文件是否存在
if (isExist)
//打开已存在的工作薄 Workbook
wb = App.Workbooks.Open(fileName, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis);
//新建工作表
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.Add(oMis, oMis, 1, oMis);
//指定工作表名
ws.Name = DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + wb.Sheets.C //加 wb.Sheets.Count 防止重名
//增加一工作薄 Workbook
wb = App.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
//新建工作薄后默认有一个工作表,取得第一个工作表
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets[1];
//指定工作表名
ws.Name = DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString();
//增加一工作表 Worksheet
//声明两个变量记录Excle当前操作的行号与列号
int rown = 0;
int coln = 0;
//声明一变量用于记录当前DatagridView的总行数
int colCount = _DataGridView.Columns.C
_DataGridView.SuspendLayout();
//将DataGirdView列头写入Excel中
foreach (DataGridViewColumn dgvc in _DataGridView.Columns)
selectRange = ws.get_Range(ws.Cells[rown + 1, ++coln], ws.Cells[rown + 1, coln]);
selectRange.Columns.ColumnWidth = dgvc.Width / 10;
selectRange.Interior.ColorIndex = 16;
selectRange.Interior.Pattern = Microsoft.Office.Interop.Excel.Constants.xlS
selectRange.Font.ColorIndex = 2;
selectRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlC
selectRange.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlC
selectRange.set_Item(1, 1, dgvc.HeaderText);
//行号增加一
//将DataGridView中所有的行数写入Excel中
foreach (DataGridViewRow dgvr in _DataGridView.Rows)
for (int i = 0; i & colC i++)
selectRange = ws.get_Range(ws.Cells[rown + 1, i + 1], ws.Cells[rown + 1, i + 1]);
selectRange.set_Item(1, 1, dgvr.Cells[i].Value);
//写入完成后将有数据的范围内设置其边框与内部线条
ws.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = 3;
ws.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = 3;
ws.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = 3;
ws.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = 3;
ws.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = 2;
ws.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = 2;
//设置Wookbook为已保存状态
wb.Saved =
if (isExist)
wb.Save();
//将当前工作薄保存为指定的文件名
wb.SaveCopyAs(fileName);
catch (Exception exp)
MessageBox.Show(exp.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
_DataGridView.ResumeLayout();
wb.Close(false, null, null);
//退出Excel程序
App.Quit();
//将当前光标更改回原来的状态
Cursor.Current = currentC
/// &summary&
/// 打开文件
/// &/summary&
/// &param name="fileName"&文件名&/param&
private void OpenFile(string fileName)
if (MessageBox.Show("你想打开这个文件吗?", "导出到...", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo.FileName = fileN
process.StartInfo.Verb = "Open";
process.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.N
process.Start();
MessageBox.Show("你的计算机中未安装相关程序,不能打开该文档!","错误！", MessageBoxButtons.OK, MessageBoxIcon.Error);
using Susing System.IO;using System.Dusing System.Cusing System.Data.OleDb;using System.Wusing System.Web.UI;using System.Web.UI.WebCnamespace DotNet.Utilities{
/// &summary&
/// Excel操作类
/// &/summary&
/// Microsoft Excel 11.0 Object Library
public class ExcelHelper
#region 数据导出至Excel文件
/// &/summary&
/// 导出Excel文件，自动返回可下载的文件流
/// &/summary&
public static void DataTable1Excel(System.Data.DataTable dtData)
GridView gvExport =
HttpContext curContext = HttpContext.C
StringWriter strWriter =
HtmlTextWriter htmlWriter =
if (dtData != null)
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
curContext.Response.Charset = "utf-8";
strWriter = new StringWriter();
htmlWriter = new HtmlTextWriter(strWriter);
gvExport = new GridView();
gvExport.DataSource = dtData.DefaultV
gvExport.AllowPaging =
gvExport.DataBind();
gvExport.RenderControl(htmlWriter);
curContext.Response.Write("&meta http-equiv=\"Content-Type\" content=\"text/charset=gb2312\"/&" + strWriter.ToString());
curContext.Response.End();
/// &summary&
/// 导出Excel文件，转换为可读模式
/// &/summary&
public static void DataTable2Excel(System.Data.DataTable dtData)
DataGrid dgExport =
HttpContext curContext = HttpContext.C
StringWriter strWriter =
HtmlTextWriter htmlWriter =
if (dtData != null)
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
curContext.Response.Charset = "";
strWriter = new StringWriter();
htmlWriter = new HtmlTextWriter(strWriter);
dgExport = new DataGrid();
dgExport.DataSource = dtData.DefaultV
dgExport.AllowPaging =
dgExport.DataBind();
dgExport.RenderControl(htmlWriter);
curContext.Response.Write(strWriter.ToString());
curContext.Response.End();
/// &summary&
/// 导出Excel文件，并自定义文件名
/// &/summary&
public static void DataTable3Excel(System.Data.DataTable dtData, String FileName)
GridView dgExport =
HttpContext curContext = HttpContext.C
StringWriter strWriter =
HtmlTextWriter htmlWriter =
if (dtData != null)
HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8);
curContext.Response.AddHeader("content-disposition", "filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls");
curContext.Response.ContentType = "application nd.ms-excel";
curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
curContext.Response.Charset = "GB2312";
strWriter = new StringWriter();
htmlWriter = new HtmlTextWriter(strWriter);
dgExport = new GridView();
dgExport.DataSource = dtData.DefaultV
dgExport.AllowPaging =
dgExport.DataBind();
dgExport.RenderControl(htmlWriter);
curContext.Response.Write(strWriter.ToString());
curContext.Response.End();
/// &summary&
/// 将数据导出至Excel文件
/// &/summary&
/// &param name="Table"&DataTable对象&/param&
/// &param name="ExcelFilePath"&Excel文件路径&/param&
public static bool OutputToExcel(DataTable Table, string ExcelFilePath)
if (File.Exists(ExcelFilePath))
throw new Exception("该文件已经存在！");
if ((Table.TableName.Trim().Length == 0) || (Table.TableName.ToLower() == "table"))
Table.TableName = "Sheet1";
//数据表的列数
int ColCount = Table.Columns.C
//用于记数，实例化参数时的序号
int i = 0;
//创建参数
OleDbParameter[] para = new OleDbParameter[ColCount];
//创建表结构的SQL语句
string TableStructStr = @"Create Table " + Table.TableName + "(";
//连接字符串
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(connString);
//创建表结构
OleDbCommand objCmd = new OleDbCommand();
//数据类型集合
ArrayList DataTypeList = new ArrayList();
DataTypeList.Add("System.Decimal");
DataTypeList.Add("System.Double");
DataTypeList.Add("System.Int16");
DataTypeList.Add("System.Int32");
DataTypeList.Add("System.Int64");
DataTypeList.Add("System.Single");
//遍历数据表的所有列，用于创建表结构
foreach (DataColumn col in Table.Columns)
//如果列属于数字列，则设置该列的数据类型为double
if (DataTypeList.IndexOf(col.DataType.ToString()) &= 0)
para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.Double);
objCmd.Parameters.Add(para[i]);
//如果是最后一列
if (i + 1 == ColCount)
TableStructStr += col.ColumnName + " double)";
TableStructStr += col.ColumnName + " double,";
para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.VarChar);
objCmd.Parameters.Add(para[i]);
//如果是最后一列
if (i + 1 == ColCount)
TableStructStr += col.ColumnName + " varchar)";
TableStructStr += col.ColumnName + " varchar,";
//创建Excel文件及文件结构
objCmd.Connection = objC
mandText = TableStructS
if (objConn.State == ConnectionState.Closed)
objConn.Open();
objCmd.ExecuteNonQuery();
catch (Exception exp)
//插入记录的SQL语句
string InsertSql_1 = "Insert into " + Table.TableName + " (";
string InsertSql_2 = " Values (";
string InsertSql = "";
//遍历所有列，用于插入记录，在此创建插入记录的SQL语句
for (int colID = 0; colID & ColC colID++)
if (colID + 1 == ColCount)
//最后一列
InsertSql_1 += Table.Columns[colID].ColumnName + ")";
InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ")";
InsertSql_1 += Table.Columns[colID].ColumnName + ",";
InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ",";
InsertSql = InsertSql_1 + InsertSql_2;
//遍历数据表的所有数据行
for (int rowID = 0; rowID & Table.Rows.C rowID++)
for (int colID = 0; colID & ColC colID++)
if (para[colID].DbType == DbType.Double && Table.Rows[rowID][colID].ToString().Trim() == "")
para[colID].Value = 0;
para[colID].Value = Table.Rows[rowID][colID].ToString().Trim();
mandText = InsertS
objCmd.ExecuteNonQuery();
catch (Exception exp)
string str = exp.M
if (objConn.State == ConnectionState.Open)
objConn.Close();
catch (Exception exp)
/// &summary&
/// 将数据导出至Excel文件
/// &/summary&
/// &param name="Table"&DataTable对象&/param&
/// &param name="Columns"&要导出的数据列集合&/param&
/// &param name="ExcelFilePath"&Excel文件路径&/param&
public static bool OutputToExcel(DataTable Table, ArrayList Columns, string ExcelFilePath)
if (File.Exists(ExcelFilePath))
throw new Exception("该文件已经存在！");
//如果数据列数大于表的列数，取数据表的所有列
if (Columns.Count & Table.Columns.Count)
for (int s = Table.Columns.Count + 1; s &= Columns.C s++)
Columns.RemoveAt(s);
//移除数据表列数后的所有列
//遍历所有的数据列，如果有数据列的数据类型不是 DataColumn，则将它移除
DataColumn column = new DataColumn();
for (int j = 0; j & Columns.C j++)
column = (DataColumn)Columns[j];
catch (Exception)
Columns.RemoveAt(j);
if ((Table.TableName.Trim().Length == 0) || (Table.TableName.ToLower() == "table"))
Table.TableName = "Sheet1";
//数据表的列数
int ColCount = Columns.C
//创建参数
OleDbParameter[] para = new OleDbParameter[ColCount];
//创建表结构的SQL语句
string TableStructStr = @"Create Table " + Table.TableName + "(";
//连接字符串
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(connString);
//创建表结构
OleDbCommand objCmd = new OleDbCommand();
//数据类型集合
ArrayList DataTypeList = new ArrayList();
DataTypeList.Add("System.Decimal");
DataTypeList.Add("System.Double");
DataTypeList.Add("System.Int16");
DataTypeList.Add("System.Int32");
DataTypeList.Add("System.Int64");
DataTypeList.Add("System.Single");
DataColumn col = new DataColumn();
//遍历数据表的所有列，用于创建表结构
for (int k = 0; k & ColC k++)
col = (DataColumn)Columns[k];
//列的数据类型是数字型
if (DataTypeList.IndexOf(col.DataType.ToString().Trim()) &= 0)
para[k] = new OleDbParameter("@" + col.Caption.Trim(), OleDbType.Double);
objCmd.Parameters.Add(para[k]);
//如果是最后一列
if (k + 1 == ColCount)
TableStructStr += col.Caption.Trim() + " Double)";
TableStructStr += col.Caption.Trim() + " Double,";
para[k] = new OleDbParameter("@" + col.Caption.Trim(), OleDbType.VarChar);
objCmd.Parameters.Add(para[k]);
//如果是最后一列
if (k + 1 == ColCount)
TableStructStr += col.Caption.Trim() + " VarChar)";
TableStructStr += col.Caption.Trim() + " VarChar,";
//创建Excel文件及文件结构
objCmd.Connection = objC
mandText = TableStructS
if (objConn.State == ConnectionState.Closed)
objConn.Open();
objCmd.ExecuteNonQuery();
catch (Exception exp)
//插入记录的SQL语句
string InsertSql_1 = "Insert into " + Table.TableName + " (";
string InsertSql_2 = " Values (";
string InsertSql = "";
//遍历所有列，用于插入记录，在此创建插入记录的SQL语句
for (int colID = 0; colID & ColC colID++)
if (colID + 1 == ColCount)
//最后一列
InsertSql_1 += Columns[colID].ToString().Trim() + ")";
InsertSql_2 += "@" + Columns[colID].ToString().Trim() + ")";
InsertSql_1 += Columns[colID].ToString().Trim() + ",";
InsertSql_2 += "@" + Columns[colID].ToString().Trim() + ",";
InsertSql = InsertSql_1 + InsertSql_2;
//遍历数据表的所有数据行
DataColumn DataCol = new DataColumn();
for (int rowID = 0; rowID & Table.Rows.C rowID++)
for (int colID = 0; colID & ColC colID++)
//因为列不连续，所以在取得单元格时不能用行列编号，列需得用列的名称
DataCol = (DataColumn)Columns[colID];
if (para[colID].DbType == DbType.Double && Table.Rows[rowID][DataCol.Caption].ToString().Trim() == "")
para[colID].Value = 0;
para[colID].Value = Table.Rows[rowID][DataCol.Caption].ToString().Trim();
mandText = InsertS
objCmd.ExecuteNonQuery();
catch (Exception exp)
string str = exp.M
if (objConn.State == ConnectionState.Open)
objConn.Close();
catch (Exception exp)
#endregion
/// &summary&
/// 获取Excel文件数据表列表
/// &/summary&
public static ArrayList GetExcelTables(string ExcelFileName)
DataTable dt = new DataTable();
ArrayList TablesList = new ArrayList();
if (File.Exists(ExcelFileName))
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + ExcelFileName))
conn.Open();
dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
catch (Exception exp)
//获取数据表个数
int tablecount = dt.Rows.C
for (int i = 0; i & i++)
string tablename = dt.Rows[i][2].ToString().Trim().TrimEnd('$');
if (TablesList.IndexOf(tablename) & 0)
TablesList.Add(tablename);
return TablesL
/// &summary&
/// 将Excel文件导出至DataTable(第一行作为表头)
/// &/summary&
/// &param name="ExcelFilePath"&Excel文件路径&/param&
/// &param name="TableName"&数据表名，如果数据表名错误，默认为第一个数据表名&/param&
public static DataTable InputFromExcel(string ExcelFilePath, string TableName)
if (!File.Exists(ExcelFilePath))
throw new Exception("Excel文件不存在！");
//如果数据表名不存在，则数据表名为Excel文件的第一个数据表
ArrayList TableList = new ArrayList();
TableList = GetExcelTables(ExcelFilePath);
if (TableName.IndexOf(TableName) & 0)
TableName = TableList[0].ToString().Trim();
DataTable table = new DataTable();
OleDbConnection dbcon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0");
OleDbCommand cmd = new OleDbCommand("select * from [" + TableName + "$]", dbcon);
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
if (dbcon.State == ConnectionState.Closed)
dbcon.Open();
adapter.Fill(table);
catch (Exception exp)
if (dbcon.State == ConnectionState.Open)
dbcon.Close();
/// &summary&
/// 获取Excel文件指定数据表的数据列表
/// &/summary&
/// &param name="ExcelFileName"&Excel文件名&/param&
/// &param name="TableName"&数据表名&/param&
public static ArrayList GetExcelTableColumns(string ExcelFileName, string TableName)
DataTable dt = new DataTable();
ArrayList ColsList = new ArrayList();
if (File.Exists(ExcelFileName))
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + ExcelFileName))
conn.Open();
dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, TableName, null });
//获取列个数
int colcount = dt.Rows.C
for (int i = 0; i & i++)
string colname = dt.Rows[i]["Column_Name"].ToString().Trim();
ColsList.Add(colname);
return ColsL
不是您所需，查看更多相关问题与答案
德问是一个专业的编程问答社区，请
后再提交答案
关注该问题的人
共被浏览 (7062) 次2009年10月 .NET技术大版内专家分月排行榜第三
2009年10月 .NET技术大版内专家分月排行榜第三
2010年 总版技术专家分年内排行榜第一2009年 总版技术专家分年内排行榜第一
2011年 总版技术专家分年内排行榜第二
2010年 总版技术专家分年内排行榜第一2009年 总版技术专家分年内排行榜第一
2011年 总版技术专家分年内排行榜第二
本帖子已过去太久远了，不再提供回复功能。}