VSTO学习笔记（五）批量编辑Excel 2010 x64

近期因为工作的需要，经常要批量处理大量的Excel文件，如果纯手工一个个修改，非常的麻烦，于是写了这么一个帮助类，希望能对你有所帮助。里面很多方法可以进一步推广，增减适当的参数，部分方法用到了C# 4.0新特性，如果需要调试，请安装Visual Studio 2010。

示例代码下载

本系列所有示例代码均在 Visual Studio 2010 Ultimate RC + Office 2010 Professional Plus Beta x64 上测试通过。 

 

首先添加引用：

using Microsoft.Office.Interop.Excel;

using System.Drawing;

using System.IO;

 

代码

using  System;
using  System.Collections.Generic;
using  System.Linq;
using  System.Text;
using  Microsoft.Office.Interop.Excel;
using  System.Drawing;
using  System.IO;

namespace  ExcelHelp
{
     ///   <summary>
     ///  Date: 2010.03.31
     ///  Author: Mr.Brooks
     ///  HomePage:  http://www.cnblogs.com/brooks-dotnet/
     ///  Email: undead_47@163.com
     ///   </summary>
     class  ExcelHelp
    {
         ///   <summary>
         ///  将一个工作簿中一个工作表中的内容复制到另一个工作簿的一个工作表中
         ///   </summary>
         ///   <param name="v_strSourceWorkbook"> 源工作薄的完整路径 </param>
         ///   <param name="v_strSourceWorksheet"> 源工作表名 </param>
         ///   <param name="v_strDestWorkbook"> 目标工作薄的完整路径 </param>
         ///   <param name="v_strDestWorksheet"> 目的工作表名 </param>
         private   static   void  fnCopy( string  v_strSourceWorkbook,  string  v_strSourceWorksheet,  string  v_strDestWorkbook,  string  v_strDestWorksheet)
        {
            Application app  =   new  Application();
            Workbook srcbook  =  app.Workbooks.Open(v_strSourceWorkbook);
            Worksheet srcsheet  =  srcbook.Worksheets[v_strSourceWorksheet];

            Workbook destbook  =  app.Workbooks.Open(v_strDestWorkbook);
            Worksheet destsheet  =  destbook.Worksheets[v_strDestWorksheet];

             try
            {
                 // Worksheet.UsedRange: 获取工作表中所使用的范围
                 for  ( int  i  =   1 ; i  <=  srcsheet.UsedRange.Rows.Count; i ++ )
                {
                     for  ( int  j  =   1 ; j  <=  srcsheet.UsedRange.Columns.Count; j ++ )
                    {
                         // 将Excel的颜色与RGB联系起来
                         if  (srcsheet.Cells[i, j].Interior.Color  !=  ColorTranslator.ToOle(Color.FromArgb( 255 ,  204 ,  153 )))
                        {
                            destsheet.Cells[i, j].Value2  =  srcsheet.Cells[i, j].Value2;
                        }
                    }
                }
                 // 屏蔽Excel的提示信息
                app.Application.DisplayAlerts  =   false ;
                destbook.Save();
            }
             catch  (Exception ex)
            {
                 throw  ex;
            }
             finally  
            {
                app.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(srcbook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(srcsheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(destbook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(destsheet);

                app  =   null ;
                srcbook  =   null ;
                destbook  =   null ;
                srcsheet  =   null ;
                destsheet  =   null ;
                GC.Collect();
            }
        }

 

Range.Interior.Color是Office中表示颜色的方法，其返回一个整数，表示一种特定的颜色：

 但是我们平时用的最多的是RGB表示的颜色，可以用

srcsheet.Cells[i, j].Interior.Color != ColorTranslator.ToOle(Color.FromArgb(255, 204, 153))

将ColorIndex与RGB进行关联。

 

 

代码

         ///   <summary>
         ///  判断一个工作簿的一个工作表有没有添加保护
         ///   </summary>
         ///   <param name="v_strDir"> 工作簿的完整路径 </param>
         ///   <param name="v_strSheetName"> 工作表名 </param>
         private   static   void  fn判断工作表是否被保护( string  v_strDir,  string  v_strSheetName)
        {
            StringBuilder sb  =   new  StringBuilder();
            Application app  =   new  Application();
            DirectoryInfo dir  =   new  DirectoryInfo(v_strDir);

             try
            {
                 // 递归查找所有Excel 2007/2010的文件
                 foreach  (FileInfo f  in  dir.GetFiles( " *.xlsx " , SearchOption.AllDirectories))
                {
                    Workbook book  =  app.Workbooks.Open(f.FullName);
                     if  (book.Worksheets[v_strSheetName].ProtectContents)
                    {
                        sb.Append(book.Name  +   " , " );
                    }
                    app.Application.DisplayAlerts  =   false ;
                    book.Save();
                }
            }
             catch  (Exception ex)
            {
                 throw  ex;
            }
             finally
            {
                app.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                app  =   null ;
                GC.Collect();
            }

            Console.WriteLine(sb.ToString());
        }

若不知道Excel工作簿或工作表的密码，可以用Passware进行破解，经过测试，Passware 可以破解Office 2003/2007/2010，其官方主页称可以破解Windows 7 的 BitLocker 加密技术。

 

 

代码

         ///   <summary>
         ///  判断一个工作表中所有的单元格是否包含公式
         ///   </summary>
         ///   <param name="v_strPath"> 工作簿的完整路径 </param>
         private   static   void  fn判断单元格是否包含公式( string  v_strPath)
        {
            StringBuilder sb  =   new  StringBuilder();
            Application app  =   new  Application();
            DirectoryInfo dir  =   new  DirectoryInfo(v_strPath);

             try
            {
                 foreach  (FileInfo file  in  dir.GetFiles( " *.xlsx " , SearchOption.AllDirectories))
                {
                    Workbook book  =  app.Workbooks.Open(file.FullName);
                     foreach  (Worksheet sheet  in  book.Worksheets)
                    {
                         for  ( int  i  =   1 ; i  <  sheet.UsedRange.Rows.Count; i ++ )
                        {
                             for  ( int  j  =   1 ; j  <  sheet.UsedRange.Columns.Count; j ++ )
                            {
                                 if  (sheet.Cells[i, j].Interior.Color  !=  ColorTranslator.ToOle(Color.FromArgb( 255 ,  204 ,  153 )))
                                {
                                     if  (sheet.Cells[i, j].HasFormula)
                                    {
                                        sb.Append(book.Name  +   "   "   +  sheet.Name  +   "  第 "   +  i.ToString()  +   " 行第 "   +  j.ToString()  +   " 列包含公式！\n " );
                                    }
                                }
                            }
                        }
                    }
                }
            }
             catch  (Exception ex)
            {
                 throw  ex;
            }
             finally
            {
                app.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                app  =   null ;
                GC.Collect();
            }

            Console.WriteLine(sb.ToString());
        }

 

 

 

代码

         ///   <summary>
         ///  获取指定工作簿的所有工作表
         ///   </summary>
         ///   <param name="v_strDir"> 工作簿的完整路径 </param>
         private   static   void  fn获取指定工作簿的所有工作表( string  v_strDir)
        {
            StringBuilder sb  =   new  StringBuilder();
            Application app  =   new  Application();
            DirectoryInfo dir  =   new  DirectoryInfo(v_strDir);

             try
            {
                 foreach  (FileInfo f  in  dir.GetFiles( " *.xlsx " , SearchOption.AllDirectories))
                {
                    Workbook book  =  app.Workbooks.Open(f.FullName);
                    sb.Append(book.Name);
                     foreach  (Worksheet sheet  in  book.Worksheets)
                    {
                        sb.Append(sheet.Name  +   "   " );
                    }
                    sb.Append( " \n " );
                    app.Application.DisplayAlerts  =   false ;
                    book.Save();
                }
            }
             catch  (Exception ex)
            {
                 throw  ex;
            }
             finally
            {
                app.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                app  =   null ;
                GC.Collect();
            }

            Console.WriteLine(sb.ToString());
        }

 

 

批量转换Excel的格式

 

代码

         ///   <summary>
         ///  批量转换Excel 2003 至Excel 2007/2010格式
         ///  可以修改XlFileFormat枚举的值来转换为想要的格式
         ///   </summary>
         ///   <param name="v_strDir"> 工作簿的查找路径 </param>
         private   static   void  fn批量转换Excel文件格式( string  v_strDir)
        {
            Application app  =   new  Application();
            DirectoryInfo dir  =   new  DirectoryInfo(v_strDir);
            Workbook book;
            app.Application.DisplayAlerts  =   false ;

             try
            {
                 foreach  (FileInfo fi  in  dir.GetFiles( " *.xls " , SearchOption.AllDirectories))
                {
                    book  =  app.Workbooks.Open(fi.FullName);
                    book.SaveAs(fi.DirectoryName  +   @" \ "   +  fi.Name.Replace(fi.Extension,  string .Empty)  +   " .xlsx " , XlFileFormat.xlOpenXMLWorkbook);
                }
            }
             catch  (Exception ex)
            {
                 throw  ex;
            }
             finally
            {
                app.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                app  =   null ;
                GC.Collect();
            }
        }

 

 

比较两个工作表的内容

 

代码

         ///   <summary>
         ///  比较两个工作表的内容
         ///   </summary>
         ///   <param name="v_strSourcePath"> 第一个工作簿的路径 </param>
         ///   <param name="v_strDestPath"> 第二个工作簿的路径 </param>
         private   static   void  fn比较两个工作表的内容( string  v_strSourcePath,  string  v_strDestPath)
        {
            Application app  =   new  Application();
            Workbook srcBook  =  app.Workbooks.Open(v_strSourcePath);
            Workbook destBook  =  app.Workbooks.Open(v_strDestPath);

             // 记录查找结果到本文文件中
            FileStream fs  =   new  FileStream( " log.txt " , FileMode.OpenOrCreate, FileAccess.ReadWrite);
            StreamWriter sw  =   new  StreamWriter(fs);

             try
            {
                 foreach  (Worksheet sheet  in  srcBook.Worksheets)
                {
                     for  ( int  i  =   1 ; i  <=  sheet.UsedRange.Rows.Count; i ++ )
                    {
                         for  ( int  j  =   1 ; j  <=  sheet.UsedRange.Columns.Count; j ++ )
                        {
                             if  (sheet.Cells[i, j].Interior.Color  !=  ColorTranslator.ToOle(Color.FromArgb( 255 ,  204 ,  153 )))
                            {
                                 string  src  =  sheet.Cells[i, j].Value  ==   null   ?   string .Empty : sheet.Cells[i, j].Value.ToString();
                                 string  dest  =  destBook.Worksheets[sheet.Name].Cells[i, j].Value  ==   null   ?   string .Empty : destBook.Worksheets[sheet.Name].Cells[i, j].Value.ToString();

                                 if  (src  !=  dest)
                                {
                                    sw.WriteLine( @" 当前工作表：  "   +  sheet.Name  +   "  第 "   +  i  +   " 行 "   +  j  +   " 列数据不相同！ " );
                                }
                            }
                        }
                    }
                }
            }
             catch  (Exception ex)
            {
                 throw  ex;
            }
             finally  
            {
                sw.Flush();
                sw.Close();
                app.Application.DisplayAlerts  =   false ;
                srcBook.Save();
                destBook.Save();

                app.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(srcBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(destBook);
                app  =   null ;
                srcBook  =   null ;
                destBook  =   null ;
                GC.Collect();
            }
        }

 

 

小结：

本次通过一系列的实例探讨了Excel的操作方法，我觉得还挺实用，你可以根据需求进行完善。代码仅供测试，如果欲操作的Excel数量巨大，请谨慎使用。

后续篇章我会继续研究Excel中的操作。

转载于:https://www.cnblogs.com/brooks-dotnet/archive/2010/03/31/1701697.html