C#开发Excel报表二

      在这里介绍一下excel开发报表用到的一些基本的方法，例如单元格的合并，行与列的插入等操作。以及在excel中遍历出相邻单元格的并合并的方法。

      单元格合并，单元格合并需要注意的是在合并之前需要清空带合并单元格的数据，否则在合并时会弹出提示框，在合并之后在给单元格赋值。

       //合并单元格
        public void RangeMerge(string pos1, string pos2,string name,object value)
        {
            Excel.Range cell = Worksheet.get_Range(pos1, pos2);
            try
            {
                cell.Value2 = null; //清空单元格中的数据
                cell.Merge(Missing.Value);//拆分单元格所对应的方法为UnMerge(Missing.Value)
                //cell.Name=name;
                cell.Value2 = value;
            }
            catch (Exception ex)
            {
                throw new Exception("合并单元格出错/n待合并的单元格:"+pos1+","+pos2,ex);
            }
        }

 

 

      向Excel中插入一行

       //在excel中插入空白行
        public void InsertRow(int index)
        {

           //index代表的是excel中的行号，即是在哪一行之前插入空白行
            Excel.Range cell =(Excel.Range)Worksheet.Rows[index,Missing.Value];
            cell.Insert(Excel.XlDirection.xlDown,Missing.Value);
        }

 

      向Excel中插入一列
        //在excel中插入空白列
        public void InsertColumns(int index)
        {

            //index代表的是excel中的列号，即是在那一列之前插入空白列
            Excel.Range cell = (Excel.Range)Worksheet.Columns[index, Missing.Value];
            cell.Insert(Excel.XlDirection.xlToLeft, Missing.Value);
        }

 

       接下来遍历同一行中的数据相同的相邻列，并执行合并操作

      /// <summary>
        /// //合并同一行中数据相同的连续列
        /// </summary>
        /// <param name="s1">比较的数据</param>
        /// <param name="RowIndex">当前行的索引</param>
        /// <param name="CurrentIndex">带比较列的索引</param>
        /// <param name="EndIndex">进行比较的范围，即最后一个待比较列的索引</param>
        /// <param name="ColumnIndex">待合并范围的第一列的索引</param>
        private void MergerColumn(string s1, int RowIndex, int CurrentIndex, int EndIndex, int ColumnIndex)
        {
            //判断当前的比较是否还在进行比较的范围之类
            if (CurrentIndex <= EndIndex + 1)
            {
                //将当前列的索引转换成excel中的索引号，例如1转换成A
                string index;

                //在此处需要判断带比较的列的索引号是否超过了A因为在excel中索引号到了Z之后，开始以AA，AB进行命名

                if (CurrentIndex > ConvertColumnIDToInt('Z'))
                {
                    index = "A"+ConvertToColumnID('A', CurrentIndex - ConvertColumnIDToInt('Z') - 1);
                }
                else
                {
                    index = ConvertColumnIDToChar(CurrentIndex);
                }
                //获取当前待比较单元格是数据
                string s2 = ExcelFileHandle.GetRange(index + RowIndex.ToString(), true).Value2 == null ? "startCloumn" : ExcelFileHandle.GetRange(index + RowIndex.ToString().ToString(), true).Value2.ToString();
                //当待比较数据与比较数据相同时，则继续比较该行的下一列的数据
                if (s1 == s2)
                {
                    ExcelFileHandle.SetRangeValue2(index + RowIndex.ToString(), "");
                    CurrentIndex = CurrentIndex + 1;
                    MergerColumn(s1, RowIndex, CurrentIndex, EndIndex, ColumnIndex);
                }
                else //当待比较数据与比较数据不同时，要考虑将之前相同列的数据进行合并
                {
                    //获取相同列中的第一列的索引号
                    string startIndex;
                    if (ColumnIndex > ConvertColumnIDToInt('Z'))
                    {
                        startIndex = "A"+ConvertToColumnID('A', ColumnIndex - ConvertColumnIDToInt('Z') - 1);
                    }
                    else
                    {
                        startIndex = ConvertColumnIDToChar(ColumnIndex);
                    }
                    //获取相同列中最后一列的索引号
                   
                    string endIndex ;
                    if (CurrentIndex - 1 > ConvertColumnIDToInt('Z'))
                    {
                        endIndex = "A"+ConvertToColumnID('A', CurrentIndex - ConvertColumnIDToInt('Z') - 2);
                    }
                    else
                    {
                        endIndex = ConvertColumnIDToChar(CurrentIndex - 1);
                    }
                    //如果存在两个或者两个以上连续的相同列，则合并
                    if (ColumnIndex < CurrentIndex - 1)
                    {
                        ExcelFileHandle.RangeMerge(startIndex + RowIndex.ToString(), endIndex + RowIndex.ToString(), "", s1);
                    }
                    //对该行的下一列数据的进行比较合并
                    //设置下一列的索引
                    string nextIndex;
                    if (CurrentIndex > ConvertColumnIDToInt('Z'))
                    {
                        nextIndex = "A"+ConvertToColumnID('A', CurrentIndex - ConvertColumnIDToInt('Z') - 1);
                    }
                    else
                    {
                        nextIndex = ConvertColumnIDToChar(CurrentIndex);
                    }
                    s1 = ExcelFileHandle.GetRange(nextIndex + RowIndex.ToString(), true).Value2 == null ? "lastCloumn" : ExcelFileHandle.GetRange(nextIndex + RowIndex.ToString().ToString(), true).Value2.ToString();
                    MergerColumn(s1, RowIndex, CurrentIndex + 1, EndIndex, CurrentIndex);
                    ColumnIndex++;
                }
            }
        }