原文: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;
{
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 ))
将ColorIndex与RGB进行关联。
/// 判断一个工作簿的一个工作表有没有添加保护
/// </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 加密技术。
{
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>
/// <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的格式
/// 批量转换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();
}
}
比较两个工作表的内容
{
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中的操作。