使用C#对Excel文件进行内容筛选

最新推荐文章于 2024-05-14 15:15:30 发布

Lundli

最新推荐文章于 2024-05-14 15:15:30 发布

阅读量5.6k

点赞数 6

分类专栏： C# 文章标签： C# COM组件 C#文件操作 C#读取Excel

本文链接：https://blog.csdn.net/qq_41543031/article/details/93632598

版权

C# 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

（一）问题

一个使用C#编写的小工具，对指定文件夹下的所有Excel文件进行筛选，如果一个文件中含有某个指定的字符串，记录该文件，并且定位这个字符串出现的位置。

网上使用C#对Excel文件进行读写的方法主要有四种：

（1）使用OleDb的方法读取Excel文件；

（2）将xls文件转化为csv文件再进行读取；

（3）使用NPOI进行Excel文件读取；

（4）使用COM组件进行读取。

（二）尝试

这几种读取Excel文件的方法我都了解了一下，每种方法各有利弊。下面进行简单的描述：

（1）使用OleDb的方法读取Excel文件

这种方法相关内容在网上可以找到很多，很多人把这种方法当作读取Excel文件的主流。简单来说就是将Excel文件当作数据库进行操作，对表中内容使用sql语句进行提取，其间需要使用到DataSet类。

顺便说一句，对于不同的.net数据提供者，ADO.NET采用不同的Connection对象连接数据库。这些Connection对我们屏蔽了实现的具体细节，并提供了一种统一的实现方式。Connection类有四种：SqlConnection、OleDbConnection、OdbcConnection和OracleConnection。分别用来连接SQL Server数据库、OLE DB数据库（如Access）、ODBC数据库、Oracle数据库。与数据库的所有通讯都是通过Connection对象完成的。本文简单谈一谈OleDb。

建立OleDb连接的核心是建立连接字符串ConnectionString，主要注意的是，对xls文件（Excel2003）和xlsx文件（Excel2007）进行连接的连接字符串不同。假设excelPath为目的excel文件的路径，则建立OleDb连接的语句分别为：

OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelPath + ";" + "Extended Properties=\"Excel 8.0;HDR=yes;IMEX=1;\"");

OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.12.0;Data Source=" + excelPath + ";" + "Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1;\"");

其中"HDR=yes"是说Excel文件的第一列是列明而不是数。如果列中数据类型不一致，使用"IMEX=1"可以避免类型冲突。下面是一段网上的示例代码：

public DataSet ExcelToDS(string Path)  
{  
    string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";  
    OleDbConnection conn = new OleDbConnection(strConn);  
    conn.Open(); 
    string strExcel = "";  
    OleDbDataAdapter myCommand = null;  
    DataSet ds = null;  
    strExcel="select * from [sheet1$]";  
    myCommand = new OleDbDataAdapter(strExcel, strConn);  
    ds = new DataSet();  
    myCommand.Fill(ds,"table1");  
    return ds;  
}

但是在尝试这种方法解决问题的时候遇到了一些麻烦，比如说满足问题需要的Sql并不是很好写（数据库基本功稍差，惭愧）。另外在网上没有找到在使用OleDb读取文件的时候获取excel所有sheet表的方法（后来在另外一片博客中找到了，代码没有经过尝试：https://www.cnblogs.com/xifengyeluo/p/8617865.html）。再者DataSet数据类型也不是很熟悉，因此本方法没有继续下去。

（2）将xls文件转化为csv文件再进行读取

之前使用过Java读取xls文件，当时直接将xls文件当作csv文件（Comma-Separated Values File逗号分隔值文件）进行读取解析。C#也可以使用类似的方法读取xls文件，等价于使用文件流读取一个txt文本文件。

同样附上一段示例代码：

FileStream fs = new FileStream(excelPath, FileMode.Open, FileAccess.Read, FileShare.None);  
StreamReader sr = new StreamReader(fs, System.Text.Encoding.GetEncoding(936));  
string str = sr.ReadLine();  //读取表头
string target = Console.ReadLine();   //输入想要查询的字符串
while (str != null)  
{      
    int count = 0;
    str = sr.ReadLine();
    string[] strs = str.Split(',');  
    foreach(string s in strs)
    {
        if(s == target)
            Console.WriteLine(++count);
    }  
}     
sr.Close();

将xls文件转化为csv文件最大的问题在于分隔符的问题。一般来说，使用这种方法读取的时候，我们每读取一行字符串相当于读取了xls文件中的一行，之后会使用Split（‘,’）方法进行字符串分割从而获得表中的每一单元格的数据。但是如果原本xls正文中有逗号的话，分割会出现错误。另外还有很多问题需要考虑，比如说：一个文件多个sheet的获取方法、定位当前的单元格的方法、这种方法的实现有很多字符串操作。所以这种使用数据流来读取xls的方法并不“优美”。

（3）使用NPOI进行Excel文件读取

这种方法能够查阅到的资料过少，因此没有进行尝试。有时间的话，了解一下再来补充。

（4）使用COM组件进行读取

这种方法需要先引用Microsoft.Office.interop.Excel。可以很灵活的读取Excel中的数据，而且使用方式很丰富，基本上凡是打开OfficeExcel软件能够用鼠标点击完成的事，调用COM组件都能完成，而且可以调用Excel自身带的宏方法。

添加COM组件引用的过程如下图所示，使用的编译器为VS2017：

一个最直接的想法就是使用COM组件按照行列依次读取Excel文件中每个sheet的单元格，具体代码如下：

static void ReadExcel(string path, string target)
{
    Console.WriteLine(path);
    try
    {
        //如果要读取多个Excel文件建议将下面两个变量的声明和初始化放在全局变量的位置
        //每新建一次Excel.Application都相当于打开了一次Excel.exe，将其声明为全局变量可以提高性能
        Microsoft.Office.Interop.Excel._Application xlsApp = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbooks books = xlsApp.Workbooks;

        books.Open(path);
        Microsoft.Office.Interop.Excel.Sheets sheets = xlsApp.Worksheets;
        
        //注意：sheet和row，column的起始索引都是1
        for (int s = 1; s <= sheets.Count; s++)
        {
            Console.WriteLine(path + "\t" + s);    //打印搜索进度
            int rows = sheets[s].Rows.Count;
            int columns = sheets[s].Columns.Count;

            for(int i=1;i<=rows;i++)
            {
                for(int j=1;j<=columns;j++)
                {
                    //下面这一行获取了一个单元格
                    Microsoft.Office.Interop.Excel.Range cell = sheets[s].Range[xlsApp.Cells[i, j], xlsApp.Cells[i, j]];
                    if(cell.Text == target)
                    {
                        //将满足条件的单元格路径、sheet序列、位置、内容记录进入一个txt文件，这个函数需要自己实现
                        RecordIntoTXT(path, s, cell.Address, target);
                    }
                }    
            }
        }
    }
    catch (Exception e)
    {
        //sw1为一个log文件的文件流，将出现异常的文件路径记录到文件中
        //这个位置也可以使用throw抛出异常
        sw1.WriteLine(path);
    }
}

这种方法网上也能找到很多类似的代码，比如：

https://blog.csdn.net/qq_33459369/article/details/79314465中的代码。这个博客中的代码使用单线程读取Excel文件的时候使用了range.Select方法，这个方法相当于是在Excel表格中选中了一个范围内的格子（在这个代码语境下是选中了一个（i，j）位置的格子），然后再使用ActiveCell字段获取被选中的格子，再进行后续操作，比较麻烦，也没有必要。

到现在为止，已经完成了这个问题的核心，也就是读取Excel文件。但是这种方法有很大的不足。首先是效率问题，这种使用代码遍历单元格的执行效率极低，甚至到了一个不能接受的程度。另外，上面的示例代码只是判断了一下字符串相同，如果要进行字符串匹配的话（即判断target字符串是否被包含在表格的字符串中，最经典的字符串匹配算法是KMP算法，有兴趣的可以了解一下。当然，使用string内置的方法也可以）就又会多一些工作量。所以还有很大的提升空间，在一番尝试下，终于有了一个比较优美的解答。

（三）最终解

之前在描述COM组件的时候提了一句，使用COM组件可以调用Excel自身的宏方法。在Excel中寻找指定的内容最直接的办法当然是调用Excel本身的Find方法（相当于在Excel中使用Ctrl+F快捷键）。这样一来，可以避免之前代码中对行列进行的二重循环，也解决了字符串匹配的问题。唯一需要注意的地方在于使用Find+FindNext方法联合进行查找的时候需要定位初始位置，否则会进行无限循环，这也算是FindNext方法的特性吧。示例代码如下：

static void ReadExcel(string path, string target)
{
    Console.WriteLine(path);
    try
    {
        Microsoft.Office.Interop.Excel._Application xlsApp = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbooks books = xlsApp.Workbooks;
        books.Open(path);
        Microsoft.Office.Interop.Excel.Sheets sheets = xlsApp.Worksheets;

        for (int s = 1; s <= sheets.Count; s++)
        {
            Console.WriteLine(path + "\t" + s);
            Microsoft.Office.Interop.Excel.Range range = sheets[s].Cells;
            Microsoft.Office.Interop.Excel.Range ans = range.Find(target);
            if (ans == null) continue;
            string firstAddress = ans.Address;    //定位初始位置
            do
            {
                string text = ans.Text;
                RecordIntoTXT(path, s, ans.Address, text);
                ans = range.FindNext(ans.Cells[1, 1]);    //意思是从当前查找位置开始寻找下一个
            } while (ans != null && ans.Address != firstAddress);    //如果到达初始位置表示当前sheet查找结束
        }
    }
    catch (Exception e)
    {
        sw1.WriteLine(path);
    }
}

在使用COM组件进行读取Excel文件的时候，一定要注意最终程序的出口。如果在执行完上面的函数没有进行任何结束处理就退出程序的话，Excel程序其实还是在后台运行的，双击之前的Excel文件会提示正在被修改只能以只读模式打开。因此需要增加一个退出程序的函数。网上的资料中这个函数的实现方法都大致相同，如下给出示例：

static void QuitExcel()
{
    if (xlsApp != null)    //xlsApp为上面那个示例函数中的变量，当作全局变量声明了
        xlsApp.Quit();
    System.Diagnostics.Process[] pros = System.Diagnostics.Process.GetProcessesByName("excel");
    foreach (System.Diagnostics.Process pro in pros)
    {
        pro.Kill();    //杀死了所有excel进程
    }
    GC.Collect();    //进行垃圾回收，其实我并不知道为什么在这里要强制调用垃圾回收，只是网上这样写了，我就照做了
}

（四）除了读取Excel文件之外的其他功能实现

到现在位置，整个代码的核心部分就已经完成了。我们还需要完成一些核心的支持工作。

（1）文件夹下所有文件的遍历

这一部分纯属文件目录相关的操作，需要引入System.IO命名空间。基本思想是获取一个给定的根目录，将所有excel文件加入一个list中，再获取这个根目录下的所有子文件夹，使用相同的方法遍历所有的子文件夹，将里面的所有excel文件同样加载到同一个list中，示例代码如下：

static void GetFiles(string path)
{
    DirectoryInfo root = new DirectoryInfo(path);

    FileInfo[] files = root.GetFiles();        //获取根目录的excel文件
    string fileName;
    foreach (FileInfo f in files)
    {
        fileName = f.Name.ToLower();
        if (fileName.EndsWith(".xls") || fileName.EndsWith(".xlsx"))
        {
            allFiles.Add(f);    //allFiles是一个全局的List<FileInfo>
        }
    }

    //下面这几行遍历子节点
    DirectoryInfo[] directories = root.GetDirectories();
    foreach (DirectoryInfo d in directories)
        GetFiles(d.FullName);
}

（2）查找结果的记录

查找结果记录的方法可以自选，这里使用了存储在桌面下的txt文件中，开启结束文件流的示例代码如下:

//fs,sw,fs1,sw1为四个全局变量
static void StartFileStream()
{
    string desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);    //这一行获取桌面路径

    fs = new FileStream(desktopPath + "\\record.txt", FileMode.Create, FileAccess.Write);
    sw = new StreamWriter(fs);
    fs1 = new FileStream(desktopPath + "\\log.txt", FileMode.Create, FileAccess.Write);
    sw1 = new StreamWriter(fs1);
}

static void EndFileStream()
{
    sw.Flush();    //清空缓存
    sw.Close();    //关闭writer
    fs.Close();    //关闭文件流
    sw1.Flush();
    sw1.Close();
    fs1.Close();
}

到此位置，整个问题的功能基本实现。整体代码有机会的话，会上传到GitHub，到那时候再来这里附上链接。

注意：使用COM组件打开Excel文件的时候会出现一些问题。当你文件本身存在问题，即直接双击打开一个Excel文件在表头出现如下提示的时候，使用COM组件打开会失败，这时候当时的try-catch语句的功能就体现出来了，会将这个文件的路径记录到log中，便于后续人工操作。另外，有的Excel文件在打开的时候会弹出一个Warining对话框，让用户选择一些操作，这个时候使用COM组件进行读取的时候也弹出对话框让用户进行选择。还有一点需要注意，使用这种方法读取Excel文件之后，所有被读取过的xlsx没有问题，而被读取过的xls文件会在SVN中提示文件有修改，猜测可能是新版的Excel读取xls文件会就修改原有文件中的游标位置之类的辅助信息，文档本体内容并没有变化。

（←示例图片如左）

Lundli

关注

6
点赞
踩
48

收藏

觉得还不错? 一键收藏
1
评论
使用C#对Excel文件进行内容筛选

（一）问题一个使用C#编写的小工具，对指定文件夹下的所有Excel文件进行筛选，如果一个文件中含有某个指定的字符串，记录该文件，并且定位这个字符串出现的位置。网上使用C#对Excel文件进行读写的方法主要有四种：（1）使用OleDb的方法读取Excel文件；（2）将xls文件转化为csv文件再进行读取；（3）使用NPOI进行Excel文件读取；（4）使用COM组件进行读取...
复制链接

扫一扫