导出dataGridView为EXCEL并操作EXCEL单元格合并

 //导出Excel
        public static void TransitDataToExcel2(DataTable DataTableForTransit, string[] CaptionValue, bool IsShowByDialog)
        {         
            if (DataTableForTransit == null || DataTableForTransit.Rows.Count == 0) return;                
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的电脑未安装Excel");
                return;
            }
            System.Windows.Forms.SaveFileDialog saveDia = new SaveFileDialog();
            saveDia.Filter = "Excel|*.xls";
            saveDia.Title = "导出为Excel文件";           
            if (saveDia.ShowDialog() == System.Windows.Forms.DialogResult.OK
            && !string.Empty.Equals(saveDia.FileName))
           {
               Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
               Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
               Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
               Microsoft.Office.Interop.Excel.Range range = null;
               //获取dataTable的行数
               long  totalCount = DataTableForTransit.Rows.Count;
               long rowRead = 0;
               float percent = 0;
               string fileName = saveDia.FileName;
               MessageBox.Show("正在导出，请等候!");
               //写入列标题
               for (int i = 0; i < CaptionValue.Length; i++)
               {
                   worksheet.Cells[1, i + 1] = CaptionValue[i];
                   range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
                   //range.Interior.ColorIndex = 15;//背景颜色
                   range.Font.Bold = true;//粗体
                   range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中
                   //加边框
                   range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);

                  
                   range.EntireColumn.AutoFit();//自动调整列宽
                   range.EntireRow.AutoFit();//自动调整行高
               }
 
               //写入内容
 
               for (int r = 0; r < DataTableForTransit.DefaultView.Count; r++)
               {                    
                   for (int i = 0; i < DataTableForTransit.Columns.Count; i++)
                   {
                       worksheet.Cells[r + 2, i + 1] = DataTableForTransit.DefaultView[r][i];
                       range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, i + 1];
                       range.Font.Size = 9;//字体大小
                       //加边框
                       range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
                       range.EntireColumn.AutoFit();//自动调整列宽
                       range.EntireRow.AutoFit();//自动调整行高
                       range.WrapText =true;
                   }
 
                   rowRead++;
                
                   percent = ((float)(100 * rowRead)) / totalCount;
                   System.Windows.Forms.Application.DoEvents();
               }
              
               int excelCount = worksheet.UsedRange.CurrentRegion.Rows.Count;//获得记录的行数
//合并当前表格中某一列内容相同的单元格
               MergeCell(ref worksheet, 1, excelCount, col1,Letter1);
               MergeCell(ref worksheet, 1, excelCount, col2,Letter2);
              
               range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
               if (DataTableForTransit.Columns.Count > 1)
               {
                   range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
               }
 
               try
               {
                   workbook.Saved = true;
                   workbook.SaveCopyAs(fileName);
               }
               catch (Exception ex)
               {
                   MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
                   return;
               }
 
               workbooks.Close();
               if (xlApp != null)
               {
                   xlApp.Workbooks.Close();
                   xlApp.Quit();
                   int generation = System.GC.GetGeneration(xlApp);
                   System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                   xlApp = null;
                   System.GC.Collect(generation);
 
               }
 
               GC.Collect();//强行销毁
 #region 强行杀死最近打开的Excel进程
 
               System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
               System.DateTime startTime = new DateTime();
               int m, killId = 0;
               for (m = 0; m < excelProc.Length; m++)
               {
                   if (startTime < excelProc[m].StartTime)
                   {
                       startTime = excelProc[m].StartTime;
                       killId = m;
                   }
               }
               if (excelProc[killId].HasExited == false)
               {
                   excelProc[killId].Kill();
               }
 
               #endregion

               MessageBox.Show("导出成功!");
           }
       }

合并单元格方法

其中mySheet为表格、 int startLine开始合并的行, int recCount结束合并的行, string col要合并的列,int letter合并列的列数

  private static void MergeCell(ref Microsoft.Office.Interop.Excel.Worksheet mySheet, int startLine, int recCount, string col,int letter)
        {
            //获得起始行合并列单元格的填充内容
            string qy1 = mySheet.get_Range(col + startLine.ToString(), col + startLine.ToString()).Text.ToString();
            Microsoft.Office.Interop.Excel.Range rg1;
            string strtemp = "";
            bool endCycle = false;
            //从起始行到终止行做循环
            for (int i = startLine; i <= recCount + startLine - 1 && !endCycle; )
            {
                for (int j = i + 1; j <= recCount + startLine - 1; j++)
                {
                    rg1 = mySheet.get_Range(col + j.ToString(), col + j.ToString());//获得下一行的填充内容
                    strtemp = rg1.Text.ToString().Trim();
                    //最后一行时，标记循环结束
                    if (j == recCount + startLine - 1)
                        endCycle = true;
                    if (strtemp.Trim() == qy1.Trim())//内容等于初始内容
                    {
                        rg1 = mySheet.get_Range(col + i.ToString(), col + j.ToString());//选取上条合并位置和当前行的合并区域
                        rg1.ClearContents();//清空要合并的区域
                        rg1.MergeCells = true;
                        rg1.WrapText = true;//自动换行
                        //rg1.EntireColumn.AutoFit();//自动调整列宽
                        rg1.EntireRow.AutoFit();//自动调整行高
                        mySheet.Cells[i, letter+1] = qy1;
                       // mySheet.get_Range(i, letter + 1).WrapText = true;
                      
                           
                    }
                    else//内容不等于初始内容
                    {
                        i = j;//i获取新值
                        qy1 = mySheet.get_Range(col + j.ToString(), col + j.ToString()).Text.ToString();
                        break;
                    }
                }
            }
        }

其中col 为EXCEL的大表头A、B、C、E