C#自用DataGridView 分页转EXCEL

添加引用：COM  Microsoft Excel 11.0 Object Library

命名空间：

using System;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using ExcelApplication = Microsoft.Office.Interop.Excel.ApplicationClass;

 

        /// <summary>
        /// 用于将DataGridView内容分页转EXCEL表格
        /// </summary>
        /// <param name="DGV"></param>
        /// <param name="ToExcelRowMax">转为EXCEL每页的最大转换行数：函数中未对EXCEL表格页可存储最大行65535进行传入参数限制，如有需要可自行添加判断语句</param>
        private void toExcel(DataGridView DGV,int ToExcelRowMax)
        {
            //对DataGridView中隐藏列的计数
            int visiblefalse = 0;
            try
            {
                //创建一个Excel文件
                ExcelApplication myExcel = new ExcelApplication();
                myExcel.Application.Workbooks.Add(true);
                int n = 0;
                if (DGV.Rows.Count > ToExcelRowMax)
                {
                    if (DGV.Rows.Count % ToExcelRowMax == 0)
                        n = DGV.Rows.Count / ToExcelRowMax - 1;
                    else
                        n = DGV.Rows.Count / ToExcelRowMax;
                }
                for (int page = 0; page <= n; page++)
                {
                    //让Excel文件可见
                    myExcel.Visible = true;
                    Worksheet sheet = (Worksheet)myExcel.ActiveSheet;
                    Range range = null;
                    Range range2 = null;
                    Int32 row = 1;
                    //DataGridView.Name若已赋值则作为EXCEL表的首行标题
                    if (DGV.Name.Trim().ToString() != "" && DGV.Name.ToString() != null)
                    {
                        //第一行为报表名称
                        myExcel.Cells[row, 1] = DGV.Name.Trim().ToString();
                        row = row + 1;
                    }
                    //第二行为报表列名
                    int showColumn = 1;
                    for (int i = 0; i < DGV.ColumnCount; i++)
                    {
                        if (DGV.Columns[i].Visible == true)
                        {
                            myExcel.Cells[row, showColumn++] = DGV.Columns[i].HeaderText;
                        }
                        else
                            visiblefalse++;
                    }
                    if (DGV.Name.Trim().ToString() != "" && DGV.Name.ToString() != null)
                    {

                        if (DGV.Columns.Count > 0 && DGV.Columns.Count - visiblefalse > 0)
                        {
                            //标题栏的合并单元格，居中
                            range = sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[1, DGV.Columns.Count - visiblefalse]);
                            range.Merge(0);
                            range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                        }
                    }
                    try
                    {
                        //逐行写入数据：：过滤掉不显示的列
                        int i = 0;
                        for (; i < ToExcelRowMax && i < DGV.Rows.Count - ToExcelRowMax * page; i++)
                        {
                            showColumn = 1;
                            for (int j = 0; j < DGV.ColumnCount; j++)
                            {
                                if (DGV.Columns[j].Visible == true)
                                {
                                    //对于设置单元格为纯文本格式可以使用两种方式，较为简单的是在文本的起始以单引号开头，
                                    //但是对于EXCEL表格使用者在点击该单元格时将显示形式如'date的数据。
                                    //另一种可以对EXCEL表格范围数据段上NumberFormatLocal="@",则该范围内的格式将为纯文本格式（可以通过EXCEL宏录制,得到该方式）
                                    //该方式下的纯文本格式定义将不影响EXCEL表格上的数据显示
                                    if (DGV.Rows[i + page * ToExcelRowMax].Cells[j].Value != null)
                                    {
                                        if (DGV.Rows[i + page * ToExcelRowMax].Cells[j].ValueType.Name.ToUpper() == "STRING")
                                        {
                                            myExcel.get_Range(myExcel.Cells[row + 1 + i, showColumn], myExcel.Cells[row + 1 + i, showColumn]).NumberFormatLocal = "@";
                                            myExcel.Cells[row + 1 + i, showColumn++] = DGV.Rows[i + page * ToExcelRowMax].Cells[j].FormattedValue.ToString();
                                        }
                                        else
                                            myExcel.Cells[row + 1 + i, showColumn++] = DGV.Rows[i + page * ToExcelRowMax].Cells[j].FormattedValue.ToString();

                                    }
                                }
                            }
                        }
                        //设置最适合列宽
                        myExcel.Columns.AutoFit();
                        range2 = myExcel.get_Range(sheet.Cells[1, 1], sheet.Cells[1, 1]);

                        if (DGV.Columns.Count > 0 && DGV.Columns.Count - visiblefalse > 0)
                        {
                            if (DGV.Name.Trim().ToString() != "" && DGV.Name.ToString() != null)
                            {
                                range2 = myExcel.get_Range(sheet.Cells[1, 1], sheet.Cells[i + 2, DGV.Columns.Count - visiblefalse]);
                            }
                            else
                                range2 = myExcel.get_Range(sheet.Cells[1, 1], sheet.Cells[i + 1, DGV.Columns.Count - visiblefalse]);
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("生成Excel错误：" + ex.Message, "生成Excel", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }

                    range2.Borders.Weight = 2;
                    range2.Borders.get_Item(XlBordersIndex.xlEdgeTop).Weight = XlBorderWeight.xlThick;
                    range2.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlContinuous;
                    range2.Borders.get_Item(XlBordersIndex.xlEdgeLeft).Weight = XlBorderWeight.xlThick;
                    range2.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous;
                    range2.Borders.get_Item(XlBordersIndex.xlEdgeRight).Weight = XlBorderWeight.xlThick;
                    range2.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous;
                    range2.Borders.get_Item(XlBordersIndex.xlEdgeBottom).Weight = XlBorderWeight.xlThick;
                    range2.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous;
                    //对导出的EXCEL表格设置边框
                    range2.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).Weight = XlBorderWeight.xlThin;
                    if (DGV.Rows.Count > 0) //Rows+Headre<2则无法设置水平中线模式
                    {
                        range2.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;
                    }
                    range2.Borders.get_Item(XlBordersIndex.xlInsideVertical).Weight = XlBorderWeight.xlThin;
                    if (DGV.Columns.Count - visiblefalse > 1) //Columns<2则无法设置水平中线模式
                    {
                        range2.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous;
                    }
                    //分页
                    if (page + 1 <= n)
                        myExcel.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                }
            }
            catch
            {
                MessageBox.Show("生成DataGridView失败，无法转成Excel");
                return;
            }
        }