DataGridView输出或保存为Excel文件（支持超过65536行多Sheet输出）

/// <summary>

        /// DataGridView控件数据导出到Excel,可设定每一个Sheet的行数
        /// 建立多个工作表来装载更多的数据
        /// </summary>

        /// <param name="ExportGrid">DataGridView控件</param>

        /// <param name="fullFileName">保存的文件路径</param>

        /// <param name="SheetRowsCount">每一个Sheet的行数</param>

        /// <param name="IsOpenFile">是否打开文件</param>

        /// <returns>True/False</returns>

        public bool OutputFileToExcel(DataGridView ExportGrid, string fullFileName, int SheetRowsCount, bool IsOpenFile)

        {

            int id = 0;

            bool ExportSuccess = false;

            //如果网格尚未数据绑定
            if (ExportGrid == null)

            {

                return false;

            }

            ////Excel2003 工作表大小 65,536 行乘以 256 列 

            //if (ExportGrid.Rows.Count > 65536 || ExportGrid.ColumnCount > 256)

            //{

            //    return false;

            //}

            // 列索引，行索引
            int colIndex = 0;

            int rowIndex = 0;

            int objcetRowIndex = 0;

            //总可见列数，总可见行数
            int colCount = ExportGrid.Columns.GetColumnCount(DataGridViewElementStates.Visible);

            int rowCount = ExportGrid.Rows.GetRowCount(DataGridViewElementStates.Visible);

            if (rowCount == 0 || colCount == 0)  //如果DataGridView中没有行，返回
            {

                return false;

            }

            // 创建Excel对象                    

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

            if (xlApp == null)

            {

                return false;

            }

            // 创建Excel工作薄
            Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);

            Microsoft.Office.Interop.Excel.Range range = null;

            IntPtr t = new IntPtr(xlApp.Hwnd);

 

            object oMissing = System.Reflection.Missing.Value;

            int JLevel = 0;

            JLevel = int.Parse(Math.Ceiling((ExportGrid.RowCount + 0.00) / SheetRowsCount).ToString());

            xlBook.Worksheets.Add(oMissing, oMissing, JLevel - 1, oMissing);

            for (int i = 1; i < xlBook.Worksheets.Count + 1; i++)

            {

                ((Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[i]).Name = "数据表" + i.ToString();

            }

            for (int j = 1; j < JLevel + 1; j++)

            {

                colIndex = 0;

                objcetRowIndex = 0;

                Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets.get_Item("数据表" + j.ToString());

                // 创建缓存数据
                object[,] objData = new object[SheetRowsCount + 1, colCount];

                // 获取列标题，隐藏的列不处理
                for (int i = 0; i < ExportGrid.ColumnCount; i++)

                {

                    if (ExportGrid.Columns[i].Visible)

                    {

                        objData[objcetRowIndex, colIndex++] = ExportGrid.Columns[i].HeaderText;

                        if (ExportGrid.Columns[i].ValueType.ToString() == "System.String")

                        {

                            //设置成文本型,有效避免将前置的0自动删除了
                            range = xlSheet.get_Range(xlSheet.Cells[1, colIndex], xlSheet.Cells[SheetRowsCount + 1, colIndex]);

                            range.NumberFormat = "@";

                        }

                    }

                }

 

                for (int i = (j - 1) * SheetRowsCount; i < SheetRowsCount * j; i++)

                {

                    rowIndex++;

                    objcetRowIndex++;

                    colIndex = 0;

                    for (int k = 0; k < ExportGrid.ColumnCount; k++)

                    {

                        if (ExportGrid.Columns[k].Visible)

                        {

                            objData[objcetRowIndex, colIndex++] = ExportGrid[k, rowIndex - 1].Value;

                        }

                    }

                    Application.DoEvents();

 

 

                    if (i >= ExportGrid.RowCount - 1)

                    {

                        break;

                    }

 

                }

                // 写入Excel

                range = xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[SheetRowsCount + 1, colCount]);

                range.Value2 = objData;

                //设置列头格式
                range = xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, colCount]);

                range.Font.Bold = true;

                range.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;

 

                //设置报表表格为最适应宽度
                xlSheet.Cells.EntireColumn.AutoFit();

                xlSheet.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;

                xlSheet.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;

                xlSheet.UsedRange.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

            }

 

            // 保存
            try

            {

                xlBook.Saved = true;

                xlBook.SaveCopyAs(fullFileName);

                ExportSuccess = true;

            }

            catch

            {

                ExportSuccess = false;

            }

            finally

            {

                //释放资源，关闭进程
                xlApp.Quit();

                GetWindowThreadProcessId(t, out id);

                System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(id);

                p.Kill();

            }

            if (IsOpenFile == true)

            {

                HS.Audit.Utilite.FileOperate FO = new HS.Audit.Utilite.FileOperate();

                FO.OpenFile(fullFileName);

            }

            return ExportSuccess;

        }