先引入Execl插件。
创建一个按钮事件添加DataGridView.
以下是代码参考:
using System;
using System.Collections;
using System.Data;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace YC300
{
public class ExportToExcel
{
public Excel.Application m_xlApp = null;
public void OutputAsExcelFile(DataGridView dataGridView_result)
{
if (dataGridView_result.Rows.Count <= 0)
{
MessageBox.Show("无数据!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); return;
}
string filePath = "";
SaveFileDialog s = new SaveFileDialog
{
Title = "保存Excel文件",
Filter = "Excel文件(*.xlsx)|*.xlsx",
FilterIndex = 1
};
if (s.ShowDialog() == DialogResult.OK)
filePath = s.FileName;
else
return;
//第一步:将dataGridView转化为dataTable,这样能够过滤掉dataGridView中的隐藏列
DataTable tmpDataTable = new DataTable("tmpDataTable");
DataTable modelTable = new DataTable("ModelTable");
for (int column = 0; column < dataGridView_result.Columns.Count; column++)
{
if (dataGridView_result.Columns[column].Visible == true)
{
DataColumn tempColumn = new DataColumn(dataGridView_result.Columns[column].HeaderText, typeof(string));
tmpDataTable.Columns.Add(tempColumn);
DataColumn modelColumn = new DataColumn(dataGridView_result.Columns[column].Name, typeof(string));
modelTable.Columns.Add(modelColumn);
}
}
for (int row = 0; row < dataGridView_result.Rows.Count; row++)
{
if (dataGridView_result.Rows[row].Visible == false)
continue;
DataRow tempRow = tmpDataTable.NewRow();
for (int i = 0; i < tmpDataTable.Columns.Count; i++)
tempRow[i] = dataGridView_result.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 (int i = 0; i < columnNum; i++) //写入字段
datas[0, i] = tmpDataTable.Columns[i].Caption;
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 = 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 = m_xlApp.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]];
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
m_xlApp.Visible = false;
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 (Exception ex)
{
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 void EndReport()
{
try
{
}
catch { }
finally
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Workbooks); //如果电脑没有装正版的Execl会提示异常,把.xls格式改成wps的.xlsx格式导出成功
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 void KillProcessThread()
{
ArrayList myProcess = new ArrayList();
for (int i = 0; i < myProcess.Count; i++)
{
try
{
System.Diagnostics.Process.GetProcessById(int.Parse((string)myProcess[i])).Kill();
}
catch { }
}
}
}
}
最后按钮事件调取ExportToExcel的方法:
/// <summary>
/// 导出Excel表格至本地路径
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnOutExcel_Click(object sender, EventArgs e)
{
ExportToExcel excel = new ExportToExcel();
excel.OutputAsExcelFile(dataGridView_result);
}