C#操作Excel进行文件读写合并单元格列求和

1.添加引用

C#读取Excel文件需要先添加引用com组件：Microsoft.Office.Interop.Excel.dll

在解决方案资源管理器中右击引用→添加引用

添加引用

            using Microsoft.Office.Interop.Excel；

2.导入要处理的Excel文件（以对话框的形式选择要导入的文件）

            OpenFileDialog file = new OpenFileDialog();
            file.Multiselect = false;//只能选择一个文件
            file.Title = "选择需要处理的Excel文件";
            file.Filter= "Excel文件(*.xls)|*.*";
            try {
                if (file.ShowDialog() == System.Windows.Forms.DialogResult.OK) {
                    Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
                    app.DisplayAlerts = false;
                    app.ScreenUpdating = false;
                    Microsoft.Office.Interop.Excel.Workbook workb = app.Workbooks.Open(file.FileName, Type.Missing);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("导入文件时出错");
            }

3.Excel表的插入行插入列

Microsoft.Office.Interop.Excel.Worksheet worksheet = workb.Worksheets[1];//Worksheet从1开始计数
int rowcount = worksheet.UsedRange.Rows.Count;//计算Excel有效范围内的最大行数
int colcount = worksheet.UsedRange.Columns.Count;//计算Excel有效范围内的最大列数
//插入行
Microsoft.Office.Interop.Excel.Range InsertRow1 = worksheet.Rows[3, Type.Missing];//插入新的第三行
InsertRow1.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, Type.Missing);
Microsoft.Office.Interop.Excel.Range InsertRow2 = worksheet.Rows[5, Type.Missing];//插入新的第五行
InsertRow2.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, Type.Missing);
//插入列
Microsoft.Office.Interop.Excel.Range InsertCol1 = worksheet.Columns[1, Type.Missing];//插入新的第一列
InsertCol1.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftToRight, Type.Missing);

4.Excel表的单元格加减乘除

                    worksheet.Cells[1, 1] = "数1";
                    worksheet.Cells[1, 2] = "数2";
                    worksheet.Cells[1, 3] = "加";
                    worksheet.Cells[1, 4] = "减";
                    worksheet.Cells[1, 5] = "乘";
                    worksheet.Cells[1, 6] = "除";
                    worksheet.Cells[2, 1] = 12;
                    worksheet.Cells[2, 2] = 123;
                    worksheet.Cells[2, 3] = worksheet.Cells[1, 1].Value + worksheet.Cells[1, 2].Value;//加
                    worksheet.Cells[2, 4] = worksheet.Cells[1, 1].Value - worksheet.Cells[1, 2].Value;//减
                    worksheet.Cells[2, 5] = worksheet.Cells[1, 1].Value * worksheet.Cells[1, 2].Value;//乘
                    worksheet.Cells[2, 6] = worksheet.Cells[1, 1].Value / worksheet.Cells[1, 2].Value;//除

5.Excel表的合并单元格

                    Range r1 = worksheet.Range[worksheet.Cells[3,1],worksheet.Cells[3,12]];//合并第三行第一列到底12列的单元格
                    r1.Clear();//清除要合并区域中的内容，避免跳出仅保留左上角内容的提示
                    r1.MergeCells = true;
                    r1.Value2 = "合并后的单元格";

6.Excel表的格式设置

                    r1.Font.Name = "等线";//设置字体
                    r1.Font.Color = Color.Pink;//设置字体颜色
                    r1.Font.Size = 14;//设置字体大小
                    r1.Font.Bold = false;//设置字体是否加粗
                    r1.HorizontalAlignment = Constants.xlCenter;//设置字体水平居中
                    r1.VerticalAlignment = Constants.xlCenter;//设置字体垂直居中
                    Microsoft.Office.Interop.Excel.Borders border = r1.Borders;//设置边框样式
                    border.LineStyle = XlLineStyle.xlContinuous;//设置边框线型
                    border.Weight = XlBorderWeight.xlThin;//设置边框粗细

7.保存修改后的结果

原始excel文件是一个空白表，编辑处理之后的excel内容如下

8.关闭Excel进程 

为避免出现上述情况，需要关闭Excel进程（添加引用 using System.Diagnostics;）

                    Process[] procs = Process.GetProcessesByName("excel");
                    foreach (Process pro in procs)
                    {
                        pro.Kill();//杀掉Excel进程
                    }