C#操作Excel文件暨C#实现在Excel中将连续多列相同数据项合并

C#操作Excel文件(读取Excel，写入Excel)

看到论坛里面不断有人提问关于读取excel和导入excel的相关问题。闲暇时间将我所知道的对excel的操作加以总结，现在共享大家，希望给大家能够给大家带了一定的帮助。
另外我们还要注意一些简单的问题1.excel文件只能存储65535行数据，如果你的数据大于65535行，那么就需要将excel分割存放了。2.关于乱码，这主要是字符设置问题。

1.加载Excel（读取excel内容）返回值是一个DataSet

        //加载Excel
        public static DataSet LoadDataFromExcel(string filePath)
        {
            try
            {
                string strConn;
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
                OleDbConnection OleConn = new OleDbConnection(strConn);
                OleConn.Open();
                String sql = "SELECT * FROM  [Sheet1$]";//可是更改Sheet名称，比如sheet2，等等

                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                DataSet OleDsExcle = new DataSet();
                OleDaExcel.Fill(OleDsExcle, "Sheet1");
                OleConn.Close();
                return OleDsExcle;
            }
            catch (Exception err)
            {
                MessageBox.Show("数据绑定Excel失败!失败原因：" + err.Message, "提示信息",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                return null;
            }
        }
2.写入Excel内容，参数：excelTable是要导入excel的一个table表 

        public static bool SaveDataTableToExcel(System.Data.DataTable excelTable, string filePath)
        {
            Microsoft.Office.Interop.Excel.Application app =
                new Microsoft.Office.Interop.Excel.ApplicationClass();
            try
            {
                app.Visible = false;
                Workbook wBook = app.Workbooks.Add(true);
                Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
                if (excelTable.Rows.Count > 0)
                {
                    int row = 0;
                    row = excelTable.Rows.Count;
                    int col = excelTable.Columns.Count;
                    for (int i = 0; i < row; i++)
                    {
                        for (int j = 0; j < col; j++)
                        {
                            string str = excelTable.Rows[i][j].ToString();
                            wSheet.Cells[i + 2, j + 1] = str;
                        }
                    }
                }

                int size = excelTable.Columns.Count;
                for (int i = 0; i < size; i++)
                {
                    wSheet.Cells[1, 1 + i] = excelTable.Columns[i].ColumnName;
                }
                //设置禁止弹出保存和覆盖的询问提示框
                app.DisplayAlerts = false;
                app.AlertBeforeOverwriting = false;
                //保存工作簿
                wBook.Save();
                //保存excel文件
                app.Save(filePath);
                app.SaveWorkspace(filePath);
                app.Quit();
                app = null;
                return true;
            }
            catch (Exception err)
            {
                MessageBox.Show("导出Excel出错！错误原因：" + err.Message, "提示信息",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }
            finally
            {
            }
        }

C#实现在Excel中将连续多列相同数据项合并

效果图如下：

代码如下：

/** <summary>
        /// 合并工作表中指定行数和列数数据相同的单元格
        /// </summary>
        /// <param name="sheetIndex">工作表索引</param>
        /// <param name="beginRowIndex">开始行索引</param>
        /// <param name="beginColumnIndex">开始列索引</param>
        /// <param name="rowCount">要合并的行数</param>
        /// <param name="columnCount">要合并的列数</param>
        public void MergeWorkSheet(int sheetIndex,int beginRowIndex,int beginColumnIndex,int rowCount,int columnCount)
        {

            //检查参数
            if ( columnCount < 1 || rowCount < 1)
                return ;

            for(int col=0;col<columnCount;col++)
            {
                int mark = 0;            //标记比较数据中第一条记录位置
                int mergeCount = 1;        //相同记录数，即要合并的行数
                string text = "";
               
                for(int row=0;row<rowCount;row++)
                {
                    string prvName = "";
                    string nextName = "";

                    //最后一行不用比较
                    if( row + 1 < rowCount)       
                    {
                        for(int n=0;n<=col;n++)
                        {
                            range = (Excel.Range)workSheet.Cells[row + beginRowIndex,n + beginColumnIndex];
                            range = (Excel.Range)range.MergeArea.get_Item(1,1);
                            text = range.Text.ToString();
                            prvName = prvName + text;

                            range = (Excel.Range)workSheet.Cells[row + 1 + beginRowIndex,n + beginColumnIndex];
                            range = (Excel.Range)range.MergeArea.get_Item(1,1);
                            nextName = nextName + range.Text.ToString();

                        }
                           
                        if(prvName == nextName)
                        {
                            mergeCount++;

                            if(row == rowCount - 2)
                            {
                                this.MergeCells(sheetIndex,beginRowIndex + mark,beginColumnIndex + col,beginRowIndex + mark + mergeCount - 1,beginColumnIndex + col,text);
                            }
                        }
                        else
                        {
                            this.MergeCells(sheetIndex,beginRowIndex + mark,beginColumnIndex + col,beginRowIndex + mark + mergeCount - 1,beginColumnIndex + col,text);
                            mergeCount = 1;
                            mark = row + 1;
                        }
                           
                    }       
                }
            }
        }

 

转载于:https://www.cnblogs.com/ly5201314/archive/2009/06/06/1497448.html