C#中操作Excel单元格合并

这里主要是对于一个已知的EXCEL进行合并里面的单元格的方法

string savePath="C:\\Users\\Administrator\\Desktop\\xxx.xls";//excel的路径

Microsoft.Office.Interop.Excel.Application xlApp = null;//Execl的操作类
Microsoft.Office.Interop.Excel.Workbook myWorkbook = null;//打开已有的工作簿
try
{
   xlApp = new Microsoft.Office.Interop.Excel.Application();
   myWorkbook = xlApp.Workbooks.Open(savePath);
   Microsoft.Office.Interop.Excel.Worksheet mySheet = myWorkbook.ActiveSheet;//获取工作表

   int excelCount = mySheet.UsedRange.CurrentRegion.Rows.Count;//获得记录的行数
   MergeCell(ref mySheet, 1, excelCount, "A");//合并单元格
   MergeCell(ref mySheet, 1, excelCount, "B");//合并单元格

   //设置禁止弹出保存和覆盖的询问提示框
   xlApp.DisplayAlerts = false;
   xlApp.AlertBeforeOverwriting = false;

   //保存所做的修改
   xlApp.SaveWorkspace();
}
catch (Exception ex)
{

//异常时,处理代码
}
finally
{
   if (xlApp!=null)
        xlApp.Quit();//退出Excel
   xlApp = null;
}


/// <summary>
/// 合并指定EXCEL的单元格
/// </summary>
/// <param name="mySheet">指定的EXCEL工作表</param>
/// <param name="startLine">起始行</param>
/// <param name="recCount">总行数</param>
/// <param name="col">要合并的列</param>
private void MergeCell(ref Microsoft.Office.Interop.Excel.Worksheet mySheet, int startLine, int recCount, string col)
{
    //获得起始行合并列单元格的填充内容
    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;
                 if (col == "A")
                     mySheet.Cells[i, 1] = qy1;
                 else if (col == "B")
                     mySheet.Cells[i, 2] = qy1;
             }
             else//内容不等于初始内容
             {
                 i = j;//i获取新值
                 qy1 = mySheet.get_Range(col + j.ToString(), col + j.ToString()).Text.ToString();
                 break;
             }
         }
     }
}

阅读更多
换一批

没有更多推荐了,返回首页