批量编辑Excel x64.docx

1、批量编辑Excel x64批量编辑Excel 2010 x64首先添加引用：using Microsoft.Office.Interop.Excel;using System.Drawing;using System.IO;代码usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Text;usingMicrosoft.Office.Interop.Excel;usingSystem.Drawing;usingSystem.IO;namespaceExcelHelp/Date:2010.03.31/Au

2、thor:Mr.Brooks/HomePage:/Email:undead_47/classExcelHelp/将一个工作簿中一个工作表中的内容复制到另一个工作簿的一个工作表中/源工作薄的完整路径/源工作表名/目标工作薄的完整路径/目的工作表名privatestaticvoidfnCopy(stringv_strSourceWorkbook,stringv_strSourceWorksheet,stringv_strDestWorkbook,stringv_strDestWorksheet)Applicationapp=newApplication();Workbooksrcbook=app.

3、Workbooks.Open(v_strSourceWorkbook);Worksheetsrcsheet=srcbook.Worksheetsv_strSourceWorksheet;Workbookdestbook=app.Workbooks.Open(v_strDestWorkbook);Worksheetdestsheet=destbook.Worksheetsv_strDestWorksheet;try/Worksheet.UsedRange:获取工作表中所使用的范围for(inti=1;i=srcsheet.UsedRange.Rows.Count;i+)for(intj=1;j=

4、srcsheet.UsedRange.Columns.Count;j+)/将Excel的颜色与RGB联系起来if(srcsheet.Cellsi,j.Interior.Color!=ColorTranslator.ToOle(Color.FromArgb(255,204,153)destsheet.Cellsi,j.Value2=srcsheet.Cellsi,j.Value2;/屏蔽Excel的提示信息app.Application.DisplayAlerts=false;destbook.Save();catch(Exceptionex)throwex;finallyapp.Quit();

5、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.Rel

6、easeComObject(destsheet);app=null;srcbook=null;destbook=null;srcsheet=null;destsheet=null;GC.Collect();Range.Interior.Color是Office中表示颜色的方法，其返回一个整数，表示一种特定的颜色：但是我们平时用的最多的是RGB表示的颜色，可以用srcsheet.Cellsi,j.Interior.Color!=ColorTranslator.ToOle(Color.FromArgb(255,204,153)将ColorIndex与RGB进行关联。代码/判断一个工作簿的一个工作表

7、有没有添加保护/工作簿的完整路径/工作表名privatestaticvoidfn判断工作表是否被保护(stringv_strDir,stringv_strSheetName)StringBuildersb=newStringBuilder();Applicationapp=newApplication();DirectoryInfodir=newDirectoryInfo(v_strDir);try/递归查找所有Excel2007/2010的文件foreach(FileInfofindir.GetFiles(*.xlsx,SearchOption.AllDirectories)Workbook

8、book=app.Workbooks.Open(f.FullName);if(book.Worksheetsv_strSheetName.ProtectContents)sb.Append(book.Name+,);app.Application.DisplayAlerts=false;book.Save();catch(Exceptionex)throwex;finallyapp.Quit();System.Runtime.InteropServices.Marshal.ReleaseComObject(app);app=null;GC.Collect();Console.WriteLine

9、(sb.ToString();若不知道Excel工作簿或工作表的密码，可以用Passware进行破解，经过测试，Passware 可以破解Office 2003/2007/2010，其官方主页称可以破解Windows 7 的 BitLocker 加密技术。代码/判断一个工作表中所有的单元格是否包含公式/工作簿的完整路径privatestaticvoidfn判断单元格是否包含公式(stringv_strPath)StringBuildersb=newStringBuilder();Applicationapp=newApplication();DirectoryInfodir=newDirect

10、oryInfo(v_strPath);tryforeach(FileInfofileindir.GetFiles(*.xlsx,SearchOption.AllDirectories)Workbookbook=app.Workbooks.Open(file.FullName);foreach(Worksheetsheetinbook.Worksheets)for(inti=1;isheet.UsedRange.Rows.Count;i+)for(intj=1;jsheet.UsedRange.Columns.Count;j+)if(sheet.Cellsi,j.Interior.Color!=

11、ColorTranslator.ToOle(Color.FromArgb(255,204,153)if(sheet.Cellsi,j.HasFormula)sb.Append(book.Name+sheet.Name+第+i.ToString()+行第+j.ToString()+列包含公式！n);catch(Exceptionex)throwex;finallyapp.Quit();System.Runtime.InteropServices.Marshal.ReleaseComObject(app);app=null;GC.Collect();Console.WriteLine(sb.ToS

12、tring();代码/获取指定工作簿的所有工作表/工作簿的完整路径privatestaticvoidfn获取指定工作簿的所有工作表(stringv_strDir)StringBuildersb=newStringBuilder();Applicationapp=newApplication();DirectoryInfodir=newDirectoryInfo(v_strDir);tryforeach(FileInfofindir.GetFiles(*.xlsx,SearchOption.AllDirectories)Workbookbook=app.Workbooks.Open(f.Full

13、Name);sb.Append(book.Name);foreach(Worksheetsheetinbook.Worksheets)sb.Append(sheet.Name+);sb.Append(n);app.Application.DisplayAlerts=false;book.Save();catch(Exceptionex)throwex;finallyapp.Quit();System.Runtime.InteropServices.Marshal.ReleaseComObject(app);app=null;GC.Collect();Console.WriteLine(sb.T

14、oString();批量转换Excel的格式代码/批量转换Excel2003至Excel2007/2010格式/可以修改XlFileFormat枚举的值来转换为想要的格式/工作簿的查找路径privatestaticvoidfn批量转换Excel文件格式(stringv_strDir)Applicationapp=newApplication();DirectoryInfodir=newDirectoryInfo(v_strDir);Workbookbook;app.Application.DisplayAlerts=false;tryforeach(FileInfofiindir.GetFil

15、es(*.xls,SearchOption.AllDirectories)book=app.Workbooks.Open(fi.FullName);book.SaveAs(fi.DirectoryName+fi.Name.Replace(fi.Extension,string.Empty)+.xlsx,XlFileFormat.xlOpenXMLWorkbook);catch(Exceptionex)throwex;finallyapp.Quit();System.Runtime.InteropServices.Marshal.ReleaseComObject(app);app=null;GC

16、.Collect();比较两个工作表的内容代码/比较两个工作表的内容/第一个工作簿的路径/第二个工作簿的路径privatestaticvoidfn比较两个工作表的内容(stringv_strSourcePath,stringv_strDestPath)Applicationapp=newApplication();WorkbooksrcBook=app.Workbooks.Open(v_strSourcePath);WorkbookdestBook=app.Workbooks.Open(v_strDestPath);/记录查找结果到本文文件中FileStreamfs=newFileStream(log.txt,FileMode.OpenOrCreate,FileAccess.ReadWrite);StreamWritersw=newStreamWriter(fs);tryforeach(WorksheetsheetinsrcBook.Worksheets)for(inti=1;i=sheet.UsedRange.Rows.Count;i+)