近期因?yàn)楣ぷ鞯男枰?，?jīng)常要批量處理大量的Excel文件，如果純手工一個(gè)個(gè)修改，非常的麻煩，于是寫(xiě)了這么一個(gè)幫助類，希望能對(duì)你有所幫助。里面很多方法可以進(jìn)一步推廣，增減適當(dāng)?shù)膮?shù)，部分方法用到了C# 4.0新特性，如果需要調(diào)試，請(qǐng)安裝Visual Studio 2010。

示例代碼下載

本系列所有示例代碼均在 Visual Studio 2010 Ultimate RC + Office 2010 Professional Plus Beta x64 上測(cè)試通過(guò)。 

首先添加引用：

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.rzrgm.cn/brooks-dotnet/
    /// Email: undead_47@163.com
    /// </summary>
    class ExcelHelp
    {
        /// <summary>
        /// 將一個(gè)工作簿中一個(gè)工作表中的內(nèi)容復(fù)制到另一個(gè)工作簿的一個(gè)工作表中
        /// </summary>
        /// <param name="v_strSourceWorkbook">源工作薄的完整路徑</param>
        /// <param name="v_strSourceWorksheet">源工作表名</param>
        /// <param name="v_strDestWorkbook">目標(biāo)工作薄的完整路徑</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聯(lián)系起來(lái)
                        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中表示顏色的方法，其返回一個(gè)整數(shù)，表示一種特定的顏色：

 但是我們平時(shí)用的最多的是RGB表示的顏色，可以用

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

將ColorIndex與RGB進(jìn)行關(guān)聯(lián)。

代碼

        /// <summary>
        /// 判斷一個(gè)工作簿的一個(gè)工作表有沒(méi)有添加保護(hù)
        /// </summary>
        /// <param name="v_strDir">工作簿的完整路徑</param>
        /// <param name="v_strSheetName">工作表名</param>
        private static void fn判斷工作表是否被保護(hù)(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進(jìn)行破解，經(jīng)過(guò)測(cè)試，Passware 可以破解Office 2003/2007/2010，其官方主頁(yè)稱可以破解Windows 7 的 BitLocker 加密技術(shù)。

代碼

        /// <summary>
        /// 判斷一個(gè)工作表中所有的單元格是否包含公式
        /// </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());
        }

批量轉(zhuǎn)換Excel的格式

代碼

        /// <summary>
        /// 批量轉(zhuǎn)換Excel 2003 至Excel 2007/2010格式
        /// 可以修改XlFileFormat枚舉的值來(lái)轉(zhuǎn)換為想要的格式
        /// </summary>
        /// <param name="v_strDir">工作簿的查找路徑</param>
        private static void fn批量轉(zhuǎn)換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();
            }
        }

比較兩個(gè)工作表的內(nèi)容

代碼

        /// <summary>
        /// 比較兩個(gè)工作表的內(nèi)容
        /// </summary>
        /// <param name="v_strSourcePath">第一個(gè)工作簿的路徑</param>
        /// <param name="v_strDestPath">第二個(gè)工作簿的路徑</param>
        private static void fn比較兩個(gè)工作表的內(nèi)容(string v_strSourcePath, string v_strDestPath)
        {
            Application app = new Application();
            Workbook srcBook = app.Workbooks.Open(v_strSourcePath);
            Workbook destBook = app.Workbooks.Open(v_strDestPath);

            //記錄查找結(jié)果到本文文件中
            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(@"當(dāng)前工作表： " + sheet.Name + " 第" + i + "行" + j + "列數(shù)據(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();
            }
        }

小結(jié)：

本次通過(guò)一系列的實(shí)例探討了Excel的操作方法，我覺(jué)得還挺實(shí)用，你可以根據(jù)需求進(jìn)行完善。代碼僅供測(cè)試，如果欲操作的Excel數(shù)量巨大，請(qǐng)謹(jǐn)慎使用。

后續(xù)篇章我會(huì)繼續(xù)研究Excel中的操作。