在程序开发中很多情况下需要将dataGridView控件中的数据结果以Excel或者Word的形式导出来，今天我们就一起来实现这个功能。由于在实际工作需要可能从数据库中查找出的结果集中某些数据列不需要显示出来，就在dataGridView中将对应的列隐藏了，这时导出时就会将隐藏的列一起导出来，这不是我们想要的结果。所以我们需要先将dataGridView中显示的部分存进DataTable，然后就DataTable导出到Excel，代码如下：
第一步：添加引用
首先，我们需要引用office的COM组件，版本信息由于所安装的OFFICE版本不一样而有所差别，基本名称除了中间的数字之外其它都是一样的。
除此之外，还要引用Microsoft.Office.Interop.Excel.dll动态链接库，一般都存放在office的安装目录下。
第二步：创建类
using System;
using System.Collections;
using System.Text;
using System.Data;
using System.Threading;
using System.Windows.Forms;
using System.Collections.Generic;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
namespace SqlExecute
{
public class ExportToExcel
{
public Excel.Applicationm_xlApp = null;
public voidOutputAsExcelFile(DataGridView dataGridView)
{
if (dataGridView.Rows.Count <= 0)
{
MessageBox.Show("无数据！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); return;
}
string filePath = "";
SaveFileDialog s = newSaveFileDialog();
s.Title = "保存Excel文件";
s.Filter = "Excel文件(*.xlsx)|*.xlsx|Excel97-2003(*.xls)|*.xls";
s.FilterIndex = 1;
if (s.ShowDialog() == DialogResult.OK)
filePath = s.FileName;
else
return;
//第一步：将dataGridView转化为dataTable,这样可以过滤掉dataGridView中的隐藏列
DataTable tmpDataTable = newDataTable("tmpDataTable");
DataTable modelTable = newDataTable("ModelTable");
for (int column = 0;column < dataGridView.Columns.Count; column++)
{
if (dataGridView.Columns[column].Visible== true)
{
DataColumn tempColumn = new DataColumn(dataGridView.Columns[column].HeaderText,typeof(string));
tmpDataTable.Columns.Add(tempColumn);
DataColumn modelColumn = new DataColumn(dataGridView.Columns[column].Name,typeof(string));
modelTable.Columns.Add(modelColumn);
}
}
for (int row = 0; row< dataGridView.Rows.Count; row++)
{
if (dataGridView.Rows[row].Visible == false)
continue;
DataRow tempRow =tmpDataTable.NewRow();
for (inti = 0; i < tmpDataTable.Columns.Count; i++)
tempRow[i] =dataGridView.Rows[row].Cells[modelTable.Columns[i].ColumnName].Value;
tmpDataTable.Rows.Add(tempRow);
}
if (tmpDataTable == null)
{
return;
}
//第二步：导出dataTable到Excel
long rowNum = tmpDataTable.Rows.Count;//行数
int columnNum = tmpDataTable.Columns.Count;//列数
Excel.Application m_xlApp = new Excel.Application();
m_xlApp.DisplayAlerts = false;//不显示更改提示
m_xlApp.Visible = false;
Excel.Workbooks workbooks =m_xlApp.Workbooks;
Excel.Workbook workbook =workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
try
{
string[,] datas = new string[rowNum +1, columnNum];
for (inti = 0; i < columnNum; i++) //写入字段
datas[0, i] = tmpDataTable.Columns[i].Caption;
//Excel.Range range =worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]);
Excel.Rangerange = 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 (inti = 0; i < columnNum; i++)
{
object obj =tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()];
datas[r + 1, i] = obj == null ? "": "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
}
System.Windows.Forms.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();//列宽自适应。
//worksheet.Name = "dd";
//m_xlApp.WindowState =Excel.XlWindowState.xlMaximized;
m_xlApp.Visible = false;
// = worksheet.get_Range(worksheet.Cells[1,1], worksheet.Cells[rowNum + 1, columnNum]);
range = m_xlApp.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1,columnNum]];
//range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Size = 9;
range.RowHeight = 14.25;
range.Borders.LineStyle = 1;
range.HorizontalAlignment = 1;
workbook.Saved = true;
workbook.SaveCopyAs(filePath);
}
catch (Exceptionex)
{
MessageBox.Show("导出异常：" + ex.Message, "导出异常", MessageBoxButtons.OK,MessageBoxIcon.Warning);
}
finally
{
EndReport();
}
m_xlApp.Workbooks.Close();
m_xlApp.Workbooks.Application.Quit();
m_xlApp.Application.Quit();
m_xlApp.Quit();
MessageBox.Show("导出成功！", "提示", MessageBoxButtons.OK,MessageBoxIcon.Information);
}
private voidEndReport()
{
object missing = System.Reflection.Missing.Value;
try
{
//m_xlApp.Workbooks.Close();
//m_xlApp.Workbooks.Application.Quit();
//m_xlApp.Application.Quit();
//m_xlApp.Quit();
}
catch { }
finally
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Application);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp);
m_xlApp = null;
}
catch { }
try
{
//清理垃圾进程
this.killProcessThread();
}
catch { }
GC.Collect();
}
}
private voidkillProcessThread()
{
ArrayList myProcess = newArrayList();
for (int i = 0; i< myProcess.Count; i++)
{
try
{
System.Diagnostics.Process.GetProcessById(int.Parse((string)myProcess[i])).Kill();
}
catch { }
}
}
}
}
第三步：调用
调用时只需要将上面所创建的类实例化，然后调取其中的导出方法并将所要导出的DataGridView以参数的形式传递即可。
private voidbtnExportToExcel_Click(object sender, EventArgs e)
{
ExportToExcel exportExcle = new ExportToExcel();
exportExcle.OutputAsExcelFile(dgvData);
}
第四处步：使用
在执行导出操作时会弹出保存文件的对话框，输入文件名保存即可。