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;

        }