datagridview如何导出到EXCEL方法

合集下载

将DataGridView中的数据导出至Excel中(亲测成功)

将DataGridView中的数据导出至Excel（亲测成功）一、建立一个静态方法public static void ExportAsExcel(DataGridView dgv){OutputAsExcelFile(DGVHelper.DataGridViewToTable(dgv));}二、把DataGridView中的数据转换到DataTable中///<summary>///将DataGridView中的数据转换为DataTable包含隐藏列///</summary>///<param name="dgv"></param>///<returns></returns>public static DataTable DataGridViewToTable(DataGridView dgv){DataTable dt=new DataTable();//循环列标题名称，处理了隐藏的行不显示for(int count=0;count<dgv.Columns.Count;count++){if(dgv.Columns[count].Visible==true){dt.Columns.Add(dgv.Columns[count].HeaderText.ToString());}}//循环行，处理了隐藏的行不显示for(int count=0;count<dgv.Rows.Count;count++){DataRow dr=dt.NewRow();int curr=0;for(int countsub=0;countsub<dgv.Columns.Count;countsub++){if(dgv.Columns[countsub].Visible==true){if(dgv.Rows[count].Cells[countsub].Value!=null){dr[curr]=dgv.Rows[count].Cells[countsub].Value.ToString();}else{dr[curr]="";}curr++;}}dt.Rows.Add(dr);}return dt;}三、把DataTable中的数据导出到Excel中public static void OutputAsExcelFile(DataTable dt){if(dt.Rows.Count<=0){MessM.PromptInfo("提示","无数据！");return;}SaveFileDialog s=new SaveFileDialog{Title="保存Excel文件",Filter="Excel文件(*.xls)|*.xls",FilterIndex=1};if(s.ShowDialog()==DialogResult.OK)filePath=s.FileName;elsereturn;DTExportToExcel(dt);}///<summary>///第二步：导出dataTable到Excel///</summary>///<param name="dt"></param>private static void DTExportToExcel(DataTable dt){//第二步：导出dataTable到Excellong rowNum=dt.Rows.Count;//行数int columnNum=dt.Columns.Count;//列数Excel.Application m_xlApp=new Excel.Application{DisplayAlerts=false,//不显示更改提示Visible=false};Excel.Workbooks workbooks=m_xlApp.Workbooks;Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1try{string[,]datas=new string[rowNum+1,columnNum];for(int i=0;i<columnNum;i++)//写入字段datas[0,i]=dt.Columns[i].Caption;//Excel.Range range=worksheet.get_Range(worksheet.Cells[1,1],worksheet.Cells[1, columnNum]);Excel.Range range=m_xlApp.Range[worksheet.Cells[1,1],worksheet.Cells[1, columnNum]];range.Interior.ColorIndex=15;//15代表灰色range.Font.Bold=true;range.Font.Size=10;int r=0;for(r=0;r<rowNum;r++){for(int i=0;i<columnNum;i++){object obj=dt.Rows[r][dt.Columns[i].ToString()];datas[r+1,i]=obj==null?"":"'"+obj.ToString().Trim();//在obj.ToString()前加单引號是为了防止自己主动转化格式}Application.DoEvents();//加入进度条}//Excel.Range fchR=worksheet.get_Range(worksheet.Cells[1,1], worksheet.Cells[rowNum+1,columnNum]);Excel.Range fchR=m_xlApp.Range[worksheet.Cells[1,1],worksheet.Cells[rowNum+1, columnNum]];fchR.Value2=datas;worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。

c#datagridview导出到excel

c#datagridview导出到excel ⽅法⼀：添加dll引⽤右击选择你所在的项⽬的“引⽤”，选择“添加引⽤”。

弹出“添加引⽤”对话框。

选择“COM”选项卡。

选择“Microsoft Excel 11.0 Object Library”单击“确定”按钮。

代码public static bool ExportForDataGridview(DataGridView gridView, string fileName, bool isShowExcle) {//建⽴Ｅｘｃｅｌ对象Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); try{if (app == null){return false;}app.Visible = isShowExcle;Workbooks workbooks = app.Workbooks;_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);Sheets sheets = workbook.Worksheets;_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);if (worksheet == null){return false;}string sLen = "";//取得最后⼀列列名char H = (char)(64 + gridView.ColumnCount / 26);char L = (char)(64 + gridView.ColumnCount % 26);if (gridView.ColumnCount < 26){sLen = L.ToString();}else{sLen = H.ToString() + L.ToString();}//标题string sTmp = sLen + "1";Range ranCaption = worksheet.get_Range(sTmp, "A1");string[] asCaption = new string[gridView.ColumnCount];for (int i = 0; i < gridView.ColumnCount; i++){asCaption[i] = gridView.Columns[i].HeaderText;}ranCaption.Value2 = asCaption;//数据object[] obj = new object[gridView.Columns.Count];for (int r = 0; r < gridView.RowCount - 1; r++){for (int l = 0; l < gridView.Columns.Count; l++){if (gridView[l, r].ValueType == typeof(DateTime)){obj[l] = gridView[l, r].Value.ToString();}else{obj[l] = gridView[l, r].Value;}}string cell1 = sLen + ((int)(r + 2)).ToString();string cell2 = "A" + ((int)(r + 2)).ToString();Range ran = worksheet.get_Range(cell1, cell2);ran.Value2 = obj;}//保存workbook.SaveCopyAs(fileName);workbook.Saved = true;}finally{//关闭erControl = false;app.Quit();}return true;}⽅法⼆⽤流保存成xls⽂件. 这种⽅法⽐较好,不⽤引⽤Excel组件. 下⾯是具体例⼦,可以参考using System.IO;////// 另存新档按钮///private void SaveAs() //另存新档按钮导出成Excel{SaveFileDialog saveFileDialog = new SaveFileDialog();saveFileDialog.Filter = "Execl files (*.xls)|*.xls";saveFileDialog.FilterIndex = 0;saveFileDialog.RestoreDirectory = true;saveFileDialog.CreatePrompt = true;saveFileDialog.Title = "Export Excel File To";saveFileDialog.ShowDialog();Stream myStream;myStream = saveFileDialog.OpenFile();//StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312")); StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));string str = "";try{//写标题for (int i = 0; i < dgvAgeWeekSex.ColumnCount; i++){if (i > 0){str += "\t";}str += dgvAgeWeekSex.Columns[i].HeaderText;}sw.WriteLine(str);//写内容for (int j = 0; j < dgvAgeWeekSex.Rows.Count; j++){string tempStr = "";for (int k = 0; k < dgvAgeWeekSex.Columns.Count; k++){if (k > 0){tempStr += "\t";}tempStr += dgvAgeWeekSex.Rows[j].Cells[k].Value.ToString(); }sw.WriteLine(tempStr);}sw.Close();myStream.Close();}catch (Exception e){MessageBox.Show(e.ToString());}finally{sw.Close();myStream.Close();}}。

C#DataGridView导出excel的几种方法

C#DataGridView导出excel的⼏种⽅法第⼀种、⾸先本form上游datagridview的控件及数据，再建⼀个button控件作为导出按钮，在按钮click事件中写⼊以下代码此乃数据集的⽅式，即先将数据放⼊数据集表⾥作为对象与excel⼀⼀对应//保存⽂件对话框SaveFileDialog sfd = new SaveFileDialog();sfd.Filter = "Excel⽂件|*.xlsx|Word⽂件|*.docx";sfd.FilterIndex = 0;if (sfd.ShowDialog() == DialogResult.OK){string search = "select * from 旧备件表 where(0=0)";if (this.textBox1.Text.Length > 0){search = search + " and 物料编码=" + "'" + textBox1.Text + "'";}if (this.textBox2.Text.Length > 0){search = search + " and 设备号=" + "'" + textBox2.Text + "'";}if (this.textBox3.Text.Length > 0){search = search + " and 物料描述 like" + "'%" + textBox3.Text + "%'";//实现物料描述的模糊查询}if (this.textBox4.Text.Length > 0){search = search + " and 备件序列号 like" + "'%" + textBox4.Text + "%'";//实现备件序列号的模糊查询}//调⽤导出Excel的⽅法，传⼊DataTable数据表和路径SqlDataAdapter sda = new SqlDataAdapter(search, DataBase.GetSqlConnection());System.Data.DataTable dt = new System.Data.DataTable();//将数据库中查到的数据填充到DataTable数据表sda.Fill(dt);ExportExcel(dt, sfd.FileName);}}void ExportExcel(System.Data.DataTable dt, string filepath){//创建Excel应⽤程序类的⼀个实例，相当于从电脑开始菜单打开ExcelApplicationClass xlsxapp = new ApplicationClass();//新建⼀张Excel⼯作簿Workbook wbook = xlsxapp.Workbooks.Add(true);//第⼀个sheet页Worksheet wsheet = (Worksheet)wbook.Worksheets.get_Item(1);//将DataTable的列名显⽰在Excel表第⼀⾏for (int i = 0; i < dt.Columns.Count; i++){//注意Excel表的⾏和列的索引都是从1开始的wsheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;}//遍历DataTable，给Excel赋值for (int i = 0; i < dt.Rows.Count; i++){for (int j = 0; j < dt.Columns.Count; j++){//从第⼆⾏第⼀列开始写⼊数据wsheet.Cells[i + 2, j + 1] = dt.Rows[i][j];}}//保存⽂件wbook.SaveAs(filepath);//释放资源xlsxapp.Quit();}第⼆种、此乃直接将datagridview⾥的数据⼀⼀导出放⼊excel指定的单元格⾥。

C#将DataGridView中显示的数据导出到Excel（.xls和.xlsx格式）—NPOI

C#将DataGridView中显⽰的数据导出到Excel（.xls和.xlsx格式）—NPOI 前⾔本地数据库表中有46785条数据，测试正常初次运⾏程序第⼀次导出，⽤时在4-5s左右；此后再导出⽤时在2-3s左右；可能与缓存有关⽀持导出为.xls和.xlsx格式可以⾃定义字体和字号 exe测试⽂件下载地址：密码：test实现步骤⼀、安装NPOI右键项⽬→ "管理NuGet程序包" →搜索NPOI →安装⼆、创建类using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;using erModel;using erModel;//导出xls格式⽤HSSFusing erModel;//导出xlsx格式⽤XSSFusing System.IO;using System.Runtime.InteropServices;using System.Diagnostics;using NPOI.SS.Util;namespace ExportToExcel{public class ExportDgvToExcel{#region NPOI DataGridView 导出 EXCEL/// <summary>/// NPOI DataGridView 导出 EXCEL/// 03版Excel-xls最⼤⾏数是65536⾏,最⼤列数是256列/// 07版Excel-xlsx最⼤⾏数是1048576⾏,最⼤列数是16384列/// </summary>/// <param name="fileName">默认保存⽂件名</param>/// <param name="dgv">DataGridView</param>/// <param name="fontname">字体名称</param>/// <param name="fontsize">字体⼤⼩</param>public void ExportExcel(string fileName, DataGridView dgv, string fontname, short fontsize){IWorkbook workbook;ISheet sheet;Stopwatch sw = null;//判断datagridview中内容是否为空if (dgv.Rows.Count == 0){MessageBox.Show("DataGridView中内容为空,请先导⼊数据!", "提⽰", MessageBoxButtons.OK, MessageBoxIcon.Warning); return;}//保存⽂件string saveFileName = "";SaveFileDialog saveFileDialog = new SaveFileDialog();saveFileDialog.DefaultExt = "xls";saveFileDialog.Filter = "Excel⽂件(*.xls)|*.xls|Excel⽂件(*.xlsx)|*.xlsx";saveFileDialog.RestoreDirectory = true;saveFileDialog.Title = "Excel⽂件保存路径";saveFileDialog.FileName = fileName;MemoryStream ms = new MemoryStream(); //MemoryStreamif (saveFileDialog.ShowDialog() == DialogResult.OK)//**程序开始计时**//sw = new Stopwatch();sw.Start();saveFileName = saveFileDialog.FileName;//检测⽂件是否被占⽤if (!CheckFiles(saveFileName)){MessageBox.Show("⽂件被占⽤,请关闭⽂件" + saveFileName);workbook = null;ms.Close();ms.Dispose();return;}}else{workbook = null;ms.Close();ms.Dispose();}//*** 根据扩展名xls和xlsx来创建对象string fileExt = Path.GetExtension(saveFileName).ToLower();if (fileExt == ".xlsx"){workbook = new XSSFWorkbook();}else if (fileExt == ".xls"){workbook = new HSSFWorkbook();}else{workbook = null;}//***//创建Sheetif (workbook != null){sheet = workbook.CreateSheet("Sheet1");//Sheet的名称}else{return;}//设置单元格样式ICellStyle cellStyle = workbook.CreateCellStyle();//⽔平居中对齐和垂直居中对齐cellStyle.Alignment = erModel.HorizontalAlignment.Center;cellStyle.VerticalAlignment = erModel.VerticalAlignment.Center;//设置字体IFont font = workbook.CreateFont();font.FontName = fontname;//字体名称font.FontHeightInPoints = fontsize;//字号font.Color = NPOI.HSSF.Util.HSSFColor.Black.Index;//字体颜⾊cellStyle.SetFont(font);//添加列名IRow headRow = sheet.CreateRow(0);for (int i = 0; i < dgv.Columns.Count; i++){//隐藏⾏列不导出if (dgv.Columns[i].Visible == true){headRow.CreateCell(i).SetCellValue(dgv.Columns[i].HeaderText);headRow.GetCell(i).CellStyle = cellStyle;}}//根据类型写⼊内容for (int rowNum = 0; rowNum < dgv.Rows.Count; rowNum++){///跳过第⼀⾏,第⼀⾏为列名IRow dataRow = sheet.CreateRow(rowNum + 1);for (int columnNum = 0; columnNum < dgv.Columns.Count; columnNum++){int columnWidth = sheet.GetColumnWidth(columnNum) / 256; //列宽//隐藏⾏列不导出if (dgv.Rows[rowNum].Visible == true && dgv.Columns[columnNum].Visible == true) {//防⽌⾏列超出Excel限制if (fileExt == ".xls"){//03版Excel最⼤⾏数是65536⾏,最⼤列数是256列if (rowNum > 65536){MessageBox.Show("⾏数超过Excel限制!");return;}if (columnNum > 256){MessageBox.Show("列数超过Excel限制!");return;}}else if (fileExt == ".xlsx"){//07版Excel最⼤⾏数是1048576⾏,最⼤列数是16384列if (rowNum > 1048576)MessageBox.Show("⾏数超过Excel限制!");return;}if (columnNum > 16384){MessageBox.Show("列数超过Excel限制!");return;}}ICell cell = dataRow.CreateCell(columnNum);if (dgv.Rows[rowNum].Cells[columnNum].Value == null){cell.SetCellType(CellType.Blank);}else{if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Int32")){cell.SetCellValue(Convert.ToInt32(dgv.Rows[rowNum].Cells[columnNum].Value));}else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.String")){cell.SetCellValue(dgv.Rows[rowNum].Cells[columnNum].Value.ToString());}else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Single")){cell.SetCellValue(Convert.ToSingle(dgv.Rows[rowNum].Cells[columnNum].Value));}else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Double")){cell.SetCellValue(Convert.ToDouble(dgv.Rows[rowNum].Cells[columnNum].Value));}else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Decimal")){cell.SetCellValue(Convert.ToDouble(dgv.Rows[rowNum].Cells[columnNum].Value));}else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.DateTime")){cell.SetCellValue(Convert.ToDateTime(dgv.Rows[rowNum].Cells[columnNum].Value).ToString("yyyy-MM-dd"));}else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.DBNull")){cell.SetCellValue("");}}//设置列宽IRow currentRow;if (sheet.GetRow(rowNum) == null){currentRow = sheet.CreateRow(rowNum);}else{currentRow = sheet.GetRow(rowNum);}if (currentRow.GetCell(columnNum) != null){ICell currentCell = currentRow.GetCell(columnNum);int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;if (columnWidth < length){columnWidth = length + 10; //设置列宽数值}}sheet.SetColumnWidth(columnNum, columnWidth * 256);//单元格样式dataRow.GetCell(columnNum).CellStyle = cellStyle;}}}//保存为Excel⽂件workbook.Write(ms);FileStream file = new FileStream(saveFileName, FileMode.Create);workbook.Write(file);file.Close();workbook = null;ms.Close();ms.Dispose();//**程序结束计时**//sw.Stop();double totalTime = sw.ElapsedMilliseconds / 1000.0;MessageBox.Show(fileName + " 导出成功\n耗时" + totalTime + "s", "提⽰", MessageBoxButtons.OK, rmation); }#endregion#region 检测⽂件是否被占⽤/// <summary>/// 判定⽂件是否打开/// </summary>[DllImport("kernel32.dll")]public static extern IntPtr _lopen(string lpPathName, int iReadWrite);[DllImport("kernel32.dll")]public static extern bool CloseHandle(IntPtr hObject);public const int OF_READWRITE = 2;public const int OF_SHARE_DENY_NONE = 0x40;public readonly IntPtr HFILE_ERROR = new IntPtr(-1);/// <summary>/// 检测⽂件被占⽤/// </summary>/// <param name="FileNames">要检测的⽂件路径</param>/// <returns></returns>public bool CheckFiles(string FileNames){if (!File.Exists(FileNames)){//⽂件不存在return true;}IntPtr vHandle = _lopen(FileNames, OF_READWRITE | OF_SHARE_DENY_NONE); if (vHandle == HFILE_ERROR){//⽂件被占⽤return false;}//⽂件没被占⽤CloseHandle(vHandle);return true;}#endregion}}三、调⽤private void btnExport_Click_1(object sender, EventArgs e){ExportDgvToExcel export = new ExportDgvToExcel();export.ExportExcel("", dgv, "宋体", 11);//默认⽂件名,DataGridView控件的名称,字体,字号}。

GridView导出到Excel或Word文件

GridView导出到Excel或Word文件收藏在项目中我们经常会遇到要求将一些数据导出成Excel或者Word表格的情况，比如中国移动（我是中国移动用户）网上查话费的页面中就有一个导出到Excel的功能，光大网上银行查看历史明细也有这些功能....，原本以为这个问题不难的，不过看到网上经常有朋友问，于是我整理了一下，供大家参考。

前台页面：<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ExportDemo. aspx.cs" Inherits="ExportDemo" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http:// /TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="/1999/xhtml"><head runat="server"><title>GridView导出到Excel或Word文件——周公的博客：http://blog.csdn.ne t</title></head><body><form id="form1" runat="server"><div><asp:GridView ID="gvPersonList" runat="server" AutoGenerateColumns ="False"><Columns><asp:BoundField DataField="Id" HeaderText="编号"/><asp:BoundField DataField="Name" HeaderText="姓名"/><asp:TemplateField HeaderText="性别"><ItemTemplate><%# Eval("Sex").ToString()=="true"?"男":"女" %></ItemTemplate></asp:TemplateField><asp:BoundField DataField="Age" HeaderText="年龄"/><asp:TemplateField HeaderText="婚否"><ItemTemplate><%# Boolean.Parse(Eval("Married").ToString())==true?"是": "否" %></ItemTemplate></asp:TemplateField></Columns></asp:GridView><asp:Button ID="btnToExcel" runat="server" OnClick="btnToExcel_Clic k" Text="导出到Excel"/><asp:Button ID="btnToWord" runat="server" OnClick="btnToWord_Click" Text="导出到Word"/></div></form></body></html>后台代码：using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;///<summary>///程序说明：这是一个GridView导出成Excel或者Word文件的实例。

导出DataGridView数据到指定Excel模板

导出DataGridView数据到指定Excel模板string path2 = "C:/Documents and Settings/Administrator/桌面/日返工率.xls";//导出模板文件路径DirectoryInfo dinfo = new DirectoryInfo(path2);string strStart = dateTimePicker1.Value.ToString();UnPercentDal dataDal = new UnPercentDal();DataTable MyDS_Grid = dataDal.GetUnPercentDal(strStart);string FileName = "日返工率";string FileName2 = DateTime.T oday.ToString("yyyyMMdd") + newRandom(/doc/6c1224707.html,lisecon d).Next(10000).T oString();Microsoft.Office.Interop.Excel.Application xApp = newMicrosoft.Office.Interop.Excel.ApplicationClass();xApp.Visible = true;//得到WorkBook对象, 下面的是打开已有的文件Microsoft.Office.Interop.Excel.Workbook xBook = xApp.Workbooks._Open(path2, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);Microsoft.Office.Interop.Excel.Worksheet xSheet =(Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[1];foreach (DataRow row in MyDS_Grid.Rows){Microsoft.Office.Interop.Excel.Range rng1 = xSheet.get_Range("A1", Missing.Value);//插入数据到A1行rng1.Value2 = DateTime.Now.ToShortDateString();Microsoft.Office.Interop.Excel.Range rng2 =xSheet.get_Range("A2", Missing.Value);rng2.Value2 = row["OrderCode"].ToString();Microsoft.Office.Interop.Excel.Range rng3 = xSheet.get_Range("A3", Missing.Value);rng3.Value2 = row["SerialNum"].ToString();Microsoft.Office.Interop.Excel.Range rng4 = xSheet.get_Range("A4", Missing.Value);rng4.Value2 = row["MesSort"].T oString();Microsoft.Office.Interop.Excel.Range rng7 = xSheet.get_Range("A7", Missing.Value);rng7.Value2 = row["BrushDate"].ToString();Microsoft.Office.Interop.Excel.Range rng8 = xSheet.get_Range("B6", Missing.Value);rng8.Value2 = row["EmployeeName"].T oString() + "元/m\u00b2";//"m\u00b2"是输出m上标2就是平方米。

C#实现连接数据库查询，将dgv控件数据导出和导入Excel

C#实现连接数据库查询，将dgv控件数据导出和导⼊Excel 前提：主页⾯主页⾯FOm1的代码DataTable dt = new DataTable();dt.Columns.Add("epc");dt.Columns.Add("crc");dt.Columns.Add("pc");dt.Columns.Add("time");dataGridView1.DataSource = dt;dataGridView1.Columns[0].Width = 205;dataGridView1.Columns[1].Width = 200;dataGridView1.Columns[2].Width = 190;dataGridView1.Columns[3].Width = 200;进度条⽅法代码：private void SetProgressMessage(int pro, ProgressBar proBar){//如果当前调⽤⽅不是创建控件的⼀⽅，则需要使⽤this.Invoke()//在这⾥，ProgressBar控件是由主线程创建的，所以⼦线程要对该控件进⾏操作//必须执⾏this.InvokeRequired进⾏判断。

if (this.InvokeRequired){DelSetPro setPro = new DelSetPro(SetProgressMessage);this.Invoke(setPro, new object[] { pro, proBar });}else{proBar.Value = Convert.ToInt32(pro);}}//调⽤进度条的主⽅法private void SleepForProgressBar01(){for (int i = 1; i <= 100; i++){Thread.Sleep(10);SetProgressMessage(i, progressBar1);}//DialogResult dr01 = MessageBox.Show("ProgressBar01 has been finished!");//if (dr01.Equals(DialogResult.OK))//{// SetProgressBarVisi(progressBar1);//}}好了，界⾯布局代码写好之后就开始写功能代码了第⼀步：连接数据，把数据库表的数据放⼊DGV控件⾥1、⾸先新建⼀个sqlbase类public class sqlbase{//我这⾥连接的是Sqlserver数据库（server：数据库服务器名称，database：你要操作的数据库名称，其余的是你的登录名称和密码）public static string ConnectionStr = "server=Lenovo;database=TEST;uid=sa;pwd=aa1997924";//这个⽅法表⽰可以对数据库进⾏增删改的操作public static int ExecuteNonQuery(string sql){int result = 0;using (SqlConnection con = new SqlConnection(ConnectionStr)) {if (con.State == ConnectionState.Closed){con.Open();}try{SqlCommand cmd = new SqlCommand(sql, con);result = cmd.ExecuteNonQuery();}catch{result = 0;}}return result;}//这个⽅法是对数据库进⾏查询public static DataSet GetDataTableBySql(string sql){//DataTable dt = new DataTable();DataSet ds = new DataSet();using (SqlConnection con = new SqlConnection(ConnectionStr)) {if (con.State == ConnectionState.Closed){con.Open();}try{SqlDataAdapter sda = new SqlDataAdapter(sql, con);sda.Fill(ds);}catch{ds = null;}}return ds;}2、双击查询按钮，在事件中写如下代码string strsql = "select * from test";//查询语句DataSet data1 = sqlbase.GetDataTableBySql(strsql); dataGridView1.DataSource = data1.Tables[0]; SleepForProgressBar01();点击查询按钮：如下显⽰第⼆步：将Excel导⼊1、在导⼊按钮事件中写如下代码：private void btn_daoru_Click(object sender, EventArgs e){try{//获取Excel⽂件路径和名称OpenFileDialog odXls = new OpenFileDialog();//指定相应的打开⽂档的⽬录 AppDomain.CurrentDomain.BaseDirectory定位到Debug⽬录，再根据实际情况进⾏⽬录调整string folderPath = AppDomain.CurrentDomain.BaseDirectory + @"databackup\";odXls.InitialDirectory = folderPath;// 设置⽂件格式odXls.Filter = "Excel files office2003(*.xls)|*.xls|Excel office2010(*.xlsx)|*.xlsx|All files (*.*)|*.*";//openFileDialog1.Filter = "图⽚⽂件(*.jpg)|*.jpg|(*.JPEG)|*.jpeg|(*.PNG)|*.png";odXls.FilterIndex = 2;odXls.RestoreDirectory = true;if (odXls.ShowDialog() == DialogResult.OK){this.txtFilePath.Text = odXls.FileName;this.txtFilePath.ReadOnly = true;string sConnString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source={0};" + "Extended Properties='Excel8.0;HDR=NO;IMEX=1';", odXls.FileName);if ((System.IO.Path.GetExtension(txtFilePath.Text.Trim())).ToLower() == ".xls"){sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "data source=" + odXls.FileName + ";Extended Properties=Excel 5.0;Persist Security Info=False";//sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtFilePath.Text.Trim() + ";Extended Properties=\"Excel 8.0;HDR=" + strHead + ";IMEX=1\"";}using (OleDbConnection oleDbConn = new OleDbConnection(sConnString)){oleDbConn.Open();DataTable dt = oleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });//判断是否cmb中已有数据，有则清空if (cmbtablename.Items.Count > 0){cmbtablename.DataSource = null;cmbtablename.Items.Clear();}//遍历dt的rows得到所有的TABLE_NAME，并Add到cmb中foreach (DataRow dr in dt.Rows){cmbtablename.Items.Add((String)dr["TABLE_NAME"]);}if (cmbtablename.Items.Count > 0){cmbtablename.SelectedIndex = 0;}SleepForProgressBar01();// btnshow_Click();MessageBox.Show("导⼊成功");//加载Excel⽂件数据按钮// this.btnshow.Enabled = true;}}}catch (Exception ex){MessageBox.Show(ex.Message);}}2、点击导⼊后会弹框让你选择⽂件3、选择你要导⼊的Excel⽂件，显⽰如下界⾯，textbox控件会显⽰⽂件地址，combox会显⽰⽂件的多个分表第三步、将导⼊的⽂档放⼊DGV控件中1、新增⼀个加载⽅法private void btnshow_Click(){#region 读取相应的表名的Excel⽂件中数据到当前DataGridview中显⽰OleDbConnection ole = null;OleDbDataAdapter da = null;DataTable dt = null;string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source={0};" + "Extended Properties='Excel8.0;HDR=NO;IMEX=1';", txtFilePath.Text.Trim());if ((System.IO.Path.GetExtension(txtFilePath.Text.Trim())).ToLower() == ".xls"){strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "data source=" + txtFilePath.Text.Trim() + ";Extended Properties=Excel 5.0;Persist Security Info=False";//sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtFilePath.Text.Trim() + ";Extended Properties=\"Excel 8.0;HDR=" + strHead + ";IMEX=1\"";}string sTableName = cmbtablename.Text.Trim();string strExcel = "select * from [" + sTableName + "]";try{ole = new OleDbConnection(strConn);ole.Open();da = new OleDbDataAdapter(strExcel, ole);dt = new DataTable();da.Fill(dt);this.dataGridView1.DataSource = dt;//因为⽣成Excel的时候第⼀⾏是标题，所以要做如下操作：//1.修改DataGridView列头的名字，//2.数据列表中删除第⼀⾏for (int i = 0; i < dt.Columns.Count; i++){//dgvdata.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;//dgvdata.Columns[i].Name = dt.Columns[i].ColumnName;dataGridView1.Columns[i].HeaderCell.Value = dt.Rows[0][i].ToString();//c# winform ⽤代码修改DataGridView列头的名字，设置列名,修改列名}//DataGridView删除⾏dataGridView1.Rows.Remove(dataGridView1.Rows[0]);//删除第⼀⾏//dgvdata.Rows.Remove(dgvdata.CurrentRow);//删除当前光标所在⾏//dgvdata.Rows.Remove(dgvdata.Rows[dgvdata.Rows.Count - 1]);//删除最后⼀⾏//dgvdata.Rows.Clear();//删除所有⾏ole.Close();}catch (Exception ex){MessageBox.Show(ex.Message);}finally{if (ole != null)ole.Close();}#endregion}2、在加载按钮事件中调⽤上⾯⽅法即可3、点击加载按钮，如下显⽰注意：这⾥可以选择，第⼆个分表进⾏导⼊（导⼊的字段名可以和绑定dgv列名不同，并不限制列的个数）第四部：导出Excel（将dgv控件的数据表导出Excel⽂档）1、新建⽅法导出⽅法private void ExportExcels(string fileName, DataGridView myDGV){string saveFileName = "";SaveFileDialog saveDialog = new SaveFileDialog();saveDialog.DefaultExt = "xls";saveDialog.Filter = "Excel⽂件|*.xls";saveDialog.FileName = fileName;saveDialog.ShowDialog();saveFileName = saveDialog.FileName;if (saveFileName.IndexOf(":") < 0) return; //被点了取消Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();if (xlApp == null){MessageBox.Show("⽆法创建Excel对象，可能您的机⼦未安装Excel");return;}Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1//写⼊标题for (int i = 0; i < myDGV.ColumnCount; i++){worksheet.Cells[1, i + 1] = myDGV.Columns[i].HeaderText;}bel6.Visible = true;bel6.Text = myDGV.Rows.Count.ToString();//总进度//写⼊数值for (int r = 0; r < myDGV.Rows.Count; r++){bel3.Visible = true;bel3.Text = r.ToString();//实时进度for (int i = 0; i < myDGV.ColumnCount; i++){worksheet.Cells[r + 2, i + 1] = myDGV.Rows[r].Cells[i].Value;}System.Windows.Forms.Application.DoEvents();}worksheet.Columns.EntireColumn.AutoFit();//列宽⾃适应if (saveFileName != ""){try{workbook.Saved = true;workbook.SaveCopyAs(saveFileName);}catch (Exception ex){MessageBox.Show("导出⽂件时出错,⽂件可能正被打开！\n" + ex.Message);}}xlApp.Quit();GC.Collect();//强⾏销毁MessageBox.Show("⽂件： " + fileName + ".xls 保存成功", "信息提⽰", MessageBoxButtons.OK, rmation); }2、在导出事件写如下代码private void btnshow_Click(object sender, EventArgs e){if (this.dataGridView1.Rows.Count<=0){MessageBox.Show("当前没有可导出的数据！");return;}string a = "D:" + "\\KKHMD.xls";ExportExcels(a, dataGridView1);}3、单击导出，显⽰如下，选择保存地址注意：我这⾥添加了进度显⽰：4、导出成功好了，⾄此所有功能以实现1、连接数据库，将数据绑定到的dgv控件2、Excel的导⼊3、Excel的导出。

把WinForm的DataGridView的数据导出到Excel三种方法

1.#region DataGridView数据显示到Excel2./// <summary>3./// 打开Excel并将DataGridView控件中数据导出到Excel4./// </summary>5./// <param name="dgv">DataGridView对象</param>6./// <param name="isShowExcle">是否显示Excel界面</param>7./// <remarks>8./// add com "Microsoft Excel 11.0 Object Library"9./// using Excel=Microsoft.Office.Interop.Excel;10./// </remarks>11./// <returns> </returns>12.public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle)13.{14.if(dgv.Rows.Count == 0)15.return false;16.//建立Excel对象17.Excel.Application excel = new Excel.Application();18.excel.Application.Workbooks.Add(true);19.excel.Visible = isShowExcle;20.//生成字段名称21.for(int i = 0; i < dgv.ColumnCount; i++)22.{23.excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;24.}25.//填充数据26.for(int i = 0; i < dgv.RowCount - 1; i++)27.{28.for(int j = 0; j < dgv.ColumnCount; j++)29.{30.if(dgv[j, i].ValueType == typeof(string))31.{32.excel.Cells[i + 2, j + 1] = "'"+ dgv[j, i].Value.ToString();33.}34.else35.{36.excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();38.}39.}40.return true;41.}42.#endregion43.44.#region DateGridView导出到csv格式的Excel45./// <summary>46./// 常用方法，列之间加\t，一行一行输出，此文件其实是csv文件，不过默认可以当成Excel打开。

C#DataGridView导出到Excel

else
objData[rowIndex, colIndex] = myDataGridView.Rows[rowIndex - 1].Cells[colIndexs[colIndex]].Value.ToString();
}
System.Windows.Forms.Application.DoEvents();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
xlSheet = null;
if (filenameString.Trim() == "") return false;
FileInfo file = new FileInfo(filenameString);
if (file.Exists)
{
try
int rowCount = myDataGridView.RowCount;
List<int> colIndexs = new List<int>();
// 创建缓存数据
object[,] objData = new object[rowCount + 1, dispcolCount];
{
MessageBox.Show("保存出错,请检查!");
return false;
}
finally
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook.Worksheets);

c#中datagridview数据导出到excel源码

private void button5_Click_1(object sender, EventArgs e){//建立Excel对象Excel.Application excel = new Excel.Application();excel.Application.Workbooks.Add(true);//生成字段名称for (int i = 0; i < dataGridView2.ColumnCount; i++){excel.Cells[1,i + 1] = dataGridView2.Columns[i].HeaderText;if (y == 0){y = 1;toolStripStatusLabel6.Text = "数据导入中，请等待!";}}//填充数据for (int i = 0; i < dataGridView2.RowCount - 1; i++){for (int j = 0; j < dataGridView2.ColumnCount; j++){if (dataGridView2[j, i].Value == typeof(string)){excel.Cells[i + 2, j + 1] = "" + dataGridView2[i, j].Value.ToString();}else{excel.Cells[i + 2, j + 1] = dataGridView2[j, i].Value.ToString();}}}excel.Visible = true;}===================================================================[原创]万能，高效-C#导出数据到Excel2008年03月18日星期二下午12:56 class CommanPrint{/// <summary>/// 导出Excel/// 版权所有: 天山寒雪QQ:757015000 MSN: haijun.qin@/// </summary>/// <param name="mydgv">控件DataGridView</param>/// <param name="dic">中英文对照的标题</param>public static void ExportTasks(DataGridView mydgv, Dictionary<string, string> dic) {// 定义要使用的Excel 组件接口// 定义Application 对象,此对象表示整个Excel 程序Microsoft.Office.Interop.Excel.Application excelApp = null;// 定义Workbook对象,此对象代表工作薄Microsoft.Office.Interop.Excel.Workbook workBook;// 定义Worksheet 对象,此对象表示Execel 中的一张工作表Microsoft.Office.Interop.Excel.Worksheet ws = null;//定义Range对象,此对象代表单元格区域Microsoft.Office.Interop.Excel.Range range;int dcell = 1;int rowindex = 0; int colindex = 0;int rowcount = mydgv.Rows.Count;int colcount = mydgv.Columns.Count;int dispcolcount = dic.Count;try{//初始化Application 对象excelAppexcelApp = new Microsoft.Office.Interop.Excel.Application();//在工作薄的第一个工作表上创建任务列表workBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);ws = (Worksheet)workBook.Worksheets[1];// 命名工作表的名称为 = "Sheet1";//创建缓存Object[,] objdata = new object[rowcount + 1, colcount];//创建标题foreach (string s in dic.Keys){objdata[rowindex, colindex++] = dic[s].ToString();}//获取数据for (int i = 0; i < rowcount; i++){dcell = 0;foreach (string ss in dic.Keys){for (int j = 0; j < colcount; j++){if (mydgv.Columns[j].Name == ss){objdata[i + 1, dcell++] = mydgv.Rows[i].Cells[j].FormattedValue.ToString(); //得到样式之后的值}}}}//写入Excelrange = ws.get_Range(excelApp.Cells[1, 1], excelApp.Cells[rowcount, dispcolcount]); range.Value2 = objdata;System.Windows.Forms.Application.DoEvents();//设置格式excelApp.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft; //全局左对齐excelApp.Cells.EntireColumn.AutoFit();range = ws.get_Range(excelApp.Cells[1, 1], excelApp.Cells[1, colcount]);range.Font.Bold = true; //标题粗体//显示ExcelexcelApp.Visible = true;}catch (Exception ex){throw ex;}============================================================using System;using System.Collections.Generic;using System.Text;using System.Web.UI.WebControls;using System.Data;using System.Web.UI.HtmlControls;namespace Commonpublic class ExcelHelper{// Excel导出public static void Export(GridView dgExport, DataTable dtData){System.Web.HttpContext curContext = System.Web.HttpContext.Current;// IO用于导出并返回excel文件System.IO.StringWriter strWriter = null;System.Web.UI.HtmlTextWriter htmlWriter = null;if (dtData != null){// 设置编码和附件格式curContext.Response.Clear();curContext.Response.ClearContent();curContext.Response.Buffer = true;curContext.Response.ContentType = "application/vnd.ms-excel";curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-7");curContext.Response.Charset = "GB2312";curContext.Response.AppendHeader("content-disposition", "filename=\"" + System.Web.HttpUtility.UrlEncode(dtData.TableName, System.Text.Encoding.UTF8) + ".xls\"");// 导出excel文件strWriter = new System.IO.StringWriter();htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);HtmlForm frm = new HtmlForm();frm.Attributes["runat"] = "server";frm.Controls.Add(dgExport);dgExport.DataSource = dtData.DefaultView;dgExport.DataBind();// 返回客户端dgExport.RenderControl(htmlWriter);curContext.Response.Write(strWriter.ToString());curContext.Response.End();}}}}。

把WinForm的DataGridView的数据导出到Excel三种方法

把WinForm的DataGridView的数据导出到Excel三种方法导出WinForm的DataGridView数据到Excel有多种方法，下面将详细介绍三种常用的方法：方法一：使用Microsoft.Office.Interop.Excel库这是一种常用的方法，使用Microsoft.Office.Interop.Excel库可以直接操作Excel文件。

首先，需要在项目中添加对Microsoft Office 的引用。

然后，可以按照以下步骤导出数据：1. 创建一个Excel应用程序对象：```csharpusing Excel = Microsoft.Office.Interop.Excel;Excel.Application excelApp = new Excel.Application(;```2.创建一个工作簿对象：```csharpExcel.Workbook workbook =excelApp.Workbooks.Add(Type.Missing);```3.创建一个工作表对象：```csharpExcel.Worksheet worksheet = workbook.ActiveSheet;```4. 将DataGridView中的数据导入到Excel中：```csharpfor (int i = 0; i < dataGridView.Rows.Count; i++)for (int j = 0; j < dataGridView.Columns.Count; j++)worksheet.Cells[i + 1, j + 1] =dataGridView.Rows[i].Cells[j].Value.ToString(;}```5. 保存Excel文件并关闭Excel应用程序：```csharpworkbook.SaveAs("路径\\文件名.xlsx");excelApp.Quit(;```方法二：使用OpenXml库OpenXml是一种用于操作Office文件的开放式标准。

C# winform Datagridview导出Excel两个方法

saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "导出文件保存路径";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
//strName储存保存EXCEL路径
Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
excel.Application.Workbooks.Add(true); ;
//生成字段名称，逐条写，无效率
for (int i = 0; i < gridView.ColumnCount; i++)
{
/// </summary>
/// <param name="gridView"></param>
/// <param name="saveFileDialog"></param>
public void ToExcel1(DataGridView gridView, SaveFileDialog saveFileDialog)
{
try
{
if (gridView.Rows.Count == 0)

GridView导出Excel

GridView导出Excel程序.//直接使用的方法public virtual void ToExcel(){if (CheckSearchField()){DoExcel(GetArray());}else{Sys_UserFunction.PageRegisterAlert(this.Page, up_Panel, "请查询后再预览报表");}}//判断是否可预览private Boolean CheckSearchField(){Boolean SearchField = false;if (RptTitle != ""){SearchField = true;}return SearchField;}//获取GridView字段方法private string GetArray(){string Titles = string.Empty;string ColNames = string.Empty;int i = 0;foreach (TableCell item in BaseGrid.HeaderRow.Cells){if (item.Controls.Count != 0){Titles += (item.Controls[0] as LinkButton).Text;}else{if (this.BaseGrid.Columns[i] is BoundField){BoundField b_Col umn = (BoundField)this.BaseGrid.Columns[i];if (b_Column.Visible){ColNames += b_Column.DataField.ToUpper() + ",";Titles += item.Text + ",";}}i++;}}return ColNames.Substring(0, ColNames.Length - 1) + "|" + Titles.Substring(0, Titles.Length - 1);}//导出Excel实现方式public virtual void DoExcel(string ExcelExInfo){string strFileName = "Export.xls";string[] Fields = ExcelExInfo.Split('|')[0].Split(',');string[] Titles = ExcelExInfo.Split('|')[1].Split(',');DataTable dtTable = new DataTable();dtTable = RptData();System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(strFileName)); //防止文件名出现乱码System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; //设置输出文件类型为excel文件。

将DataGridView或DataTable导出为Excel支持进度条

将DataGridView或DataT able导出为Excel支持进度条使用说明://编程语言:C# 主要用于Winforms应用程序的报表导出操作。

//使用时，请添加引用微软的Microsoft.Office.Interop.Excel组件//在以前的上传文档基础上增加两个重载方法，共四种参数的重载：//重载方法1:ExportToExcel(System.Data.DataTable dt)//重载方法2:ExportToExcel(DataGridView dgv)//重载方法3:ExportToExcel(System.Data.DataTable dt, ProgressBar pb)//重载方法4:ExportToExcel(DataGridView dgv, ProgressBar pb)//最后两个重载方法支持进度条控件源代码如下:using System;using System.Collections.Generic;using System.Text;using Microsoft.Office.Interop.Excel;using System.Windows.Forms;namespace DoorControl{/// <summary>/// 静态方法ExportToExcel提供四种参数的重载/// DataGridView或DataTable导出到Excel,可以选择是否添加进度条来查看导出excel 进度/// 需要使用进度条时,请在窗体中加入一个ProgressBar控件progressBar1,并设置progressBar1.Visible=false/// </summary>public class DataTableToExcel{/// <summary>/// 将DataGridV iew转化为DataTable/// </summary>/// <param name="dv"></param>/// <returns></returns>public static System.Data.DataTable dvtodt(DataGridView dv){System.Data.DataTable dt = new System.Data.DataTable();System.Data.DataColumn dc;for (int i = 0; i < dv.Columns.Count; i++){dc = new System.Data.DataColumn();dc.ColumnName = dv.Columns[i].HeaderText.ToString();dt.Columns.Add(dc);}for (int j = 0; j < dv.Rows.Count; j++){System.Data.DataRow dr = dt.NewRow();for (int x = 0; x < dv.Columns.Count; x++){dr[x] = dv.Rows[j].Cells[x].V alue;}dt.Rows.Add(dr);}return dt;}/// <summary>/// 将DataGridV iew导出为Excel文件(.xls)/// </summary>/// <param name="dgv">要导出的DatGridView</param>public static void ExportToExcel(DataGridView dgv){//获得DataTableSystem.Data.DataTable dt = dvtodt(dgv);ExportToExcel(dt);}/// <summary>/// 将DataGridV iew导出为excel文件,导出过程中显示进度条/// </summary>/// <param name="dgv">要导出的DatGridView</param>/// <param name="pb">与dgv在同一窗体的ProgressBar控件,pb初始化时Visible=false</param>public static void ExportToExcel(DataGridView dgv, ProgressBar pb){//获得DataTableSystem.Data.DataTable dt = dvtodt(dgv);ExportToExcel(dt, pb);}/// <summary>/// 将DataTable导出为Excel文件(.xls)/// </summary>/// <param name="dt">要导出的DataTable</param>public static void ExportToExcel(System.Data.DataTable dt){if (dt == null) return;Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();if (xlApp == null){MessageBox.Show("无法创建Excel对象，可能您的电脑未安装Excel");return;}System.Windows.Forms.SaveFileDialog saveDia = new SaveFileDialog();saveDia.Filter = "Excel|*.xls";saveDia.Title = "导出为Excel文件";saveDia.FileName = string.Format("{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss"));if (saveDia.ShowDialog() == System.Windows.Forms.DialogResult.OK&& !string.Empty.Equals(saveDia.FileName)){Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBA Template.xlWBA TWorksheet);Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1Microsoft.Office.Interop.Excel.Range range = null;long totalCount = dt.Rows.Count;long rowRead = 0;float percent = 0;string fileName = saveDia.FileName;//写入标题for (int i = 0; i < dt.Columns.Count; i++){worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];//range.Interior.ColorIndex = 15;//背景颜色range.Font.Bold = true;//粗体range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中//加边框range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin,Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);//range.ColumnWidth = 4.63;//设置列宽//range.EntireColumn.AutoFit();//自动调整列宽//r1.EntireRow.AutoFit();//自动调整行高}//写入内容for (int r = 0; r < dt.DefaultView.Count; r++){for (int i = 0; i < dt.Columns.Count; i++){worksheet.Cells[r + 2, i + 1] = dt.DefaultView[r][i];range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, i + 1];range.Font.Size = 9;//字体大小//加边框range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin,Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);range.EntireColumn.AutoFit();//自动调整列宽}rowRead++;percent = ((float)(100 * rowRead)) / totalCount;////进度条//pb.Maximum = (int)totalCount;//pb.V alue = (int)rowRead;System.Windows.Forms.Application.DoEvents();}range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;if (dt.Columns.Count > 1){range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideV ertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;}try{workbook.Saved = true;workbook.SaveCopyAs(fileName);}catch (Exception ex){MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);return;}workbooks.Close();if (xlApp != null){xlApp.Workbooks.Close();xlApp.Quit();int generation = System.GC.GetGeneration(xlApp);System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);xlApp = null;System.GC.Collect(generation);}GC.Collect();//强行销毁#region 强行杀死最近打开的Excel进程System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");System.DateTime startTime = new DateTime();int m, killId = 0;for (m = 0; m < excelProc.Length; m++){if (startTime < excelProc[m].StartTime){startTime = excelProc[m].StartTime;killId = m;}}if (excelProc[killId].HasExited == false){excelProc[killId].Kill();}#endregionMessageBox.Show("导出成功!");}}/// <summary>/// 将DataTable导出为excel文件,导出过程中显示进度条/// </summary>/// <param name="dt">要导出的DataTable对象</param>/// <param name="pb">窗体中的ProgressBar控件,pb初始化时Visible=false</param>public static void ExportToExcel(System.Data.DataTable dt, ProgressBar pb){if (dt == null) return;Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();if (xlApp == null){MessageBox.Show("无法创建Excel对象，可能您的电脑未安装Excel");return;}System.Windows.Forms.SaveFileDialog saveDia = new SaveFileDialog();saveDia.Filter = "Excel|*.xls";saveDia.Title = "导出为Excel文件";saveDia.FileName = string.Format("{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss"));if (saveDia.ShowDialog() == System.Windows.Forms.DialogResult.OK&& !string.Empty.Equals(saveDia.FileName)){Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBA Template.xlWBA TWorksheet);Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1Microsoft.Office.Interop.Excel.Range range = null;long totalCount = dt.Rows.Count;pb.V isible = true;//将进度条控件显示pb.Maximum = (int)totalCount;//设置进度条控件的最大值long rowRead = 0;float percent = 0;string fileName = saveDia.FileName;//写入标题for (int i = 0; i < dt.Columns.Count; i++){worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];//range.Interior.ColorIndex = 15;//背景颜色range.Font.Bold = true;//粗体range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中//加边框range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin,Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);//range.ColumnWidth = 4.63;//设置列宽//range.EntireColumn.AutoFit();//自动调整列宽//r1.EntireRow.AutoFit();//自动调整行高}//写入内容for (int r = 0; r < dt.DefaultView.Count; r++){for (int i = 0; i < dt.Columns.Count; i++){worksheet.Cells[r + 2, i + 1] = dt.DefaultView[r][i];range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, i + 1];range.Font.Size = 9;//字体大小//加边框range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin,Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);range.EntireColumn.AutoFit();//自动调整列宽}rowRead++;percent = ((float)(100 * rowRead)) / totalCount;//进度条控件需要用百分号表示进度时,使用该行代码pb.V alue = (int)rowRead;//设置进度条控件的当前值System.Windows.Forms.Application.DoEvents();}range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;if (dt.Columns.Count > 1){range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideV ertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;}try{workbook.Saved = true;workbook.SaveCopyAs(fileName);}catch (Exception ex){MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);return;}workbooks.Close();if (xlApp != null){xlApp.Workbooks.Close();xlApp.Quit();int generation = System.GC.GetGeneration(xlApp);System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);xlApp = null;System.GC.Collect(generation);}GC.Collect();//强行销毁#region 强行杀死最近打开的Excel进程System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");System.DateTime startTime = new DateTime();int m, killId = 0;for (m = 0; m < excelProc.Length; m++){if (startTime < excelProc[m].StartTime){startTime = excelProc[m].StartTime;killId = m;}}if (excelProc[killId].HasExited == false){excelProc[killId].Kill();}#endregionpb.V isible = false;//再次将进度条隐藏MessageBox.Show("导出成功!");}}}}。

C#.NET将控件（DataGridView）中的数据导出到Excel中

C#.NET将控件（DataGridView）中的数据导出到Excel中public static void Exeort2Excel(string strCaption, DataGridView dgv){try{TimeSpan dateBegin = new TimeSpan(DateTime.Now.Ticks);//SaveFileDialog file = new SaveFileDialog();//file.DefaultExt = "xls";//file.Filter = "Excel文件|*.xls";////file.FilterIndex = 1;//string saveFilePath = "";//if (file.ShowDialog() == DialogResult.OK)//{// saveFilePath = file.FileName;//}//else//{// return;//}//-***************获取excel对象***************Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();if (app == null){MessageBox.Show("无法启动，可能你的机器上没有安装Excel！", "", MessageBoxButtons.OK, MessageBoxIcon.Warning);return;Microsoft.Office.Interop.Excel.Workbook workbook = app.Workbooks.Add(true);Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets [1];Microsoft.Office.Interop.Excel.Range range;//string oldCaption = "jiang_gy";//列索引，行索引，总列数，总行数int colIndex = 0;int rowIndex = 0;int colCount = dgv.Columns.Count;int rowCount = dgv.Rows.Count;// *****************获取数据*********************// 创建缓存数据object[,] objData = new object[rowCount + 3, colCount];// 创建列标题foreach (DataGridViewColumn col in dgv.Columns){objData[rowIndex, colIndex++] = col.HeaderText;}// 获取具体数据for (rowIndex = 1; rowIndex < rowCount; rowIndex++){for (colIndex = 0; colIndex < colCount; colIndex++){objData[rowIndex, colIndex] = dgv.Rows[rowIndex - 1].Cells[colIndex].Value;}objData[rowCount + 1, 0] = "合计：";// rowCount.ToString();objData[rowCount + 1, 1] = rowCount.ToString();//********************* 写入Excel*******************range = worksheet.get_Range(app.Cells[2, 1], app.Cells[rowCount + 3, colCount]);range.Value2 = objData;//range.Borders.LineStyle = 1;System.Windows.Forms.Application.DoEvents();range = worksheet.get_Range(app.Cells[2, 1], app.Cells[rowCount + 1, colCount]);range.Borders.LineStyle = 1;//********************* 设置输出格式****************//设置顶部说明range = worksheet.get_Range(app.Cells[1, 1], app.Cells[1, colCount]);range.MergeCells = true;range.RowHeight = 38;range.Font.Bold = true;range.Font.Size = 14;//range.Font.ColorIndex = 10;range.Borders.LineStyle = 1;app.ActiveCell.FormulaR1C1 = strCaption;//特殊数据格式//range = worksheet.get_Range(app.Cells[2, 1], app.Cells[rowCount, colCount]);//range.NumberFormat = "yyyy-MM-dd hh:mm:ss";app.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;range = worksheet.get_Range(app.Cells[2, 1], app.Cells[2, colCount]);//range.Interior.ColorIndex = 10;range.Font.Bold = true;range.RowHeight = 20;range.Borders.LineStyle = 1;((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 1]).ColumnWidth = 15;((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 2]).ColumnWidth = 30;((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 3]).ColumnWidth = 15;((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 4]).ColumnWidth = 32;((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 5]).ColumnWidth = 30;//设置合计为右对齐并加粗//range = worksheet.get_Range(app.Cells[rowCount + 2, 1], app.Cells[rowCount + 4, colCount]);//range.Borders.LineStyle = 0;range = worksheet.get_Range(app.Cells[rowCount + 3, 1], app.Cells[rowCount + 3, 1]);range.Font.Bold = true;range.HorizontalAlignment =Microsoft.Office.Interop.Excel.Constants.xlRight;//设置合计值为左对齐range = worksheet.get_Range(app.Cells[rowCount + 3, 2], app.Cells[rowCount + 3, 2]);range.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;//*********************** 保存 *********************try{//app.Save(saveFilePath);app.Visible = true;//workbook.Saved = true;//workbook.SaveCopyAs(saveFilePath);}catch (Exception ex){MessageBox.Show("保存文件出错:" + ex.Message);}//********************* 释放************************app.Quit();GC.Collect();TimeSpan dateEnd = new TimeSpan(DateTime.Now.Ticks);TimeSpan tspan = dateBegin.Subtract(dateEnd).Duration();//MessageBox.Show(tspan.ToString());//if (File.Exists(saveFileName))//{// System.Diagnostics.Process.Start(saveFileName);//}}catch (Exception ex){MessageBox.Show("无法启动，可能你的机器上没有安装Excel！"+ex.Message.ToString(), "", MessageBoxButtons.OK, MessageBoxIcon.Warning);}}。

GridView导出Excel的方法以及导出后文件怎样变成文本的形式

GridView导出Excel的方法以及导出后文件怎样变成文本的形式GridView导出Excel的方法一、带有复选框的导出excel文件protected void dc_Click(object sender, EventArgs e){Export("application/ms-excel", "预订已通过人员.xls");}//"预订已通过人员.xls"为excel文件的表头private void Export(string FileType, string FileName){Response.ContentEncoding = System.Text.Encoding.UTF7;Response.Clear();Response.Buffer = false;Response.Charset = "GB2312";Response.AppendHeader("Content-Disposition","attachment;filename=exl.xls");Response.ContentEncoding =System.Text.Encoding.GetEncoding("GB2312");Response.ContentType = "application/ms-excel";Response.Write("content=\\"text/html; charset=GB2312\\">");this.EnableViewState = false;//转换为table 表格HtmlTable ht = new HtmlTable();ht.Border = 1;HtmlTableRow htrt = new HtmlTableRow();HtmlTableCell htct = new HtmlTableCell();htct.ColSpan = 15;htct.Align = "center";htct.InnerHtml = "预订已通过人员";htrt.Cells.Add(htct);ht.Rows.Add(htrt);HtmlTableRow htrh = new HtmlTableRow();string[] strArray = { "ID", "领取ID", "工号", "姓名", "性别", "部门", "工种", "状态", "领取类型", "代领人", "物品", "预订时间","审核时间", "数量", "物品型号","接收人签字" };for (int i = 0; i < 16; i++){HtmlTableCell htc = new HtmlTableCell();htc.InnerHtml = "" + strArray[i] + "";htrh.Cells.Add(htc);}ht.Rows.Add(htrh);for (int i = 0; i < GV_ypck.Rows.Count; i++)//取行{HtmlTableRow htr = null;for (int j = 0; j < 17; j++)//取列{if (j == 0){CheckBox cb4this =(CheckBox)GV_ypck.Rows[i].Cells[0].FindControl("CheckBox1 ");if (!cb4this.Enabled || !cb4this.Checked){break;//取消取列elsehtr = new HtmlTableRow();}else if (j >= 1 && j <= 16){HtmlTableCell htc = new HtmlTableCell();htc.InnerT ext =(GV_ypck.Rows[i].Cells[j].Text.Trim() == " " ? "" :GV_ypck.Rows[i].Cells[j].Text.Trim());htr.Cells.Add(htc);}}if (htr != null && htr.Cells.Count > 0){ht.Rows.Add(htr);}}System.IO.StringWriter oStringWriter = newSystem.IO.StringWriter();HtmlTextWriter oHtmlTextWriter = newHtmlTextWriter(oStringWriter);//ht.Attributes.Add("style", "vnd.ms-excel.numberformat:@"); ht.RenderControl(oHtmlTextWriter);Response.Write(oStringWriter.ToString());Response.End();}//未将GridView放到runat="server"里public override void VerifyRenderingInServerForm(Control control) {二、不带复选框的导出excel文件//在gridview绑定时进行设置导出的excel文件为文本形式//ydwp.DataSource = dt;//ydwp.Attributes.Add("style", "vnd.ms-excel.numberformat:@");//ydwp.DataBind();// 导出列表信息到Excelprotected void dc_Click(object sender, EventArgs e){Export("application/ms-excel", "已领取人员表.xls");}private void Export(string FileType, string FileName){ydwp.AllowPaging = false;Response.Charset = "GB2312";Response.ContentEncoding = System.Text.Encoding.UTF7;Response.Clear();Response.Buffer = false;Response.Charset = "GB2312";Response.AppendHeader("Content-Disposition","attachment;filename=exl.xls");Response.ContentEncoding =System.Text.Encoding.GetEncoding("GB2312");Response.ContentType = "application/ms-excel";Response.Write("");//this.EnableViewState = false;System.IO.StringWriter oStringWriter = newSystem.IO.StringWriter();HtmlTextWriter oHtmlTextWriter = newHtmlTextWriter(oStringWriter);ydwp.RenderControl(oHtmlTextWriter);Response.Write(oStringWriter.ToString());Response.End();}//注：三、在导出的excel文件中往往需要将excel文件设置为文本的形式需要在方法或者绑定gridview中加.Attributes.Add("style", "vnd.ms-excel.numberformat:@");。

C#DataGridview用NPOI导出Excel文件

C#DataGridview⽤NPOI导出Excel⽂件导出excel我⽤的是nuget 的NPOI，直接在项⽬中添加的，引⽤到项⽬中，下⾯是截图：下⾯我把ExcelHelper贴出来public static class ExcelHelper{public static void ExportToExcel(DataTable dt){ExportToExcelData(dt);}public static DataTable ToDataTable(this DataGridView myDGV){DataTable dt = new DataTable();for (int i = 0; i < myDGV.ColumnCount; i++){dt.Columns.Add(myDGV.Columns[i].HeaderText);}//写⼊数值for (int r = 0; r < myDGV.Rows.Count; r++){List<object> values = new List<object>();for (int i = 0; i < myDGV.ColumnCount; i++){values.Add(myDGV.Rows[r].Cells[i].Value);}dt.Rows.Add(values .ToArray());}return dt;}#region导出///<summary>///数据导出///</summary>///<param name="data"></param>///<param name="sheetName"></param>public static void ExportToExcelData(this DataTable data){ExportToExcel(data, "Sheet1");}///<summary>///数据导出///</summary>///<param name="data"></param>///<param name="sheetName"></param>public static void ExportToExcel(this DataTable data, string sheetName){SaveFileDialog fileDialog = new SaveFileDialog();//fileDialog.Filter = "Excel(97-2003)|*.xls|Excel(2007-2013)|*.xlsx";fileDialog.Filter = "Excel|*.xls|Excel|*.xlsx";if (fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.Cancel){return;}IWorkbook workbook = new XSSFWorkbook();ISheet sheet = workbook.CreateSheet(sheetName);IRow rowHead = sheet.CreateRow(0);//填写表头for (int i = 0; i < data.Columns.Count; i++){rowHead.CreateCell(i, CellType.String).SetCellValue(data.Columns[i].ColumnName.ToString());}//填写内容for (int i = 0; i < data.Rows.Count; i++){IRow row = sheet.CreateRow(i + 1);for (int j = 0; j < data.Columns.Count; j++){row.CreateCell(j, CellType.String).SetCellValue(data.Rows[i][j].ToString());}}for (int i = 0; i < data.Columns.Count; i++){sheet.AutoSizeColumn(i);}using (FileStream stream = File.OpenWrite(fileDialog.FileName)){workbook.Write(stream);stream.Close();}MessageBox.Show("导出数据成功!", "提⽰", MessageBoxButtons.OK, rmation); GC.Collect();}#endregion#region导⼊///<summary>///导⼊的⽂件名///</summary>///<param name="fileName"></param>///<returns></returns>public static DataSet ExcelToDataSet(string fileName){return ExcelToDataSet(fileName, true);}///<summary>///返回dataset///</summary>///<param name="fileName"></param>///<param name="firstRowAsHeader"></param>///<returns></returns>public static DataSet ExcelToDataSet(string fileName, bool firstRowAsHeader){int sheetCount = 0;return ExcelToDataSet(fileName, firstRowAsHeader, out sheetCount);}///<summary>///返回dataset///</summary>///<param name="fileName">⽂件名</param>///<param name="firstRowAsHeader">⽂件头</param>///<param name="sheetCount">内容</param>///<returns></returns>public static DataSet ExcelToDataSet(string fileName, bool firstRowAsHeader, out int sheetCount){using (DataSet ds = new DataSet()){using (FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read)){IWorkbook workbook = WorkbookFactory.Create(fileStream);IFormulaEvaluator evaluator = WorkbookFactory.CreateFormulaEvaluator(workbook);sheetCount = workbook.NumberOfSheets;for (int i = 0; i < sheetCount; ++i){ISheet sheet = workbook.GetSheetAt(i);DataTable dt = ExcelToDataTable(sheet, evaluator, firstRowAsHeader);ds.Tables.Add(dt);}return ds;}}}///<summary>///返回DataTable///</summary>///<param name="sheet"></param>///<param name="evaluator"></param>///<param name="firstRowAsHeader"></param>///<returns></returns>private static DataTable ExcelToDataTable(ISheet sheet, IFormulaEvaluator evaluator, bool firstRowAsHeader) {if (firstRowAsHeader){return ExcelToDataTableFirstRowAsHeader(sheet, evaluator);}else{return ExcelToDataTable(sheet, evaluator);}}private static DataTable ExcelToDataTableFirstRowAsHeader(ISheet sheet, IFormulaEvaluator evaluator){try{using (DataTable dt = new DataTable()){IRow firstRow = sheet.GetRow(0);int cellCount = GetCellCount(sheet);for (int i = 0; i < cellCount; i++){if (firstRow.GetCell(i) != null){dt.Columns.Add(firstRow.GetCell(i).StringCellValue ?? string.Format("F{0}", i + 1), typeof(string)); }else{dt.Columns.Add(string.Format("F{0}", i + 1), typeof(string));}}for (int i = 1; i <= stRowNum; i++){IRow row = sheet.GetRow(i);DataRow dr = dt.NewRow();FillDataRowByRow(row, evaluator, ref dr);dt.Rows.Add(dr);}dt.TableName = sheet.SheetName;return dt;}}catch (Exception ex){MessageBox.Show(ex.Message);return null;}}private static DataTable ExcelToDataTable(ISheet sheet, IFormulaEvaluator evaluator){using (DataTable dt = new DataTable()){if (stRowNum != 0){int cellCount = GetCellCount(sheet);for (int i = 0; i < cellCount; i++){dt.Columns.Add(string.Format("F{0}", i), typeof(string));}for (int i = 0; i < sheet.FirstRowNum; ++i){DataRow dr = dt.NewRow();dt.Rows.Add(dr);}for (int i = sheet.FirstRowNum; i <= stRowNum; i++){IRow row = sheet.GetRow(i);DataRow dr = dt.NewRow();FillDataRowByRow(row, evaluator, ref dr);dt.Rows.Add(dr);}}dt.TableName = sheet.SheetName;return dt;}}///<summary>///填充数据///</summary>///<param name="row"></param>///<param name="evaluator"></param>///<param name="dr"></param>private static void FillDataRowByRow(IRow row, IFormulaEvaluator evaluator, ref DataRow dr){if (row != null){for (int j = 0; j < dr.Table.Columns.Count; j++){ICell cell = row.GetCell(j);if (cell != null){switch (cell.CellType){case CellType.Blank:{dr[j] = DBNull.Value;break;}case CellType.Boolean:{dr[j] = cell.BooleanCellValue;break;}case CellType.Numeric:{if (DateUtil.IsCellDateFormatted(cell)){dr[j] = cell.DateCellValue;}else{dr[j] = cell.NumericCellValue;}break;}case CellType.String:{dr[j] = cell.StringCellValue;break;}case CellType.Error:{dr[j] = cell.ErrorCellValue;break;}case CellType.Formula:{cell = evaluator.EvaluateInCell(cell) as HSSFCell;dr[j] = cell.ToString();break;}default:throw new NotSupportedException(string.Format("Unsupported format type:{0}", cell.CellType)); }}}}}///<summary>///获取单元格///</summary>///<param name="sheet"></param>///<returns></returns>private static int GetCellCount(ISheet sheet){int firstRowNum = sheet.FirstRowNum;int cellCount = 0;for (int i = sheet.FirstRowNum; i <= stRowNum; ++i){IRow row = sheet.GetRow(i);if (row != null && stCellNum > cellCount){cellCount = stCellNum;}}return cellCount;}#endregion}如果DataGridview的数据源有多余的列，我们可以⽤扩展⽅法，把dataGridView转成DataTable，ExcelHelper中有，但是我拿出来，需要⾃⼰注意下。

GridView导出Excel的超好例子

GridView导出Excel的超好例子其实网上有很多关于Excel的例子，但是不是很好，他们的代码没有很全，读的起来还很晦涩。

经过这几天的摸索，终于可以完成我想要导出报表Excel的效果了。

下面是我的效果图。

一.前台的页面图GridView的第一页的内容GridView第二页的内容：大家可能遇到这种情况，就是这个时候导出Excel的时候，打开Excel的时候发现GridView的第二页的内容却没有导出到Excel表里面。

其实解决这种情况，很简单，只要在导出之前，把Gridview的设置分页设置为Flase就行了。

下面是我导出Gridview里面的全部内容，打开Excel表如下：这就可以导出全部的GridView里面的内容了，包括了GridView 的第一页和第二页的内容。

二、实现的代码1.前台的代码：<div style=" margin:20px;"><asp:GridView ID="gvRecord" runat="server" AllowPaging="True" CellPadding="3"AutoGenerateColumns="False" BorderColor="White"BorderStyle="Ridge" BorderWidth="2px" BackColor="White" CellSpacing="1"GridLines="None" onprerender="gvRecord_PreRender"onpageindexchanged="gvRecord_PageIndexChanged"onpageindexchanging="gvRecord_PageIndexChanging" > <PagerSettings FirstPageText="Home Page" LastPageText="Last Page"NextPageText="Next" PreviousPageText="Previous" /><RowStyle ForeColor="Black" BackColor="#E5F1FF" HorizontalAlign="Center" /><Columns><asp:TemplateField HeaderText="Extension"><ItemTemplate><asp:Label ID="Label1" runat="server" Text='<%# Bind("Extn") %>'></asp:Label></ItemTemplate><ItemStyle Width="200px" /></asp:TemplateField><asp:TemplateField HeaderText="Wake up time"><ItemTemplate><asp:Label ID="Label2" runat="server" Text='<%# Bind("CallTime") %>'></asp:Label></ItemTemplate><ItemStyle Width="300px" /></asp:TemplateField><asp:TemplateField HeaderText="Status"><ItemTemplate><asp:Label ID="Label3" runat="server" Text='<%# getResult(Eval("Status").ToString()) %>'></asp:Label> </ItemTemplate><ItemStyle Width="250px" /></asp:TemplateField><asp:TemplateField HeaderText="Call number"><ItemTemplate><asp:Label ID="Label4" runat="server" Text='<%# Bind("callcount") %>'></asp:Label></ItemTemplate><ItemStyle Width="150px" /></asp:TemplateField></Columns><FooterStyle BackColor="#C6C3C6" ForeColor="Black" /> <PagerStyle BackColor="#C6C3C6" ForeColor="Black" HorizontalAlign="Right" /><SelectedRowStyle BackColor="#9471DE" Font-Bold="True" ForeColor="White" /><HeaderStyle BackColor="#C9E2FF" Font-Bold="True"ForeColor="#000000" /></asp:GridView><p><asp:Button ID="btnExcel" CssClass="button" runat="server" Text="Statements "onclick="btnExcel_Click" /></p></div>2.后台代码如下：using System;using System.Collections;using System.Configuration;using System.Data;using System.Linq;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.HtmlControls;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Xml.Linq;using Model;using DAL;using System.Collections.Generic;using System.IO;public partial class _3C_CallManager_CallRecord : System.Web.UI.Page{int selectFlag = 0;protected void Page_Load(object sender, EventArgs e) {if (!IsPostBack){bind();}}public string getResult(string str){if (str == "0")return "Haven't called";if (str == "1")return "check-out";if (str == "2")return "success";if (str == "3")return "fail";if (str == "4")return "dealing";if (str == "5")return "Artificial wake";elsereturn "unkown";}public void bind() {selectFlag = 0;gvRecord.DataSource = VgCallService.GetInfo(); gvRecord.DataBind();}protected void btnExcel_Click(object sender, EventArgs e) {DateTime dt = System.DateTime.Now;string str = dt.ToString("yyyyMMddhhmmss");str = str + ".xls";gvRecord.AllowPaging = false;if (selectFlag == 0)bind();if (selectFlag == 1)selectBind();GridViewToExcel(gvRecord, "application/ms-excel", str);// Export(gvRecord, "application/ms-excel", str);}/// <summary>/// 将网格数据导出到Excel/// </summary>/// <param name="ctrl">网格名称(如GridView1)</param> /// <param name="FileType">要导出的文件类型(Excel:application/ms-excel)</param>/// <param name="FileName">要保存的文件名</param>public static void GridViewToExcel(Control ctrl, string FileType, string FileName){HttpContext.Current.Response.Charset = "GB2312";HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;//注意编码HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8).ToString());HttpContext.Current.Response.ContentType = FileType;//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/mswordctrl.Page.EnableViewState = false;StringWriter tw = new StringWriter();HtmlTextWriter hw = new HtmlTextWriter(tw);ctrl.RenderControl(hw);HttpContext.Current.Response.Write(tw.ToString());HttpContext.Current.Response.End();}/// <summary>/// ReLoad this VerifyRenderingInServerForm is neccessary /// </summary>/// <param name="control"></param>public override void VerifyRenderingInServerForm(Control control){}protected void gvRecord_PreRender(object sender, EventArgs e){if(selectFlag==0)bind();}protected void gvRecord_PageIndexChanged(object sender, EventArgs e){}protected void gvRecord_PageIndexChanging(object sender, GridViewPageEventArgs e){this.gvRecord.PageIndex = e.NewPageIndex;bind();}}注意问题说明：1)前台页面<%@ Page Language="C#" EnableEventValidation="false"AutoEventWireup="true" CodeFile="CallRecord.aspx.cs"Inherits="_3C_CallManager_CallRecord" %>注意添加EnableEventValidation="false"，要不然会报错的。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

return true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "错误提示");
}
{ ቤተ መጻሕፍቲ ባይዱ
excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
}
//填充数据
for (int i = 0; i < dgv.RowCount - 1; i++)
excel.Application.Workbooks.Add(true);
excel.Visible = isShowExcel;
//生成字段名称
for (int i = 0; i < dgv.ColumnCount; i++)
}
else
{
excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();
//新增加一个工作簿，Workbook是直接保存，不会弹出保存对话框，加上Application会弹出保存对话框，值为false会报错
excel.Application.Workbooks.Add(true);
//生成Excel中列头名称
{
if (dgv.Rows.Count == 0)
return false;
//建立Excel对象
Excel.Application excel = new Excel.Application();
//把DataGridView当前页的数据保存在Excel中
for (int i = 0; i < dgv.Rows.Count - 1; i++)
{
for (int j = 0; j < dgv.Columns.Count; j++)
return false;
}
#endregion
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="dgv">DataGridView对象</param>
excel.AlertBeforeOverwriting = false;
//保存工作簿
excel.Application.Workbooks.Add(true).Save();
//保存excel文件
/// <param name="isShowExcel">是否显示Excel界面</param>
/// <returns></returns>
public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcel)
{
for (int j = 0; j < dgv.ColumnCount; j++)
{
if (dgv[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();
}
}
}
excel.Save("D:" + "\\HBMange.xls");
//确保Excel进程关闭
excel.Quit();
excel = null;
GC.Collect();
}
}
}
//设置禁止弹出保存和覆盖的询问提示框
excel.DisplayAlerts = false;
{
excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString();
}
else
如下：
#region SaveDataGireViewtoExcel
public bool SaveDataGireViewtoExcel(DataGridView dgv)
{
try
{
//实例化一个Excel.Application对象
for (int i = 0; i < dgv.Columns.Count; i++)
{
excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
}
{
if (dgv[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString();
return true;
}
Excel.Application excel =
new Excel.Application();
//让后台执行设置为不可见，为true的话会看到打开一个Excel，然后数据在往里写
excel.Visible = false;