C#将dataGridView中显示的数据导出到Excel（大数据量超实用版）

来源：互联网发布：肯塔基大学知乎编辑：程序博客网时间：2024/06/05 09:41

开发中很多情况下需要将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);

3 0