【C#】Excel操作——两个Excel表格比较，如果相同跳过，如果不同将复制到一个表格

需求

客户有两个xlsx表格，表格1数据全，但是身份证号码和手机号码有错误；表格2数据少些，但是身份证号码和手机号码全部正确，用户要求把表格2中的身份证号码和手机号与表格1作比较，如果表格1的身份证号码或手机号错误，进行更正

我对xlsx表格不是很熟悉，所以用c#编写了一个小程序，主要用Aspose.Cells.dll实现了用户的需求(下载dll请单击这里)，现将主要代码记录如下：

public static void Compare(string ExcelPath1, string ExcelPath2)
        {
            //try
            //{
                //先获得WorkBook 类似数据集DataSet
                Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(ExcelPath1);
                //获取数据工作表 可以用数字索引 类似DataTable
                Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
                //数据范围 默认从第0行 第0列开始
                //最大行 最大列
                //是否读取列字段名  
                Aspose.Cells.Workbook workbook2 = new Aspose.Cells.Workbook(ExcelPath2);
                Aspose.Cells.Worksheet sheet2 = workbook2.Worksheets[0];
                Cell startcell = sheet.Cells[0, 0];
                for (int i = 0; i < sheet2.Cells.MaxDataRow; i++)
                {
                    Cell startcel = sheet2.Cells[i, 5];
                    if (startcel.Value!=null&&startcel.Value.ToString()!="姓名")
                    {
                        Cell firstCell2 = sheet.Cells.FindStringContains(startcel.Value.ToString(), startcell);
                        if (sheet2.Cells[i, 9].Value==null)
                        {
                            continue;
                        }
                        if (sheet2.Cells[i, 9].Value != null && sheet.Cells[firstCell2.Row, firstCell2.Column + 1].Value == null)
                        {
                            sheet.Cells[firstCell2.Row, firstCell2.Column + 1].PutValue(sheet2.Cells[i, 9].Value.ToString().Trim());
                            Style style = sheet.Cells[firstCell2.Row, firstCell2.Column + 1].GetStyle();
                            style.Font.Color = Color.Yellow;
                            sheet.Cells[firstCell2.Row, firstCell2.Column + 1].SetStyle(style);
                            WriteLog(startcel.Value.ToString() + "====2");
                        }
                        else
                        {
                            if (sheet2.Cells[i, 9].Value.ToString().Trim() != sheet.Cells[firstCell2.Row, firstCell2.Column + 1].Value.ToString().Trim())
                            {
                                sheet.Cells[firstCell2.Row, firstCell2.Column + 1].PutValue(sheet2.Cells[i, 9].Value.ToString().Trim());
                                Style style = sheet.Cells[firstCell2.Row, firstCell2.Column + 1].GetStyle();
                                style.Font.Color = Color.Red;
                                sheet.Cells[firstCell2.Row, firstCell2.Column + 1].SetStyle(style);
                                WriteLog(startcel.Value.ToString());
                            }
                        }

                        if (sheet2.Cells[i, 16].Value == null)
                        {
                            continue;
                        }
                        if (sheet2.Cells[i, 16].Value != null && sheet.Cells[firstCell2.Row, firstCell2.Column + 2].Value == null)
                        {
                            sheet.Cells[firstCell2.Row, firstCell2.Column + 3].PutValue(sheet2.Cells[i, 16].Value.ToString().Trim());
                            Style style = sheet.Cells[firstCell2.Row, firstCell2.Column + 3].GetStyle();
                            style.Font.Color = Color.Yellow;
                            sheet.Cells[firstCell2.Row, firstCell2.Column + 3].SetStyle(style);
                            WriteLog(startcel.Value.ToString() + "====3");
                        }
                        else
                        {
                            if (sheet2.Cells[i, 16].Value.ToString().Trim() != sheet.Cells[firstCell2.Row, firstCell2.Column + 2].Value.ToString().Trim())
                            {
                                sheet.Cells[firstCell2.Row, firstCell2.Column + 3].PutValue(sheet2.Cells[i, 16].Value.ToString().Trim());
                                Style style = sheet.Cells[firstCell2.Row, firstCell2.Column + 3].GetStyle();
                                style.Font.Color = Color.Red;
                                sheet.Cells[firstCell2.Row, firstCell2.Column + 3].SetStyle(style);
                                WriteLog(startcel.Value.ToString() + "====4");
                            }
                        }
                    }
                }
               // dt = sheet.Cells.ExportDataTable(0, 0, sheet.Cells.MaxDataRow + 1, sheet.Cells.MaxDataColumn + 1, true);
                
                //Cell firstCell = sheet.Cells.FindStringContains("赵秀林", startcell);
                //Cell cell = sheet.Cells[firstCell.Row, firstCell.Column + 2];
                //cell.PutValue(123456.789);
                //Style style = cell.GetStyle();
                //style.Font.Color = Color.Red;
                //cell.SetStyle(style);
                workbook.Save("d:\\z3.xlsx");
                // DataTableExport(dt, "d:\\z1.xlsx");
            //}
            //catch (Exception ex)
            //{
            //    dt = null;
            //}
           // return dt;
        }

实现了用户需求后，我整理其它一些代码以备后用。

1、xlsx转DataTable

public static DataTable GetDataTable(string ExcelPath, string SheetName)
        {
            DataTable dt = new DataTable();
            try
            {
                //先获得WorkBook 类似数据集DataSet
                Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(ExcelPath);
                //获取数据工作表 可以用数字索引 类似DataTable
                Aspose.Cells.Worksheet sheet = workbook.Worksheets[SheetName];
                //数据范围 默认从第0行 第0列开始
                //最大行 最大列
                //是否读取列字段名               
                dt = sheet.Cells.ExportDataTable(0, 0, sheet.Cells.MaxDataRow + 1, sheet.Cells.MaxDataColumn + 1, true);
                Cell startcell=sheet.Cells[0,0];
                Cell firstCell = sheet.Cells.FindStringContains("赵秀林", startcell);
                Cell cell = sheet.Cells[firstCell.Row,firstCell.Column+2];
                cell.PutValue(123456.789);
                Style style = cell.GetStyle();
                style.Font.Color = Color.Red;
                cell.SetStyle(style);
            }
            catch (Exception)
            {
                dt = null;
            }
            return dt;
        }

2、DataTable转Excel

        /// <summary>
/// DataTable数据导出Excel
/// </summary>
/// <param name="data">DataTable 数据</param>
/// <param name="filepath">文件保存路径名称</param>
        public static void DataTableExport(System.Data.DataTable data, string filepath)
        {
            // 创建工作簿
            Aspose.Cells.Workbook book = new Workbook();
            // 创建工作表
            Worksheet sheet = book.Worksheets[0];
            // 单元格
            Cells cells = sheet.Cells;
            // 生成行 列名行 
            for (int i = 0; i < data.Columns.Count; i++)
            {
                cells[0, i].PutValue(data.Columns[i].ColumnName);
            }
            // 生成数据行 
            for (int i = 0; i < data.Rows.Count; i++)
            {
                for (int k = 0; k < data.Columns.Count; k++)
                {
                    cells[1 + i, k].PutValue(data.Rows[i][k].ToString()); //添加数据
                }
            }
            // 自适应宽
            sheet.AutoFitColumns();
            // 保存
            book.Save(filepath);
            GC.Collect();
        }

源代码下载请单击这里