C#将dataGridView中显示的数据导出到Excel(大数据量超有用版)

       开发中非常多情况下须要将dataGridView控件中显示的数据结果以Excel或者Word的形式导出来,本例就来实现这个功能。

因为从数据库中查找出某些数据列可能不是必需显示出来,在dataGridView中将相应的列隐藏了。这时导出时就会将隐藏的列导出来。显然不是我们所预期的。

做法是先将dataGridView中显示的部分存进DataTable。然后就DataTable导出到Excel,代码例如以下:

第一步:加入Excel引用


第二步:创建类

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;

public class ExportToExcel
{
    public Excel.Application m_xlApp = null;

    public void OutputAsExcelFile(DataGridView dataGridView)
    {
        if (dataGridView.Rows.Count <= 0)
        {
            MessageBox.Show("无数据!

","提示",MessageBoxButtons.OK,MessageBoxIcon.Warning); return; } string filePath=""; SaveFileDialog s = new SaveFileDialog(); s.Title = "保存Excel文件"; s.Filter = "Excel文件(*.xls)|*.xls"; s.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.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 (int i = 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 (int i = 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.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 = 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 (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() { 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 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 d = new ExportToExcel();
d.OutputAsExcelFile(dataGridView1);


posted @ 2017-08-19 18:32  zsychanpin  阅读(2409)  评论(0编辑  收藏  举报